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.

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.

JDBC support

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

  • Oracle

  • Microsoft SQL Server

  • Sybase (tested with the Sybase jConnect for JDBC driver)

  • MySQL (tested with the MariaDB Connector/J driver)

  • Postgres

  • H2

The Oracle MySQL Connector/J driver is not supported.

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 the new Java Transformer Module (JTM) API, (com.caplin.jtm), require the creation of database tables as an extra installation step.

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:

  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.

    3. Configure the JDBC connection to your relational database. See [Configuring the connection to your relational database].

  3. In your persistence database, create the table TF_LEGACY_PERSISTENCE, which is required by Transformer 7 to support the legacy JTM persistence API. Adapt the syntax and data types of the packaged reference SQLite implementation to 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,
        JTM_VALUE TEXT
    );
  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.

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
    CREATE TABLE IF NOT EXISTS TF_RECORD_PERSISTENCE (
        PERSISTENCE_USER VARCHAR(250) NOT NULL,
        PERSISTENCE_ID VARCHAR(100) NOT NULL,
        PERSISTENCE_DATA VARCHAR(4096) NOT NULL, (1)
        PRIMARY KEY (PERSISTENCE_USER, PERSISTENCE_ID)
    );
    1 Adjust the length of the PERSISTENCE_DATA column type so that it can store the largest amount of data persisted by your front-end applications. Ask your developers for guidance.
    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.

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. 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. Create the following directory: <Framework-root>/global_config/overrides/PersistenceService/Transformer/lib/java

  3. Copy the JDBC driver JAR file to the directory created in step 2.

  4. In the file <Framework-root>/global_config/overrides/PersistenceService/Transformer/etc/java.conf, make the following changes:

    1. Add the following line (where jar_filename is the name of JDBC driver JAR file):

      jvm-global-classpath "${ccd}/../lib/java/jar_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

enable-file-database

FALSE

Set to FALSE to use database-backed persistence.

persistence-classid

com/caplin/transformer/persistence/JdbcPersistorImpl

The implementation of the Persistor interface usds to persist data to the database. The example value in this table is the default JDBC Persistor that comes packaged with the Persistence Service.

init-string

jdbc:mysql://localhost:3306/rtas

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

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 com.caplin.transformer.persistence.JdbcPersistorImpl accepts one 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

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

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: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

enable-file-database

TRUE

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

add-database-params/init-string

${ccd}/persistence.db

The filename for the SQLite database. If the database does not exist, the Persistence Service will create it. For a list of valid 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.

File: kits/PersistenceService/Transformer/etc/bootstrap.sql
CREATE TABLE IF NOT EXISTS TF_LEGACY_PERSISTENCE (
    JTM_KEY TEXT PRIMARY KEY NOT NULL,
    JTM_VALUE TEXT
);

Persistence Service Client (Transformer 7.1.0)

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 VARCHAR(4096) NOT NULL, (1)
    PRIMARY KEY (PERSISTENCE_USER, PERSISTENCE_ID)
);
1 Adjust the length of the PERSISTENCE_DATA column to accommodate the data storage requirements of your front-end applications. Ask your developers for guidance.

Alerts Service 7.0.0

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

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

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 VARCHAR(4000) NOT NULL, (1)
    WATCHLIST_POSITION NUMBER,
    PRIMARY KEY (WATCHLIST_USER, WATCHLIST_ID)
);

CREATE TRIGGER IF NOT EXISTS TF_WATCHLIST_PERSISTENCE_ADD_POSITION (2)
    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 Adjust the length of the WATCHLIST_DATA column so that it can accommodate all the serialised watchlists for a user. As a guide, one watchlist of 8 currency pairs occupies approximately 350 characters.
2 Customise the name of the trigger to suit the requirements of your database.

See also: