Gérer les images dans une base de données

SQLPro

La plupart des développeurs commentent l'erreur de vouloir gérer des images dans les bases de données en stockant les données binaires représentant les images dans des colonnes de table. Cette gestion grève les performances des serveurs - en plus de s'avérer lente et difficilement exploitable - alors que le stockage des images dans de simples fichiers organisés en répertoire reste encore la meilleure solution.
Encore faut-il pouvoir retrouver ces images et gérer leurs métadonnées.

Voici un article qui explique comment faire cela avec n'importe quelle base de données relationnelle. ♪

- Titre : Gérer les images dans une base de données

- Auteur : SQLPro

- Parution : 26 août 2003

- Licence : Copyright ® 2003 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- Préambule

- 1. Étude des caractéristiques des images

- 2. Stockage des images

- 3. Un premier modèle de données

- 4. La gestion de l'obsolescence

- 5. Script SQL complet

- 6. Aller plus loin

- 7. Discussion sur la solution…

Préambule

Les bases de données relationnelles supposent l'existence de relations entre les données contenues dans des tables, par exemple pour y effectuer des jointures ou des filtres. La performance d'une base de données est alors sans commune mesure avec ce qu'un développeur pourrait faire en n'utilisant que les ressources d'un langage de programmation et de simples fichiers de données. À l'inverse, le stockage de données dont le filtrage ou la jointure ne peut s'opérer directement en SQL diminue très lourdement les performances des SGBDR et constitue un non-sens… Compte tenu du volume important des images, la base se trouve alors polluée avec une quantité d'informations non négligeable dont seule une petite partie est réellement utilisée par le moteur SQL.

Allez-vous un jour poser au moteur SQL de votre SGBDR la requête :

SELECT Image
FROM   TableImage
WHERE  Image contient 'un chien jaune sur une balançoire'

???
Non bien sûr ! Donc il est inutile de s'amuser à stocker des images dans une base de données, d'autant que la manipulation des BLOBS (les colonnes spécialisées pour ce genre de données) ne peut pas la plupart du temps faire l'objet de requêtes SQL basiques…

Il ne faut pas oublier non plus que ce que fait le mieux un ordinateur, c'est justement la manipulation des fichiers… Il y a donc tout à gagner à utiliser un stockage sous forme de fichiers dans l'OS plutôt que de manipuler des flux binaires dans des colonnes de type BLOB.

1 - Étude des caractéristiques des images

En revanche, les images possèdent des caractéristiques (que l'on nomme attributs lorsque l'on modélise) qu'il est souvent utile de collecter dans une base de données.
Voici quelques-unes des principales caractéristiques des images :

FORMAT (CHAR 4)

BMP, JPEG, TIF, GIF…

COULEURS (INTEGER)

2 (noir & blanc), 256 (niveaux de gris), 1024 (couleurs) …

LARGEUR (INTEGER)

la largeur de l'image

HAUTEUR (INTEGER)

la hauteur de l'image

NOM (VARCHAR 256)

le nom du fichier

TAILLE (INTEGER)

le volume des données

DESCRIPTION (VARCHAR 1024)

une description de l'image pouvant faire l'objet d'une requête LIKE

LEGENDE (VARCHAR 256)

légende s'inscrivant sous l'image

HINT (VARCHAR 256)

information apparaissant au survol de la souris

COPYRIGHT (VARCHAR 256)

auteur / propriétaire de l'image

DATE CREATION

date de création

Bien entendu on peut rajouter différentes caractéristiques répondant à ses propres besoins…

Cette approche est souvent appelée technique des métadonnées, parce que l'on ne travaille pas directement sur la donnée, mais sur les caractéristiques, les paramètres de la donnée.

2 - Stockage des images

On peut stocker toutes ses images dans un seul et même répertoire d'un serveur de fichiers. Il suffit de connaître le chemin de ce répertoire qui peut être fixe. Appelons-le « PATH_IMAGE ». Dès lors, pour retrouver l'emplacement d'une image précise, il suffit de concaténer la constante PATH_IMAGE au nom de l'image.

