1-- 2-- PostgreSQL database dump 3-- 4-- Dumped from database version 9.6.2 5-- Dumped by pg_dump version 9.6.2 6SET statement_timeout = 0; 7 8SET lock_timeout = 0; 9 10SET idle_in_transaction_session_timeout = 0; 11 12SET client_encoding = 'UTF8'; 13 14SET standard_conforming_strings = ON; 15 16SET check_function_bodies = FALSE; 17 18SET client_min_messages = warning; 19 20SET row_security = OFF; 21 22-- 23-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 24-- 25CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; 26 27-- 28-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 29-- 30COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; 31 32SET search_path = public, pg_catalog; 33 34-- 35-- Name: add(integer, integer); Type: FUNCTION; Schema: public; Owner: gilles 36-- 37CREATE FUNCTION ADD (integer, integer) 38 RETURNS integer 39 LANGUAGE sql 40 IMMUTABLE STRICT 41 AS $_$ 42 SELECT 43 $1 + $2; 44 45$_$; 46 47ALTER FUNCTION public.add (integer, integer) OWNER TO gilles; 48 49-- 50-- 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 51-- 52CREATE 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) 53 RETURNS boolean 54 LANGUAGE plpgsql 55 SECURITY DEFINER 56 SET search_path TO admin, pg_temp 57 AS $_$ 58DECLARE 59 passed boolean; 60BEGIN 61 SELECT 62 (pwd = $2) INTO passed 63 FROM 64 pwds 65 WHERE 66 username = $1; 67 RETURN passed; 68END; 69$_$; 70 71ALTER 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; 72 73-- 74-- Name: dup(integer); Type: FUNCTION; Schema: public; Owner: gilles 75-- 76CREATE FUNCTION dup (integer, OUT f1 integer, OUT f2 text) 77 RETURNS record 78 LANGUAGE sql 79 AS $_$ 80 SELECT 81 $1, 82 CAST($1 AS text) || ' is text' 83$_$; 84 85ALTER FUNCTION public.dup (integer, OUT f1 integer, OUT f2 text) OWNER TO gilles; 86 87-- 88-- Name: increment(integer); Type: FUNCTION; Schema: public; Owner: gilles 89-- 90CREATE FUNCTION INCREMENT (i integer) 91 RETURNS integer 92 LANGUAGE plpgsql 93 AS $$ 94BEGIN 95 RETURN i + 1; 96END; 97$$; 98 99ALTER FUNCTION public.increment (i integer) OWNER TO gilles; 100 101-- 102-- Name: peuple_stock(integer, integer); Type: FUNCTION; Schema: public; Owner: userdb 103-- 104CREATE FUNCTION peuple_stock (annee_debut integer, annee_fin integer) 105 RETURNS bigint 106 LANGUAGE plpgsql 107 AS $$ 108DECLARE 109 v_annee integer; 110 v_nombre integer; 111 v_contenant_id integer; 112 v_vin_id integer; 113 compteur bigint := 0; 114 annees integer; 115 contenants integer; 116 vins integer; 117 tuples_a_generer integer; 118BEGIN 119 -- vider la table de stock 120 TRUNCATE TABLE stock; 121 -- calculer le nombre d'annees 122 SELECT 123 (annee_fin - annee_debut) + 1 INTO annees; 124 -- nombre de contenants 125 SELECT 126 count(*) 127 FROM 128 contenant INTO contenants; 129 -- nombre de vins 130 SELECT 131 count(*) 132 FROM 133 vin INTO vins; 134 -- calcul des combinaisons 135 SELECT 136 annees * contenants * vins INTO tuples_a_generer; 137 --on boucle sur tous les millesimes: disons 1930 a 2000 138 -- soit 80 annees 139 FOR v_annee IN annee_debut..annee_fin LOOP 140 -- on boucle sur les contenants possibles 141 FOR v_contenant_id IN 1..contenants LOOP 142 -- idem pour l'id du vin 143 FOR v_vin_id IN 1..vins LOOP 144 -- on prends un nombre de bouteilles compris entre 6 et 18 145 SELECT 146 round(random() * 12) + 6 INTO v_nombre; 147 -- insertion dans la table de stock 148 INSERT INTO stock (vin_id, contenant_id, annee, nombre) 149 VALUES (v_vin_id, v_contenant_id, v_annee, v_nombre); 150 IF (((compteur % 1000) = 0) OR (compteur = tuples_a_generer)) THEN 151 RAISE NOTICE 'stock : % sur % tuples generes', compteur, tuples_a_generer; 152 END IF; 153 compteur := compteur + 1; 154 END LOOP; 155 --fin boucle vin 156 END LOOP; 157 -- fin boucle contenant 158 END LOOP; 159 --fin boucle annee 160 RETURN compteur; 161END; 162$$; 163 164ALTER FUNCTION public.peuple_stock (annee_debut integer, annee_fin integer) OWNER TO userdb; 165 166-- 167-- Name: peuple_vin(); Type: FUNCTION; Schema: public; Owner: userdb 168-- 169CREATE FUNCTION peuple_vin () 170 RETURNS bigint 171 LANGUAGE plpgsql 172 AS $$ 173DECLARE 174 v_recoltant_id integer; 175 v_appellation_id integer; 176 v_type_vin_id integer; 177 recoltants integer; 178 appellations integer; 179 types_vins integer; 180 tuples_a_generer integer; 181 compteur bigint := 0; 182BEGIN 183 -- vider la table de stock, qui depend de vin, puis vin 184 DELETE FROM stock; 185 DELETE FROM vin; 186 -- compter le nombre de recoltants 187 SELECT 188 count(*) 189 FROM 190 recoltant INTO recoltants; 191 -- compter le nombre d'appellations 192 SELECT 193 count(*) 194 FROM 195 appellation INTO appellations; 196 -- compter le nombre de types de vins 197 SELECT 198 count(*) 199 FROM 200 type_vin INTO types_vins; 201 -- calculer le nombre de combinaisons possibles 202 SELECT 203 (recoltants * appellations * types_vins) INTO tuples_a_generer; 204 --on boucle sur tous les recoltants 205 FOR v_recoltant_id IN 1..recoltants LOOP 206 -- on boucle sur les appelations 207 FOR v_appellation_id IN 1..appellations LOOP 208 -- on boucle sur les types de vins 209 FOR v_type_vin_id IN 1..types_vins LOOP 210 -- insertion dans la table de vin 211 INSERT INTO vin (recoltant_id, appellation_id, type_vin_id) 212 VALUES (v_recoltant_id, v_appellation_id, v_type_vin_id); 213 IF (((compteur % 1000) = 0) OR (compteur = tuples_a_generer)) THEN 214 RAISE NOTICE 'vins : % sur % tuples generes', compteur, tuples_a_generer; 215 END IF; 216 compteur := compteur + 1; 217 END LOOP; 218 --fin boucle type vin 219 END LOOP; 220 -- fin boucle appellations 221 END LOOP; 222 --fin boucle recoltants 223 RETURN compteur; 224END; 225$$; 226 227ALTER FUNCTION public.peuple_vin () OWNER TO userdb; 228 229-- 230-- Name: trous_stock(); Type: FUNCTION; Schema: public; Owner: userdb 231-- 232CREATE FUNCTION trous_stock () 233 RETURNS bigint 234 LANGUAGE plpgsql 235 AS $$ 236DECLARE 237 stock_total integer; 238 echantillon integer; 239 vins_disponibles integer; 240 contenants_disponibles integer; 241 v_vin_id integer; 242 v_contenant_id integer; 243 v_tuples bigint := 0; 244 annee_min integer; 245 annee_max integer; 246 v_annee integer; 247BEGIN 248 -- on compte le nombre de tuples dans stock 249 SELECT 250 count(*) 251 FROM 252 stock INTO stock_total; 253 RAISE NOTICE 'taille du stock %', stock_total; 254 -- on calcule la taille de l'echantillon a 255 -- supprimer de la table stock 256 SELECT 257 round(stock_total / 10) INTO echantillon; 258 RAISE NOTICE 'taille de l''echantillon %', echantillon; 259 -- on compte le nombre de vins disponibles 260 SELECT 261 count(*) 262 FROM 263 vin INTO vins_disponibles; 264 RAISE NOTICE '% vins disponibles', vins_disponibles; 265 -- on compte le nombre de contenants disponibles 266 SELECT 267 count(*) 268 FROM 269 contenant INTO contenants_disponibles; 270 RAISE NOTICE '% contenants disponibles', contenants_disponibles; 271 -- on recupere les bornes min/max de annees 272 SELECT 273 min(annee), 274 max(annee) 275 FROM 276 stock INTO annee_min, 277 annee_max; 278 -- on fait une boucle correspondant a 1% des tuples 279 -- de la table stock 280 FOR v_tuples IN 1..echantillon LOOP 281 -- selection d'identifiant, au hasard 282 --select round(random()*contenants_disponibles) into v_contenant_id; 283 v_contenant_id := round(random() * contenants_disponibles); 284 --select round(random()*vins_disponibles) into v_vin_id; 285 v_vin_id := round(random() * vins_disponibles); 286 v_annee := round(random() * (annee_max - annee_min)) + (annee_min); 287 -- si le tuple est deja efface, ce n'est pas grave.. 288 DELETE FROM stock 289 WHERE contenant_id = v_contenant_id 290 AND vin_id = v_vin_id 291 AND annee = v_annee; 292 IF (((v_tuples % 100) = 0) OR (v_tuples = echantillon)) THEN 293 RAISE NOTICE 'stock : % sur % echantillon effaces', v_tuples, echantillon; 294 END IF; 295 END LOOP; 296 --fin boucle v_tuples 297 RETURN echantillon; 298END; 299$$; 300 301ALTER FUNCTION public.trous_stock () OWNER TO userdb; 302 303-- 304-- Name: trous_vin(); Type: FUNCTION; Schema: public; Owner: userdb 305-- 306CREATE FUNCTION trous_vin () 307 RETURNS bigint 308 LANGUAGE plpgsql 309 AS $$ 310DECLARE 311 vin_total integer; 312 echantillon integer; 313 v_vin_id integer; 314 v_tuples bigint := 0; 315 v_annee integer; 316BEGIN 317 -- on compte le nombre de tuples dans vin 318 SELECT 319 count(*) 320 FROM 321 vin INTO vin_total; 322 RAISE NOTICE '% vins disponibles', vin_total; 323 -- on calcule la taille de l'echantillon a 324 -- supprimer de la table vin 325 SELECT 326 round(vin_total / 10) INTO echantillon; 327 RAISE NOTICE 'taille de l''echantillon %', echantillon; 328 -- on fait une boucle correspondant a 10% des tuples 329 -- de la table vin 330 FOR v_tuples IN 1..echantillon LOOP 331 -- selection d'identifiant, au hasard 332 v_vin_id := round(random() * vin_total); 333 -- si le tuple est deja efface, ce n'est pas grave.. 334 -- TODO remplacer ce delete par un trigger on delete cascade 335 -- voir dans druid le schema??? 336 DELETE FROM stock 337 WHERE vin_id = v_vin_id; 338 DELETE FROM vin 339 WHERE id = v_vin_id; 340 IF (((v_tuples % 100) = 0) OR (v_tuples = echantillon)) THEN 341 RAISE NOTICE 'vin : % sur % echantillon effaces', v_tuples, echantillon; 342 END IF; 343 END LOOP; 344 --fin boucle v_tuples 345 RETURN echantillon; 346END; 347$$; 348 349ALTER FUNCTION public.trous_vin () OWNER TO userdb; 350 351SET default_tablespace = ''; 352 353SET default_with_oids = FALSE; 354 355-- 356-- Name: appellation; Type: TABLE; Schema: public; Owner: userdb 357-- 358CREATE TABLE appellation ( 359 id integer NOT NULL, 360 libelle text NOT NULL, 361 region_id integer 362) 363WITH ( 364 autovacuum_enabled = OFF 365); 366 367ALTER TABLE appellation OWNER TO userdb; 368 369-- 370-- Name: appellation_id_seq; Type: SEQUENCE; Schema: public; Owner: userdb 371-- 372CREATE SEQUENCE appellation_id_seq 373 START WITH 1 374 INCREMENT BY 1 375 NO MINVALUE 376 NO MAXVALUE 377 CACHE 1; 378 379ALTER TABLE appellation_id_seq OWNER TO userdb; 380 381-- 382-- Name: appellation_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: userdb 383-- 384ALTER SEQUENCE appellation_id_seq OWNED BY appellation.id; 385 386-- 387-- Name: contenant; Type: TABLE; Schema: public; Owner: userdb 388-- 389CREATE TABLE contenant ( 390 id integer NOT NULL, 391 contenance real NOT NULL, 392 libelle text 393) 394WITH ( 395 autovacuum_enabled = OFF, 396 fillfactor = '20' 397); 398 399ALTER TABLE contenant OWNER TO userdb; 400 401-- 402-- Name: contenant_id_seq; Type: SEQUENCE; Schema: public; Owner: userdb 403-- 404CREATE SEQUENCE contenant_id_seq 405 START WITH 1 406 INCREMENT BY 1 407 NO MINVALUE 408 NO MAXVALUE 409 CACHE 1; 410 411ALTER TABLE contenant_id_seq OWNER TO userdb; 412 413-- 414-- Name: contenant_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: userdb 415-- 416ALTER SEQUENCE contenant_id_seq OWNED BY contenant.id; 417 418-- 419-- Name: recoltant; Type: TABLE; Schema: public; Owner: userdb 420-- 421CREATE TABLE recoltant ( 422 id integer NOT NULL, 423 nom text, 424 adresse text 425) 426WITH ( 427 autovacuum_enabled = OFF 428); 429 430ALTER TABLE recoltant OWNER TO userdb; 431 432-- 433-- Name: recoltant_id_seq; Type: SEQUENCE; Schema: public; Owner: userdb 434-- 435CREATE SEQUENCE recoltant_id_seq 436 START WITH 1 437 INCREMENT BY 1 438 NO MINVALUE 439 NO MAXVALUE 440 CACHE 1; 441 442ALTER TABLE recoltant_id_seq OWNER TO userdb; 443 444-- 445-- Name: recoltant_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: userdb 446-- 447ALTER SEQUENCE recoltant_id_seq OWNED BY recoltant.id; 448 449-- 450-- Name: region; Type: TABLE; Schema: public; Owner: userdb 451-- 452CREATE TABLE region ( 453 id integer NOT NULL, 454 libelle text NOT NULL 455) 456WITH ( 457 autovacuum_enabled = OFF 458); 459 460ALTER TABLE region OWNER TO userdb; 461 462-- 463-- Name: region_id_seq; Type: SEQUENCE; Schema: public; Owner: userdb 464-- 465CREATE SEQUENCE region_id_seq 466 START WITH 1 467 INCREMENT BY 1 468 NO MINVALUE 469 NO MAXVALUE 470 CACHE 1; 471 472ALTER TABLE region_id_seq OWNER TO userdb; 473 474-- 475-- Name: region_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: userdb 476-- 477ALTER SEQUENCE region_id_seq OWNED BY region.id; 478 479-- 480-- Name: stock; Type: TABLE; Schema: public; Owner: userdb 481-- 482CREATE TABLE stock ( 483 vin_id integer NOT NULL, 484 contenant_id integer NOT NULL, 485 annee integer NOT NULL, 486 nombre integer NOT NULL 487) 488WITH ( 489 autovacuum_enabled = OFF 490); 491 492ALTER TABLE stock OWNER TO userdb; 493 494-- 495-- Name: type_vin; Type: TABLE; Schema: public; Owner: userdb 496-- 497CREATE TABLE type_vin ( 498 id integer NOT NULL, 499 libelle text NOT NULL 500) 501WITH ( 502 autovacuum_enabled = OFF 503); 504 505ALTER TABLE type_vin OWNER TO userdb; 506 507-- 508-- Name: type_vin_id_seq; Type: SEQUENCE; Schema: public; Owner: userdb 509-- 510CREATE SEQUENCE type_vin_id_seq 511 START WITH 1 512 INCREMENT BY 1 513 NO MINVALUE 514 NO MAXVALUE 515 CACHE 1; 516 517ALTER TABLE type_vin_id_seq OWNER TO userdb; 518 519-- 520-- Name: type_vin_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: userdb 521-- 522ALTER SEQUENCE type_vin_id_seq OWNED BY type_vin.id; 523 524-- 525-- Name: vin; Type: TABLE; Schema: public; Owner: userdb 526-- 527CREATE TABLE vin ( 528 id integer NOT NULL, 529 recoltant_id integer, 530 appellation_id integer NOT NULL, 531 type_vin_id integer NOT NULL 532) 533WITH ( 534 autovacuum_enabled = OFF 535); 536 537ALTER TABLE vin OWNER TO userdb; 538 539-- 540-- Name: vin_id_seq; Type: SEQUENCE; Schema: public; Owner: userdb 541-- 542CREATE SEQUENCE vin_id_seq 543 START WITH 1 544 INCREMENT BY 1 545 NO MINVALUE 546 NO MAXVALUE 547 CACHE 1; 548 549ALTER TABLE vin_id_seq OWNER TO userdb; 550 551-- 552-- Name: vin_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: userdb 553-- 554ALTER SEQUENCE vin_id_seq OWNED BY vin.id; 555 556-- 557-- Name: appellation id; Type: DEFAULT; Schema: public; Owner: userdb 558-- 559ALTER TABLE ONLY appellation 560 ALTER COLUMN id SET DEFAULT nextval('appellation_id_seq'::regclass); 561 562-- 563-- Name: contenant id; Type: DEFAULT; Schema: public; Owner: userdb 564-- 565ALTER TABLE ONLY contenant 566 ALTER COLUMN id SET DEFAULT nextval('contenant_id_seq'::regclass); 567 568-- 569-- Name: recoltant id; Type: DEFAULT; Schema: public; Owner: userdb 570-- 571ALTER TABLE ONLY recoltant 572 ALTER COLUMN id SET DEFAULT nextval('recoltant_id_seq'::regclass); 573 574-- 575-- Name: region id; Type: DEFAULT; Schema: public; Owner: userdb 576-- 577ALTER TABLE ONLY region 578 ALTER COLUMN id SET DEFAULT nextval('region_id_seq'::regclass); 579 580-- 581-- Name: type_vin id; Type: DEFAULT; Schema: public; Owner: userdb 582-- 583ALTER TABLE ONLY type_vin 584 ALTER COLUMN id SET DEFAULT nextval('type_vin_id_seq'::regclass); 585 586-- 587-- Name: vin id; Type: DEFAULT; Schema: public; Owner: userdb 588-- 589ALTER TABLE ONLY vin 590 ALTER COLUMN id SET DEFAULT nextval('vin_id_seq'::regclass); 591 592-- 593-- Name: appellation appellation_libelle_key; Type: CONSTRAINT; Schema: public; Owner: userdb 594-- 595ALTER TABLE ONLY appellation 596 ADD CONSTRAINT appellation_libelle_key UNIQUE (libelle); 597 598-- 599-- Name: appellation appellation_pkey; Type: CONSTRAINT; Schema: public; Owner: userdb 600-- 601ALTER TABLE ONLY appellation 602 ADD CONSTRAINT appellation_pkey PRIMARY KEY (id); 603 604-- 605-- Name: contenant contenant_pkey; Type: CONSTRAINT; Schema: public; Owner: userdb 606-- 607ALTER TABLE ONLY contenant 608 ADD CONSTRAINT contenant_pkey PRIMARY KEY (id); 609 610-- 611-- Name: recoltant recoltant_pkey; Type: CONSTRAINT; Schema: public; Owner: userdb 612-- 613ALTER TABLE ONLY recoltant 614 ADD CONSTRAINT recoltant_pkey PRIMARY KEY (id); 615 616-- 617-- Name: region region_pkey; Type: CONSTRAINT; Schema: public; Owner: userdb 618-- 619ALTER TABLE ONLY region 620 ADD CONSTRAINT region_pkey PRIMARY KEY (id); 621 622-- 623-- Name: stock stock_pkey; Type: CONSTRAINT; Schema: public; Owner: userdb 624-- 625ALTER TABLE ONLY stock 626 ADD CONSTRAINT stock_pkey PRIMARY KEY (vin_id, contenant_id, annee); 627 628-- 629-- Name: type_vin type_vin_pkey; Type: CONSTRAINT; Schema: public; Owner: userdb 630-- 631ALTER TABLE ONLY type_vin 632 ADD CONSTRAINT type_vin_pkey PRIMARY KEY (id); 633 634-- 635-- Name: vin vin_pkey; Type: CONSTRAINT; Schema: public; Owner: userdb 636-- 637ALTER TABLE ONLY vin 638 ADD CONSTRAINT vin_pkey PRIMARY KEY (id); 639 640-- 641-- Name: appellation appellation_region_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: userdb 642-- 643ALTER TABLE ONLY appellation 644 ADD CONSTRAINT appellation_region_id_fkey FOREIGN KEY (region_id) REFERENCES region (id) ON DELETE CASCADE; 645 646-- 647-- Name: stock stock_contenant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: userdb 648-- 649ALTER TABLE ONLY stock 650 ADD CONSTRAINT stock_contenant_id_fkey FOREIGN KEY (contenant_id) REFERENCES contenant (id) ON DELETE CASCADE; 651 652-- 653-- Name: stock stock_vin_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: userdb 654-- 655ALTER TABLE ONLY stock 656 ADD CONSTRAINT stock_vin_id_fkey FOREIGN KEY (vin_id) REFERENCES vin (id) ON DELETE CASCADE; 657 658-- 659-- Name: vin vin_appellation_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: userdb 660-- 661ALTER TABLE ONLY vin 662 ADD CONSTRAINT vin_appellation_id_fkey FOREIGN KEY (appellation_id) REFERENCES appellation (id) ON DELETE CASCADE; 663 664-- 665-- Name: vin vin_recoltant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: userdb 666-- 667ALTER TABLE ONLY vin 668 ADD CONSTRAINT vin_recoltant_id_fkey FOREIGN KEY (recoltant_id) REFERENCES recoltant (id) ON DELETE CASCADE; 669 670-- 671-- Name: vin vin_type_vin_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: userdb 672-- 673ALTER TABLE ONLY vin 674 ADD CONSTRAINT vin_type_vin_id_fkey FOREIGN KEY (type_vin_id) REFERENCES type_vin (id) ON DELETE CASCADE; 675 676-- 677-- PostgreSQL database dump complete 678-- 679