Create FRIM 7.2.1 relationship view from FRIM 5.5.1.3

Mar 26, 2023 • admin • Category: Forgerock Frim

The following is a quick guide to creating a FRIM 7.2.1 view of a FRIM 5.5.1.3 RELATIONSHIP table, which could be used for migration purposes

Overview

Given a FRIM 5.5.1.3 Relationship table Schema with data

ID	OBJECTTYPES_ID	OBJECTID	REV	FULLOBJECT–
`3`	`7`	`70d1386b-8290-4ec4-ad86-0316b7e4f24d`	`0`	`{"firstId":"managed/credential/04973da5-c618-4498-bf93-ef9eaddcae88","firstPropertyName":"userName","secondId":"managed/user/c5a3e54f-bb4b-4932-b814-9cdce75a3147","secondPropertyName":"credential","properties":{},"_id":"70d1386b-8290-4ec4-ad86-0316b7e4f24d","_rev":"0"}`

we need to translate it to the FRIM 7.2.1 RELATIONSHIP Table Schema

ID	OJECTTYPES_ID	OBJECTID	REV	FULLOBJECT	FIRSTRESOURCECOLLECTION	FIRSTRESOURCEID	FIRSTPROPERTYNAME	SECONDRESOURCECOLLECTION	SECONDRESOURCEID	SECONDPROPERTYNAME
`3`	`70d1386b-8290-4ec4-ad86-0316b7e4f24d`	`10`	`0`	`{"firstResourceCollection":"managed/credential","firstResourceId":"04973da5-c618-4498-bf93-ef9eaddcae88","firstPropertyName":"userName","secondResourceCollection":"managed/user","secondResourceId":"c5a3e54f-bb4b-4932-b814-9cdce75a3147","secondPropertyName":"credential","properties":null,"_id":"70d1386b-8290-4ec4-ad86-0316b7e4f24d","_rev":0}`	`managed/credential`	`04973da5-c618-4498-bf93-ef9eaddcae88`	`userName`	`managed/user`	`c5a3e54f-bb4b-4932-b814-9cdce75a3147`	`credential`

During a normal migration the following script is run

/*
 * Copyright 2018 ForgeRock AS. All Rights Reserved
 *
 * Use of this code requires a commercial software license with ForgeRock AS.
 * or with one of its affiliates. All use shall be exclusively subject
 * to such license between the licensee and ForgeRock AS.
 */

var pagedResultsCookie = null;
/** Sets the initial query params. */
var params = {
    '_queryFilter': 'true',
    '_pageSize': 1000
};

do {
    var relationships = openidm.query('repo/relationships', params);
    if (pagedResultsCookie != null) {
        params._pagedResultsCookie = pagedResultsCookie;
    }
    /** update the cookie with the new one from the query. */
    pagedResultsCookie = relationships.pagedResultsCookie;

    relationships.result.forEach(function(r) {
        openidm.update("repo/relationships/" + r._id, r._rev, {
            "_id": r._id,
            "_rev": r._rev,
            "firstResourceId": org.forgerock.json.resource.ResourcePath.valueOf(r.firstId).leaf(),
            "firstResourceCollection": org.forgerock.json.resource.ResourcePath.valueOf(r.firstId).parent().toString(),
            "firstPropertyName": r.firstPropertyName,
            "secondResourceId": org.forgerock.json.resource.ResourcePath.valueOf(r.secondId).leaf(),
            "secondResourceCollection": org.forgerock.json.resource.ResourcePath.valueOf(r.secondId).parent().toString(),
            "secondPropertyName": r.secondPropertyName,
            "properties": r.properties
        });
    });
} while (pagedResultsCookie !== null);

In essence this loops through all the existing 5.5.1.3 relatonships and converts them into the 7.2.1 format.

We can emulate this functionality through creating a view that translates the data in a similair way, and for this it is necessary to have a function funcResourcePath that converts a Managed Object Path into its component parts of parent and leaf. This function will require a object that contains these values so that they can be returned and used in the view. Finally this object can be used in the view.

Create Object

We need to create an object that contains the parent and leaf data. Since a Managed Object is identified by a UUID we can set the leaf value to be a VARCHAR2 of length 36. For the parent this could be very large, but for this we are going to set it to a length of 255 characters.

create or replace type resourcePath as object
    ( parent VARCHAR2(255),
      leaf VARCHAR2(36)
    )

Create Function

given a string managed/credential/04973da5-c618-4498-bf93-ef9eaddcae88 it will convert into resourcepath object

parent	leaf
`managed/credential`	`04973da5-c618-4498-bf93-ef9eaddcae88`

We use the regexp_substr function to help us use a regular expression to find the 2 groups parent and leaf values. It may not be the most efficient and tuning will be required to find the best way to make this efficient for daily usage. the regular expression is (.*)\/(.*) which will split a MO string into the 2 component parts. Finally the parent and leaf values are returned in a resourcePath object.

create or replace function funcResourcePath
   (p_path IN VARCHAR2)
   RETURN resourcePath
