Important
La traduction est le fruit d’un effort communautaire auquel vous pouvez vous joindre. Cette page est actuellement traduite à 86.42%.
15.2. Leçon : 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 des paquets PostgreSQL et des instructions d’installation selon votre Système d’exploitation à https://www.postgresql.org/download/. Veuillez noter que la suite de cette documentation supposera une exécution de QGIS avec 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
Then create a streets table:
create table streets (id serial not null primary key, name varchar(50));
serial and varchar are data types. serial tells
PostgreSQL to start an integer sequence (auto-number) to populate the id
automatically for every new record. varchar(50) tells PostgreSQL to
create a character field of 50 characters in length.
You will notice that the command ends with a ; - all SQL commands should
be terminated this way. When you press Enter, psql will report something
like this:
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
That means your table was created successfully, with a primary key streets_pkey
using streets.id.
Note: If you hit return without entering a ;, then you will get a prompt like
this: address-#. This is because PostgreSQL is expecting you to enter more. Enter
; to run your command.
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.
★★☆ Essayez vous-même
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.
Réponse
Le SQL nécessaire pour créer la table correcte des personnes est
create table people (id serial not null primary key,
name varchar(50),
house_no int not null,
street_id int not null,
phone_no varchar null );
Le schéma pour la table (entrer \\d people) ressemble à cela
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)
À des fins d’illustration, nous avons volontairement omis la contrainte fkey.
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);
That tells the people table that its street_id fields must match
a valid street id from the streets table.
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
If you want to get rid of a table you can use the DROP command:
drop table streets;
Dans notre exemple actuel, la commande ci-dessus ne fonctionnerait pas. Pourquoi ?
Réponse
The reason the DROP command would not work in this case is because the
people table has a Foreign Key constraint to the streets table. This means
that dropping (or deleting) the streets table would leave the people table
with references to non-existent streets data.
It is possible to “force” the streets table to be deleted by using the
CASCADE command, but this would also delete the people and any other table
which had a relationship to the streets table. Use with caution!
If you used the same drop table command on the people table, it would
be successful:
drop table people;
Note
If you actually did enter that command and dropped the people
table, now would be a good time to rebuild it, as you will need it in the
next exercises.
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. Conclusion
Vous avez étudié comment créer une nouvelle base de données à partir de rien.
15.2.13. La suite ?
Dans le prochain chapitre, nous allons étudier comment utiliser une base de données spatiale pour ajouter de nouvelles données.