1'''library_backend.py - Comic book library backend using sqlite.''' 2 3import os 4import datetime 5 6from mcomix import archive_tools 7from mcomix import constants 8from mcomix import thumbnail_tools 9from mcomix import log 10from mcomix import callback 11from mcomix import tools 12from mcomix.preferences import prefs 13from mcomix.library import backend_types 14from mcomix.sqlite3 import sqlite3 15# Only for importing legacy data from last-read module 16from mcomix import last_read_page 17 18 19#: Identifies the 'Recent' collection that stores recently read books. 20COLLECTION_RECENT = -2 21 22 23class _LibraryBackend(object): 24 25 '''The LibraryBackend handles the storing and retrieval of library 26 data to and from disk. 27 ''' 28 29 #: Current version of the library database structure. 30 # See method _upgrade_database() for changes between versions. 31 DB_VERSION = 6 32 33 def __init__(self): 34 35 def row_factory(cursor, row): 36 '''Return rows as sequences only when they have more than 37 one element. 38 ''' 39 if len(row) == 1: 40 return row[0] 41 return row 42 43 if sqlite3 is not None: 44 self._con = sqlite3.connect(constants.LIBRARY_DATABASE_PATH, 45 check_same_thread=False, isolation_level=None) 46 self._con.row_factory = row_factory 47 self.enabled = True 48 49 self.watchlist = backend_types._WatchList(self) 50 51 version = self._library_version() 52 self._upgrade_database(version, _LibraryBackend.DB_VERSION) 53 else: 54 self._con = None 55 self.watchlist = None 56 self.enabled = False 57 58 def get_books_in_collection(self, collection=None, filter_string=None): 59 '''Return a sequence with all the books in <collection>, or *ALL* 60 books if <collection> is None. If <filter_string> is not None, we 61 only return books where the <filter_string> occurs in the path. 62 ''' 63 if collection is None: 64 if filter_string is None: 65 cur = self._con.execute('''select id from Book''') 66 else: 67 cur = self._con.execute('''select id from Book 68 where path like ?''', ('%%%s%%' % filter_string, )) 69 70 return cur.fetchall() 71 else: 72 books = [] 73 subcollections = self.get_all_collections_in_collection(collection) 74 for coll in [ collection ] + subcollections: 75 if filter_string is None: 76 cur = self._con.execute('''select id from Book 77 where id in (select book from Contain where collection = ?) 78 ''', (coll,)) 79 else: 80 cur = self._con.execute('''select id from Book 81 where id in (select book from Contain where collection = ?) 82 and path like ?''', (coll, '%%%s%%' % filter_string)) 83 books.extend(cur.fetchall()) 84 return books 85 86 def get_book_by_path(self, path): 87 ''' Retrieves a book from the library, specified by C{path}. 88 If the book doesn't exist, None is returned. Otherwise, a 89 L{backend_types._Book} instance is returned. ''' 90 91 path = tools.relpath2root(path,abs_fallback=prefs['portable allow abspath']) 92 if not path: 93 # path is None, means running in portable mode 94 # and currect path is out of same mount point 95 # so book should not exist in library 96 return 97 98 cur = self.execute('''select id, name, path, pages, format, 99 size, added 100 from book where path = ?''', (path,)) 101 book = cur.fetchone() 102 cur.close() 103 104 if book: 105 return backend_types._Book(*book) 106 else: 107 return None 108 109 def get_book_by_id(self, id): 110 ''' Retrieves a book from the library, specified by C{id}. 111 If the book doesn't exist, C{None} is returned. Otherwise, a 112 L{backend_types._Book} instance is returned. ''' 113 114 cur = self.execute('''select id, name, path, pages, format, 115 size, added 116 from book where id = ?''', (id,)) 117 book = cur.fetchone() 118 cur.close() 119 120 if book: 121 return backend_types._Book(*book) 122 else: 123 return None 124 125 def get_book_cover(self, book): 126 '''Return a pixbuf with a thumbnail of the cover of <book>, or 127 None if the cover can not be fetched. 128 ''' 129 try: 130 path = self._con.execute('''select path from Book 131 where id = ?''', (book,)).fetchone() 132 except Exception: 133 log.error( _('! Non-existant book #%i'), book ) 134 return None 135 136 return self.get_book_thumbnail(path) 137 138 def get_book_path(self, book): 139 '''Return the filesystem path to <book>, or None if <book> isn't 140 in the library. 141 ''' 142 try: 143 path = self._con.execute('''select path from Book 144 where id = ?''', (book,)).fetchone() 145 except Exception: 146 log.error( _('! Non-existant book #%i'), book ) 147 return None 148 149 return path 150 151 def get_book_thumbnail(self, path): 152 ''' Returns a pixbuf with a thumbnail of the cover of the book at <path>, 153 or None, if no thumbnail could be generated. ''' 154 155 # Use the maximum image size allowed by the library, so that thumbnails 156 # might be downscaled, but never need to be upscaled (and look ugly). 157 thumbnailer = thumbnail_tools.Thumbnailer(dst_dir=constants.LIBRARY_COVERS_PATH, 158 store_on_disk=True, 159 archive_support=True, 160 size=(constants.MAX_LIBRARY_COVER_SIZE, 161 constants.MAX_LIBRARY_COVER_SIZE)) 162 thumb = thumbnailer.thumbnail(path) 163 164 if thumb is None: log.warning( _('! Could not get cover for book "%s"'), path ) 165 return thumb 166 167 def get_book_name(self, book): 168 '''Return the name of <book>, or None if <book> isn't in the 169 library. 170 ''' 171 cur = self._con.execute('''select name from Book 172 where id = ?''', (book,)) 173 name = cur.fetchone() 174 if name is not None: 175 return name 176 else: 177 return None 178 179 def get_book_pages(self, book): 180 '''Return the number of pages in <book>, or None if <book> isn't 181 in the library. 182 ''' 183 cur = self._con.execute('''select pages from Book 184 where id = ?''', (book,)) 185 return cur.fetchone() 186 187 def get_book_format(self, book): 188 '''Return the archive format of <book>, or None if <book> isn't 189 in the library. 190 ''' 191 cur = self._con.execute('''select format from Book 192 where id = ?''', (book,)) 193 return cur.fetchone() 194 195 def get_book_size(self, book): 196 '''Return the size of <book> in bytes, or None if <book> isn't 197 in the library. 198 ''' 199 cur = self._con.execute('''select size from Book 200 where id = ?''', (book,)) 201 return cur.fetchone() 202 203 def get_collections_in_collection(self, collection=None): 204 '''Return a sequence with all the subcollections in <collection>, 205 or all top-level collections if <collection> is None. 206 ''' 207 if collection is None: 208 cur = self._con.execute('''select id from Collection 209 where supercollection isnull 210 order by name''') 211 else: 212 cur = self._con.execute('''select id from Collection 213 where supercollection = ? 214 order by name''', (collection,)) 215 return cur.fetchall() 216 217 def get_all_collections_in_collection(self, collection): 218 ''' Returns a sequence of <all> subcollections in <collection>, 219 that is, even subcollections that are again a subcollection of one 220 of the previous subcollections. ''' 221 222 if collection is None: raise ValueError('Collection must not be <None>') 223 224 to_search = [ collection ] 225 collections = [ ] 226 # This assumes that the library is built like a tree, so no circular references. 227 while len(to_search) > 0: 228 collection = to_search.pop() 229 subcollections = self.get_collections_in_collection(collection) 230 collections.extend(subcollections) 231 to_search.extend(subcollections) 232 233 return collections 234 235 def get_all_collections(self): 236 '''Return a sequence with all collections (flattened hierarchy). 237 The sequence is sorted alphabetically by collection name. 238 ''' 239 cur = self._con.execute('''select id from Collection 240 order by name''') 241 return cur.fetchall() 242 243 def get_collection_name(self, collection): 244 '''Return the name field of the <collection>, or None if the 245 collection does not exist. 246 ''' 247 cur = self._con.execute('''select name from Collection 248 where id = ?''', (collection,)) 249 name = cur.fetchone() 250 if name is not None: 251 return name 252 else: 253 return None 254 255 def get_collection_by_name(self, name): 256 '''Return the collection called <name>, or None if no such 257 collection exists. Names are unique, so at most one such collection 258 can exist. 259 ''' 260 cur = self._con.execute('''select id, name, supercollection 261 from collection 262 where name = ?''', (name,)) 263 result = cur.fetchone() 264 cur.close() 265 if result: 266 return backend_types._Collection(*result) 267 else: 268 return None 269 270 def get_collection_by_id(self, id): 271 ''' Returns the collection with ID C{id}. 272 @param id: Integer value. May be C{-1} or C{None} for default collection. 273 @return: L{_Collection} if found, None otherwise. 274 ''' 275 if id is None or id == -1: 276 return backend_types.DefaultCollection 277 else: 278 cur = self._con.execute('''select id, name, supercollection 279 from collection 280 where id = ?''', (id,)) 281 result = cur.fetchone() 282 cur.close() 283 284 if result: 285 return backend_types._Collection(*result) 286 else: 287 return None 288 289 def get_recent_collection(self): 290 ''' Returns the "Recent" collection, especially created for 291 storing recently opened files. ''' 292 return self.get_collection_by_id(COLLECTION_RECENT) 293 294 def get_supercollection(self, collection): 295 '''Return the supercollection of <collection>.''' 296 cur = self._con.execute('''select supercollection from Collection 297 where id = ?''', (collection,)) 298 return cur.fetchone() 299 300 def add_book(self, path, collection=None): 301 '''Add the archive at <path> to the library. If <collection> is 302 not None, it is the collection that the books should be put in. 303 Return True if the book was successfully added (or was already 304 added). 305 ''' 306 path = tools.relpath2root(path,abs_fallback=prefs['portable allow abspath']) 307 if not path: 308 # path is None, means running in portable mode 309 # and currect path is out of same mount point 310 # so do not add book to library 311 return 312 name = os.path.basename(path) 313 info = archive_tools.get_archive_info(path) 314 if info is None: 315 return False 316 format, pages, size = info 317 318 # Thumbnail for the newly added book will be generated once it 319 # is actually needed with get_book_thumbnail(). 320 old = self._con.execute('''select id from Book 321 where path = ?''', (path,)).fetchone() 322 try: 323 cursor = self._con.cursor() 324 if old is not None: 325 cursor.execute('''update Book set 326 name = ?, pages = ?, format = ?, size = ? 327 where path = ?''', (name, pages, format, size, path)) 328 book_id = old 329 else: 330 cursor.execute('''insert into Book 331 (name, path, pages, format, size) 332 values (?, ?, ?, ?, ?)''', 333 (name, path, pages, format, size)) 334 book_id = cursor.lastrowid 335 336 book = backend_types._Book(book_id, name, path, pages, 337 format, size, datetime.datetime.now().isoformat()) 338 self.book_added(book) 339 340 cursor.close() 341 342 if collection is not None: 343 self.add_book_to_collection(book_id, collection) 344 345 return True 346 except sqlite3.Error: 347 log.error( _('! Could not add book "%s" to the library'), path ) 348 return False 349 350 @callback.Callback 351 def book_added(self, book): 352 ''' Event that triggers when a new book is successfully added to the 353 library. 354 @param book: L{_Book} instance of the newly added book. 355 ''' 356 pass 357 358 @callback.Callback 359 def book_added_to_collection(self, book, collection_id): 360 ''' Event that triggers when a book is added to the 361 specified collection. 362 @param book: L{_Book} instance of the added book. 363 @param collection_id: ID of the collection. 364 ''' 365 pass 366 367 368 def add_collection(self, name): 369 '''Add a new collection with <name> to the library. Return True 370 if the collection was successfully added. 371 ''' 372 try: 373 # The Recent pseudo collection initializes the lowest rowid 374 # with -2, meaning that instead of starting from 1, 375 # auto-incremental will start from -1. Avoid this. 376 cur = self._con.execute('''select max(id) from collection''') 377 maxid = cur.fetchone() 378 if maxid is not None and maxid < 1: 379 self._con.execute('''insert into collection 380 (id, name) values (?, ?)''', (1, name)) 381 else: 382 self._con.execute('''insert into Collection 383 (name) values (?)''', (name,)) 384 return True 385 except sqlite3.Error: 386 log.error( _('! Could not add collection "%s"'), name ) 387 return False 388 389 def add_book_to_collection(self, book, collection): 390 '''Put <book> into <collection>.''' 391 try: 392 self._con.execute('''insert into Contain 393 (collection, book) values (?, ?)''', (collection, book)) 394 self.book_added_to_collection(self.get_book_by_id(book), 395 collection) 396 except sqlite3.DatabaseError: # E.g. book already in collection. 397 pass 398 except sqlite3.Error: 399 log.error( _('! Could not add book %(book)s to collection %(collection)s'), 400 {'book' : book, 'collection' : collection} ) 401 402 def add_collection_to_collection(self, subcollection, supercollection): 403 '''Put <subcollection> into <supercollection>, or put 404 <subcollection> in the root if <supercollection> is None. 405 ''' 406 if supercollection is None: 407 self._con.execute('''update Collection 408 set supercollection = NULL 409 where id = ?''', (subcollection,)) 410 else: 411 self._con.execute('''update Collection 412 set supercollection = ? 413 where id = ?''', (supercollection, subcollection)) 414 415 def rename_collection(self, collection, name): 416 '''Rename the <collection> to <name>. Return True if the renaming 417 was successful. 418 ''' 419 try: 420 self._con.execute('''update Collection set name = ? 421 where id = ?''', (name, collection)) 422 return True 423 except sqlite3.DatabaseError: # E.g. name taken. 424 pass 425 except sqlite3.Error: 426 log.error( _('! Could not rename collection to "%s"'), name ) 427 return False 428 429 def duplicate_collection(self, collection): 430 '''Duplicate the <collection> by creating a new collection 431 containing the same books. Return True if the duplication was 432 successful. 433 ''' 434 name = self.get_collection_name(collection) 435 if name is None: # Original collection does not exist. 436 return False 437 copy_name = name + ' ' + _('(Copy)') 438 while self.get_collection_by_name(copy_name): 439 copy_name = copy_name + ' ' + _('(Copy)') 440 if self.add_collection(copy_name) is None: # Could not create the new. 441 return False 442 copy_collection = self._con.execute('''select id from Collection 443 where name = ?''', (copy_name,)).fetchone() 444 self._con.execute('''insert or ignore into Contain (collection, book) 445 select ?, book from Contain 446 where collection = ?''', (copy_collection, collection)) 447 return True 448 449 def clean_collection(self, collection=None): 450 ''' Removes files from <collection> that no longer exist. If <collection> 451 is None, all collections are cleaned. Returns the number of deleted books. ''' 452 book_ids = self.get_books_in_collection(collection) 453 deleted = 0 454 for id in book_ids: 455 path = self.get_book_path(id) 456 if path and not os.path.isfile(path): 457 self.remove_book(id) 458 deleted += 1 459 460 return deleted 461 462 def remove_book(self, book): 463 '''Remove the <book> from the library.''' 464 path = self.get_book_path(book) 465 if path is not None: 466 thumbnailer = thumbnail_tools.Thumbnailer(dst_dir=constants.LIBRARY_COVERS_PATH) 467 thumbnailer.delete(path) 468 self._con.execute('delete from Book where id = ?', (book,)) 469 self._con.execute('delete from Contain where book = ?', (book,)) 470 471 def remove_collection(self, collection): 472 '''Remove the <collection> (sans books) from the library.''' 473 self._con.execute('''update watchlist set collection = NULL 474 where collection = ?''', (collection,)) 475 self._con.execute('delete from Collection where id = ?', (collection,)) 476 self._con.execute('delete from Contain where collection = ?', 477 (collection,)) 478 self._con.execute('''update Collection set supercollection = NULL 479 where supercollection = ?''', (collection,)) 480 481 def remove_book_from_collection(self, book, collection): 482 '''Remove <book> from <collection>.''' 483 self._con.execute('''delete from Contain 484 where book = ? and collection = ?''', (book, collection)) 485 486 def execute(self, *args): 487 ''' Passes C{args} directly to the C{execute} method of the SQL 488 connection. ''' 489 return self._con.execute(*args) 490 491 def begin_transaction(self): 492 ''' Normally, the connection is in auto-commit mode. Calling 493 this method will switch to transactional mode, automatically 494 starting a transaction when a DML statement is used. ''' 495 self._con.isolation_level = 'IMMEDIATE' 496 497 def end_transaction(self): 498 ''' Commits any changes to the database and switches back 499 to auto-commit mode. ''' 500 self._con.commit() 501 self._con.isolation_level = None 502 503 def close(self): 504 '''Commit changes and close cleanly.''' 505 if self._con is not None: 506 self._con.commit() 507 self._con.close() 508 509 global _backend 510 _backend = None 511 512 def _table_exists(self, table): 513 ''' Checks if C{table} exists in the database. ''' 514 cursor = self._con.cursor() 515 exists = cursor.execute('pragma table_info(%s)' % table).fetchone() is not None 516 cursor.close() 517 return exists 518 519 def _library_version(self): 520 ''' Examines the library database structure to determine 521 which version of MComix created it. 522 523 @return C{version} from the table C{Info} if available, 524 C{0} otherwise. C{-1} if the database has not been created yet.''' 525 526 # Check if Comix' tables exist 527 tables = ('book', 'collection', 'contain') 528 for table in tables: 529 if not self._table_exists(table): 530 return -1 531 532 if self._table_exists('info'): 533 cursor = self._con.cursor() 534 version = cursor.execute('''select value from info 535 where key = 'version' ''').fetchone() 536 cursor.close() 537 538 if not version: 539 log.warning(_('Could not determine library database version!')) 540 return -1 541 else: 542 return int(version) 543 else: 544 # Comix database format 545 return 0 546 547 def _create_tables(self): 548 ''' Creates all required tables in the database. ''' 549 self._create_table_book() 550 self._create_table_collection() 551 self._create_table_contain() 552 self._create_table_info() 553 self._create_table_watchlist() 554 self._create_table_recent() 555 556 def _upgrade_database(self, from_version, to_version): 557 ''' Performs sequential upgrades to the database, bringing 558 it from C{from_version} to C{to_version}. If C{from_version} 559 is -1, the database structure will simply be re-created at the 560 current version. ''' 561 562 if from_version < 5: 563 self._create_tables() 564 return 565 566 if from_version != to_version: 567 upgrades = range(from_version, to_version) 568 log.info(_('Upgrading library database version from %(from)d to %(to)d.'), 569 { 'from' : from_version, 'to' : to_version }) 570 571 if 5 in upgrades: 572 # Changed all 'string' columns into 'text' columns 573 self._con.execute('''alter table book rename to book_old''') 574 self._create_table_book() 575 self._con.execute('''insert into book 576 (id, name, path, pages, format, size, added) 577 select id, name, path, pages, format, size, added from book_old''') 578 self._con.execute('''drop table book_old''') 579 580 self._con.execute('''alter table collection rename to collection_old''') 581 self._create_table_collection() 582 self._con.execute('''insert into collection 583 (id, name, supercollection) 584 select id, name, supercollection from collection_old''') 585 self._con.execute('''drop table collection_old''') 586 587 self._con.execute('''update info set value = ? where key = 'version' ''', 588 (str(_LibraryBackend.DB_VERSION),)) 589 590 def _create_table_book(self): 591 self._con.execute('''create table if not exists book ( 592 id integer primary key, 593 name text, 594 path text unique, 595 pages integer, 596 format integer, 597 size integer, 598 added datetime default current_timestamp)''') 599 600 def _create_table_collection(self): 601 self._con.execute('''create table if not exists collection ( 602 id integer primary key, 603 name text unique, 604 supercollection integer)''') 605 606 def _create_table_contain(self): 607 self._con.execute('''create table if not exists contain ( 608 collection integer not null, 609 book integer not null, 610 primary key (collection, book))''') 611 612 def _create_table_info(self): 613 self._con.execute('''create table if not exists info ( 614 key text primary key, 615 value text)''') 616 self._con.execute('''insert into info 617 (key, value) values ('version', ?)''', 618 (str(_LibraryBackend.DB_VERSION),)) 619 620 def _create_table_watchlist(self): 621 self._con.execute('''create table if not exists watchlist ( 622 path text primary key, 623 collection integer references collection (id) on delete set null, 624 recursive boolean not null)''') 625 626 def _create_table_recent(self): 627 self._con.execute('''create table if not exists recent ( 628 book integer primary key, 629 page integer, 630 time_set datetime)''') 631 self._con.execute('''insert or ignore into collection (id, name) 632 values (?, ?)''', (COLLECTION_RECENT, _('Recent'))) 633 634 635_backend = None 636 637 638def LibraryBackend(): 639 ''' Returns the singleton instance of the library backend. ''' 640 global _backend 641 if _backend is not None: 642 return _backend 643 else: 644 _backend = _LibraryBackend() 645 return _backend 646 647# vim: expandtab:sw=4:ts=4 648