Debian Codegraph @

resources/metadata_sqlite.sql

raw
1 CREATE TABLE authors ( id INTEGER PRIMARY KEY,
2 name TEXT NOT NULL COLLATE NOCASE,
3 sort TEXT COLLATE NOCASE,
4 link TEXT NOT NULL DEFAULT '',
5 UNIQUE(name)
6 );
7 CREATE TABLE books ( id INTEGER PRIMARY KEY AUTOINCREMENT,
8 title TEXT NOT NULL DEFAULT 'Unknown' COLLATE NOCASE,
9 sort TEXT COLLATE NOCASE,
10 timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
11 pubdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
12 series_index REAL NOT NULL DEFAULT 1.0,
13 author_sort TEXT COLLATE NOCASE,
14 path TEXT NOT NULL DEFAULT '',
15 uuid TEXT,
16 has_cover BOOL DEFAULT 0,
17 last_modified TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:00+00:00');
18 CREATE TABLE books_authors_link ( id INTEGER PRIMARY KEY,
19 book INTEGER NOT NULL,
20 author INTEGER NOT NULL,
21 UNIQUE(book, author)
22 );
23 CREATE TABLE books_languages_link ( id INTEGER PRIMARY KEY,
24 book INTEGER NOT NULL,
25 lang_code INTEGER NOT NULL,
26 item_order INTEGER NOT NULL DEFAULT 0,
27 UNIQUE(book, lang_code)
28 );
29 CREATE TABLE books_plugin_data(id INTEGER PRIMARY KEY,
30 book INTEGER NOT NULL,
31 name TEXT NOT NULL,
32 val TEXT NOT NULL,
33 UNIQUE(book,name));
34 CREATE TABLE books_publishers_link ( id INTEGER PRIMARY KEY,
35 book INTEGER NOT NULL,
36 publisher INTEGER NOT NULL,
37 UNIQUE(book)
38 );
39 CREATE TABLE books_ratings_link ( id INTEGER PRIMARY KEY,
40 book INTEGER NOT NULL,
41 rating INTEGER NOT NULL,
42 UNIQUE(book, rating)
43 );
44 CREATE TABLE books_series_link ( id INTEGER PRIMARY KEY,
45 book INTEGER NOT NULL,
46 series INTEGER NOT NULL,
47 UNIQUE(book)
48 );
49 CREATE TABLE books_pages_link (
50 book INTEGER PRIMARY KEY,
51 pages INTEGER DEFAULT 0 NOT NULL,
52 algorithm INTEGER DEFAULT 0 NOT NULL,
53 format TEXT DEFAULT '' NOT NULL COLLATE NOCASE,
54 format_size INTEGER DEFAULT 0 NOT NULL,
55 timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
56 needs_scan INTEGER NOT NULL DEFAULT 0 CHECK(needs_scan IN (0, 1)),
57 FOREIGN KEY (book) REFERENCES books(id) ON DELETE CASCADE
58 );
59 CREATE TRIGGER books_pages_link_create_trigger AFTER INSERT ON books FOR EACH ROW
60 BEGIN
61 INSERT INTO books_pages_link(book) VALUES(NEW.id);
62 END;
63 CREATE INDEX books_pages_link_pidx ON books_pages_link (needs_scan);
64
65 CREATE TABLE books_tags_link ( id INTEGER PRIMARY KEY,
66 book INTEGER NOT NULL,
67 tag INTEGER NOT NULL,
68 UNIQUE(book, tag)
69 );
70 CREATE TABLE comments ( id INTEGER PRIMARY KEY,
71 book INTEGER NOT NULL,
72 text TEXT NOT NULL COLLATE NOCASE,
73 UNIQUE(book)
74 );
75 CREATE TABLE conversion_options ( id INTEGER PRIMARY KEY,
76 format TEXT NOT NULL COLLATE NOCASE,
77 book INTEGER,
78 data BLOB NOT NULL,
79 UNIQUE(format,book)
80 );
81 CREATE TABLE custom_columns (
82 id INTEGER PRIMARY KEY AUTOINCREMENT,
83 label TEXT NOT NULL,
84 name TEXT NOT NULL,
85 datatype TEXT NOT NULL,
86 mark_for_delete BOOL DEFAULT 0 NOT NULL,
87 editable BOOL DEFAULT 1 NOT NULL,
88 display TEXT DEFAULT '{}' NOT NULL,
89 is_multiple BOOL DEFAULT 0 NOT NULL,
90 normalized BOOL NOT NULL,
91 UNIQUE(label)
92 );
93 CREATE TABLE data ( id INTEGER PRIMARY KEY,
94 book INTEGER NOT NULL,
95 format TEXT NOT NULL COLLATE NOCASE,
96 uncompressed_size INTEGER NOT NULL,
97 name TEXT NOT NULL,
98 UNIQUE(book, format)
99 );
100 CREATE TABLE feeds ( id INTEGER PRIMARY KEY,
101 title TEXT NOT NULL,
102 script TEXT NOT NULL,
103 UNIQUE(title)
104 );
105 CREATE TABLE identifiers ( id INTEGER PRIMARY KEY,
106 book INTEGER NOT NULL,
107 type TEXT NOT NULL DEFAULT 'isbn' COLLATE NOCASE,
108 val TEXT NOT NULL COLLATE NOCASE,
109 UNIQUE(book, type)
110 );
111 CREATE TABLE languages ( id INTEGER PRIMARY KEY,
112 lang_code TEXT NOT NULL COLLATE NOCASE,
113 link TEXT NOT NULL DEFAULT '',
114 UNIQUE(lang_code)
115 );
116 CREATE TABLE library_id ( id INTEGER PRIMARY KEY,
117 uuid TEXT NOT NULL,
118 UNIQUE(uuid)
119 );
120 CREATE TABLE metadata_dirtied(id INTEGER PRIMARY KEY,
121 book INTEGER NOT NULL,
122 UNIQUE(book));
123 CREATE TABLE annotations_dirtied(id INTEGER PRIMARY KEY,
124 book INTEGER NOT NULL,
125 UNIQUE(book));
126 CREATE TABLE preferences(id INTEGER PRIMARY KEY,
127 key TEXT NOT NULL,
128 val TEXT NOT NULL,
129 UNIQUE(key));
130 CREATE TABLE publishers ( id INTEGER PRIMARY KEY,
131 name TEXT NOT NULL COLLATE NOCASE,
132 sort TEXT COLLATE NOCASE,
133 link TEXT NOT NULL DEFAULT '',
134 UNIQUE(name)
135 );
136 CREATE TABLE ratings ( id INTEGER PRIMARY KEY,
137 rating INTEGER CHECK(rating > -1 AND rating < 11),
138 link TEXT NOT NULL DEFAULT '',
139 UNIQUE (rating)
140 );
141 CREATE TABLE series ( id INTEGER PRIMARY KEY,
142 name TEXT NOT NULL COLLATE NOCASE,
143 sort TEXT COLLATE NOCASE,
144 link TEXT NOT NULL DEFAULT '',
145 UNIQUE (name)
146 );
147 CREATE TABLE tags ( id INTEGER PRIMARY KEY,
148 name TEXT NOT NULL COLLATE NOCASE,
149 link TEXT NOT NULL DEFAULT '',
150 UNIQUE (name)
151 );
152 CREATE TABLE last_read_positions ( id INTEGER PRIMARY KEY,
153 book INTEGER NOT NULL,
154 format TEXT NOT NULL COLLATE NOCASE,
155 user TEXT NOT NULL,
156 device TEXT NOT NULL,
157 cfi TEXT NOT NULL,
158 epoch REAL NOT NULL,
159 pos_frac REAL NOT NULL DEFAULT 0,
160 UNIQUE(user, device, book, format)
161 );
162
163 CREATE TABLE annotations ( id INTEGER PRIMARY KEY,
164 book INTEGER NOT NULL,
165 format TEXT NOT NULL COLLATE NOCASE,
166 user_type TEXT NOT NULL,
167 user TEXT NOT NULL,
168 timestamp REAL NOT NULL,
169 annot_id TEXT NOT NULL,
170 annot_type TEXT NOT NULL,
171 annot_data TEXT NOT NULL,
172 searchable_text TEXT NOT NULL DEFAULT '',
173 UNIQUE(book, user_type, user, format, annot_type, annot_id)
174 );
175
176 CREATE VIRTUAL TABLE annotations_fts USING fts5(searchable_text, content = 'annotations', content_rowid = 'id', tokenize = 'unicode61 remove_diacritics 2');
177 CREATE VIRTUAL TABLE annotations_fts_stemmed USING fts5(searchable_text, content = 'annotations', content_rowid = 'id', tokenize = 'porter unicode61 remove_diacritics 2');
178
179 CREATE TRIGGER annotations_fts_insert_trg AFTER INSERT ON annotations
180 BEGIN
181 INSERT INTO annotations_fts(rowid, searchable_text) VALUES (NEW.id, NEW.searchable_text);
182 INSERT INTO annotations_fts_stemmed(rowid, searchable_text) VALUES (NEW.id, NEW.searchable_text);
183 END;
184
185 CREATE TRIGGER annotations_fts_delete_trg AFTER DELETE ON annotations
186 BEGIN
187 INSERT INTO annotations_fts(annotations_fts, rowid, searchable_text) VALUES('delete', OLD.id, OLD.searchable_text);
188 INSERT INTO annotations_fts_stemmed(annotations_fts_stemmed, rowid, searchable_text) VALUES('delete', OLD.id, OLD.searchable_text);
189 END;
190
191 CREATE TRIGGER annotations_fts_update_trg AFTER UPDATE ON annotations
192 BEGIN
193 INSERT INTO annotations_fts(annotations_fts, rowid, searchable_text) VALUES('delete', OLD.id, OLD.searchable_text);
194 INSERT INTO annotations_fts(rowid, searchable_text) VALUES (NEW.id, NEW.searchable_text);
195 INSERT INTO annotations_fts_stemmed(annotations_fts_stemmed, rowid, searchable_text) VALUES('delete', OLD.id, OLD.searchable_text);
196 INSERT INTO annotations_fts_stemmed(rowid, searchable_text) VALUES (NEW.id, NEW.searchable_text);
197 END;
198
199
200 CREATE VIEW meta AS
201 SELECT id, title,
202 (SELECT sortconcat(bal.id, name) FROM books_authors_link AS bal JOIN authors ON(author = authors.id) WHERE book = books.id) authors,
203 (SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,
204 (SELECT rating FROM ratings WHERE ratings.id IN (SELECT rating from books_ratings_link WHERE book=books.id)) rating,
205 timestamp,
206 (SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,
207 (SELECT concat(name) FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,
208 (SELECT text FROM comments WHERE book=books.id) comments,
209 (SELECT name FROM series WHERE series.id IN (SELECT series FROM books_series_link WHERE book=books.id)) series,
210 series_index,
211 sort,
212 author_sort,
213 (SELECT concat(format) FROM data WHERE data.book=books.id) formats,
214 path,
215 pubdate,
216 uuid
217 FROM books;
218 CREATE VIEW tag_browser_authors AS SELECT
219 id,
220 name,
221 (SELECT COUNT(id) FROM books_authors_link WHERE author=authors.id) count,
222 (SELECT AVG(ratings.rating)
223 FROM books_authors_link AS tl, books_ratings_link AS bl, ratings
224 WHERE tl.author=authors.id AND bl.book=tl.book AND
225 ratings.id = bl.rating AND ratings.rating <> 0) avg_rating,
226 sort AS sort
227 FROM authors;
228 CREATE VIEW tag_browser_filtered_authors AS SELECT
229 id,
230 name,
231 (SELECT COUNT(books_authors_link.id) FROM books_authors_link WHERE
232 author=authors.id AND books_list_filter(book)) count,
233 (SELECT AVG(ratings.rating)
234 FROM books_authors_link AS tl, books_ratings_link AS bl, ratings
235 WHERE tl.author=authors.id AND bl.book=tl.book AND
236 ratings.id = bl.rating AND ratings.rating <> 0 AND
237 books_list_filter(bl.book)) avg_rating,
238 sort AS sort
239 FROM authors;
240 CREATE VIEW tag_browser_filtered_publishers AS SELECT
241 id,
242 name,
243 (SELECT COUNT(books_publishers_link.id) FROM books_publishers_link WHERE
244 publisher=publishers.id AND books_list_filter(book)) count,
245 (SELECT AVG(ratings.rating)
246 FROM books_publishers_link AS tl, books_ratings_link AS bl, ratings
247 WHERE tl.publisher=publishers.id AND bl.book=tl.book AND
248 ratings.id = bl.rating AND ratings.rating <> 0 AND
249 books_list_filter(bl.book)) avg_rating,
250 name AS sort
251 FROM publishers;
252 CREATE VIEW tag_browser_filtered_ratings AS SELECT
253 id,
254 rating,
255 (SELECT COUNT(books_ratings_link.id) FROM books_ratings_link WHERE
256 rating=ratings.id AND books_list_filter(book)) count,
257 (SELECT AVG(ratings.rating)
258 FROM books_ratings_link AS tl, books_ratings_link AS bl, ratings
259 WHERE tl.rating=ratings.id AND bl.book=tl.book AND
260 ratings.id = bl.rating AND ratings.rating <> 0 AND
261 books_list_filter(bl.book)) avg_rating,
262 rating AS sort
263 FROM ratings;
264 CREATE VIEW tag_browser_filtered_series AS SELECT
265 id,
266 name,
267 (SELECT COUNT(books_series_link.id) FROM books_series_link WHERE
268 series=series.id AND books_list_filter(book)) count,
269 (SELECT AVG(ratings.rating)
270 FROM books_series_link AS tl, books_ratings_link AS bl, ratings
271 WHERE tl.series=series.id AND bl.book=tl.book AND
272 ratings.id = bl.rating AND ratings.rating <> 0 AND
273 books_list_filter(bl.book)) avg_rating,
274 (title_sort(name)) AS sort
275 FROM series;
276 CREATE VIEW tag_browser_filtered_tags AS SELECT
277 id,
278 name,
279 (SELECT COUNT(books_tags_link.id) FROM books_tags_link WHERE
280 tag=tags.id AND books_list_filter(book)) count,
281 (SELECT AVG(ratings.rating)
282 FROM books_tags_link AS tl, books_ratings_link AS bl, ratings
283 WHERE tl.tag=tags.id AND bl.book=tl.book AND
284 ratings.id = bl.rating AND ratings.rating <> 0 AND
285 books_list_filter(bl.book)) avg_rating,
286 name AS sort
287 FROM tags;
288 CREATE VIEW tag_browser_publishers AS SELECT
289 id,
290 name,
291 (SELECT COUNT(id) FROM books_publishers_link WHERE publisher=publishers.id) count,
292 (SELECT AVG(ratings.rating)
293 FROM books_publishers_link AS tl, books_ratings_link AS bl, ratings
294 WHERE tl.publisher=publishers.id AND bl.book=tl.book AND
295 ratings.id = bl.rating AND ratings.rating <> 0) avg_rating,
296 name AS sort
297 FROM publishers;
298 CREATE VIEW tag_browser_ratings AS SELECT
299 id,
300 rating,
301 (SELECT COUNT(id) FROM books_ratings_link WHERE rating=ratings.id) count,
302 (SELECT AVG(ratings.rating)
303 FROM books_ratings_link AS tl, books_ratings_link AS bl, ratings
304 WHERE tl.rating=ratings.id AND bl.book=tl.book AND
305 ratings.id = bl.rating AND ratings.rating <> 0) avg_rating,
306 rating AS sort
307 FROM ratings;
308 CREATE VIEW tag_browser_series AS SELECT
309 id,
310 name,
311 (SELECT COUNT(id) FROM books_series_link WHERE series=series.id) count,
312 (SELECT AVG(ratings.rating)
313 FROM books_series_link AS tl, books_ratings_link AS bl, ratings
314 WHERE tl.series=series.id AND bl.book=tl.book AND
315 ratings.id = bl.rating AND ratings.rating <> 0) avg_rating,
316 (title_sort(name)) AS sort
317 FROM series;
318 CREATE VIEW tag_browser_tags AS SELECT
319 id,
320 name,
321 (SELECT COUNT(id) FROM books_tags_link WHERE tag=tags.id) count,
322 (SELECT AVG(ratings.rating)
323 FROM books_tags_link AS tl, books_ratings_link AS bl, ratings
324 WHERE tl.tag=tags.id AND bl.book=tl.book AND
325 ratings.id = bl.rating AND ratings.rating <> 0) avg_rating,
326 name AS sort
327 FROM tags;
328 CREATE INDEX authors_idx ON books (author_sort COLLATE NOCASE);
329 CREATE INDEX books_authors_link_aidx ON books_authors_link (author);
330 CREATE INDEX books_authors_link_bidx ON books_authors_link (book);
331 CREATE INDEX books_idx ON books (sort COLLATE NOCASE);
332 CREATE INDEX books_languages_link_aidx ON books_languages_link (lang_code);
333 CREATE INDEX books_languages_link_bidx ON books_languages_link (book);
334 CREATE INDEX books_publishers_link_aidx ON books_publishers_link (publisher);
335 CREATE INDEX books_publishers_link_bidx ON books_publishers_link (book);
336 CREATE INDEX books_ratings_link_aidx ON books_ratings_link (rating);
337 CREATE INDEX books_ratings_link_bidx ON books_ratings_link (book);
338 CREATE INDEX books_series_link_aidx ON books_series_link (series);
339 CREATE INDEX books_series_link_bidx ON books_series_link (book);
340 CREATE INDEX books_tags_link_aidx ON books_tags_link (tag);
341 CREATE INDEX books_tags_link_bidx ON books_tags_link (book);
342 CREATE INDEX comments_idx ON comments (book);
343 CREATE INDEX conversion_options_idx_a ON conversion_options (format COLLATE NOCASE);
344 CREATE INDEX conversion_options_idx_b ON conversion_options (book);
345 CREATE INDEX custom_columns_idx ON custom_columns (label);
346 CREATE INDEX data_idx ON data (book);
347 CREATE INDEX lrp_idx ON last_read_positions (book);
348 CREATE INDEX annot_idx ON annotations (book);
349 CREATE INDEX formats_idx ON data (format);
350 CREATE INDEX languages_idx ON languages (lang_code COLLATE NOCASE);
351 CREATE INDEX publishers_idx ON publishers (name COLLATE NOCASE);
352 CREATE INDEX series_idx ON series (name COLLATE NOCASE);
353 CREATE INDEX tags_idx ON tags (name COLLATE NOCASE);
354 CREATE TRIGGER books_delete_trg
355 AFTER DELETE ON books
356 BEGIN
357 DELETE FROM books_authors_link WHERE book=OLD.id;
358 DELETE FROM books_publishers_link WHERE book=OLD.id;
359 DELETE FROM books_ratings_link WHERE book=OLD.id;
360 DELETE FROM books_series_link WHERE book=OLD.id;
361 DELETE FROM books_tags_link WHERE book=OLD.id;
362 DELETE FROM books_languages_link WHERE book=OLD.id;
363 DELETE FROM data WHERE book=OLD.id;
364 DELETE FROM last_read_positions WHERE book=OLD.id;
365 DELETE FROM annotations WHERE book=OLD.id;
366 DELETE FROM comments WHERE book=OLD.id;
367 DELETE FROM conversion_options WHERE book=OLD.id;
368 DELETE FROM books_plugin_data WHERE book=OLD.id;
369 DELETE FROM identifiers WHERE book=OLD.id;
370 END;
371 CREATE TRIGGER books_insert_trg AFTER INSERT ON books
372 BEGIN
373 UPDATE books SET sort=title_sort(NEW.title),uuid=uuid4() WHERE id=NEW.id;
374 END;
375 CREATE TRIGGER books_update_trg
376 AFTER UPDATE ON books
377 BEGIN
378 UPDATE books SET sort=title_sort(NEW.title)
379 WHERE id=NEW.id AND OLD.title <> NEW.title;
380 END;
381 CREATE TRIGGER fkc_comments_insert
382 BEFORE INSERT ON comments
383 BEGIN
384 SELECT CASE
385 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
386 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
387 END;
388 END;
389 CREATE TRIGGER fkc_comments_update
390 BEFORE UPDATE OF book ON comments
391 BEGIN
392 SELECT CASE
393 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
394 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
395 END;
396 END;
397 CREATE TRIGGER fkc_data_insert
398 BEFORE INSERT ON data
399 BEGIN
400 SELECT CASE
401 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
402 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
403 END;
404 END;
405 CREATE TRIGGER fkc_data_update
406 BEFORE UPDATE OF book ON data
407 BEGIN
408 SELECT CASE
409 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
410 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
411 END;
412 END;
413 CREATE TRIGGER fkc_lrp_insert
414 BEFORE INSERT ON last_read_positions
415 BEGIN
416 SELECT CASE
417 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
418 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
419 END;
420 END;
421 CREATE TRIGGER fkc_lrp_update
422 BEFORE UPDATE OF book ON last_read_positions
423 BEGIN
424 SELECT CASE
425 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
426 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
427 END;
428 END;
429 CREATE TRIGGER fkc_annot_insert
430 BEFORE INSERT ON annotations
431 BEGIN
432 SELECT CASE
433 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
434 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
435 END;
436 END;
437 CREATE TRIGGER fkc_annot_update
438 BEFORE UPDATE OF book ON annotations
439 BEGIN
440 SELECT CASE
441 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
442 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
443 END;
444 END;
445 CREATE TRIGGER fkc_delete_on_authors
446 BEFORE DELETE ON authors
447 BEGIN
448 SELECT CASE
449 WHEN (SELECT COUNT(id) FROM books_authors_link WHERE author=OLD.id) > 0
450 THEN RAISE(ABORT, 'Foreign key violation: authors is still referenced')
451 END;
452 END;
453 CREATE TRIGGER fkc_delete_on_languages
454 BEFORE DELETE ON languages
455 BEGIN
456 SELECT CASE
457 WHEN (SELECT COUNT(id) FROM books_languages_link WHERE lang_code=OLD.id) > 0
458 THEN RAISE(ABORT, 'Foreign key violation: language is still referenced')
459 END;
460 END;
461 CREATE TRIGGER fkc_delete_on_languages_link
462 BEFORE INSERT ON books_languages_link
463 BEGIN
464 SELECT CASE
465 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
466 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
467 WHEN (SELECT id from languages WHERE id=NEW.lang_code) IS NULL
468 THEN RAISE(ABORT, 'Foreign key violation: lang_code not in languages')
469 END;
470 END;
471 CREATE TRIGGER fkc_delete_on_publishers
472 BEFORE DELETE ON publishers
473 BEGIN
474 SELECT CASE
475 WHEN (SELECT COUNT(id) FROM books_publishers_link WHERE publisher=OLD.id) > 0
476 THEN RAISE(ABORT, 'Foreign key violation: publishers is still referenced')
477 END;
478 END;
479 CREATE TRIGGER fkc_delete_on_series
480 BEFORE DELETE ON series
481 BEGIN
482 SELECT CASE
483 WHEN (SELECT COUNT(id) FROM books_series_link WHERE series=OLD.id) > 0
484 THEN RAISE(ABORT, 'Foreign key violation: series is still referenced')
485 END;
486 END;
487 CREATE TRIGGER fkc_delete_on_tags
488 BEFORE DELETE ON tags
489 BEGIN
490 SELECT CASE
491 WHEN (SELECT COUNT(id) FROM books_tags_link WHERE tag=OLD.id) > 0
492 THEN RAISE(ABORT, 'Foreign key violation: tags is still referenced')
493 END;
494 END;
495 CREATE TRIGGER fkc_insert_books_authors_link
496 BEFORE INSERT ON books_authors_link
497 BEGIN
498 SELECT CASE
499 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
500 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
501 WHEN (SELECT id from authors WHERE id=NEW.author) IS NULL
502 THEN RAISE(ABORT, 'Foreign key violation: author not in authors')
503 END;
504 END;
505 CREATE TRIGGER fkc_insert_books_publishers_link
506 BEFORE INSERT ON books_publishers_link
507 BEGIN
508 SELECT CASE
509 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
510 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
511 WHEN (SELECT id from publishers WHERE id=NEW.publisher) IS NULL
512 THEN RAISE(ABORT, 'Foreign key violation: publisher not in publishers')
513 END;
514 END;
515 CREATE TRIGGER fkc_insert_books_ratings_link
516 BEFORE INSERT ON books_ratings_link
517 BEGIN
518 SELECT CASE
519 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
520 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
521 WHEN (SELECT id from ratings WHERE id=NEW.rating) IS NULL
522 THEN RAISE(ABORT, 'Foreign key violation: rating not in ratings')
523 END;
524 END;
525 CREATE TRIGGER fkc_insert_books_series_link
526 BEFORE INSERT ON books_series_link
527 BEGIN
528 SELECT CASE
529 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
530 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
531 WHEN (SELECT id from series WHERE id=NEW.series) IS NULL
532 THEN RAISE(ABORT, 'Foreign key violation: series not in series')
533 END;
534 END;
535 CREATE TRIGGER fkc_insert_books_tags_link
536 BEFORE INSERT ON books_tags_link
537 BEGIN
538 SELECT CASE
539 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
540 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
541 WHEN (SELECT id from tags WHERE id=NEW.tag) IS NULL
542 THEN RAISE(ABORT, 'Foreign key violation: tag not in tags')
543 END;
544 END;
545 CREATE TRIGGER fkc_update_books_authors_link_a
546 BEFORE UPDATE OF book ON books_authors_link
547 BEGIN
548 SELECT CASE
549 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
550 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
551 END;
552 END;
553 CREATE TRIGGER fkc_update_books_authors_link_b
554 BEFORE UPDATE OF author ON books_authors_link
555 BEGIN
556 SELECT CASE
557 WHEN (SELECT id from authors WHERE id=NEW.author) IS NULL
558 THEN RAISE(ABORT, 'Foreign key violation: author not in authors')
559 END;
560 END;
561 CREATE TRIGGER fkc_update_books_languages_link_a
562 BEFORE UPDATE OF book ON books_languages_link
563 BEGIN
564 SELECT CASE
565 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
566 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
567 END;
568 END;
569 CREATE TRIGGER fkc_update_books_languages_link_b
570 BEFORE UPDATE OF lang_code ON books_languages_link
571 BEGIN
572 SELECT CASE
573 WHEN (SELECT id from languages WHERE id=NEW.lang_code) IS NULL
574 THEN RAISE(ABORT, 'Foreign key violation: lang_code not in languages')
575 END;
576 END;
577 CREATE TRIGGER fkc_update_books_publishers_link_a
578 BEFORE UPDATE OF book ON books_publishers_link
579 BEGIN
580 SELECT CASE
581 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
582 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
583 END;
584 END;
585 CREATE TRIGGER fkc_update_books_publishers_link_b
586 BEFORE UPDATE OF publisher ON books_publishers_link
587 BEGIN
588 SELECT CASE
589 WHEN (SELECT id from publishers WHERE id=NEW.publisher) IS NULL
590 THEN RAISE(ABORT, 'Foreign key violation: publisher not in publishers')
591 END;
592 END;
593 CREATE TRIGGER fkc_update_books_ratings_link_a
594 BEFORE UPDATE OF book ON books_ratings_link
595 BEGIN
596 SELECT CASE
597 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
598 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
599 END;
600 END;
601 CREATE TRIGGER fkc_update_books_ratings_link_b
602 BEFORE UPDATE OF rating ON books_ratings_link
603 BEGIN
604 SELECT CASE
605 WHEN (SELECT id from ratings WHERE id=NEW.rating) IS NULL
606 THEN RAISE(ABORT, 'Foreign key violation: rating not in ratings')
607 END;
608 END;
609 CREATE TRIGGER fkc_update_books_series_link_a
610 BEFORE UPDATE OF book ON books_series_link
611 BEGIN
612 SELECT CASE
613 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
614 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
615 END;
616 END;
617 CREATE TRIGGER fkc_update_books_series_link_b
618 BEFORE UPDATE OF series ON books_series_link
619 BEGIN
620 SELECT CASE
621 WHEN (SELECT id from series WHERE id=NEW.series) IS NULL
622 THEN RAISE(ABORT, 'Foreign key violation: series not in series')
623 END;
624 END;
625 CREATE TRIGGER fkc_update_books_tags_link_a
626 BEFORE UPDATE OF book ON books_tags_link
627 BEGIN
628 SELECT CASE
629 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
630 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
631 END;
632 END;
633 CREATE TRIGGER fkc_update_books_tags_link_b
634 BEFORE UPDATE OF tag ON books_tags_link
635 BEGIN
636 SELECT CASE
637 WHEN (SELECT id from tags WHERE id=NEW.tag) IS NULL
638 THEN RAISE(ABORT, 'Foreign key violation: tag not in tags')
639 END;
640 END;
641 CREATE TRIGGER series_insert_trg
642 AFTER INSERT ON series
643 BEGIN
644 UPDATE series SET sort=title_sort(NEW.name) WHERE id=NEW.id;
645 END;
646 CREATE TRIGGER series_update_trg
647 AFTER UPDATE ON series
648 BEGIN
649 UPDATE series SET sort=title_sort(NEW.name) WHERE id=NEW.id;
650 END;
651 -- Set application id to "cali" as a big endian number
652 PRAGMA application_id = 0x63616c69;
653 pragma user_version=27;