src/gpodder/dbsqlite.py

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