# -*- coding: utf-8 -*-
#
# gPodder - A media aggregator and podcast client
# Copyright (c) 2005-2018 The gPodder Team
#
# gPodder is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 3 of the License, or
# (at your option) any later version.
#
# gPodder is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see .
#
#
# dbsqlite.py -- SQLite persistence layer for gPodder
#
# 2008-06-13 Justin Forest
# 2010-04-24 Thomas Perl
#
import logging
import threading
from sqlite3 import dbapi2 as sqlite
import gpodder
from gpodder import schema, util
_ = gpodder.gettext
logger = logging.getLogger(__name__)
class Database(object):
TABLE_PODCAST = 'podcast'
TABLE_EPISODE = 'episode'
def __init__(self, filename):
self.database_file = filename
self._db = None
self.lock = threading.RLock()
def close(self):
self.commit()
with self.lock:
self.db.isolation_level = None
self.db.execute('VACUUM')
self.db.isolation_level = ''
self._db.close()
self._db = None
def purge(self, max_episodes, podcast_id):
"""
Deletes old episodes. Should be called
before adding new episodes to a podcast.
"""
if max_episodes == 0:
return
with self.lock:
cur = self.cursor()
logger.debug('Purge requested for podcast %d', podcast_id)
sql = """
DELETE FROM %s
WHERE podcast_id = ?
AND state <> ?
AND id NOT IN
(SELECT id FROM %s WHERE podcast_id = ?
ORDER BY published DESC LIMIT ?)""" % (self.TABLE_EPISODE, self.TABLE_EPISODE)
cur.execute(sql, (podcast_id, gpodder.STATE_DOWNLOADED, podcast_id, max_episodes))
cur.close()
@property
def db(self):
if self._db is None:
self._db = sqlite.connect(self.database_file, check_same_thread=False)
# Check schema version, upgrade if necessary
schema.upgrade(self._db, self.database_file)
# Sanity checks for the data in the database
schema.check_data(self)
logger.debug('Database opened.')
return self._db
def cursor(self):
return self.db.cursor()
def commit(self):
with self.lock:
try:
logger.debug('Commit.')
self.db.commit()
except Exception as e:
logger.error('Cannot commit: %s', e, exc_info=True)
def get_content_types(self, id):
"""Given a podcast ID, returns the content types"""
with self.lock:
cur = self.cursor()
cur.execute('SELECT mime_type FROM %s WHERE podcast_id = ?' % self.TABLE_EPISODE, (id,))
for (mime_type,) in cur:
yield mime_type
cur.close()
def get_podcast_statistics(self, podcast_id=None):
"""Given a podcast ID, returns the statistics for it
If the podcast_id is omitted (using the default value), the
statistics will be calculated over all podcasts.
Returns a tuple (total, deleted, new, downloaded, unplayed)
"""
total, deleted, new, downloaded, unplayed = 0, 0, 0, 0, 0
with self.lock:
cur = self.cursor()
if podcast_id is not None:
cur.execute('SELECT COUNT(*), state, is_new FROM %s '
'WHERE podcast_id = ? GROUP BY state, is_new'
% self.TABLE_EPISODE, (podcast_id,))
else:
cur.execute('SELECT COUNT(*), state, is_new FROM %s '
'GROUP BY state, is_new' % self.TABLE_EPISODE)
for count, state, is_new in cur:
total += count
if state == gpodder.STATE_DELETED:
deleted += count
elif state == gpodder.STATE_NORMAL and is_new:
new += count
elif state == gpodder.STATE_DOWNLOADED:
downloaded += count
if is_new:
unplayed += count
cur.close()
return (total, deleted, new, downloaded, unplayed)
def load_podcasts(self, factory):
logger.info('Loading podcasts')
sql = 'SELECT * FROM %s' % self.TABLE_PODCAST
with self.lock:
cur = self.cursor()
cur.execute(sql)
keys = [desc[0] for desc in cur.description]
result = [factory(dict(list(zip(keys, row))), self) for row in cur]
cur.close()
return result
def load_episodes(self, podcast, factory):
assert podcast.id
logger.info('Loading episodes for podcast %d', podcast.id)
sql = 'SELECT * FROM %s WHERE podcast_id = ? ORDER BY published DESC' % self.TABLE_EPISODE
args = (podcast.id,)
with self.lock:
cur = self.cursor()
cur.execute(sql, args)
keys = [desc[0] for desc in cur.description]
result = [factory(dict(list(zip(keys, row)))) for row in cur]
cur.close()
return result
def delete_podcast(self, podcast):
assert podcast.id
with self.lock:
cur = self.cursor()
logger.debug('delete_podcast: %d (%s)', podcast.id, podcast.url)
cur.execute("DELETE FROM %s WHERE id = ?" % self.TABLE_PODCAST, (podcast.id, ))
cur.execute("DELETE FROM %s WHERE podcast_id = ?" % self.TABLE_EPISODE, (podcast.id, ))
cur.close()
self.db.commit()
def save_podcast(self, podcast):
self._save_object(podcast, self.TABLE_PODCAST, schema.PodcastColumns)
def save_episode(self, episode):
self._save_object(episode, self.TABLE_EPISODE, schema.EpisodeColumns)
def _save_object(self, o, table, columns):
with self.lock:
try:
cur = self.cursor()
values = [util.convert_bytes(getattr(o, name))
for name in columns]
if o.id is None:
qmarks = ', '.join('?' * len(columns))
sql = 'INSERT INTO %s (%s) VALUES (%s)' % (table, ', '.join(columns), qmarks)
cur.execute(sql, values)
o.id = cur.lastrowid
else:
qmarks = ', '.join('%s = ?' % name for name in columns)
values.append(o.id)
sql = 'UPDATE %s SET %s WHERE id = ?' % (table, qmarks)
cur.execute(sql, values)
except Exception as e:
logger.error('Cannot save %s: %s', o, e, exc_info=True)
cur.close()
def get(self, sql, params=None):
"""
Returns the first cell of a query result, useful for COUNT()s.
"""
with self.lock:
cur = self.cursor()
if params is None:
cur.execute(sql)
else:
cur.execute(sql, params)
row = cur.fetchone()
cur.close()
if row is None:
return None
else:
return row[0]
def podcast_download_folder_exists(self, foldername):
"""
Returns True if a foldername for a channel exists.
False otherwise.
"""
foldername = util.convert_bytes(foldername)
return self.get("SELECT id FROM %s WHERE download_folder = ?" %
self.TABLE_PODCAST, (foldername,)) is not None
def episode_filename_exists(self, podcast_id, filename):
"""
Returns True if a filename for an episode exists.
False otherwise.
"""
filename = util.convert_bytes(filename)
return self.get("SELECT id FROM %s WHERE podcast_id = ? AND download_filename = ?" %
self.TABLE_EPISODE, (podcast_id, filename,)) is not None
def get_last_published(self, podcast):
"""
Look up the most recent publish date of a podcast.
"""
return self.get('SELECT MAX(published) FROM %s WHERE podcast_id = ?' % self.TABLE_EPISODE, (podcast.id,))
def delete_episode_by_guid(self, guid, podcast_id):
"""
Deletes episodes that have a specific GUID for
a given channel. Used after feed updates for
episodes that have disappeared from the feed.
"""
guid = util.convert_bytes(guid)
with self.lock:
cur = self.cursor()
cur.execute('DELETE FROM %s WHERE podcast_id = ? AND guid = ?' %
self.TABLE_EPISODE, (podcast_id, guid))