Choisir le bon type de données sous InterBase

Par Olivier Dahan (e-naxos)

Version 1.3 du 13-5-2001 - maj 10-9-2005

Introduction

Choisir le bon type de chaque champ peut sembler accessoire dans le sens où ce choix paraît ne pas en être un : c’est une chaîne, c’est un entier, etc.. tout dépend de l’analyse, on ne fait alors que faire correspondre aux prescriptions de cette dernière la réalité des types physiques offerts par la base choisie.

Cette vision des choses est à la fois réductrice et idyllique… car que ce soit pour les chaînes de caractères ou pour les numériques, il existe des variantes et des subtilités propres à chaque SGBD-R. Le problème du bon typage se pose avec encore plus de difficulté lorsqu’il s’agit de migrer des données provenant d’un SGBD différent de la cible car en plus du choix parfois délicat du type s’ajoute les différences dans les types disponibles et les problèmes de conversions.

Un exemple simple : passer des données depuis Paradox vers Interbase peut être troublant si les données originales exploitent les possibilités du type Booléen absent de Interbase.

Nous allons essayer ici de faire le point sur les différent types qu’Interbase met à notre disposition ainsi que la technique de création de Domaines.

Les Domaines

Avec les bases de données relationnelles, dans le sens où la base n’est pas passive mais pilotée par un logiciel (le serveur de données), on dispose d’une puissance accrue qui autorise des définitions de données plus sophistiquées qu’avec une base passive.

Ainsi, lorsque vous manipulez des bases sous Interbase, n’hésitez surtout pas à définir des Domaines.

L’intégrité de la base sera assurée quel que soit le frontal et la modification de règles d’affaires (business rules) sera rendu plus aisé, notamment leur propagation sur l’ensemble des tables définies.

Qu’est-ce qu’un Domaine ?

Un domaine a pour équivalence une déclaration de type dans un source Pascal. Au même titre que le développeur peut créer ses propres types en Pascal, l’administrateur d’une base Interbase peut créer ses propres types de données.

La souplesse n’est bien entendu pas celle d’un langage comme Pascal, quoi que… En effet, un domaine peut déclarer des Checks (des Contrôles), des valeurs par défaut, des tests, qui seront appliqués en temps réel sur les données. On aimerait finalement bien une telle souplesse en développement classique !

Exemple de Domaines :

Création du Domaine « Tableau1 » qui est un tableau bidimensionnel de chaînes de 31 caractères :

CREATE DOMAIN Tableau1 AS CHAR(31)  [4:5]; 

Création du Domaine « Description » qui est un Blob de type texte avec sa définition de segmentation et l’assignation d’un jeu de caractères :

CREATE DOMAIN DESCRIPTION AS BLOB SUB_TYPE TEXT SEGMENT SIZE 80 CHARACTER SET SJIS;

Création du Domaine « Nom_Utilisateur » comme une chaîne variable de 20 caractères maximum utilisant par défaut le nom de l’utilisateur connecté à la base :

CREATE DOMAIN « Nom_Utilisateur » AS VARCHAR(20) DEFAULT USER;

Création du Domaine « MonDomaine » définissant un type entier n’acceptant pas le Null et ayant la valeur zéro par défaut :

CREATE DOMAIN MonDomaine INTEGER DEFAULT 0 NOT NULL;

Etc …

Mais nous reviendrons plus tard sur la syntaxe complète de définition des domaines. Car pour définir des domaines, il faut utiliser des types de données prédéfinis, et tout le monde connaît la célèbre question « qui de la poule ou de l’œuf ? » …

Ainsi, jetons un œil sur les types mis à notre disposition par Interbase.

Les types de données

Interbase nous offre une palette intéressante de types de base pour définir nos données

Les chaînes

CHAR et VARCHAR

Ces deux types définissent un type chaîne de caractères. Le choix de l’un ou l’autre n’est que rarement clair pour le débutant (pas seulement d’ailleurs…).

La difficulté provient, il faut l’avouer, des différences d’implémentation dans les différents SGBD-R ainsi que la confusion qui peut régner dans la documentation d’une base donnée.

Si on se réfère simplement à celle de Interbase, on pourrait conclure (trop rapidement) que le type CHAR stocke une chaîne complétée d’espaces remplissant toute la taille définie et que le VARCHAR ne stocke que le nombre de caractères saisis.

En fait la réalité est plus nuancée car VARCHAR réclamera parfois plus d’espace que CHAR !

