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 TYPE: data types are changed in appendColumnType() in PostgreSQLSchemaManager.java based on each jdbc type's equivalent in PostgreSQL. Here is a tabular comparison between Postgre and MySQL data types. Also here is a mapping JDBC data types to PostgreSQL data types.

2- STORAGE ENGINE: 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

3- CHARACTER SET: In MySQL, the character set should be defined when creating a table. Whereas in PostgreSQL when a database is created, the character set is set to 'UTF8' by default.

/* MySQL version of creating table script */
create table test.RangeTest(
   id int auto_increment not null, s varchar(1) character set utf8 null, bin varbinary(1) null,
   n int null, n1 tinyint unsigned null, l bigint null, "DEC" decimal(10,5) null,
   f float null, d double null, tm datetime null, b boolean null,
   constraint RangeTest_PK primary key(id)
)engine=InnoDB character set = utf8

/* PostgreSQL version of creating table script */
reate table test.RangeTest(
   id integer not null, s text null, bin bytea null,
   n integer null, n1 smallint null, l bigint null, "DEC" decimal(10,5) null,
   f double precision null, d double precision null, tm timestamp null, b boolean null,
   constraint RangeTest_PK primary key(id)
);

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'