store api data as one json per match

2 files changed, 77 insertions, 102 deletions

diff --git a/api.py b/api.py
index 117f55f..8559288 100644
--- a/api.py
+++ b/api.py
@@ -31,21 +31,8 @@ class Worker(object):
         async with self._pool.acquire() as con:
             await con.execute("""
                 CREATE TABLE IF NOT EXISTS
-                match (
-                    id TEXT PRIMARY KEY,
-                    type TEXT DEFAULT 'match',
-                    attributes JSONB,
-                    relations JSONB
-                )
-                """)
-            await con.execute("""
-                CREATE TABLE IF NOT EXISTS
-                player (
-                    id TEXT PRIMARY KEY,
-                    type TEXT DEFAULT 'player',
-                    attributes JSONB
-                )
-                """)
+                match (id TEXT PRIMARY KEY, data JSONB)
+            """)
 
         root = os.path.realpath(
             os.path.join(os.getcwd(), os.path.dirname(__file__)))
@@ -60,17 +47,13 @@ class Worker(object):
             async for data in api.matches(region=payload["region"],
                                           params=payload["params"]):
                 logging.debug("%s: inserting into database", jobid)
-                objects = await con.fetch(self._insertquery, json.dumps(data))
-                logging.info("%s: inserted %s", jobid,
-                             {t: len([s for s in objects if s["type"] == t])
-                              for t in set([e["type"] for e in objects])}
-                            )
-                for obj in objects:
+                matchids = await con.fetch(self._insertquery, json.dumps(data))
+                logging.info("%s: inserted %s matches", jobid, len(matchids))
+                for matchid in matchids:
                     await self._queue.request(jobtype="process",
                                               priority=priority,
                                               payload={
-                                                  "id": obj["id"],
-                                                  "type": obj["type"]
+                                                  "id": matchid["id"],
                                               })
 
     async def _work(self):
diff --git a/insert.sql b/insert.sql
index 9e95e0f..1212c8d 100644
--- a/insert.sql
+++ b/insert.sql
@@ -1,82 +1,74 @@
 WITH
- -- parse source string
- srcjson AS (
-   SELECT $1::JSONB
-   AS data
- ),
- -- split into data / included
- matches AS (
-   SELECT JSONB_ARRAY_ELEMENTS(srcjson.data->'data') AS data FROM srcjson
- ),
- includes AS (
-   SELECT JSONB_ARRAY_ELEMENTS(srcjson.data->'included') AS data FROM srcjson
- ),
- -- filter included by type
- rosters AS (
-   SELECT includes.data AS data FROM includes WHERE includes.data->>'type'='roster'
- ),
- participants AS (
-   SELECT includes.data AS data FROM includes WHERE includes.data->>'type'='participant'
- ),
- players AS (
-   SELECT includes.data AS data FROM includes WHERE includes.data->>'type'='player'
- ),
- -- link participant-player
- linked_participants AS (
-   SELECT
-     JSONB_BUILD_OBJECT(
-       'data', participants.data - 'relationships',
-       'relations', participants.data->'relationships'->'player'
-       -- stop: don't embed player object, it will be put into a seperate table
-       -- instead, only reference the player ids in a jsonb array
-     ) AS data
-     FROM participants
-  ),
+-- parse source string
+srcjson AS (
+  SELECT $1::JSONB AS data
+),
+-- split into data / included
+matches AS (
+  SELECT JSONB_ARRAY_ELEMENTS(srcjson.data->'data') AS data FROM srcjson
+),
+includes AS (
+  SELECT JSONB_ARRAY_ELEMENTS(srcjson.data->'included') AS data FROM srcjson
+),
+-- filter included by type
+rosters AS (
+  SELECT includes.data AS data FROM includes WHERE includes.data->>'type'='roster'
+),
+participants AS (
+  SELECT includes.data AS data FROM includes WHERE includes.data->>'type'='participant'
+),
+players AS (
+  SELECT includes.data AS data FROM includes WHERE includes.data->>'type'='player'
+),
+-- cleanup players
+linked_players AS (
+  SELECT
+    JSONB_BUILD_OBJECT(
+      'data', players.data-'relationships'
+    ) AS data
+  FROM players
+),
+-- link participant-player
+linked_participants AS (
+  SELECT
+    JSONB_BUILD_OBJECT(
+      'data', participants.data-'relationships',
+      'relations',  TO_JSONB(ARRAY(
+        SELECT * FROM linked_players
+        WHERE participants.data->'relationships'->'player'->'data' = JSONB_BUILD_OBJECT('id', linked_players.data->>'id', 'type', linked_players.data->>'type')
+      ))
+    ) AS data
+  FROM participants
+),
  -- link roster-participants
- linked_rosters AS (
-   SELECT
-     JSONB_BUILD_OBJECT(
-     'data', rosters.data - 'relationships',
-     'relations', TO_JSONB(ARRAY(
-       SELECT * FROM linked_participants
-       WHERE rosters.data->'relationships'->'participants'->'data' @> JSONB_BUILD_ARRAY(JSONB_BUILD_OBJECT('id', linked_participants.data->'data'->>'id', 'type', linked_participants.data->'data'->>'type'))
-     ))
-   ) AS data
-   FROM rosters
- ),
- -- link match-rosters
- linked_matches AS (
-   SELECT
-   matches.data->>'id' AS id,
-   matches.data->>'type' AS type,
-   matches.data->'attributes' AS attributes,
-   TO_JSONB(ARRAY(
-     SELECT * FROM linked_rosters
-     WHERE matches.data->'relationships'->'rosters'->'data' @> JSONB_BUILD_ARRAY(JSONB_BUILD_OBJECT('id', linked_rosters.data->'data'->>'id', 'type', linked_rosters.data->'data'->>'type'))
-   )) AS relations
+linked_rosters AS (
+  SELECT
+    JSONB_BUILD_OBJECT(
+      'data', rosters.data-'relationships',
+      'relations', TO_JSONB(ARRAY(
+        SELECT * FROM linked_participants
+        WHERE rosters.data->'relationships'->'participants'->'data' @> JSONB_BUILD_ARRAY(JSONB_BUILD_OBJECT('id', linked_participants.data->'data'->>'id', 'type', linked_participants.data->'data'->>'type'))
+      ))
+    ) AS data
+  FROM rosters
+),
+-- link match-rosters
+linked_matches AS (
+  SELECT
+    matches.data->>'id' AS id,
+    JSONB_BUILD_OBJECT(
+      'data', matches.data-'relationships',
+      'relations', TO_JSONB(ARRAY(
+        SELECT * FROM linked_rosters
+        WHERE matches.data->'relationships'->'rosters'->'data' @> JSONB_BUILD_ARRAY(JSONB_BUILD_OBJECT('id', linked_rosters.data->'data'->>'id', 'type', linked_rosters.data->'data'->>'type'))
+      ))
+    ) AS data
   FROM matches
- ),
- -- cleanup players
- linked_players AS (
-   SELECT
-   players.data->>'id' AS id,
-   players.data->>'type' AS type,
-   players.data->'attributes' AS attributes
-   FROM players
- ),
- -- insert!
- insert_matches AS (
-   INSERT INTO match SELECT * FROM linked_matches
-   ON CONFLICT(id) DO NOTHING
-   RETURNING type, id -- TODO conflict shouldn't happen in prod
- ),
- insert_players AS (
-   INSERT INTO player SELECT * FROM linked_players
-   ON CONFLICT(id) DO
-   UPDATE SET attributes=EXCLUDED.attributes
-   WHERE (player.attributes->'stats'->>'played')::int < (EXCLUDED.attributes->'stats'->>'played')::int
-   RETURNING type, id
- )
- SELECT type, id FROM insert_matches
- UNION
- SELECT type, id FROM insert_players
+),
+-- insert!
+insert_matches AS (
+  INSERT INTO match(id, data) SELECT * FROM linked_matches
+  ON CONFLICT(id) DO NOTHING
+  RETURNING id
+)
+SELECT id FROM insert_matches