API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jan 24th, 2018
64
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 23.48 KB | None | 0 0
raw download clone embed print report
  1. do
  2. $$
  3. declare
  4. rec record;
  5. query_text text;
  6. begin
  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
  16. AS
  17. $function$
  18. begin
  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;
  37. end;
  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
  43. AS
  44. $function$
  45. begin
  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;
  66. end;
  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
  72. AS
  73. $function$
  74. begin
  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. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  311. end;
  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
  317. AS
  318. $function$
  319. begin
  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;
  324. end;
  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
  329. as
  330. $function$
  331. begin
  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. );
  385. END;
  386. $BODYGIS$ LANGUAGE plpgsql;
  387.  
  388. CREATE TRIGGER trg_d_gis_srs BEFORE INSERT ON regadm.d_gis_srs FOR EACH ROW EXECUTE PROCEDURE regadm.f_d_gis_srs();
  389. end;
  390. $function$ language plpgsql';
  391. execute query_text;
  392. end$$;
  393.  
  394. do
  395. $$
  396. begin
  397. perform regadm.sp_create_gis_ef_tables_shared();
  398. exception when others then null;
  399. end$$;
  400.  
  401. do
  402. $$
  403. declare
  404. c bigint;
  405. rec record;
  406. begin
  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;
  424. end$$;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!