src/gpodder/dbsqlite.py

   1 # -*- coding: utf-8 -*-
   2 #
   3 # gPodder - A media aggregator and podcast client
   4 # Copyright (c) 2005-2010 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 import gpodder
  23 _ = gpodder.gettext
  24
  25 import sys
  26
  27 have_sqlite = True
  28
  29 try:
  30     from sqlite3 import dbapi2 as sqlite
  31     from sqlite3 import OperationalError
  32 except ImportError:
  33     try:
  34         from pysqlite2 import dbapi2 as sqlite
  35         from pysqlite2.dbapi2 import OperationalError
  36     except ImportError:
  37         have_sqlite = False
  38
  39 # TODO: show a message box
  40 if not have_sqlite:
  41     print >>sys.stderr, 'Please install pysqlite2 or Python 2.5.'
  42     sys.exit(1)
  43
  44 from gpodder.liblogger import log
  45 from email.Utils import mktime_tz
  46 from email.Utils import parsedate_tz
  47 from email.Utils import formatdate
  48 from threading import RLock
  49 import string
  50 import re
  51
  52 class Database(object):
  53     UNICODE_TRANSLATE = {ord(u'ö'): u'o', ord(u'ä'): u'a', ord(u'ü'): u'u'}
  54
  55     def __init__(self, filename):
  56         self.database_file = filename
  57         self.channel_map = {}
  58         self._db = None
  59         self.lock = RLock()
  60
  61     def close(self):
  62         self.commit()
  63
  64         cur = self.cursor(lock=True)
  65         log('Optimizing database for faster startup.', sender=self)
  66         cur.execute("VACUUM")
  67         cur.close()
  68         self.lock.release()
  69
  70         self._db.close()
  71         self._db = None
  72
  73     def log(self, message, *args, **kwargs):
  74         if False:
  75             try:
  76                 message = message % args
  77                 log('%s', message, sender=self)
  78             except TypeError, e:
  79                 log('Exception in log(): %s: %s', e, message, sender=self)
  80
  81     def purge(self, max_episodes, channel_id):
  82         """
  83         Deletes old episodes.  Should be called
  84         before adding new episodes to a channel.
  85         """
  86         cur = self.cursor(lock=True)
  87
  88         self.log("purge(%s)", channel_id)
  89         sql = """
  90             DELETE FROM episodes
  91             WHERE channel_id = ?
  92             AND state <> ?
  93             AND id NOT IN
  94             (SELECT id FROM episodes WHERE channel_id = ?
  95             ORDER BY pubDate DESC LIMIT ?)"""
  96         cur.execute(sql, (channel_id, gpodder.STATE_DOWNLOADED, channel_id, max_episodes))
  97
  98         cur.close()
  99         self.lock.release()
 100
 101     def db_sort_cmp(self, a, b):
 102         """
 103         Compare two strings for sorting, including removing
 104         a possible "The " prefix and converting umlauts to
 105         normal characters so they can be sorted correctly.
 106         (i.e. "Ö1" should not appear at the end of the list)
 107         """
 108         try:
 109             a = a.decode('utf-8', 'ignore').lower()
 110             a = re.sub('^the ', '', a)
 111             a = a.translate(self.UNICODE_TRANSLATE)
 112             b = b.decode('utf-8', 'ignore').lower()
 113             b = re.sub('^the ', '', b)
 114             b = b.translate(self.UNICODE_TRANSLATE)
 115             return cmp(a, b)
 116         except:
 117             log('Error while comparing "%s" and "%s"', a, b, sender=self, traceback=True)
 118             a = re.sub('^the ', '', a.lower())
 119             b = re.sub('^the ', '', b.lower())
 120             return cmp(a, b)
 121
 122     @property
 123     def db(self):
 124         if self._db is None:
 125             self._db = sqlite.connect(self.database_file, check_same_thread=False)
 126             self._db.text_factory = str
 127             self._db.create_collation("UNICODE", self.db_sort_cmp)
 128             self.log('Connected')
 129             self.__check_schema()
 130         return self._db
 131
 132     def cursor(self, lock=False):
 133         if lock:
 134             self.lock.acquire()
 135         return self.db.cursor()
 136
 137     def commit(self):
 138         self.lock.acquire()
 139         try:
 140             self.log("COMMIT")
 141             self.db.commit()
 142         except ProgrammingError, e:
 143             log('Error commiting changes: %s', e, sender=self, traceback=True)
 144         self.lock.release()
 145
 146     def __check_schema(self):
 147         """
 148         Creates all necessary tables and indexes that don't exist.
 149         """
 150         self.log('Setting up tables and views')
 151
 152         cur = self.cursor(lock=True)
 153
 154         self.upgrade_table("channels", (
 155             ("id", "INTEGER PRIMARY KEY"),
 156             ("url", "TEXT"),
 157             ("title", "TEXT"),
 158             ("override_title", "TEXT"),
 159             ("link", "TEXT"),
 160             ("description", "TEXT"),
 161             ("image", "TEXT"),
 162             ("pubDate", "INTEGER"),
 163             ("sync_to_devices", "INTEGER"),
 164             ("device_playlist_name", "TEXT"),
 165             ("username", "TEXT"),
 166             ("password", "TEXT"),
 167             ("last_modified", "TEXT"),
 168             ("etag", "TEXT"),
 169             ("deleted", "INTEGER"),
 170             ("channel_is_locked", "INTEGER"),
 171             ("foldername", "TEXT"),
 172             ("auto_foldername", "INTEGER"),
 173             ("release_expected", "INTEGER"),
 174             ("release_deviation", "INTEGER"),
 175             ("updated_timestamp", "INTEGER"),
 176             ))
 177
 178         self.upgrade_table("episodes", (
 179             ("id", "INTEGER PRIMARY KEY"),
 180             ("channel_id", "INTEGER"),
 181             ("url", "TEXT"),
 182             ("title", "TEXT"),
 183             ("length", "INTEGER"),
 184             ("mimetype", "TEXT"),
 185             ("guid", "TEXT"),
 186             ("description", "TEXT"),
 187             ("link", "TEXT"),
 188             ("pubDate", "INTEGER"),
 189             ("state", "INTEGER"),
 190             ("played", "INTEGER"),
 191             ("locked", "INTEGER"),
 192             ("filename", "TEXT"),
 193             ("auto_filename", "INTEGER"),
 194             ))
 195
 196         cur.execute("""CREATE UNIQUE INDEX IF NOT EXISTS idx_foldername ON channels (foldername)""")
 197         cur.execute("""CREATE UNIQUE INDEX IF NOT EXISTS idx_url ON channels (url)""")
 198         cur.execute("""CREATE INDEX IF NOT EXISTS idx_sync_to_devices ON channels (sync_to_devices)""")
 199         cur.execute("""CREATE INDEX IF NOT EXISTS idx_title ON channels (title)""")
 200         cur.execute("""CREATE INDEX IF NOT EXISTS idx_deleted ON channels (deleted)""")
 201
 202         cur.execute("""CREATE UNIQUE INDEX IF NOT EXISTS idx_guid ON episodes (guid)""")
 203         cur.execute("""CREATE UNIQUE INDEX IF NOT EXISTS idx_filename ON episodes (filename)""")
 204         cur.execute("""CREATE INDEX IF NOT EXISTS idx_channel_id ON episodes (channel_id)""")
 205         cur.execute("""CREATE INDEX IF NOT EXISTS idx_pubDate ON episodes (pubDate)""")
 206         cur.execute("""CREATE INDEX IF NOT EXISTS idx_state ON episodes (state)""")
 207         cur.execute("""CREATE INDEX IF NOT EXISTS idx_played ON episodes (played)""")
 208         cur.execute("""CREATE INDEX IF NOT EXISTS idx_locked ON episodes (locked)""")
 209
 210         cur.execute("""CREATE TEMPORARY VIEW episodes_downloaded AS SELECT channel_id, COUNT(*) AS count FROM episodes WHERE state = 1 GROUP BY channel_id""")
 211         cur.execute("""CREATE TEMPORARY VIEW episodes_new AS SELECT channel_id, COUNT(*) AS count FROM episodes WHERE state = 0 AND played = 0 GROUP BY channel_id""")
 212         cur.execute("""CREATE TEMPORARY VIEW episodes_unplayed AS SELECT channel_id, COUNT(*) AS count FROM episodes WHERE played = 0 AND state = %d GROUP BY channel_id""" % gpodder.STATE_DOWNLOADED)
 213
 214         # Make sure deleted episodes are played, to simplify querying statistics.
 215         try:
 216             cur.execute("UPDATE episodes SET played = 1 WHERE state = ?", (gpodder.STATE_DELETED, ))
 217         except OperationalError:
 218             pass
 219
 220         cur.close()
 221         self.lock.release()
 222
 223     def get_channel_count(self, id):
 224         """Given a channel ID, returns the statistics for it
 225
 226         Returns a tuple (total, deleted, new, downloaded, unplayed)
 227         """
 228         total = self.__get__('SELECT COUNT(*) FROM episodes WHERE channel_id = ?', (id,))
 229         deleted = self.__get__('SELECT COUNT(*) FROM episodes WHERE state = ? AND channel_id = ?', (gpodder.STATE_DELETED, id))
 230         new = self.__get__('SELECT COUNT(*) FROM episodes WHERE state = ? AND played = ? AND channel_id = ?', (gpodder.STATE_NORMAL, False, id))
 231         downloaded = self.__get__('SELECT COUNT(*) FROM episodes WHERE state = ? AND channel_id = ?', (gpodder.STATE_DOWNLOADED, id))
 232         unplayed = self.__get__('SELECT COUNT(*) FROM episodes WHERE state = ? AND played = ? AND channel_id = ?', (gpodder.STATE_DOWNLOADED, False, id))
 233         return (total, deleted, new, downloaded, unplayed)
 234
 235     def get_total_count(self):
 236         """Get statistics for all non-deleted channels
 237
 238         Returns a tuple (total, deleted, new, downloaded, unplayed)
 239         """
 240         total = self.__get__('SELECT COUNT(*) FROM episodes WHERE channel_id IN (SELECT id FROM channels WHERE (deleted IS NULL OR deleted=0))')
 241         deleted = self.__get__('SELECT COUNT(*) FROM episodes WHERE state = ? AND channel_id IN (SELECT id FROM channels WHERE (deleted IS NULL OR deleted=0))', (gpodder.STATE_DELETED,))
 242         new = self.__get__('SELECT COUNT(*) FROM episodes WHERE state = ? AND played = ? AND channel_id IN (SELECT id FROM channels WHERE (deleted IS NULL OR deleted=0))', (gpodder.STATE_NORMAL, False,))
 243         downloaded = self.__get__('SELECT COUNT(*) FROM episodes WHERE state = ? AND channel_id IN (SELECT id FROM channels WHERE (deleted IS NULL OR deleted=0))', (gpodder.STATE_DOWNLOADED,))
 244         unplayed = self.__get__('SELECT COUNT(*) FROM episodes WHERE state = ? AND played = ? AND channel_id IN (SELECT id FROM channels WHERE (deleted IS NULL OR deleted=0))', (gpodder.STATE_DOWNLOADED, False,))
 245         return (total, deleted, new, downloaded, unplayed)
 246
 247     def load_channels(self, factory=None, url=None):
 248         """
 249         Returns channel descriptions as a list of dictionaries or objects,
 250         returned by the factory() function, which receives the dictionary
 251         as the only argument.
 252         """
 253
 254         self.log("load_channels()")
 255
 256         cur = self.cursor(lock=True)
 257         cur.execute("""
 258             SELECT
 259                 id,
 260                 url,
 261                 title,
 262                 override_title,
 263                 link,
 264                 description,
 265                 image,
 266                 pubDate,
 267                 sync_to_devices,
 268                 device_playlist_name,
 269                 username,
 270                 password,
 271                 last_modified,
 272                 etag,
 273                 channel_is_locked,
 274                 foldername,
 275                 auto_foldername,
 276                 release_expected,
 277                 release_deviation,
 278                 updated_timestamp
 279             FROM
 280                 channels
 281             WHERE
 282                 (deleted IS NULL OR deleted = 0)
 283             ORDER BY
 284                 title COLLATE UNICODE
 285                 """)
 286
 287         stats = self.stats()
 288
 289         result = []
 290         for row in cur.fetchall():
 291             channel = {
 292                 'id': row[0],
 293                 'url': row[1],
 294                 'title': row[2],
 295                 'override_title': row[3],
 296                 'link': row[4],
 297                 'description': row[5],
 298                 'image': row[6],
 299                 'pubDate': self.__formatdate__(row[7]),
 300                 'sync_to_devices': row[8],
 301                 'device_playlist_name': row[9],
 302                 'username': row[10],
 303                 'password': row[11],
 304                 'last_modified': row[12],
 305                 'etag': row[13],
 306                 'channel_is_locked': row[14],
 307                 'foldername': row[15],
 308                 'auto_foldername': row[16],
 309                 'release_expected': row[17],
 310                 'release_deviation': row[18],
 311                 'updated_timestamp': row[19],
 312                 }
 313
 314             if row[0] in stats:
 315                 channel['count_downloaded'] = stats[row[0]][0]
 316                 channel['count_new'] = stats[row[0]][1]
 317                 channel['count_unplayed'] = stats[row[0]][2]
 318
 319             if url is None:
 320                 # Maintain url/id relation for faster updates (otherwise
 321                 # we'd need to issue an extra query to find the channel id).
 322                 self.channel_map[channel['url']] = channel['id']
 323
 324             if url is None or url == channel['url']:
 325                 if factory is None:
 326                     result.append(channel)
 327                 else:
 328                     result.append(factory(channel, self))
 329
 330         cur.close()
 331         self.lock.release()
 332
 333         return result
 334
 335     def stats(self):
 336         cur = self.cursor(lock=True)
 337         self.log("stats()")
 338         cur.execute("""
 339             SELECT c.id, d.count, n.count, u.count
 340             FROM channels c
 341             LEFT JOIN episodes_downloaded d ON d.channel_id = c.id
 342             LEFT JOIN episodes_new n ON n.channel_id = c.id
 343             LEFT JOIN episodes_unplayed u ON u.channel_id = c.id
 344             WHERE c.deleted = 0
 345             """)
 346
 347         data = {}
 348
 349         for row in cur.fetchall():
 350             data[row[0]] = (row[1] or 0, row[2] or 0, row[3] or 0)
 351
 352         cur.close()
 353         self.lock.release()
 354
 355         return data
 356
 357     def save_channel(self, c):
 358         if c.id is None:
 359             c.id = self.find_channel_id(c.url)
 360
 361         cur = self.cursor(lock=True)
 362         self.log("save_channel((%s)%s)", c.id or "new", c.url)
 363
 364         if c.id is None:
 365             cur.execute("INSERT INTO channels (url, title, override_title, link, description, image, pubDate, sync_to_devices, device_playlist_name, username, password, last_modified, etag, channel_is_locked, foldername, auto_foldername, release_expected, release_deviation, updated_timestamp) 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, c.channel_is_locked, c.foldername, c.auto_foldername, c.release_expected, c.release_deviation, c.updated_timestamp))
 366             self.channel_map[c.url] = cur.lastrowid
 367         else:
 368             cur.execute("UPDATE channels SET url = ?, title = ?, override_title = ?, link = ?, description = ?, image = ?, pubDate = ?, sync_to_devices = ?, device_playlist_name = ?, username = ?, password = ?, last_modified = ?, etag = ?, channel_is_locked = ?, foldername = ?, auto_foldername = ?, release_expected = ?, release_deviation = ?, updated_timestamp = ?, 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.channel_is_locked, c.foldername, c.auto_foldername, c.release_expected, c.release_deviation, c.updated_timestamp, c.id, ))
 369
 370         cur.close()
 371         self.lock.release()
 372
 373     def delete_channel(self, channel, purge=False):
 374         if channel.id is None:
 375             channel.id = self.find_channel_id(channel.url)
 376
 377         cur = self.cursor(lock=True)
 378         self.log("delete_channel((%d)%s), purge=%d", channel.id, channel.url, purge)
 379
 380         if purge:
 381             cur.execute("DELETE FROM channels WHERE id = ?", (channel.id, ))
 382             cur.execute("DELETE FROM episodes WHERE channel_id = ?", (channel.id, ))
 383             if channel.url in self.channel_map:
 384                 del self.channel_map[channel.url]
 385         else:
 386             cur.execute("UPDATE channels SET deleted = 1 WHERE id = ?", (channel.id, ))
 387             cur.execute("DELETE FROM episodes WHERE channel_id = ? AND state <> ?", (channel.id, gpodder.STATE_DOWNLOADED))
 388
 389         cur.close()
 390         # Commit changes
 391         self.db.commit()
 392         self.lock.release()
 393
 394     def __read_episodes(self, factory=None, where=None, params=None, commit=True):
 395         sql = "SELECT url, title, length, mimetype, guid, description, link, pubDate, state, played, locked, filename, auto_filename, id FROM episodes"
 396
 397         if where:
 398             sql = "%s %s" % (sql, where)
 399
 400         if params is None:
 401             params = ()
 402
 403         cur = self.cursor(lock=True)
 404         cur.execute(sql, params)
 405
 406         result = []
 407         for row in cur.fetchall():
 408             episode = {
 409                 'url': row[0],
 410                 'title': row[1],
 411                 'length': row[2],
 412                 'mimetype': row[3],
 413                 'guid': row[4],
 414                 'description': row[5],
 415                 'link': row[6],
 416                 'pubDate': row[7],
 417                 'state': row[8],
 418                 'is_played': row[9],
 419                 'is_locked': row[10],
 420                 'filename': row[11],
 421                 'auto_filename': row[12],
 422                 'id': row[13],
 423                 }
 424             if episode['state'] is None:
 425                 episode['state'] = gpodder.STATE_NORMAL
 426             if factory is None:
 427                 result.append(episode)
 428             else:
 429                 result.append(factory(episode, self))
 430
 431         cur.close()
 432         self.lock.release()
 433         return result
 434
 435     def load_episodes(self, channel, factory=None, limit=1000, state=None):
 436         if channel.id is None:
 437             channel.id = self.find_channel_id(channel.url)
 438
 439         self.log("load_episodes((%d)%s)", channel.id, channel.url)
 440
 441         if state is None:
 442             return self.__read_episodes(factory = factory, where = """
 443                 WHERE channel_id = ? AND state = ? OR id IN
 444                 (SELECT id FROM episodes WHERE channel_id = ?
 445                 ORDER BY pubDate DESC LIMIT ?)
 446                 ORDER BY pubDate DESC
 447                 """, params = (channel.id, gpodder.STATE_DOWNLOADED, channel.id, limit, ))
 448         else:
 449             return self.__read_episodes(factory = factory, where = " WHERE channel_id = ? AND state = ? ORDER BY pubDate DESC LIMIT ?", params = (channel.id, state, limit, ))
 450
 451     def load_episode(self, url, factory=None):
 452         self.log("load_episode(%s)", url)
 453         list = self.__read_episodes(factory=factory, where=' WHERE url=? LIMIT ?', params=(url, 1))
 454         if list:
 455             return list[0]
 456         else:
 457             return None
 458
 459     def save_episode(self, e):
 460         if not e.guid:
 461             log('Refusing to save an episode without guid: %s', e)
 462             return
 463
 464         self.lock.acquire()
 465
 466         self.log("save_episode((%s)%s)", e.id, e.guid)
 467
 468         try:
 469             cur = self.cursor()
 470             channel_id = self.find_channel_id(e.channel.url)
 471
 472             if e.id is None:
 473                 e.id = self.__get__("SELECT id FROM episodes WHERE guid = ?", (e.guid, ))
 474                 self.log("save_episode() -- looking up id")
 475
 476             if e.id is None:
 477                 cur.execute("INSERT INTO episodes (channel_id, url, title, length, mimetype, guid, description, link, pubDate, state, played, locked, filename, auto_filename) 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, e.filename, e.auto_filename, ))
 478                 e.id = cur.lastrowid
 479             else:
 480                 cur.execute("UPDATE episodes SET title = ?, length = ?, mimetype = ?, description = ?, link = ?, pubDate = ?, state = ?, played = ?, locked = ?, filename = ?, auto_filename = ? WHERE id = ?", (e.title, e.length, e.mimetype, e.description, e.link, self.__mktime__(e.pubDate), e.state, e.is_played, e.is_locked, e.filename, e.auto_filename, e.id, ))
 481         except Exception, e:
 482             log('save_episode() failed: %s', e, sender=self)
 483
 484         cur.close()
 485         self.lock.release()
 486
 487     def mark_episode(self, url, state=None, is_played=None, is_locked=None, toggle=False):
 488         cur = self.cursor(lock=True)
 489         cur.execute("SELECT state, played, locked FROM episodes WHERE url = ?", (url, ))
 490
 491         self.log("mark_episode(%s, state=%s, played=%s, locked=%s)", url, state, is_played, is_locked)
 492
 493         try:
 494             ( cur_state, cur_played, cur_locked ) = cur.fetchone()
 495         except:
 496             # This only happens when we try to mark an unknown episode,
 497             # which is typical for database upgrade, so we just ignore it.
 498             cur.close()
 499             self.lock.release()
 500             return
 501
 502         if toggle:
 503             if is_played:
 504                 cur_played = not cur_played
 505             if is_locked:
 506                 cur_locked = not cur_locked
 507         else:
 508             if state is not None:
 509                 cur_state = state
 510             if is_played is not None:
 511                 cur_played = is_played
 512             if is_locked is not None:
 513                 cur_locked = is_locked
 514
 515         cur.close()
 516
 517         cur = self.cursor()
 518         cur.execute("UPDATE episodes SET state = ?, played = ?, locked = ? WHERE url = ?", (cur_state, cur_played, cur_locked, url, ))
 519         cur.close()
 520
 521         self.lock.release()
 522
 523     def update_channel_lock(self, channel):
 524         log("update_channel_lock(%s, locked=%s)", channel.url, channel.channel_is_locked, sender=self)
 525
 526         cur = self.cursor(lock=True)
 527         cur.execute("UPDATE channels SET channel_is_locked = ? WHERE url = ?", (channel.channel_is_locked, channel.url, ))
 528         cur.close()
 529
 530         self.lock.release()
 531
 532     def __get__(self, sql, params=None):
 533         """
 534         Returns the first cell of a query result, useful for COUNT()s.
 535         """
 536         cur = self.cursor(lock=True)
 537
 538         self.log("__get__(): %s", sql)
 539
 540         if params is None:
 541             cur.execute(sql)
 542         else:
 543             cur.execute(sql, params)
 544
 545         row = cur.fetchone()
 546         cur.close()
 547         self.lock.release()
 548
 549         if row is None:
 550             return None
 551         else:
 552             return row[0]
 553
 554     def __mktime__(self, date):
 555         if isinstance(date, float) or isinstance(date, int):
 556             return date
 557         if date is None or '' == date:
 558             return None
 559         try:
 560             return mktime_tz(parsedate_tz(date))
 561         except TypeError:
 562             log('Could not convert "%s" to a unix timestamp.', date)
 563             return None
 564
 565     def __formatdate__(self, date):
 566         try:
 567             return formatdate(date, localtime=1)
 568         except TypeError:
 569             log('Could not convert "%s" to a string date.', date)
 570             return None
 571
 572     def channel_foldername_exists(self, foldername):
 573         """
 574         Returns True if a foldername for a channel exists.
 575         False otherwise.
 576         """
 577         return self.__get__("SELECT id FROM channels WHERE foldername = ?", (foldername,)) is not None
 578
 579     def remove_foldername_if_deleted_channel(self, foldername):
 580         cur = self.cursor(lock=True)
 581         self.log('Setting foldername=NULL for folder "%s"', foldername)
 582         cur.execute('UPDATE channels SET foldername=NULL ' + \
 583                     'WHERE foldername=? AND deleted=1', (foldername,))
 584         cur.close()
 585         self.lock.release()
 586
 587     def episode_filename_exists(self, filename):
 588         """
 589         Returns True if a filename for an episode exists.
 590         False otherwise.
 591         """
 592         return self.__get__("SELECT id FROM episodes WHERE filename = ?", (filename,)) is not None
 593
 594     def find_channel_id(self, url):
 595         """
 596         Looks up the channel id in the map (which lists all undeleted
 597         channels), then tries to look it up in the database, including
 598         deleted channels.
 599         """
 600         if url in self.channel_map.keys():
 601             return self.channel_map[url]
 602         else:
 603             self.log("find_channel_id(%s)", url)
 604             return self.__get__("SELECT id FROM channels WHERE url = ?", (url, ))
 605
 606     def force_last_new(self, channel):
 607         old = self.__get__("""SELECT COUNT(*) FROM episodes WHERE channel_id = ?
 608             AND state IN (?, ?)""", (channel.id, gpodder.STATE_DOWNLOADED,
 609             gpodder.STATE_DELETED))
 610
 611         cur = self.cursor(lock=True)
 612
 613         self.log("force_last_new((%d)%s)", channel.id, channel.url)
 614
 615         if old > 0:
 616             cur.execute("""
 617                 UPDATE episodes SET played = 1 WHERE channel_id = ?
 618                 AND played = 0 AND pubDate < (SELECT MAX(pubDate)
 619                 FROM episodes WHERE channel_id = ? AND state IN (?, ?))""",
 620                 (channel.id, channel.id, gpodder.STATE_DOWNLOADED,
 621                 gpodder.STATE_DELETED, ))
 622         else:
 623             cur.execute("""
 624                 UPDATE episodes SET played = 1 WHERE channel_id = ?
 625                 AND pubDate <> (SELECT MAX(pubDate) FROM episodes
 626                 WHERE channel_id = ?)""", (channel.id, channel.id, ))
 627
 628         cur.close()
 629         self.lock.release()
 630
 631     def upgrade_table(self, table_name, fields):
 632         """
 633         Creates a table or adds fields to it.
 634         """
 635         cur = self.cursor(lock=True)
 636
 637         cur.execute("PRAGMA table_info(%s)" % table_name)
 638         available = cur.fetchall()
 639
 640         if not len(available):
 641             log('Creating table %s', table_name, sender=self)
 642             sql = "CREATE TABLE %s (%s)" % (table_name, ", ".join([a+" "+b for (a,b) in fields]))
 643             cur.execute(sql)
 644         else:
 645             available = [row[1] for row in available]
 646
 647             for field_name, field_type in fields:
 648                 if field_name not in available:
 649                     log('Adding column %s to %s (%s)', table_name, field_name, field_type, sender=self)
 650                     cur.execute("ALTER TABLE %s ADD COLUMN %s %s" % (table_name, field_name, field_type))
 651
 652         self.lock.release()
 653
 654     def delete_empty_episodes(self, channel_id):
 655         """
 656         Deletes episodes which haven't been downloaded.
 657         Currently used when a channel URL is changed.
 658         """
 659         cur = self.cursor(lock=True)
 660         log('Deleting old episodes from channel #%d' % channel_id)
 661         cur.execute("DELETE FROM episodes WHERE channel_id = ? AND state != ?", (channel_id, gpodder.STATE_DOWNLOADED, ))
 662         self.lock.release()
 663
 664     def delete_episode_by_guid(self, guid, channel_id):
 665         """
 666         Deletes episodes that have a specific GUID for
 667         a given channel. Used after feed updates for
 668         episodes that have disappeared from the feed.
 669         """
 670         cur = self.cursor(lock=True)
 671         cur.execute('DELETE FROM episodes WHERE channel_id = ? AND guid = ?', \
 672                 (channel_id, guid))
 673         self.lock.release()
 674