diff options
| -rw-r--r-- | api.py | 63 | ||||
| -rw-r--r-- | crawler.py | 7 | ||||
| -rw-r--r-- | queries.py | 44 |
3 files changed, 102 insertions, 12 deletions
@@ -1,28 +1,67 @@ #!/usr/bin/python +import datetime import asyncio +import socket +import sys +import json +import aiohttp.web +import aiohttp_route_decorator +import aiohttp_wsgi + import database import crawler -import datetime +import queries + +route = aiohttp_route_decorator.RouteCollector() +db = database.Database() -async def main(): - db = database.Database() - await db.connect("postgres://vgstats@localhost/vgstats") + +async def recrawl(): + """Gets the latest matches and inserts them into the database.""" + print("getting recent matches") api = crawler.Crawler() + + # TODO: insert API version (force update if changed) + # get or put when the last crawl was executed try: last_match_update = await db.meta("last_match_update") except KeyError: last_match_update = datetime.datetime(1, 1, 1).isoformat() await db.meta("last_match_update", last_match_update) - print("getting new matches since " + last_match_update) - await db.meta("last_match_update", datetime.datetime.now().isoformat()) + nowiso = datetime.datetime.now().isoformat() + + # crawl and upsert matches = await api.matches_since(last_match_update) - print("inserting data into database") - await db.upsert("na", matches, True) - print("calculating stats") - print(await db.select("SELECT data->'attributes'->>'name' as name FROM player WHERE CAST(data->'attributes'->'stats'->>'winStreak' AS integer) > 3")) + if len(matches) > 0: + print("got a lot new data items: " + str(len(matches))) + await db.upsert(matches, True) + await db.meta("last_match_update", nowiso) + + asyncio.ensure_future(recrawl_soon()) + +async def recrawl_soon(): + """Calls `recrawl` after 60 seconds.""" + print("crawler sleeping, Zzzzzz…") + await asyncio.sleep(60) + asyncio.ensure_future(recrawl()) + + +@route("/matches") +async def api_matches(request): + data = (await db.select(queries.matches))[0]["data"] + return aiohttp.web.Response(text=str(data)) + +@route("/status") +async def api_status(_): + resp = json.dumps({"version": "0.1.0"}) + return aiohttp.web.Response(text=resp) + loop = asyncio.get_event_loop() -loop.run_until_complete(main()) -loop.close() +loop.run_until_complete(db.connect("postgres://vgstats@localhost/vgstats")) +loop.create_task(recrawl()) +app = aiohttp.web.Application(loop=loop) +route.add_to_router(app.router) +aiohttp.web.run_app(app) @@ -37,6 +37,13 @@ class Crawler(object): assert response.status == 200 return await response.json() + async def version(self): + """Returns the current API version.""" + + async with aiohttp.ClientSession() as session: + status = await self._req(session, "status") + return status["data"]["attributes"]["version"] + async def matches(self, region="na", params=None): """Queries the API for matches and their related data. diff --git a/queries.py b/queries.py new file mode 100644 index 0000000..52d03ad --- /dev/null +++ b/queries.py @@ -0,0 +1,44 @@ +#!/usr/bin/python + +# TODO: read this from sql files +# TODO: index + +create_index = """ +CREATE INDEX ON match((data->>'id')); +CREATE INDEX ON roster((data->>'id')); +CREATE INDEX ON participant((data->>'id')); +CREATE INDEX ON match((data->'attributes'->>'createdAt')); +""" + +matches = """ +SELECT ARRAY_TO_JSON(ARRAY( +SELECT + JSONB_BUILD_OBJECT( + 'id', match.data->>'id', + 'date', match.data->'attributes'->>'createdAt', + 'duration', CAST( + match.data->'attributes'->>'duration' + AS INTEGER), + 'teams', ARRAY( + SELECT( + SELECT + JSONB_BUILD_OBJECT( + 'id', roster.data->>'id', + 'side', roster.data->'attributes'->'stats'->>'side', + 'kills', roster.data->'attributes'->'stats'->>'heroKills', + 'players', ARRAY( + SELECT( + SELECT + participant.data->'attributes'->>'actor' + FROM participant WHERE relparticipant->>'id' = participant.data->>'id') + FROM JSONB_ARRAY_ELEMENTS(roster.data->'relationships'->'participants'->'data') relparticipant + ) + ) + FROM roster WHERE relroster->>'id' = roster.data->>'id') + FROM JSONB_ARRAY_ELEMENTS(match.data->'relationships'->'rosters'->'data') relroster + ) +) +FROM match +ORDER BY match.data->'attributes'->>'createdAt' DESC +)) AS data +""" |