database overview

total size 454.6 MB
total tags 48
total documents 164
has full-text-search yes, 121 indexed documents
top 5 tags

database triggers

  1. delete_full_text_trigger

    Trigger on table “DocumentHasBinaryMetadata”

    CREATE TRIGGER delete_full_text_trigger AFTER DELETE ON DocumentHasBinaryMetadata WHEN EXISTS (SELECT * FROM BinaryMetadata AS bm WHERE OLD.metadata_uuid = bm.uuid AND bm.name = 'full-text') BEGIN   DELETE FROM document_title_authors_text_view_fts WHERE uuid = OLD.document_uuid; END

  2. insert_full_text_trigger

    Trigger on table “DocumentHasBinaryMetadata”

    CREATE TRIGGER insert_full_text_trigger AFTER INSERT ON DocumentHasBinaryMetadata WHEN EXISTS (SELECT * FROM BinaryMetadata AS bm WHERE NEW.metadata_uuid = bm.uuid AND bm.name = 'full-text') BEGIN   INSERT INTO document_title_authors_text_view_fts(uuid, title, authors, full_text)   SELECT d.rowid AS rowid, d.title AS title, d.authors AS authors, bm.data AS full_text FROM document_title_authors AS d JOIN BinaryMetadata AS bm ON bm.uuid = NEW.metadata_uuid WHERE d.rowid=NEW.document_uuid AND bm.name = 'full-text' AND bm.uuid = NEW.metadata_uuid; END

SQL schema reference