Exemple :

PATH_IMAGE := '\\SRV_files\images\'
BITMAP_FILE := '\\SRV_files\images\' + NOM

et le tour est joué !

Mais cette technique simpliste possède un inconvénient majeur : il peut y avoir trop de fichiers dans le répertoire… N'oublions pas que les systèmes d'exploitation possèdent des limites et même si elles sont très larges, il n'est jamais impossible de les atteindre.

Une autre solution consiste à multiplier le nombre de répertoires et d'y stocker les images, soient par leurs dates d'insertion, soit par un volume ou un nombre prédéfini.

Par date

par exemple par mois + année. Exemple :
IMG_2001_01 pour les images insérées en janvier
IMG_2001_02 pour les images insérées en février
…

Par nombre

par exemple en limitant à 256 images
IMG_001 contient les 256 premières images insérées
IMG_001 contient les images insérées de la 257e à la 512e dans leur ordre d'insertion…

Par volume

par exemple en limitant le volume des images à 600 Mo
IMG_01 contient autant d'images que possible dans la limite de 600 Mo
IMG_02 contient un volume maximal de 600 Mo d'images insérées à la suite du remplissage de IMG_01…

Pour ma part je choisis en général la technique du volume, en la limitant à 600 Mo. Pourquoi ? Parce que 600 Mo c'est un CD Rom et qu'il m'est donc très facile pour l'archivage de graver un CD d'images par répertoire !

Dès lors se pose le problème de savoir où chaque image se situe. Il suffit de rajouter aux caractéristiques déjà spécifiées, le chemin de stockage de chaque image, chemin dont la référence en clair peut être stockée dans une autre table.

3 - Un premier modèle de données

Voici un premier modèle conceptuel des données :

Vous noterez que j'ai rajouté une table de référence pour le format des images (T_TYPE_IMAGE_TIM), cela me permet de créer préventivement tous les formats avec lesquels je désire travailler, ne serait-ce qu'à des fins de contrôles et de validation des données.

Il me faut cependant une variable globale de départ, celle du point d'entrée dans le système de fichier (le « path ») à partir duquel je vais pouvoir créer mes répertoires de stockage. Cela peut être fait dans un fichier INI, dans un outil spécifique au système (la registry Windows par exemple, mais alors plus de portabilité vers un autre OS !) ou bien encore (et c'est ce que je préfère) dans une table de paramétrage de ma base de données…

4 - La gestion de l'obsolescence

Une chose intéressante est de pouvoir gérer l'obsolescence des images. Autrement dit, gérer une image qui n'est plus utilisée, ou bien encore remplacée par une autre.

Pour la suppression, rien de plus simple il suffit de créer une colonne supplémentaire dans la table T_IMAGE_IMG, afin d'y mettre la date de suppression. Ainsi toute requête pour retrouver une image filtrera sur cette date et celle du système. Cela présente l'avantage indéniable de permettre de restituer des pages web obsolètes (archives par exemple) avec les images d'origine plutôt qu'avec des « trous ». Sachez aussi, que de manière générale on évite en matière de bases de données de supprimer les informations.

Pour le remplacement, il suffit de relier la table T_IMAGE à elle-même afin d'informer que l'image visée a été remplacée par une nouvelle. Une simple autoréférence suffit en général. Il faudra donc veiller à ce que cette colonne soit NULL dans les requêtes de recherche, et à défaut, rechercher l'image de remplacement.

Voici donc un modèle plus complet de l'organisation de notre système de gestion des métadonnées des images :

Et un modèle physique équivalent en SQL 2 :

À noter que l'autoréférence sur la table des images pour gérer l'obsolescence a été définie par une colonne de nom IMG_ID_REMPLACEMENT.

5 - Script SQL complet

Voici un script SQL complet pour la création d'une telle base et de son référentiel de données :

-- CRÉATION DES TABLES

-- ============================================================
--   Table : T_PARAM_BASE_PRB
-- ============================================================
create table T_PARAM_BASE_PRB
(
PRB_ID                INTEGER               not null,
PRB_NOM               CHAR(256)             not null,
PRB                   VARCHAR(256)          not null,
primary key (PRB_ID)
);

