def test_path_with_removed_args(path, args, expected):
request = Request.fake(path)
actual = utils.path_with_removed_args(request, args)
assert expected == actual
# Run the test again but this time use the path= argument
request = Request.fake("/")
actual = utils.path_with_removed_args(request, args, path=path)
assert expected == actual
def test_request_url_vars():
scope = {
"http_version": "1.1",
"method": "POST",
"path": "/",
"raw_path": b"/",
"query_string": b"",
"scheme": "http",
"type": "http",
"headers": [[b"content-type", b"application/x-www-form-urlencoded"]],
}
assert {} == Request(scope, None).url_vars
assert {"name": "cleo"} == Request(
dict(scope, url_route={"kwargs": {"name": "cleo"}}), None
).url_vars
async def test_column_facet_suggest_skip_if_already_selected(app_client):
facet = ColumnFacet(
app_client.ds,
Request.fake("/?_facet=planet_int&_facet=on_earth"),
database="fixtures",
sql="select * from facetable",
table="facetable",
)
suggestions = await facet.suggest()
assert [
{
"name": "created",
"toggle_url": "http://localhost/?_facet=planet_int&_facet=on_earth&_facet=created",
},
{
"name": "state",
"toggle_url": "http://localhost/?_facet=planet_int&_facet=on_earth&_facet=state",
},
{
"name": "_city_id",
"toggle_url": "http://localhost/?_facet=planet_int&_facet=on_earth&_facet=_city_id",
},
{
"name": "_neighborhood",
"toggle_url": "http://localhost/?_facet=planet_int&_facet=on_earth&_facet=_neighborhood",
},
{
"name": "tags",
"toggle_url": "http://localhost/?_facet=planet_int&_facet=on_earth&_facet=tags",
},
{
"name": "complex_array",
"toggle_url": "http://localhost/?_facet=planet_int&_facet=on_earth&_facet=complex_array",
},
] == suggestions
def test_path_with_format_replace_format():
request = Request.fake("/foo/bar.csv")
assert (utils.path_with_format(
request=request, format="blob") == "/foo/bar.csv?_format=blob")
assert (utils.path_with_format(request=request,
format="blob",
replace_format="csv") == "/foo/bar.blob")
async def test_request_post_body():
scope = {
"http_version": "1.1",
"method": "POST",
"path": "/",
"raw_path": b"/",
"query_string": b"",
"scheme": "http",
"type": "http",
"headers": [[b"content-type", b"application/json"]],
}
data = {"hello": "world"}
async def receive():
return {
"type": "http.request",
"body": json.dumps(data, indent=4).encode("utf-8"),
"more_body": False,
}
request = Request(scope, receive)
body = await request.post_body()
assert isinstance(body, bytes)
assert data == json.loads(body)
def test_request_blank_values():
request = Request.fake("/?a=b&foo=bar&foo=bar2&baz=")
assert request.args._data == {
"a": ["b"],
"foo": ["bar", "bar2"],
"baz": [""]
}
def test_json_in_query_string_name():
query_string = (
'?_through.["roadside_attraction_characteristics"%2C"characteristic_id"]=1'
)
request = Request.fake("/" + query_string)
assert (request.args[
'_through.["roadside_attraction_characteristics","characteristic_id"]']
== "1")
async def test_date_facet_results(app_client):
facet = DateFacet(
app_client.ds,
Request.fake("/?_facet_date=created"),
database="fixtures",
sql="select * from facetable",
table="facetable",
)
buckets, timed_out = await facet.facet_results()
assert [] == timed_out
assert {
"created": {
"name":
"created",
"type":
"date",
"results": [
{
"value": "2019-01-14",
"label": "2019-01-14",
"count": 4,
"toggle_url":
"http://localhost/?_facet_date=created&created__date=2019-01-14",
"selected": False,
},
{
"value": "2019-01-15",
"label": "2019-01-15",
"count": 4,
"toggle_url":
"http://localhost/?_facet_date=created&created__date=2019-01-15",
"selected": False,
},
{
"value": "2019-01-17",
"label": "2019-01-17",
"count": 4,
"toggle_url":
"http://localhost/?_facet_date=created&created__date=2019-01-17",
"selected": False,
},
{
"value": "2019-01-16",
"label": "2019-01-16",
"count": 3,
"toggle_url":
"http://localhost/?_facet_date=created&created__date=2019-01-16",
"selected": False,
},
],
"hideable":
True,
"toggle_url":
"/",
"truncated":
False,
}
} == buckets
async def test_array_facet_suggest_not_if_all_empty_arrays(app_client):
facet = ArrayFacet(
app_client.ds,
Request.fake("/"),
database="fixtures",
sql="select * from facetable where tags = '[]'",
table="facetable",
)
suggestions = await facet.suggest()
assert [] == suggestions
async def test_column_facet_from_metadata_cannot_be_hidden(app_client):
facet = ColumnFacet(
app_client.ds,
Request.fake("/"),
database="fixtures",
sql="select * from facetable",
table="facetable",
metadata={"facets": ["city_id"]},
)
buckets, timed_out = await facet.facet_results()
assert [] == timed_out
assert {
"city_id": {
"name":
"city_id",
"type":
"column",
"hideable":
False,
"toggle_url":
"/",
"results": [
{
"value": 1,
"label": "San Francisco",
"count": 6,
"toggle_url": "http://localhost/?city_id=1",
"selected": False,
},
{
"value": 2,
"label": "Los Angeles",
"count": 4,
"toggle_url": "http://localhost/?city_id=2",
"selected": False,
},
{
"value": 3,
"label": "Detroit",
"count": 4,
"toggle_url": "http://localhost/?city_id=3",
"selected": False,
},
{
"value": 4,
"label": "Memnonia",
"count": 1,
"toggle_url": "http://localhost/?city_id=4",
"selected": False,
},
],
"truncated":
False,
}
} == buckets
async def test_array_facet_results(app_client):
facet = ArrayFacet(
app_client.ds,
Request.fake("/?_facet_array=tags"),
database="fixtures",
sql="select * from facetable",
table="facetable",
)
buckets, timed_out = await facet.facet_results()
assert [] == timed_out
assert {
"tags": {
"name":
"tags",
"type":
"array",
"results": [
{
"value": "tag1",
"label": "tag1",
"count": 2,
"toggle_url":
"http://localhost/?_facet_array=tags&tags__arraycontains=tag1",
"selected": False,
},
{
"value": "tag2",
"label": "tag2",
"count": 1,
"toggle_url":
"http://localhost/?_facet_array=tags&tags__arraycontains=tag2",
"selected": False,
},
{
"value": "tag3",
"label": "tag3",
"count": 1,
"toggle_url":
"http://localhost/?_facet_array=tags&tags__arraycontains=tag3",
"selected": False,
},
],
"hideable":
True,
"toggle_url":
"/",
"truncated":
False,
}
} == buckets
async def test_array_facet_suggest(app_client):
facet = ArrayFacet(
app_client.ds,
Request.fake("/"),
database="fixtures",
sql="select * from facetable",
table="facetable",
)
suggestions = await facet.suggest()
assert [{
"name": "tags",
"type": "array",
"toggle_url": "http://localhost/?_facet_array=tags",
}] == suggestions
def test_request_properties(path, query_string, expected_full_path):
scope = {
"http_version": "1.1",
"method": "POST",
"path": path,
"raw_path": path.encode("latin-1"),
"query_string": query_string.encode("latin-1"),
"scheme": "http",
"type": "http",
}
request = Request(scope, None)
assert request.path == path
assert request.query_string == query_string
assert request.full_path == expected_full_path
async def test_column_facet_results(app_client):
facet = ColumnFacet(
app_client.ds,
Request.fake("/?_facet=_city_id"),
database="fixtures",
sql="select * from facetable",
table="facetable",
)
buckets, timed_out = await facet.facet_results()
assert [] == timed_out
assert [
{
"name": "_city_id",
"type": "column",
"hideable": True,
"toggle_url": "/",
"results": [
{
"value": 1,
"label": "San Francisco",
"count": 6,
"toggle_url": "http://localhost/?_facet=_city_id&_city_id__exact=1",
"selected": False,
},
{
"value": 2,
"label": "Los Angeles",
"count": 4,
"toggle_url": "http://localhost/?_facet=_city_id&_city_id__exact=2",
"selected": False,
},
{
"value": 3,
"label": "Detroit",
"count": 4,
"toggle_url": "http://localhost/?_facet=_city_id&_city_id__exact=3",
"selected": False,
},
{
"value": 4,
"label": "Memnonia",
"count": 1,
"toggle_url": "http://localhost/?_facet=_city_id&_city_id__exact=4",
"selected": False,
},
],
"truncated": False,
}
] == buckets
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": "/"
}]
}
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"
}
async def test_request_post_vars():
scope = {
"http_version": "1.1",
"method": "POST",
"path": "/",
"raw_path": b"/",
"query_string": b"",
"scheme": "http",
"type": "http",
"headers": [[b"content-type", b"application/x-www-form-urlencoded"]],
}
async def receive():
return {"type": "http.request", "body": b"foo=bar&baz=1", "more_body": False}
request = Request(scope, receive)
assert {"foo": "bar", "baz": "1"} == await request.post_vars()
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 == {}
def test_request_args():
request = Request.fake("/foo?multi=1&multi=2&single=3")
assert "1" == request.args.get("multi")
assert "3" == request.args.get("single")
assert "1" == request.args["multi"]
assert "3" == request.args["single"]
assert ["1", "2"] == request.args.getlist("multi")
assert [] == request.args.getlist("missing")
assert "multi" in request.args
assert "single" in request.args
assert "missing" not in request.args
expected = ["multi", "single"]
assert expected == list(request.args.keys())
for i, key in enumerate(request.args):
assert expected[i] == key
assert 2 == len(request.args)
with pytest.raises(KeyError):
request.args["missing"]
async def test_column_facet_suggest_skip_if_enabled_by_metadata(app_client):
facet = ColumnFacet(
app_client.ds,
Request.fake("/"),
database="fixtures",
sql="select * from facetable",
table="facetable",
metadata={"facets": ["_city_id"]},
)
suggestions = [s["name"] for s in await facet.suggest()]
assert [
"created",
"planet_int",
"on_earth",
"state",
"_neighborhood",
"tags",
"complex_array",
] == suggestions
def test_request_repr():
request = Request.fake("/foo?multi=1&multi=2&single=3")
assert (
repr(request) ==
'<asgi.Request method="GET" url="http://localhost/foo?multi=1&multi=2&single=3">'
)
def test_path_with_added_args(path, added_args, expected):
request = Request.fake(path)
actual = utils.path_with_added_args(request, added_args)
assert expected == actual
def test_path_with_format(path, format, extra_qs, expected):
request = Request.fake(path)
actual = utils.path_with_format(request, format, extra_qs)
assert expected == actual
async def as_csv(self, request, database):
kwargs = {}
stream = request.args.get("_stream")
# Do not calculate facets or counts:
extra_parameters = [
"{}=1".format(key) for key in ("_nofacet", "_nocount")
if not request.args.get(key)
]
if extra_parameters:
# Replace request object with a new one with modified scope
if not request.query_string:
new_query_string = "&".join(extra_parameters)
else:
new_query_string = (request.query_string + "&" +
"&".join(extra_parameters))
new_scope = dict(request.scope,
query_string=new_query_string.encode("latin-1"))
receive = request.receive
request = Request(new_scope, receive)
if stream:
# Some quick soundness checks
if not self.ds.setting("allow_csv_stream"):
raise BadRequest("CSV streaming is disabled")
if request.args.get("_next"):
raise BadRequest("_next not allowed for CSV streaming")
kwargs["_size"] = "max"
# Fetch the first page
try:
response_or_template_contexts = await self.data(request)
if isinstance(response_or_template_contexts, Response):
return response_or_template_contexts
elif len(response_or_template_contexts) == 4:
data, _, _, _ = response_or_template_contexts
else:
data, _, _ = response_or_template_contexts
except (sqlite3.OperationalError, InvalidSql) as e:
raise DatasetteError(str(e), title="Invalid SQL", status=400)
except sqlite3.OperationalError as e:
raise DatasetteError(str(e))
except DatasetteError:
raise
# Convert rows and columns to CSV
headings = data["columns"]
# if there are expanded_columns we need to add additional headings
expanded_columns = set(data.get("expanded_columns") or [])
if expanded_columns:
headings = []
for column in data["columns"]:
headings.append(column)
if column in expanded_columns:
headings.append(f"{column}_label")
content_type = "text/plain; charset=utf-8"
preamble = ""
postamble = ""
trace = request.args.get("_trace")
if trace:
content_type = "text/html; charset=utf-8"
preamble = ("<html><head><title>CSV debug</title></head>"
'<body><textarea style="width: 90%; height: 70vh">')
postamble = "</textarea></body></html>"
async def stream_fn(r):
nonlocal data, trace
limited_writer = LimitedWriter(r, self.ds.setting("max_csv_mb"))
if trace:
await limited_writer.write(preamble)
writer = csv.writer(EscapeHtmlWriter(limited_writer))
else:
writer = csv.writer(limited_writer)
first = True
next = None
while first or (next and stream):
try:
kwargs = {}
if next:
kwargs["_next"] = next
if not first:
data, _, _ = await self.data(request, **kwargs)
if first:
if request.args.get("_header") != "off":
await writer.writerow(headings)
first = False
next = data.get("next")
for row in data["rows"]:
if any(isinstance(r, bytes) for r in row):
new_row = []
for column, cell in zip(headings, row):
if isinstance(cell, bytes):
# If this is a table page, use .urls.row_blob()
if data.get("table"):
pks = data.get("primary_keys") or []
cell = self.ds.absolute_url(
request,
self.ds.urls.row_blob(
database,
data["table"],
path_from_row_pks(
row, pks, not pks),
column,
),
)
else:
# Otherwise generate URL for this query
url = self.ds.absolute_url(
request,
path_with_format(
request=request,
format="blob",
extra_qs={
"_blob_column":
column,
"_blob_hash":
hashlib.sha256(
cell).hexdigest(),
},
replace_format="csv",
),
)
cell = url.replace("&_nocount=1",
"").replace(
"&_nofacet=1",
"")
new_row.append(cell)
row = new_row
if not expanded_columns:
# Simple path
await writer.writerow(row)
else:
# Look for {"value": "label": } dicts and expand
new_row = []
for heading, cell in zip(data["columns"], row):
if heading in expanded_columns:
if cell is None:
new_row.extend(("", ""))
else:
assert isinstance(cell, dict)
new_row.append(cell["value"])
new_row.append(cell["label"])
else:
new_row.append(cell)
await writer.writerow(new_row)
except Exception as e:
sys.stderr.write("Caught this error: {}\n".format(e))
sys.stderr.flush()
await r.write(str(e))
return
await limited_writer.write(postamble)
headers = {}
if self.ds.cors:
add_cors_headers(headers)
if request.args.get("_dl", None):
if not trace:
content_type = "text/csv; charset=utf-8"
disposition = 'attachment; filename="{}.csv"'.format(
request.url_vars.get("table", database))
headers["content-disposition"] = disposition
return AsgiStream(stream_fn,
headers=headers,
content_type=content_type)
async def resolve_table(root,
info,
filter=None,
where=None,
first=None,
after=None,
search=None,
sort=None,
sort_desc=None,
**kwargs):
if first is None:
first = 10
if return_first_row:
first = 1
pairs = []
column_name_rev = {v: k for k, v in meta.graphql_columns.items()}
for filter_ in filter or []:
for column_name, operations in filter_.items():
for operation_name, value in operations.items():
if isinstance(value, list):
value = ",".join(value)
pairs.append([
"{}__{}".format(column_name_rev[column_name],
operation_name.rstrip("_")),
value,
])
if pk_args is not None:
for pk in pk_args:
if kwargs.get(pk) is not None:
pairs.append([pk, kwargs[pk]])
qs = {}
qs.update(pairs)
if after:
qs["_next"] = after
qs["_size"] = first
if search and meta.supports_fts:
qs["_search"] = search
if related_fk:
related_column = meta.graphql_columns.get(related_fk.column,
related_fk.column)
related_other_column = table_metadata[
related_fk.other_table].graphql_columns.get(
related_fk.other_column, related_fk.other_column)
qs[related_column] = getattr(root, related_other_column)
if where:
qs["_where"] = where
if sort:
qs["_sort"] = column_name_rev[sort]
elif sort_desc:
qs["_sort_desc"] = column_name_rev[sort_desc]
path_with_query_string = "/{}/{}.json?{}".format(
database_name, table_name, urllib.parse.urlencode(qs))
context = info.context
if context and "time_started" in context:
elapsed_ms = (time.monotonic() - context["time_started"]) * 1000
if context[
"time_limit_ms"] and elapsed_ms > context["time_limit_ms"]:
assert False, "Time limit exceeded: {:.2f}ms > {}ms - {}".format(
elapsed_ms, context["time_limit_ms"],
path_with_query_string)
context["num_queries_executed"] += 1
if (context["num_queries_limit"]
and context["num_queries_executed"] >
context["num_queries_limit"]):
assert False, "Query limit exceeded: {} > {} - {}".format(
context["num_queries_executed"],
context["num_queries_limit"],
path_with_query_string,
)
request = Request.fake(path_with_query_string)
view = TableView(DatasetteSpecialConfig(datasette))
data, _, _ = await view.data(request,
database=database_name,
hash=None,
table=table_name,
_next=after)
klass = table_classes[table_name]
data["rows"] = [klass.from_row(r) for r in data["rows"]]
if return_first_row:
try:
return data["rows"][0]
except IndexError:
return None
else:
return data
def test_request_fake_url_vars():
request = Request.fake("/")
assert request.url_vars == {}
request = Request.fake("/", url_vars={"database": "fixtures"})
assert request.url_vars == {"database": "fixtures"}
async def test_column_facet_results_column_starts_with_underscore(app_client):
facet = ColumnFacet(
app_client.ds,
Request.fake("/?_facet=_neighborhood"),
database="fixtures",
sql="select * from facetable",
table="facetable",
)
buckets, timed_out = await facet.facet_results()
assert [] == timed_out
assert buckets == [
{
"name": "_neighborhood",
"type": "column",
"hideable": True,
"toggle_url": "/",
"results": [
{
"value": "Downtown",
"label": "Downtown",
"count": 2,
"toggle_url": "http://localhost/?_facet=_neighborhood&_neighborhood__exact=Downtown",
"selected": False,
},
{
"value": "Arcadia Planitia",
"label": "Arcadia Planitia",
"count": 1,
"toggle_url": "http://localhost/?_facet=_neighborhood&_neighborhood__exact=Arcadia+Planitia",
"selected": False,
},
{
"value": "Bernal Heights",
"label": "Bernal Heights",
"count": 1,
"toggle_url": "http://localhost/?_facet=_neighborhood&_neighborhood__exact=Bernal+Heights",
"selected": False,
},
{
"value": "Corktown",
"label": "Corktown",
"count": 1,
"toggle_url": "http://localhost/?_facet=_neighborhood&_neighborhood__exact=Corktown",
"selected": False,
},
{
"value": "Dogpatch",
"label": "Dogpatch",
"count": 1,
"toggle_url": "http://localhost/?_facet=_neighborhood&_neighborhood__exact=Dogpatch",
"selected": False,
},
{
"value": "Greektown",
"label": "Greektown",
"count": 1,
"toggle_url": "http://localhost/?_facet=_neighborhood&_neighborhood__exact=Greektown",
"selected": False,
},
{
"value": "Hayes Valley",
"label": "Hayes Valley",
"count": 1,
"toggle_url": "http://localhost/?_facet=_neighborhood&_neighborhood__exact=Hayes+Valley",
"selected": False,
},
{
"value": "Hollywood",
"label": "Hollywood",
"count": 1,
"toggle_url": "http://localhost/?_facet=_neighborhood&_neighborhood__exact=Hollywood",
"selected": False,
},
{
"value": "Koreatown",
"label": "Koreatown",
"count": 1,
"toggle_url": "http://localhost/?_facet=_neighborhood&_neighborhood__exact=Koreatown",
"selected": False,
},
{
"value": "Los Feliz",
"label": "Los Feliz",
"count": 1,
"toggle_url": "http://localhost/?_facet=_neighborhood&_neighborhood__exact=Los+Feliz",
"selected": False,
},
{
"value": "Mexicantown",
"label": "Mexicantown",
"count": 1,
"toggle_url": "http://localhost/?_facet=_neighborhood&_neighborhood__exact=Mexicantown",
"selected": False,
},
{
"value": "Mission",
"label": "Mission",
"count": 1,
"toggle_url": "http://localhost/?_facet=_neighborhood&_neighborhood__exact=Mission",
"selected": False,
},
{
"value": "SOMA",
"label": "SOMA",
"count": 1,
"toggle_url": "http://localhost/?_facet=_neighborhood&_neighborhood__exact=SOMA",
"selected": False,
},
{
"value": "Tenderloin",
"label": "Tenderloin",
"count": 1,
"toggle_url": "http://localhost/?_facet=_neighborhood&_neighborhood__exact=Tenderloin",
"selected": False,
},
],
"truncated": False,
}
]
