12 déc. 2023 09:51:57 Pablo Abreu
Bonnes pratiques SQL
Création de Table
Lutece implémente un système basé sur REGEXP pour adapter le SQL à différents moteurs de BDD. Cette traduction a lieu au moment de l'execution de ANT pour la construction de la base de données. Ci-dessous une série de bonnes pratiques afin de faciliter ce processus.
| OK | Non recommandé | commentaire |
|---|---|---|
| CREATE TABLE core_admin_auth_db_module | CREATE TABLE ‘core_admin_auth_db_module’ | les caractère d'échappement ‘’ utilisés pour les tables ou colonnes doivent être retirés |
| CREATE TABLE col1 VARCHAR(16), | CREATE TABLE col1 VARCHAR(16) collate utf8_unicode_ci | La définition de l'encodage doit être retirée |
| col INT DEFAULT 9 NOT NULL | col INT NOT NULL DEFAULT 0 | Default et NOT NULL doivent être dans le bon ordre |
| CREATE TABLE ... ( ... ); | CREATE TABLE ... ( ... ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; | Le type de storage engine ne doit pas être spédifié |
| col INT | col INT UNSIGNED | UNSIGNED n'est pas supporté par tous les moteurs de BDD |
| LONG VARCHAR | TINYTEXT;MEDIUM;LONGTEXT | Ne pas utiliser les types TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT. |
| col VAR BINARY LONG | TINYBLOB, BLOB, LONGBLOB | Ne pas utiliser les types TINYBLOB, BLOB, LONGBLOB |
| col SMALLINT | col TINYINT | Ne pas utiliser le type TINYINT |
| /*!40101 SET NAMES utf8 */; | Ne pas laisser de commentaires contextuels sans signification | |
| 'Lutece''s table' | 'Lutece\'s table' | Ne pas échapper les apostrophes avec le caractère \ |
| INSERT INTO (...) VALUES (0x3C3F786D6C207665) | INSERT INTO (...) VALUES ('xsl:stylesheet...') | Pour limiter les risques d'incompatibilité, utiliser si possible le stockage en héxadécimales |
| col TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL | col TIMESTAMP DEFAULT NOW() NOT NULL | NOW() n'est pas compatible avec Oracle |
| specify NULL or NOT NULL and specify DEFAULT value (CURRENT_TIMESTAMP or '1980-01-01 00:00') | col TIMESTAMP | comportement inconsistant en fonction des moteurs de bdd (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 | ne fonctionne pas avec mysql5.5 |
| col TIMESTAMP DEFAULT '1980-01-01 00:00:00' | col TIMESTAMP DEFAULT '0000-00-00 00:00:00' NOT NULL | ne fonctionne pas avec mysql >= 5.7 (default strict mode) |
| col TIMESTAMP DEFAULT '1980-01-01 00:00:00' | col TIMESTAMP DEFAULT 0 NOT NULL | ne fonctionne pas avec mysql >= 5.7 (default strict mode) |
| col SMALLINT default 0 | col boolean default NULL | boolean ne fonctionne pas avec postgres |
| col INT default 0 | col INT(6) default 0 | INT(n) ne fonctionne pas pour hypersql |
| CREATE INDEX idx_name on table_name ( col ) | INDEX ( col ), KEY ( col ) | Les index doivent être déclarés séparément pour postgres et hypersql. |
| create table foo(id int AUTO_INCREMENT, PRIMARY KEY(id) ) | create table foo(id int PRIMARY KEY AUTO_INCREMENT ) | Pour que les expressions REGEXP pour postgresql fonctionnent correctement, auto_increment doit être juste après INT |
Autres instructions
| OK | Non recommandé | commentaire |
|---|---|---|
| INSERT into table( primary_key ) values ( DEFAULT ) ou pas de clé primaire | INSERT into table( primary_key ) values ( NULL ) | non supporté pour hsqldb |
| SELECT * FROM table | REPLACE INTO table values (...) LIMIT X OFFSET Y | Des extensions communes MySQL (REPLACE, LIMIT, OFFSET, ...) ne sont pas supportées par d'autres types de BDD |
Automatisation du passage des scripts au démarrage
Le plugin Plugin liquibase permet d'automatiser le passage des scripts SQL.