1#!/usr/local/bin/python3.8 2# vim:fileencoding=utf-8 3# License: GPLv3 Copyright: 2016, Kovid Goyal <kovid at kovidgoyal.net> 4 5 6''' 7Device driver for the Paladin devices 8''' 9 10import os, time, sys 11from contextlib import closing 12 13from calibre.devices.mime import mime_type_ext 14from calibre.devices.errors import DeviceError 15from calibre.devices.usbms.driver import USBMS, debug_print 16from calibre.devices.usbms.books import CollectionsBookList, BookList 17 18DBPATH = 'paladin/database/books.db' 19 20 21class ImageWrapper: 22 23 def __init__(self, image_path): 24 self.image_path = image_path 25 26 27class PALADIN(USBMS): 28 name = 'Paladin Device Interface' 29 gui_name = 'Paladin eLibrary' 30 description = _('Communicate with the Paladin readers') 31 author = 'David Hobley' 32 supported_platforms = ['windows', 'osx', 'linux'] 33 path_sep = '/' 34 booklist_class = CollectionsBookList 35 36 FORMATS = ['epub', 'pdf'] 37 CAN_SET_METADATA = ['collections'] 38 CAN_DO_DEVICE_DB_PLUGBOARD = True 39 40 VENDOR_ID = [0x2207] #: Onyx Vendor Id (currently) 41 PRODUCT_ID = [0x0010] 42 BCD = None 43 44 SUPPORTS_SUB_DIRS = True 45 SUPPORTS_USE_AUTHOR_SORT = True 46 MUST_READ_METADATA = True 47 EBOOK_DIR_MAIN = 'paladin/books' 48 49 EXTRA_CUSTOMIZATION_MESSAGE = [ 50 _( 51 'Comma separated list of metadata fields ' 52 'to turn into collections on the device. Possibilities include: ' 53 ) + 'series, tags, authors', 54 ] 55 EXTRA_CUSTOMIZATION_DEFAULT = [ 56 ', '.join(['series', 'tags']), 57 ] 58 OPT_COLLECTIONS = 0 59 60 plugboards = None 61 plugboard_func = None 62 63 device_offset = None 64 65 def books(self, oncard=None, end_session=True): 66 import apsw 67 dummy_bl = BookList(None, None, None) 68 69 if ( 70 (oncard == 'carda' and not self._card_a_prefix) or 71 (oncard and oncard != 'carda') 72 ): 73 self.report_progress(1.0, _('Getting list of books on device...')) 74 return dummy_bl 75 76 prefix = self._card_a_prefix if oncard == 'carda' else self._main_prefix 77 78 # Let parent driver get the books 79 self.booklist_class.rebuild_collections = self.rebuild_collections 80 bl = USBMS.books(self, oncard=oncard, end_session=end_session) 81 82 dbpath = self.normalize_path(prefix + DBPATH) 83 debug_print("SQLite DB Path: " + dbpath) 84 85 with closing(apsw.Connection(dbpath)) as connection: 86 cursor = connection.cursor() 87 # Query collections 88 query = ''' 89 SELECT books._id, tags.tagname 90 FROM booktags 91 LEFT OUTER JOIN books 92 LEFT OUTER JOIN tags 93 WHERE booktags.book_id = books._id AND 94 booktags.tag_id = tags._id 95 ''' 96 cursor.execute(query) 97 98 bl_collections = {} 99 for i, row in enumerate(cursor): 100 bl_collections.setdefault(row[0], []) 101 bl_collections[row[0]].append(row[1]) 102 103 # collect information on offsets, but assume any 104 # offset we already calculated is correct 105 if self.device_offset is None: 106 query = 'SELECT filename, addeddate FROM books' 107 cursor.execute(query) 108 109 time_offsets = {} 110 for i, row in enumerate(cursor): 111 try: 112 comp_date = int(os.path.getmtime(self.normalize_path(prefix + row[0])) * 1000) 113 except (OSError, TypeError): 114 # In case the db has incorrect path info 115 continue 116 device_date = int(row[1]) 117 offset = device_date - comp_date 118 time_offsets.setdefault(offset, 0) 119 time_offsets[offset] = time_offsets[offset] + 1 120 121 try: 122 device_offset = max(time_offsets, key=lambda a: time_offsets.get(a)) 123 debug_print("Device Offset: %d ms"%device_offset) 124 self.device_offset = device_offset 125 except ValueError: 126 debug_print("No Books To Detect Device Offset.") 127 128 for idx, book in enumerate(bl): 129 query = 'SELECT _id, thumbnail FROM books WHERE filename = ?' 130 t = (book.lpath,) 131 cursor.execute(query, t) 132 133 for i, row in enumerate(cursor): 134 book.device_collections = bl_collections.get(row[0], None) 135 thumbnail = row[1] 136 if thumbnail is not None: 137 thumbnail = self.normalize_path(prefix + thumbnail) 138 book.thumbnail = ImageWrapper(thumbnail) 139 140 cursor.close() 141 142 return bl 143 144 def set_plugboards(self, plugboards, pb_func): 145 self.plugboards = plugboards 146 self.plugboard_func = pb_func 147 148 def sync_booklists(self, booklists, end_session=True): 149 debug_print('PALADIN: starting sync_booklists') 150 151 opts = self.settings() 152 if opts.extra_customization: 153 collections = [x.strip() for x in 154 opts.extra_customization[self.OPT_COLLECTIONS].split(',')] 155 else: 156 collections = [] 157 debug_print('PALADIN: collection fields:', collections) 158 159 if booklists[0] is not None: 160 self.update_device_database(booklists[0], collections, None) 161 if len(booklists) > 1 and booklists[1] is not None: 162 self.update_device_database(booklists[1], collections, 'carda') 163 164 USBMS.sync_booklists(self, booklists, end_session=end_session) 165 debug_print('PALADIN: finished sync_booklists') 166 167 def update_device_database(self, booklist, collections_attributes, oncard): 168 import apsw 169 debug_print('PALADIN: starting update_device_database') 170 171 plugboard = None 172 if self.plugboard_func: 173 plugboard = self.plugboard_func(self.__class__.__name__, 174 'device_db', self.plugboards) 175 debug_print("PALADIN: Using Plugboard", plugboard) 176 177 prefix = self._card_a_prefix if oncard == 'carda' else self._main_prefix 178 if prefix is None: 179 # Reader has no sd card inserted 180 return 181 source_id = 1 if oncard == 'carda' else 0 182 183 dbpath = self.normalize_path(prefix + DBPATH) 184 debug_print("SQLite DB Path: " + dbpath) 185 186 collections = booklist.get_collections(collections_attributes) 187 188 with closing(apsw.Connection(dbpath)) as connection: 189 self.remove_orphaned_records(connection, dbpath) 190 self.update_device_books(connection, booklist, source_id, 191 plugboard, dbpath) 192 self.update_device_collections(connection, booklist, collections, source_id, dbpath) 193 194 debug_print('PALADIN: finished update_device_database') 195 196 def remove_orphaned_records(self, connection, dbpath): 197 try: 198 cursor = connection.cursor() 199 200 debug_print("Removing Orphaned Collection Records") 201 202 # Purge any collections references that point into the abyss 203 query = 'DELETE FROM booktags WHERE book_id NOT IN (SELECT _id FROM books)' 204 cursor.execute(query) 205 query = 'DELETE FROM booktags WHERE tag_id NOT IN (SELECT _id FROM tags)' 206 cursor.execute(query) 207 208 debug_print("Removing Orphaned Book Records") 209 210 cursor.close() 211 except Exception: 212 import traceback 213 tb = traceback.format_exc() 214 raise DeviceError((('The Paladin database is corrupted. ' 215 ' Delete the file %s on your reader and then disconnect ' 216 ' reconnect it. If you are using an SD card, you ' 217 ' should delete the file on the card as well. Note that ' 218 ' deleting this file will cause your reader to forget ' 219 ' any notes/highlights, etc.')%dbpath)+' Underlying error:' 220 '\n'+tb) 221 222 def get_database_min_id(self, source_id): 223 sequence_min = 0 224 if source_id == 1: 225 sequence_min = 4294967296 226 227 return sequence_min 228 229 def set_database_sequence_id(self, connection, table, sequence_id): 230 cursor = connection.cursor() 231 232 # Update the sequence Id if it exists 233 query = 'UPDATE sqlite_sequence SET seq = ? WHERE name = ?' 234 t = (sequence_id, table,) 235 cursor.execute(query, t) 236 237 # Insert the sequence Id if it doesn't 238 query = ('INSERT INTO sqlite_sequence (name, seq) ' 239 'SELECT ?, ? ' 240 'WHERE NOT EXISTS (SELECT 1 FROM sqlite_sequence WHERE name = ?)') 241 cursor.execute(query, (table, sequence_id, table,)) 242 243 cursor.close() 244 245 def read_device_books(self, connection, source_id, dbpath): 246 sequence_min = self.get_database_min_id(source_id) 247 sequence_max = sequence_min 248 sequence_dirty = 0 249 250 debug_print("Book Sequence Min: %d, Source Id: %d"%(sequence_min,source_id)) 251 252 try: 253 cursor = connection.cursor() 254 255 # Get existing books 256 query = 'SELECT filename, _id FROM books' 257 cursor.execute(query) 258 except Exception: 259 import traceback 260 tb = traceback.format_exc() 261 raise DeviceError((('The Paladin database is corrupted. ' 262 ' Delete the file %s on your reader and then disconnect ' 263 ' reconnect it. If you are using an SD card, you ' 264 ' should delete the file on the card as well. Note that ' 265 ' deleting this file will cause your reader to forget ' 266 ' any notes/highlights, etc.')%dbpath)+' Underlying error:' 267 '\n'+tb) 268 269 # Get the books themselves, but keep track of any that are less than the minimum. 270 # Record what the max id being used is as well. 271 db_books = {} 272 for i, row in enumerate(cursor): 273 if not hasattr(row[0], 'replace'): 274 continue 275 lpath = row[0].replace('\\', '/') 276 db_books[lpath] = row[1] 277 if row[1] < sequence_min: 278 sequence_dirty = 1 279 else: 280 sequence_max = max(sequence_max, row[1]) 281 282 # If the database is 'dirty', then we should fix up the Ids and the sequence number 283 if sequence_dirty == 1: 284 debug_print("Book Sequence Dirty for Source Id: %d"%source_id) 285 sequence_max = sequence_max + 1 286 for book, bookId in db_books.items(): 287 if bookId < sequence_min: 288 # Record the new Id and write it to the DB 289 db_books[book] = sequence_max 290 sequence_max = sequence_max + 1 291 292 # Fix the Books DB 293 query = 'UPDATE books SET _id = ? WHERE filename = ?' 294 t = (db_books[book], book,) 295 cursor.execute(query, t) 296 297 # Fix any references so that they point back to the right book 298 t = (db_books[book], bookId,) 299 query = 'UPDATE booktags SET tag_id = ? WHERE tag_id = ?' 300 cursor.execute(query, t) 301 302 self.set_database_sequence_id(connection, 'books', sequence_max) 303 debug_print("Book Sequence Max: %d, Source Id: %d"%(sequence_max,source_id)) 304 305 cursor.close() 306 return db_books 307 308 def update_device_books(self, connection, booklist, source_id, plugboard, 309 dbpath): 310 from calibre.ebooks.metadata.meta import path_to_ext 311 from calibre.ebooks.metadata import authors_to_sort_string, authors_to_string 312 opts = self.settings() 313 314 db_books = self.read_device_books(connection, source_id, dbpath) 315 cursor = connection.cursor() 316 317 for book in booklist: 318 # Run through plugboard if needed 319 if plugboard is not None: 320 newmi = book.deepcopy_metadata() 321 newmi.template_to_attribute(book, plugboard) 322 else: 323 newmi = book 324 325 # Get Metadata We Want 326 lpath = book.lpath 327 try: 328 if opts.use_author_sort: 329 if newmi.author_sort: 330 author = newmi.author_sort 331 else: 332 author = authors_to_sort_string(newmi.authors) 333 else: 334 author = authors_to_string(newmi.authors) 335 except Exception: 336 author = _('Unknown') 337 title = newmi.title or _('Unknown') 338 339 # Get modified date 340 # If there was a detected offset, use that. Otherwise use UTC (same as Sony software) 341 modified_date = os.path.getmtime(book.path) * 1000 342 if self.device_offset is not None: 343 modified_date = modified_date + self.device_offset 344 345 if lpath not in db_books: 346 query = ''' 347 INSERT INTO books 348 (bookname, authorname, description, addeddate, seriesname, seriesorder, filename, mimetype) 349 values (?,?,?,?,?,?,?,?) 350 ''' 351 t = (title, author, book.get('comments', None), int(time.time() * 1000), 352 book.get('series', None), book.get('series_index', sys.maxsize), lpath, 353 book.mime or mime_type_ext(path_to_ext(lpath))) 354 cursor.execute(query, t) 355 book.bookId = connection.last_insert_rowid() 356 debug_print('Inserted New Book: (%u) '%book.bookId + book.title) 357 else: 358 query = ''' 359 UPDATE books 360 SET bookname = ?, authorname = ?, addeddate = ? 361 WHERE filename = ? 362 ''' 363 t = (title, author, modified_date, lpath) 364 cursor.execute(query, t) 365 book.bookId = db_books[lpath] 366 db_books[lpath] = None 367 368 for book, bookId in db_books.items(): 369 if bookId is not None: 370 # Remove From Collections 371 query = 'DELETE FROM tags WHERE _id in (select tag_id from booktags where book_id = ?)' 372 t = (bookId,) 373 cursor.execute(query, t) 374 # Remove from Books 375 query = 'DELETE FROM books where _id = ?' 376 t = (bookId,) 377 cursor.execute(query, t) 378 debug_print('Deleted Book:' + book) 379 380 cursor.close() 381 382 def read_device_collections(self, connection, source_id, dbpath): 383 sequence_min = self.get_database_min_id(source_id) 384 sequence_max = sequence_min 385 sequence_dirty = 0 386 387 debug_print("Collection Sequence Min: %d, Source Id: %d"%(sequence_min,source_id)) 388 389 try: 390 cursor = connection.cursor() 391 392 # Get existing collections 393 query = 'SELECT _id, tagname FROM tags' 394 cursor.execute(query) 395 except Exception: 396 import traceback 397 tb = traceback.format_exc() 398 raise DeviceError((('The Paladin database is corrupted. ' 399 ' Delete the file %s on your reader and then disconnect ' 400 ' reconnect it. If you are using an SD card, you ' 401 ' should delete the file on the card as well. Note that ' 402 ' deleting this file will cause your reader to forget ' 403 ' any notes/highlights, etc.')%dbpath)+' Underlying error:' 404 '\n'+tb) 405 406 db_collections = {} 407 for i, row in enumerate(cursor): 408 db_collections[row[1]] = row[0] 409 if row[0] < sequence_min: 410 sequence_dirty = 1 411 else: 412 sequence_max = max(sequence_max, row[0]) 413 414 # If the database is 'dirty', then we should fix up the Ids and the sequence number 415 if sequence_dirty == 1: 416 debug_print("Collection Sequence Dirty for Source Id: %d"%source_id) 417 sequence_max = sequence_max + 1 418 for collection, collectionId in db_collections.items(): 419 if collectionId < sequence_min: 420 # Record the new Id and write it to the DB 421 db_collections[collection] = sequence_max 422 sequence_max = sequence_max + 1 423 424 # Fix the collection DB 425 query = 'UPDATE tags SET _id = ? WHERE tagname = ?' 426 t = (db_collections[collection], collection, ) 427 cursor.execute(query, t) 428 429 # Fix any references in existing collections 430 query = 'UPDATE booktags SET tag_id = ? WHERE tag_id = ?' 431 t = (db_collections[collection], collectionId,) 432 cursor.execute(query, t) 433 434 self.set_database_sequence_id(connection, 'tags', sequence_max) 435 debug_print("Collection Sequence Max: %d, Source Id: %d"%(sequence_max,source_id)) 436 437 # Fix up the collections table now... 438 sequence_dirty = 0 439 sequence_max = sequence_min 440 441 debug_print("Collections Sequence Min: %d, Source Id: %d"%(sequence_min,source_id)) 442 443 query = 'SELECT _id FROM booktags' 444 cursor.execute(query) 445 446 db_collection_pairs = [] 447 for i, row in enumerate(cursor): 448 db_collection_pairs.append(row[0]) 449 if row[0] < sequence_min: 450 sequence_dirty = 1 451 else: 452 sequence_max = max(sequence_max, row[0]) 453 454 if sequence_dirty == 1: 455 debug_print("Collections Sequence Dirty for Source Id: %d"%source_id) 456 sequence_max = sequence_max + 1 457 for pairId in db_collection_pairs: 458 if pairId < sequence_min: 459 # Record the new Id and write it to the DB 460 query = 'UPDATE booktags SET _id = ? WHERE _id = ?' 461 t = (sequence_max, pairId,) 462 cursor.execute(query, t) 463 sequence_max = sequence_max + 1 464 465 self.set_database_sequence_id(connection, 'booktags', sequence_max) 466 debug_print("Collections Sequence Max: %d, Source Id: %d"%(sequence_max,source_id)) 467 468 cursor.close() 469 return db_collections 470 471 def update_device_collections(self, connection, booklist, collections, 472 source_id, dbpath): 473 474 if collections: 475 db_collections = self.read_device_collections(connection, source_id, dbpath) 476 cursor = connection.cursor() 477 478 for collection, books in collections.items(): 479 if collection not in db_collections: 480 query = 'INSERT INTO tags (tagname) VALUES (?)' 481 t = (collection,) 482 cursor.execute(query, t) 483 db_collections[collection] = connection.last_insert_rowid() 484 debug_print('Inserted New Collection: (%u) '%db_collections[collection] + collection) 485 486 # Get existing books in collection 487 query = ''' 488 SELECT books.filename, book_id 489 FROM booktags 490 LEFT OUTER JOIN books 491 WHERE tag_id = ? AND books._id = booktags.book_id 492 ''' 493 t = (db_collections[collection],) 494 cursor.execute(query, t) 495 496 db_books = {} 497 for i, row in enumerate(cursor): 498 db_books[row[0]] = row[1] 499 500 for idx, book in enumerate(books): 501 if collection not in book.device_collections: 502 book.device_collections.append(collection) 503 if db_books.get(book.lpath, None) is None: 504 query = ''' 505 INSERT INTO booktags (tag_id, book_id) values (?,?) 506 ''' 507 t = (db_collections[collection], book.bookId) 508 cursor.execute(query, t) 509 debug_print('Inserted Book Into Collection: ' + 510 book.title + ' -> ' + collection) 511 512 db_books[book.lpath] = None 513 514 for bookPath, bookId in db_books.items(): 515 if bookId is not None: 516 query = ('DELETE FROM booktags ' 517 'WHERE book_id = ? AND tag_id = ? ') 518 t = (bookId, db_collections[collection],) 519 cursor.execute(query, t) 520 debug_print('Deleted Book From Collection: ' + bookPath + ' -> ' + collection) 521 522 db_collections[collection] = None 523 524 for collection, collectionId in db_collections.items(): 525 if collectionId is not None: 526 # Remove Books from Collection 527 query = ('DELETE FROM booktags ' 528 'WHERE tag_id = ?') 529 t = (collectionId,) 530 cursor.execute(query, t) 531 # Remove Collection 532 query = ('DELETE FROM tags ' 533 'WHERE _id = ?') 534 t = (collectionId,) 535 cursor.execute(query, t) 536 debug_print('Deleted Collection: ' + repr(collection)) 537 538 cursor.close() 539 540 def rebuild_collections(self, booklist, oncard): 541 debug_print('PALADIN: starting rebuild_collections') 542 543 opts = self.settings() 544 if opts.extra_customization: 545 collections = [x.strip() for x in 546 opts.extra_customization[self.OPT_COLLECTIONS].split(',')] 547 else: 548 collections = [] 549 debug_print('PALADIN: collection fields:', collections) 550 551 self.update_device_database(booklist, collections, oncard) 552 553 debug_print('PALADIN: finished rebuild_collections') 554