1/** 2 * Query Builder Component 3 * 4 * A "mad libs" style interface that lets users construct database queries 5 * using plain English sentences with dropdowns and text inputs. 6 * Abstracts SQL syntax for non-technical users. 7 */ 8 9import { useState, useEffect } from 'react'; 10import { html } from '../html.js?v=3.8.36'; 11import { 12 Play, 13 RotateCcw, 14 Sparkles, 15 HelpCircle, 16 Loader2 17} from 'lucide-react'; 18import { queryAsObjects, isSqliteReady, initSqlite } from '../services/archiveService.js?v=3.8.36'; 19import { ERAS } from '../constants.js?v=3.8.36'; 20import { 21 extractRecordIds, 22 templateIsComposable, 23 resolveFieldValues, 24} from '../services/queryComposition.js?v=3.8.36'; 25 26const eraOrderCase = ERAS 27 .map((era, index) => `WHEN '${era.replace(/'/g, "''")}' THEN ${index + 1}`) 28 .join('\n '); 29 30// Query template definitions 31const QUERY_TEMPLATES = [ 32 { 33 id: 'count-by-field', 34 composable: false, 35 name: 'Count records', 36 sentence: ['Count all records grouped by', 'FIELD', 'showing the top', 'LIMIT', 'results'], 37 fields: { 38 FIELD: { 39 type: 'dropdown', 40 options: [ 41 { label: 'year', value: 'year' }, 42 { label: 'era', value: 'era' }, 43 { label: 'publication', value: 'pub' }, 44 { label: 'type (article/social)', value: 'type' } 45 ], 46 default: 'year' 47 }, 48 LIMIT: { 49 type: 'number', 50 default: 10, 51 min: 1, 52 max: 100 53 } 54 }, 55 buildSql: (values) => ` 56 SELECT ${values.FIELD}, COUNT(*) as count 57 FROM records 58 WHERE ${values.FIELD} != '' 59 GROUP BY ${values.FIELD} 60 ORDER BY count DESC 61 LIMIT ${values.LIMIT} 62 ` 63 }, 64 { 65 id: 'search-titles', 66 composable: true, 67 name: 'Search titles', 68 sentence: ['Find records where the title contains', 'SEARCH_TERM', 'limited to', 'LIMIT', 'results'], 69 fields: { 70 SEARCH_TERM: { 71 type: 'text', 72 placeholder: 'enter search term...', 73 default: '' 74 }, 75 LIMIT: { 76 type: 'number', 77 default: 20, 78 min: 1, 79 max: 100 80 } 81 }, 82 buildSql: (values) => ` 83 SELECT id, title, date, pub 84 FROM records 85 WHERE title LIKE '%${values.SEARCH_TERM.replace(/'/g, "''")}%' 86 ORDER BY date DESC 87 LIMIT ${values.LIMIT} 88 ` 89 }, 90 { 91 id: 'records-by-year', 92 composable: true, 93 name: 'Records from year', 94 sentence: ['Show me all records from the year', 'YEAR', 'limited to', 'LIMIT', 'results'], 95 fields: { 96 YEAR: { 97 type: 'dropdown', 98 options: Array.from({ length: 40 }, (_, i) => { 99 const year = 2025 - i; 100 return { label: String(year), value: String(year) }; 101 }), 102 default: '2024' 103 }, 104 LIMIT: { 105 type: 'number', 106 default: 25, 107 min: 1, 108 max: 200 109 } 110 }, 111 buildSql: (values) => ` 112 SELECT id, title, date, pub, type 113 FROM records 114 WHERE year = '${values.YEAR}' 115 ORDER BY date DESC 116 LIMIT ${values.LIMIT} 117 ` 118 }, 119 { 120 id: 'records-by-era', 121 composable: true, 122 name: 'Records by era', 123 sentence: ['Show records from the', 'ERA', 'era, limited to', 'LIMIT', 'results'], 124 fields: { 125 ERA: { 126 type: 'dropdown', 127 options: ERAS.map(era => ({ label: era, value: era })), 128 default: ERAS[ERAS.length - 1] 129 }, 130 LIMIT: { 131 type: 'number', 132 default: 25, 133 min: 1, 134 max: 200 135 } 136 }, 137 buildSql: (values) => ` 138 SELECT id, title, date, pub, year 139 FROM records 140 WHERE era = '${values.ERA}' 141 ORDER BY date DESC 142 LIMIT ${values.LIMIT} 143 ` 144 }, 145 { 146 id: 'top-categories', 147 composable: false, 148 name: 'Top categories', 149 sentence: ['Show the top', 'LIMIT', 'categories by number of records'], 150 fields: { 151 LIMIT: { 152 type: 'number', 153 default: 15, 154 min: 1, 155 max: 50 156 } 157 }, 158 buildSql: (values) => ` 159 SELECT category, COUNT(*) as record_count 160 FROM record_categories 161 GROUP BY category 162 ORDER BY record_count DESC 163 LIMIT ${values.LIMIT} 164 ` 165 }, 166 { 167 id: 'records-by-category', 168 composable: true, 169 name: 'Records in category', 170 sentence: ['Find records in the', 'CATEGORY', 'category, showing', 'LIMIT', 'results'], 171 fields: { 172 CATEGORY: { 173 type: 'text', 174 placeholder: 'enter category name...', 175 default: 'Press criticism' 176 }, 177 LIMIT: { 178 type: 'number', 179 default: 20, 180 min: 1, 181 max: 100 182 } 183 }, 184 buildSql: (values) => ` 185 SELECT DISTINCT r.id, r.title, r.date, r.pub 186 FROM records r 187 JOIN record_categories rc ON r.id = rc.record_id 188 WHERE rc.category LIKE '%${values.CATEGORY.replace(/'/g, "''")}%' 189 ORDER BY r.date DESC 190 LIMIT ${values.LIMIT} 191 ` 192 }, 193 { 194 id: 'top-publications', 195 composable: false, 196 name: 'Top publications', 197 sentence: ['Show the top', 'LIMIT', 'publications Jay has written for'], 198 fields: { 199 LIMIT: { 200 type: 'number', 201 default: 15, 202 min: 1, 203 max: 50 204 } 205 }, 206 buildSql: (values) => ` 207 SELECT pub as publication, COUNT(*) as articles 208 FROM records 209 WHERE pub != '' 210 GROUP BY pub 211 ORDER BY articles DESC 212 LIMIT ${values.LIMIT} 213 ` 214 }, 215 { 216 id: 'top-people', 217 composable: false, 218 name: 'Most mentioned people', 219 sentence: ['Show the top', 'LIMIT', 'most frequently mentioned people'], 220 fields: { 221 LIMIT: { 222 type: 'number', 223 default: 15, 224 min: 1, 225 max: 50 226 } 227 }, 228 buildSql: (values) => ` 229 SELECT e.name, COUNT(re.record_id) as mentions 230 FROM entities e 231 JOIN record_entities re ON e.id = re.entity_id 232 WHERE e.type = 'Person' 233 GROUP BY e.id 234 ORDER BY mentions DESC 235 LIMIT ${values.LIMIT} 236 ` 237 }, 238 { 239 id: 'top-concepts', 240 composable: false, 241 name: 'Most common concepts', 242 sentence: ['Show the top', 'LIMIT', 'most frequently discussed concepts'], 243 fields: { 244 LIMIT: { 245 type: 'number', 246 default: 15, 247 min: 1, 248 max: 50 249 } 250 }, 251 buildSql: (values) => ` 252 SELECT concept, COUNT(*) as occurrences 253 FROM record_concepts 254 GROUP BY concept 255 ORDER BY occurrences DESC 256 LIMIT ${values.LIMIT} 257 ` 258 }, 259 { 260 id: 'records-mentioning-person', 261 composable: true, 262 name: 'Records mentioning person', 263 sentence: ['Find records that mention', 'PERSON_NAME', 'limited to', 'LIMIT', 'results'], 264 fields: { 265 PERSON_NAME: { 266 type: 'text', 267 placeholder: 'enter person name...', 268 default: 'Trump' 269 }, 270 LIMIT: { 271 type: 'number', 272 default: 20, 273 min: 1, 274 max: 100 275 } 276 }, 277 buildSql: (values) => ` 278 SELECT DISTINCT r.id, r.title, r.date, r.pub 279 FROM records r 280 JOIN record_entities re ON r.id = re.record_id 281 JOIN entities e ON re.entity_id = e.id 282 WHERE e.name LIKE '%${values.PERSON_NAME.replace(/'/g, "''")}%' 283 ORDER BY r.date DESC 284 LIMIT ${values.LIMIT} 285 ` 286 }, 287 { 288 id: 'yearly-output', 289 composable: false, 290 name: 'Yearly output', 291 sentence: ['Show how many', 'TYPE', 'Jay produced each year'], 292 fields: { 293 TYPE: { 294 type: 'dropdown', 295 options: [ 296 { label: 'total records', value: 'all' }, 297 { label: 'articles', value: 'article' }, 298 { label: 'social posts', value: 'social' }, 299 { label: 'videos', value: 'video' } 300 ], 301 default: 'all' 302 } 303 }, 304 buildSql: (values) => { 305 const whereClause = values.TYPE === 'all' ? '' : `AND type = '${values.TYPE}'`; 306 return ` 307 SELECT year, COUNT(*) as count 308 FROM records 309 WHERE year != '' ${whereClause} 310 GROUP BY year 311 ORDER BY year 312 `; 313 } 314 }, 315 { 316 id: 'compare-eras', 317 composable: false, 318 name: 'Compare eras', 319 sentence: ['Compare the number of records across all eras'], 320 fields: {}, 321 buildSql: () => ` 322 SELECT 323 era, 324 COUNT(*) as total_records, 325 COUNT(DISTINCT pub) as unique_publications 326 FROM records 327 WHERE era != '' 328 GROUP BY era 329 ORDER BY 330 CASE era 331 ${eraOrderCase} 332 ELSE ${ERAS.length + 1} 333 END, 334 era 335 ` 336 }, 337 { 338 id: 'category-cooccurrence', 339 composable: false, 340 name: 'Related categories', 341 sentence: ['Show categories that often appear together, top', 'LIMIT', 'pairs'], 342 fields: { 343 LIMIT: { 344 type: 'number', 345 default: 10, 346 min: 1, 347 max: 30 348 } 349 }, 350 buildSql: (values) => ` 351 SELECT
352 rc1.category as category_1, 353 rc2.category as category_2, 354 COUNT(*) as times_together 355 FROM record_categories rc1 356 JOIN record_categories rc2 ON rc1.record_id = rc2.record_id 357 WHERE rc1.category < rc2.category 358 GROUP BY rc1.category, rc2.category 359 ORDER BY times_together DESC 360 LIMIT ${values.LIMIT} 361 ` 362 } 363]; 364 365// Dropdown component 366const Dropdown = ({ options, value, onChange, label }) => { 367 return html` 368 <select 369 aria-label=${label} 370 value=${value} 371 onChange=${(e) => onChange(e.target.value)} 372 className="archive-query-field archive-query-field--dropdown" 373 style=${{ minWidth: '140px' }} 374 > 375 ${options.map(opt => html` 376 <option key=${opt.value} value=${opt.value}>${opt.label}</option> 377 `)} 378 </select> 379 `; 380}; 381 382// Number input component 383const NumberInput = ({ value, onChange, min, max, label }) => { 384 return html` 385 <input 386 aria-label=${label} 387 type="number" 388 value=${value} 389 onChange=${(e) => onChange(parseInt(e.target.value) || min)} 390 min=${min} 391 max=${max} 392 className="archive-query-field archive-query-field--number" 393 /> 394 `; 395}; 396 397// Text input component 398const TextInput = ({ value, onChange, placeholder, label }) => { 399 return html` 400 <input 401 aria-label=${label} 402 type="text" 403 value=${value} 404 onChange=${(e) => onChange(e.target.value)} 405 placeholder=${placeholder} 406 className="archive-query-field archive-query-field--text" 407 style=${{ minWidth: '160px' }} 408 /> 409 `; 410}; 411 412// Query sentence renderer 413const QuerySentence = ({ template, values, onChange }) => { 414 const fieldLabels = { 415 FIELD: 'Group records by', 416 LIMIT: 'Result limit', 417 SEARCH_TERM: 'Title search term', 418 YEAR: 'Year', 419 ERA: 'Era', 420 CATEGORY: 'Category', 421 PERSON_NAME: 'Person name', 422 TYPE: 'Record type', 423 }; 424 425 return html` 426 <div className="archive-query-sentence__line"> 427 ${template.sentence.map((part, index) => { 428 // Check if this part is a field placeholder 429 if (template.fields[part]) { 430 const field = template.fields[part]; 431 const currentValue = values[part] ?? field.default; 432 433 if (field.type === 'dropdown') { 434 return html`<${Dropdown} 435 key=${index} 436 options=${field.options} 437 value=${currentValue} 438 label=${fieldLabels[part] || part} 439 onChange=${(val) => onChange(part, val)} 440 />`; 441 } else if (field.type === 'number') { 442 return html`<${NumberInput} 443 key=${index} 444 value=${currentValue} 445 onChange=${(val) => onChange(part, val)} 446 min=${field.min} 447 max=${field.max} 448 label=${fieldLabels[part] || part} 449 />`; 450 } else if (field.type === 'text') { 451 return html`<${TextInput} 452 key=${index} 453 value=${currentValue} 454 onChange=${(val) => onChange(part, val)} 455 placeholder=${field.placeholder} 456 label=${fieldLabels[part] || part} 457 />`; 458 } 459 } 460 // Regular text 461 return html`<span key=${index}>${part}</span>`; 462 })} 463 </div> 464 `; 465}; 466 467// Results table component 468const ResultsTable = ({ results }) => { 469 if (!results || results.length === 0) { 470 return html`<p className="archive-query-empty">No results found</p>`; 471 } 472 473 const columns = Object.keys(results[0]); 474 475 return html` 476 <div className="archive-data-scroll" role="region" tabIndex="0" aria-label="Query results"> 477 <table className="archive-data-table"> 478 <thead> 479 <tr> 480 ${columns.map(col => html` 481 <th key=${col} scope="col"> 482 ${col.replace(/_/g, ' ')} 483 </th> 484 `)} 485 </tr> 486 </thead> 487 <tbody> 488 ${results.map((row, i) => html` 489 <tr key=${i}> 490 ${columns.map(col => html` 491 <td key=${col}> 492 ${row[col] ?? 'â'} 493 </td> 494 `)} 495 </tr> 496 `)} 497 </tbody> 498 </table> 499 </div> 500 `; 501}; 502 503// Main QueryBuilder component 504const QueryBuilder = ({ onRecordResults }) => { 505 const [selectedTemplateId, setSelectedTemplateId] = useState(QUERY_TEMPLATES[0].id); 506 const [fieldValues, setFieldValues] = useState({}); 507 const [results, setResults] = useState(null); 508 const [error, setError] = useState(null); 509 const [showSql, setShowSql] = useState(false); 510 const [resultCount, setResultCount] = useState(0); 511 // SQLite loads on first query rather than on dashboard mount (issue #338). 512 const [dbLoading, setDbLoading] = useState(false); 513 514 const selectedTemplate = QUERY_TEMPLATES.find(t => t.id === selectedTemplateId); 515 516 // Initialize field values when the template changes. resolveFieldValues with an 517 // empty source yields each declared field's default, the same resolution the two 518 // buildSql sites use, so init, reset, and build never drift from one definition. 519 useEffect(() => { 520 if (selectedTemplate) { 521 setFieldValues(resolveFieldValues(selectedTemplate, {})); 522 setResults(null); 523 setError(null); 524 } 525 }, [selectedTemplateId]); 526 527 const handleFieldChange = (fieldName, value) => { 528 setFieldValues(prev => ({ ...prev, [fieldName]: value })); 529 }; 530 531 const runQuery = async () => { 532 try { 533 // Load SQLite on demand. The dashboard no longer initializes it on mount, 534 // so the first query in either query surface pays the one-time load. 535 if (!isSqliteReady()) { 536 setDbLoading(true); 537 const ready = await initSqlite(); 538 setDbLoading(false); 539 if (!ready) { 540 setError('Could not load the query database. Please try again.'); 541 setResults(null); 542 setResultCount(0); 543 return; 544 } 545 } 546 547 const sql = selectedTemplate.buildSql(resolveFieldValues(selectedTemplate, fieldValues)); 548 const queryResults = queryAsObjects(sql); 549 550 if (templateIsComposable(selectedTemplate)) { 551 setResults(null); 552 setResultCount(queryResults.length); 553 setError(null); 554 onRecordResults(extractRecordIds(queryResults)); 555 return; 556 } 557 558 setResults(queryResults); 559 setResultCount(queryResults.length); 560 setError(null); 561 } catch (err) { 562 setDbLoading(false); 563 setError(err.message); 564 setResults(null); 565 setResultCount(0); 566 } 567 }; 568 569 const resetQuery = () => { 570 setFieldValues(resolveFieldValues(selectedTemplate, {})); 571 setResults(null); 572 setError(null); 573 setResultCount(0); 574 }; 575 576 const currentSql = selectedTemplate ? selectedTemplate.buildSql(resolveFieldValues(selectedTemplate, fieldValues)) : ''; 577 578 return html` 579 <div className="archive-query-builder"> 580 <!-- Template Selector --> 581 <div className="archive-query-template"> 582 <label htmlFor="query-template"> 583 I want to: 584 </label> 585 <select 586 id="query-template" 587 value=${selectedTemplateId} 588 onChange=${(e) => setSelectedTemplateId(e.target.value)} 589 className="archive-control" 590 > 591 ${QUERY_TEMPLATES.map(template => html` 592 <option key=${template.id} value=${template.id}>${template.name}</option> 593 `)} 594 </select> 595 </div> 596 597 <!-- Query Sentence Builder --> 598 <div className="archive-query-sentence"> 599 <div className="archive-query-sentence__instruction"> 600 <${Sparkles} aria-hidden="true" /> 601 <p> 602 Complete the sentence below by choosing options or entering values. 603 Ruled fields are interactive. 604 </p> 605 </div> 606 607 ${selectedTemplate && html` 608 <${QuerySentence} 609 template=${selectedTemplate} 610 values=${fieldValues} 611 onChange=${handleFieldChange} 612 /> 613 `} 614 615 <!-- Action Buttons --> 616 <div className="archive-query-actions"> 617 <button 618 type="button" 619 onClick=${runQuery} 620 disabled=${dbLoading} 621 className="archive-action archive-action--primary" 622 > 623 ${dbLoading 624 ? html`<${Loader2} className="w-4 h-4 animate-spin" /> Loading database...` 625 : html`<${Play} className="w-4 h-4" /> Run query`} 626 </button> 627 <button 628 type="button" 629 onClick=${resetQuery} 630 className="archive-action archive-action--secondary" 631 > 632 <${RotateCcw} className="w-4 h-4" /> 633 Reset 634 </button> 635 <button 636 type="button" 637 onClick=${() => setShowSql(!showSql)} 638 className="archive-action archive-action--quiet" 639 > 640 <${HelpCircle} className="w-4 h-4" /> 641 ${showSql ? 'Hide' : 'Show'} SQL 642 </button> 643 </div> 644 645 <!-- SQL Preview (collapsible) --> 646 ${showSql && html` 647 <div className="archive-query-sql-preview archive-data-scroll" role="region" tabIndex="0" aria-label="Generated SQL"> 648 <pre>${currentSql.trim()}</pre> 649 </div> 650 `} 651 </div> 652 653 <!-- Results --> 654 ${error && html` 655 <div className="archive-notice archive-notice--danger archive-query-error" role="alert"> 656 <p><strong>Error:</strong> ${error}</p> 657 </div> 658 `} 659 660 ${results && html` 661 <div className="archive-data-panel archive-query-results"> 662 <div className="archive-query-results__header"> 663 <h4>Results</h4>
664 <span>${resultCount} ${resultCount === 1 ? 'row' : 'rows'} returned</span> 665 </div> 666 <div className="archive-query-results__table"> 667 <${ResultsTable} results=${results} /> 668 </div> 669 </div> 670 `} 671 672 <!-- Color Legend --> 673 <div className="archive-query-legend" aria-label="Query field legend"> 674 <div> 675 <span className="archive-query-legend__swatch archive-query-legend__swatch--dropdown" aria-hidden="true"></span> 676 <span>Dropdown choice</span> 677 </div> 678 <div> 679 <span className="archive-query-legend__swatch archive-query-legend__swatch--number" aria-hidden="true"></span> 680 <span>Number</span> 681 </div> 682 <div> 683 <span className="archive-query-legend__swatch archive-query-legend__swatch--text" aria-hidden="true"></span> 684 <span>Text search</span> 685 </div> 686 </div> 687 </div> 688 `; 689}; 690 691export default QueryBuilder;
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.