« Programmation SQL/Exemples » : différence entre les versions

==== Modèle de données ====

Créons tout d'abord une représentation d'un utilisateur.

create table utilisateur

(

creation timestamp not null

);

<code>sid</code> est un identifiant dont la particularité est d'être unique et strictement monotone grâce au fait qu'il est basé sur une <code>séquence</code> qui possède les mêmes propriétés. Une séquence est une fonction strictement monotone qui renvoie une liste d'entiers distincts à chaque fois qu'elle est appelée. <code>sid</code> se prête donc particulièrement à une utilisation comme clé primaire. Utiliser des clés primaires arbitraires, c'est-à-dire créer un identifiant spécifiquement à cet usage, plutôt que d'utiliser des attributs uniques existants (comme dans ce cas, l'adresse email) facilite les jointures.

 

Pour s'assurer de l'unicité de l'adresse email dans cette table, ajoutons une contrainte d'unicité sur l'attribut <code>email</code>

 

Créons à présent la table qui comportera toutes les images. Comme nous construisons une bibliothèque qui pourra contenir un grand nombre d'images, nous stockerons ces images en dehors du SGBD et ne ferons figurer que le chemin qui mène au fichier qui contient l'image. Il est possible de stocker les images elles-mêmes dans la base mais cela conduira à limiter la capacité du SGBD, qui, sous une charge importante, passera un certain temps à extraire les images de ses tables et limitera le trafic maximum.

create table image

(

creation timestamp not null

);

Associons maintenant images et utilisateurs.

create table image_utilisateur

(

utilisateur_sid integer not null

);

Pour être sûr que <code>image_sid</code> et <code>utilisateur_sid</code> pointent vers des objets existants, ajoutons 2 contraintes d'intégrité référentielle.

alter table image_utilisateur add constraint image_utilisateur_utilisateur_fk

foreign key (utilisateur_sid) references utilisateur(sid);

alter table image_utilisateur add constraint image_utilisateur_image_fk

foreign key (image_sid) references image(sid);

 

Ajoutons des mots-clés :

create table mot_cle

(

creation timestamp not null

);

Et associons-les aux images :

create table image_mot_cle

(

mot_cle_sid integer not null

);

Même traitement pour éviter les mots-clés inexistants attachés à des images inexistantes :

alter table image_mot_cle add constraint image_mot_cle_mot_cle_fk

foreign key (mot_cle_sid) references mot_cle(sid);

alter table image_mot_cle add constraint image_mot_cle_image_fk

foreign key (image_sid) references image(sid);

Dans cet exemple, nous omettons de créer des index puisque ceux-ci, quoique qu'indispensables pour obtenir une performance correcte lorsque la [[w:cardinalité|cardinalité]] des relations dépassent la dizaine ou la centaine, ne modifient en rien le résultat.

 

==== Exemples de requête ====

Nous avons besoin de données pour ces exemples.

insert into utilisateur (prenom, nom, email, creation) values ('Dupont', 'Dupond', 'dupont@dupond.fr', now());

insert into utilisateur (prenom, nom, email, creation) values ('Durant', 'Durand', 'durant@durand.fr', now());

insert into mot_cle (nom, creation) values ('populaire', now());

insert into image_mot_cle (image_sid, mot_cle_sid) values (3, 1);

Combien d'images appartiennent-elles à l'utilisateur dont l'email est "dupont@dupond.fr" ?

select count(*)

from utilisateur u

join image_utilisateur iu on (u.sid = iu.utilisateur_sid)

where u.email = 'dupont@dupond.fr';

Quelles sont les 10 images les plus partagées ?

select i.chemin, i.nom, count(*) as partage

from image i

order by count(*) desc

limit 10;

Quel est l'utilisateur avec le plus d'images ?

select u.prenom, u.nom

from utilisateur u

order by count(*) desc

limit 1;

Quelles sont les images qui n'appartiennent à personne ?

select i.chemin, i.nom

from image i

left outer join image_utilisateur iu on (i.sid = iu.image_sid)

where iu.utilisateur_sid is null;

Ajoutons le mot-clé "wiki" à toutes les images de "dupont@dupond.fr".

insert into image_mot_cle (image_sid, mot_cle_sid)

select iu.image_sid, mc2.sid

and mc2.nom = 'wiki'

group by mc2.sid, iu.image_sid;

Dans cette dernière requête, nous voulons éviter les images qui sont déjà associées au mot-clé "wiki", tout en conservant les images sans mot-clé ou associées à d'autres mot-clés, le tout pour l'utilisateur en question.