From 6dfc71bf6b4d617ffc10f60cdc06b376e26b1c34 Mon Sep 17 00:00:00 2001 From: Kapil Viren Ahuja Date: Fri, 17 Feb 2017 15:54:55 +0530 Subject: refactored to adapt to changes in Web's DB model. this will only work with branch - https://github.com/vainglorygame/vainsocial.com/tree/features/db-model-refactoring --- queries/player.sql | 14 ++++---------- 1 file changed, 4 insertions(+), 10 deletions(-) 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 -- cgit v1.3.1 From b7e442270e0427c8ebf0d246edd91c26d1ad37a1 Mon Sep 17 00:00:00 2001 From: kvahuja Date: Fri, 17 Feb 2017 21:08:14 +0530 Subject: match and roster tables refactored --- queries/match.sql | 18 ++++++------------ queries/roster.sql | 17 +++++++++++++++++ 2 files changed, 23 insertions(+), 12 deletions(-) create mode 100644 queries/roster.sql diff --git a/queries/match.sql b/queries/match.sql index 0ff0c62..8ee527a 100644 --- a/queries/match.sql +++ b/queries/match.sql @@ -1,18 +1,12 @@ 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", -attributes->'stats'->>'queue' AS "queue", - -false AS "anyAFK", -'' AS "winningTeam", -0 AS "krakenCaptures", -0 AS "laneMinionsSlayed", -0 AS "jungleMinionsSlayed", -0 AS "heroDeaths" +COALESCE(NULLIF(attributes->>'patchVersion', ''), '0') AS "patchVersion", +attributes->>'shardId' AS "shardId", +attributes->'stats'->>'endGameReason' AS "endGameReason", +attributes->'stats'->>'queue' AS "queue" FROM match 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 -- cgit v1.3.1 From 22add9e90e2c62a3a08d64b8c73a66b8de77bc97 Mon Sep 17 00:00:00 2001 From: kvahuja Date: Sat, 18 Feb 2017 15:42:48 +0530 Subject: refactoring as part of https://github.com/vainglorygame/vainsocial.com/issues/17 --- queries/match_participation.sql | 24 ------------------------ queries/match_results.sql | 22 ---------------------- queries/participant.sql | 28 ++++++++++++++++++++++++++++ 3 files changed, 28 insertions(+), 46 deletions(-) delete mode 100644 queries/match_participation.sql delete mode 100644 queries/match_results.sql create mode 100644 queries/participant.sql 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..a84274d --- /dev/null +++ b/queries/participant.sql @@ -0,0 +1,28 @@ +SELECT + +id AS "apiId", +relationships->'player'->'data'->>'id' AS "playerId", + +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 -- cgit v1.3.1 From fd0c4e67b13ee0d90b579a3d2279ff7a815725c2 Mon Sep 17 00:00:00 2001 From: kvahuja Date: Sun, 19 Feb 2017 13:11:26 +0530 Subject: match to roster relationship --- queries/match.sql | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) diff --git a/queries/match.sql b/queries/match.sql index 8ee527a..4f82933 100644 --- a/queries/match.sql +++ b/queries/match.sql @@ -7,6 +7,9 @@ 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" +attributes->'stats'->>'queue' AS "queue", + +relationships->'rosters'->'data'->0->>'id' as roster_1, +relationships->'rosters'->'data'->1->>'id' as roster_2 FROM match -- cgit v1.3.1 From d940ad82f7e34576bf6cc6b8a93cbc298765315e Mon Sep 17 00:00:00 2001 From: kvahuja Date: Sun, 19 Feb 2017 16:01:10 +0530 Subject: change to how the FK are managed in EORM --- queries/participant.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/queries/participant.sql b/queries/participant.sql index a84274d..b7685a9 100644 --- a/queries/participant.sql +++ b/queries/participant.sql @@ -1,7 +1,7 @@ SELECT id AS "apiId", -relationships->'player'->'data'->>'id' AS "playerId", +relationships->'player'->'data'->>'id' AS "player_apiId", attributes->>'actor' AS "hero", (attributes->'stats'->>'assists')::int AS "assists", -- cgit v1.3.1