Initial support for fetching 'favorite' fiddles for logged in users, via REST only (no UI support yet)

6 files changed, 331 insertions, 13 deletions

diff --git a/src/main/resources/conf/endpoint-favorites.json b/src/main/resources/conf/endpoint-favorites.json
new file mode 100644
index 0000000..b997abf
--- /dev/null
+++ b/src/main/resources/conf/endpoint-favorites.json
@@ -0,0 +1,5 @@
+{
+    "context" : "endpoint/favorites*",
+    "type" : "text/javascript",
+    "file" : "script/favorites.js"
+}
\ No newline at end of file
diff --git a/src/main/resources/conf/provisioner.openicf-fiddles.json b/src/main/resources/conf/provisioner.openicf-fiddles.json
index 63f3e2f..080dec7 100644
--- a/src/main/resources/conf/provisioner.openicf-fiddles.json
+++ b/src/main/resources/conf/provisioner.openicf-fiddles.json
@@ -5,6 +5,12 @@
         "bundleVersion" : "[1.4.0.0,2.0.0.0)",
         "connectorName" : "org.forgerock.openicf.connectors.scriptedsql.ScriptedSQLConnector"
     },
+    "resultsHandlerConfig" : {
+        "enableNormalizingResultsHandler" : false,
+        "enableFilteredResultsHandler" : false,
+        "enableCaseInsensitiveFilter" : false,
+        "enableAttributesToGetSearchResultsHandler" : false
+    },
     "producerBufferSize" : 100,
     "connectorPoolingSupported" : true,
     "poolConfigOption" : {
@@ -88,26 +94,139 @@
                     "items" : {
                         "type" : "object",
                         "properties" : {
-                            "id" : {
-                                "type" : "integer"
-                            },
                             "schema_def_id" : {
                                 "type" : "integer"
                             },
                             "query_id" : {
                                 "type" : "integer"
-                            },
-                            "num_accesses" : {
+                            }
+                        }
+                    }
+                }
+            }
+        },
+        "user_fiddles" : {
+            "$schema" : "http://json-schema.org/draft-03/schema",
+            "id" : "user_fiddles",
+            "type" : "object",
+            "nativeType" : "user_fiddles",
+            "properties" : {
+                "_id" : {
+                    "type" : "integer",
+                    "required" : false,
+                    "nativeName" : "__UID__",
+                    "nativeType" : "integer",
+                    "flags" : [
+                        "NOT_CREATABLE",
+                        "NOT_UPDATEABLE"
+                    ]
+                },
+                "user_fiddles_id" : {
+                    "type" : "string",
+                    "nativeName" : "__NAME__",
+                    "nativeType" : "string"
+                },
+                "user_id" : {
+                    "type" : "string",
+                    "nativeName" : "user_id",
+                    "nativeType" : "string"
+                },
+                "full_name" : {
+                    "type" : "string",
+                    "nativeName" : "full_name",
+                    "nativeType" : "string"
+                },
+                "db_type_id" : {
+                    "type" : "integer",
+                    "nativeName" : "db_type_id",
+                    "nativeType" : "integer"
+                },
+                "short_code" : {
+                    "type" : "string",
+                    "nativeName" : "short_code",
+                    "nativeType" : "string"
+                },
+                "query_id" : {
+                    "type" : "integer",
+                    "nativeName" : "query_id",
+                    "nativeType" : "integer"
+                },
+                "num_accesses" : {
+                    "type" : "integer",
+                    "nativeName" : "num_accesses",
+                    "nativeType" : "integer"
+                },
+                "last_accessed" : {
+                    "type" : "string",
+                    "nativeName" : "last_accessed",
+                    "nativeType" : "string"
+                },
+                "favorite" : {
+                    "type" : "boolean",
+                    "nativeName" : "favorite",
+                    "nativeType" : "boolean"
+                },
+                "ddl" : {
+                    "type" : "string",
+                    "nativeName" : "ddl",
+                    "nativeType" : "string"
+                },
+                "sql" : {
+                    "type" : "string",
+                    "nativeName" : "sql",
+                    "nativeType" : "string"
+                },
+                "sets" : {
+                    "type": "array",
+                    "nativeName" : "sets",
+                    "nativeType" : "object",
+                    "items" : {
+                        "type" : "object",
+                        "properties" : {
+                            "row_count" : {
                                 "type" : "integer"
                             },
-                            "last_accessed" : {
+                            "columns" : {
                                 "type" : "string"
                             },
-                            "show_in_history" : {
+                            "succeeded" : {
                                 "type" : "boolean"
                             },
-                            "favorite" : {
-                                "type" : "boolean"
+                            "error_message" : {
+                                "type" : "string"
+                            },
+                            "statement_sql" : {
+                                "type" : "string"
+                            }
+                        }
+                    }
+                },
+                "structure" : {
+                    "type": "array",
+                    "nativeName" : "structure",
+                    "nativeType" : "object",
+                    "items" : {
+                        "type" : "object",
+                        "properties" : {
+                            "table_name" : {
+                                "type" : "string"
+                            },
+                            "table_type" : {
+                                "type" : "string"
+                            },
+                            "columns": {
+                                "type": "array",
+                                "items": {
+                                    "type" : "object",
+                                    "properties": {
+                                        "name": {
+                                            "type": "string"
+                                        },
+                                        "type": {
+                                            "type": "string"
+                                        }
+                                    }
+                                }
                             }
                         }
                     }
diff --git a/src/main/resources/db/sqlfiddle/schema.sql b/src/main/resources/db/sqlfiddle/schema.sql
index b8a7607..d6952db 100644
--- a/src/main/resources/db/sqlfiddle/schema.sql
+++ b/src/main/resources/db/sqlfiddle/schema.sql
@@ -215,7 +215,6 @@ CREATE TABLE user_fiddles (
     query_id integer,
     last_accessed timestamp without time zone DEFAULT now(),
     num_accesses integer DEFAULT 1,
-    show_in_history smallint DEFAULT 1,
     favorite smallint DEFAULT 0
 );
 
diff --git a/src/main/resources/script/access.js b/src/main/resources/script/access.js
index b1782c7..5cd00b9 100755
--- a/src/main/resources/script/access.js
+++ b/src/main/resources/script/access.js
@@ -82,6 +82,18 @@ var httpAccessConfig =
            "methods"    : "action,read",
            "actions"    : "getToken"
         },
+        {
+           "pattern"    : "endpoint/favorites",
+           "roles"      : "openidm-authorized",
+           "methods"    : "query",
+           "actions"    : "*"
+        },
+        {
+           "pattern"    : "endpoint/favorites/*",
+           "roles"      : "openidm-authorized",
+           "methods"    : "update",
+           "actions"    : "*"
+        },
         // openidm-admin can request nearly anything (some exceptions being a few system endpoints)
         {
             "pattern"   : "*",
diff --git a/src/main/resources/script/favorites.js b/src/main/resources/script/favorites.js
new file mode 100644
index 0000000..2f88ed2
--- /dev/null
+++ b/src/main/resources/script/favorites.js
@@ -0,0 +1,27 @@
+(function () {
+
+    if (request.method !== "query" && request.method !== "update") {
+        throw {
+            "code" : 400
+        };
+    }
+
+
+    if (request.method === "query") {
+
+        if (!request.queryId || request.queryId !== "myFavorites") {
+            throw {
+                "code" : 400,
+                "message": "Unsupport query request"
+            };
+        }
+
+        return openidm.query("system/fiddles/user_fiddles", {
+                    "_queryFilter": '/favorite eq true AND /user_id eq "' + context.security.authorizationId.id + '"'
+                });
+
+    } else { // request.method === "update"
+
+    }
+
+}());
\ No newline at end of file
diff --git a/src/main/resources/tools/fiddles/SearchScript.groovy b/src/main/resources/tools/fiddles/SearchScript.groovy
index cf31f51..e4f1577 100644
--- a/src/main/resources/tools/fiddles/SearchScript.groovy
+++ b/src/main/resources/tools/fiddles/SearchScript.groovy
@@ -36,6 +36,12 @@ def fieldMap = [
         "__NAME__": "u.subject",
         "__UID__": "u.issuer = ? AND u.subject = ?"
     ],
+    "user_fiddles": [
+        "__UID__": "uf.id",
+        "__NAME__": "u.issuer = ? AND u.subject = ? AND sd.db_type_id = ? AND sd.short_code = ?",
+        "user_id": "u.issuer = ? AND u.subject = ?",
+        "favorite": "uf.favorite = (case when 'true' = ? then 1 else 0 end)"
+    ],
     "db_types": [
         "__NAME__": "d.full_name",
         "__UID__": "d.id"
@@ -75,7 +81,28 @@ queryParser = { queryObj ->
 
 
         // special cases for concatenated-keys
-        if (objectClass.objectClassValue == "users" && queryObj.get("left") == "__UID__") {
+        if (objectClass.objectClassValue == "user_fiddles" && queryObj.get("left") == "__NAME__") {
+            def id_parts = queryObj.get("right").split(":")
+            def queryString = fieldMap[objectClass.objectClassValue][queryObj.get("left")]
+
+            whereParams.push(id_parts[0]) // issuer
+            whereParams.push(id_parts[1]) // subject
+            whereParams.push(id_parts[2].toInteger()) // db_type_id
+            whereParams.push(id_parts[3]) // short_code
+
+            if (id_parts.size() == 5) {
+                whereParams.push(id_parts[4].toInteger()) // query_id
+                queryString += " AND uf.query_id = ?"
+            } else {
+                queryString += " AND uf.query_id IS NULL"
+            }
+
+            return queryString
+
+        } else if (
+                    (objectClass.objectClassValue == "users" && queryObj.get("left") == "__UID__") ||
+                    (objectClass.objectClassValue == "user_fiddles" && queryObj.get("left") == "user_id")
+                ) {
             def user_parts = queryObj.get("right").split(":")
             assert user_parts.size() == 2
 
@@ -108,6 +135,9 @@ queryParser = { queryObj ->
             int rightSide = queryObj.get("right").toInteger()
             return fieldMap[objectClass.objectClassValue][queryObj.get("left")] + " >= (current_timestamp - interval '${rightSide} minutes')"
 
+        } else if (queryObj.get("left") == "favorite") {
+            whereParams.push(queryObj.get("right").toString())
+            return fieldMap[objectClass.objectClassValue][queryObj.get("left")]
         } else {
 
             if (queryObj.get("operation") == "CONTAINS") {
@@ -125,8 +155,9 @@ queryParser = { queryObj ->
             // integer parameters
             } else if (queryObj.get("left") == "schema_def_id" ||
                        queryObj.get("left") == "db_type_id" ||
-                       (objectClass.objectClassValue == "db_types" && queryObj.get("left") == "__UID__")) {
-
+                       (objectClass.objectClassValue == "db_types" && queryObj.get("left") == "__UID__") ||
+                       (objectClass.objectClassValue == "user_fiddles" && queryObj.get("left") == "__UID__")
+                    ) {
                 whereParams.push(queryObj.get("right").toInteger())
 
             } else {
@@ -190,6 +221,131 @@ switch ( objectClass.objectClassValue ) {
     }
     break
 
+    case "user_fiddles":
+
+    def dataCollector = [ uid : "" ]
+    def handleCollectedData = { 
+
+        if (dataCollector.uid != "") {
+            handler {
+                id dataCollector.id
+                uid dataCollector.uid
+                attribute 'user_id', dataCollector.user_id
+                attribute 'db_type_id', dataCollector.db_type_id
+                attribute 'short_code', dataCollector.short_code
+                attribute 'query_id', dataCollector.query_id
+                attribute 'last_accessed', dataCollector.last_accessed
+                attribute 'num_accesses', dataCollector.num_accesses
+                attribute 'favorite', dataCollector.favorite
+                attribute 'structure', dataCollector.structure
+                attribute 'full_name', dataCollector.full_name
+                attribute 'ddl', dataCollector.ddl
+                attribute 'sql', dataCollector.sql
+                attribute 'sets', dataCollector.sets
+            }
+        }
+    }
+    
+    sql.eachRow("""
+        SELECT
+            uf.id,
+            u.issuer,
+            u.subject,
+            to_char(uf.last_accessed, 'YYYY-MM-DD HH24:MI:SS.MS') as last_accessed,
+            uf.num_accesses,
+            uf.favorite,
+            sd.db_type_id,
+            dt.full_name,
+            sd.short_code,
+            sd.ddl,
+            sd.structure_json,
+            uf.query_id,
+            query_plus_sets.query_set_id,
+            query_plus_sets.sql,
+            query_plus_sets.row_count,
+            query_plus_sets.columns,
+            query_plus_sets.succeeded,
+            query_plus_sets.statement_sql,
+            query_plus_sets.error_message
+        FROM
+            user_fiddles uf
+                INNER JOIN schema_defs sd ON
+                    uf.schema_def_id = sd.id
+                INNER JOIN db_types dt ON
+                    sd.db_type_id = dt.id
+                INNER JOIN users u ON
+                    uf.user_id = u.id
+                LEFT OUTER JOIN LATERAL (
+                    SELECT
+                        q.sql,
+                        qs.id as query_set_id,
+                        qs.row_count,
+                        qs.columns_list as columns,
+                        qs.succeeded,
+                        qs.sql as statement_sql,
+                        qs.error_message
+                    FROM
+                        queries q
+                            LEFT OUTER JOIN query_sets qs ON
+                                q.id = qs.query_id AND
+                                q.schema_def_id = qs.schema_def_id
+                    WHERE
+                        q.schema_def_id = uf.schema_def_id AND
+                        q.id = uf.query_id
+
+                ) query_plus_sets ON
+                    (true = true)
+        ${where}
+        ORDER BY
+            uf.last_accessed DESC,
+            uf.id,
+            query_plus_sets.query_set_id
+    """, whereParams) { row ->
+
+        def structure = row.structure_json != null ? (new JsonSlurper()).parseText(row.structure_json) : null
+        String user_fiddles_id = row.issuer + ":" + row.subject + ":" + row.db_type_id + ":" + row.short_code
+
+        if (row.query_id != null) {
+            user_fiddles_id += ":" + row.query_id
+        }
+
+        if (dataCollector.uid != (row.id as String)) {
+            handleCollectedData()
+
+            dataCollector = [
+                id : user_fiddles_id,
+                uid : row.id as String,
+                user_id: row.issuer + ":" + row.subject as String,
+                db_type_id: row.db_type_id,
+                short_code: row.short_code,
+                query_id: row.query_id,
+                last_accessed: row.last_accessed,
+                num_accesses: row.num_accesses,
+                favorite: row.favorite,
+                structure: structure,
+                full_name: row.full_name,
+                ddl: row.ddl,
+                sql: row.sql,
+                sets : [ ]
+            ]
+        }
+
+        if (row.query_id != null) {
+            dataCollector.sets.add([
+                row_count: row.row_count,
+                columns: row.columns,
+                succeeded: row.succeeded,
+                statement_sql: row.statement_sql,
+                error_message: row.error_message
+            ])
+        }
+
+    }
+
+    handleCollectedData()
+
+    break
+
     case "schema_defs":
 
     sql.eachRow("""