def get_json(db, view, complete=False, title=False):
"Return the JSON for the view."
result = {"name": view["name"]}
if complete or title:
result["description"] = view.get("description")
result["title"] = view.get("title")
result["nrows"] = view.get("nrows")
result["rows"] = {
"href": utils.url_for(
"api_view.rows_json", dbname=db["name"], viewname=view["name"]
)
}
result["data"] = {
"href": utils.url_for(
"api_view.rows_csv", dbname=db["name"], viewname=view["name"]
),
"content-type": constants.CSV_MIMETYPE,
"format": "csv",
}
if complete:
result["database"] = {
"href": utils.url_for("api_db.database", dbname=db["name"])
}
else:
result["href"] = utils.url_for(
"api_view.view", dbname=db["name"], viewname=view["name"]
)
return result
def get_json(dbs):
"Return JSON for the databases."
result = []
for db in dbs:
data = dbshare.api.db.get_json(db)
data["href"] = utils.url_for("api_db.database", dbname=db["name"])
result.append(data)
return result
def get_json(db, table, complete=False, title=False):
"Return JSON for the table."
result = {"name": table["name"]}
if complete or title:
result["description"] = table.get("description")
result["title"] = table.get("title")
result["nrows"] = table["nrows"]
result["rows"] = {
"href":
utils.url_for("api_table.rows_json",
dbname=db["name"],
tablename=table["name"])
}
result["data"] = {
"href":
utils.url_for("api_table.rows_csv",
dbname=db["name"],
tablename=table["name"]),
"content-type":
constants.CSV_MIMETYPE,
"format":
"csv",
}
if complete:
result["database"] = {
"href": utils.url_for("api_db.database", dbname=db["name"])
}
result["statistics"] = {
"href":
utils.url_for("api_table.statistics",
dbname=db["name"],
tablename=table["name"])
}
result["indexes"] = [
i for i in db["indexes"].values() if i["table"] == table["name"]
]
for i in result["indexes"]:
i.pop("table")
else:
result["href"] = utils.url_for("api_table.table",
dbname=db["name"],
tablename=table["name"])
return result
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 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 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 get_json(username):
"Get the JSON for a user or owner."
return {
"username": username,
"href": utils.url_for("api_user.user", username=username),
}
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 set_schema_base_url():
"Must be done after URL routes have been defined."
dbshare.schema.set_base_url(utils.url_for("api_schema.schema"))