12 déc. 2023, 09:51:57 pablo.abreu

Bonnes pratiques SQL

Table des matières

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’exécution de ANT pour la construction de la base de données. Ci-dessous une série de bonnes pratiques afin de faciliter ce processus.

OKNon recommandécommentaire
CREATE TABLE core_admin_auth_db_moduleCREATE TABLE ‘core_admin_auth_db_module’les caractères 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_ciLa définition de l’encodage doit être retirée
col INT DEFAULT 9 NOT NULLcol INT NOT NULL DEFAULT 0Default 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écifié
col INTcol INT UNSIGNEDUNSIGNED n’est pas supporté par tous les moteurs de BDD
LONG VARCHARTINYTEXT;MEDIUM;LONGTEXTNe pas utiliser les types TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.
col VAR BINARY LONGTINYBLOB, BLOB, LONGBLOBNe pas utiliser les types TINYBLOB, BLOB, LONGBLOB
col SMALLINTcol TINYINTNe 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 hexadécimales
col TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULLcol TIMESTAMP DEFAULT NOW() NOT NULLNOW() n’est pas compatible avec Oracle
//specify NULL or NOT NULL and specify DEFAULT value (CURRENT_TIMESTAMP or ‘1980-01-01 00:00’)col TIMESTAMPcomportement inconsistant en fonction des moteurs de bdd (par exemple, 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_TIMESTAMPne fonctionne pas avec mysql 5.5
col TIMESTAMP DEFAULT ‘1980-01-01 00:00:00’col TIMESTAMP DEFAULT ‘0000-00-00 00:00:00’ NOT NULLne fonctionne pas avec mysql >= 5.7 (mode strict par défaut)
col TIMESTAMP DEFAULT ‘1980-01-01 00:00:00’col TIMESTAMP DEFAULT 0 NOT NULLne fonctionne pas avec mysql >= 5.7 (mode strict par défaut)
col SMALLINT default 0col boolean default NULLboolean ne fonctionne pas avec postgres
col INT default 0col INT(6) default 0INT(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

OKNon recommandécommentaire
INSERT into table( primary_key ) values ( DEFAULT ) // ou pas de clé primaireINSERT into table( primary_key ) values ( NULL )non supporté pour hsqldb
SELECT * FROM tableREPLACE INTO table values (…) LIMIT X OFFSET YDes 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 liquibase permet d’automatiser le passage des scripts SQL.