Updating fiddle provisioner config to take advantage of ScriptedSQL

1.4 complex object support, primarily to begin the implementation of
tracking query sets as child objects of queries.

4 files changed, 193 insertions, 167 deletions

diff --git a/src/main/resources/conf/provisioner.openicf-fiddles.json b/src/main/resources/conf/provisioner.openicf-fiddles.json
index 24a00ae..a2d8a3c 100644
--- a/src/main/resources/conf/provisioner.openicf-fiddles.json
+++ b/src/main/resources/conf/provisioner.openicf-fiddles.json
@@ -150,36 +150,6 @@
                         "NOT_UPDATEABLE"
                     ]
                 },
-                "execution_plan_prefix" : {
-                    "type" : "string",
-                    "required" : false,
-                    "nativeName" : "execution_plan_prefix",
-                    "nativeType" : "string",
-                    "flags" : [
-                        "NOT_CREATABLE",
-                        "NOT_UPDATEABLE"
-                    ]
-                },
-                "execution_plan_suffix" : {
-                    "type" : "string",
-                    "required" : false,
-                    "nativeName" : "execution_plan_suffix",
-                    "nativeType" : "string",
-                    "flags" : [
-                        "NOT_CREATABLE",
-                        "NOT_UPDATEABLE"
-                    ]
-                },
-                "execution_plan_xslt" : {
-                    "type" : "string",
-                    "required" : false,
-                    "nativeName" : "execution_plan_xslt",
-                    "nativeType" : "string",
-                    "flags" : [
-                        "NOT_CREATABLE",
-                        "NOT_UPDATEABLE"
-                    ]
-                },
                 "md5" : {
                     "type" : "string",
                     "required" : false,
@@ -190,26 +160,6 @@
                         "NOT_UPDATEABLE"
                     ]
                 },                
-                "simple_name" : {
-                    "type" : "string",
-                    "required" : false,
-                    "nativeName" : "simple_name",
-                    "nativeType" : "string",
-                    "flags" : [
-                        "NOT_CREATABLE",
-                        "NOT_UPDATEABLE"
-                    ]
-                },
-                "full_name" : {
-                    "type" : "string",
-                    "required" : false,
-                    "nativeName" : "full_name",
-                    "nativeType" : "string",
-                    "flags" : [
-                        "NOT_CREATABLE",
-                        "NOT_UPDATEABLE"
-                    ]
-                },
                 "short_code" : {
                     "type" : "string",
                     "required" : true,
@@ -252,11 +202,40 @@
                     "nativeName" : "statement_separator",
                     "nativeType" : "string"
                 },
-                "batch_separator" : {
-                    "type" : "string",
+                "relationships" : {
+                    "type" : "object",
                     "required" : false,
-                    "nativeName" : "batch_separator",
-                    "nativeType" : "string"
+                    "properties" : {
+                        "db_type" : {
+                            "type" : "object",
+                            "properties" : {
+                                "id" : {
+                                    "type" : "integer"
+                                },
+                                "context" : {
+                                    "type" : "string" 
+                                },
+                                "execution_plan_prefix" : {
+                                    "type" : "string"
+                                },
+                                "execution_plan_suffix" : {
+                                    "type" : "string"
+                                },
+                                "execution_plan_xslt" : {
+                                    "type" : "string"
+                                },
+                                "simple_name" : {
+                                    "type" : "string"
+                                },
+                                "full_name" : {
+                                    "type" : "string"
+                                },
+                                "batch_separator" : {
+                                    "type" : "string"
+                                }
+                            }
+                        }
+                    }
                 }
             }
         },
@@ -298,69 +277,44 @@
                     "required" : false,
                     "nativeName" : "query_id",
                     "nativeType" : "integer"
