# Activating the Persistence Service 7

This page provides instructions for activating Transformer 7’s Persistence Service.

For instructions on activating Transformer 6.2’s Persistence Service, see [Activating the Persistence Service (Transformer 6.2)](transformer-activating-the-persistence-service.md).

If you are a software developer looking for instructions on using the Persistence Service, see [Persisting data with Transformer](transformer-persist-data-to-transformer.md).

**❗ IMPORTANT**\
If you are upgrading from Caplin Platform 6.2, see [Breaking changes in Transformer 7’s Persistence Service](#breaking-changes-in-transformer-7s-persistence-service).

## Requirements

Transformer’s Persistence Service is packaged with Transformer but deactivated by default.

The Persistence Service requires the `persistence` option in Transformer’s licence.

## Overview

The Persistence Service comprises two blades:

* **Persistence Service**: persists data to relational databases for the Java Transformer Module (JTM) persistence APIs [com.caplin.jtm.persistence](https://docs.caplin.com/developer/api/transformer_java_sdk_2/latest/com/caplin/jtm/persistence/package-summary.html) and [com.caplin.transformer.module.persistence](https://docs.caplin.com/developer/api/transformer_java_sdk/latest/com/caplin/transformer/module/persistence/package-summary.html) (legacy). Required by all Caplin FX Suite applications.
* **Persistence Service Client**: provides persistence services to StreamLink clients. Requires the Persistence Service.

The Persistence Service connects to relational database servers using Java Database Connectivity (JDBC) and to local SQLite files using a built-in driver.

### Supported database servers

The Persistence Service supports JDBC connections to the following database servers:

| Database | Requirements | Data types |
| --- | --- | --- |
| Oracle | Oracle JDBC driver version 7+ | INT, NUMBER, VARCHAR, VARCHAR2, CLOB^†^, BLOB^†^, SMALLINT, FLOAT, NUMBER, DECIMAL |
| MariaDB | [MariaDB Connector/J](https://mariadb.org/download/) | INT, NUMERIC, VARCHAR, TEXT, BLOB^†^, SMALLINT, FLOAT, BIGINT, DOUBLE PRECISION |
| Postgres |  | INT, NUMERIC, VARCHAR, TEXT, BYTEA, SMALLINT, FLOAT, BIGINT, DOUBLE PRECISION |
| H2 |  | INT, NUMBER, VARCHAR, VARCHAR2, CLOB^†^, BLOB^†^, SMALLINT, FLOAT, BIGINT, DOUBLE |

^†^ The data types CLOB and BLOB are supported from version 7.1.1 of Transformer.

### SQLite support

**❗ IMPORTANT**\
SQLite is not supported by Caplin for use in production.

Transformer 7’s Persistence Service includes a built-in driver for SQLite and is configured to persist to a local SQLite file by default.

Despite being the default persistence store, SQLite is not supported by Caplin for use in production. Transformer 7’s support for SQLite is provided for the convenience of developers running a local installation of Transformer and to serve as a reference implementation for database administrators.

Each blade that uses Transformer 7’s new JTM persistence API includes reference data-definition language (DDL) scripts for SQLite. Database administrators should adapt these reference scripts to the syntax and data types of their relational database server. For more information, see [SQLite DDL scripts](#sqlite-ddl-scripts).

## Breaking changes in Transformer 7’s Persistence Service

If you are upgrading from Transformer 6.2 to Transformer 7, be aware of the following breaking changes in the Persistence Service:

* Transformer 7.0 does not support Transformer 6.2’s 'filedb' persistence store.
* Transformer 7.0 does not support synchronisation of local persistence stores between nodes in a Transformer cluster.
* Transformer 7.0 modules that use Transformer 7’s new persistence API  ([com.caplin.jtm.persistence](https://docs.caplin.com/developer/api/transformer_java_sdk_2/latest/com/caplin/jtm/persistence/package-summary.html)) require the manual creation of database tables as an extra installation step.
* Transformer 7.0 requires the MariaDB database driver when working with either MySQL or MariaDB databases.

For a more details of breaking changes, see [Upgrading to Caplin Platform 7: Persistence Service](../platform-architecture/platform-arch-upgrading-to-caplin-platform-7.md#persistence-service).

## Activating the Persistence Service

To activate the Persistence Service, follow the steps below:

1. From the root directory of your DFW, run the command below to activate the Persistence Service:

   ```
   ./dfw activate PersistenceService
   ```
2. If you are using a relational database server for persistence (required in production), follow the steps below:
   1. In the file `global_config/overrides/PersistenceService/Transformer/etc/java.conf`, uncomment the line below to enable Transformer’s JVM:

      ```
      jvm-location ${JVM_LOCATION}
      ```
   2. Add your relational database’s JDBC driver to Transformer’s Java classpath. See [Adding a JDBC driver to Transformer’s Java classpath](#adding-a-jdbc-driver-to-transformers-java-classpath).
   3. Configure the JDBC connection to your relational database. See [Configuring the JDBC connection](#configuring-the-jdbc-connection).
3. In your persistence database, create the table `TF_LEGACY_PERSISTENCE`, which is required by Transformer 7 to support the legacy JTM’s persistence API. Adapt the syntax and data types of the SQLite DDL below to the DDL of your relational database server:

   **File: kits/PersistenceService/Transformer/etc/bootstrap.sql**

   ```sql
   CREATE TABLE IF NOT EXISTS TF_LEGACY_PERSISTENCE (
       JTM_KEY TEXT PRIMARY KEY NOT NULL, ①
       JTM_VALUE TEXT ②
   );
   ```
   1. For `JTM_KEY`, choose a character datatype of at least 255 characters in length
   2. For `JTM_VALUE`, choose a high-capacity character type such as TEXT or CLOB.

      **💡 TIP**\
      If you are upgrading from Transformer 6.2 and already have a persistence database table, then you can use your existing persistence data to guide you when choosing suitable datatypes for the columns `JTM_KEY` and `JTM_VALUE`.
4. From the root directory of your DFW, run the command below to restart Transformer:

   ```
   ./dfw start Transformer
   ```

## Activating the Persistence Service Client

The Persistence Service Client provides persistence services to StreamLink clients.

This module requires the Persistence Service to be active. If you have not already activated it, see [Activating the Persistence Service](#activating-the-persistence-service).

To activate the Persistence Service Client, follow the steps below:

1. From the root directory of your DFW, run the command below to activate the Persistence Service:

   ```
   ./dfw activate PersistenceServiceClient
   ```
2. In your persistence database, create the table `TF_RECORD_PERSISTENCE`. Adapt the syntax and data types of the packaged reference SQLite implementation to your relational database server:

   **File: kits/PersistenceServiceClient/Transformer/etc/bootstrap.sql**

   ```sql
   CREATE TABLE IF NOT EXISTS TF_RECORD_PERSISTENCE (
       PERSISTENCE_USER VARCHAR(250) NOT NULL,
       PERSISTENCE_ID VARCHAR(100) NOT NULL,
       PERSISTENCE_DATA TEXT NOT NULL, ①
       PRIMARY KEY (PERSISTENCE_USER, PERSISTENCE_ID)
   );
   ```
   1. Choose a high-capacity character type for `PERSISTENCE_DATA`, such as TEXT or CLOB.

      **📌 NOTE**\
      If you are using Transformer in development with the reference SQLite implementation, then you can skip this step. The table is created automatically on startup.
3. From the root directory of your DFW, run the command below to restart Transformer:

   ```
   ./dfw start Transformer
   ```
4. Authorise users to read and write to Persistence Service Client subjects. See [Permissioning users for the Persistence Service Client](#permissioning-users-for-the-persistence-service-client).

## Adding a JDBC driver to Transformer’s Java classpath

To add your relational database’s JDBC driver to Transformer’s classpath, follow the steps below:

1. Create the following directory: `<Framework-root>/global_config/overrides/PersistenceService/Transformer/lib/java`
2. Copy the JDBC driver JAR file to the directory created in step 1.
3. In the file `<Framework-root>/global_config/overrides/PersistenceService/Transformer/etc/java.conf`, make the following changes:
   1. Add the following line (where `__filename__` is the name of JDBC driver JAR file):

      ```
      jvm-global-classpath "${ccd}/../lib/java/__filename__"
      ```
   2. Uncomment the following line:

      ```
      jvm-location ${JVM_LOCATION}
      ```

## Configuring the JDBC connection

To configure Transformer’s JDBC connection to your relational database server, edit the file `<Framework-root>/global_config/overrides/PersistenceService/Transformer/etc/persistence.conf`

The example configuration below configures the Persistence Service to connect to a database called 'rtas' on a MariaDB server on host 192.168.1.50.

**File: global_config/overrides/PersistenceService/Transformer/etc/persistence.conf**

```
enable-file-database FALSE

#
# Uncomment the jvm-location configuration in the java.conf file in this directory
# if the java persistence API is being used.
#
persistence-classid com/caplin/transformer/persistence/JdbcPersistorImpl
add-database-params
   init-string    jdbc:mariadb://192.168.1.50:3306/rtas
   driver-name    org.mariadb.jdbc.Driver
   username       transformer
   password       password
   extra-params   5
end-database-params
```

For an explanation of the configuration items above, see the table below:

| Configuration item | Value | Comment |
| --- | --- | --- |
| [`enable-file-database`](transformer-persistence-configuration.md#enable-file-database) | `FALSE` | Set to `FALSE` to persist to a JDBC database. |
| [`persistence-classid`](transformer-persistence-configuration.md#persistence-classid) | `com/caplin/transformer/persistence/JdbcPersistorImpl` | An implementation of the  [Persistor](https://docs.caplin.com/developer/api/transformer_persistence/latest/com/caplin/transformer/persistence/Persistor.html) interface. The implementation packaged with the Persistence Service persists data to JDBC databases:  [JdbcPersistorImpl](https://docs.caplin.com/developer/api/transformer_persistence/latest/com/caplin/transformer/persistence/JdbcPersistorImpl.html). |
| [`init-string`](transformer-persistence-configuration.md#init-string) | `jdbc:mariadb://192.168.1.50:3306/rtas` | The JDBC URL of the database. The example value in this table specifies a MariaDB database called 'rtas' on host 192.168.1.50. |
| [`driver-name`](transformer-persistence-configuration.md#driver-name) | `org.mariadb.jdbc.Driver` | The name of the JDBC driver’s main class. The example value in this table specifies the [MariaDB Connector/J](https://mariadb.com/docs/connectors/mariadb-connector-j/about-mariadb-connector-j) driver. |
| [`username`](transformer-persistence-configuration.md#username) | `root` | The username of the Persistence Service’s database account. |
| [`password`](transformer-persistence-configuration.md#password) | `password` | The password of the Persistence Service’s database account. |
| [`extra-params`](transformer-persistence-configuration.md#extra-params) | `5` | Extra parameters accepted by the [Persistor](https://docs.caplin.com/developer/api/transformer_persistence/latest/com/caplin/transformer/persistence/Persistor.html). The  [JdbcPersistorImpl](https://docs.caplin.com/developer/api/transformer_persistence/latest/com/caplin/transformer/persistence/JdbcPersistorImpl.html) class accepts one extra parameter: the maximum time to wait, in seconds, for the operation used to check the validity of a database connection to complete. See [Connection.isValid](https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#isvalid-int-). |

## Transaction support

The persistence service does not support database transactions.

To ensure that parallel writes are not made to the same persistence key, follow the guidance below:

* In load-balanced deployments of Transformer, enable the source-affinity load-balancing algorithm to ensure that a user’s persistence operations occur in sequence on the same instance of Transformer.
* Do not share persistence tables between Transformer modules.

Support for transactions will be added in a later release of Transformer.

## Permissioning users for the Persistence Service Client

To permit front-end application users to persist data using the Persistence Service Client, configure Caplin’s Permissioning Service with the rules and permissions below.

For more information about permissioning, see [User Authentication and Permissioning](../liberator/liberator-user-authentication-and-permissioning.md).

### Rules

Define rules that trigger when users contribute (publish) to the Persistence Service Client’s CONTROL and RECORD subjects. The strings used for the permission namespace and for the action are arbitrary; you are free to customise them to your own naming scheme.

| Message Subject | Rule Type | Permission Namespace | Action | Action Ref | Product Ref |
| --- | --- | --- | --- | --- | --- |
| ^/PRIVATE/PERSISTENCE/%u/CONTROL | WRITE | PERSISTENCE_SERVICE | PERSISTENCE-CONTROL | - | ALL_PRODUCTS |
| ^/PRIVATE/PERSISTENCE/%u/RECORD/.* | WRITE | PERSISTENCE_SERVICE | PERSISTENCE-RECORD | - | ALL_PRODUCTS |

### Permissions

Assign users the following permissions.

| Product Set | Permission Namespace | Action | Authorisation |
| --- | --- | --- | --- |
| .* | PERSISTENCE_SERVICE | PERSISTENCE-CONTROL | ALLOW |
| .* | PERSISTENCE_SERVICE | PERSISTENCE-RECORD | ALLOW |
| ^/PRIVATE/PERSISTENCE/%u/CONTROL | - | VIEW | ALLOW |
| ^/PRIVATE/PERSISTENCE/%u/RECORD/.* | - | VIEW | ALLOW |

## SQLite reference implementation

**❗ IMPORTANT**\
SQLite is not supported in production environments and is not compatible with Transformer clusters

Data is persisted to database tables in a local SQLite file. Persistence to SQLite is configured by default but is not supported for use in production deployments.

SQLite cannot be used in a Transformer 7 cluster. Transformer 7, unlike Transformer 6.2, does not synchronise persisted data between local persistence stores of cluster nodes.

When the Persistence Service is configured to persist data to a SQLite file, the service runs through the following tasks at start-up:

1. Creates the SQLite database file (if it does not exist).
2. Reads each Transformer module’s `persistence-extra.conf` configuration file (if present).
3. Runs SQLite bootstrap scripts defined in the `persistence-extra.conf` configuration files.

### Configuring the SQLite implementation

SQLite persistence is configured by default.

No database driver is required.

The configuration below shows the `persistence.conf` file configured for SQLite persistence:

**File: global_config/overrides/PersistenceService/Transformer/etc/persistence.conf**

```
enable-file-database  TRUE

#
# Uncomment the jvm-location configuration in the java.conf file in this directory
# if the java persistence API is being used.
#
#persistence-classid com/caplin/transformer/persistence/JdbcPersistorImpl
add-database-params
   init-string    ${ccd}/persistence.db
#  init-string    jdbc:mariadb://localhost:3306/rtas
#  driver-name    org.mariadb.jdbc.Driver
#  username       root
#  password       password
end-database-params
```

For an explanation of the configuration items above, see the table below:

| Configuration item | Value | Comment |
| --- | --- | --- |
| [`enable-file-database`](transformer-persistence-configuration.md#enable-file-database) | `TRUE` | Set to `TRUE` to enable persistence to a local SQLite file. |
| [`init-string`](transformer-persistence-configuration.md#init-string) | `${ccd}/persistence.db` | The filename for the SQLite database. If the database does not exist, the Persistence Service create its. For a list of valid SQLite filename formats, see URI Filenames in [SQLite C Interface: Opening a new database](https://sqlite.org/c3ref/open.html). |

### SQLite DDL scripts

The following Caplin-supplied blades include a SQLite data-definition language (DDL) script:

* Persistence Service (Transformer 7)
* Persistence Service Client (Transformer 7)
* Watchlist Service 7
* Alerts Service 7
* High-Low Service 7

Database administrators should adapt each DDL script to the syntax and data types of their own relational database server.

Scripts from specific releases are reproduced below for illustrative purposes only. You should always refer to the scripts included in the blades that you deploy.

#### Persistence Service (Transformer 7.1.0)

The Persistence Service requires a table (`TF_LEGACY_PERSISTENCE`)  to support the legacy JTM persistence API [com.caplin.transformer.module.persistence](https://docs.caplin.com/developer/api/transformer_java_sdk/latest/com/caplin/transformer/module/persistence/package-summary.html).

If you are upgrading from Transformer 6.2 and already have a persistence database table, then use the capacities of the datatypes in your existing table to guide you when converting the SQLite datatypes used in the DDL below to the datatypes of your database server.

**File: kits/PersistenceService/Transformer/etc/bootstrap.sql**

```sql
CREATE TABLE IF NOT EXISTS TF_LEGACY_PERSISTENCE (
    JTM_KEY TEXT PRIMARY KEY NOT NULL, ①
    JTM_VALUE TEXT ②
);
```
1. For `JTM_KEY`, choose a character datatype of at least 255 characters in length
2. For `JTM_VALUE`, choose a high-capacity character type such as TEXT or CLOB.

#### Persistence Service Client (Transformer 7.1.0)

Adapt the SQLite DDL below to the SQL syntax of your database server.

**File: kits/PersistenceServiceClient/Transformer/etc/bootstrap.sql**

```sql
CREATE TABLE IF NOT EXISTS TF_RECORD_PERSISTENCE (
    PERSISTENCE_USER VARCHAR(250) NOT NULL,
    PERSISTENCE_ID VARCHAR(100) NOT NULL,
    PERSISTENCE_DATA TEXT NOT NULL, ①
    PRIMARY KEY (PERSISTENCE_USER, PERSISTENCE_ID)
);
```
1. For `PERSISTENCE_DATA`, choose a high-capacity character type such as TEXT or CLOB.

#### Alerts Service 7.0.0

Adapt the SQLite DDL below to the SQL syntax of your database server.

**File: kits/AlertsService/Transformer/etc/bootstrap.sql**

```sql
CREATE TABLE IF NOT EXISTS TF_TRIGGER_PERSISTENCE (
    TRIGGER_USER VARCHAR(250) NOT NULL,
    TRIGGER_ID VARCHAR(250) NOT NULL,
    TRIGGER_DATA VARCHAR(4096) NOT NULL,
    TRIGGER_CREATIONTIME LONG,
    PRIMARY KEY (TRIGGER_USER, TRIGGER_ID)
);

CREATE TABLE IF NOT EXISTS TF_NOTIFICATION_PERSISTENCE (
    NOTIFICATION_USER VARCHAR(250) NOT NULL,
    NOTIFICATION_ID VARCHAR(250) NOT NULL,
    NOTIFICATION_DATA VARCHAR(4096) NOT NULL,
    NOTIFICATION_CREATIONTIME LONG,
    PRIMARY KEY (NOTIFICATION_USER, NOTIFICATION_ID)
);
```

#### High-Low Service 7.0.0

Adapt the SQLite DDL below to the SQL syntax of your database server.

**File: kits/HighLowService/Transformer/etc/create_highlow_db.sql**

```sql
CREATE TABLE IF NOT EXISTS HIGH_LOW_TABLE (
    subject TEXT PRIMARY KEY NOT NULL,
    high_price TEXT,
    low_price TEXT,
    open_price TEXT,
    close_price TEXT,
    percent_change TEXT,
    points_change TEXT
);
```

#### Watchlist Service 7.0.1

Adapt the SQLite DDL below to the SQL syntax of your database server.

**File: kits/WatchlistService/Transformer/etc/bootstrap.sql**

```sql
CREATE TABLE IF NOT EXISTS TF_WATCHLIST_PERSISTENCE (
    WATCHLIST_USER VARCHAR(250) NOT NULL,
    WATCHLIST_ID VARCHAR(250) NOT NULL,
    WATCHLIST_DATA TEXT NOT NULL, ①
    WATCHLIST_POSITION NUMBER, ②
    PRIMARY KEY (WATCHLIST_USER, WATCHLIST_ID)
);

CREATE TRIGGER IF NOT EXISTS TF_WATCHLIST_PERSISTENCE_ADD_POSITION ③
    AFTER INSERT ON TF_WATCHLIST_PERSISTENCE
FOR EACH ROW WHEN NEW.WATCHLIST_POSITION IS NULL
BEGIN
    UPDATE TF_WATCHLIST_PERSISTENCE
        SET WATCHLIST_POSITION = (
            SELECT COUNT(*) - 1
            FROM TF_WATCHLIST_PERSISTENCE
            WHERE WATCHLIST_USER = NEW.WATCHLIST_USER
        )
    WHERE rowid = NEW.rowid;
END;
```
1. For `WATCHLIST_DATA`, choose a high-capacity character type such as TEXT or CLOB.
2. For `WATCHLIST_POSITION`, choose an integer type.
3. The name of the trigger is arbitrary. Customise it to suit the limits of your database. For example, trigger names in Oracle cannot be longer than 32 characters.

---

**See also:**

* [Persistence configuration](transformer-persistence-configuration.md)
* [Installing the Watchlist Service](configure-transformer-to-host-synchronised-watchlists.md)
* [Installing the Alerts Service](transformer-installing-the-alerts-service.md)