[airavata-data-catalog] 02/05: view macro attempt, but Calcite doesn't support PostgreSQL json functions

[machristie]

This is an automated email from the ASF dual-hosted git repository.

machristie pushed a commit to branch calcite
in repository https://gitbox.apache.org/repos/asf/airavata-data-catalog.git

commit d6087eac4347bcbe7094d596dbacd680e9cab1c7
Author: Marcus Christie <machris...@apache.org>
AuthorDate: Mon Feb 27 09:44:55 2023 -0500

    view macro attempt, but Calcite doesn't support PostgreSQL json functions
---
 .../datacatalog/api/query/MetadataQueryParser.java        | 15 ++++++++++++---
 1 file changed, 12 insertions(+), 3 deletions(-)

diff --git 
a/data-catalog-api/server/src/main/java/org/apache/airavata/datacatalog/api/query/MetadataQueryParser.java
 
b/data-catalog-api/server/src/main/java/org/apache/airavata/datacatalog/api/query/MetadataQueryParser.java
index 57d2b17..995497e 100644
--- 
a/data-catalog-api/server/src/main/java/org/apache/airavata/datacatalog/api/query/MetadataQueryParser.java
+++ 
b/data-catalog-api/server/src/main/java/org/apache/airavata/datacatalog/api/query/MetadataQueryParser.java
@@ -5,6 +5,8 @@ import java.sql.DriverManager;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.Collections;
 
 import javax.sql.DataSource;
 
@@ -14,6 +16,7 @@ import org.apache.calcite.plan.RelOptUtil;
 import org.apache.calcite.rel.RelNode;
 import org.apache.calcite.rel.RelRoot;
 import org.apache.calcite.schema.SchemaPlus;
+import org.apache.calcite.schema.impl.ViewTable;
 import org.apache.calcite.sql.SqlExplainFormat;
 import org.apache.calcite.sql.SqlExplainLevel;
 import org.apache.calcite.sql.SqlNode;
@@ -69,8 +72,11 @@ public class MetadataQueryParser {
         // JdbcSchema.create(rootSchema, POSTGRESQL_SCHEMA, 
postgresDataSource, null,
         // null));
         // rootSchema.add("data_product", jdbcSchema.getTable("data_product"));
-        JdbcSchema dcSchema = JdbcSchema.create(rootSchema, "data_catalog", 
postgresDataSource, null, null);
-        rootSchema.add("data_catalog", dcSchema);
+        SchemaPlus dcSchema = rootSchema.add("data_catalog",
+                JdbcSchema.create(rootSchema, "data_catalog", 
postgresDataSource, null, null));
+        dcSchema.add("smilesdb", ViewTable.viewMacro(dcSchema,
+                "select data_product_id, parent_data_product_id, external_id, 
metadata, name from \"data_catalog\".\"data_product\" where 
jsonb_path_exists(\"metadata\", '$.foo == \"bar\"')",
+                Collections.emptyList(), Arrays.asList("data_catalog", 
"smilesdb"), false));
 
         System.out.println("subschemas: " + rootSchema.getSubSchemaNames());
 
@@ -81,8 +87,11 @@ public class MetadataQueryParser {
                 .build();
         // String query = "SELECT * FROM public.\"data_product\" WHERE
         // \"data_product_id\" = 1";
-        String query = "SELECT * FROM data_catalog.data_product WHERE 
data_product_id = 1";
+        // String query = "SELECT * FROM data_catalog.data_product WHERE 
data_product_id
+        // = 1";
         // String query = "SELECT * FROM data_product WHERE data_product_id = 
1";
+        // "smilesdb" view macro
+        String query = "SELECT * FROM data_catalog.smilesdb WHERE 
data_product_id = 1";
         Planner planner = Frameworks.getPlanner(config);
         SqlNode parse = planner.parse(query);