641 lines
29 KiB
MySQL
641 lines
29 KiB
MySQL
|
CREATE TABLE authors ( id INTEGER PRIMARY KEY,
|
||
|
|
name TEXT NOT NULL COLLATE NOCASE,
|
||
|
|
sort TEXT COLLATE NOCASE,
|
||
|
|
link TEXT NOT NULL DEFAULT "",
|
||
|
|
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,
|
||
|
|
pubdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
series_index REAL NOT NULL DEFAULT 1.0,
|
||
|
|
author_sort TEXT COLLATE NOCASE,
|
||
|
|
isbn TEXT DEFAULT "" COLLATE NOCASE,
|
||
|
|
lccn TEXT DEFAULT "" COLLATE NOCASE,
|
||
|
|
path TEXT NOT NULL DEFAULT "",
|
||
|
|
flags INTEGER NOT NULL DEFAULT 1,
|
||
|
|
uuid TEXT,
|
||
|
|
has_cover BOOL DEFAULT 0,
|
||
|
|
last_modified TIMESTAMP NOT NULL DEFAULT "2000-01-01 00:00:00+00:00");
|
||
|
|
CREATE TABLE sqlite_sequence(name,seq);
|
||
|
|
CREATE TABLE books_authors_link ( id INTEGER PRIMARY KEY,
|
||
|
|
book INTEGER NOT NULL,
|
||
|
|
author INTEGER NOT NULL,
|
||
|
|
UNIQUE(book, author)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE books_languages_link ( id INTEGER PRIMARY KEY,
|
||
|
|
book INTEGER NOT NULL,
|
||
|
|
lang_code INTEGER NOT NULL,
|
||
|
|
item_order INTEGER NOT NULL DEFAULT 0,
|
||
|
|
UNIQUE(book, lang_code);
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE books_plugin_data(id INTEGER PRIMARY KEY,
|
||
|
|
book INTEGER NON NULL,
|
||
|
|
name TEXT NON NULL,
|
||
|
|
val TEXT NON NULL,
|
||
|
|
UNIQUE(book,name));
|
||
|
|
|
||
|
|
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 custom_columns (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
label TEXT NOT NULL,
|
||
|
|
name TEXT NOT NULL,
|
||
|
|
datatype TEXT NOT NULL,
|
||
|
|
mark_for_delete BOOL DEFAULT 0 NOT NULL,
|
||
|
|
editable BOOL DEFAULT 1 NOT NULL,
|
||
|
|
display TEXT DEFAULT "{}" NOT NULL,
|
||
|
|
is_multiple BOOL DEFAULT 0 NOT NULL,
|
||
|
|
normalized BOOL NOT NULL,
|
||
|
|
UNIQUE(label)
|
||
|
|
);
|
||
|
|
|
||
|
|
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 TABLE feeds ( id INTEGER PRIMARY KEY,
|
||
|
|
title TEXT NOT NULL,
|
||
|
|
script TEXT NOT NULL,
|
||
|
|
UNIQUE(title)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE identifiers ( id INTEGER PRIMARY KEY,
|
||
|
|
book INTEGER NON NULL,
|
||
|
|
type TEXT NON NULL DEFAULT "isbn" COLLATE NOCASE,
|
||
|
|
val TEXT NON NULL COLLATE NOCASE,
|
||
|
|
UNIQUE(book, type)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE languages ( id INTEGER PRIMARY KEY,
|
||
|
|
lang_code TEXT NON NULL COLLATE NOCASE,
|
||
|
|
UNIQUE(lang_code)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE library_id ( id INTEGER PRIMARY KEY,
|
||
|
|
uuid TEXT NOT NULL,
|
||
|
|
UNIQUE(uuid)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE metadata_dirtied(id INTEGER PRIMARY KEY,
|
||
|
|
book INTEGER NOT NULL,
|
||
|
|
UNIQUE(book));
|
||
|
|
|
||
|
|
CREATE TABLE preferences(id INTEGER PRIMARY KEY,
|
||
|
|
key TEXT NON NULL,
|
||
|
|
val TEXT NON NULL,
|
||
|
|
UNIQUE(key));
|
||
|
|
|
||
|
|
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 VIEW meta AS
|
||
|
|
SELECT id, title,
|
||
|
|
(SELECT sortconcat(bal.id, name) FROM books_authors_link AS bal JOIN authors ON(author = authors.id) 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,
|
||
|
|
path,
|
||
|
|
lccn,
|
||
|
|
pubdate,
|
||
|
|
flags,
|
||
|
|
uuid
|
||
|
|
FROM books;
|
||
|
|
CREATE VIEW tag_browser_authors AS SELECT
|
||
|
|
id,
|
||
|
|
name,
|
||
|
|
(SELECT COUNT(id) FROM books_authors_link WHERE author=authors.id) count,
|
||
|
|
(SELECT AVG(ratings.rating)
|
||
|
|
FROM books_authors_link AS tl, books_ratings_link AS bl, ratings
|
||
|
|
WHERE tl.author=authors.id AND bl.book=tl.book AND
|
||
|
|
ratings.id = bl.rating AND ratings.rating <> 0) avg_rating,
|
||
|
|
sort AS sort
|
||
|
|
FROM authors;
|
||
|
|
CREATE VIEW tag_browser_filtered_authors AS SELECT
|
||
|
|
id,
|
||
|
|
name,
|
||
|
|
(SELECT COUNT(books_authors_link.id) FROM books_authors_link WHERE
|
||
|
|
author=authors.id AND books_list_filter(book)) count,
|
||
|
|
(SELECT AVG(ratings.rating)
|
||
|
|
FROM books_authors_link AS tl, books_ratings_link AS bl, ratings
|
||
|
|
WHERE tl.author=authors.id AND bl.book=tl.book AND
|
||
|
|
ratings.id = bl.rating AND ratings.rating <> 0 AND
|
||
|
|
books_list_filter(bl.book)) avg_rating,
|
||
|
|
sort AS sort
|
||
|
|
FROM authors;
|
||
|
|
CREATE VIEW tag_browser_filtered_publishers AS SELECT
|
||
|
|
id,
|
||
|
|
name,
|
||
|
|
(SELECT COUNT(books_publishers_link.id) FROM books_publishers_link WHERE
|
||
|
|
publisher=publishers.id AND books_list_filter(book)) count,
|
||
|
|
(SELECT AVG(ratings.rating)
|
||
|
|
FROM books_publishers_link AS tl, books_ratings_link AS bl, ratings
|
||
|
|
WHERE tl.publisher=publishers.id AND bl.book=tl.book AND
|
||
|
|
ratings.id = bl.rating AND ratings.rating <> 0 AND
|
||
|
|
books_list_filter(bl.book)) avg_rating,
|
||
|
|
name AS sort
|
||
|
|
FROM publishers;
|
||
|
|
CREATE VIEW tag_browser_filtered_ratings AS SELECT
|
||
|
|
id,
|
||
|
|
rating,
|
||
|
|
(SELECT COUNT(books_ratings_link.id) FROM books_ratings_link WHERE
|
||
|
|
rating=ratings.id AND books_list_filter(book)) count,
|
||
|
|
(SELECT AVG(ratings.rating)
|
||
|
|
FROM books_ratings_link AS tl, books_ratings_link AS bl, ratings
|
||
|
|
WHERE tl.rating=ratings.id AND bl.book=tl.book AND
|
||
|
|
ratings.id = bl.rating AND ratings.rating <> 0 AND
|
||
|
|
books_list_filter(bl.book)) avg_rating,
|
||
|
|
rating AS sort
|
||
|
|
FROM ratings;
|
||
|
|
CREATE VIEW tag_browser_filtered_series AS SELECT
|
||
|
|
id,
|
||
|
|
name,
|
||
|
|
(SELECT COUNT(books_series_link.id) FROM books_series_link WHERE
|
||
|
|
series=series.id AND books_list_filter(book)) count,
|
||
|
|
(SELECT AVG(ratings.rating)
|
||
|
|
FROM books_series_link AS tl, books_ratings_link AS bl, ratings
|
||
|
|
WHERE tl.series=series.id AND bl.book=tl.book AND
|
||
|
|
ratings.id = bl.rating AND ratings.rating <> 0 AND
|
||
|
|
books_list_filter(bl.book)) avg_rating,
|
||
|
|
(title_sort(name)) AS sort
|
||
|
|
FROM series;
|
||
|
|
CREATE VIEW tag_browser_filtered_tags AS SELECT
|
||
|
|
id,
|
||
|
|
name,
|
||
|
|
(SELECT COUNT(books_tags_link.id) FROM books_tags_link WHERE
|
||
|
|
tag=tags.id AND books_list_filter(book)) count,
|
||
|
|
(SELECT AVG(ratings.rating)
|
||
|
|
FROM books_tags_link AS tl, books_ratings_link AS bl, ratings
|
||
|
|
WHERE tl.tag=tags.id AND bl.book=tl.book AND
|
||
|
|
ratings.id = bl.rating AND ratings.rating <> 0 AND
|
||
|
|
books_list_filter(bl.book)) avg_rating,
|
||
|
|
name AS sort
|
||
|
|
FROM tags;
|
||
|
|
CREATE VIEW tag_browser_publishers AS SELECT
|
||
|
|
id,
|
||
|
|
name,
|
||
|
|
(SELECT COUNT(id) FROM books_publishers_link WHERE publisher=publishers.id) count,
|
||
|
|
(SELECT AVG(ratings.rating)
|
||
|
|
FROM books_publishers_link AS tl, books_ratings_link AS bl, ratings
|
||
|
|
WHERE tl.publisher=publishers.id AND bl.book=tl.book AND
|
||
|
|
ratings.id = bl.rating AND ratings.rating <> 0) avg_rating,
|
||
|
|
name AS sort
|
||
|
|
FROM publishers;
|
||
|
|
CREATE VIEW tag_browser_ratings AS SELECT
|
||
|
|
id,
|
||
|
|
rating,
|
||
|
|
(SELECT COUNT(id) FROM books_ratings_link WHERE rating=ratings.id) count,
|
||
|
|
(SELECT AVG(ratings.rating)
|
||
|
|
FROM books_ratings_link AS tl, books_ratings_link AS bl, ratings
|
||
|
|
WHERE tl.rating=ratings.id AND bl.book=tl.book AND
|
||
|
|
ratings.id = bl.rating AND ratings.rating <> 0) avg_rating,
|
||
|
|
rating AS sort
|
||
|
|
FROM ratings;
|
||
|
|
CREATE VIEW tag_browser_series AS SELECT
|
||
|
|
id,
|
||
|
|
name,
|
||
|
|
(SELECT COUNT(id) FROM books_series_link WHERE series=series.id) count,
|
||
|
|
(SELECT AVG(ratings.rating)
|
||
|
|
FROM books_series_link AS tl, books_ratings_link AS bl, ratings
|
||
|
|
WHERE tl.series=series.id AND bl.book=tl.book AND
|
||
|
|
ratings.id = bl.rating AND ratings.rating <> 0) avg_rating,
|
||
|
|
(title_sort(name)) AS sort
|
||
|
|
FROM series;
|
||
|
|
CREATE VIEW tag_browser_tags AS SELECT
|
||
|
|
id,
|
||
|
|
name,
|
||
|
|
(SELECT COUNT(id) FROM books_tags_link WHERE tag=tags.id) count,
|
||
|
|
(SELECT AVG(ratings.rating)
|
||
|
|
FROM books_tags_link AS tl, books_ratings_link AS bl, ratings
|
||
|
|
WHERE tl.tag=tags.id AND bl.book=tl.book AND
|
||
|
|
ratings.id = bl.rating AND ratings.rating <> 0) avg_rating,
|
||
|
|
name AS sort
|
||
|
|
FROM tags;
|
||
|
|
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_languages_link_aidx ON books_languages_link (lang_code);
|
||
|
|
CREATE INDEX books_languages_link_bidx ON books_languages_link (book);
|
||
|
|
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 custom_columns_idx ON custom_columns (label);
|
||
|
|
CREATE INDEX data_idx ON data (book);
|
||
|
|
CREATE INDEX formats_idx ON data (format);
|
||
|
|
CREATE INDEX languages_idx ON languages (lang_code COLLATE NOCASE);
|
||
|
|
CREATE INDEX publishers_idx ON publishers (name COLLATE NOCASE);
|
||
|
|
CREATE INDEX series_idx ON series (name 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 books_languages_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;
|
||
|
|
DELETE FROM books_plugin_data WHERE book=OLD.id;
|
||
|
|
DELETE FROM identifiers WHERE book=OLD.id;
|
||
|
|
END;
|
||
|
|
CREATE TRIGGER books_insert_trg AFTER INSERT ON books
|
||
|
|
BEGIN
|
||
|
|
UPDATE books SET sort=title_sort(NEW.title),uuid=uuid4() 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 AND OLD.title <> NEW.title;
|
||
|
|
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_on_authors
|
||
|
|
BEFORE DELETE ON authors
|
||
|
|
BEGIN
|
||
|
|
SELECT CASE
|
||
|
|
WHEN (SELECT COUNT(id) FROM books_authors_link WHERE author=OLD.id) > 0
|
||
|
|
THEN RAISE(ABORT, 'Foreign key violation: authors is still referenced')
|
||
|
|
END;
|
||
|
|
END;
|
||
|
|
CREATE TRIGGER fkc_delete_on_languages
|
||
|
|
BEFORE DELETE ON languages
|
||
|
|
BEGIN
|
||
|
|
SELECT CASE
|
||
|
|
WHEN (SELECT COUNT(id) FROM books_languages_link WHERE lang_code=OLD.id) > 0
|
||
|
|
THEN RAISE(ABORT, 'Foreign key violation: language is still referenced')
|
||
|
|
END;
|
||
|
|
END;
|
||
|
|
CREATE TRIGGER fkc_delete_on_languages_link
|
||
|
|
BEFORE INSERT ON books_languages_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 languages WHERE id=NEW.lang_code) IS NULL
|
||
|
|
THEN RAISE(ABORT, 'Foreign key violation: lang_code not in languages')
|
||
|
|
END;
|
||
|
|
END;
|
||
|
|
CREATE TRIGGER fkc_delete_on_publishers
|
||
|
|
BEFORE DELETE ON publishers
|
||
|
|
BEGIN
|
||
|
|
SELECT CASE
|
||
|
|
WHEN (SELECT COUNT(id) FROM books_publishers_link WHERE publisher=OLD.id) > 0
|
||
|
|
THEN RAISE(ABORT, 'Foreign key violation: publishers is still referenced')
|
||
|
|
END;
|
||
|
|
END;
|
||
|
|
CREATE TRIGGER fkc_delete_on_series
|
||
|
|
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_on_tags
|
||
|
|
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: tags 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_languages_link_a
|
||
|
|
BEFORE UPDATE OF book ON books_languages_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_languages_link_b
|
||
|
|
BEFORE UPDATE OF lang_code ON books_languages_link
|
||
|
|
BEGIN
|
||
|
|
SELECT CASE
|
||
|
|
WHEN (SELECT id from languages WHERE id=NEW.lang_code) IS NULL
|
||
|
|
THEN RAISE(ABORT, 'Foreign key violation: lang_code not in languages')
|
||
|
|
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 TABLE custom_column_1(
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
book INTEGER,
|
||
|
|
value TEXT NOT NULL COLLATE NOCASE,
|
||
|
|
UNIQUE(book));
|
||
|
|
|
||
|
|
CREATE INDEX custom_column_1_idx ON custom_column_1 (book);
|
||
|
|
CREATE TRIGGER fkc_insert_custom_column_1
|
||
|
|
BEFORE INSERT ON custom_column_1
|
||
|
|
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_custom_column_1
|
||
|
|
BEFORE UPDATE OF book ON custom_column_1
|
||
|
|
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 TABLE custom_column_2(
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
book INTEGER,
|
||
|
|
value BOOL NOT NULL ,
|
||
|
|
UNIQUE(book));
|
||
|
|
|
||
|
|
CREATE INDEX custom_column_2_idx ON custom_column_2 (book);
|
||
|
|
CREATE TRIGGER fkc_insert_custom_column_2
|
||
|
|
BEFORE INSERT ON custom_column_2
|
||
|
|
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_custom_column_2
|
||
|
|
BEFORE UPDATE OF book ON custom_column_2
|
||
|
|
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 TABLE custom_column_3(
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
book INTEGER,
|
||
|
|
value BOOL NOT NULL ,
|
||
|
|
UNIQUE(book));
|
||
|
|
|
||
|
|
CREATE INDEX custom_column_3_idx ON custom_column_3 (book)
|
||
|
|
CREATE TRIGGER fkc_insert_custom_column_3
|
||
|
|
BEFORE INSERT ON custom_column_3
|
||
|
|
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_custom_column_3
|
||
|
|
BEFORE UPDATE OF book ON custom_column_3
|
||
|
|
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 series_insert_trg
|
||
|
|
AFTER INSERT ON series
|
||
|
|
BEGIN
|
||
|
|
UPDATE series SET sort=title_sort(NEW.name) WHERE id=NEW.id;
|
||
|
|
END;
|
||
|
|
CREATE TRIGGER series_update_trg
|
||
|
|
AFTER UPDATE ON series
|
||
|
|
BEGIN
|
||
|
|
UPDATE series SET sort=title_sort(NEW.name) WHERE id=NEW.id;
|
||
|
|
END;
|