DB/create.sql

   1 SET client_encoding = 'UTF-8';
   2 SET check_function_bodies = false;
   3 SET client_min_messages = warning;
   4 SET escape_string_warning = off;
   5
   6
   7 SET search_path = public, pg_catalog;
   8
   9
  10 CREATE SEQUENCE appart_id_seq
  11     INCREMENT BY 1
  12     NO MAXVALUE
  13     NO MINVALUE
  14     CACHE 1;
  15
  16 ALTER TABLE public.appart_id_seq OWNER TO appart;
  17
  18
  19 SET default_tablespace = '';
  20
  21 SET default_with_oids = false;
  22
  23
  24 /** Version 1 **/
  25
  26
  27 CREATE TABLE appart (
  28     id integer DEFAULT nextval('appart_id_seq'::regclass) NOT NULL,
  29     prix numeric(28,0),
  30     prixcharges numeric(10,0),
  31     rue text DEFAULT ''::text NOT NULL,
  32     nbpiece character varying(28),
  33     surface numeric(28,0),
  34     description text DEFAULT ''::text NOT NULL,
  35     contact text DEFAULT ''::text NOT NULL,
  36     url character varying(200) DEFAULT ''::character varying NOT NULL,
  37     etage numeric(5,0),
  38     annee_construction numeric(28,0),
  39     points_positifs text DEFAULT ''::text NOT NULL,
  40     points_negatifs text DEFAULT ''::text NOT NULL,
  41     date_visite date,
  42     visite_indications text DEFAULT ''::text NOT NULL,
  43     statut smallint DEFAULT 0 NOT NULL,
  44     localite character varying(100) DEFAULT ''::character varying
  45 );
  46
  47
  48 ALTER TABLE public.appart OWNER TO appart;
  49
  50 COMMENT ON COLUMN appart.statut IS 'statut : 0 = pas visité 1 = on a rendez-vous, 2 = visité, 3 = déjà pris :(';
  51
  52 ALTER TABLE ONLY appart ADD CONSTRAINT appart_pkey PRIMARY KEY (id);
  53
  54 REVOKE ALL ON SCHEMA public FROM PUBLIC;
  55 REVOKE ALL ON SCHEMA public FROM postgres;
  56 GRANT ALL ON SCHEMA public TO postgres;
  57 GRANT ALL ON SCHEMA public TO PUBLIC;
  58
  59 REVOKE ALL ON TABLE appart FROM PUBLIC;
  60 REVOKE ALL ON TABLE appart FROM appart;
  61 GRANT ALL ON TABLE appart TO appart;
  62 GRANT ALL ON TABLE appart TO appart_group;
  63
  64 REVOKE ALL ON TABLE site FROM PUBLIC;
  65 REVOKE ALL ON TABLE site FROM appart;
  66 GRANT ALL ON TABLE site TO appart;
  67 GRANT ALL ON TABLE site TO appart_group;
  68
  69
  70 /** Version 2 **/
  71
  72 ALTER TABLE public.appart ALTER date_visite TYPE timestamp without time zone;
  73 ALTER TABLE public.appart ADD COLUMN date_libre date;
  74
  75 ALTER TABLE public.appart ADD COLUMN url_map text;
  76 ALTER TABLE public.appart ALTER COLUMN url_map SET DEFAULT '';
  77