diff options
author | jakefeasel <jfeasel@gmail.com> | 2014-09-02 23:26:14 -0700 |
---|---|---|
committer | jakefeasel <jfeasel@gmail.com> | 2014-09-02 23:26:14 -0700 |
commit | ff0e37e8d795714dd5d116194fdb44ab7bd12d7f (patch) | |
tree | 4f87368c7df48c5dba0cd2aacfa88d885db9f30e /src | |
parent | 2f98b46a765e966e7031236b05e9d2720c0ffa37 (diff) | |
download | sqlfiddle2-ff0e37e8d795714dd5d116194fdb44ab7bd12d7f.zip sqlfiddle2-ff0e37e8d795714dd5d116194fdb44ab7bd12d7f.tar.gz sqlfiddle2-ff0e37e8d795714dd5d116194fdb44ab7bd12d7f.tar.bz2 |
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.
Diffstat (limited to 'src')
-rw-r--r-- | src/main/resources/conf/provisioner.openicf-fiddles.json | 186 | ||||
-rw-r--r-- | src/main/resources/script/createSchema.groovy | 30 | ||||
-rw-r--r-- | src/main/resources/script/executeQuery.groovy | 40 | ||||
-rw-r--r-- | src/main/resources/tools/fiddles/SearchScript.groovy | 104 |
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": |