PostgreSQL Adapter-nexj/schema-test

Working Inside Schema 'test'

Initializing the SQL environment

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.

Modifying Syntax

• PostgreSQLSchemaManager.java

CREATE TABLE test.xxx

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

2- In MySQL, the storage engine is set to a transactional safe engine such as InnoDB, whereas PostgreSQL has a single built in engine. So, implementation of appendTableSuffix() in PostgreSQLManager.java was removed

create table test.Account(
    id binary(16) not null, contactId binary(16) not null, constraint Account_PK primary key(id)
 )ENGINE=InnoDB CHARACTER SET=utf8;  // PostgreSQL version with no ENGINE and CHARACTER

crateTextTable

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

Files to Configure

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 main.upgrade test PostgreSQLAdapterTest.java ] // Extends SQLAdapterTest.java PostgreSQLSchemaManagerTest.java // Extends SQLSchemaManagerTest.java Script - scripts are being processed through SQLDataTest.java postgresql_setup.sql TO be cond'