Oct 22, 2018 7:26:30 PM Jon Harper avatar   703

SQL Recipes

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
specify NULL or NOT NULL and specify DEFAULT value (CURRENT_TIMESTAMP or '1980-01-01 00:00') col TIMESTAMP inconsistent behavior across databases (for example mysql versions 5.5, 5.6, 5.7, 8)
Do it in the java code :
daoUtil.setTimestamp( new Timestamp(System.currentTimeMillis( ) ) )
col1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP, col2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP doesn't work my mysql5.5
col TIMESTAMP DEFAULT '1980-01-01 00:00:00' col TIMESTAMP DEFAULT '0000-00-00 00:00:00' NOT NULL doesn't work with mysql >= 5.7 (default strict mode)
col TIMESTAMP DEFAULT '1980-01-01 00:00:00' col TIMESTAMP DEFAULT 0 NOT NULL doesn't work with mysql >= 5.7 (default strict mode)
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
INSERT into table( primary_key ) values ( DEFAULT ) or omit the primary key entirely INSERT into table( primary_key ) values ( NULL ) not supported by hsqldb

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