Apr 8, 2020 10:04:06 AM seb leridon avatar

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
CREATE TABLE core_admin_auth_db_module CREATE TABLE ‘core_admin_auth_db_module’ Escapement characters ‘’ used for table or column names must be removed
 CREATE TABLE col1 VARCHAR(16),  CREATE TABLE col1 VARCHAR(16) collate utf8_unicode_ci  Definition of encoding must be deleted 
 col INT DEFAULT 9 NOT NULL  col INT NOT NULL DEFAULT 0  Default value and nullable property must respect a given order. 
 CREATE TABLE ... ( ... );  CREATE TABLE ... ( ... ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;  When creating a table, the storage engine as well as the encoding must not be specified. 
 col INT   col INT UNSIGNED UNSIGNED is not supported on all databases  
 LONG VARCHAR  TINYTEXT;MEDIUM;LONGTEXT  Do not use the data types TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT. Use LONG VARCHAR writen in two words 
 col VAR BINARY LONG  TINYBLOB, BLOB, LONGBLOB  Do not use data types like TINYBLOB, BLOB, LONGBLOB 
 col SMALLINT  col TINYINT  Do not use the type TINYINT 
   /*!40101 SET NAMES utf8 */;  remove contextual comments
 'Lutece''s table'  'Lutece\'s table'  Do not use the escapement character \ to escape a single quote 
 INSERT INTO (...) VALUES (0x3C3F786D6C207665)  INSERT INTO (...) VALUES ('xsl:stylesheet...') To limit the risks of incompatibility, always prefer to store the data in binary when possible.
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 on 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

DODON'Tcomment
INSERT into table( primary_key ) values ( DEFAULT ) or omit the primary key entirely INSERT into table( primary_key ) values ( NULL ) not supported by hsqldb
SELECT * FROM table REPLACE INTO table values (...) LIMIT X OFFSET Y Some commonly used MySQL extensions (REPLACE, LIMIT, OFFSET, ...) are not supported on other databases. Avoid them if possible.