aboutsummaryrefslogtreecommitdiff
path: root/backend/hist_data
diff options
context:
space:
mode:
Diffstat (limited to 'backend/hist_data')
-rw-r--r--backend/hist_data/README.md28
-rw-r--r--backend/hist_data/enwiki/README.md2
-rwxr-xr-xbackend/hist_data/enwiki/gen_img_data.py43
-rwxr-xr-xbackend/hist_data/gen_desc_data.py5
-rw-r--r--backend/hist_data/gen_disp_data.py45
-rwxr-xr-xbackend/hist_data/reduce_event_data.py42
6 files changed, 97 insertions, 68 deletions
diff --git a/backend/hist_data/README.md b/backend/hist_data/README.md
index b557b14..d05016c 100644
--- a/backend/hist_data/README.md
+++ b/backend/hist_data/README.md
@@ -21,6 +21,12 @@ This directory holds files used to generate the history database data.db.
- `pop`: <br>
Format: `id INT PRIMARY KEY, pop INT` <br>
Associates each event with a popularity measure (currently an average monthly viewcount)
+- `dist`: <br>
+ Format: `scale INT, unit INT, count INT, PRIMARY KEY (scale, unit)` <br>
+ Maps scale units to counts of events in them.
+- `event_disp`: <br>
+ Format: `id INT, scale INT, PRIMARY KEY (id, scale)` <br>
+ Maps events to scales they are 'displayable' on (used to make displayed events more uniform across time).
- `images`: <br>
Format: `id INT PRIMARY KEY, url TEXT, license TEXT, artist TEXT, credit TEXT` <br>
Holds metadata for available images
@@ -30,12 +36,6 @@ This directory holds files used to generate the history database data.db.
- `descs`: <br>
Format: `id INT PRIMARY KEY, wiki_id INT, desc TEXT` <br>
Associates an event's enwiki title with a short description.
-- `dist`: <br>
- Format: `scale INT, unit INT, count INT, PRIMARY KEY (scale, unit)` <br>
- Maps scale units to event counts.
-- `event_disp`: <br>
- Format: `id INT, scale INT, PRIMARY KEY (id, scale)` <br>
- Maps events to scales they are 'displayable' on (used to make displayed events more uniform across time).
# Generating the Database
@@ -51,13 +51,15 @@ Some of the scripts use third-party packages:
1. Run `gen_events_data.py`, which creates `data.db`, and adds the `events` table.
## Generate Popularity Data
-1. Obtain 'page view files' in enwiki/, as specified in it's README.
+1. Obtain an enwiki dump and 'page view files' in enwiki/, as specified in the README.
1. Run `gen_pop_data.py`, which adds the `pop` table, using data in enwiki/ and the `events` table.
+## Generate Event Display Data, and Reduce Dataset
+1. Run `gen_disp_data.py`, which adds the `dist` and `event_disp` tables, and removes events not in `event_disp`.
+
## Generate Image Data and Popularity Data
1. In enwiki/, run `gen_img_data.py` which looks at pages in the dump that match entries in `events`,
looks for infobox image names, and stores them in an image database.
- Uses popularity data in enwiki/ to find the top N events in each event category.
1. In enwiki/, run `download_img_license_info.py`, which downloads licensing info for found
images, and adds them to the image database.
1. In enwiki/, run `download_imgs.py`, which downloads images into enwiki/imgs/.
@@ -69,11 +71,8 @@ Some of the scripts use third-party packages:
- An input x.gif might produce x-1.jpg, x-2.jpg, etc, instead of x.jpg.
## Generate Description Data
-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 `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).
+1. Run `gen_desc_data.py`, which adds the `descs` table, using data in enwiki/, and the `events` table.
## Optionally Add Extra Event Data
1. Additional events can be described in `picked/events.json`, with images for them put
@@ -81,7 +80,4 @@ Some of the scripts use third-party packages:
1. Can run `gen_picked_data.py` to add those described events to the database.
## Remove Events Without Images/Descs
-1. Run `reduce_event_data.py` to remove data for events that have no image/description.
-
-## Generate Distribution and Displayability Data
-1. Run `gen_disp_data.py`, which add the `dist` and `event_disp` tables.
+1. Run `reduce_event_data.py` to remove data for events that have no image.
diff --git a/backend/hist_data/enwiki/README.md b/backend/hist_data/enwiki/README.md
index 29fc2ff..262ebdb 100644
--- a/backend/hist_data/enwiki/README.md
+++ b/backend/hist_data/enwiki/README.md
@@ -38,7 +38,7 @@ This directory holds files obtained/derived from [English Wikipedia](https://en.
Used to download licensing metadata for image names, via wikipedia's online API, and store them into a database.
- `img_data.db` <br>
Used to hold metadata about infobox images for a set of page IDs.
- Generated using `get_enwiki_img_data.py` and `download_img_license_info.py`. <br>
+ Generated using `gen_img_data.py` and `download_img_license_info.py`. <br>
Tables: <br>
- `page_imgs`: `page_id INT PRIMARY KEY, title TEXT UNIQUE, img_name TEXT` <br>
`img_name` may be NULL, which means 'none found', and is used to avoid re-processing page IDs.
diff --git a/backend/hist_data/enwiki/gen_img_data.py b/backend/hist_data/enwiki/gen_img_data.py
index b4ade9f..922b893 100755
--- a/backend/hist_data/enwiki/gen_img_data.py
+++ b/backend/hist_data/enwiki/gen_img_data.py
@@ -14,10 +14,8 @@ import sqlite3
DUMP_FILE = 'enwiki-20220501-pages-articles-multistream.xml.bz2'
INDEX_DB = 'dump_index.db'
-PAGEVIEW_DB = 'pageview_data.db'
IMG_DB = 'img_data.db' # The database to create
DB_FILE = os.path.join('..', 'data.db')
-MAX_IMGS_PER_CTG = 20000
#
ID_LINE_REGEX = re.compile(r'<id>(.*)</id>')
IMG_LINE_REGEX = re.compile(r'.*\| *image *= *([^|]*)')
@@ -179,49 +177,28 @@ def getImageName(content: list[str]) -> str | None:
return None
return None
-def getInputPageIdsFromDb(dbFile: str, pageviewDb: str, indexDb: str, maxImgsPerCtg: int) -> set[int]:
+def getInputPageIdsFromDb(dbFile: str, indexDb: str) -> set[int]:
print('Getting event data')
- titleToCtg: dict[str, str] = {}
+ titles: set[str] = set()
dbCon = sqlite3.connect(dbFile)
- for title, ctg in dbCon.execute('SELECT title, ctg from events'):
- titleToCtg[title] = ctg
+ for (title,) in dbCon.execute('SELECT title from events'):
+ titles.add(title)
dbCon.close()
- print('Getting top images for each event category')
- ctgToTitles: dict[str, list[str]] = {}
- dbCon = sqlite3.connect(pageviewDb)
- for (title,) in dbCon.execute('SELECT title FROM views ORDER BY views DESC'):
- if title not in titleToCtg:
- continue
- ctg = titleToCtg[title]
- if ctg not in ctgToTitles:
- ctgToTitles[ctg] = []
- elif len(ctgToTitles[ctg]) == maxImgsPerCtg:
- continue
- ctgToTitles[ctg].append(title)
- del titleToCtg[title]
- dbCon.close()
- for title, ctg in titleToCtg.items(): # Account for titles without view counts
- if ctg not in ctgToTitles:
- ctgToTitles[ctg] = []
- elif len(ctgToTitles[ctg]) == maxImgsPerCtg:
- continue
- ctgToTitles[ctg].append(title)
print('Getting page IDs')
pageIds: set[int] = set()
dbCon = sqlite3.connect(indexDb)
dbCur = dbCon.cursor()
- for ctg in ctgToTitles:
- for title in ctgToTitles[ctg]:
- row = dbCur.execute('SELECT id FROM offsets WHERE title = ?', (title,)).fetchone()
- if row:
- pageIds.add(row[0])
+ for title in titles:
+ row = dbCur.execute('SELECT id FROM offsets WHERE title = ?', (title,)).fetchone()
+ if row:
+ pageIds.add(row[0])
dbCon.close()
- print(f'Result: {len(pageIds)} out of {len(titleToCtg)}')
+ print(f'Result: {len(pageIds)} out of {len(titles)}')
return pageIds
if __name__ == '__main__':
import argparse
parser = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
parser.parse_args()
#
- pageIds = getInputPageIdsFromDb(DB_FILE, PAGEVIEW_DB, INDEX_DB, MAX_IMGS_PER_CTG)
+ pageIds = getInputPageIdsFromDb(DB_FILE, INDEX_DB)
genData(pageIds, DUMP_FILE, INDEX_DB, IMG_DB)
diff --git a/backend/hist_data/gen_desc_data.py b/backend/hist_data/gen_desc_data.py
index 68f9e56..0d7ee88 100755
--- a/backend/hist_data/gen_desc_data.py
+++ b/backend/hist_data/gen_desc_data.py
@@ -16,10 +16,9 @@ def genData(enwikiDb: str, dbFile: str) -> None:
dbCur = dbCon.cursor()
dbCur.execute('CREATE TABLE descs (id INT PRIMARY KEY, wiki_id INT, desc TEXT)')
#
- print('Getting events with images')
+ print('Getting events')
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):
+ for eventId, title in dbCur.execute('SELECT id, title FROM events'):
titleToId[title] = eventId
#
print('Getting Wikipedia descriptions')
diff --git a/backend/hist_data/gen_disp_data.py b/backend/hist_data/gen_disp_data.py
index a81263f..e771e57 100644
--- a/backend/hist_data/gen_disp_data.py
+++ b/backend/hist_data/gen_disp_data.py
@@ -1,7 +1,7 @@
#!/usr/bin/python3
"""
-Adds data about event distribution and displayability to the database.
+Adds data about event distribution to the database, and removes events not eligible for display.
"""
# Enable unit testing code to, when running this script, resolve imports of modules within this directory
@@ -23,12 +23,12 @@ 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: set[tuple[int, int]] = set() # Maps event ids to scales they are displayable on
+ idScales: dict[int, list[int]] = {} # Maps event ids to scales 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):
iterNum += 1
- if iterNum % 1e3 == 0:
+ if iterNum % 1e5 == 0:
print(f'At iteration {iterNum}')
# For each scale
for scale in scales:
@@ -42,16 +42,49 @@ def genData(dbFile: str, scales: list[int], maxDisplayedPerUnit: int) -> None:
counts = [1, 0]
if counts[1] < maxDisplayedPerUnit:
counts[1] += 1
- idScales.add((eventId, scale))
+ if eventId not in idScales:
+ idScales[eventId] = []
+ idScales[eventId].append(scale)
scaleUnitToCounts[(scale, unit)] = counts
+ print(f'Results: {len(idScales)} displayable events')
+ #
+ print('Looking for non-displayable events')
+ eventsToDel: list[int] = []
+ for eventId, eventStart, fmt in dbCur.execute(query):
+ if eventId in idScales:
+ continue
+ eventsToDel.append(eventId)
+ # Remove from data to be added to 'dist'
+ for scale in scales:
+ unit = dateToUnit(dbDateToHistDate(eventStart, fmt), scale)
+ count = scaleUnitToCounts[(scale, unit)][0] - 1
+ if count == 0:
+ del scaleUnitToCounts[(scale, unit)]
+ else:
+ scaleUnitToCounts[(scale, unit)][0] = count
+ query2 = 'SELECT events.id FROM events LEFT JOIN pop ON events.id = pop.id WHERE pop.id IS NULL'
+ for (eventId,) in dbCur.execute(query2): # Include events without scores
+ eventsToDel.append(eventId)
+ print(f'Found {len(eventsToDel)}')
+ #
+ print(f'Deleting {len(eventsToDel)} events')
+ iterNum = 0
+ for eventId in eventsToDel:
+ iterNum += 1
+ if iterNum % 1e5 == 0:
+ print(f'At iteration {iterNum}')
+ #
+ dbCur.execute('DELETE FROM events WHERE id = ?', (eventId,))
+ dbCur.execute('DELETE FROM pop WHERE id = ?', (eventId,))
#
print('Writing to db')
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, scale in idScales:
- dbCur.execute('INSERT INTO event_disp VALUES (?, ?)', (eventId, scale))
+ for eventId, scales in idScales.items():
+ for scale in scales:
+ dbCur.execute('INSERT INTO event_disp VALUES (?, ?)', (eventId, scale))
#
print('Closing db')
dbCon.commit()
diff --git a/backend/hist_data/reduce_event_data.py b/backend/hist_data/reduce_event_data.py
index 15c2ab5..c061f90 100755
--- a/backend/hist_data/reduce_event_data.py
+++ b/backend/hist_data/reduce_event_data.py
@@ -1,23 +1,43 @@
#!/usr/bin/python3
"""
-Delete extraneous events from the database that have no image (and consequently no description)
+Delete events from the database that have no image.
"""
+# Enable unit testing code to, when running this script, resolve imports of modules within this directory
+import os, sys
+parentDir = os.path.dirname(os.path.realpath(__file__))
+sys.path.append(parentDir)
+
import argparse
import sqlite3
+from cal import SCALES, dbDateToHistDate, dateToUnit
DB_FILE = 'data.db'
-def reduceData(dbFile: str) -> None:
+def reduceData(dbFile: str, scales: list[int]) -> None:
dbCon = sqlite3.connect(dbFile)
dbCur = dbCon.cursor()
#
print('Getting events to delete')
- eventsToDel = set()
- query = 'SELECT events.id FROM events LEFT JOIN event_imgs ON events.id = event_imgs.id WHERE event_imgs.id IS NULL'
- for (eventId,) in dbCur.execute(query):
- eventsToDel.add(eventId)
+ eventsToDel: list[int] = []
+ scaleUnitToDelCount: dict[tuple[int, int], int] = {} # Stores counts to subtract from entries in 'dist'
+ query = 'SELECT events.id, events.start, events.fmt FROM events' \
+ ' LEFT JOIN event_imgs ON events.id = event_imgs.id WHERE event_imgs.id IS NULL'
+ iterNum = 0
+ for (eventId, start, fmt) in dbCur.execute(query):
+ if iterNum % 1e5 == 0:
+ print(f'At iteration {iterNum}')
+ #
+ eventsToDel.append(eventId)
+ date = dbDateToHistDate(start, fmt)
+ for scale in scales:
+ unit = dateToUnit(date, scale)
+ if (scale, unit) not in scaleUnitToDelCount:
+ scaleUnitToDelCount[(scale, unit)] = 1
+ else:
+ scaleUnitToDelCount[(scale, unit)] += 1
+ print(f'Found {len(eventsToDel)}')
#
print('Deleting events')
iterNum = 0
@@ -26,8 +46,12 @@ def reduceData(dbFile: str) -> None:
if iterNum % 1e5 == 0:
print(f'At iteration {iterNum}')
#
- dbCur.execute('DELETE from events where id = ?', (eventId,))
- dbCur.execute('DELETE from pop where id = ?', (eventId,))
+ dbCur.execute('DELETE FROM events WHERE id = ?', (eventId,))
+ dbCur.execute('DELETE FROM pop WHERE id = ?', (eventId,))
+ dbCur.execute('DELETE FROM event_disp WHERE id = ?', (eventId,))
+ for (scale, unit), delCount in scaleUnitToDelCount.items():
+ dbCur.execute('UPDATE dist SET count = count - ? WHERE scale = ? AND unit = ?', (delCount, scale, unit))
+ dbCur.execute('DELETE FROM dist WHERE count < 1')
#
dbCon.commit()
dbCon.close()
@@ -36,4 +60,4 @@ if __name__ == '__main__':
parser = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
args = parser.parse_args()
#
- reduceData(DB_FILE)
+ reduceData(DB_FILE, SCALES)
generated by cgit v1.2.3 (git 2.39.1) at 2026-04-24 23:23:48 +0000