def single_issue(dbfile, id):
c = util.cursor(dbfile)
vals = c.execute("SELECT %s FROM ticket where id=%d" % \
(fields, id)).fetchone()
changes = issue_changes_and_attachments(id)
return issue.issue(**dict(zip(fields.split(","), vals) + \
[('_changes_and_attachments', changes)]))
def user_rem():
sid = request.sid
cur = cursor()
cur.execute('SELECT NAME, GAME FROM USERS WHERE SID = %s', (sid, ))
user_data = cur.fetchone()
if user_data is None:
print_log_line('Phantom sid tried to disconnect: %s' % (sid, ))
return
username = user_data[0]
game = user_data[1]
print_log_line('User %s (%s) leaving %s' % (username, sid, game))
game_state = get_game_by_name(game)
if game_state is None:
print_log_line('Missing game data: %s' % (game, ))
return
game_state.remove_user(username)
if game_state.num_users() == 0:
cur.execute('DELETE FROM GAMES WHERE NAME = %s', (game, ))
else:
update_game_state(game, game_state)
cur.execute('DELETE FROM USERS WHERE SID = %s', (sid, ))
new_state = game_state.generate_game_state()
update_message = 'User %s has left' % (username, )
socketio.emit('game_state_update', {
'game_state': new_state,
'event': 'leave',
'status': update_message
},
room=game)
def single_issue(dbfile, id):
c = util.cursor(dbfile)
vals = c.execute("SELECT %s FROM ticket where id=%d" % \
(fields, id)).fetchone()
changes = issue_changes_and_attachments(id)
return issue.issue(**dict(zip(fields.split(","), vals) + \
[('_changes_and_attachments', changes)]))
def flip_tile(args):
user = args.get('user')
game = args.get('room')
cur = cursor()
game_state = get_game_by_name(game)
if game_state is None:
print_log_line('user %s attempted to access missing room %s' %
(user, game))
return
flipped_tile = game_state.flip_tile()
new_state = game_state.generate_game_state()
update_game_state(game, game_state)
state_update = 'No more letters'
if flipped_tile is not None:
article = 'an' if flipped_tile in 'ERIOASFHLXNM' else 'a'
state_update = '%s flipped %s "%s"' % (user, article, flipped_tile)
print_log_line('room %s: %s flipped %s "%s"' %
(game, user, article, flipped_tile))
socketio.emit('game_state_update', {
'status': state_update,
'event': 'flip',
'game_state': new_state
},
room=game)
def get_logs():
cur = cursor()
cur.execute(
'SELECT LOG_LINE, TIME FROM LOGS ORDER BY TIME DESC LIMIT 1000')
logs = cur.fetchall()
return '<br>'.join(
['%s: %s' % (log_line[1], log_line[0]) for log_line in logs])
def get_status():
cur = cursor()
cur.execute('SELECT * FROM GAMES')
game_status = cur.fetchall()
cur.execute('SELECT * FROM USERS')
user_status = cur.fetchall()
return json.dumps(game_status) + '<br>' + json.dumps(user_status)
def issues(dbfile):
c = util.cursor(dbfile)
for (id,) in c.execute("SELECT id FROM ticket").fetchall():
vals = c.execute("SELECT %s FROM ticket where id=%d" % \
(fields, id)).fetchone()
changes = issue_changes_and_attachments(id)
yield issue.issue(**dict(zip(fields.split(","), vals) + \
[('_changes_and_attachments', changes)]))
def issue_changes_and_attachments(id):
c = util.cursor()
c.execute("SELECT time, author, field, oldvalue, newvalue FROM ticket_change WHERE ticket= '%s' ORDER BY time ASC" % (id,))
for vals in c.fetchall():
yield vals
c.execute("SELECT time, author, description, filename FROM attachment WHERE id='%s' ORDER BY time ASC" % (id,))
for vals in c.fetchall():
yield vals
def get_game_by_name(game_name):
cur = cursor()
cur.execute('SELECT STATE FROM GAMES WHERE NAME = %s', (game_name, ))
game_state_str = cur.fetchone()
if game_state_str is None:
return None
game_state = game_data.deserialize_game_room(json.loads(game_state_str[0]))
return game_state
def issues(dbfile):
c = util.cursor(dbfile)
for (id, ) in c.execute("SELECT id FROM ticket").fetchall():
vals = c.execute("SELECT %s FROM ticket where id=%d" % \
(fields, id)).fetchone()
changes = issue_changes_and_attachments(id)
yield issue.issue(**dict(zip(fields.split(","), vals) + \
[('_changes_and_attachments', changes)]))
def issue_changes_and_attachments(id):
c = util.cursor()
for vals in c.execute(
"SELECT time, author, field, oldvalue, newvalue FROM ticket_change WHERE ticket=?",
(id, )).fetchall():
yield vals
for vals in c.execute(
"SELECT time, author, description, filename FROM attachment WHERE id=?",
(id, )).fetchall():
yield vals
def get_author_msc_matrix():
if not force_gen and all(os.path.isfile(filename) for filename in [
"derived_data/author_msc_map.npz",
"derived_data/author_msc_map__row2author_name.json",
"derived_data/author_msc_map__col2msc_code.json"]):
mat = load_csr_matrix("derived_data/author_msc_map.npz")
with open("derived_data/author_msc_map__row2author_name.json") as f:
row2author_map = json.load(f)
with open("derived_data/author_msc_map__col2msc_code.json") as f:
col2msc_map = json.load(f)
return mat, row2author_map, col2msc_map
else:
author2msc_map = defaultdict(lambda: defaultdict(int))
cursor().execute("""SELECT display_name, msc, COUNT(*) FROM authorship
JOIN msc_assignment ON authorship.document = msc_assignment.document
WHERE authorship.rank <= 2 AND msc_assignment.pos <= 3
GROUP BY display_name, msc
ORDER BY display_name""")
for row in cursor():
author2msc_map[row[0]][row[1][:2]] += row[2]
author_names, msc_counts = zip(*author2msc_map.items())
msc_code2index_map = dict(zip(msc_classes, range(len(msc_classes))))
col2msc_map = {index: msc for msc, index in msc_code2index_map.items()}
mat = build_csr_matrix(msc_counts, token2index_map=msc_code2index_map)
save_csr_matrix(mat, "derived_data/author_msc_map")
row2author_map = dict(zip(range(len(author_names)), author_names))
with open("derived_data/author_msc_map__row2author_name.json", "w") as f:
json.dump(row2author_map, f)
with open("derived_data/author_msc_map__col2msc_code.json", "w") as f:
json.dump(col2msc_map, f)
return mat, row2author_map, col2msc_map
def setup_db():
destroy_db()
cur = cursor()
cur.execute('CREATE TABLE USERS ( \
NAME TEXT NOT NULL, \
SID TEXT , \
GAME TEXT )')
cur.execute('CREATE TABLE GAMES ( \
NAME TEXT NOT NULL, \
STATE TEXT )')
cur.execute('CREATE TABLE LOGS ( \
LOG_LINE TEXT NOT NULL, \
TIME TIMESTAMP NOT NULL)')
def enrich_ids_with_authors(setting):
f = open("derived_data/" + setting_string(**setting) + "__ids")
f2 = open("derived_data/" + setting_string(**setting) + "__ids_with_authors", "w")
findAuthorStmt = "SELECT display_name, zbmath_id FROM authorship WHERE document=%(document)s AND rank<=%(maxrank)s"
max_author_rank = 2
if config("max_author_rank") is not None:
max_author_rank = config("max_author_rank")
for line in f:
x = line.split(";")
ids = {}
if setting['granularity'] == 'paragraphs':
ids['doc'] = x[0].strip()
ids['par'] = x[1].strip()
elif setting['granularity'] == 'documents':
ids['doc'] = x[0].strip()
else:
raise ValueError("granularity must be either 'paragraphs' or 'documents'")
authors = []
cursor().execute(findAuthorStmt, {"document": ids['doc'], "maxrank": max_author_rank})
for row in cursor():
display_name = row[0]
zbmath_id = row[1]
authors.append((display_name, zbmath_id))
if setting['granularity'] == 'paragraphs':
newline = ids['doc'] + ";" + ids['par'] + ";" + ";".join(map(lambda author: author[0] + "(" + str(author[1]) + ")", authors)) + (";" * (max_author_rank-len(authors)))
elif setting['granularity'] == 'documents':
newline = ids['doc'] + ";" + ";".join(map(lambda author: author[0] + "(" + str(author[1]) + ")", authors)) + (";" * (max_author_rank-len(authors)))
else:
raise ValueError("granularity must be either 'paragraphs' or 'documents'")
f2.write(newline + "\n")
f.close()
f2.close()
def destroy_db():
cur = cursor()
try:
cur.execute('DROP TABLE USERS')
except Exception:
pass
try:
cur.execute('DROP TABLE GAMES')
except Exception:
pass
try:
cur.execute('DROP TABLE LOGS')
except Exception:
pass
def user_disc():
sid = request.sid
cur = cursor()
cur.execute('SELECT NAME, GAME FROM USERS WHERE SID = %s', (sid, ))
user_data = cur.fetchone()
if user_data is None:
print_log_line('Unknown user with SID = %s disconnected' % (sid, ))
return
username, game = user_data
print_log_line('%s (%s) disconnected' % (username, sid))
cur.execute('UPDATE USERS SET SID = NULL WHERE SID = %s', (sid, ))
cur.execute('SELECT NAME FROM USERS WHERE GAME = %s AND SID IS NOT NULL',
(game, ))
if cur.fetchone() is None:
cur.execute('DELETE FROM GAMES WHERE NAME = %s', (game, ))
def join_game(data):
sid = request.sid
username = data['username']
game_name = data['game_name']
cur = cursor()
game_state = get_game_by_name(game_name)
cur.execute('DELETE FROM USERS WHERE NAME = %s', (username, ))
cur.execute('INSERT INTO USERS (NAME, SID, GAME) VALUES (%s, %s, %s)',
(username, sid, game_name))
flask_socketio.join_room(game_name)
if game_state is None:
##create the game:
cur.execute('INSERT INTO GAMES (NAME) VALUES (%s)', (game_name, ))
game_state = game_data.game_room(username)
else:
if game_state.has_user(username):
print_log_line('%s updating sid to %s' % (username, sid))
send_challenge_updates(sid, game_state)
return
game_state.add_user(username)
print_log_line('user %s (%s) joining game %s' % (username, sid, game_name))
update_game_state(game_name, game_state)
cur.execute('UPDATE GAMES SET STATE = %s WHERE NAME = %s',
(json.dumps(game_state.generate_game_state()), game_name))
new_state = game_state.generate_game_state()
update_message = 'User %s has joined' % (username, )
socketio.emit('game_state_update', {
'game_state': new_state,
'event': 'join',
'status': update_message
},
room=game_name)
send_challenge_updates(sid, game_state)
def get_ticket_ccs():
c = util.cursor()
return [
s.strip() for v in c.execute("SELECT cc FROM ticket").fetchall()
for s in v[0].split(",") if s != ""
]
def update_game_state(game_name, game_state):
cur = cursor()
cur.execute('UPDATE GAMES SET STATE = %s WHERE NAME = %s',
(json.dumps(game_state.generate_game_state()), game_name))
def get_ticket_reporters():
c = util.cursor()
return [v[0] for v in c.execute("SELECT reporter FROM ticket").fetchall()]
def get_ticket_ccs():
c = util.cursor()
return [s.strip() for v in c.execute("SELECT cc FROM ticket").fetchall() for s in v[0].split(",") if s != ""]
def get_ticket_reporters():
c = util.cursor()
return [v[0] for v in c.execute("SELECT reporter FROM ticket").fetchall()]
def print_log_line(log_line):
cur = cursor()
cur.execute('INSERT INTO LOGS (LOG_LINE, TIME) VALUES (%s, NOW())',
(log_line, ))
