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.

- STEPS for Connection

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.

- Modifications

Changes Done to MySQL adapter

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) )engine=innoDB;

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

3- Working inside Schema 'test'

CREATE TABLE test.xxx

data types are changed in appendColumnType() in PostgreSQLSchemaManager.java based on each jdbc type's equivalent in PostgreSQL.

The implementation in crateTextTable in file PostgreSQLSchemaManager.java is removed for now (base class's version is being called).

CREATE INDEX test.xxx

CREATE Trigger test.xxx // if it's needed for PostgreSQL as well as MySQL