From c1f3ee04077420be4ac016d9fc8af9f1ccc8d787 Mon Sep 17 00:00:00 2001 From: schneefux Date: Tue, 28 Feb 2017 18:01:50 +0100 Subject: support split queries --- api.py | 17 ++++++++++------- queries/player.sql | 9 --------- 2 files changed, 10 insertions(+), 16 deletions(-) delete mode 100644 queries/player.sql diff --git a/api.py b/api.py index f668b74..ce186d4 100644 --- a/api.py +++ b/api.py @@ -44,12 +44,15 @@ class Worker(object): """Initialize the database.""" scriptroot = os.path.realpath( os.path.join(os.getcwd(), os.path.dirname(__file__))) - for path in glob.glob(scriptroot + "/queries/*.sql"): + for path in glob.glob(scriptroot + "/queries/*/*.sql"): # utf-8-sig is used by pgadmin, doesn't hurt to specify - # file names: web target table - table = os.path.splitext(os.path.basename(path))[0] + # directory names: web target table + table = os.path.basename(os.path.dirname(path)) with open(path, "r", encoding="utf-8-sig") as file: - self._queries[table] = file.read() + try: + self._queries[table].append(file.read()) + except KeyError: + self._queries[table] = [file.read()] logging.info("loaded query '%s'", table) async def _execute_job(self, jobid, payload): @@ -61,9 +64,9 @@ class Worker(object): async with self._pool.acquire() as con: logging.debug("%s: compiling '%s' from '%s'", jobid, object_id, table) - async with con.transaction(): - await con.execute(self._queries[table], - object_id) + for query in self._queries[table]: + async with con.transaction(): + await con.execute(query, object_id) async def _work(self): """Fetch a job and run it.""" diff --git a/queries/player.sql b/queries/player.sql deleted file mode 100644 index e847de2..0000000 --- a/queries/player.sql +++ /dev/null @@ -1,9 +0,0 @@ --- count the number of matches we have from that player in the database --- UPDATE player SET "games_in_db"= -SELECT -COUNT(DISTINCT match."apiId") AS "games_in_db" -FROM player -JOIN participant ON player."apiId" = participant."player_apiId" -JOIN roster ON participant."roster_apiId" = roster."apiId" -JOIN match ON roster."match_apiId" = match."apiId" -WHERE player.id=$1 -- cgit v1.3.1