def run() -> None: run_elo = False # pylint: disable=consider-using-enumerate for i in range(0, len(USERNAME_COLUMNS)): # pylint: disable=consider-using-enumerate for j in range(i + 1, len(USERNAME_COLUMNS)): sql = """ SELECT p1.id AS p1_id, p2.id AS p2_id, '{col1}' AS col1, '{col2}' AS col2 FROM person AS p1 LEFT JOIN person AS p2 ON p1.{col1} = p2.{col2} AND p1.id <> p2.id WHERE p1.id IS NOT NULL AND p2.id IS NOT NULL """.format(col1=USERNAME_COLUMNS[i], col2=USERNAME_COLUMNS[j]) pairs = [Container(row) for row in db().select(sql)] if len(pairs) > 0: run_elo = True for pair in pairs: person.squash(pair.p1_id, pair.p2_id, pair.col1, pair.col2) if run_elo: logger.warning('Running maintenance task to correct all Elo ratings.') elo.run()
def apply_rules_to_decks(decks: List[Deck]) -> None: if len(decks) == 0: return decks_by_id = {} for d in decks: decks_by_id[d.id] = d id_list = ', '.join(str(d.id) for d in decks) sql = """ SELECT deck_id, archetype_name FROM ({apply_rules_query}) AS applied_rules GROUP BY deck_id HAVING COUNT(DISTINCT archetype_id) = 1 """.format( apply_rules_query=apply_rules_query(f'deck_id IN ({id_list})')) for r in (Container(row) for row in db().select(sql)): decks_by_id[r.deck_id].rule_archetype_name = r.archetype_name
def prime_cache(d: Deck) -> None: set_colors(d) colors_s = json.dumps(d.colors) colored_symbols_s = json.dumps(d.colored_symbols) set_legality(d) legal_formats_s = json.dumps(list(d.legal_formats)) normalized_name = deck_name.normalize(d) db().begin() db().execute('DELETE FROM deck_cache WHERE deck_id = %s', [d.id]) sql = """ INSERT INTO deck_cache (deck_id, normalized_name, colors, colored_symbols, legal_formats, wins, draws, losses, active_date) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) """ db().execute(sql, [ d.id, normalized_name, colors_s, colored_symbols_s, legal_formats_s, 0, 0, 0, dtutil.dt2ts(d.created_date) ]) db().commit() # If it was worth priming the in-db cache it's worth busting the in-memory cache to pick up the changes. redis.clear(f'decksite:deck:{d.id}')
def load_people(where: str = '1 = 1', order_by: str = '`all_num_decks` DESC, name', season_id: Optional[int] = None) -> Sequence[Person]: sql = """ SELECT p.id, {person_query} AS name, p.mtgo_username, p.tappedout_username, p.mtggoldfish_username, p.discord_id, p.elo, {all_select}, SUM(DISTINCT CASE WHEN d.competition_id IS NOT NULL AND {season_query} THEN 1 ELSE 0 END) AS `all_num_competitions` FROM person AS p LEFT JOIN deck AS d ON p.id = d.person_id {season_join} {nwdl_join} WHERE ({where}) GROUP BY p.id ORDER BY {order_by} """.format(person_query=query.person_query(), all_select=deck.nwdl_select('all_', query.season_query(season_id)), nwdl_join=deck.nwdl_join(), season_join=query.season_join(), where=where, season_query=query.season_query(season_id), order_by=order_by) people = [Person(r) for r in db().execute(sql)] if len(people) > 0: set_decks(people, season_id) set_achievements(people, season_id) set_head_to_head(people, season_id) return people
def load_opponent_stats(decks): if len(decks) == 0: return decks_by_id = {d.id: d for d in decks} sql = """ SELECT d.id, SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) AS opp_wins, SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS opp_losses, SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS opp_draws, ROUND(SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF((SUM(CASE WHEN dm.games <> odm.games THEN 1 ELSE 0 END)), 0), 2) * 100 AS omw, IFNULL(MIN(CASE WHEN m.elimination > 0 THEN m.elimination END), 0) AS stage_reached, GROUP_CONCAT(m.elimination) AS elim FROM deck AS d INNER JOIN deck_match AS my_dm ON my_dm.deck_id = d.id LEFT JOIN deck_match AS my_odm ON my_odm.match_id = my_dm.match_id AND my_odm.deck_id <> d.id INNER JOIN deck AS od ON od.id = my_odm.deck_id INNER JOIN deck_match AS dm ON dm.deck_id = od.id LEFT JOIN deck_match AS odm ON odm.match_id = dm.match_id AND odm.deck_id <> dm.deck_id INNER JOIN `match` AS m ON m.id = dm.match_id WHERE d.id IN ({deck_ids}) GROUP BY d.id """.format( deck_ids=', '.join(map(sqlescape, map(str, decks_by_id.keys())))) for row in db().execute(sql): decks_by_id[row['id']].opp_wins = row['opp_wins'] decks_by_id[row['id']].opp_losses = row['opp_losses'] decks_by_id[row['id']].omw = row['omw'] decks_by_id[row['id']].stage_reached = row['stage_reached'] decks_by_id[row['id']].elim = row[ 'elim'] # This property is never used? and is always a bunch of zeroes?
def load_person_statless(where: str = 'TRUE', season_id: Optional[int] = None) -> Person: person_query = query.person_query() sql = f""" SELECT p.id, {person_query} AS name, p.mtgo_username, p.tappedout_username, p.mtggoldfish_username, p.discord_id, p.elo, p.locale FROM person AS p WHERE {where} """ people = [Person(r) for r in db().select(sql)] for p in people: p.season_id = season_id return guarantee.exactly_one(people)
def load_cards(person_id: Optional[int] = None, season_id: Optional[int] = None, tournament_only: bool = False) -> List[Card]: if person_id: table = '_card_person_stats' where = 'person_id = {person_id}'.format(person_id=sqlescape(person_id)) group_by = 'person_id, name' else: table = '_card_stats' where = 'TRUE' group_by = 'name' if tournament_only: where = f"({where}) AND deck_type = 'tournament'" sql = """ SELECT name, SUM(num_decks) AS num_decks, SUM(wins) AS wins, SUM(losses) AS losses, SUM(draws) AS draws, SUM(wins - losses) AS record, SUM(perfect_runs) AS perfect_runs, SUM(tournament_wins) AS tournament_wins, SUM(tournament_top8s) AS tournament_top8s, IFNULL(ROUND((SUM(wins) / NULLIF(SUM(wins + losses), 0)) * 100, 1), '') AS win_percent FROM {table} AS cs WHERE ({where}) AND ({season_query}) GROUP BY {group_by} ORDER BY num_decks DESC, record, name """.format(table=table, season_query=query.season_query(season_id), where=where, group_by=group_by) cs = [Container(r) for r in db().select(sql)] cards = oracle.cards_by_name() for c in cs: c.update(cards[c.name]) return cs
def load_matchups(archetype_id): sql = """ SELECT oa.id, oa.name, SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS all_wins, -- IFNULL so we still count byes as wins. SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS all_losses, SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS all_draws, IFNULL(ROUND((SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN dm.games <> IFNULL(odm.games, 0) THEN 1 ELSE 0 END), 0)) * 100, 1), '') AS all_win_percent, SUM(CASE WHEN d.created_date > %s AND dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS season_wins, -- IFNULL so we still count byes as wins. SUM(CASE WHEN d.created_date > %s AND dm.games < odm.games THEN 1 ELSE 0 END) AS season_losses, SUM(CASE WHEN d.created_date > %s AND dm.games = odm.games THEN 1 ELSE 0 END) AS season_draws, IFNULL(ROUND((SUM(CASE WHEN d.created_date > %s AND dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN d.created_date > %s AND dm.games <> IFNULL(odm.games, 0) THEN 1 ELSE 0 END), 0)) * 100, 1), '') AS season_win_percent FROM archetype AS a INNER JOIN deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id) INNER JOIN deck_match AS dm ON d.id = dm.deck_id INNER JOIN deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id INNER JOIN deck AS od ON od.id = odm.deck_id INNER JOIN archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.id) WHERE a.id = %s GROUP BY oa.id ORDER BY `season_wins` DESC, `all_wins` DESC """.format(all_select=deck.nwdl_all_select(), season_select=deck.nwdl_season_select(), nwdl_join=deck.nwdl_join()) return [ Container(m) for m in db().execute(sql, [int(rotation.last_rotation().timestamp())] * 5 + [archetype_id]) ]
def do_validation(self): if len(self.mtgo_username) == 0: self.errors['mtgo_username'] = "******" elif len(self.mtgo_username) > card.MAX_LEN_VARCHAR: self.errors['mtgo_username'] = "******".format(n=card.MAX_LEN_VARCHAR) elif active_decks_by(self.mtgo_username): self.errors['mtgo_username'] = "******" if len(self.name) == 0: self.errors['name'] = 'Deck Name is required' elif len(self.name) > card.MAX_LEN_TEXT: self.errors['name'] = 'Deck Name is too long (max {n})'.format(n=card.MAX_LEN_TEXT) else: self.source = 'League' self.competition_id = db().value(active_competition_id_query()) self.identifier = identifier(self) self.url = url_for('competitions', competition_id=self.competition_id) self.decklist = self.decklist.strip() if len(self.decklist) == 0: self.errors['decklist'] = 'Decklist is required' else: self.cards = None if self.decklist.startswith('<?xml'): try: self.cards = decklist.parse_xml(self.decklist) except InvalidDataException as e: self.errors['decklist'] = 'Unable to read .dek decklist. Try exporting from Magic Online as Text and pasting the result.'.format(specific=str(e)) else: try: self.cards = decklist.parse(self.decklist) except InvalidDataException as e: self.errors['decklist'] = '{specific}. Try exporting from Magic Online as Text and pasting the result.'.format(specific=str(e)) if self.cards is not None: try: vivified = decklist.vivify(self.cards) errors = {} if 'Penny Dreadful' not in legality.legal_formats(vivified, None, errors): self.errors['decklist'] = 'Deck is not legal in Penny Dreadful - {error}'.format(error=errors.get('Penny Dreadful')) except InvalidDataException as e: self.errors['decklist'] = str(e)
def load_archetypes_deckless( where='1 = 1', order_by='`season.num_decks` DESC, `all.num_decks` DESC, `season.wins` DESC, `all.wins` DESC' ): sql = """ SELECT a.id, a.name, aca.ancestor AS parent_id, COUNT(DISTINCT d.id) AS `all.num_decks`, SUM(d.wins) AS `all.wins`, SUM(d.losses) AS `all.losses`, SUM(d.draws) AS `all.draws`, IFNULL(ROUND((SUM(wins) / SUM(wins + losses)) * 100, 1), '') AS `all.win_percent`, SUM(CASE WHEN d.created_date >= %s THEN 1 ELSE 0 END) AS `season.num_decks`, SUM(CASE WHEN d.created_date >= %s THEN wins ELSE 0 END) AS `season.wins`, SUM(CASE WHEN d.created_date >= %s THEN losses ELSE 0 END) AS `season.losses`, SUM(CASE WHEN d.created_date >= %s THEN draws ELSE 0 END) AS `season.draws`, IFNULL(ROUND((SUM(CASE WHEN d.created_date >= %s THEN wins ELSE 0 END) / SUM(CASE WHEN d.created_date >= %s THEN wins ELSE 0 END + CASE WHEN d.created_date >= %s THEN losses ELSE 0 END)) * 100, 1), '') AS `season.win_percent` FROM archetype AS a LEFT JOIN archetype_closure AS aca ON a.id = aca.descendant AND aca.depth = 1 LEFT JOIN archetype_closure AS acd ON a.id = acd.ancestor LEFT JOIN deck AS d ON acd.descendant = d.archetype_id WHERE {where} GROUP BY a.id ORDER BY {order_by} """.format(where=where, order_by=order_by) archetypes = [ Archetype(a) for a in db().execute(sql, [rotation.last_rotation().timestamp()] * 7) ] archetypes_by_id = {a.id: a for a in archetypes} for a in archetypes: a.decks = [] a.parent = archetypes_by_id.get(a.parent_id, None) return archetypes
def load_archetypes_deckless( where: str = '1 = 1', order_by: str = '`all_num_decks` DESC, `all_wins` DESC, name', season_id: int = None) -> List[Archetype]: sql = """ SELECT a.id, a.name, aca.ancestor AS parent_id, {all_select} FROM archetype AS a LEFT JOIN archetype_closure AS aca ON a.id = aca.descendant AND aca.depth = 1 LEFT JOIN archetype_closure AS acd ON a.id = acd.ancestor LEFT JOIN deck AS d ON acd.descendant = d.archetype_id {season_join} {nwdl_join} WHERE ({where}) AND ({season_query}) GROUP BY a.id, aca.ancestor -- aca.ancestor will be unique per a.id because of integrity constraints enforced elsewhere (each archetype has one ancestor) but we let the database know here. ORDER BY {order_by} """.format(all_select=deck.nwdl_all_select(), season_join=query.season_join(), nwdl_join=deck.nwdl_join(), where=where, season_query=query.season_query(season_id), order_by=order_by) archetypes = [Archetype(a) for a in db().execute(sql)] archetypes_by_id = {a.id: a for a in archetypes} for a in archetypes: a.decks = [] a.parent = archetypes_by_id.get(a.parent_id, None) return archetypes
def season_stats() -> Dict[int, Dict[str, Union[int, datetime.datetime]]]: sql = """ SELECT season_id, start_date, end_date, DATEDIFF(IFNULL(FROM_UNIXTIME(end_date), NOW()), FROM_UNIXTIME(start_date)) AS length_in_days, num_decks, num_league_decks, num_people, num_competitions, num_archetypes, num_matches, num_cards FROM _season_stats """ stats = {r['season_id']: r for r in db().select(sql)} for season in stats.values(): season['start_date'] = dtutil.ts2dt(season['start_date']) season['end_date'] = dtutil.ts2dt( season['end_date']) if season['end_date'] else None return stats
def load_notes(): sql = """ SELECT pn.created_date, pn.creator_id, {creator_query} AS creator, pn.subject_id, {subject_query} AS subject, note FROM person_note AS pn INNER JOIN person AS c ON pn.creator_id = c.id INNER JOIN person AS s ON pn.subject_id = s.id ORDER BY s.id, pn.created_date DESC """.format(creator_query=query.person_query('c'), subject_query=query.person_query('s')) notes = [Container(r) for r in db().execute(sql)] for n in notes: n.created_date = dtutil.ts2dt(n.created_date) return notes
def get_matches(d: deck.Deck, should_load_decks: bool = False) -> List[Container]: sql = """ SELECT m.`date`, m.id, m.round, m.elimination, dm1.games AS game_wins, dm2.deck_id AS opponent_deck_id, IFNULL(dm2.games, 0) AS game_losses, d2.name AS opponent_deck_name, {person_query} AS opponent FROM `match` AS m INNER JOIN deck_match AS dm1 ON m.id = dm1.match_id AND dm1.deck_id = %s LEFT JOIN deck_match AS dm2 ON m.id = dm2.match_id AND dm2.deck_id <> %s INNER JOIN deck AS d1 ON dm1.deck_id = d1.id LEFT JOIN deck AS d2 ON dm2.deck_id = d2.id LEFT JOIN person AS p ON p.id = d2.person_id ORDER BY m.date, round """.format(person_query=query.person_query()) matches = [Container(m) for m in db().execute(sql, [d.id, d.id])] if should_load_decks: opponents = [ m.opponent_deck_id for m in matches if m.opponent_deck_id is not None ] if len(opponents) > 0: decks = deck.load_decks('d.id IN ({ids})'.format(ids=', '.join( [sqlescape(str(deck_id)) for deck_id in opponents]))) else: decks = [] decks_by_id = {d.id: d for d in decks} for m in matches: m.date = dtutil.ts2dt(m.date) if should_load_decks and m.opponent_deck_id is not None and decks_by_id.get( m.opponent_deck_id): m.opponent_deck = decks_by_id[m.opponent_deck_id] elif should_load_decks: m.opponent_deck = None return matches
def load_all_matchups(where: str = 'TRUE', season_id: Optional[int] = None, retry: bool = False) -> List[Container]: sql = """ SELECT archetype_id, a.name AS archetype_name, opponent_archetype_id AS id, oa.name AS name, SUM(wins) AS all_wins, SUM(losses) AS all_losses, SUM(draws) AS all_draws, IFNULL(ROUND((SUM(wins) / NULLIF(SUM(wins + losses), 0)) * 100, 1), '') AS all_win_percent FROM _matchup_stats AS ms INNER JOIN archetype AS a ON archetype_id = a.id INNER JOIN archetype AS oa ON opponent_archetype_id = oa.id LEFT JOIN ({season_table}) AS season ON season.start_date <= ms.day AND (season.end_date IS NULL OR season.end_date > ms.day) WHERE ({where}) AND ({season_query}) GROUP BY archetype_id, opponent_archetype_id ORDER BY all_wins DESC, oa.name """.format(season_table=query.season_table(), where=where, season_query=query.season_query(season_id)) try: return [Container(m) for m in db().select(sql)] except DatabaseException as e: if not retry: print(f"Got {e} trying to load_all_matchups so trying to preaggregate. If this is happening on user time that's undesirable.") preaggregate_matchups() return load_all_matchups(where=where, season_id=season_id, retry=True) print(f'Failed to preaggregate. Giving up.') raise e
def delete_match(match_id: int) -> None: db().begin('delete_match') rs = db().select( 'SELECT deck_id, games FROM deck_match WHERE match_id = %s', [match_id]) if not rs: raise TooFewItemsException( 'No deck_match entries found for match_id `{match_id}`') left_id = rs[0]['deck_id'] left_games = rs[0]['games'] if len(rs) > 1: right_id = rs[1]['deck_id'] right_games = rs[1]['games'] else: right_id, right_games = 0, 0 update_cache(left_id, left_games, right_games, delete=True) update_cache(right_id, right_games, left_games, delete=True) sql = 'DELETE FROM `match` WHERE id = %s' db().execute(sql, [match_id]) db().commit('delete_match') if rs: redis.clear(f'decksite:deck:{left_id}', f'decksite:deck:{right_id}')
def detail(self, p: Person, season_id: Optional[int] = None) -> Optional[List[Deck]]: if self.detail_sql is None: return None sql = """ SELECT GROUP_CONCAT({k}_detail) FROM _achievements WHERE person_id={id} AND {season_query} GROUP BY person_id """.format(k=self.key, id=p.id, season_query=query.season_query(season_id)) ids = db().value(sql) result = Container() result.decks = deck.load_decks( where=f'd.id IN ({ids})') if ids is not None else [] for f in self.flags: result[f] = True return result
def load_competitive_stats(decks: List[Deck]) -> None: decks_by_id = {d.id: d for d in decks if d.get('omw') is None} if len(decks_by_id) == 0: return if len(decks_by_id) < 1000: where = 'd.id IN ({deck_ids})'.format( deck_ids=', '.join(map(sqlescape, map(str, decks_by_id.keys())))) else: where = 'TRUE' # MySQL doesn't like to be asked to do IN queries for very long argument lists. Just load everything. (MariaDB doesn't care, interestingly.) sql = """ SELECT d.id, ROUND(SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF((SUM(CASE WHEN dm.games <> odm.games THEN 1 ELSE 0 END)), 0), 2) * 100 AS omw FROM deck AS d INNER JOIN deck_match AS my_dm ON my_dm.deck_id = d.id LEFT JOIN deck_match AS my_odm ON my_odm.match_id = my_dm.match_id AND my_odm.deck_id <> d.id INNER JOIN deck AS od ON od.id = my_odm.deck_id INNER JOIN deck_match AS dm ON dm.deck_id = od.id LEFT JOIN deck_match AS odm ON odm.match_id = dm.match_id AND odm.deck_id <> dm.deck_id INNER JOIN `match` AS m ON m.id = dm.match_id WHERE {where} GROUP BY d.id """.format(where=where) rs = db().select(sql) for row in rs: if decks_by_id.get(row['id']): decks_by_id[row['id']].omw = row['omw']
def overlooked_decks() -> List[Deck]: sql = """ SELECT deck.id as deck_id FROM deck LEFT JOIN _applied_rules ON deck.id = _applied_rules.deck_id WHERE _applied_rules.rule_id IS NULL AND deck.archetype_id IN ( SELECT DISTINCT archetype_id FROM rule ) """ deck_ids = [str(row['deck_id']) for row in db().select(sql)] if not deck_ids: return [] ids_list = ', '.join(deck_ids) return deck.load_decks(where=f'd.id IN ({ids_list})')
def set_achievements(people: List[Person], season_id: int = None) -> None: people_by_id = {person.id: person for person in people} sql = """ SELECT p.id, COUNT(DISTINCT CASE WHEN ct.name = 'Gatherling' THEN d.id ELSE NULL END) AS tournament_entries, COUNT(DISTINCT CASE WHEN d.finish = 1 AND ct.name = 'Gatherling' THEN d.id ELSE NULL END) AS tournament_wins, COUNT(DISTINCT CASE WHEN ct.name = 'League' THEN d.id ELSE NULL END) AS league_entries, CASE WHEN COUNT(CASE WHEN d.retired = 1 THEN 1 ELSE NULL END) = 0 THEN True ELSE False END AS completionist, SUM(CASE WHEN ct.name = 'League' AND dc.wins >= 5 AND dc.losses THEN 1 ELSE 0 END) AS perfect_runs, SUM( CASE WHEN d.id IN ( SELECT -- MAX here is just to fool MySQL to give us the id of the deck that crushed the perfect run from an aggregate function. There is only one value to MAX. MAX(CASE WHEN dm.games < odm.games AND dm.match_id IN (SELECT MAX(match_id) FROM deck_match WHERE deck_id = d.id) THEN odm.deck_id ELSE NULL END) AS deck_id FROM deck AS d INNER JOIN deck_match AS dm ON dm.deck_id = d.id INNER JOIN deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id WHERE d.competition_id IN ({competition_ids_by_type_select}) GROUP BY d.id HAVING SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) >=4 AND SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) = 1 AND SUM(CASE WHEN dm.games < odm.games AND dm.match_id IN (SELECT MAX(match_id) FROM deck_match WHERE deck_id = d.id) THEN 1 ELSE 0 END) = 1 ) THEN 1 ELSE 0 END ) AS perfect_run_crushes FROM person AS p LEFT JOIN deck AS d ON d.person_id = p.id LEFT JOIN deck_cache AS dc ON dc.deck_id = d.id {season_join} {competition_join} WHERE p.id IN ({ids}) AND ({season_query}) GROUP BY p.id """.format( competition_join=query.competition_join(), competition_ids_by_type_select=query.competition_ids_by_type_select( 'League'), ids=', '.join(str(k) for k in people_by_id.keys()), season_join=query.season_join(), season_query=query.season_query(season_id)) results = [Container(r) for r in db().select(sql)] for result in results: people_by_id[result['id']].num_achievements = len( [k for k, v in result.items() if k != 'id' and v > 0]) people_by_id[result['id']].achievements = result people_by_id[result['id']].achievements.pop('id')
def assign(deck_id, archetype_id): return db().execute( 'UPDATE deck SET reviewed = TRUE, archetype_id = ? WHERE id = ?', [archetype_id, deck_id])
def set_locale(person_id: int, locale: str) -> None: db().execute('UPDATE person SET locale = %s WHERE id = %s', [locale, person_id])
def squash(p1id: int, p2id: int, col1: str, col2: str) -> None: logger.warning('Squashing {p1id} and {p2id} on {col1} and {col2}'.format( p1id=p1id, p2id=p2id, col1=col1, col2=col2)) db().begin('squash') new_value = db().value( 'SELECT {col2} FROM person WHERE id = %s'.format(col2=col2), [p2id]) db().execute('UPDATE deck SET person_id = %s WHERE person_id = %s', [p1id, p2id]) db().execute('DELETE FROM person WHERE id = %s', [p2id]) db().execute( 'UPDATE person SET {col2} = %s WHERE id = %s'.format(col2=col2), [new_value, p1id]) db().commit('squash')
def is_banned(mtgo_username: str) -> bool: return db().value('SELECT banned FROM person WHERE mtgo_username = %s', [mtgo_username]) == 1
def remove_discord_link(discord_id: int) -> int: sql = 'UPDATE person SET discord_id = NULL WHERE discord_id = %s' return db().execute(sql, [discord_id])
def unlink_discord(person_id: int) -> int: sql = 'UPDATE person SET discord_id = NULL WHERE id = %s' return db().execute(sql, [person_id])
def add_note(creator_id: int, subject_id: int, note: str) -> None: sql = 'INSERT INTO person_note (created_date, creator_id, subject_id, note) VALUES (UNIX_TIMESTAMP(NOW()), %s, %s, %s)' db().execute(sql, [creator_id, subject_id, note])
def associate(d: deck.Deck, discord_id: int) -> int: person_id = db().value('SELECT person_id FROM deck WHERE id = %s', [d.id], fail_on_missing=True) sql = 'UPDATE person SET discord_id = %s WHERE id = %s' return db().execute(sql, [discord_id, person_id])
def associate(d, discord_id): person = guarantee.exactly_one( load_people('d.id = {deck_id}'.format(deck_id=sqlescape(d.id)))) sql = 'UPDATE person SET discord_id = %s WHERE id = %s' return db().execute(sql, [discord_id, person.id])
def percent(self, season_id: Optional[int] = None) -> float: # pylint: disable=unused-argument sql = 'SELECT COUNT(*) AS mnum FROM _achievements' r = db().select(sql)[0] return len(self.hosts) * 100.0 / int(r['mnum'])