Merge pull request #9 from vainglorygame/queries_from_dir

Queries from dir

4 files changed, 84 insertions, 0 deletions

diff --git a/queries/match.sql b/queries/match.sql
new file mode 100644
index 0000000..970412b
--- /dev/null
+++ b/queries/match.sql
@@ -0,0 +1,18 @@
+SELECT
+
+id AS "match_id",
+(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"
+
+FROM apidata WHERE type='match'
diff --git a/queries/match_participation.sql b/queries/match_participation.sql
new file mode 100644
index 0000000..0fed84b
--- /dev/null
+++ b/queries/match_participation.sql
@@ -0,0 +1,23 @@
+SELECT
+
+id AS "player_id",
+0 AS "rosterId",
+0 AS "fk_player_id",
+0 AS "fk_match_results_id",
+attributes->>'actor' AS "hero",
+(attributes->'stats'->>'kills')::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",
+(attributes->'stats'->>'nonJungleMinionKills')::int AS "laneMinionsSlayed",
+(attributes->'stats'->>'minionKills')::int AS "jungleMinionsSlayed",
+(attributes->'stats'->>'turretCaptures')::int AS "turretsDestroyed",
+(attributes->'stats'->>'krakenCaptures')::int AS "krakenCaptures",
+(attributes->'stats'->>'goldMineCaptures')::int AS "goldMineCaptures",
+(attributes->'stats'->>'crystalMineCaptures')::int AS "crystalMineCaptures",
+(attributes->'stats'->>'wentAfk')::bool::int AS "wentAfk",
+FALSE AS "perfectGame"
+
+FROM apidata WHERE type='participant'
diff --git a/queries/match_results.sql b/queries/match_results.sql
new file mode 100644
index 0000000..b1e750b
--- /dev/null
+++ b/queries/match_results.sql
@@ -0,0 +1,22 @@
+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 apidata WHERE type='roster'
diff --git a/queries/player.sql b/queries/player.sql
new file mode 100644
index 0000000..cf71d20
--- /dev/null
+++ b/queries/player.sql
@@ -0,0 +1,21 @@
+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",
+0 AS "totalGamePlaytime",
+(attributes->'stats'->>'played_ranked')::int AS "playedRanked",
+(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"
+
+FROM apidata WHERE type='player'