def explore(resource_id, owner, table):
"""
Database exploration: returns the first rows of
a database table. The db credentials are retrieved from
Pyrog. The number of returned rows may be specified using
query params (eg: /explore/<resource_id>/<table>?first=10).
"""
limit = request.args.get("first", 10, type=int)
# Get headers
authorization_header = request.headers.get("Authorization")
pyrog_client = pyrog.PyrogClient(authorization_header)
resource = pyrog_client.get_resource(resource_id)
# Get credentials
if not resource["source"]["credential"]:
raise OperationOutcome("credentialId is required to explore the DB.")
credentials = resource["source"]["credential"]
# Get filters
filters = resource["filters"]
try:
explorer = DatabaseExplorer(credentials)
return jsonify(
explorer.explore(owner, table, limit=limit, filters=filters))
except OperationalError as e:
if "could not connect to server" in str(e):
raise OperationOutcome(f"Could not connect to the database: {e}")
else:
raise OperationOutcome(e)
except Exception as e:
raise OperationOutcome(e)
def get_owner_schema(owner):
credentials = request.get_json()
try:
explorer = DatabaseExplorer(credentials)
db_schema = explorer.get_owner_schema(owner)
return jsonify(db_schema)
except OperationalError as e:
if "could not connect to server" in str(e):
raise OperationOutcome(f"Could not connect to the database: {e}")
else:
raise OperationOutcome(e)
except Exception as e:
raise OperationOutcome(e)
def get_resource(self, resource_id):
resp = self.run_graphql_query(resource_query,
variables={"resourceId": resource_id})
resource = resp["data"]["resource"]
if not resource:
raise OperationOutcome(
f"Resource with id {resource_id} does not exist")
return resource
def __init__(self, db_config: Optional[dict] = None):
self._db_model = db_config.get("model")
if self._db_model not in DB_DRIVERS:
raise OperationOutcome(f"Database type {self._db_model} is unknown")
self._sql_engine = create_engine(get_sql_url(self._db_model, db_config))
self._metadata = MetaData(bind=self._sql_engine)
self.db_schema = {}
def run_graphql_query(self,
graphql_query,
variables=None,
auth_required=True):
"""
This function queries a GraphQL endpoint and returns a json parsed
response. If auth_required is true, the auth token will be passed
in an Authorization header (an error will be raised if
the token is missing).
"""
if not PYROG_URL:
raise OperationOutcome("PYROG_URL is missing from environment")
try:
response = requests.post(
PYROG_URL,
headers=self.headers,
json={
"query": graphql_query,
"variables": variables
},
)
except requests.exceptions.ConnectionError:
raise OperationOutcome("Could not connect to the Pyrog service")
if response.status_code != 200:
raise OperationOutcome(
"Graphql query failed with returning code "
f"{response.status_code}\n{response.json()}.")
body = response.json()
if "errors" in body:
status_code = body["errors"][0].get("statusCode")
error_message = body["errors"][0].get("message")
if status_code == 401:
raise AuthenticationError(error_message)
if status_code == 403:
raise AuthorizationError(
"You don't have the rights to perform this action.")
raise OperationOutcome(
f"GraphQL query failed with errors: {[err['message'] for err in body['errors']]}."
)
return body
def __init__(self, auth_header):
if not auth_header:
# Note that the id token is not mandatory because pyrog-server can
# introspect the access to token with Hydra
raise OperationOutcome(
"An authorization token is required to forward queries to Pyrog-server"
)
self.headers = {
"content-type": "application/json",
"Authorization": auth_header
}
def explore(self, owner: str, table_name: str, limit: int, filters=[]):
"""
Returns the first rows of a table alongside the column names.
"""
self.check_connection_exists()
with session_scope(self) as session:
try:
return self.get_table_rows(
session=session,
owner=owner,
table_name=table_name,
limit=limit,
filters=filters,
)
except InvalidRequestError as e:
if "requested table(s) not available" in str(e):
raise OperationOutcome(f"Table {table_name} does not exist in database")
else:
raise OperationOutcome(e)
except Exception as e:
raise OperationOutcome(e)
def check_connection_exists(self):
if not self._sql_engine:
raise OperationOutcome("DatabaseExplorer was not provided with any credentials.")