NotAShelf
bda36ac152
Signed-off-by: NotAShelf <raf@notashelf.dev> Change-Id: I379005c29a79a637a8e1fc3709907cd36a6a6964
54 lines
2.2 KiB
SQL
54 lines
2.2 KiB
SQL
-- V12: Book Management Schema
|
|
-- Adds comprehensive book metadata tracking, authors, and identifiers
|
|
|
|
-- Book metadata (supplements media_items for EPUB/PDF/MOBI)
|
|
CREATE TABLE book_metadata (
|
|
media_id TEXT PRIMARY KEY REFERENCES media_items(id) ON DELETE CASCADE,
|
|
isbn TEXT,
|
|
isbn13 TEXT, -- Normalized ISBN-13 for lookups
|
|
publisher TEXT,
|
|
language TEXT, -- ISO 639-1 code
|
|
page_count INTEGER,
|
|
publication_date TEXT, -- ISO 8601 date string
|
|
series_name TEXT,
|
|
series_index REAL, -- Supports 1.5, etc.
|
|
format TEXT, -- 'epub', 'pdf', 'mobi', 'azw3'
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
) STRICT;
|
|
|
|
CREATE INDEX idx_book_isbn13 ON book_metadata(isbn13);
|
|
CREATE INDEX idx_book_series ON book_metadata(series_name, series_index);
|
|
CREATE INDEX idx_book_publisher ON book_metadata(publisher);
|
|
CREATE INDEX idx_book_language ON book_metadata(language);
|
|
|
|
-- Multiple authors per book (many-to-many)
|
|
CREATE TABLE book_authors (
|
|
media_id TEXT NOT NULL REFERENCES media_items(id) ON DELETE CASCADE,
|
|
author_name TEXT NOT NULL,
|
|
author_sort TEXT, -- "Last, First" for sorting
|
|
role TEXT NOT NULL DEFAULT 'author', -- author, translator, editor, illustrator
|
|
position INTEGER NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (media_id, author_name, role)
|
|
) STRICT;
|
|
|
|
CREATE INDEX idx_book_authors_name ON book_authors(author_name);
|
|
CREATE INDEX idx_book_authors_sort ON book_authors(author_sort);
|
|
|
|
-- Multiple identifiers (ISBN variants, ASIN, DOI, etc.)
|
|
CREATE TABLE book_identifiers (
|
|
media_id TEXT NOT NULL REFERENCES media_items(id) ON DELETE CASCADE,
|
|
identifier_type TEXT NOT NULL, -- isbn, isbn13, asin, doi, lccn, oclc
|
|
identifier_value TEXT NOT NULL,
|
|
PRIMARY KEY (media_id, identifier_type, identifier_value)
|
|
) STRICT;
|
|
|
|
CREATE INDEX idx_book_identifiers ON book_identifiers(identifier_type, identifier_value);
|
|
|
|
-- Trigger to update updated_at on book_metadata changes
|
|
CREATE TRIGGER update_book_metadata_timestamp
|
|
AFTER UPDATE ON book_metadata
|
|
FOR EACH ROW
|
|
BEGIN
|
|
UPDATE book_metadata SET updated_at = datetime('now') WHERE media_id = NEW.media_id;
|
|
END;
|