Pourquoi ?

 … car pour gérer le VARCHAR Interbase est obligé d’ajouter 2 octets définissant la longueur du texte.

Si les services de VARCHAR semblent remporter l’avantage le plus souvent, le type CHAR peut être finalement plus économe pour des petites chaînes.

On peut aussi être surpris lorsqu’on lit dans la documentation qu’Interbase complète avec des espaces les chaînes CHAR et VARCHAR avant les stocker pour les amener à leur taille maxi réelle. Mais ce que ne dit pas le manuel c’est que ces espaces de fin de chaîne sont compressés (avec un compteur), et donc, de fait, vos bases ne sont pas remplies d’espaces…

L’une des différences essentielles pour l’application est que VARCHAR retourne les caractères de la chaîne écrite dans le champ alors que CHAR retourne systématiquement la chaîne complétée par des espaces. Comme cela n’est pas vraiment pratique, la plupart des développeurs préfèrent les VARCHAR. Mais ce choix doit être pondéré par les aspects évoqués plus haut.

Interbase supporte les définitions suivantes :

Les types NATIONAL ne sont guères utilisés il faut l’avouer… En effet, ce qui est « national » pour les américains ne l’est pas forcément pour le reste du monde, il est donc conseillé de déclarer le jeu de caractères à utiliser au niveau de la base, voir des champs de type caractère.

Interbase étant un produit bien pensé, il est possible de définir un jeu de caractères global pour toute une base tout en ayant la possibilité de définir un jeu différent ponctuellement pour un champ en particulier. Cette possibilité permet de gérer des langues différentes dans une base, par exemple une base de traduction ou des articles traduits, le tout sans perdre les spécificités calligraphiques de chaque langue. Il faut ajouter qu’Interbase offre des jeux de caractères sur 16 bit, plus gourmands en espace disque mais permettant de gérer une multitude de langues dans la même base.

La définition d’un jeu de caractères détermine à la fois les caractères qui sont utilisables dans les CHAR, VARCHAR et les Blobs de type texte, et l’ordonnancement à utiliser pour les tris.

(à noter que les Blobs ne sont pas concernés par cette dernière mesure).

Exemple de définition d’un jeu de caractères au niveau d’un champ :

CREATE TABLE TEST

(PRENOM VARCHAR(10) CHARACTER SET ISO8859_1,

. . .);

Exemple de définition d’un jeu de caractères par défaut au niveau de la base de données :

CREATE DATABASE "europe.gdb" DEFAULT CHARACTER SET ISO8859_1;

Les ordonnancements de tri (Collation order) sont très utiles pour les jeux de caractères pouvant supporter plusieurs tris différents, c’est le cas des jeux ISO8859_1 et DOS437 par exemple. Dans ce cas, en plus du jeu de caractères il peut être judicieux de contrôler la séquence d’ordonnancement des caractères pour les tris afin d’obtenir des listes correspondant aux souhaits des utilisateurs.

La définition pour un champ d’une séquence particulière se fait de la façon suivante (exemple sur un ALTER qui ajoute un champ, ce qui pourrait se faire sur le CREATE bien entendu) :

ALTER TABLE "FR_CA_EMP"

ADD ADDRESS VARCHAR(40) CHARACTER SET ISO8859_1          NOT NULL

COLLATE FR_CA;

Il faut noter que Interbase supporte aussi la spécification ponctuelle d’une séquence dans une opération de comparaison afin de contrôler quelle séquence sera utilisée. Exemple dans une clause WHERE qu’on suppose extraite d’un SELECT :

… WHERE NOMFAMILLE COLLATE FR_CA = :NomFamille_A_Chercher; 

Cette syntaxe peut surprendre mais il faut comprendre que dans un jeu de caractères donné (un "charset"), il peut y avoir plusieurs façons de comparer les caractères, notamment en ce qui concerne les équivalences entres majuscules et minuscules, nationales ou non (exemple: É et é et e, ou bien savoir si M et m sont identiques ou non, triés comme ..M m N n ... ou bien ... M N .. Z puis ... m n .. z, etc).

La syntaxe est aussi acceptée dans un ORDER BY :

… ORDER BY NOMFAMILLE COLLATE FR_CA,PRENOM COLLATE FR_CA;

Idem dans un GROUP BY :

… GROUP BY NOMFAMILLE COLLATE FR_CA, PRENOM COLLATE FR_CA;

Attention : le choix du jeu de caractères supporté par un champ (ou par tous les champs) conditionne le stockage maximum qui pourrait être fait dans ce champs.

