Skip to content

Commit 78e45ea

Browse files
simonwsimonw
committed
New ?tags__arraycontains=tag lookup against JSON fields
Part one of supporting facet-by-JSON-array, refs #359
1 parent 3a208a4 commit 78e45ea

File tree

6 files changed

+74
-41
lines changed

6 files changed

+74
-41
lines changed

‎datasette/utils.py‎

Lines changed: 21 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -565,6 +565,16 @@ def detect_fts_sql(table):
565565
'''.format(table=table)
566566

567567

568+
def detect_json1(conn=None):
569+
if conn is None:
570+
conn = sqlite3.connect(":memory:")
571+
try:
572+
conn.execute("SELECT json('{}')")
573+
return True
574+
except Exception:
575+
return False
576+
577+
568578
def table_columns(conn, table):
569579
return [
570580
r[1]
@@ -584,7 +594,7 @@ def __init__(self, key, display, sql_template, human_template, format='{}', nume
584594
self.numeric = numeric
585595
self.no_argument = no_argument
586596

587-
def where_clause(self, column, value, param_counter):
597+
def where_clause(self, table, column, value, param_counter):
588598
converted = self.format.format(value)
589599
if self.numeric and converted.isdigit():
590600
converted = int(converted)
@@ -597,6 +607,7 @@ def where_clause(self, column, value, param_counter):
597607
kwargs = {
598608
'c': column,
599609
'p': 'p{}'.format(param_counter),
610+
't': table,
600611
}
601612
return self.sql_template.format(**kwargs), converted
602613

@@ -613,6 +624,7 @@ def human_clause(self, column, value):
613624

614625
class Filters:
615626
_filters = [
627+
# key, display, sql_template, human_template, format=, numeric=, no_argument=
616628
Filter('exact', '=', '"{c}" = :{p}', lambda c, v: '{c} = {v}' if v.isdigit() else '{c} = "{v}"'),
617629
Filter('not', '!=', '"{c}" != :{p}', lambda c, v: '{c} != {v}' if v.isdigit() else '{c} != "{v}"'),
618630
Filter('contains', 'contains', '"{c}" like :{p}', '{c} contains "{v}"', format='%{}%'),
@@ -624,6 +636,11 @@ class Filters:
624636
Filter('lte', '\u2264', '"{c}" <= :{p}', '{c} \u2264 {v}', numeric=True),
625637
Filter('glob', 'glob', '"{c}" glob :{p}', '{c} glob "{v}"'),
626638
Filter('like', 'like', '"{c}" like :{p}', '{c} like "{v}"'),
639+
] + ([Filter('arraycontains', 'array contains', """rowid in (
640+
select {t}.rowid from {t}, json_each({t}.{c}) j
641+
where j.value = :{p}
642+
)""", '{c} contains "{v}"')
643+
] if detect_json1() else []) + [
627644
Filter('isnull', 'is null', '"{c}" is null', '{c} is null', no_argument=True),
628645
Filter('notnull', 'is not null', '"{c}" is not null', '{c} is not null', no_argument=True),
629646
Filter('isblank', 'is blank', '("{c}" is null or "{c}" = "")', '{c} is blank', no_argument=True),
@@ -677,7 +694,7 @@ def has_selections(self):
677694
return bool(self.pairs)
678695

679696
def convert_unit(self, column, value):
680-
"If the user has provided a unit in the quey, convert it into the column unit, if present."
697+
"If the user has provided a unit in the query, convert it into the column unit, if present."
681698
if column not in self.units:
682699
return value
683700

@@ -690,13 +707,13 @@ def convert_unit(self, column, value):
690707
column_unit = self.ureg(self.units[column])
691708
return value.to(column_unit).magnitude
692709

693-
def build_where_clauses(self):
710+
def build_where_clauses(self, table):
694711
sql_bits = []
695712
params = {}
696713
for i, (column, lookup, value) in enumerate(self.selections()):
697714
filter = self._filters_by_key.get(lookup, None)
698715
if filter:
699-
sql_bit, param = filter.where_clause(column, self.convert_unit(column, value), i)
716+
sql_bit, param = filter.where_clause(table, column, self.convert_unit(column, value), i)
700717
sql_bits.append(sql_bit)
701718
if param is not None:
702719
param_id = 'p{}'.format(i)

‎datasette/views/table.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -293,7 +293,7 @@ async def data(self, request, database, hash, table, default_labels=False, _nex
293293
table_metadata = self.ds.table_metadata(database, table)
294294
units = table_metadata.get("units", {})
295295
filters = Filters(sorted(other_args.items()), units, ureg)
296-
where_clauses, params = filters.build_where_clauses()
296+
where_clauses, params = filters.build_where_clauses(table)
297297

298298
# _search support:
299299
fts_table = await self.ds.execute_against_connection_in_thread(

‎tests/fixtures.py‎

Lines changed: 17 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -523,26 +523,27 @@ def render_cell(value, database):
523523
state text,
524524
city_id integer,
525525
neighborhood text,
526+
tags text,
526527
FOREIGN KEY ("city_id") REFERENCES [facet_cities](id)
527528
);
528529
INSERT INTO facetable
529-
(planet_int, on_earth, state, city_id, neighborhood)
530+
(planet_int, on_earth, state, city_id, neighborhood, tags)
530531
VALUES
531-
(1, 1, 'CA', 1, 'Mission'),
532-
(1, 1, 'CA', 1, 'Dogpatch'),
533-
(1, 1, 'CA', 1, 'SOMA'),
534-
(1, 1, 'CA', 1, 'Tenderloin'),
535-
(1, 1, 'CA', 1, 'Bernal Heights'),
536-
(1, 1, 'CA', 1, 'Hayes Valley'),
537-
(1, 1, 'CA', 2, 'Hollywood'),
538-
(1, 1, 'CA', 2, 'Downtown'),
539-
(1, 1, 'CA', 2, 'Los Feliz'),
540-
(1, 1, 'CA', 2, 'Koreatown'),
541-
(1, 1, 'MI', 3, 'Downtown'),
542-
(1, 1, 'MI', 3, 'Greektown'),
543-
(1, 1, 'MI', 3, 'Corktown'),
544-
(1, 1, 'MI', 3, 'Mexicantown'),
545-
(2, 0, 'MC', 4, 'Arcadia Planitia')
532+
(1, 1, 'CA', 1, 'Mission', '["tag1", "tag2"]'),
533+
(1, 1, 'CA', 1, 'Dogpatch', '["tag1", "tag3"]'),
534+
(1, 1, 'CA', 1, 'SOMA', '[]'),
535+
(1, 1, 'CA', 1, 'Tenderloin', '[]'),
536+
(1, 1, 'CA', 1, 'Bernal Heights', '[]'),
537+
(1, 1, 'CA', 1, 'Hayes Valley', '[]'),
538+
(1, 1, 'CA', 2, 'Hollywood', '[]'),
539+
(1, 1, 'CA', 2, 'Downtown', '[]'),
540+
(1, 1, 'CA', 2, 'Los Feliz', '[]'),
541+
(1, 1, 'CA', 2, 'Koreatown', '[]'),
542+
(1, 1, 'MI', 3, 'Downtown', '[]'),
543+
(1, 1, 'MI', 3, 'Greektown', '[]'),
544+
(1, 1, 'MI', 3, 'Corktown', '[]'),
545+
(1, 1, 'MI', 3, 'Mexicantown', '[]'),
546+
(2, 0, 'MC', 4, 'Arcadia Planitia', '[]')
546547
;
547548
548549
INSERT INTO simple_primary_key VALUES (1, 'hello');

‎tests/test_api.py‎

Lines changed: 18 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,4 @@
1+
from datasette.utils import detect_json1
12
from .fixtures import ( # noqa
23
app_client,
34
app_client_no_files,
@@ -115,7 +116,7 @@ def test_database_page(app_client):
115116
'hidden': False,
116117
'primary_keys': ['id'],
117118
}, {
118-
'columns': ['pk', 'planet_int', 'on_earth', 'state', 'city_id', 'neighborhood'],
119+
'columns': ['pk', 'planet_int', 'on_earth', 'state', 'city_id', 'neighborhood', 'tags'],
119120
'name': 'facetable',
120121
'count': 15,
121122
'foreign_keys': {
@@ -882,6 +883,18 @@ def test_table_filter_queries(app_client, path, expected_rows):
882883
assert expected_rows == response.json['rows']
883884

884885

886+
@pytest.mark.skipif(
887+
not detect_json1(),
888+
reason="Requires the SQLite json1 module"
889+
)
890+
def test_table_filter_json_arraycontains(app_client):
891+
response = app_client.get("/fixtures/facetable.json?tags__arraycontains=tag1")
892+
assert [
893+
[1, 1, 1, 'CA', 1, 'Mission', '["tag1", "tag2"]'],
894+
[2, 1, 1, 'CA', 1, 'Dogpatch', '["tag1", "tag3"]']
895+
] == response.json['rows']
896+
897+
885898
def test_max_returned_rows(app_client):
886899
response = app_client.get(
887900
'/fixtures.json?sql=select+content+from+no_primary_key'
@@ -1244,7 +1257,8 @@ def test_expand_labels(app_client):
12441257
"value": 1,
12451258
"label": "San Francisco"
12461259
},
1247-
"neighborhood": "Dogpatch"
1260+
"neighborhood": "Dogpatch",
1261+
"tags": '["tag1", "tag3"]'
12481262
},
12491263
"13": {
12501264
"pk": 13,
@@ -1255,7 +1269,8 @@ def test_expand_labels(app_client):
12551269
"value": 3,
12561270
"label": "Detroit"
12571271
},
1258-
"neighborhood": "Corktown"
1272+
"neighborhood": "Corktown",
1273+
"tags": '[]',
12591274
}
12601275
} == response.json
12611276

‎tests/test_csv.py‎

Lines changed: 16 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -17,22 +17,22 @@
1717
'''.replace('\n', '\r\n')
1818