-- ============================================================
--   Index : T_PARAM_BASE_PRB_PK
-- ============================================================
create unique index T_PARAM_BASE_PRB_PK on T_PARAM_BASE_PRB (PRB_ID asc);

-- ============================================================
--   Table : T_TYPE_IMAGE_TIM
-- ============================================================
create table T_TYPE_IMAGE_TIM
(
TIM_ID                INTEGER               not null,
TIM_FORMAT            CHAR(4)               not null,
TIM_LIBELLE           VARCHAR(32)           not null,
primary key (TIM_ID)
);

-- ============================================================
--   Index : T_TYPE_IMAGE_TIM_PK
-- ============================================================
create unique index T_TYPE_IMAGE_TIM_PK on T_TYPE_IMAGE_TIM (TIM_ID asc);

-- ============================================================
--   Table : T_STOCKAGE_IMAGE_SIM
-- ============================================================
create table T_STOCKAGE_IMAGE_SIM
(
SIM_ID                INTEGER               not null,
SIM_DATE_CREATION     DATE                  not null,
SIM_PATH              VARCHAR(1024)         not null,
primary key (SIM_ID)
);

-- ============================================================
--   Index : T_STOCKAGE_IMAGE_SIM_PK
-- ============================================================
create unique index T_STOCKAGE_IMAGE_SIM_PK on T_STOCKAGE_IMAGE_SIM (SIM_ID asc);

-- ============================================================
--   Table : T_IMAGE_IMG
-- ============================================================
create table T_IMAGE_IMG
(
IMG_ID                INTEGER               not null,
TIM_ID                INTEGER               not null,
IMG_ID_REMPLACEMENT   INTEGER                       ,
IMG_NOM_FICHIER       VARCHAR(256)          not null,
IMG_DATE_CREATION     DATE                          ,
IMG_COULEURS          INTEGER                       ,
IMG_LARGEUR           INTEGER                       ,
IMG_HAUTEUR           INTEGER                       ,
IMG_TAILLE            INTEGER                       ,
IMG_DESCRIPTION       VARCHAR(1024)                 ,
IMG_LEGENDE           VARCHAR(256)                  ,
IMG_HINT              VARCHAR(256)                  ,
IMG_COPYRIGHT         VARCHAR(256)                  ,
IMG_DATE_SUPPRESSION  DATE                          ,
SIM_ID                INTEGER               not null,
primary key (IMG_ID)
);

-- ============================================================
--   Index : T_IMAGE_IMG_PK
-- ============================================================
create unique index T_IMAGE_IMG_PK on T_IMAGE_IMG (IMG_ID asc);

-- ============================================================
--   Index : T_IMAGE_NOM_UNI
-- ============================================================
create unique index T_IMAGE_NOM_UNI on T_IMAGE_IMG (IMG_NOM_FICHIER asc);

-- ============================================================
--   Index : TJ_IMGTIM_FK
-- ============================================================
create index TJ_IMGTIM_FK on T_IMAGE_IMG (TIM_ID asc);

-- ============================================================
--   Index : TJ_IMGIMG_FK
-- ============================================================
create index TJ_IMGIMG_FK on T_IMAGE_IMG (IMG_ID_REMPLACEMENT asc);

-- ============================================================
--   Index : TJ_IMGSIM_FK
-- ============================================================
create index TJ_IMGSIM_FK on T_IMAGE_IMG (SIM_ID asc);

alter table T_IMAGE_IMG
add foreign key  (TIM_ID)
references T_TYPE_IMAGE_TIM (TIM_ID);

alter table T_IMAGE_IMG
add foreign key  (IMG_ID_REMPLACEMENT)
references T_IMAGE_IMG (IMG_ID);

alter table T_IMAGE_IMG
add foreign key  (SIM_ID)
references T_STOCKAGE_IMAGE_SIM (SIM_ID);

-- insertion des formats de fichiers d'image prédéfinis

