From 472fa9c1f11a3c16e10541ce8b9de44a6dadeeec Mon Sep 17 00:00:00 2001 From: Terry Truong Date: Wed, 4 Jan 2023 15:58:18 +1100 Subject: Add 'unit' column to event_disp table Use 'unit' to narrow search of 'event_disp' values Simplify SQL queries to use 'unit' instead of 'start' and 'fmt' Fix minor HistDate documentation error --- backend/hist_data/cal.py | 2 +- backend/hist_data/gen_disp_data.py | 13 +++++++------ 2 files changed, 8 insertions(+), 7 deletions(-) (limited to 'backend/hist_data') diff --git a/backend/hist_data/cal.py b/backend/hist_data/cal.py index 29959ef..efb5bab 100644 --- a/backend/hist_data/cal.py +++ b/backend/hist_data/cal.py @@ -77,7 +77,7 @@ class HistDate: - 'month' and 'day' are at least 1, if given - 'gcal' may be: - True: Indicates a Gregorian calendar date - - False: Means the date should, for display, be converted to a Julian calendar date + - False: Indicates a Julian calendar date - None: 'month' and 'day' are 1 (required for dates before MIN_CAL_YEAR) """ def __init__(self, gcal: bool | None, year: int, month=1, day=1): diff --git a/backend/hist_data/gen_disp_data.py b/backend/hist_data/gen_disp_data.py index d796d92..e8b2bf4 100755 --- a/backend/hist_data/gen_disp_data.py +++ b/backend/hist_data/gen_disp_data.py @@ -26,7 +26,7 @@ def genData(dbFile: str, scales: list[int], maxDisplayedPerUnit: int) -> None: scaleUnitToCounts: dict[tuple[int, int], list[int]] = {} # Maps scale and unit to two counts (num events in that unit, num events displayable for that unit) # Only includes events with popularity values - idScales: dict[int, list[int]] = {} # Maps event ids to scales they are displayable on + idScales: dict[int, list[tuple[int, int]]] = {} # Maps event ids to scales+units they are displayable on iterNum = 0 query = 'SELECT events.id, start, fmt FROM events INNER JOIN pop ON events.id = pop.id ORDER BY pop.pop DESC' for eventId, eventStart, fmt in dbCur.execute(query): @@ -47,7 +47,7 @@ def genData(dbFile: str, scales: list[int], maxDisplayedPerUnit: int) -> None: counts[1] += 1 if eventId not in idScales: idScales[eventId] = [] - idScales[eventId].append(scale) + idScales[eventId].append((scale, unit)) scaleUnitToCounts[(scale, unit)] = counts print(f'Results: {len(idScales)} displayable events') # @@ -84,10 +84,11 @@ def genData(dbFile: str, scales: list[int], maxDisplayedPerUnit: int) -> None: dbCur.execute('CREATE TABLE dist (scale INT, unit INT, count INT, PRIMARY KEY (scale, unit))') for (scale, unit), (count, _) in scaleUnitToCounts.items(): dbCur.execute('INSERT INTO dist VALUES (?, ?, ?)', (scale, unit, count)) - dbCur.execute('CREATE TABLE event_disp (id INT, scale INT, PRIMARY KEY (id, scale))') - for eventId, scales in idScales.items(): - for scale in scales: - dbCur.execute('INSERT INTO event_disp VALUES (?, ?)', (eventId, scale)) + dbCur.execute('CREATE TABLE event_disp (id INT, scale INT, unit INT, PRIMARY KEY (id, scale))') + dbCur.execute('CREATE INDEX event_disp_scale_unit_idx ON event_disp(scale, unit)') + for eventId, scaleUnits in idScales.items(): + for [scale, unit] in scaleUnits: + dbCur.execute('INSERT INTO event_disp VALUES (?, ?, ?)', (eventId, scale, unit)) # print('Closing db') dbCon.commit() -- cgit v1.2.3