def query(dbname):
"Perform a query of the database; return rows."
try:
db = dbshare.db.get_check_read(dbname)
except ValueError:
flask.abort(http.client.UNAUTHORIZED)
except KeyError:
flask.abort(http.client.NOT_FOUND)
timer = utils.Timer()
try:
query = flask.request.get_json()
sql = dbshare.query.get_sql_statement(query)
dbcnx = dbshare.db.get_cnx(dbname)
cursor = utils.execute_timeout(dbcnx, sql)
except (jsonschema.ValidationError, sqlite3.Error) as error:
utils.abort_json(http.client.BAD_REQUEST, error)
except SystemError:
flask.abort(http.client.REQUEST_TIMEOUT)
columns = [d[0] for d in cursor.description]
rows = cursor.fetchall()
result = {
"query": query,
"sql": sql,
"nrows": len(rows),
"columns": columns,
"cpu_time": timer(),
"data": [dict(zip(columns, row)) for row in rows],
}
return utils.jsonify(utils.get_json(**result), "/query/output")
def public():
"Return the list of public databases."
result = {
"title": "Public databases",
"databases": get_json(dbshare.dbs.get_dbs(public=True)),
}
return utils.jsonify(utils.get_json(**result), "/dbs")
def all():
"Return the list of all databases."
dbs = dbshare.dbs.get_dbs()
result = {
"title": "All databases",
"total_size": sum([db["size"] for db in dbs]),
"databases": get_json(dbs),
}
return utils.jsonify(utils.get_json(**result), "/dbs")
def owner(username):
"Return the list of databases owned by the given user."
if not dbshare.dbs.has_access(username):
return flask.abort(http.client.UNAUTHORIZED)
dbs = dbshare.dbs.get_dbs(owner=username)
result = {
"title": f"Databases owned by {username}",
"user": dbshare.api.user.get_json(username),
"total_size": sum([db["size"] for db in dbs]),
"databases": get_json(dbs),
}
return utils.jsonify(utils.get_json(**result), "/dbs")
def table(dbname, tablename):
"""GET: Return the schema for the table.
PUT: Create the table.
DELETE: Delete the table.
"""
if utils.http_GET():
try:
db = dbshare.db.get_check_read(dbname)
except ValueError:
flask.abort(http.client.UNAUTHORIZED)
except KeyError:
flask.abort(http.client.NOT_FOUND)
try:
schema = db["tables"][tablename]
except KeyError:
flask.abort(http.client.NOT_FOUND)
result = get_json(db, schema, complete=True)
result.update(schema)
return utils.jsonify(utils.get_json(**result), "/table")
elif utils.http_PUT():
try:
db = dbshare.db.get_check_write(dbname)
except ValueError:
flask.abort(http.client.UNAUTHORIZED)
except KeyError:
flask.abort(http.client.NOT_FOUND)
try:
with dbshare.db.DbSaver(db) as saver:
schema = flask.request.get_json()
saver.add_table(schema)
for index in schema.get("indexes", []):
saver.add_index(tablename, index)
except (jsonschema.ValidationError, ValueError) as error:
utils.abort_json(http.client.BAD_REQUEST, error)
return flask.redirect(
flask.url_for("api_table.table",
dbname=dbname,
tablename=tablename))
elif utils.http_DELETE():
try:
db = dbshare.db.get_check_write(dbname)
except ValueError:
flask.abort(http.client.UNAUTHORIZED)
except KeyError:
flask.abort(http.client.NOT_FOUND)
try:
with dbshare.db.DbSaver(db) as saver:
saver.delete_table(tablename)
except ValueError as error:
utils.abort_json(http.client.BAD_REQUEST, error)
return ("", http.client.NO_CONTENT)
def all():
"Return the list of all user accounts."
sql = "SELECT username, email, role, status, created, modified" " FROM users"
users = [{
"username": row[0],
"email": row[1],
"role": row[2],
"status": row[3],
"created": row[4],
"modified": row[5],
"href": utils.url_for("api_user.user", username=row[0]),
} for row in flask.g.syscnx.execute(sql)]
result = {"title": "All user accounts.", "users": users}
return utils.jsonify(utils.get_json(**result), "/users")
def view(dbname, viewname):
"""GET: Return the schema for the view.
PUT: Create the view.
DELETE: Delete the view.
"""
if utils.http_GET():
try:
db = dbshare.db.get_check_read(dbname, nrows=[viewname])
except ValueError:
flask.abort(http.client.UNAUTHORIZED)
except KeyError:
flask.abort(http.client.NOT_FOUND)
try:
schema = db["views"][viewname]
except KeyError:
flask.abort(http.client.NOT_FOUND)
result = get_json(db, schema, complete=True)
result.update(schema)
result.pop("type", None)
return utils.jsonify(utils.get_json(**result), "/view")
elif utils.http_PUT():
try:
db = dbshare.db.get_check_write(dbname)
except ValueError:
flask.abort(http.client.UNAUTHORIZED)
except KeyError:
flask.abort(http.client.NOT_FOUND)
try:
with dbshare.db.DbSaver(db) as saver:
saver.add_view(flask.request.get_json(), create=True)
except (jsonschema.ValidationError, ValueError) as error:
utils.abort_json(http.client.BAD_REQUEST, error)
return flask.redirect(
flask.url_for("api_view.view", dbname=dbname, viewname=viewname)
)
elif utils.http_DELETE():
try:
db = dbshare.db.get_check_write(dbname)
except ValueError:
flask.abort(http.client.UNAUTHORIZED)
except KeyError:
flask.abort(http.client.NOT_FOUND)
try:
with dbshare.db.DbSaver(db) as saver:
saver.delete_view(viewname)
except ValueError as error:
utils.abort_json(http.client.BAD_REQUEST, error)
return ("", http.client.NO_CONTENT)
def user(username):
"Return the API JSON user display."
user = dbshare.user.get_user(username=username)
if user is None:
flask.abort(http.client.NOT_FOUND)
if not dbshare.user.is_admin_or_self(user):
flask.abort(http.client.UNAUTHORIZED)
# Remove sensitive information.
user.pop("password")
user.pop("apikey", None)
user["total_size"] = dbshare.db.get_usage(username)[1]
user["databases"] = {
"href": utils.url_for("api_dbs.owner", username=user["username"])
}
return utils.jsonify(utils.get_json(**user), "/user")
def statistics(dbname, tablename):
"GET: Return the schema for the table with statistics for the columns."
try:
db = dbshare.db.get_check_read(dbname)
except ValueError:
flask.abort(http.client.UNAUTHORIZED)
except KeyError:
flask.abort(http.client.NOT_FOUND)
try:
schema = db["tables"][tablename]
except KeyError:
flask.abort(http.client.NOT_FOUND)
result = get_json(db, schema, complete=False)
dbshare.table.compute_statistics(db, schema)
result.update(schema)
return utils.jsonify(utils.get_json(**result), "/table/statistics")
def rows_json(dbname, tablename):
"Return the rows in JSON format."
try:
db = dbshare.db.get_check_read(dbname)
except ValueError:
flask.abort(http.client.UNAUTHORIZED)
except KeyError:
flask.abort(http.client.NOT_FOUND)
try:
schema = db["tables"][tablename]
except KeyError:
flask.abort(http.client.NOT_FOUND)
try:
dbcnx = dbshare.db.get_cnx(dbname)
columns = [c["name"] for c in schema["columns"]]
colnames = ",".join([f'"{c}"' for c in columns])
sql = f'SELECT {colnames} FROM "{tablename}"'
try:
cursor = utils.execute_timeout(dbcnx, sql)
except SystemError:
flask.abort(http.client.REQUEST_TIMEOUT)
except sqlite3.Error:
flask.abort(http.client.INTERNAL_SERVER_ERROR)
result = {
"name": tablename,
"title": schema.get("title") or "Table {}".format(tablename),
"source": {
"type":
"table",
"href":
utils.url_for("api_table.table",
dbname=db["name"],
tablename=tablename),
},
"nrows": schema["nrows"],
"data": [dict(zip(columns, row)) for row in cursor],
}
return utils.jsonify(utils.get_json(**result), "/rows")
def root():
"API root resource; links to other API resources."
schema_base_url = flask.current_app.config["SCHEMA_BASE_URL"]
result = {
"title": "DbShare API",
"version": dbshare.__version__,
"databases": {"public": {"href": utils.url_for("api_dbs.public")}},
}
if flask.g.current_user:
result["databases"]["owner"] = {
"href": utils.url_for(
"api_dbs.owner", username=flask.g.current_user["username"]
)
}
result["schema"] = {"href": schema_base_url}
if flask.g.is_admin:
result["databases"]["all"] = {"href": utils.url_for("api_dbs.all")}
result["users"] = {"all": {"href": utils.url_for("api_users.all")}}
if flask.g.current_user:
result["user"] = dbshare.api.user.get_json(flask.g.current_user["username"])
result["operations"] = {
"database": {
"query": {
"title": "Perform a database query.",
"href": utils.url_for_unq("api_db.query", dbname="{dbname}"),
"variables": {"dbname": {"title": "Name of the database."}},
"method": "POST",
"input": {
"content-type": constants.JSON_MIMETYPE,
"schema": {"href": schema_base_url + "/query/input"},
},
"output": {
"content-type": constants.JSON_MIMETYPE,
"schema": {"href": schema_base_url + "/query/output"},
},
}
}
}
if flask.g.current_user:
result["operations"]["database"].update(
{
"create": {
"title": "Create a new database.",
"href": utils.url_for_unq("api_db.database", dbname="{dbname}"),
"variables": {"dbname": {"title": "Name of the database."}},
"method": "PUT",
},
"edit": {
"title": "Edit the database metadata.",
"href": utils.url_for_unq("api_db.database", dbname="{dbname}"),
"variables": {"dbname": {"title": "Name of the database."}},
"method": "POST",
"input": {
"content-type": constants.JSON_MIMETYPE,
"schema": {"href": schema_base_url + "/db/edit"},
},
},
"delete": {
"title": "Delete the database.",
"href": utils.url_for_unq("api_db.database", dbname="{dbname}"),
"variables": {"dbname": {"title": "Name of the database."}},
"method": "DELETE",
},
"readonly": {
"title": "Set the database to read-only.",
"href": utils.url_for_unq("api_db.database", dbname="{dbname}"),
"variables": {"dbname": {"title": "Name of the database."}},
"method": "POST",
},
"readwrite": {
"title": "Set the database to read-write.",
"href": utils.url_for_unq("api_db.database", dbname="{dbname}"),
"variables": {"dbname": {"title": "Name of the database."}},
"method": "POST",
},
}
)
result["operations"]["table"] = {
"create": {
"title": "Create a new table in the database.",
"href": utils.url_for_unq(
"api_table.table", dbname="{dbname}", tablename="{tablename}"
),
"variables": {
"dbname": {"title": "Name of the database."},
"tablename": {"title": "Name of the table."},
},
"method": "PUT",
"input": {
"content-type": constants.JSON_MIMETYPE,
"schema": {"href": schema_base_url + "/table/create"},
},
},
"delete": {
"title": "Delete the table from the database.",
"href": utils.url_for_unq(
"api_table.table", dbname="{dbname}", tablename="{tablename}"
),
"variables": {
"dbname": {"title": "Name of the database."},
"tablename": {"title": "Name of the table."},
},
"method": "DELETE",
},
"insert": {
"title": "Insert rows from JSON or CSV data into the table.",
"href": utils.url_for_unq(
"api_table.insert", dbname="{dbname}", tablename="{tablename}"
),
"variables": {
"dbname": {"title": "Name of the database."},
"tablename": {"title": "Name of the table."},
},
"method": "POST",
"input": [
{
"content-type": constants.JSON_MIMETYPE,
"schema": {"href": schema_base_url + "/table/input"},
},
{"content-type": constants.CSV_MIMETYPE},
],
},
"update": {
"title": "Update rows in the table from CSV data.",
"href": utils.url_for_unq(
"api_table.update", dbname="{dbname}", tablename="{tablename}"
),
"variables": {
"dbname": {"title": "Name of the database."},
"tablename": {"title": "Name of the table."},
},
"method": "POST",
"input": {"content-type": constants.CSV_MIMETYPE},
},
"empty": {
"title": "Empty the table; remove all rows.",
"href": utils.url_for_unq(
"api_table.empty", dbname="{dbname}", tablename="{tablename}"
),
"variables": {
"dbname": {"title": "Name of the database."},
"tablename": {"title": "Name of the table."},
},
"method": "POST",
},
}
result["operations"]["view"] = {
"create": {
"title": "Create a new view in the database.",
"href": utils.url_for_unq(
"api_view.view", dbname="{dbname}", viewname="{viewname}"
),
"variables": {
"dbname": {"title": "Name of the database."},
"viewname": {"title": "Name of the view."},
},
"method": "PUT",
"input": {
"content-type": constants.JSON_MIMETYPE,
"schema": {"href": schema_base_url + "/view/create"},
},
},
"delete": {
"title": "Delete the view from the database.",
"href": utils.url_for_unq(
"api_view.view", dbname="{dbname}", viewname="{viewname}"
),
"variables": {
"dbname": {"title": "Name of the database."},
"viewname": {"title": "Name of the view."},
},
"method": "DELETE",
},
}
return utils.jsonify(utils.get_json(**result), "/root")
def database(dbname):
"""GET: List the database tables, views and metadata.
PUT: Create the database, load the data if any input.
POST: Edit the database metadata.
DELETE: Delete the database.
"""
if utils.http_GET():
try:
db = dbshare.db.get_check_read(dbname, nrows=True)
except ValueError:
flask.abort(http.client.UNAUTHORIZED)
except KeyError:
flask.abort(http.client.NOT_FOUND)
return utils.jsonify(utils.get_json(**get_json(db, complete=True)),
"/db")
elif utils.http_PUT():
db = dbshare.db.get_db(dbname)
if db is not None:
utils.abort_json(http.client.FORBIDDEN, "database exists")
if not flask.request.content_length:
add_func = None
elif flask.request.content_type is None:
add_func = None
elif flask.request.content_type == constants.SQLITE3_MIMETYPE:
add_func = dbshare.db.add_sqlite3_database
elif flask.request.content_type == constants.XLSX_MIMETYPE:
add_func = dbshare.db.add_xlsx_database
else:
flask.abort(http.client.UNSUPPORTED_MEDIA_TYPE)
try:
if add_func:
db = add_func(
dbname,
io.BytesIO(flask.request.get_data()),
flask.request.content_length,
)
else:
with dbshare.db.DbSaver() as saver:
dbname = saver.set_name(dbname)
saver.initialize()
db = saver.db
except ValueError as error:
utils.abort_json(http.client.BAD_REQUEST, error)
return flask.redirect(flask.url_for(".database", dbname=dbname))
elif utils.http_POST(csrf=False):
try:
db = dbshare.db.get_check_write(dbname)
except ValueError:
flask.abort(http.client.UNAUTHORIZED)
except KeyError:
flask.abort(http.client.NOT_FOUND)
try:
data = flask.request.get_json()
utils.json_validate(data, dbshare.schema.db.edit)
with dbshare.db.DbSaver(db) as saver:
try:
dbname = saver.set_name(data["name"])
except KeyError:
pass
try:
saver.set_title(data["title"])
except KeyError:
pass
try:
saver.set_description(data["description"])
except KeyError:
pass
try:
saver.set_public(data["public"])
except KeyError:
pass
except (jsonschema.ValidationError, ValueError) as error:
utils.abort_json(http.client.BAD_REQUEST, error)
return flask.redirect(flask.url_for(".database", dbname=dbname))
elif utils.http_DELETE(csrf=False):
try:
dbshare.db.get_check_write(dbname)
except ValueError:
flask.abort(http.client.UNAUTHORIZED)
except KeyError:
flask.abort(http.client.NOT_FOUND)
dbshare.db.delete_database(dbname)
return ("", http.client.NO_CONTENT)