def _rune(self, name): with get_session() as s: place = get_place_from_string(s, name) rune_verb = get_verb(s, "rune").id return self._valid_milestone().filter( Milestone.place_id == place.id, Milestone.verb_id == rune_verb).exists()
def _orb(self): with get_session() as s: verb_id = get_verb(s, "orb").id return self._valid_milestone().filter( Milestone.verb_id == verb_id ).exists()
def _win(self): with get_session() as s: ktyp_id = get_ktyp(s, "winning").id return type_coerce( and_(Game.ktyp_id != None, Game.ktyp_id == ktyp_id, Game.end <= self.end), Integer)
def __init__(self, **kwargs): with get_session() as s: self.number = kwargs["number"] self.species = get_species(s, kwargs["species"]) self.background = get_background(s, kwargs["background"]) self.start = kwargs["start"] self.end = kwargs["end"] # todo: clean up the retry removal g1 = aliased(Game) g2 = aliased(Game) possiblegames = self._valid_games(g1).add_columns( g1.player_id, g1.start, g1.end).filter( g1.gid.in_( self._valid_games(g2).filter( g2.player_id == g1.player_id).order_by( g2.start).limit(2))).join( latestmilestones, g1.gid == latestmilestones.c.gid).add_column( latestmilestones.c.xl).cte() pg2 = possiblegames.alias() self.gids = Query(possiblegames.c.gid).outerjoin( pg2, and_( pg2.c.player_id == possiblegames.c.player_id, possiblegames.c.start > pg2.c.start)).filter(pg2.c.gid == None)
def standingstable(): with get_session() as s: sp = '<div class="card"><table>' sp += '<tr class="head"><th></th><th>Player</th>' sp += ''.join(['<th>' + description(wk, True) +'</th>' for wk in csdc.weeks ]) sp +='<th>15 Rune Win</th><th>Zig:$</th><th>Zot @ XL20</th>' sp +='<th>Win <40k Turns</th><th>No Lair Win</th><th>Ascetic Rune</th>' sp += '<th>CSDC Score</th><th>Weekly Bonuses</th><th>Game High Score</th></tr>' place = 1 for p in csdc.overview().with_session(s).all(): acct = s.query(Account).filter_by(id = p.account_id).first(); sp += '<tr>' sp += '<td class="total">{}.</td>'.format(place) place += 1 sp += '<td class="name">{}{}</td>'.format( serverflag(acct.server.name) if acct else "", p.CsdcContestant.player.name) sp += ('<td class="pt">{}</td>' * len(csdc.weeks)).format( *[ _ifnone(getattr(p, "wk" + wk.number), "") for wk in csdc.weeks]) for c in ("fifteenrune", "zig", "lowxlzot", "sub40k", "nolairwin", "asceticrune"): sp += ('<td class="pt">{}</td>').format(_ifnone(getattr(p, c), "")) sp += '<td class="total">{}</td>'.format(p.grandtotal) sp += '<td class="pt">{}</td><td class="pt">{}</td>'.format(p.tiebreak, _ifnone(p.hiscore, "")) sp += '</tr>' sp += '</table></div>' return sp
def refresh(sources_file, sources_dir, socketio): t_i = time.time() source_data = sources.source_data(sources_file) sources.download_sources(sources_file, sources_dir) new_events = [] with orm.get_session() as sess: for src in os.scandir(sources_dir): if not src.is_file() and src.name in source_data: expected_files = [ sources.url_to_filename(x) for x in source_data[src.name] ] logging.debug('scanning {} files, expect [{}]'.format( src.name, ','.join(expected_files))) for file in os.scandir(src.path): if file.is_file() and file.name in expected_files: new_events += _refresh_from_file(file, src, sess) if len(new_events) > 0 and len( new_events ) < 100: # don't want to do huge sends over sockets TODO: make a config option socketio.emit('crawlevent', json.dumps([e.getDict() for e in new_events])) logging.info('Refreshed in {} seconds'.format(time.time() - t_i))
def create_user_database(): queries = { 'artist_create': ''' CREATE TABLE Artist ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NULL ); ''', 'song_create': ''' CREATE TABLE Song ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, id_artist INT NULL, title VARCHAR(100) NOT NULL, body TEXT NOT NULL, CONSTRAINT FK_artist FOREIGN KEY (id_artist) REFERENCES artist (id) ); ''' } engine = orm.connect_to_engine(get_user_database_file(), orm.SQLITE) session = orm.get_session(engine) session.execute(queries['artist_create']) session.execute(queries['song_create'])
def __init__(self, **kwargs): with get_session() as s: self.number = kwargs["number"] self.species = get_species(s, kwargs["species"]) self.background = get_background(s, kwargs["background"]) self.gods = [get_god(s, g) for g in kwargs["gods"]] self.start = kwargs["start"] self.end = kwargs["end"] self.tier1 = kwargs.get("bonus1", NoBonus) self.tier2 = kwargs.get("bonus2", NoBonus) g1 = aliased(Game) g2 = aliased(Game) possiblegames = self._valid_games(g1).add_columns( g1.player_id, g1.start, g1.end).filter( g1.gid.in_( self._valid_games(g2).filter( g2.player_id == g1.player_id).order_by( g2.start).limit(2))).join( latestmilestones, g1.gid == latestmilestones.c.gid).add_column( latestmilestones.c.xl).cte() pg2 = possiblegames.alias() self.gids = Query(possiblegames.c.gid).outerjoin( pg2, and_(pg2.c.player_id == possiblegames.c.player_id, possiblegames.c.start > pg2.c.start)).filter( or_(pg2.c.gid == None, and_(pg2.c.end != None, pg2.c.xl < 5)))
def get(self): t_i = time.time() offset = 0 limit = 1000 args = parser.parse_args() logger.debug(args) if args['offset']!=None: offset = args['offset'] if args['limit']!=None and args['limit'] < 1000: limit = args['limit'] # TODO: make max limit a config option with orm.get_session() as sess: q = sess.query(Event) if args['type']!=None: q = q.filter_by(type=args['type']) if args['src']!=None: q = q.filter_by(src_abbr=args['src']) if args['reverse']==None: # cant offset when reversed q = q.filter(Event.id >= offset) else: q = q.order_by(Event.id.desc()) q = q.limit(limit) result = default_ok_result.copy() qstring = str(q.statement.compile(dialect=sqlite.dialect(), compile_kwargs={"literal_binds": True})).replace('\n','') logger.debug("DB Query: {}".format(qstring)) es = q.all() logger.debug('queried db in {} seconds.'.format(time.time()-t_i)) result.update({'offset': offset, 'next_offset': (es[-1].id+1 if len(es)>0 else offset), 'results': [e.getDict() for e in es]}) return result
def _god(self, name): with get_session() as s: god_id = get_god(s, name).id rune_verb = get_verb(s, "rune").id return self._valid_milestone().filter( Milestone.god_id == god_id, Milestone.verb_id == rune_verb, Milestone.runes == 1).exists()
def refresh(sources_file: str, sources_dir: str, fetch: Optional[bool] = True): t_i = time.time() source_data = sources.source_data(sources_file) if fetch: sources.download_sources(sources_file, sources_dir) with orm.get_session() as sess: for src in os.scandir(sources_dir): if not src.is_file() and src.name in source_data: expected_files = [ sources.url_to_filename(x) for _, x in source_data[src.name].items() ] logging.debug('scanning {} files, expect [{}]'.format( src.name, ','.join(expected_files))) # it is important that this refresh first so we get begins # before ends! milestones = os.path.join( src.path, sources.url_to_filename( source_data[src.name]["milestones"])) _refresh_from_file(milestones, src, sess) logfile = os.path.join( src.path, sources.url_to_filename(source_data[src.name]["logfile"])) _refresh_from_file(logfile, src, sess) logging.info('Refreshed in {} seconds'.format(time.time() - t_i))
def _zig(self): with get_session() as s: zexit = get_verb(s, "zig.exit").id zplace = get_place_from_string(s, "Zig:27").id return self._valid_milestone().filter( Milestone.verb_id == zexit, Milestone.oplace_id == zplace).exists()
def refresh_static(sources_dir): t_i = time.time() with orm.get_session() as sess: for src in os.scandir(sources_dir): for file in os.scandir(src.path): if file.is_file() and not file.name.startswith('.'): _refresh_from_file(file, src, sess) logging.info('Refreshed static in {} seconds'.format(time.time() - t_i))
def _asceticrune(self): with get_session() as s: verb_id = get_verb(s, "rune").id return self._valid_milestone().filter( Milestone.verb_id == verb_id, Milestone.potionsused == 0, Milestone.scrollsused == 0).exists()
def _fifteenrune(self): with get_session() as s: ktyp_id = get_ktyp(s, "winning").id return type_coerce(and_( Game.ktyp_id != None, Game.ktyp_id == ktyp_id, Game.end <= self.end, self._rune(15)), Integer)
def _brend(self): with get_session() as s: verb_id = get_verb(s, "br.end").id multilevel_places = Query(Place.id).join(Branch).filter( Branch.multilevel) return self._valid_milestone().filter( Milestone.place_id.in_(multilevel_places), Milestone.verb_id == verb_id).exists()
def _uniq(self): with get_session() as s: verb_ids = [ get_verb(s, u).id for u in ["uniq", "uniq.ban", "uniq.pac", "uniq.slime"] ] return self._valid_milestone().filter( Milestone.verb_id.in_(verb_ids)).exists()
def _lowxlzot(self): with get_session() as s: verb_id = get_verb(s, "br.enter").id place = get_place_from_string(s, "Zot:1").id return self._valid_milestone().filter( Milestone.xl <= 20, Milestone.verb_id == verb_id, Milestone.place_id == place).exists()
def setup_database(): with get_session() as sess: if os.environ.get('SCOREBOARD_SKIP_DB_SETUP') == None: setup_species(sess) setup_backgrounds(sess) setup_gods(sess) setup_branches(sess) setup_ktyps(sess) setup_verbs(sess) setup_skills(sess)
def _sub40k(self): with get_session() as s: ktyp_id = get_ktyp(s, "winning").id return type_coerce(func.ifnull(and_( Game.ktyp_id == ktyp_id, Game.end <= self.end, ~self._valid_milestone().filter( Milestone.turn >= 40000).exists() ), 0), Integer)
def _god(self): with get_session() as s: worship_id = get_verb(s, "god.worship").id champ_id = get_verb(s, "god.maxpiety").id abandon_id = get_verb(s, "god.renounce").id god_ids = [g.id for g in self.gods] return and_( or_(* [_champion_god(self._valid_milestone(), g) for g in self.gods]), ~self._valid_milestone().filter( Milestone.verb_id == abandon_id).exists())
def _nolairwin(self): with get_session() as s: ktyp_id = get_ktyp(s, "winning").id brenter = get_verb(s, "br.enter").id lair = get_place_from_string(s, "Lair:1").id return type_coerce(and_(Game.ktyp_id != None, Game.ktyp_id == ktyp_id, Game.end <= self.end, ~self._valid_milestone().filter( Milestone.verb_id == brenter, Milestone.place_id == lair).exists()), Integer)
def scoretable(wk, div): sp = "" sp += ("""<div class="card"><table><tr class="head"> <th>Player</th> <th>Unique Kill</th> <th>Branch Enter</th> <th>Branch End</th> <th>Champion God</th> <th>Collect a Rune</th> <th>Collect 3 Runes</th> <th>Obtain the Orb</th> <th>Win</th> <th>Bonus I</th> <th>Bonus II</th> <th>Total</th> </tr>""") with get_session() as s: for g in wk.sortedscorecard().with_session(s).all(): if g.Game == None: sp += """<tr class="{}"><td class="name">{}</td> <td colspan="9"></td><td class="total">0</td></tr>""".format( "none", g.Player.name) continue sp += ('<tr class="{}">'.format( "won" if g.Game.won and g.Game.end.replace(tzinfo=datetime.timezone.utc) <= wk.end else "alive" if g.Game.alive else "dead")) namestr = '<td class="name">{flag}<a href="{url}">{name}</a></td>' if not g.Game.alive else '<td class="name">{flag}{name}</td>' sp += (namestr.format( url = morgue_url(g.Game), name = g.Game.player.name, flag = serverflag(g.Game.account.server.name))) sp += ( (('<td class="pt">{}</td>' * 10) + '<td class="total">{}</td>').format( g.uniq, g.brenter, g.brend, g.god, g.rune, g.threerune, g.orb, g.win, g.bonusone, g.bonustwo, g.total)) sp += ('</tr>\n') sp += '</table></div>' return sp
def scoretable(wk, div): sp = "" sp += ("""<table><tr class="head"> <th>Player</th> <th>Reach XL10</th> <th>Win</th> <th>Runes</th><th>Gods</th><th>Speed</th><th>Turns</th> <th>Total</th> </tr>""") with get_session() as s: for g in wk.scorecard().with_session(s).all(): if g.Game == None: sp += """<tr class="{}"><td class="name">{}</td> <td colspan="9"></td><td class="total">0</td></tr>""".format( "none", g.Player.name) continue bonus_gods = ["lucy", "chei", "qaz", "jiyva"] god_total = 0 bonus_runes = [ "slimy", "silver", "iron", "bone", "obsidian", "icy", "pan" ] rune_total = 0 for i in range(len(bonus_gods)): god_total += int(_ifnone(getattr(g, bonus_gods[i]), "0")) for i in range(len(bonus_runes)): rune_total += int(_ifnone(getattr(g, bonus_runes[i]), "0")) sp += ('<tr class="{}">'.format( "won" if g.Game.won and g.Game.end <= wk.end else "alive" if g. Game.alive else "dead")) sp += ('<td class="name"><a href="{}">{}</a></td>'.format( morgue_url(g.Game), g.Game.player.name)) sp += ((('<td class="pt">{}</td>' * 6) + '<td class="total">{}</td>').format( g.xl, g.win, str(rune_total), str(god_total), g.time, g.turns, g.total)) sp += ('</tr>\n') sp += '</table>' return sp
def initialize_weeks(): with get_session() as s: weeks.append( CsdcWeek(number="1", species="Sk", background="AK", gods=(), start=datetime.datetime(2019, 12, 20), end=datetime.datetime(2019, 12, 29))) weeks.append( CsdcWeek(number="2", species="On", background="VM", gods=(), start=datetime.datetime(2019, 12, 23), end=datetime.datetime(2020, 1, 1))) weeks.append( CsdcWeek(number="3", species="VS", background="Rg", gods=(), start=datetime.datetime(2019, 12, 26), end=datetime.datetime(2020, 1, 4))) weeks.append( CsdcWeek(number="4", species="Dj", background="Cj", gods=(), start=datetime.datetime(2019, 12, 29), end=datetime.datetime(2020, 1, 7))) weeks.append( CsdcWeek(number="5", species="SD", background="SA", gods=(), start=datetime.datetime(2020, 1, 1), end=datetime.datetime(2020, 1, 10))) weeks.append( CsdcWeek(number="6", species="Hu", background="Wz", gods=(), start=datetime.datetime(2020, 1, 4), end=datetime.datetime(2020, 1, 13)))
def _champion_god(milestones, god): """Query if the supplied god get championed in the provided milestone set""" with get_session() as s: worship_id = get_verb(s, "god.worship").id champ_id = get_verb(s, "god.maxpiety").id maxpiety = milestones.filter(Milestone.god_id == god.id, Milestone.verb_id == champ_id).exists() worship = milestones.filter(Milestone.god_id == god.id, Milestone.verb_id == worship_id).exists() neverworship = ~milestones.filter(Milestone.verb_id == worship_id).exists() champion_conditions = { "GOD_NO_GOD": neverworship, "Xom": worship, "Gozag": worship } return champion_conditions.get(god.name, maxpiety)
def download_morgues(week, morguedir) -> None: dest = os.path.join(morguedir, week.number) if not os.path.exists(dest): os.mkdir(dest) urls = [] with orm.get_session() as s: for g in week.sortedscorecard().with_session(s).all(): if g.Game is not None and g.Game.ktyp is not None: urls.append(morgue_url(g.Game)) p = multiprocessing.Pool(SIMULTANEOUS_DOWNLOADS) jobs = [] for url in urls: jobs.append(p.apply_async(download_morgue_file, (url, dest))) for job in jobs: job.get() p.close() p.join()
def standingstable(): with get_session() as s: sp = "<table>" sp += '<tr class="head"><th>Player</th>' sp += ''.join( ['<th>' + description(wk, True) + '</th>' for wk in csdc.weeks]) sp += '<th>15 Rune Win</th><th>Win <50k Turns</th><th>Zig:$</th>' sp += '<th>Zot @ XL20</th><th>No Lair Win</th><th>Ascetic Rune</th>' sp += '<th>Score</th></tr>' for p in csdc.overview().with_session(s).all(): sp += '<tr>' sp += '<td class="name">{}</td>'.format( p.CsdcContestant.player.name) sp += ('<td class="pt">{}</td>' * len(csdc.weeks)).format(*[ _ifnone(getattr(p, "wk" + wk.number), "") for wk in csdc.weeks ]) sp += '<td class="pt"></td>' * 6 sp += '<td class="total">{}</td>'.format(p.grandtotal) sp += '</tr>' return sp
async def main(): argparser = argparse.ArgumentParser() argparser.add_argument( 'db_path', help='Path to sqlite database for writing data into' ) subparsers = argparser.add_subparsers(dest='action') scrape_parser = subparsers.add_parser( 'scrape-cases', help='Scrape case info for all judges ' ) scrape_parser.add_argument( 'from_date', help='Date to start looking for judgements from, in format dd/mm/yyyy' ) scrape_parser.add_argument( 'to_date', help='Date to look for judgements till, in format dd/mm/yyyy' ) populate_contents_parser = subparsers.add_parser( 'populate-contents', help='Populate text content for all judgements missing text content' ) args = argparser.parse_args() logging.basicConfig(format='%(asctime)s %(message)s', level=logging.INFO) db_session = orm.get_session(args.db_path) action = args.action if action == 'scrape-cases': await scrape_cases(db_session, args.from_date, args.to_date) elif action == 'populate-contents': await populate_contents(db_session)
def scoretable(wk, div): sp = "" sp += ("""<table><tr class="head"> <th>Player</th> <th>Unique Kill</th> <th>Branch Enter</th> <th>Branch End</th> <th>Champion God</th> <th>Collect a Rune</th> <th>Collect 3 Runes</th> <th>Win</th> <th>Bonus I</th> <th>Bonus II</th> <th>Total</th> </tr>""") with get_session() as s: for g in wk.scorecard().with_session(s).all(): if g.Game == None: sp += """<tr class="{}"><td class="name">{}</td> <td colspan="9"></td><td class="total">0</td></tr>""".format( "none", g.Player.name) continue sp += ('<tr class="{}">'.format( "won" if g.Game.won and g.Game.end <= wk.end else "alive" if g. Game.alive else "dead")) sp += ('<td class="name"><a href="{}">{}</a></td>'.format( morgue_url(g.Game), g.Game.player.name)) sp += ((('<td class="pt">{}</td>' * 9) + '<td class="total">{}</td>').format( g.uniq, g.brenter, g.brend, g.god, g.rune, g.threerune, g.win, g.bonusone, g.bonustwo, g.total)) sp += ('</tr>\n') sp += '</table>' return sp