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
