From 3c7b19d77476d774762b4a6c97bff18bcd0b249a Mon Sep 17 00:00:00 2001
From: schneefux <schneefux+commit@schneefux.xyz>
Date: Mon, 27 Mar 2017 20:58:21 +0200
Subject: use 2.0 joblib

---
 insert.sql | 74 --------------------------------------------------------------
 1 file changed, 74 deletions(-)
 delete mode 100644 insert.sql

(limited to 'insert.sql')

diff --git a/insert.sql b/insert.sql
deleted file mode 100644
index bc6485d..0000000
--- a/insert.sql
+++ /dev/null
@@ -1,74 +0,0 @@
-﻿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'
-),
--- 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->'data'->>'id', 'type', linked_players.data->'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,
-    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
-),
--- insert!
-insert_matches AS (
-  INSERT INTO match(id, data) SELECT * FROM linked_matches
-  ON CONFLICT(id) DO NOTHING
-  RETURNING id
-)
-SELECT DISTINCT id FROM insert_matches
-- 
cgit v1.3.1

