15.2. Lesson: Implémenter le modèle de données
Maintenant que toutes les notions théoriques ont été abordées, passons à l’étape de la création d’une base de donnés. Cette base de données servira à tous les exercices de toutes les leçons à venir.
Le but de cette leçon: Installer les logiciels requis et les utiliser pour mettre en place notre base de données exemple.
15.2.1. Installer PostgreSQL
Note
Vous trouverez les paquets PostgreSQL et les instructions d’installation sur votre système d’exploitation à https://www.postgresql.org/download/. Veuillez noter que la documentation ci-après suppose que les utilisateurs utilisent QGIS sous Ubuntu.
Avec Ubuntu :
sudo apt install postgresql-9.1
Vous devriez obtenir un message similaire à celui-ci:
[sudo] password for qgis:
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
postgresql-client-9.1 postgresql-client-common postgresql-common
Suggested packages:
oidentd ident-server postgresql-doc-9.1
The following NEW packages will be installed:
postgresql-9.1 postgresql-client-9.1 postgresql-client-common postgresql-common
0 upgraded, 4 newly installed, 0 to remove and 5 not upgraded.
Need to get 5,012kB of archives.
After this operation, 19.0MB of additional disk space will be used.
Do you want to continue [Y/n]?
Appuyez sur Y et sur Entrée et patientez jusqu’à ce que le téléchargement et l’installation soit terminés.
15.2.2. Aide
PostgreSQL a une très bonne documentation online.
15.2.3. Créer une base de données utilisateur
Avec Ubuntu :
Une fois l’installation complète, exécuter cette commande pour devenir l’utilisateur de postgres puis créez une nouvelle base de données utilisateur :
sudo su - postgres
Saisissez votre mot de passe normalement lorsqu’il vous ait demandé (vous aurez besoin des droits sudo).
Maintenant, à la vue de l’invite de commande postgres, créez la base de données utilisateur. Vérifiez bien que le nom d’utilisateur correspond à votre nom de session unix : cela vous facilitera la vie, car postgres vous identifiera automatiquement lorsque vous vous connecterez avec cet identifiant :
createuser -d -E -i -l -P -r -s qgis
Saisissez un mot de passe lorsque c’est demandé. Il est conseillé d’utiliser un mot de passe différent de celui de votre session.
Que signifient ces options?
-d, --createdb role can create new databases
-E, --encrypted encrypt stored password
-i, --inherit role inherits privileges of roles it is a member of (default)
-l, --login role can login (default)
-P, --pwprompt assign a password to new role
-r, --createrole role can create new roles
-s, --superuser role will be superuser
Vous devriez maintenant quitter l’environnement postgres en tapant :
exit
15.2.4. Vérifier le nouveau compte
psql -l
devrait renvoyer quelque chose comme ceci:
Name | Owner | Encoding | Collation | Ctype |
----------+----------+----------+------------+------------+
postgres | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
template0 | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
template1 | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
(3 rows)
Tapez Q pour sortir.
15.2.5. Créer une base de données
La commande createdb est utilisée pour créer une nouvelle base de données. Elle doit être lancée à l’invite de commande suivant :
createdb address -O qgis
Vous pouvez vérifier l’existence de votre nouvelle base de données avec cette commande:
psql -l
qui devrait renvoyer quelque chose comme ceci:
Name | Owner | Encoding | Collation | Ctype | Access privileges
----------+----------+----------+------------+------------+-----------------------
address | qgis | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
postgres | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 |
template0 | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 | =c/postgres: postgres=CTc/postgres
template1 | postgres | UTF8 | en_ZA.utf8 | en_ZA.utf8 | =c/postgres: postgres=CTc/postgres
(4 rows)
Tapez Q pour sortir.
15.2.6. Lancer une session de shell de base de données
Vous pouvez facilement vous connecter à votre base de données comme cela
psql address
Pour quitter la base de données de l’environnement psql, saisissez
\q
Pour obtenir de l’aide dans l’interface système, saisissez
\?
Pour obtenir de l’aide sur les commandes sql, saisissez
\help
Pour obtenir de l’aide spécifique sur une commande, saisissez (par exemple)
\help create table
voir aussi la feuille Psql.
15.2.7. Créer des tables en SQL
Commençons à créer quelques tables ! Nous utiliserons notre modèle entité-relation comme guide. D’abord, connectez-vous à l’adresse de la base de données:
psql address
Puis créez une table streets
create table streets (id serial not null primary key, name varchar(50));
serial et varchar sont des types de données. serial indique à PostgreSQL de démarrer une séquence d’entier (numérotation automatique) pour remplir le champ id automatiquement à chaque enregistrement. varchar(50) indique à PostgreSQL de créer un champ de caractère de 50 caractères de long.
Vous remarquerez que les commandes se terminent par le caractère ;. Toutes les commandes SQL se terminent ainsi. Lorsque vous appuyez sur Entrée, psql renvoie la sortie suivante:
NOTICE: CREATE TABLE will create implicit sequence "streets_id_seq"
for serial column "streets.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"streets_pkey" for table "streets"
CREATE TABLE
Ça signifie que votre table a été créée avec succès et possède une clé primaire streets_pkey basée sur streets.id.
Note: si vous avez appuyé sur Entrée sans avoir saisi un ; alors vous aurez un prompt de la forme address-#. PG s’attend à ce que vous terminiez votre commande. Entrez ; avant de la lancer.
Pour visualiser le schéma de votre table, vous pouvez saisir:
\d streets
Ce qui vous renverra quelque-chose comme:
Table "public.streets"
Column | Type | Modifiers
--------+-----------------------+--------------------------------------
id | integer | not null default
| | nextval('streets_id_seq'::regclass)
name | character varying(50) |
Indexes:
"streets_pkey" PRIMARY KEY, btree (id)
Pour visualiser le contenu de votre table, vous pouvez utiliser ceci:
select * from streets;
Ce qui vous renverra quelque-chose comme:
id | name
---+------
(0 rows)
Comme vous pouvez le voir, notre table est actuellement vide.
Try Yourself 
Utilisez l’approche présentée ci-dessus pour créer une table des personnes appelées:
Ajouter des champs tels que le numéro de téléphone, l’adresse résidentielle, le nom, etc. (ces noms ne sont pas valides, arrangez-le pour qu’ils le soient). Assurez-vous de donner une colonne d’identifiant à la table avec le même type de données que plus haut.
15.2.8. Créer des clefs en SQL
Le problème de notre précédente solution est que la base de données ne connais pas les relations logiques entre les personnes et les rues. Pour l’exprimer de manière formelle, nous devons définir des clefs étrangères qui pointent vers la clef primaire de la table des rues.
Il y a deux manières de le faire:
Ajouter la clef après que la table ait été créée.
Définir la clef lors de la création de la table.
Notre table a déjà été créée, utilisons donc la première méthode:
alter table people
add constraint people_streets_fk foreign key (street_id) references streets(id);
Cela indique à la table people que le champ street_id doit être un id valide de la table streets.
La méthode la plus courante pour créer une contrainte consiste à le faire au moment de la création de la table:
create table people (id serial not null primary key,
name varchar(50),
house_no int not null,
street_id int references streets(id) not null,
phone_no varchar null);
\d people
Après avoir ajouté la contrainte, notre table ressemble à:
Table "public.people"
Column | Type | Modifiers
-----------+-----------------------+---------------------------------
id | integer | not null default
| | nextval('people_id_seq'::regclass)
name | character varying(50) |
house_no | integer | not null
street_id | integer | not null
phone_no | character varying |
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"people_streets_fk" FOREIGN KEY (id) REFERENCES streets(id)
15.2.9. Créer des index en SQL
Nous voulons accélérer les recherches sur les noms des personnes. Pour y parvenir, nous pouvons créer un index sur la colonne de nom de notre table de personnes:
create index people_name_idx on people(name);
\d people
Qui résulte dans:
Table "public.people"
Column | Type | Modifiers
-----------+-----------------------+-----------------------------------
id | integer | not null default nextval
| | ('people_id_seq'::regclass)
name | character varying(50) |
house_no | integer | not null
street_id | integer | not null
phone_no | character varying |
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
"people_name_idx" btree (name) <-- new index added!
Foreign-key constraints:
"people_streets_fk" FOREIGN KEY (id) REFERENCES streets(id)
15.2.10. Supprimer des tables en SQL
Si vous voulez supprimer une table, vous pouvez utiliser la commande drop:
drop table streets;
Note
Dans l’exemple en cours, la commande ci-dessus ne fonctionnera pas. Pourquoi? Voir la raison
Si vous avez utilisé la même commande drop table sur la table people, elle devrait avoir réussi:
drop table people;
Note
Si vous avez entré cette commande et supprimé la table people, il sera bon de la recréer car vous en aurez besoin pour les prochains exercices.
15.2.11. Un mot sur pgAdmin III
Nous vous avons montré des commandes SQL lancées depuis le prompt psql car c’est un moyen très efficace d’apprendre à utiliser des bases de données. Néanmoins, il existe des moyens plus rapides et plus simples pour le faire. Installez pgAdmin III et vous pourrez créer, supprimer, modifier les tables en cliquant dans une interface graphique.
Sous Ubuntu, vous pouvez l’installer comme suit:
sudo apt install pgadmin3
PgAdmin III sera détaillé dans un prochain module.
15.2.12. In Conclusion
Vous avez étudié comment créer une nouvelle base de données à partir de rien.
15.2.13. What’s Next?
Dans le prochain chapitre, nous allons étudier comment utiliser une base de données spatiale pour ajouter de nouvelles données.