Si le jeu de caractère utilise 1 octet par caractère, la chaîne la plus longue stockagble fait 32 767 octets. Mais si le jeu utilise 2, voire 3 octets, la plus grande chaîne qu’on puisse stocker sera de 1/2 ou 1/3 de cette taille seulement … C’est pour cela que dans le tableau des principaux jeux de caractères présenté en annexe, vous trouverez une indication sur la taille nécessaire à chaque caractère.

BLOB texte

Bien qu’étant un stockage binaire étendu (Blob = Binary Large OBject) pouvant donc contenir n’importe quoi, la plupart des SGBD-R modernes propose une extension spéciale pour stocker du texte. Interbase fait bien entendu partie de ceux-ci.

En quoi cela est-il nécessaire si les Blobs peuvent déjà contenir ce qu’on veut ?

Justement … les Blobs ne sont qu’une commodité, ils ne sont pas sensés être traités par le moteur. Certains affirment qu’ils sont même contraire à l’esprit de normalisation puisqu’il viole 1NF en n’étant pas atomique, mais cela est une autre affaire !

Donc, pour permettre notamment de faire des recherches sur le texte contenu dans un Blob, il faut que le moteur offre un moyen de typer le Blob, ce qui est un peu antinomique, certes… Une fois le fourre-tout typé en texte, Interbase autorise les recherches sur son contenu ce qui est particulièrement intéressant.

Pour déclarer un tel Blob, vous utiliserez la syntaxe suivante :

CREATE TABLE TABLE2

 FicheID INTEGER, MonMemo BLOB SUB_TYPE 1);

C’est le sous-type 1 qui permet d’indiquer le type texte. 

Les Blobs sont stockés par segmentation, il est donc possible et même souhaitable de définir la taille des segments pour optimiser l’espace occupé par la base. Nous abordons ce point dans la section dédiée aux Blobs binaires.

Les Numériques

 Le travail sur les numériques est souvent essentiel, notamment lorsqu’il concerne des données en devises ou des informations à caractère scientifique.

Dans le premier cas c’est l’exactitude des données et les arrondis qui posent le plus souvent problème. Dans le second, en plus de tout cela, c’est le stockage de la partie significative qui est problématique. En effet, en physique, à la non instar des mathématiques, 2 ou 2.0 ou 2.00 ne sont pas du tout égaux (la raison est que 2 sous-entend une précision à l’unité, 2.0 une précision au dixième, etc. Le zéro est donc significatif autant qu’un autre nombre).

Autant dire tout de suite qu’en dehors d’une représentation chaîne gérée par application, il n’y a pas de solution pour ce problème des nombres en physique.

On  utilise principalement les ordinateurs, depuis leur origine, pour leur capacité à calculer. Paradoxalement, stocker des nombres et calculer est la chose la moins bien faite par les ordinateurs !

En effet, le stockage d’un numérique s’effectue, représentation binaire oblige, par puissance de deux pour la partie entière et par inverse de puissances de deux pour la partie décimale.

Si pour la partie entière cela ne pose pas de problème particulier, pour la partie décimale l’ordinateur ne peut représenter qu’une approximation du nombre saisi puisqu’il est représenté comme somme de ½ + ¼ + 1/8 etc … Et c’est ainsi qu’on peut voir des situations où l’utilisateur saisi 2,59 alors qu’un affichage de la zone donnera 2,5899999999999999 par exemple…

Le moyen de s’en convaincre est le petit code suivant :

Var D : double;

D := 1/3;
if (D+D+D)=1.0 then ShowMessage('Égalité') else ShowMessage('Pas égal');

La plupart des développeurs s’attendent à voir s’afficher « Égalité » mais ce n’est pas ce qui se passe…

Car la représentation binaire de 1/3 ne tient pas sur les 8 octets d’un Double. La seule solution consiste alors à stocker les 16 premiers digits soit 0.3333333333333333 , ce qui est proche de 1/3 mais qui ne peut pas être égal à 1/3.

Ce n’est pas un problème qui vient de Intel, de Microsoft ou de Borland, cela est inhérent à notre système mathématique. En gros ce problème est plus scientifique que technique.

Ce problème est présent dans les langages de développement mais aussi dans les bases de données qui fonctionnent sur le même modèle mathématique.

Cela est particulièrement gênant pour les données comptables dans une base.

