def get_current_intentions(): cur = f.get_db().cursor() try: cur.execute( "select " "uid, name, description, important, recurrent, parent, created, " # for recurrent intentions that didn't reach their reminder we set "finished" # to mark them as completed "case when (recurrent = 't' and timezone('MSK'::text, now()) < " "(startdate + (frequency * INTERVAL '1 day') - (reminder * INTERVAL '1 day'))) " "then startdate else finished end as finished, " "startdate, frequency, reminder, oldstartdate " "FROM public.intentions where " # non-recurrent intentions that are either not completed or completed less than 5 days ago "(recurrent = 'f' and coalesce(finished, timezone('MSK'::text, now())) >= " "(timezone('MSK'::text, now()) - '2 day'::interval)) OR " # recurrent intentions that were completed recently (less than 5 days ago) "(recurrent = 't' and timezone('MSK'::text, now()) < (startdate + '2 day'::interval)) OR " # recurrent intentions that should be completed in the near future (reminder reached) "(recurrent = 't' and timezone('MSK'::text, now()) > " "(startdate + (frequency * INTERVAL '1 day') - (reminder * INTERVAL '1 day')));" ) todo = f.dictfetchall(cur) result = f.get_nested(todo) except psycopg2.Error as e: f.get_db().rollback() result = [{"error": str(e)}] finally: cur.close() return result
def get_notes(join="", where=""): cur = f.get_db().cursor() sql = """\ select n.*, (select string_agg(name, ',') from topics t left join notes_topics nt on nt.topic = t.uid where nt.note = n.uid) topics, nt.name as typename, nt.fullname as fullname from notes n left join notetypes nt on n.type = nt.uid %s %s order by created desc;""" % ( join, where, ) try: cur.execute(sql) result = f.dictfetchall(cur) if not result: result = [{"error": f.NO_NOTE, "details": ""}] except psycopg2.Error as e: f.get_db().rollback() result = [{"error": f.QUERY_ERR, "details": str(e)}] finally: cur.close() return result
def translations_editpage(link): """Render translation editor page with a chosen song preloaded. Keyword arguments: link : string -- an unique identifier of the translation """ cur = f.get_db().cursor() sql = """\ select tr.uid, tr.link, tr.engname, tr.runame, tr.original, tr.translation, tr.footnotes, tr.comment, tr.video, a.author, a.link as authorlink, json_agg(t) filter (where t.uid is not null) as tags from translations.translations tr left join translations.authors a on tr.author = a.uid left join translations.translations_tags tt on tr.uid = tt.translation left join translations.tags t on t.uid = tt.tag where tr.deleted = false and tr.link = '%s' GROUP BY tr.uid, a.uid;""" % ( link ) try: cur.execute(sql) result = f.dictfetchall(cur) if not result: result = [{"error": "Текст не найден!", "details": ""}] except psycopg2.Error as e: f.get_db().rollback() result = [{"error": f.QUERY_ERR, "details": str(e)}] finally: cur.close() return render_template("create_tr.html", content=result[0])
def get_tags(): """Return a list of all tags. Every tag is presented as a dict with keys: uid, tag, link. """ cur = f.get_db().cursor() sql = """\ select distinct t.uid, t.tag, t.link from translations.tags t inner join translations.translations_tags tt on t.uid = tt.tag inner join translations.translations tr on tt.translation = tr.uid where tr.deleted = false order by t.tag;""" try: cur.execute(sql) result = f.dictfetchall(cur) if not result: result = [{"error": "Тэгов не найдено!", "details": ""}] except psycopg2.Error as e: f.get_db().rollback() result = [{"error": f.QUERY_ERR, "details": str(e)}] finally: cur.close() return result
def intent_delete(): uid = (request.args.get("uid", ""),) cur = f.get_db().cursor() try: cur.execute("DELETE FROM intentions WHERE uid = %s::uuid;", uid) f.get_db().commit() # no exception handling; simple alert about "500 server error" finally: cur.close() return str(uid)
def topics_delete(): uid = (request.args.get("uid", ""), ) cur = f.get_db().cursor() try: cur.execute("DELETE FROM topics WHERE uid = %s::uuid;", uid) f.get_db().commit() # no exception handling; simple alert about "500 server error" finally: cur.close() return str(uid) # getting uid back to delete post from page
def get_translations_list(author=None, tag=None): """Return JSON with a list of translations for the sidebar. Can be filtered by author or tag. Keyword arguments: author : string -- an unique identifier of the author (default None) tag : string -- an unique identifier of the tag (default None) """ cur = f.get_db().cursor() where = "" filter = None filtername = None if author: filter = "author" where = "and a.link = '%s'" % author.replace("'", "''") cur.execute( "SELECT author from translations.authors " "where link = %s ;", (author,) ) result = f.dictfetchall(cur) if result: filtername = result[0]["author"] elif tag: # it's either author or tag, not both filter = "tag" where = "and t.link = '%s'" % tag.replace("'", "''") cur.execute("SELECT tag from translations.tags " "where link = %s ;", (tag,)) result = f.dictfetchall(cur) if result: filtername = result[0]["tag"] sql = """\ select distinct tr.uid, tr.engname, tr.runame, tr.link from translations.translations tr left join translations.authors a on tr.author = a.uid left join translations.translations_tags tt on tr.uid = tt.translation left join translations.tags t on t.uid = tt.tag where tr.deleted = false %s order by tr.engname;""" % ( where, ) try: cur.execute(sql) result = f.dictfetchall(cur) if not result: result = [{"error": "Переводов не найдено!", "details": ""}] except psycopg2.Error as e: f.get_db().rollback() result = [{"error": f.QUERY_ERR, "details": str(e)}] finally: cur.close() # flask 1.1 feature: any dict gets converted into json response on endpoint function call return {"filter": filter, "filtername": filtername, "response": result}
def delete(): """Add a new note or modify an existing one.""" uid = (request.args.get("uid", ""), ) cur = f.get_db().cursor() try: cur.execute("DELETE FROM notes WHERE uid = %s::uuid;", uid) f.get_db().commit() # no exception handling; simple alert about "500 server error" finally: cur.close() return str(uid) # getting uid back to delete post from page
def create(cls, login, password): cur = f.get_db().cursor() userid = str(uuid.uuid4()) cur.execute( "INSERT into users (uid, login, password) VALUES (%s, %s, %s);", (userid, login, password), ) cur.execute( "INSERT into users_permissions (userid, permissionid) " "VALUES (%s, (SELECT uid FROM permissions WHERE name = 'user'));", (userid, ), ) f.get_db().commit()
def mark(): uid = (request.args.get("uid", ""), ) status = (request.args.get("status", ""), ) cur = f.get_db().cursor() try: cur.execute("UPDATE notes SET important = %s WHERE uid = %s::uuid;", (status, uid)) f.get_db().commit() print("mark set!") # no exception handling; simple alert about "500 server error" finally: cur.close() return str(uid)
def get_by_field(cls, field, value): cur = f.get_db().cursor() cur.execute( "SELECT u.uid, u.name, u.login, u.password, string_agg(p.name, ',') FROM users u " "LEFT JOIN users_permissions up on u.uid = up.userid " "LEFT JOIN permissions p on p.uid = up.permissionid " "WHERE u.{} = %s " "GROUP BY u.uid; ".format(field), (value, ), ) dbuser = cur.fetchone() appuser = CabinetUser() if not dbuser: return None ( appuser.id, appuser.name, appuser.login, appuser.password, appuser.permissions, ) = ( dbuser[0], dbuser[1], dbuser[2], dbuser[3], dbuser[4].split(",") if dbuser[4] else None, ) if appuser.id not in users: users[appuser.id] = appuser return appuser
def get_peoplenames(): cur = f.get_db().cursor() sql = """\ select uid, name, 1 as count from people;""" try: cur.execute(sql) result = f.dictfetchall(cur) if not result: result = [{"error": f.EMPTY_TOPIC_LIST, "details": ""}] except psycopg2.Error as e: f.get_db().rollback() result = [{"error": f.QUERY_ERR, "details": str(e)}] finally: cur.close() return result
def my_form_post(): """Add a new note or modify an existing one.""" postuid = request.form["uid"] importance = request.form["importance"] maintext = request.form["maintext"] topic = request.form["topic"] source = request.form["source"] print("source") notetype = request.form["notetype"] print("notetype") if postuid == "00000000-0000-0000-0000-000000000000": postuid = str(uuid.uuid4()) if maintext == "<p><br></p>": maintext = "" # for some bizarre reason None doesn't get converted into null cur = f.get_db().cursor() try: cur.execute( "INSERT INTO notes (uid, maintext, important, url, type) " "VALUES (%s, %s, %s, %s, (SELECT uid FROM notetypes WHERE name = %s)) " "ON CONFLICT (uid) DO UPDATE SET maintext=excluded.maintext, changed=timezone('MSK'::text, now()), " "important=excluded.important, url=excluded.url, type=excluded.type;", (postuid, maintext, importance, source, notetype), ) if topic: topicsarray = topic.split(",") topicsarray = [i.strip() for i in topicsarray] topicsarray = [i for i in topicsarray if i] # removing empty strings for t in topicsarray: cur.execute( "INSERT INTO topics (name) VALUES (%s) ON CONFLICT DO NOTHING;", (t, ), ) cur.execute("DELETE FROM notes_topics WHERE note = %s;", (postuid, )) for t in topicsarray: cur.execute("SELECT uid FROM topics WHERE name = %s;", (t, )) topicuid = cur.fetchone()[0] cur.execute( "INSERT INTO notes_topics (note, topic) VALUES (%s, %s);", (postuid, topicuid), ) f.get_db().commit() # no exception handling; simple alert about "500 server error" finally: cur.close() return n.notes_load(postuid)
def intent_check(): uid = request.args.get("uid", "") status = request.args.get("status", "") print(uid) print(status) cur = f.get_db().cursor() try: cur.execute("SELECT recurrent FROM intentions WHERE uid = %s::uuid;", (uid,)) recurrent = cur.fetchone()[0] cur.execute("SELECT frequency FROM intentions WHERE uid = %s::uuid;", (uid,)) frequency = cur.fetchone()[0] if recurrent: if status == "true": cur.execute( "UPDATE intentions SET oldstartdate = startdate WHERE uid = %s::uuid;", (uid,), ) # set startdate to next 4:00 AM cur.execute( "UPDATE intentions " "SET startdate = date_trunc('day', timezone('MSK'::text, now()) + interval '20 hours') " "+ interval '4 hours' " "WHERE uid = %s::uuid;", (uid,), ) else: cur.execute( "UPDATE intentions SET startdate = oldstartdate WHERE uid = %s::uuid;", (uid,), ) else: if status == "true": cur.execute( "UPDATE intentions SET finished = timezone('MSK'::text, now()) WHERE uid = %s::uuid;", (uid,), ) else: cur.execute( "UPDATE intentions SET finished = NULL WHERE uid = %s::uuid;", (uid,), ) f.get_db().commit() # no exception handling; simple alert about "500 server error" finally: cur.close() return str(uid)
def delete_translation(link): """Delete an existing translation. Keyword arguments: link : string -- an unique identifier of the translation """ cur = f.get_db().cursor() try: cur.execute( "UPDATE translations.translations SET deleted = true, datedel = now() WHERE link = %s; ", (link,), ) f.get_db().commit() except psycopg2.Error as e: f.get_db().rollback() return make_response(jsonify({"error": f.QUERY_ERR, "details": str(e)}), 500) finally: cur.close() return "", 204 # the "it's done" response
def get_topics(joinwhere=""): cur = f.get_db().cursor() sql = ("""\ select distinct t.uid, t.name, count(nt.uid) as count from topics t left join notes_topics nt on t.uid = nt.topic %s group by t.uid, t.name, nt.uid order by t.name;""" % joinwhere) try: cur.execute(sql) result = f.dictfetchall(cur) if not result: result = [{"error": f.EMPTY_TOPIC_LIST, "details": ""}] except psycopg2.Error as e: f.get_db().rollback() result = [{"error": f.QUERY_ERR, "details": str(e)}] finally: cur.close() return result
def get_all_intentions(): cur = f.get_db().cursor() try: cur.execute( "select " "uid, name, description, important, recurrent, parent, created, " "case when (recurrent = 't' and timezone('MSK'::text, now()) < " "(startdate + (frequency * INTERVAL '1 day') - (reminder * INTERVAL '1 day'))) " "then startdate else finished end as finished, " "startdate, frequency, reminder, oldstartdate " "FROM public.intentions ;" ) todo = f.dictfetchall(cur) result = f.get_nested(todo) except psycopg2.Error as e: f.get_db().rollback() result = [{"error": str(e)}] finally: cur.close() return result
def get_people(where=""): cur = f.get_db().cursor() sql = """\ select * from people %s; """ % ( where ) try: print(sql) cur.execute(sql) result = f.dictfetchall(cur) if not result: result = [{"error": f.NO_NOTE, "details": ""}] except psycopg2.Error as e: f.get_db().rollback() result = [{"error": f.QUERY_ERR, "details": str(e)}] finally: cur.close() return result
def get_translation(link): """Return HTML of a chosen translation from database. Used to load translations seamlessly, avoiding page reloads. Not to be called directly from browser. Keyword arguments: link : string -- an unique identifier of the translation """ # TODO: restrict access to this endpoint for external requests. cur = f.get_db().cursor() sql = """\ select tr.engname, tr.link, tr.runame, tr.original, tr.translation, tr.footnotes, tr.comment, tr.video, a.author, a.link as authorlink, json_agg(t) filter (where t.uid is not null) as tags from translations.translations tr left join translations.authors a on tr.author = a.uid left join translations.translations_tags tt on tr.uid = tt.translation left join translations.tags t on t.uid = tt.tag where tr.link = '%s' GROUP BY tr.uid, a.uid;""" % ( link ) try: cur.execute(sql) result = f.dictfetchall(cur) if not result: result = [{"error": "Текст не найден!", "details": ""}] except psycopg2.Error as e: f.get_db().rollback() result = [{"error": f.QUERY_ERR, "details": str(e)}] finally: cur.close() return render_template("single_tr.html", content=result[0])
def get_authors(): """Return a list of all authors. Every author is presented as a dict with keys: uid, author, link. """ cur = f.get_db().cursor() sql = """\ select distinct a.uid, a.author, a.link from translations.authors a inner join translations.translations tr on a.uid = tr.author where tr.deleted = false order by a.author;""" try: cur.execute(sql) result = f.dictfetchall(cur) if not result: result = [{"error": "Авторов не найдено!", "details": ""}] except psycopg2.Error as e: f.get_db().rollback() result = [{"error": f.QUERY_ERR, "details": str(e)}] finally: cur.close() return result
def save_translation(): """Save the contents of an editor as a new or modified translation.""" uid = request.form["uid"] engname = request.form["engname"].strip() runame = request.form["runame"].strip() original = request.form["original"] translation = request.form["translation"] link = request.form["link"] footnotes = request.form["footnotes"] author = request.form["author"].strip() authoruid = None tags = request.form["tags"] comment = request.form["comment"] video = request.form["video"] if uid == "": uid = str(uuid.uuid4()) if link == "": link = f.sanitize_url(engname, 64) if not link: # in case there's nothing left after sanitizing link = str(uuid.uuid4()) cur = f.get_db().cursor() sql = """\ select uid from translations.translations where link = '%s' ;""" % ( link ) try: cur.execute(sql) # checking if a translation with identical name exists result = cur.fetchone() if result and result[0] != uid: # if we have a different translation with identical name, # just slap random uuid on the link to make it unique link += str(uuid.uuid4()) if author: authorlink = f.sanitize_url(author, 64) if not authorlink: # in case there's nothing left after sanitizing authorlink = str(uuid.uuid4()) authoruid = str(uuid.uuid4()) # checking if author exists cur.execute( "SELECT uid from translations.authors " "where author = %s ;", (author,) ) result = cur.fetchone() if result: # if he does we use UID from db authoruid = result[0] else: # if he doesn't we check if link exists # (possible when two authors differ by capitalization or spaces) cur.execute( "SELECT uid from translations.authors " "where link = %s ;", (authorlink,), ) if cur.fetchone(): # if we have a different author with identical link, # just slap random uuid on the link to make it unique authorlink += str(uuid.uuid4()) cur.execute( "INSERT INTO translations.authors (uid, author, link) " "VALUES (%s, %s, %s);", (authoruid, author, authorlink), ) cur.execute( "INSERT INTO translations.translations " "(uid, engname, runame, original, translation, link, footnotes, author, comment, video) " "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) " "ON CONFLICT (uid) DO UPDATE SET engname=excluded.engname, runame=excluded.runame, " "original=excluded.original, translation=excluded.translation, link=excluded.link, " "footnotes=excluded.footnotes, author=excluded.author, comment=excluded.comment, video=excluded.video;", ( uid, engname, runame, original, translation, link, footnotes, authoruid, comment, video, ), ) if tags: tagarray = tags.split(",") tagarray = [i.strip() for i in tagarray] tagarray = [i for i in tagarray if i] # removing empty strings cur.execute( "DELETE FROM translations.translations_tags WHERE translation = %s;", (uid,), ) for tag in tagarray: taglink = f.sanitize_url(tag, 64) if not taglink: # in case there's nothing left after sanitizing taglink = str(uuid.uuid4()) taguid = str(uuid.uuid4()) # checking if tag exists cur.execute( "SELECT uid from translations.tags " "where tag = %s ;", (tag,) ) result = cur.fetchone() if result: # if it does we use UID from db taguid = result[0] else: # if it doesn't we check if link exists # (possible when two tags differ by capitalization or spaces) cur.execute( "SELECT uid from translations.tags " "where link = %s ;", (taglink,), ) if cur.fetchone(): # if we have a different tag with identical link, # just slap random uuid on the link to make it unique taglink += str(uuid.uuid4()) cur.execute( "INSERT INTO translations.tags (uid, tag, link) " "VALUES (%s, %s, %s);", (taguid, tag, taglink), ) cur.execute( "INSERT INTO translations.translations_tags (translation, tag) " "VALUES (%s, %s);", (uid, taguid), ) f.get_db().commit() finally: cur.close() return redirect("/" + link)