1
edit
Changes
no edit summary
<big><big> PostgreSQL Adapter for NexJ - Working Inside Schema 'test' </big></big>
{{MinNexJ_Express Index}}
== Working with Database Structure - 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, 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).
:TODO To be checked if it's the case for PostgreSQL as well.
* <u><code>CREATE INDEX </code></u>
:The implementation in '''''<code> createIndex() </code>''''' is removed for now (base class's version is being called).
:TODO To be checked if it's the case for PostgreSQL as well.
:Note- If 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: <code>table_indexname</code>
* <code> <u> CREATE Trigger </u></code>
:TODO To be checked if triggers are needed for PostgreSQL as well. It's not an abstract method in '''''SQLSchemaManager.java'''''.
: <code>'''''createTrigger()'''''</code> is added to '''''MySQLSchemaManager.java'''''
===UPDGRADING 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
: [[ 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>