-                }
-            }
-        },
-        "query_sets": {
-            "$schema" : "http://json-schema.org/draft-03/schema",
-            "id" : "query_sets",
-            "type" : "object",
-            "nativeType" : "query_sets",
-            "properties" : {
-                "_id" : {
-                    "type" : "integer",
-                    "nativeName" : "__UID__",
-                    "nativeType" : "integer"
-                },
-                "query_set_id" : {
-                    "type" : "integer",
-                    "nativeName" : "id",
-                    "nativeType" : "integer"
-                },
-                "query_id" : {
-                    "type" : "integer",
-                    "nativeName" : "query_id",
-                    "nativeType" : "integer"
-                },
-                "schema_def_id" : {
-                    "type" : "integer",
-                    "nativeName" : "schema_def_id",
-                    "nativeType" : "integer"
                 },
-                "row_count" : {
-                    "type" : "integer",
-                    "nativeName" : "row_count",
-                    "nativeType" : "integer"
-                },
-                "execution_time" : {
-                    "type" : "string",
-                    "nativeName" : "execution_time",
-                    "nativeType" : "string"
-                },
-                "execution_plan" : {
-                    "type" : "string",
-                    "nativeName" : "execution_plan",
-                    "nativeType" : "string"
-                },
-                "succeeded" : {
-                    "type" : "string",
-                    "nativeName" : "succeeded",
-                    "nativeType" : "string"
-                },
-                "error_message" : {
-                    "type" : "string",
-                    "nativeName" : "error_message",
-                    "nativeType" : "string"
-                },
-                "sql" : {
-                    "type" : "string",
-                    "nativeName" : "sql",
-                    "nativeType" : "string"
-                },
-                "columns_list" : {
-                    "type" : "string",
-                    "nativeName" : "columns_list",
-                    "nativeType" : "string"
+                "relationships" : {
+                    "type" : "object",
+                    "required" : false,
+                    "properties" : {
+                        "query_sets" : {
+                            "type": "array",
+                            "items" : {
+                                "type" : "object",
+                                "properties" : {
+                                    "id" : {
+                                        "type" : "integer"
+                                    },
+                                    "row_count" : {
+                                        "type" : "integer"
+                                    },
+                                    "execution_time" : {
+                                        "type" : "string"
+                                    },
+                                    "execution_plan" : {
+                                        "type" : "string"
+                                    },
+                                    "succeeded" : {
+                                        "type" : "string"
+                                    },
+                                    "error_message" : {
+                                        "type" : "string"
+                                    },
+                                    "sql" : {
+                                        "type" : "string"
+                                    },
+                                    "columns_list" : {
+                                        "type" : "string"
+                                    }
+                                }
+                            }
+                        }
+                    }
                 }
             }
         }
diff --git a/src/main/resources/script/createSchema.groovy b/src/main/resources/script/createSchema.groovy
index d5c73c6..2025c1c 100644
--- a/src/main/resources/script/createSchema.groovy
+++ b/src/main/resources/script/createSchema.groovy
@@ -10,6 +10,7 @@ try {
     assert content.db_type_id && content.db_type_id instanceof Integer
     assert content.ddl.size() <= 8000
 
+    def db_type = openidm.read("system/fiddles/db_types/" + content.db_type_id)
     def existing_schema = []
     def schema_def
     def md5hash
@@ -54,18 +55,22 @@ try {
                 }
         }
 
-        // if there is an error thrown from here, it will be caught below;
-        // It is necessary to build the real db at this stage so that we can fail early if there
-        // is a problem (and get a handle on the real error involved in the creation)
-        openidm.create("system/hosts/databases", null, [
-            "db_type_id": content.db_type_id,
-            "schema_name": "db_" + content.db_type_id + "_" + short_code,
-            "username": "user_" + content.db_type_id + "_" + short_code,
-            "pw": content.db_type_id + "_" + short_code,
-            "ddl": content.ddl,
-            "statement_separator": content.statement_separator
-        ])
+        // we only need to attempt to create a DB if the context for it is "host"
+        if (db_type.context == "host") {
+
+            // if there is an error thrown from here, it will be caught below;
+            // It is necessary to build the real db at this stage so that we can fail early if there
+            // is a problem (and get a handle on the real error involved in the creation)
+            openidm.create("system/hosts/databases", null, [
+                "db_type_id": content.db_type_id,
+                "schema_name": "db_" + content.db_type_id + "_" + short_code,
+                "username": "user_" + content.db_type_id + "_" + short_code,
+                "pw": content.db_type_id + "_" + short_code,
+                "ddl": content.ddl,
+                "statement_separator": content.statement_separator
+            ])
 
+        }
         // this schema_def will be linked to the above running db below as part of reconById
         schema_def = openidm.create("system/fiddles/schema_defs", null, [
             "db_type_id": content.db_type_id,
@@ -83,9 +88,10 @@ try {
 
     assert fragment_parts.size() == 2
 
-    if (schema_def.context == "host") {
+    if (db_type.context == "host") {
 
         // this ensures that there is a live running db up for the schema_def
+        // if this schema was just created for the first time, then it will link to the newly-created DB from above
         response.reconId = openidm.action("recon", "reconById", [:],
             [
                 "mapping" : "fiddles_hosts",
diff --git a/src/main/resources/script/executeQuery.groovy b/src/main/resources/script/executeQuery.groovy
index 58c6c61..44520ba 100644
--- a/src/main/resources/script/executeQuery.groovy
+++ b/src/main/resources/script/executeQuery.groovy
@@ -89,12 +89,14 @@ def schema_def = openidm.read("system/fiddles/schema_defs/" + content.db_type_id
 
 assert schema_def != null
 
+def db_type = schema_def.relationships.db_type
+
 // Update the timestamp for the schema_def each time this instance is used, so we know if it should stay running longer
 schema_def.last_used = (new Date().format("yyyy-MM-dd HH:mm:ss.S"))
 openidm.update("system/fiddles/schema_defs/" + schema_def._id, null, schema_def)
 
-// Save a copy of this query (or retrieve the id of one that already exists)
-def m = openidm.create("system/fiddles/queries", 
+// Save a copy of this query (or retrieve the details of one that already exists)
+def query = openidm.create("system/fiddles/queries", 
     null, 
     [
         "md5": "n/a",
@@ -102,15 +104,11 @@ def m = openidm.create("system/fiddles/queries",
         "statement_separator": content.statement_separator,
         "schema_def_id": schema_def.schema_def_id
     ]
-)._id =~ /^\d+_\w+_(\d+)*$/
-
-
-
-int queryId = m[0][1].toInteger()
+)
 
-def response = [ID: queryId]
+def response = [ID: query.query_id]
 
-if (schema_def.context == "host") {
+if (db_type.context == "host") {
 
     // Use the presence of a link between fiddle and host db to determine if we need to provision a running instance of this db
     def hostLink = openidm.query("repo/link", [
@@ -148,7 +146,7 @@ if (schema_def.context == "host") {
     hostConnection.withStatement { it.queryTimeout = 10 }
 
     // mysql handles transactions poorly; better to just make the whole thing readonly
-    if (schema_def.simple_name == "MySQL") {
+    if (db_type.simple_name == "MySQL") {
         hostConnection.getConnection().setReadOnly(true)
     }
 
@@ -164,12 +162,12 @@ if (schema_def.context == "host") {
             char carrageReturn = 13
             def statementGroups = Pattern.compile("([\\s\\S]*?)(?=(" + separator + "\\s*)|\$)")
 
-            if (schema_def.batch_separator && schema_def.batch_separator.size()) {
-                content.sql = content.sql.replaceAll(Pattern.compile(newline + schema_def.batch_separator + carrageReturn + "?(" + newline + "|\$)", Pattern.CASE_INSENSITIVE), separator)
+            if (db_type.batch_separator && db_type.batch_separator.size()) {
+                content.sql = content.sql.replaceAll(Pattern.compile(newline + db_type.batch_separator + carrageReturn + "?(" + newline + "|\$)", Pattern.CASE_INSENSITIVE), separator)
             }
-            if (schema_def.simple_name == "Oracle") {
+            if (db_type.simple_name == "Oracle") {
                 hostConnection.execute("INSERT INTO system." + deferred_table + " VALUES (2)")
-            } else if (schema_def.simple_name == "PostgreSQL" ) {
+            } else if (db_type.simple_name == "PostgreSQL" ) {
                 hostConnection.execute("INSERT INTO " + deferred_table + " VALUES (2)")
             }
 
@@ -180,19 +178,19 @@ if (schema_def.context == "host") {
 
                         def executionPlan = null
 
-                        if (schema_def.execution_plan_prefix || schema_def.execution_plan_suffix) {
-                            def executionPlanSQL = (schema_def.execution_plan_prefix?:"") + statement[1] + (schema_def.execution_plan_suffix?:"")
+                        if (db_type.execution_plan_prefix || db_type.execution_plan_suffix) {
+                            def executionPlanSQL = (db_type.execution_plan_prefix?:"") + statement[1] + (db_type.execution_plan_suffix?:"")
                             executionPlanSQL = executionPlanSQL.replaceAll("#schema_short_code#", schema_def.short_code)
-                            executionPlanSQL = executionPlanSQL.replaceAll("#query_id#", queryId.toString())
+                            executionPlanSQL = executionPlanSQL.replaceAll("#query_id#", query.query_id.toString())
 
-                            if (schema_def.batch_separator && schema_def.batch_separator.size()) {
-                                executionPlanSQL = executionPlanSQL.replaceAll(Pattern.compile(newline + schema_def.batch_separator + carrageReturn + "?(" + newline + "|\$)", Pattern.CASE_INSENSITIVE), separator)
+                            if (db_type.batch_separator && db_type.batch_separator.size()) {
+                                executionPlanSQL = executionPlanSQL.replaceAll(Pattern.compile(newline + db_type.batch_separator + carrageReturn + "?(" + newline + "|\$)", Pattern.CASE_INSENSITIVE), separator)
                             }
 
                             // the savepoint for postgres allows us to fail safely if users provide DDL in their queries;
                             // normally, this would result in an exception that breaks the rest of the transaction. Save points
                             // preserve the transaction.
-                            if (schema_def.simple_name == "PostgreSQL") {
+                            if (db_type.simple_name == "PostgreSQL") {
                                 hostConnection.execute("SAVEPOINT sp;")
                             }
 
@@ -202,7 +200,7 @@ if (schema_def.context == "host") {
                                 }
                             }
                             
-                            if (schema_def.simple_name == "PostgreSQL") {
+                            if (db_type.simple_name == "PostgreSQL") {
                                 hostConnection.execute("ROLLBACK TO sp;")
                             }
 
diff --git a/src/main/resources/tools/fiddles/SearchScript.groovy b/src/main/resources/tools/fiddles/SearchScript.groovy
index 82d58c9..1861a8a 100644
--- a/src/main/resources/tools/fiddles/SearchScript.groovy
+++ b/src/main/resources/tools/fiddles/SearchScript.groovy
@@ -177,18 +177,48 @@ switch ( objectClass.objectClassValue ) {
             attribute 'minutes_since_last_used', (row.minutes_since_last_used != null ? row.minutes_since_last_used.toInteger(): null)
             attribute 'short_code', row.short_code
             attribute 'statement_separator', row.statement_separator
-            attribute 'simple_name', row.simple_name
-            attribute 'full_name', row.full_name
-            attribute 'execution_plan_prefix', row.execution_plan_prefix
-            attribute 'execution_plan_suffix', row.execution_plan_suffix
-            attribute 'execution_plan_xslt', row.execution_plan_xslt
-            attribute 'batch_separator', row.batch_separator
+            attribute 'relationships', [
+                'db_type' : [
+                    id : row.db_type_id.toInteger(),
+                    context : row.context,
+                    simple_name : row.simple_name,
+                    full_name : row.full_name,
+                    execution_plan_prefix : row.execution_plan_prefix,
+                    execution_plan_suffix : row.execution_plan_suffix,
+                    execution_plan_xslt : row.execution_plan_xslt,
+                    batch_separator : row.batch_separator
+                ]
+            ]
         }
 
     }
     break
 
     case "queries":
+
+    def dataCollector = [ uid: "" ]
+
+    def handleCollectedData = {
+        if (dataCollector.uid != "") {
+            // we must be done with the previous set, so handle it
+
+            handler {
+                id dataCollector.id
+                uid dataCollector.uid
+                attribute 'fragment', dataCollector.uid
+                attribute 'md5', dataCollector.id
+                attribute 'query_id', dataCollector.query_id
+                attribute 'schema_def_id', dataCollector.schema_def_id
+                attribute 'sql', dataCollector.sql
+                attribute 'statement_separator', dataCollector.statement_separator
+                attribute 'relationships', [
+                    'query_sets' : dataCollector.relationships.query_sets
+                ]
+            }
+
+        }
+    }
+
     sql.eachRow("""
         SELECT 
             q.schema_def_id,
@@ -197,24 +227,62 @@ switch ( objectClass.objectClassValue ) {
             s.short_code,
             q.sql,
             q.statement_separator,
-            q.md5
+            q.md5,
+            qs.id as query_set_id,
+            qs.row_count,
+            qs.execution_time,
+            qs.execution_plan,
+            qs.succeeded,
+            qs.error_message,
+            qs.sql as query_set_sql,
+            qs.columns_list
         FROM 
-            schema_defs s 
+            schema_defs s
                 INNER JOIN queries q ON
                     q.schema_def_id = s.id
-        """ + where, whereParams) { row ->
-        handler {
-            id row.md5
-            uid (row.db_type_id + '_' + row.short_code + '_' + row.id) as String
-            attribute 'fragment', row.db_type_id + '_' + row.short_code + '_' + row.id
-            attribute 'md5', row.md5
-            attribute 'query_id', row.id.toInteger()
-            attribute 'schema_def_id', row.schema_def_id.toInteger()
-            attribute 'sql', row.sql
-            attribute 'statement_separator', row.statement_separator
+                LEFT OUTER JOIN query_sets qs ON
+                    q.id = qs.query_id AND
+                    q.schema_def_id = qs.schema_def_id
+        ${where}
+        ORDER BY
+            q.schema_def_id,
+            q.id,
+            qs.id
+        """, whereParams) { row ->
+
+        if (dataCollector.uid != row.db_type_id + '_' + row.short_code + '_' + row.id) {
+
+            handleCollectedData();
+
+            dataCollector = [
+                id : row.md5,
+                uid : (row.db_type_id + '_' + row.short_code + '_' + row.id) as String,
+                query_id : row.id.toInteger(),
+                schema_def_id : row.schema_def_id.toInteger(),
+                sql : row.sql,
+                statement_separator : row.statement_separator,
+                relationships : [
+                    query_sets : [ ]
+                ]
+            ]
         }
 
+        if (row.query_set_id) {
+            dataCollector.relationships.query_sets.add([
+                id : row.query_set_id.toInteger(),
+                row_count : row.row_count,
+                execution_time : row.execution_time,
+                execution_plan : row.execution_plan,
+                succeeded : row.succeeded,
+                error_message : row.error_message,
+                sql : row.query_set_sql,
+                columns_list : row.columns_list
+            ])
+        }
     }
+
+    handleCollectedData();
+
     break
 
     case "db_types":