async def reconcile(request, datasette):
database = request.url_vars["db_name"]
table = request.url_vars["db_table"]
db = datasette.get_database(database)
# get plugin configuration
config = datasette.plugin_config(
"datasette-reconcile", database=database, table=table
)
config = await check_config(config, db, table)
# check user can at least view this table
await check_permissions(
request,
[
("view-table", (database, table)),
("view-database", database),
"view-instance",
],
datasette,
)
# work out if we are looking for queries
post_vars = await request.post_vars()
queries = post_vars.get("queries", request.args.get("queries"))
if queries:
queries = json.loads(queries)
return Response.json(
{
q[0]: {"result": q[1]}
async for q in reconcile_queries(queries, config, db, table)
},
headers={
"Access-Control-Allow-Origin": "*",
},
)
# if we're not then just return the service specification
return Response.json(
service_manifest(config, database, table, datasette, request),
headers={
"Access-Control-Allow-Origin": "*",
},
)
async def parallel_queries(datasette):
db = datasette.get_database()
with tracer.trace_child_tasks():
one, two = await asyncio.gather(
db.execute("select coalesce(sleep(0.1), 1)"),
db.execute("select coalesce(sleep(0.1), 2)"),
)
return Response.json({
"one": one.single_value(),
"two": two.single_value()
})
async def csv_importer_status(scope, receive, datasette, request):
if not await datasette.permission_allowed(
request.actor, "csv-importer", default=False
):
raise Forbidden("Permission denied for csv-importer")
plugin_config = datasette.plugin_config(
"datasette-csv-importer"
) or {}
db = get_status_database(datasette, plugin_config)
status_table = get_status_table(plugin_config)
query = f"select * from {status_table} where id = ? limit 1"
result = await db.execute(query, (request.url_vars["task_id"],))
return Response.json(dict(result.first()))
async def data(
self,
request,
database,
hash,
sql,
editable=True,
canned_query=None,
metadata=None,
_size=None,
named_parameters=None,
write=False,
):
params = {key: request.args.get(key) for key in request.args}
if "sql" in params:
params.pop("sql")
if "_shape" in params:
params.pop("_shape")
private = False
if canned_query:
# Respect canned query permissions
await self.check_permissions(
request,
[
("view-query", (database, canned_query)),
("view-database", database),
"view-instance",
],
)
private = not await self.ds.permission_allowed(
None, "view-query", (database, canned_query), default=True)
else:
await self.check_permission(request, "execute-sql", database)
# Extract any :named parameters
named_parameters = named_parameters or self.re_named_parameter.findall(
sql)
named_parameter_values = {
named_parameter: params.get(named_parameter) or ""
for named_parameter in named_parameters
if not named_parameter.startswith("_")
}
# Set to blank string if missing from params
for named_parameter in named_parameters:
if named_parameter not in params and not named_parameter.startswith(
"_"):
params[named_parameter] = ""
extra_args = {}
if params.get("_timelimit"):
extra_args["custom_time_limit"] = int(params["_timelimit"])
if _size:
extra_args["page_size"] = _size
templates = [f"query-{to_css_class(database)}.html", "query.html"]
# Execute query - as write or as read
if write:
if request.method == "POST":
body = await request.post_body()
body = body.decode("utf-8").strip()
if body.startswith("{") and body.endswith("}"):
params = json.loads(body)
# But we want key=value strings
for key, value in params.items():
params[key] = str(value)
else:
params = dict(parse_qsl(body, keep_blank_values=True))
# Should we return JSON?
should_return_json = (request.headers.get("accept")
== "application/json"
or request.args.get("_json")
or params.get("_json"))
if canned_query:
params_for_query = MagicParameters(params, request,
self.ds)
else:
params_for_query = params
ok = None
try:
cursor = await self.ds.databases[database].execute_write(
sql, params_for_query, block=True)
message = metadata.get(
"on_success_message"
) or "Query executed, {} row{} affected".format(
cursor.rowcount, "" if cursor.rowcount == 1 else "s")
message_type = self.ds.INFO
redirect_url = metadata.get("on_success_redirect")
ok = True
except Exception as e:
message = metadata.get("on_error_message") or str(e)
message_type = self.ds.ERROR
redirect_url = metadata.get("on_error_redirect")
ok = False
if should_return_json:
return Response.json({
"ok": ok,
"message": message,
"redirect": redirect_url,
})
else:
self.ds.add_message(request, message, message_type)
return self.redirect(request, redirect_url or request.path)
else:
async def extra_template():
return {
"request": request,
"path_with_added_args": path_with_added_args,
"path_with_removed_args": path_with_removed_args,
"named_parameter_values": named_parameter_values,
"canned_query": canned_query,
"success_message": request.args.get("_success") or "",
"canned_write": True,
}
return (
{
"database": database,
"rows": [],
"truncated": False,
"columns": [],
"query": {
"sql": sql,
"params": params
},
"private": private,
},
extra_template,
templates,
)
else: # Not a write
if canned_query:
params_for_query = MagicParameters(params, request, self.ds)
else:
params_for_query = params
results = await self.ds.execute(database,
sql,
params_for_query,
truncate=True,
**extra_args)
columns = [r[0] for r in results.description]
if canned_query:
templates.insert(
0,
f"query-{to_css_class(database)}-{to_css_class(canned_query)}.html",
)
allow_execute_sql = await self.ds.permission_allowed(request.actor,
"execute-sql",
database,
default=True)
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())))
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 = jinja2.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,
}))
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,
}
return (
{
"database": database,
"query_name": canned_query,
"rows": results.rows,
"truncated": results.truncated,
"columns": columns,
"query": {
"sql": sql,
"params": params
},
"private": private,
"allow_execute_sql": allow_execute_sql,
},
extra_template,
templates,
)
async def post(request):
if request.method == "GET":
return Response.html(request.scope["csrftoken"]())
else:
return Response.json(await request.post_vars())
async def schema_versions_json(datasette):
return Response.json(await _schema_versions(datasette))
def test_response_json():
response = Response.json({"this_is": "json"})
assert 200 == response.status
assert '{"this_is": "json"}' == response.body
assert "application/json; charset=utf-8" == response.content_type
def asgi_scope(scope):
return Response.json(scope, default=repr)
async def view_graphql(request, datasette):
if request.method == "OPTIONS":
return Response.text("ok",
headers=CORS_HEADERS if datasette.cors else {})
body = await post_body(request)
database = request.url_vars.get("database")
try:
datasette.get_database(database)
except KeyError:
raise NotFound("Database does not exist")
if not body and "text/html" in request.headers.get("accept", ""):
return Response.html(
await datasette.render_template("graphiql.html", {
"database": database,
},
request=request),
headers=CORS_HEADERS if datasette.cors else {},
)
schema = await schema_for_database_via_cache(datasette, database=database)
if request.args.get("schema"):
return Response.text(print_schema(schema))
incoming = {}
if body:
incoming = json.loads(body)
query = incoming.get("query")
variables = incoming.get("variables")
operation_name = incoming.get("operationName")
else:
query = request.args.get("query")
variables = request.args.get("variables", "")
if variables:
variables = json.loads(variables)
operation_name = request.args.get("operationName")
if not query:
return Response.json(
{"error": "Missing query"},
status=400,
headers=CORS_HEADERS if datasette.cors else {},
)
config = datasette.plugin_config("datasette-graphql") or {}
context = {
"time_started":
time.monotonic(),
"time_limit_ms":
config.get("time_limit_ms") or DEFAULT_TIME_LIMIT_MS,
"num_queries_executed":
0,
"num_queries_limit":
config.get("num_queries_limit") or DEFAULT_NUM_QUERIES_LIMIT,
}
result = await graphql(
schema,
query,
operation_name=operation_name,
variable_values=variables,
context_value=context,
executor=AsyncioExecutor(),
return_promise=True,
)
response = {"data": result.data}
if result.errors:
response["errors"] = [format_error(error) for error in result.errors]
return Response.json(
response,
status=200 if not result.errors else 500,
headers=CORS_HEADERS if datasette.cors else {},
)
async def csv_importer(scope, receive, datasette, request):
"""
CSV Importer initiates a CSV import using the CLI tool CSVs-to-SQlite.
Accepts HTTP POST with form data as follows:
`csv` should contain the CSV file to be imported
`database` is the name of the database file to be written to. If blank,
we will choose a name base on the uploaded file name.
If `xhr` is set to `1` we will assume a JS client is running and this
endpoint will return JSON (as opposed to rendering a different HTML
template without `xhr` set to `1`).
A valid `csrftoken` needs to be provided.
Any form input starting with "-" are interpreted as arguments to
the CLI tool. Such arguments are considered single-toggle arguments
that don't use any parameters, so "--on true" will be interpreted
as running the tool with "--on".
"""
if not await datasette.permission_allowed(
request.actor, "csv-importer", default=False
):
raise Forbidden("Permission denied for csv-importer")
plugin_config = datasette.plugin_config(
"datasette-csv-importer"
) or {}
print("plugin_config", plugin_config)
db = get_status_database(datasette, plugin_config)
status_table = get_status_table(plugin_config)
# We need the ds_request to pass to render_template for CSRF tokens
ds_request = request
# We use the Starlette request object to handle file uploads
starlette_request = Request(scope, receive)
# If we aren't uploading a new file (POST), show uploader screen
if starlette_request.method != "POST":
print("plugin_config", plugin_config)
return Response.html(
await datasette.render_template(
"csv_importer.html", {}, request=ds_request
)
)
formdata = await starlette_request.form()
csv = formdata["csv"]
# csv.file is a SpooledTemporaryFile. csv.filename is the filename
filename = csv.filename
basename = os.path.splitext(filename)[0]
if "database" in formdata and formdata["database"]:
basename = formdata["database"]
outfile_db = os.path.join(get_dbpath(plugin_config), f"{basename}.db")
if basename in datasette.databases:
global_access = await datasette.permission_allowed(
request.actor, "view-database", default=False
)
specific_access = await datasette.permission_allowed(
request.actor, "view-database", (basename,), default=False
)
if not specific_access and not global_access:
raise Forbidden("view-database access required for existing database!")
task_id = str(uuid.uuid4())
def insert_initial_record(conn):
database = sqlite_utils.Database(conn)
database[status_table].insert(
{
"id": task_id,
"filename": filename,
"dbname": basename,
"started": str(datetime.datetime.utcnow()),
"completed": None,
"exitcode": -1,
"status": "in-progress",
"message": "Setting up import...",
"output": None,
},
pk="id",
alter=True,
)
await db.execute_write_fn(insert_initial_record, block=True)
csv_fields = [
"--primary-key", "--fts", "--index", "--date", "--datetime",
"--datetime-format"
]
args = []
for key, value in formdata.items():
if not key.startswith("-"):
continue
# this is a toggle/flag arg with no param
if value is True or value == "true":
args.append(key)
continue
if not value or value == "false":
continue
# we have a columns list field, split it up w/ dupe keys
# TODO: This screws up when column names have commas in them!
if "," in value and key in csv_fields:
for v in re.split(r"/,\s*/", value):
if not v or not v.strip():
continue
args.append(key)
args.append(value)
continue
args.append(key)
args.append(value)
def set_status(conn, message):
print("Setting status", message)
status_database = sqlite_utils.Database(conn)
status_database[status_table].update(
task_id,
{
"message": message,
},
)
print("Successfully set status!")
# run the command, capture its output
def run_cli_import(conn):
set_status(conn, "Running CSV import...")
exitcode = -1
output = None
message = None
try:
with tempfile.NamedTemporaryFile() as temp:
temp.write(csv.file.read())
temp.flush()
args.append(temp.name)
args.append(outfile_db)
# run the import command, capturing stdout
with Capturing() as output:
exitcode = command.main(
args=args, prog_name="cli", standalone_mode=False
)
if exitcode is not None:
exitcode = int(exitcode)
# detect a failure to write DB where tool returns success
# code this makes it so we don't have to read the
# CLI output to figure out if the command succeeded or not
if not os.path.exists(outfile_db) and not exitcode:
exitcode = -2
except Exception as e:
print("Exception", e)
exitcode = -2
message = str(e)
set_status(conn, "Adding database to internal DB list...")
# Adds this DB to the internel DBs list
if basename not in datasette.databases:
print("Adding database", basename)
datasette.add_database(
Database(datasette, path=outfile_db, is_mutable=True),
name=basename,
)
# print("Database added successfully!")
# try:
# loop = asyncio.get_running_loop()
# except RuntimeError:
# loop = asyncio.new_event_loop()
# print("Running schema refresh...")
# loop.run_until_complete(datasette.refresh_schemas())
# print("Schema refresh complete!")
csvspath = get_csvspath(plugin_config)
if csvspath:
set_status(conn, "Saving CSV to server directory...")
csv_db_name = args[-1].replace(".db", "")
csv_table_name = csv_db_name
if "-t" in formdata:
csv_table_name = formdata["-t"]
if "--table" in formdata:
csv_table_name = formdata["--table"]
outfile_csv = os.path.join(
csvspath, f"{csv_db_name}--{csv_table_name}.csv"
)
outfile_args = os.path.join(
csvspath, f"{csv_db_name}--{csv_table_name}.json"
)
# success! save our configs and CSV
print("Writing CSV", outfile_csv)
with open(outfile_csv, "wb") as f:
csv.file.seek(0)
f.write(csv.file.read())
print("Writing args to", outfile_args)
with open(outfile_args, "w") as f:
f.write(json.dumps(args, indent=2))
if get_use_live_metadata(plugin_config):
print("Running live-config integration...")
set_status(
conn, "Running live-config plugin integration..."
)
# add the permission table, grant access to current user only
# this will create the DB if not exists
print("Opening DB:", outfile_db)
out_db = sqlite_utils.Database(sqlite3.connect(outfile_db))
try:
out_db["__metadata"].get("allow")
except sqlite_utils.db.NotFoundError:
# don't overwrite, only create
out_db["__metadata"].insert({
"key": "tables",
"value": json.dumps({
"__metadata": {
"hidden": True
}
}),
}, pk="key", alter=True, replace=False, ignore=True)
if get_use_live_permissions(plugin_config):
print("Setting live-permissions plugin status...")
set_status(
conn,
"Running live-permissions plugin integration..."
)
print("Running set_perms_for_live_permissions with basename:", basename)
set_perms_for_live_permissions(datasette, request.actor, basename)
print("set_perms_for_live_permissions complete!")
if not message:
message = "Import successful!" if not exitcode else "Failure"
print("Updating status", message)
status_database = sqlite_utils.Database(conn)
status_database[status_table].update(
task_id,
{
"completed": str(datetime.datetime.utcnow()),
"exitcode": exitcode,
"status": "completed",
"message": message,
"output": "\n".join(output),
},
)
await db.execute_write_fn(run_cli_import)
if formdata.get("xhr"):
return Response.json(
{
"url": datasette.urls.path("/{filename}".format(
filename=quote_plus(filename),
)),
"status_database_path": quote_plus(db.name),
"status_table": quote_plus(status_table),
"task_id": task_id,
}
)
return Response.html(
await datasette.render_template(
"csv_importer_done.html", {
"filename": filename,
"task_id": task_id,
},
)
)
async def render_response(request):
if request.args.get("_broken"):
return "this should break"
return Response.json({"this_is": "json"})
