Apr 14, 2017 4:45:33 PM Jon Harper avatar   35    

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
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.
TODO IDENTITY/SERIAL/AUTO_INCREMENT Document lutece 6 auto_increment support once syntax is settled

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