NexJ Express Connecting to PostgreSQL

PostgreSQL Adapter for NexJ - Create Test Environment

1- Setup the postgresql-connection

For testing the connection, the 'PostgreSQLAdapterTest.java' is being used.

• TODOs
• Files to configure based on PostgreSQL properties

src

PostgreSQLAdapter.java // Extends SQLAdapter.java - SQL Persistence adapter, responsible for regular data queries (insert, select, delete)

PostgreSQLSchemaManager.java // Extends SQLSchemaManager.java - class for reading, creating and upgrading the database schema

system.dstype

DatabaseTool.java // This file calles SQLSchemaMangerFactory.java and creates the connection

RelationalDatabase.java // Sets the default properties for the supported drivers

RelationalDatabaseFragment.java // Sets the custom fragment properties for the supported drivers

Driver type and some other properties in JDBCInfo.java

SQLSchemaManagerFactory.java // Sets the connection for the particular adapter type

test

AllTests.java

PostgreSQLAdapterTest.java // Extends SQLAdapterTest.java

PostgreSQLSchemaManagerTest.java // Extends SQLSchemaManagerTest.java

default.config // System resources are being extracted from these 4 XML files

postgresql.connection

filestorage.connections

filestoragefragment.connections

Script - scripts are being processed through SQLDataTest.java

postgresql_setup.sql

TO be cond'

• NOTES

Installed the JDBC3 Postgresql Driver, Version 9.0-801 and added it to the libraries in JDK

The driver class which is being used for NexJ Express Model is: org.postgresql.xa.PGXADataSource

The driver is loaded from RelationalDatabaseFragment.java and RelationalDatabase.java

Metadata is loaded from the particular driver

   /* MySQLAdapterTest.java */
      try
      {
         s_metadata = loadMetadata(ADAPTER_NAME);
      }

   /* SQLDataTest.java */
   protected static Metadata loadMetadata(String sAdapter)
   {
      Properties props = SysUtil.getConfigProperties();  // sets DEFAULT_CONFIG_URL = '/' + NAMESPACE + "/default.config"
      props = new Properties(props);
      props.setProperty(XMLMetadataLoader.CONNECTIONS_URL_PROPERTY, "/nexj/" + sAdapter.toLowerCase(Locale.ENGLISH) + ".connections");  // sets the properties to postgresql.connection

After all properties are loaded, it creates the connection through Connection Factory files.

When a connection is first established, this initial SQL statement should execute in MySQL, which is not the case for PostgreSQL, so it was commented out:

/* MySQLAdapter.java */

public String getInitialSQL()
   {
      StringBuffer buf = new StringBuffer();

      buf.append("set sql_mode = concat(@@sql_mode, ',ANSI_QUOTES')"); // allow using doublequote when quoting column names in "CREATE TABLE" statements
      buf.append(";set optimizer_search_depth = 0"); // let DB automatically decide on how long it takes to examine plans, improves long planning sessions
      buf.append(";set max_sort_length = ").append(Math.max(MAX_VARCHAR_PRECISION, MAX_VARBINARY_PRECISION)); // set TEXT/BLOB minimum sorting length to be same as cutoff between varchar/text
      return buf.toString();
   }

Also the same line should be commented out in postgresql_create.sql script, which is being used to create tables in the database 'test':

/* nexj/core/persistence/sql/etc/postgresql_create.sql */
set sql_mode = concat(@@sql_mode, ',ANSI_QUOTES');

After activating the connection, the database is locked.

In MySQL, the storage engine is set to a transactional safe engine such as InnoDB, whereas PostgreSQL has a single built in engine. So, changes needed to be done to this script:

create table test.Account(
    id binary(16) not null, contactId binary(16) not null, account Type varchar(16) character set utf8 not null, funds double null,
    constraint Account_PK primary key(id)
 );

2- Create a PostgreSQL database through some scripts

Through the process in postgresql_setup.sql, the database was created, initialized and tested:

A database called 'test is created in PostgreSQL : CREATE DATABASE test

To create a user along with password: CREATE USER test WITH PASSWORD 'test';

Login to the database 'test' with username 'test': psql -U test test

Create a Schema called 'test' inside the database 'test' with particular owner 'test': CREATE SCHEMA test AUTHORIZATION test;

Create a table called 'Mutex' with primary key: CREATE TABLE test.Mutex(id INT PRIMARY KEY);

Insert a value into the primary key: INSERT INTO test.Mutex(id) VALUES (1);