data/pgsql/v3.0.0rc1.sql

--------------------------------------------------------------------------------
-- Script de mise à jour vers la version v3.0.0-rc1
--
-- @package openfoncier
-- @version SVN : $Id$
--------------------------------------------------------------------------------

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