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 ++++++------ backend/histplorer.py | 35 +++++++++++-------------------- backend/tests/test_gen_disp_data.py | 42 ++++++++++++++++++------------------- 4 files changed, 41 insertions(+), 51 deletions(-) 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() diff --git a/backend/histplorer.py b/backend/histplorer.py index 830705b..6e7e340 100755 --- a/backend/histplorer.py +++ b/backend/histplorer.py @@ -218,29 +218,18 @@ def lookupEvents(start: HistDate | None, end: HistDate | None, scale: int, ctg: constraints = ['event_disp.scale = ?'] params: list[str | int] = [scale] # Constrain by start/end - if start is not None: - constraint = '(start >= ? AND fmt > 0 OR start >= ? AND fmt = 0)' - if start.gcal is None: - startJdn = gregorianToJdn(start.year, 1, 1) if start.year >= MIN_CAL_YEAR else 0 - constraints.append(constraint) - params.extend([startJdn, start.year]) - else: - startJdn = gregorianToJdn(start.year, start.month, start.day) - constraints.append(constraint) - year = start.year if start.month == 1 and start.day == 1 else start.year + 1 - params.extend([startJdn, year]) - if end is not None: - constraint = '(start < ? AND fmt > 0 OR start < ? AND fmt = 0)' - if scale < 1 and (end.month > 1 or end.day > 1): - constraint = '(start < ? AND fmt > 0 OR start <= ? AND fmt = 0)' - if end.gcal is None: - endJdn = gregorianToJdn(end.year, 1, 1) if end.year >= MIN_CAL_YEAR else -1 - constraints.append(constraint) - params.extend([endJdn, end.year]) - else: - endJdn = gregorianToJdn(end.year, end.month, end.day) - constraints.append(constraint) - params.extend([endJdn, end.year]) + startUnit = dateToUnit(start, scale) if start is not None else None + endUnit = dateToUnit(end, scale) if end is not None else None + if start is not None and startUnit == endUnit: + constraints.append('event_disp.unit = ?') + params.append(startUnit) + else: + if start is not None: + constraints.append('event_disp.unit >= ?') + params.append(startUnit) + if end is not None: + constraints.append('event_disp.unit < ?') + params.append(endUnit) # Constrain by event category if ctg is not None: constraints.append('ctg = ?') diff --git a/backend/tests/test_gen_disp_data.py b/backend/tests/test_gen_disp_data.py index db6ddc0..792a9b2 100644 --- a/backend/tests/test_gen_disp_data.py +++ b/backend/tests/test_gen_disp_data.py @@ -93,27 +93,27 @@ class TestGenData(unittest.TestCase): } ) self.assertEqual( - readTestDbTable(dbFile, 'SELECT id, scale FROM event_disp'), + readTestDbTable(dbFile, 'SELECT id, scale, unit FROM event_disp'), { - (5, 10), - (7, 10), - (2, 10), - (5, 1), - (7, 1), - (4, 1), - (2, 1), - (1, MONTH_SCALE), - (7, MONTH_SCALE), - (4, MONTH_SCALE), - (5, MONTH_SCALE), - (11, MONTH_SCALE), - (2, MONTH_SCALE), - (1, DAY_SCALE), - (7, DAY_SCALE), - (6, DAY_SCALE), - (4, DAY_SCALE), - (5, DAY_SCALE), - (11, DAY_SCALE), - (2, DAY_SCALE), + (5, 10, 190), + (7, 10, 190), + (2, 10, 200), + (5, 1, 1900), + (7, 1, 1900), + (4, 1, 1901), + (2, 1, 2002), + (1, MONTH_SCALE, 2415021), + (7, MONTH_SCALE, 2415021), + (4, MONTH_SCALE, 2415386), + (5, MONTH_SCALE, 2415307), + (11, MONTH_SCALE, 2415307), + (2, MONTH_SCALE, 2452593), + (1, DAY_SCALE, 2415021), + (7, DAY_SCALE, 2415021), + (6, DAY_SCALE, 2415030), + (4, DAY_SCALE, 2415386), + (5, DAY_SCALE, 2415307), + (11, DAY_SCALE, 2415307), + (2, DAY_SCALE, 2452607), } ) -- cgit v1.2.3