data/pgsql/v3.0.0-b5.sql


-- version atreal - greasque

-- schema
-- SET search_path = openfoncier, pg_catalog;

-- complement travaux
alter table dossier add travaux_complement character varying(100);

-- mise à jour des civilites
insert into civilite(civilite) values ('M.');
insert into civilite(civilite) values ('Mlle');
update dossier set demandeur_civilite='M.' where demandeur_civilite='Mr';
update dossier set demandeur_civilite='Mlle' where demandeur_civilite='Melle';
update dossier set delegataire_civilite='M.' where delegataire_civilite='Mr';
update dossier set delegataire_civilite='Mlle' where delegataire_civilite='Melle';
update proprietaire set civilite='M.' where civilite = 'Mr';
update proprietaire set civilite='Mlle' where civilite = 'Melle';
delete from civilite where civilite='Mr';
delete from civilite where civilite='Melle';

-- ajout de complement d'adresse pour le service
alter table service add adresse2 character varying(39);

-- version
update om_version set om_version='3.0.0-b5';

-- ajout de complement d'adresses dans le dossier
alter table dossier add demandeur_adresse_complement character varying(39) NOT NULL default '';
alter table dossier add delegataire_adresse_complement character varying(39) NOT NULL default '';

-- table service
alter table service add adresse2 character varying(39) NOT NULL default '';

SET search_path = opentaxepub, pg_catalog;

CREATE TABLE om_sig_wms
(
  om_sig_wms integer NOT NULL,
  libelle character varying(50) NOT NULL,
  om_collectivite integer NOT NULL,
  id character varying(50) NOT NULL,
  chemin character varying(255) NOT NULL,
  couches character varying(255) NOT NULL,
  CONSTRAINT om_sig_wms_pkey PRIMARY KEY (om_sig_wms)
  )
WITH (
  OIDS=FALSE
);
ALTER TABLE ONLY om_sig_wms
    ADD CONSTRAINT om_sig_wms_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);

