
-- Fichier d exemple pour PostgresSQL
-- 
-- @author  : Batiste Bieler
-- @company : http://dosimple.ch
-- @version : 0.1
-- 
-- Copyright (C) 2006  Bieler Batiste
-- 
-- This library is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License as published by the Free Software Foundation; either
-- 
-- This library is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
-- Lesser General Public License for more details.
-- 
-- You should have received a copy of the GNU Lesser General Public
-- License along with this library; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

-- routine de suppression

DROP TRIGGER recalcul_total ON produit_commande;
DROP FUNCTION calcul_total_trigger();
DROP FUNCTION calcul_total(integer);
DROP FUNCTION produits_de_la_commande(integer);
DROP TYPE produit_de_la_commande;
DROP VIEW vente_produits;
DROP TABLE produit_commande;
DROP TABLE commandes;
DROP TABLE produits;
DROP TABLE clients;

-- création des tables

CREATE TABLE clients
(
    pkey SERIAL PRIMARY KEY,
    nom VARCHAR(50) NOT NULL,
    prenom VARCHAR(50) NOT NULL
);
CREATE TABLE commandes
(
    pkey SERIAL PRIMARY KEY,
    -- la commande pointe sur un client
    client_pkey INTEGER references clients NOT NULL,
    total NUMERIC(10,2)
);
CREATE TABLE produits
(
    pkey SERIAL PRIMARY KEY,
    -- la contrainte "prix_positif" impose  un prix strictement positif
    prix NUMERIC(10,2) CONSTRAINT prix_positif CHECK (prix > 0) NOT NULL,
    -- l intitulé du produit doit être unique
    label VARCHAR(50) UNIQUE NOT NULL
);
-- cette table indique la quantité d un produit pour une commande
CREATE TABLE produit_commande
(
    -- "ON DELETE CASCADE" indique que si la ligne de données
    -- référencée est supprimée celle-ci doit l être également
    commande_pkey INTEGER references commandes ON DELETE CASCADE,
    produit_pkey INTEGER references produits ON DELETE CASCADE,
    -- la quantité doit être strictement positive
    quantite INTEGER CONSTRAINT quantite_positive CHECK (quantite > 0) NOT NULL,
    -- la clé primaire est formée de la clé du produit et de la commande
    PRIMARY KEY(produit_pkey, commande_pkey)
);

-- Vue

CREATE VIEW vente_produits AS SELECT produits.*, (SELECT SUM(produit_commande.quantite) FROM produit_commande WHERE produit_commande.produit_pkey=produits.pkey) as quantite FROM produits;

-- Procédures

-- créer un type de donné est nécessaire pour ce qui va suivre
CREATE TYPE produit_de_la_commande AS (pkey INTEGER, prix NUMERIC(10,2), label VARCHAR(50), quantite INTEGER);

-- la fonction "produits_de_la_commande" retourne tout les produits d une commande choisie avec
-- les quantités associées
CREATE OR REPLACE FUNCTION produits_de_la_commande(INTEGER) RETURNS SETOF produit_de_la_commande AS
$BODY$
DECLARE
-- on déclare une variable de travail type "produit_de_la_commande"
produit produit_de_la_commande;
BEGIN
-- la clé de la commande est passé comme paramètre. Si ce paramètre n est
-- nommmé il prend comme valeur $n. N étant la position du paramètre dans le prototype.
FOR produit IN SELECT produits.*, produit_commande.quantite FROM produits, produit_commande 
WHERE commande_pkey=$1 and produit_pkey=produits.pkey
    LOOP
        -- cette syntaxe est nécessaire pour retourner plusieurs lignes
        RETURN NEXT produit;
    END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql';

-- cette fonction calcul le total d'une commande, le mets à jour et le renvoi
CREATE OR REPLACE FUNCTION calcul_total(INTEGER, OUT commande_total NUMERIC) AS
$BODY$
DECLARE
produit produit_de_la_commande;
BEGIN
commande_total=0.0;
FOR produit IN SELECT * FROM produits_de_la_commande($1)
    LOOP
        commande_total=commande_total+(produit.prix*produit.quantite);
    END LOOP;
    -- mise à jour
    UPDATE commandes SET total=commande_total WHERE pkey=$1;
END;
$BODY$
LANGUAGE 'plpgsql';

-- Triggers

-- un trigger a besoin d'une fonction spécial qui retourne un trigger
CREATE OR REPLACE FUNCTION calcul_total_trigger() RETURNS TRIGGER AS
$BODY$
BEGIN
    -- NEW correspond à notre ligne qui vient d être créé/modifiée
    -- PERFORM est nécessaire car la valeur de retour ne nous interesse pas
    PERFORM calcul_total(NEW.commande_pkey);
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';

-- notre trigger qui réagis sur INSERT ou UPDATE
CREATE TRIGGER recalcul_total
    AFTER INSERT OR UPDATE ON produit_commande FOR EACH ROW
    EXECUTE PROCEDURE calcul_total_trigger();

-- Quelques données

INSERT INTO clients (nom,prenom) VALUES ('Dupont', 'André');
INSERT INTO clients (nom,prenom) VALUES ('Dupond', 'Jaques');
INSERT INTO clients (nom,prenom) VALUES ('Hornoz', 'Sandrine');
INSERT INTO clients (nom,prenom) VALUES ('Potto', 'Hélène');

INSERT INTO produits (prix,label) VALUES (1.2, 'Gomme blanche');
INSERT INTO produits (prix,label) VALUES (9.5, 'Taille crayon en fer blanc');
INSERT INTO produits (prix,label) VALUES (2.3, 'Taille crayon plastique');
INSERT INTO produits (prix,label) VALUES (0.2, 'Crayon');

INSERT INTO commandes (client_pkey,total) VALUES (1,0);
INSERT INTO commandes (client_pkey,total) VALUES (2,0);

INSERT INTO produit_commande (commande_pkey,produit_pkey,quantite) VALUES (1,1,5);
INSERT INTO produit_commande (commande_pkey,produit_pkey,quantite) VALUES (1,2,1);
INSERT INTO produit_commande (commande_pkey,produit_pkey,quantite) VALUES (1,3,1);
INSERT INTO produit_commande (commande_pkey,produit_pkey,quantite) VALUES (1,4,20);
INSERT INTO produit_commande (commande_pkey,produit_pkey,quantite) VALUES (2,4,10);
INSERT INTO produit_commande (commande_pkey,produit_pkey,quantite) VALUES (2,1,4);



