src/gpodder/dbsqlite.py

   1 # -*- coding: utf-8 -*-
   2 #
   3 # gPodder - A media aggregator and podcast client
   4 # Copyright (c) 2005-2018 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
  20 #
  21 # dbsqlite.py -- SQLite persistence layer for gPodder
  22 #
  23 # 2008-06-13 Justin Forest <justin.forest@gmail.com>
  24 # 2010-04-24 Thomas Perl <thp@gpodder.org>
  25 #
  26
  27 import logging
  28 import threading
  29 from sqlite3 import dbapi2 as sqlite
  30
  31 import gpodder
  32 from gpodder import schema, util
  33
  34 _ = gpodder.gettext
  35
  36 logger = logging.getLogger(__name__)
  37
  38
  39 class Database(object):
  40     TABLE_PODCAST = 'podcast'
  41     TABLE_EPISODE = 'episode'
  42
  43     def __init__(self, filename):
  44         self.database_file = filename
  45         self._db = None
  46         self.lock = threading.RLock()
  47
  48     def close(self):
  49         self.commit()
  50
  51         with self.lock:
  52             self.db.isolation_level = None
  53             self.db.execute('VACUUM')
  54             self.db.isolation_level = ''
  55
  56         self._db.close()
  57         self._db = None
  58
  59     def purge(self, max_episodes, podcast_id):
  60         """
  61         Deletes old episodes.  Should be called
  62         before adding new episodes to a podcast.
  63         """
  64         if max_episodes == 0:
  65             return
  66
  67         with self.lock:
  68             cur = self.cursor()
  69
  70             logger.debug('Purge requested for podcast %d', podcast_id)
  71             sql = """
  72                 DELETE FROM %s
  73                 WHERE podcast_id = ?
  74                 AND state <> ?
  75                 AND id NOT IN
  76                 (SELECT id FROM %s WHERE podcast_id = ?
  77                 ORDER BY published DESC LIMIT ?)""" % (self.TABLE_EPISODE, self.TABLE_EPISODE)
  78             cur.execute(sql, (podcast_id, gpodder.STATE_DOWNLOADED, podcast_id, max_episodes))
  79
  80             cur.close()
  81
  82     @property
  83     def db(self):
  84         if self._db is None:
  85             self._db = sqlite.connect(self.database_file, check_same_thread=False)
  86
  87             # Check schema version, upgrade if necessary
  88             schema.upgrade(self._db, self.database_file)
  89
  90             # Sanity checks for the data in the database
  91             schema.check_data(self)
  92
  93             logger.debug('Database opened.')
  94         return self._db
  95
  96     def cursor(self):
  97         return self.db.cursor()
  98
  99     def commit(self):
 100         with self.lock:
 101             try:
 102                 logger.debug('Commit.')
 103                 self.db.commit()
 104             except Exception as e:
 105                 logger.error('Cannot commit: %s', e, exc_info=True)
 106
 107     def get_content_types(self, id):
 108         """Given a podcast ID, returns the content types"""
 109         with self.lock:
 110             cur = self.cursor()
 111             cur.execute('SELECT mime_type FROM %s WHERE podcast_id = ?' % self.TABLE_EPISODE, (id,))
 112             for (mime_type,) in cur:
 113                 yield mime_type
 114             cur.close()
 115
 116     def get_podcast_statistics(self, podcast_id=None):
 117         """Given a podcast ID, returns the statistics for it
 118
 119         If the podcast_id is omitted (using the default value), the
 120         statistics will be calculated over all podcasts.
 121
 122         Returns a tuple (total, deleted, new, downloaded, unplayed)
 123         """
 124         total, deleted, new, downloaded, unplayed = 0, 0, 0, 0, 0
 125
 126         with self.lock:
 127             cur = self.cursor()
 128             if podcast_id is not None:
 129                 cur.execute('SELECT COUNT(*), state, is_new FROM %s '
 130                             'WHERE podcast_id = ? GROUP BY state, is_new'
 131                             % self.TABLE_EPISODE, (podcast_id,))
 132             else:
 133                 cur.execute('SELECT COUNT(*), state, is_new FROM %s '
 134                             'GROUP BY state, is_new' % self.TABLE_EPISODE)
 135             for count, state, is_new in cur:
 136                 total += count
 137                 if state == gpodder.STATE_DELETED:
 138                     deleted += count
 139                 elif state == gpodder.STATE_NORMAL and is_new:
 140                     new += count
 141                 elif state == gpodder.STATE_DOWNLOADED:
 142                     downloaded += count
 143                     if is_new:
 144                         unplayed += count
 145
 146             cur.close()
 147
 148         return (total, deleted, new, downloaded, unplayed)
 149
 150     def load_podcasts(self, factory):
 151         logger.info('Loading podcasts')
 152
 153         sql = 'SELECT * FROM %s' % self.TABLE_PODCAST
 154
 155         with self.lock:
 156             cur = self.cursor()
 157             cur.execute(sql)
 158
 159             keys = [desc[0] for desc in cur.description]
 160             result = [factory(dict(list(zip(keys, row))), self) for row in cur]
 161             cur.close()
 162
 163         return result
 164
 165     def load_episodes(self, podcast, factory):
 166         assert podcast.id
 167
 168         logger.info('Loading episodes for podcast %d', podcast.id)
 169
 170         sql = 'SELECT * FROM %s WHERE podcast_id = ? ORDER BY published DESC' % self.TABLE_EPISODE
 171         args = (podcast.id,)
 172
 173         with self.lock:
 174             cur = self.cursor()
 175             cur.execute(sql, args)
 176
 177             keys = [desc[0] for desc in cur.description]
 178             result = [factory(dict(list(zip(keys, row)))) for row in cur]
 179             cur.close()
 180
 181         return result
 182
 183     def delete_podcast(self, podcast):
 184         assert podcast.id
 185
 186         with self.lock:
 187             cur = self.cursor()
 188             logger.debug('delete_podcast: %d (%s)', podcast.id, podcast.url)
 189
 190             cur.execute("DELETE FROM %s WHERE id = ?" % self.TABLE_PODCAST, (podcast.id, ))
 191             cur.execute("DELETE FROM %s WHERE podcast_id = ?" % self.TABLE_EPISODE, (podcast.id, ))
 192
 193             cur.close()
 194             self.db.commit()
 195
 196     def save_podcast(self, podcast):
 197         self._save_object(podcast, self.TABLE_PODCAST, schema.PodcastColumns)
 198
 199     def save_episode(self, episode):
 200         self._save_object(episode, self.TABLE_EPISODE, schema.EpisodeColumns)
 201
 202     def _save_object(self, o, table, columns):
 203         with self.lock:
 204             try:
 205                 cur = self.cursor()
 206                 values = [util.convert_bytes(getattr(o, name))
 207                         for name in columns]
 208
 209                 if o.id is None:
 210                     qmarks = ', '.join('?' * len(columns))
 211                     sql = 'INSERT INTO %s (%s) VALUES (%s)' % (table, ', '.join(columns), qmarks)
 212                     cur.execute(sql, values)
 213                     o.id = cur.lastrowid
 214                 else:
 215                     qmarks = ', '.join('%s = ?' % name for name in columns)
 216                     values.append(o.id)
 217                     sql = 'UPDATE %s SET %s WHERE id = ?' % (table, qmarks)
 218                     cur.execute(sql, values)
 219             except Exception as e:
 220                 logger.error('Cannot save %s: %s', o, e, exc_info=True)
 221
 222             cur.close()
 223
 224     def get(self, sql, params=None):
 225         """
 226         Returns the first cell of a query result, useful for COUNT()s.
 227         """
 228         with self.lock:
 229             cur = self.cursor()
 230
 231             if params is None:
 232                 cur.execute(sql)
 233             else:
 234                 cur.execute(sql, params)
 235
 236             row = cur.fetchone()
 237             cur.close()
 238
 239         if row is None:
 240             return None
 241         else:
 242             return row[0]
 243
 244     def podcast_download_folder_exists(self, foldername):
 245         """
 246         Returns True if a foldername for a channel exists.
 247         False otherwise.
 248         """
 249         foldername = util.convert_bytes(foldername)
 250
 251         return self.get("SELECT id FROM %s WHERE download_folder = ?" %
 252                 self.TABLE_PODCAST, (foldername,)) is not None
 253
 254     def episode_filename_exists(self, podcast_id, filename):
 255         """
 256         Returns True if a filename for an episode exists.
 257         False otherwise.
 258         """
 259         filename = util.convert_bytes(filename)
 260
 261         return self.get("SELECT id FROM %s WHERE podcast_id = ? AND download_filename = ?" %
 262                 self.TABLE_EPISODE, (podcast_id, filename,)) is not None
 263
 264     def get_last_published(self, podcast):
 265         """
 266         Look up the most recent publish date of a podcast.
 267         """
 268         return self.get('SELECT MAX(published) FROM %s WHERE podcast_id = ?' % self.TABLE_EPISODE, (podcast.id,))
 269
 270     def delete_episode_by_guid(self, guid, podcast_id):
 271         """
 272         Deletes episodes that have a specific GUID for
 273         a given channel. Used after feed updates for
 274         episodes that have disappeared from the feed.
 275         """
 276         guid = util.convert_bytes(guid)
 277
 278         with self.lock:
 279             cur = self.cursor()
 280             cur.execute('DELETE FROM %s WHERE podcast_id = ? AND guid = ?' %
 281                     self.TABLE_EPISODE, (podcast_id, guid))