CREATE TABLE om_sig_point_wms
(
  om_sig_point_wms integer NOT NULL,
  om_sig_wms integer NOT NULL,
  om_sig_point integer NOT NULL,
  ol_map character varying(50) NOT NULL,
  ordre integer NOT NULL,
  visibility character varying(3) NOT NULL,
  CONSTRAINT om_sig_point_wms_pkey PRIMARY KEY (om_sig_point_wms)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE ONLY om_sig_point_wms
    ADD CONSTRAINT om_sig_point_wms_om_sig_point_fkey FOREIGN KEY (om_sig_point) REFERENCES om_sig_point(om_sig_point);
ALTER TABLE ONLY om_sig_point_wms
    ADD CONSTRAINT om_sig_point_wms_om_sig_wms_fkey FOREIGN KEY (om_sig_wms) REFERENCES om_sig_wms(om_sig_wms);

CREATE SEQUENCE om_sig_wms_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

  
CREATE SEQUENCE om_sig_point_wms_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

  -- AB-FIN 2012-05-11 - ajout table om_sig_wms et om_sig_point_wms
  
  -- AB-DEB 2012-05-14 - ajout multi-géométrie
  
ALTER TABLE ONLY om_sig_point ADD COLUMN type_geometrie character varying(30);
ALTER TABLE ONLY om_sig_point ADD COLUMN lib_geometrie character varying(50);

CREATE TABLE om_sig_point_comp
(
  om_sig_point_comp integer NOT NULL,
  om_sig_point integer NOT NULL,
  libelle character varying(50) NOT NULL,
  ordre integer NOT NULL,
  actif character varying(3),
  comp_maj character varying(3),
  type_geometrie character varying(30),
  comp_table_update character varying(30),
  comp_champ character varying(30),
  CONSTRAINT om_sig_point_comp_pkey PRIMARY KEY (om_sig_point_comp)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE ONLY om_sig_point_comp
    ADD CONSTRAINT om_sig_point_comp_om_sig_point_fkey FOREIGN KEY (om_sig_point) REFERENCES om_sig_point(om_sig_point);

CREATE SEQUENCE om_sig_point_comp_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE om_sig_point_comp_seq OWNER TO postgres;
-- AB-FIN 2012-05-14 - ajout multi-géométrie
1	fraynaud	325
2	fraynaud	340	-- version atreal - greasque
3
4	fraynaud	325	-- schema
5			-- SET search_path = openfoncier, pg_catalog;
6
7	atreal	316	-- complement travaux
8	atreal	310	alter table dossier add travaux_complement character varying(100);
9	atreal	316
10			-- mise à jour des civilites
11	atreal	310	insert into civilite(civilite) values ('M.');
12	atreal	309	insert into civilite(civilite) values ('Mlle');
13	atreal	316	update dossier set demandeur_civilite='M.' where demandeur_civilite='Mr';
14	atreal	309	update dossier set demandeur_civilite='Mlle' where demandeur_civilite='Melle';
15	atreal	310	update dossier set delegataire_civilite='M.' where delegataire_civilite='Mr';
16	atreal	309	update dossier set delegataire_civilite='Mlle' where delegataire_civilite='Melle';
17	atreal	310	update proprietaire set civilite='M.' where civilite = 'Mr';
18	atreal	309	update proprietaire set civilite='Mlle' where civilite = 'Melle';
19			delete from civilite where civilite='Mr';
20	atreal	316	delete from civilite where civilite='Melle';
21
22			-- ajout de complement d'adresse pour le service
23			alter table service add adresse2 character varying(39);
24
25			-- version
26	fraynaud	340	update om_version set om_version='3.0.0-b5';
27	atreal	316
28			-- ajout de complement d'adresses dans le dossier
29			alter table dossier add demandeur_adresse_complement character varying(39) NOT NULL default '';
30			alter table dossier add delegataire_adresse_complement character varying(39) NOT NULL default '';
31
32	atreal	318	-- table service
33			alter table service add adresse2 character varying(39) NOT NULL default '';
34	fraynaud	340
35			SET search_path = opentaxepub, pg_catalog;
36
37			CREATE TABLE om_sig_wms
38			(
39			om_sig_wms integer NOT NULL,
40			libelle character varying(50) NOT NULL,
41			om_collectivite integer NOT NULL,
42			id character varying(50) NOT NULL,
43			chemin character varying(255) NOT NULL,
44			couches character varying(255) NOT NULL,
45			CONSTRAINT om_sig_wms_pkey PRIMARY KEY (om_sig_wms)
46			)
47			WITH (
48			OIDS=FALSE
49			);
50			ALTER TABLE ONLY om_sig_wms
51			ADD CONSTRAINT om_sig_wms_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);
52
53			CREATE TABLE om_sig_point_wms
54			(
55			om_sig_point_wms integer NOT NULL,
56			om_sig_wms integer NOT NULL,
57			om_sig_point integer NOT NULL,
58			ol_map character varying(50) NOT NULL,
59			ordre integer NOT NULL,
60			visibility character varying(3) NOT NULL,
61			CONSTRAINT om_sig_point_wms_pkey PRIMARY KEY (om_sig_point_wms)
62			)
63			WITH (
64			OIDS=FALSE
65			);
66			ALTER TABLE ONLY om_sig_point_wms
67			ADD CONSTRAINT om_sig_point_wms_om_sig_point_fkey FOREIGN KEY (om_sig_point) REFERENCES om_sig_point(om_sig_point);
68			ALTER TABLE ONLY om_sig_point_wms
69			ADD CONSTRAINT om_sig_point_wms_om_sig_wms_fkey FOREIGN KEY (om_sig_wms) REFERENCES om_sig_wms(om_sig_wms);
70
71			CREATE SEQUENCE om_sig_wms_seq
72			INCREMENT 1
73			MINVALUE 1
74			MAXVALUE 9223372036854775807
75			START 1
76			CACHE 1;
77
78
79			CREATE SEQUENCE om_sig_point_wms_seq
80			INCREMENT 1
81			MINVALUE 1
82			MAXVALUE 9223372036854775807
83			START 1
84			CACHE 1;
85
86			-- AB-FIN 2012-05-11 - ajout table om_sig_wms et om_sig_point_wms
87
88			-- AB-DEB 2012-05-14 - ajout multi-géométrie
89
90			ALTER TABLE ONLY om_sig_point ADD COLUMN type_geometrie character varying(30);
91			ALTER TABLE ONLY om_sig_point ADD COLUMN lib_geometrie character varying(50);
92
93			CREATE TABLE om_sig_point_comp
94			(
95			om_sig_point_comp integer NOT NULL,
96			om_sig_point integer NOT NULL,
97			libelle character varying(50) NOT NULL,
98			ordre integer NOT NULL,
99			actif character varying(3),
100			comp_maj character varying(3),
101			type_geometrie character varying(30),
102			comp_table_update character varying(30),
103			comp_champ character varying(30),
104			CONSTRAINT om_sig_point_comp_pkey PRIMARY KEY (om_sig_point_comp)
105			)
106			WITH (
107			OIDS=FALSE
108			);
109			ALTER TABLE ONLY om_sig_point_comp
110			ADD CONSTRAINT om_sig_point_comp_om_sig_point_fkey FOREIGN KEY (om_sig_point) REFERENCES om_sig_point(om_sig_point);
111
112			CREATE SEQUENCE om_sig_point_comp_seq
113			INCREMENT 1
114			MINVALUE 1
115			MAXVALUE 9223372036854775807
116			START 1
117			CACHE 1;
118			ALTER TABLE om_sig_point_comp_seq OWNER TO postgres;
119			-- AB-FIN 2012-05-14 - ajout multi-géométrie