Le multimedia dans l'objet-relationnel

Les types SQL3 pour le multimedia

SQL3 a défini des types de données "classiques" :

• CHAR(n) : chaîne de carractères de longueur n
• CHAR VARYING(n) : chaîne de caractères de longueur maximale n [ Oracle nomme ce type VARCHAR2(n) ]
• INTEGER : nombre entier [Oracle nomme ce type NUMBER ]
• NUMERIC(p,q) ; nombre décimal de p chiffres avec q chiffres décimaux [Oracle nomme ce type NUMBER(p,q)]
• FLOAT: nombre flottant [pas d'équivalent chez Oracle]
• DATE : date
• TIME : heure
• TIMESTAMP : date et heure [pas d'équivalent chez Oracle]

Il a aussi introduit des types de données se référant à des objets multimédias. Ces types sont repris dans Oracle :

• BLOB : objet codé en binaire (Binary Large Object)
• CLOB : objet codé en caractères (Character Large Object)
• NCLOB : objet codé en caractères "nationaux" (National Character Large Objet)
• BFILE : fichier binaire externe (Binary File)

BLOB, CLOB, NCLOB (les "LOB" ) correspondent à des documents multimédias. BLOB est utilisé pour des fichiers binaires de type image, audio ou vidéo. CLOB et NCLOB sont utilisés pour des données texte volumineuses. Dans tous les cas, le stockage comprend un "LOB-locator", qui est un pointeur vers les données multimédias, et les données multimédias elles-mêmes, "LOB-value". Dans Oracle, si la taille du LOB est inférieure à 4Ko, le LOB-locator et la LOB-value sont stockés directement dans la table qui les réfère. Sinon la table ne contient que le LOB-locator et la LOB-value est stockée en dehors de la table (mais dans la base de données cependant). La taille d'un LOB peut aller jusqu'à 128 To, mais tout dépend du SGBD utilisé (4Go pour Oracle)

A contrario BFILE désigne des fichiers représentant des objets multimédias stockés à l'extérieur de la base de données. Dans la base de données BFILE correspond à un pointeur (BFILE-locator) permettant de trouver l'objet extérieur à la base de données (sous forme d'un fichier binaire).

Exemple 1 : trombinoscope : on souhaite créer une table répertoriant des personnes avec leur nom, leur prénom et leur photographie. On ajoutera un numéro identificateur pour distinguer les personnes. On a donc une relation PERSONNE (numero, nom, prenom, photo) dont la vue conceptuelle est la suivante :

CREATE TABLE personne
(
          numero NUMBER PRIMARY KEY,
          nom VARCHAR2(20),
          prenom VARCHAR2(20),
          photo BLOB
);

On a ici choisi de stocker les photographies dans la base de données. Bien entendu, dans la table, on n'a pas les photos directement (comme dans l'image précédente) mais en général des LOB-locators.

Exemple 2 : catalogue de produit --> table PRODUIT (numero, nom, description, commentaire, photo) où description est un long texte, commentaire une plage sonore et photo une image.

CREATE TABLE produit
(
          numero NUMBER PRIMARY KEY,
          nom VARCHAR2(30),
          description CLOB,
          commentaire BLOB,
          photo BFILE
);

On a ici choisi de stocker les photos à l'extérieur de la base de données mais pas les sons, ni les textes. Comme dans l'exemple précédent, dans les champs description, commentaire, photo on trouve des LOB-locators qui pointent vers les objets concernés (sauf si la taille des CLOB et BLOB est inférieure à 4 Ko).

exemple avec Oracle XE :

Manipulation des données multimédias (Oracle)

Considérons la création de table précédente :

CREATE TABLE produit
(
          numero NUMBER PRIMARY KEY,
          nom VARCHAR2(30),
          description CLOB,
          commentaire BLOB,
          photo BFILE
);

et examinons les possibilités de manipulation des données par type de donnée en supposant que les champs numero et nom sont déjà renseignés..

CLOB, NCLOB

Commençons par initialiser à "vide" le champ description avec la fonction EMPTY_CLOB(). Par exemple,

UPDATE produit
SET description = EMPTY_CLOB( )
WHERE nom = "aspirateur";

La fonction EMPTY_CLOB( ) initialise un champ vide pour un CLOB ou un NCLOB. Pour les BLOB, on a une fonction équivalente EMPTY_BLOB( ).

Pour insérer un CLOB ou un NCLOB dont la taille est inférieure à 4ko, l'utilisation de SQL est très simple : il suffit de mettre à jour le champ correspondant au CLOB.

Par exemple

UPDATE produit SET description = 'Puissance 2000 W - Dépression 29 kPa - Débit d air 29 dm3/s - Décibel 79 dB(A) - Capacité de la cassette 2 litres - Rayon d action 8 m - XYZ 8' WHERE numero = 4;

et si l'on voulait insérer un nouvel enregistrement avec une valeur pour le CLOB :

INSERT INTO produit (numero, nom, description) VALUES (5, 'mixeur', 'Blablablablablablabla');

exemple avec OracleXE :

Pour lire un CLOB dont la taille est inférieure à 4 ko, un simple SELECT suffit car le type est équivalent à un VARCHAR :

SELECT description FROM produit WHERE numero = 1;

exemple avec OracleXE :

Cependant, dans le cas général, Pour accéder à un LOB il faut utiliser les fonctions du paquetage DBMS_LOB dont voici un extrait :

ainsi que la fonction d'écriture DBMS_OUTPUT.PUT_LINE().

Comme exemple, identifions la taille des CLOB de la table produit. Il faut pour cela créer une procédure en PL/SQL :

CREATE OR REPLACE PROCEDURE taille_clob(num IN INTEGER)
AS
v_clob CLOB;
BEGIN
SELECT description INTO v_clob FROM produit WHERE numero=num;
DBMS_OUTPUT.PUT_LINE('La taille du CLOB est : ' || DBMS_LOB.GETLENGTH(v_clob));
END;

On obtiendrait avec Oracle XE :

Pour lire le "champ" description de la table produit pour numero = 4, il faut aussi créer des procédures avec le langage PL/SQL. Tout d'abord, il faut récupérer le LOB-locator avec la procédure req_clob(v_clob, num) qui à partir de la valeur num du numéro de produit fournit le LOB_locator v_clob :

CREATE OR REPLACE PROCEDURE req_clob(v_clob IN OUT CLOB, num IN INTEGER) 
IS
BEGIN
          SELECT description INTO v_clob FROM produit WHERE numero =num;
END;

Puis avec le LOB-locator, on accède au CLOB avec la procédure Read_description (num)

create or replace PROCEDURE Read_description(num IN INTEGER)
IS
          loc_clob CLOB;
          buffer VARCHAR2(400);
          position INTEGER := 1;
          taille INTEGER := 400;
BEGIN
          req_clob(loc_clob,num);
          buffer :=DBMS_LOB.GETLENGTH(loc_clob);
          DBMS_LOB.READ(loc_clob, taille, position, buffer);
          DBMS_OUTPUT.PUT_LINE('buffer = ' || buffer);
          DBMS_OUTPUT.PUT_LINE('taille = ' || taille);
END;

Nous avons choisi un buffer de taille 400 car la longueur des chaînes est inférieure à cette valeur. Pour manipuler les données issues de la base de données, on utilise les fonctions

On obtiendrait avec OracleXE :

Si l'on prend un très grand texte (entre 4Ko et 4Go) il sera stocké dans la base, mais pas dans la table. Nous ne considérerons pas ce cas ici car il nous entrainerait dans des spécificités complexes d'Oracle.

BFILE

Rappelons que le type BFILE correspond à des fichiers binaires enregistrés à l'extérieur de la base de données. La première chose à faire est de créer un alias du répertoire où se trouve le fichier binaire.

CREATE DIRECTORY MEDIAS AS 'C:\MEDIAS';

Bien entendu, il faut les droits pour créer le répertoire et y accéder (GRANT ..... TO......). Ceci peut être effectué avec une ligne de commande SQL en se connectant comme administrateur de la base (on suppose que son login est SYSTEM et que son mot de passe est SYSTEM ; par ailleurs l'utilisateur est par exemple TARTARIN) :

SQL> connect SYSTEM/SYSTEM
Connected
SQL> GRANT CREATE ANY DIRECTORY TO TARTARIN;
Grant succeeded

Après avoir créé la DIRECTORY MEDIAS, on peut ensuite insérer les images (définies par l'attribut photo) dans la table produit :

UPDATE produit SET photo=BFILENAME('MEDIAS','lave-linge.png') WHERE numero=1;
UPDATE produit SET photo=BFILENAME('MEDIAS','four.png') WHERE numero=2;
UPDATE produit SET photo=BFILENAME('MEDIAS','grille-pain.png') WHERE numero=3;
UPDATE produit SET photo=BFILENAME('MEDIAS','aspirateur.png') WHERE numero=4;

Pour manipuler les objets de type BFILE on utilise aussi des fonctions DBMS_LOB spécifiques qui consistent à ouvrir un fichier, charger un fichier et fermer un fichier. Toute opération doit ouvrir et fermer un fichier. L'opération de fermeture peut être utile car le nombre maximal de fichiers ouverts est limité (par défaut à 10 dans Oracle).

Nous allons utiliser ces fonctions dans le paragraphe suivant.

BLOB

Les BLOBs se manipulent comme les CLOB. Pour insérer un BLOB qui n'est pas un texte (dans notre exemple c'est un fichier son de 8218 Ko), il faut passer par BFILE. On utilise par exemple la procédure suivante :

CREATE OR REPLACE PROCEDURE Insert_blob(num IN INTEGER)
IS
          src_blob BFILE := BFILENAME('MEDIAS','son1.mp3');
          dest_blob BLOB;
BEGIN
          UPDATE produit SET commentaire= EMPTY_BLOB() WHERE numero = num;
          SELECT commentaire INTO dest_blob FROM produit WHERE numero = num;
          DBMS_LOB.OPEN(src_blob, DBMS_LOB.LOB_READONLY);
          DBMS_LOB.LoadFromFile(dest_blob,src_blob,DBMS_LOB.GETLENGTH(src_blob));
          DBMS_LOB.CLOSE(src_blob);
          COMMIT;
END;

BEGIN
          Insert_blob(1);
END;

Bien entendu, on ne peut entendre le son avec la base de données (il faudrait faire un programme incorporant la recherche SQL). Toutefois, on peut vérifier que le BLOB est bien là en affichant sa taille avec la procédure

CREATE OR REPLACE PROCEDURE taille_blob(num IN INTEGER)
IS
v_blob BLOB;
BEGIN
SELECT commentaire INTO v_blob FROM produit WHERE numero=num;
DBMS_OUTPUT.PUT_LINE('La taille du BLOB est : ' || DBMS_LOB.GETLENGTH(v_blob));
END;

exemple avec OracleXE

Le résultat est concordant car on obtient 8414449/1024 = 8127 Ko.

A noter que l'on peut obtenir plus rapidement ce résultat avec :

Test

Choisir la bonne réponse dans les questions suivantes. Une bonne réponse rapporte 1 point, une mauvaise - 1 point. Le choix d'une réponse n'est pas obligatoire. Il peut y avoir plusieurs bonnes réponses.