Debian Codegraph @

src/calibre/db/schema_upgrades.py

raw
1 #!/usr/bin/env python
2 # License: GPLv3 Copyright: 2011, Kovid Goyal <[email protected]>
3
4 import os
5
6 from calibre import prints
7 from calibre.utils.date import DEFAULT_DATE, isoformat
8
9
10 class SchemaUpgrade:
11 def __init__(self, db, library_path, field_metadata):
12 db.execute('BEGIN EXCLUSIVE TRANSACTION')
13 self.db = db
14 self.library_path = library_path
15 self.field_metadata = field_metadata
16 # Upgrade database
17 try:
18 while True:
19 uv = next(self.db.execute('pragma user_version'))[0]
20 meth = getattr(self, f'upgrade_version_{uv}', None)
21 if meth is None:
22 break
23 else:
24 prints(f'Upgrading database to version {uv + 1}...')
25 meth()
26 self.db.execute(f'pragma user_version={uv + 1}')
27 except Exception:
28 self.db.execute('ROLLBACK')
29 raise
30 else:
31 self.db.execute('COMMIT')
32 finally:
33 self.db = self.field_metadata = None
34
35 def upgrade_version_1(self):
36 """
37 Normalize indices.
38 """
39 assert self.db is not None
40 self.db.execute('''\
41 DROP INDEX IF EXISTS authors_idx;
42 CREATE INDEX authors_idx ON books (author_sort COLLATE NOCASE, sort COLLATE NOCASE);
43 DROP INDEX IF EXISTS series_idx;
44 CREATE INDEX series_idx ON series (name COLLATE NOCASE);
45 DROP INDEX IF EXISTS series_sort_idx;
46 CREATE INDEX series_sort_idx ON books (series_index, id);
47 ''')
48
49 def upgrade_version_2(self):
50 """Fix Foreign key constraints for deleting from link tables."""
51 assert self.db is not None
52 script = '''\
53 DROP TRIGGER IF EXISTS fkc_delete_books_%(ltable)s_link;
54 CREATE TRIGGER fkc_delete_on_%(table)s
55 BEFORE DELETE ON %(table)s
56 BEGIN
57 SELECT CASE
58 WHEN (SELECT COUNT(id) FROM books_%(ltable)s_link WHERE %(ltable_col)s=OLD.id) > 0
59 THEN RAISE(ABORT, 'Foreign key violation: %(table)s is still referenced')
60 END;
61 END;
62 DELETE FROM %(table)s WHERE (SELECT COUNT(id) FROM books_%(ltable)s_link WHERE %(ltable_col)s=%(table)s.id) < 1;
63 '''
64 self.db.execute(script % dict(ltable='authors', table='authors', ltable_col='author'))
65 self.db.execute(script % dict(ltable='publishers', table='publishers', ltable_col='publisher'))
66 self.db.execute(script % dict(ltable='tags', table='tags', ltable_col='tag'))
67 self.db.execute(script % dict(ltable='series', table='series', ltable_col='series'))
68
69 def upgrade_version_3(self):
70 "Add path to result cache"
71 assert self.db is not None
72 self.db.execute('''
73 DROP VIEW IF EXISTS meta;
74 CREATE VIEW meta AS
75 SELECT id, title,
76 (SELECT concat(name) FROM authors WHERE authors.id IN (SELECT author from books_authors_link WHERE book=books.id)) authors,
77 (SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,
78 (SELECT rating FROM ratings WHERE ratings.id IN (SELECT rating from books_ratings_link WHERE book=books.id)) rating,
79 timestamp,
80 (SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,
81 (SELECT concat(name) FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,
82 (SELECT text FROM comments WHERE book=books.id) comments,
83 (SELECT name FROM series WHERE series.id IN (SELECT series FROM books_series_link WHERE book=books.id)) series,
84 series_index,
85 sort,
86 author_sort,
87 (SELECT concat(format) FROM data WHERE data.book=books.id) formats,
88 isbn,
89 path
90 FROM books;
91 ''')
92
93 def upgrade_version_4(self):
94 "Rationalize books table"
95 assert self.db is not None
96 self.db.execute('''
97 CREATE TEMPORARY TABLE
98 books_backup(id,title,sort,timestamp,series_index,author_sort,isbn,path);
99 INSERT INTO books_backup SELECT id,title,sort,timestamp,series_index,author_sort,isbn,path FROM books;
100 DROP TABLE books;
101 CREATE TABLE books ( id INTEGER PRIMARY KEY AUTOINCREMENT,
102 title TEXT NOT NULL DEFAULT 'Unknown' COLLATE NOCASE,
103 sort TEXT COLLATE NOCASE,
104 timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
105 pubdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
106 series_index REAL NOT NULL DEFAULT 1.0,
107 author_sort TEXT COLLATE NOCASE,
108 isbn TEXT DEFAULT "" COLLATE NOCASE,
109 lccn TEXT DEFAULT "" COLLATE NOCASE,
110 path TEXT NOT NULL DEFAULT "",
111 flags INTEGER NOT NULL DEFAULT 1
112 );
113 INSERT INTO
114 books (id,title,sort,timestamp,pubdate,series_index,author_sort,isbn,path)
115 SELECT id,title,sort,timestamp,timestamp,series_index,author_sort,isbn,path FROM books_backup;
116 DROP TABLE books_backup;
117
118 DROP VIEW IF EXISTS meta;
119 CREATE VIEW meta AS
120 SELECT id, title,
121 (SELECT concat(name) FROM authors WHERE authors.id IN (SELECT author from books_authors_link WHERE book=books.id)) authors,
122 (SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,
123 (SELECT rating FROM ratings WHERE ratings.id IN (SELECT rating from books_ratings_link WHERE book=books.id)) rating,
124 timestamp,
125 (SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,
126 (SELECT concat(name) FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,
127 (SELECT text FROM comments WHERE book=books.id) comments,
128 (SELECT name FROM series WHERE series.id IN (SELECT series FROM books_series_link WHERE book=books.id)) series,
129 series_index,
130 sort,
131 author_sort,
132 (SELECT concat(format) FROM data WHERE data.book=books.id) formats,
133 isbn,
134 path,
135 lccn,
136 pubdate,
137 flags
138 FROM books;
139 ''')
140
141 def upgrade_version_5(self):
142 "Update indexes/triggers for new books table"
143 assert self.db is not None
144 self.db.execute('''
145 CREATE INDEX authors_idx ON books (author_sort COLLATE NOCASE);
146 CREATE INDEX books_idx ON books (sort COLLATE NOCASE);
147 CREATE TRIGGER books_delete_trg
148 AFTER DELETE ON books
149 BEGIN
150 DELETE FROM books_authors_link WHERE book=OLD.id;
151 DELETE FROM books_publishers_link WHERE book=OLD.id;
152 DELETE FROM books_ratings_link WHERE book=OLD.id;
153 DELETE FROM books_series_link WHERE book=OLD.id;
154 DELETE FROM books_tags_link WHERE book=OLD.id;
155 DELETE FROM data WHERE book=OLD.id;
156 DELETE FROM comments WHERE book=OLD.id;
157 DELETE FROM conversion_options WHERE book=OLD.id;
158 END;
159 CREATE TRIGGER books_insert_trg
160 AFTER INSERT ON books
161 BEGIN
162 UPDATE books SET sort=title_sort(NEW.title) WHERE id=NEW.id;
163 END;
164 CREATE TRIGGER books_update_trg
165 AFTER UPDATE ON books
166 BEGIN
167 UPDATE books SET sort=title_sort(NEW.title) WHERE id=NEW.id;
168 END;
169
170 UPDATE books SET sort=title_sort(title) WHERE sort IS NULL;
171 ''')
172
173 def upgrade_version_6(self):
174 "Show authors in order"
175 assert self.db is not None
176 self.db.execute('''
177 DROP VIEW IF EXISTS meta;
178 CREATE VIEW meta AS
179 SELECT id, title,
180 (SELECT sortconcat(bal.id, name) FROM books_authors_link AS bal JOIN authors ON(author = authors.id) WHERE book = books.id) authors,
181 (SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,
182 (SELECT rating FROM ratings WHERE ratings.id IN (SELECT rating from books_ratings_link WHERE book=books.id)) rating,
183 timestamp,
184 (SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,
185 (SELECT concat(name) FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,
186 (SELECT text FROM comments WHERE book=books.id) comments,
187 (SELECT name FROM series WHERE series.id IN (SELECT series FROM books_series_link WHERE book=books.id)) series,
188 series_index,
189 sort,
190 author_sort,
191 (SELECT concat(format) FROM data WHERE data.book=books.id) formats,
192 isbn,
193 path,
194 lccn,
195 pubdate,
196 flags
197 FROM books;
198 ''')
199
200 def upgrade_version_7(self):
201 "Add uuid column"
202 assert self.db is not None
203 self.db.execute('''
204 ALTER TABLE books ADD COLUMN uuid TEXT;
205 DROP TRIGGER IF EXISTS books_insert_trg;
206 DROP TRIGGER IF EXISTS books_update_trg;
207 UPDATE books SET uuid=uuid4();
208
209 CREATE TRIGGER books_insert_trg AFTER INSERT ON books
210 BEGIN
211 UPDATE books SET sort=title_sort(NEW.title),uuid=uuid4() WHERE id=NEW.id;
212 END;
213
214 CREATE TRIGGER books_update_trg AFTER UPDATE ON books
215 BEGIN
216 UPDATE books SET sort=title_sort(NEW.title) WHERE id=NEW.id;
217 END;
218
219 DROP VIEW IF EXISTS meta;
220 CREATE VIEW meta AS
221 SELECT id, title,
222 (SELECT sortconcat(bal.id, name) FROM books_authors_link AS bal JOIN authors ON(author = authors.id) WHERE book = books.id) authors,
223 (SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,
224 (SELECT rating FROM ratings WHERE ratings.id IN (SELECT rating from books_ratings_link WHERE book=books.id)) rating,
225 timestamp,
226 (SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,
227 (SELECT concat(name) FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,
228 (SELECT text FROM comments WHERE book=books.id) comments,
229 (SELECT name FROM series WHERE series.id IN (SELECT series FROM books_series_link WHERE book=books.id)) series,
230 series_index,
231 sort,
232 author_sort,
233 (SELECT concat(format) FROM data WHERE data.book=books.id) formats,
234 isbn,
235 path,
236 lccn,
237 pubdate,
238 flags,
239 uuid
240 FROM books;
241 ''')
242
243 def upgrade_version_8(self):
244 "Add Tag Browser views"
245 assert self.db is not None
246
247 def create_tag_browser_view(table_name, column_name):
248 assert self.db is not None
249 self.db.execute(f'''
250 DROP VIEW IF EXISTS tag_browser_{table_name};
251 CREATE VIEW tag_browser_{table_name} AS SELECT
252 id,
253 name,
254 (SELECT COUNT(id) FROM books_{table_name}_link WHERE {column_name}={table_name}.id) count
255 FROM {table_name};
256 ''')
257
258 for tn in ('authors', 'tags', 'publishers', 'series'):
259 cn = tn[:-1]
260 if tn == 'series':
261 cn = tn
262 create_tag_browser_view(tn, cn)
263
264 def upgrade_version_9(self):
265 "Add custom columns"
266 assert self.db is not None
267 self.db.execute('''
268 CREATE TABLE custom_columns (
269 id INTEGER PRIMARY KEY AUTOINCREMENT,
270 label TEXT NOT NULL,
271 name TEXT NOT NULL,
272 datatype TEXT NOT NULL,
273 mark_for_delete BOOL DEFAULT 0 NOT NULL,
274 editable BOOL DEFAULT 1 NOT NULL,
275 display TEXT DEFAULT "{}" NOT NULL,
276 is_multiple BOOL DEFAULT 0 NOT NULL,
277 normalized BOOL NOT NULL,
278 UNIQUE(label)
279 );
280 CREATE INDEX IF NOT EXISTS custom_columns_idx ON custom_columns (label);
281 CREATE INDEX IF NOT EXISTS formats_idx ON data (format);
282 ''')
283
284 def upgrade_version_10(self):
285 "Add restricted Tag Browser views"
286 assert self.db is not None
287 assert self.field_metadata is not None
288
289 def create_tag_browser_view(table_name, column_name, view_column_name):
290 script = f'''
291 DROP VIEW IF EXISTS tag_browser_{table_name};
292 CREATE VIEW tag_browser_{table_name} AS SELECT
293 id,
294 {view_column_name},
295 (SELECT COUNT(id) FROM books_{table_name}_link WHERE {column_name}={table_name}.id) count
296 FROM {table_name};
297 DROP VIEW IF EXISTS tag_browser_filtered_{table_name};
298 CREATE VIEW tag_browser_filtered_{table_name} AS SELECT
299 id,
300 {view_column_name},
301 (SELECT COUNT(books_{table_name}_link.id) FROM books_{table_name}_link WHERE
302 {column_name}={table_name}.id AND books_list_filter(book)) count
303 FROM {table_name};
304 '''
305 assert self.db is not None
306 self.db.execute(script)
307
308 for field in self.field_metadata.values():
309 if field['is_category'] and not field['is_custom'] and 'link_column' in field:
310 table = self.db.get(
311 "SELECT name FROM sqlite_master WHERE type='table' AND name=?",
312 ('books_{}_link'.format(field['table']),),
313 all=False,
314 )
315 if table is not None:
316 create_tag_browser_view(field['table'], field['link_column'], field['column'])
317
318 def upgrade_version_11(self):
319 "Add average rating to tag browser views"
320 assert self.db is not None
321 assert self.field_metadata is not None
322
323 def create_std_tag_browser_view(table_name, column_name, view_column_name, sort_column_name):
324 script = f'''
325 DROP VIEW IF EXISTS tag_browser_{table_name};
326 CREATE VIEW tag_browser_{table_name} AS SELECT
327 id,
328 {view_column_name},
329 (SELECT COUNT(id) FROM books_{table_name}_link WHERE {column_name}={table_name}.id) count,
330 (SELECT AVG(ratings.rating)
331 FROM books_{table_name}_link AS tl, books_ratings_link AS bl, ratings
332 WHERE tl.{column_name}={table_name}.id AND bl.book=tl.book AND
333 ratings.id = bl.rating AND ratings.rating <> 0) avg_rating,
334 {sort_column_name} AS sort
335 FROM {table_name};
336 DROP VIEW IF EXISTS tag_browser_filtered_{table_name};
337 CREATE VIEW tag_browser_filtered_{table_name} AS SELECT
338 id,
339 {view_column_name},
340 (SELECT COUNT(books_{table_name}_link.id) FROM books_{table_name}_link WHERE
341 {column_name}={table_name}.id AND books_list_filter(book)) count,
342 (SELECT AVG(ratings.rating)
343 FROM books_{table_name}_link AS tl, books_ratings_link AS bl, ratings
344 WHERE tl.{column_name}={table_name}.id AND bl.book=tl.book AND
345 ratings.id = bl.rating AND ratings.rating <> 0 AND
346 books_list_filter(bl.book)) avg_rating,
347 {sort_column_name} AS sort
348 FROM {table_name};
349
350 '''
351 assert self.db is not None
352 self.db.execute(script)
353
354 def create_cust_tag_browser_view(table_name, link_table_name):
355 script = f'''
356 DROP VIEW IF EXISTS tag_browser_{table_name};
357 CREATE VIEW tag_browser_{table_name} AS SELECT
358 id,
359 value,
360 (SELECT COUNT(id) FROM {link_table_name} WHERE value={table_name}.id) count,
361 (SELECT AVG(r.rating)
362 FROM {link_table_name},
363 books_ratings_link AS bl,
364 ratings AS r
365 WHERE {link_table_name}.value={table_name}.id AND bl.book={link_table_name}.book AND
366 r.id = bl.rating AND r.rating <> 0) avg_rating,
367 value AS sort
368 FROM {table_name};
369
370 DROP VIEW IF EXISTS tag_browser_filtered_{table_name};
371 CREATE VIEW tag_browser_filtered_{table_name} AS SELECT
372 id,
373 value,
374 (SELECT COUNT({link_table_name}.id) FROM {link_table_name} WHERE value={table_name}.id AND
375 books_list_filter(book)) count,
376 (SELECT AVG(r.rating)
377 FROM {link_table_name},
378 books_ratings_link AS bl,
379 ratings AS r
380 WHERE {link_table_name}.value={table_name}.id AND bl.book={link_table_name}.book AND
381 r.id = bl.rating AND r.rating <> 0 AND
382 books_list_filter(bl.book)) avg_rating,
383 value AS sort
384 FROM {table_name};
385 '''
386 assert self.db is not None
387 self.db.execute(script)
388
389 for field in self.field_metadata.values():
390 if field['is_category'] and not field['is_custom'] and 'link_column' in field:
391 table = self.db.get(
392 "SELECT name FROM sqlite_master WHERE type='table' AND name=?",
393 ('books_{}_link'.format(field['table']),),
394 all=False,
395 )
396 if table is not None:
397 create_std_tag_browser_view(field['table'], field['link_column'], field['column'], field['category_sort'])
398
399 db_tables = self.db.get('''SELECT name FROM sqlite_master
400 WHERE type='table'
401 ORDER BY name''')
402 tables = []
403 for (table,) in db_tables:
404 tables.append(table)
405 for table in tables:
406 link_table = f'books_{table}_link'
407 if table.startswith('custom_column_') and link_table in tables:
408 create_cust_tag_browser_view(table, link_table)
409
410 self.db.execute('UPDATE authors SET sort=author_to_author_sort(name)')
411
412 def upgrade_version_12(self):
413 "DB based preference store"
414 assert self.db is not None
415 script = '''
416 DROP TABLE IF EXISTS preferences;
417 CREATE TABLE preferences(id INTEGER PRIMARY KEY,
418 key TEXT NOT NULL,
419 val TEXT NOT NULL,
420 UNIQUE(key));
421 '''
422 self.db.execute(script)
423
424 def upgrade_version_13(self):
425 "Dirtied table for OPF metadata backups"
426 assert self.db is not None
427 script = '''
428 DROP TABLE IF EXISTS metadata_dirtied;
429 CREATE TABLE metadata_dirtied(id INTEGER PRIMARY KEY,
430 book INTEGER NOT NULL,
431 UNIQUE(book));
432 INSERT INTO metadata_dirtied (book) SELECT id FROM books;
433 '''
434 self.db.execute(script)
435
436 def upgrade_version_14(self):
437 "Cache has_cover"
438 assert self.db is not None
439 self.db.execute('ALTER TABLE books ADD COLUMN has_cover BOOL DEFAULT 0')
440 data = self.db.get('SELECT id,path FROM books', all=True)
441
442 def has_cover(path):
443 if path:
444 path = os.path.join(self.library_path, path.replace('/', os.sep), 'cover.jpg')
445 return os.path.exists(path)
446 return False
447
448 ids = [(x[0],) for x in data if has_cover(x[1])]
449 self.db.executemany('UPDATE books SET has_cover=1 WHERE id=?', ids)
450
451 def upgrade_version_15(self):
452 "Remove commas from tags"
453 assert self.db is not None
454 self.db.execute("UPDATE OR IGNORE tags SET name=REPLACE(name, ',', ';')")
455 self.db.execute("UPDATE OR IGNORE tags SET name=REPLACE(name, ',', ';;')")
456 self.db.execute("UPDATE OR IGNORE tags SET name=REPLACE(name, ',', '')")
457
458 def upgrade_version_16(self):
459 assert self.db is not None
460 self.db.execute('''
461 DROP TRIGGER IF EXISTS books_update_trg;
462 CREATE TRIGGER books_update_trg
463 AFTER UPDATE ON books
464 BEGIN
465 UPDATE books SET sort=title_sort(NEW.title)
466 WHERE id=NEW.id AND OLD.title <> NEW.title;
467 END;
468 ''')
469
470 def upgrade_version_17(self):
471 "custom book data table (for plugins)"
472 assert self.db is not None
473 script = '''
474 DROP TABLE IF EXISTS books_plugin_data;
475 CREATE TABLE books_plugin_data(id INTEGER PRIMARY KEY,
476 book INTEGER NOT NULL,
477 name TEXT NOT NULL,
478 val TEXT NOT NULL,
479 UNIQUE(book,name));
480 DROP TRIGGER IF EXISTS books_delete_trg;
481 CREATE TRIGGER books_delete_trg
482 AFTER DELETE ON books
483 BEGIN
484 DELETE FROM books_authors_link WHERE book=OLD.id;
485 DELETE FROM books_publishers_link WHERE book=OLD.id;
486 DELETE FROM books_ratings_link WHERE book=OLD.id;
487 DELETE FROM books_series_link WHERE book=OLD.id;
488 DELETE FROM books_tags_link WHERE book=OLD.id;
489 DELETE FROM data WHERE book=OLD.id;
490 DELETE FROM comments WHERE book=OLD.id;
491 DELETE FROM conversion_options WHERE book=OLD.id;
492 DELETE FROM books_plugin_data WHERE book=OLD.id;
493 END;
494 '''
495 self.db.execute(script)
496
497 def upgrade_version_18(self):
498 """
499 Add a library UUID.
500 Add an identifiers table.
501 Add a languages table.
502 Add a last_modified column.
503 NOTE: You cannot downgrade after this update, if you do
504 any changes you make to book isbns will be lost.
505 """
506 assert self.db is not None
507 script = '''
508 DROP TABLE IF EXISTS library_id;
509 CREATE TABLE library_id ( id INTEGER PRIMARY KEY,
510 uuid TEXT NOT NULL,
511 UNIQUE(uuid)
512 );
513
514 DROP TABLE IF EXISTS identifiers;
515 CREATE TABLE identifiers ( id INTEGER PRIMARY KEY,
516 book INTEGER NOT NULL,
517 type TEXT NOT NULL DEFAULT "isbn" COLLATE NOCASE,
518 val TEXT NOT NULL COLLATE NOCASE,
519 UNIQUE(book, type)
520 );
521
522 DROP TABLE IF EXISTS languages;
523 CREATE TABLE languages ( id INTEGER PRIMARY KEY,
524 lang_code TEXT NOT NULL COLLATE NOCASE,
525 UNIQUE(lang_code)
526 );
527
528 DROP TABLE IF EXISTS books_languages_link;
529 CREATE TABLE books_languages_link ( id INTEGER PRIMARY KEY,
530 book INTEGER NOT NULL,
531 lang_code INTEGER NOT NULL,
532 item_order INTEGER NOT NULL DEFAULT 0,
533 UNIQUE(book, lang_code)
534 );
535
536 DROP TRIGGER IF EXISTS fkc_delete_on_languages;
537 CREATE TRIGGER fkc_delete_on_languages
538 BEFORE DELETE ON languages
539 BEGIN
540 SELECT CASE
541 WHEN (SELECT COUNT(id) FROM books_languages_link WHERE lang_code=OLD.id) > 0
542 THEN RAISE(ABORT, 'Foreign key violation: language is still referenced')
543 END;
544 END;
545
546 DROP TRIGGER IF EXISTS fkc_delete_on_languages_link;
547 CREATE TRIGGER fkc_delete_on_languages_link
548 BEFORE INSERT ON books_languages_link
549 BEGIN
550 SELECT CASE
551 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
552 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
553 WHEN (SELECT id from languages WHERE id=NEW.lang_code) IS NULL
554 THEN RAISE(ABORT, 'Foreign key violation: lang_code not in languages')
555 END;
556 END;
557
558 DROP TRIGGER IF EXISTS fkc_update_books_languages_link_a;
559 CREATE TRIGGER fkc_update_books_languages_link_a
560 BEFORE UPDATE OF book ON books_languages_link
561 BEGIN
562 SELECT CASE
563 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
564 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
565 END;
566 END;
567 DROP TRIGGER IF EXISTS fkc_update_books_languages_link_b;
568 CREATE TRIGGER fkc_update_books_languages_link_b
569 BEFORE UPDATE OF lang_code ON books_languages_link
570 BEGIN
571 SELECT CASE
572 WHEN (SELECT id from languages WHERE id=NEW.lang_code) IS NULL
573 THEN RAISE(ABORT, 'Foreign key violation: lang_code not in languages')
574 END;
575 END;
576
577 DROP INDEX IF EXISTS books_languages_link_aidx;
578 CREATE INDEX books_languages_link_aidx ON books_languages_link (lang_code);
579 DROP INDEX IF EXISTS books_languages_link_bidx;
580 CREATE INDEX books_languages_link_bidx ON books_languages_link (book);
581 DROP INDEX IF EXISTS languages_idx;
582 CREATE INDEX languages_idx ON languages (lang_code COLLATE NOCASE);
583
584 DROP TRIGGER IF EXISTS books_delete_trg;
585 CREATE TRIGGER books_delete_trg
586 AFTER DELETE ON books
587 BEGIN
588 DELETE FROM books_authors_link WHERE book=OLD.id;
589 DELETE FROM books_publishers_link WHERE book=OLD.id;
590 DELETE FROM books_ratings_link WHERE book=OLD.id;
591 DELETE FROM books_series_link WHERE book=OLD.id;
592 DELETE FROM books_tags_link WHERE book=OLD.id;
593 DELETE FROM books_languages_link WHERE book=OLD.id;
594 DELETE FROM data WHERE book=OLD.id;
595 DELETE FROM comments WHERE book=OLD.id;
596 DELETE FROM conversion_options WHERE book=OLD.id;
597 DELETE FROM books_plugin_data WHERE book=OLD.id;
598 DELETE FROM identifiers WHERE book=OLD.id;
599 END;
600
601 INSERT INTO identifiers (book, val) SELECT id,isbn FROM books WHERE isbn;
602
603 ALTER TABLE books ADD COLUMN last_modified TIMESTAMP NOT NULL DEFAULT "{}";
604
605 '''.format(isoformat(DEFAULT_DATE, sep=' '))
606 # Sqlite does not support non constant default values in alter
607 # statements
608 self.db.execute(script)
609
610 def upgrade_version_19(self):
611 assert self.db is not None
612 recipes = self.db.get('SELECT id,title,script FROM feeds')
613 if recipes:
614 from calibre.web.feeds.recipes import custom_recipe_filename, custom_recipes
615
616 bdir = os.path.dirname(custom_recipes.file_path)
617 for id_, title, script in recipes:
618 existing = frozenset(map(int, custom_recipes))
619 if id_ in existing:
620 id_ = max(existing) + 1000
621 id_ = str(id_)
622 fname = custom_recipe_filename(id_, title)
623 custom_recipes[id_] = (title, fname)
624 if isinstance(script, str):
625 script = script.encode('utf-8')
626 with open(os.path.join(bdir, fname), 'wb') as f:
627 f.write(script)
628
629 def upgrade_version_20(self):
630 """
631 Add a link column to the authors table.
632 """
633 assert self.db is not None
634 script = '''
635 ALTER TABLE authors ADD COLUMN link TEXT NOT NULL DEFAULT "";
636 '''
637 self.db.execute(script)
638
639 def upgrade_version_21(self):
640 """
641 Write the series sort into the existing sort column in the series table
642 """
643 assert self.db is not None
644 script = '''
645 DROP TRIGGER IF EXISTS series_insert_trg;
646 DROP TRIGGER IF EXISTS series_update_trg;
647
648 UPDATE series SET sort=title_sort(name);
649
650 CREATE TRIGGER series_insert_trg
651 AFTER INSERT ON series
652 BEGIN
653 UPDATE series SET sort=title_sort(NEW.name) WHERE id=NEW.id;
654 END;
655
656 CREATE TRIGGER series_update_trg
657 AFTER UPDATE ON series
658 BEGIN
659 UPDATE series SET sort=title_sort(NEW.name) WHERE id=NEW.id;
660 END;
661 '''
662 self.db.execute(script)
663
664 def upgrade_version_22(self):
665 """Create the last_read_positions table"""
666 assert self.db is not None
667 self.db.execute('''
668 DROP TABLE IF EXISTS last_read_positions;
669 CREATE TABLE last_read_positions ( id INTEGER PRIMARY KEY,
670 book INTEGER NOT NULL,
671 format TEXT NOT NULL COLLATE NOCASE,
672 user TEXT NOT NULL,
673 device TEXT NOT NULL,
674 cfi TEXT NOT NULL,
675 epoch REAL NOT NULL,
676 pos_frac REAL NOT NULL DEFAULT 0,
677 UNIQUE(user, device, book, format)
678 );
679 DROP INDEX IF EXISTS lrp_idx;
680 CREATE INDEX lrp_idx ON last_read_positions (book);
681
682 DROP TRIGGER IF EXISTS books_delete_trg;
683 CREATE TRIGGER books_delete_trg
684 AFTER DELETE ON books
685 BEGIN
686 DELETE FROM books_authors_link WHERE book=OLD.id;
687 DELETE FROM books_publishers_link WHERE book=OLD.id;
688 DELETE FROM books_ratings_link WHERE book=OLD.id;
689 DELETE FROM books_series_link WHERE book=OLD.id;
690 DELETE FROM books_tags_link WHERE book=OLD.id;
691 DELETE FROM books_languages_link WHERE book=OLD.id;
692 DELETE FROM data WHERE book=OLD.id;
693 DELETE FROM last_read_positions WHERE book=OLD.id;
694 DELETE FROM comments WHERE book=OLD.id;
695 DELETE FROM conversion_options WHERE book=OLD.id;
696 DELETE FROM books_plugin_data WHERE book=OLD.id;
697 DELETE FROM identifiers WHERE book=OLD.id;
698 END;
699
700 DROP TRIGGER IF EXISTS fkc_lrp_insert;
701 DROP TRIGGER IF EXISTS fkc_lrp_update;
702 CREATE TRIGGER fkc_lrp_insert
703 BEFORE INSERT ON last_read_positions
704 BEGIN
705 SELECT CASE
706 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
707 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
708 END;
709 END;
710 CREATE TRIGGER fkc_lrp_update
711 BEFORE UPDATE OF book ON last_read_positions
712 BEGIN
713 SELECT CASE
714 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
715 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
716 END;
717 END;
718
719 ''')
720
721 def upgrade_version_23(self):
722 """Create the annotations table"""
723 assert self.db is not None
724 self.db.execute('''
725 DROP TABLE IF EXISTS annotations_dirtied;
726 CREATE TABLE annotations_dirtied(id INTEGER PRIMARY KEY,
727 book INTEGER NOT NULL,
728 UNIQUE(book));
729 DROP TABLE IF EXISTS annotations;
730 CREATE TABLE annotations ( id INTEGER PRIMARY KEY,
731 book INTEGER NOT NULL,
732 format TEXT NOT NULL COLLATE NOCASE,
733 user_type TEXT NOT NULL,
734 user TEXT NOT NULL,
735 timestamp REAL NOT NULL,
736 annot_id TEXT NOT NULL,
737 annot_type TEXT NOT NULL,
738 annot_data TEXT NOT NULL,
739 searchable_text TEXT NOT NULL DEFAULT "",
740 UNIQUE(book, user_type, user, format, annot_type, annot_id)
741 );
742
743 DROP INDEX IF EXISTS annot_idx;
744 CREATE INDEX annot_idx ON annotations (book);
745
746 DROP TABLE IF EXISTS annotations_fts;
747 DROP TABLE IF EXISTS annotations_fts_stemmed;
748 CREATE VIRTUAL TABLE annotations_fts USING fts5(searchable_text,
749 content = 'annotations', content_rowid = 'id', tokenize = 'unicode61 remove_diacritics 2');
750 CREATE VIRTUAL TABLE annotations_fts_stemmed USING fts5(searchable_text,
751 content = 'annotations', content_rowid = 'id', tokenize = 'porter unicode61 remove_diacritics 2');
752
753 DROP TRIGGER IF EXISTS annotations_fts_insert_trg;
754 CREATE TRIGGER annotations_fts_insert_trg AFTER INSERT ON annotations
755 BEGIN
756 INSERT INTO annotations_fts(rowid, searchable_text) VALUES (NEW.id, NEW.searchable_text);
757 INSERT INTO annotations_fts_stemmed(rowid, searchable_text) VALUES (NEW.id, NEW.searchable_text);
758 END;
759
760 DROP TRIGGER IF EXISTS annotations_fts_delete_trg;
761 CREATE TRIGGER annotations_fts_delete_trg AFTER DELETE ON annotations
762 BEGIN
763 INSERT INTO annotations_fts(annotations_fts, rowid, searchable_text) VALUES('delete', OLD.id, OLD.searchable_text);
764 INSERT INTO annotations_fts_stemmed(annotations_fts_stemmed, rowid, searchable_text) VALUES('delete', OLD.id, OLD.searchable_text);
765 END;
766
767 DROP TRIGGER IF EXISTS annotations_fts_update_trg;
768 CREATE TRIGGER annotations_fts_update_trg AFTER UPDATE ON annotations
769 BEGIN
770 INSERT INTO annotations_fts(annotations_fts, rowid, searchable_text) VALUES('delete', OLD.id, OLD.searchable_text);
771 INSERT INTO annotations_fts(rowid, searchable_text) VALUES (NEW.id, NEW.searchable_text);
772 INSERT INTO annotations_fts_stemmed(annotations_fts_stemmed, rowid, searchable_text) VALUES('delete', OLD.id, OLD.searchable_text);
773 INSERT INTO annotations_fts_stemmed(rowid, searchable_text) VALUES (NEW.id, NEW.searchable_text);
774 END;
775
776 DROP TRIGGER IF EXISTS books_delete_trg;
777 CREATE TRIGGER books_delete_trg
778 AFTER DELETE ON books
779 BEGIN
780 DELETE FROM books_authors_link WHERE book=OLD.id;
781 DELETE FROM books_publishers_link WHERE book=OLD.id;
782 DELETE FROM books_ratings_link WHERE book=OLD.id;
783 DELETE FROM books_series_link WHERE book=OLD.id;
784 DELETE FROM books_tags_link WHERE book=OLD.id;
785 DELETE FROM books_languages_link WHERE book=OLD.id;
786 DELETE FROM data WHERE book=OLD.id;
787 DELETE FROM last_read_positions WHERE book=OLD.id;
788 DELETE FROM annotations WHERE book=OLD.id;
789 DELETE FROM comments WHERE book=OLD.id;
790 DELETE FROM conversion_options WHERE book=OLD.id;
791 DELETE FROM books_plugin_data WHERE book=OLD.id;
792 DELETE FROM identifiers WHERE book=OLD.id;
793 END;
794
795 DROP TRIGGER IF EXISTS fkc_annot_insert;
796 DROP TRIGGER IF EXISTS fkc_annot_update;
797 CREATE TRIGGER fkc_annot_insert
798 BEFORE INSERT ON annotations
799 BEGIN
800 SELECT CASE
801 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
802 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
803 END;
804 END;
805 CREATE TRIGGER fkc_annot_update
806 BEFORE UPDATE OF book ON annotations
807 BEGIN
808 SELECT CASE
809 WHEN (SELECT id from books WHERE id=NEW.book) IS NULL
810 THEN RAISE(ABORT, 'Foreign key violation: book not in books')
811 END;
812 END;
813
814 ''')
815
816 def upgrade_version_24(self):
817 assert self.db is not None
818 self.db.reindex_annotations()
819
820 def upgrade_version_25(self):
821 assert self.db is not None
822 alters = []
823 for record in self.db.execute('SELECT label,name,datatype,editable,display,normalized,id,is_multiple FROM custom_columns'):
824 data = {
825 'label': record[0],
826 'name': record[1],
827 'datatype': record[2],
828 'editable': bool(record[3]),
829 'display': record[4],
830 'normalized': bool(record[5]),
831 'num': record[6],
832 'is_multiple': bool(record[7]),
833 }
834 if data['normalized']:
835 tn = 'custom_column_{}'.format(data['num'])
836 alters.append(f"ALTER TABLE {tn} ADD COLUMN link TEXT NOT NULL DEFAULT '';")
837
838 alters.append("ALTER TABLE publishers ADD COLUMN link TEXT NOT NULL DEFAULT '';")
839 alters.append("ALTER TABLE series ADD COLUMN link TEXT NOT NULL DEFAULT '';")
840 alters.append("ALTER TABLE tags ADD COLUMN link TEXT NOT NULL DEFAULT '';")
841 # These aren't necessary in that there is no UI to set links, but having them
842 # makes the code uniform
843 alters.append("ALTER TABLE languages ADD COLUMN link TEXT NOT NULL DEFAULT '';")
844 alters.append("ALTER TABLE ratings ADD COLUMN link TEXT NOT NULL DEFAULT '';")
845 self.db.execute('\n'.join(alters))
846
847 def upgrade_version_26(self):
848 "Drop unused columns from books and create pages table"
849 assert self.db is not None
850 columns = {x[0] for x in self.db.execute('SELECT name FROM pragma_table_info("books")')}
851 statements = [
852 '''
853 CREATE TABLE books_pages_link (
854 book INTEGER PRIMARY KEY,
855 pages INTEGER DEFAULT 0 NOT NULL,
856 algorithm INTEGER DEFAULT 0 NOT NULL,
857 format TEXT DEFAULT '' NOT NULL COLLATE NOCASE,
858 format_size INTEGER DEFAULT 0 NOT NULL,
859 timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
860 needs_scan INTEGER NOT NULL DEFAULT 0 CHECK(needs_scan IN (0, 1)),
861 FOREIGN KEY (book) REFERENCES books(id) ON DELETE CASCADE
862 );
863 CREATE TRIGGER books_pages_link_create_trigger AFTER INSERT ON books FOR EACH ROW
864 BEGIN
865 INSERT INTO books_pages_link(book) VALUES(NEW.id);
866 END;
867 CREATE INDEX books_pages_link_pidx ON books_pages_link (needs_scan);
868 INSERT INTO books_pages_link(book,needs_scan) SELECT id,1 FROM books;
869
870 DROP VIEW meta;
871 CREATE VIEW meta AS
872 SELECT id, title,
873 (SELECT sortconcat(bal.id, name) FROM books_authors_link AS bal JOIN authors ON(author = authors.id) WHERE book = books.id) authors,
874 (SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,
875 (SELECT rating FROM ratings WHERE ratings.id IN (SELECT rating from books_ratings_link WHERE book=books.id)) rating,
876 timestamp,
877 (SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,
878 (SELECT concat(name) FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,
879 (SELECT text FROM comments WHERE book=books.id) comments,
880 (SELECT name FROM series WHERE series.id IN (SELECT series FROM books_series_link WHERE book=books.id)) series,
881 series_index,
882 sort,
883 author_sort,
884 (SELECT concat(format) FROM data WHERE data.book=books.id) formats,
885 path,
886 pubdate,
887 uuid
888 FROM books;
889
890 PRAGMA application_id = 0x63616c69;
891 ''',
892 ]
893 for x in {'flags', 'isbn', 'lccn'} & columns:
894 statements.append(f'ALTER TABLE books DROP COLUMN {x};')
895 self.db.execute('\n'.join(statements))