Fonctionnement de Base
**********************

l'installation de postgresql  se fait simplement par:

* lancement d'un exe pour windows (http://www.postgresql.org/download/)
* en utilisant votre installateur préféré (apt-get install postgresql)

l'outil principal est le client postgresql **psql**

gestion du serveur
==================

via l'outil **pg_ctl**

pg_ctl — initialiser, démarrer, arrêter ou contrôler le serveur PostgreSQL

les différentes utilisations:

* pg_ctl init[db] [-s] [-D répertoire_données] [-o options-initdb]
* pg_ctl start [-w] [-t secondes] [-s] [-D répertoire_données] [-l nomfichier] [-o options] [-p chemin] [-c]
* pg_ctl stop [-W] [-t secondes] [-s] [-D répertoire_données] [-m [s[mart]] | [f[ast]] | [i[mmediate]] ]
* pg_ctl restart [-w] [-t secondes] [-s] [-D répertoire_données] [-c] [-m [s[mart]] | [f[ast]] | [i[mmediate]] ] [-o options]
* pg_ctl reload [-D répertoire_données]
* pg_ctl status [-s] [-D répertoire_données]
* pg_ctl promote [-s] [-D répertoire_données]
* pg_ctl kill nom_signal id_processus
* pg_ctl register [-N nom_service] [-U nom_utilisateur] [-P mot_de_passe] [-D répertoire_données] [-S [a[uto]] | [d[emand]] ] [-w] [-t secondes] [-s] [-o options]
* pg_ctl unregister [-N nom_service]


Création d'une base
===================

.. code-block:: sql

    CREATE DATABASE Namedatabase WITH ENCODING='UTF8' CONNECTION LIMIT=-1;

Suppression d'une base
======================

Il faut d'abord identifier qui est connecté à la base

.. code-block:: sql

    select * 
    from pg_stat_activity 
    where datname = 'mydatabase'; 

Puis bloquer les nouvelles connections pour le role PUBLIC 

.. code-block:: sql

    REVOKE CONNECT ON DATABASE mycompta FROM PUBLIC; 

puis tuer les connexions

.. code-block:: sql
 
    SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE procpid in (4004, 5384) 

ou

.. code-block:: sql
 
    SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE procpid in (select procpid from pg_stat_activity where datname= 'mydatabase') 

et enfin supprimer la base

.. code-block:: sql

    drop database mydatabase; 

Création d'une table
====================

un exemple simple

.. code-block:: sql
    
    CREATE TABLE cpte (
        id        serial CONSTRAINT key_cpte PRIMARY KEY,
        lib       varchar(100) NOT NULL
    );

.. note::

    dans cet exemple une sequence est crée pour gérer l'id de la table cpte


un autre exemple avec:

* des contraintes
* des foreign clé
* des défault

.. code-block:: sql

    CREATE TABLE op (
        id        serial CONSTRAINT key_op PRIMARY KEY,
        cpt       integer REFERENCES cpte(id),  
        dat       date NOT NULL,
        tiers     varchar(100) DEFAULT 'inconnu',
        type      varchar(100) DEFAULT 'autre',
        debit     numeric DEFAULT 0,  
        credit    numeric DEFAULT 0,  
        etat      char(1) DEFAULT 'N'
    ); 

Création d'un index
===================

création d'un index op_idx sur la table op

.. code-block:: sql

    CREATE UNIQUE INDEX op_idx ON op (cpt,dat,id);

Création d'une view
===================

.. code-block:: sql

     CREATE VIEW op_view AS SELECT   op1.id, 
                                op1.cpt,
                                op1.dat,
                                op1.tiers,
                                op1.type,
                                op1.etat,
                                op1.debit,
                                op1.credit,
                                (select sum(op2.credit-op2.debit) 
                                        from op as op2 
                                        where op2.cpt = op1.cpt 
                                        and (op2.dat < op1.dat or (op2.dat = op1.dat and op1.id <= op2.id))) as solde
                        FROM op as op1
                        ORDER BY op1.dat, op1.id;


.. note::

   il faut noter dans cet exemple que les deux requêtes sql sont imbriquées


Intégration d'un trigger
========================

Un trigger permet de lancer une action avant ou après une action sur une table.

Les actions prises en compte sont:

* INSERT
* UPDATE
* DELETE

l'implémentation d'un triggers nécessite

* la création d'une fonction
* la création d'un trigger

exemple de triggers sur la tabke op

