def edit_tag(keyword):
if not can_delete_tag(keyword):
return "<h1>It's forbidden, my dear</h1>", 403
error = None
tag = query_db("select * \
from keywords \
where keyword = ?", [keyword],
one=True)
if request.method == 'GET':
request.form.id = tag['keywordid']
request.form.name = tag['keyword']
if request.method == 'POST':
if request.form['name'] == "":
error = 'Please fill the name'
else:
con = get_db()
with con:
con.execute(
'update keywords set keyword = ? \
where keywordid = ?',
[request.form['name'], request.form['id']])
flash('You successfully modified the tag')
return redirect(
url_for('keyword', keyword=request.form['name']))
return render_template(
'catalog/edit.html',
editname="tag",
error=error,
name=keyword,
titleP="Edit the tag",
keywords=query_db("select * from keywords where not (keyword = ?)",
[keyword]))
def author(fullname):
a = query_db("select authorid from authors where \
fullname = ?", [fullname],
one=True)
author_not_assc = query_db(
'SELECT authorid FROM authors WHERE fullname = ? \
and authorid not in (SELECT DISTINCT authorid FROM papers_authors)',
[fullname])
if (a is None):
return render_catalog('catalog/papers-of-author.html',
error="Author " + fullname + " is not found",
fullname=fullname)
authorid = a['authorid']
papers = query_db(
"select p.* \
from papers as p, \
papers_authors as pa \
where \
p.deleted_at is null and \
p.paperid=pa.paperid and \
pa.authorid = ? \
order by p.lastcommentat desc", [authorid])
return render_catalog('catalog/papers-of-author.html',
entry=a,
papers=with_description(papers),
author_not_assc=author_not_assc,
fullname=fullname)
def domain(domainname):
domain = query_db("select domainid from domains where \
domainname = ?", [domainname],
one=True)
domain_not_assc = query_db(
'SELECT domainid FROM domains WHERE domainname = ? \
and domainid not in (SELECT DISTINCT domainid FROM papers_domains)',
[domainname])
if (domain is None):
return render_catalog('catalog/papers-in-domain.html',
error="Domain " + domainname + " not found",
domainname=domainname)
domainid = domain['domainid']
papers = query_db(
"select p.* \
from papers as p, \
papers_domains as pd \
where p.deleted_at is null and \
p.paperid=pd.paperid \
and pd.domainid = ? \
order by p.lastcommentat desc", [domainid])
return render_catalog('catalog/papers-in-domain.html',
entry=domain,
papers=with_description(papers),
domain_not_assc=domain_not_assc,
domainname=domainname)
def keyword(keyword):
k = query_db("select keywordid from keywords where \
keyword = ?", [keyword],
one=True)
tag_not_assc = query_db(
'SELECT keyword FROM keywords WHERE keyword = ? \
and keywordid not in (SELECT DISTINCT keywordid FROM papers_keywords)',
[keyword])
if (k is None):
return render_catalog('catalog/papers-with-keyword.html',
error="Tag " + keyword + " is not found",
keyword=keyword)
keywordid = k['keywordid']
papers = query_db(
"select p.* \
from papers as p, \
papers_keywords as pk \
where \
p.deleted_at is null and \
p.paperid=pk.paperid and \
pk.keywordid = ? \
order by p.lastcommentat desc", [keywordid])
return render_catalog('catalog/papers-with-keyword.html',
entry=k,
papers=with_description(papers),
tag_not_assc=tag_not_assc,
keyword=keyword)
def all(page=1):
count = query_db("select count(*) as c from papers", one=True)['c']
# how many papers on page?
onpage = 5
maxpage = int(ceil(float(count) / onpage))
seq = query_db(
"select * \
from papers as p \
where p.deleted_at is null \
order by p.lastcommentat DESC \
limit ?, ?", [(page - 1) * onpage, onpage])
(commentsTail, commentsHead, liked_by, liked) = previews(seq)
return render_template('main-list.html',
seq=seq,
commentsTail=commentsTail,
commentsHead=commentsHead,
liked_by=liked_by,
liked=liked,
maxpage=maxpage,
curpage=page,
headurl='/all')
def onepaper(paperid, title = None):
paper = get_paper_w_uploader(paperid)
liked = query_db(
"select count(*) as c \
from likes \
where paperid=? and userid=?",
[paperid,get_user_id()],
one=True)['c']
if paper['edited_at'] is not None:
paper['edituser'] = query_db(
"select username \
from users \
where userid = ?",
[paper['edited_by']], one=True)['username']
authors=get_authors(paperid)
domains=get_domains(paperid)
keywords=get_keywords(paperid)
comments=get_comments(paperid)
return render_template('paper/onepaper.html',
entry=paper,
comments=comments,
authors=authors,
domains=domains,
keywords=keywords,
liked=liked,
liked_by=liked_by(paperid))
def edit_author(fullname):
if not can_delete_author(fullname):
return "<h1>It's forbidden, my dear</h1>", 403
error = None
author = query_db("select * \
from authors \
where fullname = ?", [fullname],
one=True)
if request.method == 'GET':
request.form.id = author['authorid']
request.form.name = author['fullname']
if request.method == 'POST':
if request.form['name'] == "":
error = 'Please enter a valid name'
else:
con = get_db()
with con:
con.execute(
'update authors set fullname = ? \
where authorid = ?',
[request.form['name'], request.form['id']])
flash('You successfully modified the author')
return redirect(
url_for('author', fullname=request.form['name']))
return render_template(
'catalog/edit.html',
editname="author",
error=error,
name=fullname,
titleP="Edit the author",
authors=query_db("select * from authors where not (fullname = ?)",
[fullname]))
def usersite(username, page=1):
""" Generate previews of papers uploaded/liked
by specified user
"""
u = query_db("select * from users where username = ?", [username],
one=True)
if not u: abort(404)
# count the paper uploaded/liked by this user
count = query_db("select count(distinct p.paperid) as c \
from papers as p, likes as l \
where \
p.deleted_at is null and \
(p.userid = ? or \
(p.paperid = l.paperid and l.userid = ?) \
) \
", [u['userid'], u['userid']],
one=True)['c']
# how many papers on page?
onpage = 3
maxpage = int(ceil(float(count) / onpage))
# submitted papers by user
# +
# other user papers liked by him
# ordered by submition time or like time :)
seq = query_db(
"select *, lastcommentat as sorttime \
from papers \
where deleted_at is null and \
userid = ? \
union \
select p.*, CASE \
WHEN l.liketime > p.lastcommentat \
THEN l.liketime \
ELSE p.lastcommentat END as sorttime\
from papers as p, likes as l \
where p.deleted_at is null and \
p.paperid = l.paperid and l.userid = ? \
and p.userid <> ? \
order by sorttime DESC \
limit ?, ?",
[u['userid'], u['userid'], u['userid'], (page - 1) * onpage, onpage])
(commentsTail, commentsHead, liked_by, liked) = previews(seq)
return render_template('usersite.html',
seq=seq,
user=u,
commentsTail=commentsTail,
commentsHead=commentsHead,
liked_by=liked_by,
liked=liked,
maxpage=maxpage,
curpage=page,
headurl='/' + username)
def render_catalog(template_name, **context):
domains = query_db("select * from domains order by domainname")
keywords = query_db("select * from keywords order by keyword")
authors = query_db("select * from authors order by fullname")
users = query_db("select * from users order by username")
return render_template(template_name,
domains=domains,
keywords=keywords,
authors=authors,
users=users,
**context)
def render_catalog(template_name, **context):
domains = query_db("select * from domains order by lower(domainname)")
keywords = query_db("select * from keywords order by lower(keyword)")
authors = query_db("select * from authors order by lower(fullname)")
users = query_db(
"select * from users where valid = 1 or userid = 1 order by username")
return render_template(template_name,
domains=domains,
keywords=keywords,
authors=authors,
users=users,
**context)
def catalog():
if request.args.get('q'):
a_ids_str = ''
k_ids_str = ''
d_ids_str = ''
q = request.args.get('q')
qLike = '%' + request.args.get('q') + '%'
a = query_db(
"select authorid from authors where \
lower(fullname) like lower(?) ", [qLike])
a_ids = [str(author['authorid']) for author in a]
if (len(a_ids) > 0):
a_ids_str = ",".join(a_ids)
k = query_db(
"select keywordid from keywords where \
lower(keyword) like lower(?)", [qLike])
k_ids = [str(keyword['keywordid']) for keyword in k]
if (len(k_ids) > 0):
k_ids_str = ",".join(k_ids)
d = query_db(
"select domainid from domains where \
lower(domainname) like lower(?)", [qLike])
d_ids = [str(domain['domainid']) for domain in d]
if (len(d_ids) > 0):
d_ids_str = ",".join(d_ids)
sql = "select DISTINCT p.* \
from papers as p inner join papers_authors as pa on p.paperid=pa.paperid \
inner join papers_keywords as pk on p.paperid=pk.paperid \
inner join papers_domains as pd on p.paperid=pd.paperid \
and p.deleted_at is null \
and \
(pa.authorid In (" + a_ids_str + ") \
or \
pk.keywordid In (" + k_ids_str + ") \
or \
pd.domainid In (" + d_ids_str + ") \
or \
lower(title) like lower('" + qLike + "')) \
order by p.lastcommentat desc"
papers = query_db(sql)
else:
papers = []
return render_catalog('catalog/catalog-search.html',
papers=with_description(papers))
def userinfo(name):
u = query_db("select * from users where username = ?", [name], one=True)
if (u is None):
return render_catalog('catalog/oneuser.html',
error="User " + name + " is not found",
username=name)
papers = query_db(
"select p.* from papers as p, likes as l \
where \
p.deleted_at is null and \
p.paperid = l.paperid and \
l.userid = ? \
order by l.liketime desc", [u['userid']])
return render_catalog('catalog/oneuser.html',
papers=with_description(papers),
username=u['username'])
def add_comment(paperid):
con = get_db()
error = None
with con:
con.execute('insert into comments \
(comment,userid,paperid) \
values (?,?,?)',
[
# here we do not escape, because we will
# do it in jinja
request.form['comment'],
get_user_id(),
paperid
])
con.execute('update papers set lastcommentat=datetime() \
where paperid = ?', [paperid])
if user_authenticated():
flash('You successfully commented the paper')
else:
flash('You anonymously commented the paper')
last_c_id=query_db("SELECT last_insert_rowid() as lid",
one=True)['lid']
# notify user about new comment
comment_was_added(paperid, last_c_id)
return redirect(url_for('onepaper',paperid=paperid, title = None, error=error)
+ "#comment-"
+ str(last_c_id))
def commentators(paperid):
return query_db(
"select u.* \
from comments as c, users as u \
where c.userid = u.userid and \
c.deleted_at is null and \
c.paperid = ?", [paperid])
def last_week_updates():
# example link
# https://papers-gamma.link/paper/52/#comment-1055
## Every notif is a tuple (link, text, createtime, username, type)
## TODO: use notifs table
papers_n_comments = query_db("select '/paper/' || p.paperid as link, \
p.title as text, \
p.createtime as createtime, \
u.username as username, \
'paper' as type \
from papers as p \
left join users as u on p.userid = u.userid \
where deleted_at is null \
and p.createtime > date('now','-10 days') \
UNION \
select \
'/paper/' || c.paperid || '/#comment-' || c.commentid as link, \
c.comment as text, \
c.createtime as createtime, \
u.username as username, \
'comment' as type \
from \
comments as c \
left join users as u on c.userid = u.userid \
where \
c.deleted_at is null and \
c.createtime > date('now','-10 days') \
\
order by createtime desc \
")
return render_template('news.html', notifs=papers_n_comments)
def who_likes(paperid):
return query_db(
"select u.* \
from likes as l, users as u \
where l.userid = u.userid and \
l.paperid = ? ",
[paperid])
def delete_paper_with_check(paperid):
if can_delete_paper(paperid):
pap = query_db("select * from papers where paperid = ?",
[paperid], one=True)
delete_paper(paperid)
flash('You successfully removed the paper')
return redirect(url_for('all'))
else:
return "<h1>Forbidden</h1>", 403
def delete_comment_with_check(commentid):
if can_delete_comment(commentid):
comment = query_db("select * from comments where commentid = ?",
[commentid], one=True)
delete_comment(commentid)
flash('You successfully removed the comment')
return redirect(url_for('onepaper',
paperid=comment['paperid']))
else:
return "<h1>Forbidden</h1>", 403
def delete_tag_with_check(keyword):
if can_delete_tag(keyword):
tag = query_db("select * from keywords where keyword = ?", [keyword],
one=True)
delete_tag(keyword)
delete_papers_tags(tag['keywordid'])
flash('You successfully removed the tag')
return redirect(url_for('catalog'))
else:
return "<h1>Forbidden</h1>", 403
def delete_author_with_check(fullname):
if can_delete_author(fullname):
author = query_db("select * from authors where fullname = ?",
[fullname],
one=True)
delete_author(fullname)
delete_papers_authors(author['authorid'])
flash('You successfully removed the author')
return redirect(url_for('catalog'))
else:
return "<h1>Forbidden</h1>", 403
def delete_domain_with_check(domainname):
if can_delete_domain(domainname):
dom = query_db("select * from domains where domainname = ?",
[domainname],
one=True)
delete_domain(domainname)
delete_papers_domain(dom['domainid'])
flash('You successfully removed the domain')
return redirect(url_for('catalog'))
else:
return "<h1>Forbidden</h1>", 403
def domain(domainname):
d = query_db("select domainid from domains where \
domainname = ?", [domainname],
one=True)
if (d is None):
return render_catalog('catalog/papers-in-domain.html',
error = "Domain " + \
domainname + " not found",
domainname=domainname)
domainid = d['domainid']
papers = query_db(
"select p.* \
from papers as p, \
papers_domains as pd \
where p.deleted_at is null and \
p.paperid=pd.paperid \
and pd.domainid = ? \
order by p.lastcommentat desc", [domainid])
return render_catalog('catalog/papers-in-domain.html',
papers=with_description(papers),
domainname=domainname)
def keyword(keyword):
k = query_db("select keywordid from keywords where \
keyword = ?", [keyword],
one=True)
if (k is None):
return render_catalog('catalog/papers-with-keyword.html',
error = "Tag " + \
keyword + " is not found",
keyword=keyword)
keywordid = k['keywordid']
papers = query_db(
"select p.* \
from papers as p, \
papers_keywords as pk \
where \
p.deleted_at is null and \
p.paperid=pk.paperid and \
pk.keywordid = ? \
order by p.lastcommentat desc", [keywordid])
return render_catalog('catalog/papers-with-keyword.html',
papers=with_description(papers),
keyword=keyword)
def catalog():
if request.args.get('q'):
q = '%' + request.args.get('q') + '%'
papers = query_db(
"select * from papers \
where \
deleted_at is null and \
lower(title) like lower(?) \
order by title", [q])
else:
papers = []
return render_catalog('catalog/catalog-search.html',
papers=with_description(papers))
def author(fullname):
a = query_db("select authorid from authors where \
fullname = ?", [fullname],
one=True)
if (a is None):
return render_catalog('catalog/papers-of-author.html',
error = "Author " + \
fullname + " is not found",
fullname=fullname)
authorid = a['authorid']
papers = query_db(
"select p.* \
from papers as p, \
papers_authors as pa \
where \
p.deleted_at is null and \
p.paperid=pa.paperid and \
pa.authorid = ? \
order by p.lastcommentat desc", [authorid])
return render_catalog('catalog/papers-of-author.html',
papers=with_description(papers),
fullname=fullname)
def new_password_link():
error = None
if request.method == 'POST':
u = query_db('select userid,username,email,createtime,valid \
from users \
where email = ?', [request.form['email']],
one=True)
if u is not None:
send_password_change_mail(request.form['email'])
flash('A confirmation link has been sent to you. \n\
Please, check your mailbox (%s)' % request.form['email'])
return redirect(url_for('index'))
else:
error = 'User with this email does not exists'
return render_template('users/restore.html', error=error)
def edit_domain(domainname):
if not can_delete_domain(domainname):
return "<h1>It's forbidden, my dear</h1>", 403
error = None
domain = query_db("select * \
from domains \
where domainname = ?", [domainname],
one=True)
if request.method == 'GET':
request.form.id = domain['domainid']
request.form.name = domain['domainname']
if request.method == 'POST':
if request.form['name'] == "":
error = 'Please enter a valid name'
else:
con = get_db()
with con:
con.execute(
'update domains set domainname = ? \
where domainid = ? ',
[request.form['name'], request.form['id']])
flash('You successfully modified the domain')
return redirect(
url_for('domain', domainname=request.form['name']))
return render_template(
'catalog/edit.html',
entry=domain,
editname="domain",
error=error,
name=domainname,
titleP="Edit the domain",
domains=query_db("select * from domains where not (domainname = ?)",
[domainname]))
def send_mail_(usermail, message, subject):
with app.app_context():
u = query_db('select * \
from users \
where email = ?', [usermail],
one=True)
if (not u['notifs_muted'] and u['userid'] != 1):
# Create a text/plain message
msg = MIMEText(message)
msg['Subject'] = subject
msg['From'] = 'Papers-gamma Team ' + '<' + MAIL_USER + '>'
msg['To'] = usermail
# Send the message via our own SMTP server.
s = smtplib.SMTP_SSL(MAIL_SERVER)
s.login(MAIL_USER, MAIL_PASS)
s.send_message(msg)
s.quit()
def register_confirmation(key):
error = None
u = query_db('select userid,username,email, \
createtime,valid,about \
from users \
where key = ?',
[key], one=True)
if u is not None:
con = get_db()
with con:
con.execute('update users set valid = 1, key = null \
where key = ?',
[key])
session.permanent = True
session['user'] = u
flash('Hello ' + u['username'] + \
'. You have successfully confirmed your email address')
return redirect(url_for('usersite',username=session['user']['username']))
def last_month_updates():
# example link
# https://papers-gamma.link/paper/52/#comment-1055
papers = query_db("select '/paper/' || p.paperid as link, \
p.title as text, \
p.createtime as createtime, \
u.username as username, \
(select count(*) from comments c \
where c.paperid = p.paperid \
and c.deleted_at is null \
) as count_comment \
from papers as p \
left join users as u on p.userid = u.userid \
where p.deleted_at is null \
and p.createtime > date('now','-30 days') \
order by createtime desc \
")
return render_template('lastmonth.html', notifs=papers)