Définition de données avec QC

Les « pseudo-types » de données de QC présentaient quels types de données utiliser avec QC.

Pour parvenir à ce résultat, on peut créer les tables dont on a besoin directement dans Access, TOAD, SQL Server Enterprise Manager… pour la base de données cible en veillant à bien respecter ces consignes.

Puis, lorsque le besoin d’utiliser une autre base de données se présente, il suffit de recréer les tables à l’identique dans l’autre base de données. Si on a été un peu prévoyant et que l’on dispose des scripts SQL de création de la base de données, il est possible de les modifier à coup de rechercher / remplacer pour les traduire de la syntaxe de la base de données source dans celle la base de données cible.

• Avantage : on ne se complique pas la vie tant qu’on a pas besoin de changer de base de données.
• Inconvénient : pas toujours évident d’avoir des scripts à jour (ou de pouvoir les regénérer) et le rechercher / remplacer ne fait pas tout.

Lorsqu’il est très probable que l’application devra être multi bases (ou lorsque la base de données cible n’est pas encore définie au début du projet), il est possible de rédiger les scripts de création de la structure de la base de données directement dans une syntaxe « multi-base ».

L’outil de requêtes disponible avec QC supporte un certain nombre de commandes DDL (Data Definition Language) qui couvrent la majorité des cas rencontrés dans les scripts de création et de mise à jour des tables d’une base de données relationnelle.

Créer une table, sa clé primaire et un index

/* Create table
*/
CREATE TABLE test_Parametres (
idParametre AUTOINCREMENT,
libelle TEXT(100)
);
GO;

/* Create primary key
*/
ALTER TABLE test_Parametres
ADD CONSTRAINT PK_test_Parametres PRIMARY KEY (idParametre);
GO;

/* Create index
*/
CREATE UNIQUE INDEX UK_test_Parametres_libelle ON test_Parametres (libelle);
GO;

Créer une table, une clé étrangère et un index

/* Create table and primary key
*/
CREATE TABLE test_Donnees (
  idDonnee AUTOINCREMENT,
  vraiFaux BOOLEAN,
  petitEntier SMALLINT,
  entier INT,
  grandEntier BIGINT,
  simplePrecision SINGLE,
  doublePrecision DOUBLE,
  desSous CURRENCY,
  texteLong TEXT(2000),
  texteCourt TEXT(100),
  texteMemo MEMO,
  jourHeure DATETIME,
  idParametre INT,
  CONSTRAINT PK_test_Donnees PRIMARY KEY (idDonnee)
);
GO;

/* Create foreign key
*/
ALTER TABLE test_Donnees
ADD CONSTRAINT FK_test_Donnees_Parametres FOREIGN KEY (idParametre) REFERENCES test_Parametres (idParametre) ON DELETE CASCADE ON UPDATE CASCADE;
GO;

/* Create index
*/
CREATE INDEX IK_test_Donnees_texteCourt ON test_Donnees (texteCourt);
GO;

Notes :

• dans ce cas, la création de la clé primaire a été définie au niveau de la commande CREATE TABLE.
• ON UPDATE CASCADE n’est pas directement supporté avec Oracle

Modifier une table

/* Add a column
*/
ALTER TABLE test_Donnees ADD COLUMN autreChamp TEXT(50);
GO;

/* Modify a column
*/
ALTER TABLE test_Donnees MODIFY COLUMN autreChamp TEXT(100);
GO;

/* Rename a column
*/
ALTER TABLE test_Donnees RENAME COLUMN autreChamp TO autreTexte;
GO;

/* Remove a column
*/
ALTER TABLE test_Donnees DROP COLUMN autreTexte;
GO;

Note : RENAME COLUMN ne fonctionne pas avec Access et MySQL.

Faire du ménage

DROP TABLE test_Donnees;
GO;

DROP TABLE test_Parametres;
GO;

Les « pseudo-types » de données de QC

QC étant destiné à être multi base de données, il est nécessaire de prendre un certain nombre de précautions pour tout ce qui concerne SQL, qu’il s’agisse des requêtes ou des types de données.

Pour les requêtes on verra plus tard, pour les types de données, c’est ici.

Pour faciliter le développement multi base de données voire simplifier la migration d’une base de données à l’autre, QC ne gère qu’un sous-ensemble de types de données, qui fonctionnent de façon plus ou moins identique dans la plupart des bases de données.

La liste des « pseudo-types » de données reconnues par QC est la suivante :

• AUTOINCREMENT
• BOOLEAN
• SMALLINT
• INT
• BIGINT
• SINGLE
• DOUBLE
• CURRENCY
• TEXT(2000)
• TEXT
• MEMO
• DATETIME
• DATE
• TIME
• BINDATA

Remarques :

• Pour le type de données TEXT, il faut impérativement définir une longueur comprise entre 1 et 255 ou strictement égale à 2000.
• Il est conseillé d’éviter d’utiliser des données de types CURRENCY (n’existe réellement que sous Access et Sql Server) et BINDATA.
• Le type DATE et le type TIME ne doivent pas être utilisés. Ils sont uniquement destinés à gérer des tables déjà existantes pour lesquelles il serait impossible de modifier le type de données.

Transposition pour une base de données Access

• AUTOINCREMENT : INT NOT NULL IDENTITY (1, 1)
• BOOLEAN : BIT
• SMALLINT : SMALLINT
• INT : INTEGER
• BIGINT : LONG
• SINGLE : REAL
• DOUBLE : DOUBLE PRECISION
• CURRENCY : MONEY
• TEXT(2000) : MEMO
• TEXT : TEXT
• MEMO : MEMO
• DATETIME : DATETIME
• DATE : DATE
• TIME : TIME
• BINDATA : IMAGE

