From 69cee93c3bfbf95e1dd5e20bc6aa64bd82f95634 Mon Sep 17 00:00:00 2001
From: schneefux <schneefux+commit@schneefux.xyz>
Date: Sat, 25 Feb 2017 22:16:03 +0100
Subject: rewrite to use shared queue, fixes #36

---
 insert.sql | 82 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 82 insertions(+)
 create mode 100644 insert.sql

(limited to 'insert.sql')

diff --git a/insert.sql b/insert.sql
new file mode 100644
index 0000000..be4dcae
--- /dev/null
+++ b/insert.sql
@@ -0,0 +1,82 @@
+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
+  ),
+ -- 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->>'id', 'type', linked_participants.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
+  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 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 id
+ )
+ SELECT * FROM insert_matches
+ UNION
+ SELECT * FROM insert_players
-- 
cgit v1.3.1