diff options
| -rw-r--r-- | api.py | 40 | ||||
| -rw-r--r-- | queries.py | 20 | ||||
| -rw-r--r-- | queries/hero-winrates.sql | 18 | ||||
| -rw-r--r-- | queries/index.sql | 4 | ||||
| -rw-r--r-- | queries/recent-matches.sql | 30 | ||||
| -rw-r--r-- | requirements.txt | 6 |
6 files changed, 9 insertions, 109 deletions
@@ -1,20 +1,12 @@ #!/usr/bin/python -import datetime import asyncio -import socket -import sys import os -import json -import aiohttp.web -import aiohttp_route_decorator import database import crawler -import queries -route = aiohttp_route_decorator.RouteCollector() db = database.Database() @@ -52,7 +44,7 @@ async def crawl_region(region): await db.upsert(matches, True) -async def recrawl(): +async def crawl_forever(): """Gets the latest matches from all regions every 5 minutes.""" print("getting recent matches") @@ -61,28 +53,14 @@ async def recrawl(): # get or put when the last crawl was executed # crawl and upsert + tasks = [] for region in ["na", "eu"]: # fire workers - asyncio.ensure_future(crawl_region(region)) + tasks.append(asyncio.ensure_future(crawl_region(region))) + await asyncio.gather(*tasks) # wait until all have completed await asyncio.sleep(300) - asyncio.ensure_future(recrawl()) - - -@route("/matches") -async def api_matches(request): - data = (await db.select(queries.queries["recent-matches"]))[0]["data"] - return aiohttp.web.Response(text=str(data)) - -@route("/winrates") -async def api_winrates(request): - data = (await db.select(queries.queries["hero-winrates"]))[0]["data"] - return aiohttp.web.Response(text=str(data)) - -@route("/status") -async def api_status(_): - resp = json.dumps({"version": "0.1.0"}) - return aiohttp.web.Response(text=resp) + asyncio.ensure_future(crawl_forever()) # repeat. loop = asyncio.get_event_loop() @@ -93,8 +71,6 @@ loop.run_until_complete(db.connect( password=os.environ["POSTGRESQL_PASSWORD"], database=os.environ["POSTGRESQL_DB"] )) -loop.run_until_complete(queries.load_queries("/apps/api/queries")) -loop.create_task(recrawl()) -app = aiohttp.web.Application(loop=loop) -route.add_to_router(app.router) -aiohttp.web.run_app(app) +loop.run_until_complete( + crawl_forever() +) diff --git a/queries.py b/queries.py deleted file mode 100644 index 1e6c3cc..0000000 --- a/queries.py +++ /dev/null @@ -1,20 +0,0 @@ -#!/usr/bin/python -import asyncio -import glob -import os -import asyncpg - -queries = dict() - -async def load_queries(path): - """Prepares a folder of SQL files as SQL statements. - - :param path: Path to the `.sql` files. - :type path: str - """ - # load from queries/ - queryfiles = glob.glob(path + "/*.sql") - for fp in queryfiles: - with open(fp, "r", encoding="utf-8-sig") as qfile: - name = os.path.splitext(os.path.basename(fp))[0] - queries[name] = qfile.read() diff --git a/queries/hero-winrates.sql b/queries/hero-winrates.sql deleted file mode 100644 index 51f40e1..0000000 --- a/queries/hero-winrates.sql +++ /dev/null @@ -1,18 +0,0 @@ -SELECT ARRAY_TO_JSON(ARRAY( -SELECT - JSONB_BUILD_OBJECT( - 'actor', actors.actor, - 'winrate', (SELECT - SUM((CASE WHEN (participant.data->'attributes'->'stats'->>'winner')='true' THEN 1 ELSE 0 END))::float - / - COUNT(*)::float - FROM participant - WHERE (participant.data->'attributes'->>'actor') = actors.actor) - ) -FROM -(SELECT - DISTINCT participant.data->'attributes'->>'actor' - AS actor - FROM participant) -AS actors) -) AS data;
\ No newline at end of file diff --git a/queries/index.sql b/queries/index.sql deleted file mode 100644 index 5af21b5..0000000 --- a/queries/index.sql +++ /dev/null @@ -1,4 +0,0 @@ -CREATE INDEX ON match((data->>'id')); -CREATE INDEX ON roster((data->>'id')); -CREATE INDEX ON participant((data->>'id')); -CREATE INDEX ON match((data->'attributes'->>'createdAt')); diff --git a/queries/recent-matches.sql b/queries/recent-matches.sql deleted file mode 100644 index 1bde01b..0000000 --- a/queries/recent-matches.sql +++ /dev/null @@ -1,30 +0,0 @@ -SELECT ARRAY_TO_JSON(ARRAY( -SELECT - JSONB_BUILD_OBJECT( - 'id', (match.data->'attributes'->>'shardId') || (match.data->>'id'), - 'date', match.data->'attributes'->>'createdAt', - 'duration', CAST( - match.data->'attributes'->>'duration' - AS INTEGER), - 'teams', ARRAY( - SELECT( - SELECT - JSONB_BUILD_OBJECT( - 'id', roster.data->>'id', - 'side', roster.data->'attributes'->'stats'->>'side', - 'kills', roster.data->'attributes'->'stats'->>'heroKills', - 'players', ARRAY( - SELECT( - SELECT - participant.data->'attributes'->>'actor' - FROM participant WHERE relparticipant->>'id' = participant.data->>'id') - FROM JSONB_ARRAY_ELEMENTS(roster.data->'relationships'->'participants'->'data') relparticipant - ) - ) - FROM roster WHERE relroster->>'id' = roster.data->>'id') - FROM JSONB_ARRAY_ELEMENTS(match.data->'relationships'->'rosters'->'data') relroster - ) -) -FROM match -ORDER BY match.data->'attributes'->>'createdAt' DESC -)) AS data; diff --git a/requirements.txt b/requirements.txt index c323d27..7d8614e 100644 --- a/requirements.txt +++ b/requirements.txt @@ -1,14 +1,10 @@ -aiodns==1.1.1 aiohttp==1.2.0 -aiohttp-route-decorator==0.1.3 appdirs==1.4.0 async-timeout==1.1.0 asyncpg==0.8.4 -cchardet==1.1.2 chardet==2.3.0 multidict==2.1.4 packaging==16.8 -pycares==2.1.1 pyparsing==2.1.10 six==1.10.0 -yarl==0.8.1 +yarl==0.9.0 |