Add Bibliography
Populates bibliography (58 entries), scholars (29), timeline (39 events) from hardcoded research data.
1"""Add bibliography table and populate with works cited in Russell's dissertation. 2 3Tracks which works we have vs. which are cited but missing from our collection. 4Enables gap analysis for scholarship expansion. 5""" 6 7import sqlite3 8from pathlib import Path 9 10BASE_DIR = Path(__file__).resolve().parent.parent 11DB_PATH = BASE_DIR / "db" / "hp.db" 12 13SCHEMA = """ 14CREATE TABLE IF NOT EXISTS bibliography ( 15 id INTEGER PRIMARY KEY, 16 author TEXT NOT NULL, 17 title TEXT NOT NULL, 18 year TEXT, 19 pub_type TEXT CHECK(pub_type IN ('article','book','chapter','thesis','manuscript')), 20 journal_or_publisher TEXT, 21 cited_in TEXT DEFAULT 'Russell 2014', 22 in_collection BOOLEAN DEFAULT 0, 23 collection_filename TEXT, 24 hp_relevance TEXT CHECK(hp_relevance IN ('PRIMARY','DIRECT','INDIRECT','TANGENTIAL')), 25 topic_cluster TEXT, 26 notes TEXT, 27 UNIQUE(author, title) 28); 29 30CREATE TABLE IF NOT EXISTS scholars ( 31 id INTEGER PRIMARY KEY, 32 name TEXT NOT NULL UNIQUE, 33 birth_year INTEGER, 34 death_year INTEGER, 35 nationality TEXT, 36 institution TEXT, 37 specialization TEXT, 38 hp_focus TEXT, 39 bio_notes TEXT, 40 work_count INTEGER DEFAULT 0 41); 42 43CREATE TABLE IF NOT EXISTS scholar_works ( 44 scholar_id INTEGER REFERENCES scholars(id), 45 bib_id INTEGER REFERENCES bibliography(id), 46 PRIMARY KEY (scholar_id, bib_id) 47); 48 49CREATE TABLE IF NOT EXISTS timeline_events ( 50 id INTEGER PRIMARY KEY, 51 year INTEGER NOT NULL, 52 year_end INTEGER, 53 event_type TEXT CHECK(event_type IN ( 54 'PUBLICATION','EDITION','TRANSLATION','DISCOVERY', 55 'ATTRIBUTION','ACQUISITION','EXHIBITION','OTHER' 56 )), 57 title TEXT NOT NULL, 58 description TEXT, 59 scholar_id INTEGER REFERENCES scholars(id), 60 bib_id INTEGER REFERENCES bibliography(id), 61 manuscript_shelfmark TEXT 62); 63""" 64 65# High-priority HP-specific works from Russell's bibliography 66# that we do NOT have in our collection 67HP_BIBLIOGRAPHY = [ 68 # --- Works we HAVE --- 69 ('James Russell', 'Many Other Things Worthy of Knowledge and Memory: The Hypnerotomachia Poliphili and its Annotators, 1499-1700', '2014', 'thesis', 'Durham University', True, 'PhD_Thesis_ _James_Russell Hypnerotomachia Polyphili.pdf', 'DIRECT', 'reception'), 70 ('Anthony Blunt', 'The Hypnerotomachia Poliphili in 17th Century France', '1937', 'article', 'Journal of the Warburg Institute 1:2', True, None, 'DIRECT', 'reception'), 71 ('Mario Praz', 'Some Foreign Imitators of the Hypnerotomachia Poliphili', '1947', 'article', 'Italica 24:1', True, None, 'DIRECT', 'reception'), 72 ('Liane Lefaivre', "Leon Battista Alberti's Hypnerotomachia Poliphili", '1997', 'book', 'MIT Press', True, None, 'DIRECT', 'authorship'), 73 ('Rosemary Trippe', 'The Hypnerotomachia Poliphili: Image, Text, and Vernacular Poetics', '2002', 'article', 'Renaissance Quarterly 55:4', True, None, 'DIRECT', 'text_image'), 74 ('L.E. Semler', "Robert Dallington's Hypnerotomachia and the Protestant Antiquity of Elizabethan England", '2006', 'article', 'Studies in Philology 103:2', True, None, 'DIRECT', 'reception'), 75 ('John Dixon Hunt', 'Experiencing Gardens in the Hypnerotomachia Poliphili', '1998', 'article', 'Word & Image 14:1-2', True, None, 'DIRECT', 'architecture_gardens'), 76 ('Roswitha Stewering', 'Architectural Representations in the Hypnerotomachia Poliphili', '2000', 'article', 'Journal of the SAH 59:1', True, None, 'DIRECT', 'architecture_gardens'), 77 ('Efthymia Priki', 'Elucidating and Enigmatizing: the Reception of the HP', '2009', 'article', 'eSharp 14', True, None, 'DIRECT', 'reception'), 78 79 # --- HIGH PRIORITY GAPS: foundational HP scholarship --- 80 ('Maria Teresa Casella & Giovanni Pozzi', 'Francesco Colonna. Biografia e opere', '1959', 'book', 'Antenore, Padua', False, None, 'DIRECT', 'authorship'), 81 ('Maurizio Calvesi', "La pugna d'amore in sogno di Francesco Colonna Romano", '1996', 'book', 'Lithos Editrice, Rome', False, None, 'DIRECT', 'authorship'), 82 ('Myriam Billanovich & Emilio Menegazzo', 'Francesco Colonna tra Padova e Venezia', '1966', 'article', 'Italia medioevale e umanistica 9', False, None, 'DIRECT', 'authorship'), 83 ('Myriam Billanovich', 'Francesco Colonna, il Poliphilo e la famiglia Lelli', '1968', 'article', 'Italia medioevale e umanistica 19', False, None, 'DIRECT', 'authorship'), 84 ('Myriam Billanovich', 'Francesco Colonna e la famiglia Lelli', '1976', 'article', 'Italia medioevale e umanistica 19', False, None, 'DIRECT', 'authorship'), 85 ('Domenico Gnoli', 'Il Sogno di Polifilo', '1899', 'article', 'La Bibliofilia 1', False, None, 'DIRECT', 'authorship'), 86 ('Christian Huelsen', 'Le illustrazioni della Hypnerotomachia Poliphili', '1910', 'article', 'La Bibliofilia 12', False, None, 'DIRECT', 'text_image'), 87 ('E.H. Gombrich', 'Hypnerotomachiana', '1972', 'chapter', 'in Symbolic Images, Phaidon', False, None, 'DIRECT', 'text_image'), 88 ('Giorgio Agamben', 'Il Sogno della lingua. Per una lettura del Polifilo', '1984', 'chapter', 'in I linguaggi del sogno, Olschki', False, None, 'DIRECT', 'dream_religion'), 89 ('George D. Painter', 'The Hypnerotomachia Poliphilo of 1499', '1963', 'book', 'Eugrammia Press, London', False, None, 'DIRECT', 'material_bibliographic'), 90 ('Giovanni Pozzi', 'Il Polifilo nella storia del libro illustrato veneziano', '1981', 'chapter', 'in Giorgione e l\'Umanesimo veneziano', False, None, 'DIRECT', 'text_image'), 91 ('Linda Fierz-David', 'The Dream of Poliphilo', '1987', 'book', 'Spring Publications, Dallas', False, None, 'DIRECT', 'dream_religion'), 92 ('A. Khomentovskaia', "Felice Feliciano da Verona comme l'auteur de l'HP", '1935', 'article', 'La Bibliofilia 37-38', False, None, 'DIRECT', 'authorship'), 93 ('Emanuela Kretzulesco-Quaranta', 'Les Jardins du Songe', '1976', 'book', 'Les Belles Lettres', False, None, 'DIRECT', 'architecture_gardens'), 94 ('Charles Nodier', 'Franciscus Columna', '1844', 'book', 'Galerie des Beaux-Arts, Paris', False, None, 'DIRECT', 'reception'), 95 ('Peter Dronke', 'Introduction [to HP]', '1981', 'chapter', 'Las Ediciones del Portico, Madrid', False, None, 'DIRECT', 'reception'), 96 ('Edoardo Fumagalli', "Francesco Colonna lettore di Apuleio", '1984', 'article', 'Italia medioevale e umanistica 27', False, None, 'DIRECT', 'authorship'), 97 ('Edoardo Fumagalli', "Due esemplari dell'Hypnerotomachia Poliphili", '1992', 'article', 'Aevum 66', False, None, 'DIRECT', 'material_bibliographic'), 98 ('Dorothea Stichel', 'Reading the HP in the Cinquecento', '1994', 'chapter', 'in Aldus Manutius and Renaissance Culture', False, None, 'DIRECT', 'reception'), 99 ('William H. Sherman', 'Used Books', '2007', 'book', 'University of Pennsylvania Press', False, None, 'INDIRECT', 'reception'), 100 ('Carlo Caruso', "Un geroglifico dell'Hypnerotomachia Poliphili", '2004', 'article', 'Filologia italiana 1', False, None, 'DIRECT', 'text_image'), 101 ('Alfredo Perifano', 'Nazari et Colonna: La Reecriture Alchimique', '2004', 'article', "Bibliotheque d'Humanisme et Renaissance 66:2", False, None, 'DIRECT', 'dream_religion'), 102 ('N. Harris', 'Rising quadrats in the woodcuts of the Aldine HP', '2002', 'article', 'Gutenberg Jahrbuch 77', False, None, 'DIRECT', 'material_bibliographic'), 103 ('Kent Hieatt & Anne Lake Prescott', 'Contemporizing Antiquity', '1992', 'article', 'Word and Image 8', False, None, 'DIRECT', 'text_image'), 104 ('Philip Hofer', 'Variant Copies of the 1499 Poliphilus', '1932', 'article', 'Bulletin of the NYPL 36', False, None, 'DIRECT', 'material_bibliographic'), 105 ('Helena Szepe', 'Artistic Identity in the Poliphilo', '1997', 'article', 'Papers of the BSC 35:1', False, None, 'DIRECT', 'text_image'), 106 ('Benedetto Croce', 'La Hypnerotomachia Polyphili', '1950', 'article', 'Quaderni della Critica 4', False, None, 'DIRECT', 'reception'), 107 ('Fritz Saxl', 'A Scene from the HP in a Painting by Garofalo', '1937', 'article', 'Journal of the Warburg Institute 1', False, None, 'DIRECT', 'text_image'), 108 ('Lamberto Donati', 'Diciamo qualcosa del Polifilo!', '1938', 'article', 'Maso Finiguerra 3', False, None, 'DIRECT', 'reception'), 109 ('Lamberto Donati', 'Studio esegetico sul Polifilo', '1950', 'article', 'La Bibliofilia 52', False, None, 'DIRECT', 'reception'), 110 ('William S. Heckscher', "Bernini's Elephant and Obelisk", '1947', 'article', 'Art Bulletin 29', False, None, 'DIRECT', 'architecture_gardens'), 111 ('William M. Ivins Jr.', 'The Aldine Hypnerotomachia Poliphili of 1499', '1923', 'article', 'Metropolitan Museum of Art Bulletin 18:12', False, None, 'DIRECT', 'material_bibliographic'), 112 ('Sophie Huper', 'The Architectural Monuments of the HP', '1956', 'thesis', 'State University of Iowa', False, None, 'DIRECT', 'architecture_gardens'), 113 ('Silvia Fogliati & David Dutto', 'Il Giardino di Polifilo', '2002', 'book', 'Franco Maria Ricci, Milan', False, None, 'DIRECT', 'architecture_gardens'), 114 ('A. Perez-Gomez', 'Polyphilo, or The Dark Forest Revisited', '1994', 'book', 'MIT Press', False, None, 'DIRECT', 'architecture_gardens'), 115 ('Anthony Colantuono', 'Titian, Colonna, and the Renaissance Science of Procreation', '2010', 'book', 'Ashgate', False, None, 'DIRECT', 'text_image'), 116 ('Esteban Alejandro Cruz', 'Re-Discovering Antiquity through the Dreams of Poliphilus', '2006', 'book', 'Trafford, Oxford', False, None, 'DIRECT', 'architecture_gardens'), 117 ('Helen Barolini', 'Aldus and his Dream Book', '1992', 'book', 'Italica Press, New York', False, None, 'DIRECT', 'material_bibliographic'), 118 ('A. Serena', "Gli elementi trevigiani dell'HP", '1926', 'article', 'Atti del Reale Istituto Veneto 86:2', False, None, 'DIRECT', 'authorship'), 119 ('Giuseppe Biadego', 'Intorno al sogno di Polifilo', '1900', 'article', 'Atti del Reale Istituto Veneto 60:2', False, None, 'DIRECT', 'authorship'), 120 ('Emilio Menegazzo', 'Per la biografia di Francesco Colonna', '1962', 'article', 'Italia medioevale e umanistica 5', False, None, 'DIRECT', 'authorship'), 121 ('Emilio Menegazzo', 'Francesco Colonna baccelliere nello Studio', '1966', 'article', 'Italia medioevale e umanistica 11', False, None, 'DIRECT', 'authorship'), 122 ('Yasamin Bahadorzadeh', 'Silent Theatre', '2008', 'book', 'VDM Verlag Dr. Muller', False, None, 'DIRECT', 'architecture_gardens'), 123 ('Marcel Francon', "Francesco Colonna's Poliphili Hypnerotomachia and Pantagruel", '1954', 'article', 'Italica 31:3', False, None, 'DIRECT', 'reception'), 124 125 # --- TIMELINE ANCHORS: editions and translations --- 126 ('Francesco Colonna', 'Hypnerotomachia Poliphili', '1499', 'book', 'Aldus Manutius, Venice', False, None, 'PRIMARY', 'text_image'), 127 ('Francesco Colonna', 'La Hypnerotomachia di Poliphilo (2nd edition)', '1545', 'book', 'Figlioli di Aldo, Venice', False, None, 'PRIMARY', 'text_image'), 128 ('Jean Martin (trans.)', 'Discours du Songe de Poliphile', '1546', 'book', 'Jacques Kerver, Paris', False, None, 'PRIMARY', 'reception'), 129 ('R.D. (trans.)', 'The Strife of Love in a Dreame', '1592', 'book', 'Simon Waterston, London', False, None, 'PRIMARY', 'reception'), 130 ('Beroalde de Verville', 'Le Tableau des riches Inventions...dans le songe de Poliphile', '1600', 'book', 'Guillemot, Paris', False, None, 'PRIMARY', 'dream_religion'), 131] 132 133# Key scholars for profile pages 134SCHOLARS = [ 135 ('Francesco Colonna', None, 1527, 'Italian', 'SS. Giovanni e Paolo, Venice', 'Dominican friar, presumed author', 'authorship (acrostic attribution)'), 136 ('Aldus Manutius', 1449, 1515, 'Italian', 'Aldine Press, Venice', 'printer, publisher, humanist', 'published 1499 and 1545 editions'), 137 ('Benedetto Giovio', 1471, 1545, 'Italian', 'Como', 'humanist, Plinian scholar', 'annotator of Modena and Como copies'), 138 ('Paolo Giovio', 1483, 1552, 'Italian', 'Como / Rome', 'historian, biographer', 'brother of Benedetto; possible co-annotator'), 139 ('Ben Jonson', 1572, 1637, 'English', 'London', 'playwright, poet', 'annotator of BL C.60.o.12 (1545 edition)'), 140 ('Fabio Chigi (Pope Alexander VII)', 1599, 1667, 'Italian', 'Rome / Vatican', 'pope, patron, bibliophile', 'annotator of Vatican Chig.II.610'), 141 ('Jean Martin', None, 1553, 'French', 'Paris', 'translator', '1546 French translation'), 142 ('Beroalde de Verville', 1556, 1626, 'French', 'Tours', 'writer, alchemist', '1600 alchemical edition'), 143 ("Jean d'Espagnet", 1564, 1637, 'French', None, 'alchemist, natural philosopher', "BL Hand B's alchemical framework"), 144 ('Robert Dallington', 1561, 1637, 'English', None, 'diplomat, writer', '1592 English translation (R.D.)'), 145 ('Giovanni Pozzi', 1923, 2002, 'Swiss-Italian', 'University of Fribourg', 'philologist', 'critical edition (1964, with Ciapponi)'), 146 ('Liane Lefaivre', None, None, 'Canadian', 'TU Delft', 'architectural historian', 'Alberti attribution thesis (1997)'), 147 ('Maurizio Calvesi', 1927, 2020, 'Italian', 'Sapienza, Rome', 'art historian', 'Roman Colonna attribution (1996)'), 148 ('Maria Teresa Casella', None, None, 'Italian', None, 'philologist', 'biography and works (1959, with Pozzi)'), 149 ('Dorothea Stichel', None, None, 'German', None, 'book historian', 'first study of Modena marginalia (1994)'), 150 ('James Russell', None, None, 'British', 'Durham University', 'book historian, marginalia scholar', 'world census of annotated copies (2014)'), 151 ('Efthymia Priki', None, None, 'Greek', None, 'literary scholar', 'hieroglyphs, narrative, text-image, reception'), 152 ('James O\'Neill', None, None, 'British', 'Durham University', 'narratologist', 'self-transformation, authorship, walking methodology'), 153 ('Myriam Billanovich', None, None, 'Italian', None, 'philologist', 'Francesco Colonna biography, Lelli family'), 154 ('Emilio Menegazzo', None, None, 'Italian', None, 'philologist', 'Francesco Colonna biography'), 155 ('Christian Huelsen', 1858, 1935, 'German', 'DAI Rome', 'archaeologist, topographer', 'HP woodcut illustrations (1910)'), 156 ('E.H. Gombrich', 1909, 2001, 'Austrian-British', 'Warburg Institute', 'art historian', 'Hypnerotomachiana (1972)'), 157 ('Anthony Blunt', 1907, 1983, 'British', 'Courtauld Institute', 'art historian', 'HP in 17th-century France (1937)'), 158 ('Fritz Saxl', 1890, 1948, 'Austrian-British', 'Warburg Institute', 'art historian', 'HP scene in Garofalo painting (1937)'), 159 ('Domenico Gnoli', 1838, 1915, 'Italian', 'Rome', 'literary historian', 'Il Sogno di Polifilo (1899)'), 160 ('Charles Nodier', 1780, 1844, 'French', 'Bibliothèque de l\'Arsenal', 'librarian, writer', 'Franciscus Columna (1844)'), 161 ('Georg Leidinger', 1870, 1945, 'German', 'Bayerische Staatsbibliothek', 'librarian, paleographer', "Dürer's ownership of HP (1929)"), 162 ('Linda Fierz-David', None, None, 'Swiss', None, 'Jungian analyst', 'Dream of Poliphilo (1950/1987)'), 163 ('Mario Praz', 1896, 1982, 'Italian', 'Sapienza, Rome', 'literary historian, anglicist', 'Foreign imitators of HP (1947)'), 164] 165 166# Timeline events 167TIMELINE = [ 168 (1499, None, 'PUBLICATION', 'Hypnerotomachia Poliphili published', 'Aldus Manutius publishes the HP in Venice. 172 woodcuts. Author identified by acrostic as POLIAM FRATER FRANCISCVS COLVMNA PERAMAVIT.', 'text_image'), 169 (1500, 1545, 'OTHER', 'Giovio brothers annotate Modena and Como copies', 'Benedetto Giovio applies Plinian encyclopedic reading to two copies.', 'reception'), 170 (1545, None, 'EDITION', 'Second Aldine edition published', "Aldus' sons reprint the HP with recast woodcuts.", 'material_bibliographic'), 171 (1546, None, 'TRANSLATION', 'French translation by Jean Martin', 'Published by Jacques Kerver in Paris with modified and additional woodcuts.', 'reception'), 172 (1554, None, 'EDITION', 'Second French edition', 'Kerver reprints Martin translation.', 'material_bibliographic'), 173 (1561, None, 'EDITION', 'Third French edition with Gohorry notice', 'Jacques Gohorry adds prefatory notice.', 'material_bibliographic'), 174 (1592, None, 'TRANSLATION', 'English translation: The Strife of Love in a Dreame', 'R.D. (Robert Dallington) publishes partial English translation for Simon Waterston.', 'reception'), 175 (1600, None, 'EDITION', "Beroalde de Verville's alchemical edition", 'Le Tableau des riches Inventions includes tableau steganographique of alchemical symbols.', 'dream_religion'), 176 (1600, 1637, 'OTHER', 'Ben Jonson annotates BL copy', 'Jonson mines the 1545 HP for stage design imagery and linguistic material.', 'reception'), 177 (1641, None, 'ACQUISITION', 'Thomas Bourne purchases BL copy', 'Recorded purchase date of May 6, 1641. Anonymous alchemist (Hand B) annotates after this date.', 'reception'), 178 (1641, 1700, 'OTHER', "Anonymous alchemist annotates BL copy (d'Espagnet school)", "Hand B applies alchemical reading centering on 'Master Mercury' following d'Espagnet's framework.", 'dream_religion'), 179 (1650, 1700, 'OTHER', 'Anonymous alchemist annotates Buffalo copy (pseudo-Geber school)', 'Hand E applies Geberian alchemical reading emphasizing Sol/Luna and sulphur.', 'dream_religion'), 180 (1655, 1667, 'OTHER', 'Pope Alexander VII annotates Vatican copy', 'Fabio Chigi combs text for acutezze (verbal wit) and architectural parallels with Rome.', 'reception'), 181 (1804, None, 'EDITION', 'Legrand edition (Paris)', 'J.G. Legrand publishes new French edition.', 'material_bibliographic'), 182 (1844, None, 'PUBLICATION', "Nodier's Franciscus Columna", 'Charles Nodier publishes romanticized biography of Colonna.', 'reception'), 183 (1883, None, 'EDITION', 'Popelin French translation', 'Claudius Popelin publishes new French translation.', 'material_bibliographic'), 184 (1899, None, 'PUBLICATION', "Gnoli's Il Sogno di Polifilo", 'Domenico Gnoli publishes foundational study.', 'authorship'), 185 (1904, None, 'EDITION', 'Methuen facsimile edition', 'London facsimile of 1499 edition.', 'material_bibliographic'), 186 (1910, None, 'PUBLICATION', "Huelsen's study of HP woodcut illustrations", 'Christian Huelsen publishes analysis of woodcuts and their architectural sources.', 'text_image'), 187 (1929, None, 'DISCOVERY', "Leidinger discovers Dürer's ownership inscription", "Georg Leidinger finds proof that Albrecht Dürer owned a copy of the HP, purchased by Erasmus Hock in 1555.", 'material_bibliographic'), 188 (1935, None, 'ATTRIBUTION', 'Khomentovskaia proposes Felice Feliciano as author', 'Alternative attribution beyond Francesco Colonna.', 'authorship'), 189 (1937, None, 'PUBLICATION', "Blunt's HP in 17th Century France", 'Anthony Blunt traces French reception. Saxl publishes on Garofalo painting.', 'reception'), 190 (1947, None, 'PUBLICATION', "Praz's Foreign Imitators + Heckscher on Bernini's Elephant", "Praz traces HP influence on Swinburne, Beardsley, de Mandiargues. Heckscher publishes on Bernini's elephant and obelisk.", 'reception'), 191 (1950, None, 'PUBLICATION', "Croce's La Hypnerotomachia; Fierz-David's Jungian reading", 'Croce publishes study. Fierz-David publishes Jungian interpretation (expanded 1987).', 'dream_religion'), 192 (1959, None, 'PUBLICATION', 'Casella & Pozzi: Francesco Colonna. Biografia e opere', 'Foundational biographical study establishing the Venetian Dominican attribution.', 'authorship'), 193 (1964, None, 'EDITION', 'Pozzi & Ciapponi critical edition', 'Giovanni Pozzi and Lucia Ciapponi publish critical edition with Antenore.', 'material_bibliographic'), 194 (1966, None, 'PUBLICATION', 'Billanovich & Menegazzo: archival discoveries', 'Major archival work on Francesco Colonna and the Lelli family in Padua and Venice.', 'authorship'), 195 (1972, None, 'PUBLICATION', "Gombrich's Hypnerotomachiana", 'E.H. Gombrich publishes study in Symbolic Images.', 'text_image'), 196 (1976, None, 'PUBLICATION', "Kretzulesco-Quaranta's Les Jardins du Songe", 'Major study of HP garden symbolism.', 'architecture_gardens'), 197 (1994, None, 'PUBLICATION', "Stichel's study of Modena marginalia; Perez-Gomez's Dark Forest", 'First study of an annotated copy. Perez-Gomez publishes architectural reading.', 'reception'), 198 (1996, None, 'PUBLICATION', "Calvesi's La pugna d'amore in sogno", 'Major study arguing for a Roman Francesco Colonna rather than the Venetian Dominican.', 'authorship'), 199 (1997, None, 'PUBLICATION', "Lefaivre's Alberti attribution", 'Liane Lefaivre proposes Leon Battista Alberti as author.', 'authorship'), 200 (1998, None, 'PUBLICATION', 'Word & Image special issue on HP', 'Major scholarly collection: Hunt, Leslie, Bury, Curran, Griggs, Segre, Stewering, Temple.', 'architecture_gardens'), 201 (1999, None, 'EDITION', "Godwin's English translation; Ariani & Gabriele critical edition", 'Joscelyn Godwin publishes full English translation (Thames & Hudson). Adelphi publishes Italian critical edition.', 'text_image'), 202 (2002, None, 'PUBLICATION', "Trippe's Image, Text, and Vernacular Poetics", 'Rosemary Trippe recovers HP as vernacular literature.', 'text_image'), 203 (2006, None, 'PUBLICATION', "Semler on Dallington's English HP", 'L.E. Semler rehabilitates 1592 English adaptation.', 'reception'), 204 (2014, None, 'PUBLICATION', "Russell's world census of annotated copies", "James Russell's PhD thesis documents marginalia across six copies.", 'reception'), 205 (2015, None, 'PUBLICATION', 'Word & Image special issue (2015)', 'Second major scholarly collection: Farrington, Nygren, Fabiani Giannetto, Pumroy, Keller.', 'material_bibliographic'), 206 (2020, None, 'PUBLICATION', "O'Neill's Self-Transformation thesis", "James O'Neill's PhD thesis on Poliphilo's inner transformation.", 'architecture_gardens'), 207] 208 209 210def main(): 211 conn = sqlite3.connect(DB_PATH) 212 cur = conn.cursor() 213 214 print("Creating bibliography and scholars tables...") 215 cur.executescript(SCHEMA) 216 conn.commit() 217 218 # Insert bibliography 219 print("Inserting bibliography entries...") 220 bib_count = 0 221 for entry in HP_BIBLIOGRAPHY: 222 author, title, year, pub_type, journal, in_coll, filename, relevance, topic = entry 223 cur.execute( 224 """INSERT OR IGNORE INTO bibliography 225 (author, title, year, pub_type, journal_or_publisher, 226 in_collection, collection_filename, hp_relevance, topic_cluster) 227 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""", 228 (author, title, year, pub_type, journal, in_coll, filename, relevance, topic) 229 ) 230 bib_count += cur.rowcount 231 conn.commit() 232 print(f" Inserted {bib_count} bibliography entries") 233 234 # Insert scholars 235 print("Inserting scholar profiles...") 236 scholar_count = 0 237 for s in SCHOLARS: 238 name, birth, death, nat, inst, spec, hp_focus = s 239 cur.execute( 240 """INSERT OR IGNORE INTO scholars 241 (name, birth_year, death_year, nationality, institution, 242 specialization, hp_focus) 243 VALUES (?, ?, ?, ?, ?, ?, ?)""", 244 (name, birth, death, nat, inst, spec, hp_focus) 245 ) 246 scholar_count += cur.rowcount 247 conn.commit() 248 print(f" Inserted {scholar_count} scholar profiles") 249 250 # Link scholars to bibliography 251 print("Linking scholars to works...") 252 link_count = 0 253 for entry in HP_BIBLIOGRAPHY: 254 author = entry[0] 255 title = entry[1] 256 # Find scholar by name match (first author) 257 first_author = author.split(' & ')[0].split(' (')[0].strip() 258 # Try last name match 259 last_name = first_author.split()[-1] if first_author else None 260 if not last_name: 261 continue 262 cur.execute("SELECT id FROM scholars WHERE name LIKE ?", (f'%{last_name}%',)) 263 scholar_row = cur.fetchone() 264 if not scholar_row: 265 continue 266 cur.execute("SELECT id FROM bibliography WHERE author=? AND title=?", (author, title)) 267 bib_row = cur.fetchone() 268 if not bib_row: 269 continue 270 cur.execute( 271 "INSERT OR IGNORE INTO scholar_works (scholar_id, bib_id) VALUES (?, ?)", 272 (scholar_row[0], bib_row[0]) 273 ) 274 link_count += cur.rowcount 275 conn.commit() 276 print(f" Created {link_count} scholar-work links") 277 278 # Update work counts 279 cur.execute(""" 280 UPDATE scholars SET work_count = ( 281 SELECT COUNT(*) FROM scholar_works WHERE scholar_id = scholars.id 282 ) 283 """) 284 conn.commit() 285 286 # Insert timeline events 287 print("Inserting timeline events...") 288 timeline_count = 0 289 for evt in TIMELINE: 290 year, year_end, evt_type, title, desc, topic = evt 291 cur.execute( 292 """INSERT OR IGNORE INTO timeline_events 293 (year, year_end, event_type, title, description) 294 VALUES (?, ?, ?, ?, ?)""", 295 (year, year_end, evt_type, title, desc) 296 ) 297 timeline_count += cur.rowcount 298 conn.commit() 299 print(f" Inserted {timeline_count} timeline events") 300 301 # Summary stats 302 print("\n=== Database Summary ===") 303 for table in ['bibliography', 'scholars', 'scholar_works', 'timeline_events', 304 'annotator_hands', 'dissertation_refs', 'matches', 'images', 'manuscripts']: 305 cur.execute(f"SELECT COUNT(*) FROM {table}") 306 print(f" {table}: {cur.fetchone()[0]} rows") 307 308 print("\nBibliography gap analysis:") 309 cur.execute("SELECT COUNT(*) FROM bibliography WHERE in_collection = 1") 310 have = cur.fetchone()[0] 311 cur.execute("SELECT COUNT(*) FROM bibliography WHERE in_collection = 0") 312 missing = cur.fetchone()[0] 313 print(f" In collection: {have}") 314 print(f" Missing (cited by Russell): {missing}") 315 print(f" Coverage: {have}/{have+missing} ({100*have//(have+missing)}%)") 316 317 print("\nScholar profiles by nationality:") 318 cur.execute("SELECT nationality, COUNT(*) FROM scholars GROUP BY nationality ORDER BY COUNT(*) DESC") 319 for row in cur.fetchall(): 320 print(f" {row[0]}: {row[1]}") 321 322 conn.close() 323 print("\nDone.") 324 325 326if __name__ == "__main__": 327 main()