async def data(self, request, name, hash):
if request.args.get("sql"):
if not self.ds.config["allow_sql"]:
raise DatasetteError("sql= is not allowed", status=400)
sql = request.raw_args.pop("sql")
validate_sql_select(sql)
return await self.custom_sql(request, name, hash, sql)
info = self.ds.inspect()[name]
metadata = self.ds.metadata.get("databases", {}).get(name, {})
self.ds.update_with_inherited_metadata(metadata)
tables = list(info["tables"].values())
tables.sort(key=lambda t: (t["hidden"], t["name"]))
return {
"database":
name,
"tables":
tables,
"hidden_count":
len([t for t in tables if t["hidden"]]),
"views":
info["views"],
"queries": [{
"name": query_name,
"sql": query_sql
} for query_name, query_sql in (
metadata.get("queries") or {}).items()],
"config":
self.ds.config,
}, {
"database_hash": hash,
"show_hidden": request.args.get("_show_hidden"),
"editable": True,
"metadata": metadata,
}, ("database-{}.html".format(to_css_class(name)), "database.html")
async def data(self,
request,
database,
hash,
default_labels=False,
_size=None):
if request.args.get("sql"):
if not self.ds.config("allow_sql"):
raise DatasetteError("sql= is not allowed", status=400)
sql = request.raw_args.pop("sql")
validate_sql_select(sql)
return await self.custom_sql(request,
database,
hash,
sql,
_size=_size)
info = self.ds.inspect()[database]
metadata = (self.ds.metadata("databases") or {}).get(database, {})
self.ds.update_with_inherited_metadata(metadata)
tables = list(info["tables"].values())
tables.sort(key=lambda t: (t["hidden"], t["name"]))
return {
"database": database,
"size": info["size"],
"tables": tables,
"hidden_count": len([t for t in tables if t["hidden"]]),
"views": info["views"],
"queries": self.ds.get_canned_queries(database),
}, {
"show_hidden": request.args.get("_show_hidden"),
"editable": True,
"metadata": metadata,
}, ("database-{}.html".format(to_css_class(database)), "database.html")
async def extra_template():
display_rows = []
for row in results.rows:
display_row = []
for column, value in zip(results.columns, row):
display_value = value
# Let the plugins have a go
# pylint: disable=no-member
plugin_value = pm.hook.render_cell(
value=value,
column=column,
table=None,
database=database,
datasette=self.ds,
)
if plugin_value is not None:
display_value = plugin_value
else:
if value in ("", None):
display_value = jinja2.Markup(" ")
elif is_url(str(display_value).strip()):
display_value = jinja2.Markup(
'<a href="{url}">{url}</a>'.format(
url=jinja2.escape(value.strip())))
display_row.append(display_value)
display_rows.append(display_row)
# Show 'Edit SQL' button only if:
# - User is allowed to execute SQL
# - SQL is an approved SELECT statement
# - No magic parameters, so no :_ in the SQL string
edit_sql_url = None
is_validated_sql = False
try:
validate_sql_select(sql)
is_validated_sql = True
except InvalidSql:
pass
if allow_execute_sql and is_validated_sql and ":_" not in sql:
edit_sql_url = (self.ds.urls.database(database) + "?" +
urlencode({
**{
"sql": sql,
},
**named_parameter_values,
}))
return {
"display_rows": display_rows,
"custom_sql": True,
"named_parameter_values": named_parameter_values,
"editable": editable,
"canned_query": canned_query,
"edit_sql_url": edit_sql_url,
"metadata": metadata,
"config": self.ds.config_dict(),
"request": request,
"path_with_added_args": path_with_added_args,
"path_with_removed_args": path_with_removed_args,
"hide_sql": "_hide_sql" in params,
}
async def data(self, request, database, hash, default_labels=False, _size=None):
metadata = (self.ds.metadata("databases") or {}).get(database, {})
self.ds.update_with_inherited_metadata(metadata)
if request.args.get("sql"):
if not self.ds.config("allow_sql"):
raise DatasetteError("sql= is not allowed", status=400)
sql = request.raw_args.pop("sql")
validate_sql_select(sql)
return await QueryView(self.ds).data(
request, database, hash, sql, _size=_size, metadata=metadata
)
db = self.ds.databases[database]
table_counts = await db.table_counts(5)
views = await db.view_names()
hidden_table_names = set(await db.hidden_table_names())
all_foreign_keys = await db.get_all_foreign_keys()
tables = []
for table in table_counts:
table_columns = await db.table_columns(table)
tables.append(
{
"name": table,
"columns": table_columns,
"primary_keys": await db.primary_keys(table),
"count": table_counts[table],
"hidden": table in hidden_table_names,
"fts_table": await db.fts_table(table),
"foreign_keys": all_foreign_keys[table],
}
)
tables.sort(key=lambda t: (t["hidden"], t["name"]))
return (
{
"database": database,
"size": db.size,
"tables": tables,
"hidden_count": len([t for t in tables if t["hidden"]]),
"views": views,
"queries": self.ds.get_canned_queries(database),
},
{
"show_hidden": request.args.get("_show_hidden"),
"editable": True,
"metadata": metadata,
"allow_download": self.ds.config("allow_download")
and not db.is_mutable
and database != ":memory:",
},
("database-{}.html".format(to_css_class(database)), "database.html"),
)
def test_validate_sql_select_good(good_sql):
utils.validate_sql_select(good_sql)
def test_validate_sql_select_bad(bad_sql):
with pytest.raises(utils.InvalidSql):
utils.validate_sql_select(bad_sql)
async def data(self,
request,
database,
hash,
default_labels=False,
_size=None):
await self.check_permissions(
request,
[
("view-database", database),
"view-instance",
],
)
metadata = (self.ds.metadata("databases") or {}).get(database, {})
self.ds.update_with_inherited_metadata(metadata)
if request.args.get("sql"):
sql = request.args.get("sql")
validate_sql_select(sql)
return await QueryView(self.ds).data(request,
database,
hash,
sql,
_size=_size,
metadata=metadata)
db = self.ds.databases[database]
table_counts = await db.table_counts(5)
hidden_table_names = set(await db.hidden_table_names())
all_foreign_keys = await db.get_all_foreign_keys()
views = []
for view_name in await db.view_names():
visible, private = await check_visibility(
self.ds,
request.actor,
"view-table",
(database, view_name),
)
if visible:
views.append({
"name": view_name,
"private": private,
})
tables = []
for table in table_counts:
visible, private = await check_visibility(
self.ds,
request.actor,
"view-table",
(database, table),
)
if not visible:
continue
table_columns = await db.table_columns(table)
tables.append({
"name": table,
"columns": table_columns,
"primary_keys": await db.primary_keys(table),
"count": table_counts[table],
"hidden": table in hidden_table_names,
"fts_table": await db.fts_table(table),
"foreign_keys": all_foreign_keys[table],
"private": private,
})
tables.sort(key=lambda t: (t["hidden"], t["name"]))
canned_queries = []
for query in (await
self.ds.get_canned_queries(database,
request.actor)).values():
visible, private = await check_visibility(
self.ds,
request.actor,
"view-query",
(database, query["name"]),
)
if visible:
canned_queries.append(dict(query, private=private))
async def database_actions():
links = []
for hook in pm.hook.database_actions(
datasette=self.ds,
database=database,
actor=request.actor,
):
extra_links = await await_me_maybe(hook)
if extra_links:
links.extend(extra_links)
return links
return (
{
"database":
database,
"path":
self.ds.urls.database(database),
"size":
db.size,
"tables":
tables,
"hidden_count":
len([t for t in tables if t["hidden"]]),
"views":
views,
"queries":
canned_queries,
"private":
not await self.ds.permission_allowed(
None, "view-database", database, default=True),
"allow_execute_sql":
await self.ds.permission_allowed(request.actor,
"execute-sql",
database,
default=True),
},
{
"database_actions":
database_actions,
"show_hidden":
request.args.get("_show_hidden"),
"editable":
True,
"metadata":
metadata,
"allow_download":
self.ds.setting("allow_download") and not db.is_mutable
and not db.is_memory,
},
(f"database-{to_css_class(database)}.html", "database.html"),
)
async def extra_template():
display_rows = []
for row in results.rows if results else []:
display_row = []
for column, value in zip(results.columns, row):
display_value = value
# Let the plugins have a go
# pylint: disable=no-member
plugin_display_value = None
for candidate in pm.hook.render_cell(
value=value,
column=column,
table=None,
database=database,
datasette=self.ds,
):
candidate = await await_me_maybe(candidate)
if candidate is not None:
plugin_display_value = candidate
break
if plugin_display_value is not None:
display_value = plugin_display_value
else:
if value in ("", None):
display_value = Markup(" ")
elif is_url(str(display_value).strip()):
display_value = Markup(
'<a href="{url}">{url}</a>'.format(
url=escape(value.strip())))
elif isinstance(display_value, bytes):
blob_url = path_with_format(
request=request,
format="blob",
extra_qs={
"_blob_column":
column,
"_blob_hash":
hashlib.sha256(display_value).hexdigest(),
},
)
display_value = Markup(
'<a class="blob-download" href="{}"><Binary: {} byte{}></a>'
.format(
blob_url,
len(display_value),
"" if len(value) == 1 else "s",
))
display_row.append(display_value)
display_rows.append(display_row)
# Show 'Edit SQL' button only if:
# - User is allowed to execute SQL
# - SQL is an approved SELECT statement
# - No magic parameters, so no :_ in the SQL string
edit_sql_url = None
is_validated_sql = False
try:
validate_sql_select(sql)
is_validated_sql = True
except InvalidSql:
pass
if allow_execute_sql and is_validated_sql and ":_" not in sql:
edit_sql_url = (self.ds.urls.database(database) + "?" +
urlencode({
**{
"sql": sql,
},
**named_parameter_values,
}))
show_hide_hidden = ""
if metadata.get("hide_sql"):
if bool(params.get("_show_sql")):
show_hide_link = path_with_removed_args(
request, {"_show_sql"})
show_hide_text = "hide"
show_hide_hidden = (
'<input type="hidden" name="_show_sql" value="1">')
else:
show_hide_link = path_with_added_args(
request, {"_show_sql": 1})
show_hide_text = "show"
else:
if bool(params.get("_hide_sql")):
show_hide_link = path_with_removed_args(
request, {"_hide_sql"})
show_hide_text = "show"
show_hide_hidden = (
'<input type="hidden" name="_hide_sql" value="1">')
else:
show_hide_link = path_with_added_args(
request, {"_hide_sql": 1})
show_hide_text = "hide"
hide_sql = show_hide_text == "show"
return {
"display_rows": display_rows,
"custom_sql": True,
"named_parameter_values": named_parameter_values,
"editable": editable,
"canned_query": canned_query,
"edit_sql_url": edit_sql_url,
"metadata": metadata,
"settings": self.ds.settings_dict(),
"request": request,
"show_hide_link": show_hide_link,
"show_hide_text": show_hide_text,
"show_hide_hidden": markupsafe.Markup(show_hide_hidden),
"hide_sql": hide_sql,
}
def test_validate_sql_select_good(good_sql):
utils.validate_sql_select(good_sql)
def test_validate_sql_select_bad(bad_sql):
with pytest.raises(utils.InvalidSql):
utils.validate_sql_select(bad_sql)
