diff options
| -rw-r--r-- | backend/hist_data/README.md | 14 | ||||
| -rwxr-xr-x | backend/hist_data/gen_desc_data.py | 60 | ||||
| -rwxr-xr-x | backend/hist_data/gen_imgs.py | 1 | ||||
| -rw-r--r-- | backend/tests/test_gen_desc_data.py | 79 | ||||
| -rw-r--r-- | backend/tests/test_gen_imgs.py | 7 |
5 files changed, 151 insertions, 10 deletions
diff --git a/backend/hist_data/README.md b/backend/hist_data/README.md index c5cf66f..32836e2 100644 --- a/backend/hist_data/README.md +++ b/backend/hist_data/README.md @@ -5,7 +5,7 @@ This directory holds files used to generate the history database data.db. Format: `id INT PRIMARY KEY, title TEXT UNIQUE, start INT, start_upper INT, end INT, end_upper INT, fmt INT, ctg TEXT` <br> - Each row has a Wikidata ID, Wikipedia title, start and end dates, and an event category. + Each row has an ID, Wikipedia title, start and end dates, and an event category. - `start*` and `end*` specify start and end dates. `start_upper`, `end`, and `end_upper`, are optional. If `start_upper` is present, it and `start` denote an uncertain range of start times. @@ -27,15 +27,18 @@ This directory holds files used to generate the history database data.db. - `event_imgs`: <br> Format: `id INT PRIMARY KEY, img_id INT` <br> Assocates events with images +- `descs` <br> + Format: `title TEXT PRIMARY KEY, desc TEXT` <br> + Associates an event's enwiki title with a short description. # Generating the Database ## Environment Some of the scripts use third-party packages: - `jdcal`: For date conversion -- `indexed_bzip2`: For parallelised bzip2 processing. -- `mwxml`, `mwparserfromhell`: For parsing Wikipedia dumps. -- `requests`: For downloading data. +- `indexed_bzip2`: For parallelised bzip2 processing +- `mwxml`, `mwparserfromhell`: For parsing Wikipedia dumps +- `requests`: For downloading data ## Generate Event Data 1. Obtain a Wikidata JSON dump in wikidata/, as specified in it's README. @@ -59,4 +62,5 @@ Some of the scripts use third-party packages: 1. Obtain an enwiki dump in enwiki/, as specified in the README. 1. In enwiki/, run `gen_dump_index.db.py`, which generates a database for indexing the dump. 1. In enwiki/, run `gen_desc_data.py`, which extracts page descriptions into a database. -1. Run +1. Run `gen_desc_data.py`, which adds the `descs` table, using data in enwiki/, + and the `events` and `images` tables (only adds descriptions for events with images). diff --git a/backend/hist_data/gen_desc_data.py b/backend/hist_data/gen_desc_data.py new file mode 100755 index 0000000..68f9e56 --- /dev/null +++ b/backend/hist_data/gen_desc_data.py @@ -0,0 +1,60 @@ +#!/usr/bin/python3 + +""" +Maps events to short descriptions from Wikipedia, +and stores them in the database. +""" + +import os, sqlite3 + +ENWIKI_DB = os.path.join('enwiki', 'desc_data.db') +DB_FILE = 'data.db' + +def genData(enwikiDb: str, dbFile: str) -> None: + print('Creating table') + dbCon = sqlite3.connect(dbFile) + dbCur = dbCon.cursor() + dbCur.execute('CREATE TABLE descs (id INT PRIMARY KEY, wiki_id INT, desc TEXT)') + # + print('Getting events with images') + titleToId: dict[str, int] = {} + query = 'SELECT events.id, events.title FROM events INNER JOIN event_imgs ON events.id = event_imgs.id' + for eventId, title in dbCur.execute(query): + titleToId[title] = eventId + # + print('Getting Wikipedia descriptions') + enwikiCon = sqlite3.connect(enwikiDb) + enwikiCur = enwikiCon.cursor() + iterNum = 0 + for title, eventId in titleToId.items(): + iterNum += 1 + if iterNum % 1e4 == 0: + print(f'At iteration {iterNum}') + # Get wiki ID + row = enwikiCur.execute('SELECT id FROM pages WHERE title = ?', (title,)).fetchone() + if row is None: + continue + wikiId = row[0] + # Check for redirect + wikiIdToGet = wikiId + query = \ + 'SELECT pages.id FROM redirects INNER JOIN pages ON redirects.target = pages.title WHERE redirects.id = ?' + row = enwikiCur.execute(query, (wikiId,)).fetchone() + if row is not None: + wikiIdToGet = row[0] + # Get desc + row = enwikiCur.execute('SELECT desc FROM descs where id = ?', (wikiIdToGet,)).fetchone() + if row is None: + continue + dbCur.execute('INSERT INTO descs VALUES (?, ?, ?)', (eventId, wikiId, row[0])) + # + print('Closing databases') + dbCon.commit() + dbCon.close() + +if __name__ == '__main__': + import argparse + parser = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter) + args = parser.parse_args() + # + genData(ENWIKI_DB, DB_FILE) diff --git a/backend/hist_data/gen_imgs.py b/backend/hist_data/gen_imgs.py index 526da1b..0b2f480 100755 --- a/backend/hist_data/gen_imgs.py +++ b/backend/hist_data/gen_imgs.py @@ -1,7 +1,6 @@ #!/usr/bin/python3 """ - Looks at images described by a database, and generates resized/cropped versions into an output directory, with names of the form 'eventId1.jpg'. Adds the image associations and metadata to the history database. diff --git a/backend/tests/test_gen_desc_data.py b/backend/tests/test_gen_desc_data.py new file mode 100644 index 0000000..6f321b4 --- /dev/null +++ b/backend/tests/test_gen_desc_data.py @@ -0,0 +1,79 @@ +import unittest +import tempfile, os + +from tests.common import createTestDbTable, readTestDbTable +from hist_data.gen_desc_data import genData + +class TestGenData(unittest.TestCase): + def test_gen(self): + with tempfile.TemporaryDirectory() as tempDir: + # Create temp enwiki db + enwikiDb = os.path.join(tempDir, 'enwiki_descs.db') + createTestDbTable( + enwikiDb, + 'CREATE TABLE pages (id INT PRIMARY KEY, title TEXT UNIQUE)', + 'INSERT INTO pages VALUES (?, ?)', + { + (1, 'I'), + (3, 'III'), + (4, 'IV'), + (5, 'V'), + (6, 'VI'), + } + ) + createTestDbTable( + enwikiDb, + 'CREATE TABLE redirects (id INT PRIMARY KEY, target TEXT)', + 'INSERT INTO redirects VALUES (?, ?)', + { + (5, 'IV'), + } + ) + createTestDbTable( + enwikiDb, + 'CREATE TABLE descs (id INT PRIMARY KEY, desc TEXT)', + 'INSERT INTO descs VALUES (?, ?)', + { + (1, 'One'), + (3, 'Three'), + (4, 'Four'), + (5, 'Five'), + (6, 'Six'), + } + ) + # Create temp history db + dbFile = os.path.join(tempDir, 'data.db') + createTestDbTable( + dbFile, + 'CREATE TABLE events (id INT PRIMARY KEY, title TEXT UNIQUE, ' \ + 'start INT, start_upper INT, end INT, end_upper INT, fmt INT, ctg TEXT)', + 'INSERT INTO events VALUES (?, ?, ?, ?, ?, ?, ?, ?)', + { + (10, 'I', 100, None, None, None, 0, 'event'), + (20, 'II', 200, None, None, None, 0, 'discovery'), + (30, 'III', 300, None, 350, None, 0, 'event'), + (50, 'V', 5, 10, None, None, 1, 'human'), + (60, 'VI', 6000, None, None, None, None, 'event'), + } + ) + createTestDbTable( + dbFile, + 'CREATE TABLE event_imgs (id INT PRIMARY KEY, img_id INT)', + 'INSERT INTO event_imgs VALUES (?, ?)', + { + (10, 100), + (30, 300), + (50, 500), + } + ) + # Run + genData(enwikiDb, dbFile) + # Check + self.assertEqual( + readTestDbTable(dbFile, 'SELECT id, wiki_id, desc from descs'), + { + (10, 1, 'One'), + (30, 3, 'Three'), + (50, 5, 'Four'), + } + ) diff --git a/backend/tests/test_gen_imgs.py b/backend/tests/test_gen_imgs.py index 2541c1d..f8bfeb6 100644 --- a/backend/tests/test_gen_imgs.py +++ b/backend/tests/test_gen_imgs.py @@ -11,8 +11,7 @@ class TestGenImgs(unittest.TestCase): @patch('hist_data.gen_imgs.convertImage', autospec=True) def test_gen(self, convertImageMock): with tempfile.TemporaryDirectory() as tempDir: - convertImageMock.side_effect = \ - lambda imgPath, outPath: shutil.copy(imgPath, outPath) + convertImageMock.side_effect = lambda imgPath, outPath: shutil.copy(imgPath, outPath) # Create temp images imgDir = os.path.join(tempDir, 'enwiki_imgs') os.mkdir(imgDir) @@ -63,7 +62,7 @@ class TestGenImgs(unittest.TestCase): '200.jpg', }) self.assertEqual( - readTestDbTable(dbFile, 'SELECT id, img_id from event_imgs'), + readTestDbTable(dbFile, 'SELECT id, img_id FROM event_imgs'), { (10, 100), (20, 200), @@ -71,7 +70,7 @@ class TestGenImgs(unittest.TestCase): } ) self.assertEqual( - readTestDbTable(dbFile, 'SELECT id, url, license, artist, credit from images'), + readTestDbTable(dbFile, 'SELECT id, url, license, artist, credit FROM images'), { (100, 'https://en.wikipedia.org/wiki/File:one.jpg', 'CC BY-SA 3.0', 'author1', 'credits1'), (200, 'https://en.wikipedia.org/wiki/File:two.jpeg', 'cc-by', 'author2', 'credits2'), |