def test_not_in(self):
self.assertEqual(constraints_to_sql({'txo.age__not_in': [18, 38]}),
('txo.age NOT IN (18, 38)', {}))
self.assertEqual(
constraints_to_sql(
{'txo.age__not_in': 'SELECT age from ages_table'}),
('txo.age NOT IN (SELECT age from ages_table)', {}))
def test_dot(self):
self.assertEqual(constraints_to_sql({'txo.position': 18}),
('txo.position = :txo_position0', {
'txo_position0': 18
}))
self.assertEqual(constraints_to_sql({'txo.position#6': 18}),
('txo.position = :txo_position6', {
'txo_position6': 18
}))
def test_in(self):
self.assertEqual(constraints_to_sql({'txo.age__in': [18, 38]}),
('txo.age IN (18, 38)', {}))
self.assertEqual(
constraints_to_sql({'txo.age__in': ['abc123', 'def456']}),
("txo.age IN ('abc123', 'def456')", {}))
self.assertEqual(
constraints_to_sql({'txo.age__in': 'SELECT age from ages_table'}),
('txo.age IN (SELECT age from ages_table)', {}))
def test_in(self):
self.assertEqual(constraints_to_sql({'txo.age__in#2': [18, 38]}),
('txo.age IN (:txo_age__in2_0, :txo_age__in2_1)', {
'txo_age__in2_0': 18,
'txo_age__in2_1': 38
}))
self.assertEqual(
constraints_to_sql({'txo.name__in': ('abc123', 'def456')}),
('txo.name IN (:txo_name__in0_0, :txo_name__in0_1)', {
'txo_name__in0_0': 'abc123',
'txo_name__in0_1': 'def456'
}))
self.assertEqual(
constraints_to_sql({'txo.age__in': 'SELECT age from ages_table'}),
('txo.age IN (SELECT age from ages_table)', {}))
def fts_action_sql(claims=None, action='insert'):
select = {
'rowid':
"claim.rowid",
'claim_name':
"claim.normalized",
'channel_name':
"channel.normalized",
'title':
"claim.title",
'description':
"claim.description",
'author':
"claim.author",
'tags':
"(select group_concat(tag, ' ') from tag where tag.claim_hash=claim.claim_hash)"
}
if action == 'delete':
select['search'] = '"delete"'
where, values = "", {}
if claims:
where, values = constraints_to_sql({
'claim.claim_hash__in':
[sqlite3.Binary(claim_hash) for claim_hash in claims]
})
where = 'WHERE ' + where
return f"""
INSERT INTO search ({','.join(select.keys())})
SELECT {','.join(select.values())} FROM claim
LEFT JOIN claim as channel ON (claim.channel_hash=channel.claim_hash) {where}
""", values
def test_any(self):
self.assertEqual(
constraints_to_sql(
{'ages__any': {
'txo.age__gt': 18,
'txo.age__lt': 38
}}),
('(txo.age > :ages__any0_txo_age__gt0 OR txo.age < :ages__any0_txo_age__lt0)',
{
'ages__any0_txo_age__gt0': 18,
'ages__any0_txo_age__lt0': 38
}))
def _delete_sql(table: str, constraints: dict) -> Tuple[str, dict]:
where, values = constraints_to_sql(constraints)
return f"DELETE FROM {table} WHERE {where}", values
def test_in_invalid(self):
with self.assertRaisesRegex(ValueError, 'list, set or string'):
constraints_to_sql({'ages__in': 9})