Les FLOAT et les nombre à double précision fournissent assez de positions décimales pour stocker sans problème et avec exactitude les n décimales utiles pour une donnée en devise (si on fait abstraction des décimales surnuméraires par un affichage arrondi). Par contre, le problème devient insoluble s’il y a des calculs et des comparaisons sur ces nombres.

Bien entendu cela est .. monaie courante dans une application de gestion !

La première solution qui vient à l’esprit, si les décimales posent problème, c’est de les éliminer et d’utiliser des entiers en travaillant en Cents ou Centimes.

Le problème est que Interbase ne propose que des entiers 32 bit en dialecte 1 (celui qui fonctionne avec toutes les versions existantes) et qu’un tel entier est limité à 2 147 483 648, soit, si on travaille en centimes à 21 474 836.48 FF, ce qui est assez peu. Que dire si vous devez faire des calculs en Lire Italienne ou d’autres monnaies du même type.

Le mieux serait d’utiliser le type entier 64 bit mais cela n’existe qu’en dialecte 3 (Interbase 6.x aujourd’hui), ce qui limite grandement son utilisation.

Actuellement, le choix de la majorité des développeurs se porte sur le type NUMERIC et dans une définition étendue en 15.4, soit 15 digits dont 4 décimales, ce qui permet de stocker des nombres jusqu’à 99 999 999 999.9999 (99 milliards).

Avec l’Euro il conviendra dans certains cas de prévoir 1 ou 2 décimales de plus, ce qui, du coup risque de devenir un peu court (15 chiffres significatifs étant le maximum).

En marge, un lien sur les règles de calculs liées à l'euro : http://www.euro.gouv.fr/consommateurs/arrondi.htm

Il ne faut pas oublier que le BDE à une fonction BCD (Binary-Coded Decimal) qui, utilisée conjointement au type NUMERIC(15,4) donne d’excellents résultats.

Il existe d’autres solutions si on ne souhaite pas utiliser le BDE :

-          IB Objects de Jason Wharton qui offre des types étendus

-          IB Express de Delphi qui avec IB 6.x offre un support INTEGER 64 bit en dialecte 3.

Les syntaxes acceptées par Interbase en dialecte 1 pour les numériques sont les suivantes :

Interbase 6 se conforme au standard ANSI 92 et offre, en dialecte 3 une précision allant jusqu’à 18 digits au lieu de 15 pour les numériques. A partir de la version 6, les champs NUMERIC et DECIMAL sont stockés comme des valeurs exactes, sur 16, 32 ou 64 bits selon la précision déclarée. De plus, un type entier 64 bits est disponible (INT64), autant de raisons de passer à la version 6 de Interbase !

Les Dates

Interbase offre dans son dialecte 1 un seul type date qui stocke aussi bien la date que l’heure. Interbase 6.x offre une séparation date / heure apporte une meilleure représentation des données en accord avec la sémantique de l’application, ainsi qu’un respect plus fort de la 1NF (voir mon article sur la normalisation des bases de données que vous trouverez sur mon site).

La définition d’une date se fait en dialecte 1 par DATE, les valeurs sont stockées sur 64 bits, et son étendue lui permet de stocker les dates entre le 1^er janvier 100 et le 29 février 32678. On peut d’ors et déjà prévoir le bug du 1^er mars 32 679 … pour ceux qui seront là !!!

Interbase 6 pose le nouveau mot clé TIMESTAMP qui, en dialecte 1, est l’équivalent de DATE pour les versions d’Interbase précédentes.

En dialecte 3 par contre, TIMESTAMP joue le rôle de l’ancien type DATE en stockant date et heure dans le même champ.

Attention, le mot clé DATE définit en dialecte 3 une champ ne contenant qu’une date, sans heure.

Le nouveau type TIME définit une heure sans date.

Les Binaires

Même si à l’origine les SGBD-R ne devaient stocker que des champs atomiques ayant un sens constant (définition même de 1NF), la plupart des éditeurs on finit par ajouter un type de colonne fourre-tout : les Binary Large OBjects, Blob’s ou Gros Objets Binaires.

Gros, car leur taille n’est pas fixée et qu’elle peut être importante.

Binaire, car une colonne de type BLOB n’est vu par la base que comme un tas d’octets indifférencié.

Concernant les BLOB’s, Interbase propose plusieurs distinctions subtiles et intéressantes.

On pourrait se demander pourquoi typer les binaires, ce qui paraît paradoxal. En fait, il existe au moins deux utilisations courantes des BLOB’s qui le justifient : soit on utilise leur caractéristique LARGE (le "L" de Blob) pour stocker du texte de taille non fixée, soit on utilise leur caractère BINARY (le premier "B" de Blob) pour stocker des images.

