def get_movecnt(match_id):
cur = get_db().cursor(cursor_factory=DictCursor)
cur.execute('SELECT COUNT(*) FROM move mv'
' WHERE mv.match_id = %s', (match_id, ))
result = cur.fetchone()
cur.close()
return result['count']
def login():
if (request.method == 'POST'):
username = request.form['username']
password = request.form['password']
error = None
dbcon = get_db()
cur = dbcon.cursor(cursor_factory=DictCursor)
cur.execute("SELECT * FROM auth_user WHERE username = %s",
(username, ))
user = cur.fetchone()
cur.close()
if (user is None):
error = 'Incorrect username.'
elif (not check_password_hash(user['password'], password)):
error = 'Incorrect password.'
if (error is None):
session.clear()
session['user_id'] = user['id']
return redirect(url_for('index'))
flash(error)
return render_template('auth/login.html')
def register():
if (request.method == 'POST'):
username = request.form['username']
password = request.form['password']
error = None
dbcon = get_db()
cur = dbcon.cursor(cursor_factory=DictCursor)
if (not username):
error = 'Username is required.'
elif (not password):
error = 'Password is required.'
else:
cur.execute("SELECT id FROM auth_user WHERE username = %s",
(username, ))
user = cur.fetchone()
if (user is not None):
error = 'User {} is already registered.'.format(username)
if (error is None):
cur.execute(
"INSERT INTO auth_user (username, password) VALUES (%s, %s)",
(username, generate_password_hash(password)))
cur.close()
dbcon.commit()
return redirect(url_for('auth.login'))
flash(error)
cur.close()
return render_template('auth/register.html')
def get_last_move(match_id):
cur = get_db().cursor(cursor_factory=DictCursor)
cur.execute(
'SELECT * FROM move mv'
' WHERE mv.match_id = %s ORDER BY id DESC LIMIT 1', (match_id, ))
move = cur.fetchone()
cur.close()
return move
def get_moves(match_id):
cur = get_db().cursor(cursor_factory=DictCursor)
cur.execute(
'SELECT * FROM move mv'
' WHERE mv.match_id = %s ORDER BY id ASC', (match_id, ))
moves = cur.fetchall()
cur.close()
return moves
def get_match(id):
cur = get_db().cursor(cursor_factory=DictCursor)
cur.execute(
'SELECT m.id, status, level, created, board, auth_user_id, auth_guest_id, username'
' FROM match m JOIN auth_user u ON m.auth_user_id = u.id'
' WHERE m.id = %s', (id, ))
match = cur.fetchone()
cur.close()
return match
def new_move(match_id, prevfields, src, dst, prompiece):
dbcon = get_db()
cur = dbcon.cursor()
cur.execute(
'INSERT INTO move (match_id, prevfields, src, dst, prompiece)'
' VALUES (%s, %s, %s, %s, %s)',
(match_id, prevfields, src, dst, prompiece))
dbcon.commit()
cur.close()
def update_player(match_id, iswhite, name, ishuman, consumedsecs):
dbcon = get_db()
cur = dbcon.cursor()
cur.execute(
'UPDATE player SET iswhite = %s, name = %s, ishuman = %s, consumedsecs = %s'
' WHERE match_id = %s and iswhite = %s',
(iswhite, name, ishuman, consumedsecs, match_id, iswhite))
dbcon.commit()
cur.close()
def get_player(match_id, iswhite):
cur = get_db().cursor(cursor_factory=DictCursor)
cur.execute(
'SELECT * FROM player p'
' WHERE p.match_id = %s AND p.iswhite = %s', (
match_id,
iswhite,
))
player = cur.fetchone()
cur.close()
return player
def get_matches():
cur = get_db().cursor(cursor_factory=DictCursor)
cur.execute(
'SELECT m.id, status, level, created, auth_user_id, username,'
' (SELECT name FROM player p WHERE p.match_id = m.id and p.iswhite = true) as wplayer_name, '
' (SELECT ishuman FROM player p WHERE p.match_id = m.id and p.iswhite = true) as wplayer_ishuman, '
' (SELECT name FROM player p WHERE p.match_id = m.id and p.iswhite = false) as bplayer_name, '
' (SELECT ishuman FROM player p WHERE p.match_id = m.id and p.iswhite = false) as bplayer_ishuman'
' FROM match m JOIN auth_user u ON m.auth_user_id = u.id'
' ORDER BY created DESC')
matches = cur.fetchall()
cur.close()
return matches
def load_logged_in_user():
userid = session.get('user_id')
if (userid is None):
g.user = None
else:
dbcon = get_db()
cur = dbcon.cursor(cursor_factory=DictCursor)
cur.execute('SELECT * FROM auth_user WHERE id = %s', (userid, ))
user = cur.fetchone()
cur.close()
if (user is not None):
g.user = user
def update_match(id, status, level, board, \
wplayer_name, wplayer_ishuman, wplayer_consumedsecs, \
bplayer_name, bplayer_ishuman, bplayer_consumedsecs):
dbcon = get_db()
cur = dbcon.cursor()
cur.execute(
'UPDATE match SET status = %s, level = %s, board = %s'
' WHERE id = %s', (status, level, board, id))
if (wplayer_name is not None and wplayer_ishuman is not None):
cur.execute(
'UPDATE player SET name = %s, ishuman = %s, consumedsecs = %s'
' WHERE match_id = %s and iswhite = true',
(wplayer_name, wplayer_ishuman, wplayer_consumedsecs, id))
if (bplayer_name is not None and bplayer_ishuman is not None):
cur.execute(
'UPDATE player SET name = %s, ishuman = %s, consumedsecs = %s'
' WHERE match_id = %s and iswhite = false',
(bplayer_name, bplayer_ishuman, bplayer_consumedsecs, id))
dbcon.commit()
cur.close()
def new_match(level, wplayer_name, wplayer_ishuman, bplayer_name,
bplayer_ishuman):
dbcon = get_db()
cur = dbcon.cursor(cursor_factory=DictCursor)
cur.execute('INSERT INTO match (level, auth_user_id) VALUES(%s, %s)',
(level, g.user['id']))
cur.execute('SELECT LASTVAL()')
matchid = cur.fetchone()['lastval']
iswhite = True
cur.execute(
'INSERT INTO player (match_id, name, iswhite, ishuman)'
' VALUES (%s, %s, %s, %s)',
(matchid, wplayer_name, iswhite, wplayer_ishuman))
iswhite = False
cur.execute(
'INSERT INTO player (match_id, name, iswhite, ishuman)'
' VALUES (%s, %s, %s, %s)',
(matchid, bplayer_name, iswhite, bplayer_ishuman))
dbcon.commit()
cur.close()
return get_match(matchid)
def delete_move(id):
dbcon = get_db()
cur = dbcon.cursor()
cur.execute('DELETE FROM move WHERE id = %s', (id, ))
dbcon.commit()
cur.close()
def index():
db = get_db()
matches = get_matches()
return render_template('match/index.html', matches=matches)