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.
|
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
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
| 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
- Concept of Adapter [1]
- FOSSLC PostgreSQL
