1 # -*- coding: utf-8 -*-
3 # gPodder - A media aggregator and podcast client
4 # Copyright (c) 2005-2010 Thomas Perl and the gPodder Team
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.
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.
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/>.
19 # dbsqlite.py -- SQLite interface
20 # Justin Forest <justin.forest@gmail.com> 2008-06-13
30 from sqlite3
import dbapi2
as sqlite
31 from sqlite3
import OperationalError
34 from pysqlite2
import dbapi2
as sqlite
35 from pysqlite2
.dbapi2
import OperationalError
39 # TODO: show a message box
41 print >>sys
.stderr
, 'Please install pysqlite2 or Python 2.5.'
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
52 class Database(object):
53 UNICODE_TRANSLATE
= {ord(u
'ö'): u
'o', ord(u
'ä'): u
'a', ord(u
'ü'): u
'u'}
55 def __init__(self
, filename
):
56 self
.database_file
= filename
64 cur
= self
.cursor(lock
=True)
65 log('Optimizing database for faster startup.', sender
=self
)
73 def log(self
, message
, *args
, **kwargs
):
76 message
= message
% args
77 log('%s', message
, sender
=self
)
79 log('Exception in log(): %s: %s', e
, message
, sender
=self
)
81 def purge(self
, max_episodes
, channel_id
):
83 Deletes old episodes. Should be called
84 before adding new episodes to a channel.
86 cur
= self
.cursor(lock
=True)
88 self
.log("purge(%s)", channel_id
)
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
))
101 def db_sort_cmp(self
, a
, b
):
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)
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
)
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())
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
()
132 def cursor(self
, lock
=False):
135 return self
.db
.cursor()
142 except ProgrammingError
, e
:
143 log('Error commiting changes: %s', e
, sender
=self
, traceback
=True)
146 def __check_schema(self
):
148 Creates all necessary tables and indexes that don't exist.
150 self
.log('Setting up tables and views')
152 cur
= self
.cursor(lock
=True)
154 self
.upgrade_table("channels", (
155 ("id", "INTEGER PRIMARY KEY"),
158 ("override_title", "TEXT"),
160 ("description", "TEXT"),
162 ("pubDate", "INTEGER"),
163 ("sync_to_devices", "INTEGER"),
164 ("device_playlist_name", "TEXT"),
165 ("username", "TEXT"),
166 ("password", "TEXT"),
167 ("last_modified", "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"),
178 self
.upgrade_table("episodes", (
179 ("id", "INTEGER PRIMARY KEY"),
180 ("channel_id", "INTEGER"),
183 ("length", "INTEGER"),
184 ("mimetype", "TEXT"),
186 ("description", "TEXT"),
188 ("pubDate", "INTEGER"),
189 ("state", "INTEGER"),
190 ("played", "INTEGER"),
191 ("locked", "INTEGER"),
192 ("filename", "TEXT"),
193 ("auto_filename", "INTEGER"),
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)""")
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)""")
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
)
214 # Make sure deleted episodes are played, to simplify querying statistics.
216 cur
.execute("UPDATE episodes SET played = 1 WHERE state = ?", (gpodder
.STATE_DELETED
, ))
217 except OperationalError
:
223 def get_channel_count(self
, id):
224 """Given a channel ID, returns the statistics for it
226 Returns a tuple (total, deleted, new, downloaded, unplayed)
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
)
235 def get_total_count(self
):
236 """Get statistics for all non-deleted channels
238 Returns a tuple (total, deleted, new, downloaded, unplayed)
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
)
247 def load_channels(self
, factory
=None, url
=None):
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.
254 self
.log("load_channels()")
256 cur
= self
.cursor(lock
=True)
268 device_playlist_name,
282 (deleted IS NULL OR deleted = 0)
284 title COLLATE UNICODE
290 for row
in cur
.fetchall():
295 'override_title': row
[3],
297 'description': row
[5],
299 'pubDate': self
.__formatdate
__(row
[7]),
300 'sync_to_devices': row
[8],
301 'device_playlist_name': row
[9],
304 'last_modified': row
[12],
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],
315 channel
['count_downloaded'] = stats
[row
[0]][0]
316 channel
['count_new'] = stats
[row
[0]][1]
317 channel
['count_unplayed'] = stats
[row
[0]][2]
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']
324 if url
is None or url
== channel
['url']:
326 result
.append(channel
)
328 result
.append(factory(channel
, self
))
336 cur
= self
.cursor(lock
=True)
339 SELECT c.id, d.count, n.count, u.count
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
349 for row
in cur
.fetchall():
350 data
[row
[0]] = (row
[1] or 0, row
[2] or 0, row
[3] or 0)
357 def save_channel(self
, c
):
359 c
.id = self
.find_channel_id(c
.url
)
361 cur
= self
.cursor(lock
=True)
362 self
.log("save_channel((%s)%s)", c
.id or "new", c
.url
)
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
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, ))
373 def delete_channel(self
, channel
, purge
=False):
374 if channel
.id is None:
375 channel
.id = self
.find_channel_id(channel
.url
)
377 cur
= self
.cursor(lock
=True)
378 self
.log("delete_channel((%d)%s), purge=%d", channel
.id, channel
.url
, 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
]
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
))
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"
398 sql
= "%s %s" % (sql
, where
)
403 cur
= self
.cursor(lock
=True)
404 cur
.execute(sql
, params
)
407 for row
in cur
.fetchall():
414 'description': row
[5],
419 'is_locked': row
[10],
421 'auto_filename': row
[12],
424 if episode
['state'] is None:
425 episode
['state'] = gpodder
.STATE_NORMAL
427 result
.append(episode
)
429 result
.append(factory(episode
, self
))
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
)
439 self
.log("load_episodes((%d)%s)", channel
.id, channel
.url
)
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
, ))
449 return self
.__read
_episodes
(factory
= factory
, where
= " WHERE channel_id = ? AND state = ? ORDER BY pubDate DESC LIMIT ?", params
= (channel
.id, state
, limit
, ))
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))
459 def save_episode(self
, e
):
461 log('Refusing to save an episode without guid: %s', e
)
466 self
.log("save_episode((%s)%s)", e
.id, e
.guid
)
470 channel_id
= self
.find_channel_id(e
.channel
.url
)
473 e
.id = self
.__get
__("SELECT id FROM episodes WHERE guid = ?", (e
.guid
, ))
474 self
.log("save_episode() -- looking up id")
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
, ))
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, ))
482 log('save_episode() failed: %s', e
, sender
=self
)
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
, ))
491 self
.log("mark_episode(%s, state=%s, played=%s, locked=%s)", url
, state
, is_played
, is_locked
)
494 ( cur_state
, cur_played
, cur_locked
) = cur
.fetchone()
496 # This only happens when we try to mark an unknown episode,
497 # which is typical for database upgrade, so we just ignore it.
504 cur_played
= not cur_played
506 cur_locked
= not cur_locked
508 if state
is not None:
510 if is_played
is not None:
511 cur_played
= is_played
512 if is_locked
is not None:
513 cur_locked
= is_locked
518 cur
.execute("UPDATE episodes SET state = ?, played = ?, locked = ? WHERE url = ?", (cur_state
, cur_played
, cur_locked
, url
, ))
523 def update_channel_lock(self
, channel
):
524 log("update_channel_lock(%s, locked=%s)", channel
.url
, channel
.channel_is_locked
, sender
=self
)
526 cur
= self
.cursor(lock
=True)
527 cur
.execute("UPDATE channels SET channel_is_locked = ? WHERE url = ?", (channel
.channel_is_locked
, channel
.url
, ))
532 def __get__(self
, sql
, params
=None):
534 Returns the first cell of a query result, useful for COUNT()s.
536 cur
= self
.cursor(lock
=True)
538 self
.log("__get__(): %s", sql
)
543 cur
.execute(sql
, params
)
554 def __mktime__(self
, date
):
555 if isinstance(date
, float) or isinstance(date
, int):
557 if date
is None or '' == date
:
560 return mktime_tz(parsedate_tz(date
))
562 log('Could not convert "%s" to a unix timestamp.', date
)
565 def __formatdate__(self
, date
):
567 return formatdate(date
, localtime
=1)
569 log('Could not convert "%s" to a string date.', date
)
572 def channel_foldername_exists(self
, foldername
):
574 Returns True if a foldername for a channel exists.
577 return self
.__get
__("SELECT id FROM channels WHERE foldername = ?", (foldername
,)) is not None
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
,))
587 def episode_filename_exists(self
, filename
):
589 Returns True if a filename for an episode exists.
592 return self
.__get
__("SELECT id FROM episodes WHERE filename = ?", (filename
,)) is not None
594 def find_channel_id(self
, url
):
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
600 if url
in self
.channel_map
.keys():
601 return self
.channel_map
[url
]
603 self
.log("find_channel_id(%s)", url
)
604 return self
.__get
__("SELECT id FROM channels WHERE url = ?", (url
, ))
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
))
611 cur
= self
.cursor(lock
=True)
613 self
.log("force_last_new((%d)%s)", channel
.id, channel
.url
)
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
, ))
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, ))
631 def upgrade_table(self
, table_name
, fields
):
633 Creates a table or adds fields to it.
635 cur
= self
.cursor(lock
=True)
637 cur
.execute("PRAGMA table_info(%s)" % table_name
)
638 available
= cur
.fetchall()
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
]))
645 available
= [row
[1] for row
in available
]
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
))
654 def delete_empty_episodes(self
, channel_id
):
656 Deletes episodes which haven't been downloaded.
657 Currently used when a channel URL is changed.
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
, ))
664 def delete_episode_by_guid(self
, guid
, channel_id
):
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.
670 cur
= self
.cursor(lock
=True)
671 cur
.execute('DELETE FROM episodes WHERE channel_id = ? AND guid = ?', \