def _store_word(db, word, uid):
if word['try_again'] == False: return word
retval = {'word': word['word'], 'oword': word['oword'], 'cid': word['cid'], 'confirmed': False,
'wid': None, 'error': None}
if not jotools.checkword(word['word']):
retval['wid'] = None
retval['error'] = _(u'Forbidden characters in the word')
retval['try_again'] = False
return retval
if word['cid'] == None:
retval['wid'] = None
retval['error'] = _(u'Word class must be set')
retval['try_again'] = True
return retval
if word['cid'] != 0:
if not word['confirmed']:
homonyms = _list_homonyms(db, word['word'])
if len(homonyms) > 0:
retval['wid'] = None
retval['error'] = homonyms
retval['try_again'] = True
return retval
retval['wid'] = db.query("SELECT nextval('word_wid_seq')").getresult()[0][0]
db.query("INSERT INTO word(wid, word, class, cuser) VALUES(%i, '%s', %i, %i)" \
% (retval['wid'], jotools.escape_sql_string(retval['word']),
retval['cid'], uid))
retval['error'] = u'<a href="edit?wid=%i">%s</a>' % (retval['wid'], _(u'Word added'))
if word['oword'] != None:
db.query("UPDATE raw_word SET processed = TRUE WHERE word = '%s'" \
% jotools.escape_sql_string(word['oword']))
if retval['error'] == None:
retval['error'] = _(u'Word removed from the list of candidate words')
retval['try_again'] = False
if retval['error'] == None: retval['error'] = _(u'Nothing was done')
return retval
def add(req):
(uid, uname, editable) = jotools.get_login_user(req)
if not jotools.is_admin(uid):
joheaders.error_page(req, _(u'You must be an administrator to do this'))
return '\n'
datafields = ['firstname', 'lastname', 'uname', 'email', 'passwd']
values = {}
for datafield in datafields:
values[datafield] = jotools.get_param(req, datafield, u'')
if datafield != 'passwd':
values[datafield] = jotools.escape_sql_string(values[datafield])
if datafield not in ['email', 'passwd'] and values[datafield] == '':
joheaders.error_page(req, _(u'Required field %s is missing') % datafield)
return '\n'
if values['passwd'] == u'':
joheaders.error_page(req, _(u'Required field %s is missing') % u'passwd')
return '\n'
pwhash = sha.new((_config.PW_SALT + values['passwd']).encode('UTF-8')).hexdigest()
privdb = jodb.connect_private()
newuid = privdb.query("SELECT nextval('appuser_uid_seq')").getresult()[0][0]
try:
privdb.query(("INSERT INTO appuser(uid, uname, firstname, lastname, email, pwhash)" +
"VALUES(%i, '%s', '%s', '%s', '%s', '%s')") % (newuid, values['uname'],
values['firstname'], values['lastname'], values['email'], pwhash))
except ProgrammingError:
joheaders.error_page(req, _(u'User name is already in use'))
return '\n'
db = jodb.connect()
db.query(("INSERT INTO appuser(uid, uname, firstname, lastname, email)" +
"VALUES(%i, '%s', '%s', '%s', '%s')") % (newuid, values['uname'],
values['firstname'], values['lastname'], values['email']))
joheaders.ok_page(req, _(u'New user was added succesfully'))
return '\n'
def _list_homonyms(db, word):
res = db.query(("SELECT w.wid, w.word, wc.name FROM word w, wordclass wc " +
"WHERE w.class = wc.classid AND w.word = '%s'") \
% jotools.escape_sql_string(word))
h = u''
for r in res.getresult():
h = h + u'<a href="edit?wid=%i">%s(%s)</a> ' \
% (r[0], jotools.escape_html(unicode(r[1], 'UTF-8')),
jotools.escape_html(unicode(r[2], 'UTF-8')))
if len(h) == 0: return u''
else: return _(u'Homonyms') + u': ' + h
def _is_old_word(req, db, word):
"Checks if given word should be rejected. Returns an error message or None, if word is OK."
# Previously suggested words
results = db.query("SELECT word FROM raw_word WHERE word = '%s'" \
% jotools.escape_sql_string(word))
if results.ntuples() > 0: return u"Sanaa on jo ehdotettu lisättäväksi."
# Words in database
results = db.query("SELECT word FROM word WHERE word = '%s'" \
% jotools.escape_sql_string(word))
if results.ntuples() > 0: return u'<a href="query/wlist?word=%s">Sana on jo tietokannassa</a>.' \
% jotools.escape_url(word.encode('UTF-8'))
# Alternative spellings
results = db.query("SELECT wid FROM related_word WHERE replace(replace(related_word, " +
"'=', ''), '|', '') LIKE '%s'" % jotools.escape_sql_string(word))
if results.ntuples() > 0: return u'<a href="word/edit?wid=%i">Sana on jo tietokannassa</a>.' \
% results.getresult()[0][0]
return None
def add_from_db(req):
(uid, uname, editable) = jotools.get_login_user(req)
if not editable:
joheaders.error_page(req, _(u'You are not allowed to edit data'))
return '\n'
if req.method != 'GET':
joheaders.error_page(req, _(u'Only GET requests are allowed'))
return '\n'
db = jodb.connect()
words_per_page = 15
category = jotools.get_param(req, 'category', None)
if category == None: condition = ""
else: condition = "AND coalesce(info, '') = '%s'" \
% jotools.escape_sql_string(category)
results = db.query("SELECT count(*) FROM raw_word WHERE processed = FALSE %s" \
% condition)
nwords = results.getresult()[0][0]
if nwords <= words_per_page: limit = ""
else: limit = "LIMIT %i OFFSET %i" % (words_per_page,
random.randint(0, nwords - words_per_page))
results = db.query(("SELECT word, coalesce(notes, '') FROM raw_word " +
"WHERE processed = FALSE %s " +
"ORDER BY word %s") % (condition, limit))
if results.ntuples() == 0 and category == None:
joheaders.error_page(req, _(u'There are no words to be added'))
return '\n'
if results.ntuples() == 0 and category != None:
joheaders.error_page(req, _(u'There are no words to be added') + u' ' +
_(u'in category %s') % jotools.escape_html(category))
return '\n'
class_res = db.query("select classid, name from wordclass").getresult()
joheaders.page_header_navbar_level1(req, _(u"Add words"), uid, uname)
jotools.write(req, u'<form method="post" action="add">\n')
jotools.write(req, u'<table class="border">\n')
jotools.write(req, u'<tr><th>%s</th><th>%s</th><th>%s</th></tr>\n' \
% (_(u'Word'), _(u'Word class'), _(u'Notes')))
i = 0
for result in results.getresult():
word = unicode(result[0], 'UTF-8')
notes = unicode(result[1], 'UTF-8')
jotools.write(req, u'<tr><td><input type="hidden" name="origword%i" value=%s />' \
% (i, jotools.escape_form_value(word)))
jotools.write(req, u'<input type="text" name="word%i" value=%s /></td><td>' \
% (i, jotools.escape_form_value(word)))
jotools.write(req, _get_class_selector(class_res, None, i, True))
jotools.write(req, u'</td><td>')
jotools.write(req, jotools.escape_html(notes))
jotools.write(req, u'</td></tr>\n')
i = i + 1
jotools.write(req, u'</table>\n' +
u'<p><input type="submit" value="%s"></p></form>\n' % _(u"Add words"))
joheaders.page_footer_plain(req)
return '\n'
def index(req):
db = jodb.connect()
privdb = jodb.connect_private()
(uid, uname, editable) = jotools.get_login_user(req)
joheaders.page_header_navbar_level1(req, u"Ehdota uusia sanoja", uid, uname)
word = jotools.get_param(req, "word", u"").strip()
wtype = jotools.get_param(req, "type", u"").strip()
comment = jotools.get_param(req, "comment", u"").strip()
if word != u"":
if not jotools.checkword(word):
jotools.write(req, u'<p class="error">Sanassa on kiellettyjä merkkejä.</p>')
_print_entry_form(req, db)
else:
db.query("BEGIN")
error = _is_old_word(req, db, word)
if error != None:
jotools.write(req, u'<p class="error">%s</p>' % error)
_print_entry_form(req, db)
elif not (editable or _allow_new_word(req, privdb, True)):
_print_error_forbidden(req)
else:
db.query("INSERT INTO raw_word(word, info, notes) " +
"VALUES('%s', '%s', '%s')" % \
(jotools.escape_sql_string(word),
jotools.escape_sql_string(wtype),
jotools.escape_sql_string(comment)))
jotools.write(req, u'<p class="ok">Ehdotuksesi on tallennettu. ' +
u'Kiitos avusta!</p>')
_print_entry_form(req, db)
db.query("COMMIT")
elif editable or _allow_new_word(req, privdb, False):
_print_entry_form(req, db)
else:
_print_error_forbidden(req)
joheaders.page_footer_plain(req)
return '\n'
def wlist(req):
# The select clause
qselect = "SELECT w.wid, w.word, c.name AS classname, w.class FROM word w, wordclass c"
# Initial conditions
conditions = ["w.class = c.classid"]
# Word form conditions
word = jotools.get_param(req, 'word', u'')
if word != u'':
if not jotools.checkre(word):
joheaders.error_page(req, _(u'Word has forbidden characters in it'))
return "\n"
if jotools.get_param(req, 'wordre', u'') == u'on':
compop = '~*'
compword = jotools.expandre(word)
elif jotools.get_param(req, 'wordsimplere', u'') == u'on':
compop = 'ILIKE'
compword = word
else:
compop = '='
compword = word
# Use subquery if searching from alternative forms
cond = "w.word %s '%s'" % (compop, jotools.escape_sql_string(compword))
if jotools.get_param(req, 'altforms', u'') == u'on':
cond = cond + " OR w.wid IN (" + \
"SELECT rw.wid FROM related_word rw WHERE " + \
"replace(replace(rw.related_word, '=', ''), '|', '') %s '%s')" \
% (compop, jotools.escape_sql_string(compword))
conditions.append(cond)
# Word class conditions
wclass = jotools.toint(jotools.get_param(req, 'wordclass', u''))
if wclass > 0:
conditions.append("w.class = %i" % wclass)
# Text attribute conditions
aid = jotools.toint(jotools.get_param(req, 'textaid', u''))
if aid != 0:
value = jotools.get_param(req, 'textvalue', u'')
if value == u'':
cond = "w.wid NOT IN (SELECT wid FROM string_attribute_value WHERE aid = %i)" % aid
else:
cond = ("w.wid IN (SELECT wid FROM string_attribute_value " +
"WHERE aid = %i AND value = '%s')") % (aid, jotools.escape_sql_string(value))
conditions.append(cond)
# Flag conditions
for field in req.form.list:
if field.name.startswith('flagon'):
aid = jotools.toint(field.name[6:])
if jotools.get_param(req, 'flagon%i' % aid, u'') == u'on':
cond = "w.wid IN (SELECT wid FROM flag_attribute_value WHERE aid = %i)" % aid
conditions.append(cond)
if field.name.startswith('flagoff'):
aid = jotools.toint(field.name[7:])
if jotools.get_param(req, 'flagoff%i' % aid, u'') == u'on':
cond = "w.wid NOT IN (SELECT wid FROM flag_attribute_value WHERE aid = %i)" % aid
conditions.append(cond)
# FIXME: user should be able to select the order
order = "ORDER BY w.word, c.name, w.wid"
# Build the full select clause
if len(conditions) == 0:
select = qselect + " " + order
elif len(conditions) == 1:
select = qselect + " WHERE (" + conditions[0] + ") " + order
else:
select = qselect + " WHERE (" + conditions[0]
for condition in conditions[1:]:
select = select + ") AND (" + condition
select = select + ") " + order
outputtype = jotools.get_param(req, "listtype", u'html')
jooutput.call(req, outputtype, select)
return "\n"
def change(req, wid = None):
if req.method != 'POST':
joheaders.error_page(req, _(u'Only POST requests are allowed'))
return '\n'
(uid, uname, editable) = jotools.get_login_user(req)
if not editable:
joheaders.error_page(req, _(u'You are not allowed to edit data'))
return '\n'
if (wid == None):
joheaders.error_page(req, _(u'Parameter %s is required') % u'wid')
return '\n'
wid_n = jotools.toint(wid)
db = jodb.connect()
db.query("begin")
wclass_results = db.query("select class from word where wid = %i" % wid_n)
if wclass_results.ntuples() == 0:
joheaders.error_page(req, _(u'Word %i does not exist') % wid_n)
db.query("rollback")
return '\n'
wclass = wclass_results.getresult()[0][0]
edfield_results = db.query(("select a.type, a.aid, a.descr from attribute a, attribute_class ac " +
"where a.aid = ac.aid and ac.classid = %i and a.editable = TRUE") % wclass)
eid = db.query("select nextval('event_eid_seq')").getresult()[0][0]
event_inserted = False
messages = []
for attribute in edfield_results.getresult():
if attribute[0] == 1: # string attribute
html_att = 'string%i' % attribute[1]
newval = jotools.get_param(req, html_att, None)
if newval == None: continue
vresults = db.query(("select s.value from string_attribute_value s where " +
"s.wid = %i and s.aid = %i") % (wid_n, attribute[1]))
if vresults.ntuples() == 0: oldval = u""
else: oldval = unicode(vresults.getresult()[0][0], 'UTF-8')
if oldval == newval: continue
if not event_inserted:
db.query("insert into event(eid, eword, euser) values(%i, %i, %i)" % \
(eid, wid_n, uid))
event_inserted = True
if newval == u'':
db.query(("delete from string_attribute_value where wid = %i " +
"and aid = %i") % (wid_n, attribute[1]))
elif oldval == u'':
db.query(("insert into string_attribute_value(wid, aid, value, eevent) " +
"values(%i, %i, '%s', %i)") % (wid_n, attribute[1],
jotools.escape_sql_string(newval), eid))
else:
db.query(("update string_attribute_value set value='%s', eevent=%i " +
"where wid=%i and aid=%i") %
(jotools.escape_sql_string(newval), eid, wid_n, attribute[1]))
messages.append(u"%s: '%s' -> '%s'" % (unicode(attribute[2], 'UTF-8'),
oldval, newval))
if attribute[0] == 3: # integer attribute
html_att = 'int%i' % attribute[1]
newval_s = jotools.get_param(req, html_att, None)
if newval_s == None: continue
newval_s = newval_s.strip()
if newval_s == u'':
newval = None
else:
try: newval = int(newval_s)
except ValueError: continue
# Limit value range to prevent troubles with storing the
# value into the database
if newval < -1000000 or newval > 1000000: continue
vresults = db.query(("select i.value from int_attribute_value i where " +
"i.wid = %i and i.aid = %i") % (wid_n, attribute[1]))
if vresults.ntuples() == 0: oldval = None
else: oldval = vresults.getresult()[0][0]
if oldval == newval: continue
if not event_inserted:
db.query("insert into event(eid, eword, euser) values(%i, %i, %i)" % \
(eid, wid_n, uid))
event_inserted = True
if newval == None:
db.query(("delete from int_attribute_value where wid = %i " +
"and aid = %i") % (wid_n, attribute[1]))
elif oldval == None:
db.query(("insert into int_attribute_value(wid, aid, value, eevent) " +
"values(%i, %i, %i, %i)") % (wid_n, attribute[1],
newval, eid))
else:
db.query(("update int_attribute_value set value=%i, eevent=%i " +
"where wid=%i and aid=%i") %
(newval, eid, wid_n, attribute[1]))
if oldval == None: oldval_s = _(u'(None)')
else: oldval_s = `oldval`
if newval == None: newval_s = _(u'(None)')
else: newval_s = `newval`
messages.append(u"%s: %s -> %s" % (unicode(attribute[2], 'UTF-8'),
oldval_s, newval_s))
comment = jotools.get_param(req, 'comment', u'')
if comment != u'':
if not event_inserted:
db.query("insert into event(eid, eword, euser) values(%i, %i, %i)" % \
(eid, wid_n, uid))
event_inserted = True
db.query("update event set comment = '%s' where eid = %i" \
% (jotools.escape_sql_string(comment), eid))
if event_inserted and len(messages) > 0:
mess_str = jotools.escape_sql_string(reduce(lambda x, y: x + u"\n" + y, messages, u""))
db.query("update event set message = '%s' where eid = %i" % (mess_str, eid))
db.query("commit")
joheaders.redirect_header(req, u'edit?wid=%i' % wid_n)
return '\n'
def rwords(req, wid = None):
(uid, uname, editable) = jotools.get_login_user(req)
if not editable:
joheaders.error_page(req, _(u'You are not allowed to edit data'))
return '\n'
if wid == None:
joheaders.error_page(req, _(u'Parameter %s is required') % u'wid')
return '\n'
wid_n = jotools.toint(wid)
db = jodb.connect()
results = db.query("select word, class from word where wid = %i" % wid_n)
if results.ntuples() == 0:
joheaders.error_page(req, _(u'Word %i does not exist') % wid_n)
return '\n'
wordinfo = results.getresult()[0]
if req.method == 'GET': # show editor
word = unicode(wordinfo[0], 'UTF-8')
classid = wordinfo[1]
title1 = _(u'Word') + u': ' + word
link1 = u'edit?wid=%i' % wid_n
title2 = _(u'related words')
joheaders.page_header_navbar_level2(req, title1, link1, title2, uid, uname, wid_n)
jotools.write(req, u'<p>%s</p>\n' % joeditors.call(db, u'word_class', [classid]))
jotools.write(req, joeditors.call(db, u'rwords_edit_form', [wid_n]))
joheaders.page_footer_plain(req)
return '\n'
if req.method != 'POST':
joheaders.error_page(req, _(u'Only GET and POST requests are allowed'))
return '\n'
db.query("begin")
rword_results = db.query("SELECT rwid, related_word FROM related_word WHERE wid = %i" % wid_n)
rword_res = rword_results.getresult()
eid = db.query("select nextval('event_eid_seq')").getresult()[0][0]
event_inserted = False
messages = []
for attribute in rword_res:
html_att = 'rword%i' % attribute[0]
if jotools.get_param(req, html_att, u'') == u'on': remove = True
else: remove = False
if not remove: continue
if not event_inserted:
db.query("insert into event(eid, eword, euser) values(%i, %i, %i)" % \
(eid, wid_n, uid))
event_inserted = True
db.query("delete from related_word where wid = %i and rwid = %i" \
% (wid_n, attribute[0]))
messages.append(_(u"Alternative spelling removed: '%s'") \
% jotools.escape_html(unicode(attribute[1], 'UTF-8')))
newwords = jotools.get_param(req, 'add', u'')
for word in jotools.unique(newwords.split()):
if not jotools.checkword(word): continue
already_listed = False
for attribute in rword_res:
if word == unicode(attribute[1], 'UTF-8'):
already_listed = True
break
if already_listed: continue
if not event_inserted:
db.query("insert into event(eid, eword, euser) values(%i, %i, %i)" % \
(eid, wid_n, uid))
event_inserted = True
db.query("insert into related_word(wid, eevent, related_word) values(%i, %i, '%s')" \
% (wid_n, eid, jotools.escape_sql_string(word)))
messages.append(_(u"Alternative spelling added: '%s'") % jotools.escape_html(word))
comment = jotools.get_param(req, 'comment', u'')
if comment != u'':
if not event_inserted:
db.query("insert into event(eid, eword, euser) values(%i, %i, %i)" % \
(eid, wid_n, uid))
event_inserted = True
db.query("update event set comment = '%s' where eid = %i" \
% (jotools.escape_sql_string(comment), eid))
if event_inserted and len(messages) > 0:
mess_str = jotools.escape_sql_string(reduce(lambda x, y: x + u"\n" + y, messages, u""))
db.query("update event set message = '%s' where eid = %i" % (mess_str, eid))
db.query("commit")
joheaders.redirect_header(req, u'edit?wid=%i' % wid_n)
return '\n'
def flags(req, wid = None):
(uid, uname, editable) = jotools.get_login_user(req)
if not editable:
joheaders.error_page(req, _(u'You are not allowed to edit data'))
return '\n'
if wid == None:
joheaders.error_page(req, _(u'Parameter %s is required') % u'wid')
return '\n'
wid_n = jotools.toint(wid)
db = jodb.connect()
results = db.query("select word, class from word where wid = %i" % wid_n)
if results.ntuples() == 0:
joheaders.error_page(req, _(u'Word %i does not exist') % wid_n)
return '\n'
wordinfo = results.getresult()[0]
if req.method == 'GET': # show editor
word = unicode(wordinfo[0], 'UTF-8')
classid = wordinfo[1]
title1 = _(u'Word') + u': ' + word
link1 = u'edit?wid=%i' % wid_n
title2 = _(u'flags')
joheaders.page_header_navbar_level2(req, title1, link1, title2, uid, uname, wid_n)
jotools.write(req, u'<p>%s</p>\n' % joeditors.call(db, u'word_class', [classid]))
jotools.write(req, joeditors.call(db, u'flag_edit_form', [wid_n, classid]))
joheaders.page_footer_plain(req)
return '\n'
if req.method != 'POST':
joheaders.error_page(req, _(u'Only GET and POST requests are allowed'))
return '\n'
db.query("begin")
edfield_results = db.query(("SELECT a.aid, a.descr, CASE WHEN fav.wid IS NULL THEN 'f' ELSE 't' END " +
"FROM attribute_class ac, attribute a " +
"LEFT OUTER JOIN flag_attribute_value fav ON (a.aid = fav.aid and fav.wid = %i) " +
"WHERE a.aid = ac.aid AND ac.classid = %i AND a.type = 2" +
"ORDER BY a.descr") % (wid_n, wordinfo[1]))
eid = db.query("select nextval('event_eid_seq')").getresult()[0][0]
event_inserted = False
messages = []
for attribute in edfield_results.getresult():
html_att = 'attr%i' % attribute[0]
if jotools.get_param(req, html_att, u'') == u'on': newval = True
else: newval = False
if attribute[2] == 't': oldval = True
else: oldval = False
if oldval == newval: continue
if not event_inserted:
db.query("insert into event(eid, eword, euser) values(%i, %i, %i)" % \
(eid, wid_n, uid))
event_inserted = True
if newval == False:
db.query(("delete from flag_attribute_value where wid = %i " +
"and aid = %i") % (wid_n, attribute[0]))
messages.append(_(u"Flag removed: '%s'") % unicode(attribute[1], 'UTF-8'))
if newval == True:
db.query(("insert into flag_attribute_value(wid, aid, eevent) " +
"values(%i, %i, %i)") % (wid_n, attribute[0], eid))
messages.append(_(u"Flag added: '%s'") % unicode(attribute[1], 'UTF-8'))
comment = jotools.get_param(req, 'comment', u'')
if comment != u'':
if not event_inserted:
db.query("insert into event(eid, eword, euser) values(%i, %i, %i)" % \
(eid, wid_n, uid))
event_inserted = True
db.query("update event set comment = '%s' where eid = %i" \
% (jotools.escape_sql_string(comment), eid))
if event_inserted and len(messages) > 0:
mess_str = jotools.escape_sql_string(reduce(lambda x, y: x + u"\n" + y, messages, u""))
db.query("update event set message = '%s' where eid = %i" % (mess_str, eid))
db.query("commit")
joheaders.redirect_header(req, u'edit?wid=%i' % wid_n)
return '\n'
