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).

If you are a software developer looking for instructions on using the Persistence Service, see Persisting data with Transformer.

If you are upgrading from Caplin Platform 6.2, see Breaking changes in Transformer 7’s Persistence Service.

Contents:

Requirements
Overview
Breaking changes in Transformer 7’s Persistence Service
Activating the Persistence Service
Activating the Persistence Service Client
Adding a JDBC driver to Transformer’s Java classpath
Configuring the JDBC connection
Transaction support
Permissioning users for the Persistence Service Client
SQLite reference implementation

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 and com.caplin.transformer.module.persistence (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
Sybase	Sybase jConnect for JDBC	INT, NUMERIC, VARCHAR, TEXT, VARBINARY^†, SMALLINT, FLOAT, BIGINT, DOUBLE PRECISION
SQL Server		INT, NUMERIC, VARCHAR, TEXT, VARBINARY^†, SMALLINT, FLOAT, BIGINT, DOUBLE PRECISION
MySQL / MariaDB	MariaDB Connector/J	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

Database

Requirements

Data types

Oracle

Oracle JDBC driver version 7+

INT, NUMBER, VARCHAR, VARCHAR2, CLOB^†, BLOB^†, SMALLINT, FLOAT, NUMBER, DECIMAL

Sybase

Sybase jConnect for JDBC

INT, NUMERIC, VARCHAR, TEXT, VARBINARY^†, SMALLINT, FLOAT, BIGINT, DOUBLE PRECISION

SQL Server

INT, NUMERIC, VARCHAR, TEXT, VARBINARY^†, SMALLINT, FLOAT, BIGINT, DOUBLE PRECISION

MySQL / MariaDB

MariaDB Connector/J

INT, NUMERIC, VARCHAR, TEXT, BLOB^†, SMALLINT, FLOAT, BIGINT, DOUBLE PRECISION

Postgres

INT, NUMERIC, VARCHAR, TEXT, BYTEA, SMALLINT, FLOAT, BIGINT, DOUBLE PRECISION

INT, NUMBER, VARCHAR, VARCHAR2, CLOB^†, BLOB^†, SMALLINT, FLOAT, BIGINT, DOUBLE

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

SQLite support

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.

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) 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.

Activating the Persistence Service

To activate the Persistence Service, follow the steps below:

From the root directory of your DFW, run the command below to activate the Persistence Service:
```
./dfw activate PersistenceService
```
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.
3. Configure the JDBC connection to your relational database. See Configuring the JDBC connection.

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

CREATE TABLE IF NOT EXISTS TF_LEGACY_PERSISTENCE (
    JTM_KEY TEXT PRIMARY KEY NOT NULL, (1)
    JTM_VALUE TEXT (2)
);

1 For JTM_KEY, choose a character datatype of at least 255 characters in length

For JTM_VALUE, choose a high-capacity character type such as TEXT or CLOB.

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.

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.

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

From the root directory of your DFW, run the command below to activate the Persistence Service:
```
./dfw activate PersistenceServiceClient
```

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

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, (1)
    PRIMARY KEY (PERSISTENCE_USER, PERSISTENCE_ID)
);

Choose a high-capacity character type for PERSISTENCE_DATA, such as TEXT or CLOB.

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.

From the root directory of your DFW, run the command below to restart Transformer:
```
./dfw start Transformer
```
Authorise users to read and write to Persistence Service Client subjects. See 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:

Create the following directory: <Framework-root>/global_config/overrides/PersistenceService/Transformer/lib/java
Copy the JDBC driver JAR file to the directory created in step 1.
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 MySQL 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:mysql://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

Configuration item	Value	Comment
`enable-file-database`	`FALSE`	Set to `FALSE` to persist to a JDBC database.
`persistence-classid`	`com/caplin/transformer/persistence/JdbcPersistorImpl`	An implementation of the Persistor interface. The implementation packaged with the Persistence Service persists data to JDBC databases: JdbcPersistorImpl.
`init-string`	`jdbc:mysql://192.168.1.50:3306/rtas`	The JDBC URL of the database. The example value in this table specifies a MySQL database called 'rtas' on host 192.168.1.50.
`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 driver.
`username`	`root`	The username of the Persistence Service’s database account.
`password`	`password`	The password of the Persistence Service’s database account.
`extra-params`	`5`	Extra parameters accepted by the Persistor. The JdbcPersistorImpl 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.

enable-file-database

FALSE

Set to FALSE to persist to a JDBC database.

persistence-classid

com/caplin/transformer/persistence/JdbcPersistorImpl

An implementation of the Persistor interface. The implementation packaged with the Persistence Service persists data to JDBC databases: JdbcPersistorImpl.

init-string

jdbc:mysql://192.168.1.50:3306/rtas

The JDBC URL of the database. The example value in this table specifies a MySQL database called 'rtas' on host 192.168.1.50.

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 driver.

username

root

The username of the Persistence Service’s database account.

password

The password of the Persistence Service’s database account.

extra-params

5

Extra parameters accepted by the Persistor. The JdbcPersistorImpl 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.

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.

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

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

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

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:

Creates the SQLite database file (if it does not exist).
Reads each Transformer module’s persistence-extra.conf configuration file (if present).
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:mysql://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

Configuration item	Value	Comment
`enable-file-database`	`TRUE`	Set to `TRUE` to enable persistence to a local SQLite file.
`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.

enable-file-database

TRUE

Set to TRUE to enable persistence to a local SQLite file.

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.

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.

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

CREATE TABLE IF NOT EXISTS TF_LEGACY_PERSISTENCE (
    JTM_KEY TEXT PRIMARY KEY NOT NULL, (1)
    JTM_VALUE TEXT (2)
);

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

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, (1)
    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

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

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

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, (1)
    WATCHLIST_POSITION NUMBER, (2)
    PRIMARY KEY (WATCHLIST_USER, WATCHLIST_ID)
);

CREATE TRIGGER IF NOT EXISTS TF_WATCHLIST_PERSISTENCE_ADD_POSITION (3)
    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: