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