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)