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))