basic OPDS (and HTML) catalog provider for eBooks
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 

37 lines
1.5 KiB

--
-- books_authors_link
--
CREATE TABLE books_authors_link ( id INTEGER PRIMARY KEY,
book INTEGER NOT NULL,
author INTEGER NOT NULL,
UNIQUE(book, author)
);
CREATE INDEX books_authors_link_aidx ON books_authors_link (author);
CREATE INDEX books_authors_link_bidx ON books_authors_link (book);
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_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;