INSERT INTO T_TYPE_IMAGE_TIM (TIM_ID, TIM_FORMAT, TIM_LIBELLE)
VALUES (1, 'BMP ', 'Microsoft bitmap')
INSERT INTO T_TYPE_IMAGE_TIM (TIM_ID, TIM_FORMAT, TIM_LIBELLE)
VALUES (2, 'GIF ', 'Compuserve GIF')
INSERT INTO T_TYPE_IMAGE_TIM (TIM_ID, TIM_FORMAT, TIM_LIBELLE)
VALUES (3, 'JPG ', 'Internet compressé')
INSERT INTO T_TYPE_IMAGE_TIM (TIM_ID, TIM_FORMAT, TIM_LIBELLE)
VALUES (4, 'JPEG ', 'Internet compressé')

Vous aurez peut-être remarqué que j'ai ajouté subrepticement un index unique sur la colonne IMG_NOM_FICHIER afin d'éviter de tenter d'insérer deux fichiers d'images portant le même nom !

6 - Aller plus loin

Pourquoi ne pas gérer des droits d'accès aux images en utilisant cette technique de métadonnées ?
Simple, il suffit d'utiliser une table des utilisateurs (ou en créer une) avec des droits d'accès (lecture par exemple) et de créer une table de jointure entre la table des images et celle des utilisateurs.

Il existe encore de nombreuses autres possibilités d'exploitation d'un tel modèle et de la technique des données. Je laisse à votre sagacité le soin de le compléter en fonction de vos besoins.
Bien entendu je serais intéressé que vous me fassiez part de vos expériences en la matière…

Au fait, un tel modèle peut aussi être utilisé pour gérer des fichiers de son ou de vidéo… Et pourquoi pas des images enrichies, comme des bitmap vectoriels pour de la cartographie interactive par exemple ?

7 - Discussion sur la solution…

À propos de : « Faut-il insérer des images directement dans une colonne BLOB d'une table ? »

Une discussion a eu lieu sur Internet dans le forum consacré à SQL Server… En voici l'essentiel

David :

Il y a des situations où cela s'avère être une excellente
idée. Notre application (médicale), utilisée (entre autres)
par les huit plus grands hôpitaux de Norvège, stocke les images associées aux patients dans une DB SQL Server.

Nous avons choisi d'utiliser un server dédié (lié) pour
y enregistrer les images, séparé du serveur d'exploitation
contenant les données patient proprement dites et nous
ne ferions marche arrière (retour vers un file system) sous
aucun prétexte.

La gestion des backups est uniforme, l'insertion d'une
image dans un dossier patient peut être partie intégrante
d'une transaction (la solution avec file system obligeant
à recourir à une mécanique beaucoup plus complexe,
basée sur MTS, par exemple), l'écriture de l'application
client en est simplifiée ...

Med :

Avec le recul sur une telle architecture,
Quelle est la chose que tu aurais faite autrement ?

David :

En fait, la gestion des images est arrivée très tard dans
le cycle de développement de notre application. Il y avait
bien la possibilité d'intégrer des éléments multimédias
(rx, protocoles dictés), mais, comparé à la masse totale
des données, c'était plutôt marginal.

La situation a réellement changé  le jour où les hôpitaux
ont souhaité, en plus de l'intégration usuelle des images
(RX, scans, etc.),  scanner leurs documents et les ajouter au
dossier. La proportion data/blobs change alors radicalement.

On se trouve aujourd'hui dans une situation où les
images sont, parfois, en partie stockées dans la DB
d'exploitation (les images historiquement créées avant
que l'on ne prenne en charge le scanning) et en partie
sur un serveur dédié. Ça fait un peu désordre. Cependant,
il serait relativement facile de remédier à cela.

Med :

Quelle est la fonctionnalité que tu aurais souhaité trouver
dans SQL/Server et qui t'a manquée ?

Ce qui nous a le plus embêtés, c'est de devoir procéder à
l'intégration des images au dossier en deux étapes :

1) utiliser bii pour remplir une table temporaire avec le BLOB;
et les informations relatives au patient qu'il concerne
2) lancer un processus utilisant le contenu de cette table
temporaire et "intégrant" l'image au dossier

