--
--









--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


--
-- Name: add(integer, integer); Type: FUNCTION; Schema: public; Owner: gilles
--

CREATE FUNCTION ADD (integer, integer)
    RETURNS integer
    LANGUAGE sql
    IMMUTABLE STRICT
    AS $_$
    SELECT
        $1 + $2;
$_$;

ALTER FUNCTION public.add (integer, integer) OWNER TO gilles;

--
-- Name: check_password(text, text, text, text, text, text, text, text, text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: public; Owner: gilles
--

CREATE FUNCTION check_password (uname1 text, pass1 text, uname2 text, pass2 text, uname3 text, pass3 text, uname4 text, pass4 text, uname5 text, pass5 text, uname6 text, pass6 text, uname7 text, pass7 text, uname8 text, pass8 text, uname9 text, pass9 text)
    RETURNS boolean
    LANGUAGE plpgsql
    SECURITY DEFINER
    SET search_path TO admin, pg_temp
    AS $_$
DECLARE
    passed BOOLEAN;
BEGIN
    SELECT
        (pwd = $2) INTO passed
    FROM
        pwds
    WHERE
        username = $1;
    RETURN passed;
END;
$_$;

ALTER FUNCTION public.check_password (uname1 text, pass1 text, uname2 text, pass2 text, uname3 text, pass3 text, uname4 text, pass4 text, uname5 text, pass5 text, uname6 text, pass6 text, uname7 text, pass7 text, uname8 text, pass8 text, uname9 text, pass9 text) OWNER TO gilles;

--
-- Name: dup(integer); Type: FUNCTION; Schema: public; Owner: gilles
--

CREATE FUNCTION dup (integer, OUT f1 integer, OUT f2 text)
    RETURNS record
    LANGUAGE sql
    AS $_$
    SELECT
        $1,
        CAST($1 AS text) || ' is text'
$_$;

ALTER FUNCTION public.dup (integer, OUT f1 integer, OUT f2 text) OWNER TO gilles;

--
-- Name: increment(integer); Type: FUNCTION; Schema: public; Owner: gilles
--

CREATE FUNCTION INCREMENT (i integer)
    RETURNS integer
    LANGUAGE plpgsql
    AS $$
BEGIN
    RETURN i + 1;
END;
$$;

ALTER FUNCTION public.increment (i integer) OWNER TO gilles;

--
-- Name: peuple_stock(integer, integer); Type: FUNCTION; Schema: public; Owner: userdb
--

CREATE FUNCTION peuple_stock (annee_debut integer, annee_fin integer)
    RETURNS bigint
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_annee integer;
    v_nombre integer;
    v_contenant_id integer;
    v_vin_id integer;
    compteur bigint := 0;
    annees integer;
    contenants integer;
    vins integer;
    tuples_a_generer integer;
BEGIN
    -- vider la table de stock
    TRUNCATE TABLE stock;
    -- calculer le nombre d'annees
    SELECT
        (annee_fin - annee_debut) + 1 INTO annees;
    -- nombre de contenants
    SELECT
        count(*)
    FROM
        contenant INTO contenants;
    -- nombre de vins
    SELECT
        count(*)
    FROM
        vin INTO vins;
    -- calcul des combinaisons
    SELECT
        annees * contenants * vins INTO tuples_a_generer;
    --on boucle sur tous les millesimes: disons 1930 a 2000
    -- soit 80 annees
    FOR v_annee IN annee_debut..annee_fin LOOP
        -- on boucle sur les contenants possibles
        FOR v_contenant_id IN 1..contenants LOOP
            -- idem pour l'id du vin
            FOR v_vin_id IN 1..vins LOOP
                -- on prends un nombre de bouteilles compris entre 6 et 18
                SELECT
                    round(random() * 12) + 6 INTO v_nombre;
                -- insertion dans la table de stock
                INSERT INTO stock (vin_id, contenant_id, annee, nombre)
                    VALUES (v_vin_id, v_contenant_id, v_annee, v_nombre);
                IF (((compteur % 1000) = 0) OR (compteur = tuples_a_generer)) THEN
                    raise notice 'stock : % sur % tuples generes', compteur, tuples_a_generer;
                END IF;
                compteur := compteur + 1;
            END LOOP;
            --fin boucle vin
        END LOOP;
        -- fin boucle contenant
    END LOOP;
    --fin boucle annee
    RETURN compteur;
END;
$$;

ALTER FUNCTION public.peuple_stock (annee_debut integer, annee_fin integer) OWNER TO userdb;

--
-- Name: peuple_vin(); Type: FUNCTION; Schema: public; Owner: userdb
--

CREATE FUNCTION peuple_vin ()
    RETURNS bigint
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_recoltant_id integer;
    v_appellation_id integer;
    v_type_vin_id integer;
    recoltants integer;
    appellations integer;
    types_vins integer;
    tuples_a_generer integer;
    compteur bigint := 0;
BEGIN
    -- vider la table de stock, qui depend de vin, puis vin
    DELETE FROM stock;
    DELETE FROM vin;
    -- compter le nombre de recoltants
    SELECT
        count(*)
    FROM
        recoltant INTO recoltants;
    -- compter le nombre d'appellations
    SELECT
        count(*)
    FROM
        appellation INTO appellations;
    -- compter le nombre de types de vins
    SELECT
        count(*)
    FROM
        type_vin INTO types_vins;
    -- calculer le nombre de combinaisons possibles
    SELECT
        (recoltants * appellations * types_vins) INTO tuples_a_generer;
    --on boucle sur tous les recoltants
    FOR v_recoltant_id IN 1..recoltants LOOP
        -- on boucle sur les appelations
        FOR v_appellation_id IN 1..appellations LOOP
            -- on boucle sur les types de vins
            FOR v_type_vin_id IN 1..types_vins LOOP
                -- insertion dans la table de vin
                INSERT INTO vin (recoltant_id, appellation_id, type_vin_id)
                    VALUES (v_recoltant_id, v_appellation_id, v_type_vin_id);
                IF (((compteur % 1000) = 0) OR (compteur = tuples_a_generer)) THEN
                    raise notice 'vins : % sur % tuples generes', compteur, tuples_a_generer;
                END IF;
                compteur := compteur + 1;
            END LOOP;
            --fin boucle type vin
        END LOOP;
        -- fin boucle appellations
    END LOOP;
    --fin boucle recoltants
    RETURN compteur;
END;
$$;

ALTER FUNCTION public.peuple_vin () OWNER TO userdb;

--
-- Name: trous_stock(); Type: FUNCTION; Schema: public; Owner: userdb
--

CREATE FUNCTION trous_stock ()
    RETURNS bigint
    LANGUAGE plpgsql
    AS $$
DECLARE
    stock_total integer;
    echantillon integer;
    vins_disponibles integer;
    contenants_disponibles integer;
    v_vin_id integer;
    v_contenant_id integer;
    v_tuples bigint := 0;
    annee_min integer;
    annee_max integer;
    v_annee integer;
BEGIN
    -- on compte le nombre de tuples dans stock
    SELECT
        count(*)
    FROM
        stock INTO stock_total;
    raise NOTICE 'taille du stock %', stock_total;
    -- on calcule la taille de l'echantillon a
    -- supprimer de la table stock
    SELECT
        round(stock_total / 10) INTO echantillon;
    raise NOTICE 'taille de l''echantillon %', echantillon;
    -- on compte le nombre de vins disponibles
    SELECT
        count(*)
    FROM
        vin INTO vins_disponibles;
    raise NOTICE '% vins disponibles', vins_disponibles;
    -- on compte le nombre de contenants disponibles
    SELECT
        count(*)
    FROM
        contenant INTO contenants_disponibles;
    raise NOTICE '% contenants disponibles', contenants_disponibles;
    -- on recupere les bornes min/max de annees
    SELECT
        min(annee),
        max(annee)
    FROM
        stock INTO annee_min,
        annee_max;
    -- on fait une boucle correspondant a 1% des tuples
    -- de la table stock
    FOR v_tuples IN 1..echantillon LOOP
        -- selection d'identifiant, au hasard
        --select round(random()*contenants_disponibles) into v_contenant_id;
        v_contenant_id := round(random() * contenants_disponibles);
        --select round(random()*vins_disponibles) into v_vin_id;
        v_vin_id := round(random() * vins_disponibles);
        v_annee := round(random() * (annee_max - annee_min)) + (annee_min);
        -- si le tuple est deja efface, ce n'est pas grave..
        DELETE FROM stock
        WHERE contenant_id = v_contenant_id
            AND vin_id = v_vin_id
            AND annee = v_annee;
        IF (((v_tuples % 100) = 0) OR (v_tuples = echantillon)) THEN
            raise notice 'stock : % sur % echantillon effaces', v_tuples, echantillon;
        END IF;
    END LOOP;
    --fin boucle v_tuples
    RETURN echantillon;
END;
$$;

ALTER FUNCTION public.trous_stock () OWNER TO userdb;

--
-- Name: trous_vin(); Type: FUNCTION; Schema: public; Owner: userdb
--

CREATE FUNCTION trous_vin ()
    RETURNS bigint
    LANGUAGE plpgsql
    AS $$
DECLARE
    vin_total integer;
    echantillon integer;
    v_vin_id integer;
    v_tuples bigint := 0;
    v_annee integer;
BEGIN
    -- on compte le nombre de tuples dans vin
    SELECT
        count(*)
    FROM
        vin INTO vin_total;
    raise NOTICE '% vins disponibles', vin_total;
    -- on calcule la taille de l'echantillon a
    -- supprimer de la table vin
    SELECT
        round(vin_total / 10) INTO echantillon;
    raise NOTICE 'taille de l''echantillon %', echantillon;
    -- on fait une boucle correspondant a 10% des tuples
    -- de la table vin
    FOR v_tuples IN 1..echantillon LOOP
        -- selection d'identifiant, au hasard
        v_vin_id := round(random() * vin_total);
        -- si le tuple est deja efface, ce n'est pas grave..
        -- TODO remplacer ce delete par un trigger on delete cascade
        --      voir dans druid le schema???
        DELETE FROM stock
        WHERE vin_id = v_vin_id;
        DELETE FROM vin
        WHERE id = v_vin_id;
        IF (((v_tuples % 100) = 0) OR (v_tuples = echantillon)) THEN
            raise notice 'vin : % sur % echantillon effaces', v_tuples, echantillon;
        END IF;
    END LOOP;
    --fin boucle v_tuples
    RETURN echantillon;
END;
$$;

ALTER FUNCTION public.trous_vin () OWNER TO userdb;



--
-- Name: appellation; Type: TABLE; Schema: public; Owner: userdb
--

CREATE TABLE appellation (
    id integer NOT NULL,
    libelle text NOT NULL,
    region_id integer
)
WITH (
    autovacuum_enabled = off
);

ALTER TABLE appellation OWNER TO userdb;

--
-- Name: appellation_id_seq; Type: SEQUENCE; Schema: public; Owner: userdb
--

CREATE SEQUENCE appellation_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE appellation_id_seq OWNER TO userdb;

--
-- Name: appellation_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: userdb
--

ALTER SEQUENCE appellation_id_seq OWNED BY appellation.id;

--
-- Name: contenant; Type: TABLE; Schema: public; Owner: userdb
--

CREATE TABLE contenant (
    id integer NOT NULL,
    contenance real NOT NULL,
    libelle text
)
WITH (
    autovacuum_enabled = off,
    fillfactor = '20'
);

ALTER TABLE contenant OWNER TO userdb;

--
-- Name: contenant_id_seq; Type: SEQUENCE; Schema: public; Owner: userdb
--

CREATE SEQUENCE contenant_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE contenant_id_seq OWNER TO userdb;

--
-- Name: contenant_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: userdb
--

ALTER SEQUENCE contenant_id_seq OWNED BY contenant.id;

--
-- Name: recoltant; Type: TABLE; Schema: public; Owner: userdb
--

CREATE TABLE recoltant (
    id integer NOT NULL,
    nom text,
    adresse text
)
WITH (
    autovacuum_enabled = off
);

ALTER TABLE recoltant OWNER TO userdb;

--
-- Name: recoltant_id_seq; Type: SEQUENCE; Schema: public; Owner: userdb
--

CREATE SEQUENCE recoltant_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE recoltant_id_seq OWNER TO userdb;

--
-- Name: recoltant_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: userdb
--

ALTER SEQUENCE recoltant_id_seq OWNED BY recoltant.id;

--
-- Name: region; Type: TABLE; Schema: public; Owner: userdb
--

CREATE TABLE region (
    id integer NOT NULL,
    libelle text NOT NULL
)
WITH (
    autovacuum_enabled = off
);

ALTER TABLE region OWNER TO userdb;

--
-- Name: region_id_seq; Type: SEQUENCE; Schema: public; Owner: userdb
--

CREATE SEQUENCE region_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE region_id_seq OWNER TO userdb;

--
-- Name: region_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: userdb
--

ALTER SEQUENCE region_id_seq OWNED BY region.id;

--
-- Name: stock; Type: TABLE; Schema: public; Owner: userdb
--

CREATE TABLE stock (
    vin_id integer NOT NULL,
    contenant_id integer NOT NULL,
    annee integer NOT NULL,
    nombre integer NOT NULL
)
WITH (
    autovacuum_enabled = off
);

ALTER TABLE stock OWNER TO userdb;

--
-- Name: type_vin; Type: TABLE; Schema: public; Owner: userdb
--

CREATE TABLE type_vin (
    id integer NOT NULL,
    libelle text NOT NULL
)
WITH (
    autovacuum_enabled = off
);

ALTER TABLE type_vin OWNER TO userdb;

--
-- Name: type_vin_id_seq; Type: SEQUENCE; Schema: public; Owner: userdb
--

CREATE SEQUENCE type_vin_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE type_vin_id_seq OWNER TO userdb;

--
-- Name: type_vin_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: userdb
--

ALTER SEQUENCE type_vin_id_seq OWNED BY type_vin.id;

--
-- Name: vin; Type: TABLE; Schema: public; Owner: userdb
--

CREATE TABLE vin (
    id integer NOT NULL,
    recoltant_id integer,
    appellation_id integer NOT NULL,
    type_vin_id integer NOT NULL
)
WITH (
    autovacuum_enabled = off
);

ALTER TABLE vin OWNER TO userdb;

--
-- Name: vin_id_seq; Type: SEQUENCE; Schema: public; Owner: userdb
--

CREATE SEQUENCE vin_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE vin_id_seq OWNER TO userdb;

--
-- Name: vin_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: userdb
--

ALTER SEQUENCE vin_id_seq OWNED BY vin.id;

--
-- Name: appellation id; Type: DEFAULT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY appellation ALTER COLUMN id SET DEFAULT nextval('appellation_id_seq'::regclass);

--
-- Name: contenant id; Type: DEFAULT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY contenant ALTER COLUMN id SET DEFAULT nextval('contenant_id_seq'::regclass);

--
-- Name: recoltant id; Type: DEFAULT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY recoltant ALTER COLUMN id SET DEFAULT nextval('recoltant_id_seq'::regclass);

--
-- Name: region id; Type: DEFAULT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY region ALTER COLUMN id SET DEFAULT nextval('region_id_seq'::regclass);

--
-- Name: type_vin id; Type: DEFAULT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY type_vin ALTER COLUMN id SET DEFAULT nextval('type_vin_id_seq'::regclass);

--
-- Name: vin id; Type: DEFAULT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY vin ALTER COLUMN id SET DEFAULT nextval('vin_id_seq'::regclass);

--
-- Name: appellation appellation_libelle_key; Type: CONSTRAINT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY appellation
    ADD CONSTRAINT appellation_libelle_key UNIQUE (libelle);

--
-- Name: appellation appellation_pkey; Type: CONSTRAINT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY appellation
    ADD CONSTRAINT appellation_pkey PRIMARY KEY (id);

--
-- Name: contenant contenant_pkey; Type: CONSTRAINT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY contenant
    ADD CONSTRAINT contenant_pkey PRIMARY KEY (id);

--
-- Name: recoltant recoltant_pkey; Type: CONSTRAINT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY recoltant
    ADD CONSTRAINT recoltant_pkey PRIMARY KEY (id);

--
-- Name: region region_pkey; Type: CONSTRAINT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY region
    ADD CONSTRAINT region_pkey PRIMARY KEY (id);

--
-- Name: stock stock_pkey; Type: CONSTRAINT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY stock
    ADD CONSTRAINT stock_pkey PRIMARY KEY (vin_id, contenant_id, annee);

--
-- Name: type_vin type_vin_pkey; Type: CONSTRAINT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY type_vin
    ADD CONSTRAINT type_vin_pkey PRIMARY KEY (id);

--
-- Name: vin vin_pkey; Type: CONSTRAINT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY vin
    ADD CONSTRAINT vin_pkey PRIMARY KEY (id);

--
-- Name: appellation appellation_region_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY appellation
    ADD CONSTRAINT appellation_region_id_fkey FOREIGN KEY (region_id) REFERENCES region (id) ON DELETE CASCADE;

--
-- Name: stock stock_contenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY stock
    ADD CONSTRAINT stock_contenant_id_fkey FOREIGN KEY (contenant_id) REFERENCES contenant (id) ON DELETE CASCADE;

--
-- Name: stock stock_vin_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY stock
    ADD CONSTRAINT stock_vin_id_fkey FOREIGN KEY (vin_id) REFERENCES vin (id) ON DELETE CASCADE;

--
-- Name: vin vin_appellation_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY vin
    ADD CONSTRAINT vin_appellation_id_fkey FOREIGN KEY (appellation_id) REFERENCES appellation (id) ON DELETE CASCADE;

--
-- Name: vin vin_recoltant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY vin
    ADD CONSTRAINT vin_recoltant_id_fkey FOREIGN KEY (recoltant_id) REFERENCES recoltant (id) ON DELETE CASCADE;

--
-- Name: vin vin_type_vin_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: userdb
--

ALTER TABLE ONLY vin
    ADD CONSTRAINT vin_type_vin_id_fkey FOREIGN KEY (type_vin_id) REFERENCES type_vin (id) ON DELETE CASCADE;

--
--
