def find_one() -> Optional[KeyEntry]:
'''
Finds some key. Useful for checking if there's a valid key in there
'''
c = connection.get_cursor()
try:
res = c.execute('''
SELECT * FROM keys
''').fetchone()
return res if res is None else key_from_row(res)
finally:
c.close()
def find_all_unspents() -> List[Prevout]:
c = connection.get_cursor()
try:
res = [
prevout_from_row(p) for p in c.execute('''
SELECT * from prevouts
WHERE spent_at = -2
''')
]
return res
finally:
c.close()
def find_by_child(child_tx_id: str) -> List[Prevout]:
c = connection.get_cursor()
try:
res = [
prevout_from_row(p) for p in c.execute(
'''
SELECT * from prevouts
WHERE spent_by = :child_tx_id
''', {'child_tx_id': child_tx_id})
]
return res
finally:
c.close()
def find_all_addresses() -> List[str]:
'''
Finds all addresses that we're tracking
'''
c = connection.get_cursor()
try:
return [
r['address'] for r in c.execute('''
SELECT address FROM addresses
''')
]
finally:
c.close()
def find_heaviest() -> List[Header]:
c = connection.get_cursor()
try:
res = [
header_from_row(r) for r in c.execute('''
SELECT * FROM headers
WHERE accumulated_work =
(SELECT max(accumulated_work) FROM headers)
''')
]
return res
finally:
c.close()
def find_all() -> List[Prevout]:
'''
Finds all prevouts
'''
c = connection.get_cursor()
try:
res = [
prevout_from_row(r) for r in c.execute('''
SELECT * FROM prevouts
''')
]
return res
finally:
c.close()
def find_associated_pubkeys(script: bytes) -> List[str]:
'''
looks up pubkeys associated with a script
somewhat redundant with pubkeys_from_script
'''
c = connection.get_cursor()
try:
res = c.execute(
'''
SELECT pubkey FROM pubkey_to_script
WHERE script = :script
''', {'script': script})
return [r['pubkey'] for r in res]
finally:
c.close()
def find_by_script(script: bytes) -> List[AddressEntry]:
'''
Finds all AddressEntries with the corresponding Script
'''
c = connection.get_cursor()
try:
res = [
address_from_row(r) for r in c.execute(
'''
SELECT * FROM addresses
WHERE script = :script
''', {'script': script})
]
return res
finally:
c.close()
def store_header(header: Union[Header, str]) -> bool:
'''
Stores a header in the database
Args:
header (str or dict): parsed or unparsed header
Returns:
(bool): true if succesful, false if error
'''
if isinstance(header, str):
header = parse_header(header)
if not check_work(header):
return False
if header['height'] == 0:
parent_height, parent_work = parent_height_and_work(header)
if parent_height != 0:
header['height'] = parent_height + 1 # type: ignore
header['accumulated_work'] = (parent_work + header['difficulty']
) # type: ignore
else:
header['height'] = 0
header['accumulated_work'] = 0
c = connection.get_cursor()
try:
c.execute(
'''
INSERT OR REPLACE INTO headers VALUES (
:hash,
:version,
:prev_block,
:merkle_root,
:timestamp,
:nbits,
:nonce,
:difficulty,
:hex,
:height,
:accumulated_work)
''', (header))
connection.commit()
return True
except Exception:
return False
finally:
c.close()
def store_address(address: Union[str, AddressEntry]) -> bool:
'''
stores an address in the db
accepts a string address
'''
a: AddressEntry
if type(address) is str:
a = {
'address': cast(str, address),
'script': b'',
'script_pubkeys': []
}
else:
a = cast(AddressEntry, address)
if not validate_address(a):
return False
c = connection.get_cursor()
try:
c.execute(
'''
INSERT OR REPLACE INTO addresses VALUES (
:address,
:script)
''', a)
# NB: we track what pubkeys show up in what scripts so we can search
for pubkey in a['script_pubkeys']:
c.execute(
'''
INSERT OR REPLACE INTO pubkey_to_script VALUES (
:pubkey,
:script)
''', {
'pubkey': pubkey,
'script': a['script']
})
connection.commit()
return True
except Exception:
return False
finally:
c.close()
def find_by_outpoint(outpoint: Outpoint) -> Optional[Prevout]:
c = connection.get_cursor()
try:
res = [
prevout_from_row(p) for p in c.execute(
'''
SELECT * from prevouts
WHERE tx_id = :tx_id
AND idx = :index
''', outpoint)
]
for p in res:
# little hacky. returns first entry
# we know there can only be one
return p
return None
finally:
c.close()
def find_by_address(address: str) -> Optional[AddressEntry]:
'''
Finds an AddressEntry for the address if it exists, returns None otherwise
'''
c = connection.get_cursor()
try:
res = c.execute(
'''
SELECT * from addresses
WHERE address = :address
''', {'address': address})
for a in res:
# little hacky. returns first entry
# we know there can only be one
return address_from_row(a)
return None
finally:
c.close()
def find_by_pubkey(pubkey: str,
secret_phrase: Optional[str] = None,
get_priv: bool = False) -> List[KeyEntry]:
'''
finds a key by its pubkey
'''
c = connection.get_cursor()
try:
res = [
key_from_row(r, secret_phrase, get_priv) for r in c.execute(
'''
SELECT * FROM keys
WHERE pubkey = :pubkey
''', {'pubkey': pubkey})
]
return res
finally:
c.close()
def find_by_pubkey(pubkey: str) -> List[AddressEntry]:
'''
Finds all AddressEntries whose script includes the specified pubkey
'''
c = connection.get_cursor()
try:
res = [
address_from_row(r) for r in c.execute(
'''
SELECT * FROM addresses
WHERE script IN
(SELECT script FROM pubkey_to_script
WHERE pubkey = :pubkey)
''', {'pubkey': pubkey})
]
return res
finally:
c.close()
def find_spent_by_mempool_tx() -> List[Prevout]:
'''
Finds prevouts that have been spent by a tx in the mempool
Useful for checking if a tx can be replaced or has confirmed
'''
c = connection.get_cursor()
try:
# I don't like this.
# figure out how to do this without string format
res = [
prevout_from_row(p) for p in c.execute('''
SELECT * from prevouts
WHERE spent_at == -1
''')
]
return res
finally:
c.close()
def find_by_address(address: str) -> List[Prevout]:
'''
Finds prevouts by associated address
One address may have many prevouts
Args:
address (str):
'''
c = connection.get_cursor()
try:
return [
prevout_from_row(r) for r in c.execute(
'''
SELECT * FROM prevouts
WHERE address = :address
''', {'address': address})
]
finally:
c.close()
def find_by_script(script: bytes,
secret_phrase: Optional[str] = None,
get_priv: bool = False) -> List[KeyEntry]:
'''
Finds all KeyEntries whose pubkey appears in a certain script
'''
c = connection.get_cursor()
try:
res = [
key_from_row(r, secret_phrase, get_priv) for r in c.execute(
'''
SELECT * FROM keys
WHERE pubkey IN
(SELECT pubkey FROM pubkey_to_script
WHERE script = :script)
''', {'script': script})
]
return res
finally:
c.close()
def find_by_address(address: str,
secret_phrase: Optional[str] = None,
get_priv: bool = False) -> Optional[KeyEntry]:
'''
finds a key by its primary address
its primary address is the bech32 p2wpkh of its compressed pubkey
'''
c = connection.get_cursor()
try:
res = c.execute(
'''
SELECT * FROM keys
WHERE address = :address
''', {'address': address})
for a in res:
# little hacky. returns first entry
# we know there can only be one
return key_from_row(a, secret_phrase, get_priv)
return None
finally:
c.close()
def find_by_value_range(lower_value: int,
upper_value: int,
unspents_only: bool = True) -> List[Prevout]:
c = connection.get_cursor()
try:
# I don't like this.
# figure out how to do this without string format
res = [
prevout_from_row(p) for p in c.execute(
'''
SELECT * from prevouts
WHERE value <= :upper_value
AND value >= :lower_value
AND spent_at {operator} -2
'''.format(operator=('==' if unspents_only else '!=')), {
'upper_value': upper_value,
'lower_value': lower_value
})
]
return res
finally:
c.close()
def batch_store_header(h: List[Union[Header, str]]) -> bool:
'''
Stores a batch of headers in the database
Args:
header list(str or dict): parsed or unparsed header
Returns:
(bool): true if succesful, false if error
'''
# TODO: Refactor and improve
c = connection.get_cursor()
headers: List[Header] = []
for i in range(len(h)):
if isinstance(h[i], str):
headers.append(parse_header(h[i])) # type: ignore
else:
headers.append(cast(Header, h[i]))
headers[i]['height'] = 0
headers[i]['accumulated_work'] = 0
headers = list(filter(check_work, headers))
# NB: this block finds the last header for which we know a parent
# it discards headers earlier in the batch
# this pretty much assumes batches are ordered
for i in range(len(headers)):
parent = find_by_hash(cast(str,
headers[i]['prev_block'])) # type: ignore
if parent:
headers[i]['height'] = parent['height'] + 1 # type: ignore
headers[i]['accumulated_work'] = (
parent['accumulated_work'] # type: ignore
+ headers[0]['difficulty']) # type: ignore
headers = headers[i:]
break
# NB: this block checks if the header has a parent in the current batch
# it populates the height and accumulated work fields if so
for header in headers:
if header['height'] == 0:
results = list(
filter( # type: ignore
lambda k: k['hash'] == header['prev_block'], headers))
# only populate the fields if the parent has a height
if len(results) != 0 and results[0]['height'] > 0: # type: ignore
header['height'] = results[0]['height'] + 1 # type: ignore
header['accumulated_work'] = (
results[0]['accumulated_work'] # type: ignore
+ header['difficulty']) # type: ignore
try:
for header in headers:
c.execute(
'''
INSERT OR REPLACE INTO headers VALUES (
:hash,
:version,
:prev_block,
:merkle_root,
:timestamp,
:nbits,
:nonce,
:difficulty,
:hex,
:height,
:accumulated_work)
''', (header))
connection.commit()
return True
except Exception:
return False
finally:
c.close()