Transposition pour une base de données Sql Server

• AUTOINCREMENT : INT NOT NULL IDENTITY (1, 1)
• BOOLEAN : BIT
• SMALLINT : SMALLINT
• INT : INTEGER
• BIGINT : BIGINT
• SINGLE : REAL
• DOUBLE : DOUBLE PRECISION
• CURRENCY : MONEY
• TEXT(2000) : NVARCHAR(2000)
• TEXT : NVARCHAR
• MEMO : NTEXT
• DATETIME : DATETIME
• DATE : DATETIME
• TIME : DATETIME
• BINDATA : IMAGE

Transposition pour une base de données Oracle

• AUTOINCREMENT : INT NOT NULL
• BOOLEAN : NUMBER(1,0)
• SMALLINT : NUMBER(5,0)
• INT : NUMBER(10,0)
• BIGINT : NUMBER(20,0)
• SINGLE : FLOAT(24)
• DOUBLE : FLOAT(48)
• CURRENCY : NUMBER(19,1)
• TEXT(2000) : VARCHAR2(2000)
• TEXT : VARCHAR2
• MEMO : CLOB
• DATETIME : DATE
• DATE : DATE
• TIME : DATE
• BINDATA : BLOB

Dans le cas du pseudo-type AUTOINCREMENT, l’incrémentation automatique est simulée à l’aide :

• d’une séquence SEQ_NomTable,
• d’un trigger before insert TRG_BI_NomTable

Transposition pour une base de données PostgreSQL

• AUTOINCREMENT : BIGSERIAL
• BOOLEAN : NUMERIC(1)
• SMALLINT : INT2
• INT : INT4
• BIGINT : INT8
• SINGLE : FLOAT4
• DOUBLE : FLOAT8
• CURRENCY : NUMERIC(16,4)
• TEXT(2000) : VARCHAR(2000)
• TEXT : VARCHAR
• MEMO : TEXT
• DATETIME : TIMESTAMP WITHOUT TIME ZONE
• DATE : DATE
• TIME : TIME
• BINDATA : BYTEA

Transposition pour une base de données MySQL

• AUTOINCREMENT : INT AUTO_INCREMENT
• BOOLEAN : TINYINT(1)
• SMALLINT : SMALLINT
• INT : INTEGER
• BIGINT : BIGINT
• SINGLE : FLOAT
• DOUBLE : DOUBLE PRECISION
• CURRENCY : DECIMAL(16,4)
• TEXT(2000) : TEXT
• TEXT : VARCHAR
• MEMO : MEDIUMTEXT
• DATETIME : DATETIME
• DATE : DATE
• TIME : TIME
• BINDATA : MEDIUMBLOB

Ecrire du code SQL portable

Quelques sites de référence pour ne plus coder du SQL tête baissée et prendre les bons réflexes de la portabilité :

• SQLzoo.net – A Gentle Introduction to SQL (http://sqlzoo.net/) : idéal pour vérifier et tester sur plusieurs bases de données,
• Les fonctions dans SQL (http://sqlpro.developpez.com/cours/sqlaz/fonctions/) : tableau comparatif des différentes fonctions (ne pas tout croire les yeux fermés car certaines fonctions marquées non disponibles existent bel et bien),
• Tips on Writing Portable SQL (http://phplens.com/lens/adodb/tips_portable_sql.htm) : très orienté PHP et ADOdb mais intéressant,
• Building Truly Portable Database Applications in PHP (http://www.analysisandsolutions.com/presentations/portability/slides/toc.htm) : étude vraiment complète.

Structure d’une table PostgreSQL depuis Information_Schema

SELECT COLUMN_NAME,
       DATA_TYPE,
       CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION,
       NUMERIC_SCALE,
       IS_NULLABLE,
       SUBSTR(COALESCE(COLUMN_DEFAULT, '' ), 1, 8 ) = 'nextval(' AS IS_AUTOINCREMENT,
       COLUMN_DEFAULT
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = 'xxxxxxxx'
ORDER BY ORDINAL_POSITION

(Publié à l’origine sur http://www.bigbold.com/snippets/posts/show/816)

SELECT COLUMN_NAME,
       DATA_TYPE,
       CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION,
       NUMERIC_SCALE,
       IS_NULLABLE,
       COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS IS_AUTOINCREMENT,
       COLUMN_DEFAULT
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = 'xxxxxxxx'
ORDER BY ORDINAL_POSITION

(Publié à l’origine sur http://www.bigbold.com/snippets/posts/show/815)

Edit : Voir aussi « system_function_schema.fn_datadictionary« , une fonction qui renvoie le dictionnaire de données pour n’importe quelle base Sql Server.

Définir une valeur Identity sous Sql Server

SET IDENTITY_INSERT MyTable ON;
INSERT INTO MyTable
    (MyIdentityField, MyFirstField, MySecondField)
VALUES
    (12345, 'ABCDE', 'etc...');
SET IDENTITY_INSERT MyTable OFF;

(Publié à l’origine sur http://www.bigbold.com/snippets/posts/show/755)

Literal DateTime pour SqlServer

CONVERT(DATETIME, 'yyyymmdd', 112)

112 -> ISO date cf. CAST and CONVERT

Edit : une bien meilleure solution de Wild Richard :

{d 'yyyy-mm-dd'}

(Publié à l’origine sur http://www.bigbold.com/snippets/posts/show/754)