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']])