Difference between revisions of "FR/Documentation/HSQLDB Guide/ch02"

From Apache OpenOffice Wiki
Jump to: navigation, search
(New page: Test de l'oxt SUN WIKI PUBLISHER 1.0)
 
Line 1: Line 1:
Test de l'oxt SUN WIKI PUBLISHER 1.0
+
Test de l'extension SUN WIKI PUBLISHER 1.0 oxt
 +
 
 +
Menu Fichier > Exporter > Format de fichier
 +
 
 +
== HTML: <META NAME="CHANGEDBY" CONTENT="Dominique Pautrel">HTML: <META NAME="CHANGEDBY" CONTENT="Dominique Pautrel">HTML: <META NAME="CHANGEDBY" CONTENT="Dominique Pautrel">HTML: <META NAME="CHANGEDBY" CONTENT="Dominique Pautrel">Chapitre 2. ProblÚmes liés au SQL ==
 +
(Le document original, à valeur normative, est à cette page : [http://hsqldb.org/doc/guide/ch02.html http://hsqldb.org/doc/guide/ch02.html]&nbsp;)
 +
 
 +
=== Fred Toussi ===
 +
Groupe de Développement HSQLDB
 +
 
 +
<tt><nowiki><</nowiki>[mailto:ft@cluedup.com ft@cluedup.com]></tt>
 +
 
 +
Copyright 2002-2005 Fred Toussi. Il est accordé la permission de distribuer ce document sans aucune altération et sous les termes de la licence HSQLDB. Une permission additionnelle est accordée au Groupe de Développement HSQLDB de distribuer ce document avec ou sans altérations et sous les termes de la licence HSQLDB.
 +
 
 +
Note de traduction : Ici, il y a néanmoins l'altération de la traduction. En cas de doute, se référer au document original, à valeur normative, à l'adresse : [http://hsqldb.org/web/hsqlDocsFrame.html http://hsqldb.org/web/hsqlDocsFrame.html]&nbsp;
 +
 
 +
$Date: 2005/07/01 17:06:32 $
 +
 
 +
'''Table des MatiÚres (Table of Contents)'''
 +
 
 +
[#N102AE But de ce document]
 +
 
 +
[#N102B3 Support du standard SQL]
 +
 
 +
[#N102D3 Contraintes et Index]
 +
 
 +
[#N102D6 Contraintes de clés primaires]
 +
 
 +
[#N102DF Contrainte unique]
 +
 
 +
[#N1030E Index uniques]
 +
 
 +
[#N10318 Clés externes]
 +
 
 +
[#N1033B Index et vitesse d’exécution des requêtes]
 +
 
 +
[#N10372 Condition Where ou Jointure ?]
 +
 
 +
[#N103A6 Sous-requêtes et Jointures]
 +
 
 +
[#N103BF Types de champs et opérations arithmétiques]
 +
 
 +
[#N103D6 Les nombres entiers]
 +
 
 +
[#N10426 Autres types numériques]
 +
 
 +
[#N1045F Les types BIT et BOOLEAN]
 +
 
 +
[#N1047A Stockage et manipulation des objets Java]
 +
 
 +
[#N10499 Caractéristiques de taille, précision et échelle]
 +
 
 +
[#N104AE Séquences et Identity]
 +
 
 +
[#N104B3 Champs Identifiants auto-incrémentés]
 +
 
 +
[#N104E1 Séquences]
 +
 
 +
[#N104FC ProblÚmes avec les transactions]
 +
 
 +
[#N10521 Nouvelles fonctionnalités et changements]
 +
 
 +
== But de ce document ==
 +
Beaucoup de questions fréquemment posées dans les Forums et Mailing-Lists trouvent leur réponse dans ce guide. Si vous voulez utiliser HSQLDB avec votre application, vous devez lire ce guide.
 +
 
 +
== Support du standard SQL ==
 +
HSQLDB 1.8.0 supporte le langage SQL défini par les standards SQL 92, 99 et 2003. Cela signifie que quand une caractéristique de ce standard est supportée, ex.: left outer join, la syntaxe est celle qui est spécifiée par le texte standard. Nombre de caractéristiques du SQL 92 et 99 sont supportées jusqu’à un niveau avancé, il y a un support pour la plupart des mots de la SQL 2003 Foundation et pour plusieurs fonctionnalités optionnelles de ce standard. Toutefois, certaines fonctionnalités ne sont pas supportées, aussi nous ne prétendons pas assurer un support pour la totalité des caractéristiques des standards.
 +
 
 +
Le chapitre Syntaxe SQL de ce guide [#sqlsyntax-chapter SQL Syntax] liste tous les mots clés et la syntaxe utilisée. Lors de l’écriture ou de la conversion de la grammaire SQL DDL (Data Definition Language – Langage de définition des données) et DML (Data Manipulation Language – Langage de manipulation des données) pour HSQLDB, vous devez consulter la syntaxe supportée et modifier les déclarations (la grammaire) en conséquence.
 +
 
 +
Certains mots sont réservés par le standard et ne peuvent pas être utilisés comme des noms de tables ou de colonnes. Par exemple, le mot POSITION est une fonction définie par les standards d’un rÃŽle similaire à String.indexOf() en langage Java, et est donc réservé. A l’heure actuelle, HSQLDB ne vous empêchera pas d’utiliser un mot réservé si il ne le gÚre pas ou ne le connait pas. Par exemple BEGIN est un mot réservé qui n’est pas aujourd’hui pris en charge par HSQLDB et est donc permis comme nom de table ou de colonne. Vous devez éviter l’usage de tels mots, comme les versions futures de HSQLDB seront amenées à les prendre en charge, rejetants alors vos définitions de tables et vos requêtes. La liste complête des mots réservés du SQL est dans la source de la classe <tt>org.hsqldb.Token</tt>
 +
 
 +
HSQLDB prend en charge également des mots-clés et expressions qui ne font pas partie du standard SQL. Des expressions telles que <tt>SELECT TOP 5 FROM ..</tt>, <tt>SELECT LIMIT 0 10 FROM ...</tt> ou <tt>DROP TABLE mytable IF EXISTS</tt> sont parmi quelques exemples.
 +
 
 +
Tous les mots-clés peuvent être utilisés pour les objets de base de données s’il sont encadrés par des guillemets doubles.
 +
 
 +
== Contraintes et Index ==
 +
=== Contraintes de clefs primaires ===
 +
Avant la version 1.7.0, une instruction <tt><nowiki>CONSTRAINT <name> PRIMARY KEY</nowiki></tt> était traduite par le moteur en un index unique, et de plus, une colonne cachée était ajoutée à la table avec un autre index unique. Depuis la version 1.7.0 les deux contraintes de clef primaire simple et multicolonnes sont gérées. Elles sont autorisées par un index unique sur la ou les colonne(s) de clef primaire. Aucune autre colonne cachée n’est nécessaire pour ces index.
 +
 
 +
=== Contrainte unique ===
 +
Conformément au standard SQL, une contrainte unique sur une colonne simple signifie que deux valeurs ne peuvent être égales à moins que l’une d’entre elles soit NULL. Ce qui signifie que vous pouvez avoir une ou plusieurs lignes ou la valeur de cette colonne est NULL.
 +
 
 +
Une contrainte unique sur des colonnes multiples (c1, c2, c3, ...) signifie qu’il ne peut pas y avoir deux jeux de valeurs identiques sauf si l’une des valeurs est NULL. Chaque colonne peut contenir des valeurs répétées. L’exemple suivant satisfait à une contrainte UNIQUE sur les deux colonnes:
 +
 
 +
'''Exemple&nbsp;2.1.&nbsp;Valeurs de colonnes satisfaisant à une contrainte UNIQUE sur deux colonnes'''
 +
 
 +
 
 +
{| class="prettytable"
 +
| 1,
 +
| 2
 +
 
 +
|-
 +
| 2,
 +
| 1
 +
 
 +
|-
 +
| 2,
 +
| 2
 +
 
 +
|-
 +
| NULL,
 +
| 1
 +
 
 +
|-
 +
| NULL,
 +
| 1
 +
 
 +
|-
 +
| 1,
 +
| NULL
 +
 
 +
|-
 +
| NULL,
 +
| NULL
 +
 
 +
|-
 +
| NULL,
 +
| NULL
 +
 
 +
|}
 +
Depuis la version 1.7.2 le comportement des contraintes et index UNIQUE avec le respect de la valeur NULL a changé pour se conformer aux standards SQL. Une ligne dans laquelle une des valeurs pour n’importe quelle colonne de la contrainte UNIQUE est NULL peut toujours être ajoutée à la table. Donc plusieurs lignes peuvent contenir les mêmes valeurs pour les colonnes de la contrainte UNIQUE si l’une des valeurs est NULL.
 +
 
 +
=== Index uniques ===
 +
Dans la version 1.8.0, les index UNIQUE définis par l'utilisateur peuvent encore être déclarés mais ils sont obsolÚtes. Au lieu de l'index, vous devez utiliser une contrainte UNIQUE.
 +
 
 +
<nowiki>CONSTRAINT <Nom de l'index> UNIQUE créé toujours en interne un index unique sur les champs, comme dans les versions précédentes. Il a donc exactement le même effet que la déclaration index UNIQUE, maintenant obsolÚte.</nowiki>
 +
 
 +
=== Clés externes ===
 +
Depuis la version 1.7.0, HSQLDB a implémenté les clés externes à colonne simple et multiples. Une clé externe peut aussi être spécifiée en référence à une table cible sans avoir à nommer la ou les colonnes cibles. Dans ce cas la ou les colonnes de clé primaire de la table cible sont utilisées comme les colonnes référencées. Les types des champs de chaque paire Colonne de référence / Colonne référencée doivent être identiques. Lors de la déclaration d'une clé externe, une contrainte unique (ou clé primaire) doit exister dans les colonnes référencées de la table contenant la clé primaire. Un index non unique est créé automatiquement sur les colonnes référencées. Par exemple:
 +
 
 +
CREATE TABLE enfant(c1 INTEGER, c2 VARCHAR, FOREIGN KEY (c1, c2) REFERENCES parent(p1, p2));
 +
 
 +
Il doit y avoir une contrainte UNIQUE sur les colonnes (p1, p2) dans la table nommée “parent”. Un index non unique est automatiquement créé sur les colonnes (c1, c2) dans la table nommée “enfant”. Les colonnes p1 et c1 doivent être du même type (INTEGER). Les colonnes p2 et c2 doivent également être du même type (VARCHAR).
 +
 
 +
=== Index et vitesse d’exécution des requêtes ===
 +
HSQLDB ne fait pas usage d’index pour améliorer le tri dans l’affichage des requêtes. Mais les index jouent un rÃŽle crucial dans l’amélioration de la vitesse d’exécution des requêtes. Si aucun index n’est utilisé dans une requête portant sur une seule table, comme une requête suppression (DELETE), alors toutes les lignes de la table devront être examinées. Avec un index sur un des champ figurant dans la clause WHERE, il est souvent possible de partir directement de la premiÚre ligne concernée, et ainsi réduire le nombre de lignes examinées.
 +
 
 +
Les index sont encore plus importants dans le cas de jointures entre plusieurs tables. <tt>SELECT ... FROM t1 JOIN t2 ON t1.c1 = t2.c2 </tt>est exécutée en prenant les lignes de t1 une par une et en trouvant une ligne correspondante dans t2. S’il n’y a pas d’index dans t2.c2, alors pour chaque ligne de t1, toutes les lignes de t2 devront être vérifiées. Alors qu’avec un index, une ligne correspondante peut être trouvée en une fraction de seconde. Et si la requête comporte une condition sur t1, i.e., <tt>SELECT ... FROM t1 JOIN t2 ON t1.c1 = t2.c2 WHERE t1.c3 = 4</tt> alors un index sur t1.c3 supprimera le besoin de vérifier toutes les lignes de t1 une à une, et réduira le temps d’exécution à moins d’une milliseconde par enregistrement. En effet, si t1 et t2 contiennent chacune 10&nbsp;000 lignes, la requête sans index devra vérifier 100&nbsp;000&nbsp;000 de combinaisons de lignes. Avec un index sur t2.c2, cela se retrouve réduit à 10&nbsp;000 vérifications de lignes et recherches dans l’index. Avec l’index additionnel sur t2.c2, en moyenne 4 lignes seulement sont vérifiées avant d’obtenir la premiÚre ligne de résultat.
 +
 
 +
Les index sont créés automatiquement pour les clés primaires et les champs sans doublons. Pour en définir d’autres utilisez la commande CREATE INDEX.
 +
 
 +
Notez que dans HSQLDB un index unique sur plusieurs champs peut être utilisé en interne comme un index avec doublons sur la premiÚre colonne de la liste. Par exemple: <tt>CONSTRAINT nom1 UNIQUE (c1, c2, c3); </tt>signifie qu’il y a l’équivalent de <tt>CREATE INDEX nom2 ON unetable(c1);</tt>. Aussi vous n’avez pas besoin de spécifier un autre index si vous avez besoin de celui du premier champ de la liste.
 +
 
 +
Dans la version 1.8.0, un index multicolonnes accélerera les requêtes qui contiennent des jointures ou des valeurs dans TOUTES les colonnes. Vous n’avez pas besoin de déclarer d’autres index individuels sur ces champs à moins que vous n’utilisiez des requêtes qui effectuent des recherches seulement sur une partie de ces champs. Par exemple, les lignes d’une table qui a une clé primaire ou une contrainte UNIQUE sur trois champs, ou même un simple index sur ces champs, peuvent être trouvées efficacement quand les valeurs pour ces trois champs sont spécifiées dans la clause WHERE. Par exemple, <tt>SELECT ... FROM t1 WHERE t1.c1 = 4 AND t1.c2 = 6 AND t1.c3 = 8 </tt>utilisera un index sur <tt>t1(c1,c2,c3)</tt> s’il existe.
 +
 
 +
Suite aux améliorations des index multi-clés, l’ordre de déclaration des champs dans l’index ou la contrainte a moins d’effet qu’avant sur la vitesse des recherches. Cependant si le champ qui contient le plus de valeurs différentes est en premiÚre place, la recherche sera sensiblement plus rapide
 +
 
 +
Un index multichamps n’accélÚrera pas seulement les requêtes sur le second ou le troisiÚme champ. Le premier champ doit être spécifié dans les conditions JOIN&nbsp;..&nbsp;ON ou WHERE.
 +
 
 +
La vitesse d’exécution des requêtes dépend beaucoup de l’ordre des tables dans les clauses JOIN&nbsp;..&nbsp;ON ou FROM. Par exemple la deuxiÚme requête ci-dessous sera plus rapide avec de grandes tables (par le fait qu’il y ait un index sur <tt>TB.COL3</tt>). La raison est que TB.COL3 peut être évaluée trÚs rapidement si la recherche s’applique sur la premiÚre table (et s’il y a un index sur TB.COL3):
 +
 
 +
....(TB est une trÚs grande table avec seulement quelques lignes ou TB.COL3 = 4)
 +
 
 +
    SELECT * FROM TA JOIN TB ON TA.COL1 = TB.COL2 AND TB.COL3 = 4;
 +
 
 +
    SELECT * FROM TB JOIN TA ON TA.COL1 = TB.COL2 AND TB.COL3 = 4;
 +
 
 +
La rÚgle générale est de mettre d’abord la table dont un des champs a des conditions restrictives.
 +
 
 +
La version 1.7.3 fournit des index automatiques à la volée pour les vues et sous-sélections utilisées dans une requête. Un index est ajouté à une vue quand elle est jointe à une table ou à une autre vue.
 +
 
 +
=== Condition Where ou jointure ? ===
 +
Utiliser des conditions WHERE pour joindre des tables revient à réduire la vitesse d’exécution. Par exemple la requête suivante sera généralement lente, même indexée:
 +
 
 +
    SELECT ... FROM TA, TB, TC WHERE TC.COL3 = TA.COL1 AND TC.COL3=TB.COL2 AND TC.COL4 = 1
 +
 
 +
La requête implique que <tt>TA.COL1 = TB.COL2</tt> sans spécifier explicitement cette condition. Si TA et TB contiennent chacune 100 lignes, 10&nbsp;000 lignes seront jointes à TC avant d'appliquer les conditions de colonnes, et ceci bien qu'il y ait des index sur les colonnes jointes. Avec le mot-clé JOIN, la condition <tt>TA.COL1 = TB.COL2</tt> doit être explicite. Elle restreindra la combinaison de lignes de TA et TB avant qu'elle ne soit jointe à TC, aboutissant plus rapidement sur de grandes tables.
 +
 
 +
    SELECT ... FROM TA JOIN TB ON TA.COL1 = TB.COL2 JOIN TC ON TB.COL2 = TC.COL3 WHERE TC.COL4 = 1
 +
 
 +
La requête pourrait être grandement accélérée si l'ordre des tables jointes était changé, pour que <tt>TC.COL1 = 1</tt> soit appliqué en premier et que moins de lignes soient jointes ensemble:
 +
 
 +
    SELECT ... FROM TC JOIN TB ON TC.COL3 = TB.COL2 JOIN TA ON TC.COL3 = TA.COL1 WHERE TC.COL4 = 1
 +
 
 +
Dans l'exemple précédent le moteur applique automatiquement <tt>TC.COL4 = 1</tt> à TC et joint seulement aux autres tables le jeu de lignes qui satisfait à cette condition. Les index sur <tt>TC.COL4</tt>, <tt>TB.COL2</tt> et <tt>TA.COL1</tt> seront utilisés s'ils existent et accélÚreront encore la requête.
 +
 
 +
=== Sous-requêtes et Jointures ===
 +
Utiliser les jointures et définir l'ordre des tables pour un maximum de performances s'applique à tous les domaines. Par exemple, la deuxiÚme requête ci dessous doit être plus rapide s'il y a des index sur TA.COL1 et TB.COL3
 +
 
 +
'''Exemple&nbsp;2.2.&nbsp;Comparaison de requêtes'''
 +
 
 +
    SELECT ... FROM TA WHERE TA.COL1 = (SELECT MAX(TB.COL2) FROM TB WHERE TB.COL3 = 4)
 +
 
 +
    SELECT ... FROM (SELECT MAX(TB.COL2) C1 FROM TB WHERE TB.COL3 = 4) T2 JOIN TA ON TA.COL1 = T2.C1
 +
 
 +
La deuxiÚme requête transforme <tt>MAX(TB.COL2)</tt> en une table à une seule ligne avant de la joindre à TA. Avec un index sur TA.COL1, c'est trÚs rapide. La premiÚre requête, quant à elle, testera chaque ligne et évaluera <tt>MAX(TB.COL2) </tt>encore et encore.
 +
 
 +
== Types de champs et opérations arithmétiques ==
 +
Tous les types de colonnes de table pris en charge par HSQLDB peuvent être indexés et possÚdent des fonctionnalités comparables. Les types de champ peuvent être explicitement convertis en utilisant la fonction de librairie CONVERT(), mais la plupart du temps ils sont convertis automatiquement. Il est recommandé de ne pas utiliser d'index sur les types LONGVARBINARY, LONGVARCHAR et OTHER, puisque ces index ne seront sans doute plus implémentés dans les versions futures.
 +
 
 +
Les versions précédentes de HSQLDB ne prenaient que peu en charge les opérations arithmétiques. Par exemple, il n'était pas possible d'insérer <tt>10/2,5</tt> dans aucun champ DOUBLE ou DECIMAL. Depuis la version 1.7.0, toutes les opérations sont possibles avec les rÚgles suivantes:
 +
 
 +
TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC et DECIMAL (sans point décimal) sont de type entier et correspondent avec byte, short, int, long et BigDecimal dans Java. Le type SQL détermine les valeurs minimum et maximum qui peuvent être supportées pour chaque type de champ. Par exemple la valeur range (amplitude) pour TINYINT permet de -128 à +127, bien que le type de champ utilisé dans Java pour le manipuler soit <tt>java.lang.Integer</tt>.
 +
 
 +
REAL, FLOAT, DOUBLE sont tous interpretés comme double dans Java.
 +
 
 +
DECIMAL et NUMERIC sont interprétés comme <tt>java.math.BigDecimal</tt> et peuvent être constitués d'un trÚs grand nombre de chiffres.
 +
 
 +
=== Les nombres entiers ===
 +
TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC et DECIMAL (sans le point décimal) sont complÚtement interchangeables en interne, et sans étranglement des données. Le résultat des opérations est retourné dans un <tt>ResultSet</tt> JDBC dépendant du type des opérandes et dans un des types Java suivants: <tt>Integer</tt>, <tt>Long</tt> ou <tt>BigDecimal</tt>. On peut utiliser la méthode <tt>ResultSet.getXXXX()</tt>pour retrouver des valeurs, aussi longtemps que le résultat de ces derniÚres peut être affiché dans le type résultant. Ce type est basé de maniÚre arbitraire sur la requête et non sur ses lignes de résultat. Ce type ne change pas quand cette même requête qui retournait une ligne en retourne plusieurs, suite à l'ajout de données dans les tables.
 +
 
 +
Si la déclaration SELECT fait référence à un seul champ ou une fonction, le type retourné est le type correspondant au champ ou au type retourné par la fonction. Par exemple:
 +
 
 +
    CREATE TABLE t(a INTEGER, b BIGINT); SELECT MAX(a), MAX(b) FROM t;
 +
 
 +
Affiche un résultat ou le type de la premiÚre colonne est <tt>java.lang.Integer</tt> et celui de la seconde colonne est <tt>java.lang.Long</tt>. Toutefois,
 +
 
 +
    SELECT MAX(a) + 1, MAX(b) + 1 FROM t;
 +
 
 +
Retourne des valeurs de type <tt>java.lang.Long</tt> et <tt>BigDecimal</tt>, en résultat à une évolution uniforme du type pour toutes les valeurs de retour.
 +
 
 +
Il n'y a pas de limite intégrée sur la taille des valeurs entiÚres intermédiaires à l'intérieur des expressions. Vous devez donc déterminer le type de la colonne du <tt>ResultSet</tt> et choisir la méthode <tt>getXXXX()</tt> appropriée pour pouvoir la gérer. Alternativement, vous pouvez utiliser la méthode <tt>getObject()</tt> , fondre la réponse en <tt>java.lang.Number</tt> et utiliser l'une des méthodes <tt>intValue()</tt> ou <tt>longValue()</tt> sur le résultat.
 +
 
 +
Le résultat d'une expression doit “tenir” dans la colonne cible pour pouvoir être stocké dans le champ. Sinon vous obtiendrez un message d'erreur. Par exemple le résultat de <tt>1234567890123456789012 / 12345687901234567890</tt> peut être stocké dans tous les types de champs d'entiers, y compris une colonne TINYINT. Effectivement, c'est une petite valeur.
 +
 
 +
=== Autres types numériques ===
 +
Dans les déclarations SQL, les nombre dotés d'un point décimal sont traités comme de type DECIMAL sauf si ils sont écris avec un exposant. <tt>0.2</tt> est donc considéré comme DECIMAL alors que <tt>0.2E0</tt> est traité comme DOUBLE.
 +
 
 +
A l'utilisation de <tt>PreparedStatement.setDouble()</tt> ou <tt>setFloat()</tt> , la valeur est automatiquement traitée comme DOUBLE.
 +
 
 +
Quand un REAL, FLOAT ou DOUBLE (tous synonymes) font partie d'une expression, le type du résultat est DOUBLE.
 +
 
 +
D'autre part, si aucune valeur DOUBLE n'existe, et qu'une valeur DECIMAL ou NUMERIC fait partie de l'expression, le type du résultat est DECIMAL. Le résultat peut être récupéré depuis un <tt>ResultSet</tt> dans le type requis aussi longtemps qu'il peut y être représenté. Cela signifie que les valeurs DECIMAL peuvent être converties en DOUBLE, à moins qu'elles ne soient en dehors de la plage comprise entre <tt>Double.MIN_VALUE</tt> et <tt>Double.MAX_VALUE</tt>. Comme pour les valeurs entiÚres, le résultat d'une expression doit “tenir” dans la colonne cible pour pouvoir être stocké dans le champ. Sinon vous obtiendrez un message d'erreur.
 +
 
 +
La distinction entre DOUBLE et DECIMAL est importante en cas de division. Quand les variables sont de type DECIMAL, le résultat est une valeur munie d'une échelle (nombre de chiffres à droite du point décimal) égale à la plus grande des échelles des variables. Avec une variable de type DOUBLE, l'échelle reflÚtera le résultat actuel de l'opération. Par exemple, <tt>10.0/8.0</tt> (DECIMAL) égale <tt>1.2</tt> mais <tt>10.0E0/8.0E0</tt> (DOUBLE) égale <tt>1.25</tt>. En dehors des opérations de division, les valeurs de type DECIMAL représentent l'aithmétique exacte; Lors de la multiplication, l'échelle résultante est la somme des échelles des deux variables.
 +
 
 +
Les trois types de valeurs REAL, FLOAT et DOUBLE sont tous stockés dans la base de données comme des objets <tt>java.lang.Double</tt>. Les valeurs spéciales telles que NaN ou +-l'infini sont aussi stockées et prises en charge. Ces valeurs peuvent être fournies à la base de données via les méthodes JDBC PreparedStatement. Elles sont relues dans des objets ResultSet.
 +
 
 +
=== Les types Bit et Boolean ===
 +
Depuis la version 1.7.2, le type BIT est juste un alias pour BOOLEAN. L'image primaire du champ BOOLEAN est <tt>'true'</tt> ou <tt>'false'</tt> ('vrai' ou 'faux'), que ce soit comme type BOOLEAN ou comme une chaîne de caractÚres lorsqu'on l'utilise depuis JDBC. Ce type de champ peut aussi être initialisé en utilisant des valeurs de n'importe quel type numérique. Dans ce cas 0 est interprété comme <tt>false</tt> et n'importe quelle autre valeur, telle 1, est traduite en <tt>true</tt>.
 +
 
 +
Depuis la version 1.7.3 le type BOOLEAN se conforme aux standards SQL et prend en charge l'état UNDEFINED (indéfini) en plus de TRUE ou FALSE. Les valeur NULL sont traitées comme indéfinies. Cette amélioration affecte les requêtes contenant la déclaration NOT IN. Veuillez lire le fichier texte test, TestSelfNot.txt, pour des exemples de requêtes.
 +
 
 +
=== Stockage et manipulation des objets Java ===
 +
Depuis la version 1.7.2 la prise en charge s'est améliorée et chaque objet JAVA serialisable peut être ajouté directement dans une colonne de type OTHER en utilisant les variations des méthodes de <tt>PreparedStatement.setObject()</tt>.
 +
 
 +
A but de comparaison et en ce qui concerne les index, deux objets Java sont considérés comme égaux à moins que l'un d'entre eux ne soit NULL. Vous ne pouvez pas chercher un objet spécifique ou réaliser une jointure sur une colonne de type OTHER
 +
 
 +
Veuillez noter que HSQLDB n'est pas une base de données relationnelle d'objets. Les objets Java peuvent seulement être stockés en interne et aucune autre opération que l'assignation entre colonnes de type OTHER ou Tester les NULL. Des tests tels que <tt>WHERE object1 = object2</tt>, ou <tt>WHERE object1 = ? </tt>ne signifient pas ce que vous pourriez escompter, puisque tous les objets non nuls satisfont à un tel test. Mais <tt>WHERE object1 IS NOT NULL</tt> est parfaitement acceptable.
 +
 
 +
Le moteur ne renvoie pas d'erreurs quand des valeurs de colonnes normales sont assignées aux colonnes d'objets Java (par exemple l'assignation d'une valeur INTEGER ou STRING à un tel champ par une déclaration SQL comme <tt>UPDATE mytable SET objectcol = intcol WHERE ...</tt>) mais c'est la rendre indisponible dans le futur. Aussi utilisez les colonnes de type OTHER seulement pour stocker vos objets, et pour rien d'autre.
 +
 
 +
=== Caractéristiques de taille, de précision et d'échelle ===
 +
Avant la version 1.7.2, toutes les définitions de champ de table dotées d'une taille de colonne, d'un qualificatif précision ou échelle étaient acceptées et ignorées.
 +
 
 +
Depuis la version 1.8.0, de tels qualificatifs doivent se conformer aux standards SQL. Par exemple INTEGER(8) n'est maintenant plus accepté. Les qualificatifs sont toujours ignorés sauf si vous ajoutez une propriété à votre base de données. <tt>SET PROPERTY "sql.enforce_strict_size" TRUE </tt>imposera les tailles pour les types de champs CHARACTER ou VARCHAR et marquera chaque chaîne de caractÚres lors de l'insertion ou la mise à jour d'une colonne CHARACTER. Les qualificatifs Précision et échelle sont également imposés pour les types DECIMAL et NUMERIC. TIMESTAMP peut être seulement utilisé avec une précision de 0 ou 6.
 +
 
 +
Casting a value to a qualified CHARACTER type will result in truncation or padding as you would expect. So a test such as <tt>CAST (mycol AS VARCHAR(2)) = 'xy'</tt> will find the values beginning with 'xy'. This is the equivalent of <tt>SUBSTRING(mycol FROM 1 FOR 2)</tt> = 'xy'.
 +
 
 +
 
 +
== Sequences et Identity ==
 +
Le mot clé SEQUENCE est apparu dans la version 1.7.2 avec un sous-ensemble de la syntaxe standard SQL 200n. La syntaxe SQL 200n correspondante pour les colonnes IDENTITY a aussi été ajoutée.
 +
 
 +
=== Champs Identifiants auto-incrémentés ===
 +
Chaque table peut contenir une colonne auto-incrémentée, connue comme la colonne IDENTITY. Une colonne IDENTITY est toujours considérée comme la clé primaire de la table (et donc, les clés primaires multi-colonnes sont impossibles si une colonne IDENTITY existe). La prise en charge de <tt><nowiki>CREATE TABLE <tablename>(<colname> IDENTITY, ...)</nowiki></tt> a été ajoutée comme raccourci.
 +
 
 +
Depuis la version 1.7.2, la syntaxe standard SQL est utilisée par défaut, ce qui permet de préciser les valeurs initiales. La forme supportée est <tt><nowiki><colname> INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH n, [INCREMENT BY m])PRIMARY KEY, ...)</nowiki></tt>. La prise en charge des colonnes d'identités pour BIGINT a également été ajoutée. En conséquence, une colonne IDENTITY est seulement une colonne INTEGER ou BIGINT avec une valeur par défaut créée par un générateur séquentiel.
 +
 
 +
Quand vous ajoutez une nouvelle ligne à une telle table en utilisant la déclaration <tt><nowiki>INSERT INTO <tablename> ...; </nowiki></tt>, vous pouvez utiliser la valeur NULL pour la colonne IDENTITY, ce qui aura pour effet une valeur auto-générée pour ce champ. La fonction <tt>IDENTITY() </tt>renvoie la derniÚre valeur ajoutée dans n'importe quelle colonne IDENTITY par cette connexion. Utilisez la déclaration SQL <tt>CALL IDENTITY(); </tt>pour récupérer cette valeur. Si vous voulez utiliser la valeur pour un champ d'une table enfant, vous pouvez utiliser <tt><nowiki>INSERT INTO <tableEnfant> VALEURS (...,IDENTITY(),...);</nowiki></tt>. Les deux types d'appel à <tt>IDENTITY()</tt> doivent être réalisés avant toute autre mise à jour ou des déclarations d'insertions resteront irrésolues dans la base de données.
 +
 
 +
La prochaine valeur IDENTITY à insérer peut être réglée par la déclaration
 +
 
 +
<nowiki>ALTER TABLE ALTER COLUMN <column name> RESTART WITH <new value>;</nowiki>
 +
 
 +
=== Séquences ===
 +
La syntaxe et l'utilisation de SQL 200n est différente de celle de nombres de moteurs de base de données. Les séquences sont créées par la commande <tt>CREATE SEQUENCE</tt> et leur valeur courante peut être modifiée à tout moment avec <tt>ALTER SEQUENCE</tt>. La valeur suivante d'une séquence est récupérée avec l'expression <tt><nowiki>NEXT VALUE FOR <nomDuChamp></nowiki></tt>. Cette expression peut être utilisée pour ajouter et mettre à jour des lignes de la table. Vous pouvez aussi l'utiliser dans les déclarations SELECT. Par exemple, si vous voulez énumérer les lignes d'une commande SELECT en ordre séquentiel, vous pouvez utiliser:
 +
 
 +
== Example 2.3. Énumérer les lignes d'une commande SELECT en ordre séquentiel ==
 +
    SELECT NEXT VALUE FOR mysequence, col1, col2 FROM mytable WHERE ...
 +
 
 +
Veuillez noter que la sémantique des séquences est légÚrement différente de celle définie par SQL 200n. Par exemple si vous utilisez la même séquence deux fois dans la même requête insertion, vous obtiendrez deux valeurs différentes, et non la même comme le requiert le standard.
 +
 
 +
Vous pouvez rechercher dans la table SYSTEM_SEQUENCES la prochaine valeur qui sera retournée pour chaque séquence définie. La colonne SEQUENCE_NAME contient le nom et le champ NEXT_VALUE contient la prochaine valeur à retourner.
 +
 
 +
== ProblÚmes avec les transactions ==
 +
HSQLDB prend en charge les transactions au niveau READ_UNCOMMITTED (Lecture seule non validée), également connu comme le niveau 0 Isolation de la transaction.Ceci signifie que durant la durée de vie de la transaction, les autres connexions pourront voir les changements appliqués aux données. La prise en charge des transactions fonctionne généralement correctement. Les rapports de bugs concernant les transactions en cours de validation lorsque la base se ferme brutalement ont été corrigés. Toutefois, les problÚmes suivants pourraient être rencontrés dans le cas de connexions multiples à une base de données utilisant les transactions:
 +
 
 +
Si deux transactions modifient la même ligne, aucune exception n’est soulignée lorsque les deux transactions sont validées. Ceci peut être évité en construisant votre base de données de telle façon que la consistence des données de l’application ne dépende pas de la modification exclusive des données par une seule transaction. Vous pouvez définir une propriété de la base de données pour provoquer l’exception quand cela se produit.
 +
 
 +
SET PROPERTY "sql.tx_no_multi_rewrite" TRUE
 +
 
 +
Quand une commande <tt>ALTER TABLE .. INSERT COLUMN</tt> or <tt>DROP COLUMN</tt> abouti à un changement de la structure de la table, la session en cours est validée. Si une transaction non validée engagée par une autre connexion a changé les données dans la table, il ne sera plus possible d'annuler les changements aprÚs la commande <tt>ALTER TABLE</tt> . Cela s'applique aussi aux commandes <tt>ADD INDEX</tt> or <tt>ADD CONSTRAINT</tt> . Il est recommandé de n'utiliser ces commandes ALTER qu'une fois assuré qu'aucune autre connexion n'effectue de transactions.
 +
 
 +
Lorsqu'une commande CHECKPOINT pose problÚme, les transactions non validées peuvent être continuées, validées ou annulées. Toutefois, si la base de données n'est pas fermée proprement par la suite grâce à la commande SHUTDOWN, toute transaction de ce type restant non validée au moment de l'arrêt, est en partie validée (jusqu'à l'état du CHECKPOINT) au démarrage suivant. Il est préconisé d'utiliser la commande CHECKPOINT même s'il n'y a aucune transaction non validée en cours, car il est connu qu'un tel type de transaction ne tarde pas à voir un autre arrêt impromptu affecter ses données.
 +
 
 +
== Nouvelles fonctionnalités et changements ==
 +
Dans les derniÚres versions jusqu'à la 1.8.0 beaucoup d'améliorations ont vu le jour pour une meilleure prise en charge de SQL. Elles sont listées au chapitre [#sqlsyntax-chapter SQL Syntax] , dans [../../changelog_1_8_0.txt ../changelog_1_8_0.txt] and [../../changelog_1_7_2.txt ../changelog_1_7_2.txt]. Vous trouverez des fonctions et expressions comme POSITION(), SUBSTRING(), NULLIF(), COALESCE(), CASE ... WHEN .. ELSE, ANY, ALL etc. parmi eux. D'autres améliorations ne sont peut être pas évidentes dans la documentation mais il y a pu avoir des changements de comportement depuis les versions précédentes. Les plus importantes parmi elles sont la manipulation de NULL dans les jointures (les colonnes nulles ne sont plus jointes) et les jointures externes (les résultats sont maintenant corrects). Vous devez tester vos applications avec la derniÚre version pour vous assurer qu’elles ne s’en remettent pas à un comportement incorrect d’un moteur obsolÚte. Le moteur continuera d’évoluer vers la prise en charge du plein standard SQL dans les futures versions, aussi est-il meilleur de ne se reposer sur aucune fonctionnalité non standard de la présente version.

Revision as of 11:04, 12 March 2009

Test de l'extension SUN WIKI PUBLISHER 1.0 oxt

Menu Fichier > Exporter > Format de fichier

HTML: <META NAME="CHANGEDBY" CONTENT="Dominique Pautrel">HTML: <META NAME="CHANGEDBY" CONTENT="Dominique Pautrel">HTML: <META NAME="CHANGEDBY" CONTENT="Dominique Pautrel">HTML: <META NAME="CHANGEDBY" CONTENT="Dominique Pautrel">Chapitre 2. ProblÚmes liés au SQL

(Le document original, à valeur normative, est à cette page : http://hsqldb.org/doc/guide/ch02.html )

Fred Toussi

Groupe de Développement HSQLDB

<ft@cluedup.com>

Copyright 2002-2005 Fred Toussi. Il est accordé la permission de distribuer ce document sans aucune altération et sous les termes de la licence HSQLDB. Une permission additionnelle est accordée au Groupe de Développement HSQLDB de distribuer ce document avec ou sans altérations et sous les termes de la licence HSQLDB.

Note de traduction : Ici, il y a néanmoins l'altération de la traduction. En cas de doute, se référer au document original, à valeur normative, à l'adresse : http://hsqldb.org/web/hsqlDocsFrame.html 

$Date: 2005/07/01 17:06:32 $

Table des MatiÚres (Table of Contents)

[#N102AE But de ce document]

[#N102B3 Support du standard SQL]

[#N102D3 Contraintes et Index]

[#N102D6 Contraintes de clés primaires]

[#N102DF Contrainte unique]

[#N1030E Index uniques]

[#N10318 Clés externes]

[#N1033B Index et vitesse d’exécution des requêtes]

[#N10372 Condition Where ou Jointure ?]

[#N103A6 Sous-requêtes et Jointures]

[#N103BF Types de champs et opérations arithmétiques]

[#N103D6 Les nombres entiers]

[#N10426 Autres types numériques]

[#N1045F Les types BIT et BOOLEAN]

[#N1047A Stockage et manipulation des objets Java]

[#N10499 Caractéristiques de taille, précision et échelle]

[#N104AE Séquences et Identity]

[#N104B3 Champs Identifiants auto-incrémentés]

[#N104E1 Séquences]

[#N104FC ProblÚmes avec les transactions]

[#N10521 Nouvelles fonctionnalités et changements]

But de ce document

Beaucoup de questions fréquemment posées dans les Forums et Mailing-Lists trouvent leur réponse dans ce guide. Si vous voulez utiliser HSQLDB avec votre application, vous devez lire ce guide.

Support du standard SQL

HSQLDB 1.8.0 supporte le langage SQL défini par les standards SQL 92, 99 et 2003. Cela signifie que quand une caractéristique de ce standard est supportée, ex.: left outer join, la syntaxe est celle qui est spécifiée par le texte standard. Nombre de caractéristiques du SQL 92 et 99 sont supportées jusqu’à un niveau avancé, il y a un support pour la plupart des mots de la SQL 2003 Foundation et pour plusieurs fonctionnalités optionnelles de ce standard. Toutefois, certaines fonctionnalités ne sont pas supportées, aussi nous ne prétendons pas assurer un support pour la totalité des caractéristiques des standards.

Le chapitre Syntaxe SQL de ce guide [#sqlsyntax-chapter SQL Syntax] liste tous les mots clés et la syntaxe utilisée. Lors de l’écriture ou de la conversion de la grammaire SQL DDL (Data Definition Language – Langage de définition des données) et DML (Data Manipulation Language – Langage de manipulation des données) pour HSQLDB, vous devez consulter la syntaxe supportée et modifier les déclarations (la grammaire) en conséquence.

Certains mots sont réservés par le standard et ne peuvent pas être utilisés comme des noms de tables ou de colonnes. Par exemple, le mot POSITION est une fonction définie par les standards d’un rÃŽle similaire à String.indexOf() en langage Java, et est donc réservé. A l’heure actuelle, HSQLDB ne vous empêchera pas d’utiliser un mot réservé si il ne le gÚre pas ou ne le connait pas. Par exemple BEGIN est un mot réservé qui n’est pas aujourd’hui pris en charge par HSQLDB et est donc permis comme nom de table ou de colonne. Vous devez éviter l’usage de tels mots, comme les versions futures de HSQLDB seront amenées à les prendre en charge, rejetants alors vos définitions de tables et vos requêtes. La liste complête des mots réservés du SQL est dans la source de la classe org.hsqldb.Token

HSQLDB prend en charge également des mots-clés et expressions qui ne font pas partie du standard SQL. Des expressions telles que SELECT TOP 5 FROM .., SELECT LIMIT 0 10 FROM ... ou DROP TABLE mytable IF EXISTS sont parmi quelques exemples.

Tous les mots-clés peuvent être utilisés pour les objets de base de données s’il sont encadrés par des guillemets doubles.

Contraintes et Index

Contraintes de clefs primaires

Avant la version 1.7.0, une instruction CONSTRAINT <name> PRIMARY KEY était traduite par le moteur en un index unique, et de plus, une colonne cachée était ajoutée à la table avec un autre index unique. Depuis la version 1.7.0 les deux contraintes de clef primaire simple et multicolonnes sont gérées. Elles sont autorisées par un index unique sur la ou les colonne(s) de clef primaire. Aucune autre colonne cachée n’est nécessaire pour ces index.

Contrainte unique

Conformément au standard SQL, une contrainte unique sur une colonne simple signifie que deux valeurs ne peuvent être égales à moins que l’une d’entre elles soit NULL. Ce qui signifie que vous pouvez avoir une ou plusieurs lignes ou la valeur de cette colonne est NULL.

Une contrainte unique sur des colonnes multiples (c1, c2, c3, ...) signifie qu’il ne peut pas y avoir deux jeux de valeurs identiques sauf si l’une des valeurs est NULL. Chaque colonne peut contenir des valeurs répétées. L’exemple suivant satisfait à une contrainte UNIQUE sur les deux colonnes:

Exemple 2.1. Valeurs de colonnes satisfaisant à une contrainte UNIQUE sur deux colonnes


1, 2
2, 1
2, 2
NULL, 1
NULL, 1
1, NULL
NULL, NULL
NULL, NULL

Depuis la version 1.7.2 le comportement des contraintes et index UNIQUE avec le respect de la valeur NULL a changé pour se conformer aux standards SQL. Une ligne dans laquelle une des valeurs pour n’importe quelle colonne de la contrainte UNIQUE est NULL peut toujours être ajoutée à la table. Donc plusieurs lignes peuvent contenir les mêmes valeurs pour les colonnes de la contrainte UNIQUE si l’une des valeurs est NULL.

Index uniques

Dans la version 1.8.0, les index UNIQUE définis par l'utilisateur peuvent encore être déclarés mais ils sont obsolÚtes. Au lieu de l'index, vous devez utiliser une contrainte UNIQUE.

CONSTRAINT <Nom de l'index> UNIQUE créé toujours en interne un index unique sur les champs, comme dans les versions précédentes. Il a donc exactement le même effet que la déclaration index UNIQUE, maintenant obsolÚte.

Clés externes

Depuis la version 1.7.0, HSQLDB a implémenté les clés externes à colonne simple et multiples. Une clé externe peut aussi être spécifiée en référence à une table cible sans avoir à nommer la ou les colonnes cibles. Dans ce cas la ou les colonnes de clé primaire de la table cible sont utilisées comme les colonnes référencées. Les types des champs de chaque paire Colonne de référence / Colonne référencée doivent être identiques. Lors de la déclaration d'une clé externe, une contrainte unique (ou clé primaire) doit exister dans les colonnes référencées de la table contenant la clé primaire. Un index non unique est créé automatiquement sur les colonnes référencées. Par exemple:

CREATE TABLE enfant(c1 INTEGER, c2 VARCHAR, FOREIGN KEY (c1, c2) REFERENCES parent(p1, p2));

Il doit y avoir une contrainte UNIQUE sur les colonnes (p1, p2) dans la table nommée “parent”. Un index non unique est automatiquement créé sur les colonnes (c1, c2) dans la table nommée “enfant”. Les colonnes p1 et c1 doivent être du même type (INTEGER). Les colonnes p2 et c2 doivent également être du même type (VARCHAR).

Index et vitesse d’exécution des requêtes

HSQLDB ne fait pas usage d’index pour améliorer le tri dans l’affichage des requêtes. Mais les index jouent un rÃŽle crucial dans l’amélioration de la vitesse d’exécution des requêtes. Si aucun index n’est utilisé dans une requête portant sur une seule table, comme une requête suppression (DELETE), alors toutes les lignes de la table devront être examinées. Avec un index sur un des champ figurant dans la clause WHERE, il est souvent possible de partir directement de la premiÚre ligne concernée, et ainsi réduire le nombre de lignes examinées.

Les index sont encore plus importants dans le cas de jointures entre plusieurs tables. SELECT ... FROM t1 JOIN t2 ON t1.c1 = t2.c2 est exécutée en prenant les lignes de t1 une par une et en trouvant une ligne correspondante dans t2. S’il n’y a pas d’index dans t2.c2, alors pour chaque ligne de t1, toutes les lignes de t2 devront être vérifiées. Alors qu’avec un index, une ligne correspondante peut être trouvée en une fraction de seconde. Et si la requête comporte une condition sur t1, i.e., SELECT ... FROM t1 JOIN t2 ON t1.c1 = t2.c2 WHERE t1.c3 = 4 alors un index sur t1.c3 supprimera le besoin de vérifier toutes les lignes de t1 une à une, et réduira le temps d’exécution à moins d’une milliseconde par enregistrement. En effet, si t1 et t2 contiennent chacune 10 000 lignes, la requête sans index devra vérifier 100 000 000 de combinaisons de lignes. Avec un index sur t2.c2, cela se retrouve réduit à 10 000 vérifications de lignes et recherches dans l’index. Avec l’index additionnel sur t2.c2, en moyenne 4 lignes seulement sont vérifiées avant d’obtenir la premiÚre ligne de résultat.

Les index sont créés automatiquement pour les clés primaires et les champs sans doublons. Pour en définir d’autres utilisez la commande CREATE INDEX.

Notez que dans HSQLDB un index unique sur plusieurs champs peut être utilisé en interne comme un index avec doublons sur la premiÚre colonne de la liste. Par exemple: CONSTRAINT nom1 UNIQUE (c1, c2, c3); signifie qu’il y a l’équivalent de CREATE INDEX nom2 ON unetable(c1);. Aussi vous n’avez pas besoin de spécifier un autre index si vous avez besoin de celui du premier champ de la liste.

Dans la version 1.8.0, un index multicolonnes accélerera les requêtes qui contiennent des jointures ou des valeurs dans TOUTES les colonnes. Vous n’avez pas besoin de déclarer d’autres index individuels sur ces champs à moins que vous n’utilisiez des requêtes qui effectuent des recherches seulement sur une partie de ces champs. Par exemple, les lignes d’une table qui a une clé primaire ou une contrainte UNIQUE sur trois champs, ou même un simple index sur ces champs, peuvent être trouvées efficacement quand les valeurs pour ces trois champs sont spécifiées dans la clause WHERE. Par exemple, SELECT ... FROM t1 WHERE t1.c1 = 4 AND t1.c2 = 6 AND t1.c3 = 8 utilisera un index sur t1(c1,c2,c3) s’il existe.

Suite aux améliorations des index multi-clés, l’ordre de déclaration des champs dans l’index ou la contrainte a moins d’effet qu’avant sur la vitesse des recherches. Cependant si le champ qui contient le plus de valeurs différentes est en premiÚre place, la recherche sera sensiblement plus rapide

Un index multichamps n’accélÚrera pas seulement les requêtes sur le second ou le troisiÚme champ. Le premier champ doit être spécifié dans les conditions JOIN .. ON ou WHERE.

La vitesse d’exécution des requêtes dépend beaucoup de l’ordre des tables dans les clauses JOIN .. ON ou FROM. Par exemple la deuxiÚme requête ci-dessous sera plus rapide avec de grandes tables (par le fait qu’il y ait un index sur TB.COL3). La raison est que TB.COL3 peut être évaluée trÚs rapidement si la recherche s’applique sur la premiÚre table (et s’il y a un index sur TB.COL3):

....(TB est une trÚs grande table avec seulement quelques lignes ou TB.COL3 = 4)
    SELECT * FROM TA JOIN TB ON TA.COL1 = TB.COL2 AND TB.COL3 = 4;
    SELECT * FROM TB JOIN TA ON TA.COL1 = TB.COL2 AND TB.COL3 = 4;

La rÚgle générale est de mettre d’abord la table dont un des champs a des conditions restrictives.

La version 1.7.3 fournit des index automatiques à la volée pour les vues et sous-sélections utilisées dans une requête. Un index est ajouté à une vue quand elle est jointe à une table ou à une autre vue.

Condition Where ou jointure ?

Utiliser des conditions WHERE pour joindre des tables revient à réduire la vitesse d’exécution. Par exemple la requête suivante sera généralement lente, même indexée:

    SELECT ... FROM TA, TB, TC WHERE TC.COL3 = TA.COL1 AND TC.COL3=TB.COL2 AND TC.COL4 = 1

La requête implique que TA.COL1 = TB.COL2 sans spécifier explicitement cette condition. Si TA et TB contiennent chacune 100 lignes, 10 000 lignes seront jointes à TC avant d'appliquer les conditions de colonnes, et ceci bien qu'il y ait des index sur les colonnes jointes. Avec le mot-clé JOIN, la condition TA.COL1 = TB.COL2 doit être explicite. Elle restreindra la combinaison de lignes de TA et TB avant qu'elle ne soit jointe à TC, aboutissant plus rapidement sur de grandes tables.

    SELECT ... FROM TA JOIN TB ON TA.COL1 = TB.COL2 JOIN TC ON TB.COL2 = TC.COL3 WHERE TC.COL4 = 1

La requête pourrait être grandement accélérée si l'ordre des tables jointes était changé, pour que TC.COL1 = 1 soit appliqué en premier et que moins de lignes soient jointes ensemble:

    SELECT ... FROM TC JOIN TB ON TC.COL3 = TB.COL2 JOIN TA ON TC.COL3 = TA.COL1 WHERE TC.COL4 = 1

Dans l'exemple précédent le moteur applique automatiquement TC.COL4 = 1 à TC et joint seulement aux autres tables le jeu de lignes qui satisfait à cette condition. Les index sur TC.COL4, TB.COL2 et TA.COL1 seront utilisés s'ils existent et accélÚreront encore la requête.

Sous-requêtes et Jointures

Utiliser les jointures et définir l'ordre des tables pour un maximum de performances s'applique à tous les domaines. Par exemple, la deuxiÚme requête ci dessous doit être plus rapide s'il y a des index sur TA.COL1 et TB.COL3

Exemple 2.2. Comparaison de requêtes

    SELECT ... FROM TA WHERE TA.COL1 = (SELECT MAX(TB.COL2) FROM TB WHERE TB.COL3 = 4)
    SELECT ... FROM (SELECT MAX(TB.COL2) C1 FROM TB WHERE TB.COL3 = 4) T2 JOIN TA ON TA.COL1 = T2.C1

La deuxiÚme requête transforme MAX(TB.COL2) en une table à une seule ligne avant de la joindre à TA. Avec un index sur TA.COL1, c'est trÚs rapide. La premiÚre requête, quant à elle, testera chaque ligne et évaluera MAX(TB.COL2) encore et encore.

Types de champs et opérations arithmétiques

Tous les types de colonnes de table pris en charge par HSQLDB peuvent être indexés et possÚdent des fonctionnalités comparables. Les types de champ peuvent être explicitement convertis en utilisant la fonction de librairie CONVERT(), mais la plupart du temps ils sont convertis automatiquement. Il est recommandé de ne pas utiliser d'index sur les types LONGVARBINARY, LONGVARCHAR et OTHER, puisque ces index ne seront sans doute plus implémentés dans les versions futures.

Les versions précédentes de HSQLDB ne prenaient que peu en charge les opérations arithmétiques. Par exemple, il n'était pas possible d'insérer 10/2,5 dans aucun champ DOUBLE ou DECIMAL. Depuis la version 1.7.0, toutes les opérations sont possibles avec les rÚgles suivantes:

TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC et DECIMAL (sans point décimal) sont de type entier et correspondent avec byte, short, int, long et BigDecimal dans Java. Le type SQL détermine les valeurs minimum et maximum qui peuvent être supportées pour chaque type de champ. Par exemple la valeur range (amplitude) pour TINYINT permet de -128 à +127, bien que le type de champ utilisé dans Java pour le manipuler soit java.lang.Integer.

REAL, FLOAT, DOUBLE sont tous interpretés comme double dans Java.

DECIMAL et NUMERIC sont interprétés comme java.math.BigDecimal et peuvent être constitués d'un trÚs grand nombre de chiffres.

Les nombres entiers

TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC et DECIMAL (sans le point décimal) sont complÚtement interchangeables en interne, et sans étranglement des données. Le résultat des opérations est retourné dans un ResultSet JDBC dépendant du type des opérandes et dans un des types Java suivants: Integer, Long ou BigDecimal. On peut utiliser la méthode ResultSet.getXXXX()pour retrouver des valeurs, aussi longtemps que le résultat de ces derniÚres peut être affiché dans le type résultant. Ce type est basé de maniÚre arbitraire sur la requête et non sur ses lignes de résultat. Ce type ne change pas quand cette même requête qui retournait une ligne en retourne plusieurs, suite à l'ajout de données dans les tables.

Si la déclaration SELECT fait référence à un seul champ ou une fonction, le type retourné est le type correspondant au champ ou au type retourné par la fonction. Par exemple:

    CREATE TABLE t(a INTEGER, b BIGINT); SELECT MAX(a), MAX(b) FROM t;

Affiche un résultat ou le type de la premiÚre colonne est java.lang.Integer et celui de la seconde colonne est java.lang.Long. Toutefois,

    SELECT MAX(a) + 1, MAX(b) + 1 FROM t;

Retourne des valeurs de type java.lang.Long et BigDecimal, en résultat à une évolution uniforme du type pour toutes les valeurs de retour.

Il n'y a pas de limite intégrée sur la taille des valeurs entiÚres intermédiaires à l'intérieur des expressions. Vous devez donc déterminer le type de la colonne du ResultSet et choisir la méthode getXXXX() appropriée pour pouvoir la gérer. Alternativement, vous pouvez utiliser la méthode getObject() , fondre la réponse en java.lang.Number et utiliser l'une des méthodes intValue() ou longValue() sur le résultat.

Le résultat d'une expression doit “tenir” dans la colonne cible pour pouvoir être stocké dans le champ. Sinon vous obtiendrez un message d'erreur. Par exemple le résultat de 1234567890123456789012 / 12345687901234567890 peut être stocké dans tous les types de champs d'entiers, y compris une colonne TINYINT. Effectivement, c'est une petite valeur.

Autres types numériques

Dans les déclarations SQL, les nombre dotés d'un point décimal sont traités comme de type DECIMAL sauf si ils sont écris avec un exposant. 0.2 est donc considéré comme DECIMAL alors que 0.2E0 est traité comme DOUBLE.

A l'utilisation de PreparedStatement.setDouble() ou setFloat() , la valeur est automatiquement traitée comme DOUBLE.

Quand un REAL, FLOAT ou DOUBLE (tous synonymes) font partie d'une expression, le type du résultat est DOUBLE.

D'autre part, si aucune valeur DOUBLE n'existe, et qu'une valeur DECIMAL ou NUMERIC fait partie de l'expression, le type du résultat est DECIMAL. Le résultat peut être récupéré depuis un ResultSet dans le type requis aussi longtemps qu'il peut y être représenté. Cela signifie que les valeurs DECIMAL peuvent être converties en DOUBLE, à moins qu'elles ne soient en dehors de la plage comprise entre Double.MIN_VALUE et Double.MAX_VALUE. Comme pour les valeurs entiÚres, le résultat d'une expression doit “tenir” dans la colonne cible pour pouvoir être stocké dans le champ. Sinon vous obtiendrez un message d'erreur.

La distinction entre DOUBLE et DECIMAL est importante en cas de division. Quand les variables sont de type DECIMAL, le résultat est une valeur munie d'une échelle (nombre de chiffres à droite du point décimal) égale à la plus grande des échelles des variables. Avec une variable de type DOUBLE, l'échelle reflÚtera le résultat actuel de l'opération. Par exemple, 10.0/8.0 (DECIMAL) égale 1.2 mais 10.0E0/8.0E0 (DOUBLE) égale 1.25. En dehors des opérations de division, les valeurs de type DECIMAL représentent l'aithmétique exacte; Lors de la multiplication, l'échelle résultante est la somme des échelles des deux variables.

Les trois types de valeurs REAL, FLOAT et DOUBLE sont tous stockés dans la base de données comme des objets java.lang.Double. Les valeurs spéciales telles que NaN ou +-l'infini sont aussi stockées et prises en charge. Ces valeurs peuvent être fournies à la base de données via les méthodes JDBC PreparedStatement. Elles sont relues dans des objets ResultSet.

Les types Bit et Boolean

Depuis la version 1.7.2, le type BIT est juste un alias pour BOOLEAN. L'image primaire du champ BOOLEAN est 'true' ou 'false' ('vrai' ou 'faux'), que ce soit comme type BOOLEAN ou comme une chaîne de caractÚres lorsqu'on l'utilise depuis JDBC. Ce type de champ peut aussi être initialisé en utilisant des valeurs de n'importe quel type numérique. Dans ce cas 0 est interprété comme false et n'importe quelle autre valeur, telle 1, est traduite en true.

Depuis la version 1.7.3 le type BOOLEAN se conforme aux standards SQL et prend en charge l'état UNDEFINED (indéfini) en plus de TRUE ou FALSE. Les valeur NULL sont traitées comme indéfinies. Cette amélioration affecte les requêtes contenant la déclaration NOT IN. Veuillez lire le fichier texte test, TestSelfNot.txt, pour des exemples de requêtes.

Stockage et manipulation des objets Java

Depuis la version 1.7.2 la prise en charge s'est améliorée et chaque objet JAVA serialisable peut être ajouté directement dans une colonne de type OTHER en utilisant les variations des méthodes de PreparedStatement.setObject().

A but de comparaison et en ce qui concerne les index, deux objets Java sont considérés comme égaux à moins que l'un d'entre eux ne soit NULL. Vous ne pouvez pas chercher un objet spécifique ou réaliser une jointure sur une colonne de type OTHER

Veuillez noter que HSQLDB n'est pas une base de données relationnelle d'objets. Les objets Java peuvent seulement être stockés en interne et aucune autre opération que l'assignation entre colonnes de type OTHER ou Tester les NULL. Des tests tels que WHERE object1 = object2, ou WHERE object1 = ? ne signifient pas ce que vous pourriez escompter, puisque tous les objets non nuls satisfont à un tel test. Mais WHERE object1 IS NOT NULL est parfaitement acceptable.

Le moteur ne renvoie pas d'erreurs quand des valeurs de colonnes normales sont assignées aux colonnes d'objets Java (par exemple l'assignation d'une valeur INTEGER ou STRING à un tel champ par une déclaration SQL comme UPDATE mytable SET objectcol = intcol WHERE ...) mais c'est la rendre indisponible dans le futur. Aussi utilisez les colonnes de type OTHER seulement pour stocker vos objets, et pour rien d'autre.

Caractéristiques de taille, de précision et d'échelle

Avant la version 1.7.2, toutes les définitions de champ de table dotées d'une taille de colonne, d'un qualificatif précision ou échelle étaient acceptées et ignorées.

Depuis la version 1.8.0, de tels qualificatifs doivent se conformer aux standards SQL. Par exemple INTEGER(8) n'est maintenant plus accepté. Les qualificatifs sont toujours ignorés sauf si vous ajoutez une propriété à votre base de données. SET PROPERTY "sql.enforce_strict_size" TRUE imposera les tailles pour les types de champs CHARACTER ou VARCHAR et marquera chaque chaîne de caractÚres lors de l'insertion ou la mise à jour d'une colonne CHARACTER. Les qualificatifs Précision et échelle sont également imposés pour les types DECIMAL et NUMERIC. TIMESTAMP peut être seulement utilisé avec une précision de 0 ou 6.

Casting a value to a qualified CHARACTER type will result in truncation or padding as you would expect. So a test such as CAST (mycol AS VARCHAR(2)) = 'xy' will find the values beginning with 'xy'. This is the equivalent of SUBSTRING(mycol FROM 1 FOR 2) = 'xy'.


Sequences et Identity

Le mot clé SEQUENCE est apparu dans la version 1.7.2 avec un sous-ensemble de la syntaxe standard SQL 200n. La syntaxe SQL 200n correspondante pour les colonnes IDENTITY a aussi été ajoutée.

Champs Identifiants auto-incrémentés

Chaque table peut contenir une colonne auto-incrémentée, connue comme la colonne IDENTITY. Une colonne IDENTITY est toujours considérée comme la clé primaire de la table (et donc, les clés primaires multi-colonnes sont impossibles si une colonne IDENTITY existe). La prise en charge de CREATE TABLE <tablename>(<colname> IDENTITY, ...) a été ajoutée comme raccourci.

Depuis la version 1.7.2, la syntaxe standard SQL est utilisée par défaut, ce qui permet de préciser les valeurs initiales. La forme supportée est <colname> INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH n, [INCREMENT BY m])PRIMARY KEY, ...). La prise en charge des colonnes d'identités pour BIGINT a également été ajoutée. En conséquence, une colonne IDENTITY est seulement une colonne INTEGER ou BIGINT avec une valeur par défaut créée par un générateur séquentiel.

Quand vous ajoutez une nouvelle ligne à une telle table en utilisant la déclaration INSERT INTO <tablename> ...; , vous pouvez utiliser la valeur NULL pour la colonne IDENTITY, ce qui aura pour effet une valeur auto-générée pour ce champ. La fonction IDENTITY() renvoie la derniÚre valeur ajoutée dans n'importe quelle colonne IDENTITY par cette connexion. Utilisez la déclaration SQL CALL IDENTITY(); pour récupérer cette valeur. Si vous voulez utiliser la valeur pour un champ d'une table enfant, vous pouvez utiliser INSERT INTO <tableEnfant> VALEURS (...,IDENTITY(),...);. Les deux types d'appel à IDENTITY() doivent être réalisés avant toute autre mise à jour ou des déclarations d'insertions resteront irrésolues dans la base de données.

La prochaine valeur IDENTITY à insérer peut être réglée par la déclaration

ALTER TABLE ALTER COLUMN <column name> RESTART WITH <new value>;

Séquences

La syntaxe et l'utilisation de SQL 200n est différente de celle de nombres de moteurs de base de données. Les séquences sont créées par la commande CREATE SEQUENCE et leur valeur courante peut être modifiée à tout moment avec ALTER SEQUENCE. La valeur suivante d'une séquence est récupérée avec l'expression NEXT VALUE FOR <nomDuChamp>. Cette expression peut être utilisée pour ajouter et mettre à jour des lignes de la table. Vous pouvez aussi l'utiliser dans les déclarations SELECT. Par exemple, si vous voulez énumérer les lignes d'une commande SELECT en ordre séquentiel, vous pouvez utiliser:

Example 2.3. Énumérer les lignes d'une commande SELECT en ordre séquentiel

    SELECT NEXT VALUE FOR mysequence, col1, col2 FROM mytable WHERE ...

Veuillez noter que la sémantique des séquences est légÚrement différente de celle définie par SQL 200n. Par exemple si vous utilisez la même séquence deux fois dans la même requête insertion, vous obtiendrez deux valeurs différentes, et non la même comme le requiert le standard.

Vous pouvez rechercher dans la table SYSTEM_SEQUENCES la prochaine valeur qui sera retournée pour chaque séquence définie. La colonne SEQUENCE_NAME contient le nom et le champ NEXT_VALUE contient la prochaine valeur à retourner.

ProblÚmes avec les transactions

HSQLDB prend en charge les transactions au niveau READ_UNCOMMITTED (Lecture seule non validée), également connu comme le niveau 0 Isolation de la transaction.Ceci signifie que durant la durée de vie de la transaction, les autres connexions pourront voir les changements appliqués aux données. La prise en charge des transactions fonctionne généralement correctement. Les rapports de bugs concernant les transactions en cours de validation lorsque la base se ferme brutalement ont été corrigés. Toutefois, les problÚmes suivants pourraient être rencontrés dans le cas de connexions multiples à une base de données utilisant les transactions:

Si deux transactions modifient la même ligne, aucune exception n’est soulignée lorsque les deux transactions sont validées. Ceci peut être évité en construisant votre base de données de telle façon que la consistence des données de l’application ne dépende pas de la modification exclusive des données par une seule transaction. Vous pouvez définir une propriété de la base de données pour provoquer l’exception quand cela se produit.

SET PROPERTY "sql.tx_no_multi_rewrite" TRUE

Quand une commande ALTER TABLE .. INSERT COLUMN or DROP COLUMN abouti à un changement de la structure de la table, la session en cours est validée. Si une transaction non validée engagée par une autre connexion a changé les données dans la table, il ne sera plus possible d'annuler les changements aprÚs la commande ALTER TABLE . Cela s'applique aussi aux commandes ADD INDEX or ADD CONSTRAINT . Il est recommandé de n'utiliser ces commandes ALTER qu'une fois assuré qu'aucune autre connexion n'effectue de transactions.

Lorsqu'une commande CHECKPOINT pose problÚme, les transactions non validées peuvent être continuées, validées ou annulées. Toutefois, si la base de données n'est pas fermée proprement par la suite grâce à la commande SHUTDOWN, toute transaction de ce type restant non validée au moment de l'arrêt, est en partie validée (jusqu'à l'état du CHECKPOINT) au démarrage suivant. Il est préconisé d'utiliser la commande CHECKPOINT même s'il n'y a aucune transaction non validée en cours, car il est connu qu'un tel type de transaction ne tarde pas à voir un autre arrêt impromptu affecter ses données.

Nouvelles fonctionnalités et changements

Dans les derniÚres versions jusqu'à la 1.8.0 beaucoup d'améliorations ont vu le jour pour une meilleure prise en charge de SQL. Elles sont listées au chapitre [#sqlsyntax-chapter SQL Syntax] , dans [../../changelog_1_8_0.txt ../changelog_1_8_0.txt] and [../../changelog_1_7_2.txt ../changelog_1_7_2.txt]. Vous trouverez des fonctions et expressions comme POSITION(), SUBSTRING(), NULLIF(), COALESCE(), CASE ... WHEN .. ELSE, ANY, ALL etc. parmi eux. D'autres améliorations ne sont peut être pas évidentes dans la documentation mais il y a pu avoir des changements de comportement depuis les versions précédentes. Les plus importantes parmi elles sont la manipulation de NULL dans les jointures (les colonnes nulles ne sont plus jointes) et les jointures externes (les résultats sont maintenant corrects). Vous devez tester vos applications avec la derniÚre version pour vous assurer qu’elles ne s’en remettent pas à un comportement incorrect d’un moteur obsolÚte. Le moteur continuera d’évoluer vers la prise en charge du plein standard SQL dans les futures versions, aussi est-il meilleur de ne se reposer sur aucune fonctionnalité non standard de la présente version.

Personal tools