def create_table(self):
DATABASE.execute(
'CREATE TABLE IF NOT EXISTS {0!s} (id INTEGER PRIMARY KEY AUTOINCREMENT, '
'addon_id, url, content_type TEXT DEFAULT "video", label TEXT DEFAULT "Unknown", '
'thumbnail TEXT DEFAULT "", CONSTRAINT unq UNIQUE (addon_id, url, content_type) )'
.format(self.TABLE), '')
DATABASE.execute(
'''CREATE TRIGGER IF NOT EXISTS default_label_url
AFTER INSERT ON {0!s}
WHEN new.label="Unknown"
BEGIN
UPDATE {0!s} SET label=new.url WHERE id=new.id;
END
;
'''.format(self.TABLE), '')
DATABASE.execute(
'ALTER TABLE {0!s} ADD COLUMN thumbnail TEXT DEFAULT ""'.format(
self.TABLE),
'',
suppress=True)
exists = DATABASE.fetch(
'SELECT name FROM sqlite_master WHERE type="table" AND name=?',
(self.OLD_TABLE, ))
if exists:
DATABASE.execute(
'INSERT INTO {0!s} (addon_id, url) SELECT addon_id, url FROM {1!s}'
.format(self.TABLE, self.OLD_TABLE), '')
DATABASE.execute(
'ALTER TABLE {0!s} RENAME TO {1!s}'.format(
self.OLD_TABLE, '{0!s}_bak'.format(self.OLD_TABLE)), '')
def add(self, url, content_type, label=None, thumb=''):
if label is None:
label = url
label = unquote(label)
thumb = unquote(thumb)
execute = 'INSERT INTO {0!s} (addon_id, url, content_type, label, thumbnail) VALUES (?, ?, ?, ?, ?)'.format(
self.TABLE)
inserted = DATABASE.execute(
execute, (self.ID, str(url), str(content_type), label, thumb))
if inserted == 1:
execute = 'SELECT COUNT(*) FROM {0!s} WHERE addon_id=?'.format(
self.TABLE)
result = int(DATABASE.fetch(execute, (self.ID, ))[0][0])
if result > self.size_limit():
execute = 'DELETE FROM {0!s} WHERE ROWID = (SELECT MIN(ROWID) FROM {0!s}) AND addon_id=?'.format(
self.TABLE)
result, rowcount = DATABASE.execute_w_rowcount(
execute, (self.ID, ))
if rowcount < 1:
execute = 'DELETE * FROM {0!s} WHERE addon_id=?'.format(
self.TABLE)
result, rowcount = DATABASE.execute_w_rowcount(
execute, (self.ID, ))
if rowcount < 1:
result = DATABASE.execute('DROP TABLE {0!s}'.format(
self.TABLE))
self.vacuum()
self.create_table()
if rowcount > 0:
self.vacuum()
def get(self, include_ids=False, row_id=None):
if row_id is None:
execute = 'SELECT * FROM {0!s} WHERE addon_id=? ORDER BY id DESC'.format(
self.TABLE)
selected = DATABASE.fetch(execute, (self.ID, ))
else:
execute = 'SELECT * FROM {0!s} WHERE id=? AND addon_id=?'.format(
self.TABLE)
selected = DATABASE.fetch(execute, (row_id, self.ID))
results = []
if selected:
for id_key, addon_id, query, content_type, label, thumbnail in selected:
if not include_ids:
results.extend([(unquote(query), content_type, label,
unquote(thumbnail))])
else:
results.extend([(id_key, unquote(query), content_type,
label, unquote(thumbnail))])
return results
else:
return []