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

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),
 				}
 			)