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 update(dbname, tablename):
"POST: Update table rows from CSV data (JSON not implemented)."
try:
db = dbshare.db.get_check_write(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:
# CSV input data
if flask.request.content_type == constants.CSV_MIMETYPE:
csvfile = io.BytesIO(flask.request.data)
dbshare.table.update_csv_rows(db, schema, csvfile, ",")
# Unrecognized input data type
else:
flask.abort(http.client.UNSUPPORTED_MEDIA_TYPE)
except (ValueError, sqlite3.Error) as error:
utils.abort_json(http.client.BAD_REQUEST, error)
return flask.redirect(
flask.url_for("api_table.table", dbname=dbname, tablename=tablename))
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 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 empty(dbname, tablename):
"POST: Empty the table; delete all rows."
try:
db = dbshare.db.get_check_write(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:
with dbshare.db.DbSaver(db) as saver:
saver.empty_table(schema)
except sqlite3.Error as error:
utils.abort_json(http.client.BAD_REQUEST, error)
return flask.redirect(
flask.url_for("api_table.table", dbname=dbname, tablename=tablename))
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)
def insert(dbname, tablename):
"POST: Insert rows from JSON or CSV data into the table."
try:
db = dbshare.db.get_check_write(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:
# JSON input data
if flask.request.is_json:
try:
data = flask.request.get_json()
utils.json_validate(data, dbshare.schema.table.input)
except jsonschema.ValidationError as error:
utils.abort_json(http.client.BAD_REQUEST, error)
columns = schema["columns"]
# Check validity of values in input data.
rows = []
for pos, item in enumerate(data["data"]):
values = []
for column in columns:
try:
value = item[column["name"]]
except KeyError:
if column.get("notnull"):
raise ValueError(f"missing key '{column['name']}'"
f" in item # {pos}")
value = None
else:
try:
if column["type"] == constants.INTEGER:
if not isinstance(value, int):
raise TypeError
elif column["type"] == constants.REAL:
if not isinstance(value, (int, float)):
raise TypeError
elif column["type"] == constants.TEXT:
if not isinstance(value, str):
raise TypeError
elif column["type"] == constants.BLOB:
raise TypeError
except TypeError:
raise ValueError(f"'{column['name']}'invalid type"
f" in item # {pos}")
values.append(value)
rows.append(values)
# CSV input data
elif flask.request.content_type == constants.CSV_MIMETYPE:
csvfile = io.BytesIO(flask.request.data)
rows = dbshare.table.get_csv_rows(schema, csvfile, ",", True)
# Unrecognized input data type
else:
flask.abort(http.client.UNSUPPORTED_MEDIA_TYPE)
dbshare.table.insert_rows(db, schema, rows)
except (ValueError, sqlite3.Error) as error:
utils.abort_json(http.client.BAD_REQUEST, error)
return flask.redirect(
flask.url_for("api_table.table", dbname=dbname, tablename=tablename))