def update_margin_positions(self, cursor: 'Cursor') -> None:
"""Upgrades the margin positions table to use the new asset ids if they are ethereum tokens
And also makes sure the new primary key id matches the rules used in the app
"""
query = cursor.execute(
'SELECT id, location, open_time, close_time, profit_loss,'
'pl_currency,fee,fee_currency,link,notes from margin_positions;', )
m_tuples = query.fetchall()
cursor.execute('DELETE from margin_positions;')
new_tuples = []
for entry in m_tuples:
new_pl_currency = self.get_new_asset_identifier_if_existing(
entry[5])
new_fee_currency = self.get_new_asset_identifier_if_existing(
entry[7])
# formulate the new DB identifier primary key. Copy the identifier() functionality
open_time_str = 'None' if entry[2] == 0 else str(entry[2])
new_id_string = (str(deserialize_location_from_db(entry[1])) +
open_time_str + str(entry[3]) + new_pl_currency +
new_fee_currency + entry[8])
new_id = hash_id(new_id_string)
new_tuples.append((
new_id,
entry[1],
entry[2],
entry[3],
entry[4],
new_pl_currency,
entry[6],
new_fee_currency,
entry[8],
entry[9],
))
cursor.executemany(
'INSERT INTO margin_positions('
'id, location, open_time, close_time, profit_loss,'
'pl_currency,fee,fee_currency,link,notes) '
'VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?);',
new_tuples,
)
def update_asset_movements(self, cursor: 'Cursor') -> None:
"""Upgrades the asset movements table to use the new asset ids if they are ethereum tokens
And also makes sure the new primary key id matches the rules used in the app
"""
query = cursor.execute(
'SELECT id, location, category, address, transaction_id, time,'
'asset,amount,fee_asset,fee,link from asset_movements;', )
m_tuples = query.fetchall()
cursor.execute('DELETE from asset_movements;')
new_tuples = []
for entry in m_tuples:
new_asset = self.get_new_asset_identifier_if_existing(entry[6])
new_fee_asset = self.get_new_asset_identifier_if_existing(entry[8])
# formulate the new DB identifier primary key. Copy the identifier() functionality
new_id_string = (
str(deserialize_location_from_db(entry[1])) +
str(deserialize_asset_movement_category_from_db(entry[2])) +
str(entry[5]) + new_asset + new_fee_asset + entry[10])
new_id = hash_id(new_id_string)
new_tuples.append((
new_id,
entry[1],
entry[2],
entry[3],
entry[4],
entry[5],
new_asset,
entry[7],
new_fee_asset,
entry[9],
entry[10],
))
cursor.executemany(
'INSERT INTO asset_movements('
'id, location, category, address, transaction_id, time,'
'asset, amount, fee_asset, fee, link) '
'VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);',
new_tuples,
)
def update_trades(self, cursor: 'Cursor') -> None:
"""Upgrades the trades table to use base/quote asset instead of a pair
Also upgrades all asset ids if they are ethereum tokens
And also makes sure the new primary key id matches the rules used in the app
"""
# Get all old data and transform it to the new schema
query = cursor.execute(
'SELECT id, '
' time, '
' location, '
' pair, '
' type, '
' amount, '
' rate, '
' fee, '
' fee_currency, '
' link, '
' notes from trades; ', )
new_trade_tuples = []
for entry in query:
try:
base, quote = pair_get_asset_ids(entry[3])
except ValueError as e:
self.msg_aggregator.add_warning(
f'During v24 -> v25 DB upgrade {str(e)}. This should not have happened.'
f' Removing the trade with id {entry[0]} at timestamp {entry[1]} '
f'and location {str(deserialize_location_from_db(entry[2]))} that '
f'contained the offending pair from the DB.', )
continue
new_id = self.get_new_asset_identifier(base)
new_base = new_id if new_id else base
new_id = self.get_new_asset_identifier(quote)
new_quote = new_id if new_id else quote
new_id = self.get_new_asset_identifier(entry[8])
new_fee_currency = new_id if new_id else entry[8]
timestamp = entry[1]
amount = entry[5]
rate = entry[6]
old_link = entry[9]
link = None if old_link == '' else old_link
notes = None if entry[10] == '' else entry[10]
# Copy the identifier() functionality. This identifier does not sound like a good idea
new_trade_id_string = (
str(deserialize_location_from_db(entry[2])) + str(timestamp) +
str(deserialize_trade_type_from_db(entry[4])) + new_base +
new_quote + amount + rate + old_link)
new_trade_id = hash_id(new_trade_id_string)
new_trade_tuples.append((
new_trade_id,
entry[1], # time
entry[2], # location
new_base,
new_quote,
entry[4], # type
amount,
rate,
entry[7], # fee
new_fee_currency,
link,
notes,
))
# Upgrade the table
cursor.execute('DROP TABLE IF EXISTS trades;')
cursor.execute("""
CREATE TABLE IF NOT EXISTS trades (
id TEXT PRIMARY KEY NOT NULL,
time INTEGER NOT NULL,
location CHAR(1) NOT NULL DEFAULT('A') REFERENCES location(location),
base_asset TEXT NOT NULL,
quote_asset TEXT NOT NULL,
type CHAR(1) NOT NULL DEFAULT ('A') REFERENCES trade_type(type),
amount TEXT NOT NULL,
rate TEXT NOT NULL,
fee TEXT,
fee_currency TEXT,
link TEXT,
notes TEXT
);
""")
# Insert the new data
executestr = """
INSERT INTO trades(
id,
time,
location,
base_asset,
quote_asset,
type,
amount,
rate,
fee,
fee_currency,
link,
notes)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"""
cursor.executemany(executestr, new_trade_tuples)