54
54
class Database(object):
55
55
UNICODE_TRANSLATE = {ord(u'ö'): u'o', ord(u'ä'): u'a', ord(u'ü'): u'u'}
57
# Column names and types for the channels table
57
# Column names, types, required and default values for the channels table
58
58
TABLE_CHANNELS = "channels"
59
59
SCHEMA_CHANNELS = (
60
('id', 'INTEGER PRIMARY KEY'),
61
('url', 'TEXT'), # Feed (RSS/Atom) URL of the podcast
62
('title', 'TEXT'), # Podcast name
63
('override_title', 'TEXT'), # Podcast name if user-defined
64
('link', 'TEXT'), # Website URL for the podcast
65
('description', 'TEXT'), # Description of podcast contents
66
('image', 'TEXT'), # URL to cover art for the image
67
('pubDate', 'INTEGER'), # Date and time of last feed publication
68
('sync_to_devices', 'INTEGER'), # 1 if syncing to devices is enabled, 0 otherwise
69
('device_playlist_name', 'TEXT'), # Name of the playlist on the device for syncing
70
('username', 'TEXT'), # Username for HTTP authentication (feed update + downloads)
71
('password', 'TEXT'), # Password for HTTP authentication (feed update + downloads)
72
('last_modified', 'TEXT'), # Last-modified HTTP header from last update
73
('etag', 'TEXT'), # ETag HTTP header from last update
74
('channel_is_locked', 'INTEGER'), # 1 if deletion is prevented, 0 otherwise
75
('foldername', 'TEXT'), # Folder name (basename) to put downloaded episodes
76
('auto_foldername', 'INTEGER'), # 1 if the foldername was auto-generated, 0 otherwise
77
('release_expected', 'INTEGER'), # Statistic value for when a new release is expected
78
('release_deviation', 'INTEGER'), # Deviation of the release cycle differences
79
('updated_timestamp', 'INTEGER'), # Timestamp of the last feed update
60
('id', 'INTEGER PRIMARY KEY', True, '-1'),
61
('url', 'TEXT', True, "''"), # Feed (RSS/Atom) URL of the podcast
62
('title', 'TEXT', True, "''"), # Podcast name
63
('override_title', 'TEXT', True, "''"), # Podcast name if user-defined
64
('link', 'TEXT', True, "''"), # Website URL for the podcast
65
('description', 'TEXT', False, None), # Description of podcast contents
66
('image', 'TEXT', False, None), # URL to cover art for the image
67
('pubDate', 'INTEGER', True, '0'), # Date and time of last feed publication
68
('sync_to_devices', 'INTEGER', True, '1'), # 1 if syncing to devices is enabled, 0 otherwise
69
('device_playlist_name', 'TEXT', True, "'gPodder'"), # Name of the playlist on the device for syncing
70
('username', 'TEXT', True, "''"), # Username for HTTP authentication (feed update + downloads)
71
('password', 'TEXT', True, "''"), # Password for HTTP authentication (feed update + downloads)
72
('last_modified', 'TEXT', False, None), # Last-modified HTTP header from last update
73
('etag', 'TEXT', False, None), # ETag HTTP header from last update
74
('channel_is_locked', 'INTEGER', True, '0'), # 1 if deletion is prevented, 0 otherwise
75
('foldername', 'TEXT', True, "''"), # Folder name (basename) to put downloaded episodes
76
('auto_foldername', 'INTEGER', True, '1'), # 1 if the foldername was auto-generated, 0 otherwise
77
('release_expected', 'INTEGER', True, '0'), # Statistic value for when a new release is expected
78
('release_deviation', 'INTEGER', True, '0'), # Deviation of the release cycle differences
79
('updated_timestamp', 'INTEGER', True, '0'), # Timestamp of the last feed update
80
('feed_update_enabled', 'INTEGER', True, '1'), # 0 to skip this feed when checking for new episodes
82
83
('foldername', 'UNIQUE INDEX'),
88
89
# Column names and types for the episodes table
89
90
TABLE_EPISODES = 'episodes'
90
91
SCHEMA_EPISODES = (
91
('id', 'INTEGER PRIMARY KEY'),
92
('channel_id', 'INTEGER'), # Foreign key: ID of the podcast of this episode
93
('url', 'TEXT'), # Download URL of the media file
94
('title', 'TEXT'), # Episode title
95
('length', 'INTEGER'), # File length of the media file in bytes
96
('mimetype', 'TEXT'), # Mime type of the media file
97
('guid', 'TEXT'), # GUID of the episode item
98
('description', 'TEXT'), # Longer text description
99
('link', 'TEXT'), # Website URL for the episode
100
('pubDate', 'INTEGER'), # Date and time of publication
101
('state', 'INTEGER'), # Download state (see gpodder.STATE_* constants)
102
('played', 'INTEGER'), # 1 if it's new or played, 0 otherwise
103
('locked', 'INTEGER'), # 1 if deletion is prevented, 0 otherwise
104
('filename', 'TEXT'), # Filename for the downloaded file (or NULL)
105
('auto_filename', 'INTEGER'), # 1 if the filename was auto-generated, 0 otherwise
106
('total_time', 'INTEGER'), # Length in seconds
107
('current_position', 'INTEGER'), # Current playback position
108
('current_position_updated', 'INTEGER'), # Set to NOW when updating current_position
92
('id', 'INTEGER PRIMARY KEY', True, '-1'),
93
('channel_id', 'INTEGER', True, '-1'), # Foreign key: ID of the podcast of this episode
94
('url', 'TEXT', True, "''"), # Download URL of the media file
95
('title', 'TEXT', True, "''"), # Episode title
96
('length', 'INTEGER', True, '0'), # File length of the media file in bytes
97
('mimetype', 'TEXT', True, "''"), # Mime type of the media file
98
('guid', 'TEXT', True, "''"), # GUID of the episode item
99
('description', 'TEXT', True, "''"), # Longer text description
100
('link', 'TEXT', True, "''"), # Website URL for the episode
101
('pubDate', 'INTEGER', True, '0'), # Date and time of publication
102
('state', 'INTEGER', True, '0'), # Download state (see gpodder.STATE_* constants)
103
('played', 'INTEGER', True, '1'), # 1 if it's new or played, 0 otherwise
104
('locked', 'INTEGER', True, '0'), # 1 if deletion is prevented, 0 otherwise
105
('filename', 'TEXT', False, None), # Filename for the downloaded file (or NULL)
106
('auto_filename', 'INTEGER', True, '0'), # 1 if the filename was auto-generated, 0 otherwise
107
('total_time', 'INTEGER', True, '0'), # Length in seconds
108
('current_position', 'INTEGER', True, '0'), # Current playback position
109
('current_position_updated', 'INTEGER', True, '0'), # Set to NOW when updating current_position
110
111
INDEX_EPISODES = (
111
112
('guid', 'UNIQUE INDEX'),
607
608
self.lock.release()
610
def recreate_table(self, cur, table_name, fields, index_list):
611
log('Rename table %s', table_name, sender=self)
612
new_table_name = table_name + "_save"
613
cur.execute("ALTER TABLE %s RENAME TO %s" % (table_name, new_table_name))
614
#log("ALTER TABLE %s RENAME TO %s" % (table_name, new_table_name))
616
log('Delete existing indices', sender=self)
617
for column, typ in index_list:
618
cur.execute('DROP INDEX IF EXISTS idx_%s' % (column))
620
self.create_table(cur, table_name, fields)
622
log('Correct NULL values in the existing data', sender=self)
623
columns = set((column, default) for column, typ, required, default in fields if required)
624
for column, default in columns:
625
cur.execute('UPDATE %s SET %s = %s where %s IS NULL' % (new_table_name, column, default, column))
627
log('Copy data from table %s to table %s' % (new_table_name, table_name), sender=self)
628
columns = ', '.join(f[0] for f in fields)
629
cur.execute("INSERT INTO %(tab)s (%(col)s) SELECT %(col)s FROM %(new_tab)s" %
630
{'tab': table_name, 'col': columns, 'new_tab': new_table_name})
632
def create_table(self, cur, table_name, fields):
633
log('Creating table %s', table_name, sender=self)
635
for column, typ, required, default in fields:
637
columns += '\n %s %s NOT NULL DEFAULT %s,' % (column, typ, default)
639
columns += '\n %s %s,' % (column, typ)
640
columns = columns.rstrip(',')
641
sql = "CREATE TABLE %s (%s)" % (table_name, columns)
609
644
def upgrade_table(self, table_name, fields, index_list):
611
646
Creates a table or adds fields to it.
616
651
available = cur.fetchall()
618
653
if not available:
619
log('Creating table %s', table_name, sender=self)
620
columns = ', '.join(' '.join(f) for f in fields)
621
sql = "CREATE TABLE %s (%s)" % (table_name, columns)
654
self.create_table(cur, table_name, fields)
624
657
# Table info columns, as returned by SQLite
625
ID, NAME, TYPE, NULL, DEFAULT = range(5)
626
existing = set(column[NAME] for column in available)
628
for field_name, field_type in fields:
629
if field_name not in existing:
630
log('Adding column: %s.%s (%s)', table_name, field_name, field_type, sender=self)
631
cur.execute("ALTER TABLE %s ADD COLUMN %s %s" % (table_name, field_name, field_type))
658
ID, NAME, TYPE, NOTNULL, DEFAULT = range(5)
659
exists_notnull_column = any(bool(column[NOTNULL]) for column in available)
661
if not exists_notnull_column:
662
self.recreate_table(cur, table_name, fields, index_list)
665
existing = set(column[NAME] for column in available)
666
for field_name, field_type, field_required, field_default in fields:
667
if field_name not in existing:
668
log('Adding column: %s.%s (%s)', table_name, field_name, field_type, sender=self)
669
sql = "ALTER TABLE %s ADD COLUMN %s %s" % (table_name, field_name, field_type)
671
sql += " NOT NULL DEFAULT %s" % (field_default)
633
674
for column, typ in index_list:
634
675
cur.execute('CREATE %s IF NOT EXISTS idx_%s ON %s (%s)' % (typ, column, table_name, column))