grid view
grid view (click to close)
full-text search on grid view
full-text search on grid view (click to close)
and on table view
and on table view (click to close)
database view
database view (click to close)
d3.js tag visualization
d3.js tag visualization (click to close)
document view
document view (click to close)

bibliothēcula f (genitive bibliothēculae ); first declension (Late Latin)

why

where

https://github.com/epilys/bibliothecula GPLv3

what

schema.{sql,md} and extended-schema.{sql,md}

/* core bibliothecula schema */

/* CREATE_DOCUMENTS */
CREATE TABLE IF NOT EXISTS "Documents" (
        "uuid" CHARACTER(32) NOT NULL PRIMARY KEY,
        "title" TEXT NOT NULL,
        "title_suffix" TEXT DEFAULT NULL, -- disambiguate documents with matching titles
        "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" CHARACTER(32) NOT NULL PRIMARY KEY,
        "name" TEXT NULL,
        "data" TEXT NOT NULL,
        "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_BINARYMETADATA */
CREATE TABLE IF NOT EXISTS "BinaryMetadata" (
        "uuid" CHARACTER(32) NOT NULL PRIMARY KEY,
        "name" TEXT NULL,
        "data" BLOB NOT NULL,
        "compressed" BOOLEAN NOT NULL DEFAULT (0),
        "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" CHARACTER(32) NOT NULL
            REFERENCES "Documents" ("uuid") ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
        "metadata_uuid" CHARACTER(32) NOT NULL
            REFERENCES "TextMetadata" ("uuid") ON DELETE CASCADE 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_DOCUMENTHASBINARYMETADATA */
CREATE TABLE IF NOT EXISTS "DocumentHasBinaryMetadata" (
        "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        "name" TEXT NOT NULL,
        "document_uuid" CHARACTER(32) NOT NULL
            REFERENCES "Documents" ("uuid") ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
        "metadata_uuid" CHARACTER(32) NOT NULL
            REFERENCES "BinaryMetadata" ("uuid") ON DELETE CASCADE 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_VIEW_DOCUMENTS_TITLE_AUTHORS
 Auxiliary view for use in document_title_authors_text_view_fts index.
 Returns document title and a NULL byte separated string with all
 authors or NULL for all documents.
 https://sqlite.org/lang_createview.html sqlite3 reference for for
 creating views */
CREATE VIEW document_title_authors (rowid, title, authors) AS
SELECT uuid, title, authors
FROM
    Documents AS d
    LEFT JOIN (SELECT
            document_uuid,
            GROUP_CONCAT (data, '\0') AS authors
        FROM
            DocumentHasTextMetadata AS dhtm
            JOIN TextMetadata AS tm ON dhtm.metadata_uuid = tm.uuid
        WHERE
            tm.name = 'author'
        GROUP BY
            document_uuid) AS authors ON d.uuid = authors.document_uuid;

/* FTS_CREATE_TABLE
 Create a full-text search index using the fts5 module.
 https://sqlite.org/fts5.html sqlite3 reference */
CREATE VIRTUAL TABLE IF NOT EXISTS document_title_authors_text_view_fts
    USING fts5(title, authors, full_text, uuid UNINDEXED);

/* FTS_CREATE_INSERT_TRIGGER
 Trigger to insert full text data when a DocumentHasBinaryMetadata row
 for a full-text BinaryMetadata is created.
 https://sqlite.org/lang_createtrigger.html sqlite3 reference for for
 creating triggers */
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,
    BinaryMetadata AS bm
WHERE
    d.rowid = NEW.document_uuid
    AND bm.name = 'full-text'
    AND bm.uuid = NEW.metadata_uuid;
END;

/* 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.
 https://sqlite.org/lang_createtrigger.html 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;

stuff

fuse filesystem 

$ ls ~/Documents/biblfs | head
total 393K
drwxr-xr-x  2 epilys epilys     0 Jan  1  1970 ./
drwxrwxrwt 98 root   root    120K Jun 14 19:27 ../
-rw-r--r--  1 epilys epilys  589K Jun 14 00:35 a-a-milne_the-red-house-mystery.epub
-rw-r--r--  1 epilys epilys  450K Jun 14 00:58 aesop_fables_v-s-vernon-jones.epub
-rw-r--r--  1 epilys epilys  601K Jun 14 00:58 agatha-christie_poirot-investigates.epub
-rw-r--r--  1 epilys epilys  594K Jun 14 00:58 agatha-christie_the-man-in-the-brown-suit.epub
-rw-r--r--  1 epilys epilys  523K Jun 14 00:40 agatha-christie_the-murder-on-the-links.epub
-rw-r--r--  1 epilys epilys  780K Jun 14 00:37 agatha-christie_the-mysterious-affair-at-styles.epub
-rw-r--r--  1 epilys epilys  651K Jun 14 00:58 agatha-christie_the-secret-adversary.epub
$ ls "~/Documents/biblfs/tags/Arthurian romances"
total 1.5K
drwxr-xr-x 2 epilys epilys    0 Jan  1  1970 ./
drwxr-xr-x 2 epilys epilys    0 Jan  1  1970 ../
-rw-r--r-- 1 epilys epilys 817K Jun 14 00:35 alfred-lord-tennyson_idylls-of-the-king.epub
-rw-r--r-- 1 epilys epilys 979K Jun 14 00:45 mark-twain_a-connecticut-yankee-in-king-arthurs-court.epub
-rw-r--r-- 1 epilys epilys 1.6M Jun 14 00:39 thomas-malory_le-morte-darthur.epub
$ ls ~/Documents/biblfs/query/journal | head
total 73K
drwxr-xr-x 2 epilys epilys     0 Jan  1  1970 ./
drwxr-xr-x 2 epilys epilys     0 Jan  1  1970 ../
-rw-r--r-- 1 epilys epilys  1.1M Jun 14 00:40 alexander-hamilton_john-jay_james-madison_the-federalist-papers.epub
-rw-r--r-- 1 epilys epilys  679K Jun 14 00:35 alexander-pushkin_eugene-onegin_henry-spalding.epub
-rw-r--r-- 1 epilys epilys  1.7M Jun 14 00:35 alexandre-dumas_the-count-of-monte-cristo_chapman-and-hall.epub
-rw-r--r-- 1 epilys epilys  1.1M Jun 14 00:35 alexandre-dumas_the-three-musketeers_william-robson.epub
-rw-r--r-- 1 epilys epilys  572K Jun 14 00:35 ambrose-bierce_the-devils-dictionary.epub
-rw-r--r-- 1 epilys epilys  664K Jun 14 00:35 anatole-france_penguin-island_a-w-evans.epub
-rw-r--r-- 1 epilys epilys  1.1M Jun 14 00:35 anthony-trollope_the-way-we-live-now.epub

bumblebat web gui

see non-interactive, static demo here

grid view
grid view
full-text search on grid view
full-text search on grid view
and on table view
and on table view
database view
database view
d3.js tag visualization
d3.js tag visualization
document view
document view

shell

manage your database through the powerful python3 or IPython3 interactive shells with convenience wrappers around common database operations including viewing, editing, adding and removing files.

% python3.7 bibl-shell.py bibliothecula.db
    python3 3.7.3 (default, Jan 22 2021, 20:04:44) [GCC 8.3.0]
                                bibliothecula shell 📇 📚 🏷️ 🦇
           (_    ,_,    _)
           / `'--) (--'` \      exported objects:
          /  _,-'\_/'-,_  \      - conn : sqlite3.Connection
         /.-'     "     '-.\     -   db : Database (see NAMESPACE dict
            ______ ______                            for every  import)
          _/      Y      \_       >>> help(db)
         // ~~ ~~ | ~~ ~  \\      >>> help(conn)
        // ~ ~ ~~ | ~~~ ~~ \\     >>> print(LONG_SHELL_BANNER)
       //________.|.________\\    >>> db.stats()
      `----------`-'----------'

    Connected to bibliothecula.db, last modified 2021-06-21 00:07
    >>>