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