1/** 2 * SQLite Service for Jay Rosen's Internet Archive 3 * 4 * Provides SQL querying capabilities using sql.js (SQLite compiled to WebAssembly) 5 * 6 * Features: 7 * - In-browser SQLite database 8 * - Full SQL query support 9 * - Indexed tables for fast lookups 10 * - Analytics and aggregation queries 11 */ 12 13// sql.js is loaded dynamically to ensure JS and WASM are from the same source 14// The import map version can have WASM binary mismatches 15 16import { IS_LOCAL, BASE_PATH } from '../utils/pathResolver.js?v=3.8.36'; 17import { ERAS } from '../constants.js?v=3.8.36'; 18 19// Self-hosted sql.js WASM binary (#291). The loader JS below is pinned with 20// SRI, but sql.js exposes no SRI hook for the .wasm it fetches via locateFile, 21// so a cdnjs compromise could swap arbitrary WebAssembly. Serving the binary 22// from our own pinned-deploy bundle removes that single-CDN trust. Path mirrors 23// sw.js: locally the frontend dir is served at /frontend; on GH Pages and 24// PressThink the archive is nested under BASE_PATH and frontend sits beneath it. 25const SQL_WASM_PATH = IS_LOCAL 26 ? '/frontend/vendor/sql-wasm-1.10.3.wasm' 27 : `${BASE_PATH}/frontend/vendor/sql-wasm-1.10.3.wasm`; 28 29// Database instance 30let db = null; 31let dbInitializing = false; 32let dbInitPromise = null; 33 34// Track if data has been loaded 35let dataLoaded = false; 36 37// sql.js module cache 38let initSqlJs = null; 39 40/** 41 * Load sql.js dynamically from CDN 42 * This ensures the JS and WASM binary are compatible 43 */ 44const loadSqlJs = async () => { 45 if (initSqlJs) return initSqlJs; 46 47 // Use a script tag to load the UMD bundle which handles WASM loading properly 48 return new Promise((resolve, reject) => { 49 // Check if already loaded globally 50 if (window.initSqlJs) { 51 initSqlJs = window.initSqlJs; 52 resolve(initSqlJs); 53 return; 54 } 55 56 const script = document.createElement('script'); 57 script.src = 'https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.3/sql-wasm.min.js'; 58 // Subresource Integrity: pin the CDN payload so a hijacked or compromised 59 // CDN cannot inject arbitrary JS into the main app. See issue #151. 60 script.integrity = 'sha384-upXEZ8BUrRTJ5jBg9rLYXxwca56l9U3ymkbtFghF69ivmL1U7I9t07Hd1v7g/Pim'; 61 script.crossOrigin = 'anonymous'; 62 script.onload = () => { 63 initSqlJs = window.initSqlJs; 64 resolve(initSqlJs); 65 }; 66 script.onerror = () => reject(new Error('Failed to load sql.js')); 67 document.head.appendChild(script); 68 }); 69}; 70 71/** 72 * Initialize the SQLite database 73 * Loads the WASM binary and creates an empty database 74 */ 75export const initDatabase = async () => { 76 if (db) return db; 77 78 if (dbInitializing) { 79 return dbInitPromise; 80 } 81 82 dbInitializing = true; 83 dbInitPromise = (async () => { 84 console.log('[SQLite] Initializing database...'); 85 86 try { 87 // Load sql.js dynamically 88 const sqlJsInit = await loadSqlJs(); 89 90 // Point sql.js at the self-hosted, version-pinned .wasm (#291) rather 91 // than re-fetching it from cdnjs. The loader JS above is SRI-verified, 92 // but locateFile has no integrity hook, so a CDN-served binary would be 93 // unverified. The committed copy is part of the FTP-deployed bundle and 94 // its bytes are pinned by a SHA-384 regression test. 95 const SQL = await sqlJsInit({ 96 locateFile: () => SQL_WASM_PATH 97 }); 98 99 db = new SQL.Database(); 100 console.log('[SQLite] Database initialized'); 101 102 // Create tables 103 createTables(); 104 105 return db; 106 } catch (error) { 107 console.error('[SQLite] Failed to initialize:', error); 108 dbInitializing = false; 109 throw error; 110 } 111 })(); 112 113 return dbInitPromise; 114}; 115 116/** 117 * Create the database schema 118 */ 119function createTables() { 120 // Records table 121 db.run(` 122 CREATE TABLE IF NOT EXISTS records ( 123 id TEXT PRIMARY KEY, 124 title TEXT, 125 author TEXT, 126 date TEXT, 127 year TEXT, 128 era TEXT, 129 pub TEXT, 130 url TEXT, 131 summary TEXT, 132 quote TEXT, 133 type TEXT, 134 verified INTEGER 135 ) 136 `); 137 138 // Create indexes for common queries 139 db.run('CREATE INDEX IF NOT EXISTS idx_records_year ON records(year)'); 140 db.run('CREATE INDEX IF NOT EXISTS idx_records_era ON records(era)'); 141 db.run('CREATE INDEX IF NOT EXISTS idx_records_type ON records(type)'); 142 db.run('CREATE INDEX IF NOT EXISTS idx_records_date ON records(date)'); 143 db.run('CREATE INDEX IF NOT EXISTS idx_records_pub ON records(pub)'); 144 145 // Categories junction table (many-to-many) 146 db.run(` 147 CREATE TABLE IF NOT EXISTS record_categories ( 148 record_id TEXT, 149 category TEXT, 150 PRIMARY KEY (record_id, category) 151 ) 152 `); 153 db.run('CREATE INDEX IF NOT EXISTS idx_rc_category ON record_categories(category)'); 154 155 // Concepts junction table 156 db.run(` 157 CREATE TABLE IF NOT EXISTS record_concepts ( 158 record_id TEXT, 159 concept TEXT, 160 PRIMARY KEY (record_id, concept) 161 ) 162 `); 163 db.run('CREATE INDEX IF NOT EXISTS idx_rcon_concept ON record_concepts(concept)'); 164 165 // Tags junction table 166 db.run(` 167 CREATE TABLE IF NOT EXISTS record_tags ( 168 record_id TEXT, 169 tag TEXT, 170 PRIMARY KEY (record_id, tag) 171 ) 172 `); 173 db.run('CREATE INDEX IF NOT EXISTS idx_rt_tag ON record_tags(tag)'); 174 175 // Entities table 176 db.run(` 177 CREATE TABLE IF NOT EXISTS entities ( 178 id TEXT PRIMARY KEY, 179 name TEXT, 180 normalized_name TEXT, 181 type TEXT, 182 role TEXT, 183 affiliation TEXT, 184 prominence INTEGER, 185 total_mentions INTEGER 186 ) 187 `); 188 db.run('CREATE INDEX IF NOT EXISTS idx_entities_type ON entities(type)'); 189 db.run('CREATE INDEX IF NOT EXISTS idx_entities_name ON entities(name)'); 190 191 // Record-Entity relationships 192 db.run(` 193 CREATE TABLE IF NOT EXISTS record_entities ( 194 record_id TEXT, 195 entity_id TEXT, 196 PRIMARY KEY (record_id, entity_id) 197 ) 198 `); 199 db.run('CREATE INDEX IF NOT EXISTS idx_re_entity ON record_entities(entity_id)'); 200 db.run('CREATE INDEX IF NOT EXISTS idx_re_record ON record_entities(record_id)'); 201 202 console.log('[SQLite] Tables created'); 203} 204 205/** 206 * Load archive data into the database 207 * @param {Object} data - Archive data object with records, entities, etc. 208 */ 209export const loadArchiveData = async (data) => { 210 if (!db) { 211 await initDatabase(); 212 } 213 214 if (dataLoaded) { 215 console.log('[SQLite] Data already loaded'); 216 return; 217 } 218
219 console.log('[SQLite] Loading archive data...'); 220 const startTime = performance.now(); 221 222 // Use a transaction for faster inserts 223 db.run('BEGIN TRANSACTION'); 224 225 try { 226 // Prepare statements 227 const insertRecord = db.prepare(` 228 INSERT OR REPLACE INTO records VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 229 `); 230 231 const insertCategory = db.prepare(` 232 INSERT OR IGNORE INTO record_categories VALUES (?, ?) 233 `); 234 235 const insertConcept = db.prepare(` 236 INSERT OR IGNORE INTO record_concepts VALUES (?, ?) 237 `); 238 239 const insertTag = db.prepare(` 240 INSERT OR IGNORE INTO record_tags VALUES (?, ?) 241 `); 242 243 // Insert records 244 for (const record of (data.records || [])) { 245 insertRecord.run([ 246 record.id, 247 record.title || '', 248 record.author || 'Jay Rosen', 249 record.date || '', 250 record.year || '', 251 record.era || '', 252 record.pub || '', 253 record.url || '', 254 record.summary || record.summaryPreview || '', 255 record.quote || '', 256 record.type || 'article', 257 record.verified ? 1 : 0 258 ]); 259 260 // Insert categories 261 for (const category of (record.categories || [])) { 262 insertCategory.run([record.id, category]); 263 } 264 265 // Insert concepts 266 for (const concept of (record.concepts || [])) { 267 insertConcept.run([record.id, concept]); 268 } 269 270 // Insert tags 271 for (const tag of (record.tags || [])) { 272 insertTag.run([record.id, tag]); 273 } 274 } 275 276 insertRecord.free(); 277 insertCategory.free(); 278 insertConcept.free(); 279 insertTag.free(); 280 281 // Insert entities if provided 282 if (data.entities && data.entities.length > 0) { 283 const insertEntity = db.prepare(` 284 INSERT OR REPLACE INTO entities VALUES (?, ?, ?, ?, ?, ?, ?, ?) 285 `); 286 287 for (const entity of data.entities) { 288 insertEntity.run([ 289 entity.id, 290 entity.name || '', 291 entity.normalizedName || '', 292 entity.type || '', 293 entity.role || '', 294 entity.affiliation || '', 295 entity.prominence || 0, 296 entity.totalMentions || 1 297 ]); 298 } 299 300 insertEntity.free(); 301 } 302 303 // Insert record-entity relationships 304 // Source 1: explicit recordEntityMap (from archive-entities.json format) 305 // Source 2: relatedIds on each record (from archive-data.json format) 306 const insertRelation = db.prepare(` 307 INSERT OR IGNORE INTO record_entities VALUES (?, ?) 308 `); 309 310 let reCount = 0; 311 312 if (data.recordEntityMap) { 313 for (const [recordId, entityIds] of Object.entries(data.recordEntityMap)) { 314 for (const entityId of entityIds) { 315 insertRelation.run([recordId, entityId]); 316 reCount++; 317 } 318 } 319 } else { 320 // Fall back to relatedIds embedded in each record 321 for (const record of (data.records || [])) { 322 for (const entityId of (record.relatedIds || [])) { 323 insertRelation.run([record.id, entityId]); 324 reCount++; 325 } 326 } 327 } 328 329 insertRelation.free(); 330 console.log(`[SQLite] Loaded ${reCount} record-entity relationships`); 331 332 db.run('COMMIT'); 333 dataLoaded = true; 334 335 const elapsed = performance.now() - startTime; 336 console.log(`[SQLite] Loaded ${data.records?.length || 0} records in ${elapsed.toFixed(0)}ms`); 337 338 } catch (error) { 339 db.run('ROLLBACK'); 340 console.error('[SQLite] Failed to load data:', error); 341 throw error; 342 } 343}; 344 345/** 346 * Execute a raw SQL query 347 * @param {string} sql - SQL query string 348 * @param {Array} params - Query parameters 349 * @returns {Array} Query results 350 */ 351export const query = (sql, params = []) => { 352 if (!db) { 353 throw new Error('Database not initialized. Call initDatabase() first.'); 354 } 355 return db.exec(sql, params); 356}; 357 358/** 359 * Execute a query and return results as objects 360 * @param {string} sql - SQL query string 361 * @param {Array} params - Query parameters 362 * @returns {Array} Array of row objects 363 */ 364export const queryAsObjects = (sql, params = []) => { 365 const results = query(sql, params); 366 if (results.length === 0) return []; 367
368 const columns = results[0].columns; 369 return results[0].values.map(row => { 370 const obj = {}; 371 columns.forEach((col, i) => obj[col] = row[i]); 372 return obj; 373 }); 374}; 375 376// ============================================ 377// ANALYTICS QUERIES 378// ============================================ 379 380/** 381 * Get record count by year 382 */ 383export const getRecordCountByYear = () => { 384 return queryAsObjects(` 385 SELECT year, COUNT(*) as count 386 FROM records 387 WHERE year != '' 388 GROUP BY year 389 ORDER BY year 390 `); 391}; 392 393/** 394 * Get record count by category 395 */ 396export const getRecordCountByCategory = () => { 397 return queryAsObjects(` 398 SELECT category, COUNT(*) as count 399 FROM record_categories 400 GROUP BY category 401 ORDER BY count DESC 402 `); 403}; 404 405/** 406 * Get record count by era 407 */ 408export const getRecordCountByEra = () => { 409 const eraOrderCase = ERAS 410 .map((era, index) => `WHEN '${era.replace(/'/g, "''")}' THEN ${index + 1}`) 411 .join('\n '); 412 413 return queryAsObjects(` 414 SELECT era, COUNT(*) as count 415 FROM records 416 WHERE era != '' 417 GROUP BY era 418 ORDER BY 419 CASE era 420 ${eraOrderCase} 421 ELSE ${ERAS.length + 1} 422 END, 423 era 424 `); 425}; 426 427/** 428 * Get most mentioned entities 429 */ 430export const getMostMentionedEntities = (entityType = null, limit = 20) => { 431 // Use the greater of: junction table count or entity's own totalMentions field. 432 // The totalMentions field is pre-computed from the full dataset and is more 433 // complete than the junction table (which only covers records with relatedIds). 434 let sql = ` 435 SELECT e.name, e.type, 436 MAX(COALESCE(e.total_mentions, 0), COUNT(re.record_id)) as mentions 437 FROM entities e 438 LEFT JOIN record_entities re ON e.id = re.entity_id 439 `; 440 441 const params = []; 442 if (entityType) { 443 sql += ' WHERE e.type = ?'; 444 params.push(entityType); 445 } 446 447 sql += ` 448 GROUP BY e.id 449 ORDER BY mentions DESC 450 LIMIT ? 451 `; 452 params.push(limit); 453 454 return queryAsObjects(sql, params); 455}; 456 457/** 458 * Get most common concepts 459 */ 460export const getMostCommonConcepts = (limit = 20) => { 461 return queryAsObjects(` 462 SELECT concept, COUNT(*) as count 463 FROM record_concepts 464 GROUP BY concept 465 ORDER BY count DESC 466 LIMIT ? 467 `, [limit]); 468}; 469 470/** 471 * Get category co-occurrence (which categories appear together) 472 */ 473export const getCategoryCoOccurrence = (limit = 20) => { 474 return queryAsObjects(` 475 SELECT 476 rc1.category as category1, 477 rc2.category as category2, 478 COUNT(*) as co_occurrences 479 FROM record_categories rc1 480 JOIN record_categories rc2 ON rc1.record_id = rc2.record_id 481 WHERE rc1.category < rc2.category 482 GROUP BY rc1.category, rc2.category 483 ORDER BY co_occurrences DESC 484 LIMIT ? 485 `, [limit]); 486}; 487 488/** 489 * Search records with full-text matching 490 */ 491export const searchRecords = (searchTerm, limit = 50) => { 492 const term = `%${searchTerm}%`; 493 return queryAsObjects(` 494 SELECT * 495 FROM records 496 WHERE title LIKE ? OR summary LIKE ? OR quote LIKE ? 497 ORDER BY date DESC 498 LIMIT ? 499 `, [term, term, term, limit]); 500}; 501 502/** 503 * Check if database is initialized and loaded 504 */ 505export const isReady = () => db !== null && dataLoaded; 506 507/** 508 * Get database statistics 509 */ 510export const getStats = () => { 511 if (!db) return null; 512 513 const recordCount = queryAsObjects('SELECT COUNT(*) as count FROM records')[0]?.count || 0; 514 const categoryCount = queryAsObjects('SELECT COUNT(DISTINCT category) as count FROM record_categories')[0]?.count || 0; 515 const conceptCount = queryAsObjects('SELECT COUNT(DISTINCT concept) as count FROM record_concepts')[0]?.count || 0; 516 const entityCount = queryAsObjects('SELECT COUNT(*) as count FROM entities')[0]?.count || 0; 517 518 return { 519 records: recordCount, 520 categories: categoryCount, 521 concepts: conceptCount, 522 entities: entityCount 523 }; 524};
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.