def execute(self):
utils.backup_database(self.connection.path, self.connection.version)
log.info(u'Adding new quality field in the tv_episodes table')
self.connection.action('DROP TABLE IF EXISTS tmp_tv_episodes;')
self.connection.action('ALTER TABLE tv_episodes RENAME TO tmp_tv_episodes;')
self.connection.action(
'CREATE TABLE IF NOT EXISTS tv_episodes '
'(episode_id INTEGER PRIMARY KEY, showid NUMERIC, indexerid INTEGER, indexer INTEGER, '
'name TEXT, season NUMERIC, episode NUMERIC, description TEXT, airdate NUMERIC, hasnfo NUMERIC, '
'hastbn NUMERIC, status NUMERIC, quality NUMERIC, location TEXT, file_size NUMERIC, release_name TEXT, '
'subtitles TEXT, subtitles_searchcount NUMERIC, subtitles_lastsearch TIMESTAMP, '
'is_proper NUMERIC, scene_season NUMERIC, scene_episode NUMERIC, absolute_number NUMERIC, '
'scene_absolute_number NUMERIC, version NUMERIC DEFAULT -1, release_group TEXT, manually_searched NUMERIC);'
)
# Re-insert old values, setting the new quality column to the invalid value of -1
self.connection.action(
'INSERT INTO tv_episodes '
'(showid, indexerid, indexer, name, season, episode, description, airdate, hasnfo, '
'hastbn, status, quality, location, file_size, release_name, subtitles, subtitles_searchcount, '
'subtitles_lastsearch, is_proper, scene_season, scene_episode, absolute_number, scene_absolute_number, '
'version, release_group, manually_searched) '
'SELECT showid, indexerid, indexer, '
'name, season, episode, description, airdate, hasnfo, '
'hastbn, status, -1 AS quality, location, file_size, release_name, '
'subtitles, subtitles_searchcount, subtitles_lastsearch, '
'is_proper, scene_season, scene_episode, absolute_number, '
'scene_absolute_number, version, release_group, manually_searched '
'FROM tmp_tv_episodes;'
)
# We have all that we need, drop the old table
for index in ['idx_sta_epi_air', 'idx_sta_epi_sta_air', 'idx_status']:
log.info(u'Dropping the index on {0}', index)
self.connection.action('DROP INDEX IF EXISTS {index};'.format(index=index))
self.connection.action('DROP TABLE IF EXISTS tmp_tv_episodes;')
log.info(u'Splitting the composite status into status and quality')
sql_results = self.connection.select('SELECT status from tv_episodes GROUP BY status;')
for episode in sql_results:
composite_status = episode['status']
status, quality = utils.split_composite_status(composite_status)
self.connection.action('UPDATE tv_episodes SET status = ?, quality = ? WHERE status = ?;',
[status, quality, composite_status])
# Update `history` table: Remove the quality value from `action`
log.info(u'Removing the quality from the action field, as this is a composite status')
sql_results = self.connection.select('SELECT action FROM history GROUP BY action;')
for item in sql_results:
composite_action = item['action']
status, quality = utils.split_composite_status(composite_action)
self.connection.action('UPDATE history SET action = ? WHERE action = ?;',
[status, composite_action])
self.inc_minor_version()
def execute(self):
utils.backup_database(self.connection.path, self.connection.version)
log.info(u'Adding new quality field in the tv_episodes table')
self.connection.action('DROP TABLE IF EXISTS tmp_tv_episodes;')
self.connection.action('ALTER TABLE tv_episodes RENAME TO tmp_tv_episodes;')
self.connection.action(
'CREATE TABLE IF NOT EXISTS tv_episodes '
'(episode_id INTEGER PRIMARY KEY, showid NUMERIC, indexerid INTEGER, indexer INTEGER, '
'name TEXT, season NUMERIC, episode NUMERIC, description TEXT, airdate NUMERIC, hasnfo NUMERIC, '
'hastbn NUMERIC, status NUMERIC, quality NUMERIC, location TEXT, file_size NUMERIC, release_name TEXT, '
'subtitles TEXT, subtitles_searchcount NUMERIC, subtitles_lastsearch TIMESTAMP, '
'is_proper NUMERIC, scene_season NUMERIC, scene_episode NUMERIC, absolute_number NUMERIC, '
'scene_absolute_number NUMERIC, version NUMERIC DEFAULT -1, release_group TEXT, manually_searched NUMERIC);'
)
# Re-insert old values, setting the new quality column to the invalid value of -1
self.connection.action(
'INSERT INTO tv_episodes '
'(showid, indexerid, indexer, name, season, episode, description, airdate, hasnfo, '
'hastbn, status, quality, location, file_size, release_name, subtitles, subtitles_searchcount, '
'subtitles_lastsearch, is_proper, scene_season, scene_episode, absolute_number, scene_absolute_number, '
'version, release_group, manually_searched) '
'SELECT showid, indexerid, indexer, '
'name, season, episode, description, airdate, hasnfo, '
'hastbn, status, -1 AS quality, location, file_size, release_name, '
'subtitles, subtitles_searchcount, subtitles_lastsearch, '
'is_proper, scene_season, scene_episode, absolute_number, '
'scene_absolute_number, version, release_group, manually_searched '
'FROM tmp_tv_episodes;'
)
# We have all that we need, drop the old table
for index in ['idx_sta_epi_air', 'idx_sta_epi_sta_air', 'idx_status']:
log.info(u'Dropping the index on {0}', index)
self.connection.action('DROP INDEX IF EXISTS {index};'.format(index=index))
self.connection.action('DROP TABLE IF EXISTS tmp_tv_episodes;')
log.info(u'Splitting the composite status into status and quality')
sql_results = self.connection.select('SELECT status from tv_episodes GROUP BY status;')
for episode in sql_results:
composite_status = episode['status']
status, quality = utils.split_composite_status(composite_status)
self.connection.action('UPDATE tv_episodes SET status = ?, quality = ? WHERE status = ?;',
[status, quality, composite_status])
# Update `history` table: Remove the quality value from `action`
log.info(u'Removing the quality from the action field, as this is a composite status')
sql_results = self.connection.select('SELECT action FROM history GROUP BY action;')
for item in sql_results:
composite_action = item['action']
status, quality = utils.split_composite_status(composite_action)
self.connection.action('UPDATE history SET action = ? WHERE action = ?;',
[status, composite_action])
self.inc_minor_version()
def translate_status(self):
"""
Add columns status and quality.
Translate composite status/quality from old_status to the new fields.
"""
log.info(u'Transforming old_status (composite) to separated fields status + quality.')
self.connection.action('DROP TABLE IF EXISTS new_history;')
self.connection.action('CREATE TABLE new_history (date NUMERIC, size NUMERIC, release TEXT, provider TEXT, '
'status NUMERIC DEFAULT -1, quality NUMERIC DEFAULT 0, showid NUMERIC DEFAULT -1, '
'season NUMERIC DEFAULT -1, episode NUMERIC DEFAULT -1, indexer_id NUMERIC)')
self.connection.action('INSERT INTO new_history (date, size, release, provider, '
'status, quality, showid, season, episode, indexer_id) '
'SELECT date, size, release, provider, '
'old_status, -1, showid, season, episode, indexer_id '
'FROM history;')
self.connection.action('DROP TABLE IF EXISTS history;')
self.connection.action('ALTER TABLE new_history RENAME TO history;')
self.connection.action('DROP TABLE IF EXISTS new_history;')
sql_results = self.connection.select('SELECT status FROM history GROUP BY status;')
for result in sql_results:
status, quality = utils.split_composite_status(result[b'status'])
self.connection.action('UPDATE history SET status = ?, quality = ? WHERE status = ?;',
[status, quality, result[b'status']])
def translate_status(self):
"""
Add columns status and quality.
Translate composite status/quality from old_status to the new fields.
"""
log.info(u'Transforming old_status (composite) to separated fields status + quality.')
self.connection.action('DROP TABLE IF EXISTS new_history;')
self.connection.action('CREATE TABLE new_history (date NUMERIC, size NUMERIC, release TEXT, provider TEXT, '
'status NUMERIC DEFAULT -1, quality NUMERIC DEFAULT 0, showid NUMERIC DEFAULT -1, '
'season NUMERIC DEFAULT -1, episode NUMERIC DEFAULT -1, indexer_id NUMERIC)')
self.connection.action('INSERT INTO new_history (date, size, release, provider, '
'status, quality, showid, season, episode, indexer_id) '
'SELECT date, size, release, provider, '
'old_status, -1, showid, season, episode, indexer_id '
'FROM history;')
self.connection.action('DROP TABLE IF EXISTS history;')
self.connection.action('ALTER TABLE new_history RENAME TO history;')
self.connection.action('DROP TABLE IF EXISTS new_history;')
sql_results = self.connection.select('SELECT status FROM history GROUP BY status;')
for result in sql_results:
status, quality = utils.split_composite_status(result['status'])
self.connection.action('UPDATE history SET status = ?, quality = ? WHERE status = ?;',
[status, quality, result['status']])