data/pgsql/v3.0.0rc1.sql

-- passage de la version 3.0.0rc1

-- schema 
-- SET search_path = openfoncier, pg_catalog;
update om_version set om_version='3.0.0-rc1';
-- ----------------------------------------------------------------
-- version 3.0.0b6 multi geometrie + geometrie complementaire + wms
-- mise a niveau de la base en om 4.2.0
-- ----------------------------------------------------------------
-- ajout geometrie multipolygone dans dossier
SELECT AddGeometryColumn ( 'openfoncier', 'dossier', 'geom1', 2154 , 'MULTIPOLYGON', 2 );
-- renommer la table om_sig_point
ALTER TABLE om_sig_point RENAME TO om_sig_map;
-- supprimer la contrainte de cle primaire et secondaire
ALTER TABLE om_sig_map DROP constraint om_sig_point_pkey;
ALTER TABLE om_sig_map DROP constraint om_sig_point_om_collectivite_fkey;
-- renommer la cle primaire om_sig_point -> om_sig_map
ALTER TABLE ONLY om_sig_map RENAME COLUMN om_sig_point TO om_sig_map;
-- supprimer la sequence
DROP SEQUENCE om_sig_point_seq;
-- ajouter les champs nouveaux
ALTER TABLE ONLY om_sig_map ADD COLUMN type_geometrie character varying(30);
ALTER TABLE ONLY om_sig_map ADD COLUMN lib_geometrie character varying(50);
-- integrite referentielle
ALTER TABLE ONLY om_sig_map
    ADD CONSTRAINT om_sig_map_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);
-- cle primaire
ALTER TABLE ONLY om_sig_map
    ADD CONSTRAINT om_sig_map_pkey PRIMARY KEY (om_sig_map);

-- tables supplementaires SIG

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,
  PRIMARY KEY  (om_sig_wms)
);

CREATE TABLE om_sig_map_wms
(
  om_sig_map_wms integer NOT NULL,
  om_sig_wms integer NOT NULL,
  om_sig_map integer NOT NULL,
  ol_map character varying(50) NOT NULL,
  ordre integer NOT NULL,
  visibility character varying(3) NOT NULL,
  panier character varying(3),
  pa_nom character varying(50),
  pa_layer character varying(50),
  pa_attribut character varying(50),
  pa_encaps character varying(3),
  pa_sql text,
  pa_type_geometrie character varying(30),
  PRIMARY KEY  (om_sig_map_wms)
);

CREATE TABLE om_sig_map_comp
(
  om_sig_map_comp integer NOT NULL,
  om_sig_map 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),
  PRIMARY KEY  (om_sig_map_comp)
);

-- integrite referentielle

ALTER TABLE ONLY om_sig_map_wms
    ADD CONSTRAINT om_sig_map_wms_om_sig_map_fkey FOREIGN KEY (om_sig_map) REFERENCES om_sig_map(om_sig_map);
ALTER TABLE ONLY om_sig_map_wms
    ADD CONSTRAINT om_sig_map_wms_om_sig_wms_fkey FOREIGN KEY (om_sig_wms) REFERENCES om_sig_wms(om_sig_wms);
ALTER TABLE ONLY om_sig_map_comp
    ADD CONSTRAINT om_sig_map_comp_om_sig_map_fkey FOREIGN KEY (om_sig_map) REFERENCES om_sig_map(om_sig_map);
ALTER TABLE ONLY om_sig_wms
    ADD CONSTRAINT om_sig_wms_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);


-- sequence

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

  
CREATE SEQUENCE om_sig_map_wms_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
  
CREATE SEQUENCE om_sig_map_comp_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE SEQUENCE om_sig_map_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
  
-- nouveau om_parametres


INSERT INTO om_parametre (om_parametre, libelle, valeur, om_collectivite) VALUES
        (nextval('om_parametre_seq'), 'numero_unique', '0', 1),
        (nextval('om_parametre_seq'), 'lettre', 'R',  1),
        (nextval('om_parametre_seq'), 'ville', 'Arles', 1),
        (nextval('om_parametre_seq'), 'cp', '13200',  1);
