1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
|
from datasette.filters import Filters, through_filters, where_filters, search_filters
from datasette.utils.asgi import Request
from .fixtures import app_client
import pytest
@pytest.mark.parametrize(
"args,expected_where,expected_params",
[
((("name_english__contains", "foo"),), ['"name_english" like :p0'], ["%foo%"]),
(
(("foo", "bar"), ("bar__contains", "baz")),
['"bar" like :p0', '"foo" = :p1'],
["%baz%", "bar"],
),
(
(("foo__startswith", "bar"), ("bar__endswith", "baz")),
['"bar" like :p0', '"foo" like :p1'],
["%baz", "bar%"],
),
(
(("foo__lt", "1"), ("bar__gt", "2"), ("baz__gte", "3"), ("bax__lte", "4")),
['"bar" > :p0', '"bax" <= :p1', '"baz" >= :p2', '"foo" < :p3'],
[2, 4, 3, 1],
),
(
(("foo__like", "2%2"), ("zax__glob", "3*")),
['"foo" like :p0', '"zax" glob :p1'],
["2%2", "3*"],
),
# Multiple like arguments:
(
(("foo__like", "2%2"), ("foo__like", "3%3")),
['"foo" like :p0', '"foo" like :p1'],
["2%2", "3%3"],
),
# notlike:
(
(("foo__notlike", "2%2"),),
['"foo" not like :p0'],
["2%2"],
),
(
(("foo__isnull", "1"), ("baz__isnull", "1"), ("bar__gt", "10")),
['"bar" > :p0', '"baz" is null', '"foo" is null'],
[10],
),
((("foo__in", "1,2,3"),), ["foo in (:p0, :p1, :p2)"], ["1", "2", "3"]),
# date
((("foo__date", "1988-01-01"),), ['date("foo") = :p0'], ["1988-01-01"]),
# JSON array variants of __in (useful for unexpected characters)
((("foo__in", "[1,2,3]"),), ["foo in (:p0, :p1, :p2)"], [1, 2, 3]),
(
(("foo__in", '["dog,cat", "cat[dog]"]'),),
["foo in (:p0, :p1)"],
["dog,cat", "cat[dog]"],
),
# Not in, and JSON array not in
((("foo__notin", "1,2,3"),), ["foo not in (:p0, :p1, :p2)"], ["1", "2", "3"]),
((("foo__notin", "[1,2,3]"),), ["foo not in (:p0, :p1, :p2)"], [1, 2, 3]),
# JSON arraycontains, arraynotcontains
(
(("Availability+Info__arraycontains", "yes"),),
[":p0 in (select value from json_each([table].[Availability+Info]))"],
["yes"],
),
(
(("Availability+Info__arraynotcontains", "yes"),),
[":p0 not in (select value from json_each([table].[Availability+Info]))"],
["yes"],
),
],
)
def test_build_where(args, expected_where, expected_params):
f = Filters(sorted(args))
sql_bits, actual_params = f.build_where_clauses("table")
assert expected_where == sql_bits
assert {f"p{i}": param for i, param in enumerate(expected_params)} == actual_params
@pytest.mark.asyncio
async def test_through_filters_from_request(app_client):
request = Request.fake(
'/?_through={"table":"roadside_attraction_characteristics","column":"characteristic_id","value":"1"}'
)
filter_args = await through_filters(
request=request,
datasette=app_client.ds,
table="roadside_attractions",
database="fixtures",
)()
assert filter_args.where_clauses == [
"pk in (select attraction_id from roadside_attraction_characteristics where characteristic_id = :p0)"
]
assert filter_args.params == {"p0": "1"}
assert filter_args.human_descriptions == [
'roadside_attraction_characteristics.characteristic_id = "1"'
]
assert filter_args.extra_context == {}
@pytest.mark.asyncio
async def test_through_filters_from_request(app_client):
request = Request.fake(
'/?_through={"table":"roadside_attraction_characteristics","column":"characteristic_id","value":"1"}'
)
filter_args = await through_filters(
request=request,
datasette=app_client.ds,
table="roadside_attractions",
database="fixtures",
)()
assert filter_args.where_clauses == [
"pk in (select attraction_id from roadside_attraction_characteristics where characteristic_id = :p0)"
]
assert filter_args.params == {"p0": "1"}
assert filter_args.human_descriptions == [
'roadside_attraction_characteristics.characteristic_id = "1"'
]
assert filter_args.extra_context == {}
@pytest.mark.asyncio
async def test_where_filters_from_request(app_client):
request = Request.fake("/?_where=pk+>+3")
filter_args = await where_filters(
request=request,
datasette=app_client.ds,
database="fixtures",
)()
assert filter_args.where_clauses == ["pk > 3"]
assert filter_args.params == {}
assert filter_args.human_descriptions == []
assert filter_args.extra_context == {
"extra_wheres_for_ui": [{"text": "pk > 3", "remove_url": "/"}]
}
@pytest.mark.asyncio
async def test_search_filters_from_request(app_client):
request = Request.fake("/?_search=bobcat")
filter_args = await search_filters(
request=request,
datasette=app_client.ds,
database="fixtures",
table="searchable",
)()
assert filter_args.where_clauses == [
"rowid in (select rowid from searchable_fts where searchable_fts match escape_fts(:search))"
]
assert filter_args.params == {"search": "bobcat"}
assert filter_args.human_descriptions == ['search matches "bobcat"']
assert filter_args.extra_context == {"supports_search": True, "search": "bobcat"}
|
