: <codebig> CREATE TABLE test.xxx </code>:: data types are changed in <code> appendColumnType() </codebig> in ''PostgreSQLSchemaManager.java'' based on each jdbc type's equivalent in PostgreSQL.:: The implementation in <code> crateTextTable </code> in file ''PostgreSQLSchemaManager.java'' is removed Adapter for now (base classNexJ - Working Inside Schema 's version is being called).: <code>CREATE INDEX test.xxx ' </code> : <codebig> CREATE Trigger test.xxx // if it's needed for PostgreSQL as well as MySQL</codebig>

* Modifications{|class="collapsible collapsed" style="border1-style:solid;border-width:thin;border-color:black"!alignInitializing the SQL environment="left" style="background-color:SteelBlue; font-weight:bold;border-style:solid;border-width:thin;padding: 2px 2px 2px 2px;"| ''* 'Changes Done to MySQL adapter'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:

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

 == 2- DATA DEFINITION LANGUAGE _ DDL ===== 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>''''' 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, changes needed to implementation of '''''<code> appendTableSuffix() </code>''''' in PostgreSQLManager.java was removed: 3- CHARACTER SET: In MySQL, the character set should be done 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>::

create /* PostgreSQL version of creating table script */ CREATE TABLE test.AccountRangeTest( id binary(16) serial not null, contactId binarys text null, bin bytea null, n integer null, n1 smallint null, l bigint null, "DEC" decimal(1610,5) not null, account Type varchar(16) character set utf8 not f double precision null, funds d double precision null, tm timestamp null, b boolean null, constraint Account_PK RangeTest_PK primary key(id) )engine=innoDB;// 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');

|}*<u><code> CREATE TEXT TABLE </code></u>{|:The implementation in '''''<code> crateTextTable() </code>''''' is removed for now (base class="collapsible collapsed" style="border-style's version is being called).:solid;border-widthTODO To be checked if it's the case for PostgreSQL as well. * <u><code>CREATE INDEX </code></u> :thin;border-colorThe implementation in '''''<code> createIndex() </code>''''' is removed for now (base class's version is being called).:black"TODO To be checked if it's the case for PostgreSQL as well.!align="left" style="background-color:SteelBlue; fontNote-weightIf there was a complain for duplicate index name, a fully qualified name can be used. Just note that PostgreSQL doesn't like fully qualified name as <code>table.indexname</code> for indexes or triggers, instead a '_' can be used:bold;border-style<code>table_indexname</code> * <code> <u> CREATE Trigger </u></code>:solid;border-widthTODO To be checked if triggers are needed for PostgreSQL as well. It's not an abstract method in '''''SQLSchemaManager.java'''''.:thin;padding: 2px 2px 2px 2px;"| <code>'''''createTrigger()'''''Files </code> is added to Configure'''''MySQLSchemaManager.java''''' |- |===UPDGRADING SCHEMA===* List of Files needed to be modified :src:: - [[ PostgreSQL_Adapter-nexj/Adapter SchemaManger | PostgreSQLAdapterPostgreSQLSchemaManager.java ]] // Extends SQLAdapter.java - SQL Persistence adapter, responsible for regular data queries -- (insert, select, delete):: [[ PostgreSQL_Adapter-nexj/SchemaManger | PostgreSQLSchemaManager.java SchemaBackup]] ) // Extends SQLSchemaManager.java - class for reading, creating and upgrading the database schema:: [[ PostgreSQL_Adapter-nexj/Upgrade | main.upgrade ]]: test- [[ PostgreSQL_Adapter-nexj/SchemaMangerTest | PostgreSQLSchemaManagerTest.java]] // Extends SQLSchemaManagerTest.java:Script - postgresql_drop.sql * <u><code> DROP TABLE </code></u> * <u><code> INSERT INTO TABLE </code></u> * <u><code> ALTER TABLE </code></u> ==3- DATA MANIPULATION LANGUAGE _ DML ==* List of Files needed to be modified[[sampleProj]]: src- [[ PostgreSQL_Adapter-nexj/AdapterTest Adapter | PostgreSQLAdapterTestPostgreSQLAdapter.java ]]] // Extends SQLAdapterTestSQLAdapter.java - SQL Persistence adapter, responsible for regular data queries (insert, select, delete):: test- [[ PostgreSQL_Adapter-nexj/SchemaMangerTest AdapterTest | PostgreSQLSchemaManagerTestPostgreSQLAdapterTest.java ]]] // Extends SQLSchemaManagerTestSQLAdapterTest.java

:: postgresql_setuppostgresql_insert.sql:: TO be cond'|}