summaryrefslogtreecommitdiffstats
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/main/resources/conf/provisioner.openicf-fiddles.json186
-rw-r--r--src/main/resources/script/createSchema.groovy30
-rw-r--r--src/main/resources/script/executeQuery.groovy40
-rw-r--r--src/main/resources/tools/fiddles/SearchScript.groovy104
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":
generated by cgit v1.1 at 2025-05-17 23:30:24 +0000