From 0c7abdcf1887285a5dccf0073629521a91cc0276 Mon Sep 17 00:00:00 2001
From: schneefux <schneefux+commit@schneefux.xyz>
Date: Thu, 26 Jan 2017 23:50:22 +0100
Subject: api: demo /matches endpoint

---
 api.py     | 63 ++++++++++++++++++++++++++++++++++++++++++++++++++------------
 crawler.py |  7 +++++++
 queries.py | 44 +++++++++++++++++++++++++++++++++++++++++++
 3 files changed, 102 insertions(+), 12 deletions(-)
 create mode 100644 queries.py

diff --git a/api.py b/api.py
index f5e1380..326070d 100644
--- a/api.py
+++ b/api.py
@@ -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)
diff --git a/crawler.py b/crawler.py
index 07ca22f..68b994f 100644
--- a/crawler.py
+++ b/crawler.py
@@ -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
+"""
-- 
cgit v1.3.1