15.2. Lesson: Implémenter le modèle de données¶

15.2.1. Installer PostgreSQL¶

Avec Ubuntu

sudo apt-get 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 dispose d’une excellente documentation en ligne : <http://www.postgresql.org/docs/9.1/static/index.html>`_.

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 quitter.

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 quitter.

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 Psql cheat sheet - disponible en ligne ici.

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 à la base de données adresse

psql address

puis créez la 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 pouvez remarquer 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 votre 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.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 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élerer les recherches sur les noms des personnes. Pour y parvenir, nous pouvons créer un index sur la colonnne 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;

Si vous avez utilisé la même commande drop table sur la table people, elle devrait avoir réussi:

drop table people;

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-get 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.