1Transact as a PostgreSQL Extension

Prerequisites

Installation of the following is required before proceeding:

  • PostgreSQL database

Database Creation

3 databases are needed for the full installation of 1Transact on PostgreSQL:

  1. Feature Schema
  2. Messaging Schema
  3. Security Schema

In order to generate these, connect to PostgreSQL and run the following command to create the databases:

create database "testDb";

Messaging Database Set-up

Connect to the JMS database and create a table within it, using the following SQL:

\c sms_jms_store

CREATE TABLE mstransact_wlstore(

id INTEGER NOT NULL PRIMARY KEY,

type INTEGER NOT NULL,

handle INTEGER NOT NULL,

record BYTEA NOT NULL);

Steps for Install

  1. Extract the ms-transact-release-[version_number]-release.zip from 1Transact_[VersionNumber].zip.
  2. Copy sms_1transact.control and sms_1transact.sql to <postgres-installation>\share\extension. These files contain the database installation definitions.

  3. Connect to the database in which you want to install 1Transact using tools such as the PSQL command line: (psql -U <user>) or from the PGAdmin Execute Arbitrary SQL Queries window.

    Note: PGAdmin users will not be able to use the /I helper functions.

  4. Execute the SQL

    create extension sms_1transact;

    SELECT sms_1transact.initialise();

    This will create the sms_1transact schema and metadata tables and functions in that schema.

  5. To check that the installation has succeeded:
    • From the PSQL command window you can list the database objects and the functions using:
      • \d sms_1transact.*
      • \df sms_1transact.*
    • From the pgAdmin query window, select the Graphical Query Builder tab, expand the schemas node and check that there is a sms_1transact item.

     

  6. You can unistall the 1Transact Postgres extension by first un-version-enabling all your tables and then executing drop extension sms_1transact;

Note: It is possible that whilst trying to drop the 1Transact Postgres extension it will fail, giving the following hint:
Hint: Use DROP ... CASCADE to drop the dependent objects too.
DO NOT use this cascade command as it will delete your features, instead you should version-disable your feature tables before dropping the extension.

Adding PostgreSQL JDBC driver to WebLogic

The standard WebLogic distribution does not include support for connecting to a PostgreSQL database. The PostgreSQL JDBC driver must be added manually to the 1SMS domain, and the domain start-up scripts must be edited to ensure it is available in the classpath. The JDBC driver can be downloaded from the PostreSQL website (use version 42.2.5).

  1. Stop WebLogic Admin server for your domain.
  2. Ensure the domain directory includes a sub-directory named drivers. Copy postgresql-42.2.5.jar into this directory.

  3. Modify PRE_CLASSPATH in setUserOverrides.cmd (Windows) or setUserOverrides.sh (Linux).

    For example on Windows:

    • set PRE_CLASSPATH=C:\oracle\weblogic1213\user_projects\domains/smsdomain\drivers\postgresql-42.2.5.jar

    or on Linux:

    • export PRE_CLASSPATH=C:/oracle/weblogic1213/user_projects/domains/smsdomain/drivers/postgresql-42.2.5.jar

  4. Re-start Admin server for your domain.

Note: The PostgreSQL JDBC driver must be available to the 1SMS installer when it is installing 1Transact for PostgreSQL. The installer will execute setUserOverrides to ensure the driver is on the class path. The directory containing the driver does not have to be named drivers. However, it is recommended that the driver is not installed into the domain lib directory- setUserOverrides would not normally put the lib directory on the class path.