From 16ba2a3e926105b2dd732e8cb28eddcdda1d1fc2 Mon Sep 17 00:00:00 2001
From: schneefux <schneefux+commit@schneefux.xyz>
Date: Fri, 27 Jan 2017 13:12:35 +0100
Subject: api: move db queries to sql files

---
 api.py                     |  4 +++-
 queries.py                 | 56 +++++++++++++---------------------------------
 queries/index.sql          |  4 ++++
 queries/recent-matches.sql | 30 +++++++++++++++++++++++++
 4 files changed, 53 insertions(+), 41 deletions(-)
 create mode 100644 queries/index.sql
 create mode 100644 queries/recent-matches.sql

diff --git a/api.py b/api.py
index 326070d..bbd173a 100644
--- a/api.py
+++ b/api.py
@@ -24,6 +24,7 @@ async def recrawl():
     api = crawler.Crawler()
 
     # TODO: insert API version (force update if changed)
+    # TODO: create database indices
     # get or put when the last crawl was executed
     try:
         last_match_update = await db.meta("last_match_update")
@@ -50,7 +51,7 @@ async def recrawl_soon():
 
 @route("/matches")
 async def api_matches(request):
-    data = (await db.select(queries.matches))[0]["data"]
+    data = (await db.select(queries.queries["recent-matches"]))[0]["data"]
     return aiohttp.web.Response(text=str(data))
 
 @route("/status")
@@ -61,6 +62,7 @@ async def api_status(_):
 
 loop = asyncio.get_event_loop()
 loop.run_until_complete(db.connect("postgres://vgstats@localhost/vgstats"))
+loop.run_until_complete(queries.load_queries("queries/"))
 loop.create_task(recrawl())
 app = aiohttp.web.Application(loop=loop)
 route.add_to_router(app.router)
diff --git a/queries.py b/queries.py
index 52d03ad..de4895e 100644
--- a/queries.py
+++ b/queries.py
@@ -1,44 +1,20 @@
 #!/usr/bin/python
+import asyncio
+import glob
+import os
+import asyncpg
 
-# TODO: read this from sql files
-# TODO: index
+queries = dict()
 
-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'));
-"""
+async def load_queries(path):
+    """Prepares a folder of SQL files as SQL statements.
 
-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
-"""
+    :param path: Path to the `.sql` files.
+    :type path: str
+    """
+    # load from queries/
+    queryfiles = glob.glob(path + "/*.sql")
+    for fp in queryfiles:
+        with open(fp, "r") as qfile:
+            name = os.path.splitext(os.path.basename(fp))[0]
+            queries[name] = qfile.read()
diff --git a/queries/index.sql b/queries/index.sql
new file mode 100644
index 0000000..5af21b5
--- /dev/null
+++ b/queries/index.sql
@@ -0,0 +1,4 @@
+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'));
diff --git a/queries/recent-matches.sql b/queries/recent-matches.sql
new file mode 100644
index 0000000..fb237d7
--- /dev/null
+++ b/queries/recent-matches.sql
@@ -0,0 +1,30 @@
+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