De fait, Interbase offre un sous-types de BLOB spécialisé pour les textes non formatés et offre des types utilisateurs utilisés notamment pour les images bitmap.

Comme il faut bien conserver un champ binaire large non typé, il existe aussi un type BLOB de base. Les types BLOB utilisateurs sans signification pour Interbase permettent, eux, de mieux typer les divers BLOB’s dans une application (par exemple des paquets de données compressées en provenance d’instruments de mesures différents à lire et analyser de façon différente par l’application).

Voici les différents types binaires connus (certains sont réservés pour Interbase)  :

Exemples de déclarations

CREATE TABLE EMPLOYEES (NAME CHAR(20), 

Tâchez de mettre des tailles de segment en relation avec les données réellement stockées dans le BLOB, cela aura pour effet d’optimiser la gestion interne des BLOBs.

Il semblerait malgré tout que la majorité des auteurs avisés (comme Ann Harrison ou d’autres) n’apportent aucun intérêt à la segmentation qui est présentée comme une réminiscence du passé (le segment a une taille de 80 caractères par défaut ce qui correspond à la largeur en colonne d’une console informatique en mode texte). A vous de voir, tests à l’appui… et dites nous ce que vous pensez !

Les Tableaux

Interbase autorise la définition de tableaux de valeurs. Cela n’est pas très « normal » .. au sens des Formes Normales, mais il faut avouer que cela peut être fort utile et éviter dans certains cas précis de définir des relations maître / détails ou des répétitions de colonnes disgracieuses (et propres, du coup, à faire hurler un amoureux de la normalisation ! ).

Les tableaux ne sont utilisables sous Delphi et Cbuilder de façon native que depuis peu de temps, de fait ce type a été délaissé par les développeurs.

En toute honnêteté il n’y a pas de domaine d’application classique dans lequel la définition de tableaux s’avère indispensable, de plus ce type n’est pas portable et pourra poser des problèmes en cas de changement de base cible. Ne souhaitant pas mettre l’accent sur ce type, je vous invite à prendre connaissance de la syntaxe dans le guide du DDL de Interbase.

Sachez que le type existe, qu’il peut être utile dans certains cas, mais évitez d’en faire usage…

Les domaines, syntaxe

Comment promis, voici la syntaxe précise de définition d’un domaine :

CREATE DOMAIN domain [AS] <datatype>

[DEFAULT { literal | NULL | USER}]

[NOT NULL] [CHECK ( <dom_search_condition>)]

[COLLATE collation];

ALTER DOMAIN permet de modifier une définition sans faire de suppression / re-création, opération impossible si des colonnes étaient déjà définies.

Voici un exemple de domaine complexe :

CREATE DOMAIN CUSTNO 

      AS INTEGER

        DEFAULT 9999

        CHECK (VALUE > 1000);

Ici, le domaine créé s’appelle CUSTNO et il définit un entier dont la valeur par défaut est 9999 (DEFAULT), valeur qui ne peut pas être supérieure à 1000 (CHECK).

Cette définition force l’utilisateur à saisir une valeur correcte puisque le champ est initialisé avec une valeur se trouvant hors du domaine. Cette « astuce » est parfois utilisée lorsque les règles de validation doivent se trouver dans la base de données plutôt que dans l’application.

Bien que le champ accepte la valeur NULL, on ne peut pas faire une insertion sans renseigner la zone, c’est une façon de créer une saisie obligatoire sur un champ NOT NULL …

En guise de conclusion

Le sujet ne prête pas à conclure à vrai dire.. l’exploration, pour intéressante qu’elle soit, des types disponibles sous Interbase n’est malgré tout pas un champ infini propre à exalter nos imaginations fertiles de tritureurs d’octets… Mais, une bonne base de données bien construite cela commence par une définition des types de champs en accord avec l’analyse fonctionnelle.

Faire propre, pérenne, solide, c’est plus un travail de maçon que d’artiste, certes, mais c’est tout aussi noble !

Annexe : Les jeu de caractères les plus usuels

Vous avez compris l’intérêt des jeux de caractères et des ordres de séquence ? Alors voici une petite grille pour vous repérez ! J’ai regroupé ici les principaux jeux que vous êtes sensés utiliser. Interbase en définit d’autres, n’oubliez pas de consulter la doc …

Olivier Dahan.
Développement et Formation Win32/.NET, Delphi/C#
e-naxos