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

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.

Article lu   fois.

L'auteur

Site personnelSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

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 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 :

 
Sélectionnez

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 :

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é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 :

 
Sélectionnez

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 :

Image non disponible

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 :

Image non disponible

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

Image non disponible

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 :

 
Sélectionnez

-- 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);
 
 
Sélectionnez

-- 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'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 :
 
Sélectionnez

    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 :
 
Sélectionnez

    Avec le recul sur une telle architecture,
    Quelle est la chose que tu aurais fait autrement ?
David :
 
Sélectionnez

    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 :
 
Sélectionnez

    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 :
 
Sélectionnez

    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 :
 
Sélectionnez

    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) :
 
Sélectionnez

    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
 
Sélectionnez

    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.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

Livres
SQL - développement
SQL - le cours de référence sur le langage SQL
Avant d'aborder le SQL
Définitions
SGBDR fichier ou client/serveur ?
La base de données exemple (gestion d'un hôtel)
Modélisation MERISE
Mots réservés du SQL
Le SQL de A à Z
Les fondements
Le simple (?) SELECT
Les jointures, ou comment interroger plusieurs tables
Groupages, ensembles et sous-ensembles
Les sous-requêtes
Insérer, modifier, supprimer
Création des bases
Gérer les privilèges ("droits")
Toutes les fonctions de SQL
Les techniques des SGBDR
Les erreur les plus fréquentes en SQL
Les petits papiers de SQLPro
Conférence Borland 2003
L'héritage des données
Données et normes
Modélisation par méta données
Optimisez votre SGBDR et vos requêtes SQL
Le temps, sa mesure, ses calculs
QBE, le langage de ZLOOF
Des images dans ma base
La jointure manquante
Clefs auto incrémentées
L'indexation textuelle
L'art des "Soundex"
Une seule colonne, plusieurs données
La division relationnelle, mythe ou réalité ?
Gestion d'arborescence en SQL
L'avenir de SQL
Méthodes et standards
Les doublons
SQL Server
Eviter les curseurs
Un aperçu de TRANSACT SQL V 2000
SQL Server 2000 et les collations
Sécurisation des accès aux bases de données SQL Server
Des UDF pour SQL Server
SQL Server et le fichier de log...
Paradox
De vieux articles publiés entre 1995 et 1999 dans la défunte revue Point DBF

  

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.