def test_query_limit_offset(self):
self.assertEqual(query("select * from foo", limit=10),
("select * from foo LIMIT 10", {}))
self.assertEqual(query("select * from foo", offset=10),
("select * from foo OFFSET 10", {}))
self.assertEqual(query("select * from foo", limit=20, offset=10),
("select * from foo LIMIT 20 OFFSET 10", {}))
def test_query_order_by(self):
self.assertEqual(query("select * from foo", order_by='foo'),
("select * from foo ORDER BY foo", {}))
self.assertEqual(query("select * from foo", order_by=['foo', 'bar']),
("select * from foo ORDER BY foo, bar", {}))
with self.assertRaisesRegex(ValueError,
'order_by must be string or list'):
query("select * from foo", order_by={'foo': 'bar'})
def split_inputs_into_claims_supports_and_other(self, txis):
txo_hashes = {txi.txo_ref.hash for txi in txis}
claims = self.execute(
*query("SELECT txo_hash, claim_hash, normalized FROM claim",
txo_hash__in=txo_hashes)).fetchall()
txo_hashes -= {r.txo_hash for r in claims}
supports = {}
if txo_hashes:
supports = self.execute(
*query("SELECT txo_hash, claim_hash FROM support",
txo_hash__in=txo_hashes)).fetchall()
txo_hashes -= {r.txo_hash for r in supports}
return claims, supports, txo_hashes
def test_query_interpolation(self):
self.maxDiff = None
# tests that interpolation replaces longer keys first
self.assertEqual(
interpolate(*query(
"select * from foo",
a__not='b',
b__in='select * from blah where c=:$c',
d__any={
'one__like': 'o',
'two': 2
},
a0=3,
a00=1,
a00a=2,
a00aa=4, # <-- breaks without correct interpolation key order
ahash=memoryview(sha256(b'hello world')),
limit=10,
order_by='b',
**{'$c': 3})),
"select * from foo WHERE a != 'b' AND "
"b IN (select * from blah where c=3) AND "
"(one LIKE 'o' OR two = 2) AND "
"a0 = 3 AND a00 = 1 AND a00a = 2 AND a00aa = 4 "
"AND ahash = X'b94d27b9934d3e08a52e52d7da7dabfac484efe37a5380ee9088f7ace2efcde9' "
"ORDER BY b LIMIT 10",
)
def test_query_interpolation(self):
self.maxDiff = None
# tests that interpolation replaces longer keys first
self.assertEqual(
interpolate(*query(
"select * from foo",
a__not='b',
b__in='select * from blah where c=:$c',
d__any={
'one__like': 'o',
'two': 2
},
a0=3,
a00=1,
a00a=2,
a00aa=4, # <-- breaks without correct interpolation key order
ahash=memoryview(sha256(b'hello world')),
limit=10,
order_by='b',
**{'$c': 3})),
"select * from foo WHERE a != 'b' AND "
"b IN (select * from blah where c=3) AND "
"(one LIKE 'o' OR two = 2) AND "
"a0 = 3 AND a00 = 1 AND a00a = 2 AND a00aa = 4 "
"AND ahash = e9cdefe2acf78890ee80537ae3ef84c4faab7ddad7522ea5083e4d93b9274db9 "
"ORDER BY b LIMIT 10",
)
def test_query(self):
self.assertEqual(
query("select * from foo"),
("select * from foo", {})
)
self.assertEqual(
query(
"select * from foo",
a__not='b', b__in='select * from blah where c=:$c',
d__any={'one__like': 'o', 'two': 2}, limit=10, order_by='b', **{'$c': 3}),
(
"select * from foo WHERE a != :a__not0 AND "
"b IN (select * from blah where c=:$c) AND "
"(one LIKE :d__any0_one__like0 OR two = :d__any0_two0) ORDER BY b LIMIT 10",
{'a__not0': 'b', 'd__any0_one__like0': 'o', 'd__any0_two0': 2, '$c': 3}
)
)
def delete_claims(self, claim_hashes: Set[bytes]):
""" Deletes claim supports and from claimtrie in case of an abandon. """
if claim_hashes:
binary_claim_hashes = [sqlite3.Binary(claim_hash) for claim_hash in claim_hashes]
affected_channels = self.execute(*query(
"SELECT channel_hash FROM claim", channel_hash__is_not_null=1, claim_hash__in=binary_claim_hashes
)).fetchall()
for table in ('claim', 'support', 'claimtrie'):
self.execute(*self._delete_sql(table, {'claim_hash__in': binary_claim_hashes}))
self._clear_claim_metadata(binary_claim_hashes)
return {r['channel_hash'] for r in affected_channels}
return set()
def validate_channel_signatures(self, height, new_claims, updated_claims,
spent_claims, affected_channels, timer):
if not new_claims and not updated_claims and not spent_claims:
return
sub_timer = timer.add_timer('segregate channels and signables')
sub_timer.start()
channels, new_channel_keys, signables = {}, {}, {}
for txo in chain(new_claims, updated_claims):
try:
claim = txo.claim
except:
continue
if claim.is_channel:
channels[txo.claim_hash] = txo
new_channel_keys[
txo.claim_hash] = claim.channel.public_key_bytes
else:
signables[txo.claim_hash] = txo
sub_timer.stop()
sub_timer = timer.add_timer('make list of channels we need to lookup')
sub_timer.start()
missing_channel_keys = set()
for txo in signables.values():
claim = txo.claim
if claim.is_signed and claim.signing_channel_hash not in new_channel_keys:
missing_channel_keys.add(claim.signing_channel_hash)
sub_timer.stop()
sub_timer = timer.add_timer('lookup missing channels')
sub_timer.start()
all_channel_keys = {}
if new_channel_keys or missing_channel_keys or affected_channels:
all_channel_keys = dict(
self.execute(
*query("SELECT claim_hash, public_key_bytes FROM claim",
claim_hash__in=[
sqlite3.Binary(channel_hash)
for channel_hash in set(new_channel_keys)
| missing_channel_keys | affected_channels
])))
sub_timer.stop()
sub_timer = timer.add_timer('prepare for updating claims')
sub_timer.start()
changed_channel_keys = {}
for claim_hash, new_key in new_channel_keys.items():
if claim_hash not in all_channel_keys or all_channel_keys[
claim_hash] != new_key:
all_channel_keys[claim_hash] = new_key
changed_channel_keys[claim_hash] = new_key
claim_updates = []
for claim_hash, txo in signables.items():
claim = txo.claim
update = {
'claim_hash': sqlite3.Binary(claim_hash),
'channel_hash': None,
'signature': None,
'signature_digest': None,
'signature_valid': None
}
if claim.is_signed:
update.update({
'channel_hash':
sqlite3.Binary(claim.signing_channel_hash),
'signature':
sqlite3.Binary(txo.get_encoded_signature()),
'signature_digest':
sqlite3.Binary(txo.get_signature_digest(self.ledger)),
'signature_valid':
0
})
claim_updates.append(update)
sub_timer.stop()
sub_timer = timer.add_timer(
'find claims affected by a change in channel key')
sub_timer.start()
if changed_channel_keys:
sql = f"""
SELECT * FROM claim WHERE
channel_hash IN ({','.join('?' for _ in changed_channel_keys)}) AND
signature IS NOT NULL
"""
for affected_claim in self.execute(
sql, [sqlite3.Binary(h) for h in changed_channel_keys]):
if affected_claim['claim_hash'] not in signables:
claim_updates.append({
'claim_hash':
sqlite3.Binary(affected_claim['claim_hash']),
'channel_hash':
sqlite3.Binary(affected_claim['channel_hash']),
'signature':
sqlite3.Binary(affected_claim['signature']),
'signature_digest':
sqlite3.Binary(affected_claim['signature_digest']),
'signature_valid':
0
})
sub_timer.stop()
sub_timer = timer.add_timer('verify signatures')
sub_timer.start()
for update in claim_updates:
channel_pub_key = all_channel_keys.get(update['channel_hash'])
if channel_pub_key and update['signature']:
update['signature_valid'] = Output.is_signature_valid(
bytes(update['signature']),
bytes(update['signature_digest']), channel_pub_key)
sub_timer.stop()
sub_timer = timer.add_timer('update claims')
sub_timer.start()
if claim_updates:
self.db.executemany(
f"""
UPDATE claim SET
channel_hash=:channel_hash, signature=:signature, signature_digest=:signature_digest,
signature_valid=:signature_valid,
channel_join=CASE
WHEN signature_valid=1 AND :signature_valid=1 AND channel_hash=:channel_hash THEN channel_join
WHEN :signature_valid=1 THEN {height}
END,
canonical_url=CASE
WHEN signature_valid=1 AND :signature_valid=1 AND channel_hash=:channel_hash THEN canonical_url
WHEN :signature_valid=1 THEN
(SELECT short_url FROM claim WHERE claim_hash=:channel_hash)||'/'||
claim_name||COALESCE(
(SELECT shortest_id(other_claim.claim_id, claim.claim_id) FROM claim AS other_claim
WHERE other_claim.signature_valid = 1 AND
other_claim.channel_hash = :channel_hash AND
other_claim.normalized = claim.normalized),
'#'||substr(claim_id, 1, 1)
)
END
WHERE claim_hash=:claim_hash;
""", claim_updates)
sub_timer.stop()
sub_timer = timer.add_timer('update claims affected by spent channels')
sub_timer.start()
if spent_claims:
self.execute(
f"""
UPDATE claim SET
signature_valid=CASE WHEN signature IS NOT NULL THEN 0 END,
channel_join=NULL, canonical_url=NULL
WHERE channel_hash IN ({','.join('?' for _ in spent_claims)})
""", [sqlite3.Binary(cid) for cid in spent_claims])
sub_timer.stop()
sub_timer = timer.add_timer('update channels')
sub_timer.start()
if channels:
self.db.executemany(
"""
UPDATE claim SET
public_key_bytes=:public_key_bytes,
public_key_hash=:public_key_hash
WHERE claim_hash=:claim_hash""", [{
'claim_hash':
sqlite3.Binary(claim_hash),
'public_key_bytes':
sqlite3.Binary(txo.claim.channel.public_key_bytes),
'public_key_hash':
sqlite3.Binary(
self.ledger.address_to_hash160(
self.ledger.public_key_to_address(
txo.claim.channel.public_key_bytes)))
} for claim_hash, txo in channels.items()])
sub_timer.stop()
sub_timer = timer.add_timer('update claims_in_channel counts')
sub_timer.start()
if all_channel_keys:
self.db.executemany(
f"""
UPDATE claim SET
claims_in_channel=(
SELECT COUNT(*) FROM claim AS claim_in_channel
WHERE claim_in_channel.signature_valid=1 AND
claim_in_channel.channel_hash=claim.claim_hash
)
WHERE claim_hash = ?
""", [(sqlite3.Binary(channel_hash), )
for channel_hash in all_channel_keys.keys()])
sub_timer.stop()
def get_claims(cols, for_count=False, **constraints) -> List:
if 'order_by' in constraints:
sql_order_by = []
for order_by in constraints['order_by']:
is_asc = order_by.startswith('^')
column = order_by[1:] if is_asc else order_by
if column not in ORDER_FIELDS:
raise NameError(f'{column} is not a valid order_by field')
if column == 'name':
column = 'normalized'
sql_order_by.append(
f"claim.{column} ASC" if is_asc else f"claim.{column} DESC"
)
constraints['order_by'] = sql_order_by
ops = {'<=': '__lte', '>=': '__gte', '<': '__lt', '>': '__gt'}
for constraint in INTEGER_PARAMS:
if constraint in constraints:
value = constraints.pop(constraint)
postfix = ''
if isinstance(value, str):
if len(value) >= 2 and value[:2] in ops:
postfix, value = ops[value[:2]], value[2:]
elif len(value) >= 1 and value[0] in ops:
postfix, value = ops[value[0]], value[1:]
if constraint == 'fee_amount':
value = Decimal(value)*1000
constraints[f'claim.{constraint}{postfix}'] = int(value)
if constraints.pop('is_controlling', False):
if {'sequence', 'amount_order'}.isdisjoint(constraints):
for_count = False
constraints['claimtrie.claim_hash__is_not_null'] = ''
if 'sequence' in constraints:
constraints['order_by'] = 'claim.activation_height ASC'
constraints['offset'] = int(constraints.pop('sequence')) - 1
constraints['limit'] = 1
if 'amount_order' in constraints:
constraints['order_by'] = 'claim.effective_amount DESC'
constraints['offset'] = int(constraints.pop('amount_order')) - 1
constraints['limit'] = 1
if 'claim_id' in constraints:
claim_id = constraints.pop('claim_id')
if len(claim_id) == 40:
constraints['claim.claim_id'] = claim_id
else:
constraints['claim.claim_id__like'] = f'{claim_id[:40]}%'
if 'name' in constraints:
constraints['claim.normalized'] = normalize_name(constraints.pop('name'))
if 'public_key_id' in constraints:
constraints['claim.public_key_hash'] = sqlite3.Binary(
ctx.get().ledger.address_to_hash160(constraints.pop('public_key_id')))
if 'channel' in constraints:
channel_url = constraints.pop('channel')
match = resolve_url(channel_url)
if isinstance(match, sqlite3.Row):
constraints['channel_hash'] = match['claim_hash']
else:
return [[0]] if cols == 'count(*)' else []
if 'channel_hash' in constraints:
constraints['claim.channel_hash'] = sqlite3.Binary(constraints.pop('channel_hash'))
if 'channel_ids' in constraints:
channel_ids = constraints.pop('channel_ids')
if channel_ids:
constraints['claim.channel_hash__in'] = [
sqlite3.Binary(unhexlify(cid)[::-1]) for cid in channel_ids
]
if 'not_channel_ids' in constraints:
not_channel_ids = constraints.pop('not_channel_ids')
if not_channel_ids:
not_channel_ids_binary = [
sqlite3.Binary(unhexlify(ncid)[::-1]) for ncid in not_channel_ids
]
if constraints.get('has_channel_signature', False):
constraints['claim.channel_hash__not_in'] = not_channel_ids_binary
else:
constraints['null_or_not_channel__or'] = {
'claim.signature_valid__is_null': True,
'claim.channel_hash__not_in': not_channel_ids_binary
}
if 'signature_valid' in constraints:
has_channel_signature = constraints.pop('has_channel_signature', False)
if has_channel_signature:
constraints['claim.signature_valid'] = constraints.pop('signature_valid')
else:
constraints['null_or_signature__or'] = {
'claim.signature_valid__is_null': True,
'claim.signature_valid': constraints.pop('signature_valid')
}
elif constraints.pop('has_channel_signature', False):
constraints['claim.signature_valid__is_not_null'] = True
if 'txid' in constraints:
tx_hash = unhexlify(constraints.pop('txid'))[::-1]
nout = constraints.pop('nout', 0)
constraints['claim.txo_hash'] = sqlite3.Binary(
tx_hash + struct.pack('<I', nout)
)
if 'claim_type' in constraints:
constraints['claim.claim_type'] = CLAIM_TYPES[constraints.pop('claim_type')]
if 'stream_types' in constraints:
stream_types = constraints.pop('stream_types')
if stream_types:
constraints['claim.stream_type__in'] = [
STREAM_TYPES[stream_type] for stream_type in stream_types
]
if 'media_types' in constraints:
media_types = constraints.pop('media_types')
if media_types:
constraints['claim.media_type__in'] = media_types
if 'fee_currency' in constraints:
constraints['claim.fee_currency'] = constraints.pop('fee_currency').lower()
_apply_constraints_for_array_attributes(constraints, 'tag', clean_tags, for_count)
_apply_constraints_for_array_attributes(constraints, 'language', lambda _: _, for_count)
_apply_constraints_for_array_attributes(constraints, 'location', lambda _: _, for_count)
select = f"SELECT {cols} FROM claim"
sql, values = query(
select if for_count else select+"""
LEFT JOIN claimtrie USING (claim_hash)
LEFT JOIN claim as channel ON (claim.channel_hash=channel.claim_hash)
""", **constraints
)
return execute_query(sql, values)
def get_claims(self, cols, **constraints):
if 'order_by' in constraints:
sql_order_by = []
for order_by in constraints['order_by']:
is_asc = order_by.startswith('^')
column = order_by[1:] if is_asc else order_by
if column not in self.ORDER_FIELDS:
raise NameError(f'{column} is not a valid order_by field')
if column == 'name':
column = 'normalized'
sql_order_by.append(f"claim.{column} ASC"
if is_asc else f"claim.{column} DESC")
constraints['order_by'] = sql_order_by
ops = {'<=': '__lte', '>=': '__gte', '<': '__lt', '>': '__gt'}
for constraint in self.INTEGER_PARAMS:
if constraint in constraints:
value = constraints.pop(constraint)
postfix = ''
if isinstance(value, str):
if len(value) >= 2 and value[:2] in ops:
postfix, value = ops[value[:2]], int(value[2:])
elif len(value) >= 1 and value[0] in ops:
postfix, value = ops[value[0]], int(value[1:])
constraints[f'claim.{constraint}{postfix}'] = value
if constraints.pop('is_controlling', False):
if {'sequence', 'amount_order'}.isdisjoint(constraints):
constraints['claimtrie.claim_hash__is_not_null'] = ''
if 'sequence' in constraints:
constraints['order_by'] = 'claim.activation_height ASC'
constraints['offset'] = int(constraints.pop('sequence')) - 1
constraints['limit'] = 1
if 'amount_order' in constraints:
constraints['order_by'] = 'claim.effective_amount DESC'
constraints['offset'] = int(constraints.pop('amount_order')) - 1
constraints['limit'] = 1
if 'claim_id' in constraints:
constraints['claim.claim_hash'] = sqlite3.Binary(
unhexlify(constraints.pop('claim_id'))[::-1])
if 'name' in constraints:
constraints['claim.normalized'] = normalize_name(
constraints.pop('name'))
if 'channel' in constraints:
url = URL.parse(constraints.pop('channel'))
if url.channel.claim_id:
constraints['channel_id'] = url.channel.claim_id
else:
constraints['channel_name'] = url.channel.name
if 'channel_id' in constraints:
constraints['channel_hash'] = unhexlify(
constraints.pop('channel_id'))[::-1]
if 'channel_hash' in constraints:
constraints['channel.claim_hash'] = sqlite3.Binary(
constraints.pop('channel_hash'))
if 'channel_name' in constraints:
constraints['channel.normalized'] = normalize_name(
constraints.pop('channel_name'))
if 'txid' in constraints:
tx_hash = unhexlify(constraints.pop('txid'))[::-1]
nout = constraints.pop('nout', 0)
constraints['claim.txo_hash'] = sqlite3.Binary(
tx_hash + struct.pack('<I', nout))
_apply_constraints_for_array_attributes(constraints, 'tag')
_apply_constraints_for_array_attributes(constraints, 'language')
_apply_constraints_for_array_attributes(constraints, 'location')
try:
return self.db.execute(*query(
f"""
SELECT {cols} FROM claim
LEFT JOIN claimtrie USING (claim_hash)
LEFT JOIN claim as channel ON (claim.channel_hash=channel.claim_hash)
""", **constraints)).fetchall()
except:
self.logger.exception('Failed to execute claim search query:')
print(
query(
f"""
SELECT {cols} FROM claim
LEFT JOIN claimtrie USING (claim_hash)
LEFT JOIN claim as channel ON (claim.channel_hash=channel.claim_hash)
""", **constraints))
raise
def get_claims(self, cols, join=True, **constraints):
if 'order_by' in constraints:
sql_order_by = []
for order_by in constraints['order_by']:
is_asc = order_by.startswith('^')
column = order_by[1:] if is_asc else order_by
if column not in self.ORDER_FIELDS:
raise NameError(f'{column} is not a valid order_by field')
if column == 'name':
column = 'normalized'
sql_order_by.append(
f"claim.{column} ASC" if is_asc else f"claim.{column} DESC"
)
constraints['order_by'] = sql_order_by
ops = {'<=': '__lte', '>=': '__gte', '<': '__lt', '>': '__gt'}
for constraint in self.INTEGER_PARAMS:
if constraint in constraints:
value = constraints.pop(constraint)
postfix = ''
if isinstance(value, str):
if len(value) >= 2 and value[:2] in ops:
postfix, value = ops[value[:2]], int(value[2:])
elif len(value) >= 1 and value[0] in ops:
postfix, value = ops[value[0]], int(value[1:])
constraints[f'claim.{constraint}{postfix}'] = value
if constraints.pop('is_controlling', False):
if {'sequence', 'amount_order'}.isdisjoint(constraints):
join = True
constraints['claimtrie.claim_hash__is_not_null'] = ''
if 'sequence' in constraints:
constraints['order_by'] = 'claim.activation_height ASC'
constraints['offset'] = int(constraints.pop('sequence')) - 1
constraints['limit'] = 1
if 'amount_order' in constraints:
constraints['order_by'] = 'claim.effective_amount DESC'
constraints['offset'] = int(constraints.pop('amount_order')) - 1
constraints['limit'] = 1
if 'claim_id' in constraints:
claim_id = constraints.pop('claim_id')
if len(claim_id) == 40:
constraints['claim.claim_id'] = claim_id
else:
constraints['claim.claim_id__like'] = f'{claim_id[:40]}%'
if 'name' in constraints:
constraints['claim.normalized'] = normalize_name(constraints.pop('name'))
if 'channel' in constraints:
channel_url = constraints.pop('channel')
match = self._resolve_one(channel_url)
if isinstance(match, sqlite3.Row):
constraints['channel_hash'] = match['claim_hash']
else:
raise LookupError(f'Could not resolve channel "{channel_url}".')
if 'channel_hash' in constraints:
constraints['claim.channel_hash'] = sqlite3.Binary(constraints.pop('channel_hash'))
if 'channel_ids' in constraints:
channel_ids = constraints.pop('channel_ids')
if channel_ids:
constraints['claim.channel_hash__in'] = [
sqlite3.Binary(unhexlify(cid)[::-1]) for cid in channel_ids
]
if 'txid' in constraints:
tx_hash = unhexlify(constraints.pop('txid'))[::-1]
nout = constraints.pop('nout', 0)
constraints['claim.txo_hash'] = sqlite3.Binary(
tx_hash + struct.pack('<I', nout)
)
_apply_constraints_for_array_attributes(constraints, 'tag')
_apply_constraints_for_array_attributes(constraints, 'language')
_apply_constraints_for_array_attributes(constraints, 'location')
select = f"SELECT {cols} FROM claim"
sql, values = query(
select if not join else select+"""
LEFT JOIN claimtrie USING (claim_hash)
LEFT JOIN claim as channel ON (claim.channel_hash=channel.claim_hash)
""", **constraints
)
try:
return self.db.execute(sql, values).fetchall()
except:
self.logger.exception(f'Failed to execute claim search query: {sql}')
raise
def validate_channel_signatures(self, height, new_claims, updated_claims, spent_claims):
if not new_claims and not updated_claims and not spent_claims:
return
channels, new_channel_keys, signables = {}, {}, {}
for txo in chain(new_claims, updated_claims):
try:
claim = txo.claim
except:
continue
if claim.is_channel:
channels[txo.claim_hash] = txo
new_channel_keys[txo.claim_hash] = claim.channel.public_key_bytes
else:
signables[txo.claim_hash] = txo
missing_channel_keys = set()
for txo in signables.values():
claim = txo.claim
if claim.is_signed and claim.signing_channel_hash not in new_channel_keys:
missing_channel_keys.add(claim.signing_channel_hash)
all_channel_keys = {}
if new_channel_keys or missing_channel_keys:
all_channel_keys = dict(self.execute(*query(
"SELECT claim_hash, public_key_bytes FROM claim",
claim_hash__in=[
sqlite3.Binary(channel_hash) for channel_hash in
set(new_channel_keys) | missing_channel_keys
]
)))
changed_channel_keys = {}
for claim_hash, new_key in new_channel_keys.items():
if claim_hash not in all_channel_keys or all_channel_keys[claim_hash] != new_key:
all_channel_keys[claim_hash] = new_key
changed_channel_keys[claim_hash] = new_key
claim_updates = []
for claim_hash, txo in signables.items():
claim = txo.claim
update = {
'claim_hash': sqlite3.Binary(claim_hash),
'channel_hash': None,
'signature': None,
'signature_digest': None,
'is_channel_signature_valid': False
}
if claim.is_signed:
update.update({
'channel_hash': sqlite3.Binary(claim.signing_channel_hash),
'signature': sqlite3.Binary(txo.get_encoded_signature()),
'signature_digest': sqlite3.Binary(txo.get_signature_digest(self.ledger))
})
claim_updates.append(update)
if changed_channel_keys:
sql = f"""
SELECT * FROM claim WHERE
channel_hash IN ({','.join('?' for _ in changed_channel_keys)}) AND
signature IS NOT NULL
"""
for affected_claim in self.execute(sql, [sqlite3.Binary(h) for h in changed_channel_keys]):
if affected_claim['claim_hash'] not in signables:
claim_updates.append({
'claim_hash': sqlite3.Binary(affected_claim['claim_hash']),
'channel_hash': sqlite3.Binary(affected_claim['channel_hash']),
'signature': sqlite3.Binary(affected_claim['signature']),
'signature_digest': sqlite3.Binary(affected_claim['signature_digest']),
'is_channel_signature_valid': False
})
for update in claim_updates:
channel_pub_key = all_channel_keys.get(update['channel_hash'])
if channel_pub_key and update['signature']:
update['is_channel_signature_valid'] = Output.is_signature_valid(
bytes(update['signature']), bytes(update['signature_digest']), channel_pub_key
)
if claim_updates:
self.db.executemany(f"""
UPDATE claim SET
channel_hash=:channel_hash, signature=:signature, signature_digest=:signature_digest,
is_channel_signature_valid=:is_channel_signature_valid,
channel_join=CASE
WHEN is_channel_signature_valid AND :is_channel_signature_valid THEN channel_join
WHEN :is_channel_signature_valid THEN {height}
END,
canonical_url=CASE
WHEN is_channel_signature_valid AND :is_channel_signature_valid THEN canonical_url
WHEN :is_channel_signature_valid THEN
(SELECT short_url FROM claim WHERE claim_hash=:channel_hash)||'/'||
claim_name||COALESCE(
(SELECT shortest_id(other_claim.claim_id, claim.claim_id) FROM claim AS other_claim
WHERE other_claim.normalized = claim.normalized AND
other_claim.channel_hash = :channel_hash AND
other_claim.is_channel_signature_valid = 1),
'#'||substr(claim_id, 1, 1)
)
END
WHERE claim_hash=:claim_hash;
""", claim_updates)
if spent_claims:
self.execute(
f"""
UPDATE claim SET is_channel_signature_valid=0, channel_join=NULL, canonical_url=NULL
WHERE channel_hash IN ({','.join('?' for _ in spent_claims)})
""", [sqlite3.Binary(cid) for cid in spent_claims]
)
if channels:
self.db.executemany(
"UPDATE claim SET public_key_bytes=:public_key_bytes WHERE claim_hash=:claim_hash", [{
'claim_hash': sqlite3.Binary(claim_hash),
'public_key_bytes': sqlite3.Binary(txo.claim.channel.public_key_bytes)
} for claim_hash, txo in channels.items()]
)
if all_channel_keys:
self.db.executemany(f"""
UPDATE claim SET
claims_in_channel=(
SELECT COUNT(*) FROM claim AS claim_in_channel
WHERE claim_in_channel.channel_hash=claim.claim_hash AND
claim_in_channel.is_channel_signature_valid
)
WHERE claim_hash = ?
""", [(sqlite3.Binary(channel_hash),) for channel_hash in all_channel_keys.keys()])
def _get_claims(cols, for_count=False, **constraints) -> Tuple[str, Dict]:
if 'order_by' in constraints:
sql_order_by = []
for order_by in constraints['order_by']:
is_asc = order_by.startswith('^')
column = order_by[1:] if is_asc else order_by
if column not in ORDER_FIELDS:
raise NameError(f'{column} is not a valid order_by field')
if column == 'name':
column = 'normalized'
sql_order_by.append(
f"claim.{column} ASC" if is_asc else f"claim.{column} DESC")
constraints['order_by'] = sql_order_by
ops = {'<=': '__lte', '>=': '__gte', '<': '__lt', '>': '__gt'}
for constraint in INTEGER_PARAMS:
if constraint in constraints:
value = constraints.pop(constraint)
postfix = ''
if isinstance(value, str):
if len(value) >= 2 and value[:2] in ops:
postfix, value = ops[value[:2]], value[2:]
elif len(value) >= 1 and value[0] in ops:
postfix, value = ops[value[0]], value[1:]
if constraint == 'fee_amount':
value = Decimal(value) * 1000
constraints[f'claim.{constraint}{postfix}'] = int(value)
if constraints.pop('is_controlling', False):
if {'sequence', 'amount_order'}.isdisjoint(constraints):
for_count = False
constraints['claimtrie.claim_hash__is_not_null'] = ''
if 'sequence' in constraints:
constraints['order_by'] = 'claim.activation_height ASC'
constraints['offset'] = int(constraints.pop('sequence')) - 1
constraints['limit'] = 1
if 'amount_order' in constraints:
constraints['order_by'] = 'claim.effective_amount DESC'
constraints['offset'] = int(constraints.pop('amount_order')) - 1
constraints['limit'] = 1
if 'claim_id' in constraints:
claim_id = constraints.pop('claim_id')
if len(claim_id) == 40:
constraints['claim.claim_id'] = claim_id
else:
constraints['claim.claim_id__like'] = f'{claim_id[:40]}%'
elif 'claim_ids' in constraints:
constraints['claim.claim_id__in'] = constraints.pop('claim_ids')
if 'reposted_claim_id' in constraints:
constraints['claim.reposted_claim_hash'] = sqlite3.Binary(
unhexlify(constraints.pop('reposted_claim_id'))[::-1])
if 'name' in constraints:
constraints['claim.normalized'] = normalize_name(
constraints.pop('name'))
if 'public_key_id' in constraints:
constraints['claim.public_key_hash'] = sqlite3.Binary(
ctx.get().ledger.address_to_hash160(
constraints.pop('public_key_id')))
if 'channel_hash' in constraints:
constraints['claim.channel_hash'] = sqlite3.Binary(
constraints.pop('channel_hash'))
if 'channel_ids' in constraints:
channel_ids = constraints.pop('channel_ids')
if channel_ids:
constraints['claim.channel_hash__in'] = [
sqlite3.Binary(unhexlify(cid)[::-1]) for cid in channel_ids
]
if 'not_channel_ids' in constraints:
not_channel_ids = constraints.pop('not_channel_ids')
if not_channel_ids:
not_channel_ids_binary = [
sqlite3.Binary(unhexlify(ncid)[::-1])
for ncid in not_channel_ids
]
if constraints.get('has_channel_signature', False):
constraints[
'claim.channel_hash__not_in'] = not_channel_ids_binary
else:
constraints['null_or_not_channel__or'] = {
'claim.signature_valid__is_null': True,
'claim.channel_hash__not_in': not_channel_ids_binary
}
if 'blocklist_channel_ids' in constraints:
blocklist_ids = constraints.pop('blocklist_channel_ids')
if blocklist_ids:
blocking_channels = [
sqlite3.Binary(unhexlify(channel_id)[::-1])
for channel_id in blocklist_ids
]
constraints.update({
f'$blocking_channel{i}': a
for i, a in enumerate(blocking_channels)
})
blocklist = ', '.join([
f':$blocking_channel{i}' for i in range(len(blocking_channels))
])
constraints[
'claim.claim_hash__not_in#blocklist_channel_ids'] = f"""
SELECT reposted_claim_hash FROM claim WHERE channel_hash IN ({blocklist})
"""
if 'signature_valid' in constraints:
has_channel_signature = constraints.pop('has_channel_signature', False)
if has_channel_signature:
constraints['claim.signature_valid'] = constraints.pop(
'signature_valid')
else:
constraints['null_or_signature__or'] = {
'claim.signature_valid__is_null': True,
'claim.signature_valid': constraints.pop('signature_valid')
}
elif constraints.pop('has_channel_signature', False):
constraints['claim.signature_valid__is_not_null'] = True
if 'txid' in constraints:
tx_hash = unhexlify(constraints.pop('txid'))[::-1]
nout = constraints.pop('nout', 0)
constraints['claim.txo_hash'] = sqlite3.Binary(tx_hash +
struct.pack('<I', nout))
if 'claim_type' in constraints:
constraints['claim.claim_type'] = CLAIM_TYPES[constraints.pop(
'claim_type')]
if 'stream_types' in constraints:
stream_types = constraints.pop('stream_types')
if stream_types:
constraints['claim.stream_type__in'] = [
STREAM_TYPES[stream_type] for stream_type in stream_types
]
if 'media_types' in constraints:
media_types = constraints.pop('media_types')
if media_types:
constraints['claim.media_type__in'] = media_types
if 'fee_currency' in constraints:
constraints['claim.fee_currency'] = constraints.pop(
'fee_currency').lower()
_apply_constraints_for_array_attributes(constraints, 'tag', clean_tags,
for_count)
_apply_constraints_for_array_attributes(constraints, 'language',
lambda _: _, for_count)
_apply_constraints_for_array_attributes(constraints, 'location',
lambda _: _, for_count)
if 'text' in constraints:
constraints["search"] = constraints.pop("text")
constraints["order_by"] = FTS_ORDER_BY
select = f"SELECT {cols} FROM search JOIN claim ON (search.rowid=claim.rowid)"
else:
select = f"SELECT {cols} FROM claim"
if not for_count:
select += " LEFT JOIN claimtrie USING (claim_hash)"
return query(select, **constraints)
