rewrite to use joblib (fixes #20), use updated raw schema, check for duplicates

4 files changed, 81 insertions, 60 deletions

diff --git a/queries/match.sql b/queries/match.sql
index 5dfa692..1e6d37f 100644
--- a/queries/match.sql
+++ b/queries/match.sql
@@ -1,15 +1,15 @@
 SELECT
 
-id AS "apiId",
-(attributes->>'createdAt')::timestamp as "createdAt",
-(attributes->>'duration')::int AS "duration",
-attributes->>'gameMode' AS "gameMode",
-COALESCE(NULLIF(attributes->>'patchVersion', ''), '0') AS "patchVersion",
-attributes->>'shardId' AS "shardId",
-attributes->'stats'->>'endGameReason' AS "endGameReason",
-attributes->'stats'->>'queue' AS "queue" --,
+match.id AS "apiId",
+(match.data->'data'->'attributes'->>'createdAt')::timestamp as "createdAt",
+(match.data->'data'->'attributes'->>'duration')::int AS "duration",
+match.data->'data'->'attributes'->>'gameMode' AS "gameMode",
+COALESCE(NULLIF(match.data->'data'->'attributes'->>'patchVersion', ''), '0') AS "patchVersion",
+match.data->'data'->'attributes'->>'shardId' AS "shardId",
+match.data->'data'->'attributes'->'stats'->>'endGameReason' AS "endGameReason",
+match.data->'data'->'attributes'->'stats'->>'queue' AS "queue",
 
---relationships->'rosters'->'data'->0->>'id' as "roster_1",
---relationships->'rosters'->'data'->1->>'id' as "roster_2"
+match.data->'relations'->0->'data'->>'id' as "roster_1",
+match.data->'relations'->1->'data'->>'id' as "roster_2"
 
-FROM "match"
+FROM match WHERE id=$1
diff --git a/queries/participant.sql b/queries/participant.sql
index d1d81ce..e11179c 100644
--- a/queries/participant.sql
+++ b/queries/participant.sql
@@ -1,28 +1,34 @@
+WITH rosters AS (SELECT
+  JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster
+FROM match WHERE id=$1),
+participants AS (SELECT
+  JSONB_ARRAY_ELEMENTS(rosters.roster->'relations') AS participant
+FROM rosters)
 SELECT
 
-id AS "apiId",
-relationships->'player'->'data'->>'id' AS "playerId",
+participant->'data'->>'id' AS "apiId",
+participant->'relations'->0->'data'->>'id' AS "player_apiId",
 
-attributes->>'actor' AS "hero",
-(attributes->'stats'->>'assists')::int AS "assists",
-COALESCE(NULLIF(attributes->'stats'->>'crystalMineCaptures', ''), '0')::int AS "crystalMineCaptures",
-(attributes->'stats'->>'deaths')::int AS "deaths",
-(attributes->'stats'->>'farm')::float AS "farm",
-(attributes->'stats'->>'firstAfkTime')::float AS "firstAfkTime",
-COALESCE(NULLIF(attributes->'stats'->>'goldMineCaptures', ''), '0')::int AS "goldMineCaptures",
-COALESCE(NULLIF(attributes->'stats'->>'jungleKills', ''), '0')::int AS "jungleKills",
-COALESCE(NULLIF(attributes->'stats'->>'karmaLevel', ''), '0')::int AS "karmaLevel",
-COALESCE(NULLIF(attributes->'stats'->>'kills', ''), '0')::int AS "kills",
-COALESCE(NULLIF(attributes->'stats'->>'krakenCaptures', ''), '0')::int AS "krakenCaptures",
-COALESCE(NULLIF(attributes->'stats'->>'level', ''), '0')::int AS "level",
-COALESCE(NULLIF(attributes->'stats'->>'minionKills', ''), '0')::int AS "minionKills",
-COALESCE(NULLIF(attributes->'stats'->>'skillTier', ''), '0')::int AS "skillTier",
-COALESCE(NULLIF(attributes->'stats'->>'skinKey', ''), '0') AS "skinKey",
-(attributes->'stats'->>'wentAfk')::bool::bool AS "wentAfk",
-(attributes->'stats'->>'winner')::bool::bool AS "winner",
-attributes->'stats'->'itemGrants' AS "itemGrants",
-attributes->'stats'->'itemSells' AS "itemSells",
-attributes->'stats'->'itemUses' AS "itemUses",
-attributes->'stats'->'items' AS "items"
+participant->'data'->'attributes'->>'actor' AS "hero",
+(participant->'data'->'attributes'->'stats'->>'assists')::int AS "assists",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'crystalMineCaptures', ''), '0')::int AS "crystalMineCaptures",
+(participant->'data'->'attributes'->'stats'->>'deaths')::int AS "deaths",
+(participant->'data'->'attributes'->'stats'->>'farm')::float AS "farm",
+(participant->'data'->'attributes'->'stats'->>'firstAfkTime')::float AS "firstAfkTime",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'goldMineCaptures', ''), '0')::int AS "goldMineCaptures",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'jungleKills', ''), '0')::int AS "jungleKills",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'karmaLevel', ''), '0')::int AS "karmaLevel",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'kills', ''), '0')::int AS "kills",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'krakenCaptures', ''), '0')::int AS "krakenCaptures",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'level', ''), '0')::int AS "level",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'minionKills', ''), '0')::int AS "minionKills",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'skillTier', ''), '0')::int AS "skillTier",
+COALESCE(NULLIF(participant->'data'->'attributes'->'stats'->>'skinKey', ''), '0') AS "skinKey",
+(participant->'data'->'attributes'->'stats'->>'wentAfk')::bool AS "wentAfk",
+(participant->'data'->'attributes'->'stats'->>'winner')::bool AS "winner",
+participant->'data'->'attributes'->'stats'->'itemGrants' AS "itemGrants",
+participant->'data'->'attributes'->'stats'->'itemSells' AS "itemSells",
+participant->'data'->'attributes'->'stats'->'itemUses' AS "itemUses",
+participant->'data'->'attributes'->'stats'->'items' AS "items"
 
