support split queries

2 files changed, 10 insertions, 16 deletions

diff --git a/api.py b/api.py
index f668b74..ce186d4 100644
--- a/api.py
+++ b/api.py
@@ -44,12 +44,15 @@ class Worker(object):
         """Initialize the database."""
         scriptroot = os.path.realpath(
             os.path.join(os.getcwd(), os.path.dirname(__file__)))
-        for path in glob.glob(scriptroot + "/queries/*.sql"):
+        for path in glob.glob(scriptroot + "/queries/*/*.sql"):
             # utf-8-sig is used by pgadmin, doesn't hurt to specify
-            # file names: web target table
-            table = os.path.splitext(os.path.basename(path))[0]
+            # directory names: web target table
+            table = os.path.basename(os.path.dirname(path))
             with open(path, "r", encoding="utf-8-sig") as file:
-                self._queries[table] = file.read()
+                try:
+                    self._queries[table].append(file.read())
+                except KeyError:
+                    self._queries[table] = [file.read()]
                 logging.info("loaded query '%s'", table)
 
     async def _execute_job(self, jobid, payload):
@@ -61,9 +64,9 @@ class Worker(object):
         async with self._pool.acquire() as con:
             logging.debug("%s: compiling '%s' from '%s'",
                           jobid, object_id, table)
-            async with con.transaction():
-                await con.execute(self._queries[table],
-                                  object_id)
+            for query in self._queries[table]:
+                async with con.transaction():
+                    await con.execute(query, object_id)
 
     async def _work(self):
         """Fetch a job and run it."""
diff --git a/queries/player.sql b/queries/player.sql
deleted file mode 100644
index e847de2..0000000
--- a/queries/player.sql
+++ /dev/null
@@ -1,9 +0,0 @@
--- count the number of matches we have from that player in the database
--- UPDATE player SET "games_in_db"=
-SELECT
-COUNT(DISTINCT match."apiId") AS "games_in_db"
-FROM player
-JOIN participant ON player."apiId" = participant."player_apiId"
-JOIN roster ON participant."roster_apiId" = roster."apiId"
-JOIN match ON roster."match_apiId" = match."apiId"
-WHERE player.id=$1