David :

Il est dommage que l'inclusion d'une image doive se faire par
un outil externe (bii) pas toujours très bien supporté (la
documentation dans le BOL est risible). Il aurait été chouette
de pouvoir disposer d'outils intégrés, SPs, whatever ... permettant
de "jouer" avec les BLOB facilement à partir d'ISQL.

Med :

Sur ce sujet, j'ai été franchement impressionné par la solution
IBM proposée avec DB2
DB2 propose une solution qui allie le meilleur des deux mondes
L'avantage des fichiers gérés par le système de fichiers de l'OS
L'avantage de données gérées par un moteur relationnel
Pour mettre en œuvre cette solution très simple,  DB2 rajoute un
nouveau type de données
Ce type de données, DATALINK, ne fait rien d'autre que référencer
l'url d'un fichier
Mais avec tous les avantages d'un type de données classique d'un SGBDR

Intégrité référentielle
Personne ne peut renommer ou détruire le fichier tant qu'il est
référencé dans la base

Contrôle des accès
Les permissions du SGBDR s'appliquent au système de fichiers !!!

Sauvegarde/Restauration
La sauvegarde se fait par le SGBDR de manière classique
Vous sauvegardez votre base et les fichiers sont sauvegardés

Gestion des transactions
Les modifications de la base et du système de fichiers sont
effectuées dans la même transaction

La bonne nouvelle est que IBM a soumis ce nouveau type de données à
l'ANSI qui l'a adopté pour SQL3
Je pense que l'avenir se situe d’une telle solution et j'espère que
l'on aura bientôt dans les prochaines versions de SQL/Server

Pour plus de détails, faites une recherche sur DB2 DATALINK

Fred (SQLpro) :

En ce qui concerne la solution d'inclure les images dans la base,
voici, pour ma part, les remarques que j'apporte au sujet :

inconvénients :
Sauvegarde partielle sélective difficile, voire impossible.
Portabilité amoindrie.
Récupération de certaines images impossible en cas de défaillance
du Serveur de données.
Récupération des images dans la requête difficile à impossible
(le BDE de Borland le permet, mais pas ODBC ni ADO de Microsoft)

Avantage :
uniformité de la solution

Denis

J'ai moi-même travaillé en tant que Chef de Projet sur un projet d'imagerie
médicale pour une très grosse clinique privée (250 lits).
( 4 mois d'étude, 14 mois de développement)
Les images médicales (en couleur pour certaines) sont énormes (comptez 7 à
10 Mo / image) !
Il était hors de question de les stocker dans la base : trop de charge
serveur, temps de réponse côté client beaucoup trop long.

La solution retenue est la même que celle que tu proposes.
Certains ont réagi à ton post sur l'aspect "sécurité". Quoi de plus
confidentiel que des images médicales ?
Et bien ce fut un jeu d'enfant que de mettre en place une stratégie adaptée.
À l'époque, c'était sur un serveur NT différent de la base.

1 PDC NT 4
1 Serveur NT 4 membre du domaine avec SQL Server 7
1 Serveur NT 4 membre du domaine  en RAID 5 hot-plug avec baie
supplémentaire disponible pour accueillir des disques en plus : stockage des
images + sauvegarde
le nom de chaque image était tout simplement le n° de dossier du patient sur
6 positions suivi d'un numéro d'ordre sur 2 positions.

Réseaux Cat 5 : 100 Mb avec liaison inter-bâtiments en fibre L0 et switch à
chaque étage (3 en tout) le tout sur 4 bâtiments.
Bien entendu, le cout de cette solution était très élevé. Rien qu'en hard,
on a dépassé les 150 000 euros.

À mon avis, si j'avais aujourd'hui à faire un projet similaire, je referais
sans hésiter la même chose.
Certes, j'adapterais l'aspect hard et développement en fonction du besoin
réel du client, mais je conserverais l'externalisation des images.

Ceux qui ne le font pas, c'est parce que trop souvent ils ne connaissent pas
bien les possibilités offertes par Windows ou Novell.