The bibliothecula schema.
name value
CREATE_BINARYMETADATA CREATE TABLE IF NOT EXISTS "BinaryMetadata"(   "uuid" CHAR(32) NOT NULL PRIMARY KEY,   "name" TEXT NULL,   "data" BLOB NOT NULL,   "created" DATETIME NOT NULL,   "last_modified" DATETIME NOT NULL   );
CREATE_DOCUMENTHASBINARYMETADATA CREATE TABLE IF NOT EXISTS "DocumentHasBinaryMetadata"(   "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,   "name" TEXT NOT NULL,   "document_uuid" CHAR(32) NOT NULL REFERENCES "Documents"("uuid") DEFERRABLE INITIALLY DEFERRED,   "metadata_uuid" CHAR(32) NOT NULL REFERENCES "BinaryMetadata"("uuid") DEFERRABLE INITIALLY DEFERRED,   "created" DATETIME NOT NULL DEFAULT(strftime('%Y-%m-%d %H:%M:%f', 'now')),   "last_modified" DATETIME NOT NULL DEFAULT(strftime('%Y-%m-%d %H:%M:%f', 'now'))   );
CREATE_DOCUMENTHASTEXTMETADATA CREATE TABLE IF NOT EXISTS "DocumentHasTextMetadata"(   "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,   "name" TEXT NOT NULL,   "document_uuid" CHAR(32) NOT NULL REFERENCES "Documents"("uuid") DEFERRABLE INITIALLY DEFERRED,   "metadata_uuid" CHAR(32) NOT NULL REFERENCES "TextMetadata"("uuid") DEFERRABLE INITIALLY DEFERRED,   "created" DATETIME NOT NULL DEFAULT(strftime('%Y-%m-%d %H:%M:%f', 'now')),   "last_modified" DATETIME NOT NULL DEFAULT(strftime('%Y-%m-%d %H:%M:%f', 'now'))   );
CREATE_DOCUMENTS CREATE TABLE IF NOT EXISTS "Documents"(   "uuid" CHAR(32) NOT NULL PRIMARY KEY,   "title" TEXT NOT NULL,   "title_suffix" TEXT,   "created" DATETIME NOT NULL DEFAULT(strftime('%Y-%m-%d %H:%M:%f', 'now')),   "last_modified" DATETIME NOT NULL DEFAULT(strftime('%Y-%m-%d %H:%M:%f', 'now'))   );
CREATE_TEXTMETADATA CREATE TABLE IF NOT EXISTS "TextMetadata"(   "uuid" CHAR(32) NOT NULL PRIMARY KEY,   "name" TEXT NULL,   "data" TEXT NOT NULL,   "created" DATETIME NOT NULL,   "last_modified" DATETIME NOT NULL   );

SQL statements reference

A list of utility SQL statements for use with the bibliothecula schema.
topics name documentation value options
Using the CLI
exampleCLI
 CLI_EDIT_FILE Edit any binary BLOB with the edit() function in the CLI. sqlite3 reference for Documentation on the CLI EDIT function UPDATE BinaryMetadata SET data=edit(data, 'vim') WHERE uuid ='17ee75452e574e03b0b8e4ef2bc9be25';
exampleCLI
 CLI_EXCTRACT_FILE Exctract a binary BLOB from any column using the CLI. sqlite3 reference for Documentation on the CLI file I/O functions SELECT writefile('file.pdf', data) FROM BinaryMetadata WHERE uuid ='17ee75452e574e03b0b8e4ef2bc9be25');
exampleCLI
 CLI_INSERT_FILE The sqlite3 CLI has some special I/O function to facilate reading and writing files. readfile(PATH) will return the bytes read from the path PATH as a BLOB. We also use json_object() to create a new name for the entry; this could also be done with json_replace(). sqlite3 reference for Documentation on the CLI file I/O functions and sqlite3 reference for Documentation on JSON1 extension UPDATE BinaryMetadata SET data=readfile('file.pdf'), name=json_object('content_type', 'application/pdf', 'filename', 'file.pdf', 'size', LENGTH(readfile('file.pdf')) ), last_modified = strftime('%Y-%m-%d %H:%M:%f', 'now') WHERE uuid = '17ee75452e574e03b0b8e4ef2bc9be25';
exampleCLI
 CLI_VIEW_FILE View any binary BLOB with the edit() function in the CLI by ignoring the value it returns. sqlite3 reference for Documentation on the CLI EDIT function SELECT LENGTH(edit(data, 'zathura')) FROM BinaryMetadata WHERE uuid ='17ee75452e574e03b0b8e4ef2bc9be25';
Example queries
topics name documentation value options
query dataexample
 QUERY_BACKREFS_FROM_TEXT_FILES Find backreferences from plain text files. SELECT DISTINCT REPLACE(tok.token, '-', '') AS target, texts.uuid AS referrer FROM uuidtok AS tok, (SELECT uuid, data, json_extract(name, '$.content_type') AS _type FROM BinaryMetadata WHERE json_valid(name) AND _type LIKE "%text/%") AS texts WHERE tok.input=texts.data AND LENGTH(tok.token) = 36 AND EXISTS (SELECT * FROM Documents WHERE uuid = REPLACE(tok.token, '-', ''));
query dataexample
 QUERY_BACKREF_CANDIDATES SELECT DISTINCT token FROM uuidtok WHERE input = (SELECT data FROM BinaryMetadata WHERE uuid = '17ee75452e574e03b0b8e4ef2bc9be25') AND LENGTH(token) = 36;
query dataexample
 QUERY_TEXT_FILES SELECT uuid, name, json_extract(name, '$.content_type') AS _type FROM BinaryMetadata WHERE json_valid(name) AND _type LIKE "%text/%";
query dataexample
 QUERY_UUID_WITH_HYPHENS SELECT * FROM Documents WHERE uuid = REPLACE('7ec63f30-5882-46ac-855d-bdcaf8f29700', '-', '');
query dataexample
 QUERY_VALID_JSON_NAMES SELECT uuid, name FROM BinaryMetadata WHERE json_valid(name);
Indices and searching
topics name documentation value options
indexexample
 CREATE_BACKREF_INDEX Create backref index. CREATE VIRTUAL TABLE backrefs_fts USING fts5(referrer, target);
create tableindex
 CREATE_UUID_TOKENIZER sqlite3 reference CREATE VIRTUAL TABLE IF NOT EXISTS uuidtok USING fts3tokenize('unicode61', "tokenchars=-1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ", "separators= ");
create view
 CREATE_VIEW_DOCUMENTS_TITLE_AUTHORS Auxiliary view for use in document_title_authors_text_view view. Returns document title and a comma separated string with all authors or NULL for all documents. sqlite3 reference for for creating views CREATE VIEW IF NOT EXISTS document_title_authors(rowid, title, authors) AS SELECT * FROM document_title_without_authors UNION SELECT * FROM document_title_with_authors
create view
 CREATE_VIEW_DOCUMENT_TITLE_AUTHORS_TEXT Auxiliary view for use in FTS create trigger. Returns document title, a comma separated string with all authors or NULL if there aren't any. and the full text blob for all documents. sqlite3 reference for for creating views CREATE VIEW IF NOT EXISTS document_title_authors_text_view(rowid, title, authors, full_text, metadata_uuid) AS SELECT v.rowid AS uuid, v.title AS title, v.authors AS authors, bm.data AS full_text, bm.uuid AS metadata_uuid FROM document_title_authors AS v, DocumentHasBinaryMetadata AS dhbm, BinaryMetadata AS bm WHERE bm.uuid = dhbm.metadata_uuid AND v.uuid = dhbm.document_uuid AND dhbm.name = 'full-text' ORDER BY dhbm.last_modified
create view
 CREATE_VIEW_WITHOUT_AUTHORS Auxiliary view for use in document_title_authors view. Returns document title and NULL for all documents that have no author metadata. sqlite3 reference for for creating views CREATE VIEW IF NOT EXISTS document_title_without_authors(rowid, title, authors) AS SELECT d.uuid AS uuid, d.title AS title, NULL AS authors FROM Documents AS d WHERE NOT EXISTS (SELECT * FROM TextMetadata AS t, DocumentHasTextMetadata AS dt WHERE t.name = 'author' AND t.uuid = dt.metadata_uuid AND dt.document_uuid = d.uuid)
create view
 CREATE_VIEW_WITH_AUTHORS Auxiliary view for use in document_title_authors view. Returns document title and a comma separated string with all authors for all documents that have author metadata. sqlite3 reference for for creating views CREATE VIEW IF NOT EXISTS document_title_with_authors(rowid, title, authors) AS SELECT d.uuid AS uuid, d.title AS title, GROUP_CONCAT(authors.author) AS authors FROM Documents AS d, (SELECT d.uuid AS uuid, tm.data AS author FROM Documents AS d, TextMetadata AS tm, DocumentHasTextMetadata AS dhtm WHERE dhtm.document_uuid = d.uuid AND dhtm.metadata_uuid = tm.uuid AND tm.name = 'author') AS authors WHERE authors.uuid = d.uuid GROUP BY d.uuid
indexcreate trigger
 FTS_CREATE_DELETE_TRIGGER Trigger to remove a document's full text from the full text search table when the full-text binary metadata is deleted. sqlite3 reference for for creating triggers CREATE TRIGGER IF NOT EXISTS delete_full_text_trigger AFTER DELETE ON DocumentHasBinaryMetadata WHEN OLD.name = 'full-text' BEGIN   DELETE FROM document_title_authors_text_view_fts   WHERE uuid = OLD.document_uuid; END
indexcreate trigger
 FTS_CREATE_INSERT_TRIGGER Trigger to insert full text data when a DocumentHasBinaryMetadata row for a full-text BinaryMetadata is created. sqlite3 reference for for creating triggers CREATE TRIGGER IF NOT EXISTS insert_full_text_trigger AFTER INSERT ON DocumentHasBinaryMetadata WHEN NEW.name = 'full-text' BEGIN   INSERT INTO document_title_authors_text_view_fts(uuid, title, authors, full_text)   SELECT d.rowid AS rowid, d.title AS title, d.authors AS authors, d.full_text AS full_text   FROM document_title_authors_text_view AS d   WHERE d.metadata_uuid = NEW.metadata_uuid   AND d.rowid=NEW.document_uuid; END
create tableindex
 FTS_CREATE_TABLE Create a full-text search index using the fts5 module. sqlite3 reference CREATE VIRTUAL TABLE IF NOT EXISTS document_title_authors_text_view_fts USING fts5(title, authors, full_text, uuid UNINDEXED)
indexquery data
 FTS_INTEGRITY_CHECK This command is used to verify that the full-text index is internally consistent. sqlite3 reference INSERT INTO document_title_authors_text_view_fts(document_title_authors_text_view_fts) VALUES('integrity-check')
index
 FTS_OPTIMIZE This command merges all individual b-trees that currently make up the full-text index into a single large b-tree structure. Because it reorganizes the entire FTS index, the optimize command can take a long time to run. sqlite3 reference INSERT INTO document_title_authors_text_view_fts(document_title_authors_text_view_fts) VALUES('optimize')
index
 FTS_REBUILD This command first deletes the entire full-text index, then rebuilds it. sqlite3 reference INSERT INTO document_title_authors_text_view_fts(document_title_authors_text_view_fts) VALUES('rebuild')
indexquery data
 FTS_SEARCH This command queries the full-text search index for documents. sqlite3 reference SELECT uuid FROM document_title_authors_text_view_fts('query text')
indexquery data
 FTS_SELECT_CONFIG This command returns the values of persistent configuration parameters. sqlite3 reference SELECT * FROM document_title_authors_text_view_fts_config

Analytical table sizes

Size of each table and index.
Tables
BinaryMetadata 413.8 MB
document_title_authors_text_view_fts_content 29.3 MB
document_title_authors_text_view_fts_data 11.0 MB
DocumentHasTextMetadata 124.0 KB
TextMetadata 76.0 KB
DocumentHasBinaryMetadata 60.0 KB
document_title_authors_text_view_fts_idx 48.0 KB
Documents 32.0 KB
django_migrations 4.0 KB
sqlite_sequence 4.0 KB
django_admin_log 4.0 KB
django_session 4.0 KB
document_title_authors_text_view_fts_docsize 4.0 KB
document_title_authors_text_view_fts_config 4.0 KB
backrefs_fts_data 4.0 KB
backrefs_fts_idx 4.0 KB
backrefs_fts_content 4.0 KB
backrefs_fts_docsize 4.0 KB
backrefs_fts_config 4.0 KB
document_title_authors_text_view_fts 0 bytes
uuidtok 0 bytes
backrefs_fts 0 bytes
tables totals 454.5 MB
Indices
sqlite_autoindex_TextMetadata_1 32.0 KB
sqlite_autoindex_BinaryMetadata_1 28.0 KB
sqlite_autoindex_Documents_1 12.0 KB
django_admin_log_content_type_id_c4bce8eb 4.0 KB
django_admin_log_user_id_c564eba6 4.0 KB
sqlite_autoindex_django_session_1 4.0 KB
django_session_expire_date_a5c62663 4.0 KB
indices totals 88.0 KB
all 454.6 MB