-- SPDX-License-Identifier: GPL-3.0-or-later PRAGMA user_version=2; PRAGMA application_id=0x56434341; CREATE TABLE metadata(key TEXT PRIMARY KEY,value TEXT NOT NULL) WITHOUT ROWID; CREATE TABLE document( id INTEGER PRIMARY KEY, sha256 TEXT NOT NULL UNIQUE CHECK(length(sha256)=64 AND sha256 NOT GLOB '*[^a-f0-9]*'), title TEXT NOT NULL, category TEXT NOT NULL, collection TEXT NOT NULL, format TEXT NOT NULL, source_url TEXT NOT NULL, member_path TEXT, delivery_url TEXT, byte_size INTEGER NOT NULL CHECK(byte_size > 0), page_count INTEGER NOT NULL CHECK(page_count > 0), issue_year INTEGER CHECK(issue_year BETWEEN 1900 AND 2099), member_offset INTEGER, member_compressed_bytes INTEGER, member_method INTEGER, archive_bytes INTEGER ); CREATE INDEX document_browse ON document(category,collection,title COLLATE NOCASE,sha256); CREATE INDEX document_issue_year ON document(collection,issue_year,title COLLATE NOCASE,sha256) WHERE issue_year IS NOT NULL; CREATE INDEX document_title ON document(title COLLATE NOCASE,sha256); CREATE TABLE page( id INTEGER PRIMARY KEY, document_id INTEGER NOT NULL REFERENCES document(id), page_number INTEGER NOT NULL CHECK(page_number > 0), page_label TEXT, locator_kind TEXT NOT NULL CHECK(locator_kind IN ('pdf_page','worksheet','image','section')), is_ocr INTEGER NOT NULL CHECK(is_ocr IN (0,1)), UNIQUE(document_id,page_number) ); -- Rendered text for complex non-PDF formats. PDF and image OCR stays in the research store. CREATE TABLE reader_section( page_id INTEGER PRIMARY KEY REFERENCES page(id), text TEXT NOT NULL ); -- Stored positions refer only to the synthetic alphabetical term bag, never to source order. CREATE VIRTUAL TABLE page_terms USING fts5( terms,title_terms,content='',tokenize='unicode61 remove_diacritics 2',detail=full,columnsize=1 ); CREATE TABLE reviewed_example( original_sha256 TEXT NOT NULL REFERENCES document(sha256), first_page INTEGER NOT NULL CHECK(first_page > 0), last_page INTEGER NOT NULL CHECK(last_page >= first_page), title TEXT NOT NULL, kind TEXT NOT NULL CHECK(kind IN ('snippet','basic_program','source')), language TEXT NOT NULL, author TEXT NOT NULL, provenance TEXT NOT NULL, source TEXT NOT NULL, source_sha256 TEXT NOT NULL, PRIMARY KEY(original_sha256,first_page,last_page,title) ) WITHOUT ROWID;