-FROM "participant"
+FROM participants
diff --git a/queries/player.sql b/queries/player.sql
index 992cb44..0619ee6 100644
--- a/queries/player.sql
+++ b/queries/player.sql
@@ -1,15 +1,29 @@
+WITH rosters AS (SELECT
+  match.data->'data'->'attributes'->>'createdAt' AS matchdate,
+  JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster
+FROM match WHERE id=$1),
+participants AS (SELECT
+  matchdate,
+  JSONB_ARRAY_ELEMENTS(rosters.roster->'relations') AS participant
+FROM rosters),
+players AS (SELECT
+  matchdate,
+  JSONB_ARRAY_ELEMENTS(participants.participant->'relations') AS player
+FROM participants)
+
 SELECT
 
-id AS "apiId",
-attributes->>'name' AS "name",
-(attributes->'stats'->>'level')::int AS "level",
-(attributes->'stats'->>'xp')::int AS "xp",
-(attributes->'stats'->>'played')::int AS "played",
-(attributes->'stats'->>'played_ranked')::int AS "playedRanked",
-(attributes->'stats'->>'played')::int - (attributes->'stats'->>'played_ranked')::int AS "playedCasual",
-(attributes->'stats'->>'wins')::int AS "wins",
-(attributes->'stats'->>'lifetimeGold')::float AS "lifetimeGold",
+players.player->'data'->>'id' AS "apiId",
+players.player->'data'->'attributes'->>'name' AS "name",
+(players.player->'data'->'attributes'->'stats'->>'level')::int AS "level",
+(players.player->'data'->'attributes'->'stats'->>'xp')::int AS "xp",
+(players.player->'data'->'attributes'->'stats'->>'played')::int AS "played",
+(players.player->'data'->'attributes'->'stats'->>'played_ranked')::int AS "playedRanked",
+(players.player->'data'->'attributes'->'stats'->>'played')::int - (players.player->'data'->'attributes'->'stats'->>'played_ranked')::int AS "playedCasual",
+(players.player->'data'->'attributes'->'stats'->>'wins')::int AS "wins",
+(players.player->'data'->'attributes'->'stats'->>'lifetimeGold')::float AS "lifetimeGold",
+CASE WHEN players.player->'data'->'attributes'->>'name'=$2 THEN matchdate ELSE 'epoch'::timestamp::text END AS "lastMatchCreatedDate",
 
 0 AS "streak"
 
-FROM "player"
+FROM players
diff --git a/queries/roster.sql b/queries/roster.sql
index eaf0ed6..c61967b 100644
--- a/queries/roster.sql
+++ b/queries/roster.sql
@@ -1,17 +1,18 @@
+WITH rosters AS (SELECT
+  JSONB_ARRAY_ELEMENTS(match.data->'relations') AS roster
+FROM match WHERE id=$1)
 SELECT
+roster->'data'->>'id' as "apiId",
+(roster->'data'->'attributes'->'stats'->>'acesEarned')::int AS "acesEarned",
+(roster->'data'->'attributes'->'stats'->>'gold')::int AS "gold",
+(roster->'data'->'attributes'->'stats'->>'heroKills')::int AS "heroKills",
+(roster->'data'->'attributes'->'stats'->>'krakenCaptures')::int AS "krakenCaptures",
+roster->'data'->'attributes'->'stats'->>'side' AS "side",
+roster->'data'->'attributes'->'stats'->>'side' AS "teamColor",
+(roster->'data'->'attributes'->'stats'->>'turretKills')::int AS "turretKills",
+(roster->'data'->'attributes'->'stats'->>'turretsRemaining')::int AS "turretsRemaining",
 
-id AS "apiId",
-(attributes->'stats'->>'acesEarned')::int AS "acesEarned",
-(attributes->'stats'->>'gold')::int AS "gold",
-(attributes->'stats'->>'heroKills')::int AS "heroKills",
-(attributes->'stats'->>'krakenCaptures')::int AS "krakenCaptures",
-attributes->'stats'->>'side' AS "side",
-attributes->'stats'->>'side' AS "teamColor",
-(attributes->'stats'->>'turretKills')::int AS "turretKills",
-(attributes->'stats'->>'turretsRemaining')::int AS "turretsRemaining",
-
-relationships->'participants'->'data'->0->>'id' AS "participant_1",
-relationships->'participants'->'data'->1->>'id' AS "participant_2",
-relationships->'participants'->'data'->2->>'id' AS "participant_3"
-
-FROM "roster"
+roster->'relations'->0->'data'->>'id' AS "participant_1",
+roster->'relations'->1->'data'->>'id' AS "participant_2",
+roster->'relations'->2->'data'->>'id' AS "participant_3"
+FROM rosters