src/panucci/dbsqlite.py

   1 #!/usr/bin/env python
   2 #
   3 # This file is part of Panucci.
   4 # Copyright (c) 2008-2009 The Panucci Audiobook and Podcast Player Project
   5 #
   6 # Panucci is free software: you can redistribute it and/or modify
   7 # it under the terms of the GNU General Public License as published by
   8 # the Free Software Foundation, either version 3 of the License, or
   9 # (at your option) any later version.
  10 #
  11 # Panucci is distributed in the hope that it will be useful,
  12 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  14 # GNU General Public License for more details.
  15 #
  16 # You should have received a copy of the GNU General Public License
  17 # along with Panucci.  If not, see <http://www.gnu.org/licenses/>.
  18 #
  19 # _Heavily_ inspired by gPodder's dbsqlite
  20 #
  21
  22 import os.path
  23 import logging
  24 import string
  25 import time
  26
  27 log = logging.getLogger('panucci.dbsqlite')
  28
  29 try:
  30     from sqlite3 import dbapi2 as sqlite
  31 except ImportError:
  32     log.critical('Error importing sqlite, FAIL!')
  33
  34 from settings import settings
  35 from simplegconf import gconf
  36
  37 class Storage(object):
  38     def __init__(self, db_file):
  39         """ db_file is the on-disk location of the database file """
  40         self.__db_file = db_file
  41         self.__db = None
  42         self.__log = logging.getLogger('panucci.dbsqlite.Storage')
  43
  44     @property
  45     def db(self):
  46         if self.__db is None:
  47             self.__db = sqlite.connect(self.__db_file)
  48             self.__log.debug('Connected to %s', self.__db_file)
  49             self.__check_schema()
  50         return self.__db
  51
  52     def cursor(self):
  53         return self.db.cursor()
  54
  55     def commit(self):
  56         try:
  57             self.__log.debug('COMMIT')
  58             self.db.commit()
  59         except ProgrammingError, e:
  60             self.__log.error('Error commiting changes!', exc_info=1)
  61
  62     def __check_schema(self):
  63         """
  64         Creates all necessary tables and indexes that don't exist.
  65         """
  66         cursor = self.cursor()
  67
  68         cursor.execute(
  69         """ CREATE TABLE IF NOT EXISTS bookmarks (
  70                 bookmark_id INTEGER PRIMARY KEY AUTOINCREMENT,
  71                 bookmark_name TEXT,
  72                 playlist_id INTEGER,
  73                 bookmark_filepath TEXT,
  74                 seek_position INTEGER,
  75                 timestamp INTEGER,
  76                 is_resume_position INTEGER,
  77                 playlist_duplicate_id INTEGER
  78             ) """ )
  79
  80         cursor.execute(
  81         """ CREATE TABLE IF NOT EXISTS playlists (
  82                 playlist_id INTEGER PRIMARY KEY AUTOINCREMENT,
  83                 filepath TEXT,
  84                 timestamp INTEGER
  85             ) """ )
  86
  87         cursor.close()
  88
  89
  90     #################################
  91     # Bookmark-related functions
  92
  93     def get_bookmarks(self, playlist_id=None, bookmark_filepath=None,
  94         playlist_duplicate_id=None, request_resume_bookmark=None):
  95         """ Note: the request_resume_bookmark argument will only return resume
  96                 bookmarks if it's set to True. If you don't care whether you're
  97                 getting resume bookmarks or not set it to None """
  98
  99         sql = 'SELECT * FROM bookmarks'
 100         conditions = []
 101         args = []
 102
 103         if playlist_id is not None:
 104             conditions.append('playlist_id = ?')
 105             args.append(playlist_id)
 106
 107         if bookmark_filepath is not None:
 108             conditions.append('bookmark_filepath = ?')
 109             args.append(bookmark_filepath)
 110
 111         if playlist_duplicate_id is not None:
 112             conditions.append('playlist_duplicate_id = ?')
 113             args.append(playlist_duplicate_id)
 114
 115         if request_resume_bookmark is not None:
 116             conditions.append('is_resume_position = ?')
 117             args.append(request_resume_bookmark)
 118
 119         if conditions:
 120             sql += ' WHERE '
 121
 122         sql += string.join(conditions, ' AND ')
 123
 124         cursor = self.cursor()
 125         cursor.execute( sql, args )
 126         bookmarks = cursor.fetchall()
 127         cursor.close()
 128
 129         return bookmarks
 130
 131     def bookmark_exists(self, playlist_id):
 132         return self.get_bookmarks( playlist_id ) is not None
 133
 134     def load_bookmarks(self, factory, *args, **kwargs):
 135         """ Load bookmarks into a dict and return a list of dicts or
 136                 return a list of the outputs from the factory function.
 137                 Set the factory function to None to not use it.
 138             Note: This is a wrapper around get_bookmarks, see get_bookmarks
 139                 for all available arguments. """
 140
 141         bkmks = self.get_bookmarks( *args, **kwargs )
 142
 143         if bkmks is None:
 144             return []
 145
 146         bkmk_list = []
 147         for bkmk in bkmks:
 148             BKMK = {
 149                 'id'                    : bkmk[0],
 150                 'bookmark_name'         : bkmk[1],
 151                 'playlist_id'           : bkmk[2],
 152                 'bookmark_filepath'     : bkmk[3],
 153                 'seek_position'         : bkmk[4],
 154                 'timestamp'             : bkmk[5],
 155                 'is_resume_position'    : bool(bkmk[6]),
 156                 'playlist_duplicate_id' : bkmk[7],
 157             }
 158
 159             if factory is not None:
 160                 BKMK = factory(BKMK)
 161
 162             bkmk_list.append(BKMK)
 163
 164         return bkmk_list
 165
 166     def save_bookmark(self, bookmark):
 167         if bookmark.id < 0:
 168             self.__log.warn('Not saving bookmark with negative id (%d)', bookmark.id)
 169             return bookmark.id
 170
 171         if bookmark.playlist_id is None:
 172             self.__log.warn('Not saving bookmark without playlist filepath')
 173             return bookmark.id
 174
 175         if bookmark.is_resume_position:
 176             self.remove_resume_bookmark( bookmark.playlist_id )
 177
 178         self.__log.info('Saving %s, %d (%s)', bookmark.bookmark_name,
 179             bookmark.seek_position, bookmark.bookmark_filepath )
 180
 181         cursor = self.cursor()
 182         cursor.execute(
 183             """ INSERT INTO bookmarks (
 184                 bookmark_name,
 185                 playlist_id,
 186                 bookmark_filepath,
 187                 seek_position,
 188                 timestamp,
 189                 is_resume_position,
 190                 playlist_duplicate_id
 191                 ) VALUES (?, ?, ?, ?, ?, ?, ?) """,
 192
 193             ( bookmark.bookmark_name, bookmark.playlist_id,
 194             bookmark.bookmark_filepath, bookmark.seek_position,
 195             bookmark.timestamp, bookmark.is_resume_position,
 196             bookmark.playlist_duplicate_id ))
 197
 198         r_id = self.__get__( 'SELECT last_insert_rowid()' )
 199
 200         cursor.close()
 201         self.commit()
 202
 203         return r_id[0]
 204
 205     def update_bookmark(self, bookmark):
 206         self.__log.info('Updating %s (%s)',
 207             bookmark.bookmark_name, bookmark.playlist_id )
 208
 209         cursor = self.cursor()
 210         cursor.execute(
 211             """ UPDATE bookmarks SET
 212                 bookmark_name = ?,
 213                 playlist_id = ?,
 214                 bookmark_filepath = ?,
 215                 seek_position = ?,
 216                 timestamp = ?,
 217                 is_resume_position = ?,
 218                 playlist_duplicate_id = ?
 219                 WHERE bookmark_id = ? """,
 220
 221             ( bookmark.bookmark_name, bookmark.playlist_id,
 222             bookmark.bookmark_filepath, bookmark.seek_position,
 223             bookmark.timestamp, bookmark.is_resume_position,
 224             bookmark.playlist_duplicate_id, bookmark.id ))
 225
 226         cursor.close()
 227         self.commit()
 228
 229     def remove_bookmark(self, bookmark_id):
 230         self.__log.info('Deleting bookmark by id: %s', bookmark_id)
 231         assert bookmark_id >= 0
 232
 233         cursor = self.cursor()
 234         cursor.execute(
 235             'DELETE FROM bookmarks WHERE bookmark_id = ?', (bookmark_id,) )
 236
 237         cursor.close()
 238         self.commit()
 239
 240     def remove_resume_bookmark(self, playlist_id):
 241         self.__log.info('Deleting resume bookmark for: %s', playlist_id)
 242
 243         cursor = self.cursor()
 244         cursor.execute(
 245             """ DELETE FROM bookmarks WHERE
 246                 playlist_id = ? AND
 247                 is_resume_position = 1 """,
 248
 249             ( playlist_id, ))
 250
 251         cursor.close()
 252         self.commit()
 253
 254     def remove_all_bookmarks(self, playlist_id):
 255         self.__log.info('Deleting all bookmarks for: %s', playlist_id)
 256
 257         cursor = self.cursor()
 258         cursor.execute(
 259             """ DELETE FROM bookmarks WHERE
 260                 playlist_id = ? """,
 261
 262             ( playlist_id, ))
 263
 264         cursor.close()
 265         self.commit()
 266
 267
 268     #################################
 269     # Playlist-related functions
 270
 271     def playlist_exists(self, filepath):
 272         if filepath is not None and filepath:
 273             return self.__get__( 'SELECT * FROM playlists WHERE filepath = ?',
 274                 filepath ) is not None
 275         else:
 276             return False
 277
 278     def get_playlist_id(self, filepath, create_new=False, update_time=False):
 279         """ Get a playlist_id by it's filepath
 280                 create_new: if True it will create a new playlist
 281                     entry if none exists for the filepath.
 282                 update_time: if True it updates the timestamp for the
 283                     playlist entry of the filepath. """
 284
 285         if self.playlist_exists(filepath):
 286             playlist_id = self.__get__(
 287                 'SELECT playlist_id FROM playlists WHERE filepath = ?',
 288                 filepath )[0]
 289         elif create_new:
 290             playlist_id = self.add_playlist( filepath )
 291         else:
 292             playlist_id = None
 293
 294         if playlist_id is not None and update_time:
 295             self.update_playlist( playlist_id, filepath )
 296
 297         return playlist_id
 298
 299     def add_playlist(self, filepath, timestamp=None):
 300         self.__log.info( 'Adding playlist: %s', filepath )
 301
 302         if timestamp is None:
 303             timestamp = time.time()
 304
 305         cursor = self.cursor()
 306         cursor.execute(
 307             """ INSERT INTO playlists (filepath, timestamp) VALUES (?,?) """,
 308             ( filepath, timestamp ) )
 309
 310         cursor.close()
 311         self.commit()
 312
 313         r_id = self.__get__( 'SELECT last_insert_rowid()' )[0]
 314
 315         return r_id
 316
 317     def update_playlist(self, playlist_id, filepath, timestamp=None):
 318         self.__log.info( 'Updating playlist: %s', filepath )
 319
 320         if timestamp is None:
 321             timestamp = time.time()
 322
 323         cursor = self.cursor()
 324         cursor.execute(
 325             """ UPDATE playlists SET
 326                 filepath = ?,
 327                 timestamp = ?
 328                 WHERE playlist_id = ? """,
 329
 330             ( filepath, timestamp, playlist_id ) )
 331
 332         cursor.close()
 333         self.commit()
 334
 335     def delete_playlist(self, playlist_id):
 336         self.__log.info( 'Deleting playlist: %d', playlist_id )
 337
 338         cursor = self.cursor()
 339         cursor.execute(
 340             """ DELETE FROM playlists WHERE playlist_id = ? """,
 341             ( playlist_id, ))
 342
 343         cursor.close()
 344         self.commit()
 345
 346     def get_latest_files(self):
 347         self.__log.debug('Finding latest files...')
 348         cursor = self.cursor()
 349
 350         cursor.execute(
 351             """ SELECT filepath FROM playlists
 352                 ORDER BY timestamp DESC """)
 353
 354         files = cursor.fetchall()
 355         cursor.close()
 356
 357         return [ f[0] for f in files ]
 358
 359
 360     def __get__(self, sql, params=None):
 361         """ Returns the first row of a query result """
 362
 363         cursor = self.cursor()
 364
 365         if params is None:
 366             cursor.execute(sql)
 367         else:
 368             if not isinstance( params, (list, tuple) ):
 369                 params = [ params, ]
 370
 371             cursor.execute(sql, params)
 372
 373         row = cursor.fetchone()
 374         cursor.close()
 375
 376         return row
 377
 378 db = Storage(os.path.expanduser(settings.db_location))
 379