Merge pull request #14 from vainglorygame/features/web-db-model-refactoring

Features web db model refactoring

6 files changed, 56 insertions, 66 deletions

diff --git a/queries/match.sql b/queries/match.sql
index 0ff0c62..4f82933 100644
--- a/queries/match.sql
+++ b/queries/match.sql
@@ -1,18 +1,15 @@
 SELECT
 
-id AS "match_id",
+id AS "apiId",
+(attributes->>'createdAt')::timestamp as "createdAt",
 (attributes->>'duration')::int AS "duration",
 attributes->>'gameMode' AS "gameMode",
-COALESCE(NULLIF(attributes->>'patchVersion', ''), '0')::int AS "patchVersion",
-attributes->>'shardId' AS "shard",
-attributes->'stats'->>'endGameReason' AS "result",
+COALESCE(NULLIF(attributes->>'patchVersion', ''), '0') AS "patchVersion",
+attributes->>'shardId' AS "shardId",
+attributes->'stats'->>'endGameReason' AS "endGameReason",
 attributes->'stats'->>'queue' AS "queue",
 
-false AS "anyAFK",
-'' AS "winningTeam",
-0 AS "krakenCaptures",
-0 AS "laneMinionsSlayed",
-0 AS "jungleMinionsSlayed",
-0 AS "heroDeaths"
+relationships->'rosters'->'data'->0->>'id' as roster_1,
+relationships->'rosters'->'data'->1->>'id' as roster_2
 
 FROM match
diff --git a/queries/match_participation.sql b/queries/match_participation.sql
deleted file mode 100644
index 43785c6..0000000
--- a/queries/match_participation.sql
+++ /dev/null
@@ -1,24 +0,0 @@
-SELECT
-
-relationships->'player'->'data'->>'id' AS player_id,
-0 AS "match_id",
-0 AS "rosterId",
-0 AS "fk_player_id",
-0 AS "fk_match_results_id",
-attributes->>'actor' AS "hero",
-COALESCE(NULLIF(attributes->'stats'->>'kills', ''), '0')::int AS "kills",
-(attributes->'stats'->>'deaths')::int AS "deaths",
-(attributes->'stats'->>'assists')::int AS "assists",
-0.0 AS "KD",
-0.0 AS "KDA",
-0 AS "killParticipation",
-COALESCE(NULLIF(attributes->'stats'->>'nonJungleMinionKills', ''), '0')::int AS "laneMinionsSlayed",
-COALESCE(NULLIF(attributes->'stats'->>'minionKills', ''), '0')::int AS "jungleMinionsSlayed",
-COALESCE(NULLIF(attributes->'stats'->>'turretCaptures', ''), '0')::int AS "turretsDestroyed",
-COALESCE(NULLIF(attributes->'stats'->>'krakenCaptures', ''), '0')::int AS "krakenCaptures",
-COALESCE(NULLIF(attributes->'stats'->>'goldMineCaptures', ''), '0')::int AS "goldMineCaptures",
-COALESCE(NULLIF(attributes->'stats'->>'crystalMineCaptures', ''), '0')::int AS "crystalMineCaptures",
-(attributes->'stats'->>'wentAfk')::bool::int AS "wentAfk",
-FALSE AS "perfectGame"
-
-FROM participant
diff --git a/queries/match_results.sql b/queries/match_results.sql
deleted file mode 100644
index c493d5c..0000000
--- a/queries/match_results.sql
+++ /dev/null
@@ -1,22 +0,0 @@
-SELECT
-
-attributes->'stats'->>'side' AS "team",
-FALSE AS "winner",
-FALSE AS "surrender",
-0 AS "teamSize",
-'' AS "hero_1",
-'' AS "hero_2",
-'' AS "hero_3",
-0 AS "laneMinionsSlayed",
-0 AS "jungleMinionsSlayed",
-(attributes->'stats'->>'turretKills')::int AS "turretsDestroyed",
-(attributes->'stats'->>'heroKills')::int AS "kills",
-0 AS "assists",
-0 AS "deaths",
-(attributes->'stats'->>'krakenCaptures')::int AS "krakenCaptures",
-0 AS "goldMineCaptures",
-0 AS "crystalMineCaptures",
-0 AS "afkCount",
-0 AS "afkTime"
-
-FROM roster
diff --git a/queries/participant.sql b/queries/participant.sql
new file mode 100644
index 0000000..b7685a9
--- /dev/null
+++ b/queries/participant.sql
@@ -0,0 +1,28 @@
+SELECT
+
+id AS "apiId",
+relationships->'player'->'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"
+
+FROM participant
diff --git a/queries/player.sql b/queries/player.sql
index 4f37018..add926a 100644
--- a/queries/player.sql
+++ b/queries/player.sql
@@ -5,17 +5,11 @@ attributes->>'name' AS "name",
 (attributes->'stats'->>'level')::int AS "level",
 (attributes->'stats'->>'xp')::int AS "xp",
 (attributes->'stats'->>'played')::int AS "played",
-0 AS "totalGamePlaytime",
 (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'->>'winStreak')::int AS "streak",
-'' AS "herosUnlocked",
-'' "skinsUnlocked",
-attributes->'stats'->>'lifetimeGold' AS "lifeTimeGold",
-0 AS "lifeTimeKills",
-0 AS "lifeTimeDeaths",
-0 AS "lifeTimeAssists",
-0 AS "lifeTimeKD",
-0 AS "lifeTimeKDA"
+(attributes->'stats'->>'lifetimeGold')::float AS "lifetimeGold",
+
+0 AS "streak"
 
 FROM player
diff --git a/queries/roster.sql b/queries/roster.sql
new file mode 100644
index 0000000..b5c4dff
--- /dev/null
+++ b/queries/roster.sql
@@ -0,0 +1,17 @@
+SELECT
+
+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