IS
   v_file resourcePath;
   parent varchar2(255);
   leaf varchar2(36);
BEGIN
    leaf := regexp_substr(p_path, '(.*)\/(.*)',1,1,NULL,2);
    parent := regexp_substr(p_path, '(.*)\/(.*)',1,1,NULL,1);
    v_file := resourcePath(parent,leaf);
    RETURN
       v_file;
END;

Ceate View

Since FRM 5.5.1.3 stores the data needed in a JSON string in a CLOB we need to be able to convert that CLOB into a JSON_TABLE and then use that table to extract the necessary values for the FRIM 7.2.1 RELATONSHIP table.

       JSON_TABLE(fullobject, '$'
         COLUMNS (FIRSTID            VARCHAR2(100 CHAR) PATH '$.firstId',
                  FIRSTPROPERTYNAME  VARCHAR2(50 CHAR)  PATH '$.firstPropertyName',
                  SECONDID           VARCHAR2(100 CHAR) PATH '$.secondId',
                  SECONDPROPERTYNAME VARCHAR2(50 CHAR)  PATH '$.secondPropertyName',
                  PROPS              VARCHAR2(50 CHAR)  PATH '$.properties',
                  META_ID            VARCHAR2(50 CHAR)  PATH '$._id',
                  META_REV           VARCHAR2(50 CHAR)  PATH '$._rev'
         )) jt;

This pivots the data so that we can read it in a select statement.

Next we need to be able to create a new FULLOBJECT value and for that we can use the JSON_OBJECT function

       JSON_OBJECT('firstResourceCollection'  VALUE funcResourcePath(jt.FIRSTID).parent,
                   'firstResourceId'          VALUE funcResourcePath(jt.FIRSTID).leaf,
                   'firstPropertyName'        VALUE jt.FIRSTPROPERTYNAME,
                   'secondResourceCollection' VALUE funcResourcePath(jt.SECONDID).parent,
                   'secondResourceId'         VALUE funcResourcePath(jt.SECONDID).leaf,
                   'secondPropertyName'       VALUE jt.SECONDPROPERTYNAME,
                   'properties'               VALUE jt.PROPS,
                   '_id'                      VALUE jt.META_ID,
                   '_rev'                     VALUE jt.META_REV
         FORMAT JSON) as FULLOBJECT

Here we are converting the 5.5.1.3 data into FRIM 7.2.1 data and use the funcResourcePath to get the parent and leaf values.

Putting it all together we get

CREATE OR REPLACE VIEW relationships_  v AS
SELECT r.objectid as OBJECTID,
       '10' as OBJECTTYPES_ID,
       jt.meta_rev as REV,
       JSON_OBJECT('firstResourceCollection'  VALUE funcResourcePath(jt.FIRSTID).parent,
                   'firstResourceId'          VALUE funcResourcePath(jt.FIRSTID).leaf,
                   'firstPropertyName'        VALUE jt.FIRSTPROPERTYNAME,
                   'secondResourceCollection' VALUE funcResourcePath(jt.SECONDID).parent,
                   'secondResourceId'         VALUE funcResourcePath(jt.SECONDID).leaf,
                   'secondPropertyName'       VALUE jt.SECONDPROPERTYNAME,
                   'properties'               VALUE jt.PROPS,
                   '_id'                      VALUE jt.META_ID,
                   '_rev'                     VALUE jt.META_REV
         FORMAT JSON) as FULLOBJECT,
      funcResourcePath(jt.FIRSTID).parent as FIRSTRESOURCECOLLECTION,
      funcResourcePath(jt.FIRSTID).leaf as FIRSTRESOURCEID,
      jt.FIRSTPROPERTYNAME as FIRSTPROPERTYNAME,
      funcResourcePath(jt.SECONDID).parent as SECONDRESOURCECOLLECTION,
      funcResourcePath(jt.SECONDID).leaf as SECONDRESOURCEID,
      jt.SECONDPROPERTYNAME as SECONDPROPERTYNAME
FROM relationships r,
       JSON_TABLE(fullobject, '$'
         COLUMNS (FIRSTID            VARCHAR2(100 CHAR) PATH '$.firstId',
                  FIRSTPROPERTYNAME  VARCHAR2(50 CHAR)  PATH '$.firstPropertyName',
                  SECONDID           VARCHAR2(100 CHAR) PATH '$.secondId',
                  SECONDPROPERTYNAME VARCHAR2(50 CHAR)  PATH '$.secondPropertyName',
                  PROPS              VARCHAR2(50 CHAR)  PATH '$.properties',
                  META_ID            VARCHAR2(50 CHAR)  PATH '$._id',
                  META_REV           VARCHAR2(50 CHAR)  PATH '$._rev'
         )) jt;

Next steps would be to convert into a MATERIALISED_VIEW and overlay the exisiting FRIM .1.4 RELATIONSHIPS table to provide a real time translation from the FRIM 5.5.1.3 instance to the FRIM 7.2.1

That is another topic to explore

Overview

Create Object

Create Function

Ceate View

Assembled by Nicholas Irving