1
edit
Changes
Created page with '<big><big> PostgreSQL Adapter for NexJ - Working Inside Schema 'test' </big></big> {{MinNexJ_Express Index}} ===1- Initializing the SQL environment=== * ''PostgreSQLAdapter.java…'
<big><big> PostgreSQL Adapter for NexJ - Working Inside Schema 'test' </big></big>
{{MinNexJ_Express Index}}
===1- Initializing the SQL environment===
* ''PostgreSQLAdapter.java''
: When a connection is first established, this initial SQL statement should execute in MySQL, which is not the case for PostgreSQL, so the implementation was removed from PostgreSQLAdapter.java:
::
<source lang=java>
/* 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();
}
</source>
* ''postgresql_create.sql''
: This file was just created as a copy of mysql_create.sql, which was not the case for PostgreSQL again. So the line in the file is removed for now. The file itself may be deleted later, if there was no need for it. Name of the script file is being returned in '''''getCreateEtcScriptName()'''''' in '''PostgreSQLSchemaManager.java'''
::
<source lang=java>
/* nexj/core/persistence/sql/etc/postgresql_create.sql */
set sql_mode = concat(@@sql_mode, ',ANSI_QUOTES');
</source>
: After activating the connection, the database is locked.
===2- Modifying Syntax ===
* <u><code> CREATE TABLE </code></u>
: File to configure: ''PostgreSQLSchemaManager.java''
:: 1- DATA TYPE: data types are changed in '''''<code> appendColumnType() </code>''''' based on each jdbc type's equivalent in PostgreSQL.
:::*[http://db.apache.org/ddlutils/databases/postgresql.html mapping JDBC data types to PostgreSQL data types]
:::*[http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL#Data_Types comparison between Postgre and MySQL data types]
:: 2- STORAGE ENGINE: In MySQL, the storage engine is set to a transactional safe engine such as [http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL#MySQL:InnoDB InnoDB], whereas PostgreSQL has a single built in engine. So, implementation of '''''<code> appendTableSuffix() </code>''''' 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.
:: 4- AUTO INCREMENT: PostgreSQL doesn't support 'auto_increment' as some other databases for a unique identifier column. There are two ways around this, 1- To create a 'sequence', 2- Use SERIAL, which is a macro around 'sequence'. SERIAL is an 'integer' and a 'sequence, with the column default to the sequences next value. As of postgreSQL 7.3, to make a serial column unique, it should be specified as a unique constraint or a primary key. More info @ [[PostgreSQL_Adapter-nexj/Resources#Mapping_Data_Types.2C_Metadata | PostgreSQL Resources]]
::
<source lang=java>
/* 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
</source>
::
<source lang=java>
/* PostgreSQL version of creating table script */
CREATE TABLE test.RangeTest(
id serial 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)
);
// Using SERIAL is another way to work around 'sequence'
//CREATE SEQUENCE test.RangeTest_id_seq;
//ALTER TABLE test.RangeTest ALTER COLUMN id SET DEFAULT NEXTVAL('test.RangeTest_id_seq');
</source>
*<u><code> CREATE TEXT TABLE </code></u>
::The implementation in '''''<code> crateTextTable </code>''''' is removed for now (base class's version is being called).
* <u><code>CREATE INDEX </code></u>
* <code> <u>CREATE Trigger</u> // if it's needed for PostgreSQL as well as MySQL</code>
{|class="collapsible collapsed" style="border-style:solid;border-width:thin;border-color:black"
!align="left" style="background-color:SteelBlue; font-weight:bold;border-style:solid;border-width:thin;padding: 2px 2px 2px 2px;"| '''Files to Configure'''
|-
|
:src
:: [[ PostgreSQL_Adapter-nexj/Adapter | PostgreSQLAdapter.java ]] // Extends SQLAdapter.java - SQL Persistence adapter, responsible for regular data queries (insert, select, delete)
:: [[ PostgreSQL_Adapter-nexj/SchemaManger | PostgreSQLSchemaManager.java ]] // Extends SQLSchemaManager.java - class for reading, creating and upgrading the database schema
:: [[ PostgreSQL_Adapter-nexj/Upgrade | main.upgrade ]]
: test
:: [[ PostgreSQL_Adapter-nexj/AdapterTest | PostgreSQLAdapterTest.java ]]] // Extends SQLAdapterTest.java
:: [[ PostgreSQL_Adapter-nexj/SchemaMangerTest | PostgreSQLSchemaManagerTest.java]] // Extends SQLSchemaManagerTest.java
: Script - scripts are being processed through SQLDataTest.java
:: postgresql_setup.sql
:: TO be cond'
|}
{{MinNexJ_Express Index}}
===1- Initializing the SQL environment===
* ''PostgreSQLAdapter.java''
: When a connection is first established, this initial SQL statement should execute in MySQL, which is not the case for PostgreSQL, so the implementation was removed from PostgreSQLAdapter.java:
::
<source lang=java>
/* 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();
}
</source>
* ''postgresql_create.sql''
: This file was just created as a copy of mysql_create.sql, which was not the case for PostgreSQL again. So the line in the file is removed for now. The file itself may be deleted later, if there was no need for it. Name of the script file is being returned in '''''getCreateEtcScriptName()'''''' in '''PostgreSQLSchemaManager.java'''
::
<source lang=java>
/* nexj/core/persistence/sql/etc/postgresql_create.sql */
set sql_mode = concat(@@sql_mode, ',ANSI_QUOTES');
</source>
: After activating the connection, the database is locked.
===2- Modifying Syntax ===
* <u><code> CREATE TABLE </code></u>
: File to configure: ''PostgreSQLSchemaManager.java''
:: 1- DATA TYPE: data types are changed in '''''<code> appendColumnType() </code>''''' based on each jdbc type's equivalent in PostgreSQL.
:::*[http://db.apache.org/ddlutils/databases/postgresql.html mapping JDBC data types to PostgreSQL data types]
:::*[http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL#Data_Types comparison between Postgre and MySQL data types]
:: 2- STORAGE ENGINE: In MySQL, the storage engine is set to a transactional safe engine such as [http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL#MySQL:InnoDB InnoDB], whereas PostgreSQL has a single built in engine. So, implementation of '''''<code> appendTableSuffix() </code>''''' 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.
:: 4- AUTO INCREMENT: PostgreSQL doesn't support 'auto_increment' as some other databases for a unique identifier column. There are two ways around this, 1- To create a 'sequence', 2- Use SERIAL, which is a macro around 'sequence'. SERIAL is an 'integer' and a 'sequence, with the column default to the sequences next value. As of postgreSQL 7.3, to make a serial column unique, it should be specified as a unique constraint or a primary key. More info @ [[PostgreSQL_Adapter-nexj/Resources#Mapping_Data_Types.2C_Metadata | PostgreSQL Resources]]
::
<source lang=java>
/* 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
</source>
::
<source lang=java>
/* PostgreSQL version of creating table script */
CREATE TABLE test.RangeTest(
id serial 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)
);
// Using SERIAL is another way to work around 'sequence'
//CREATE SEQUENCE test.RangeTest_id_seq;
//ALTER TABLE test.RangeTest ALTER COLUMN id SET DEFAULT NEXTVAL('test.RangeTest_id_seq');
</source>
*<u><code> CREATE TEXT TABLE </code></u>
::The implementation in '''''<code> crateTextTable </code>''''' is removed for now (base class's version is being called).
* <u><code>CREATE INDEX </code></u>
* <code> <u>CREATE Trigger</u> // if it's needed for PostgreSQL as well as MySQL</code>
{|class="collapsible collapsed" style="border-style:solid;border-width:thin;border-color:black"
!align="left" style="background-color:SteelBlue; font-weight:bold;border-style:solid;border-width:thin;padding: 2px 2px 2px 2px;"| '''Files to Configure'''
|-
|
:src
:: [[ PostgreSQL_Adapter-nexj/Adapter | PostgreSQLAdapter.java ]] // Extends SQLAdapter.java - SQL Persistence adapter, responsible for regular data queries (insert, select, delete)
:: [[ PostgreSQL_Adapter-nexj/SchemaManger | PostgreSQLSchemaManager.java ]] // Extends SQLSchemaManager.java - class for reading, creating and upgrading the database schema
:: [[ PostgreSQL_Adapter-nexj/Upgrade | main.upgrade ]]
: test
:: [[ PostgreSQL_Adapter-nexj/AdapterTest | PostgreSQLAdapterTest.java ]]] // Extends SQLAdapterTest.java
:: [[ PostgreSQL_Adapter-nexj/SchemaMangerTest | PostgreSQLSchemaManagerTest.java]] // Extends SQLSchemaManagerTest.java
: Script - scripts are being processed through SQLDataTest.java
:: postgresql_setup.sql
:: TO be cond'
|}
