src/gpodder/dbsqlite.py

   1 # -*- coding: utf-8 -*-
   2 #
   3 # gPodder - A media aggregator and podcast client
   4 # Copyright (c) 2005-2008 Thomas Perl and the gPodder Team
   5 #
   6 # gPodder 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 # gPodder 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 this program.  If not, see <http://www.gnu.org/licenses/>.
  18 #
  19 # dbsqlite.py -- SQLite interface
  20 # Justin Forest <justin.forest@gmail.com> 2008-06-13
  21
  22 have_sqlite = True
  23
  24 try:
  25     from sqlite3 import dbapi2 as sqlite
  26 except ImportError:
  27     try:
  28         from pysqlite2 import dbapi2 as sqlite
  29     except ImportError:
  30         have_sqlite = False
  31
  32 # TODO: show a message box
  33 if not have_sqlite:
  34     print "Please install pysqlite2 or upgrade to Python 2.5."
  35     import sys
  36     sys.exit()
  37
  38 from gpodder.liblogger import log
  39 from email.Utils import mktime_tz
  40 from email.Utils import parsedate_tz
  41 from email.Utils import formatdate
  42 from threading import RLock
  43 import string
  44
  45 class Storage(object):
  46     (STATE_NORMAL, STATE_DOWNLOADED, STATE_DELETED) = range(3)
  47
  48     lock = None
  49
  50     def __init__(self):
  51         self.settings = {}
  52         self.channel_map = {}
  53         self._db = None
  54         self.lock = RLock()
  55
  56     def setup(self, settings):
  57         self.settings = settings
  58         self.__check_schema()
  59
  60     @property
  61     def db(self):
  62         if self._db is None:
  63             self._db = sqlite.connect(self.settings['database'], check_same_thread=False)
  64             self._db.create_collation("unicode", lambda a, b: cmp(a.lower().replace('the ', ''), b.lower().replace('the ', '')))
  65             log('SQLite connected', sender=self)
  66         return self._db
  67
  68     def cursor(self, lock=False):
  69         if lock:
  70             self.lock.acquire()
  71         return self.db.cursor()
  72
  73     def commit(self):
  74         self.lock.acquire()
  75         try:
  76             self.db.commit()
  77         except ProgrammingError, e:
  78             log('Error commiting changes: %s', e, sender=self, traceback=True)
  79         self.lock.release()
  80
  81     def __check_schema(self):
  82         """
  83         Creates all necessary tables and indexes that don't exist.
  84         """
  85         log('Setting up SQLite database', sender=self)
  86
  87         cur = self.cursor(lock=True)
  88
  89         cur.execute("""CREATE TABLE IF NOT EXISTS channels (
  90             id INTEGER PRIMARY KEY,
  91             url TEXT,
  92             title TEXT,
  93             override_title TEXT,
  94             link TEXT,
  95             description TEXT,
  96             image TEXT,
  97             pubDate INTEGER,
  98             sync_to_devices INTEGER,
  99             device_playlist_name TEXT,
 100             username TEXT,
 101             password TEXT,
 102             last_modified TEXT,
 103             etag TEXT,
 104             deleted INTEGER
 105             )""")
 106         cur.execute("""CREATE UNIQUE INDEX IF NOT EXISTS idx_url ON channels (url)""")
 107         cur.execute("""CREATE INDEX IF NOT EXISTS idx_sync_to_devices ON channels (sync_to_devices)""")
 108         cur.execute("""CREATE INDEX IF NOT EXISTS idx_title ON channels (title)""")
 109         cur.execute("""CREATE INDEX IF NOT EXISTS idx_deleted ON channels (deleted)""")
 110
 111         cur.execute("""
 112             CREATE TABLE IF NOT EXISTS episodes (
 113                 id INTEGER PRIMARY KEY,
 114                 channel_id INTEGER,
 115                 url TEXT,
 116                 title TEXT,
 117                 length INTEGER,
 118                 mimetype TEXT,
 119                 guid TEXT,
 120                 description TEXT,
 121                 link TEXT,
 122                 pubDate INTEGER,
 123                 state INTEGER,
 124                 played INTEGER,
 125                 locked INTEGER
 126                 )
 127             """)
 128         cur.execute("""CREATE UNIQUE INDEX IF NOT EXISTS idx_guid ON episodes (guid)""")
 129         cur.execute("""CREATE INDEX IF NOT EXISTS idx_channel_id ON episodes (channel_id)""")
 130         cur.execute("""CREATE INDEX IF NOT EXISTS idx_pubDate ON episodes (pubDate)""")
 131         cur.execute("""CREATE INDEX IF NOT EXISTS idx_state ON episodes (state)""")
 132         cur.execute("""CREATE INDEX IF NOT EXISTS idx_played ON episodes (played)""")
 133         cur.execute("""CREATE INDEX IF NOT EXISTS idx_locked ON episodes (locked)""")
 134
 135         cur.close()
 136         self.lock.release()
 137
 138     def get_channel_stat(self, url_or_id, state=None, is_played=None, is_locked=None):
 139         where, params = ((),())
 140
 141         if state is not None:
 142             where += ("state = ?", )
 143             params += (state, )
 144         if is_played is not None:
 145             where += ("played = ?", )
 146             params += (is_played, )
 147         if is_locked is not None:
 148             where += ("locked = ?", )
 149             params += (is_locked, )
 150         if isinstance(url_or_id, int):
 151             where += ("channel_id = ?", )
 152             params += (url_or_id, )
 153         else:
 154             where += ("channel_id IN (SELECT id FROM channels WHERE url = ?)", )
 155             params += (url_or_id, )
 156
 157         if len(where):
 158             return self.__get__("SELECT COUNT(*) FROM episodes WHERE %s" % (' AND '.join(where)), params)
 159         else:
 160             return 0
 161
 162     def load_channels(self, factory=None, url=None):
 163         """
 164         Returns channel descriptions as a list of dictionaries or objects,
 165         returned by the factory() function, which receives the dictionary
 166         as the only argument.
 167         """
 168
 169         cur = self.cursor(lock=True)
 170         cur.execute("""
 171             SELECT
 172                 id,
 173                 url,
 174                 title,
 175                 override_title,
 176                 link,
 177                 description,
 178                 image,
 179                 pubDate,
 180                 sync_to_devices,
 181                 device_playlist_name,
 182                 username,
 183                 password,
 184                 last_modified,
 185                 etag
 186             FROM
 187                 channels
 188             WHERE
 189                 (deleted IS NULL OR deleted = 0)
 190             ORDER BY
 191                 title COLLATE unicode
 192                 """)
 193
 194         result = []
 195         for row in cur.fetchall():
 196             channel = {
 197                 'id': row[0],
 198                 'url': row[1],
 199                 'title': row[2],
 200                 'override_title': row[3],
 201                 'link': row[4],
 202                 'description': row[5],
 203                 'image': row[6],
 204                 'pubDate': self.__formatdate__(row[7]),
 205                 'sync_to_devices': row[8],
 206                 'device_playlist_name': row[9],
 207                 'username': row[10],
 208                 'password': row[11],
 209                 'last_modified': row[12],
 210                 'etag': row[13],
 211                 }
 212
 213             if url is None:
 214                 # Maintain url/id relation for faster updates (otherwise
 215                 # we'd need to issue an extra query to find the channel id).
 216                 self.channel_map[channel['url']] = channel['id']
 217
 218             if url is None or url == channel['url']:
 219                 if factory is None:
 220                     result.append(channel)
 221                 else:
 222                     result.append(factory(channel))
 223
 224         cur.close()
 225         self.lock.release()
 226
 227         if url is None:
 228             log('Channel list read, %d entries.', len(result), sender=self)
 229         else:
 230             log('Channel %s read from db', url, sender=self)
 231
 232         return result
 233
 234     def save_channel(self, c, bulk=False):
 235         if c.id is None:
 236             c.id = self.find_channel_id(c.url)
 237
 238         cur = self.cursor(lock=True)
 239
 240         if c.id is None:
 241             cur.execute("INSERT INTO channels (url, title, override_title, link, description, image, pubDate, sync_to_devices, device_playlist_name, username, password, last_modified, etag) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (c.url, c.title, c.override_title, c.link, c.description, c.image, self.__mktime__(c.pubDate), c.sync_to_devices, c.device_playlist_name, c.username, c.password, c.last_modified, c.etag, ))
 242             self.channel_map[c.url] = cur.lastrowid
 243             log('Added channel %s[%d]', c.url, self.channel_map[c.url], sender=self)
 244         else:
 245             cur.execute("UPDATE channels SET url = ?, title = ?, override_title = ?, link = ?, description = ?, image = ?, pubDate = ?, sync_to_devices = ?, device_playlist_name = ?, username = ?, password = ?, last_modified = ?, etag = ?, deleted = 0 WHERE id = ?", (c.url, c.title, c.override_title, c.link, c.description, c.image, self.__mktime__(c.pubDate), c.sync_to_devices, c.device_playlist_name, c.username, c.password, c.last_modified, c.etag, c.id, ))
 246
 247         cur.close()
 248         self.lock.release()
 249
 250     def delete_channel(self, channel, purge=False):
 251         if channel.id is None:
 252             channel.id = self.find_channel_id(channel.url)
 253
 254         cur = self.cursor(lock=True)
 255         log('Deleting channel %d', channel.id, sender=self)
 256
 257         if purge:
 258             cur.execute("DELETE FROM channels WHERE id = ?", (channel.id, ))
 259             cur.execute("DELETE FROM episodes WHERE channel_id = ?", (channel.id, ))
 260             if channel.url in self.channel_map:
 261                 del self.channel_map[channel.url]
 262         else:
 263             cur.execute("UPDATE channels SET deleted = 1 WHERE id = ?", (channel.id, ))
 264             cur.execute("DELETE FROM episodes WHERE channel_id = ? AND state <> ?", (channel.id, self.STATE_DELETED))
 265
 266         cur.close()
 267         self.lock.release()
 268
 269     def __read_episodes(self, factory=None, where=None, params=None, commit=True):
 270         sql = "SELECT url, title, length, mimetype, guid, description, link, pubDate, state, played, locked FROM episodes"
 271
 272         if where:
 273             sql = "%s %s" % (sql, where)
 274
 275         if params is None:
 276             params = ()
 277
 278         cur = self.cursor(lock=True)
 279         cur.execute(sql, params)
 280
 281         result = []
 282         for row in cur.fetchall():
 283             episode = {
 284                 'url': row[0],
 285                 'title': row[1],
 286                 'length': row[2],
 287                 'mimetype': row[3],
 288                 'guid': row[4],
 289                 'description': row[5],
 290                 'link': row[6],
 291                 'pubDate': row[7],
 292                 'state': row[8],
 293                 'is_played': row[9],
 294                 'is_locked': row[10],
 295                 }
 296             if episode['state'] is None:
 297                 episode['state'] = self.STATE_NORMAL
 298             if factory is None:
 299                 result.append(episode)
 300             else:
 301                 result.append(factory(episode))
 302
 303         cur.close()
 304         self.lock.release()
 305         return result
 306
 307     def load_episodes(self, channel, factory=None, limit=1000, state=None):
 308         if channel.id is None:
 309             channel.id = self.find_channel_id(channel.url)
 310
 311         if state is None:
 312             return self.__read_episodes(factory = factory, where = """
 313                 WHERE channel_id = ? AND state = ? OR id IN
 314                 (SELECT id FROM episodes WHERE channel_id = ?
 315                 ORDER BY pubDate DESC LIMIT ?)
 316                 ORDER BY pubDate DESC
 317                 """, params = (channel.id, self.STATE_DOWNLOADED, channel.id, limit, ))
 318         else:
 319             return self.__read_episodes(factory = factory, where = " WHERE channel_id = ? AND state = ? ORDER BY pubDate DESC LIMIT ?", params = (channel.id, state, limit, ))
 320
 321     def load_episode(self, url, factory=None):
 322         list = self.__read_episodes(factory = factory, where = " WHERE url = ?", params = (url, ))
 323         if len(list):
 324             return list[0]
 325
 326     def save_episode(self, e, bulk=False):
 327         if not e.guid:
 328             log('Refusing to save an episode without guid: %s', e)
 329             return
 330
 331         self.lock.acquire()
 332
 333         try:
 334             cur = self.cursor()
 335             channel_id = self.find_channel_id(e.channel.url)
 336
 337             if e.id is None:
 338                 e.id = self.__get__("SELECT id FROM episodes WHERE guid = ?", (e.guid, ))
 339
 340             if e.id is None:
 341                 log('Episode added: %s', e.title)
 342                 cur.execute("INSERT INTO episodes (channel_id, url, title, length, mimetype, guid, description, link, pubDate, state, played, locked) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", (channel_id, e.url, e.title, e.length, e.mimetype, e.guid, e.description, e.link, self.__mktime__(e.pubDate), e.state, e.is_played, e.is_locked, ))
 343                 e.id = cur.lastrowid
 344             else:
 345                 log('Episode updated: %s', e.title)
 346                 cur.execute("UPDATE episodes SET title = ?, length = ?, mimetype = ?, description = ?, link = ?, pubDate = ? WHERE id = ?", (e.title, e.length, e.mimetype, e.description, e.link, self.__mktime__(e.pubDate), e.id, ))
 347         except Exception, e:
 348             log('save_episode() failed: %s', e, sender=self)
 349
 350         cur.close()
 351         self.lock.release()
 352
 353     def mark_episode(self, url, state=None, is_played=None, is_locked=None, toggle=False):
 354         cur = self.cursor(lock=True)
 355         cur.execute("SELECT state, played, locked FROM episodes WHERE url = ?", (url, ))
 356
 357         try:
 358             ( cur_state, cur_played, cur_locked ) = cur.fetchone()
 359         except:
 360             # This only happens when we try to mark an unknown episode,
 361             # which is typical for database upgrade, so we just ignore it.
 362             cur.close()
 363             self.lock.release()
 364             return
 365
 366         if toggle:
 367             if is_played:
 368                 cur_played = not cur_played
 369             if is_locked:
 370                 cur_locked = not cur_locked
 371         else:
 372             if state is not None:
 373                 cur_state = state
 374             if is_played is not None:
 375                 cur_played = is_played
 376             if is_locked is not None:
 377                 cur_locked = is_locked
 378
 379         cur.close()
 380
 381         cur = self.cursor()
 382         cur.execute("UPDATE episodes SET state = ?, played = ?, locked = ? WHERE url = ?", (cur_state, cur_played, cur_locked, url, ))
 383         cur.close()
 384
 385         self.lock.release()
 386
 387     def __get__(self, sql, params=None):
 388         """
 389         Returns the first cell of a query result, useful for COUNT()s.
 390         """
 391         cur = self.cursor(lock=True)
 392
 393         if params is None:
 394             cur.execute(sql)
 395         else:
 396             cur.execute(sql, params)
 397
 398         row = cur.fetchone()
 399         cur.close()
 400         self.lock.release()
 401
 402         if row is None:
 403             return None
 404         else:
 405             return row[0]
 406
 407     def __mktime__(self, date):
 408         if isinstance(date, float) or isinstance(date, int):
 409             return date
 410         if date is None or '' == date:
 411             return None
 412         try:
 413             return mktime_tz(parsedate_tz(date))
 414         except TypeError:
 415             log('Could not convert "%s" to a unix timestamp.', date)
 416             return None
 417
 418     def __formatdate__(self, date):
 419         try:
 420             return formatdate(date, localtime=1)
 421         except TypeError:
 422             log('Could not convert "%s" to a string date.', date)
 423             return None
 424
 425     def find_channel_id(self, url):
 426         """
 427         Looks up the channel id in the map (which lists all undeleted
 428         channels), then tries to look it up in the database, including
 429         deleted channels.
 430         """
 431         if url in self.channel_map.keys():
 432             return self.channel_map[url]
 433         else:
 434             return self.__get__("SELECT id FROM channels WHERE url = ?", (url, ))
 435
 436     def force_last_new(self, channel):
 437         old = self.__get__("""SELECT COUNT(*) FROM episodes WHERE channel_id = ?
 438             AND state IN (?, ?)""", (channel.id, self.STATE_DOWNLOADED,
 439             self.STATE_DELETED))
 440         log('old episodes in (%d)%s: %d', channel.id, channel.url, old)
 441
 442         cur = self.cursor(lock=True)
 443
 444         if old > 0:
 445             cur.execute("""
 446                 UPDATE episodes SET played = 1 WHERE channel_id = ?
 447                 AND played = 0 AND pubDate < (SELECT MAX(pubDate)
 448                 FROM episodes WHERE channel_id = ? AND state IN (?, ?))""",
 449                 (channel.id, channel.id, self.STATE_DOWNLOADED,
 450                 self.STATE_DELETED, ))
 451         else:
 452             cur.execute("""
 453                 UPDATE episodes SET played = 1 WHERE channel_id = ?
 454                 AND pubDate <> (SELECT MAX(pubDate) FROM episodes
 455                 WHERE channel_id = ?)""", (channel.id, channel.id, ))
 456
 457         cur.close()
 458         self.lock.release()
 459
 460 db = Storage()