1	-- passage de la version 3.0.0rc1
2
3	-- schema
4	-- SET search_path = openfoncier, pg_catalog;
5	update om_version set om_version='3.0.0-rc1';
6	-- ----------------------------------------------------------------
7	-- version 3.0.0b6 multi geometrie + geometrie complementaire + wms
8	-- mise a niveau de la base en om 4.2.0
9	-- ----------------------------------------------------------------
10	-- ajout geometrie multipolygone dans dossier
11	SELECT AddGeometryColumn ( 'openfoncier', 'dossier', 'geom1', 2154 , 'MULTIPOLYGON', 2 );
12	-- renommer la table om_sig_point
13	ALTER TABLE om_sig_point RENAME TO om_sig_map;
14	-- supprimer la contrainte de cle primaire et secondaire
15	ALTER TABLE om_sig_map DROP constraint om_sig_point_pkey;
16	ALTER TABLE om_sig_map DROP constraint om_sig_point_om_collectivite_fkey;
17	-- renommer la cle primaire om_sig_point -> om_sig_map
18	ALTER TABLE ONLY om_sig_map RENAME COLUMN om_sig_point TO om_sig_map;
19	-- supprimer la sequence
20	DROP SEQUENCE om_sig_point_seq;
21	-- ajouter les champs nouveaux
22	ALTER TABLE ONLY om_sig_map ADD COLUMN type_geometrie character varying(30);
23	ALTER TABLE ONLY om_sig_map ADD COLUMN lib_geometrie character varying(50);
24	-- integrite referentielle
25	ALTER TABLE ONLY om_sig_map
26	ADD CONSTRAINT om_sig_map_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);
27	-- cle primaire
28	ALTER TABLE ONLY om_sig_map
29	ADD CONSTRAINT om_sig_map_pkey PRIMARY KEY (om_sig_map);
30
31	-- tables supplementaires SIG
32
33	CREATE TABLE om_sig_wms
34	(
35	om_sig_wms integer NOT NULL,
36	libelle character varying(50) NOT NULL,
37	om_collectivite integer NOT NULL,
38	id character varying(50) NOT NULL,
39	chemin character varying(255) NOT NULL,
40	couches character varying(255) NOT NULL,
41	PRIMARY KEY (om_sig_wms)
42	);
43
44	CREATE TABLE om_sig_map_wms
45	(
46	om_sig_map_wms integer NOT NULL,
47	om_sig_wms integer NOT NULL,
48	om_sig_map integer NOT NULL,
49	ol_map character varying(50) NOT NULL,
50	ordre integer NOT NULL,
51	visibility character varying(3) NOT NULL,
52	panier character varying(3),
53	pa_nom character varying(50),
54	pa_layer character varying(50),
55	pa_attribut character varying(50),
56	pa_encaps character varying(3),
57	pa_sql text,
58	pa_type_geometrie character varying(30),
59	PRIMARY KEY (om_sig_map_wms)
60	);
61
62	CREATE TABLE om_sig_map_comp
63	(
64	om_sig_map_comp integer NOT NULL,
65	om_sig_map integer NOT NULL,
66	libelle character varying(50) NOT NULL,
67	ordre integer NOT NULL,
68	actif character varying(3),
69	comp_maj character varying(3),
70	type_geometrie character varying(30),
71	comp_table_update character varying(30),
72	comp_champ character varying(30),
73	PRIMARY KEY (om_sig_map_comp)
74	);
75
76	-- integrite referentielle
77
78	ALTER TABLE ONLY om_sig_map_wms
79	ADD CONSTRAINT om_sig_map_wms_om_sig_map_fkey FOREIGN KEY (om_sig_map) REFERENCES om_sig_map(om_sig_map);
80	ALTER TABLE ONLY om_sig_map_wms
81	ADD CONSTRAINT om_sig_map_wms_om_sig_wms_fkey FOREIGN KEY (om_sig_wms) REFERENCES om_sig_wms(om_sig_wms);
82	ALTER TABLE ONLY om_sig_map_comp
83	ADD CONSTRAINT om_sig_map_comp_om_sig_map_fkey FOREIGN KEY (om_sig_map) REFERENCES om_sig_map(om_sig_map);
84	ALTER TABLE ONLY om_sig_wms
85	ADD CONSTRAINT om_sig_wms_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite);
86
87
88	-- sequence
89
90	CREATE SEQUENCE om_sig_wms_seq
91	INCREMENT 1
92	MINVALUE 1
93	MAXVALUE 9223372036854775807
94	START 1
95	CACHE 1;
96
97
98	CREATE SEQUENCE om_sig_map_wms_seq
99	INCREMENT 1
100	MINVALUE 1
101	MAXVALUE 9223372036854775807
102	START 1
103	CACHE 1;
104
105	CREATE SEQUENCE om_sig_map_comp_seq
106	INCREMENT 1
107	MINVALUE 1
108	MAXVALUE 9223372036854775807
109	START 1
110	CACHE 1;
111
112	CREATE SEQUENCE om_sig_map_seq
113	INCREMENT 1
114	MINVALUE 1
115	MAXVALUE 9223372036854775807
116	START 1
117	CACHE 1;
118
119	-- nouveau om_parametres
120
121
122	INSERT INTO om_parametre (om_parametre, libelle, valeur, om_collectivite) VALUES
123	(nextval('om_parametre_seq'), 'numero_unique', '0', 1),
124	(nextval('om_parametre_seq'), 'lettre', 'R', 1),
125	(nextval('om_parametre_seq'), 'ville', 'Arles', 1),
126	(nextval('om_parametre_seq'), 'cp', '13200', 1);