def test_all_action_types_writtable_in_db(database,
function_scope_messages_aggregator):
db = DBLedgerActions(database, function_scope_messages_aggregator)
query = 'SELECT COUNT(*) FROM ledger_actions WHERE identifier=?'
cursor = database.conn.cursor()
for entry in LedgerActionType:
action = LedgerAction(
identifier=0, # whatever
timestamp=1,
action_type=entry,
location=Location.EXTERNAL,
amount=FVal(1),
asset=A_ETH,
rate=None,
rate_asset=None,
link=None,
notes=None,
)
identifier = db.add_ledger_action(action)
# Check that changes have been committed to db
cursor.execute(query, (identifier, ))
assert cursor.fetchone() == (1, )
assert len(db.get_ledger_actions(None, None,
None)) == len(LedgerActionType)
def test_all_action_types_writtable_in_db(database, function_scope_messages_aggregator):
db = DBLedgerActions(database, function_scope_messages_aggregator)
for entry in LedgerActionType:
db.add_ledger_action(
timestamp=1,
action_type=entry,
location=Location.EXTERNAL,
amount=FVal(1),
asset=A_ETH,
link='',
notes='',
)
assert len(db.get_ledger_actions(None, None, None)) == len(LedgerActionType)
def query_income_loss_expense(
self,
start_ts: Timestamp,
end_ts: Timestamp,
only_cache: bool,
) -> List[LedgerAction]:
"""Queries the local DB and the exchange for the income/loss/expense history of the user
If only_cache is true only what is already cached in the DB is returned without
an actual exchange query.
"""
db = DBLedgerActions(self.db, self.db.msg_aggregator)
filter_query = LedgerActionsFilterQuery.make(
from_ts=start_ts,
to_ts=end_ts,
location=self.location,
)
# has_premium True is fine here since the result of this is not user facing atm
ledger_actions = db.get_ledger_actions(filter_query=filter_query,
has_premium=True)
if only_cache:
return ledger_actions
ranges = DBQueryRanges(self.db)
location_string = f'{str(self.location)}_ledger_actions_{self.name}'
ranges_to_query = ranges.get_location_query_ranges(
location_string=location_string,
start_ts=start_ts,
end_ts=end_ts,
)
new_ledger_actions = []
for query_start_ts, query_end_ts in ranges_to_query:
new_ledger_actions.extend(
self.query_online_income_loss_expense(
start_ts=query_start_ts,
end_ts=query_end_ts,
))
if new_ledger_actions != []:
db.add_ledger_actions(new_ledger_actions)
ranges.update_used_query_range(
location_string=location_string,
start_ts=start_ts,
end_ts=end_ts,
ranges_to_query=ranges_to_query,
)
ledger_actions.extend(new_ledger_actions)
return ledger_actions
def query_ledger_actions(
self,
has_premium: bool,
from_ts: Optional[Timestamp],
to_ts: Optional[Timestamp],
location: Optional[Location] = None,
) -> Tuple[List['LedgerAction'], int]:
"""Queries the ledger actions from the DB and applies the free version limit
TODO: Since we always query all in one call, the limiting will work, but if we start
batch querying by time then we need to amend the logic of limiting here.
Would need to use the same logic we do with trades. Using db entries count
and count what all calls return and what is sums up to
"""
db = DBLedgerActions(self.db, self.msg_aggregator)
actions = db.get_ledger_actions(from_ts=from_ts, to_ts=to_ts, location=location)
original_length = len(actions)
if has_premium is False:
actions = actions[:FREE_LEDGER_ACTIONS_LIMIT]
return actions, original_length
def test_delete_grant_events(rotkehlchen_api_server):
rotki = rotkehlchen_api_server.rest_api.rotkehlchen
# Get and save data of 3 different grants in the DB
id1 = 149
metadata1 = GitcoinGrantMetadata(
grant_id=id1,
name='Rotki - The portfolio tracker and accounting tool that protects your privacy',
created_on=1571694841,
)
json_data = {
'from_timestamp': 1622162468, # 28/05/2021
'to_timestamp': 1622246400, # 29/05/2021
'grant_id': id1,
}
response = requests.post(api_url_for(
rotkehlchen_api_server,
'gitcoineventsresource',
), json=json_data)
assert_proper_response(response)
id2 = 184
metadata2 = GitcoinGrantMetadata(
grant_id=id2,
name='TrueBlocks',
created_on=1575424305,
)
json_data = {
'from_timestamp': 1622162468, # 28/05/2021
'to_timestamp': 1622246400, # 29/05/2021
'grant_id': id2,
}
response = requests.post(api_url_for(
rotkehlchen_api_server,
'gitcoineventsresource',
), json=json_data)
assert_proper_response(response)
id3 = 223
metadata3 = GitcoinGrantMetadata(
grant_id=id3,
name='Ethereum Magicians',
created_on=1578054753,
)
json_data = {
'from_timestamp': 1622162468, # 28/05/2021
'to_timestamp': 1622246400, # 29/05/2021
'grant_id': id3,
}
response = requests.post(api_url_for(
rotkehlchen_api_server,
'gitcoineventsresource',
), json=json_data)
assert_proper_response(response)
# make sure events are saved
db = rotki.data.db
ledgerdb = DBLedgerActions(db, db.msg_aggregator)
actions = ledgerdb.get_ledger_actions(
filter_query=LedgerActionsFilterQuery.make(location=Location.GITCOIN),
has_premium=True,
)
assert len(actions) == 5
assert len([x for x in actions if x.extra_data.grant_id == id1]) == 3
assert len([x for x in actions if x.extra_data.grant_id == id2]) == 1
assert len([x for x in actions if x.extra_data.grant_id == id3]) == 1
# make sure db ranges were written
queryrange = db.get_used_query_range(f'{GITCOIN_GRANTS_PREFIX}_{id1}')
assert queryrange == (1622162468, 1622246400)
queryrange = db.get_used_query_range(f'{GITCOIN_GRANTS_PREFIX}_{id2}')
assert queryrange == (1622162468, 1622246400)
queryrange = db.get_used_query_range(f'{GITCOIN_GRANTS_PREFIX}_{id3}')
assert queryrange == (1622162468, 1622246400)
# make sure grant metadata were written
assert ledgerdb.get_gitcoin_grant_metadata() == {
id1: metadata1,
id2: metadata2,
id3: metadata3,
}
# delete 1 grant's data
response = requests.delete(api_url_for(
rotkehlchen_api_server,
'gitcoineventsresource',
), json={'grant_id': id2})
assert_proper_response(response)
# check that they got deleted but rest is fine
actions = ledgerdb.get_ledger_actions(
filter_query=LedgerActionsFilterQuery.make(location=Location.GITCOIN),
has_premium=True,
)
assert len(actions) == 4
assert len([x for x in actions if x.extra_data.grant_id == id1]) == 3
assert len([x for x in actions if x.extra_data.grant_id == id2]) == 0
assert len([x for x in actions if x.extra_data.grant_id == id3]) == 1
# make sure db ranges were written
queryrange = db.get_used_query_range(f'{GITCOIN_GRANTS_PREFIX}_{id1}')
assert queryrange == (1622162468, 1622246400)
assert db.get_used_query_range(f'{GITCOIN_GRANTS_PREFIX}_{id2}') is None
queryrange = db.get_used_query_range(f'{GITCOIN_GRANTS_PREFIX}_{id3}')
assert queryrange == (1622162468, 1622246400)
# make sure grant metadata were written
assert ledgerdb.get_gitcoin_grant_metadata() == {id1: metadata1, id3: metadata3}
# delete all remaining grant data
response = requests.delete(api_url_for(
rotkehlchen_api_server,
'gitcoineventsresource',
))
assert_proper_response(response)
# check that they got deleted but rest is fine
actions = ledgerdb.get_ledger_actions(
filter_query=LedgerActionsFilterQuery.make(location=Location.GITCOIN),
has_premium=True,
)
assert len(actions) == 0
# make sure db ranges were written
assert db.get_used_query_range(f'{GITCOIN_GRANTS_PREFIX}_{id1}') is None
assert db.get_used_query_range(f'{GITCOIN_GRANTS_PREFIX}_{id2}') is None
assert db.get_used_query_range(f'{GITCOIN_GRANTS_PREFIX}_{id3}') is None
# make sure grant metadata were written
assert ledgerdb.get_gitcoin_grant_metadata() == {}
def assert_nexo_results(rotki: Rotkehlchen):
"""A utility function to help assert on correctness of importing data from nexo"""
ledger_db = DBLedgerActions(rotki.data.db, rotki.msg_aggregator)
ledger_actions = ledger_db.get_ledger_actions(None, None, None)
asset_movements = rotki.data.db.get_asset_movements()
warnings = rotki.msg_aggregator.consume_warnings()
errors = rotki.msg_aggregator.consume_errors()
assert len(errors) == 0
assert len(warnings) == 0
expected_actions = [LedgerAction(
identifier=3,
timestamp=Timestamp(1565888464),
action_type=LedgerActionType.INCOME,
location=Location.NEXO,
amount=AssetAmount(FVal('22.5653042')),
asset=symbol_to_asset_or_token('NEXO'),
rate=None,
rate_asset=None,
link='NXT0000000009',
notes='Dividend from Nexo',
), LedgerAction(
identifier=2,
timestamp=Timestamp(1597492915),
action_type=LedgerActionType.INCOME,
location=Location.NEXO,
amount=AssetAmount(FVal('10.3585507')),
asset=symbol_to_asset_or_token('NEXO'),
rate=None,
rate_asset=None,
link='NXT0000000007',
notes='Dividend from Nexo',
), LedgerAction(
identifier=1,
timestamp=Timestamp(1614993620),
action_type=LedgerActionType.INCOME,
location=Location.NEXO,
amount=AssetAmount(FVal('1')),
asset=symbol_to_asset_or_token('USDC'),
rate=None,
rate_asset=None,
link='NXT0000000002',
notes='Interest from Nexo',
)]
expected_movements = [AssetMovement(
location=Location.NEXO,
category=AssetMovementCategory.DEPOSIT,
timestamp=Timestamp(1556116964),
address=None,
transaction_id=None,
asset=A_BTC,
amount=AssetAmount(FVal('1')),
fee_asset=A_USD,
fee=Fee(ZERO),
link='NXT0000000013',
), AssetMovement(
location=Location.NEXO,
category=AssetMovementCategory.WITHDRAWAL,
timestamp=Timestamp(1556122699),
address=None,
transaction_id=None,
asset=A_BTC,
amount=AssetAmount(FVal('0.9995')),
fee_asset=A_USD,
fee=Fee(ZERO),
link='NXT0000000012',
), AssetMovement(
location=Location.NEXO,
category=AssetMovementCategory.DEPOSIT,
timestamp=Timestamp(1558720210),
address=None,
transaction_id=None,
asset=symbol_to_asset_or_token('NEXO'),
amount=AssetAmount(FVal('1.00001')),
fee_asset=A_USD,
fee=Fee(ZERO),
link='NXT0000000011',
), AssetMovement(
location=Location.NEXO,
category=AssetMovementCategory.DEPOSIT,
timestamp=Timestamp(1565912821),
address=None,
transaction_id=None,
asset=A_EUR,
amount=AssetAmount(FVal('10000')),
fee_asset=A_USD,
fee=Fee(ZERO),
link='NXT0000000010',
), AssetMovement(
location=Location.NEXO,
category=AssetMovementCategory.WITHDRAWAL,
timestamp=Timestamp(1608131364),
address=None,
transaction_id=None,
asset=A_EUR,
amount=AssetAmount(FVal('2000.79')),
fee_asset=A_USD,
fee=Fee(ZERO),
link='NXT0000000005',
), AssetMovement(
location=Location.NEXO,
category=AssetMovementCategory.DEPOSIT,
timestamp=Timestamp(1614366540),
address=None,
transaction_id=None,
asset=A_EUR,
amount=AssetAmount(FVal('10')),
fee_asset=A_USD,
fee=Fee(ZERO),
link='NXT0000000003',
), AssetMovement(
location=Location.NEXO,
category=AssetMovementCategory.DEPOSIT,
timestamp=Timestamp(1615024314),
address=None,
transaction_id=None,
asset=symbol_to_asset_or_token('USDC'),
amount=AssetAmount(FVal('1')),
fee_asset=A_USD,
fee=Fee(ZERO),
link='NXT0000000001',
)]
assert ledger_actions == expected_actions
assert asset_movements == expected_movements
def assert_blockfi_transactions_import_results(rotki: Rotkehlchen):
"""A utility function to help assert on correctness of importing data from blockfi"""
ledger_db = DBLedgerActions(rotki.data.db, rotki.msg_aggregator)
ledger_actions = ledger_db.get_ledger_actions(None, None, None)
asset_movements = rotki.data.db.get_asset_movements()
warnings = rotki.msg_aggregator.consume_warnings()
errors = rotki.msg_aggregator.consume_errors()
assert len(errors) == 0
assert len(warnings) == 0
expected_actions = [LedgerAction(
identifier=3,
timestamp=Timestamp(1600293599),
action_type=LedgerActionType.INCOME,
location=Location.BLOCKFI,
amount=AssetAmount(FVal('0.48385358')),
asset=A_ETH,
rate=None,
rate_asset=None,
link=None,
notes='Bonus Payment from BlockFi',
), LedgerAction(
identifier=2,
timestamp=Timestamp(1606953599),
action_type=LedgerActionType.INCOME,
location=Location.BLOCKFI,
amount=AssetAmount(FVal('0.00052383')),
asset=A_BTC,
rate=None,
rate_asset=None,
link=None,
notes='Referral Bonus from BlockFi',
), LedgerAction(
identifier=1,
timestamp=Timestamp(1612051199),
action_type=LedgerActionType.INCOME,
location=Location.BLOCKFI,
amount=AssetAmount(FVal('0.56469042')),
asset=A_ETH,
rate=None,
rate_asset=None,
link=None,
notes='Interest Payment from BlockFi',
)]
assert expected_actions == ledger_actions
expected_movements = [AssetMovement(
location=Location.BLOCKFI,
category=AssetMovementCategory.DEPOSIT,
timestamp=Timestamp(1595247055),
address=None,
transaction_id=None,
asset=A_BTC,
amount=AssetAmount(FVal('1.11415058')),
fee_asset=A_USD,
fee=Fee(ZERO),
link='',
), AssetMovement(
location=Location.BLOCKFI,
category=AssetMovementCategory.WITHDRAWAL,
address=None,
transaction_id=None,
timestamp=Timestamp(1605977971),
asset=A_ETH,
amount=AssetAmount(FVal('3')),
fee_asset=A_USD,
fee=Fee(ZERO),
link='',
)]
assert expected_movements == asset_movements
def assert_cryptocom_special_events_import_results(rotki: Rotkehlchen):
"""A utility function to help assert on correctness of importing data from crypto.com"""
trades = rotki.data.db.get_trades()
ledger_db = DBLedgerActions(rotki.data.db, rotki.msg_aggregator)
ledger_actions = ledger_db.get_ledger_actions(None, None, None)
warnings = rotki.msg_aggregator.consume_warnings()
errors = rotki.msg_aggregator.consume_errors()
assert len(errors) == 0
assert len(warnings) == 0
expected_actions = [LedgerAction(
identifier=5,
timestamp=Timestamp(1609884000),
action_type=LedgerActionType.INCOME,
location=Location.CRYPTOCOM,
amount=AssetAmount(FVal('1')),
asset=symbol_to_asset_or_token('CRO'),
rate=None,
rate_asset=None,
link=None,
notes=None,
), LedgerAction(
identifier=4,
timestamp=Timestamp(1609884000),
action_type=LedgerActionType.INCOME,
location=Location.CRYPTOCOM,
amount=AssetAmount(FVal('0.5')),
asset=symbol_to_asset_or_token('MCO'),
rate=None,
rate_asset=None,
link=None,
notes=None,
), LedgerAction(
identifier=3,
timestamp=Timestamp(1609884000),
action_type=LedgerActionType.INCOME,
location=Location.CRYPTOCOM,
amount=AssetAmount(FVal('1')),
asset=symbol_to_asset_or_token('CRO'),
rate=None,
rate_asset=None,
link=None,
notes=None,
), LedgerAction(
identifier=2,
timestamp=Timestamp(1609797600),
action_type=LedgerActionType.INCOME,
location=Location.CRYPTOCOM,
amount=AssetAmount(FVal('0.02005')),
asset=A_BTC,
rate=None,
rate_asset=None,
link=None,
notes='Stake profit for asset BTC',
), LedgerAction(
identifier=1,
timestamp=Timestamp(1609624800),
action_type=LedgerActionType.INCOME,
location=Location.CRYPTOCOM,
amount=AssetAmount(FVal('0.00005')),
asset=A_BTC,
rate=None,
rate_asset=None,
link=None,
notes='Stake profit for asset BTC',
)]
assert list(reversed(expected_actions)) == ledger_actions
expected_trades = [Trade(
timestamp=Timestamp(1609884000),
location=Location.CRYPTOCOM,
base_asset=symbol_to_asset_or_token('CRO'),
quote_asset=symbol_to_asset_or_token('MCO'),
trade_type=TradeType.BUY,
amount=AssetAmount(FVal('1')),
rate=Price(FVal('10')),
fee=Fee(ZERO),
fee_currency=A_USD,
link='',
notes='MCO Earnings/Rewards Swap\nSource: crypto.com (CSV import)',
)]
assert trades == expected_trades
class GitcoinDataImporter():
def __init__(self, db: DBHandler) -> None:
self.db = db
self.db_ledger = DBLedgerActions(self.db, self.db.msg_aggregator)
self.grantid_re = re.compile(r'/grants/(\d+)/.*')
def _consume_grant_entry(self, entry: Dict[str,
Any]) -> Optional[LedgerAction]:
"""
Consumes a grant entry from the CSV and turns it into a LedgerAction
May raise:
- DeserializationError
- KeyError
- UnknownAsset
"""
if entry['Type'] != 'grant':
return None
timestamp = deserialize_timestamp_from_date(
date=entry['date'],
formatstr='%Y-%m-%dT%H:%M:%S',
location='Gitcoin CSV',
skip_milliseconds=True,
)
usd_value = deserialize_asset_amount(entry['Value In USD'])
asset = get_asset_by_symbol(entry['token_name'])
if asset is None:
raise UnknownAsset(entry['token_name'])
token_amount = deserialize_asset_amount(entry['token_value'])
if token_amount == ZERO: # try to make up for https://github.com/gitcoinco/web/issues/9213
price = query_usd_price_zero_if_error(
asset=asset,
time=timestamp,
location=f'Gitcoin CSV entry {entry["txid"]}',
msg_aggregator=self.db.msg_aggregator,
)
if price == ZERO:
self.db.msg_aggregator.add_warning(
f'Could not process gitcoin grant entry at {entry["date"]} for {asset.symbol} '
f'due to amount being zero and inability to find price. Skipping.',
)
return None
# calculate the amount from price and value
token_amount = usd_value / price # type: ignore
match = self.grantid_re.search(entry['url'])
if match is None:
self.db.msg_aggregator.add_warning(
f'Could not process gitcoin grant entry at {entry["date"]} for {asset.symbol} '
f'due to inability to read grant id. Skipping.', )
return None
grant_id = int(match.group(1))
rate = Price(usd_value / token_amount)
raw_txid = entry['txid']
tx_type, tx_id = process_gitcoin_txid(key='txid', entry=entry)
return LedgerAction(
identifier=1, # whatever does not go in the DB
timestamp=timestamp,
action_type=LedgerActionType.DONATION_RECEIVED,
location=Location.GITCOIN,
amount=token_amount,
asset=asset,
rate=rate,
rate_asset=A_USD, # let's use the rate gitcoin calculated
link=raw_txid,
notes=f'Gitcoin grant {grant_id} event',
extra_data=GitcoinEventData(
tx_id=tx_id,
grant_id=grant_id,
clr_round=None, # can't get round from CSV
tx_type=tx_type,
),
)
def import_gitcoin_csv(self, filepath: Path) -> None:
with open(filepath, 'r', encoding='utf-8-sig') as csvfile:
data = csv.DictReader(csvfile, delimiter=',', quotechar='"')
actions = []
for row in data:
try:
action = self._consume_grant_entry(row)
except UnknownAsset as e:
self.db.msg_aggregator.add_warning(
f'During gitcoin grant CSV processing found asset {e.asset_name} '
f'that cant be matched to a single known asset. Skipping entry.',
)
continue
except (DeserializationError, KeyError) as e:
msg = str(e)
if isinstance(e, KeyError):
msg = f'Missing key entry for {msg}.'
self.db.msg_aggregator.add_error(
'Unexpected data encountered during deserialization of a gitcoin CSV '
'entry. Check logs for details and open a bug report.',
)
log.error(
f'Unexpected data encountered during deserialization of a gitcoin '
f'CSV entry: {row} . Error was: {msg}', )
continue
if action:
actions.append(action)
db_actions = self.db_ledger.get_ledger_actions(
filter_query=LedgerActionsFilterQuery.make(
location=Location.GITCOIN),
has_premium=True,
)
existing_txids = [x.link for x in db_actions]
self.db_ledger.add_ledger_actions(
[x for x in actions if x.link not in existing_txids])
def test_store_same_tx_hash_in_db(database):
"""Test that if somehow during addition a duplicate is added,
it's ignored and only 1 ends up in the db"""
action1 = LedgerAction(
identifier=1,
timestamp=Timestamp(1624791600),
action_type=LedgerActionType.DONATION_RECEIVED,
location=Location.GITCOIN,
amount=FVal('0.0004789924016679019628604417823'),
asset=A_ETH,
rate=FVal('1983.33'),
rate_asset=A_USD,
link=
'0x00298f72ad40167051e111e6dc2924de08cce7cf0ad00d04ad5a9e58426536a1',
notes='Gitcoin grant 149 event',
extra_data=GitcoinEventData(
tx_id=
'0x00298f72ad40167051e111e6dc2924de08cce7cf0ad00d04ad5a9e58426536a1',
grant_id=149,
clr_round=None,
tx_type=GitcoinEventTxType.ETHEREUM,
),
)
action2 = LedgerAction(
identifier=2,
timestamp=Timestamp(1634791600),
action_type=LedgerActionType.DONATION_RECEIVED,
location=Location.GITCOIN,
amount=FVal('0.789924016679019628604417823'),
asset=A_ETH,
rate=FVal('1913.33'),
rate_asset=A_USD,
link=
'0x00298f72ad40167051e111e6dc2924de08cce7cf0ad00d04ad5a9e58426536a1',
notes='Gitcoin grant 149 event',
extra_data=GitcoinEventData(
tx_id=
'0x00298f72ad40167051e111e6dc2924de08cce7cf0ad00d04ad5a9e58426536a1',
grant_id=149,
clr_round=None,
tx_type=GitcoinEventTxType.ETHEREUM,
),
)
action3 = LedgerAction(
identifier=2,
timestamp=Timestamp(1654791600),
action_type=LedgerActionType.DONATION_RECEIVED,
location=Location.GITCOIN,
amount=FVal('2445533521905078832065264'),
asset=A_ETH,
rate=FVal('1973.33'),
rate_asset=A_USD,
link=
'sync-tx:5612f84bc20cda25b911af39b792c973bdd5916b3b6868db2420b5dafd705a90',
notes='Gitcoin grant 149 event',
extra_data=GitcoinEventData(
tx_id=
'5612f84bc20cda25b911af39b792c973bdd5916b3b6868db2420b5dafd705a90',
grant_id=149,
clr_round=None,
tx_type=GitcoinEventTxType.ZKSYNC,
),
)
dbledger = DBLedgerActions(database, database.msg_aggregator)
dbledger.add_ledger_actions([action1, action2, action3])
stored_actions = dbledger.get_ledger_actions(
filter_query=LedgerActionsFilterQuery.make(location=Location.GITCOIN),
has_premium=True,
)
assert stored_actions == [action1, action3]
errors = database.msg_aggregator.consume_errors()
warnings = database.msg_aggregator.consume_warnings()
assert len(errors) == 0
assert len(warnings) == 1
assert 'Did not add ledger action to DB' in warnings[0]