1919
EXPECTED_TABLE_WITH_LABELS_CSV = '''
20-
pk,planet_int,on_earth,state,city_id,city_id_label,neighborhood
21-
1,1,1,CA,1,San Francisco,Mission
22-
2,1,1,CA,1,San Francisco,Dogpatch
23-
3,1,1,CA,1,San Francisco,SOMA
24-
4,1,1,CA,1,San Francisco,Tenderloin
25-
5,1,1,CA,1,San Francisco,Bernal Heights
26-
6,1,1,CA,1,San Francisco,Hayes Valley
27-
7,1,1,CA,2,Los Angeles,Hollywood
28-
8,1,1,CA,2,Los Angeles,Downtown
29-
9,1,1,CA,2,Los Angeles,Los Feliz
30-
10,1,1,CA,2,Los Angeles,Koreatown
31-
11,1,1,MI,3,Detroit,Downtown
32-
12,1,1,MI,3,Detroit,Greektown
33-
13,1,1,MI,3,Detroit,Corktown
34-
14,1,1,MI,3,Detroit,Mexicantown
35-
15,2,0,MC,4,Memnonia,Arcadia Planitia
20+
pk,planet_int,on_earth,state,city_id,city_id_label,neighborhood,tags
21+
1,1,1,CA,1,San Francisco,Mission,"[""tag1"", ""tag2""]"
22+
2,1,1,CA,1,San Francisco,Dogpatch,"[""tag1"", ""tag3""]"
23+
3,1,1,CA,1,San Francisco,SOMA,[]
24+
4,1,1,CA,1,San Francisco,Tenderloin,[]
25+
5,1,1,CA,1,San Francisco,Bernal Heights,[]
26+
6,1,1,CA,1,San Francisco,Hayes Valley,[]
27+
7,1,1,CA,2,Los Angeles,Hollywood,[]
28+
8,1,1,CA,2,Los Angeles,Downtown,[]
29+
9,1,1,CA,2,Los Angeles,Los Feliz,[]
30+
10,1,1,CA,2,Los Angeles,Koreatown,[]
31+
11,1,1,MI,3,Detroit,Downtown,[]
32+
12,1,1,MI,3,Detroit,Greektown,[]
33+
13,1,1,MI,3,Detroit,Corktown,[]
34+
14,1,1,MI,3,Detroit,Mexicantown,[]
35+
15,2,0,MC,4,Memnonia,Arcadia Planitia,[]
3636
'''.lstrip().replace('\n', '\r\n')
3737

3838

‎tests/test_utils.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -187,7 +187,7 @@ def test_custom_json_encoder(obj, expected):
187187
])
188188
def test_build_where(args, expected_where, expected_params):
189189
f = utils.Filters(sorted(args.items()))
190-
sql_bits, actual_params = f.build_where_clauses()
190+
sql_bits, actual_params = f.build_where_clauses("table")
191191
assert expected_where == sql_bits
192192
assert {
193193
'p{}'.format(i): param

0 commit comments

Comments
 (0)