def _upgrade_trades_table(db: 'DBHandler') -> None: """Upgrade the trades for DAI->SAI renaming""" cursor = db.conn.cursor() # This is the data we need from trades table at v6 cursor.execute( 'SELECT id,' ' time,' ' location,' ' pair,' ' type,' ' amount,' ' rate,' ' fee,' ' fee_currency,' ' link,' ' notes FROM trades;', ) trades_to_edit = [] trades_to_delete = [] count = 0 for result in cursor: count += 1 # for each trade get all the relevant data old_trade_id = result[0] time = int(result[1]) db_location = result[2] pair = result[3] db_trade_type = result[4] amount = result[5] rate = result[6] fee = result[7] fee_currency = result[8] link = result[9] notes = result[10] should_edit_trade = (time < MCDAI_LAUNCH_TS and ('DAI' in pair or fee_currency == 'DAI')) if should_edit_trade: # Mark old trade for deletion trades_to_delete.append((old_trade_id, )) # Generate data for new trade location = v6_deserialize_location_from_db(db_location) trade_type = v6_deserialize_trade_type_from_db(db_trade_type) pair = pair.replace('DAI_', 'SAI_') pair = pair.replace('_DAI', '_SAI') if fee_currency == 'DAI': fee_currency = 'SAI' new_trade_id = v6_generate_trade_id( location=location, time=time, trade_type=trade_type, pair=pair, amount=amount, rate=rate, link=link, ) trades_to_edit.append(( new_trade_id, time, db_location, pair, db_trade_type, amount, rate, fee, fee_currency, link, notes, )) # and now delete all old trades cursor.executemany('DELETE FROM trades WHERE id = ?', trades_to_delete) # and add all newly edited trades back in the DB query = """ INSERT INTO trades( id, time, location, pair, type, amount, rate, fee, fee_currency, link, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """ cursor.executemany(query, trades_to_edit) db.conn.commit()
def _upgrade_asset_movements_table(db: 'DBHandler') -> None: """Upgrade the deposits/withdrawals for DAI->SAI renaming""" cursor = db.conn.cursor() # This is the data we need from asset_movements table at v7 query = cursor.execute( 'SELECT id,' ' location,' ' category,' ' time,' ' asset,' ' amount,' ' fee_asset,' ' fee,' ' link FROM asset_movements ', ) entries_to_edit = [] entries_to_delete = [] for result in query: old_id = result[0] db_location = result[1] db_category = result[2] time = int(result[3]) asset = result[4] amount = result[5] fee_asset = result[6] fee = result[7] link = result[8] should_edit = time < MCDAI_LAUNCH_TS and (asset == 'DAI' or fee_asset == 'DAI') if should_edit: # mark old asset movement for deletion from DB entries_to_delete.append((old_id, )) location = v6_deserialize_location_from_db(db_location) category = v7_deserialize_asset_movement_category(db_category) if asset == 'DAI': asset = 'SAI' if fee_asset == 'DAI': fee_asset = 'SAI' new_id = v7_generate_asset_movement_id( location=location, category=category, time=time, asset=asset, fee_asset=fee_asset, link=link, ) entries_to_edit.append(( new_id, db_location, db_category, time, asset, amount, fee_asset, fee, link, )) # First delete all marked asset movements cursor.executemany('DELETE FROM asset_movements WHERE id = ?', entries_to_delete) # then insert all newly edited asset movements back in the DB query = """ INSERT INTO asset_movements( id, location, category, time, asset, amount, fee_asset, fee, link) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) """ cursor.executemany(query, entries_to_edit) db.conn.commit()
def test_upgrade_db_7_to_8(user_data_dir): """Test upgrading the DB from version 7 to version 8. Test that the SAI to DAI upgrade and renaming is done succesfully. """ msg_aggregator = MessagesAggregator() _use_prepared_db(user_data_dir, 'v7_rotkehlchen.db') db = _init_db_with_target_version( target_version=8, user_data_dir=user_data_dir, msg_aggregator=msg_aggregator, ) cursor = db.conn.cursor() # Check that trades got upgraded properly query = ( 'SELECT id,' ' time,' ' location,' ' pair,' ' type,' ' amount,' ' rate,' ' fee,' ' fee_currency,' ' link,' ' notes FROM trades ORDER BY time ASC;' ) results = cursor.execute(query) count = 0 for result in results: count += 1 trade_id = result[0] time = int(result[1]) location = result[2] pair = result[3] trade_type = result[4] amount = result[5] rate = result[6] fee_currency = result[8] link = result[9] # External trades, kraken trades if location in ('A', 'B'): if time < MCDAI_LAUNCH_TS: assert pair == 'SAI_EUR' assert fee_currency == 'EUR' else: assert pair == 'DAI_EUR' assert fee_currency == 'EUR' # coinbase trades elif location == 'G': if time < MCDAI_LAUNCH_TS: assert pair == 'SAI_USD' assert fee_currency == 'USD' else: assert pair == 'DAI_USD' assert fee_currency == 'USD' # bittrex trades elif location == 'D': if time < MCDAI_LAUNCH_TS: assert pair == 'SAI_BTC' assert fee_currency == 'SAI' else: assert pair == 'DAI_BTC' assert fee_currency == 'DAI' else: raise AssertionError('Unexpected location data') # also make sure the ids still match serialized_location = v6_deserialize_location_from_db(location) serialized_trade_type = v6_deserialize_trade_type_from_db(trade_type) expected_trade_id = v6_generate_trade_id( location=serialized_location, time=time, trade_type=serialized_trade_type, pair=pair, amount=amount, rate=rate, link=link, ) assert trade_id == expected_trade_id assert count == 8, '8 trades should have been found' # Check that deposits/withdrawals got upgraded properly query = ( 'SELECT id,' ' location,' ' category,' ' time,' ' asset,' ' amount,' ' fee_asset,' ' fee,' ' link FROM asset_movements ' ) results = cursor.execute(query) count = 0 for result in results: count += 1 entry_id = result[0] location = result[1] category = result[2] time = result[3] asset = result[4] fee_asset = result[6] link = result[8] # kraken , bittrex, coinbase assert location in ('B', 'D', 'G'), 'Unexpected location of asset movement' if time < MCDAI_LAUNCH_TS: assert asset == 'SAI' assert fee_asset == 'SAI' else: assert asset == 'DAI' assert fee_asset == 'DAI' deserialized_location = v6_deserialize_location_from_db(location) deserialized_category = v7_deserialize_asset_movement_category(category) expected_id = v7_generate_asset_movement_id( location=deserialized_location, category=deserialized_category, time=time, asset=asset, fee_asset=fee_asset, link=link, ) assert expected_id == entry_id assert count == 12, '12 asset movements should have been found' # Check that both SAI and DAI are included in the ETH tokens owned query = cursor.execute( 'SELECT value FROM multisettings WHERE name="eth_token";', ) assert [q[0] for q in query.fetchall()] == ['DAI', 'SAI'] # Check that saved balances of DAI are upgraded to SAI if before the upgrade time query = cursor.execute('SELECT time, currency, amount, usd_value FROM timed_balances;') count = 0 for result in query: count += 1 time = int(result[0]) asset = result[1] if time < MCDAI_LAUNCH_TS: assert asset == 'SAI' else: assert asset == 'DAI' assert count == 2, '2 saved balances should have been found' # Finally also make sure that we have updated to the target version assert db.get_version() == 8