Difference between revisions of "PostgreSQL Adapter-nexj/schema-ddl"

From CDOT Wiki
Jump to: navigation, search
(CREATING SCHEMA)
Line 4: Line 4:
 
== Working with Database Structure - DDL ==
 
== Working with Database Structure - DDL ==
 
=== CREATING SCHEMA ===
 
=== CREATING SCHEMA ===
* List of Files needed to be modified *
+
* List of Files need to be modified *
 
:src- [[ PostgreSQL_Adapter-nexj/SchemaManger | PostgreSQLSchemaManager.java ]] --- ([[SchemaBackup]]) // Extends SQLSchemaManager.java - class for reading, creating and upgrading the database schema
 
: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
 
: test- [[ PostgreSQL_Adapter-nexj/SchemaMangerTest | PostgreSQLSchemaManagerTest.java]]  // Extends SQLSchemaManagerTest.java

Revision as of 14:48, 15 December 2010

PostgreSQL Adapter for NexJ - Working Inside Schema 'test'

Working with Database Structure - DDL

CREATING SCHEMA

  • List of Files need to be modified *
src- PostgreSQLSchemaManager.java --- (SchemaBackup) // Extends SQLSchemaManager.java - class for reading, creating and upgrading the database schema
test- PostgreSQLSchemaManagerTest.java // Extends SQLSchemaManagerTest.java
Script - scripts are being processed through SQLDataTest.java
  • CREATE TABLE
1- DATA TYPE: data types are changed in appendColumnType() based on each jdbc type's equivalent in PostgreSQL.
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.
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 Resources
/* 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 */

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');
  • CREATE TEXT TABLE
The implementation in crateTextTable() is removed for now (base class's version is being called).
TODO To be checked if it's the case for PostgreSQL as well.
  • CREATE INDEX
The implementation in createIndex() 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 table.indexname for indexes or triggers, instead a '_' can be used: table_indexname
  • CREATE Trigger
TODO To be checked if triggers are needed for PostgreSQL as well. It's not an abstract method in SQLSchemaManager.java.
createTrigger() is added to MySQLSchemaManager.java

UPDGRADING SCHEMA

  • List of Files needed to be modified
src - PostgreSQLSchemaManager.java --- (SchemaBackup) // Extends SQLSchemaManager.java - class for reading, creating and upgrading the database schema
main.upgrade
test- PostgreSQLSchemaManagerTest.java // Extends SQLSchemaManagerTest.java
Script - postgresql_drop.sql
  • DROP TABLE
  • INSERT INTO TABLE
  • ALTER TABLE