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éta-données.
Voici un article qui explique comment faire cela avec n'importe quelle base de données relationnelle.
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 jointure ou des filtres. La performance d'une base de données est alors sans communes 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. A 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'information 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 peuvent 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 utilse de collecter dans une base de données. Voici quelques unes des principales caractéristiques des images :
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éta donné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 fichier. Il suffit de connaître le chemin de ce répertoire qui peut être fiche. 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.
Un autre solution consiste à mutiplier 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 enlimitant à 256 images IMG_001 contient les 256 premières images insérées IMG_001 contient les images insérées de la 257eme à la 512eme 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 ou chaque image se situe. Il suffit de rajouter aux caractéristiques déjà spécifiées, la 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 qu'a 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'obsolescense 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 auto ré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èmes de gestion des méta données des images :
Et un modèle physique équivalent en SQL 2 :
A noter que l'auto ré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 talle base et de son référentiel de données :
-- CRÉATION DES TABLES-- ============================================================-- Table : T_PARAM_BASE_PRB -- ============================================================createtable T_PARAM_BASE_PRB
(
PRB_ID INTEGERnotnull,
PRB_NOM CHAR(256) notnull,
PRB VARCHAR(256) notnull,
primarykey (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 -- ============================================================createtable T_TYPE_IMAGE_TIM
(
TIM_ID INTEGERnotnull,
TIM_FORMAT CHAR(4) notnull,
TIM_LIBELLE VARCHAR(32) notnull,
primarykey (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 -- ============================================================createtable T_STOCKAGE_IMAGE_SIM
(
SIM_ID INTEGERnotnull,
SIM_DATE_CREATION DATEnotnull,
SIM_PATH VARCHAR(1024) notnull,
primarykey (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 -- ============================================================createtable T_IMAGE_IMG
(
IMG_ID INTEGERnotnull,
TIM_ID INTEGERnotnull,
IMG_ID_REMPLACEMENT INTEGER ,
IMG_NOM_FICHIER VARCHAR(256) notnull,
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 INTEGERnotnull,
primarykey (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 -- ============================================================createindex TJ_IMGTIM_FK on T_IMAGE_IMG (TIM_ID asc);
-- ============================================================-- Index : TJ_IMGIMG_FK -- ============================================================createindex TJ_IMGIMG_FK on T_IMAGE_IMG (IMG_ID_REMPLACEMENT asc);
-- ============================================================-- Index : TJ_IMGSIM_FK -- ============================================================createindex TJ_IMGSIM_FK on T_IMAGE_IMG (SIM_ID asc);
altertable T_IMAGE_IMG
add foreign key (TIM_ID)
references T_TYPE_IMAGE_TIM (TIM_ID);
altertable T_IMAGE_IMG
add foreign key (IMG_ID_REMPLACEMENT)
references T_IMAGE_IMG (IMG_ID);
altertable T_IMAGE_IMG
add foreign key (SIM_ID)
references T_STOCKAGE_IMAGE_SIM (SIM_ID);
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'image 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éta données ? Simple, il suffit d'utiliser une tables des utilisateurs (ou en créer une) avec des droits d'accès (lecture pare 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 besoin. 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...
A propos de : "Faut-il insérer des images directement dans une colonne BLOB d'une table ?"
Une discussion a eût 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 huits 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 patients 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 fait 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 multi-mé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é. Ca fait un peu désordre. Cependant,
il serait relativement facile de remédier à cela.
Med :
Quelle est la fonctionnalité que tu aurais souhaitée trouver
dans SQL/Server et qui t'as manquée ?
Ce qui nous a le plus embêté, 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 BLOBs facilement à partir d'ISQL.
Med :
Sur ce sujet, j'ai été franchement impressionné par le 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 oeuvre 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
Controle des acces
Les permissions du SGBDR s'applique 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és 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 de une telle solution et j'espere que
l'on aura bientot 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'iclure 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éfailanece
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.
A 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 diques 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 batiment en fibre L0 et switch à
chaque étage (3 en tout) le tout sur 4 batiments.
Bien entendu, le cout de cette solution était trés élevé. Rien qu'en hard,
on a dépassé les 150000 Euros.
A mon avis, si j'avais aujourd'hui à faire un projet similaire, je referai
sans hésiter la même chose.
Certes, j'adapterai l'aspect hard et développement en fonction du besoin
réel du client mais je conserverai 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.
