Le Système de Gestion de Bases de Données PostgreSQL de la distribution CNRS-ENSTA

Sovanna TAN

Université Paris XII-Val de Marne

Ce document est disponible en ligne sur ftp://ftp.ensta.fr/pub/linux/applis.rh50/postgres.html

La distribution CNRS-ENSTA contient un SGBD relationnel orienté objets appelé postgreSQL, version 6.2.1.¹ Ce système est dérivé du SGBD POSTGRES, prototype de recherche conçu sous la direction du Professeur Michael STONEBRAKER à l'université de Berkeley. Dans cet article, nous décrivons

l'utilisation et l'administration de postgreSQL,
ses facilités d'extension avec des types utilisateurs et des fonctions,
son API en langage C, libpq,
son interface avec Tcl/Tk,
son interface avec Java avec la connexion JDBC,
la documentation disponible.

Signalons qu'outre les interfaces détaillées ici, les sources de postgreSQL contiennent aussi des interfaces pour les langages C++ et Perl 5. Seules les installations des interfaces avec Tcl/TK et Java nécessitent des ressources qui ne sont pas directement disponibles sur le cdrom CNRS-ENSTA. Sauf mention explicite, les commandes décrites dans les quatre premières parties peuvent s'effectuer sur une machine qui n'est pas mise en réseau.

Utilisation et administration

Le système fonctionne sur le modèle client/serveur. Un programme démon postmaster reçoit les requêtes des clients concernant les bases de données. La connexion est établie au moyen de sockets TCP. Lors d'une demande de connexion, le démon se duplique, un des programmes continue à écouter les demandes de connexion, l'autre traite les requêtes du client. Sur un réseau TCP/IP, les client peuvent se trouver sur des machines distinctes de celle sur laquelle tourne le serveur et dialoguer avec ce dernier à travers le réseau.

Le serveur postmaster est lancé au boot par le script /etc/rc.d/init.d/postgresql. Les fichiers exécutables pour postgreSQL se trouvent dans /usr/bin et les autres dans le répertoire /var/lib/pgsql.

Création des utilisateurs et des bases de données

Initialement, seul l'utilisateur postgres a le droit d'utiliser le système. Mais il peut créer des utilisateurs avec qui partager ses pouvoirs ou des utilisateurs ayant des droits plus restreints. Comme un utilisateur de postgreSQL peut lier dynamiquement du code au moteur du système, pour des raisons de sécurité, root n'a pas le droit d'utiliser postgreSQL. Seuls les utilisateurs qui ont le droit d'en ajouter d'autres ont le droit de charger des fonctions écrites en C.

# su postgres
bash$ createuser  pierre
Enter user's postgres ID or RETURN to use unix user ID: 502 -> 
Is user "pierre" allowed to create databases (y/n) y
Is user "pierre" allowed to add users? (y/n) y
createuser: pierre was successfully added

L'utilisateur pierre peut ensuite créer une base avec

bash$ createdb mabase

On dispose du client psql pour travailler sur les bases auxquelles on a accès.

bash$ psql mabase
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
   You are currently connected to the database: mabase

mabase=>

Manipulation des tables d'une base

On définit une table avec ²

mabase=> create table journaux ( nom varchar(40), jour date)\g
CREATE

On la remplit.

mabase=> insert into journaux values ('Liberation','4/22/98')\g
INSERT 17601 1
mabase=> insert into journaux values ('Monde','4/22/98')\g
INSERT 17602 1
mabase=> insert into journaux values ('Parisien','4/21/98')\g
INSERT 17603 1

On peut la consulter.

mabase=> select nom from journaux where jour='4/22/98/'\g
nom       
----------
Liberation
Monde     
(2 rows)

Le langage supporté est un sous ensemble de SQL-92. En plus du modèle relationnel standard, postgreSQL permet de définir des tables à partir d'autres tables par héritage. Il permet aussi de définir des attributs qui ne sont pas atomiques mais des tableaux.

mabase=> create table journ_spec(theme char16[]) inherits (journaux)\g
CREATE
mabase=>  insert into journ_spec values ('Byte','4/1/98','{"informatique","pc"}')\g
INSERT 17660 1

On peut effectuer des commandes sur l'ensemble des tables dérivées d'une table.

mabase=> select * from journaux* \g
nom       |      jour
----------+----------
Liberation|04-22-1998
Monde     |04-22-1998
Parisier  |04-21-1998
Byte      |04-01-1998
(4 rows)

