| 1 |
-- |
| 2 |
-- START / |
| 3 |
-- passage version 4.4.5 du sig interne |
| 4 |
-- Mars 2015 |
| 5 |
-- |
| 6 |
|
| 7 |
ALTER TABLE om_sig_map ADD COLUMN champ_idx character varying(30); |
| 8 |
ALTER TABLE om_sig_map ADD COLUMN util_idx boolean; |
| 9 |
ALTER TABLE om_sig_map ADD COLUMN util_reqmo boolean; |
| 10 |
ALTER TABLE om_sig_map ADD COLUMN util_recherche boolean; |
| 11 |
ALTER TABLE om_sig_map ADD COLUMN source_flux integer; |
| 12 |
ALTER TABLE om_sig_map ADD COLUMN fond_default character varying(10); |
| 13 |
|
| 14 |
ALTER TABLE om_sig_map_comp ADD COLUMN comp_champ_idx character varying(30); |
| 15 |
|
| 16 |
UPDATE om_sig_map_comp SET comp_champ_idx = id from om_sig_map where om_sig_map_comp.om_sig_map_comp = om_sig_map.om_sig_map; |
| 17 |
|
| 18 |
UPDATE om_sig_map SET champ_idx = id, util_idx= true, util_reqmo=false, util_recherche = false; |
| 19 |
UPDATE om_sig_map SET fond_default='osm' WHERE fond_default IS NULL AND fond_osm = 'Oui'; |
| 20 |
UPDATE om_sig_map SET fond_default='Bing' WHERE fond_default IS NULL AND fond_bing = 'Oui'; |
| 21 |
UPDATE om_sig_map SET fond_default='Google' WHERE fond_default IS NULL AND fond_sat = 'Oui'; |
| 22 |
UPDATE om_sig_map SET fond_default=a.wms::text FROM (SELECT om_sig_map, min(om_sig_map_wms) as wms FROM om_sig_map_wms where baselayer = 'Oui' group by om_sig_map) a where a.om_sig_map=om_sig_map.om_sig_map; |
| 23 |
|
| 24 |
|
| 25 |
ALTER TABLE om_sig_map ALTER COLUMN champ_idx SET NOT NULL; |
| 26 |
ALTER TABLE om_sig_map ALTER COLUMN fond_default SET NOT NULL; |
| 27 |
ALTER TABLE om_sig_map ALTER COLUMN fond_osm DROP NOT NULL; |
| 28 |
ALTER TABLE om_sig_map ALTER COLUMN fond_bing DROP NOT NULL; |
| 29 |
ALTER TABLE om_sig_map ALTER COLUMN fond_sat DROP NOT NULL; |
| 30 |
ALTER TABLE om_sig_map ALTER COLUMN layer_info DROP NOT NULL; |
| 31 |
ALTER TABLE om_sig_map ALTER COLUMN maj DROP NOT NULL; |
| 32 |
|
| 33 |
ALTER TABLE ONLY om_sig_map |
| 34 |
ADD CONSTRAINT om_sig_map_om_sig_map_fkey FOREIGN KEY (source_flux) REFERENCES om_sig_map(om_sig_map); |
| 35 |
|
| 36 |
|
| 37 |
ALTER TABLE om_sig_map ALTER COLUMN fond_osm TYPE boolean USING CASE WHEN fond_osm='Oui' THEN true ELSE false END; |
| 38 |
ALTER TABLE om_sig_map ALTER COLUMN fond_bing TYPE boolean USING CASE WHEN fond_bing='Oui' THEN true ELSE false END; |
| 39 |
ALTER TABLE om_sig_map ALTER COLUMN fond_sat TYPE boolean USING CASE WHEN fond_sat='Oui' THEN true ELSE false END; |
| 40 |
ALTER TABLE om_sig_map ALTER COLUMN layer_info TYPE boolean USING CASE WHEN layer_info='Oui' THEN true ELSE false END; |
| 41 |
|
| 42 |
ALTER TABLE om_sig_map_comp ALTER COLUMN actif TYPE boolean USING CASE WHEN actif='Oui' THEN true ELSE false END; |
| 43 |
|
| 44 |
|
| 45 |
UPDATE om_sig_map_comp SET ordre = ordre +1; |
| 46 |
INSERT INTO om_sig_map_comp(om_sig_map_comp, om_sig_map, libelle, ordre, actif, comp_maj, comp_table_update, comp_champ_idx, comp_champ, type_geometrie) |
| 47 |
SELECT nextval('om_sig_map_comp_seq'), om_sig_map, lib_geometrie, 0, actif, maj, table_update, champ_idx, champ, type_geometrie |
| 48 |
FROM om_sig_map; |
| 49 |
ALTER TABLE om_sig_map DROP COLUMN lib_geometrie; |
| 50 |
ALTER TABLE om_sig_map DROP COLUMN maj; |
| 51 |
ALTER TABLE om_sig_map DROP COLUMN table_update; |
| 52 |
ALTER TABLE om_sig_map DROP COLUMN champ_idx; |
| 53 |
ALTER TABLE om_sig_map DROP COLUMN champ; |
| 54 |
ALTER TABLE om_sig_map DROP COLUMN type_geometrie; |
| 55 |
|
| 56 |
CREATE TABLE om_sig_extent ( |
| 57 |
om_sig_extent integer NOT NULL, |
| 58 |
nom character varying(150), |
| 59 |
extent character varying(150), |
| 60 |
valide boolean |
| 61 |
); |
| 62 |
|
| 63 |
CREATE SEQUENCE om_sig_extent_seq |
| 64 |
START WITH 1 |
| 65 |
INCREMENT BY 1 |
| 66 |
NO MINVALUE |
| 67 |
NO MAXVALUE |
| 68 |
CACHE 1; |
| 69 |
CREATE INDEX om_sig_extent_nom_idx |
| 70 |
ON om_sig_extent |
| 71 |
(nom ); |
| 72 |
|
| 73 |
ALTER TABLE ONLY om_sig_extent |
| 74 |
ADD CONSTRAINT om_sig_extent_pkey PRIMARY KEY (om_sig_extent); |
| 75 |
|
| 76 |
ALTER SEQUENCE om_sig_extent_seq OWNED BY om_sig_extent.om_sig_extent; |
| 77 |
|
| 78 |
INSERT INTO om_droit (om_droit, libelle, om_profil) SELECT nextval('om_droit_seq'), 'om_sig_extent', om_profil FROM om_droit WHERE libelle = 'om_sig_map'; |
| 79 |
|
| 80 |
\i init_sig_extent.sql |
| 81 |
\set schema '\'openads\'' |
| 82 |
\i update_sequences.sql |
| 83 |
|
| 84 |
INSERT INTO om_sig_extent |
| 85 |
SELECT nextval('om_sig_extent_seq'), 'USER: '||id, etendue |
| 86 |
FROM (select min(om_sig_map) as id, etendue from om_sig_map group by etendue) et_utilise |
| 87 |
WHERE etendue NOT IN (SELECT extent FROM om_sig_extent); |
| 88 |
|
| 89 |
ALTER TABLE om_sig_map ADD COLUMN om_sig_extent integer; |
| 90 |
UPDATE om_sig_map SET om_sig_extent=o.om_sig_extent FROM om_sig_extent o where o.extent=etendue; |
| 91 |
ALTER TABLE om_sig_map ALTER COLUMN om_sig_extent SET NOT NULL; |
| 92 |
ALTER TABLE om_sig_map DROP COLUMN etendue; |
| 93 |
ALTER TABLE ONLY om_sig_map |
| 94 |
ADD CONSTRAINT om_sig_map_om_sig_extent_fkey FOREIGN KEY (om_sig_extent) REFERENCES om_sig_extent(om_sig_extent); |
| 95 |
ALTER TABLE om_sig_map ADD COLUMN restrict_extent boolean; |
| 96 |
UPDATE om_sig_map SET restrict_extent=true; |
| 97 |
|
| 98 |
ALTER TABLE om_sig_map ADD COLUMN sld_marqueur character varying(254); |
| 99 |
ALTER TABLE om_sig_map ADD COLUMN sld_data character varying(254); |
| 100 |
ALTER TABLE om_sig_map ADD COLUMN point_centrage geometry(Point,2154); |
| 101 |
|
| 102 |
ALTER TABLE om_sig_map_comp ADD COLUMN obj_class character varying(100); |
| 103 |
UPDATE om_sig_map_comp SET obj_class= o.id FROM om_sig_map o WHERE o.om_sig_map=om_sig_map_comp.om_sig_map; |
| 104 |
ALTER TABLE om_sig_map_comp ALTER COLUMN obj_class SET NOT NULL; |
| 105 |
ALTER TABLE om_sig_map_comp ALTER COLUMN comp_maj TYPE boolean USING CASE WHEN comp_maj='Oui' THEN true ELSE false END; |
| 106 |
|
| 107 |
|
| 108 |
CREATE TABLE om_sig_flux ( |
| 109 |
om_sig_flux integer NOT NULL, |
| 110 |
libelle character varying(50) NOT NULL, |
| 111 |
om_collectivite integer NOT NULL, |
| 112 |
id character varying(50) NOT NULL, |
| 113 |
attribution character varying(150), |
| 114 |
chemin character varying(255) NOT NULL, |
| 115 |
couches character varying(255) NOT NULL, |
| 116 |
cache_type character varying(3), |
| 117 |
cache_gfi_chemin character varying(255), |
| 118 |
cache_gfi_couches character varying(255) |
| 119 |
); |
| 120 |
CREATE SEQUENCE om_sig_flux_seq |
| 121 |
START WITH 1 |
| 122 |
INCREMENT BY 1 |
| 123 |
NO MINVALUE |
| 124 |
NO MAXVALUE |
| 125 |
CACHE 1; |
| 126 |
ALTER SEQUENCE om_sig_flux_seq OWNED BY om_sig_flux.om_sig_flux; |
| 127 |
|
| 128 |
ALTER TABLE om_sig_map_wms ALTER COLUMN visibility TYPE boolean USING CASE WHEN visibility='Oui' THEN true ELSE false END; |
| 129 |
ALTER TABLE om_sig_map_wms ALTER COLUMN panier TYPE boolean USING CASE WHEN panier='Oui' THEN true ELSE false END; |
| 130 |
ALTER TABLE om_sig_map_wms ALTER COLUMN baselayer TYPE boolean USING CASE WHEN baselayer='Oui' THEN true ELSE false END; |
| 131 |
ALTER TABLE om_sig_map_wms ALTER COLUMN singletile TYPE boolean USING CASE WHEN singletile='Oui' THEN true ELSE false END; |
| 132 |
|
| 133 |
CREATE TABLE om_sig_map_flux ( |
| 134 |
om_sig_map_flux integer NOT NULL, |
| 135 |
om_sig_flux integer NOT NULL, |
| 136 |
om_sig_map integer NOT NULL, |
| 137 |
ol_map character varying(50) NOT NULL, |
| 138 |
ordre integer NOT NULL, |
| 139 |
visibility boolean, |
| 140 |
panier boolean, |
| 141 |
pa_nom character varying(50), |
| 142 |
pa_layer character varying(50), |
| 143 |
pa_attribut character varying(50), |
| 144 |
pa_encaps character varying(3), |
| 145 |
pa_sql text, |
| 146 |
pa_type_geometrie character varying(30), |
| 147 |
sql_filter text, |
| 148 |
baselayer boolean, |
| 149 |
singletile boolean, |
| 150 |
maxzoomlevel integer |
| 151 |
); |
| 152 |
CREATE SEQUENCE om_sig_map_flux_seq |
| 153 |
START WITH 1 |
| 154 |
INCREMENT BY 1 |
| 155 |
NO MINVALUE |
| 156 |
NO MAXVALUE |
| 157 |
CACHE 1; |
| 158 |
ALTER SEQUENCE om_sig_map_flux_seq OWNED BY om_sig_map_flux.om_sig_map_flux; |
| 159 |
|
| 160 |
ALTER TABLE ONLY om_sig_flux |
| 161 |
ADD CONSTRAINT om_sig_flux_pkey PRIMARY KEY (om_sig_flux); |
| 162 |
|
| 163 |
ALTER TABLE ONLY om_sig_map_flux |
| 164 |
ADD CONSTRAINT om_sig_map_flux_pkey PRIMARY KEY (om_sig_map_flux); |
| 165 |
|
| 166 |
ALTER TABLE ONLY om_sig_flux |
| 167 |
ADD CONSTRAINT om_sig_flux_om_collectivite_fkey FOREIGN KEY (om_collectivite) REFERENCES om_collectivite(om_collectivite); |
| 168 |
|
| 169 |
ALTER TABLE ONLY om_sig_map_flux |
| 170 |
ADD CONSTRAINT om_sig_map_flux_om_sig_map_fkey FOREIGN KEY (om_sig_map) REFERENCES om_sig_map(om_sig_map); |
| 171 |
|
| 172 |
ALTER TABLE ONLY om_sig_map_flux |
| 173 |
ADD CONSTRAINT om_sig_map_flux_om_sig_flux_fkey FOREIGN KEY (om_sig_flux) REFERENCES om_sig_flux(om_sig_flux); |
| 174 |
|
| 175 |
ALTER TABLE om_sig_wms ADD COLUMN attribution character varying(150); |
| 176 |
UPDATE om_sig_wms SET attribution = libelle; |
| 177 |
|
| 178 |
INSERT INTO om_sig_flux( |
| 179 |
om_sig_flux, libelle, om_collectivite, id, attribution, cache_type, chemin, |
| 180 |
couches, cache_gfi_chemin, cache_gfi_couches) |
| 181 |
SELECT om_sig_wms, libelle, om_collectivite, id, attribution, cache_type, chemin, |
| 182 |
couches, cache_gfi_chemin, cache_gfi_couches |
| 183 |
FROM om_sig_wms; |
| 184 |
|
| 185 |
INSERT INTO om_sig_map_flux SELECT * FROM om_sig_map_wms; |
| 186 |
|
| 187 |
SELECT setval('om_sig_flux_seq',(SELECT max(om_sig_flux) FROM om_sig_flux)); |
| 188 |
SELECT setval('om_sig_map_flux_seq',(SELECT max(om_sig_map_flux) FROM om_sig_map_flux)); |
| 189 |
|
| 190 |
DROP SEQUENCE om_sig_map_wms_seq; |
| 191 |
DROP TABLE om_sig_map_wms; |
| 192 |
|
| 193 |
DROP SEQUENCE om_sig_wms_seq; |
| 194 |
DROP TABLE om_sig_wms; |
| 195 |
UPDATE om_droit SET libelle = replace(libelle,'om_sig_wms','om_sig_flux') WHERE libelle LIKE 'om_sig_wms%'; |
| 196 |
UPDATE om_droit SET libelle = replace(libelle,'om_sig_map_wms','om_sig_map_flux') WHERE libelle LIKE 'om_sig_map_wms%'; |
| 197 |
|
| 198 |
-- |
| 199 |
-- END / |
| 200 |
-- passage version 4.4.5 du sig interne |
| 201 |
-- Mars 2015 |
| 202 |
-- |
Parent Directory
| 
Revision Log