.. code-block:: sql
    
    CREATE OR REPLACE FUNCTION  mal_helper_trigger() RETURNS TRIGGER AS
     $BODY$
    BEGIN
        -- NEW correspond à la ligne qui vient d'être créé/modifiée
        IF EXISTS(  
            SELECT *  
            FROM helper as tbl  
            WHERE tbl.tiers = NEW.tiers
        ) THEN  
           UPDATE helper  SET type=NEW.type, debit=NEW.debit, credit=NEW.credit where tiers=NEW.tiers;  
        ELSE  
           INSERT INTO helper VALUES (NEW.tiers, NEW.type, NEW.debit, NEW.credit);  
        END IF;  
        RETURN NEW ;
    END ;
     $BODY$
     LANGUAGE 'plpgsql' ;
    
    CREATE TRIGGER maj_helper AFTER INSERT OR UPDATE ON op 
        FOR EACH ROW EXECUTE PROCEDURE mal_helper_trigger();

Règle
=====

Une règle permet de modifier le comportement d'un select ou d'un update.

Un exemple contraint est la modification de la gestion du **DELETE**.

Dans la table client on ne souhaite pas supprimer un client mais plutôt le rendre inactif

.. code-block:: sql
    
    CREATE RULE supp_client AS ON DELETE TO client
    DO INSTEAD UPDATE client set actif = false where nom = OLD.nom

Séquence
========

Une séquence permet d'avoir une suite logique

::

    CREATE [ TEMPORARY | TEMP ] SEQUENCE nom [ INCREMENT [ BY ] incrément ]
    [ MINVALUE valeurmin | NO MINVALUE ]
    [ MAXVALUE valeurmax | NO MAXVALUE ]
    [ START [ WITH ] début ]
    [ CACHE cache ]
    [ [ NO ] CYCLE ]
    [ OWNED BY { table.colonne | NONE } ]

On peut donc indiquer:

* un nombre pour le départ
* un min
* un max
* si la séquence boucle (CYCLE)
* incrémentation 

Créer une séquence ascendante appelée serie, démarrant à 101 :

.. code-block:: sql

    CREATE SEQUENCE serie START 101;

Sélectionner le prochain numéro de cette séquence :

.. code-block:: sql

    SELECT nextval('serie');    

Utiliser cette séquence dans une commande INSERT :

.. code-block:: sql

    INSERT INTO distributors VALUES (nextval('serie'), 'nothing');


Information schéma
==================

description d'une table 

.. code-block:: sql

    SELECT table_name, column_name, data_type, is_nullable, character_maximum_length, udt_name   
    FROM information_schema.columns   
    WHERE table_schema = 'public'   
    AND table_name = 'mytable'   
    ORDER BY ordinal_position   

liste des clé étrangères 

.. code-block:: sql

    SELECT 
        tc.constraint_name, tc.table_name, kcu.column_name, 
        ccu.table_name AS foreign_table_name, 
        ccu.column_name AS foreign_column_name 
    FROM 
        information_schema.table_constraints AS tc 
        JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name 
        JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name 
    WHERE constraint_type = 'FOREIGN KEY' AND table_name='mytable'; 

liste des clés primaires 

.. code-block:: sql

    SELECT 
        tc.constraint_name, tc.table_name, kcu.column_name, 
        tc.constraint_type 
    FROM 
        information_schema.table_constraints AS tc 
        JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name 
    WHERE tc.table_name='mytable' where tc.constraint_type="PRIMARY_KEY"; 

liste des index (issue d'un pk, fk ou index) 

.. code-block:: sql

    SELECT c.indexrelid,  t.relname, a.attname, a.attnum, c.* 
         FROM pg_index c 
    LEFT JOIN pg_class t 
           ON c.indrelid  = t.oid 
    LEFT JOIN pg_attribute a 
           ON a.attrelid = t.oid 
          AND a.attnum = ANY(indkey) 
        WHERE t.relname = 'mytable' 


liste des vues

.. code-block:: sql

    SELECT * FROM pg_views

Création explicite de locks
===========================

::

    test=# BEGIN WORK;
    BEGIN
    test=# LOCK TABLE m_user IN EXCLUSIVE MODE;
    LOCK TABLE
    test=# commit work;
    COMMIT
    test=# BEGIN WORK;
    BEGIN
    test=# LOCK TABLE m_user IN ACCESS EXCLUSIVE MODE;
    LOCK TABLE
    test=# COMMIT WORK;

la visualisation des locks peut être réalisé par la requête suivante:

.. code-block:: sql

    SELECT *
    FROM pg_locks