home *** CD-ROM | disk | FTP | other *** search
- CREATE TABLE authors ( id INTEGER PRIMARY KEY,
- name TEXT NOT NULL COLLATE NOCASE,
- sort TEXT COLLATE NOCASE,
- UNIQUE(name)
- );
- CREATE TABLE books ( id INTEGER PRIMARY KEY AUTOINCREMENT,
- title TEXT NOT NULL DEFAULT 'Unknown' COLLATE NOCASE,
- sort TEXT COLLATE NOCASE,
- timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
- uri TEXT,
- series_index INTEGER NOT NULL DEFAULT 1,
- author_sort TEXT COLLATE NOCASE,
- isbn TEXT DEFAULT "" COLLATE NOCASE,
- path TEXT NOT NULL DEFAULT ""
- );
- CREATE TABLE books_authors_link ( id INTEGER PRIMARY KEY,
- book INTEGER NOT NULL,
- author INTEGER NOT NULL,
- UNIQUE(book, author)
- );
- CREATE TABLE books_publishers_link ( id INTEGER PRIMARY KEY,
- book INTEGER NOT NULL,
- publisher INTEGER NOT NULL,
- UNIQUE(book)
- );
- CREATE TABLE books_ratings_link ( id INTEGER PRIMARY KEY,
- book INTEGER NOT NULL,
- rating INTEGER NOT NULL,
- UNIQUE(book, rating)
- );
- CREATE TABLE books_series_link ( id INTEGER PRIMARY KEY,
- book INTEGER NOT NULL,
- series INTEGER NOT NULL,
- UNIQUE(book)
- );
- CREATE TABLE books_tags_link ( id INTEGER PRIMARY KEY,
- book INTEGER NOT NULL,
- tag INTEGER NOT NULL,
- UNIQUE(book, tag)
- );
- CREATE TABLE comments ( id INTEGER PRIMARY KEY,
- book INTEGER NON NULL,
- text TEXT NON NULL COLLATE NOCASE,
- UNIQUE(book)
- );
- CREATE TABLE conversion_options ( id INTEGER PRIMARY KEY,
- format TEXT NOT NULL COLLATE NOCASE,
- book INTEGER,
- data BLOB NOT NULL,
- UNIQUE(format,book)
- );
- CREATE TABLE feeds ( id INTEGER PRIMARY KEY,
- title TEXT NOT NULL,
- script TEXT NOT NULL,
- UNIQUE(title)
- );
- CREATE TABLE publishers ( id INTEGER PRIMARY KEY,
- name TEXT NOT NULL COLLATE NOCASE,
- sort TEXT COLLATE NOCASE,
- UNIQUE(name)
- );
- CREATE TABLE ratings ( id INTEGER PRIMARY KEY,
- rating INTEGER CHECK(rating > -1 AND rating < 11),
- UNIQUE (rating)
- );
- CREATE TABLE series ( id INTEGER PRIMARY KEY,
- name TEXT NOT NULL COLLATE NOCASE,
- sort TEXT COLLATE NOCASE,
- UNIQUE (name)
- );
- CREATE TABLE tags ( id INTEGER PRIMARY KEY,
- name TEXT NOT NULL COLLATE NOCASE,
- UNIQUE (name)
- );
- CREATE TABLE data ( id INTEGER PRIMARY KEY,
- book INTEGER NON NULL,
- format TEXT NON NULL COLLATE NOCASE,
- uncompressed_size INTEGER NON NULL,
- name TEXT NON NULL,
- UNIQUE(book, format)
- );
-
- CREATE VIEW meta AS
- SELECT id, title,
- (SELECT concat(name) FROM authors WHERE authors.id IN (SELECT author from books_authors_link WHERE book=books.id)) authors,
- (SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,
- (SELECT rating FROM ratings WHERE ratings.id IN (SELECT rating from books_ratings_link WHERE book=books.id)) rating,
- timestamp,
- (SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,
- (SELECT concat(name) FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,
- (SELECT text FROM comments WHERE book=books.id) comments,
- (SELECT name FROM series WHERE series.id IN (SELECT series FROM books_series_link WHERE book=books.id)) series,
- series_index,
- sort,
- author_sort,
- (SELECT concat(format) FROM data WHERE data.book=books.id) formats,
- isbn
- FROM books;
- CREATE INDEX authors_idx ON books (author_sort COLLATE NOCASE);
- CREATE INDEX books_authors_link_aidx ON books_authors_link (author);
- CREATE INDEX books_authors_link_bidx ON books_authors_link (book);
- CREATE INDEX books_idx ON books (sort COLLATE NOCASE);
- CREATE INDEX books_publishers_link_aidx ON books_publishers_link (publisher);
- CREATE INDEX books_publishers_link_bidx ON books_publishers_link (book);
- CREATE INDEX books_ratings_link_aidx ON books_ratings_link (rating);
- CREATE INDEX books_ratings_link_bidx ON books_ratings_link (book);
- CREATE INDEX books_series_link_aidx ON books_series_link (series);
- CREATE INDEX books_series_link_bidx ON books_series_link (book);
- CREATE INDEX books_tags_link_aidx ON books_tags_link (tag);
- CREATE INDEX books_tags_link_bidx ON books_tags_link (book);
- CREATE INDEX comments_idx ON comments (book);
- CREATE INDEX conversion_options_idx_a ON conversion_options (format COLLATE NOCASE);
- CREATE INDEX conversion_options_idx_b ON conversion_options (book);
- CREATE INDEX data_idx ON data (book);
- CREATE INDEX publishers_idx ON publishers (name COLLATE NOCASE);
- CREATE INDEX series_idx ON series (sort COLLATE NOCASE);
- CREATE INDEX tags_idx ON tags (name COLLATE NOCASE);
- CREATE TRIGGER books_delete_trg
- AFTER DELETE ON books
- BEGIN
- DELETE FROM books_authors_link WHERE book=OLD.id;
- DELETE FROM books_publishers_link WHERE book=OLD.id;
- DELETE FROM books_ratings_link WHERE book=OLD.id;
- DELETE FROM books_series_link WHERE book=OLD.id;
- DELETE FROM books_tags_link WHERE book=OLD.id;
- DELETE FROM data WHERE book=OLD.id;
- DELETE FROM comments WHERE book=OLD.id;
- DELETE FROM conversion_options WHERE book=OLD.id;
- END;
- CREATE TRIGGER books_insert_trg
- AFTER INSERT ON books
- BEGIN
- UPDATE books SET sort=title_sort(NEW.title) WHERE id=NEW.id;
- END;
- CREATE TRIGGER books_update_trg
- AFTER UPDATE ON books
- BEGIN
- UPDATE books SET sort=title_sort(NEW.title) WHERE id=NEW.id;
- END;
- CREATE TRIGGER fkc_comments_insert
- BEFORE INSERT ON comments
- BEGIN
- SELECT CASE
- WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: book not in books')
- END;
- END;
- CREATE TRIGGER fkc_comments_update
- BEFORE UPDATE OF book ON comments
- BEGIN
- SELECT CASE
- WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: book not in books')
- END;
- END;
- CREATE TRIGGER fkc_data_insert
- BEFORE INSERT ON data
- BEGIN
- SELECT CASE
- WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: book not in books')
- END;
- END;
- CREATE TRIGGER fkc_data_update
- BEFORE UPDATE OF book ON data
- BEGIN
- SELECT CASE
- WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: book not in books')
- END;
- END;
- CREATE TRIGGER fkc_delete_books_authors_link
- BEFORE DELETE ON authors
- BEGIN
- SELECT CASE
- WHEN (SELECT COUNT(id) FROM books_authors_link WHERE book=OLD.book) > 0
- THEN RAISE(ABORT, 'Foreign key violation: author is still referenced')
- END;
- END;
- CREATE TRIGGER fkc_delete_books_publishers_link
- BEFORE DELETE ON publishers
- BEGIN
- SELECT CASE
- WHEN (SELECT COUNT(id) FROM books_publishers_link WHERE book=OLD.book) > 0
- THEN RAISE(ABORT, 'Foreign key violation: publisher is still referenced')
- END;
- END;
- CREATE TRIGGER fkc_delete_books_series_link
- BEFORE DELETE ON series
- BEGIN
- SELECT CASE
- WHEN (SELECT COUNT(id) FROM books_series_link WHERE series=OLD.id) > 0
- THEN RAISE(ABORT, 'Foreign key violation: series is still referenced')
- END;
- END;
- CREATE TRIGGER fkc_delete_books_tags_link
- BEFORE DELETE ON tags
- BEGIN
- SELECT CASE
- WHEN (SELECT COUNT(id) FROM books_tags_link WHERE tag=OLD.id) > 0
- THEN RAISE(ABORT, 'Foreign key violation: tag is still referenced')
- END;
- END;
- CREATE TRIGGER fkc_insert_books_authors_link
- BEFORE INSERT ON books_authors_link
- BEGIN
- SELECT CASE
- WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: book not in books')
- WHEN (SELECT id from authors WHERE id=NEW.author) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: author not in authors')
- END;
- END;
- CREATE TRIGGER fkc_insert_books_publishers_link
- BEFORE INSERT ON books_publishers_link
- BEGIN
- SELECT CASE
- WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: book not in books')
- WHEN (SELECT id from publishers WHERE id=NEW.publisher) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: publisher not in publishers')
- END;
- END;
- CREATE TRIGGER fkc_insert_books_ratings_link
- BEFORE INSERT ON books_ratings_link
- BEGIN
- SELECT CASE
- WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: book not in books')
- WHEN (SELECT id from ratings WHERE id=NEW.rating) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: rating not in ratings')
- END;
- END;
- CREATE TRIGGER fkc_insert_books_series_link
- BEFORE INSERT ON books_series_link
- BEGIN
- SELECT CASE
- WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: book not in books')
- WHEN (SELECT id from series WHERE id=NEW.series) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: series not in series')
- END;
- END;
- CREATE TRIGGER fkc_insert_books_tags_link
- BEFORE INSERT ON books_tags_link
- BEGIN
- SELECT CASE
- WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: book not in books')
- WHEN (SELECT id from tags WHERE id=NEW.tag) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: tag not in tags')
- END;
- END;
- CREATE TRIGGER fkc_update_books_authors_link_a
- BEFORE UPDATE OF book ON books_authors_link
- BEGIN
- SELECT CASE
- WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: book not in books')
- END;
- END;
- CREATE TRIGGER fkc_update_books_authors_link_b
- BEFORE UPDATE OF author ON books_authors_link
- BEGIN
- SELECT CASE
- WHEN (SELECT id from authors WHERE id=NEW.author) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: author not in authors')
- END;
- END;
- CREATE TRIGGER fkc_update_books_publishers_link_a
- BEFORE UPDATE OF book ON books_publishers_link
- BEGIN
- SELECT CASE
- WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: book not in books')
- END;
- END;
- CREATE TRIGGER fkc_update_books_publishers_link_b
- BEFORE UPDATE OF publisher ON books_publishers_link
- BEGIN
- SELECT CASE
- WHEN (SELECT id from publishers WHERE id=NEW.publisher) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: publisher not in publishers')
- END;
- END;
- CREATE TRIGGER fkc_update_books_ratings_link_a
- BEFORE UPDATE OF book ON books_ratings_link
- BEGIN
- SELECT CASE
- WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: book not in books')
- END;
- END;
- CREATE TRIGGER fkc_update_books_ratings_link_b
- BEFORE UPDATE OF rating ON books_ratings_link
- BEGIN
- SELECT CASE
- WHEN (SELECT id from ratings WHERE id=NEW.rating) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: rating not in ratings')
- END;
- END;
- CREATE TRIGGER fkc_update_books_series_link_a
- BEFORE UPDATE OF book ON books_series_link
- BEGIN
- SELECT CASE
- WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: book not in books')
- END;
- END;
- CREATE TRIGGER fkc_update_books_series_link_b
- BEFORE UPDATE OF series ON books_series_link
- BEGIN
- SELECT CASE
- WHEN (SELECT id from series WHERE id=NEW.series) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: series not in series')
- END;
- END;
- CREATE TRIGGER fkc_update_books_tags_link_a
- BEFORE UPDATE OF book ON books_tags_link
- BEGIN
- SELECT CASE
- WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: book not in books')
- END;
- END;
- CREATE TRIGGER fkc_update_books_tags_link_b
- BEFORE UPDATE OF tag ON books_tags_link
- BEGIN
- SELECT CASE
- WHEN (SELECT id from tags WHERE id=NEW.tag) IS NULL
- THEN RAISE(ABORT, 'Foreign key violation: tag not in tags')
- END;
- END;
- CREATE TRIGGER series_insert_trg
- AFTER INSERT ON series
- BEGIN
- UPDATE series SET sort=NEW.name WHERE id=NEW.id;
- END;
- CREATE TRIGGER series_update_trg
- AFTER UPDATE ON series
- BEGIN
- UPDATE series SET sort=NEW.name WHERE id=NEW.id;
- END;
-