From 4a23340f168ff8342b96be49a02616be54f73fba Mon Sep 17 00:00:00 2001 From: schneefux Date: Mon, 13 Feb 2017 21:40:10 +0100 Subject: convert samples for each table --- api.py | 182 +++++++++++++++++++++++++++++++++++++++++++++++------------------ 1 file changed, 132 insertions(+), 50 deletions(-) diff --git a/api.py b/api.py index 921993f..ce0bf56 100644 --- a/api.py +++ b/api.py @@ -22,50 +22,23 @@ dest_db = { } -class Database(object): - async def connect(self, **connect_kwargs): - """Connect to database by arguments.""" - self._pool = await asyncpg.create_pool(**connect_kwargs) - - async def each(self, fetchquery, func, **func_args): - """Execute a function for each row that is fetched with query.""" - # TODO use iterator instead of callback - async with self._pool.acquire() as conn: - async with conn.transaction(): - tasks = [] - async for record in conn.cursor(fetchquery): - tasks.append( - asyncio.ensure_future(func(record, **func_args)) - ) - await asyncio.gather(*tasks) - - async def into(self, data, table): - """Insert a named tuple into a database.""" - items = list(data.items()) - keys, values = [x[0] for x in items], [x[1] for x in items] - placeholders = ["${}".format(i) for i, _ in enumerate(values, 1)] - query = "INSERT INTO {} (\"{}\") VALUES ({})".format( - table, "\", \"".join(keys), ", ".join(placeholders)) - - async with self._pool.acquire() as conn: - await conn.fetch(query, (*data)) - - -async def process(): - sdb = Database() - await sdb.connect(**source_db) - ddb = Database() - await ddb.connect(**dest_db) - - selqry = """ +class Processor(object): + def __init__(self): + self._queries = {} + self._srcpool = self._destpool = None + + def setup(self): + """Load conversion queries.""" + self._queries = { + "match": """ SELECT -(data->'attributes'->>'duration')::int AS "duration", -data->'attributes'->>'gameMode' AS "gameMode", -COALESCE(NULLIF(data->'attributes'->>'patchVersion', ''), '0')::int AS "patchVersion", -data->'attributes'->>'shardId' AS "shard", -data->'attributes'->'stats'->>'endGameReason' AS "result", -data->'attributes'->'stats'->>'queue' AS "queue", +(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", @@ -74,13 +47,122 @@ false AS "anyAFK", 0 AS "jungleMinionsSlayed", 0 AS "heroDeaths" -FROM match LIMIT 100 - """ +FROM apidata WHERE type='match' + """, + "match_results": """ +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", +0 AS "turretsDestroyed", +(attributes->'stats'->>'heroKills')::int AS "heroKills", +0 AS "heroAssits", +0 AS "heroDeaths", +(attributes->'stats'->>'krakenCaptures')::int AS "krakensCaptured", +0 AS "goldMineCaptures", +0 AS "crystalMineCaptures", +0 AS "afkCount", +0 AS "afkTime" + +FROM apidata WHERE type='roster' + """, + "match_participation": """ +SELECT + +0 AS "rosterId", +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", +(attributes->'stats'->>'nonJungleMinionKills')::int AS "laneMinionsSlayed", +(attributes->'stats'->>'minionKills')::int AS "jungleMinionsSlayed", +(attributes->'stats'->>'turretCaptures')::int AS "turretsDestroyed", +0 AS "heroKills", +0 AS "heroDeaths", +0 AS "heroAssits", +(attributes->'stats'->>'krakenCaptures')::int AS "krakensCaptured", +(attributes->'stats'->>'goldMineCaptures')::int AS "goldMineCaptures", +(attributes->'stats'->>'crystalMineCaptures')::int AS "crystalMineCaptures", +(attributes->'stats'->>'wentAfk')::bool::int AS "afkCount", +(attributes->'stats'->>'firstAfkTime')::float::int AS "afkTime", +FALSE AS "perfectGame" + +FROM apidata WHERE type='participant' + """, + "player": """ +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'->>'wins')::int AS "wins", +(attributes->'stats'->>'winStreak')::int AS "streak", +'' AS "herosUnlocked", +'' "skinsUnlocked", +0 AS "totalGamePlaytime", +attributes->'stats'->>'lifetimeGold' AS "lifeTimeGold", +0 AS "lifeTimeKills", +0 AS "lifeTimeDeaths", +0 AS "lifeTimeAssists", +'' AS "bestHeroPlayingWith", +'' AS "worstHeroPlayingWith", +'' AS "bestHeroPlayingAgainst", +'' AS "worstHeroPlayingAgainst", +0 AS "lifeTimeKD", +0 AS "lifeTimeKDA", +'[]'::jsonb AS "heroPerformance", +'[]'::jsonb AS "rolePerformance" + +FROM apidata WHERE type='player' + """ + } + + async def connect(self, source, dest): + """Connect to database by arguments.""" + self._srcpool = await asyncpg.create_pool(**source) + self._destpool = await asyncpg.create_pool(**dest) + + async def run(self, stop_after=-1): + """Execute a function for each row that is fetched with query.""" + # TODO unnest + async with self._srcpool.acquire() as srccon: + async with self._destpool.acquire() as destcon: + for table, query in self._queries.items(): + stop_after -= 1 # TODO for debugging, don't process whole table + logging.info("processing table %s", table) + async with srccon.transaction(): + async with destcon.transaction(): + async for rec in srccon.cursor(query): + await self.into(destcon, rec, table) + + async def into(self, conn, data, table): + """Insert a named tuple into a table.""" + items = list(data.items()) + keys, values = [x[0] for x in items], [x[1] for x in items] + placeholders = ["${}".format(i) for i, _ in enumerate(values, 1)] + query = "INSERT INTO {} (\"{}\") VALUES ({})".format( + table, "\", \"".join(keys), ", ".join(placeholders)) + await conn.execute(query, (*data)) + - await sdb.each(selqry, ddb.into, table="match") +async def main(): + pr = Processor() + await pr.connect(source_db, dest_db) + pr.setup() + await pr.run(stop_after=1000) -if __name__ == "__main__": - loop = asyncio.get_event_loop() - loop.run_until_complete( - process() - ) +logging.basicConfig(level=logging.DEBUG) +loop = asyncio.get_event_loop() +loop.run_until_complete(main()) -- cgit v1.3.1 From 98c5b16b2d804e63cb1b4835295104e49900a205 Mon Sep 17 00:00:00 2001 From: Kapil Viren Ahuja Date: Tue, 14 Feb 2017 14:15:39 +0530 Subject: changes to web's DB cols - names etc. to sync with Web DB model --- api.py | 33 +++++++++++---------------------- 1 file changed, 11 insertions(+), 22 deletions(-) diff --git a/api.py b/api.py index ce0bf56..e83b175 100644 --- a/api.py +++ b/api.py @@ -62,14 +62,12 @@ FALSE AS "surrender", 0 AS "laneMinionsSlayed", 0 AS "jungleMinionsSlayed", 0 AS "turretsDestroyed", -(attributes->'stats'->>'heroKills')::int AS "heroKills", -0 AS "heroAssits", -0 AS "heroDeaths", -(attributes->'stats'->>'krakenCaptures')::int AS "krakensCaptured", +(attributes->'stats'->>'heroKills')::int AS "kills", +0 AS "assits", +0 AS "deaths", +(attributes->'stats'->>'krakenCaptures')::int AS "krakenCaptures", 0 AS "goldMineCaptures", -0 AS "crystalMineCaptures", -0 AS "afkCount", -0 AS "afkTime" +0 AS "crystalMineCaptures" FROM apidata WHERE type='roster' """, @@ -81,19 +79,16 @@ 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.0 AS "KD", +0.0 AS "KDA", (attributes->'stats'->>'nonJungleMinionKills')::int AS "laneMinionsSlayed", (attributes->'stats'->>'minionKills')::int AS "jungleMinionsSlayed", (attributes->'stats'->>'turretCaptures')::int AS "turretsDestroyed", -0 AS "heroKills", -0 AS "heroDeaths", -0 AS "heroAssits", -(attributes->'stats'->>'krakenCaptures')::int AS "krakensCaptured", +(attributes->'stats'->>'krakenCaptures')::int AS "krakenCaptures", (attributes->'stats'->>'goldMineCaptures')::int AS "goldMineCaptures", (attributes->'stats'->>'crystalMineCaptures')::int AS "crystalMineCaptures", -(attributes->'stats'->>'wentAfk')::bool::int AS "afkCount", -(attributes->'stats'->>'firstAfkTime')::float::int AS "afkTime", +(attributes->'stats'->>'wentAfk')::bool::int AS "wentAfk", +0 AS "killParticipation", FALSE AS "perfectGame" FROM apidata WHERE type='participant' @@ -116,14 +111,8 @@ attributes->'stats'->>'lifetimeGold' AS "lifeTimeGold", 0 AS "lifeTimeKills", 0 AS "lifeTimeDeaths", 0 AS "lifeTimeAssists", -'' AS "bestHeroPlayingWith", -'' AS "worstHeroPlayingWith", -'' AS "bestHeroPlayingAgainst", -'' AS "worstHeroPlayingAgainst", 0 AS "lifeTimeKD", -0 AS "lifeTimeKDA", -'[]'::jsonb AS "heroPerformance", -'[]'::jsonb AS "rolePerformance" +0 AS "lifeTimeKDA" FROM apidata WHERE type='player' """ -- cgit v1.3.1 From f716c0f770c3a482350089348fa919a626452bf4 Mon Sep 17 00:00:00 2001 From: Kapil Viren Ahuja Date: Tue, 14 Feb 2017 15:56:06 +0530 Subject: fixed that one typo --- api.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/api.py b/api.py index e83b175..a6f3836 100644 --- a/api.py +++ b/api.py @@ -63,7 +63,7 @@ FALSE AS "surrender", 0 AS "jungleMinionsSlayed", 0 AS "turretsDestroyed", (attributes->'stats'->>'heroKills')::int AS "kills", -0 AS "assits", +0 AS "assists", 0 AS "deaths", (attributes->'stats'->>'krakenCaptures')::int AS "krakenCaptures", 0 AS "goldMineCaptures", -- cgit v1.3.1 From 0455416095e0347d644716d4438fb2b8b14c8db2 Mon Sep 17 00:00:00 2001 From: schneefux Date: Wed, 15 Feb 2017 16:02:32 +0100 Subject: load processor queries from sql files --- api.py | 102 +++++----------------------------------- queries/match.sql | 18 +++++++ queries/match_participation.sql | 23 +++++++++ queries/match_results.sql | 22 +++++++++ queries/player.sql | 21 +++++++++ 5 files changed, 97 insertions(+), 89 deletions(-) create mode 100644 queries/match.sql create mode 100644 queries/match_participation.sql create mode 100644 queries/match_results.sql create mode 100644 queries/player.sql diff --git a/api.py b/api.py index a6f3836..ac004d2 100644 --- a/api.py +++ b/api.py @@ -1,6 +1,7 @@ #!/usr/bin/python import os +import glob import logging import asyncio import asyncpg @@ -28,95 +29,18 @@ class Processor(object): self._srcpool = self._destpool = None def setup(self): - """Load conversion queries.""" - self._queries = { - "match": """ -SELECT - -(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' - """, - "match_results": """ -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", -0 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" - -FROM apidata WHERE type='roster' - """, - "match_participation": """ -SELECT - -0 AS "rosterId", -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", -(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", -0 AS "killParticipation", -FALSE AS "perfectGame" - -FROM apidata WHERE type='participant' - """, - "player": """ -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'->>'wins')::int AS "wins", -(attributes->'stats'->>'winStreak')::int AS "streak", -'' AS "herosUnlocked", -'' "skinsUnlocked", -0 AS "totalGamePlaytime", -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' - """ - } + """Load .sql files from queries/ directory. + File name is the table to insert into.""" + self._queries = {} + scriptroot = os.path.realpath( + os.path.join(os.getcwd(), os.path.dirname(__file__))) + # glob *.sql + for fp in glob.glob(scriptroot + "/queries/*.sql"): + # utf-8-sig is used by pgadmin, doesn't hurt to specify + with open(fp, "r", encoding="utf-8-sig") as file: + table = os.path.splitext(os.path.basename(fp))[0] + logging.info("loaded query for '%s'", table) + self._queries[table] = file.read() async def connect(self, source, dest): """Connect to database by arguments.""" 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' -- cgit v1.3.1