def test_query_limit_offset(self): self.assertTupleEqual(query("select * from foo", limit=10), ("select * from foo LIMIT 10", {})) self.assertTupleEqual(query("select * from foo", offset=10), ("select * from foo OFFSET 10", {})) self.assertTupleEqual(query("select * from foo", limit=20, offset=10), ("select * from foo LIMIT 20 OFFSET 10", {}))
def test_query_order_by(self): self.assertTupleEqual(query("select * from foo", order_by='foo'), ("select * from foo ORDER BY foo", {})) self.assertTupleEqual( 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=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(self): self.assertTupleEqual( query("select * from foo"), ("select * from foo", {}) ) self.assertTupleEqual( 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: affected_channels = self.execute(*query( "SELECT channel_hash FROM claim", channel_hash__is_not_null=1, claim_hash__in=claim_hashes )).fetchall() for table in ('claim', 'support', 'claimtrie'): self.execute(*self._delete_sql(table, {'claim_hash__in': claim_hashes})) self._clear_claim_metadata(claim_hashes) return {r.channel_hash for r in affected_channels} return set()
def update_claims_from_channel_hashes(self, shared_streams, shared_channels, channel_hashes): streams, channels = {}, {} if channel_hashes: sql = query( "SELECT repost.channel_hash, repost.reposted_claim_hash, target.claim_type " "FROM claim as repost JOIN claim AS target ON (target.claim_hash=repost.reposted_claim_hash)", **{ 'repost.reposted_claim_hash__is_not_null': 1, 'repost.channel_hash__in': channel_hashes } ) for blocked_claim in self.execute(*sql): if blocked_claim.claim_type == CLAIM_TYPES['stream']: streams[blocked_claim.reposted_claim_hash] = blocked_claim.channel_hash elif blocked_claim.claim_type == CLAIM_TYPES['channel']: channels[blocked_claim.reposted_claim_hash] = blocked_claim.channel_hash shared_streams.clear() shared_streams.update(streams) shared_channels.clear() shared_channels.update(channels)
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=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': claim_hash, 'channel_hash': None, 'signature': None, 'signature_digest': None, 'signature_valid': None } if claim.is_signed: update.update({ 'channel_hash': claim.signing_channel_hash, 'signature': txo.get_encoded_signature(), 'signature_digest': 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, changed_channel_keys.keys()): if affected_claim.claim_hash not in signables: claim_updates.append({ 'claim_hash': affected_claim.claim_hash, 'channel_hash': affected_claim.channel_hash, 'signature': affected_claim.signature, 'signature_digest': 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.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)}) """, spent_claims ) sub_timer.stop() sub_timer = timer.add_timer('update channels') sub_timer.start() if channels: self.executemany( """ UPDATE claim SET public_key_bytes=:public_key_bytes, public_key_hash=:public_key_hash WHERE claim_hash=:claim_hash""", [{ 'claim_hash': claim_hash, 'public_key_bytes': txo.claim.channel.public_key_bytes, 'public_key_hash': 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.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 = ? """, [(channel_hash,) for channel_hash in all_channel_keys.keys()]) sub_timer.stop() sub_timer = timer.add_timer('update blocked claims list') sub_timer.start() if (self.blocking_channel_hashes.intersection(all_channel_keys) or self.filtering_channel_hashes.intersection(all_channel_keys)): self.update_blocked_and_filtered_claims() sub_timer.stop()
def claims_query(cols, for_count=False, **constraints) -> Tuple[str, Dict]: if 'order_by' in constraints: order_by_parts = constraints['order_by'] if isinstance(order_by_parts, str): order_by_parts = [order_by_parts] sql_order_by = [] for order_by in order_by_parts: 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'] = set(constraints.pop('claim_ids')) if 'reposted_claim_id' in constraints: constraints['claim.reposted_claim_hash'] = 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'] = ( ctx.get().ledger.address_to_hash160(constraints.pop('public_key_id'))) if 'channel_hash' in constraints: constraints['claim.channel_hash'] = constraints.pop('channel_hash') if 'channel_ids' in constraints: channel_ids = constraints.pop('channel_ids') if channel_ids: constraints['claim.channel_hash__in'] = { unhexlify(cid)[::-1] for cid in channel_ids if cid } if 'not_channel_ids' in constraints: not_channel_ids = constraints.pop('not_channel_ids') if not_channel_ids: not_channel_ids_binary = { unhexlify(ncid)[::-1] for ncid in not_channel_ids } constraints['claim.claim_hash__not_in#not_channel_ids'] = not_channel_ids_binary 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'] = tx_hash + struct.pack('<I', nout) if 'claim_type' in constraints: claim_types = constraints.pop('claim_type') if isinstance(claim_types, str): claim_types = [claim_types] if claim_types: constraints['claim.claim_type__in'] = { CLAIM_TYPES[claim_type] for claim_type in claim_types } 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'] = set(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)