Choisir le bon type de données sous InterBase

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 de 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ère 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, voire 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 bits, plus gourmands en espace disque, mais permettant de gérer une multitude de langues dans la même base.

La définition, 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 entre 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 champ.

Si le jeu de caractères utilise 1 octet par caractère, la chaîne la plus longue stockable 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 proposent 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 censés être traités par le moteur. Certains affirment qu'ils sont même contraires à l'esprit de normalisation puisqu'ils violent 1NF en n'étant pas atomiques, 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.

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, à l'inverse 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 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 nombres à 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 monnaie 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 bits 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 bits, 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 bits 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. À 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 !

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.

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 1er janvier 100 et le 29 février 32678. On peut d'ores et déjà prévoir le bogue du 1er 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 un champ ne contenant qu'une date, sans heure.
Le nouveau type TIME définit une heure sans date.

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 ont fini 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 vue 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-type 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 mesure 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

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). À vous de voir, tests à l'appui… et dites-nous ce que vous pensez !

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…

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/recré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…