Certaines fonctionnalités comme les sous requêtes ou la clause HAVING dans un un GROUP BY manquent. Toutefois, l'utilisateur a la possibilité d'ajouter au système des fonctions écrites en SQL ou en C.

Etablissement d'une connexion à travers un réseau TCP/IP

Par défaut, seuls les clients fonctionnant sur la même machine que postmaster peuvent se connecter aux bases de données. Pour autoriser les clients tournant sur une autre machine, il faut déclarer cette dernière dans le fichier /var/lib/pgsql/pg_hba.conf. Supposons que l'on veuille autoriser des programmes clients tournant sur une machine appelée machine1 dont l'adresse IP est 192.168.25.1 à se connecter au serveur qui tourne sur une machine appelée serveur1. Supposons en outre que les numéros des utilisateurs de postgreSQL correspondent aux numéros des utilisateurs Unix et sont identiques sur les machines serveur1 et machine1. On ajoute dans le fichier pg_hba.conf de serveur1 la ligne suivante:

host         all        192.168.25.1  255.255.255.0      ident     sameuser

Ensuite il faut relancer postmaster sur serveur1. L'utilisateur root doit effectuer les commandes

# /etc/rc.d/init.d/postgresql stop
# /etc/rc.d/init.d/postgresql start

On se connecte à partir de machine1 au postmaster de serveur1 lorqu'on est un utilisateur autorisé avec

 bash$ psql -h serveur1 mabase

Extension de postgreSQL avec des types utilisateurs et des fonctions

Voici un exemple inspiré du tutorial de postgreSQL qui consiste à ajouter un type nombre complexe au système. On définit le type Complex et des fonctions d'entrées/sorties complex_in() et complex_out() en langage C.

#include <stdio.h>
#include <postgres.h>
#include <utils/elog.h>
#include <utils/palloc.h>

typedef struct Complex {
  double	x;
  double	y;
  } Complex;

/* fonctions pour les entrees/sorties  et l'addition de deux complexes */
/* postgresql a une fonction specifique palloc() pour gerer la memeire */

Complex * complex_in(char *str){
  double x, y;
  Complex *result;
  if (sscanf(str, " %lf +i %lf ", &x, &y) != 2) {
    elog(WARN, "complex_in: error in parsing \"%s\"", str);
    return NULL;
    }
  result = (Complex *)palloc(sizeof(Complex));
  result->x = x;
  result->y = y;
  return (result);
  }

char *complex_out(Complex *complex){
  char *result;
  if(complex == NULL)
    return(NULL);
  result = (char *) palloc(60);
  sprintf(result, "%lg +i %lg", complex->x, complex->y);
  return(result);
  }

Complex *complex_add(Complex *a, Complex *b){
  Complex *result;
  result = (Complex *)palloc(sizeof(Complex));
  result->x = a->x + b->x;
  result->y = a->y + b->y;
  return (result);
  }

Les fichiers de déclaration qui apparaissent au début du programme se trouvent dans le répertoire /usr/include/pgsql. Pour inclure ces fonctions dans postgreSQL, il faut les transformer en objet dynamique. En supposant que les fonctions se trouvent dans le fichier complex.c, la commande compilation est

bash$ cc -shared -o complex.so -I/usr/include/pgsql complex.c

On charge ensuite le contenu du fichier complex.so sous psql à l'aide des commandes SQL qui suivent:

-- chargement des fonctions d'entrees/sorties
CREATE FUNCTION complex_in(opaque)
   RETURNS complex
   AS '/home/pierre/complex.so'
   LANGUAGE 'c'\g

CREATE FUNCTION complex_out(opaque)
   RETURNS opaque
   AS '/home/pierre/complex.so'
   LANGUAGE 'c'\g

-- creation du type complex
CREATE TYPE complex (
   internallength = 16, 
   input = complex_in,
   output = complex_out
)\g

-- creation d'un table contenant des nombres complexes
CREATE TABLE test_complex (
	a	complex,
	b	complex
)\g

INSERT INTO test_complex VALUES ('4.0+i2.5','2.2+i3.0');
INSERT INTO test_complex VALUES ('3.3+i5.4', '6.+i9.5');

-- chargement de la fonction d'addition
CREATE FUNCTION complex_add(complex, complex)
   RETURNS complex
   AS '/home/pierre/complex.so'
   LANGUAGE 'c'\g

