I. Nouvelles fonctionnalités du langage SQL▲
I-A. Prise en charge de la clause WHEN NOT MATCHED BY SOURCE dans l'instruction MERGE▲
L'opérateur MERGE fusionne les lignes de la table source et de la table cible (ou vue actualisable).
Lors de l'exécution d'une instruction MERGE, les lignes sources sont lues, puis l’ordre INSERT, UPDATE ou DELETE est effectué sur la table cible en fonction des conditions.
Syntaxe de MERGE :
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
MERGE
INTO target [[AS] target_alias]
USING <source> [[AS] source_alias]
ON <join condition>
<merge when> [<merge when> ...]
[<plan clause>]
[<order by clause>]
[<returning clause>]
<source> ::= tablename | (<select_stmt>)
<merge when> ::=
<merge when matched>
| <merge when not matched by target>
| <merge when not matched by source>
<merge when matched> ::=
WHEN MATCHED [ AND <condition> ]
THEN { UPDATE SET <assignment_list> | DELETE }
<merge when not matched by target> ::=
WHEN NOT MATCHED [ BY TARGET ] [ AND <condition> ]
THEN INSERT [ <left paren> <column_list> <right paren> ]
VALUES <left paren> <value_list> <right paren>
<merge when not matched by source> ::=
WHEN NOT MATCHED BY SOURCE [ AND <condition> ] THEN
{ UPDATE SET <assignment list> | DELETE }
Firebird 5.0 a introduit les branches conditionnelles <merge when not matched by source> , qui vous permettent de mettre à jour ou de supprimer des lignes de la table cible si elles ne sont pas présentes dans la source de données.
Désormais, l'opérateur MERGE est un outil véritablement universel pour toute modification de la table cible pour un certain ensemble de données.
La source de données peut être une table, une vue, une procédure stockée ou une table dérivée. Lorsqu'une instruction MERGE est exécutée, une jointure est effectuée entre la table source (USING) et la table cible. Le type de jointure dépend de la présence de clauses WHEN NOT MATCHED :
- <merge when not matched by target>
<merge when not matched by source> FULL JOIN - <merge when not matched by source> RIGHT JOIN
- <merge when not matched by target> LEFT JOIN
- only <merge when matched> INNER JOIN
L'action sur la table cible, ainsi que les conditions dans lesquelles elle est effectuée, sont décrites dans la clause WHEN.
Il est possible d'avoir plusieurs clauses WHEN MATCHED, WHEN NOT MATCHED [BY TARGET] et WHEN NOT MATCHED BY SOURCE.
Si la condition de la clause WHEN n'est pas remplie, Firebird l'ignore et passe à la clause suivante.
Cela continuera jusqu'à ce que la condition de l'une des clauses WHEN soit remplie. Dans ce cas, l'action associée à cette clause WHEN est effectuée pour la ligne résultante de la jointure sur la table cible. Une seule action est effectuée pour chaque ligne de résultat de la jointure.
I-A-1. WHEN MATCHED▲
Spécifie que toutes les lignes cibles qui correspondent aux lignes renvoyées par l'expression <source> ON <join condition> et satisfont à des conditions de recherche supplémentaires sont mises à jour (clause UPDATE) ou supprimées (clause DELETE) conformément à la clause <merge when matched>.
Plusieurs clauses WHEN MATCHED sont autorisées. Si c’est le cas, elles doivent toutes être complétées par des termes de recherche supplémentaires, à l'exception du dernier. Une instruction MERGE ne peut pas mettre à jour la même ligne plusieurs fois ni mettre à jour et supprimer la même ligne en même temps.
I-A-2. WHEN NOT MATCHED BY SOURCE▲
Spécifie que toutes les lignes cibles qui ne correspondent pas aux lignes renvoyées par l'expression <source> ON <join condition> et satisfont à des conditions de recherche supplémentaires sont mises à jour (clause UPDATE) ou sont supprimées (clause DELETE) conformément à la clause <merge when not matched by source >. La clause WHEN NOT MATCHED BY SOURCE est devenue disponible dans Firebird 5.0.
Plusieurs clauses WHEN NOT MATCHED BY SOURCE sont autorisées. Si c’est le cas, elles doivent toutes être complétées par des termes de recherche supplémentaires, à l'exception du dernier.
Note : dans la liste SET d'une clause UPDATE, cela n'a aucun sens d'utiliser des expressions qui font référence à <source>, puisqu'aucune entrée de <source> ne correspond aux entrées cibles.
I-A-3. Exemple d’utilisation du MERGE avec la clause WHEN NOT MATCHED BY SOURCE▲
Supposons que vous ayez une liste de prix dans la table temporaire tmp_price et que vous deviez mettre à jour le prix actuel afin que :
- si le produit ne figure pas dans la liste de prix actuelle, ajoutez-le ;
- si le produit figure dans la liste de prix actuelle, mettez à jour son prix ;
- si le produit est inclus dans la liste de prix en vigueur, mais n'est pas dans la nouvelle, alors supprimez cette ligne de prix.
Toutes ces actions peuvent être effectuées dans une seule commande SQL :
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
MERGE INTO price
USING tmp_price
ON price.good_id = tmp_price.good_id
WHEN NOT MATCHED
-- add if it wasn't there
THEN INSERT(good_id, name, cost)
VALUES(tmp_price.good_id, tmp_price.name, tmp_price.cost)
WHEN MATCHED AND price.cost <> tmp_price.cost THEN
-- update the price if the product is in the new price list
–- and the price is different
UPDATE SET cost = tmp_price.cost
WHEN NOT MATCHED BY SOURCE
-- if there is no product in the new price list, then we remove it
–- from the current price list
DELETE;
Dans cet exemple, à la place de la table temporaire tmp_price, il peut y avoir une requête SELECT ou une procédure stockée plus ou moins complexe.
Notez que puisque les clauses WHEN NOT MATCHED [BY TARGET] et WHEN NOT MATCHED BY SOURCE sont présentes, la jointure entre la table cible et la source de données se fera à l'aide d'un FULL JOIN.
Dans la version actuelle de Firebird, FULL JOIN n'utilisera pas d'index à droite ni à gauche et la performance sera dégradée.
I-B. Clause SKIP LOCKED▲
Firebird 5.0 a introduit la clause SKIP LOCKED, qui peut être utilisée dans les instructions SELECT .. WITH LOCK, UPDATE et DELETE.
L'utilisation de cette clause amène le moteur à ignorer les lignes verrouillées par d'autres transactions au lieu de les attendre ou de provoquer des erreurs de conflit de mise à jour.
L'utilisation de SKIP LOCKED est utile pour implémenter des files d'attente de travail, dans lesquelles un ou plusieurs processus soumettent du travail à une table et émettent un événement, tandis que les threads de travail (exécuteur) écoutent les événements et lisent/suppriment des éléments de la table. Grâce à SKIP LOCKED, plusieurs travaux peuvent recevoir des tâches exclusives d'une table sans conflits.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT
[FIRST ...]
[SKIP ...]
FROM <sometable>
[WHERE ...]
[PLAN ...]
[ORDER BY ...]
[{ ROWS ... } | {OFFSET ...} | {FETCH ...}]
[FOR UPDATE [OF ...]]
[WITH LOCK [SKIP LOCKED]]
2.
3.
4.
5.
6.
7.
8.
UPDATE <sometable>
SET ...
[WHERE ...]
[PLAN ...]
[ORDER BY ...]
[ROWS ...]
[SKIP LOCKED]
[RETURNING ...]
2.
3.
4.
5.
6.
7.
DELETE FROM <sometable>
[WHERE ...]
[PLAN ...]
[ORDER BY ...]
[ROWS ...]
[SKIP LOCKED]
[RETURNING ...]
Lorsque les clauses FIRST/SKIP/ROWS/OFFSET/FETCH sont présentes dans la requête, le moteur ignorera d'abord les lignes verrouillées, puis appliquera des restrictions (FIRST…) aux lignes restantes dans un ensemble de résultats.
Exemples :
- créer une table et un déclencheur
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
create table emails_queue (
subject varchar(60) not null,
text blob sub_type text not null
);
set term !;
create trigger emails_queue_ins after insert on emails_queue
as
begin
post_event('EMAILS_QUEUE');
end!
set term ;!
- envoyer un message par l’application
2.
3.
insert into emails_queue (subject, text)
values ('E-mail subject', 'E-mail text...');
commit;
- application cliente
2.
3.
4.
5.
6.
-- The client application can check table to the EMAILS_QUEUE event,
-- to send emails using this command :
delete from emails_queue
rows 10
skip locked
returning subject, text;
Plusieurs instances d'une application peuvent être en cours d'exécution, par exemple pour l'équilibrage de charge.
Note : l'utilisation de SKIP LOCKED pour organiser les files d'attente sera abordée dans un article séparé.
I-C. Prise en charge du renvoi de plusieurs lignes par les opérateurs avec la clause RETURNING▲
Depuis Firebird 5.0, les instructions de modification côté client INSERT .. SELECT, UPDATE, DELETE, UPDATE OR INSERT et MERGE, avec la clause RETURNING renverront un curseur : cela signifie qu'elles sont capables de renvoyer plusieurs lignes au lieu d’émettre le message d’erreur "multiple rows in singleton select " comme précédemment.
Ces requêtes sont désormais décrites comme isc_info_sql_stmt_select, alors que dans les versions précédentes, elles étaient décrites comme isc_info_sql_stmt_exec_procedure.
Les instructions singleton INSERT .. VALUES et les instructions positionnées UPDATE et DELETE (celles contenant une clause WHERE CURRENT OF) conservent le comportement existant et sont décrites comme isc_info_sql_stmt_exec_procedure.
Cependant, toutes ces instructions, si elles sont utilisées dans PSQL et si la clause RETURNING est utilisée, sont toujours traitées comme des singletons.
Exemples d'instructions de modification contenant RETURNING et renvoyant un curseur :
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
INSERT INTO dest(name, val)
SELECT desc, num + 1 FROM src WHERE id_parent = 5
RETURNING id, name, val;
UPDATE dest
SET a = a + 1
RETURNING id, a;
DELETE FROM dest
WHERE price < 0.52
RETURNING id;
MERGE INTO PRODUCT_INVENTORY AS TARGET
USING (
SELECT
SL.ID_PRODUCT,
SUM(SL.QUANTITY)
FROM
SALES_ORDER_LINE SL
JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
WHERE S.BYDATE = CURRENT_DATE
AND SL.ID_PRODUCT = :ID_PRODUCT
GROUP BY 1
) AS SRC(ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE SET
TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
TARGET.BYDATE = CURRENT_DATE
RETURNING OLD.QUANTITY, NEW.QUANTITY, SRC.QUANTITY;
I-D. index partiels▲
Dans Firebird 5.0, lors de la création d'un index, il est devenu possible de spécifier une clause WHERE facultative, qui spécifie une condition de recherche limitant le sous-ensemble de lignes de table à indexer.
De tels index sont appelés index partiels. La condition de recherche doit contenir une ou plusieurs colonnes de table. La définition d'index partiel peut inclure une spécification UNIQUE. Dans ce cas, chaque clef de l'index doit être unique. Cela vous permet de garantir l'unicité d'un certain sous-ensemble de lignes de la table.
La définition d'un index partiel peut également inclure une clause COMPUTED BY afin que l'index partiel puisse être calculé.
La syntaxe complète pour créer un index est donc la suivante :
2.
3.
4.
5.
CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]
INDEX indexname ON tablename
{(<column_list>) | COMPUTED [BY] (<value_expression>)}
[WHERE <search_condition>]
<column_list> ::= col [, col ...]
L'optimiseur ne peut utiliser un index partiel que dans les cas suivants :
- la clause WHERE inclut exactement la même expression logique que celle définie pour l'index ;
- la condition de recherche définie pour l'index contient des expressions booléennes combinées avec un OU, et l'une d'entre elles est explicitement incluse dans la clause WHERE ;
- la condition de recherche définie pour l'index spécifie IS NOT NULL et la clause WHERE inclut une expression pour la même colonne dont on sait qu'elle ignore NULL.
Si un index régulier et un index partiel existent pour le même ensemble de colonnes, l'optimiseur choisira l'index régulier même si la clause WHERE inclut la même expression que celle définie dans l'index partiel.
La raison de ce comportement est que l'index régulier a une meilleure sélectivité que l'index partiel.
Mais il existe des exceptions à cette règle : l'utilisation de prédicats avec une mauvaise sélectivité sur les colonnes indexées, tels que <>, IS DISTINCT FROM ou IS NOT NULL, à condition que le prédicat soit utilisé dans un index partiel.
Note : les index partiels ne peuvent pas être utilisés pour contraindre une clef primaire ou une clef étrangère : la clause USING INDEX ne peut pas spécifier une définition d'index partiel.
Voyons quand les indices partiels sont utiles.
Exemple 1. Unicité partielle
Disons que nous avons une table stockant l’adresse courriel d’une personne.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
CREATE TABLE MAN_EMAILS
( CODE_MAN_EMAIL BIGINT GENERATED BY DEFAULT AS IDENTITY
, CODE_MAN BIGINT NOT NULL
, EMAIL VARCHAR(50) NOT NULL
, DEFAULT_FLAG BOOLEAN DEFAULT FALSE NOT NULL
, CONSTRAINT PK_MAN_EMAILS
PRIMARY KEY(CODE_MAN_EMAIL)
, CONSTRAINT FK_EMAILS_REF_MAN
FOREIGN KEY(CODE_MAN) REFERENCES MAN(CODE_MAN)
)
;
Une personne peut avoir plusieurs adresses courriel, mais une seule peut être l'adresse par défaut. Un index réguliler ou une restriction unique ne fonctionnera pas dans ce cas, puisque nous serons limités à seulement deux adresses.
Ici, nous pouvons utiliser l'index unique partiel :
2.
3.
4.
SELECT
COUNT(*) FILTER(WHERE IS_ANCESTOR IS TRUE) AS CNT_ANCESTOR,
COUNT(*) FILTER(WHERE IS_ANCESTOR IS FALSE) AS CNT_OTHER
FROM HORSE
Ainsi, pour une personne, on autorise autant d'adresses que souhaité avec DEFAULT_FLAG=FALSE et une seule adresse avec DEFAULT_FLAG=TRUE.
Les index partiels peuvent être utilisés simplement pour rendre l'index plus compact.
Exemple 2. Réduire la taille de l'index
Supposons que vous ayez une table de chevaux HORSE dans votre base de données et qu'elle comporte la colonne IS_ANCESTOR utilisée pour indiquer si le cheval est l'ancêtre d'une lignée ou d'une famille. Évidemment, il y a des centaines de fois moins d’ancêtres que les autres chevaux – voir le résultat de la requête ci-dessous :
2.
3.
4.
SELECT
COUNT(*) FILTER(WHERE IS_ANCESTOR IS TRUE) AS CNT_ANCESTOR,
COUNT(*) FILTER(WHERE IS_ANCESTOR IS FALSE) AS CNT_OTHER
FROM HORSE
|
CNT_ANCESTOR CNT_OTHER |
Le but est d’obtenir rapidement une liste d’ancêtres. D'après les statistiques ci-dessus, il est également évident que pour l'option IS_ANCESTOR IS FALSE, l'utilisation de l'index est pratiquement inutile.
Essayons de créer un index régulier :
CREATE INDEX IDX_HORSE_ANCESTOR ON HORSE(IS_ANCESTOR);
Mais dans ce cas, un tel index sera redondant.
Regardons ses statistiques à l'aide de l'outil gstat :
|
Index IDX_HORSE_ANCESTOR (26) |
Comme vous pouvez le voir, l'index partiel est beaucoup plus compact : il y a 1 426 nœuds dans l'index partiel au lieu de 519 623 dans l'index régulier.
Vérifions qu'il peut être utilisé pour obtenir des ancêtres :
2.
3.
SELECT COUNT(*)
FROM HORSE
WHERE IS_ANCESTOR IS TRUE;
2.
3.
4.
5.
6.
7.
Select Expression
-> Aggregate
-> Filter
-> Table "HORSE" Access By ID
-> Bitmap
-> Index "IDX_HORSE_ANCESTOR" Full Scan
COUNT
|
===================== |
Veuillez noter que si vous spécifiez WHERE IS_ANCESTOR ou WHERE IS_ANCESTOR = TRUE dans la requête, l'index ne sera pas utilisé. Il est nécessaire que l'expression spécifiée pour filtrer l'index corresponde complètement à l'expression dans le WHERE de votre requête.
Un autre cas où les index partiels peuvent être utiles est celui de leur utilisation avec des prédicats non sélectifs.
Exemple 3. Utilisation d'index partiels avec des prédicats non sélectifs
Supposons que nous devions récupérer tous les chevaux morts dont la date de décès est connue. Un cheval est définitivement mort s'il possède une date de décès, mais il arrive souvent qu'elle ne soit pas répertoriée ou qu'elle soit tout simplement inconnue. De plus, le nombre de dates de décès inconnues est bien supérieur à celui des dates connues. Pour ce faire, nous écrirons la requête suivante :
2.
3.
SELECT COUNT(*)
FROM HORSE
WHERE DEATHDATE IS NOT NULL;
Nous souhaitons obtenir cette liste le plus rapidement possible, nous allons donc essayer de créer un index sur la colonne DEATHDATE.
2.
CREATE INDEX IDX_HORSE_DEATHDATE
ON HORSE(DEATHDATE);
Essayons maintenant d'exécuter la requête ci-dessus et d'examiner son plan et ses statistiques :
|
Sélectionnez 1. 2. 3. 4. 5. 6. ===================== |
Comme vous pouvez le constater, il n’a pas été possible d’utiliser l’index.
La raison en est que les prédicats IS NOT NULL, <>, IS DISTINCT FROM sont peu sélectifs.
Actuellement, Firebird ne dispose pas d'histogrammes avec la distribution des valeurs des clefs d'index et la distribution est donc supposée uniforme. Avec une distribution uniforme, cela n'a aucun sens d'utiliser un index pour de tels prédicats, ce qui est fait.
Essayons maintenant de supprimer l'index créé précédemment et de créer un index partiel à la place :
2.
3.
4.
DROP INDEX IDX_HORSE_DEATHDATE;
CREATE INDEX IDX_HORSE_DEATHDATE
ON HORSE(DEATHDATE) WHERE DEATHDATE IS NOT NULL;
Et relançons la requête précédente
2.
3.
4.
5.
6.
7.
8.
Select Expression
-> Aggregate
-> Filter
-> Table "HORSE" Access By ID
-> Bitmap
-> Index "IDX_HORSE_DEATHDATE" Full Scan
COUNT
|
===================== |
Comme vous pouvez le voir, l'optimiseur a réussi à utiliser notre index. Mais le plus intéressant est que notre index continuera à fonctionner avec d'autres prédicats de comparaison de dates (mais cela ne fonctionnera pas pour IS NULL).
Voir exemple ci-dessous :
2.
3.
SELECT COUNT(*)
FROM HORSE
WHERE DEATHDATE = DATE'01.01.2005';
2.
3.
4.
5.
6.
7.
8.
Select Expression
-> Aggregate
-> Filter
-> Table "HORSE" Access By ID
-> Bitmap
-> Index "IDX_HORSE_DEATHDATE" Range Scan (full match)
COUNT
|
===================== |
Dans ce cas, l'optimiseur s'est rendu compte que la condition de filtre IS NOT NULL dans l'index partiel couvre tous les autres prédicats qui ne se comparent pas à NULL.
Il est important de noter que si vous spécifiez la condition COLONNE > 2 dans l'index partiel et que la requête contient la condition de recherche COLONNE > 1, alors malgré le fait que tout nombre supérieur à 2 est également supérieur à 1, l'index partiel sera ne pas être utilisé. L'optimiseur n'est pas assez intelligent pour dériver cette condition d'équivalence.
I-E. Fonctions UNICODE_CHAR et UNICODE_VAL▲
Firebird 2.1 a introduit les fonctions ASCII_CHAR - renvoyant un caractère par son code dans la table ASCII, et ASCII_VAL - renvoyant le code dans la table ASCII par caractère. Ces fonctions s'appliquent uniquement aux codages sur un seul octet ; il n'y a rien de similaire pour UTF-8. Firebird 5.0 a ajouté deux fonctions supplémentaires qui fonctionnent avec les encodages multioctets :
|
UNICODE_CHAR (number) |
La fonction UNICODE_CHAR renvoie le caractère UNICODE pour le point de code donné. La fonction UNICODE_VAL renvoie le point de code UTF-32 pour le premier caractère d'une chaîne. Pour une chaîne vide, 0 est renvoyé.
2.
3.
4.
SELECT
UNICODE_VAL(UNICODE_CHAR(0x1F601)) AS CP_VAL,
UNICODE_CHAR(0x1F601) AS CH
FROM RDB$DATABASE
Exemple de résultat(1) :
I-F. Expressions de requête entre parenthèses▲
Dans la version 5.0, la syntaxe DML a été étendue pour permettre l'utilisation d'une expression de requête entre parenthèses (SELECT, y compris les clauses order by, offset et fetch, mais sans clause with), alors qu'auparavant seule la spécification de requête était autorisée (SELECT sans clauses with, order by, offset et fetch).
Cela vous permet d'écrire des requêtes plus claires, notamment dans les opérateurs UNION, et offre une plus grande compatibilité avec les opérateurs générés par certains ORM.
Note : l’utilisation d’expressions de requête entre parenthèses n’est pas gratuite du point de vue du moteur Firebird, car elles nécessitent un contexte de requête supplémentaire par rapport à une simple spécification de requête. Le nombre maximum de contextes de requête dans une instruction est limité à 255.
Exemple :
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
(
select emp_no, salary, 'lowest' as type
from employee
order by salary asc
fetch first row only
)
union all
(
select emp_no, salary, 'highest' as type
from employee
order by salary desc
fetch first row only
);
I-G. Littéraux améliorés▲
I-G-1. Syntaxe complète des chaînes littérales▲
La syntaxe littérale de la chaîne de caractères a été modifiée pour prendre en charge la syntaxe SQL standard complète. Cela signifie que le littéral peut être « interrompu » par des espaces ou des commentaires. Cela peut être utilisé, par exemple, pour diviser un long littéral sur plusieurs lignes ou pour fournir des commentaires en ligne.
Syntaxe littérale de chaîne selon ISO/IEC 9075-2:2016 SQL - Partie 2 : Fondation
2.
3.
4.
5.
6.
7.
<character string literal> ::=
[ <introducer> <character set specification> ]
<quote> [ <character representation>... ] <quote>
[ { <separator> <quote> [ <character representation>... ] <quote> }... ]
<separator> ::=
{ <comment> | <white space> }...
Exemple :
2.
3.
4.
5.
6.
7.
8.
9.
10.
-- spaces between literals
select 'ab'
'cd'
from RDB$DATABASE;
-- output: 'abcd'
-- comment and spaces between literals
select 'ab' /* comment */ 'cd'
from RDB$DATABASE;
-- output: 'abcd'
I-G-2. Syntaxe complète pour les littéraux binaires▲
La syntaxe des littéraux de chaîne binaire a été modifiée pour prendre en charge la syntaxe SQL standard complète. Cela signifie que le littéral peut contenir des espaces pour séparer les caractères hexadécimaux et peut être « interrompu » par des espaces ou des commentaires. Cela peut être utilisé, par exemple, pour rendre une chaîne hexadécimale plus lisible en regroupant des caractères, pour diviser un long littéral sur plusieurs lignes ou pour fournir des commentaires en ligne.
La syntaxe littérale binaire est conforme à la norme ISO/IEC 9075-2:2016 SQL - Partie 2 : Fondation
2.
3.
4.
5.
<binary string literal> ::=
{X|x} <quote> [ <space>... ] [ { <hexit> [ <space>... ] <hexit> [ <space>... ] }...
] <quote>
[ { <separator> <quote> [ <space>... ] [ { <hexit> [ <space>... ]
<hexit> [ <space>... ] }... ] <quote> }... ]
Exemples :
2.
3.
4.
5.
6.
7.
8.
9.
10.
-- Grouping by bytes (spaces within a literal)
select _win1252 x'42 49 4e 41 52 59'
from RDB$DATABASE;
-- output: BINARY
-- spaces between literals
select _win1252 x'42494e'
'415259'
from RDB$DATABASE;
-- output: BINARY
I-H. Prédicat IN amélioré▲
Avant Firebird 5.0, le prédicat IN avec une liste de constantes était limité à 1 500 éléments, car il était traité en convertissant de manière récursive l'expression originale en une forme équivalente.
Ceci
|
F IN (V1, V2, ... VN) |
Est transformé en
|
(F = V1) OR (F = V2) OR .... (F = VN) |
Depuis Firebird 5.0, le traitement des prédicats IN <list> est linéaire. La limite de 1 500 objets a été augmentée à 65 535 objets. De plus, les requêtes utilisant le prédicat IN avec une liste de constantes sont traitées beaucoup plus rapidement. Cela a été discuté en détail dans la première partie.
I-I. Package RDB$BLOB_UTIL▲
Les opérations avec les BLOB dans PSQL n'étaient pas rapides, car toute modification d'un BLOB crée toujours un nouveau BLOB temporaire, ce qui entraîne une consommation de mémoire supplémentaire et, dans certains cas, un fichier de base de données plus volumineux pour stocker les BLOB temporaires. Dans Firebird 4.0.2, une fonction intégrée, BLOB_APPEND, a été ajoutée pour résoudre les problèmes de concaténation de BLOB. Dans Firebird 5.0, un package RDB$BLOB_UTIL intégré a été ajouté avec des procédures et des fonctions pour une manipulation plus efficace des BLOB.
Nous montrerons plusieurs exemples pratiques d'utilisation des fonctions du package RDB$NLOB_UTIL. La description complète peut être trouvée dans les notes de version de Firebird 5.0 et dans la référence du langage SQL de Firebird 5.0.
I-I-1. Utilisation de la fonction RDB$BLOB_UTIL.NEW_BLOB▲
La fonction RDB$BLOB_UTIL.NEW_BLOB crée un nouveau BLOB SUB_TYPE BINARY. Elle renvoie un BLOB adapté à l'ajout de données, à l’instar de BLOB_APPEND.
La différence par rapport à BLOB_APPEND est que vous pouvez définir les paramètres SEGMENTED et TEMP_STORAGE.
La fonction BLOB_APPEND crée toujours des blobs dans un stockage temporaire, ce qui n'est pas toujours la meilleure approche si le blob créé est stocké dans une table permanente, car cela nécessiterait une opération de copie.
Le BLOB renvoyé par RDB$BLOB_UTIL.NEW_BLOB peut être utilisé avec BLOB_APPEND pour ajouter des données, même si TEMP_STORAGE = FALSE.
Tableau 1. Paramètres d'entrée pour la fonction RDB$BLOB_UTIL.NEW_BLOB
|
Paramètre |
Type |
Description |
|---|---|---|
|
SEGMENTED |
BOOLEAN NOT NULL |
Type de BLOB. |
|
TEMP_STORAGE |
BOOLEAN NOT NULL |
Dans quel stockage le BLOB est-il créé ? |
I-I-1-a. Lire des BLOBs par morceaux▲
Lorsque vous deviez lire une partie d'un BLOB, vous utilisiez la fonction SUBSTRING, mais cette fonction présente un inconvénient majeur : elle renvoie toujours un nouveau BLOB temporaire.
Depuis Firebird 5.0, vous pouvez utiliser la fonction RDB$BLOB_UTIL.READ_DATA à cet effet.
Tableau 2. Paramètres d'entrée pour la fonction RDB$BLOB_UTIL.READ_DATA
|
Paramètre |
Type |
Description |
|---|---|---|
|
HANDLE |
INTEGER NOT NULL |
N° du HANDLE de BLOB ouvert |
|
LENGTH |
INTEGER |
Nombre de bytes à lire |
Type de retour :VARBINAIRE(32765)
La fonction RDB$BLOB_UTIL.READ_DATA est utilisée pour lire des éléments de données à partir d'un handle BLOB ouvert avec RDB$BLOB_UTIL.OPEN_BLOB. Lorsque le BLOB a été entièrement lu et qu'il n'y a plus de données, il renvoie NULL.
Si la valeur du paramètre LENGTH est un nombre positif, un VARBINARY de longueur maximale LENGTH est renvoyé.
Si NULL est transmis à LENGTH, un segment BLOB d'une longueur maximale de 32 765 est renvoyé.
Lorsque vous avez terminé avec un handle de BLOB, vous devez le fermer à l'aide de la procédure RDB$BLOB_UTIL.CLOSE_HANDLE.
Exemple 4. Ouvrir un BLOB et le renvoyer morceau par morceau dans EXECUTE BLOCK
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
execute block returns (s varchar(10))
as
declare b blob = '1234567';
declare bhandle integer;
begin
-- opens a BLOB for reading and returns its handle.
bhandle = rdb$blob_util.open_blob(b);
-- Getting the blob in parts
s = rdb$blob_util.read_data(bhandle, 3);
suspend;
s = rdb$blob_util.read_data(bhandle, 3);
suspend;
s = rdb$blob_util.read_data(bhandle, 3);
suspend;
-- When there is no more data, NULL is returned.
s = rdb$blob_util.read_data(bhandle, 3);
suspend;
-- Close the BLOB handle.
execute procedure rdb$blob_util.close_handle(bhandle);
end
En passant la valeur NULL en paramètre LENGTH, vous pouvez lire un BLOB segment par segment, si les segments ne dépassent pas 32765 octets.
Écrivons une procédure pour renvoyer un BLOB segment par segment
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
CREATE OR ALTER PROCEDURE SP_GET_BLOB_SEGEMENTS (
TXT BLOB SUB_TYPE TEXT CHARACTER SET NONE
)
RETURNS (
SEG VARCHAR(32765) CHARACTER SET NONE
)
AS
DECLARE H INTEGER;
BEGIN
H = RDB$BLOB_UTIL.OPEN_BLOB(TXT);
SEG = RDB$BLOB_UTIL.READ_DATA(H, NULL);
WHILE (SEG IS NOT NULL) DO
BEGIN
SUSPEND;
SEG = RDB$BLOB_UTIL.READ_DATA(H, NULL);
END
EXECUTE PROCEDURE RDB$BLOB_UTIL.CLOSE_HANDLE(H);
END
On peut l'utiliser par exemple comme ceci :
2.
3.
4.
5.
6.
7.
8.
9.
WITH
T AS (
SELECT LIST(CODE_HORSE) AS B
FROM HORSE
)
SELECT
S.SEG
FROM T
LEFT JOIN SP_GET_BLOB_SEGEMENTS(T.B) S ON TRUE
II. Résumé▲
Les nouvelles commandes SQL de Firebird 5.0 se concentrent sur une commodité accrue pour les développeurs (MERGE, SKIP LOCK), la prise en charge de grandes bases de données (index PARTIAL) et des performances accrues (RDB$BLOB_UTILS) de grandes bases de données avec des tables très longues.
Dans la partie suivante, nous examinerons plus en détail l’utilisation pratique de SKIP LOCK.
Veuillez envoyer toutes vos questions concernant ce document à Alexey Kovyazin sur firebirdsql.org.
III. Remerciements Developpez.com▲
Vous trouverez la version originale an anglais de cet article à cette adresse :
Detailed New Features of Firebird 5. Part 2: SQLs
Nous remercions le support IBSurgeon qui nous en a autorisé la traduction.
Auteur : Denis Simonov, adapté et édité par Alexey Kovyazin version 1.0 anglaise du 03.12.2023
Nous remercions également Escartefigue pour la mise au gabarit, la traduction et la relecture orthographique et SergioMaster pour la relecture technique.