27
27
from calibre.utils.config import to_json, from_json, prefs, tweaks
28
28
from calibre.utils.date import utcfromtimestamp, parse_date
29
29
from calibre.utils.filenames import (
30
is_case_sensitive, samefile, hardlink_file, ascii_filename, WindowsAtomicFolderMove, atomic_rename)
30
is_case_sensitive, samefile, hardlink_file, ascii_filename,
31
WindowsAtomicFolderMove, atomic_rename, remove_dir_if_empty)
31
32
from calibre.utils.magick.draw import save_cover_data_to
32
33
from calibre.utils.formatter_functions import load_user_template_functions
33
34
from calibre.db.tables import (OneToOneTable, ManyToOneTable, ManyToManyTable,
103
104
def __delitem__(self, key):
104
105
dict.__delitem__(self, key)
105
self.db.conn.execute('DELETE FROM preferences WHERE key=?', (key,))
106
self.db.execute('DELETE FROM preferences WHERE key=?', (key,))
107
108
def __setitem__(self, key, val):
108
109
if self.disable_setting:
110
111
raw = self.to_raw(val)
111
self.db.conn.execute('INSERT OR REPLACE INTO preferences (key,val) VALUES (?,?)', (key,
112
self.db.execute('INSERT OR REPLACE INTO preferences (key,val) VALUES (?,?)', (key, raw))
113
113
dict.__setitem__(self, key, val)
115
115
def set(self, key, val):
235
235
class Connection(apsw.Connection): # {{{
237
BUSY_TIMEOUT = 2000 # milliseconds
237
BUSY_TIMEOUT = 10000 # milliseconds
239
239
def __init__(self, path):
240
240
apsw.Connection.__init__(self, path)
348
348
os.makedirs(self.library_path)
349
349
self.is_case_sensitive = is_case_sensitive(self.library_path)
351
SchemaUpgrade(self.conn, self.library_path, self.field_metadata)
351
SchemaUpgrade(self, self.library_path, self.field_metadata)
353
353
# Guarantee that the library_id is set
356
356
# Fix legacy triggers and columns
357
self.conn.execute('''
358
358
DROP TRIGGER IF EXISTS author_insert_trg;
359
359
CREATE TEMP TRIGGER author_insert_trg
360
360
AFTER INSERT ON authors
527
527
'SELECT id FROM custom_columns WHERE mark_for_delete=1'):
529
529
table, lt = self.custom_table_names(num)
530
self.conn.execute('''\
531
531
DROP INDEX IF EXISTS {table}_idx;
532
532
DROP INDEX IF EXISTS {lt}_aidx;
533
533
DROP INDEX IF EXISTS {lt}_bidx;
544
544
'''.format(table=table, lt=lt)
546
546
self.prefs.set('update_all_last_mod_dates_on_start', True)
547
self.conn.execute('DELETE FROM custom_columns WHERE mark_for_delete=1')
547
self.execute('DELETE FROM custom_columns WHERE mark_for_delete=1')
549
549
# Load metadata for custom columns
550
550
self.custom_column_label_map, self.custom_column_num_map = {}, {}
600
600
for data in remove:
601
601
prints('WARNING: Custom column %r not found, removing.' %
603
self.conn.execute('DELETE FROM custom_columns WHERE id=?',
603
self.execute('DELETE FROM custom_columns WHERE id=?',
608
self.conn.execute('''\
609
609
CREATE TEMP TRIGGER custom_books_delete_trg
610
610
AFTER DELETE ON books
787
787
self._conn = Connection(self.dbpath)
788
788
return self._conn
790
def execute(self, sql, bindings=None):
792
return self.conn.cursor().execute(sql, bindings)
794
# This can happen if the computer was suspended see for example:
795
# https://bugs.launchpad.net/bugs/1286522. Try to reopen the db
796
if not self.conn.getautocommit():
797
raise # We are in a transaction, re-opening the db will fail anyway
798
self.reopen(force=True)
799
return self.conn.cursor().execute(sql, bindings)
801
def executemany(self, sql, sequence_of_bindings):
803
with self.conn: # Disable autocommit mode, for performance
804
return self.conn.cursor().executemany(sql, sequence_of_bindings)
806
# This can happen if the computer was suspended see for example:
807
# https://bugs.launchpad.net/bugs/1286522. Try to reopen the db
808
if not self.conn.getautocommit():
809
raise # We are in a transaction, re-opening the db will fail anyway
810
self.reopen(force=True)
811
with self.conn: # Disable autocommit mode, for performance
812
return self.conn.cursor().executemany(sql, sequence_of_bindings)
814
def get(self, *args, **kw):
815
ans = self.execute(*args)
816
if kw.get('all', True):
817
return ans.fetchall()
820
except (StopIteration, IndexError):
823
def last_insert_rowid(self):
824
return self.conn.last_insert_rowid()
790
826
def custom_field_name(self, label=None, num=None):
791
827
if label is not None:
792
828
return self.field_metadata.custom_field_prefix + label
800
836
def set_custom_column_metadata(self, num, name=None, label=None, is_editable=None, display=None):
802
838
if name is not None:
803
self.conn.execute('UPDATE custom_columns SET name=? WHERE id=?', (name, num))
839
self.execute('UPDATE custom_columns SET name=? WHERE id=?', (name, num))
805
841
if label is not None:
806
self.conn.execute('UPDATE custom_columns SET label=? WHERE id=?', (label, num))
842
self.execute('UPDATE custom_columns SET label=? WHERE id=?', (label, num))
808
844
if is_editable is not None:
809
self.conn.execute('UPDATE custom_columns SET editable=? WHERE id=?', (bool(is_editable), num))
845
self.execute('UPDATE custom_columns SET editable=? WHERE id=?', (bool(is_editable), num))
810
846
self.custom_column_num_map[num]['is_editable'] = bool(is_editable)
812
848
if display is not None:
813
self.conn.execute('UPDATE custom_columns SET display=? WHERE id=?', (json.dumps(display), num))
849
self.execute('UPDATE custom_columns SET display=? WHERE id=?', (json.dumps(display), num))
815
851
# Note: the caller is responsible for scheduling a metadata backup if necessary
826
862
normalized = datatype not in ('datetime', 'comments', 'int', 'bool',
827
863
'float', 'composite')
828
864
is_multiple = is_multiple and datatype in ('text', 'composite')
831
867
'custom_columns(label,name,datatype,is_multiple,editable,display,normalized)'
832
868
'VALUES (?,?,?,?,?,?,?)'),
968
1004
'''.format(table=table),
970
1006
script = ' \n'.join(lines)
971
self.conn.execute(script)
1007
self.execute(script)
972
1008
self.prefs.set('update_all_last_mod_dates_on_start', True)
976
1012
def delete_custom_column(self, label=None, num=None):
977
1013
data = self.custom_field_metadata(label, num)
978
self.conn.execute('UPDATE custom_columns SET mark_for_delete=1 WHERE id=?', (data['num'],))
1014
self.execute('UPDATE custom_columns SET mark_for_delete=1 WHERE id=?', (data['num'],))
1016
def close(self, force=False):
981
1017
if getattr(self, '_conn', None) is not None:
1018
self._conn.close(force)
1021
def reopen(self, force=False):
987
1023
self._conn = None
1029
1065
return self.conn.get('pragma user_version;', all=False)
1031
1067
def fset(self, val):
1032
self.conn.execute('pragma user_version=%d'%int(val))
1068
self.execute('pragma user_version=%d'%int(val))
1034
1070
return property(doc=doc, fget=fget, fset=fset)
1149
1185
Read all data from the db into the python in-memory tables
1152
with self.conn: # Use a single transaction, to ensure nothing modifies
1153
# the db while we are reading
1188
with self.conn: # Use a single transaction, to ensure nothing modifies the db while we are reading
1154
1189
for table in self.tables.itervalues():
1156
1191
table.read(self)
1496
1531
return f.read()
1498
1533
def remove_books(self, path_map, permanent=False):
1499
self.conn.executemany(
1500
1535
'DELETE FROM books WHERE id=?', [(x,) for x in path_map])
1501
1536
paths = {os.path.join(self.library_path, x) for x in path_map.itervalues() if x}
1502
1537
paths = {x for x in paths if os.path.exists(x) and self.is_deletable(x)}
1504
1539
for path in paths:
1505
1540
self.rmtree(path)
1507
os.rmdir(os.path.dirname(path))
1508
except OSError as e:
1509
if e.errno != errno.ENOTEMPTY:
1541
remove_dir_if_empty(os.path.dirname(path), ignore_metadata_caches=True)
1512
1543
delete_service().delete_books(paths, self.library_path)
1514
1545
def add_custom_data(self, name, val_map, delete_first):
1515
1546
if delete_first:
1516
self.conn.execute('DELETE FROM books_plugin_data WHERE name=?', (name, ))
1517
self.conn.executemany(
1547
self.execute('DELETE FROM books_plugin_data WHERE name=?', (name, ))
1518
1549
'INSERT OR REPLACE INTO books_plugin_data (book, name, val) VALUES (?, ?, ?)',
1519
1550
[(book_id, name, json.dumps(val, default=to_json))
1520
1551
for book_id, val in val_map.iteritems()])
1530
1561
if len(book_ids) == 1:
1531
1562
bid = next(iter(book_ids))
1532
1563
ans = {book_id:safe_load(val) for book_id, val in
1533
self.conn.execute('SELECT book, val FROM books_plugin_data WHERE book=? AND name=?', (bid, name))}
1564
self.execute('SELECT book, val FROM books_plugin_data WHERE book=? AND name=?', (bid, name))}
1534
1565
return ans or {bid:default}
1537
for book_id, val in self.conn.execute(
1568
for book_id, val in self.execute(
1538
1569
'SELECT book, val FROM books_plugin_data WHERE name=?', (name,)):
1539
1570
if not book_ids or book_id in book_ids:
1540
1571
val = safe_load(val)
1544
1575
def delete_custom_book_data(self, name, book_ids):
1546
self.conn.executemany('DELETE FROM books_plugin_data WHERE book=? AND name=?',
1577
self.executemany('DELETE FROM books_plugin_data WHERE book=? AND name=?',
1547
1578
[(book_id, name) for book_id in book_ids])
1549
self.conn.execute('DELETE FROM books_plugin_data WHERE name=?', (name,))
1580
self.execute('DELETE FROM books_plugin_data WHERE name=?', (name,))
1551
1582
def get_ids_for_custom_book_data(self, name):
1552
return frozenset(r[0] for r in self.conn.execute('SELECT book FROM books_plugin_data WHERE name=?', (name,)))
1583
return frozenset(r[0] for r in self.execute('SELECT book FROM books_plugin_data WHERE name=?', (name,)))
1554
1585
def conversion_options(self, book_id, fmt):
1555
1586
for (data,) in self.conn.get('SELECT data FROM conversion_options WHERE book=? AND format=?', (book_id, fmt.upper())):
1559
1590
def has_conversion_options(self, ids, fmt='PIPE'):
1560
1591
ids = frozenset(ids)
1561
self.conn.execute('DROP TABLE IF EXISTS conversion_options_temp; CREATE TEMP TABLE conversion_options_temp (id INTEGER PRIMARY KEY);')
1562
self.conn.executemany('INSERT INTO conversion_options_temp VALUES (?)', [(x,) for x in ids])
1563
for (book_id,) in self.conn.get(
1564
'SELECT book FROM conversion_options WHERE format=? AND book IN (SELECT id FROM conversion_options_temp)', (fmt.upper(),)):
1593
self.execute('DROP TABLE IF EXISTS conversion_options_temp; CREATE TEMP TABLE conversion_options_temp (id INTEGER PRIMARY KEY);')
1594
self.executemany('INSERT INTO conversion_options_temp VALUES (?)', [(x,) for x in ids])
1595
for (book_id,) in self.conn.get(
1596
'SELECT book FROM conversion_options WHERE format=? AND book IN (SELECT id FROM conversion_options_temp)', (fmt.upper(),)):
1568
1600
def delete_conversion_options(self, book_ids, fmt):
1569
self.conn.executemany('DELETE FROM conversion_options WHERE book=? AND format=?',
1601
self.executemany('DELETE FROM conversion_options WHERE book=? AND format=?',
1570
1602
[(book_id, fmt.upper()) for book_id in book_ids])
1572
1604
def set_conversion_options(self, options, fmt):
1573
1605
options = [(book_id, fmt.upper(), buffer(cPickle.dumps(data, -1))) for book_id, data in options.iteritems()]
1574
self.conn.executemany('INSERT OR REPLACE INTO conversion_options(book,format,data) VALUES (?,?,?)', options)
1606
self.executemany('INSERT OR REPLACE INTO conversion_options(book,format,data) VALUES (?,?,?)', options)
1576
1608
def get_top_level_move_items(self, all_paths):
1577
1609
items = set(os.listdir(self.library_path))
1629
1661
def restore_book(self, book_id, path, formats):
1630
self.conn.execute('UPDATE books SET path=? WHERE id=?', (path.replace(os.sep, '/'), book_id))
1662
self.execute('UPDATE books SET path=? WHERE id=?', (path.replace(os.sep, '/'), book_id))
1631
1663
vals = [(book_id, fmt, size, name) for fmt, size, name in formats]
1632
self.conn.executemany('INSERT INTO data (book,format,uncompressed_size,name) VALUES (?,?,?,?)', vals)
1664
self.executemany('INSERT INTO data (book,format,uncompressed_size,name) VALUES (?,?,?,?)', vals)
added
removed
src/calibre/db/backend.py