-- on peut meme definir un operateur + pour les complexes
CREATE OPERATOR + (
   leftarg = complex,
   rightarg = complex,
   procedure = complex_add,
   commutator = +
)\g

On peut stocker ces commandes dans un fichier complex.sql et dire à psql d'interpréter le contenu du fichier avec

mabase=> \i complex.sql

Vérifions le contenu de la table test_complex.

mabase=> SELECT * FROM test_complex;
a         |b       
----------+--------
4 +i 2.5  |2.2 +i 3
3.3 +i 5.4|6 +i 9.5
(2 rows)

Utilisons la fonction complex_add() et l'opérateur + pour les nombres complexes.

mabase=> SELECT complex_add(a,b) as c FROM test_complex;
c          
-----------
6.2 +i 5.5 
9.3 +i 14.9
(2 rows)

mabase=> SELECT (a + b + a) AS d FROM test_complex;
d           
------------
10.2 +i 8   
12.6 +i 20.3
(2 rows)

L'API en langage C, libpq

La distribution de postgreSQL comprend une librairie qui permet d'écrire des programmes C qui dialoguent avec le serveur. Voici un exemple de programme qui affiche le contenu de la table test_complex de la base mabase.³

#include <stdio.h>
#include <libpq-fe.h>

main() {
  char *pghost, *pgport, *pgoptions, *pgtty;
  char* dbName;
  int i,j, nbField,nbTuple;
  PGconn* conn;
  PGresult* res;
  pghost=NULL;     
  pgport=NULL;     
  pgoptions=NULL;  
  pgtty=NULL;      
  dbName="mabase";  

  /* etablissement de la connexion a la base  de donnees */
  conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
  if(PQstatus(conn) == CONNECTION_BAD) {
    fprintf(stderr,"Connection to database '%s' failed.\n", dbName);
    fprintf(stderr,"%s",PQerrorMessage(conn));
    PQfinish(conn);
    exit(1);
    }

  /* debut d'une transaction   */
  res = PQexec(conn,"BEGIN");
  PQclear(res); 

  /* Consultation d'une table de la base  */
  res = PQexec(conn,"select * from test_complex");
  if (res == NULL || PQresultStatus(res) != PGRES_TUPLES_OK) {
    fprintf(stderr,"command didn't return tuples properly\n");
    if (res) PQclear(res);
    PQfinish(conn);
    exit(1);
  } 
  nbField=PQnfields(res);
  nbTuple=PQntuples(res);
  for(i=0;i<nbField;i++){
    printf("%s\n------------\n",PQfname(res,i));
    for(j=0;j<nbTuple;j++){
      printf("%s\n",PQgetvalue(res,j,i));
      }
    }
   
  /* fin de la  transaction */
  res = PQexec(conn, "END");
  PQclear(res);

  /* fermeture de la  connection  */
  PQfinish(conn);
}

Supposons que le programme ci-dessus se trouve dans le fichier acces.c. A la compilation, il doit être lié avec la libraire /usr/lib/libpq.so. La commande correspondante est

bash$ cc -o acces -I/usr/include/pgsql -lpq acces.c

L'exécution donne

bash$ acces
a
------------
4 +i 2.5
3.3 +i 5.4
b
------------
2.2 +i 3
6 +i 9.5

L'interface avec Tcl/Tk

Il est possible de réaliser des interfaces graphiques sous Xwindow pour postgreSQL en utilisant Tcl/Tk. Pour cela, il est nécessaire de se procurer les sources de postgreSQL 6.2.1. Elles sont disponibles à ftp://ftp.postgresql.org/pub/oldstuff/postgresql-6.2.1.tar.gz. Les sources permettent de construire une librairie libpgtcl contenant des commandes qui permettent d'utiliser postgreSQL à partir de Tcl et deux interpréteurs pgtclsh et pktksh qui sont versions des interpréteurs tclsh et wish intégrant les commandes de libpgtcl. La procédure d'installation comprend plusieurs étapes. Elle est donnée en annexe.

Voici un exemple de programme Tcl/TK qui ouvre une fenêtre dans laquelle un bouton lance la commande select *from test_complex; et dont le résultat de la requête s'affiche dans la fenêtre.

#!/usr/bin/pgtksh

frame .top -borderwidth 10
pack .top -side top -fill x
button .top.quit -text Quit -command exit
button .top.stc  -text "select * from test_complex" -command  Sel
pack .top.stc .top.quit  -side  left 

