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)