1,234
edits
Changes
no edit summary
<big><big> PostgreSQL Adapter for NexJ - Working Inside Schema 'test' </big></big> ===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 it the implementation was commented outremoved from PostgreSQLAdapter.java:
::
<source lang=java>
}
</source>
* ''postgresql_create.sql'': Also This file was just created as a copy of mysql_create.sql, which was not the case for PostgreSQL again. So the same line should in the file is removed for now. The file itself may be commented out in postgresql_createdeleted later, if there was no need for it.sql Name of the script, which file is being used to create tables returned in the database 'test':'''getCreateEtcScriptName()'''''' in '''PostgreSQLSchemaManager.java'''
::
<source lang=java>
: After activating the connection, the database is locked.
== 2- DATA DEFINITION LANGUAGE _ DDL ==== Modifying Syntax = CREATING SCHEMA ===* '''''List of Files needed to be modified *:src- [[ PostgreSQL_Adapter-nexj/SchemaManger | PostgreSQLSchemaManager.java''''']] --- ([[SchemaBackup]]) // Extends SQLSchemaManager.java - class for reading, creating and upgrading the database schema: test- [[ PostgreSQL_Adapter-nexj/SchemaMangerTest | PostgreSQLSchemaManagerTest.java]] // Extends SQLSchemaManagerTest.java: Script - scripts are being processed through SQLDataTest.java * <u><code> CREATE TABLE </code></u>:: 1- '''DATA TYPE''': data types are changed in '''''<code> appendColumnType() </code> in ''PostgreSQLSchemaManager.java''' based on each jdbc type's equivalent in PostgreSQL. [http:://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL#Data_Types Here] is a tabular comparison between Postgre and MySQL data types. Also *[http://db.apache.org/ddlutils/databases/postgresql.html here] is a 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,
)engine=InnoDB character set = utf8
</source>
<source lang=java>
/* PostgreSQL version of creating table script */
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>
: Script - scripts are being processed through SQLDataTest.java
:: postgresql_setuppostgresql_insert.sql:: TO be cond'|}