frame .t
set log  [text .t.log -width 40 -height 10 \
  -borderwidth 2 -relief raised -setgrid true ]
pack .t.log  -side  left -fill both -expand true 
pack .t  -side  top -fill both -expand true 


proc Sel {} {
global input log
  set conn [pg_connect mabase]
  set res [pg_exec $conn "select * from test_complex"]
  set ntups [pg_result $res -numTuples]
  for {set i 0} {$i < $ntups} {incr i} {
    set val [pg_result $res -getTuple $i]
    $log insert end $val\n
    }
  $log insert end "-----------------------\n"
  pg_disconnect $conn
  }

Supposons que le programme se trouve dans le fichier mabase.tcl. Pour l'exécuter, on peut le rendre exétable et le lancer directement.

bash$ chmod +x mabase.tcl
bash$ mabase.tcl

On peut aussi invoquer l'interpréteur pgtksh.

bash$ pgtksh mabase.tcl

Dans les deux cas, une fenêtre s'ouvre et lorsqu'on clique sur le bouton select * from test_complex, on voit

La connexion JDBC

On trouve aussi dans les fichiers sources, des sources Java qui permettent de réaliser des connexions à postgreSQL en utilisant le protocole Java DataBase Connectivity. Il faut disposer d'une version du Java Development Kit 1.1. Une procédure d'installation de ce dernier à partir du cdrom est décrite dans un autre article du présent volume. Pour cela, il est également nécessaire de se procurer les sources de postgreSQL disponibles à ftp://ftp.postgresql.org/pub/oldstuff/postgresql-6.2.1.tar.gz. Elles permettent de construire une librairie de classes postgresql.jar qui réalisent l'interface entre postgreSQL et le package java.sql du JDK. Pour construire cette librairie, la séquence de commandes est

bash$ tar xvfz postgresql-6.2.1.tar.gz
bash$ cd postgresql-6.2.1/src
bash$ ./configure

Vous pouvez donner les réponses par défaut aux questions posées par ce programme.

bash$ cd interfaces/jdbc
bash$ make

Cette commande doit avoir créé le fichier postgresql.jar.

Pour terminer l'installation, l'utilisateur root peut recopier le fichier postgresql.jar et le répertoire postgresql dans le répertoire /usr/lib/jdk1.1.5/classes avec

# mkdir /usr/lib/jdk-1.1.5/classes
# cp  postgresql.jar /usr/lib/jdk-1.1.5/classes
# cp -r postgresql /usr/lib/jdk-1.1.5/classes

Pour tester l'installation, on peut éditer le fichier JDBC_Test.java et remplacer à la ligne 31 la chaîne de caractères "adrian" par un nom d'utilisateur habilité à utiliser mabase, soit "pierre" dans notre session. On compile JDBC_Test.java avec

bash$ export JAVA_HOME=/usr/lib/jdk-1.1.5 
bash$ javac JDBC_Test.java

L'exécution du programme donne

bash$ java JDBC_Test jdbc:postgresql://localhost/mabase
Connecting to Database URL = jdbc:postgresql://localhost/mabase
Connected...Now creating a statement
Ok...now we will create a table
Now we will insert some columns
Inserted some data
Now lets try a select
Back from the select...the following are results
row 0   1       1
row 1   2       1
row 2   3       1
Ok...dropping the table
Now closing the connection

L'inconvénient de ce programme est qu'il ne laisse pas de trace dans la base de données. Voici une adaptation de ce programme qui ajoute une entrée dans la table test_complex.

import java.io.*;
import java.lang.*;
import java.sql.*;

class piebase {
  public piebase(){} 
  public static void main(String argv[]) {
    String url = new String(argv[0]);
    Connection db;
    Statement s;
    ResultSet rs;
// Chargement du driver
    try {
      Class.forName("postgresql.Driver");
      } 
    catch (ClassNotFoundException e) {
      System.err.println("Exception: " + e.toString());
      }
    try {
      System.out.println("Connexion a l'URL = " + url);
      db = DriverManager.getConnection(url, "pierre", "");
      System.out.println("Connecte...");
      s = db.createStatement();
      System.out.println("Ajout de (1.+i3., -5.+i2.) dans test_complex");
      s.executeUpdate("insert into test_complex values ('1.+i3.','-5.+i2.')");
      System.out.println("Consultation de test_complex");
      rs = s.executeQuery("select * from test_complex");
      int i = 0;
      while (rs.next()) {
        String a = rs.getString("a");
        String b = rs.getString("b");
        System.out.println("row " + i + "	" + a + "	" + b);
	i++;
        }
      System.out.println("Fermeture de la connexion");
      s.close();
      db.close();
      } 
    catch (SQLException e) {
      System.out.println("Exception: " + e.toString());
      }
    }
}

