# Database
## Prerequisites
### Integration user
* A dedicated user should be created for the database integration. This user must have read and write access on the database table and read access on the view that needs to be integrated.
* This user should not be used to perform any other operations on the database.
### When database integration is required to communicate to a new database server
If a database system is needed to be integrated with a database type other than the type of database on which space.vars.SITENAME is deployed, follow the steps given below:
* Download the required database driver on which new database connection is to be created. Refer this to get the link of database connector jar.
* Stop the space.vars.SITENAME.
* Copy the downloaded driver into `Opshub installation directory]\OpsHubServer\lib` folder.
* Start space.vars.SITENAME.
### Fields requirement
* **Primary-key**: There must be a primary key field or a field which doesn't allow duplicate or null values.
* **Updated Time**: There should be a datetime or timestamp column storing last updated time of the record. It is mandatory to have this column when using database system at source side of integration. However, this column is optional when using database system at target side of integration.
* **Created Time**: It is required when bi-directional sync needs to be performed using the database system. There should be a datetime or timestamp column storing the time of creation of the record. However, this column is optional in all the scenarios other than the bi-directional sync scenario.
* **Created/Updated By (Optional)**: A column to store the username of the person who created or last updated the record.
* **OH\_Last\_Update (Optional)**: A text column to store required information for the recovery.
> **Note**: The columns storing "created/updated by" and "OH\_Last\_Update" are required by space.vars.SITENAME to ensure smooth data synchronization, especially if something goes wrong during the sync process.\
> Without these columns, during the synchronization, all record field values are compared with the existing values, which is inefficient.
## System Configuration
* Before you start the integration configuration, you must first configure database system.
* Click [System Configuration](https://docs.opshub.com/v7.210/integrate/configure-integrations/system-configuration) to learn the step-by-step process to configure a system.
Here is the screenshot:
### Database System form details
| **Field Name** | **Description** |
| ------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **System Name** | Provide the system's name |
| **Database Type** | Select the database type for which you want to create a new database system. Currently supported databases are: 1. MySQL 2. MS SQL Server/Azure SQL 3. Oracle 4. PostgreSQL 5. MariaDB |
| **Database Host Name** | The name of the host machine where the database server is deployed |
| **Database Port** | Port number on which database server is deployed. Generally, default ports for MySQL/MariaDB is 3306, MS SQL Server/Azure SQL is 1433, Oracle is 1521, and PostgreSQL is 5432 |
| **Instance Name** | Instance name of the MS SQL Server/Azure SQL, if it is a named instance. Applicable to MS SQL Server only |
| **Database Name** | Name of the database to connect with |
| **Schema Name** | Default schema of the database to connect with. Mandatory for MS SQL Server/Azure SQL and PostgreSQL |
| **Database User Name** | Provide the user name of a dedicated user that will be used for communicating with database. User should have the read and write permission on database |
| **Database Password** | Provide the password for the user provided in Database User Name |
| **Timezone** | Select the timezone for date and time values. If not selected, UTC timezone will be considered by default |
| **Hibernate Mapping XML** | XML mapping required to map the columns of database table with some properties. For more details, refer to [Understanding Hibernate XML Input](#understanding-hibernate-xml-input) section. |
| **Metadata** | Provide the table name and column which is used to sync links, comments, or attachments. Refer to the [Understanding Metadata JSON Input](#understanding-metadata-json-input) section for details on format and JSON structure. |
### Understanding Hibernate XML Input
* This is hibernate XML mapping for tables in which data needs to be integrated. If multiple tables need to be integrated then add one class element for each table in this input widget.
* The tables that are mapped in hibernate mapping XML will be visible in mapping and integration screens.
* Properties that are mapped with column names of table will be visible as fields.
* The column name of primary column can be anything but the property name must be `id`.
* If no value needs to be mapped with the ID column and an auto-generated value is to be added to it, the generator class can be set to `'increment'`. The data type of the ID column must be an integer when the generator class is added.\
Example:
```xml
```
* The column in the table which stores time of the last update on the record needs to be mapped with the 'updated\_time' property. It is optional when there is no need to read data using database system.
* The column in the table which stores time of the creation of the record needs to be mapped with 'created\_time' property. It is required only when bi-directional sync needs to be set-up for the database system.
* For the efficient recovery, the following columns need to be mapped correctly:
* There should be a column in the table to store username of user who created or last updated that record. This column needs to be mapped with created\_updated\_by property. The data type for created\_updated\_by field should be varchar(100).
* There should be a column in the table that needs to be mapped with OH\_Last\_Update property. The data type for OH\_Last\_Update field should be varchar(100).
* Here is the sample Hibernate XML:
```xml
```
### Understanding Metadata JSON Input
* You can provide metadata for complex data types, such as links, comments, and attachments associated with a record, in JSON format here.
An example input for the metadata JSON:
```json
"link": {
"tableName": "LINKS",
"linkIdColumn": "LINK_ID",
"entityIdColumn": "ENTITY_ID",
"entityTypeColumn": "ENTITY_TYPE",
"linkedRecordIdColumn": "LINKED_ENTITY_ID",
"linkedEntityTypeColumn": "LINKED_ENTITY_TYPE",
"linkTypeColumn": "LINK_TYPE",
"linkCreatedTimeColumn": "LINK_ADDED_TIME",
"linkCreatedByColumn": "LINK_ADDED_BY",
"supportedLinkTypes": {"Parent": "Child", "Related": "Related"}
},
"comment": {
"tableName": "COMMENTS",
"commentIdColumn": "COMMENT_ID",
"entityIdColumn": "RECORD_ID",
"entityTypeColumn": "TABLE_NAME",
"commentTitleColumn": "TITLE",
"commentBodyColumn ": "CONTENT",
"commentAuthorColumn": "AUTHOR",
"commentTypeColumn": "COMMENT_TYPE",
"commentAddedTimeColumn": "COMMENTS_ADDED_TIME"
},
"attachment": {
"tableName": "ATTACHMENTS",
"attachmentIdColumn": "ATTACHMENT_ID",
"entityIdColumn": "RECORD_ID",
"entityTypeColumn": "TABLE_NAME",
"fileNameColumn": "FILE_NAME",
"fileContentLengthColumn":"FILE_LENGTH",
"fileTypeColumn ": "FILE_TYPE",
"fileURIColumn ": "FILE_URI",
"attachmentAddedByColumn ": "ATTACHMENT_ADDED_BY",
"attachmentAddedTimeColumn": "ATTACHMENT_ADDED_TIME",
"attachmentFolderPath ": "D:/OpsHub/attachments"
}
```
* To store the links, comments and attachments information of data records, a separate table will be used in the same database/schema. Provide the table name and field details for this table.
**Links/Relationships**
| **Field Name** | **Expected Input Value** |
| ------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `tableName` | Name of the table storing link information among different data records |
| `linkIdColumn` | Column name storing unique ID of the link record |
| `entityIdColumn` | Column name storing entity Id (primary ID value of the record) having link information |
| `entityTypeColumn` | Column name storing table name of the record mentioned in entityIdColumn |
| `linkedRecordIdColumn` | Column name storing linked entity Id (primary ID value of the linked record) |
| `linkedEntityTypeColumn` | Column name storing linked table name of the record mentioned in linkedRecordIdColumn |
| `linkTypeColumn` | Column name storing link type |
| `linkCreatedTimeColumn` | Column name storing link added time |
| `linkCreatedByColumn` | Column name storing username storing the link information |
| `supportedLinkTypes` | Provide metadata for link types along with their corresponding reverse link types in a JSON sub-block. Each link type and its reverse counterpart will be supported for linking.
For example, if the JSON contains "Parent": "Child", then both Parent and Child will be valid link types, with Parent being the reverse of Child and vice versa.
If a link type has no reverse, use an empty string ("") as its reverse link type.
|
**Comments**
| **Field Name** | **Expected Input Value** |
| ------------------------ | ----------------------------------------------------------------------------------------- |
| `tableName` | Name of the table storing comment information of data records |
| `commentIdColumn` | Column name storing unique ID of the comment record |
| `entityIdColumn` | Column name storing entity Id (primary ID value of the record) having comment information |
| `entityTypeColumn` | Column name storing table name of the record mentioned in entityIdColumn |
| `commentBodyColumn` | Column name storing comment body or content of the comment |
| `commentAuthorColumn` | Column name storing comment author |
| `commentAddedTimeColumn` | Column name storing comment added time |
| `commentTypeColumn` | Column name storing comment type |
| `commentTitleColumn` | Column name storing comment title |
**Attachments**
| **Field Name** | **Expected Input Value** |
| --------------------------- | ------------------------------------------------------------------------------------------------- |
| `tableName` | Name of the table storing attachments information of data records |
| `attachmentIdColumn` | Column name storing unique ID of the attachment record |
| `entityIdColumn` | Column name storing entity Id (primary ID value of the record) having file attachment information |
| `entityTypeColumn` | Column name storing table name of the record mentioned in entityIdColumn |
| `fileNameColumn` | Column name storing file name attached to data record |
| `fileContentLengthColumn` | Column name storing file content length |
| `fileTypeColumn` | Column name storing file type |
| `fileURIColumn` | Column name storing local file URI |
| `attachmentAddedByColumn` | Column name storing attachment added by user name |
| `attachmentAddedTimeColumn` | Column name storing attachment added time |
| `attachmentFolderPath` | Local folder path (accessible to OIM user) where all attachment files will be stored |
## Mapping Configuration
* Map the fields between the database and the other system to be integrated to ensure that the data between both the systems synchronize correctly.
* Refer to Mapping Configuration page to learn the step-by-step process to configure mapping between the systems.
* Here is the screenshot:
> **Note** Field having foreign key constraint will be shown as Reference type of field. It should contain valid ID value of the table it refers to.
>
> **Note** Workflow Transition is not applicable for database connector.
## Integration Configuration
* In this step, set a time to synchronize data between the database and the other system to be integrated. Also, define parameters and conditions, if any, for integration.
* Refer to [Integration Configuration](https://docs.opshub.com/v7.210/integrate/configure-integrations/integration-configuration) page to learn the step-by-step process to configure integration between two systems.
Here is the screenshot:
### Criteria Configuration
* If the user wants to specify conditions for synchronizing an entity from database as source system to the other system, the criteria must be configured. Navigate to [Integration Configuration → Criteria Configuration](https://docs.opshub.com/v7.210/integrate/configure-integrations/integration-configuration#criteria-configuration) section on [Integration Configuration](https://docs.opshub.com/v7.210/integrate/configure-integrations/integration-configuration) page to learn in detail about Criteria Configuration.
* Set the query as per database supported query format. Criteria is applicable to any column in table. Only the where clause part of the SQL query is supported.
**Criteria query samples**
| Field Name | Criteria query usecase | Snippet |
| ------------- | ----------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------- |
| order\_value | Need to fetch only those records having order\_value > 5000 | `order_value > 5000` |
| order\_status | Need to fetch only those records having status = 'Active' | `order_status = 'Active'` |
| order\_date | Fetch only records where order\_date is after a date | order\_date >= '2024-08-15 12:00:00'
Here datetime format needs to be same as the format of the field
|
> Here datetime format needs to be same as the format of the field.
* User can also add composite query consisting of more than one columns. For example:\
`order_value > 5000 AND order_status = 'Active'`
> **Note** The name of the field mentioned in criteria query should be same as the actual name of the field in table.
### Target LookUp Configuration
* Provide query in Target Search Query field such that it is possible to search the entity in the database as the target system. In the target search query field, the user can provide a placeholder for the source system's field value between the '@'.
* Go to [Search in Target Before Sync](https://docs.opshub.com/v7.210/integrate/configure-integrations/integration-configuration#search-in-target-before-sync) section on [Integration Configuration](https://docs.opshub.com/v7.210/integrate/configure-integrations/integration-configuration) page to learn in detail about how to configure Target LookUp.
* Overall, Target LookUp Query is similar to [Criteria Configuration](#criteria-configuration), except that the value part contains a field name between '@' instead of static value.
**Target LookUp query samples**
| Field Name | Target lookup usecase | Snippet |
| ---------- | ------------------------------------------------------------------- | --------------------------------- |
| order\_id | Target lookup on entity with matching source entity id in order\_id | `order_id = '@source_entity_id@'` |
> **Note** The name of the field mentioned in target lookup query should be same as actual name of the field in table.
## Known Behavior and Limitations
* The `id` field can be mapped to any other field in another system. However, when using a database system as the target system and mapping the `id` field of a database table to a field may generate duplicate values. As a result, an error will occur, since databases do not allow duplicate values in the primary key column.
* When using database system as source system, it will fetch the records from table based on `id` and the field is mapped to `updated_time`. A record will be fetched from the table only if its `updated_time` value meets one of the these conditions:
* It is greater than or equal to the time of the last processed record.
* It is greater than or equal to the time specified in the "Start Polling Time" field on the advance integration configuration.
* If a table's username column mapped to `created_updated_by` is the same as the username in the database system form, records will be skipped during polling time.
* For Attachment sync, attachment file names must not contain characters that are unsupported by the operating system on which the space.vars.SITENAME is installed.\
For example, on Windows, characters such as `\ / : * ? " < > |` are not allowed in file names.
