Dec 12, 2023 9:51:57 AM Pablo Abreu
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
| DO | DON'T | comment |
|---|---|---|
| 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
| DO | DON'T | comment |
|---|---|---|
| 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. |
Automatically run scripts at startup
The liquibase plugin can run scripts automatically.