From 014da2e01cde7c19169efc29242e90a71b6d328c Mon Sep 17 00:00:00 2001 From: Terry Truong Date: Fri, 20 Jan 2023 21:01:35 +1100 Subject: Make gen_picked_data.py update event distribution tables --- backend/hist_data/gen_picked_data.py | 77 +++++++++++++++++++++++++++-------- backend/tests/test_gen_picked_data.py | 61 +++++++++++++++++++++++---- 2 files changed, 112 insertions(+), 26 deletions(-) diff --git a/backend/hist_data/gen_picked_data.py b/backend/hist_data/gen_picked_data.py index cd554d5..c5f4577 100755 --- a/backend/hist_data/gen_picked_data.py +++ b/backend/hist_data/gen_picked_data.py @@ -13,13 +13,14 @@ import argparse import json, sqlite3 # Local imports from gen_imgs import convertImage +from cal import SCALES, dbDateToHistDate, dateToUnit PICKED_DIR = 'picked' PICKED_EVT_FILE = 'events.json' DB_FILE = 'data.db' IMG_OUT_DIR = 'img' -def genData(pickedDir: str, pickedEvtFile: str, dbFile: str, imgOutDir: str) -> None: +def genData(pickedDir: str, pickedEvtFile: str, dbFile: str, imgOutDir: str, scales: list[int]) -> None: dbCon = sqlite3.connect(dbFile) dbCur = dbCon.cursor() # @@ -41,6 +42,7 @@ def genData(pickedDir: str, pickedEvtFile: str, dbFile: str, imgOutDir: str) -> dbCur.execute('INSERT INTO events VALUES (?, ?, ?, ?, ?, ?, ?, ?)', (nextId, event['title'], event['start'], event['start_upper'], event['end'], event['end_upper'], event['fmt'], event['ctg'])) + # Update image, description, and popularity tables if 'image' in event: print('> Adding image') image = event['image'] @@ -55,27 +57,48 @@ def genData(pickedDir: str, pickedEvtFile: str, dbFile: str, imgOutDir: str) -> if 'desc' in event: dbCur.execute('INSERT INTO descs VALUES (?, ?, ?)', (nextId, nextId, event['desc'])) dbCur.execute('INSERT INTO pop VALUES (?, ?)', (nextId, event['pop'])) + # Update event distribution tables + for scale in scales: + unit = dateToUnit(dbDateToHistDate(event['start'], event['fmt']), scale) + if dbCur.execute('SELECT count FROM dist WHERE scale = ? AND unit = ?', (scale, unit)).fetchone(): + dbCur.execute('UPDATE dist SET count = count + 1 WHERE scale = ? AND unit = ?', (scale, unit)) + else: + dbCur.execute('INSERT INTO dist VALUES (?, ?, ?)', (scale, unit, 1)) + dbCur.execute('INSERT INTO event_disp VALUES (?, ?, ?)', (nextId, scale, unit)) + # nextId -= 1 elif doDelete: if eventId: print(f'Deleting event with ID {eventId}') + row = dbCur.execute('SELECT id, start, fmt FROM events WHERE id = ?', (eventId,)).fetchone() else: print(f'Deleting event with title "{title}"') - row = dbCur.execute('SELECT id FROM events WHERE title = ?', (title,)).fetchone() + row = dbCur.execute('SELECT id, start, fmt FROM events WHERE title = ?', (title,)).fetchone() if row is None: print(f'ERROR: Could not find event with title {title}') break - eventId = row[0] + eventId, eventStart, eventFmt = row + # Note: Intentionally not deleting entries or files for images that become unused. dbCur.execute('DELETE FROM events WHERE id = ?', (eventId,)) dbCur.execute('DELETE FROM pop WHERE id = ?', (eventId,)) dbCur.execute('DELETE FROM descs WHERE id = ?', (eventId,)) dbCur.execute('DELETE FROM event_imgs WHERE id = ?', (eventId,)) - # Note: Intentionally not deleting entries or files for images that become unused. + for scale in scales: + unit = dateToUnit(dbDateToHistDate(eventStart, eventFmt), scale) + (oldCount,) = dbCur.execute( + 'SELECT count FROM dist WHERE scale = ? AND unit = ?', (scale, unit)).fetchone() + if oldCount == 1: + dbCur.execute('DELETE FROM dist WHERE scale = ? AND unit = ?', (scale, unit)) + else: + dbCur.execute('UPDATE dist SET count = count - 1 WHERE scale = ? AND unit = ?', (scale, unit)) + dbCur.execute('DELETE FROM event_disp WHERE id = ?', (eventId,)) else: # doModify print(f'Modifying event with ID {eventId}') - if dbCur.execute('SELECT id FROM events WHERE id = ?', (eventId,)).fetchone() is None: + row = dbCur.execute('SELECT start, fmt FROM events WHERE id = ?', (eventId,)).fetchone() + if row is None: print(f'ERROR: Could not find event with ID {eventId}') break + oldStart, oldFmt = row for field in ['title', 'start', 'start_upper', 'end', 'end_upper', 'fmt', 'ctg']: if field in event: dbCur.execute(f'UPDATE events SET {field} = ? WHERE id = ?', (event[field], eventId,)) @@ -89,24 +112,42 @@ def genData(pickedDir: str, pickedEvtFile: str, dbFile: str, imgOutDir: str) -> break dbCur.execute('INSERT INTO images VALUES (?, ?, ?, ?, ?)', (nextId, image['url'], image['license'], image['artist'], image['credit'])) - row = dbCur.execute('SELECT img_id FROM event_imgs WHERE id = ?', (eventId,)).fetchone() - if row is None: - dbCur.execute('INSERT INTO event_imgs VALUES (?, ?)', (eventId, nextId)) - else: + if dbCur.execute('SELECT img_id FROM event_imgs WHERE id = ?', (eventId,)).fetchone(): dbCur.execute('UPDATE event_imgs SET img_id = ? WHERE id = ?', (nextId, eventId)) # Note: Intentionally not deleting entries or files for images that become unused. - if 'desc' in event: - row = dbCur.execute('SELECT desc FROM descs WHERE id = ?', (eventId,)).fetchone() - if row is None: - dbCur.execute('INSERT INTO descs VALUES (?, ?)', (eventId, event['desc'])) else: + dbCur.execute('INSERT INTO event_imgs VALUES (?, ?)', (eventId, nextId)) + if 'desc' in event: + if dbCur.execute('SELECT desc FROM descs WHERE id = ?', (eventId,)).fetchone(): dbCur.execute('UPDATE event_imgs SET desc = ? WHERE id = ?', (event['desc'], eventId)) - if 'pop' in event: - row = dbCur.execute('SELECT pop FROM pop WHERE id = ?', (eventId,)).fetchone() - if row is None: - dbCur.execute('INSERT INTO pop VALUES (?, ?)', (eventId, event['pop'])) else: + dbCur.execute('INSERT INTO descs VALUES (?, ?)', (eventId, event['desc'])) + if 'pop' in event: + if dbCur.execute('SELECT pop FROM pop WHERE id = ?', (eventId,)).fetchone(): dbCur.execute('UPDATE pop SET pop = ? WHERE id = ?', (event['pop'], eventId)) + else: + dbCur.execute('INSERT INTO pop VALUES (?, ?)', (eventId, event['pop'])) + if 'start' in event: + # Remove old distribution data + for scale in scales: + unit = dateToUnit(dbDateToHistDate(oldStart, oldFmt), scale) + (oldCount,) = dbCur.execute( + 'SELECT count FROM dist WHERE scale = ? AND unit = ?', (scale, unit)).fetchone() + if oldCount == 1: + dbCur.execute('DELETE FROM dist WHERE scale = ? AND unit = ?', (scale, unit)) + else: + dbCur.execute('UPDATE dist SET count = count - 1 WHERE scale = ? AND unit = ?', (scale, unit)) + dbCur.execute('DELETE FROM event_disp WHERE id = ?', (eventId,)) + # Add new distribution data + newFmt = event['fmt'] if 'fmt' in event else oldFmt + for scale in scales: + unit = dateToUnit(dbDateToHistDate(event['start'], newFmt), scale) + if dbCur.execute('SELECT count FROM dist WHERE scale = ? AND unit = ?', (scale, unit)).fetchone(): + dbCur.execute('UPDATE dist SET count = count + 1 WHERE scale = ? AND unit = ?', (scale, unit)) + else: + dbCur.execute('INSERT INTO dist VALUES (?, ?, ?)', (scale, unit, 1)) + dbCur.execute('INSERT INTO event_disp VALUES (?, ?, ?)', (eventId, scale, unit)) + # Note: Intentionally not updating 'event_disp' table to account for 'indirect event displayability' nextId -= 1 # dbCon.commit() @@ -116,4 +157,4 @@ if __name__ == '__main__': parser = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter) args = parser.parse_args() # - genData(PICKED_DIR, PICKED_EVT_FILE, DB_FILE, IMG_OUT_DIR) + genData(PICKED_DIR, PICKED_EVT_FILE, DB_FILE, IMG_OUT_DIR, SCALES) diff --git a/backend/tests/test_gen_picked_data.py b/backend/tests/test_gen_picked_data.py index 8f1beda..ec1203b 100644 --- a/backend/tests/test_gen_picked_data.py +++ b/backend/tests/test_gen_picked_data.py @@ -19,11 +19,11 @@ class TestGenImgs(unittest.TestCase): createTestFile(pickedEvtFile, ''' [{ "title": "COVID-19 Pandemic", - "start": 2458919, + "start": 2019, "start_upper": null, "end": null, "end_upper": null, - "fmt": 2, + "fmt": 0, "ctg": "event", "image": { "file": "covid.jpg", @@ -62,9 +62,9 @@ class TestGenImgs(unittest.TestCase): 'start INT, start_upper INT, end INT, end_upper INT, fmt INT, ctg TEXT)', 'INSERT INTO events VALUES (?, ?, ?, ?, ?, ?, ?, ?)', { - (1, 'event one', 100, 1000, None, None, 0, 'event'), - (2, 'event two', 200, 2000, None, None, 0, 'event'), - (3, 'event three', 300, 3000, None, None, 0, 'event'), + (1, 'event one', 1, 1000, None, None, 0, 'event'), + (2, 'event two', 2, 2000, None, None, 0, 'event'), + (3, 'event three', 3, 3000, None, None, 0, 'event'), } ) createTestDbTable( @@ -103,12 +103,35 @@ class TestGenImgs(unittest.TestCase): (3, 1), } ) + createTestDbTable( + dbFile, + 'CREATE TABLE dist (scale INT, unit INT, count INT, PRIMARY KEY (scale, unit))', + 'INSERT INTO dist VALUES (?, ?, ?)', + { + (10, 0, 3), + (1, 1, 1), + (1, 2, 1), + (1, 3, 1), + } + ) + createTestDbTable( + dbFile, + 'CREATE TABLE event_disp (id INT, scale INT, unit INT, PRIMARY KEY (id, scale))', + 'INSERT INTO event_disp VALUES (?, ?, ?)', + { + (1, 10, 0), + (2, 10, 0), + (1, 1, 1), + (2, 1, 2), + (3, 1, 3), + } + ) # Create existing event images imgOutDir = os.path.join(tempDir, 'imgs') os.mkdir(imgOutDir) shutil.copy(TEST_IMG, os.path.join(imgOutDir, '10.jpg')) # Run - genData(pickedDir, pickedEvtFile, dbFile, imgOutDir) + genData(pickedDir, pickedEvtFile, dbFile, imgOutDir, [10, 1]) # Check self.assertEqual(set(os.listdir(imgOutDir)), { '10.jpg', @@ -118,9 +141,9 @@ class TestGenImgs(unittest.TestCase): self.assertEqual( readTestDbTable(dbFile, 'SELECT id, title, start, start_upper, end, end_upper, fmt, ctg FROM events'), { - (1, 'event one', 100, 1000, None, None, 0, 'event'), + (1, 'event one', 1, 1000, None, None, 0, 'event'), (2, 'foo', -100, None, None, None, 0, 'discovery'), - (-1, 'COVID-19 Pandemic', 2458919, None, None, None, 2, 'event'), + (-1, 'COVID-19 Pandemic', 2019, None, None, None, 0, 'event'), } ) self.assertEqual( @@ -155,3 +178,25 @@ class TestGenImgs(unittest.TestCase): (-1, 100), } ) + self.assertEqual( + readTestDbTable(dbFile, 'SELECT scale, unit, count from dist'), + { + (10, 0, 1), + (10, 201, 1), + (10, -10, 1), + (1, 1, 1), + (1, -100, 1), + (1, 2019, 1), + } + ) + self.assertEqual( + readTestDbTable(dbFile, 'SELECT id, scale, unit from event_disp'), + { + (1, 10, 0), + (2, 10, -10), + (-1, 10, 201), + (1, 1, 1), + (2, 1, -100), + (-1, 1, 2019), + } + ) -- cgit v1.2.3