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

---
 queries.py | 56 ++++++++++++++++----------------------------------------
 1 file changed, 16 insertions(+), 40 deletions(-)

(limited to 'queries.py')

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()
-- 
cgit v1.3.1