def save_check(db_name, account_code, quota: QuotaResponse):
tickets = 'NULL' if quota.tickets is None else quota.tickets
emds = 'NULL' if quota.emds is None else quota.emds
db_execute(
db_name,
'''INSERT INTO quota_check (account, remaining_tickets, remaining_emds)
VALUES ('{account}', {tickets}, {emds})
'''.format(account=account_code, tickets=tickets, emds=emds))
def setup_store(db_name):
db_execute(
db_name, '''CREATE TABLE IF NOT EXISTS quota_check (
id INTEGER PRIMARY KEY,
account VARCHAR,
remaining_tickets INTEGER,
remaining_emds INTEGER,
created_at integer(4) not null default (strftime('%s','now'))
)''')
def logout():
token = request.cookies.get('token')
if token is not None:
db_execute('''
UPDATE users
SET session = NULL
WHERE session = :token
''',
token=token)
resp = redirect('/')
resp.set_cookie('token', '', expires=0)
return resp
def register(name, password):
if '' in (name, password):
return redirect('/')
salt = uuid.uuid4().hex
hash = salt_hash(salt, password)
db_execute("""
INSERT INTO users (name, salt, hash)
VALUES (:name, :salt, :hash)
""",
name=name,
salt=salt,
hash=hash)
return login(name, password)
def vote(country, magnitude):
name = request.name
magnitude = int(magnitude)
assert 0 < magnitude <= 12 and magnitude != 11
db_execute("""
INSERT OR REPLACE INTO votes(user_id, contender_id, magnitude)
VALUES (
(SELECT id FROM users WHERE name = :name),
(SELECT id FROM contenders WHERE country = :country),
:magnitude
)
""",
name=name,
country=country.lower(),
magnitude=magnitude)
def ladder_global():
return db_execute("""
WITH t AS (
SELECT contender_id, SUM(magnitude) AS score FROM votes
GROUP BY contender_id
)
SELECT country, IFNULL(t.score, 0) AS score
FROM contenders LEFT JOIN t ON id = contender_id
ORDER BY score DESC, country
""")
def ladder_user(name=None):
name = name or request.authorization.username
return db_execute("""
WITH t AS (
SELECT contender_id, SUM(magnitude) AS score
FROM votes
WHERE user_id = (SELECT id FROM users WHERE name = :name)
GROUP BY contender_id
)
SELECT country, IFNULL(t.score, 0) AS score
FROM contenders LEFT JOIN t ON id = contender_id
ORDER BY t.score DESC, country
""",
name=name)
def login(name, password):
resp = redirect('/')
try:
salt, hash = db_execute('''
SELECT salt, hash
FROM users
WHERE name = :name
''',
name=name)[0]
if hash == salt_hash(salt, password):
token = salt_hash(uuid.uuid4().hex, name)
db_execute('''
UPDATE users
SET session = :token
WHERE name = :name
''',
token=token,
name=name)
resp.set_cookie('token', token)
return resp
except IndexError as e:
pass
resp.set_cookie('token', '', expires=0)
return resp
import uuid, hashlib
try:
import ujson
except ImportError:
import json as ujson # for running on termux
from utils import db_execute
try:
db_execute('DROP TABLE users')
except:
pass
try:
db_execute('DROP TABLE contenders')
except:
pass
try:
db_execute('DROP TABLE votes')
except:
pass
db_execute('''
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name text UNIQUE NOT NULL,
salt text NOT NULL,
hash text NOT NULL,
session text DEFAULT NULL
)
''')
db_execute('''
def drop_store(db_name):
db_execute(db_name, 'DROP TABLE IF EXISTS quota_check')
msg['To'] = recipient
msg.attach(MIMEText(body_text, 'plain'))
msg.attach(MIMEText(body_html, 'html'))
server = smtplib.SMTP(smtp_gateway, smtp_port)
server.ehlo()
server.starttls()
server.ehlo()
server.login(smtp_user, smtp_password)
server.sendmail(sender[1], recipient, msg.as_string())
server.close()
if __name__ == '__main__':
rows = db_execute(
conf.db_name,
'SELECT account, remaining_tickets, remaining_emds, created_at'
' FROM quota_check'
' GROUP BY account HAVING MAX(created_at)'
)
info_items = list()
alert = False
for row in rows:
if row[0] not in conf.accounts:
continue # unknown account code
info_items.append({
'code': row[0],
'remaining_tickets': row[1],
'remaining_emds': row[2],
'datetime': time.strftime('%Y-%m-%d %H:%M:%S (%Z)', time.localtime(row[3])),
'alert': (row[1] or 0) < conf.accounts[row[0]].get('alert', 0),
})
alert = info_items[-1]['alert'] or alert
def users():
return db_execute("""
SELECT name FROM users
ORDER BY name
""")