def _parse_asset_data(self, insert_text: str) -> ParsedAssetData: match = self.assets_re.match(insert_text) if match is None: raise DeserializationError( f'At asset DB update could not parse asset data out of {insert_text}', ) if len(match.groups()) != 9: raise DeserializationError( f'At asset DB update could not parse asset data out of {insert_text}', ) raw_type = self._parse_str(match.group(2), 'asset type', insert_text) asset_type = AssetType.deserialize_from_db(raw_type) raw_started = self._parse_optional_int(match.group(5), 'started', insert_text) started = Timestamp(raw_started) if raw_started else None return ParsedAssetData( identifier=self._parse_str(match.group(1), 'identifier', insert_text), asset_type=asset_type, name=self._parse_str(match.group(3), 'name', insert_text), symbol=self._parse_str(match.group(4), 'symbol', insert_text), started=started, swapped_for=self._parse_optional_str(match.group(6), 'swapped_for', insert_text), coingecko=self._parse_optional_str(match.group(7), 'coingecko', insert_text), cryptocompare=self._parse_optional_str(match.group(8), 'cryptocompare', insert_text), )
def add_asset( asset_id: str, asset_type: AssetType, data: Union[CustomEthereumToken, Dict[str, Any]], ) -> None: """ Add an asset in the DB. Either an ethereum token or a custom asset. If it's a custom asset the data should be typed. As given in by marshmallow. May raise InputError in case of error, meaning asset exists or some constraint hit""" connection = GlobalDBHandler()._conn cursor = connection.cursor() details_id: Union[str, ChecksumEthAddress] if asset_type == AssetType.ETHEREUM_TOKEN: token = cast(CustomEthereumToken, data) GlobalDBHandler().add_ethereum_token_data(token) details_id = token.address name = token.name symbol = token.symbol started = token.started swapped_for = token.swapped_for.identifier if token.swapped_for else None coingecko = token.coingecko cryptocompare = token.cryptocompare else: details_id = asset_id data = cast(Dict[str, Any], data) # The data should already be typed (as given in by marshmallow) name = data.get('name', None) symbol = data.get('symbol', None) started = data.get('started', None) swapped_for_asset = data.get('swapped_for', None) swapped_for = swapped_for_asset.identifier if swapped_for_asset else None coingecko = data.get('coingecko', None) cryptocompare = data.get('cryptocompare', None) try: cursor.execute( 'INSERT INTO assets(' 'identifier, type, name, symbol, started, swapped_for, ' 'coingecko, cryptocompare, details_reference) ' 'VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)', (asset_id, asset_type.serialize_for_db(), name, symbol, started, swapped_for, coingecko, cryptocompare, details_id), ) except sqlite3.IntegrityError as e: connection.rollback() raise InputError( f'Failed to add asset {asset_id} into the assets table for details id {details_id}', # noqa: E501 ) from e # for common asset details we have to add them after the addition of the main asset table if asset_type != AssetType.ETHEREUM_TOKEN: asset_data = cast(Dict[str, Any], data) asset_data['identifier'] = asset_id GlobalDBHandler().add_common_asset_details(asset_data) connection.commit() # success
def test_query_asset_types(rotkehlchen_api_server): response = requests.get( api_url_for( rotkehlchen_api_server, 'assetstypesresource', ), ) result = assert_proper_response_with_result(response) assert result == [str(x) for x in AssetType] assert all(isinstance(AssetType.deserialize(x), AssetType) for x in result)
def get_all_asset_data( mapping: bool ) -> Union[List[AssetData], Dict[str, Dict[str, Any]]]: """Return all asset data from the DB TODO: This can be improved. Too many sql queries. If mapping is True, return them as a Dict of identifier to data If mapping is False, return them as a List of AssetData """ result: Union[List[AssetData], Dict[str, Dict[str, Any]]] if mapping: result = {} else: result = [] cursor = GlobalDBHandler()._conn.cursor() querystr = """ SELECT A.identifier, A.type, B.address, B.decimals, A.name, A.symbol, A.started, null, A.swapped_for, A.coingecko, A.cryptocompare, B.protocol from assets as A LEFT OUTER JOIN ethereum_tokens as B ON B.address = A.details_reference WHERE A.type=? UNION ALL SELECT A.identifier, A.type, null, null, A.name, A.symbol, A.started, B.forked, A.swapped_for, A.coingecko, A.cryptocompare, null from assets as A LEFT OUTER JOIN common_asset_details as B ON B.asset_id = A.identifier WHERE A.type!=?; """ # noqa: E501 eth_token_type = AssetType.ETHEREUM_TOKEN.serialize_for_db() # pylint: disable=no-member query = cursor.execute(querystr, (eth_token_type, eth_token_type)) for entry in query: asset_type = AssetType.deserialize_from_db(entry[1]) ethereum_address: Optional[ChecksumEthAddress] if asset_type == AssetType.ETHEREUM_TOKEN: ethereum_address = string_to_ethereum_address(entry[2]) else: ethereum_address = None data = AssetData( identifier=entry[0], asset_type=asset_type, ethereum_address=ethereum_address, decimals=entry[3], name=entry[4], symbol=entry[5], started=entry[6], forked=entry[7], swapped_for=entry[8], coingecko=entry[9], cryptocompare=entry[10], protocol=entry[11], ) if mapping: result[entry[0]] = data.serialize() # type: ignore else: result.append(data) # type: ignore return result
def get_assets_with_symbol( symbol: str, asset_type: Optional[AssetType] = None ) -> List[AssetData]: # noqa: E501 """Find all asset entries that have the given symbol""" connection = GlobalDBHandler()._conn cursor = connection.cursor() query_tuples: Union[Tuple[str, str, str, str], Tuple[str, str, str, str, str]] eth_token_type = AssetType.ETHEREUM_TOKEN.serialize_for_db() # pylint: disable=no-member if asset_type is not None: asset_type_check = ' AND A.type=?' query_tuples = (symbol, eth_token_type, symbol, eth_token_type, asset_type.serialize_for_db()) # noqa: E501 else: asset_type_check = '' query_tuples = (symbol, eth_token_type, symbol, eth_token_type) querystr = f""" SELECT A.identifier, A.type, B.address, B.decimals, A.name, A.symbol, A.started, null, A.swapped_for, A.coingecko, A.cryptocompare, B.protocol from assets as A LEFT OUTER JOIN ethereum_tokens as B ON B.address = A.details_reference WHERE A.symbol=? COLLATE NOCASE AND A.type=? UNION ALL SELECT A.identifier, A.type, null, null, A.name, A.symbol, A.started, B.forked, A.swapped_for, A.coingecko, A.cryptocompare, null from assets as A LEFT OUTER JOIN common_asset_details as B ON B.asset_id = A.identifier WHERE A.symbol=? COLLATE NOCASE AND A.type!=?{asset_type_check}; """ # noqa: E501 query = cursor.execute(querystr, query_tuples) assets = [] for entry in query: asset_type = AssetType.deserialize_from_db(entry[1]) ethereum_address: Optional[ChecksumEthAddress] if asset_type == AssetType.ETHEREUM_TOKEN: ethereum_address = string_to_ethereum_address(entry[2]) else: ethereum_address = None assets.append( AssetData( identifier=entry[0], asset_type=asset_type, ethereum_address=ethereum_address, decimals=entry[3], name=entry[4], symbol=entry[5], started=entry[6], forked=entry[7], swapped_for=entry[8], coingecko=entry[9], cryptocompare=entry[10], protocol=entry[11], )) return assets
def check_asset_exists( asset_type: AssetType, name: str, symbol: str, ) -> Optional[List[str]]: """Checks if an asset of a given type, symbol and name exists in the DB already For non ethereum tokens with no unique identifier like an address this is the only way to check if something already exists in the DB. If it exists it returns a list of the identifiers of the assets. """ cursor = GlobalDBHandler()._conn.cursor() query = cursor.execute( 'SELECT identifier from assets WHERE type=? AND name=? AND symbol=?;', (asset_type.serialize_for_db(), name, symbol), ) result = query.fetchall() if len(result) == 0: return None return [x[0] for x in result]
def get_asset_data( identifier: str, form_with_incomplete_data: bool, ) -> Optional[AssetData]: """Get all details of a single asset by identifier Returns None if identifier can't be matched to an asset """ cursor = GlobalDBHandler()._conn.cursor() query = cursor.execute( 'SELECT identifier, type, name, symbol, started, swapped_for, coingecko, ' 'cryptocompare, details_reference from assets WHERE identifier=?;', (identifier, ), ) result = query.fetchone() if result is None: return None # Since comparison is case insensitive let's return original identifier saved_identifier = result[0] # get the identifier as saved in the DB. db_serialized_type = result[1] name = result[2] symbol = result[3] started = result[4] swapped_for = result[5] coingecko = result[6] cryptocompare = result[7] details_reference = result[8] forked = None decimals = None protocol = None ethereum_address = None try: asset_type = AssetType.deserialize_from_db(db_serialized_type) except DeserializationError as e: log.debug( f'Failed to read asset {identifier} from the DB due to ' f'{str(e)}. Skipping', ) return None if asset_type == AssetType.ETHEREUM_TOKEN: ethereum_address = details_reference cursor.execute( 'SELECT decimals, protocol from ethereum_tokens ' 'WHERE address=?', (ethereum_address, ), ) result = query.fetchone() if result is None: log.error( f'Found token {saved_identifier} in the DB assets table but not ' f'in the token details table.', ) return None decimals = result[0] protocol = result[1] missing_basic_data = name is None or symbol is None or decimals is None if missing_basic_data and form_with_incomplete_data is False: log.debug( f'Considering ethereum token with address {details_reference} ' f'as unknown since its missing either decimals or name or symbol', ) return None else: cursor = GlobalDBHandler()._conn.cursor() query = cursor.execute( 'SELECT forked FROM common_asset_details WHERE asset_id = ?;', (details_reference, ), ) result = query.fetchone() if result is None: log.error( f'Found asset {saved_identifier} in the DB assets table but not ' f'in the common asset details table.', ) return None forked = result[0] return AssetData( identifier=saved_identifier, name=name, symbol=symbol, asset_type=asset_type, started=started, forked=forked, swapped_for=swapped_for, ethereum_address=ethereum_address, decimals=decimals, coingecko=coingecko, cryptocompare=cryptocompare, protocol=protocol, )