· 8 years ago · Jan 24, 2018, 07:46 AM
1do
2$$
3declare
4 rec record;
5 query_text text;
6begin
7 for rec in(select lower(scheme) as scheme
8 from regadm.m_projects p
9 where exists(select * from pg_namespace nsp where nsp.nspname=lower(p.scheme))
10 and lower(scheme) not in('regadm')
11 order by 1)
12 loop
13 raise info '%',rec.scheme;
14--
15 query_text:='CREATE OR REPLACE FUNCTION %schema%.sp_clear_gis_ef_schema_tables() RETURNS void
16AS
17$function$
18begin
19 delete from %schema%.d_gis_background_layer;
20 delete from %schema%.d_gis_group_layers;
21 delete from %schema%.d_gis_layer;
22 delete from %schema%.d_gis_layer_in_group;
23 delete from %schema%.d_gis_layer_meta;
24 delete from %schema%.d_gis_map;
25 delete from %schema%.d_gis_map_background_layer;
26 delete from %schema%.d_gis_map_layer;
27 delete from %schema%.d_gis_free_layer;
28 delete from %schema%.d_gis_map_theme_section;
29 delete from %schema%.d_gis_theme_frame_item;
30 delete from %schema%.d_gis_theme_item_in_frame;
31 delete from %schema%.d_gis_theme_layer;
32 delete from %schema%.d_gis_theme_layer_legend_frame;
33 delete from %schema%.d_gis_theme_legend_frame;
34 delete from %schema%.d_gis_theme_section;
35 delete from %schema%.d_gis_theme_section_layer;
36 delete from %schema%.d_gis_view_settings;
37end;
38$function$ language plpgsql';
39 execute replace(query_text,'%schema%',rec.scheme);
40
41--
42 query_text:='CREATE OR REPLACE FUNCTION %schema%.sp_clear_gis_ef_tables_in_schema() RETURNS void
43AS
44$function$
45begin
46 drop table %schema%.d_gis_background_layer CASCADE;
47 drop table %schema%.d_gis_group_layers CASCADE;
48 drop table %schema%.d_gis_layer CASCADE;
49 drop table %schema%.d_gis_layer_in_group CASCADE;
50 drop table %schema%.d_gis_layer_meta CASCADE;
51 drop table %schema%.d_gis_map CASCADE;
52 drop table %schema%.d_gis_free_layer CASCADE;
53 drop table %schema%.d_gis_map_background_layer CASCADE;
54 drop table %schema%.d_gis_map_layer CASCADE;
55 drop table %schema%.d_gis_map_theme_section CASCADE;
56 drop table %schema%.d_gis_theme_frame_item CASCADE;
57 drop table %schema%.d_gis_theme_item_in_frame CASCADE;
58 drop table %schema%.d_gis_theme_layer CASCADE;
59 drop table %schema%.d_gis_theme_layer_legend_frame CASCADE;
60 drop table %schema%.d_gis_theme_legend_frame CASCADE;
61 drop table %schema%.d_gis_theme_section CASCADE;
62 drop table %schema%.d_gis_theme_section_layer CASCADE;
63 drop table %schema%.d_gis_view_settings CASCADE;
64
65 DROP SEQUENCE %schema%.d_gis_sequence;
66end;
67$function$ language plpgsql';
68 execute replace(query_text,'%schema%',rec.scheme);
69
70--
71 query_text:='CREATE OR REPLACE FUNCTION %schema%.sp_create_gis_ef_tables_in_schema() RETURNS void
72AS
73$function$
74begin
75 CREATE SEQUENCE %schema%.d_gis_sequence
76 INCREMENT BY 1
77 MINVALUE 1
78 MAXVALUE 9223372036854775807
79 START 1;
80
81 CREATE TABLE %schema%.d_gis_layer_meta (
82 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
83 project varchar(100),
84 alias varchar(500) NOT NULL DEFAULT ''''::character varying,
85 view_alias varchar(500),
86 geo_view_alias varchar(500) NOT NULL DEFAULT ''''::character varying,
87 CONSTRAINT "PK_%schema%.d_gis_layer_meta" PRIMARY KEY ("key")
88 );
89 CREATE UNIQUE INDEX "d_gis_layer_meta_IX_LM_U" ON %schema%.d_gis_layer_meta (alias DESC,view_alias DESC,geo_view_alias DESC) ;
90
91 CREATE TABLE %schema%.d_gis_layer (
92 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
93 display_name varchar(200) NOT NULL DEFAULT ''''::character varying,
94 type_name varchar(100) NOT NULL DEFAULT ''''::character varying,
95 "name" varchar(500),
96 meta_key bigint,
97 geoserver_key bigint,
98 overriding_style varchar(200),
99 url varchar(200),
100 copyright_info varchar(200),
101 inverted_tms bool,
102 tms_source_type integer,
103 "Discriminator" varchar(128) NOT NULL DEFAULT ''''::character varying,
104 srs_key bigint,
105 CONSTRAINT "PK_%schema%.d_gis_layer" PRIMARY KEY ("key"),
106 CONSTRAINT "FK.%schema%.d_gis_layer.geoserver_key" FOREIGN KEY (geoserver_key) REFERENCES regadm.d_gis_geoserver("key") ON DELETE CASCADE,
107 CONSTRAINT "FK.%schema%.d_gis_layer.meta_key" FOREIGN KEY (meta_key) REFERENCES %schema%.d_gis_layer_meta("key") ON DELETE CASCADE,
108 CONSTRAINT "FK.%schema%.d_gis_layer.srs_key" FOREIGN KEY (srs_key) REFERENCES regadm.d_gis_srs("key") ON DELETE CASCADE
109 );
110 CREATE INDEX "d_gis_layer_IX_geoserver_key" ON %schema%.d_gis_layer (geoserver_key DESC) ;
111 CREATE INDEX "d_gis_layer_IX_meta_key" ON %schema%.d_gis_layer (meta_key DESC) ;
112 CREATE INDEX "d_gis_layer_IX_srs_key" ON %schema%.d_gis_layer (srs_key DESC) ;
113------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
114 CREATE TABLE %schema%.d_gis_background_layer (
115 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
116 content_layer_key bigint NOT NULL DEFAULT 0,
117 icon_base64 text,
118 CONSTRAINT "PK_%schema%.d_gis_background_layer" PRIMARY KEY ("key"),
119 CONSTRAINT "FK.%schema%.d_gis_background_layer.content_layer_key" FOREIGN KEY (content_layer_key) REFERENCES %schema%.d_gis_layer("key") ON DELETE CASCADE
120 );
121 CREATE INDEX "d_gis_background_layer_IX_content_layer_key" ON %schema%.d_gis_background_layer (content_layer_key DESC) ;
122------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
123 CREATE TABLE %schema%.d_gis_group_layers (
124 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
125 display_name varchar(200) NOT NULL DEFAULT ''''::character varying,
126 "name" varchar(500) NOT NULL DEFAULT ''''::character varying,
127 CONSTRAINT "PK_%schema%.d_gis_group_layers" PRIMARY KEY ("key")
128 );
129------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
130 CREATE TABLE %schema%.d_gis_layer_in_group (
131 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
132 group_layer_key bigint NOT NULL DEFAULT 0,
133 layer_key bigint NOT NULL DEFAULT 0,
134 "order" integer NOT NULL DEFAULT 0,
135 container_key bigint NOT NULL DEFAULT 0,
136 "GroupLayer_Key" bigint,
137 CONSTRAINT "PK_%schema%.d_gis_layer_in_group" PRIMARY KEY ("key"),
138 CONSTRAINT "FK.%schema%.d_gis_layer_in_group.GroupLayer_Key" FOREIGN KEY ("GroupLayer_Key") REFERENCES %schema%.d_gis_group_layers("key"),
139 CONSTRAINT "FK.%schema%.d_gis_layer_in_group.container_key" FOREIGN KEY (container_key) REFERENCES %schema%.d_gis_group_layers("key") ON DELETE CASCADE,
140 CONSTRAINT "FK.%schema%.d_gis_layer_in_group.group_layer_key" FOREIGN KEY (group_layer_key) REFERENCES %schema%.d_gis_group_layers("key") ON DELETE CASCADE,
141 CONSTRAINT "FK.%schema%.d_gis_layer_in_group.layer_key" FOREIGN KEY (layer_key) REFERENCES %schema%.d_gis_layer("key") ON DELETE CASCADE
142 );
143 CREATE INDEX "d_gis_layer_in_group_IX_GeoL" ON %schema%.d_gis_layer_in_group (layer_key DESC) ;
144 CREATE INDEX "d_gis_layer_in_group_IX_GrL" ON %schema%.d_gis_layer_in_group (group_layer_key DESC) ;
145 CREATE INDEX "d_gis_layer_in_group_IX_GroupLayer_Key" ON %schema%.d_gis_layer_in_group ("GroupLayer_Key" DESC) ;
146 CREATE INDEX "d_gis_layer_in_group_IX_container_key" ON %schema%.d_gis_layer_in_group (container_key DESC) ;
147------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
148 CREATE TABLE %schema%.d_gis_view_settings (
149 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
150 center_x float8 NOT NULL DEFAULT 0,
151 center_y float8 NOT NULL DEFAULT 0,
152 srs_key bigint NOT NULL DEFAULT 0,
153 default_scale float8 NOT NULL DEFAULT 0,
154 min_scale float8 NOT NULL DEFAULT 0,
155 show_on_map_scale float8 NOT NULL DEFAULT 0,
156 browser_cache_lifetime integer NOT NULL DEFAULT 0,
157 is_default bool NOT NULL DEFAULT false,
158 default_zoom bigint NOT NULL,
159 min_zoom bigint NOT NULL,
160 show_on_map_zoom bigint NOT NULL,
161 CONSTRAINT "PK_%schema%.d_gis_view_settings" PRIMARY KEY ("key"),
162 CONSTRAINT "FK.%schema%.d_gis_view_settings.srs_key" FOREIGN KEY (srs_key) REFERENCES regadm.d_gis_srs("key") ON DELETE CASCADE
163 );
164 CREATE INDEX "d_gis_view_settings_IX_srs_key" ON %schema%.d_gis_view_settings (srs_key DESC) ;
165------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
166 CREATE TABLE %schema%.d_gis_map (
167 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
168 view_settings_key bigint NOT NULL DEFAULT 0,
169 untyped_layer_key bigint,
170 display_name varchar(500),
171 CONSTRAINT "PK_%schema%.d_gis_map" PRIMARY KEY ("key"),
172 CONSTRAINT "FK.%schema%.d_gis_map.untyped_layer_key" FOREIGN KEY (untyped_layer_key) REFERENCES %schema%.d_gis_layer("key") ON DELETE CASCADE,
173 CONSTRAINT "FK.%schema%.d_gis_map.view_settings_key" FOREIGN KEY (view_settings_key) REFERENCES %schema%.d_gis_view_settings("key") ON DELETE CASCADE
174 );
175 CREATE INDEX "d_gis_map_IX_untyped_layer_key" ON %schema%.d_gis_map (untyped_layer_key DESC) ;
176 CREATE INDEX "d_gis_map_IX_view_settings_key" ON %schema%.d_gis_map (view_settings_key DESC) ;
177------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
178 CREATE TABLE %schema%.d_gis_free_layer (
179 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
180 layer_key bigint NOT NULL DEFAULT 0,
181 view_settings_key bigint NOT NULL DEFAULT 0,
182 link_type integer NOT NULL DEFAULT 0,
183 overriding_style varchar(200),
184 browser_cache_lifetime integer NOT NULL DEFAULT 0,
185 display_name varchar(200),
186 CONSTRAINT "PK_%schema%.d_gis_free_layer" PRIMARY KEY ("key"),
187 CONSTRAINT "FK.%schema%.d_gis_free_layer.view_settings_key" FOREIGN KEY (view_settings_key) REFERENCES %schema%.d_gis_view_settings("key") ON DELETE CASCADE,
188 CONSTRAINT "FK.%schema%.d_gis_free_layer.layer_key" FOREIGN KEY (layer_key) REFERENCES %schema%.d_gis_layer("key") ON DELETE CASCADE
189 );
190 CREATE INDEX "d_gis_free_layer_IX_layer_key" ON %schema%.d_gis_free_layer (layer_key DESC) ;
191 CREATE INDEX "d_gis_free_layer_IX_view_settings_key" ON %schema%.d_gis_free_layer (view_settings_key DESC) ;
192------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
193 CREATE TABLE %schema%.d_gis_map_layer (
194 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
195 layer_key bigint NOT NULL DEFAULT 0,
196 link_type integer NOT NULL DEFAULT 0,
197 overriding_style varchar(200),
198 browser_cache_lifetime integer NOT NULL DEFAULT 0,
199 container_key bigint DEFAULT 0,
200 "order" integer NOT NULL DEFAULT 0,
201 display_name varchar(200),
202 visible_by_default boolean NOT NULL DEFAULT true,
203 CONSTRAINT "PK_%schema%.d_gis_map_layer" PRIMARY KEY ("key"),
204 CONSTRAINT "FK.%schema%.d_gis_map_layer.container_key" FOREIGN KEY (container_key) REFERENCES %schema%.d_gis_map("key") ON DELETE CASCADE,
205 CONSTRAINT "FK.%schema%.d_gis_map_layer.layer_key" FOREIGN KEY (layer_key) REFERENCES %schema%.d_gis_layer("key") ON DELETE CASCADE
206 );
207 CREATE INDEX "d_gis_map_layer_IX_container_key" ON %schema%.d_gis_map_layer (container_key DESC) ;
208 CREATE INDEX "d_gis_map_layer_IX_layer_key" ON %schema%.d_gis_map_layer (layer_key DESC) ;
209------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
210 CREATE TABLE %schema%.d_gis_map_background_layer (
211 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
212 layer_key bigint NOT NULL DEFAULT 0,
213 is_default bool NOT NULL DEFAULT false,
214 container_key bigint NOT NULL DEFAULT 0,
215 "order" integer NOT NULL DEFAULT 0,
216 display_name varchar(200),
217 visible_by_default boolean NOT NULL DEFAULT true,
218 CONSTRAINT "PK_%schema%.d_gis_map_background_layer" PRIMARY KEY ("key"),
219 CONSTRAINT "FK.%schema%.d_gis_map_background_layer.container_key" FOREIGN KEY (container_key) REFERENCES %schema%.d_gis_map("key") ON DELETE CASCADE,
220 CONSTRAINT "FK.%schema%.d_gis_map_background_layer.layer_key" FOREIGN KEY (layer_key) REFERENCES %schema%.d_gis_background_layer("key") ON DELETE CASCADE
221 );
222 CREATE INDEX "d_gis_map_background_layer_IX_container_key" ON %schema%.d_gis_map_background_layer (container_key DESC) ;
223 CREATE INDEX "d_gis_map_background_layer_IX_layer_key" ON %schema%.d_gis_map_background_layer (layer_key DESC) ;
224------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
225 CREATE TABLE %schema%.d_gis_theme_section (
226 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
227 "name" varchar(200) NOT NULL DEFAULT ''''::character varying,
228 CONSTRAINT "PK_%schema%.d_gis_theme_section" PRIMARY KEY ("key")
229 );
230------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
231 CREATE TABLE %schema%.d_gis_map_theme_section (
232 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
233 theme_section_key bigint NOT NULL DEFAULT 0,
234 container_key bigint NOT NULL DEFAULT 0,
235 "order" integer NOT NULL DEFAULT 0,
236 CONSTRAINT "PK_%schema%.d_gis_map_theme_section" PRIMARY KEY ("key"),
237 CONSTRAINT "FK.%schema%.d_gis_map_theme_section.container_key" FOREIGN KEY (container_key) REFERENCES %schema%.d_gis_map("key") ON DELETE CASCADE,
238 CONSTRAINT "FK.%schema%.d_gis_map_theme_section.theme_section_key" FOREIGN KEY (theme_section_key) REFERENCES %schema%.d_gis_theme_section("key") ON DELETE CASCADE
239 );
240 CREATE INDEX "d_gis_map_theme_section_IX_container_key" ON %schema%.d_gis_map_theme_section (container_key DESC);
241 CREATE INDEX "d_gis_map_theme_section_IX_theme_section_key" ON %schema%.d_gis_map_theme_section (theme_section_key DESC);
242------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
243 CREATE TABLE %schema%.d_gis_theme_layer (
244 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
245 geoserver_layer_key bigint NOT NULL DEFAULT 0,
246 link_type integer NOT NULL DEFAULT 0,
247 overriding_style varchar(200),
248 browser_cache_lifetime integer NOT NULL DEFAULT 0,
249 CONSTRAINT "PK_%schema%.d_gis_theme_layer" PRIMARY KEY ("key"),
250 CONSTRAINT "FK.%schema%.d_gis_theme_layer.geoserver_layer_key" FOREIGN KEY (geoserver_layer_key) REFERENCES %schema%.d_gis_layer("key") ON DELETE CASCADE
251 );
252 CREATE INDEX "d_gis_theme_layer_IX_geoserver_layer_key" ON %schema%.d_gis_theme_layer (geoserver_layer_key DESC);
253------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
254 CREATE TABLE %schema%.d_gis_theme_section_layer (
255 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
256 theme_layer_key bigint NOT NULL DEFAULT 0,
257 container_key bigint NOT NULL DEFAULT 0,
258 "order" integer NOT NULL DEFAULT 0,
259 CONSTRAINT "PK_%schema%.d_gis_theme_section_layer" PRIMARY KEY ("key"),
260 CONSTRAINT "FK.%schema%.d_gis_theme_section_layer.container_key" FOREIGN KEY (container_key) REFERENCES %schema%.d_gis_theme_section("key") ON DELETE CASCADE,
261 CONSTRAINT "FK.%schema%.d_gis_theme_section_layer.theme_layer_key" FOREIGN KEY (theme_layer_key) REFERENCES %schema%.d_gis_theme_layer("key") ON DELETE CASCADE
262 );
263 CREATE INDEX "d_gis_theme_section_layer_IX_container_key" ON %schema%.d_gis_theme_section_layer (container_key DESC);
264 CREATE INDEX "d_gis_theme_section_layer_IX_theme_layer_key" ON %schema%.d_gis_theme_section_layer (theme_layer_key DESC);
265------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
266 CREATE TABLE %schema%.d_gis_theme_legend_frame (
267 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
268 "name" varchar(500) NOT NULL DEFAULT ''''::character varying,
269 legend_type varchar(500),
270 is_vertical bool NOT NULL DEFAULT false,
271 min_value varchar(200),
272 max_value varchar(200),
273 color varchar(5000),
274 "Discriminator" varchar(128) NOT NULL DEFAULT ''''::character varying,
275 CONSTRAINT "PK_%schema%.d_gis_theme_legend_frame" PRIMARY KEY ("key")
276 );
277------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
278 CREATE TABLE %schema%.d_gis_theme_layer_legend_frame (
279 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
280 legend_frame_key bigint NOT NULL DEFAULT 0,
281 container_key bigint NOT NULL DEFAULT 0,
282 "order" integer NOT NULL DEFAULT 0,
283 CONSTRAINT "PK_%schema%.d_gis_theme_layer_legend_frame" PRIMARY KEY ("key"),
284 CONSTRAINT "FK.%schema%.d_gis_theme_layer_legend_frame.container_key" FOREIGN KEY (container_key) REFERENCES %schema%.d_gis_theme_layer("key") ON DELETE CASCADE,
285 CONSTRAINT "FK.%schema%.d_gis_theme_layer_legend_frame.legend_frame_key" FOREIGN KEY (legend_frame_key) REFERENCES %schema%.d_gis_theme_legend_frame("key") ON DELETE CASCADE
286 );
287 CREATE INDEX "d_gis_theme_layer_legend_frame_IX_container_key" ON %schema%.d_gis_theme_layer_legend_frame (container_key DESC);
288 CREATE INDEX "d_gis_theme_layer_legend_frame_IX_legend_frame_key" ON %schema%.d_gis_theme_layer_legend_frame (legend_frame_key DESC);
289------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
290 CREATE TABLE %schema%.d_gis_theme_frame_item (
291 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
292 "name" varchar(500),
293 color varchar(500),
294 border varchar(500),
295 icon_base64 text,
296 CONSTRAINT "PK_%schema%.d_gis_theme_frame_item" PRIMARY KEY ("key")
297 );
298------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
299 CREATE TABLE %schema%.d_gis_theme_item_in_frame (
300 item_key bigint NOT NULL DEFAULT 0,
301 "key" bigint NOT NULL DEFAULT nextval(''%schema%.d_gis_sequence''),
302 container_key bigint NOT NULL DEFAULT 0,
303 "order" integer NOT NULL DEFAULT 0,
304 CONSTRAINT "PK_%schema%.d_gis_theme_item_in_frame" PRIMARY KEY ("key"),
305 CONSTRAINT "FK.%schema%.d_gis_theme_item_in_frame.container_key" FOREIGN KEY (container_key) REFERENCES %schema%.d_gis_theme_legend_frame("key") ON DELETE CASCADE,
306 CONSTRAINT "FK.%schema%.d_gis_theme_item_in_frame.item_key" FOREIGN KEY (item_key) REFERENCES %schema%.d_gis_theme_frame_item("key") ON DELETE CASCADE
307 );
308 CREATE INDEX "d_gis_theme_item_in_frame_IX_container_key" ON %schema%.d_gis_theme_item_in_frame (container_key DESC) ;
309 CREATE INDEX "d_gis_theme_item_in_frame_IX_item_key" ON %schema%.d_gis_theme_item_in_frame (item_key DESC) ;
310------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
311end;
312$function$ language plpgsql';
313 execute replace(query_text,'%schema%',rec.scheme);
314 end loop;
315--
316 query_text:='CREATE OR REPLACE FUNCTION regadm.sp_clear_gis_ef_tables_shared() RETURNS void
317AS
318$function$
319begin
320 drop table regadm.d_gis_srs CASCADE;
321 drop table regadm.d_gis_geoserver CASCADE;
322 drop sequence regadm.d_gis_sequence;
323 drop table if exists regadm.d_gis_cache_monitor_revisions;
324end;
325$function$ LANGUAGE plpgsql';
326 execute query_text;
327--
328 query_text:='create or replace function regadm.sp_create_gis_ef_tables_shared() returns void
329as
330$function$
331begin
332 create sequence regadm.d_gis_sequence
333 increment by 1
334 minvalue 1
335 maxvalue 9223372036854775807
336 start 1;
337 create table regadm.d_gis_geoserver (
338 "key" bigint not null default nextval(''regadm.d_gis_sequence''),
339 display_name varchar(300) not null default ''''::character varying,
340 description varchar(500),
341 url varchar(200) not null default ''''::character varying,
342 login varchar(500) not null default ''''::character varying,
343 password varchar(500) not null default ''''::character varying,
344 wms_features_limit integer not null default 0,
345 constraint "pk_regadm.d_gis_geoserver" primary key ("key")
346 );
347 create unique index "d_gis_geoserver_ix_url" on regadm.d_gis_geoserver (url desc) ;
348 create table regadm.d_gis_srs (
349 "key" bigint not null default nextval(''regadm.d_gis_sequence''),
350 code varchar(500) not null default ''''::character varying,
351 wkt varchar(1000),
352 proj4 varchar(1000),
353 display_name varchar(250),
354 constraint "pk_anivsky_test.d_gis_srs" primary key ("key")
355 );
356 create unique index "d_gis_srs_ix_srs_u" on regadm.d_gis_srs (code desc);
357 CREATE OR REPLACE FUNCTION regadm.f_d_gis_srs() RETURNS trigger AS
358 $BODYGIS$
359 DECLARE
360 tsrid bigint;
361 BEGIN
362 if(trim(NEW.code) is not null and upper(trim(NEW.code)) like ''EPSG:%'') then
363 raise notice ''Обрабатывает данные "%"'',NEW.code;
364 tsrid:=regexp_replace(NEW.code,''EPSG:(\d+)'',''\1'');
365 raise notice ''Получен код %'',tsrid;
366 if not exists(select 1 from public.spatial_ref_sys where srid=tsrid) then
367 raise info ''Указанного srid=% нет в public.spatial_ref_sys. Создаем'', tsrid;
368 insert into public.spatial_ref_sys(srid,auth_name,auth_srid,srtext,proj4text) values(tsrid,NEW.code,tsrid,NEW.wkt,NEW.proj4);
369 else
370 raise info ''Указанный srid=% ÑущеÑтвует в public.spatial_ref_sys. ОбновлÑем'', tsrid;
371 update public.spatial_ref_sys
372 set auth_name=NEW.code,
373 srtext=NEW.wkt,
374 proj4text=NEW.proj4
375 where srid=tsrid;
376 end if;
377 end if;
378 return NEW;
379 CREATE TABLE regadm.d_gis_cache_monitor_revisions
380 (
381 project_alias character varying(60) NOT NULL,
382 last_revision bigint NOT NULL,
383 CONSTRAINT d_gis_cache_monitor_revisions_project_alias_key UNIQUE (project_alias)
384 );
385END;
386$BODYGIS$ LANGUAGE plpgsql;
387
388CREATE TRIGGER trg_d_gis_srs BEFORE INSERT ON regadm.d_gis_srs FOR EACH ROW EXECUTE PROCEDURE regadm.f_d_gis_srs();
389end;
390$function$ language plpgsql';
391 execute query_text;
392end$$;
393
394do
395$$
396begin
397 perform regadm.sp_create_gis_ef_tables_shared();
398exception when others then null;
399end$$;
400
401do
402$$
403declare
404 c bigint;
405 rec record;
406begin
407 select count(*) into c from information_schema.tables where table_name like 'd_gis%' and table_schema='regadm';
408 if c=0 then
409 raise info '%','regadm';
410 execute 'select regadm.sp_create_gis_ef_tables_shared()';
411 end if;
412
413 for rec in(select lower(scheme) as scheme
414 from regadm.m_projects p
415 join pg_namespace n on lower(p.scheme)=n.nspname
416 where lower(p.scheme)!='regadm')
417 loop
418 select count(*) into c from information_schema.tables where table_name like 'd_gis%' and table_schema=rec.scheme;
419 if c=0 then
420 raise info '%',rec.scheme;
421 execute 'select '||rec.scheme||'.sp_create_gis_ef_tables_in_schema()';
422 end if;
423 end loop;
424end$$;