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 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
