Jun 23, 2017 5:37:47 PM seb leridon avatar   247    

Table creation

Lutece implements a very simple regexp-based database translation system. It is used for the database creation during the ant execution. Here are some simple DOs and DON'Ts to work on all supported databases.

The following are column definitions in CREATE TABLE ( ... ) statements

DODON'Tcomment
col TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL col TIMESTAMP DEFAULT NOW() NOT NULL NOW() doesn't work on oracle
col SMALLINT default 0 col boolean default NULL boolean doesn't work on postgres
col INT default 0 col INT(6) default 0 INT(n) doesn't work on hypersql
CREATE INDEX idx_name table_name ( col ) INDEX ( col ), KEY ( col ) Need a separate statement on postgres and hypersql. No need to drop or check if exists if the table is dropped.
create table foo(id int AUTO_INCREMENT, PRIMARY KEY(id) ) create table foo(id int PRIMARY KEY AUTO_INCREMENT ) for the postgresql regexp to work, auto_increment needs to be next to the int type

Other statements

Some commonly used MySQL extensions are not supported on other databases. Avoid them if possible:

  • SELECT * FROM table LIMIT X
  • SELECT * FROM table LIMIT X OFFSET Y
  • REPLACE INTO