machristie opened a new pull request, #22:
URL: https://github.com/apache/airavata-data-catalog/pull/22
This is for #5. This PR does the work of converting an API supplied SQL
query against a metadata schema into the actual PostgreSQL query that will use
JSONB function to filter data products matching the query.
See DataCatalogAPIClient.java for examples on how to use the API, but from
one of the examples, this query is what is requested from the API:
```sql
SELECT
*
FROM
my_schema
WHERE
(
field1 < 5
OR field3 = 'bar'
)
AND field1 > 0
AND external_id = 'fff';
```
Calcite is used to parse and validate the `my_schema` metadata schema. It
also validates that `field1` and `field3` are defined metadata schema fields in
the metadata schema. Information about the validated query is then used by Data
Catalog to construct the following query:
```sql
WITH my_schema AS (
SELECT
dp_.data_product_id,
dp_.parent_data_product_id,
dp_.external_id,
dp_.name,
dp_.metadata
FROM
data_product dp_
INNER JOIN data_product_metadata_schema dpms_ ON
dpms_.data_product_id = dp_.data_product_id
INNER JOIN metadata_schema ms_ ON ms_.metadata_schema_id =
dpms_.metadata_schema_id
WHERE
ms_.metadata_schema_id = 1
)
SELECT
*
FROM
"my_schema"
WHERE
(
(
(
my_schema.metadata @@ '$.field1 <5'
OR my_schema.metadata @@ '$.field3 == "bar"'
)
AND my_schema.metadata @@ '$.field1 >0'
)
AND "my_schema"."external_id" = 'fff'
);
```
This example is just illustrative, but it shows how a WITH statement is used
to define what is effectively a virtual table for each metadata schema (only
one is shown here) and how the metadata field filters are rewritten as JSONB
filter expressions.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
