--- title: Mappings with a JDBC repository description: Reasons for choosing generic or hybrid over explicit mappings include: component: pingidm version: 8.1 page_id: pingidm:objects-guide:explicit-generic-mapping-jdbc canonical_url: https://docs.pingidentity.com/pingidm/8.1/objects-guide/explicit-generic-mapping-jdbc.html keywords: ["Data Object Model", "Repository", "JDBC"] section_ids: generic_explicit_and_hybrid_oh_my: Generic, explicit, and hybrid, oh my! generic-mappings-jdbc: Generic mappings (JDBC) searches-with-generic-mappings: Improve generic mapping search performance (JDBC) explicit-mappings-jdbc: Explicit mappings (JDBC) hybrid_mappings_jdbc: Hybrid mappings (JDBC) object_type_conversion: Object type conversion convert-explicit-to-hybrid-jdbc: Convert an explicit mapped object to a hybrid mapped object (JDBC) convert-generic-to-explicit-jdbc: Convert a generic mapped object to an explicit mapped object (JDBC) convert-generic-to-hybrid-jdbc: Convert a generic mapped object to a hybrid mapped object (JDBC) --- # Mappings with a JDBC repository ## Generic, explicit, and hybrid, oh my! Reasons for choosing generic or hybrid over explicit mappings include: * Generic and hybrid mapped objects offer the flexibility to add and subtract non-searchable properties without having to modify the Database Data Definition Language (DDL) or IDM object configuration. * The properties table for generic objects can grow large quickly. Consider that a single object with 10 searchable properties would populate 10 rows within the generic properties table. Performance can be increased if commonly searched properties are mapped to a single column in the object table. In addition, the datatype of the property value can be enforced by the DDL of the column, or perhaps a required field could be marked as `NOT NULL`. However, once a property is mapped to an explicit column, future changes to the property mapping may require a DDL change and possibly, a migration effort. | | | | - | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | PostgreSQL offers JSON capabilities that automatically makes all properties searchable. Although indexes will likely still need to be created for properties that need a performance boost. | ## Generic mappings (JDBC) Generic mapping speeds up development, and can make system maintenance more flexible by providing a stable database structure. However, generic mapping can have a performance impact and does not take full advantage of the database facilities (such as validation within the database and flexible indexing). In addition, queries can be more difficult to set up. In a generic table, the entire object content is stored in a single large-character field named `fullobject` in the `mainTable` for the object. To search on specific fields, you can read them by referring to them in the corresponding `properties table` for that object. The disadvantage of generic objects is that, because every property you might like to filter by is stored in a separate table, you must join to that table each time you need to filter by anything. The following diagram shows a pared down database structure for the default generic tables, when using a MySQL repository. The diagram indicates the relationship between the main table and the corresponding properties table for each object. ![generic-tables-erd](_images/generic-tables-erd.png)Figure 1. Generic Tables Entity Relationship Diagram These separate tables can make the query syntax particularly complex. For example, a simple query to return user entries based on a user name would need to be implemented as follows: ```sql SELECT obj.objectid, obj.rev, obj.fullobject FROM ${_dbSchema}.${_mainTable} obj INNER JOIN ${_dbSchema}.${_propTable} prop ON obj.id = prop.${_mainTable}_id INNER JOIN ${_dbSchema}.objecttypes objtype ON objtype.id = obj.objecttypes_id WHERE prop.propkey='/userName' AND prop.propvalue = ${uid} AND objtype.objecttype = ${_resource} ``` The query can be broken down as follows: 1. Select the full object, the object ID, and the object revision from the main table: ```sql SELECT obj.objectid, obj.rev, obj.fullobject FROM ${_dbSchema}.${_mainTable} obj ``` 2. Join to the properties table and locate the object with the corresponding ID: ```sql INNER JOIN ${_dbSchema}.${_propTable} prop ON obj.id = prop.${_mainTable}_id ``` 3. Join to the object types table to restrict returned entries to objects of a specific type. For example, you might want to restrict returned entries to `managed/user` objects, or `managed/role` objects: ```sql INNER JOIN ${_dbSchema}.objecttypes objtype ON objtype.id = obj.objecttypes_id ``` 4. Filter records by the `userName` property, where the userName is equal to the specified `uid` and the object type is the specified type (in this case, managed/user objects): ```sql WHERE prop.propkey='/userName' AND prop.propvalue = ${uid} AND objtype.objecttype = ${_resource} ``` The value of the `uid` field is provided as part of the query call, for example: ```javascript openidm.query("managed/user", { "_queryId": "for-userName", "uid": "jdoe" }); ``` Tables for user definable objects use a generic mapping by default. The following sample generic mapping object illustrates how `managed/` objects are stored in a generic table: ```json "genericMapping" : { "managed/*" : { "mainTable" : "managedobjects", "propertiesTable" : "managedobjectproperties", "searchableDefault" : true, "properties" : { "/picture" : { "searchable" : false } } } } ``` * `mainTable` (string, mandatory) Indicates the main table in which data is stored for this resource. The complete object is stored in the `fullobject` column of this table. The table includes an `objecttypes` foreign key that is used to distinguish the different objects stored within the table. In addition, the revision of each stored object is tracked, in the `rev` column of the table, enabling multiversion concurrency control (MVCC). For more information, refer to [Manipulating Managed Objects Programmatically](appendix-managed-objects.html#managed-objects-programmatic). * `propertiesTable` (string, mandatory) Indicates the properties table, used for searches. | | | | - | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | PostgreSQL repositories do not use these properties tables to access specific properties. Instead, the PostgreSQL `json_extract_path_text()` function achieves this functionality. | The contents of the properties table is a defined subset of the properties, copied from the character large object (CLOB) that is stored in the `fullobject` column of the main table. The properties are stored in a one-to-many style separate table. The set of properties stored here is determined by the properties that are defined as `searchable`. The stored set of searchable properties makes these values available as discrete rows that can be accessed with SQL queries, specifically, with `WHERE` clauses. It is not otherwise possible to query specific properties of the full object. The properties table includes the following columns: * `${_mainTable}_id` corresponds to the `id` of the full object in the main table, for example, `manageobjects_id`, or `genericobjects_id`. * `propkey` is the name of the searchable property, stored in JSON pointer format (for example `/mail`). * `proptype` is the data type of the property, for example `java.lang.String`. The property type is obtained from the Class associated with the value. * `propvalue` is the value of property, extracted from the full object that is stored in the main table. Regardless of the property data type, this value is stored as a string, so queries against it should treat it as such. * `searchableDefault` (boolean, optional) Specifies whether all properties of the resource should be searchable by default. Properties that are searchable are stored and indexed. You can override the default for individual properties in the `properties` element of the mapping. The preceding example indicates that all properties are searchable, with the exception of the `picture` property. For large, complex objects, having all properties searchable implies a substantial performance impact. In such a case, a separate insert statement is made in the properties table for each element in the object, every time the object is updated. Also, because these are indexed fields, the recreation of these properties incurs a cost in the maintenance of the index. You should therefore enable `searchable` only for those properties that must be used as part of a WHERE clause in a query. | | | | - | -------------------------------------------------------------------- | | | PostgreSQL repositories do not use the `searchableDefault` property. | * `properties` Lists any individual properties for which the searchable default should be overridden. Note that if an object was originally created with a subset of `searchable` properties, changing this subset (by adding a new `searchable` property in the configuration, for example) will not cause the existing values to be updated in the properties table for that object. To add the new property to the properties table for that object, you must update or recreate the object. ### Improve generic mapping search performance (JDBC) All properties in a generic mapping are searchable by default. In other words, the value of the `searchableDefault` property is `true` unless you explicitly set it to false. Although there are no individual indexes in a generic mapping, you can improve search performance by setting only those properties that you need to search as `searchable`. Properties that are searchable are created within the corresponding properties table. The properties table exists only for searches or look-ups, and has a composite index, based on the resource, then the property name. The sample JDBC repository configuration files (`db/database/conf/repo.jdbc.json`) restrict searches to specific properties by setting the `searchableDefault` to `false` for `managed/user` mappings. You must explicitly set `searchable` to true for each property that should be searched. The following sample extract from `repo.jdbc.json` indicates searches restricted to the `userName` property: ```json "genericMapping" : { "{managed_user}" : { "mainTable" : "manageduserobjects", "propertiesTable" : "manageduserobjectproperties", "searchableDefault" : false, "properties" : { "/userName" : { "searchable" : true } } } } ``` With this configuration, IDM creates entries in the properties table only for `userName` properties of managed user objects. If the global `searchableDefault` is set to false, properties that do not have a searchable attribute explicitly set to true are not written in the properties table. ## Explicit mappings (JDBC) Explicit mapping is more difficult to set up and maintain, but can take complete advantage of the native database facilities. An explicit table offers better performance and simpler queries. There is less work in the reading and writing of data, because the data is all in a single row of a single table. In addition, it is easier to create different types of indexes that apply to only specific fields in an explicit table. The disadvantage of explicit tables is the additional work required in creating the table in the schema. Also, because rows in a table are inherently more simple, it is more difficult to deal with complex objects. Any non-simple key:value pair in an object associated with an explicit table is converted to a JSON string and stored in the cell in that format. This makes the value difficult to use, from the perspective of a query attempting to search within it. You can have a generic mapping configuration for most managed objects, *and* an explicit mapping that overrides the default generic mapping in certain cases. IDM provides a sample configuration, for each JDBC repository, that sets up an explicit mapping for the managed *user* object, and a generic mapping for all other managed objects. This configuration is defined in the files named `/path/to/openidm/db/repository/conf/repo.jdbc-repository-explicit-managed-user.json`. To use this configuration, copy the file that corresponds to your repository to your project's `conf/` directory, and rename it `repo.jdbc.json`. Run the `sample-explicit-managed-user.sql` data definition script (in the `path/to/openidm/db/repository/scripts` directory) to set up the corresponding tables when you configure your JDBC repository. IDM uses explicit mapping for internal system tables, such as the tables used for auditing. Depending on the types of usage your system is supporting, you might find that an explicit mapping performs better than a generic mapping. Operations such as sorting and searching (such as those performed in the default UI) tend to be faster with explicitly-mapped objects, for example. The following sample explicit mapping object illustrates how `internal/user` objects are stored in an explicit table: ```json "explicitMapping" : { "internal/user" : { "table" : "internaluser", "objectToColumn" : { "_id" : "objectid", "_rev" : { "column" : "rev", "isNotNull" : true }, "password" : "pwd" } }, ... } ``` * `resource-uri` (string, mandatory) Indicates the URI for the resources to which this mapping applies; for example, `internal/user`. * `table` (string, mandatory) The name of the database table in which the object (in this case internal users) is stored. * `objectToColumn` (string, mandatory) The way in which specific managed object properties are mapped to columns in the table. The mapping can be a simple one to one mapping, for example `"userName": "userName"`, or a more complex JSON map or list. When a column is mapped to a JSON map or list, the syntax is as shown in the following examples: ```json "messageDetail" : { "column" : "messagedetail", "type" : "JSON_MAP" } ``` ```json "roles" : { "column" : "roles", "type" : "JSON_LIST" } ``` Available column data types you can specify are `STRING` (the default), `NUMBER`, `JSON_MAP`, `JSON_LIST`, and `FULLOBJECT`. You can also prevent a column from accepting a `NULL` value, by setting the property `isNotNull` to `true`. This property is optional; if the property is omitted, it will default to `false`. Specifying which columns do not allow a null value can improve performance when sorting and paginating large queries. The syntax is similar to when specifying a column type: ```json "createDate" : { "column" : "createDate", "isNotNull" : true } ``` | | | | - | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | Pay particular attention to the following caveats when you map properties to explicit columns in your database:* Support for data types in columns is restricted to numeric values (`NUMBER`), strings (`STRING`), and boolean values (`BOOLEAN`). Although you can specify other data types, IDM handles all other data types as strings. Your database will need to convert these types from a string to the alternative data type. This conversion is *not guaranteed to work*. If the conversion does work, the format might not be the same when the data is read from the database as it was when it was saved. For example, your database might parse a date in the format `12/12/2012` and return the date in the format `2012-12-12` when the property is read. * Passwords are encrypted before they are stored in the repository. The length of the password column must be long enough to store the encrypted password value, which can vary depending on how it is encrypted and whether it is also hashed. The `sample-explicit-managed-user.sql` file referenced in this section sets the password column to a length of 511 characters (`VARCHAR(511)` to account for the additional space an encrypted password requires. For more information about IDM encryption and an example encrypted password value, refer to [encrypt](../setup-guide/chap-cli.html#cli-encrypt) and [Secure sensitive values](../security-guide/encoding-attribute-values.html). * If your data objects include *virtual properties*, you must include columns in which to store these properties. If you don't explicitly map the virtual properties, you will encounter errors similar to the following when you attempt to create the corresponding object: ``` { "code":400, "reason":"Bad Request", "message":"Unmapped fields [/property-name/0] for type managed/user and table openidm.managed_user" } ``` To recalculate virtual property values in a query, you must set `executeOnRetrieve` to `true` in the query request parameters. For more information, refer to [Property Storage Triggers](appendix-managed-objects.html#managed-object-property-storage-triggers). | ## Hybrid mappings (JDBC) Hybrid mappings are similar to generic mappings, except some object fields are mapped directly to a column, and therefore not stored in the Entity–attribute–value (EAV) properties table. The `fullobject` column still holds all the object data and is used for object constitution. The combination of the explicit field columns and the EAV `properties table` is used for searching. ## Object type conversion You can use the [migration service](../upgrade-guide/data-migration.html) to convert objects from one type to another. ### Convert an explicit mapped object to a hybrid mapped object (JDBC) This procedure demonstrates how to migrate data to a different storage configuration within the same system using the [migration service](../upgrade-guide/data-migration.html) to convert the object data. After you finish the conversion, the converted objects are technically hybrid objects—generically mapped objects that have certain fields that are mapped to explicit columns. | | | | - | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | Considerations before you start:- After you complete the process, object resource paths must stay the same to maintain relationship references. - You must migrate data to an *empty* table. Unlike generic tables, explicit mapped objects expect the table to contain records from a single object type. - Changes made to the source object during migration might not be transferred to the new object. To ensure everything is migrated correctly, run the migration during idle time, or when the system is least busy. | This procedure assumes that the repository configuration includes explicitly mapped object types, and that such objects already exist in the corresponding tables. For example: ```json "explicitMapping" : { ... "managed/objectToConvert" : { "table" : "objecttoconvert", "objectToColumn" : { "_id" : "objectid", "_rev" : "rev", "desc" : "descr" } } ``` 1. Create the new generic table and associated properties table. Adjust the following example to match your repository requirements, as needed: ```sql CREATE TABLE `openidm`.`objecttoconvert_gen` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT , `objecttypes_id` BIGINT UNSIGNED NOT NULL , `objectid` VARCHAR(255) NOT NULL , `rev` VARCHAR(38) NOT NULL , `descr` VARCHAR(255) NOT NULL , `fullobject` MEDIUMTEXT NULL , PRIMARY KEY (`id`) , UNIQUE INDEX `idx-objecttoconvert_object` (`objecttypes_id` ASC, `objectid` ASC) , INDEX `fk_objecttoconvert_objectypes` (`objecttypes_id` ASC) , CONSTRAINT `fk_objecttoconvert_objectypes` FOREIGN KEY (`objecttypes_id` ) REFERENCES `openidm`.`objecttypes` (`id` ) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `openidm`.`objecttoconvert_genproperties` ( `objecttoconvert_gen_id` BIGINT UNSIGNED NOT NULL , `propkey` VARCHAR(255) NOT NULL , `proptype` VARCHAR(32) NULL , `propvalue` VARCHAR(2000) NULL , `propindex` BIGINT NOT NULL DEFAULT 0, PRIMARY KEY (`objecttoconvert_gen_id`, `propkey`, `propindex`), INDEX `fk_objecttoconvertproperties_managedobjects` (`objecttoconvert_gen_id` ASC) , INDEX `idx_objecttoconvertproperties_propkey` (`propkey` ASC) , INDEX `idx_objecttoconvertproperties_propvalue` (`propvalue`(255) ASC) , CONSTRAINT `fk_objecttoconvertproperties_objecttoconvert` FOREIGN KEY (`objecttoconvert_gen_id` ) REFERENCES `openidm`.`objecttoconvert_gen` (`id` ) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB; ``` 2. Modify `conf/repo.jdbc.json` to map the object path in the generic mapping section to the empty generic table. If the migrated data will have additional searchable columns, [add them now](#searches-with-generic-mappings). 3. [Create a `conf/migration.json` file](../upgrade-guide/data-migration.html) with the following details: * Update the authentication settings to match the system configuration. * Modify the `instanceUrl` to point to the same system. For example: ```json { "enabled" : true, "endpoint" : "", "connection" : { "instanceUrl" : "http://localhost:8080/openidm/", "authType" : "basic", "userName" : "openidm-admin", "password" : "openidm-admin" }, "mappings" : [ { "target" : "repo/managed/objectToConvert_gen", "source" : "repo/managed/objectToConvert" } ] } ``` 4. Call the migration service to view the mapping name that was generated: ``` curl \ --header "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header 'Accept-API-Version: resource=1.0' \ --request POST 'http://localhost:8080/openidm/migration?_action=mappingNames' [ [ "repoManagedObjecttoconvert_repoManagedObjecttoconvertGen" ] ] ``` 5. Start the migration: ``` curl \ --header "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ --request POST \ "http://localhost:8080/openidm/migration?_action=migrate&mapping=repoManagedObjecttoconvert_repoManagedObjecttoconvertGen" { "migrationResults": { "recons": [ { "name": "repoManagedObjecttoconvert_repoManagedObjecttoconvertGen", "status": "PENDING" } ] } } ``` 6. You must wait until the migration is completed. To check the status of the migration: ``` curl \ --header "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header 'Accept-API-Version: resource=1.0' \ --request POST 'http://localhost:8080/openidm/migration?_action=status' ``` > **Collapse: Example Return** > > ```json > { > "migrationResults": { > "recons": [ > { > "name": "repoManagedObjecttoconvert_repoManagedObjecttoconvertGen", > "status": { > "_id": "820a1c66-6f1a-41d8-82a4-fc5a2d246326-424", > "mapping": "repoManagedObjecttoconvert_repoManagedObjecttoconvertGen", > "state": "SUCCESS", > "stage": "COMPLETED_SUCCESS", > "stageDescription": "reconciliation completed.", > "progress": { > "source": { > "existing": { > "processed": 0, > "total": "9" > } > }, > "target": { > "existing": { > "processed": 0, > "total": "?" > }, > "created": 0, > "unchanged": 0, > "updated": 0, > "deleted": 0 > }, > "links": { > "existing": { > "processed": 0, > "total": "0" > }, > "created": 0 > } > }, > "situationSummary": { > "SOURCE_IGNORED": 0, > "FOUND_ALREADY_LINKED": 0, > "UNQUALIFIED": 0, > "ABSENT": 0, > "TARGET_IGNORED": 0, > "MISSING": 0, > "ALL_GONE": 0, > "UNASSIGNED": 0, > "AMBIGUOUS": 0, > "CONFIRMED": 0, > "LINK_ONLY": 0, > "SOURCE_MISSING": 0, > "FOUND": 0 > }, > "statusSummary": { > "SUCCESS": 0, > "FAILURE": 9 > }, > "durationSummary": { > "sourceObjectQuery": { > "min": 26, > "max": 33, > "mean": 30, > "count": 9, > "sum": 277, > "stdDev": 2 > }, > "sourceQuery": { > "min": 37, > "max": 37, > "mean": 37, > "count": 1, > "sum": 37, > "stdDev": 0 > }, > "auditLog": { > "min": 0, > "max": 1, > "mean": 0, > "count": 11, > "sum": 9, > "stdDev": 0 > }, > "linkQuery": { > "min": 4, > "max": 4, > "mean": 4, > "count": 1, > "sum": 4, > "stdDev": 0 > }, > "correlationQuery": { > "min": 8, > "max": 18, > "mean": 15, > "count": 9, > "sum": 139, > "stdDev": 4 > }, > "sourcePhase": { > "min": 113, > "max": 113, > "mean": 113, > "count": 1, > "sum": 113, > "stdDev": 0 > } > }, > "parameters": { > "sourceQuery": { > "resourceName": "external/migration/repo/managed/objectToConvert", > "queryFilter": "true", > "_fields": "_id" > }, > "targetQuery": { > "resourceName": "repo/managed/objectToConvert_gen", > "queryFilter": "true", > "_fields": "_id" > } > }, > "started": "2021-01-20T18:22:34.026Z", > "ended": "2021-01-20T18:22:34.403Z", > "duration": 377, > "sourceProcessedByNode": {} > } > } > ] > } > } > ``` | | | | - | ------------------------------------------------------------------------------------------------------------------------ | | | Optionally, you can run the migration again to account for changes that may have occurred during the original migration. | The data is now migrated to the new tables, but IDM is still referencing the previous mapping. 7. Edit the `repo.jdbc.json` file: * Remove the old mapping from `explicitMapping`: ```json "explicitMapping" : { ... "managed/objectToConvert" : { "table" : "objecttoconvert", "objectToColumn" : { "_id" : "objectid", "_rev" : "rev", "desc" : "descr" } } ``` * Modify the newly added `genericMapping` to point to the old resource path: ```json "genericMapping" : { ... "managed/objectToConvert" : { "mainTable" : "objecttoconvert_gen", "propertiesTable" : "objecttoconvert_genproperties", "searchableDefault" : false, "objectToColumn" : { "_id" : "objectid", "_rev" : "rev", "desc" : "descr" }, "properties": { "/stringArrayField" : { "searchable" : true } } }, } ``` 8. Run a SQL update statement so that the `objecttypes` table points the temporary object type to the original object type. Adjust the following example to match your repository requirements, as needed: ``` update openidm.objecttypes set objecttype = 'managed/objectToConvert' where objecttype = 'managed/objectToConvert_gen'; ``` ### Convert a generic mapped object to an explicit mapped object (JDBC) This procedure demonstrates how to migrate data to a different storage configuration within the same system using the [migration service](../upgrade-guide/data-migration.html) to convert the object data. | | | | - | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | | Considerations before you start:- After you complete the process, object resource paths must stay the same to maintain relationship references. - You must migrate data to an *empty* table. Unlike generic tables, explicit mapped objects expect the table to contain records from a single object type. - During the migration, changes made to the source object might not be transferred to the new object. To ensure everything is migrated correctly, run the migration during idle time, or when the system is least busy. | This procedure assumes an existing generic object resource path of `managed/objectToConvert`, with objects stored in the generic objects table `genericobjects`. A sample object might be: ```json { "_id" : "4213-2134-23423", "_rev" : "AB231A", "name" : "Living room camera", "properties": { "location" : "45.123N100.123W", "uptime" : 123123 }, "otherProperties" : { "bla": "blabla", "blahdee" : "da"} } ``` Before you start, consider the following: * Make sure to map a column to each field of your object. * Fields that are objects, not simple scalar values, will be stored as serialized JSON, and won't be easily searchable. * Object instances are constituted by selecting the mapped columns and putting the data in the JSON object using the field path that the column is mapped to. * Create table indexes that are inline with your system's usage of searches and sorting of the column data. For example, modify or add indexes to include all newly created columns for any fields that were configured as searchable. 1. Create the new explicit table: ```sql CREATE TABLE `openidm`.`objectToConvert` ( `objectid` VARCHAR(255) NOT NULL , `rev` VARCHAR(38) NOT NULL , `name` VARCHAR(255) NOT NULL , `location` VARCHAR(38) NULL , `uptime` BIGINT NULL , `misc` MEDIUMTEXT NULL, PRIMARY KEY (`objectid`)); ``` 2. Modify `conf/repo.jdbc.json` to add a new mapping for the object type in the `explicitMapping` node. To avoid conflict with the generically mapped object path, slightly modify the resource path. A new explicit mapping example: ```json "explicitMapping" : { ... "managed/objectToConvert_explicit": { "table": "objectToConvert", "objectToColumn" : { "_id" : "objectid", "_rev" : { "column" : "rev", "isNotNull" : true }, "name" : { "column" : "name", "isNotNull" : true }, "properties/location" : { "column": "location" }, "properties/uptime" : { "column" : "uptime" }, "otherProperties" : { "column" : "misc", "type" : "JSON_MAP" } } } ... ... } ``` 3. [Create a `conf/migration.json` file](../upgrade-guide/data-migration.html) with the following details: * Update the authentication settings to match the system configuration. * Modify the `instanceUrl` to point to the same system. For example: ```json { "enabled" : true, "endpoint" : "", "connection" : { "instanceUrl" : "http://localhost:8080/openidm/", "authType" : "basic", "userName" : "openidm-admin", "password" : "openidm-admin" }, "mappings" : [ { "target" : "repo/managed/objectToConvert_explicit", "source" : "repo/managed/objectToConvert" } ] } ``` 4. Call the migration service to view the mapping name that was generated: ``` curl \ --header "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header 'Accept-API-Version: resource=1.0' \ --request POST 'http://localhost:8080/openidm/migration?_action=mappingNames' ``` IDM returns something similar to: ```json [ [ "repoManagedObjecttoconvert_repoManagedObjecttoconvertExplicit" ] ] ``` 5. Start the migration: ``` curl \ --header "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header "Accept-API-Version: resource=1.0" \ --request POST \ "http://localhost:8080/openidm/migration?_action=migrate&mapping=repoManagedObjecttoconvert_repoManagedObjecttoconvertExplicit" ``` IDM returns something similar to: ```json { "migrationResults": { "recons": [ { "name": "repoManagedObjecttoconvert_repoManagedObjecttoconvertExplicit", "status": "PENDING" } ] } } ``` 6. You must wait until the migration completes. To check the status of the migration: ``` curl \ --header "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header 'Accept-API-Version: resource=1.0' \ --request POST 'http://localhost:8080/openidm/migration?_action=status' ``` IDM returns something similar to: ```json { "migrationResults": { "recons": [ { "name": "repoManagedObjecttoconvert_repoManagedObjecttoconvertExplicit", "status": { "_id": "820a1c66-6f1a-41d8-82a4-fc5a2d246326-424", "mapping": "repoManagedObjecttoconvert_repoManagedObjecttoconvertExplicit", "state": "SUCCESS", "stage": "COMPLETED_SUCCESS", "stageDescription": "reconciliation completed.", "progress": { "source": { "existing": { "processed": 0, "total": "9" } }, "target": { "existing": { "processed": 0, "total": "?" }, "created": 0, "unchanged": 0, "updated": 0, "deleted": 0 }, "links": { "existing": { "processed": 0, "total": "0" }, "created": 0 } }, "situationSummary": { "SOURCE_IGNORED": 0, "FOUND_ALREADY_LINKED": 0, "UNQUALIFIED": 0, "ABSENT": 0, "TARGET_IGNORED": 0, "MISSING": 0, "ALL_GONE": 0, "UNASSIGNED": 0, "AMBIGUOUS": 0, "CONFIRMED": 0, "LINK_ONLY": 0, "SOURCE_MISSING": 0, "FOUND": 0 }, "statusSummary": { "SUCCESS": 0, "FAILURE": 9 }, "durationSummary": { "sourceObjectQuery": { "min": 26, "max": 33, "mean": 30, "count": 9, "sum": 277, "stdDev": 2 }, "sourceQuery": { "min": 37, "max": 37, "mean": 37, "count": 1, "sum": 37, "stdDev": 0 }, "auditLog": { "min": 0, "max": 1, "mean": 0, "count": 11, "sum": 9, "stdDev": 0 }, "linkQuery": { "min": 4, "max": 4, "mean": 4, "count": 1, "sum": 4, "stdDev": 0 }, "correlationQuery": { "min": 8, "max": 18, "mean": 15, "count": 9, "sum": 139, "stdDev": 4 }, "sourcePhase": { "min": 113, "max": 113, "mean": 113, "count": 1, "sum": 113, "stdDev": 0 } }, "parameters": { "sourceQuery": { "resourceName": "external/migration/repo/managed/objectToConvert", "queryFilter": "true", "_fields": "_id" }, "targetQuery": { "resourceName": "repo/managed/objectToConvert_explicit", "queryFilter": "true", "_fields": "_id" } }, "started": "2021-01-20T18:22:34.026Z", "ended": "2021-01-20T18:22:34.403Z", "duration": 377, "sourceProcessedByNode": {} } } ] } } ``` | | | | - | ------------------------------------------------------------------------------------------------------------------------ | | | Optionally, you can run the migration again to account for changes that may have occurred during the original migration. | The data is now migrated to the new tables, but IDM is still referencing the previous mapping and generic table. 7. Edit the `repo.jdbc.json` file: * If the mapping of the generic resource had a mapping, it should be removed. If the generic resource was included in the `managed/*` path, as in the example, there is nothing to remove. * Modify the object path from `managed/objectToConvert_explicit` to `managed/objectToConvert`. 8. Save the `repo.jdbc.json` file. Until IDM processes the configuration change, REST requests are unavailable. 9. Once IDM finishes processing the configuration change, it is safe to delete the object data from the original generic table. Using a proper *delete cascade*, the searchable properties of the generic object are automatically deleted from the generic properties table. For example: ```sql delete from managedobjects where objecttypes_id = ( select o2.id from objecttypes o2 where objecttype = "managed/objectToConvert"); ``` ### Convert a generic mapped object to a hybrid mapped object (JDBC) This procedure demonstrates how to convert a generically stored property to an explicitly stored property; moving property value storage out of the object's property table, and into a new column on the object table. After you finish the conversion, the converted objects are technically hybrid objects—generically mapped objects that have certain fields that are mapped to explicit columns. | | | | - | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | Considerations before you start:- Changes made to the source object during migration might not be transferred to the new object. To ensure everything is migrated correctly, run the migration during idle time, or when the system is least busy. | This procedure assumes an existing generic object resource path as: `managed/objectToConvert`, with objects stored in the generic objects table `objecttoconvertobjects`. A sample object might be: ```json { "_id" : "4213-2134-23423", "_rev" : "AB231A", "name" : "Living room camera", "properties": { "location" : "45.123N100.123W", "uptime" : 123123 }, "otherProperties" : { "bla": "blabla", "blahdee" : "da"} } ``` 1. Create a new database column for the explicit field data: ```sql alter table openidm.objecttoconvertobjects add column `name` varchar(255); ``` 2. Edit the `genericMapping` section of the `conf/repo.jdbc.json` file: * For each object to convert, add the `objectToColumn` configuration for the fields to explicitly map to a column. For example: ```json "genericMapping" : { ... "managed/objectToConvert": { "mainTable" : "objecttoconvertobjects", "propertiesTable" : "objecttoconvertobjectsproperties", "searchableDefault" : true, "objectToColumn" : { "name" : "name" } } ... } ``` * If the object is defined with a wildcard mapping, such as `managed/*`, create a new mapping specifically for the object conversion. 3. Save the `conf/repo.jdbc.json` file. Until IDM processes the configuration change, REST requests are unavailable. 4. For any added columns to be usable, they must be reindexed and populated. The `rewriteObjects.js` script can read and rewrite object data to match the config from the `conf/repo.jdbc.json` file. The script reads one page of data at a time, and then writes out each object, one at a time. Consider the page size and queryFilter to efficiently process the data by splitting the data into groups that can run in parallel. The request will not return until *ALL* pages have been processed. For example, to run the `rewriteObjects.js` script with 1000 objects per page: ```none curl \ --header "X-OpenIDM-Username: openidm-admin" \ --header "X-OpenIDM-Password: openidm-admin" \ --header 'Accept-API-Version: resource=1.0' \ --header 'content-type: application/json' \ --request POST \ --data-raw '{ "type":"text/javascript", "file":"bin/defaults/script/update/rewriteObjects.js", "globals" : { "rewriteConfig" :{ "queryFilter": "true", "pageSize": 1000, "objectPaths": [ "repo/managed/objectToConvert" ] } } }' \ "http://localhost:8080/openidm/script?_action=eval" ``` 5. Now that the new column contains data, edit or create new indexes so that the new column can be queried efficiently. For example: ```sql alter table openidm.objecttoconvertobjects add index `idx_obj_name` (`name`); ```