def db_create_game(name, numb_sentences, words_shown): conn, cursor = get_conn_cursor() cursor.execute( """ INSERT INTO game.game (name, sentences, words_shown) VALUES (%s,%s,%s) """, (name, numb_sentences, words_shown)) game_id = cursor.getlastrowid() # insert a record into game status cursor.execute( """ INSERT INTO game.status (game_id) VALUES (%s) """, (game_id, )) conn.close() create_game_code(game_id) return game_id
def db_get_game_words(game_id): conn, cursor = get_conn_cursor() cursor.execute( """ SELECT words_shown FROM game.game WHERE id = %s """, (game_id, )) words_shown = cursor.fetchone()['words_shown'] cursor.execute( """ SELECT w.word word FROM game.words w LEFT JOIN game.game g ON g.id = w.game_id WHERE g.id = %s ORDER BY w.date_added DESC LIMIT %s """, (game_id, words_shown)) words = [] for x in cursor.fetchall(): words.append(x['word']) conn.close() words.reverse() return words
def db_get_game_users(game_id, with_user_id=False): conn, cursor = get_conn_cursor() cursor.execute( """ SELECT u.name name, IF(g.creator_id = u.id, true, false) creator, u.id user_id FROM game.users u LEFT JOIN game.game g ON g.id = u.game_id WHERE game_id = %s """, (game_id, )) users = [] for x in cursor.fetchall(): d = {'name': x['name'], 'is_creator': x['creator']} if with_user_id: d['user_id'] = x['user_id'] users.append(d) conn.close() return users
def db_get_game_code(game_id): conn, cursor = get_conn_cursor() cursor.execute("SELECT code FROM game.codes WHERE game_id = %s", (game_id, )) game_code = cursor.fetchone()['code'] conn.close() return game_code
def db_update_game_creator(game_id, user_id): conn, cursor = get_conn_cursor() cursor.execute( """ UPDATE game.game SET creator_id = %s WHERE id = %s """, (user_id, game_id)) conn.close()
def db_add_game_word(word, game_id, user_id): conn, cursor = get_conn_cursor() cursor.execute( """ INSERT INTO game.words (game_id, user_id, word) VALUES (%s, %s, %s) """, (game_id, user_id, word)) if word.endswith('.'): cursor.execute( """ UPDATE game.status SET sentences_completed = sentences_completed + 1 WHERE game_id = %s """, (game_id, )) next_user_id = db_get_next_user_in_order(game_id) cursor.execute( """ UPDATE game.status SET current_user_id = %s WHERE game_id = %s """, (next_user_id, game_id)) cursor.execute( """ SELECT s.sentences_completed as sentences_completed, g.sentences sentences FROM game.status s LEFT JOIN game.game g ON g.id = s.game_id WHERE game_id = %s """, (game_id, )) result = cursor.fetchone() sentences_completed = result['sentences_completed'] game_sentences = result['sentences'] if int(sentences_completed) >= int(game_sentences): cursor.execute( """ UPDATE game.status SET finished = 1, date_finished = NOW() WHERE game_id = %s """, (game_id, )) conn.close()
def db_start_game(game_id): conn, cursor = get_conn_cursor() cursor.execute( """ UPDATE game.status SET started = 1, date_started = NOW() WHERE game_id = %s """, (game_id, )) conn.close() db_set_game_order(game_id)
def db_get_completed_game_sentences_from_code(game_code): conn, cursor = get_conn_cursor() cursor.execute( """ SELECT game_id FROM game.codes WHERE code = %s """, (game_code, )) game_id = cursor.fetchone()['game_id'] conn.close() return db_get_completed_game_sentences(game_id)
def db_get_game_info_from_code(game_code): conn, cursor = get_conn_cursor() cursor.execute( """ SELECT game_id FROM game.codes WHERE code = %s """, (game_code, )) game_id = cursor.fetchone()['game_id'] conn.close() return db_get_game_info(None, game_id)
def db_create_user(name, game_id): conn, cursor = get_conn_cursor() cursor.execute( """ INSERT INTO game.users (name, game_id) SELECT t.name, t.game_id FROM (SELECT %s name, %s game_id) t JOIN game.status s ON t.game_id = s.game_id WHERE s.started != 1 """, (name, game_id)) user_id = cursor.getlastrowid() conn.close() return user_id
def db_get_completed_game_sentences(game_id): conn, cursor = get_conn_cursor() cursor.execute( """ SELECT w.word word, u.name user_name FROM game.words w LEFT JOIN game.users u ON u.id = w.user_id WHERE w.game_id = %s ORDER BY w.date_added """, (game_id, )) word_info = cursor.fetchall() conn.close() return word_info
def create_game_code(game_id): conn, cursor = get_conn_cursor() while 1: code = generate_game_code() try: cursor.execute( """ INSERT INTO game.codes (game_id, code) VALUES (%s,%s) """, (game_id, code)) conn.close() break except mysql.connector.Error as err: if err.errno == errorcode.ER_DUP_INDEX: pass else: raise err
def db_get_game_info(user_id, game_id): conn, cursor = get_conn_cursor() cursor.execute( """ SELECT g.id game_id, started game_started, finished game_finished, IF(s.current_user_id = %(user_id)s, true, false) is_current_user, u2.name current_user_name, IF(g.creator_id = %(user_id)s, true, false) is_creator, g.name game_name, c.code game_code, IF(u1.id = %(user_id)s, true, false) is_user, s.sentences_completed sentences_completed FROM game.game g LEFT JOIN game.users u1 ON g.id = u1.game_id AND u1.id = %(user_id)s LEFT JOIN game.status s ON g.id = s.game_id LEFT JOIN game.users u2 ON g.id = u2.game_id AND u2.id = s.current_user_id LEFT JOIN game.`order` o ON g.id = o.game_id LEFT JOIN game.codes c ON g.id = c.game_id WHERE g.id = %(game_id)s """, { 'game_id': game_id, 'user_id': user_id }) game_info = cursor.fetchone() conn.close() return game_info
def db_get_next_user_in_order(game_id): """ Gets the next user based on the order. :param str|int game_id: The game ID :return: The ID of the next user """ conn, cursor = get_conn_cursor() cursor.execute( """ SELECT current_user_id FROM game.status WHERE game_id = %s """, (game_id, )) current_user_id = cursor.fetchone()['current_user_id'] cursor.execute( """ SELECT user_id FROM game.`order` WHERE game_id = %s ORDER BY `order` ASC """, (game_id, )) user_order = [] for x in cursor.fetchall(): user_order.append(x['user_id']) conn.close() for i, x in enumerate(user_order): if x == current_user_id: if i + 1 == len(user_order): return user_order[0] return user_order[i + 1]
def db_set_game_order(game_id): user_ids = [x['user_id'] for x in db_get_game_users(game_id, True)] random.shuffle(user_ids) conn, cursor = get_conn_cursor() for i, user_id in enumerate(user_ids, 1): cursor.execute( """ INSERT INTO game.`order` (game_id, user_id, `order`) VALUES (%s, %s, %s) """, (game_id, user_id, i)) cursor.execute( """ UPDATE game.status SET current_user_id = %s WHERE game_id = %s """, (user_ids[0], game_id)) conn.close()
#!/usr/bin/python import db import re import whm from collections import defaultdict import sys fs = whm.all_ordered_map_files() last_shape_colors = defaultdict(lambda: ("","")) # name -> (shape, color) tuple conn, c = db.get_conn_cursor() for f in fs: #if whm.filename_to_year(f) != 1549: continue svg = whm.SvgFile(f) c.execute("delete from shapes where year=?", (svg.year(),)) this_year = defaultdict(lambda: ("", "")) present = {} for name in svg.countries(): info = svg.infos_for_country(name) if not info: continue info = info[0] present[name] = True path = svg.path_for_id(info['id']) if not path: continue # might be an ellipse