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

Although outside the scope of this document, Mac users can install PostgreSQL using Homebrew. Windows users can use the graphical installer. Please note that the documentation will assume users are running QGIS under 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 has very good online documentation.

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)

Type Q to exit.

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)

Type Q to exit.

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

See also the Psql cheat sheet.

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 :kbd:`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.

15.2.7.1. Try Yourself moderate

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.

Vérifiez vos résultats

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.

../../../_images/er-people-streets.png

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

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.