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	fmichon	1804	--------------------------------------------------------------------------------
2			-- Script de mise à jour vers la version v3.0.0-rc1
3			--
4			-- @package openfoncier
5			-- @version SVN : $Id$
6			--------------------------------------------------------------------------------
7	fraynaud	365
8	fraynaud	352	-- 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	fraynaud	365	-- mise a niveau de la base en om 4.2.0
14	fraynaud	352	-- ----------------------------------------------------------------
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	fraynaud	363	CACHE 1;
123
124			-- nouveau om_parametres
125	fraynaud	364
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);