PostgreSQL Adapter

PostgreSQL Adapter for NexJ

Project Goal

Develope an adapter to enable NexJ Express model to interact with PostgreSQL database

Current Status

Dec 2010
Database 'test' is created in Postgre. sample table 'mutex' is created for testing the connection properties. No need for SET SQL mode in Postgre Creating tables in process Tables are created. Creating indexes and triggers in process

Nov 2010

Walk through parts of code related to SQLadapter, SqlSchemaManger and setup scripts in NexJ Express code
SQLAdapter (src, test) and SQLSchemaManager (src, test) classes are extended to create PostgreSQL classes
Proper PostgreSQL Driver is installed.
Postgre package url for XADataSource is added to the related files.
cofig and connection files are modified based on Postgre properties
Application is now connected to the Postgre database.

TODOs

Project Phases

Phase 1: Investigation

Get requirements from NexJ (Meeting on Friday Nov 5th)
Walk-through the code
Familiarization with PostgreSQL

Phase 2: Connect to PostgreSQL

Create Test Environment
Files to Modify
Files to configure for connection 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

main.upgrade

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'

Phase 3: Test first design

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

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;

Phase 4: Test properties specific to PostgreSQL

Add test cases that are specific to PostgreSQL database

Phase 5: Optimization

After implementation is done, optimizing the modified files.

Resources

Intro

Concept of Adapter [1]

FOSSLC PostgreSQL

CDOT Wiki β