Pour le tester, il faut que le programme ci-dessus se trouve dans un fichier appelé piebase.java. Ce fichier peut se trouver n'importe où si JDK les postgresql.jar et postgreql se trouvent dans /usr/lib/jdk-1.1.5/classes. Sinon, il faut créer ce fichier dans postgresql-6.2.1/src/interfaces/jdbc. On lance ensuite

bash$ javac piebase.java
bash$ java piebase jdbc:postgresql://localhost/mabase
Connexion a l'URL = jdbc:postgresql://localhost/mabase
Connecte...
Ajout de (1.+i3., -5.+i2.) dans test_complex
Consultation de test_complex
row 0   4 +i 2.5        2.2 +i 3
row 1   3.3 +i 5.4      6 +i 9.5
row 2   1 +i 3  -5 +i 2
Fermeture de la connexion

On peut alors vérifier l'état de la table test_complex sous psql.

La documentation

Elle se trouve dans l'arborescence de fichiers dont la racine est le répertoire /usr/doc/postgresql-6.2.1/. Elle contient en particulier

un manuel écrit en html dont est largement inspiré le présent article, /usr/doc/postgresql-6.2.1/doc/manual/pg95user.html,
un fichier postscript /usr/doc/postgresql-6.2.1/doc/userguide.ps,
plusieurs FAQs et READMEs.

Plusieurs pages de manuel sont accessibles avec la commande man. Essayez par exemple man postmaster, man pgintro, man psql, ...

Quelques adresses sur le Web

La home page de postgreSQL: http://postgreSQL.org
Le postgreSQL HOWTO : http://sunsite.unc.edu/LDP/HOWTO/PostgreSQL-HOWTO.html
Les sources de postgreSQL sont dans: ftp://ftp.postgresql.org/pub/
ATTENTION la version installée 6.2.1 est déjà ancienne. Elle se trouve dans oldstuff//postgresql-6.2.1.tar.gz. La version courante est la 6.3.2.
La FAQ spécifique pour Linux: http://postgreSQL.org/docs/FAQ-linux.shtml

¹ Le système postgreSQL fait partie de la Redhat 5.0.

² Signalons que la chaîne de caractères \g qui indique la fin d'une commande est plus robuste que le point virgule qui n'est pas toujours interprété par psql.

³ Ce programme se contente de récupérer les champs de chaque tuple sous forme de chaîne de caractères, mais il est aussi tout à fait possible de les obtenir directement en binaire en définissant un BINARY CURSOR. Des exemples de tels programmes sont présentés dans le manuel de postgreSQL.

Annexe : Installation de l'interface avec Tcl/Tk

Voici les commandes à exécuter.

bash$ tar xvfz postgresql-6.2.1.tar.gz
bash$ cd postgresql-6.2.1/src
bash$ ./configure

Vous pouvez donner les réponses par défaut aux questions posées par ce programme.

bash$ cd interfaces/libpgtcl
bash$ make

Cette commande a pour résultat la création de la librairie libpgtcl.so.1. L'utilisateur root doit ensuite effectuer

# cp libpgtcl.so.1 /usr/lib
# ldconfig 
# ln -s /usr/lib/libpgtcl.so.1 /usr/lib/libpgtcl.so

Puis il faut construire les interpréteurs pgtclsh et pgtksh. L'utilisateur qui conduit l'installation doit se placer dans postgresql-6.2.1/src/, éditer le fichier Makefile.global et remplacer respectivement les chaînes de caractères tcl7.5 par tcl8.0, tk4.1 par tk8.0 et X11_LIBDIR= /usr/lib par X11_LIBDIR= /usr/X386/lib. Ensuite il faut faire

bash$ cd bin/pgtclsh
bash$ make

Les deux interpréteurs doivent alors se trouver dans dans le répertoire courant postgresql-6.2.1/src/bin/pgtclsh. L'utilisateur root peut les recopier dans le répertoire /usr/bin avec

# cp pgtclsh /usr/bin
# cp pgtksh /usr/bin