11
11
from itertools import repeat
12
12
from datetime import datetime
14
from PyQt4.QtCore import QCoreApplication, QThread, QReadWriteLock
15
from PyQt4.QtGui import QApplication, QImage
18
from calibre.library import title_sort
14
from PyQt4.QtCore import QThread, QReadWriteLock
16
from PIL import Image as PILImage
19
import Image as PILImage
22
from PyQt4.QtGui import QImage
24
from calibre.ebooks.metadata import title_sort
19
25
from calibre.library.database import LibraryDatabase
20
26
from calibre.library.sqlite import connect, IntegrityError
21
27
from calibre.utils.search_query_parser import SearchQueryParser
23
29
MetaInformation, authors_to_sort_string
24
30
from calibre.ebooks.metadata.meta import get_metadata, set_metadata, \
25
31
metadata_from_formats
26
from calibre.ebooks.metadata.opf2 import OPFCreator
32
from calibre.ebooks.metadata.opf2 import metadata_to_opf
27
33
from calibre.constants import preferred_encoding, iswindows, isosx, filesystem_encoding
28
34
from calibre.ptempfile import PersistentTemporaryFile
29
35
from calibre.customize.ui import run_plugins_on_import
31
from calibre import sanitize_file_name
37
from calibre.utils.filenames import ascii_filename, shorten_components_to, \
32
39
from calibre.ebooks import BOOK_EXTENSIONS
43
def delete_tree(path):
45
winshell.delete_file(path, silent=True, no_confirm=True)
50
def delete_tree(path, permanent=False):
47
52
shutil.rmtree(path)
56
winshell.delete_file(path, silent=True, no_confirm=True)
49
60
copyfile = os.link if hasattr(os, 'link') else shutil.copyfile
51
62
FIELD_MAP = {'id':0, 'title':1, 'authors':2, 'publisher':3, 'rating':4, 'timestamp':5,
52
63
'size':6, 'tags':7, 'comments':8, 'series':9, 'series_index':10,
53
'sort':11, 'author_sort':12, 'formats':13, 'isbn':14, 'path':15}
64
'sort':11, 'author_sort':12, 'formats':13, 'isbn':14, 'path':15,
65
'lccn':16, 'pubdate':17, 'flags':18, 'cover':19}
54
66
INDEX_MAP = dict(zip(FIELD_MAP.values(), FIELD_MAP.keys()))
197
209
query = query.decode('utf-8')
198
210
if location in ('tag', 'author', 'format'):
200
all = ('title', 'authors', 'publisher', 'tags', 'comments', 'series', 'formats')
212
all = ('title', 'authors', 'publisher', 'tags', 'comments', 'series', 'formats', 'isbn', 'rating', 'cover')
203
215
MAP[x] = FIELD_MAP[x]
216
EXCLUDE_FIELDS = [MAP['rating'], MAP['cover']]
204
217
location = [location] if location != 'all' else list(MAP.keys())
205
218
for i, loc in enumerate(location):
206
219
location[i] = MAP[loc]
221
rating_query = int(query) * 2
207
224
for item in self._data:
208
225
if item is None: continue
209
226
for loc in location:
210
if item[loc] and query in item[loc].lower():
227
if query == 'false' and not item[loc]:
228
if isinstance(item[loc], basestring):
229
if item[loc].strip() != '':
233
if query == 'true' and item[loc]:
234
if isinstance(item[loc], basestring):
235
if item[loc].strip() == '':
239
if rating_query and item[loc] and loc == MAP['rating'] and rating_query == int(item[loc]):
242
if item[loc] and loc not in EXCLUDE_FIELDS and query in item[loc].lower():
215
248
def remove(self, id):
517
def upgrade_version_4(self):
518
'Rationalize books table'
519
self.conn.executescript('''
521
CREATE TEMPORARY TABLE
522
books_backup(id,title,sort,timestamp,series_index,author_sort,isbn,path);
523
INSERT INTO books_backup SELECT id,title,sort,timestamp,series_index,author_sort,isbn,path FROM books;
525
CREATE TABLE books ( id INTEGER PRIMARY KEY AUTOINCREMENT,
526
title TEXT NOT NULL DEFAULT 'Unknown' COLLATE NOCASE,
527
sort TEXT COLLATE NOCASE,
528
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
529
pubdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
530
series_index REAL NOT NULL DEFAULT 1.0,
531
author_sort TEXT COLLATE NOCASE,
532
isbn TEXT DEFAULT "" COLLATE NOCASE,
533
lccn TEXT DEFAULT "" COLLATE NOCASE,
534
path TEXT NOT NULL DEFAULT "",
535
flags INTEGER NOT NULL DEFAULT 1
538
books (id,title,sort,timestamp,pubdate,series_index,author_sort,isbn,path)
539
SELECT id,title,sort,timestamp,timestamp,series_index,author_sort,isbn,path FROM books_backup;
540
DROP TABLE books_backup;
545
(SELECT concat(name) FROM authors WHERE authors.id IN (SELECT author from books_authors_link WHERE book=books.id)) authors,
546
(SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,
547
(SELECT rating FROM ratings WHERE ratings.id IN (SELECT rating from books_ratings_link WHERE book=books.id)) rating,
549
(SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,
550
(SELECT concat(name) FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,
551
(SELECT text FROM comments WHERE book=books.id) comments,
552
(SELECT name FROM series WHERE series.id IN (SELECT series FROM books_series_link WHERE book=books.id)) series,
556
(SELECT concat(format) FROM data WHERE data.book=books.id) formats,
565
def upgrade_version_5(self):
566
'Update indexes/triggers for new books table'
567
self.conn.executescript('''
569
CREATE INDEX authors_idx ON books (author_sort COLLATE NOCASE);
570
CREATE INDEX books_idx ON books (sort COLLATE NOCASE);
571
CREATE TRIGGER books_delete_trg
572
AFTER DELETE ON books
574
DELETE FROM books_authors_link WHERE book=OLD.id;
575
DELETE FROM books_publishers_link WHERE book=OLD.id;
576
DELETE FROM books_ratings_link WHERE book=OLD.id;
577
DELETE FROM books_series_link WHERE book=OLD.id;
578
DELETE FROM books_tags_link WHERE book=OLD.id;
579
DELETE FROM data WHERE book=OLD.id;
580
DELETE FROM comments WHERE book=OLD.id;
581
DELETE FROM conversion_options WHERE book=OLD.id;
583
CREATE TRIGGER books_insert_trg
584
AFTER INSERT ON books
586
UPDATE books SET sort=title_sort(NEW.title) WHERE id=NEW.id;
588
CREATE TRIGGER books_update_trg
589
AFTER UPDATE ON books
591
UPDATE books SET sort=title_sort(NEW.title) WHERE id=NEW.id;
594
UPDATE books SET sort=title_sort(title) WHERE sort IS NULL;
601
def upgrade_version_6(self):
602
'Show authors in order'
603
self.conn.executescript('''
608
(SELECT sortconcat(bal.id, name) FROM books_authors_link AS bal JOIN authors ON(author = authors.id) WHERE book = books.id) authors,
609
(SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,
610
(SELECT rating FROM ratings WHERE ratings.id IN (SELECT rating from books_ratings_link WHERE book=books.id)) rating,
612
(SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,
613
(SELECT concat(name) FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,
614
(SELECT text FROM comments WHERE book=books.id) comments,
615
(SELECT name FROM series WHERE series.id IN (SELECT series FROM books_series_link WHERE book=books.id)) series,
619
(SELECT concat(format) FROM data WHERE data.book=books.id) formats,
476
631
def last_modified(self):
477
632
''' Return last modified time as a UTC datetime object'''
510
665
authors = self.authors(id, index_is_id=True)
512
667
authors = _('Unknown')
513
author = sanitize_file_name(authors.split(',')[0][:self.PATH_LIMIT]).decode(filesystem_encoding, 'replace')
514
title = sanitize_file_name(self.title(id, index_is_id=True)[:self.PATH_LIMIT]).decode(filesystem_encoding, 'replace')
668
author = ascii_filename(authors.split(',')[0][:self.PATH_LIMIT]).decode(filesystem_encoding, 'replace')
669
title = ascii_filename(self.title(id, index_is_id=True)[:self.PATH_LIMIT]).decode(filesystem_encoding, 'replace')
515
670
name = title + ' - ' + author
671
while name.endswith('.'):
518
def rmtree(self, path):
675
def rmtree(self, path, permanent=False):
519
676
if not self.normpath(self.library_path).startswith(self.normpath(path)):
677
delete_tree(path, permanent=permanent)
522
679
def normpath(self, path):
523
680
path = os.path.abspath(os.path.realpath(path))
611
768
return f if as_file else f.read()
770
def timestamp(self, index, index_is_id=False):
772
return self.conn.get('SELECT timestamp FROM meta WHERE id=?', (index,), all=False)
773
return self.data[index][FIELD_MAP['timestamp']]
775
def pubdate(self, index, index_is_id=False):
777
return self.conn.get('SELECT pubdate FROM meta WHERE id=?', (index,), all=False)
778
return self.data[index][FIELD_MAP['pubdate']]
613
780
def get_metadata(self, idx, index_is_id=False, get_cover=False):
615
782
Convenience method to return metadata as a L{MetaInformation} object.
984
1151
self.notify('metadata', [id])
1153
def set_pubdate(self, id, dt, notify=True):
1155
self.conn.execute('UPDATE books SET pubdate=? WHERE id=?', (dt, id))
1156
self.data.set(id, FIELD_MAP['pubdate'], dt, row_is_id=True)
1159
self.notify('metadata', [id])
986
1162
def set_publisher(self, id, publisher, notify=True):
987
1163
self.conn.execute('DELETE FROM books_publishers_link WHERE book=?',(id,))
988
1164
self.conn.execute('DELETE FROM publishers WHERE (SELECT COUNT(id) FROM books_publishers_link WHERE publisher=publishers.id) < 1')
1104
1283
def set_series_index(self, id, idx, notify=True):
1105
1284
if idx is None:
1108
self.conn.execute('UPDATE books SET series_index=? WHERE id=?', (int(idx), id))
1287
self.conn.execute('UPDATE books SET series_index=? WHERE id=?', (idx, id))
1109
1288
self.conn.commit()
1113
self.data.set(row, 10, idx)
1116
self.data.set(id, FIELD_MAP['series_index'], int(idx), row_is_id=True)
1289
self.data.set(id, FIELD_MAP['series_index'], idx, row_is_id=True)
1118
1291
self.notify('metadata', [id])
1185
1358
path = path_or_stream
1186
1359
return run_plugins_on_import(path, format)
1188
def add_books(self, paths, formats, metadata, uris=[], add_duplicates=True):
1361
def create_book_entry(self, mi, cover=None, add_duplicates=True):
1362
if not add_duplicates and self.has_book(mi):
1364
series_index = 1.0 if mi.series_index is None else mi.series_index
1365
aus = mi.author_sort if mi.author_sort else ', '.join(mi.authors)
1367
if isinstance(aus, str):
1368
aus = aus.decode(preferred_encoding, 'replace')
1369
if isinstance(title, str):
1370
title = title.decode(preferred_encoding)
1371
obj = self.conn.execute('INSERT INTO books(title, series_index, author_sort) VALUES (?, ?, ?)',
1372
(title, series_index, aus))
1374
self.data.books_added([id], self)
1375
self.set_path(id, True)
1377
self.set_metadata(id, mi)
1378
if cover is not None:
1379
self.set_cover(id, cover)
1383
def add_books(self, paths, formats, metadata, add_duplicates=True):
1190
1385
Add a book to the database. The result cache is not updated.
1191
1386
:param:`paths` List of paths to book files or file-like objects
1193
formats, metadata, uris = iter(formats), iter(metadata), iter(uris)
1388
formats, metadata = iter(formats), iter(metadata)
1194
1389
duplicates = []
1196
1391
for path in paths:
1197
1392
mi = metadata.next()
1198
1393
format = formats.next()
1201
except StopIteration:
1203
1394
if not add_duplicates and self.has_book(mi):
1204
duplicates.append((path, format, mi, uri))
1395
duplicates.append((path, format, mi))
1206
series_index = 1 if mi.series_index is None else mi.series_index
1397
series_index = 1.0 if mi.series_index is None else mi.series_index
1207
1398
aus = mi.author_sort if mi.author_sort else ', '.join(mi.authors)
1208
1399
title = mi.title
1209
1400
if isinstance(aus, str):
1210
1401
aus = aus.decode(preferred_encoding, 'replace')
1211
1402
if isinstance(title, str):
1212
1403
title = title.decode(preferred_encoding)
1213
obj = self.conn.execute('INSERT INTO books(title, uri, series_index, author_sort) VALUES (?, ?, ?, ?)',
1214
(title, uri, series_index, aus))
1404
obj = self.conn.execute('INSERT INTO books(title, series_index, author_sort) VALUES (?, ?, ?)',
1405
(title, series_index, aus))
1215
1406
id = obj.lastrowid
1216
self.data.books_added([id], self.conn)
1407
self.data.books_added([id], self)
1218
1409
self.set_path(id, True)
1219
1410
self.conn.commit()
1224
1415
self.add_format(id, format, stream, index_is_id=True)
1226
1417
self.conn.commit()
1227
self.data.refresh_ids(self.conn, ids) # Needed to update format list and size
1418
self.data.refresh_ids(self, ids) # Needed to update format list and size
1229
1420
paths = list(duplicate[0] for duplicate in duplicates)
1230
1421
formats = list(duplicate[1] for duplicate in duplicates)
1231
1422
metadata = list(duplicate[2] for duplicate in duplicates)
1232
uris = list(duplicate[3] for duplicate in duplicates)
1233
return (paths, formats, metadata, uris), len(ids)
1423
return (paths, formats, metadata), len(ids)
1234
1424
return None, len(ids)
1236
1426
def import_book(self, mi, formats, notify=True):
1237
series_index = 1 if mi.series_index is None else mi.series_index
1427
series_index = 1.0 if mi.series_index is None else mi.series_index
1238
1428
if not mi.title:
1239
1429
mi.title = _('Unknown')
1240
1430
if not mi.authors:
1244
1434
aus = aus.decode(preferred_encoding, 'replace')
1245
1435
title = mi.title if isinstance(mi.title, unicode) else \
1246
1436
mi.title.decode(preferred_encoding, 'replace')
1247
obj = self.conn.execute('INSERT INTO books(title, uri, series_index, author_sort) VALUES (?, ?, ?, ?)',
1248
(title, None, series_index, aus))
1437
obj = self.conn.execute('INSERT INTO books(title, series_index, author_sort) VALUES (?, ?, ?)',
1438
(title, series_index, aus))
1249
1439
id = obj.lastrowid
1250
self.data.books_added([id], self.conn)
1440
self.data.books_added([id], self)
1251
1441
self.set_path(id, True)
1252
1442
self.set_metadata(id, mi)
1253
1443
for path in formats:
1257
1447
self.add_format_with_hooks(id, ext, path, index_is_id=True)
1258
1448
self.conn.commit()
1259
self.data.refresh_ids(self.conn, [id]) # Needed to update format list and size
1449
self.data.refresh_ids(self, [id]) # Needed to update format list and size
1261
1451
self.notify('add', [id])
1263
def move_library_to(self, newloc, progress=None):
1264
header = _(u'<p>Copying books to %s<br><center>')%newloc
1453
def move_library_to(self, newloc, progress=lambda x: x):
1265
1454
books = self.conn.get('SELECT id, path, title FROM books')
1266
if progress is not None:
1267
progress.setValue(0)
1268
progress.setLabelText(header)
1269
QCoreApplication.processEvents()
1270
progress.setAutoReset(False)
1271
progress.setRange(0, len(books))
1272
1455
if not os.path.exists(newloc):
1273
1456
os.makedirs(newloc)
1274
1457
old_dirs = set([])
1275
1458
for i, book in enumerate(books):
1276
if progress is not None:
1277
progress.setLabelText(header+_(u'Copying <b>%s</b>')%book[2])
1343
1521
x['cover'] = os.path.join(path, 'cover.jpg')
1344
1522
if not self.has_cover(x['id'], index_is_id=True):
1345
1523
x['cover'] = None
1346
path += os.sep + self.construct_file_name(record[FIELD_MAP['id']]) + '.%s'
1347
1524
formats = self.formats(record[FIELD_MAP['id']], index_is_id=True)
1349
1526
for fmt in formats.split(','):
1350
x['formats'].append(path%fmt.lower())
1351
x['fmt_'+fmt.lower()] = path%fmt.lower()
1527
path = self.format_abspath(x['id'], fmt, index_is_id=True)
1528
x['formats'].append(path)
1529
x['fmt_'+fmt.lower()] = path
1352
1530
x['available_formats'] = [i.upper() for i in formats.split(',')]
1356
1534
def migrate_old(self, db, progress):
1535
from PyQt4.QtCore import QCoreApplication
1357
1536
header = _(u'<p>Migrating old database to ebook library in %s<br><center>')%self.library_path
1358
1537
progress.setValue(0)
1359
1538
progress.setLabelText(header)
1360
1539
QCoreApplication.processEvents()
1361
1540
db.conn.row_factory = lambda cursor, row : tuple(row)
1362
1541
db.conn.text_factory = lambda x : unicode(x, 'utf-8', 'replace')
1363
books = db.conn.get('SELECT id, title, sort, timestamp, uri, series_index, author_sort, isbn FROM books ORDER BY id ASC')
1542
books = db.conn.get('SELECT id, title, sort, timestamp, series_index, author_sort, isbn FROM books ORDER BY id ASC')
1364
1543
progress.setAutoReset(False)
1365
1544
progress.setRange(0, len(books))
1367
1546
for book in books:
1368
self.conn.execute('INSERT INTO books(id, title, sort, timestamp, uri, series_index, author_sort, isbn) VALUES(?, ?, ?, ?, ?, ?, ?, ?);', book)
1547
self.conn.execute('INSERT INTO books(id, title, sort, timestamp, series_index, author_sort, isbn) VALUES(?, ?, ?, ?, ?, ?, ?, ?);', book)
1371
1550
authors ratings tags series books_tags_link
1424
1604
by_author[au] = []
1425
1605
by_author[au].append(index)
1426
1606
for au in by_author.keys():
1427
apath = os.path.join(dir, sanitize_file_name(au))
1607
aname = ascii_filename(au)[:au_len]
1608
apath = os.path.abspath(os.path.join(dir, aname))
1428
1609
if not single_dir and not os.path.exists(apath):
1429
1610
os.mkdir(apath)
1430
1611
for idx in by_author[au]:
1431
1612
title = re.sub(r'\s', ' ', self.title(idx, index_is_id=index_is_id))
1432
tpath = os.path.join(apath, sanitize_file_name(title))
1613
name = au + ' - ' + title if byauthor else title + ' - ' + au
1614
name = ascii_filename(name)
1615
tname = ascii_filename(title)
1616
tname, name = shorten_components_to(path_len-len(apath), (tname,
1620
tpath = os.path.join(apath, tname)
1433
1621
id = idx if index_is_id else self.id(idx)
1435
1623
if not single_dir and not os.path.exists(tpath):
1438
name = au + ' - ' + title if byauthor else title + ' - ' + au
1440
1626
base = dir if single_dir else tpath
1441
1627
mi = self.get_metadata(idx, index_is_id=index_is_id, get_cover=True)
1442
f = open(os.path.join(base, sanitize_file_name(name)+'.opf'), 'wb')
1443
1628
if not mi.authors:
1444
1629
mi.authors = [_('Unknown')]
1445
1630
cdata = self.cover(int(id), index_is_id=True)
1446
1631
if cdata is not None:
1447
cname = sanitize_file_name(name)+'.jpg'
1448
1633
open(os.path.join(base, cname), 'wb').write(cdata)
1449
1634
mi.cover = cname
1450
opf = OPFCreator(base, mi)
1635
with open(os.path.join(base, name+'.opf'),
1637
f.write(metadata_to_opf(mi))
1454
1639
fmts = self.formats(idx, index_is_id=index_is_id)