From 02613c8efc2b690eae1c4ffce242c2239faa8e58 Mon Sep 17 00:00:00 2001
From: 13693261870 <252740454@qq.com>
Date: 星期三, 05 七月 2023 16:18:33 +0800
Subject: [PATCH] del data
---
/dev/null | 0
.gitignore | 1 +
2 files changed, 1 insertions(+), 0 deletions(-)
diff --git a/.gitignore b/.gitignore
index 3e16485..2eaca17 100644
--- a/.gitignore
+++ b/.gitignore
@@ -38,3 +38,4 @@
/QGIS/render.png
/QGIS/*.png
/QGIS/venv
+/data
diff --git a/data/db_cx.sql b/data/db_cx.sql
deleted file mode 100644
index ea8853f..0000000
--- a/data/db_cx.sql
+++ /dev/null
@@ -1,270 +0,0 @@
-set autocommit off;
-st_length( st_geographyfromtext( st_astext( st_intersection( ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) ) ) AS acrossLength;
----------------------------------------------------------------------------------------------- -1.SQL娴嬭瘯
-select case a.userid when 2 then guid else null end "guid", a.* from lf.sys_apply a order by id desc
-select * from bs.s_explorationpoint where upper(exppointid) like '%XK0%';
-select * from lf.sys_layer where url is not null and serve_type='WMS' and data_type in ('宸ョ▼椤圭洰','宸ョ▼椤圭洰-鍦扮伨绫�','宸ョ▼椤圭洰-娴嬮噺绫�','宸ョ▼椤圭洰-娲炲簱绫�','宸ョ▼椤圭洰-鍕樺療绫�') order by id;
-insert into lf.sys_layer (pid,cn_name,en_name,url,type,level,order_num,is_show,create_user,serve_type,data_type,elev) values
- (423, '鍦伴潰灞�', 'fushun_site', 'tileset/o/39HBFG/tileset.json', 2, 3, 5, 0, 1, 'Tileset', '宸ョ▼娴嬮噺妯″瀷', 1200);
-select ST_AsText(geom) "geom", a.*, fn_get_fullname(a.depid, 1) depName, fn_get_fullname(a.dirid, 2) dirName, fn_uname(create_user) createName, fn_uname(update_user) updateName from lf.sys_publish a
-select fn_get_fullname(depcode, 1) "m1", cast(sum(sizes) as decimal(18, 3)) "sizes" from lf.sys_meta group by depcode order by depcode;
-select fn_get_fullname(code, 2) from lf.sys_dir where code='0A05'; -- 鏌ヨ瀹屾暣璺緞
-select * from lf.sys_token where token ='c36e4f94-dfde-401e-9967-2c4a449f1300'; -- FME
-
--- 鍒犻櫎鍐椾綑鏁版嵁锛屼慨澶峵ab_guid鍊�
-select id,name,path,tab,tab_guid from lf.sys_attach where tab != 'bd.b_borehole' order by id desc limit 200;
---delete from bs.s_explorationpointstratum where parentid in (select eventid from lf.sys_meta where name='鍕樻帰鐐瑰湴灞傝〃.xlsx');
-select * from lf.sys_meta where type = 'tif' or type = 'tiff' or lower(name) in ('骞抽潰鍥剧储寮曟枃浠�.xlsx', '绌胯法瓒婂湴褰㈠浘.dwg', '涓嚎鎴愭灉琛�.xlsx', '绂绘暎鐐�.xlsx', '鎺у埗鐐�.xlsx', '鍦伴潰绾�.xlsx');
-
-select * from lf.sys_layer where is_project=1 order by id desc;
-select * from lf.sys_layer order by id desc;
-select a.*,st_astext(geom) from lf.sys_publish a order by id desc;
-select * from lf.sys_meta_pub where metaid = 10516 order by id desc;
-select * from lf.sys_download order by id desc limit 20; --downid
-select * from lf.sys_apply order by id desc;
-select * from lf.sys_flow order by id desc;
-select * from lf.sys_meta where type='las' order by id desc limit 100;
-select * from lf.sys_fme_log order by id desc;
-select * from lf.sys_dir where pid = 0 order by id;
-select * from lf.sys_dict order by id desc;
-select * from lf.sys_meta where id = 10516;
-select * from lf.sys_report;
-select * from lf.sys_publish where type = 'cpt';
-select * from lf.sys_meta_pub where pubid = 132;
-
-update lf.sys_layer set serve_type = null where type = 2;
-update lf.sys_layer set serve_type = 'WMS' where type = 2 and position('LF:' in url) > 0;
-update lf.sys_layer set serve_type = '3DML' where type = 2 and serve_type is null and position('b3dm' in url) > 0;
-update lf.sys_layer set serve_type = 'Tileset' where type = 2 and serve_type is null and position('.json' in url) > 0;
-update lf.sys_layer set serve_type = 'TMS' where type = 2 and serve_type is null and position('.png' in url) > 0;
-update lf.sys_layer set serve_type = 'Mpt' where type = 2 and serve_type is null and position('.mpt' in url) > 0;
-update lf.sys_layer set serve_type = 'DEM' where type = 2 and serve_type is null and position('terrain/' in url) > 0;
-
-select id, pid, cn_name, en_name, url, serve_type, data_type, elev, pubid, is_project, bak
-from lf.sys_layer where type = 2 and serve_type='WMS' order by id;
--- update lf.sys_publish a set url = replace(url, '192.168.20.205:80', '{host}') where position('192.' in url) > 0;
-select id, pid, type, cn_name, en_name, url, serve_type, data_type, elev, pubid, is_project, bak from lf.sys_layer order by id; -- 8-299
-
-select id,name,url,path,type,dirid from lf.sys_publish where position('/SG/' in url) > 0 order by type;
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-----------------------------------------------------------------------------------------------
--- update lf.sys_publish a set geom = ST_GeomFromText('POINT Z (31.231025 113.223529 12)') where id = 1;
-select id from lf.sys_layer where cn_name = '涓夌淮鍦拌川妯″瀷' and pid = (select id from lf.sys_layer where cn_name = '鍕樺療' order by pid limit 1);
-select id from lf.sys_layer where cn_name = '鍗曚綋妯″瀷' and pid = (select id from lf.sys_layer where cn_name = '娴嬬粯' order by pid limit 1);
-select id from lf.sys_layer where cn_name = '鏁板瓧姝ｅ皠褰卞儚鍥�' and pid = (select id from lf.sys_layer where cn_name = '娴嬬粯' order by pid limit 1);
-select id from lf.sys_layer where cn_name = '涓夌淮鍦板舰妯″瀷' and pid = (select id from lf.sys_layer where cn_name = '娴嬬粯' order by pid limit 1);
-select id from lf.sys_layer where cn_name = '涓夌淮鍦拌川妯″瀷' and pid = (select id from lf.sys_layer where cn_name = '鍦扮伨' order by pid limit 1);
-select id from lf.sys_layer where cn_name = '娲炲簱妯″瀷' and pid = (select id from lf.sys_layer where cn_name = '娲炲簱' order by pid limit 1);
-select id from lf.sys_layer where cn_name = '鍏朵粬' and pid = (select id from lf.sys_layer where cn_name = '宸ョ▼鏁版嵁' order by pid limit 1);
-
-insert into lf.sys_layer (pid, cn_name, en_name, url, type, level, order_num, is_show, create_user, data_type, serve_type, pubid, elev) values
-(@pid, @cnName, @enName, @url, 2, (select coalesce(level, 0) + 1 from lf.sys_layer where id = @pid), (select coalesce(max(order_num), 0) + 1 from lf.sys_layer where pid = @pid), 0, @user, @name, @serve, @pubid, @elev)
-select fn_get_fullname(a.depcode, 1) depName, fn_get_fullname(a.dircode, 2) dirName, a.* from lf.sys_meta a order by id desc limit 10;
----------------------------------------------------------------------------------------------- SY1.鐓ゅ眰54琛�
--- drop table public.coal54;
-create table if not exists public.coal54 (
- gid serial primary key,
- top numeric(12, 3),
- bottom numeric(12, 3),
- clong numeric(12, 3),
- width numeric(12, 3),
- height numeric(12, 3),
- ctype varchar(8),
- density numeric(12, 3),
- gangue numeric(12, 5),
- volume numeric(12, 3),
- geom geometry(point, 2435)
-);
-create index idx_coal54_geom on public.coal54 using gist (geom);
-comment on table public.coal54 is '鐓ゅ眰54琛�';
-comment on column public.coal54.gid is '涓婚敭ID';
-comment on column public.coal54.top is '涓婇《楂樺害';
-comment on column public.coal54.bottom is '涓嬪簳楂樺害';
-comment on column public.coal54.clong is '闀垮害';
-comment on column public.coal54.width is '瀹藉害';
-comment on column public.coal54.height is '楂樺害';
-comment on column public.coal54.ctype is '绫诲埆';
-comment on column public.coal54.density is '瑙嗗瘑搴�';
-comment on column public.coal54.gangue is '鍚煾鐜�';
-comment on column public.coal54.volume is '浣撶Н';
-comment on column public.coal54.geom is '绌洪棿';
-
-select count(*) from public.coal54; -- 12701714
-select a.*, ST_AsText(geom) from public.coal54 a order by gid desc limit 100;
----------------------------------------------------------------------------------------------- SY2.鐓ゅ眰2000琛�
--- create sequence public.coal2000_gid_seq increment by 1 minvalue 1 maxvalue 2147483647 start 1 cache 1;
--- alter sequence public.coal2000_gid_seq restart with 12701715; vacuum analyze public.coal2000;
-
--- drop table public.coal2000;
-create table if not exists public.coal2000 (
- gid serial primary key,
- top numeric(12, 3),
- bottom numeric(12, 3),
- clong numeric(12, 3),
- width numeric(12, 3),
- height numeric(12, 3),
- ctype varchar(8),
- density numeric(12, 3),
- gangue numeric(12, 5),
- volume numeric(12, 3),
- coal_prod numeric(15, 3),
- gangue_prod numeric(15, 5),
- soil_prod numeric(15, 5),
- geom geometry(point, 4490)
-);
--- drop index if exists public.idx_coal2000_geom;
-create index idx_coal2000_geom on public.coal2000 using gist (geom);
-create index idx_coal2000_top on public.coal2000 (top);
-create index idx_coal2000_bottom on public.coal2000 (bottom);
-comment on table public.coal2000 is '鐓ゅ眰2000琛�';
-comment on column public.coal2000.gid is '涓婚敭ID';
-comment on column public.coal2000.top is '涓婇《楂樺害';
-comment on column public.coal2000.bottom is '涓嬪簳楂樺害';
-comment on column public.coal2000.clong is '闀垮害';
-comment on column public.coal2000.width is '瀹藉害';
-comment on column public.coal2000.height is '楂樺害';
-comment on column public.coal2000.ctype is '绫诲埆';
-comment on column public.coal2000.density is '瑙嗗瘑搴�';
-comment on column public.coal2000.gangue is '鍚煾鐜�';
-comment on column public.coal2000.volume is '浣撶Н';
-comment on column public.coal2000.coal_prod is '鐓ら噺';
-comment on column public.coal2000.gangue_prod is '鐭哥煶閲�';
-comment on column public.coal2000.soil_prod is '鍦熼噺';
-comment on column public.coal2000.geom is '绌洪棿';
--- drop index if exists public.idx_coal2000_ctype;
-create index idx_coal2000_ctype on public.coal2000 (ctype);
-
---鐓ら噺 = 浣撶Н * 瑙嗗瘑搴︼紝鐭哥煶閲� = 浣撶Н * 鍚煾鐜囷紝鍦熼噺 = 鎬讳綋绉� - 鐓ら噺- 鐭哥煶閲�
-select count(*) from public.coal2000; -- 12701714
-select * from public.coal2000 order by gid desc limit 100;
-select ctype, sum(volume) "volume", sum(coal_prod) "coal_prod", sum(gangue_prod) "gangue_prod", 0 "soil_prod" from public.coal2000
-where (top between 640 and 690 or bottom between 640 and 690) and ST_Intersects(geom, ST_PolygonFromText('POLYGON ((113.5807 45.4897,113.5898 45.4893,113.5857 45.4837,113.5807 45.4897))', 4490)) group by ctype order by ctype;
--- select ST_GeometryFromText('POLYGON ((113.5807 45.4897,113.5898 45.4893,113.5857 45.4837,113.5807 45.4897))', 4490)
----------------------------------------------------------------------------------------------- -1.鏇存柊閽诲瓟
-select count(*) from bd.b_borehole; -- 357824
-select count(*) from lf.sys_attach; -- 357880 = 56 + 357824
-select * from bd.b_borehole limit 10; -- 鍩虹閽诲瓟
-
--- update bd.b_borehole set path = 'Drill\' || path;
--- insert into lf.sys_attach (name, guid, path, tab, tab_guid)
-select substr(path, length(path) - position('\' in reverse(path)) + 2), eventid, path, 'bd.b_borehole', eventid from bd.b_borehole;
----------------------------------------------------------------------------------------------- 00.SQL鏌ヨ
-insert into lf.sys_meta_pub (metaid, pubid) select 1, 1 from (select 1) temp where not exists (select 1 from lf.sys_meta_pub where metaid = 1 and pubid = 1);
-
-select * from bd.dlg_agnp where ST_Intersects(geom, ST_Buffer(ST_PointFromText('POINT (100.746244878 35.588071316)', 4326), 10.0 / 1852 / 60)) limit 20;
-select * from bd.dlg_agnp where ST_Intersects(geom, ST_Buffer(ST_PointFromText('POINT (100.746244878 35.588071316)', 4326), 10.0 * 0.00000899928)) limit 20;
-
-select * from bd.dlg_agnp where ST_Intersects(geom,
- ST_GeomFromText('POLYGON((100.746245 35.588161,100.746167 35.588135,100.746134 35.588071,100.746167 35.588008,100.746245 35.587981,100.746323 35.588008,100.746355 35.588071,100.746323 35.588135,100.746245 35.588161))', 4326)
-)
-
-select * from bd.dlg_25w_boul where ST_DWithin(geom, ST_GeomFromText('POINT (101.9281 36.58675)', 4490), 10000.0 / 1852 / 60) = true limit 20;
-
-select id,cn_name,en_name,url,serve_type,data_type from lf.sys_layer where url is not null and
- serve_type='WMS' and data_type in ('宸ョ▼椤圭洰','宸ョ▼椤圭洰-鍦扮伨绫�','宸ョ▼椤圭洰-娴嬮噺绫�','宸ョ▼椤圭洰-娲炲簱绫�','宸ョ▼椤圭洰-鍕樺療绫�') order by id;
-
-select modular1,modular2 from lf.sys_operate where modular1 not in ('缁煎悎灞曠ず','鏁版嵁璐ㄦ','鏁版嵁绠＄悊','鏁版嵁浜ゆ崲','鏈嶅姟绠＄悊','绯荤粺瀵规帴','杩愮淮绠＄悊');
-delete from lf.sys_operate where modular1 not in ('缁煎悎灞曠ず','鏁版嵁璐ㄦ','鏁版嵁绠＄悊','鏁版嵁浜ゆ崲','鏈嶅姟绠＄悊','绯荤粺瀵规帴','杩愮淮绠＄悊');
-select id,cn_name,elev from lf.sys_layer where cn_name in ('鐏溅绔�','鍦颁笅绠＄嚎','涓崼绔�','搴嗛槼绔�','鍦板眰绀轰緥');
-
-select * from lf.sys_user where uid='FME';
-select * from lf.sys_token where token='c36e4f94-dfde-401e-9967-2c4a449f1300';
-update lf.sys_token set type=1,create_user=61,duration=5256000,create_time='2023-03-03 19:00:00',expire='2033-03-03 19:00:00'
-where token='c36e4f94-dfde-401e-9967-2c4a449f1300';
-
-select gid, pipename, segname, ST_AsText(ST_LineMerge(geom)) "wkt"
-from bs.m_pipesegment
-where pipename is not null and not ST_IsEmpty(geom)
-order by pipename, segname;
----------------------------------------------------------------------------------------------- 01.鍒涘缓绌洪棿鎵╁睍
-create extension postgis;
-create extension pgrouting;
-create extension postgis_raster;
-create extension postgis_topology;
-create extension fuzzystrmatch;
-create extension postgis_tiger_geocoder;
-create extension address_standardizer;
-create extension "uuid-ossp";
-
-select uuid_generate_v4(); -- uuid锛�36浣�
-select split_part('12.34.56.78', '.', 1); -- 鍒囧壊
-select substr('FY2021', 3, 2); -- 鎻愬彇
-select substr('FY2021', 3); -- 鎻愬彇
-select substring('FY2021' FROM 3 for 2); -- 鎻愬彇
-select position('23' in 'w123456'); -- 浣嶇疆
-select md5(''); -- MD5
-select concat('aa', 'bb'); -- 杩炴帴
-select concat_ws('_', 'a', 'b'); -- 杩炴帴
-select chr(97); -- ACSII鍊艰浆瀛楃
-select ascii('x'); -- 瀛楃杞珹CSII鍊�
-select to_hex(31); -- 杞�16杩涘埗
-select length('aabbcc'); -- 瀛楃闀垮害
-select reverse('aabbcc'); -- 鍙嶈浆
-select initcap('aabbcc'); -- 棣栧瓧姣嶅ぇ鍐�
-select replace('aabbcc', 'bc', 'xy'); -- 鏇挎崲
-select left('abcde', 2); -- 宸︽埅鍙�
-select right('abcde', 2); -- 鍙虫埅鍙�
----------------------------------------------------------------------------------------------- 02.鏌ヨ杩炴帴鏁�
-show max_connections;
-select count(1) from pg_stat_activity;
-
--- 鏌ヨ琛ㄦ灦鏋勫拰琛ㄥ悕
-select oid,table_catalog,table_schema,table_name
-from information_schema.tables t1, pg_class t2
-where table_schema = 'bd' and t1."table_name" = t2.relname
-order by table_catalog,table_schema,table_name;
-
--- 鏌ヨ瀛楁淇℃伅
-select e.table_catalog, e.table_schema, c.relname "tab", cast(obj_description(c.oid) as varchar) "desc", a.attnum "num", a.attname "col",
- d.typname "type", concat_ws('', d.typname, SUBSTRING(format_type(a.atttypid, a.atttypmod) from '(.*)')) "type2", b.description "bak"
-from pg_attribute a left join pg_description b on b.objoid = a.attrelid and b.objsubid = a.attnum
- left join pg_class c on a.attrelid = c.oid
- left join pg_type d on a.atttypid = d.oid
- left join information_schema.tables e on e.table_name = c.relname
-where a.attnum >= 0 and reltype > 0 and a.attname = 'updateuser' and d.typname != 'int4' --and relnamespace in (29257,20582)
-order by c.relname desc, a.attnum asc;
-
-select pg_constraint.conname as pk_name,pg_attribute.attname as colname,pg_type.typname as typename from pg_constraint inner join pg_class
-on pg_constraint.conrelid = pg_class.oid inner join pg_attribute on pg_attribute.attrelid = pg_class.oid and pg_attribute.attnum = pg_constraint.conkey[1]
-inner join pg_type on pg_type.oid = pg_attribute.atttypid where pg_class.relname = 'sys_user' and pg_constraint.contype='p';
----------------------------------------------------------------------------------------------- 03.鏌ヨ
-select depid from bd.dlg_25w_boul where depid > 0 and depid != ALL(fn_rec_array(38, 'dep'))
-and ST_Intersects(geom, ST_PolygonFromText('POLYGON ((115.94927385452 32.3754479115071 0,121.989371092554 32.2766788010181 0,121.850621222894 29.6874200067864 0,115.9727267226 29.7835368627922 0,115.94927385452 32.3754479115071 0))', 4490))
-group by depid;
-
-select * from lf.sys_dir where id in (select min(id) from lf.sys_dir where name in ('娴嬮噺锛圗SV锛�','鍕樺療锛圗GE锛�','鍦扮伨锛圗GD锛�','娲炲簱锛圗GD锛�') group by name);
-select * from lf.sys_dir where pid = 1;
-select * from lf.sys_dir order by id;
-
-select * from lf.sys_token where create_time >= '2022-09-29' and create_time <= '2022-09-30';
-
-select * from lf.sys_token where to_char(create_time,'yyyy-MM-dd') = '2022-09-30';
-
-select fn_rec_query(depid,'dep') depName,fn_rec_query(dirid,'dir') dirName,fn_ver(verid) verName,fn_uname(createuser) createName,fn_uname(updateuser) updateName from bd.dlg_agnp limit 5;
-
-select ns,tab from lf.sys_dict a where not exists (select id from lf.sys_dict b where b.ns=a.ns and b.tab=a.tab and b.field='gid') group by ns,tab; -- 鏌ヨ瀛楀吀涓笉瀛樺湪gid鐨勮〃
-----------------------------------------------------------------------------------------------
diff --git a/data/db_fn.sql b/data/db_fn.sql
deleted file mode 100644
index 387f5db..0000000
--- a/data/db_fn.sql
+++ /dev/null
@@ -1,694 +0,0 @@
------------------------------------------------------------------------------------------------------ 01.GUID鐢熸垚鍑芥暟
--- drop function new_guid();
-create or replace function new_guid()
- returns "pg_catalog"."varchar" as $body$
-declare
- v_seed_value varchar(32);
-begin
- select md5(inet_client_addr()::varchar || timeofday() || inet_server_addr()::varchar || to_hex(inet_client_port()))
- into v_seed_value;
-
- return (substr(v_seed_value,1,8) || '-' ||
- substr(v_seed_value,9,4) || '-' ||
- substr(v_seed_value,13,4) || '-' ||
- substr(v_seed_value,17,4) || '-' ||
- substr(v_seed_value,21,12));
-end;
-$body$ language 'plpgsql' volatile security definer;
-
-select new_guid(); select length(md5('-')); select uuid_generate_v4();
------------------------------------------------------------------------------------------------------ 02.閫掑綊鏌ヨ鍑芥暟
--- drop function fn_rec_query(integer, varchar);
-create or replace function fn_rec_query(id integer, tab varchar)
-returns varchar as $$
- declare
- str varchar = '';
- rec varchar = '';
- begin
- if (id is null or tab is null) then
- return '';
- end if;
-
- for rec in execute 'with recursive rs as(' ||
- 'select id,pid,name from lf.sys_' || tab || ' where id=' || id ||
- ' union select a.id,a.pid,a.name from lf.sys_' || tab || ' a, rs b where a.id=b.pid '||
- ') select name from rs order by id'
- loop
- str = str || '\' || rec;
- end loop;
-
- if (char_length(str) > 1) then
- str = substring(str, 2);
- end if;
-
- return str;
- end;
-$$ language plpgsql;
-
-select fn_rec_query(1, 'dep'); select fn_rec_query(null, 'dep'); select fn_rec_query(10, 'dir');
------------------------------------------------------------------------------------------------------ 03.閫掑綊鏌ヨ瀹屾暣鍚�
--- drop function fn_get_fullname(varchar, integer);
-create or replace function fn_get_fullname(code varchar, genre integer)
-returns varchar as $$
- declare
- rec varchar := '';
- str varchar := '';
- tab varchar := 'dep';
- begin
- if (code is null or genre is null) then
- return '';
- end if;
- if genre = 2 then
- tab := 'dir';
- end if;
-
- for rec in execute 'with recursive rs as (' ||
- 'select code, pid, name from lf.sys_' || tab || ' where code = ''' || code || ''' ' ||
- 'union select a.code, a.pid, a.name from lf.sys_' || tab || ' a, rs b where a.id = b.pid' ||
- ') select name from rs order by code'
- loop
- str = str || '\' || rec;
- end loop;
-
- if (char_length(str) > 1) then
- str = substring(str, 2);
- end if;
-
- return str;
- end;
-$$ language plpgsql;
-
-select fn_rec_query(55, 'dep'); select fn_rec_query(90, 'dir');
-select fn_get_fullname('00030701', 1); select fn_get_fullname('01000000020000', 2);
-/*with recursive rs as (
- select code, pid, name from lf.sys_dep where code = '00030701'
- union select a.code, a.pid, a.name from lf.sys_dep a, rs b where a.id = b.pid
-) select name from rs order by code;*/
------------------------------------------------------------------------------------------------------ 04.鏌ヨ鐢ㄦ埛鍚�
--- execute format('select uname from lf.sys_user where id = %s', id) into str;
--- drop function fn_uname(integer);
-create or replace function fn_uname(id integer)
-returns varchar as $$
- declare
- str varchar;
- begin
- if (id is null) then
- return null;
- end if;
-
- execute 'select uname from lf.sys_user where id = ' || id into str;
-
- return str;
- end;
-$$ language plpgsql;
-
-select fn_uname(null); select fn_uname(1);
------------------------------------------------------------------------------------------------------ 05.鏌ヨ鐗堟湰鍚�
--- drop function fn_ver(integer);
-create or replace function fn_ver(id integer)
-returns varchar as $$
- declare
- str varchar;
- begin
- if (id is null) then
- return null;
- end if;
-
- execute 'select name from lf.sys_ver where id = ' || id into str;
-
- return str;
- end;
-$$ language plpgsql;
-
-select fn_ver(0);
------------------------------------------------------------------------------------------------------ 06.閫掑綊鏌ヨID鏁扮粍
--- drop function fn_rec_array(integer, varchar);
-create or replace function fn_rec_array(id integer, tab varchar)
-returns integer[] as $$
- declare
- ids integer[];
- sid integer;
- begin
- for sid in execute 'with recursive rs as(' ||
- 'select id,pid from lf.sys_' || tab || ' where id=' || id ||
- ' union select a.id,a.pid from lf.sys_' || tab || ' a, rs b where a.pid=b.id '||
- ') select id from rs order by id'
- loop
- select array_append(ids, sid) into ids;
- end loop;
-
- return ids;
- end;
-$$ language plpgsql;
-
-select fn_rec_array(1, 'dep'); select fn_rec_array(10, 'dir');
-select * from lf.sys_user a where a.depid = ANY(fn_rec_array(15,'dep'));
-select * from lf.sys_meta where depcode like '00%'; -- select * from lf.sys_meta where depid = ANY(fn_rec_array(1, 'dep'));
-select * from lf.sys_meta where dircode like '01%'; -- select * from lf.sys_meta where dirid = ANY(fn_rec_array(57, 'dir'));
------------------------------------------------------------------------------------------------------ 07.鑾峰彇瀹炰綋鍚�
--- drop function fn_get_entity(varchar);
-create or replace function fn_get_entity(tab varchar)
-returns varchar as $$
- declare
- begin
- if (tab is null) then
- return '';
- end if;
-
- return replace(tab, '_', '');
- end;
-$$ language plpgsql;
-
-select fn_get_entity('dlg_25w_boua_s');
------------------------------------------------------------------------------------------------------ 08.鏌ヨ瀛楀吀琛ㄥ苟缁熻璁板綍 *
--- drop function fn_tab_count(varchar, varchar, varchar);
-create or replace function fn_tab_count(ns varchar, tab varchar, filters varchar)
-returns integer as $$
- declare
- cc integer;
- begin
- --raise notice 'select count(*) from %s.%s where %', ns, tab, filters;
- execute format('select count(*) from %s.%s where %s', ns, tab, filters) into cc;
-
- return cc;
- end;
-$$ language plpgsql;
-
-select fn_tab_count('bd', 'dlg_agnp', '1=1');
-select fn_tab_count('bd', 'b_rg20w_anna', '1=1');
------------------------------------------------------------------------------------------------------ 09.10杩涘埗杞�62杩涘埗
--- drop function fn_10_to_62(numeric);
-create or replace function fn_10_to_62(num numeric(30, 0))
-returns varchar as $$
- declare
- rs varchar;
- begin
- if (num = 0) then
- return '0';
- end if;
-
- WITH RECURSIVE T(N, S) AS (
- SELECT num N, '' S
- UNION ALL
- SELECT trunc(N / 62)::NUMERIC(30, 0), substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', (N % 62)::INT + 1, 1) || S FROM T WHERE N > 0
- )
- SELECT S INTO rs FROM T WHERE N = 0;
-
- return rs;
- end;
-$$ language plpgsql;
-
-select fn_10_to_62(3843); select fn_10_to_62(0); select fn_10_to_62(3844);
------------------------------------------------------------------------------------------------------ 10.62杩涘埗杞�10杩涘埗
--- drop function fn_62_to_10(varchar);
-create or replace function fn_62_to_10(ch varchar)
-returns numeric as $$
- declare
- rs numeric(30, 0);
- begin
- WITH RECURSIVE T(S, N) AS (
- SELECT ch S, 0::NUMERIC N
- UNION ALL
- SELECT SUBSTR(S, 2), (POWER(62, LENGTH(S)::NUMERIC - 1) * (strpos('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', SUBSTR(S, 1, 1)) - 1) + N) FROM T WHERE LENGTH(S) > 0
- )
- SELECT N INTO rs FROM T WHERE LENGTH(S) < 1;
-
- return rs;
- end;
-$$ language plpgsql;
-
-select fn_62_to_10('zz'); select fn_62_to_10('0'); select fn_62_to_10('100');
------------------------------------------------------------------------------------------------------ 11.鑾峰彇涓嬩竴涓紪鐮�
--- drop function fn_get_next_code(integer, integer);
-create or replace function fn_get_next_code(pid integer, genre integer)
-returns varchar as $$
- declare
- cc integer;
- code varchar;
- pcode varchar;
- tab varchar := 'dep';
- begin
- if (pid is null or genre is null) then
- return '00';
- end if;
- if genre = 2 then
- tab := 'dir';
- end if;
-
- execute 'select code from lf.sys_' || tab || ' where id = ' || pid into pcode;
- if pcode is null then
- pcode := '';
- end if;
-
- for ii in 0..3843 loop
- code := fn_10_to_62(ii);
- if length(code) = 1 then
- code := '0' || code;
- end if;
-
- execute 'select count(*) from lf.sys_' || tab || ' where pid = ' || pid || ' and code = ''' || pcode || code || '''' into cc;
- if cc = 0 then
- return pcode || code;
- end if;
- end loop;
-
- return pcode || '00';
- end;
-$$ language plpgsql;
-
-select fn_get_next_code(0, 1); select fn_get_next_code(0, 2);
------------------------------------------------------------------------------------------------------ 12.璁剧疆琛ㄧ殑缂栫爜锛�1-鍗曚綅锛�2-鐩綍
--- drop function fn_set_tab_codes(integer, integer);
-create or replace function fn_set_tab_codes(pid integer, genre integer)
-returns integer as $$
- declare
- rid integer;
- code varchar;
- cc integer := 0;
- tab varchar := 'dep';
- begin
- if (pid is null or genre is null) then
- return 0;
- end if;
- if genre = 2 then
- tab := 'dir';
- end if;
-
- execute 'select count(*) from lf.sys_' || tab || ' where pid = ' || pid into cc;
- if cc = 0 then
- return 0;
- end if;
-
- for rid in execute 'select id from lf.sys_' || tab || ' where code is null and pid = ' || pid || ' order by order_num' loop
- code := fn_get_next_code(pid, genre);
- execute 'update lf.sys_' || tab || ' set code = ''' || code || ''' where id = ' || rid;
-
- cc := cc + fn_set_tab_codes(rid, genre);
- end loop;
-
- return cc;
- end;
-$$ language plpgsql;
-
-select id, name, code from lf.sys_dir order by id;
-select fn_set_tab_codes(1, 1); select fn_set_tab_codes(0, 2);
------------------------------------------------------------------------------------------------------ 13.鑾峰彇鐩綍缂栫爜杩囨护鏉′欢 *
--- drop function fn_dir_similar(varchar);
-create or replace function fn_dir_similar(name varchar)
-returns varchar as $$
- declare
- code varchar;
- str varchar := '';
- begin
- for code in execute 'select code from lf.sys_dir where name=''' || name || '''' loop
- str := str || '|' || code;
- end loop;
-
- if length(str) > 0 then
- str := substr(str, 2);
- end if;
-
- return '(' || str || ')%';
- end;
-$$ language plpgsql;
-
-select * from lf.sys_meta where depcode similar to '(00|01|02)%';
-select fn_dir_similar('鍩虹娴嬬粯'); select fn_dir_similar('娴嬬粯锛圗SV锛�'); select * from lf.sys_meta where dircode similar to fn_dir_similar('鍩虹娴嬬粯');
------------------------------------------------------------------------------------------------------ 14.FME鏃ュ織琛ㄨЕ鍙戝櫒鍑芥暟
--- drop function fn_meta_insert();
-create or replace function fn_meta_insert() returns trigger as $$
- begin
- update lf.sys_meta set tab = new.pg_ns || '.' || new.tcdm, layer = new.tcmc, rows = new.count where eventid = new.parentid;
-
- return new;
- end;
-$$ language plpgsql;
-
--- select id, pg_ns || '.' || tcdm, parentid from lf.sys_fme_log;
------------------------------------------------------------------------------------------------------ 15.鍗曚綅琛ㄨЕ鍙戝櫒鍑芥暟
--- drop function fn_dep_insert();
-create or replace function fn_dep_insert() returns trigger as $$
- begin
- update lf.sys_dep set code = fn_get_next_code(new.pid, 1) where id = new.id;
-
- return new;
- end;
-$$ language plpgsql;
-
------------------------------------------------------------------------------------------------------ 16.鐩綍琛ㄨЕ鍙戝櫒鍑芥暟
--- drop function fn_dir_insert();
-create or replace function fn_dir_insert() returns trigger as $$
- begin
- update lf.sys_dir set code = fn_get_next_code(new.pid, 2) where id = new.id;
-
- return new;
- end;
-$$ language plpgsql;
-
------------------------------------------------------------------------------------------------------ 17.鍚屾椤圭洰鍚嶇О瑙﹀彂鍣ㄥ嚱鏁�
--- drop function fn_bs_prj();
-create or replace function fn_bs_prj() returns trigger as $$
- begin
- if (new.dirid is not null and length(new.dirid) > 1) then
- select name into new.projname from lf.sys_dir where code = substr(new.dirid, 1, 2) limit 1;
- end if;
-
- return new;
- end;
-$$ language plpgsql;
------------------------------------------------------------------------------------------------------ 19.璺緞鍒嗘瀽
--- 鍒犻櫎宸插瓨鍦ㄧ殑鍑芥暟
--- drop function pgr_fromAtoB(varchar, double precision, double precision, double precision, double precision);
-
--- 鍩轰簬浠绘剰涓ょ偣涔嬮棿鐨勬渶鐭矾寰勫垎鏋�
-create or replace function pgr_fromAtoB (
- in tbl varchar, -- 鏁版嵁搴撹〃鍚�
- in x1 double precision, -- 璧风偣x鍧愭爣
- in y1 double precision, -- 璧风偣y鍧愭爣
- in x2 double precision, -- 缁堢偣x鍧愭爣
- in y2 double precision, -- 缁堢偣y鍧愭爣
- out seq integer, -- 閬撹矾搴忓彿
- out gid integer,
- out name text, -- 閬撹矾鍚�
- out heading double precision,
- out cost double precision, -- 娑堣��
- out geom geometry -- 閬撹矾鍑犱綍闆嗗悎
-) returns setof record as $body$
-declare
- sql text;
- rec record;
- source integer;
- target integer;
- point integer;
-begin
--- 鏌ヨ璺濈鍑哄彂鐐规渶杩戠殑閬撹矾鑺傜偣
-execute 'select id::integer from ' || quote_ident(tbl)
- || '_vertices_pgr order by the_geom <-> st_geometryfromtext(''point('
- || x1 || ' ' || y1 || ')'',4490) limit 1' into rec;
-source := rec.id;
-
--- 鏌ヨ璺濈鐩殑鍦版渶杩戠殑閬撹矾鑺傜偣
-execute 'select id::integer from ' || quote_ident(tbl)
- || '_vertices_pgr order by the_geom <-> st_geometryfromtext(''point('
- || x2 || ' ' || y2 || ')'',4490) limit 1' into rec;
-target := rec.id;
-
--- 鏈�鐭矾寰勬煡璇�
-seq := 0;
-sql := 'select gid, geom, node as name, cost, source, target, st_reverse(geom) as flip_geom from '
- || 'pgr_dijkstra(''select gid as id,source::integer,target::integer,'
- || 'length::float as cost from '
- || quote_ident(tbl) || ''', '
- || source || ', ' || target
- || ' ,false) as di, '
- || quote_ident(tbl) || ' where di.edge = gid order by seq';
-
--- remember start point
-point := source;
-for rec in execute sql
- loop
- -- flip geometry (if required)
- if ( point != rec.source ) then
- rec.geom := rec.flip_geom;
- point := rec.source;
- else
- point := rec.target;
- end if;
-
- -- calculate heading (simplified)
- execute 'select degrees( st_azimuth(st_startpoint(''' || rec.geom::text
- || '''),st_endpoint(''' || rec.geom::text || ''') ) )' into heading;
-
- -- return record
- seq := seq + 1;
- gid := rec.gid;
- name := rec.name;
- cost := rec.cost;
- geom := rec.geom;
- return next;
- end loop;
-return;
-end;
-$body$ language 'plpgsql' volatile strict;
-
-select ST_astext(ST_Union(geom)) as route from pgr_fromAtoB('lrdl'::text, 116.78999, 39.9468, 116.80458, 39.94758);
-select st_astext(geom) route from pgr_fromAtoB('lrdl', 116.78999, 39.9468, 116.80458, 39.94758);
-
-/******************************************************************************** 浠ヤ笅涓鸿Е鍙戝櫒 ********************************************************************************/
--- drop trigger fme_log_trigger on lf.sys_fme_log; -- FME鏃ュ織
-create or replace trigger fn_fme_log_trigger after insert on lf.sys_fme_log for each row execute procedure fn_meta_insert();
-
-select * from lf.sys_meta limit 10;
-/*insert into lf.sys_fme_log (parentid,dirpath,pg_ns,tcmc,tcdm,count) values ('8fb3c3dd-6a12-488d-80ae-a93cc7e8b2e3','','bs','涓嚎鎴愭灉琛�','m_pipelinepoint',0);
-select * from bs.m_pipelinepoint where parentid='8fb3c3dd-6a12-488d-80ae-a93cc7e8b2e3';*/
-
--- drop trigger fn_dep_trigger on lf.sys_dep; -- 鍗曚綅
-create or replace trigger fn_dep_trigger after insert on lf.sys_dep for each row execute procedure fn_dep_insert();
-
-select * from lf.sys_dep where pid = 0 order by id;
-/*insert into lf.sys_dep (pid,name,level,order_num) values (0,'鏂板崟浣�',1,2); delete from lf.sys_dep where pid = 0 and id > 1;*/
-
--- drop trigger fn_dir_trigger on lf.sys_dep; -- 鐩綍
-create or replace trigger fn_dir_trigger after insert on lf.sys_dir for each row execute procedure fn_dir_insert();
-
-select * from lf.sys_dir where pid = 0 order by id;
-/*insert into lf.sys_dir (pid,name,level,order_num) values (0,'鏂扮洰褰�',1,6); delete from lf.sys_dir where pid = 0 and id > 309;*/
-
-
--- drop trigger if exists fn_m_pipeline_trigger on bs.m_pipeline; -- 绠＄嚎
-create or replace trigger fn_m_pipeline_trigger before insert on bs.m_pipeline for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_m_pipesegment_trigger on bs.m_pipesegment; -- 绠℃
-create or replace trigger fn_m_pipesegment_trigger before insert on bs.m_pipesegment for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_m_sitepoint_trigger on bs.m_sitepoint; -- 绔欏満鐐�
-create or replace trigger fn_m_sitepoint_trigger before insert on bs.m_sitepoint for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_m_valvehousepoint_trigger on bs.m_valvehousepoint; -- 闃�瀹ょ偣
-create or replace trigger fn_m_valvehousepoint_trigger before insert on bs.m_valvehousepoint for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_m_crossing_trigger on bs.m_crossing; -- 绌胯法瓒�
-create or replace trigger fn_m_crossing_trigger before insert on bs.m_crossing for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_m_marker_trigger on bs.m_marker; -- 涓夋々涓�鐗�
-create or replace trigger fn_m_markert_trigger before insert on bs.m_marker for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_m_hydraulic_protection_trigger on bs.m_hydraulic_protection; -- 姘村伐淇濇姢
-create or replace trigger fn_m_hydraulic_protection_trigger before insert on bs.m_hydraulic_protection for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_g_collapse_trigger on bs.g_collapse; -- 宕╁
-create or replace trigger fn_g_collapse_trigger before insert on bs.g_collapse for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_g_debrisflow_trigger on bs.g_debrisflow; -- 娉ョ煶娴�
-create or replace trigger fn_g_debrisflow_trigger before insert on bs.g_debrisflow for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_g_ground_collapse_trigger on bs.g_ground_collapse; -- 鍦伴潰濉岄櫡
-create or replace trigger fn_g_ground_collapse_trigger before insert on bs.g_ground_collapse for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_g_highandsteep_slope_trigger on bs.g_highandsteep_slope; -- 楂橀櫋杈瑰潯
-create or replace trigger fn_g_highandsteep_slope_trigger before insert on bs.g_highandsteep_slope for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_g_landslide_trigger on bs.g_landslide; -- 婊戝潯
-create or replace trigger fn_g_landslide_trigger before insert on bs.g_landslide for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_g_unstable_slope_trigger on bs.g_unstable_slope; -- 涓嶇ǔ瀹氭枩鍧�
-create or replace trigger fn_g_unstable_slope_trigger before insert on bs.g_unstable_slope for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_g_water_damage_trigger on bs.g_water_damage; -- 姘存瘉
-create or replace trigger fn_g_water_damage_trigger before insert on bs.g_water_damage for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_u_underground_hm_trigger on bs.u_underground_hydrological_monitoring; -- 鍦颁笅姘存枃鐩戞祴
-create or replace trigger fn_u_underground_hm_trigger before insert on bs.u_underground_hydrological_monitoring for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_u_sectionline_trigger on bs.u_sectionline; -- 宸ョ▼鍦拌川鍓栭潰绾�
-create or replace trigger fn_u_sectionline_trigger before insert on bs.u_sectionline for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_m_detaction_pipelinepoint_trigger on bs.m_detaction_pipelinepoint; -- 鍦颁笅绠＄嚎鎺㈡祴鎴愭灉琛�
-create or replace trigger fn_m_detaction_pipelinepoint_trigger before insert on bs.m_detaction_pipelinepoint for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_m_equipment_nameplate_trigger on bs.m_equipment_nameplate; -- 璁惧閾墝
-create or replace trigger fn_m_equipment_nameplate_trigger before insert on bs.m_equipment_nameplate for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_m_geocontrolpoint_trigger on bs.m_geocontrolpoint; -- 鎺у埗鐐硅〃
-create or replace trigger fn_m_geocontrolpoint_trigger before insert on bs.m_geocontrolpoint for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_m_groundline_trigger on bs.m_groundline; -- 鍦伴潰绾胯〃
-create or replace trigger fn_m_groundline_trigger before insert on bs.m_groundline for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_m_pipelinepoint_trigger on bs.m_pipelinepoint; -- 涓嚎鎴愭灉琛�
-create or replace trigger fn_m_pipelinepoint_trigger before insert on bs.m_pipelinepoint for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_m_poi_trigger on bs.m_poi; -- 鍏磋叮鐐�
-create or replace trigger fn_m_poi_trigger before insert on bs.m_poi for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_m_scatterpoint_trigger on bs.m_scatterpoint; -- 绂绘暎鐐硅〃
-create or replace trigger fn_m_scatterpoint_trigger before insert on bs.m_scatterpoint for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_m_surface_deformation_data_trigger on bs.m_surface_deformation_data; -- 鍦拌〃褰㈠彉鏁版嵁琛�
-create or replace trigger fn_m_surface_deformation_data_trigger before insert on bs.m_surface_deformation_data for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_s_explorationpoint_trigger on bs.s_explorationpoint; -- 鍕樻帰鐐硅〃
-create or replace trigger fn_s_explorationpoint_trigger before insert on bs.s_explorationpoint for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_s_surveyworksite_trigger on bs.s_surveyworksite; -- 鍕樺療宸ョ偣
-create or replace trigger fn_s_surveyworksitet_trigger before insert on bs.s_surveyworksite for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_s_survey_information_trigger on bs.s_survey_information; -- 鍕樺療淇℃伅琛�
-create or replace trigger fn_s_survey_information_trigger before insert on bs.s_survey_information for each row execute procedure fn_bs_prj();
-
--- drop trigger if exists fn_m_administrativedivision_trigger on bs.m_administrativedivision; -- 琛屾斂鍖哄垝琛�
-create or replace trigger fn_m_administrativedivision_trigger before insert on bs.m_administrativedivision for each row execute procedure fn_bs_prj();
-
-
-
-
-
-
-select gid,projname,eventid,pipename,dirid from bs.m_pipeline order by gid desc;
-/* insert into bs.m_pipeline (eventid,pipename,dirid) values (new_guid(),'Test','01'); delete from bs.m_pipeline where gid > 52; */
-
-/******************************************************************************** 浠ヤ笅涓哄簾寮冨嚱鏁� ********************************************************************************/
------------------------------------------------------------------------------------------------------ a.鏌ヨ鐩綍ID鏁扮粍 *
--- drop function fn_dir_arrs(pids varchar);
-create or replace function fn_dir_arrs(pids varchar) returns integer[] as $$
- declare
- ids integer[];
- sid integer;
- begin
- for sid in execute
- 'with recursive rs as(select id, pid from lf.sys_dir where id in (' || pids || ') ' ||
- 'union select a.id,a.pid from lf.sys_dir a, rs b where a.pid = b.id) ' ||
- 'select distinct id from rs order by id'
- loop
- select array_append(ids, sid) into ids;
- end loop;
-
- return ids;
- end;
-$$ language plpgsql;
-
-select fn_dir_arrs('2,5,7,9,12');
-select * from lf.sys_meta where dirid = ANY(fn_dir_arrs('2,5,7,9,12'));
------------------------------------------------------------------------------------------------------ b.鑾峰彇瀹炰綋鍚嶇О
--- drop function fn_get_entity(varchar);
-create or replace function fn_get_entity(tab varchar)
-returns varchar as $$
- declare
- str varchar;
- rs varchar = '';
- begin
- foreach str in array (select string_to_array(tab, '_')) loop
- if (length(rs) = 0 or length(str) = 1) then
- rs = rs || str;
- else
- rs = rs || initcap(str);
- end if;
- end loop;
-
- return rs;
- end;
-$$ language plpgsql;
------------------------------------------------------------------------------------------------------ c.淇敼琛ㄧ粨鏋�
--- drop function fn_alter_tab_fields();
-create or replace function fn_alter_tab_fields()
-returns integer as $$
- declare
- rec record;
- tn varchar;
- cc integer := 0;
- begin
- for rec in (select ns, tab from lf.sys_dict group by ns, tab order by ns, tab)
- loop
- raise notice 'ns = %, tab = %.', rec.ns, rec.tab;
-
- tn := rec.tab;
- if length(tn) > 32 then
- tn := replace(tn, 'standard_frozen_depth_', 's_f_d_');
- tn := replace(tn, 'general_report_on_', 'g_r_o_');
- tn := replace(tn, 'prevention_and_control_', 'p_a_c_');
- tn := replace(tn, 'water_curtain_roadway_', 'w_c_r_');
- end if;
-
- execute format('alter table %s.%s alter column %s type varchar(24)', rec.ns, rec.tab, 'depid');
- execute format('alter table %s.%s alter column %s type varchar(24)', rec.ns, rec.tab, 'dirid');
-
- execute format('update %s.%s a set depid = (select code from lf.sys_dep b where b.id = cast(a.depid as integer))', rec.ns, rec.tab);
- execute format('update %s.%s a set dirid = (select code from lf.sys_dir b where b.id = cast(a.dirid as integer))', rec.ns, rec.tab);
-
- --execute format('drop index if exists %s.idx_%s_depid', rec.ns, rec.tab);
- execute format('create index idx_%s_depid on %s.%s (depid)', tn, rec.ns, rec.tab);
-
- --execute format('drop index if exists %s.idx_%s_dirid', rec.ns, rec.tab);
- execute format('create index idx_%s_dirid on %s.%s (dirid)', tn, rec.ns, rec.tab);
-
- cc := cc + 1;
- end loop;
-
- return cc;
- end;
-$$ language plpgsql;
-
-select fn_alter_tab_fields();
-
-select gid,depid,dirid from bd.b_borehole limit 10;
-select gid,depid,dirid from bd.dlg_agnp limit 10;
-select gid,depid,dirid from bd.dlg_25w_resa limit 10;
-select gid,depid,dirid from bd.dlg_25w_boul limit 10;
------------------------------------------------------------------------------------------------------ d.10杩涘埗涓�62杩涘埗浜掕浆
--- 10杩涘埗杞�62杩涘埗
-WITH RECURSIVE T(N, S) AS (
- SELECT 3843::NUMERIC(30, 0) N, '' S
- UNION ALL
- SELECT trunc(N / 62)::NUMERIC(30, 0), substr('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', (N % 62)::INT + 1, 1) || S FROM T WHERE N > 0
-)
-SELECT S FROM T WHERE N = 0;
-
--- 62杩涘埗杞�10杩涘埗
-WITH RECURSIVE T(S, N) AS (
- SELECT 'ZZ' S, 0::NUMERIC N
- UNION ALL
- SELECT SUBSTR(S, 2), (POWER(62, LENGTH(S)::NUMERIC - 1) * (strpos('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', SUBSTR(S, 1, 1)) - 1) + N) FROM T WHERE LENGTH(S) > 0
-)
-SELECT N::NUMERIC(30, 0) FROM T WHERE LENGTH(S) < 1;
------------------------------------------------------------------------------------------------------ e.FME鏃ュ織琛ㄨЕ鍙戝櫒鍑芥暟 *
--- drop function fn_meta_insert();
-create or replace function fn_meta_insert() returns trigger as $$
- begin
- update lf.sys_meta set tab = new.pg_ns || '.' || new.tcdm, layer = new.tcmc, rows = new.count where eventid = new.parentid;
-
- --if new.count > 0 and position(new.tcdm in 'b_pac_hydrogeology,b_pac_frozensoil,b_pac_geologic_hazard,b_pac_marine_meteorological,b_pac_meteorological,m_equipment_nameplate,m_hydraulic_protection,m_marker,s_explorationpoint,u_sectionline') > 0 then
- -- select fn_auto_attach(new.id, new.pg_ns || '.' || new.tcdm, new.parentid);
- --end if;
-
- return new;
- end;
-$$ language plpgsql;
------------------------------------------------------------------------------------------------------ f.鍚屾闄勪欢鍑芥暟 *
--- drop function fn_auto_attach(integer, varchar, varchar);
-create or replace function fn_auto_attach(sid integer, tab varchar, pid varchar) returns void as $$
- declare
- rec record;
- str varchar;
- field varchar := 'materiname';
- begin
- if position('bs.' in tab) > 0 then field := 'photono'; end if;
-
- execute format('select count(*) from %s where parentid = ''%s'' and %s is not null', tab, pid, field) into sid;
- --raise notice 'tab = %, pid = %, field = %, rows = %', tab, pid, field, sid;
-
- if sid = 0 then return; end if;
-
- for rec in execute format('select eventid "eid",%s "ns" from %s where parentid=''%s'' and %s is not null', field, tab, pid, field) loop
- foreach str in array (select string_to_array(replace(replace(replace(rec.ns, '锛�', ','), '锛�', ','), ';', ','), ',')) loop
- --raise notice 'eventid = %, photono = %', rec.eid, str;
-
- execute format('insert into lf.sys_attach (name, guid, path, sizes, create_user, tab, tab_guid) ' ||
- 'select name, guid, path, sizes, create_user, ''%s'', ''%s'' from lf.sys_meta ' ||
- 'where name = ''%s'' and create_time > now()::timestamp + ''-1 hour'' order by id desc limit 1', tab, rec.eid, str);
- end loop;
- end loop;
- end;
-$$ language plpgsql;
-
--- select fn_auto_attach(142); select fn_auto_attach(143);
------------------------------------------------------------------------------------------------------
diff --git a/data/db_tab.sql b/data/db_tab.sql
deleted file mode 100644
index f4fabe8..0000000
--- a/data/db_tab.sql
+++ /dev/null
@@ -1,1321 +0,0 @@
--- create sequence lf.sys_meta_id_seq increment by 1 minvalue 1 maxvalue 2147483647 start 1 cache 1;
--- alter sequence lf.sys_layer_id_seq restart with 1;
--- vacuum analyze md.md_zxcg;
------------------------------------------------------------------------------------------------------ 01.鑿滃崟琛�
--- DROP TABLE IF EXISTS lf.sys_menu;
-create table lf.sys_menu(
- id serial primary key,
- pid integer,
- cn_name varchar(50),
- en_name varchar(50),
- url varchar(256),
- perms varchar(512),
- type smallint,
- css varchar(30),
- icon varchar(256),
- level integer,
- order_num integer,
- is_show smallint,
- create_user integer,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone,
- bak varchar(1024)
-);
-comment on table lf.sys_menu is '鑿滃崟琛�';
-comment on column lf.sys_menu.id is '涓婚敭ID';
-comment on column lf.sys_menu.pid is '鐖禝D锛�0-鏍硅妭鐐�';
-comment on column lf.sys_menu.cn_name is '涓枃鍚嶇О';
-comment on column lf.sys_menu.en_name is '鑻辨枃鍚嶇О';
-comment on column lf.sys_menu.url is '鑿滃崟Url';
-comment on column lf.sys_menu.perms is '鎺堟潈锛氬涓敤閫楀彿鍒嗛殧锛屽 user:list,user:create';
-comment on column lf.sys_menu.type is '绫诲瀷锛�0-鐩綍,1-鑿滃崟,2-鎸夐挳';
-comment on column lf.sys_menu.css is '鏍峰紡琛�';
-comment on column lf.sys_menu.icon is '鍥炬爣';
-comment on column lf.sys_menu.level is '灞傜骇锛�0-鏍硅妭鐐�';
-comment on column lf.sys_menu.order_num is '鎺掑簭鍙�';
-comment on column lf.sys_menu.is_show is '鏄惁鏄剧ず';
-comment on column lf.sys_menu.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_menu.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_menu.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_menu.update_time is '鏇存柊鏃堕棿';
-comment on column lf.sys_menu.bak is '澶囨敞';
---alter table lf.sys_menu add css varchar(30);
---alter sequence lf.sys_menu_id_seq restart with 44;
-
-select * from lf.sys_menu order by order_num;
------------------------------------------------------------------------------------------------------ 02.鍗曚綅琛�
--- DROP TABLE IF EXISTS lf.sys_dep;
-create table lf.sys_dep(
- id serial primary key,
- pid integer,
- name varchar(150),
- sname varchar(50),
- code varchar(24),
- uncode varchar(50),
- addr varchar(300),
- contact varchar(50),
- fax varchar(50),
- email varchar(50),
- post varchar(20),
- website varchar(1024),
- level integer,
- order_num integer,
- create_user integer,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone,
- bak varchar(1024)
-);
-comment on table lf.sys_dep is '鍗曚綅琛�';
-comment on column lf.sys_dep.id is '涓婚敭ID';
-comment on column lf.sys_dep.pid is '鐖禝D锛�0-鏍硅妭鐐�';
-comment on column lf.sys_dep.name is '鍚嶇О';
-comment on column lf.sys_dep.sname is '绠�绉�';
-comment on column lf.sys_dep.code is '缂栫爜';
-comment on column lf.sys_dep.uncode is '鏈烘瀯浠ｇ爜';
-comment on column lf.sys_dep.addr is '鍦板潃';
-comment on column lf.sys_dep.contact is '鑱旂郴鏂瑰紡';
-comment on column lf.sys_dep.fax is '浼犵湡';
-comment on column lf.sys_dep.email is '鐢靛瓙閭欢';
-comment on column lf.sys_dep.post is '閭斂缂栫爜';
-comment on column lf.sys_dep.website is '缃戠珯鍦板潃';
-comment on column lf.sys_dep.level is '灞傜骇锛�0-鏍硅妭鐐�';
-comment on column lf.sys_dep.order_num is '鎺掑簭鍙�';
-comment on column lf.sys_dep.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_dep.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_dep.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_dep.update_time is '鏇存柊鏃堕棿';
-comment on column lf.sys_dep.bak is '澶囨敞';
--- alter sequence lf.sys_dep_id_seq restart with 45;
--- alter table lf.sys_dep rename short to sname;
--- update lf.sys_dep set name='涓浗xxx宸ョ▼鏈夐檺鍏徃' where name='涓浗鐭虫补澶╃劧姘旂閬撳伐绋嬫湁闄愬叕鍙�';
-
-select * from lf.sys_dep order by id;
------------------------------------------------------------------------------------------------------ 03.鐩綍琛�
--- DROP TABLE IF EXISTS lf.sys_dir;
-create table lf.sys_dir(
- id serial primary key,
- pid integer,
- name varchar(150),
- exts varchar(200),
- descr varchar(1024),
- level integer,
- checks varchar(100),
- code varchar(24),
- order_num integer,
- create_user integer,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone,
- bak varchar(1024)
-);
-comment on table lf.sys_dir is '鐩綍琛�';
-comment on column lf.sys_dir.id is '涓婚敭ID';
-comment on column lf.sys_dir.pid is '鐖禝D锛�0-鏍硅妭鐐�';
-comment on column lf.sys_dir.name is '鍚嶇О';
-comment on column lf.sys_dir.exts is '鎵╁睍鍚�';
-comment on column lf.sys_dir.descr is '鐩綍璇存槑';
-comment on column lf.sys_dir.level is '灞傜骇锛�0-鏍硅妭鐐�';
-comment on column lf.sys_dir.checks is '妫�鏌ラ」';
-comment on column lf.sys_dir.code is '缂栫爜';
-comment on column lf.sys_dir.order_num is '鎺掑簭鍙�';
-comment on column lf.sys_dir.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_dir.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_dir.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_dir.update_time is '鏇存柊鏃堕棿';
-comment on column lf.sys_dir.bak is '澶囨敞';
---alter sequence lf.sys_dir_id_seq restart with 124;
-
-select * from lf.sys_dir order by id;
------------------------------------------------------------------------------------------------------ 04.鐢ㄦ埛琛�
--- DROP TABLE IF EXISTS lf.sys_user;
-create table lf.sys_user(
- id serial primary key,
- depid integer default 1,
- depcode varchar(24),
- uid varchar(20) unique,
- uname varchar(50),
- pwd varchar(200),
- salt varchar(20),
- sex smallint default 1,
- native varchar(50) default '姹�',
- contact varchar(50),
- job varchar(50),
- email varchar(50),
- addr varchar(300),
- edu varchar(20),
- idcard varchar(20),
- status smallint default 0,
- create_user integer,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone,
- bak varchar(1024)
-);
-comment on table lf.sys_user is '鐢ㄦ埛琛�';
-comment on column lf.sys_user.id is '涓婚敭ID';
-comment on column lf.sys_user.depid is '鍗曚綅ID';
-comment on column lf.sys_user.depcode is '鍗曚綅缂栫爜';
-comment on column lf.sys_user.uid is '鐢ㄦ埛ID';
-comment on column lf.sys_user.uname is '鐢ㄦ埛鍚�';
-comment on column lf.sys_user.pwd is 'MD5瀵嗙爜';
-comment on column lf.sys_user.salt is 'MD5鐩�';
-comment on column lf.sys_user.sex is '鎬у埆锛�0-濂�,1-鐢�,-1-鏈煡';
-comment on column lf.sys_user.native is '绫嶈疮';
-comment on column lf.sys_user.contact is '鑱旂郴鏂瑰紡';
-comment on column lf.sys_user.job is '宸ヤ綔';
-comment on column lf.sys_user.email is '鐢靛瓙閭欢';
-comment on column lf.sys_user.addr is '鍦板潃';
-comment on column lf.sys_user.edu is '鏁欒偛';
-comment on column lf.sys_user.idcard is '璇佷欢鍙�';
-comment on column lf.sys_user.status is '鐘舵�侊細0-姝ｅ父,1-绂佺敤,2-鍒犻櫎,3-鐢宠,4-鎷掓壒';
-comment on column lf.sys_user.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_user.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_user.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_user.update_time is '鏇存柊鏃堕棿';
-comment on column lf.sys_user.bak is '澶囨敞';
-/* insert into lf.sys_user (depid,uid,uname,pwd,create_user) values (1,'admin','绠＄悊鍛�','000000',1);
-insert into lf.sys_user (depid,uid,uname,pwd,create_user) values (20,'xl','绾胯矾瀹�','000000',1);
-insert into lf.sys_user (depid,uid,uname,pwd,create_user) values (21,'cky','绌胯法瓒婂','000000',1);
-insert into lf.sys_user (depid,uid,uname,pwd,create_user) values (22,'tj','鍦熷缓瀹�','000000',1);
-insert into lf.sys_user (depid,uid,uname,pwd,create_user) values (38,'kc','鍕樺療瀹�','000000',1);
-insert into lf.sys_user (id,depid,uid,uname,pwd,create_user) values (0,1,'guest','璁垮','',1);
-update lf.sys_user set pwd='b37f70636f1164e86cc8796201737933f65af63918d8442b'; */
-
-select * from lf.sys_user order by id;
------------------------------------------------------------------------------------------------------ 05.鐧诲綍鏃ュ織琛�
--- DROP TABLE IF EXISTS lf.sys_login;
-create table lf.sys_login(
- id serial8,
- appid integer default 1,
- ip varchar(30),
- type smallint default 1,
- status smallint default 1,
- descr varchar(100),
- userid integer default 1,
- optime timestamp(6) without time zone default now()
-) partition by range(optime);
-
-create table lf.sys_login_2022 partition of lf.sys_login for values from ('2022-01-01') to ('2023-01-01');
-create INDEX index_sys_login_2022_optime on lf.sys_login_2022 (optime);
-create table lf.sys_login_2023 partition of lf.sys_login for values from ('2023-01-01') to ('2024-01-01');
-create INDEX index_sys_login_2023_optime on lf.sys_login_2023 (optime);
-create table lf.sys_login_2024 partition of lf.sys_login for values from ('2024-01-01') to ('2025-01-01');
-create INDEX index_sys_login_2024_optime on lf.sys_login_2024 (optime);
-create table lf.sys_login_2025 partition of lf.sys_login for values from ('2025-01-01') to ('2026-01-01');
-create INDEX index_sys_login_2025_optime on lf.sys_login_2025 (optime);
-create table lf.sys_login_2026 partition of lf.sys_login for values from ('2026-01-01') to ('2027-01-01');
-create INDEX index_sys_login_2026_optime on lf.sys_login_2026 (optime);
-create table lf.sys_login_2027 partition of lf.sys_login for values from ('2027-01-01') to ('2028-01-01');
-create INDEX index_sys_login_2027_optime on lf.sys_login_2027 (optime);
-
-comment on table lf.sys_login is '鐧诲綍鏃ュ織琛�';
-comment on column lf.sys_login.id is '涓婚敭ID';
-comment on column lf.sys_login.appid is '搴旂敤绋嬪簭ID锛�1-绠￠亾鍩虹澶ф暟鎹钩鍙�,2-澶栭儴绯荤粺闆嗘垚';
-comment on column lf.sys_login.ip is 'IP鍦板潃';
-comment on column lf.sys_login.type is '绫诲埆锛�1-鐧诲綍,2-鏍￠獙,3-鐧诲嚭';
-comment on column lf.sys_login.status is '鐘舵�侊細1-鎴愬姛,0-澶辫触';
-comment on column lf.sys_login.descr is '鎻忚堪';
-comment on column lf.sys_login.userid is '鐧昏浜篒D';
-comment on column lf.sys_login.optime is '鐧昏鏃堕棿';
-/* insert into lf.sys_login (appid,ip,userid) values (1,'192.168.20.106',1);
-insert into lf.sys_login (appid,ip,userid,optime) values (1,'192.168.20.106',1,'2024-01-02');
-delete from lf.sys_login_2024; */
-
-select * from lf.sys_login; select * from lf.sys_login_2022; select * from lf.sys_login_2024;
-select a.*,b.uname from lf.sys_login a inner join lf.sys_user b on a.userid = b.id;
------------------------------------------------------------------------------------------------------ 06.鎿嶄綔鏃ュ織琛�
--- DROP TABLE IF EXISTS lf.sys_operate;
-create table lf.sys_operate(
- id serial8,
- modular1 varchar(50),
- modular2 varchar(50),
- url varchar(1024),
- ip varchar(30),
- exec bigint,
- clazz varchar(100),
- type smallint default 1,
- userid integer default 1,
- optime timestamp(6) without time zone default now(),
- bak varchar(1024)
-) partition by range(optime);
-
-create table lf.sys_operate_2022 partition of lf.sys_operate for values from ('2022-01-01') to ('2023-01-01');
-create INDEX index_sys_operate_2022_optime on lf.sys_operate_2022 (optime);
-create table lf.sys_operate_2023 partition of lf.sys_operate for values from ('2023-01-01') to ('2024-01-01');
-create INDEX index_sys_operate_2023_optime on lf.sys_operate_2023 (optime);
-create table lf.sys_operate_2024 partition of lf.sys_operate for values from ('2024-01-01') to ('2025-01-01');
-create INDEX index_sys_operate_2024_optime on lf.sys_operate_2024 (optime);
-create table lf.sys_operate_2025 partition of lf.sys_operate for values from ('2025-01-01') to ('2026-01-01');
-create INDEX index_sys_operate_2025_optime on lf.sys_operate_2025 (optime);
-create table lf.sys_operate_2026 partition of lf.sys_operate for values from ('2026-01-01') to ('2027-01-01');
-create INDEX index_sys_operate_2026_optime on lf.sys_operate_2026 (optime);
-create table lf.sys_operate_2027 partition of lf.sys_operate for values from ('2027-01-01') to ('2028-01-01');
-create INDEX index_sys_operate_2027_optime on lf.sys_operate_2027 (optime);
-
-comment on table lf.sys_operate is '鎿嶄綔鏃ュ織琛�';
-comment on column lf.sys_operate.id is '涓婚敭ID';
-comment on column lf.sys_operate.modular1 is '涓�绾фā鍧楋細濡� 鏁版嵁绠＄悊';
-comment on column lf.sys_operate.modular2 is '浜岀骇妯″潡锛氬 鐩綍绠＄悊';
-comment on column lf.sys_operate.url is '鎿嶄綔缃戝潃';
-comment on column lf.sys_operate.ip is 'IP鍦板潃';
-comment on column lf.sys_operate.exec is '鎿嶄綔鏃堕棿锛氬崟浣嶄负姣';
-comment on column lf.sys_operate.clazz is '绫诲悕\鏂规硶鍚�';
-comment on column lf.sys_operate.type is '绫诲埆锛�0-鍏跺畠,1-鏌ョ湅,2-鏂板,3-淇敼,4-鍒犻櫎,5-涓婁紶,6-涓嬭浇';
-comment on column lf.sys_operate.userid is '鎿嶄綔浜篒D';
-comment on column lf.sys_operate.optime is '鎿嶄綔鏃堕棿';
-comment on column lf.sys_operate.bak is '澶囨敞';
-/* insert into lf.sys_operate (modular,url,ip,type) values ('鏁版嵁绠＄悊\鐩綍绠＄悊','data/manage/update','192.168.20.106',5);
-insert into lf.sys_operate (modular,url,ip,type,optime) values ('鏁版嵁绠＄悊\鐩綍绠＄悊','data/manage/delete','192.168.20.107',4,'2024-01-02');
-delete from lf.sys_operate_2024; */
-
-alter table lf.sys_operate add column modular1 varchar(50); alter table lf.sys_operate drop column modular;
-select split_part(modular,'\',1),split_part(modular,'\',2) from lf.sys_operate;
-update lf.sys_operate set modular1=split_part(modular,'\',1),modular2=split_part(modular,'\',2);
-
-select a.*,b.uname from lf.sys_operate a inner join lf.sys_user b on a.userid = b.id;
-select * from lf.sys_operate; select * from lf.sys_operate_2022; select * from lf.sys_operate_2024;
------------------------------------------------------------------------------------------------------ 07.浠ょ墝琛�
--- DROP TABLE IF EXISTS lf.sys_token;
-create table lf.sys_token(
- id serial8,
- token varchar(40),
- duration integer default 240,
- expire timestamp(6) without time zone default now()::timestamp + '4 hour',
- type smallint default 0,
- ip varchar(30),
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone
-) partition by range(create_time);
-
-create table lf.sys_token_2022 partition of lf.sys_token for values from ('2022-01-01') to ('2023-01-01');
-create INDEX index_sys_token_2022_create_time on lf.sys_token_2022 (create_time);
-create table lf.sys_token_2023 partition of lf.sys_token for values from ('2023-01-01') to ('2024-01-01');
-create INDEX index_sys_token_2023_create_time on lf.sys_token_2023 (create_time);
-create table lf.sys_token_2024 partition of lf.sys_token for values from ('2024-01-01') to ('2025-01-01');
-create INDEX index_sys_token_2024_create_time on lf.sys_token_2024 (create_time);
-create table lf.sys_token_2025 partition of lf.sys_token for values from ('2025-01-01') to ('2026-01-01');
-create INDEX index_sys_token_2025_create_time on lf.sys_token_2025 (create_time);
-create table lf.sys_token_2026 partition of lf.sys_token for values from ('2026-01-01') to ('2027-01-01');
-create INDEX index_sys_token_2026_create_time on lf.sys_token_2026 (create_time);
-create table lf.sys_token_2027 partition of lf.sys_token for values from ('2027-01-01') to ('2028-01-01');
-create INDEX index_sys_token_2027_create_time on lf.sys_token_2027 (create_time);
-
-comment on table lf.sys_token is '浠ょ墝琛�';
-comment on column lf.sys_token.id is '涓婚敭ID';
-comment on column lf.sys_token.token is '浠ょ墝';
-comment on column lf.sys_token.duration is '浣跨敤鏃堕棿锛氶粯璁�240鍒嗛挓(4灏忔椂)';
-comment on column lf.sys_token.expire is '澶辨晥鏃堕棿';
-comment on column lf.sys_token.type is '绫诲瀷锛�0-涓存椂,1-鍥哄畾';
-comment on column lf.sys_token.ip is 'IP鍦板潃';
-comment on column lf.sys_token.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_token.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_token.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_token.update_time is '鏇存柊鏃堕棿';
-/* insert into lf.sys_token (token,ip) values (new_guid(),'192.168.20.106');
-insert into lf.sys_token (token,ip,expire,create_time) values (new_guid(),'192.168.20.107',now()::timestamp + '1 year 4 hour',now()::timestamp + '1 year');
-delete from lf.sys_token_2023; */
-
-select * from lf.sys_token where token='91cf74dc-1d03-4937-983f-88810589b112' and expire > now() limit 1;
-select * from lf.sys_user where id=(select create_user from lf.sys_token where token='91cf74dc-1d03-4937-983f-88810589b112' and expire > now() limit 1);
-
-select * from lf.sys_token; select * from lf.sys_token_2022; select * from lf.sys_token_2023; select now()::timestamp + '1 year 4 hour';
------------------------------------------------------------------------------------------------------ 08.璧勬簮琛�
--- DROP TABLE IF EXISTS lf.sys_res;
-create table lf.sys_res(
- id serial primary key,
- name varchar(50),
- server varchar(300),
- source varchar(300),
- depid integer default 1,
- dirid integer default 1,
- code varchar(30),
- descr varchar(200),
- img varchar(40),
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone,
- bak varchar(1024)
-);
-comment on table lf.sys_res is '璧勬簮琛�';
-comment on column lf.sys_res.id is '涓婚敭ID';
-comment on column lf.sys_res.name is '鍚嶇О';
-comment on column lf.sys_res.server is '鏈嶅姟鍦板潃';
-comment on column lf.sys_res.source is '鍘熷鍦板潃';
-comment on column lf.sys_res.depid is '鍗曚綅ID';
-comment on column lf.sys_res.dirid is '鐩綍ID';
-comment on column lf.sys_res.code is '缂栫爜';
-comment on column lf.sys_res.descr is '鎻忚堪';
-comment on column lf.sys_res.img is '棰勮鍥�';
-comment on column lf.sys_res.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_res.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_res.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_res.update_time is '鏇存柊鏃堕棿';
-comment on column lf.sys_res.bak is '澶囨敞';
-/* insert into lf.sys_res (name) values ('澶╁湴鍥惧奖鍍�');
-insert into lf.sys_res (name) values ('澶╁湴鍥剧煝閲�'); */
-
-select * from lf.sys_res;
------------------------------------------------------------------------------------------------------ 09.璧勬簮鎿嶄綔琛�
--- DROP TABLE IF EXISTS lf.sys_res_op;
-create table lf.sys_res_op(
- id serial8,
- resid integer,
- type smallint default 1,
- ip varchar(30),
- userid integer default 1,
- optime timestamp(6) without time zone default now()
-) partition by range(optime);
-
-create table lf.sys_res_op_2022 partition of lf.sys_res_op for values from ('2022-01-01') to ('2023-01-01');
-create INDEX index_sys_res_op_2022_optime on lf.sys_res_op_2022 (optime);
-create table lf.sys_res_op_2023 partition of lf.sys_res_op for values from ('2023-01-01') to ('2024-01-01');
-create INDEX index_sys_res_op_2023_optime on lf.sys_res_op_2023 (optime);
-create table lf.sys_res_op_2024 partition of lf.sys_res_op for values from ('2024-01-01') to ('2025-01-01');
-create INDEX index_sys_res_op_2024_optime on lf.sys_res_op_2024 (optime);
-create table lf.sys_res_op_2025 partition of lf.sys_res_op for values from ('2025-01-01') to ('2026-01-01');
-create INDEX index_sys_res_op_2025_optime on lf.sys_res_op_2025 (optime);
-create table lf.sys_res_op_2026 partition of lf.sys_res_op for values from ('2026-01-01') to ('2027-01-01');
-create INDEX index_sys_res_op_2026_optime on lf.sys_res_op_2026 (optime);
-create table lf.sys_res_op_2027 partition of lf.sys_res_op for values from ('2027-01-01') to ('2028-01-01');
-create INDEX index_sys_res_op_2027_optime on lf.sys_res_op_2027 (optime);
-
-comment on table lf.sys_res_op is '璧勬簮鎿嶄綔琛�';
-comment on column lf.sys_res_op.id is '涓婚敭ID';
-comment on column lf.sys_res_op.resid is '璧勬簮ID';
-comment on column lf.sys_res_op.type is '鎿嶄綔绫诲瀷锛�1-鏌ョ湅,2-鏂板,3-淇敼,4-鍒犻櫎,5-涓婁紶,6-涓嬭浇,7-缁熻';
-comment on column lf.sys_res_op.ip is 'IP鍦板潃';
-comment on column lf.sys_res_op.userid is '鎿嶄綔浜篒D';
-comment on column lf.sys_res_op.optime is '鍒涘缓鏃堕棿';
-/* insert into lf.sys_res_op (resid,ip,type) values (1,'192.168.20.106',5);
-insert into lf.sys_res_op (resid,ip,type) values (1,'192.168.20.106',1);
-insert into lf.sys_res_op (resid,ip,type,optime) values (1,'192.168.20.107',4,'2024-01-02');
-delete from lf.sys_res_op_2024; */
-
-select a.*,b.uname,c.name from lf.sys_res_op a inner join lf.sys_user b on a.userid = b.id inner join lf.sys_res c on a.resid=c.id;
-
-select * from lf.sys_res_op; select * from lf.sys_res_op_2022; select * from lf.sys_res_op_2024;
------------------------------------------------------------------------------------------------------ 10.鏉冮檺琛�
--- DROP TABLE IF EXISTS lf.sys_auth;
-create table lf.sys_auth(
- id serial primary key,
- name varchar(100),
- tag varchar(100),
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone,
- bak varchar(1024)
-);
-comment on table lf.sys_auth is '鏉冮檺琛�';
-comment on column lf.sys_auth.id is '涓婚敭ID';
-comment on column lf.sys_auth.name is '鍚嶇О';
-comment on column lf.sys_auth.tag is '鏍囪瘑';
-comment on column lf.sys_auth.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_auth.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_auth.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_auth.update_time is '鏇存柊鏃堕棿';
-comment on column lf.sys_auth.bak is '澶囨敞';
-/* insert into lf.sys_auth (name,tag) values ('鏌ョ湅','/select');
-insert into lf.sys_auth (name,tag) values ('鏂板','/insert');
-insert into lf.sys_auth (name,tag) values ('淇敼','/update');
-insert into lf.sys_auth (name,tag) values ('鍒犻櫎','/delete');
-insert into lf.sys_auth (name,tag) values ('涓婁紶','/upload');
-insert into lf.sys_auth (name,tag) values ('涓嬭浇','/download');
-insert into lf.sys_auth (name,tag) values ('缁熻','/count');
-alter sequence lf.sys_auth_id_seq restart with 8; */
-
-select * from lf.sys_auth;
------------------------------------------------------------------------------------------------------ 11.瑙掕壊琛�
--- DROP TABLE IF EXISTS lf.sys_role;
-create table lf.sys_role(
- id serial primary key,
- depid integer default 1,
- name varchar(100),
- descr varchar(100),
- is_admin integer default 0,
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone,
- bak varchar(1024)
-);
-comment on table lf.sys_role is '瑙掕壊琛�';
-comment on column lf.sys_role.id is '涓婚敭ID';
-comment on column lf.sys_role.depid is '鍗曚綅ID';
-comment on column lf.sys_role.name is '鍚嶇О';
-comment on column lf.sys_role.descr is '鎻忚堪';
-comment on column lf.sys_role.is_admin is '瑙掕壊绫诲埆锛�0-鏅�氱敤鎴凤紝1-瓒呯骇绠＄悊鍛橈紝2-鏁版嵁绠＄悊鍛橈紝3-棰嗗';
-comment on column lf.sys_role.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_role.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_role.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_role.update_time is '鏇存柊鏃堕棿';
-comment on column lf.sys_role.bak is '澶囨敞';
---insert into lf.sys_role (id,depid,name,descr,is_admin) values (1,1,'Admin','绯荤粺绠＄悊鍛�',1);
-
-select * from lf.sys_role;
-select a.*,b.name depname from lf.sys_role a inner join lf.sys_dep b on a.depid = b.id order by a.id limit 10 offset 0;
------------------------------------------------------------------------------------------------------ 12.瑙掕壊-鐢ㄦ埛琛�
--- DROP TABLE IF EXISTS lf.sys_role_user;
-create table lf.sys_role_user(
- id serial primary key,
- roleid integer,
- userid integer,
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone
-);
-comment on table lf.sys_role_user is '瑙掕壊-鐢ㄦ埛琛�';
-comment on column lf.sys_role_user.id is '涓婚敭ID';
-comment on column lf.sys_role_user.roleid is '瑙掕壊ID';
-comment on column lf.sys_role_user.userid is '鐢ㄦ埛ID';
-comment on column lf.sys_role_user.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_role_user.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_role_user.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_role_user.update_time is '鏇存柊鏃堕棿';
---insert into lf.sys_role_user (roleid,userid) values (1,1);
-
-select * from lf.sys_role_user;
------------------------------------------------------------------------------------------------------ 13.瑙掕壊-璧勬簮琛�
--- DROP TABLE IF EXISTS lf.sys_role_res;
-create table lf.sys_role_res(
- id serial primary key,
- roleid integer,
- resid integer,
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone
-);
-comment on table lf.sys_role_res is '瑙掕壊-璧勬簮琛�';
-comment on column lf.sys_role_res.id is '涓婚敭ID';
-comment on column lf.sys_role_res.roleid is '瑙掕壊ID';
-comment on column lf.sys_role_res.resid is '璧勬簮ID';
-comment on column lf.sys_role_res.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_role_res.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_role_res.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_role_res.update_time is '鏇存柊鏃堕棿';
---insert into lf.sys_role_res (roleid,resid) values (1,1);
-
-select * from lf.sys_role_res;
------------------------------------------------------------------------------------------------------ 14.鑿滃崟-鏉冮檺琛�
--- DROP TABLE IF EXISTS lf.sys_menu_auth;
-create table lf.sys_menu_auth(
- id serial primary key,
- menuid integer,
- authid integer,
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone
-);
-comment on table lf.sys_menu_auth is '鑿滃崟-鏉冮檺琛�';
-comment on column lf.sys_menu_auth.id is '涓婚敭ID';
-comment on column lf.sys_menu_auth.menuid is '鑿滃崟ID';
-comment on column lf.sys_menu_auth.authid is '鏉冮檺ID';
-comment on column lf.sys_menu_auth.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_menu_auth.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_menu_auth.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_menu_auth.update_time is '鏇存柊鏃堕棿';
---insert into lf.sys_menu_auth (menuid,authid) values (1,1),(1,2),(1,3),(1,4),(1,5);
-
-select * from lf.sys_menu_auth;
------------------------------------------------------------------------------------------------------ 15.瑙掕壊-鑿滃崟-鏉冮檺琛�
--- DROP TABLE IF EXISTS lf.sys_role_menu_auth;
-create table lf.sys_role_menu_auth(
- id serial primary key,
- roleid integer,
- menu_auth_id integer,
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone
-);
-comment on table lf.sys_role_menu_auth is '瑙掕壊-鑿滃崟琛�';
-comment on column lf.sys_role_menu_auth.id is '涓婚敭ID';
-comment on column lf.sys_role_menu_auth.roleid is '瑙掕壊ID';
-comment on column lf.sys_role_menu_auth.menu_auth_id is '鑿滃崟-鏉冮檺ID';
-comment on column lf.sys_role_menu_auth.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_role_menu_auth.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_role_menu_auth.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_role_menu_auth.update_time is '鏇存柊鏃堕棿';
---insert into lf.sys_role_menu_auth (roleid,menu_auth_id) values (1,1);
-
-select * from lf.sys_role_menu_auth;
------------------------------------------------------------------------------------------------------ 16.鍏冩暟鎹〃
--- DROP TABLE IF EXISTS lf.sys_meta;
-create table lf.sys_meta (
- id serial primary key,
- eventid varchar(38) default new_guid(),
- metaid integer default 0,
- verid integer default 0,
- depcode varchar(24) default '00',
- dircode varchar(24) default '00',
- ismeta smallint default 0,
- name varchar(256),
- type varchar(100),
- guid varchar(38) default new_guid(),
- path varchar(512),
- sizes float8 default 0,
- tab varchar(50),
- layer varchar(50),
- rows integer,
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone,
- bak varchar(1024),
- geom geometry(MultiPolygon, 4490)
-);
--- drop index lf.index_sys_meta_geom;
-create index index_sys_meta_geom on lf.sys_meta using GIST (geom);
-comment on table lf.sys_meta is '鍏冩暟鎹〃';
-comment on column lf.sys_meta.id is '涓婚敭ID';
-comment on column lf.sys_meta.eventid is 'GUID';
-comment on column lf.sys_meta.metaid is '鐖跺厓鏁版嵁ID锛�0-娌℃湁';
-comment on column lf.sys_meta.ismeta is '鏄惁涓哄厓鏁版嵁';
-comment on column lf.sys_meta.dirid is '鐩綍ID';
-comment on column lf.sys_meta.depid is '鍗曚綅ID';
-comment on column lf.sys_meta.depcode is '鐩綍缂栫爜';
-comment on column lf.sys_meta.dircode is '鍗曚綅缂栫爜';
-comment on column lf.sys_meta.verid is '鐗堟湰ID';
-comment on column lf.sys_meta.name is '鍚嶇О';
-comment on column lf.sys_meta.type is '绫诲瀷:file-鏂囦欢锛宻hp-ShapeFile锛実db-GDB锛寊ip-鍘嬬缉鍖�';
-comment on column lf.sys_meta.guid is '鏂囦欢GUID';
-comment on column lf.sys_meta.path is '瀛樺偍璺緞';
-comment on column lf.sys_meta.sizes is '澶у皬锛氬崟浣峂B';
-comment on column lf.sys_meta.tab is '琛ㄥ悕';
-comment on column lf.sys_meta.layer is '鍥惧眰';
-comment on column lf.sys_meta.rows is '琛屾暟';
-comment on column lf.sys_meta.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_meta.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_meta.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_meta.update_time is '鏇存柊鏃堕棿';
-comment on column lf.sys_meta.bak is '澶囨敞';
-comment on column lf.sys_meta.geom is '绌洪棿浣嶇疆';
--- insert into lf.sys_meta(dirid,depid,geom,sizes,name,path) values (1,1,ST_PolygonFromText('MultiPolygon (((100 0,120 0,120 20,100 20,100 0)))', 4490),0.001,'a.jpg','c:\a.jpg');
-
-select a.*,st_astext(geom) from lf.sys_meta a; select id,name,dirid,depid,verid,type,sizes,create_user,create_time,update_user,update_time,st_astext(geom) from lf.sys_meta;
------------------------------------------------------------------------------------------------------ 17.闄勪欢琛�
--- DROP TABLE IF EXISTS lf.sys_attach;
-create table lf.sys_attach(
- id serial primary key,
- name varchar(256),
- tab varchar(100),
- tab_guid varchar(40),
- guid varchar(40) default new_guid(),
- path varchar(512),
- sizes float8 default 0;
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone
-);
-comment on table lf.sys_attach is '闄勪欢琛�';
-comment on column lf.sys_attach.id is '涓婚敭ID';
-comment on column lf.sys_attach.name is '鏂囦欢鍚�';
-comment on column lf.sys_attach.tab is '琛ㄥ悕锛氬 public.data_dir';
-comment on column lf.sys_attach.tab_guid is '琛↖D';
-comment on column lf.sys_attach.guid is '鏂囦欢ID';
-comment on column lf.sys_attach.path is '瀛樺偍璺緞';
-comment on column lf.sys_attach.sizes is '鏂囦欢澶у皬锛氬崟浣峂B';
-comment on column lf.sys_attach.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_attach.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_attach.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_attach.update_time is '鏇存柊鏃堕棿';
---insert into lf.sys_attach (name,tab,path) values ('a.jpg','public.data_dir','c:\a.jpg');
---alter table lf.sys_attach alter column sizes type float8;
-
-select * from lf.sys_attach; select length(guid) from lf.sys_attach limit 1; select * from lf.sys_attach;
------------------------------------------------------------------------------------------------------ 18.鐗堟湰琛�
--- DROP TABLE IF EXISTS lf.sys_ver;
-create table lf.sys_ver(
- id serial primary key,
- dirid integer,
- name varchar(50),
- descr varchar(256),
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone
-);
-comment on table lf.sys_ver is '闄勪欢琛�';
-comment on column lf.sys_ver.id is '涓婚敭ID';
-comment on column lf.sys_ver.dirid is '鐩綍ID';
-comment on column lf.sys_ver.name is '鍚嶇О';
-comment on column lf.sys_ver.descr is '鎻忚堪';
-comment on column lf.sys_ver.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_ver.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_ver.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_ver.update_time is '鏇存柊鏃堕棿';
-insert into lf.sys_ver (id,dirid,name) values (0,0,'榛樿');
---insert into lf.sys_ver (dirid,name) values (1003,'2020-02-01');
-
-select * from lf.sys_ver order by id;
------------------------------------------------------------------------------------------------------ 19.鏁版嵁瀛楀吀琛�
--- DROP TABLE IF EXISTS lf.sys_dict;
-create table lf.sys_dict(
- id serial primary key,
- ns varchar(20) default 'lf',
- tab varchar(100),
- tab_desc varchar(100),
- field varchar(100),
- alias varchar(100),
- type varchar(100),
- len integer default 0,
- precision smallint default 0,
- order_num smallint default 0,
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone,
- tabletype varchar(20),
- unit varchar(50),
- domain_na varchar(100),
- showtype smallint default 0,
- editable smallint,
- status smallint default 0,
- bak varchar(1024)
-);
-comment on table lf.sys_dict is '闄勪欢琛�';
-comment on column lf.sys_dict.id is '涓婚敭ID';
-comment on column lf.sys_dict.ns is '琛ㄧ┖闂�';
-comment on column lf.sys_dict.tab is '琛ㄥ悕';
-comment on column lf.sys_dict.tab_desc is '琛ㄥ悕鎻忚堪';
-comment on column lf.sys_dict.field is '瀛楁鍚�';
-comment on column lf.sys_dict.alias is '鍒悕';
-comment on column lf.sys_dict.type is '绫诲瀷';
-comment on column lf.sys_dict.len is '闀垮害';
-comment on column lf.sys_dict.precision is '绮惧害';
-comment on column lf.sys_dict.order_num is '搴忓彿';
-comment on column lf.sys_dict.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_dict.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_dict.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_dict.update_time is '鏇存柊鏃堕棿';
-comment on column lf.sys_dict.tabletype is '琛ㄧ被鍨�';
-comment on column lf.sys_dict.unit is '鍗曚綅';
-comment on column lf.sys_dict.domain_na is '鍊煎煙鍚嶇О';
-comment on column lf.sys_dict.showtype is '鏄剧ず绫诲瀷';
-comment on column lf.sys_dict.editable is '鏄惁鍙紪杈�';
-comment on column lf.sys_dict.status is '鐘舵�侊細0-姝ｅ父锛�1-鍒犻櫎锛�-1-搴熷純';
-comment on column lf.sys_dict.bak is '澶囨敞';
--- alter table lf.sys_dict alter column showtype set default 0;
--- alter sequence lf.sys_dict_id_seq restart with 300;
-
-select count(*) from lf.sys_dict where status=0 and tab='sys_user';
-select * from lf.sys_dict order by ns,tab,order_num limit 10 offset 0;
-select distinct ns,tab,fn_get_entity(tab) entity,tab_desc,tabletype from lf.sys_dict order by tab;
------------------------------------------------------------------------------------------------------ 20.鏍峰紡琛�
--- DROP TABLE IF EXISTS lf.sys_style;
-create table lf.sys_style(
- id serial primary key,
- name varchar(50),
- type varchar(20),
- dirid integer default 1,
- depid integer default 1,
- ver varchar(20) default 'v1.0',
- status smallint default 1,
- precision varchar(50),
- descr varchar(256),
- fname varchar(50),
- file_guid varchar(40) default new_guid(),
- vname varchar(50),
- view_guid varchar(40) default new_guid(),
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone,
- bak varchar(1024)
-);
-comment on table lf.sys_style is '鏍峰紡琛�';
-comment on column lf.sys_style.id is '涓婚敭ID';
-comment on column lf.sys_style.name is '鍚嶇О';
-comment on column lf.sys_style.type is '绫诲瀷';
-comment on column lf.sys_style.dirid is '鐩綍ID';
-comment on column lf.sys_style.depid is '鍗曚綅ID';
-comment on column lf.sys_style.ver is '鐗堟湰';
-comment on column lf.sys_style.status is '鐘舵�侊細0-鍋滅敤,1-鍚敤';
-comment on column lf.sys_style.precision is '绮惧害锛氬 1:1000';
-comment on column lf.sys_style.descr is '鎻忚堪';
-comment on column lf.sys_style.fname is '鏍峰紡鏂囦欢鍚�';
-comment on column lf.sys_style.file_guid is '鏍峰紡鏂囦欢ID';
-comment on column lf.sys_style.vname is '棰勮鏂囦欢鍚�';
-comment on column lf.sys_style.view_guid is '棰勮鏂囦欢ID';
-comment on column lf.sys_style.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_style.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_style.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_style.update_time is '鏇存柊鏃堕棿';
-comment on column lf.sys_style.bak is '澶囨敞';
---insert into lf.sys_style (name,type,precision,descr) values ('line.edp','edp','1:1000','绠￠亾涓績绾挎牱寮忔枃浠�');
-
-select count(*) from lf.sys_style where name like '%.edp';
-select * from lf.sys_style order by ns,tab,order_num limit 10 offset 0;
------------------------------------------------------------------------------------------------------ 21.涓嬭浇琛�
--- DROP TABLE IF EXISTS lf.sys_download;
-create table lf.sys_download(
- id serial primary key,
- name varchar(50),
- type smallint default 1,
- depid integer default 1,
- sizes float8 default 0,
- dcount integer default 0,
- pwd varchar(200),
- url varchar(512),
- descr varchar(256),
- guid varchar(40) default new_guid(),
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- download_user integer,
- download_time timestamp(6) without time zone,
- geom geometry(MultiPolygon, 4490),
- bak varchar(1024)
- --geom geometry default null,
- --CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
- --CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geom IS NULL),
- --CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 4490)
-);
--- drop index if exists lf.index_sys_download_geom;
-create index index_sys_download_geom on lf.sys_download using gist (geom);
-comment on table lf.sys_download is '涓嬭浇琛�';
-comment on column lf.sys_download.id is '涓婚敭ID';
-comment on column lf.sys_download.name is '鍚嶇О';
-comment on column lf.sys_download.type is '绫诲瀷锛�1-Shp鏂囦欢锛�2-涓撻鍥撅紝3-鍏冩暟鎹紝4-涓氬姟鏁版嵁锛�5-绠￠亾鍒嗘瀽锛�6-缁熻鎶ュ憡';
-comment on column lf.sys_download.depid is '鍗曚綅ID';
-comment on column lf.sys_download.sizes is '鏂囦欢澶у皬锛氬崟浣峂B';
-comment on column lf.sys_download.dcount is '涓嬭浇娆℃暟';
-comment on column lf.sys_download.pwd is 'MD5';
-comment on column lf.sys_download.url is '涓嬭浇鍦板潃';
-comment on column lf.sys_download.descr is '鎻忚堪';
-comment on column lf.sys_download.guid is '鏂囦欢ID';
-comment on column lf.sys_download.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_download.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_download.download_user is '涓嬭浇浜篒D';
-comment on column lf.sys_download.download_time is '涓嬭浇鏃堕棿';
-comment on column lf.sys_download.bak is '澶囨敞';
-comment on column lf.sys_download.geom is '绌洪棿浣嶇疆';
--- insert into lf.sys_download (name,pwd,url,descr) values ('2022-09-27','123456','','娴嬭瘯涓嬭浇');
-
-select * from lf.sys_download order by id desc;
------------------------------------------------------------------------------------------------------ 22.鍙傛暟璁剧疆琛�
--- DROP TABLE IF EXISTS lf.sys_args;
-create table lf.sys_args(
- id serial primary key,
- name varchar(50),
- cvalue integer default 0,
- dvalue integer default 0,
- min_value integer default 0,
- max_value integer default 0,
- times integer default 1,
- descr varchar(256),
- mark varchar(50),
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone
-);
-comment on table lf.sys_args is '鍙傛暟璁剧疆琛�';
-comment on column lf.sys_args.id is '涓婚敭ID';
-comment on column lf.sys_args.name is '鍚嶇О';
-comment on column lf.sys_args.cvalue is '褰撳墠鍊�';
-comment on column lf.sys_args.dvalue is '榛樿鍊�';
-comment on column lf.sys_args.min_value is '鏈�灏忓��';
-comment on column lf.sys_args.max_value is '鏈�澶у��';
-comment on column lf.sys_args.times is '鍊嶆暟';
-comment on column lf.sys_args.descr is '鎻忚堪';
-comment on column lf.sys_args.mark is '鏍囪瘑';
-comment on column lf.sys_args.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_args.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_args.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_args.update_time is '鏇存柊鏃堕棿';
-/* insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('瀵嗙爜鍑洪敊娆℃暟',5,5,3,20,1,'榛樿鐢ㄦ埛瀵嗙爜杩炵画杈撳叆鍑洪敊5娆★紝灏辨殏鏃舵嫆缁濈櫥褰曘��','PWD_ERR_COUNT');
-insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('瀵嗙爜鍑洪敊绛夊緟鏃堕棿',5,5,1,60,1,'榛樿鐢ㄦ埛瀵嗙爜杩炵画杈撳叆澶氭鍑洪敊鏃讹紝5鍒嗛挓鍐呮嫆缁濈櫥褰曘��','PWD_ERR_TIME');
-insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('鑷姩鐧诲嚭鏃堕棿',15,15,3,1440,1,'榛樿鐢ㄦ埛15鍒嗛挓涓嶆搷浣滐紝灏辫嚜鍔ㄧ櫥鍑虹郴缁熴��','AUTO_LOGOUT');
-insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('浠ょ墝鏈夋晥鏈�',240,240,60,1440,1,'浠ょ墝榛樿鏈夋晥鏈熶负240鍒嗛挓锛岃秴鏃跺皢澶辨晥銆�','TOKEN_EXPIRE');
-insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('缂撳瓨鏈夋晥鏈�',240,240,60,1440,1,'缂撳瓨榛樿鏈夋晥鏈熶负240鍒嗛挓锛岃秴鏃跺皢澶辨晥銆�','CACHE_EXPIRE');
-insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('鏈�澶ф枃浠舵暟',2000,2000,500,2500,1,'鍗曚釜鏂囦欢澶逛笅榛樿鏈�澶у瓨鍌ㄦ枃浠剁殑鏁扮洰涓�2000涓��','MAX_FILES');
-insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('Cookie鏈夋晥鏈�',240,240,60,43200,60,'Cookie榛樿鏈夋晥鏈熶负240鍒嗛挓锛岃秴鏃跺皢澶辨晥銆�','COOKIE_MAX_AGE');
-insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('闄勪欢澶у皬',50,50,5,1024,1048576,'鍗曚釜闄勪欢澶у皬榛樿鏈�澶т负50MB銆�','MAX_FILE_SIZE');
-insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('鐢ㄦ埛璁块棶閲�',5000,5000,5,1000000,1,'褰撹秴鍑虹敤鎴疯闂噺鏃讹紝灏辨嫆缁濈櫥褰曘��','MAX_USER_LOGIN');
-insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('鏈嶅姟涓婇檺',5000,5000,5,1000000,1,'褰撹秴鍑烘湇鍔′笂闄愭椂锛屽氨鎷掔粷璁块棶銆�','MAX_SERVERS'); */
-
-select * from lf.sys_args order by id;
------------------------------------------------------------------------------------------------------ 23.榛戝悕鍗曡〃
--- DROP TABLE IF EXISTS lf.sys_blacklist;
-create table lf.sys_blacklist(
- id serial primary key,
- ip varchar(50) unique,
- type smallint default 1,
- visit integer default 0,
- descr varchar(50),
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone
-);
-comment on table lf.sys_blacklist is '榛戝悕鍗曡〃';
-comment on column lf.sys_blacklist.id is '涓婚敭ID';
-comment on column lf.sys_blacklist.ip is 'IP鍦板潃';
-comment on column lf.sys_blacklist.type is '绫诲埆锛�1-榛戝悕鍗�,2-鐧藉悕鍗�';
-comment on column lf.sys_blacklist.visit is '璁块棶娆℃暟';
-comment on column lf.sys_blacklist.descr is '鎻忚堪';
-comment on column lf.sys_blacklist.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_blacklist.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_blacklist.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_blacklist.update_time is '鏇存柊鏃堕棿';
-/* insert into lf.sys_blacklist (ip,type) values ('192.168.20.1',1);
-insert into lf.sys_blacklist (ip,type) values ('192.168.20.205',2);
-insert into lf.sys_blacklist (ip,type) values ('127.0.0.1',2);
-insert into lf.sys_blacklist (ip,type) values ('localhost',2);
-insert into lf.sys_blacklist (ip,type) values ('0:0:0:0:0:0:0:1',2);*/
-
-select * from lf.sys_blacklist;
------------------------------------------------------------------------------------------------------ 24.鏍囩粯琛�
--- DROP TABLE IF EXISTS lf.sys_mark;
-create table lf.sys_mark(
- id serial primary key,
- name varchar(50),
- wkt text,
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone
-);
-comment on table lf.sys_mark is '鏍囩粯琛�';
-comment on column lf.sys_mark.id is '涓婚敭ID';
-comment on column lf.sys_mark.name is '鍚嶇О';
-comment on column lf.sys_mark.wkt is 'WKT';
-comment on column lf.sys_mark.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_mark.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_mark.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_mark.update_time is '鏇存柊鏃堕棿';
-
-select * from lf.sys_mark;
------------------------------------------------------------------------------------------------------ 25.鍊煎煙琛�
--- drop table if exists lf.sys_domain;
-create table if not exists lf.sys_domain(
- id serial primary key,
- dom_desc varchar(100),
- dom_name varchar(50),
- dom_code varchar(50),
- code_desc varchar(50),
- level integer,
- orderid integer,
- bsm varchar(50),
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone,
- bak varchar(1024)
-);
-comment on table lf.sys_domain is '鍊煎煙琛�';
-comment on column lf.sys_domain.id is '涓婚敭id';
-comment on column lf.sys_domain.dom_desc is '鍊煎煙琛ㄦ弿杩�';
-comment on column lf.sys_domain.dom_name is '鍊煎煙琛ㄥ悕';
-comment on column lf.sys_domain.dom_code is '缂栫爜';
-comment on column lf.sys_domain.code_desc is '缂栫爜鎻忚堪';
-comment on column lf.sys_domain.level is '灞傜骇';
-comment on column lf.sys_domain.orderid is '搴忓彿';
-comment on column lf.sys_domain.bsm is '鏍囪瘑鐮�';
-comment on column lf.sys_domain.create_user is '鍒涘缓浜篿d';
-comment on column lf.sys_domain.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_domain.update_user is '鏇存柊浜篿d';
-comment on column lf.sys_domain.update_time is '鏇存柊鏃堕棿';
-comment on column lf.sys_domain.bak is '澶囨敞';
-
-select * from lf.sys_domain order by dom_name,orderid;
------------------------------------------------------------------------------------------------------ 26.鎶ュ憡妯℃澘琛�
--- drop table if exists lf.sys_report;
-create table if not exists lf.sys_report (
- id serial primary key,
- name varchar(100),
- type varchar(50),
- code varchar(50),
- fname varchar(256),
- guid varchar(38),
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone,
- bak varchar(1024)
-);
-comment on table lf.sys_report is '鎶ュ憡妯℃澘琛�';
-comment on column lf.sys_report.id is '涓婚敭ID';
-comment on column lf.sys_report.name is '鍚嶇О';
-comment on column lf.sys_report.type is '鏂囦欢绫诲瀷锛�1-Word妯℃澘锛�2-Excel妯℃澘';
-comment on column lf.sys_report.code is '缂栫爜';
-comment on column lf.sys_report.fname is '鏂囦欢鍚�';
-comment on column lf.sys_report.guid is '鏂囦欢GUID';
-comment on column lf.sys_report.create_user is '鍒涘缓浜篿d';
-comment on column lf.sys_report.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_report.update_user is '鏇存柊浜篿d';
-comment on column lf.sys_report.update_time is '鏇存柊鏃堕棿';
-comment on column lf.sys_report.bak is '澶囨敞';
--- insert into lf.sys_report (name,type,guid,code) values ('缁熻鐢ㄦ埛閲�',2,'1\aa.xlsx','');
-
-select * from lf.sys_report;
------------------------------------------------------------------------------------------------------ 27.涓嬭浇鏃ュ織琛�
--- drop table if exists lf.sys_downlog;
-create table lf.sys_downlog (
- id serial primary key,
- downid integer default 0,
- ip varchar(30),
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone
-);
-comment on table lf.sys_downlog is '涓嬭浇鏃ュ織琛�';
-comment on column lf.sys_downlog.id is '涓婚敭id';
-comment on column lf.sys_downlog.ip is 'IP鍦板潃';
-comment on column lf.sys_downlog.create_user is '鍒涘缓浜篿d';
-comment on column lf.sys_downlog.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_downlog.update_user is '鏇存柊浜篿d';
-comment on column lf.sys_downlog.update_time is '鏇存柊鏃堕棿';
--- insert into lf.sys_downlog(downid,ip,create_user) values (100,'192.168.20.39',1);
-
-select * from lf.sys_downlog;
------------------------------------------------------------------------------------------------------ 28.鏁版嵁鐢宠琛�
--- drop table if exists lf.sys_apply;
-create table lf.sys_apply (
- id serial primary key,
- userid integer default 0,
- depids varchar(500),
- depcode varchar(24),
- dircodes varchar(500),
- tabs varchar(12000),
- entities varchar(6000),
- wkt varchar(4000),
- gids varchar(5000),
- filters varchar(500),
- pwd varchar(200),
- status integer default 0,
- count integer default 0,
- descr varchar(100),
- guid varchar(40),
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone
-);
-comment on table lf.sys_apply is '鏁版嵁鐢宠琛�';
-comment on column lf.sys_apply.id is '涓婚敭ID';
-comment on column lf.sys_apply.userid is '鐢ㄦ埛ID';
-comment on column lf.sys_apply.depcode is '鍗曚綅缂栫爜';
-comment on column lf.sys_apply.dircodes is '鐩綍缂栫爜';
-comment on column lf.sys_apply.tabs is '琛ㄥ悕';
-comment on column lf.sys_apply.entities is '瀹炰綋鍚�';
-comment on column lf.sys_apply.wkt is 'WKT';
-comment on column lf.sys_apply.gids is 'GID';
-comment on column lf.sys_apply.filters is '杩囨护鏉′欢';
-comment on column lf.sys_apply.pwd is '瀵嗙爜';
-comment on column lf.sys_apply.status is '鐘舵�侊細-10-浣滃簾锛�-1-鎵撳洖锛�0~9-瀹℃牳涓紝10-閫氳繃';
-comment on column lf.sys_apply.count is '瀹℃牳鎬绘暟';
-comment on column lf.sys_apply.descr is '鎻忚堪';
-comment on column lf.sys_apply.guid is 'GUID';
-comment on column lf.sys_apply.create_user is '鍒涘缓浜篿d';
-comment on column lf.sys_apply.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_apply.update_user is '鏇存柊浜篿d';
-comment on column lf.sys_apply.update_time is '鏇存柊鏃堕棿';
-
-select * from lf.sys_apply order by id desc;
------------------------------------------------------------------------------------------------------ 29.鐢宠娴佺▼琛�
--- drop table if exists lf.sys_flow;
-create table lf.sys_flow (
- id serial primary key,
- applyid integer,
- depid integer,
- userid integer,
- status integer default 0,
- descr varchar(50),
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone
-);
-comment on table lf.sys_flow is '鐢宠娴佺▼琛�';
-comment on column lf.sys_flow.id is '涓婚敭id';
-comment on column lf.sys_flow.applyid is '鐢宠ID';
-comment on column lf.sys_flow.depid is '鍗曚綅ID';
-comment on column lf.sys_flow.userid is '鐢ㄦ埛ID';
-comment on column lf.sys_flow.status is '鐘舵�侊細-1-鎵撳洖锛�0-寰呭鏍革紝1-閫氳繃';
-comment on column lf.sys_flow.descr is '鎻忚堪';
-comment on column lf.sys_flow.create_user is '鍒涘缓浜篿d';
-comment on column lf.sys_flow.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_flow.update_user is '鏇存柊浜篿d';
-comment on column lf.sys_flow.update_time is '鏇存柊鏃堕棿';
-
-select * from lf.sys_flow;
------------------------------------------------------------------------------------------------------ 30.FME鏃ュ織琛�
-create table lf.sys_fme_log (
- id serial primary key,
- parentid varchar(38),
- dirpath varchar(1024),
- pg_ns varchar(10),
- tcmc varchar(150),
- tcdm varchar(50),
- count integer default 0,
- create_time timestamp(6) without time zone default now(),
- update_time timestamp(6) without time zone
-);
-comment on table lf.sys_fme_log is 'FME鏃ュ織琛�';
-comment on column lf.sys_fme_log.id is '涓婚敭ID';
-comment on column lf.sys_fme_log.parentid is '鐖惰〃GUID';
-comment on column lf.sys_fme_log.dirpath is '鏂囦欢璺緞';
-comment on column lf.sys_fme_log.pg_ns is '琛ㄦ灦鏋�';
-comment on column lf.sys_fme_log.tcmc is '鍥惧眰鍚嶇О';
-comment on column lf.sys_fme_log.tcdm is '琛ㄥ悕';
-comment on column lf.sys_fme_log.count is '琛屾暟';
-comment on column lf.sys_fme_log.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_fme_log.update_time is '鏇存柊鏃堕棿';
-
-select * from lf.sys_fme_log order by id desc; -- delete from lf.sys_fme_log;
------------------------------------------------------------------------------------------------------ 31.鍥惧眰琛�
--- DROP TABLE IF EXISTS lf.sys_layer;
-create table lf.sys_layer(
- id serial primary key,
- pid integer,
- pubid integer,
- cn_name varchar(50),
- en_name varchar(50),
- url varchar(512),
- test_url varchar(512),
- type smallint,
- icon varchar(256),
- level integer,
- order_num integer,
- is_show smallint,
- serve_type varchar(10),
- data_type varchar(20),
- create_user integer,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone,
- bak varchar(1024)
-);
-comment on table lf.sys_layer is '鍥惧眰琛�';
-comment on column lf.sys_layer.id is '涓婚敭ID';
-comment on column lf.sys_layer.pid is '鐖禝D锛�0-鏍硅妭鐐�';
-comment on column lf.sys_layer.pubid is '鏁版嵁鍙戝竷ID';
-comment on column lf.sys_layer.cn_name is '涓枃鍚嶇О';
-comment on column lf.sys_layer.en_name is '鑻辨枃鍚嶇О';
-comment on column lf.sys_layer.url is '鍥惧眰鍦板潃';
-comment on column lf.sys_layer.test_url is '娴嬭瘯鍦板潃';
-comment on column lf.sys_layer.type is '绫诲瀷锛�1-鍥惧眰缁�,2-鍥惧眰';
-comment on column lf.sys_layer.icon is '鍥炬爣';
-comment on column lf.sys_layer.level is '灞傜骇锛�0-鏍硅妭鐐�';
-comment on column lf.sys_layer.order_num is '鎺掑簭鍙�';
-comment on column lf.sys_layer.is_show is '鏄惁鏄剧ず';
-comment on column lf.sys_layer.serve_type is '鏈嶅姟绫诲瀷';
-comment on column lf.sys_layer.data_type is '鏁版嵁绫诲瀷';
-comment on column lf.sys_layer.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_layer.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_layer.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_layer.update_time is '鏇存柊鏃堕棿';
-comment on column lf.sys_layer.bak is '澶囨敞';
-
-select * from lf.sys_layer order by id;
------------------------------------------------------------------------------------------------------ 32.鏈嶅姟鏃ュ織琛�
--- DROP TABLE IF EXISTS lf.sys_serve_log;
-create table lf.sys_serve_log(
- id serial8,
- resid integer,
- name varchar(100),
- type varchar(50),
- url varchar(4096),
- create_user integer,
- create_time timestamp(6) without time zone default now(),
- bak varchar(1024)
-) partition by range(create_time);
-
-create table lf.sys_serve_log_2023 partition of lf.sys_serve_log for values from ('2023-01-01') to ('2024-01-01');
-create INDEX index_sys_serve_log_2023_optime on lf.sys_serve_log_2023 (create_time);
-create table lf.sys_serve_log_2024 partition of lf.sys_serve_log for values from ('2024-01-01') to ('2025-01-01');
-create INDEX index_sys_serve_log_2024_optime on lf.sys_serve_log_2024 (create_time);
-create table lf.sys_serve_log_2025 partition of lf.sys_serve_log for values from ('2025-01-01') to ('2026-01-01');
-create INDEX index_sys_serve_log_2025_optime on lf.sys_serve_log_2025 (create_time);
-create table lf.sys_serve_log_2026 partition of lf.sys_serve_log for values from ('2026-01-01') to ('2027-01-01');
-create INDEX index_sys_serve_log_2026_optime on lf.sys_serve_log_2026 (create_time);
-create table lf.sys_serve_log_2027 partition of lf.sys_serve_log for values from ('2027-01-01') to ('2028-01-01');
-create INDEX index_sys_serve_log_2027_optime on lf.sys_serve_log_2027 (create_time);
-
-comment on table lf.sys_serve_log is '鏈嶅姟鏃ュ織琛�';
-comment on column lf.sys_serve_log.id is '涓婚敭ID';
-comment on column lf.sys_serve_log.resid is '鏈嶅姟ID';
-comment on column lf.sys_serve_log.name is '鍚嶇О';
-comment on column lf.sys_serve_log.type is '绫诲瀷';
-comment on column lf.sys_serve_log.url is '鍥惧眰鍦板潃';
-comment on column lf.sys_serve_log.create_user is '璁块棶浜篒D';
-comment on column lf.sys_serve_log.create_time is '璁块棶鏃堕棿';
-comment on column lf.sys_serve_log.bak is '澶囨敞';
-/* insert into lf.sys_serve_log (resid,name,type,url) values (1,'鍏ㄥ浗褰卞儚鍥�','01','http://192.168.20.205/LFData/2d/tiles/img/{z}/{x}/{y}.png');
- insert into lf.sys_serve_log (resid,name,type,url) values (2,'鍏ㄥ浗鐭㈤噺鍥�','02','http://192.168.20.205/LFData/2d/tiles/vec/{z}/{x}/{y}.png'); */
-
-select * from lf.sys_serve_log;
------------------------------------------------------------------------------------------------------ 33.鍧愭爣绯昏〃
--- drop table if exists lf.sys_coord;
-create table if not exists lf.sys_coord(
- id serial primary key,
- epsgcode varchar(50),
- coordinate varchar(200),
- zoning varchar(50),
- remarks varchar(255)
-);
-comment on table lf.sys_coord is '鍧愭爣绯昏〃';
-comment on column lf.sys_coord.id is '涓婚敭ID';
-comment on column lf.sys_coord.epsgcode is 'EPSG缂栫爜';
-comment on column lf.sys_coord.coordinate is '鍧愭爣绯诲悕绉�';
-comment on column lf.sys_coord.zoning is '甯﹀彿';
-comment on column lf.sys_coord.remarks is '澶囨敞';
-
-select * from lf.sys_coord;
------------------------------------------------------------------------------------------------------ 34.鍏冩暟鎹�-涓嬭浇琛�
--- drop table if exists lf.sys_meta_down;
-create table lf.sys_meta_down(
- id serial primary key,
- metaid integer,
- downid integer,
- create_user integer default 1,
- create_time timestamp(6) without time zone default now()
-);
-comment on table lf.sys_meta_down is '鍏冩暟鎹�-涓嬭浇琛�';
-comment on column lf.sys_meta_down.id is '涓婚敭ID';
-comment on column lf.sys_meta_down.metaid is '鍏冩暟鎹甀D';
-comment on column lf.sys_meta_down.downid is '涓嬭浇ID';
-comment on column lf.sys_meta_down.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_meta_down.create_time is '鍒涘缓鏃堕棿';
-/* insert into lf.sys_meta_down (metaid,downid) values (1421,248);
-insert into lf.sys_meta_down (metaid,downid) values (1421,245);
-insert into lf.sys_meta_down (metaid,downid) values (1420,245);
-insert into lf.sys_meta_down (metaid,downid) values (1419,275);
-insert into lf.sys_meta_down (metaid,downid) values (1418,241);
-insert into lf.sys_meta_down (metaid,downid) values (1417,243); */
-
-select * from lf.sys_meta_down order by id desc;
-select id,name,type,url,descr,dcount,(select count(*) from lf.sys_downlog b where b.downid=a.id) "count",download_user,download_time from lf.sys_download a where type=3 order by dcount desc;
------------------------------------------------------------------------------------------------------ 35.妯″瀷琛�
--- drop table if exists lf.sys_model;
-create table lf.sys_model(
- id serial primary key,
- layerid integer,
- modelid varchar(200),
- guid varchar(40),
- name varchar(100),
- type varchar(50),
- info varchar(2048),
- url varchar(1024),
- icon varchar(256),
- create_user integer,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone,
- bak varchar(1024)
-);
-comment on table lf.sys_model is '妯″瀷琛�';
-comment on column lf.sys_model.id is '涓婚敭ID';
-comment on column lf.sys_model.layerid is '鍥惧眰ID';
-comment on column lf.sys_model.modelid is '妯″瀷ID';
-comment on column lf.sys_model.guid is 'GUID';
-comment on column lf.sys_model.name is '鍚嶇О';
-comment on column lf.sys_model.type is '绫诲瀷';
-comment on column lf.sys_model.info is '淇℃伅锛圝SON锛�';
-comment on column lf.sys_model.url is 'URL';
-comment on column lf.sys_model.icon is '鍥炬爣';
-comment on column lf.sys_model.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_model.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_model.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_model.update_time is '鏇存柊鏃堕棿';
-comment on column lf.sys_model.bak is '澶囨敞';
-
-select * from lf.sys_model order by id desc;
------------------------------------------------------------------------------------------------------ 36.鏁版嵁鍙戝竷琛�
--- drop table if exists lf.sys_publish;
-create table lf.sys_publish(
- id serial primary key,
- regid varchar(40),
- name varchar(50),
- url varchar(512),
- path varchar(200),
- type varchar(10),
- status integer default 0,
- dirid varchar(24),
- depid varchar(24),
- min integer default 0,
- max integer default 0,
- json varchar(2048),
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone,
- geom geometry(PointZ, 4490),
- bak varchar(1024)
-);
--- drop index if exists lf.idx_sys_publish_geom;
-create index idx_sys_publish_geom on lf.sys_publish using gist (geom);
-comment on table lf.sys_publish is '鏁版嵁鍙戝竷琛�';
-comment on column lf.sys_publish.id is '涓婚敭ID';
-comment on column lf.sys_publish.regid is '娉ㄥ唽ID';
-comment on column lf.sys_publish.name is '鍚嶇О';
-comment on column lf.sys_publish.url is '鍦板潃';
-comment on column lf.sys_publish.path is '璺緞';
-comment on column lf.sys_publish.type is '绫诲瀷锛歰bj锛宖bx锛宺vt锛宨fc锛�3dml锛宮pt锛孌OM';
-comment on column lf.sys_publish.status is '鐘舵�侊細-1-鍋滅敤锛�0-寰呭彂甯冿紝1-鍙戝竷涓紝2-鏇存柊鍙戝竷锛�3-宸插彂甯�';
-comment on column lf.sys_publish.dirid is '鐩綍ID';
-comment on column lf.sys_publish.depid is '鍗曚綅ID';
-comment on column lf.sys_publish.min is '鏈�灏忕骇鍒�';
-comment on column lf.sys_publish.max is '鏈�澶х骇鍒�';
-comment on column lf.sys_publish.json is 'JSON鏁版嵁';
-comment on column lf.sys_publish.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_publish.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_publish.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_publish.update_time is '鏇存柊鏃堕棿';
-comment on column lf.sys_publish.bak is '澶囨敞';
-comment on column lf.sys_publish.geom is '绌洪棿浣嶇疆';
--- alter table lf.sys_publish add column regid varchar(40);
--- insert into lf.sys_publish (name,geom) values ('Test',ST_GeomFromText('POINT Z (101.9281 36.58675 2199.5)'));
-
-select st_astext(geom) "wkt",a.* from lf.sys_publish a;
------------------------------------------------------------------------------------------------------ 37.鍏冩暟鎹�-鏁版嵁鍙戝竷琛�
--- drop table if exists lf.sys_meta_pub;
-create table lf.sys_meta_pub(
- id serial primary key,
- metaid integer,
- pubid integer,
- create_user integer default 1,
- create_time timestamp(6) without time zone default now(),
- update_user integer,
- update_time timestamp(6) without time zone
-);
-comment on table lf.sys_meta_pub is '鍏冩暟鎹�-鏁版嵁鍙戝竷琛�';
-comment on column lf.sys_meta_pub.id is '涓婚敭ID';
-comment on column lf.sys_meta_pub.metaid is '鍏冩暟鎹甀D';
-comment on column lf.sys_meta_pub.pubid is '鏁版嵁鍙戝竷ID';
-comment on column lf.sys_meta_pub.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_meta_pub.create_time is '鍒涘缓鏃堕棿';
-comment on column lf.sys_meta_pub.update_user is '鏇存柊浜篒D';
-comment on column lf.sys_meta_pub.update_time is '鏇存柊鏃堕棿';
--- insert into lf.sys_meta_pub (metaid,pubid) values (1399,1);
-
-select * from lf.sys_meta_pub;
------------------------------------------------------------------------------------------------------ 38.鏁版嵁鍙戝竷-涓嬭浇琛�
--- drop table if exists lf.sys_pub_down;
-create table lf.sys_pub_down(
- id serial primary key,
- pubid integer,
- downid integer,
- create_user integer default 1,
- create_time timestamp(6) without time zone default now()
-);
-comment on table lf.sys_pub_down is '鏁版嵁鍙戝竷-涓嬭浇琛�';
-comment on column lf.sys_pub_down.id is '涓婚敭ID';
-comment on column lf.sys_pub_down.pubid is '鏁版嵁鍙戝竷ID';
-comment on column lf.sys_pub_down.downid is '涓嬭浇ID';
-comment on column lf.sys_pub_down.create_user is '鍒涘缓浜篒D';
-comment on column lf.sys_pub_down.create_time is '鍒涘缓鏃堕棿';
-
-select * from lf.sys_pub_down;
------------------------------------------------------------------------------------------------------
diff --git a/data/dep.csv b/data/dep.csv
deleted file mode 100644
index 7b8ad16..0000000
--- a/data/dep.csv
+++ /dev/null
@@ -1,44 +0,0 @@
-1,0,中国石油天然气管道工程有限公司,,,,,,,,,,0,1,1,,,,
-2,1,职能业务部门,,,,,,,,,,1,2,1,,,,
-3,2,分公司办公室,,,,,,,,,,2,3,1,,,,
-4,2,人力资源部,,,,,,,,,,2,4,1,,,,
-5,2,企划运营部,,,,,,,,,,2,5,1,,,,
-6,2,财务资产部,,,,,,,,,,2,6,1,,,,
-7,2,技术质量安全部,,,,,,,,,,2,7,1,,,,
-8,2,技术质量安全部,,,,,,,,,,2,8,1,,,,
-9,2,党群工作部,,,,,,,,,,2,9,1,,,,
-10,1,共享支持部门,,,,,,,,,,1,10,1,,,,
-11,10,技术中心,,,,,,,,,,2,11,1,,,,
-12,10,信息档案中心,,,,,,,,,,2,12,1,,,,
-13,10,人才交流中心,,,,,,,,,,2,13,1,,,,
-14,10,招标中心,,,,,,,,,,2,14,1,,,,
-15,1,生产业务部门,,,,,,,,,,1,15,1,,,,
-16,15,市场开发部,,,,,,,,,,2,16,1,,,,
-17,15,项目管理部,,,,,,,,,,2,17,1,,,,
-18,15,工艺室,,,,,,,,,,2,18,1,,,,
-19,15,配管室,,,,,,,,,,2,19,1,,,,
-20,15,线路室,,,,,,,,,,2,20,1,,,,
-21,15,穿跨越室,,,,,,,,,,2,21,1,,,,
-22,15,土建室,,,,,,,,,,2,22,1,,,,
-23,15,机械室,,,,,,,,,,2,23,1,,,,
-24,15,仪表自动化室,,,,,,,,,,2,24,1,,,,
-25,15,电力室,,,,,,,,,,2,25,1,,,,
-26,15,通信室,,,,,,,,,,2,26,1,,,,
-27,15,技术经济室,,,,,,,,,,2,27,1,,,,
-28,15,市政工程室,,,,,,,,,,2,28,1,,,,
-29,15,海洋工程室,,,,,,,,,,2,29,1,,,,
-30,1,分公司事务部,,,,,,,,,,1,30,1,,,,
-31,30,国际项目中心,,,,,,,,,,2,31,1,,,,
-32,30,阿布扎比分公司,,,,,,,,,,2,32,1,,,,
-33,30,东北分公司,,,,,,,,,,2,33,1,,,,
-34,30,上海分公司,,,,,,,,,,2,34,1,,,,
-35,30,珠海分公司,,,,,,,,,,2,35,1,,,,
-36,30,新疆分公司,,,,,,,,,,2,36,1,,,,
-37,30,北京分公司,,,,,,,,,,2,37,1,,,,
-38,30,勘察事业部,,,,,,,,,,2,38,1,,,,
-39,30,东南亚设计咨询中心,,,,,,,,,,2,39,1,,,,
-40,30,西南设计咨询中心,,,,,,,,,,2,40,1,,,,
-41,1,服务保障部门,,,,,,,,,,1,41,1,,,,
-42,41,行政事务部,,,,,,,,,,2,42,1,,,,
-43,41,文印完成室,,,,,,,,,,2,43,1,,,,
-44,41,汽车队,,,,,,,,,,2,44,1,,,,
diff --git a/data/dep.xls b/data/dep.xls
deleted file mode 100644
index 34f4fa9..0000000
--- a/data/dep.xls
+++ /dev/null
Binary files differ
diff --git a/data/dict.csv b/data/dict.csv
deleted file mode 100644
index 63aff82..0000000
--- a/data/dict.csv
+++ /dev/null
@@ -1,235 +0,0 @@
-1,lf,sys_ver,附件表,id,主键ID,int,4,0,1,,,,,
-2,lf,sys_ver,附件表,dirid,目录ID,int,4,0,2,,,,,
-3,lf,sys_ver,附件表,name,名称,varchar,50,0,3,,,,,
-4,lf,sys_ver,附件表,descr,描述,varchar,256,0,4,,,,,
-5,lf,sys_ver,附件表,create_user,创建人ID,int,4,0,5,,,,,
-6,lf,sys_ver,附件表,create_time,创建时间,timestamp,6,0,6,,,,,
-7,lf,sys_ver,附件表,update_user,更新人ID,int,4,0,7,,,,,
-8,lf,sys_ver,附件表,update_time,更新时间,timestamp,6,0,8,,,,,
-9,lf,sys_user,用户表,id,主键ID,int,4,0,1,,,,,
-10,lf,sys_user,用户表,depid,单位ID,int,4,0,2,,,,,
-11,lf,sys_user,用户表,uid,用户ID,varchar,20,0,3,,,,,
-12,lf,sys_user,用户表,uname,用户名,varchar,50,0,4,,,,,
-13,lf,sys_user,用户表,pwd,MD5密码,varchar,200,0,5,,,,,
-14,lf,sys_user,用户表,salt,MD5盐,varchar,20,0,6,,,,,
-15,lf,sys_user,用户表,sex,性别,int,2,0,7,,,,,"0-女,1-男,-1-未知"
-16,lf,sys_user,用户表,native,籍贯,varchar,50,0,8,,,,,
-17,lf,sys_user,用户表,contact,联系方式,varchar,50,0,9,,,,,
-18,lf,sys_user,用户表,job,工作,varchar,50,0,10,,,,,
-19,lf,sys_user,用户表,email,电子邮件,varchar,50,0,11,,,,,
-20,lf,sys_user,用户表,addr,地址,varchar,300,0,12,,,,,
-21,lf,sys_user,用户表,edu,教育,varchar,20,0,13,,,,,
-22,lf,sys_user,用户表,idcard,证件号,varchar,20,0,14,,,,,
-23,lf,sys_user,用户表,status,状态,int,2,0,15,,,,,"0-正常,1-禁用,2-删除,3-申请,4-拒批"
-24,lf,sys_user,用户表,create_user,创建人ID,int,4,0,16,,,,,
-25,lf,sys_user,用户表,create_time,创建时间,timestamp,6,0,17,,,,,
-26,lf,sys_user,用户表,update_user,更新人ID,int,4,0,18,,,,,
-27,lf,sys_user,用户表,update_time,更新时间,timestamp,6,0,19,,,,,
-28,lf,sys_user,用户表,bak,备注,varchar,1024,0,20,,,,,
-29,lf,sys_token,令牌表,id,主键ID,int,8,0,1,,,,,
-30,lf,sys_token,令牌表,token,令牌,varchar,40,0,2,,,,,
-31,lf,sys_token,令牌表,duration,使用时间,int,4,0,3,,,,,"默认4小时,240分钟"
-32,lf,sys_token,令牌表,expire,失效时间,timestamp,6,0,4,,,,,
-33,lf,sys_token,令牌表,type,类型,int,2,0,5,,,,,0-临时，1-固定
-34,lf,sys_token,令牌表,ip,IP地址,varchar,30,0,6,,,,,
-35,lf,sys_token,令牌表,create_user,创建人ID,int,4,0,7,,,,,
-36,lf,sys_token,令牌表,create_time,创建时间,timestamp,6,0,8,,,,,
-37,lf,sys_token,令牌表,update_user,更新人ID,int,4,0,9,,,,,
-38,lf,sys_token,令牌表,update_time,更新时间,timestamp,6,0,10,,,,,
-39,lf,sys_style,样式表,id,主键ID,int,4,0,1,,,,,
-40,lf,sys_style,样式表,name,名称,varchar,50,0,2,,,,,
-41,lf,sys_style,样式表,type,类型,varchar,20,0,3,,,,,
-42,lf,sys_style,样式表,dirid,目录ID,int,4,0,4,,,,,
-43,lf,sys_style,样式表,depid,单位ID,int,4,0,5,,,,,
-44,lf,sys_style,样式表,ver,版本,varchar,20,0,6,,,,,
-45,lf,sys_style,样式表,status,状态,int,2,0,7,,,,,"0-停用,1-启用"
-46,lf,sys_style,样式表,precision,精度,varchar,50,0,8,,,,,如：1:1000
-47,lf,sys_style,样式表,descr,描述,varchar,256,0,9,,,,,
-48,lf,sys_style,样式表,file_guid,样式文件GUID,varchar,40,0,10,,,,,
-49,lf,sys_style,样式表,view_guid,预览文件GUID,varchar,40,0,11,,,,,
-50,lf,sys_style,样式表,create_user,创建人ID,int,4,0,12,,,,,
-51,lf,sys_style,样式表,create_time,创建时间,timestamp,6,0,13,,,,,
-52,lf,sys_style,样式表,update_user,更新人ID,int,4,0,14,,,,,
-53,lf,sys_style,样式表,update_time,更新时间,timestamp,6,0,15,,,,,
-54,lf,sys_style,样式表,bak,备注,varchar,1024,0,16,,,,,
-55,lf,sys_role_user,角色-用户表,id,主键ID,int,4,0,1,,,,,
-56,lf,sys_role_user,角色-用户表,roleid,角色ID,int,4,0,2,,,,,
-57,lf,sys_role_user,角色-用户表,userid,用户ID,int,4,0,3,,,,,
-58,lf,sys_role_user,角色-用户表,create_user,创建人ID,int,4,0,4,,,,,
-59,lf,sys_role_user,角色-用户表,create_time,创建时间,timestamp,6,0,5,,,,,
-60,lf,sys_role_user,角色-用户表,update_user,更新人ID,int,4,0,6,,,,,
-61,lf,sys_role_user,角色-用户表,update_time,更新时间,timestamp,6,0,7,,,,,
-62,lf,sys_role_res,角色-资源表,id,主键ID,int,4,0,1,,,,,
-63,lf,sys_role_res,角色-资源表,roleid,角色ID,int,4,0,2,,,,,
-64,lf,sys_role_res,角色-资源表,resid,资源ID,int,4,0,3,,,,,
-65,lf,sys_role_res,角色-资源表,create_user,创建人ID,int,4,0,4,,,,,
-66,lf,sys_role_res,角色-资源表,create_time,创建时间,timestamp,6,0,5,,,,,
-67,lf,sys_role_res,角色-资源表,update_user,更新人ID,int,4,0,6,,,,,
-68,lf,sys_role_res,角色-资源表,update_time,更新时间,timestamp,6,0,7,,,,,
-69,lf,sys_role_menu,角色-菜单表,id,主键ID,int,4,0,1,,,,,
-70,lf,sys_role_menu,角色-菜单表,roleid,角色ID,int,4,0,2,,,,,
-71,lf,sys_role_menu,角色-菜单表,menuid,菜单ID,int,4,0,3,,,,,
-72,lf,sys_role_menu,角色-菜单表,create_user,创建人ID,int,4,0,4,,,,,
-73,lf,sys_role_menu,角色-菜单表,create_time,创建时间,timestamp,6,0,5,,,,,
-74,lf,sys_role_menu,角色-菜单表,update_user,更新人ID,int,4,0,6,,,,,
-75,lf,sys_role_menu,角色-菜单表,update_time,更新时间,timestamp,6,0,7,,,,,
-76,lf,sys_role,角色表,id,主键ID,int,4,0,1,,,,,
-77,lf,sys_role,角色表,depid,单位ID,int,4,0,2,,,,,
-78,lf,sys_role,角色表,name,名称,varchar,100,0,3,,,,,
-79,lf,sys_role,角色表,descr,描述,varchar,100,0,4,,,,,
-80,lf,sys_role,角色表,create_user,创建人ID,int,4,0,5,,,,,
-81,lf,sys_role,角色表,create_time,创建时间,timestamp,6,0,6,,,,,
-82,lf,sys_role,角色表,update_user,更新人ID,int,4,0,7,,,,,
-83,lf,sys_role,角色表,update_time,更新时间,timestamp,6,0,8,,,,,
-84,lf,sys_role,角色表,bak,备注,varchar,1024,0,9,,,,,
-85,lf,sys_res_op,资源操作表,id,主键ID,int,8,0,1,,,,,
-86,lf,sys_res_op,资源操作表,resid,资源ID,int,4,0,2,,,,,
-87,lf,sys_res_op,资源操作表,type,操作类型,int,2,0,3,,,,,"1-查看,2-新增,3-修改,4-删除,5-上传,6-下载"
-88,lf,sys_res_op,资源操作表,ip,IP地址,varchar,30,0,4,,,,,
-89,lf,sys_res_op,资源操作表,userid,操作人ID,int,4,0,5,,,,,
-90,lf,sys_res_op,资源操作表,optime,创建时间,timestamp,6,0,6,,,,,
-91,lf,sys_res,资源表,id,主键ID,int,4,0,1,,,,,
-92,lf,sys_res,资源表,name,名称,varchar,50,0,2,,,,,
-93,lf,sys_res,资源表,server,服务地址,varchar,300,0,3,,,,,
-94,lf,sys_res,资源表,source,原始地址,varchar,300,0,4,,,,,
-95,lf,sys_res,资源表,depid,单位ID,int,4,0,5,,,,,
-96,lf,sys_res,资源表,dirid,目录ID,int,4,0,6,,,,,
-97,lf,sys_res,资源表,code,编码,varchar,30,0,7,,,,,
-98,lf,sys_res,资源表,descr,描述,varchar,200,0,8,,,,,
-99,lf,sys_res,资源表,img,预览图,varchar,40,0,9,,,,,
-100,lf,sys_res,资源表,create_user,创建人ID,int,4,0,10,,,,,
-101,lf,sys_res,资源表,create_time,创建时间,timestamp,6,0,11,,,,,
-102,lf,sys_res,资源表,update_user,更新人ID,int,4,0,12,,,,,
-103,lf,sys_res,资源表,update_time,更新时间,timestamp,6,0,13,,,,,
-104,lf,sys_res,资源表,bak,备注,varchar,1024,0,14,,,,,
-105,lf,sys_operate,操作日志表,id,主键ID,int,8,0,1,,,,,
-106,lf,sys_operate,操作日志表,modular,操作模块,varchar,60,0,2,,,,,如:数据管理\目录管理
-107,lf,sys_operate,操作日志表,url,操作网址,varchar,256,0,3,,,,,
-108,lf,sys_operate,操作日志表,ip,IP地址,varchar,30,0,4,,,,,
-109,lf,sys_operate,操作日志表,type,类别,int,2,0,5,,,,,"1-查看,2-新增,3-修改,4-删除,5-上传,6-下载"
-110,lf,sys_operate,操作日志表,userid,操作人ID,int,4,0,6,,,,,
-111,lf,sys_operate,操作日志表,optime,操作时间,timestamp,6,0,7,,,,,
-112,lf,sys_meta_file,元数据文件表,id,主键ID,int,4,0,1,,,,,
-113,lf,sys_meta_file,元数据文件表,metaid,元数据ID,int,4,0,2,,,,,
-114,lf,sys_meta_file,元数据文件表,name,文件名,varchar,256,0,3,,,,,
-115,lf,sys_meta_file,元数据文件表,guid,GUID,varchar,40,0,4,,,,,
-116,lf,sys_meta_file,元数据文件表,path,存储路径,varchar,512,0,5,,,,,
-117,lf,sys_meta_file,元数据文件表,fileid,父文件ID,int,4,0,6,,,,,0-没有
-118,lf,sys_meta_file,元数据文件表,create_user,创建人ID,int,4,0,7,,,,,
-119,lf,sys_meta_file,元数据文件表,create_time,创建时间,timestamp,6,0,8,,,,,
-120,lf,sys_meta_file,元数据文件表,update_user,更新人ID,int,4,0,9,,,,,
-121,lf,sys_meta_file,元数据文件表,update_time,更新时间,timestamp,6,0,10,,,,,
-122,lf,sys_meta,元数据表,id,主键ID,int,4,0,1,,,,,
-123,lf,sys_meta,元数据表,dirid,目录ID,int,4,0,2,,,,,
-124,lf,sys_meta,元数据表,depid,单位ID,int,4,0,3,,,,,
-125,lf,sys_meta,元数据表,name,名称,varchar,256,0,4,,,,,
-126,lf,sys_meta,元数据表,descr,描述,varchar,256,0,5,,,,,
-127,lf,sys_meta,元数据表,guid,GUID,varchar,40,0,6,,,,,
-128,lf,sys_meta,元数据表,create_user,创建人ID,int,4,0,7,,,,,
-129,lf,sys_meta,元数据表,create_time,创建时间,timestamp,6,0,8,,,,,
-130,lf,sys_meta,元数据表,update_user,更新人ID,int,4,0,9,,,,,
-131,lf,sys_meta,元数据表,update_time,更新时间,timestamp,6,0,10,,,,,
-132,lf,sys_meta,元数据表,geo,空间,geometry,,0,11,,,,,
-133,lf,sys_menu_auth,菜单-权限表,id,主键ID,int,4,0,1,,,,,
-134,lf,sys_menu_auth,菜单-权限表,menuid,菜单ID,int,4,0,2,,,,,
-135,lf,sys_menu_auth,菜单-权限表,authid,权限ID,int,4,0,3,,,,,
-136,lf,sys_menu_auth,菜单-权限表,create_user,创建人ID,int,4,0,4,,,,,
-137,lf,sys_menu_auth,菜单-权限表,create_time,创建时间,timestamp,6,0,5,,,,,
-138,lf,sys_menu_auth,菜单-权限表,update_user,更新人ID,int,4,0,6,,,,,
-139,lf,sys_menu_auth,菜单-权限表,update_time,更新时间,timestamp,6,0,7,,,,,
-140,lf,sys_menu,菜单表,id,主键ID,int,4,0,1,,,,,
-141,lf,sys_menu,菜单表,pid,"父ID,0-根节点",int,4,0,2,,,,,
-142,lf,sys_menu,菜单表,cn_name,中文名称,varchar,50,0,3,,,,,
-143,lf,sys_menu,菜单表,en_name,英文名称,varchar,50,0,4,,,,,
-144,lf,sys_menu,菜单表,url,菜单Url,varchar,256,0,5,,,,,
-145,lf,sys_menu,菜单表,perms,授权,varchar,512,0,6,,,,,"多个用逗号分隔，如：user:list,user:create"
-146,lf,sys_menu,菜单表,type,类型,int,2,0,7,,,,,"0-目录,1-菜单,2-按钮"
-147,lf,sys_menu,菜单表,icon,图标,varchar,256,0,8,,,,,
-148,lf,sys_menu,菜单表,level,层级,int,4,0,9,,,,,0-根节点
-149,lf,sys_menu,菜单表,order_num,排序号,int,4,0,10,,,,,
-150,lf,sys_menu,菜单表,is_show,是否显示,int,2,0,11,,,,,
-151,lf,sys_menu,菜单表,create_user,创建人ID,int,4,0,12,,,,,
-152,lf,sys_menu,菜单表,create_time,创建时间,timestamp,6,0,13,,,,,
-153,lf,sys_menu,菜单表,update_user,更新人ID,int,4,0,14,,,,,
-154,lf,sys_menu,菜单表,update_time,更新时间,timestamp,6,0,15,,,,,
-155,lf,sys_menu,菜单表,bak,备注,varchar,1024,0,16,,,,,
-156,lf,sys_login,登录日志表,id,主键ID,int,8,0,1,,,,,
-157,lf,sys_login,登录日志表,appid,应用程序ID,int,4,0,2,,,,,"1-管道基础大数据平台,2-外部系统集成"
-158,lf,sys_login,登录日志表,ip,IP地址,varchar,30,0,3,,,,,
-159,lf,sys_login,登录日志表,type,类别,int,2,0,4,,,,,"1-登录,2-校验,3-登出"
-160,lf,sys_login,登录日志表,status,状态,int,2,0,5,,,,,"1-成功,0-失败"
-161,lf,sys_login,登录日志表,browser,浏览器,varchar,100,0,6,,,,,
-162,lf,sys_login,登录日志表,userid,登录人ID,int,4,0,7,,,,,
-163,lf,sys_login,登录日志表,optime,登录时间,timestamp,6,0,8,,,,,
-164,lf,sys_dir,目录表,id,主键ID,int,4,0,1,,,,,
-165,lf,sys_dir,目录表,pid,父ID,int,4,0,2,,,,,0-根节点
-166,lf,sys_dir,目录表,name,名称,varchar,150,0,3,,,,,
-167,lf,sys_dir,目录表,code,目录编码,varchar,50,0,4,,,,,
-168,lf,sys_dir,目录表,descr,目录说明,varchar,1024,0,5,,,,,
-169,lf,sys_dir,目录表,level,层级,int,4,0,6,,,,,0-根节点
-170,lf,sys_dir,目录表,order_num,排序号,int,4,0,7,,,,,
-171,lf,sys_dir,目录表,create_user,创建人ID,int,4,0,8,,,,,
-172,lf,sys_dir,目录表,create_time,创建时间,timestamp,6,0,9,,,,,
-173,lf,sys_dir,目录表,update_user,更新人ID,int,4,0,10,,,,,
-174,lf,sys_dir,目录表,update_time,更新时间,timestamp,6,0,11,,,,,
-175,lf,sys_dir,目录表,bak,备注,varchar,1024,0,12,,,,,
-176,lf,sys_dict,附件表,id,主键ID,int,4,0,1,,,,,
-177,lf,sys_dict,附件表,ns,表空间,varchar,20,0,2,,,,,
-178,lf,sys_dict,附件表,tab,表名,varchar,100,0,3,,,,,
-179,lf,sys_dict,附件表,tab_desc,表名描述,varchar,100,0,4,,,,,
-180,lf,sys_dict,附件表,field,字段名,varchar,100,0,5,,,,,
-181,lf,sys_dict,附件表,alias,别名,varchar,100,0,6,,,,,
-182,lf,sys_dict,附件表,type,类型,varchar,100,0,7,,,,,
-183,lf,sys_dict,附件表,len,长度,int,4,0,8,,,,,
-184,lf,sys_dict,附件表,precision,精度,int,2,0,9,,,,,
-185,lf,sys_dict,附件表,order_num,序号,int,2,0,10,,,,,
-186,lf,sys_dict,附件表,create_user,创建人ID,int,4,0,11,,,,,
-187,lf,sys_dict,附件表,create_time,创建时间,timestamp,6,0,12,,,,,
-188,lf,sys_dict,附件表,update_user,更新人ID,int,4,0,13,,,,,
-189,lf,sys_dict,附件表,update_time,更新时间,timestamp,6,0,14,,,,,
-190,lf,sys_dict,附件表,bak,备注,varchar,1024,0,15,,,,,
-191,lf,sys_dep,单位表,id,主键ID,int,4,0,1,,,,,
-192,lf,sys_dep,单位表,pid,父ID,int,4,0,2,,,,,0-根节点
-193,lf,sys_dep,单位表,name,名称,varchar,150,0,3,,,,,
-194,lf,sys_dep,单位表,sname,简称,varchar,50,0,4,,,,,
-195,lf,sys_dep,单位表,code,编码,varchar,50,0,5,,,,,
-196,lf,sys_dep,单位表,uncode,机构代码,varchar,50,0,6,,,,,
-197,lf,sys_dep,单位表,addr,地址,varchar,300,0,7,,,,,
-198,lf,sys_dep,单位表,contact,联系方式,varchar,50,0,8,,,,,
-199,lf,sys_dep,单位表,fax,传真,varchar,50,0,9,,,,,
-200,lf,sys_dep,单位表,email,电子邮件,varchar,50,0,10,,,,,
-201,lf,sys_dep,单位表,post,邮政编码,varchar,20,0,11,,,,,
-202,lf,sys_dep,单位表,website,网站地址,varchar,1024,0,12,,,,,
-203,lf,sys_dep,单位表,level,层级,int,4,0,13,,,,,0-根节点
-204,lf,sys_dep,单位表,order_num,排序号,int,4,0,14,,,,,
-205,lf,sys_dep,单位表,create_user,创建人ID,int,4,0,15,,,,,
-206,lf,sys_dep,单位表,create_time,创建时间,timestamp,6,0,16,,,,,
-207,lf,sys_dep,单位表,update_user,更新人ID,int,4,0,17,,,,,
-208,lf,sys_dep,单位表,update_time,更新时间,timestamp,6,0,18,,,,,
-209,lf,sys_dep,单位表,bak,备注,varchar,1024,0,19,,,,,
-210,lf,sys_auth,权限表,id,主键ID,int,4,0,1,,,,,
-211,lf,sys_auth,权限表,name,名称,varchar,100,0,2,,,,,
-212,lf,sys_auth,权限表,tag,标识,varchar,100,0,3,,,,,
-213,lf,sys_auth,权限表,create_user,创建人ID,int,4,0,4,,,,,
-214,lf,sys_auth,权限表,create_time,创建时间,timestamp,6,0,5,,,,,
-215,lf,sys_auth,权限表,update_user,更新人ID,int,4,0,6,,,,,
-216,lf,sys_auth,权限表,update_time,更新时间,timestamp,6,0,7,,,,,
-217,lf,sys_auth,权限表,bak,备注,varchar,1024,0,8,,,,,
-218,lf,sys_attach,附件表,id,主键ID,int,4,0,1,,,,,
-219,lf,sys_attach,附件表,name,文件名,varchar,256,0,2,,,,,
-220,lf,sys_attach,附件表,tab,表名,varchar,100,0,3,,,,,如：public.data_dir
-221,lf,sys_attach,附件表,guid,GUID,varchar,40,0,4,,,,,
-222,lf,sys_attach,附件表,path,存储路径,varchar,512,0,5,,,,,
-223,lf,sys_attach,附件表,create_user,创建人ID,int,4,0,6,,,,,
-224,lf,sys_attach,附件表,create_time,创建时间,timestamp,6,0,7,,,,,
-225,lf,sys_attach,附件表,update_user,更新人ID,int,4,0,8,,,,,
-226,lf,sys_attach,附件表,update_time,更新时间,timestamp,6,0,9,,,,,
-227,lf,sys_args,参数设置表,id,主键ID,int,4,0,1,,,,,
-228,lf,sys_args,参数设置表,name,名称,varchar,50,0,2,,,,,
-229,lf,sys_args,参数设置表,cvalue,当前值,int,4,0,3,,,,,
-230,lf,sys_args,参数设置表,dvalue,默认值,int,4,0,4,,,,,
-231,lf,sys_args,参数设置表,descr,描述,varchar,256,0,5,,,,,
-232,lf,sys_args,参数设置表,create_user,创建人ID,int,4,0,6,,,,,
-233,lf,sys_args,参数设置表,create_time,创建时间,timestamp,6,0,7,,,,,
-234,lf,sys_args,参数设置表,update_user,更新人ID,int,4,0,8,,,,,
-235,lf,sys_args,参数设置表,update_time,更新时间,timestamp,6,0,9,,,,,
diff --git a/data/dict.xls b/data/dict.xls
deleted file mode 100644
index 13107ab..0000000
--- a/data/dict.xls
+++ /dev/null
Binary files differ
diff --git a/data/dir.csv b/data/dir.csv
deleted file mode 100644
index 20b04a9..0000000
--- a/data/dir.csv
+++ /dev/null
@@ -1,123 +0,0 @@
-1,0,国家基础地理数据,,,1,1,1,,,,
-2,1,DOM,,,2,2,1,,,,
-3,2,2m,,,3,3,1,,,,
-4,2,0.5m,,,3,4,1,,,,
-5,1,DEM,,,2,5,1,,,,
-6,5,30m,,,3,6,1,,,,
-7,5,90m,,,3,7,1,,,,
-8,1,DLG,,,2,8,1,,,,
-9,8,1:100万,,,3,9,1,,,,
-10,0,中俄东线管道工程南段,,,1,10,1,,,,
-11,10,勘察数据,,,2,11,1,,,,
-12,11,基础地质,,,3,12,1,,,,
-13,12,钻孔数据,,,4,13,1,,,,
-14,12,气象资料,,,4,14,1,,,,
-15,12,项目评估报告,,,4,15,1,,,,
-16,12,海洋气象,,,4,16,1,,,,
-17,11,勘察工程数据,,,3,17,1,,,,
-18,17,勘察报告说明书,,,4,18,1,,,,
-19,17,试验成果表,,,4,19,1,,,,
-20,17,地质剖面图,,,4,20,1,,,,
-21,17,现场采样照片,,,4,21,1,,,,
-22,17,踏勘资料,,,4,22,1,,,,
-23,17,数字化成果,,,4,23,1,,,,
-24,17,数据库,,,4,24,1,,,,
-25,17,三维地质模型,,,4,25,1,,,,
-26,10,岩土数据,,,2,26,1,,,,
-27,26,基础岩土资料,,,4,27,1,,,,
-28,27,地灾分布资料,,,5,28,1,,,,
-29,26,InSAR,,,4,29,1,,,,
-30,29,哨兵 Sentinel-1,,,5,30,1,,,,
-31,26,岩土工程数据,,,4,31,1,,,,
-32,31,高陡边坡设计数据,,,5,32,1,,,,
-33,31,地质灾害治理设计数据,,,5,33,1,,,,
-34,31,地质灾害勘察数据,,,5,34,1,,,,
-35,31,地灾监测数据,,,5,35,1,,,,
-36,10,测量数据,,,2,36,1,,,,
-37,36,基础地理数据,,,3,37,1,,,,
-38,37,DOM,,,4,38,1,,,,
-39,38,0.5m,,,5,39,1,,,,
-40,38,2m,,,5,40,1,,,,
-41,37,DEM,,,4,41,1,,,,
-42,41,30m,,,5,42,1,,,,
-43,41,90m,,,5,43,1,,,,
-44,37,DLG,,,4,44,1,,,,
-45,44,1:1万,,,5,45,1,,,,
-46,44,1:5万,,,5,46,1,,,,
-47,44,1:25万（有偏）,,,5,47,1,,,,
-48,44,1:100 万,,,5,48,1,,,,
-49,44,OSM 数据,,,5,49,1,,,,
-50,37,DRG,,,4,50,1,,,,
-51,50,1:1万,,,5,51,1,,,,
-52,50,1:5万,,,5,52,1,,,,
-53,50,1:10万,,,5,53,1,,,,
-54,50,1:25万,,,5,54,1,,,,
-55,37,POI,,,4,55,1,,,,
-56,55,特定场所Ⅰ,,,5,56,1,,,,
-57,55,特定场所Ⅱ,,,5,57,1,,,,
-58,55,易燃易爆场所,,,5,58,1,,,,
-59,36,测量工程数据,,,3,59,1,,,,
-60,59,DOM,,,4,60,1,,,,
-61,60,0.2m,,,5,61,1,,,,
-62,60,1m,,,5,62,1,,,,
-63,59,DEM,,,4,63,1,,,,
-64,63,2m,,,5,64,1,,,,
-65,63,12.5m,,,5,65,1,,,,
-66,59,DLG,,,4,66,1,,,,
-67,66,0.388888889,,,5,67,1,,,,
-68,66,0.736111111,,,5,68,1,,,,
-69,66,1.430555556,,,5,69,1,,,,
-70,59,数据表,,,4,70,1,,,,
-71,70,中线成果,,,5,71,1,,,,
-72,70,地面线,,,5,72,1,,,,
-73,70,地形图索引表,,,5,73,1,,,,
-74,70,离散点,,,5,74,1,,,,
-75,70,行政区划表,,,5,75,1,,,,
-76,70,基准控制点,,,5,76,1,,,,
-77,59,技术报告,,,4,77,1,,,,
-78,77,测量纲要,,,5,78,1,,,,
-79,77,技术设计方案,,,5,79,1,,,,
-80,77,总结报告,,,5,80,1,,,,
-81,59,图片,,,4,81,1,,,,
-82,81,航测相片,,,5,82,1,,,,
-83,81,三桩一牌,,,5,83,1,,,,
-84,81,水工保护,,,5,84,1,,,,
-85,81,设备铭牌,,,5,85,1,,,,
-86,81,点之记,,,5,86,1,,,,
-87,59,模型数据,,,4,87,1,,,,
-88,87,三维场景,,,5,88,1,,,,
-89,87,倾斜模型,,,5,89,1,,,,
-90,87,三维地形,,,5,90,1,,,,
-91,87,BIM 模型,,,5,91,1,,,,
-92,87,点云模型,,,5,92,1,,,,
-93,36,符号库,,,3,93,1,,,,
-94,93,三维符号,,,4,94,1,,,,
-95,93,二维符号,,,4,95,1,,,,
-96,95,CAD 块符号,,,5,96,1,,,,
-97,95,矢量出图符号,,,5,97,1,,,,
-98,10,合规数据,,,2,98,1,,,,
-99,98,法律,,,3,99,1,,,,
-100,98,法规,,,3,100,1,,,,
-101,98,国际标准,,,3,101,1,,,,
-102,98,国家标准,,,3,102,1,,,,
-103,102,测绘,,,4,103,1,,,,
-104,102,勘察,,,4,104,1,,,,
-105,102,岩土,,,4,105,1,,,,
-106,102,计算机,,,4,106,1,,,,
-107,98,行业标准,,,3,107,1,,,,
-108,107,测绘,,,4,108,1,,,,
-109,107,勘察,,,4,109,1,,,,
-110,107,岩土,,,4,110,1,,,,
-111,107,计算机,,,4,111,1,,,,
-112,98,企业标准,,,3,112,1,,,,
-113,112,中国石油天然气集团,,,4,113,1,,,,
-114,112,国家管网集团,,,4,114,1,,,,
-115,112,管道局,,,4,115,1,,,,
-116,112,管道设计院,,,4,116,1,,,,
-117,98,外部参考资料,,,3,117,1,,,,
-118,117,会议资料,,,4,118,1,,,,
-119,117,工程经验与案例,,,4,119,1,,,,
-120,10,管理数据,,,2,120,1,,,,
-121,120,视频,,,3,121,1,,,,
-122,120,照片,,,3,122,1,,,,
-123,120,周报,,,3,123,1,,,,
diff --git a/data/dir.xls b/data/dir.xls
deleted file mode 100644
index f86ed2a..0000000
--- a/data/dir.xls
+++ /dev/null
Binary files differ
diff --git a/data/lf_127.fly b/data/lf_127.fly
deleted file mode 100644
index b40ddb8..0000000
--- a/data/lf_127.fly
+++ /dev/null
Binary files differ
diff --git a/data/lf_web.fly b/data/lf_web.fly
deleted file mode 100644
index 2424bb1..0000000
--- a/data/lf_web.fly
+++ /dev/null
Binary files differ
diff --git a/data/lrc/OSM.lrc b/data/lrc/OSM.lrc
deleted file mode 100644
index bb60cde..0000000
--- a/data/lrc/OSM.lrc
+++ /dev/null
@@ -1,22 +0,0 @@
-<?xml version="1.0" encoding="GB18030"?>
-<DataDefine>
-<Version>0</Version>
-<Name>OSM.img</Name>
-<GeoGridType>WebMercatorWGS84</GeoGridType>
-<SampleSize>256</SampleSize>
-<FileExt>png</FileExt>
-<DataVersion></DataVersion>
-<DataType>urlformat</DataType>
-<TileRowDir>NorthToSouth</TileRowDir>
-<LocalPath></LocalPath>
-<UrlParamOrder>Z,X,Y</UrlParamOrder>
-<NetPath>http://s0.outdooractive.com/osm/OSMSummer/%d/%d/%d.png</NetPath>
-<Range>
-<West>-160</West>
-<East>160</East>
-<South>-85</South>
-<North>85</North>
-<LevelBegin>1</LevelBegin>
-<LevelEnd>18</LevelEnd>
-</Range>
-</DataDefine>
diff --git a/data/lrc/TMS_DOM_0.2m.lrc b/data/lrc/TMS_DOM_0.2m.lrc
deleted file mode 100644
index 3047a09..0000000
--- a/data/lrc/TMS_DOM_0.2m.lrc
+++ /dev/null
@@ -1,22 +0,0 @@
-<?xml version="1.0" encoding="GB18030"?>
-<DataDefine>
-<Version>0</Version>
-<Name>Test_0.2m.img</Name>
-<GeoGridType>WebMercatorWGS84</GeoGridType>
-<SampleSize>256</SampleSize>
-<FileExt>png</FileExt>
-<DataVersion></DataVersion>
-<DataType>urlformat</DataType>
-<TileRowDir>NorthToSouth</TileRowDir>
-<LocalPath></LocalPath>
-<UrlParamOrder>Z,X,Y</UrlParamOrder>
-<NetPath>http://103.85.165.99:8050/LFData/2d/tiles/01/%d/%d/%d.png</NetPath>
-<Range>
-<West>112.937954</West>
-<East>112.975545</East>
-<South>32.14428</South>
-<North>32.174636</North>
-<LevelBegin>4</LevelBegin>
-<LevelEnd>18</LevelEnd>
-</Range>
-</DataDefine>
diff --git a/data/lrc/TMS_DOM_0.2m_127.lrc b/data/lrc/TMS_DOM_0.2m_127.lrc
deleted file mode 100644
index 6759d1e..0000000
--- a/data/lrc/TMS_DOM_0.2m_127.lrc
+++ /dev/null
@@ -1,22 +0,0 @@
-<?xml version="1.0" encoding="GB18030"?>
-<DataDefine>
-<Version>0</Version>
-<Name>Test_0.2m.img</Name>
-<GeoGridType>WebMercatorWGS84</GeoGridType>
-<SampleSize>256</SampleSize>
-<FileExt>png</FileExt>
-<DataVersion></DataVersion>
-<DataType>urlformat</DataType>
-<TileRowDir>NorthToSouth</TileRowDir>
-<LocalPath></LocalPath>
-<UrlParamOrder>Z,X,Y</UrlParamOrder>
-<NetPath>http://192.168.20.205/LFData/2d/tiles/01/%d/%d/%d.png</NetPath>
-<Range>
-<West>112.937954</West>
-<East>112.975545</East>
-<South>32.14428</South>
-<North>32.174636</North>
-<LevelBegin>4</LevelBegin>
-<LevelEnd>18</LevelEnd>
-</Range>
-</DataDefine>
diff --git "a/data/lrc/\347\244\272\344\276\213.png" "b/data/lrc/\347\244\272\344\276\213.png"
deleted file mode 100644
index f19b71f..0000000
--- "a/data/lrc/\347\244\272\344\276\213.png"
+++ /dev/null
Binary files differ
diff --git "a/data/menu-\346\211\247\350\241\214.xls" "b/data/menu-\346\211\247\350\241\214.xls"
deleted file mode 100644
index 4959830..0000000
--- "a/data/menu-\346\211\247\350\241\214.xls"
+++ /dev/null
Binary files differ
diff --git a/data/menu.csv b/data/menu.csv
deleted file mode 100644
index 0ce7b52..0000000
--- a/data/menu.csv
+++ /dev/null
@@ -1,43 +0,0 @@
-1,0,管道基础大数据平台,Pipeline Basic Big Data Platform,,/perms,0,,0,1,0,1,,,,
-2,1,综合展示,Comprehensive Display,,,1,,1,2,1,1,,,,
-3,2,综合展示,Comprehensive Display,Synthesis,/comprehensive,1,,2,3,1,1,,,,
-4,3,图层,Coverage,,,2,,3,4,1,1,,,,
-5,3,视图,Viewport,,,2,,3,5,1,1,,,,
-6,3,漫游,Wander,,,2,,3,6,1,1,,,,
-7,3,分析,Analyse,,,2,,3,7,1,1,,,,
-8,3,查询,IQuery,,,2,,3,8,1,1,,,,
-9,3,定位,Orientation,,,2,,3,9,1,1,,,,
-10,3,测量,Measurement,,,2,,3,10,1,1,,,,
-11,3,标绘,Plotting,,,2,,3,11,1,1,,,,
-12,2,专题图,Thematic Maps,Thematic,/thematic,1,,2,12,1,1,,,,
-13,2,资料馆,Data Library,Archive,/dataLib,1,,2,13,1,1,,,,
-14,1,数据管理,Data Manage,,,1,,1,14,1,1,,,,
-15,14,数据上传,Data Updata,dataUpdata,/uploader,1,,2,15,1,1,,,,
-16,14,目录管理,Catalogue Management,catalogueManage,/dir,1,,2,16,1,1,,,,
-17,14,数据检索,Data Query,SpatialData,/dataQuery,1,,2,17,1,1,,,,
-18,14,版本管理,Version Management,versionManage,/ver,1,,2,18,1,1,,,,
-19,14,字典管理,Dictionary Management,dictionaryManage,/dict,1,,2,19,1,1,,,,
-20,14,元数据管理,Metadata Management,metadataManage,/meta,1,,2,20,1,1,,,,
-21,14,样式管理,Style Management,styleManage,/style,1,,2,21,1,1,,,,
-22,1,运维管理,Operat Management,,,1,,1,22,1,1,,,,
-23,22,系统管理,System Management,,,1,,2,23,1,1,,,,
-24,23,菜单管理,Menu Management,menuSettings,/menu,1,,3,24,1,1,,,,
-25,23,用户管理,User Management,userInfoManage,/user,1,,3,25,1,1,,,,
-26,23,单位管理,Organization Management,orgManage,/dep,1,,3,26,1,1,,,,
-27,23,资源管理,Resource Management,resourceManage,/res,1,,3,27,0,1,,,,
-28,23,角色管理,Role Management,roleManage,/role,1,,3,28,1,1,,,,
-29,23,权限管理,Permission Management,authorityManage,/auth,1,,3,29,1,1,,,,
-30,22,授权管理,Authorization Management,,,1,,2,30,1,1,,,,
-31,30,用户角色授权,User Role Authorization,userRoleAuthorization,/roleUser,1,,3,31,1,1,,,,
-32,30,菜单权限授权,Menu Permission Authorization,menuRoleAuthorization,/menuAuth,1,,3,32,1,1,,,,
-33,30,角色菜单授权,Role Menu Authorization,roleMenuAuthorization,/roleMenuAuth,1,,3,33,1,1,,,,
-34,30,角色资源授权,Role Resource Authorization,roleResAuthorization,/roleRes,1,,3,34,0,1,,,,
-35,22,运维监控,Operation And Monitoring,,/ctrl,1,,2,35,1,1,,,,
-36,35,登录日志,Login Log,logLog,/login,1,,3,36,1,1,,,,
-37,35,操作日志,Operation Log,eventlogManage,/operate,1,,3,37,1,1,,,,
-38,35,资源日志,Resource Log,operationLog,/resOp,1,,3,38,1,1,,,,
-39,35,令牌管理,Token Management,tokentool,/token,1,,3,39,1,1,,,,
-40,35,黑/白名单,Black/White List,blackwhiteList,/blacklist,1,,3,40,1,1,,,,
-41,35,数据库监控,Database Monitoring,http://192.168.20.39:12316/sign/toDruid?token=,/sign/toDruid,1,,3,41,1,1,,,,
-42,35,系统监控,System Monitoring,systemMonitoring,/ws,1,,3,42,1,1,,,,
-43,22,系统配置,System Configuration,parameterConfiguration,/args,1,,2,43,1,1,,,,
diff --git a/data/menu.xls b/data/menu.xls
deleted file mode 100644
index 9427c2f..0000000
--- a/data/menu.xls
+++ /dev/null
Binary files differ
diff --git a/data/ts.sql b/data/ts.sql
deleted file mode 100644
index 7f2221e..0000000
--- a/data/ts.sql
+++ /dev/null
@@ -1,148 +0,0 @@
------------------------------------------------------------------------ 鏁版嵁妫�鏌�
-select count(*) from bd.dlg_25w_resa; -- 157217
-select count(*) from bd.dlg_25w_boul; -- 7453
-select count(*) from bd.dlg_agnp; -- 3248
-
-select count(*) from bd.dlg_25w_hyda; -- 297884
-select count(*) from bd.dlg_25w_hydl; -- 2212590
-select count(*) from bd.dlg_25w_lrdl; -- 4690386
-select count(*) from bd.dlg_25w_lrrl; -- 51338
-select count(*) from bs.m_pipeline; -- 26
-
-select * from bd.dlg_25w_resa order by gid limit 10;
-select * from bd.dlg_25w_boul order by gid limit 10;
-select * from bd.dlg_agnp order by gid limit 10;
------------------------------------------------------------------------ 璁剧疆鏁版嵁鍗曚綅
-select * from lf.sys_dep order by id;
-
--- pid = 15, 鐢熶骇涓氬姟閮ㄩ棬
-update bd.dlg_25w_resa set depid = 20 where gid % 7 = 0; -- 绾胯矾瀹�
-update bd.dlg_25w_resa set depid = 21 where gid % 7 = 1; -- 绌胯法瓒婂
---update bd.dlg_25w_resa set depid = 22 where gid % 7 = 2; -- 鍦熷缓瀹�
-
--- pid = 38, 鍕樺療浜嬩笟閮�
-update bd.dlg_25w_resa set depid = 55 where gid % 7 = 3; -- 鍕樺療瀹�
---update bd.dlg_25w_resa set depid = 56 where gid % 7 = 4; -- 宀╁湡璁捐瀹�
---update bd.dlg_25w_resa set depid = 54 where gid % 7 = 5; -- 娴嬬粯瀹�
-
-update bd.dlg_25w_boul set depid = 20 where gid % 7 = 0; -- 绾胯矾瀹�
-update bd.dlg_25w_boul set depid = 21 where gid % 7 = 1; -- 绌胯法瓒婂
-update bd.dlg_25w_boul set depid = 55 where gid % 7 = 3; -- 鍕樺療瀹�
-
-update bd.dlg_agnp set depid = 20 where gid % 7 = 0; -- 绾胯矾瀹�
-update bd.dlg_agnp set depid = 21 where gid % 7 = 1; -- 绌胯法瓒婂
-update bd.dlg_agnp set depid = 55 where gid % 7 = 3; -- 鍕樺療瀹�
-
-update bd.dlg_25w_resa set depid = 22 where depid is null or depid = 0 or depid = 1; -- 鍦熷缓瀹�
-update bd.dlg_25w_boul set depid = 22 where depid is null or depid = 0 or depid = 1; -- 鍦熷缓瀹�
-update bd.dlg_agnp set depid = 22 where depid is null or depid = 0 or depid = 1; -- 鍦熷缓瀹�
-
-update bd.dlg_agnp set depid = null where gid = 3247;
-update bd.dlg_agnp set depid = 0 where gid = 3246;
-
-select depid from bd.dlg_25w_resa group by depid order by depid;
-select depid from bd.dlg_25w_boul group by depid order by depid;
-select depid from bd.dlg_agnp group by depid order by depid;
------------------------------------------------------------------------ 璁剧疆鐢ㄦ埛
-select * from lf.sys_user order by id;
-
-update lf.sys_user set depid = 38 where uname = '绠＄悊鍛�'; -- id = 1
-update lf.sys_user set depid = 20 where uname = '绾胯矾瀹�'; -- id = 2
-update lf.sys_user set depid = 21 where uname = '绌胯法瓒婂'; -- id = 3
-update lf.sys_user set depid = 55 where uname = '鍕樺療瀹�'; -- id = 5
------------------------------------------------------------------------ 璁剧疆瑙掕壊
-select * from lf.sys_role order by id;
-
-update lf.sys_role set is_admin=2 where descr='鏁版嵁绠＄悊鍛�';
-delete from lf.sys_role where id between 2 and 11;
-insert into lf.sys_role (depid,name,descr,is_admin,create_user) values (20,'绾胯矾瀹�-鏁版嵁绠＄悊鍛�', '鏁版嵁绠＄悊鍛�',2,1);
-insert into lf.sys_role (depid,name,descr,is_admin,create_user) values (21,'绌胯法瓒婂-鏁版嵁绠＄悊鍛�','鏁版嵁绠＄悊鍛�',2,1);
-insert into lf.sys_role (depid,name,descr,is_admin,create_user) values (55,'鍕樺療瀹�-鏁版嵁绠＄悊鍛�', '鏁版嵁绠＄悊鍛�',2,1);
-insert into lf.sys_role (depid,name,descr,is_admin,create_user) values (22,'鍦熷缓瀹�-鏁版嵁绠＄悊鍛�', '鏁版嵁绠＄悊鍛�',2,1);
-
------------------------------------------------------------------------ 璁剧疆鐢ㄦ埛瑙掕壊
-select * from lf.sys_role_user order by id;
-
--- delete from lf.sys_role_user where id > 1;
-insert into lf.sys_role_user (id, roleid, userid, create_user) values (2, 12, 2, 1);
-insert into lf.sys_role_user (id, roleid, userid, create_user) values (3, 13, 3, 1);
-insert into lf.sys_role_user (id, roleid, userid, create_user) values (4, 14, 5, 1);
-insert into lf.sys_role_user (id, roleid, userid, create_user) values (5, 15, 4, 1);
-
-alter sequence lf.sys_apply_id_seq restart with 3;
-alter sequence lf.sys_flow_id_seq restart with 7;
------------------------------------------------------------------------ 閫掑綊鏌ヨ
-select depid from bd.dlg_25w_boul where depid > 0 and depid != ALL(fn_rec_array(38, 'dep'))
-and ST_Intersects(geom, ST_PolygonFromText('POLYGON ((115.94927385452 32.3754479115071 0,121.989371092554 32.2766788010181 0,121.850621222894 29.6874200067864 0,115.9727267226 29.7835368627922 0,115.94927385452 32.3754479115071 0))', 4490))
-group by depid;
-
-select c.* from lf.sys_role a inner join lf.sys_role_user b on a.id = b.roleid inner join lf.sys_user c on b.userid = c.id
-where a.is_admin = 2 and a.depid = 55 order by c.id limit 1
-
-select c.*, fn_rec_query(c.depid, 'dep') depName,fn_uname(c.create_user) createName,fn_uname(c.update_user) updateName
-from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid inner join lf.sys_role c on b.roleid = c.id
-where a.id = 1;
-
-select a.*, fn_rec_query(a.depid, 'dep') depName,fn_uname(a.create_user) createName,fn_uname(a.update_user) updateName
-from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid inner join lf.sys_role c on b.roleid = c.id
-where c.id = 15
------------------------------------------------------------------------ 鏌ヨ 57, 163
-select * from lf.sys_meta order by id desc limit 10;
-select * from lf.sys_fme_log order by id desc;
-select * from lf.sys_layer;
-select * from lf.sys_user where id = 1;
--- update lf.sys_user set depid = 36 where id = 1
-
-select * from lf.sys_dict where ns='bd' and tab='dlg_agnp'; -- 鍦板悕琛�
-select * from bd.dlg_agnp;
-select depid from bd.dlg_agnp group by depid;
--- update bd.dlg_agnp set depid=1 where depid is null or depid=0
------------------------------------------------------------------------
-select * from lf.sys_flow where applyid = 9; -- 47,48,49
-select id,userid,depids,tabs,entities,pwd,status,count,descr,wkt from lf.sys_apply;
--- status is '鐘舵�侊細-10-浣滃簾锛�-1-鎵撳洖锛�0~9-瀹℃牳涓紝10-閫氳繃';
-update lf.sys_apply set status = 0 where id = 9;
--- delete from lf.sys_flow; delete from lf.sys_apply;
--- 瀵嗙爜锛歵est@12345_lf
-select * from lf.sys_user where id in (47,48,49);
-select * from lf.sys_download;
------------------------------------------------------------------------ 缁熻鎶ュ憡
--- alter sequence lf.sys_dict_id_seq restart with 11118;
-select fn_rec_query(depid, 'dep') "m1", count(sizes) "sizes"
-from lf.sys_meta
-group by depid
-order by depid;
-
-select fn_rec_query(depid, 'dep') "m1", count(sizes) "sizes"
-from lf.sys_meta
-group by depid
-order by depid;
-
-select modular1 "m1", modular2 "m2", count(*) "count"
-from lf.sys_operate
-group by modular1,modular2
-order by modular1 desc,modular2;
------------------------------------------------------------------------
-select * from lf.sys_menu where position('http:' in url)>0;
-select * from bs.m_marker where parentid is not null; -- delete from bs.m_marker; createtime
-select * from bs.m_hydraulic_protection;
-
-select * from lf.sys_layer where url is not null;
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
diff --git a/data/update.sql b/data/update.sql
deleted file mode 100644
index a71e683..0000000
--- a/data/update.sql
+++ /dev/null
@@ -1,613 +0,0 @@
-
---------------------------------------------------------- 琛ュ厖瀛楁
--- alter table lf.sys_meta rename column bstab to tab; alter table lf.sys_meta drop column mdtab;
-alter table lf.sys_fme_log add column create_time timestamp(6) without time zone default now();
-alter table lf.sys_meta add column layer varchar(50);
-comment on column lf.sys_meta.layer is '鍥惧眰';
-select * from lf.sys_fme_log;
-select * from lf.sys_meta order by guid,tab; -- delete from lf.sys_meta where id> 31;
--- delete from lf.sys_meta; alter sequence lf.sys_meta_id_seq restart with 1;
-
-select * from lf.sys_token where create_time < '2023-01-10';
--- delete from lf.sys_token where create_time < '2023-01-10';
-select * from lf.sys_operate where optime < '2023-01-10';
--- delete from lf.sys_operate where optime < '2023-01-10';
-select * from lf.sys_login where optime < '2023-01-10';
--- delete from lf.sys_login where optime < '2023-01-10';
---------------------------------------------------------- 鏌ヨURL
-select * from lf.sys_menu where position('http://' in url) > 0;
-select url, replace(url, 'pipe.cppe.com', '{host}') from lf.sys_menu where position('http://' in url) > 0;
-update lf.sys_menu set url = replace(url, 'pipe.cppe.com', '{host}') where position('http://' in url) > 0;
---------------------------------------------------------- 鏇存柊鑿滃崟+鐢ㄦ埛
-select * from lf.sys_menu where cn_name = '鏁版嵁搴撶洃鎺�';
--- update lf.sys_menu set url = 'http://pipe.cppe.com:8088/server/druid/index.html' where cn_name = '鏁版嵁搴撶洃鎺�';
-
-select * from lf.sys_user; -- Admin@1234_lf
--- update lf.sys_user set pwd = 'd4d25475a81ef4db4b28ce4034500f896169f15c0c68da5d';
-
-select * from lf.sys_menu order by id;
-update lf.sys_menu set url = 'http://pipe.cppe.com:88/dataxdi/#/schema/list?tags=鍏ュ簱璐ㄦ&embed=true&theme=dark&lang=zh&token=' where cn_name = '鍏ュ簱璐ㄦ';
-update lf.sys_menu set url = 'http://pipe.cppe.com:88/dataxdi/#/schema/list?tags=鎴愭灉璐ㄦ&embed=true&theme=dark&lang=zh&token=' where cn_name = '鎴愭灉璐ㄦ';
-update lf.sys_menu set url = 'http://pipe.cppe.com:88/dataxdi/#/task/list?embed=true&theme=dark&lang=zh&token=' where cn_name = '浠诲姟鐘舵��' and id = 69;
-
-update lf.sys_menu set url = 'http://pipe.cppe.com:88/dataxdi/#/schema/list?tags=鏁版嵁浜ゆ崲&embed=true&theme=dark&lang=zh&token=' where cn_name = '鏍煎紡杞崲';
-update lf.sys_menu set url = 'http://pipe.cppe.com:88/dataxdi/#/schema/list?tags=鍧愭爣杞崲&embed=true&theme=dark&lang=zh&token=' where cn_name = '鍧愭爣杞崲';
-update lf.sys_menu set url = 'http://pipe.cppe.com:88/dataxdi/#/schema/list?tags=鏁版嵁鍒嗗彂&embed=true&theme=dark&lang=zh&token=' where cn_name = '鏁版嵁鍒嗗彂';
-update lf.sys_menu set url = 'http://pipe.cppe.com:88/dataxdi/#/schema/list?tags=鏁版嵁娌荤悊&embed=true&theme=dark&lang=zh&token=' where cn_name = '鏁版嵁娌荤悊';
-update lf.sys_menu set url = 'http://pipe.cppe.com:88/dataxdi/#/task/list?embed=true&theme=dark&lang=zh&token=' where cn_name = '浠诲姟鐘舵��' and id = 74;
-
-update lf.sys_menu set url = 'http://pipe.cppe.com:88/resm/#/resourceManage?embed=true&theme=dark&lang=zh&token=' where cn_name = '鎴戠殑璧勬簮';
-update lf.sys_menu set url = 'http://pipe.cppe.com:88/resm/#/resapproval?embed=true&theme=dark&lang=zh&token=' where cn_name = '璧勬簮瀹℃牳';
---------------------------------------------------------- FME_鏁版嵁璐ㄦ
-select * from lf.sys_menu where cn_name = '鍏ュ簱璐ㄦ';
-update lf.sys_menu set url = 'http://10.7.0.186:88/dataxdi/#/schema/list?tags=鍏ュ簱璐ㄦ&embed=true&token=' where cn_name = '鍏ュ簱璐ㄦ';
-
-select * from lf.sys_menu where cn_name = '鎴愭灉璐ㄦ';
-update lf.sys_menu set url = 'http://10.7.0.186:88/dataxdi/#/schema/list?tags=鎴愭灉璐ㄦ&embed=true&token=' where cn_name = '鎴愭灉璐ㄦ';
-
-select * from lf.sys_menu where cn_name = '浠诲姟鐘舵��' and id = 69;
-update lf.sys_menu set url = 'http://10.7.0.186:88/dataxdi/#/task/list?embed=true&token=' where cn_name = '浠诲姟鐘舵��' and id = 69;
---------------------------------------------------------- FME_鏁版嵁浜ゆ崲
-select * from lf.sys_menu where cn_name = '鏍煎紡杞崲';
-update lf.sys_menu set url = 'http://10.7.0.186:88/dataxdi/#/schema/list?tags=鏁版嵁浜ゆ崲&embed=true&token=' where cn_name = '鏍煎紡杞崲';
-
-select * from lf.sys_menu where cn_name = '鍧愭爣杞崲';
-update lf.sys_menu set url = 'http://10.7.0.186:88/dataxdi/#/schema/list?tags=鍧愭爣杞崲&embed=true&token=' where cn_name = '鍧愭爣杞崲';
-
-select * from lf.sys_menu where cn_name = '鏁版嵁鍒嗗彂';
-update lf.sys_menu set url = 'http://10.7.0.186:88/dataxdi/#/schema/list?tags=鏁版嵁鍒嗗彂&embed=true&token=' where cn_name = '鏁版嵁鍒嗗彂';
-
-select * from lf.sys_menu where cn_name = '鏁版嵁娌荤悊';
-update lf.sys_menu set url = 'http://10.7.0.186:88/dataxdi/#/schema/list?tags=鏁版嵁娌荤悊&embed=true&token=' where cn_name = '鏁版嵁娌荤悊';
-
-select * from lf.sys_menu where cn_name = '浠诲姟鐘舵��' and id = 74;
-update lf.sys_menu set url = 'http://10.7.0.186:88/dataxdi/#/task/list?embed=true&token=' where cn_name = '浠诲姟鐘舵��' and id = 74;
---------------------------------------------------------- FME_鏈嶅姟绠＄悊
-select * from lf.sys_menu where cn_name = '鎴戠殑璧勬簮';
-update lf.sys_menu set url = 'http://10.7.0.186:88/resm/#/resourceManage?embed=true&token=' where cn_name = '鎴戠殑璧勬簮';
-
-select * from lf.sys_menu where cn_name = '璧勬簮瀹℃牳';
-update lf.sys_menu set url = 'http://10.7.0.186:88/resm/#/resapproval?embed=true&token=' where cn_name = '璧勬簮瀹℃牳';
---------------------------------------------------------- 鍒濆鍖栧厓鏁版嵁
-insert into lf.sys_meta (name,dirid) values ('椤圭洰WBS瀵煎嚭.xlsx',57);
-insert into lf.sys_meta (name,dirid) values ('00116BT02-CR001#ESV-DW-0101 灏侀潰.docx',62);
-insert into lf.sys_meta (name,dirid) values ('00116BT02-CR001#ESV-DW-0101锛堝ぇ娌虫部娌崇┛瓒婂湴褰㈠浘锛�0.dwg',62);
-insert into lf.sys_meta (name,dirid) values ('00116BT02-CR001#ESV-DW-0101锛堝ぇ娌虫部娌崇┛瓒婂湴褰㈠浘锛�0.dwl',62);
-insert into lf.sys_meta (name,dirid) values ('00116BT02-CR001#ESV-DW-0101锛堝ぇ娌虫部娌崇┛瓒婂湴褰㈠浘锛�0.dwl2',62);
-insert into lf.sys_meta (name,dirid) values ('00116BT02-CR001#ESV-DW-0101锛堝ぇ娌虫部娌崇┛瓒婄旱鏂潰鍥撅級0.dwg',62);
-insert into lf.sys_meta (name,dirid) values ('00116BT02-CR001#ESV-IN-0101锛堣祫鏂欏浘绾哥洰褰曪級.doc',62);
-insert into lf.sys_meta (name,dirid) values ('1绫�.gdb',74);
-insert into lf.sys_meta (name,dirid) values ('2绫�.gdb',74);
-insert into lf.sys_meta (name,dirid) values ('鏄撶噧鏄撶垎.gdb',74);
-insert into lf.sys_meta (name,dirid) values ('鍦伴潰绾�.xlsx',64);
-insert into lf.sys_meta (name,dirid) values ('鎺у埗鐐�.xlsx',64);
-insert into lf.sys_meta (name,dirid) values ('绂绘暎鐐�.xlsx',64);
-insert into lf.sys_meta (name,dirid) values ('骞抽潰鍥剧储寮曟枃浠�.xlsx',64);
-insert into lf.sys_meta (name,dirid) values ('涓嚎鎴愭灉琛�.xlsx',64);
-
-update lf.sys_meta set depid=1,type='file',path='2\'||name,sizes=1;
-
-insert into lf.sys_meta (name,dirid) values ('S318 娲涢檿绾跨┛瓒婂伐绋嬪湴璐ㄥ墫闈㈠浘.dwg',80);
-insert into lf.sys_meta (name,dirid) values ('鍦拌川鍓栭潰鍥剧储寮曡〃.xlsx',80);
-insert into lf.sys_meta (name,dirid) values ('鍦拌川璧勬枡琛�.xlsx',80);
-insert into lf.sys_meta (name,dirid) values ('鍕樻帰鐐瑰潗鏍囪〃.xlsx',80);
-insert into lf.sys_meta (name,dirid) values ('鍦熷￥鐢甸樆鐜囪〃.xlsx',80);
-
-update lf.sys_meta set depid=1,type='file',path='2\'||name,sizes=1 where path is null;
-
-with rs as (select id from lf.sys_dir where name='瑗挎皵涓滆緭鍥涚嚎澶╃劧姘旂閬撳伐绋嬶紙鍚愰瞾鐣�-涓崼锛夛紙00116BT02锛�')
-select fn_rec_query(a.dirid,'dir'),a.* from lf.sys_meta_new a where dirid = ANY(fn_rec_array((select id from rs), 'dir'));
-
-select * from lf.sys_meta where substr(path,1,2) = '2\';
---------------------------------------------------------- 鏇存柊鍐呭 ---------------------------------------------------------
-bd.* 140锛宐s.* 284锛宭f.* 34锛宮d.* 64
---------------------------------------------------------- 00 lf.sys_dict
-update lf.sys_dict set type='integer' where field='gid' and type='double';
-update lf.sys_dict set type='integer' where field='verid' and type='double';
-update lf.sys_dict set type='integer' where field='createuser' and type='text';
-update lf.sys_dict set type='integer' where field='updateuser' and type='text';
---------------------------------------------------------- 01 lf.sys_dep
--- alter sequence lf.sys_meta_id_seq restart with 1;
-update lf.sys_operate set modular2='淇℃伅绠＄悊' where modular2='鍏冩暟鎹鐞�';
-select modular1,modular2 from lf.sys_operate group by modular1,modular2;
-
-delete from lf.sys_dep where id = 59;
-update lf.sys_dep set bak = null where bak = ' ';
-update lf.sys_dep set code = null;
-update lf.sys_dep set code = '00' where pid = 0;
-update lf.sys_dep set level = level + 1;
-alter table lf.sys_dep alter column code type varchar(24);
-select fn_set_tab_codes(1, 1);
-
-select * from lf.sys_dep order by id;
-select id,pid,name,code,level,order_num,bak from lf.sys_dep where code is not null order by code;
---------------------------------------------------------- 02 lf.sys_dir
-alter table lf.sys_dir alter column code type varchar(200);
-alter table lf.sys_dir rename column code to exts;
-alter table lf.sys_dir alter column bak type varchar(100);
-alter table lf.sys_dir rename column bak to checks;
-alter table lf.sys_dir add column bak varchar(1024);
-
-update lf.sys_dir set bak=checks where checks is not null and position('check' in checks)=0;
-update lf.sys_dir set checks=null where position('check' in checks)=0;
-
-alter table lf.sys_dir add column code varchar(24);
-comment on column lf.sys_dir.exts is '鎵╁睍鍚�';
-comment on column lf.sys_dir.checks is '妫�鏌ラ」';
-select fn_set_tab_codes(0, 2);
-
-select id,checks,replace(checks,chr(10),'') from lf.sys_dir where position(chr(10) in checks) > 0;
-update lf.sys_dir set checks = replace(checks,chr(10),'') where position(chr(10) in checks) > 0; -- 鏇挎崲鍥炶溅閿�
-
-select * from lf.sys_dir order by id;
-select id,pid,name,code,level,order_num,checks,exts from lf.sys_dir where code is not null order by code;
---------------------------------------------------------- 03 lf.sys_user
-alter table lf.sys_user add column depcode varchar(24);
-comment on column lf.sys_user.depcode is '鍗曚綅缂栫爜';
-
-update lf.sys_user a set depcode = (select code from lf.sys_dep b where b.id = a.depid);
-
-select * from lf.sys_user order by id;
-select id,uname,depid,depcode from lf.sys_user order by id;
---------------------------------------------------------- 04 lf.sys_meta
-alter table lf.sys_meta add column depcode varchar(24) default '00';
-alter table lf.sys_meta add column dircode varchar(24) default '00';
-comment on column lf.sys_meta.depcode is '鐩綍缂栫爜';
-comment on column lf.sys_meta.dircode is '鍗曚綅缂栫爜';
-
-update lf.sys_meta a set depcode = (select code from lf.sys_dep b where b.id = a.depid);
-update lf.sys_meta a set dircode = (select code from lf.sys_dir b where b.id = a.dirid);
-
-alter table lf.sys_meta drop column depid;
-alter table lf.sys_meta drop column dirid;
-
--- drop index if exists lf.idx_sys_meta_depcode;
-create index idx_sys_meta_depcode on lf.sys_meta (depcode);
-
--- drop index if exists lf.idx_lf_sys_meta_dircode;
-create index idx_lf_sys_meta_dircode on lf.sys_meta (dircode);
-
-select id,name,depcode,dircode,type from lf.sys_meta order by id;
---------------------------------------------------------- 05 lf.sys_flow
-alter table lf.sys_flow alter column depid type varchar(24);
-alter table lf.sys_flow rename column depid to depcode;
-
-update lf.sys_flow a set depcode = (select code from lf.sys_dep b where b.id = cast(a.depcode as integer));
-
-select * from lf.sys_flow;
---------------------------------------------------------- 06 lf.sys_apply
-update lf.sys_apply set depids = '000204,000205,000206' where depids = '20,21,22';
-update lf.sys_apply set depids = '000205,000206' where depids = '21,22';
-
-select * from lf.sys_dep where id in (20,21,22);
-select * from lf.sys_apply;
---------------------------------------------------------- 07 bd.dlg_agnp *
-alter table bd.dlg_agnp alter column depid type varchar(24);
-alter table bd.dlg_agnp alter column dirid type varchar(24);
-
-update bd.dlg_agnp a set depid = (select code from lf.sys_dep b where b.id = cast(a.depid as integer));
-update bd.dlg_agnp a set dirid = (select code from lf.sys_dir b where b.id = cast(a.dirid as integer));
-
-select * from bd.dlg_agnp limit 20;
-select depid from bd.dlg_agnp where depid is not null and depid not like '000307%' group by depid order by depid;
-select dirid from bd.dlg_agnp group by dirid order by dirid; select id, name, code from lf.sys_dir where id in (3, 0, 163) order by code;
-select depid from bd.dlg_agnp group by depid order by depid; select id, name, code from lf.sys_dep where id in (1, 20, 21, 22, 38, 55) order by code;
---------------------------------------------------------- 08 lf.sys_role
-update lf.sys_role set is_admin = 2 where position('鏁版嵁绠＄悊鍛�' in descr) > 0;
-
-select c.* from lf.sys_role a inner join lf.sys_role_user b on a.id = b.roleid
-inner join lf.sys_user c on b.userid = c.id
-where a.is_admin = 2 and c.depcode = '000204'
-order by c.id limit 1;
-
-select c.* from lf.sys_role a inner join lf.sys_role_user b on a.id = b.roleid
-inner join lf.sys_user c on b.userid = c.id
-where a.is_admin = 2 and a.depid = 20
-order by c.id limit 1;
-
-select * from lf.sys_role;
---------------------------------------------------------- 09 lf.sys_dict 1810琛�
-select * from lf.sys_dict where field in ('geom', 'gid', 'eventid', 'parentid');
-
-update lf.sys_dict set showtype = 0 where field in ('geom', 'gid', 'eventid', 'parentid');
---------------------------------------------------------- 09 娴嬭瘯SQL
-with rs as (select code from lf.sys_dir where name='瑗挎皵涓滆緭鍥涚嚎澶╃劧姘旂閬撳伐绋嬶紙鍚愰瞾鐣�-涓崼锛夛紙00116DT02锛�')
-select fn_rec_query(a.dirid,'dir'),a.* from lf.sys_meta a where dirid = ANY(fn_rec_array((select id from rs), 'dir'));
-
-select a.*,fn_uname(a.create_user) uname,fn_get_fullname(a.depcode, 1) depName,fn_ver(a.verid) verName,fn_get_fullname(a.dircode, 2) dirName
-from lf.sys_meta a where a.dircode like (select code from lf.sys_dir where name='瑗挎皵涓滆緭鍥涚嚎澶╃劧姘旂閬撳伐绋嬶紙鍚愰瞾鐣�-涓崼锛夛紙00116DT02锛�') || '%';
-
--- 椤圭洰琛�
-select a.*,st_astext(geom) from bs.bs_project a;
-
--- 鎸夐」鐩粺璁℃暟鎹� *
-select name "鍚嶇О", (select count(*) from lf.sys_meta b where b.dircode like a.code || '%') "鏂囦欢鏁�",
- (select sum(sizes) from lf.sys_meta b where b.dircode like a.code || '%') "鏁伴噺(MB)"
-from lf.sys_dir a where id > 1 and pid = 0;
-
-select name "m1", coalesce((select sum(sizes) from lf.sys_meta b where b.dircode like a.code || '%'), 0) "sizes",
- (select count(*) from lf.sys_meta b where b.dircode like a.code || '%') "count"
-from lf.sys_dir a where pid = 0 order by a.code;
-
--- 鎸夋枃浠剁被鍨嬬粺璁� *
-select type,count(*),sum(sizes) from lf.sys_meta group by type;
-select type "m1", cast(sum(sizes) as decimal(18, 3)) "sizes", count(*) "count" from lf.sys_meta group by type order by type;
-
--- type绫诲瀷锛�1-Shp鏂囦欢锛�2-涓撻鍥撅紝3-鍏冩暟鎹紝4-涓氬姟鏁版嵁锛�5-绠￠亾鍒嗘瀽锛�6-缁熻鎶ュ憡
-select * from lf.sys_download;
-select * from lf.sys_serve_log
-
--- 鎸夌収涓撲笟缁熻
-select code , name "value" from lf.sys_dir a
- where name in ('娴嬬粯锛圗SV锛�', '鍕樺療锛圗GE锛�', '鍦扮伨锛圗GD锛�', '娲炲簱锛圗GD锛�') group by name order by name;
-select * from lf.sys_meta
-where dircode like '040000%' or dircode like '0100000000%' or dircode like '02000000%' or dircode like '030000%';
-
--- 鏁版嵁鐢宠
-select * from lf.sys_apply a
-inner join lf.sys_user b on a.userid = b.id
-inner join lf.sys_dep c on b.depid = c.id;
-
--- 璁块棶娆℃暟
-select modular1,count(*) from lf.sys_operate group by modular1 order by modular1;
-
--- 鎸夊崟浣嶇粺璁�
-select name,code,(select count(*) from lf.sys_meta b where b.depcode like a.code || '%'),
- (select count(sizes) from lf.sys_meta b where b.depcode like a.code || '%')
-from lf.sys_dep a where name in ('娴嬬粯瀹�','鍕樺療瀹�','鍦扮伨涓撲笟','娲炲簱涓撲笟');
-
--- 鏈嶅姟缁熻
-select * from lf.sys_serve_log;
-
--- 鐧诲綍娆℃暟
-select b.uname, count(*) from lf.sys_login a inner join lf.sys_user b on a.userid = b.id group by uname;
-
--- 鎸夌収澶х被缁熻
-select name,
- (select count(b.id) from lf.sys_meta b where dircode similar to '(' || (select string_agg(code, '|') from lf.sys_dir c where c.name = a.name) || ')%') "count",
- (select coalesce(sum(b.sizes), 0) from lf.sys_meta b where dircode similar to '(' || (select string_agg(code, '|') from lf.sys_dir c where c.name = a.name) || ')%') "sizes"
-from lf.sys_dir a
-where name in ('鍩虹娴嬬粯', '鍩虹鍦扮伨', '鍩虹鍕樺療', '鍚堣鏁版嵁', '绠＄悊鏁版嵁', '娴嬬粯锛圗SV锛�', '鍕樺療锛圗GE锛�', '鍦扮伨锛圗GD锛�', '娲炲簱锛圗GD锛�')
-group by name
-order by name;
-
-select name,
- (select count(b.id) from lf.sys_meta b where dircode similar to fn_dir_similar(a.name)) "count",
- (select coalesce(sum(b.sizes), 0) from lf.sys_meta b where dircode similar to fn_dir_similar(a.name)) "sizes"
-from lf.sys_dir a
-where name in ('鍩虹娴嬬粯', '鍩虹鍦扮伨', '鍩虹鍕樺療', '鍚堣鏁版嵁', '绠＄悊鏁版嵁', '娴嬬粯锛圗SV锛�', '鍕樺療锛圗GE锛�', '鍦扮伨锛圗GD锛�', '娲炲簱锛圗GD锛�')
-group by name
-order by name;
--- select * from lf.sys_meta where dircode similar to fn_dir_similar('鍩虹娴嬬粯');
-
--- 鏌ヨ鍒嗙被閿�
-select (select string_agg(code, ',') from lf.sys_dir where name = a.name) "key", name "value"
-from lf.sys_dir a
-where name in ('鍩虹鍦扮伨', '鍩虹鍕樺療', '鍚堣鏁版嵁', '绠＄悊鏁版嵁', '娴嬬粯锛圗SV锛�', '鍕樺療锛圗GE锛�', '鍦扮伨锛圗GD锛�', '娲炲簱锛圗GD锛�')
-group by name
-order by key;
-
--- 鍗曚釜椤圭洰鐨勪笅杞戒釜鏁般�佷笅杞芥鏁板拰鏁版嵁鎬婚噺
-select count(*) "涓嬭浇涓暟", sum(c.dcount) "涓嬭浇鏁伴噺", round(sum(c.dcount * c.sizes)::numeric, 3) "鏁伴噺鎬婚噺(MB)"
-from lf.sys_meta a inner join lf.sys_meta_down b on a.id = b.metaid
- inner join lf.sys_download c on b.downid = c.id
-where dircode like '06%';
-
--- 鎸夌収杈撻�佷粙璐ㄧ粺璁�
-select
- case medium when 'CPY' then '鎴愬搧娌�' when 'TRQ' then '澶╃劧姘�' when 'YY' then '鍘熸补' else '鍏跺畠' end "杈撻�佷粙璐�",
- round(sum(st_length(geom))::numeric, 2) "鎬婚噷绋�",
- count(*) "绠￠亾鏁伴噺",
- (select count(*) from bs.m_sitepoint b inner join bs.m_pipeline c on b.pipename = c.pipename where c.medium = a.medium) "绔欏満鏁伴噺",
- (select count(*) from bs.m_valvehousepoint d inner join bs.m_pipeline e on d.pipename = e.pipename where e.medium = a.medium) "闃�瀹ゆ暟閲�"
-from bs.m_pipeline a
-group by medium;
--- select * from lf.sys_domain where dom_name='dsg0016';
-
--- 鎵�鏈夐」鐩殑涓嬭浇涓暟銆佷笅杞芥鏁板拰鏁版嵁鎬婚噺
-with rs as (
- select a.dircode, c.dcount, c.sizes
- from lf.sys_meta a
- inner join lf.sys_meta_down b on a.id = b.metaid
- inner join lf.sys_download c on b.downid = c.id
-)
-select
- d.name "椤圭洰鍚嶇О",
- (select count(*) from rs where rs.dircode like d.code || '%') "涓嬭浇涓暟",
- (select coalesce(sum(rs.dcount), 0) from rs where rs.dircode like d.code || '%') "涓嬭浇鏁伴噺",
- (select round(coalesce(sum(rs.dcount * rs.sizes), 0)::numeric, 3) from rs where rs.dircode like d.code || '%') "鏁伴噺鎬婚噺(MB)"
-from lf.sys_dir d
-where pid = 0
-order by d.code;
-
---------------------------------------------------------- 02.閫掑綊鏌ヨ
-with recursive rs as(
- select * from lf.sys_menu where cn_name='绠￠亾鍩虹澶ф暟鎹钩鍙�'
-union
- select a.* from lf.sys_menu a, rs b where a.pid=b.id
-) select * FROM rs order by order_num; -- 鏌ヨ鑿滃崟
-
-with recursive rs as (
- select * from lf.sys_dep where name='涓浗鐭虫补澶╃劧姘旂閬撳伐绋嬫湁闄愬叕鍙�'
-union
- select a.* from lf.sys_dep a, rs b where a.pid=b.id
-) select * FROM rs order by order_num; -- 鏌ヨ鍗曚綅
-
-with recursive rs as(
- select * from lf.sys_dir where name='涓縿涓滅嚎绠￠亾宸ョ▼鍗楁'
-union
- select a.* from lf.sys_dir a, rs b where a.pid=b.id
-) select * FROM rs order by order_num; -- 鏁版嵁鐩綍
-
-with recursive rs as(select id, pid from lf.sys_dir where id in (2,5,7,9,12)
-union select a.id,a.pid from lf.sys_dir a, rs b where a.pid = b.id)
-select distinct id from rs order by id; -- 鏁版嵁鐩綍
---------------------------------------------------------- 03.鏌ヨ琛ㄧ粨鏋�
-select * from pg_tables; select * from pg_class order by relnamespace;
-select relnamespace,relkind,relname from pg_class where relnamespace in (select oid from pg_namespace) and relkind='r' order by 1,2;
-
-select c.relname as "琛ㄥ悕", cast(obj_description(c.oid) as varchar) as "琛ㄥ悕鎻忚堪",a.attnum as "搴忓彿", a.attname as "鍒楀悕",
- concat_ws('', t.typname, SUBSTRING(format_type(a.atttypid, a.atttypmod) from '\(.*\)')) as "瀛楁绫诲瀷",d.description as "澶囨敞"
-from pg_attribute a left join pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum
-left join pg_class c on a.attrelid = c.oid left join pg_type t on a.atttypid = t.oid
-where a.attnum >= 0 and c.relname like 'sys_%' and obj_description(c.oid) is not null --and relnamespace=20582
-order by c.relname desc, a.attnum asc;
---------------------------------------------------------- 04.绱㈠紩
-alter table lf.sys_role_user add constraint idx_unique_role_user unique (roleid, userid); -- 鑱斿悎鍞竴绱㈠紩
-insert into lf.sys_role_user (roleid,userid) values (1,1); -- 娴嬭瘯
-select * from lf.sys_role_user;
-
-alter table lf.sys_menu_auth add constraint idx_unique_menu_auth unique (menuid, authid); -- 鑱斿悎鍞竴绱㈠紩
-insert into lf.sys_menu_auth (menuid, authid) values (1,1); -- 娴嬭瘯
-select * from lf.sys_menu_auth;
-
-alter table lf.sys_role_res add constraint idx_unique_role_res unique (roleid,resid); -- 鑱斿悎鍞竴绱㈠紩
-insert into lf.sys_role_res (roleid,resid) values (1,1);
-select * from lf.sys_role_res;
-
-alter table lf.sys_role_menu_auth add constraint idx_unique_role_menu_auth unique (roleid,menu_auth_id); -- 鑱斿悎鍞竴绱㈠紩
-insert into lf.sys_role_menu_auth (roleid,menu_auth_id) values (1,1);
-select * from lf.sys_role_menu_auth;
---------------------------------------------------------- 05.鑱斿悎鏌ヨ
-select a.*,fn_uname(a.create_user) createUserName,fn_uname(a.update_user) updateUserName from lf.sys_blacklist a order by a.id;
-select * from lf.sys_user a where uname like '%' and a.depid=ANY(fn_rec_array(1,'dep'));
-select * from lf.sys_user a left join lf.sys_role_user b on a.id=b.userid;
-
-select a.* from lf.sys_user a where not exists (select b.id from lf.sys_role_user b where b.userid = a.id and b.roleid = 1)
-and uname like '%瀹�%' and a.depid = ANY(fn_rec_array(1, 'dep'))
-order by a.id limit 10 offset 0;
-
-select a.*,fn_rec_query(a.depid, 'dep') depName,fn_rec_query(a.dirid, 'dir') dirName from lf.sys_style a;
-
-select * from lf.sys_auth a left join lf.sys_menu;
-select a.* from lf.sys_auth a where not exists (select b.id from lf.sys_menu_auth b where b.authid = a.id and b.menuid = 1);
-
-select a.* from lf.sys_res a where not exists (select b.id from lf.sys_role_res b where b.resid = a.id and b.roleid = 1);
-
-select a.*,c.name from lf.sys_menu_auth a inner join lf.sys_auth c on a.authid = c.id
-where not exists (select b.id from lf.sys_role_menu_auth b where b.menu_auth_id = a.id and b.roleid = 1) and a.menuid = 1 order by c.id;
-
-select a.*,b.menuid,c.name from lf.sys_role_menu_auth a inner join lf.sys_menu_auth b on a.menu_auth_id = b.id
-inner join lf.sys_auth c on b.authid = c.id where a.roleid = 1 and b.menuid = 1 order by c.id;
-----------------------------------------------------------------------------------------------
-update lf.sys_operate set modular1='杩愮淮绠＄悊',modular2='鑿滃崟绠＄悊' where position('/Menu/select' in url)>0 and (modular1 is null or modular2 is null);
-update lf.sys_operate set modular1='杩愮淮绠＄悊',modular2='鐢ㄦ埛绠＄悊' where position('/user/select' in url)>0 and (modular1 is null or modular2 is null);
-update lf.sys_operate set modular1='杩愮淮绠＄悊',modular2='璧勬簮绠＄悊' where position('/res/select' in url)>0 and (modular1 is null or modular2 is null);
-
-delete from lf.sys_operate where modular1 is null or modular2 is null;
---------------------------------------------------------- 06.鏌ヨ鎺堟潈
-select gid,gb,name,st_astext(a.geom) from bd.dlg_agnp a;
-
--- 鏍规嵁鐢ㄦ埛Uid鏌ヨ璧勬簮
-select distinct d.id,d.name,d.server from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
-inner join lf.sys_role_res c on b.roleid = c.roleid
-inner join lf.sys_res d on c.resid = d.id
-where a.uid = 'admin';
-
--- 鏍规嵁鐢ㄦ埛Uid鏌ヨ鑿滃崟
-select distinct e.id,e.pid,e.cn_name,e.en_name,e.url,e.type,e.icon,e.level,e.order_num,e.is_show
-from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
-inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
-inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
-inner join lf.sys_menu e on d.menuid = e.id
-where a.uid = 'admin';
-
--- 鏍规嵁鐢ㄦ埛Uid鏌ヨ鏉冮檺
-select distinct f.id,e.cn_name,f.name,e.perms,f.tag
-from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
-inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
-inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
-inner join lf.sys_menu e on d.menuid = e.id
-inner join lf.sys_auth f on d.authid = f.id
-where a.uid = 'admin';
-
--- 鏍规嵁鐢ㄦ埛Uid鏌ヨ鏉冮檺2
-select distinct e.perms || f.tag as "perms"
-from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
-inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
-inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
-inner join lf.sys_menu e on d.menuid = e.id
-inner join lf.sys_auth f on d.authid = f.id
-where a.uid = 'admin';
---------------------------------------------------------- 07.鏁版嵁缁熻
-select modular2,count(*) from lf.sys_operate group by modular2;
-
-select to_char(optime,'yyyy-mm-dd') as optime,count(*) from lf.sys_login where optime between (select optime - interval '30 day')
-and optime group by to_char(optime, 'yyyy-mm-dd') order by to_char(optime, 'yyyy-mm-dd') asc;
-
-select a.*,coalesce(a.native,'') from lf.sys_user a;
-select ST_PointFromText('POINT(95.80461853400004 34.13862467200005)');
-select ST_LineFromText('LINESTRING(04.98985101830993 37.43840773692756,104.99318913447104 37.43883729720358)', 4326);
-select ST_PolygonFromText('POLYGON((104.9907822932683 37.43532941961706,104.99088987736599 37.43532941961706,104.9908670336867 37.4349030213574,104.99078327712658 37.4349030213574,104.9907822932683 37.43532941961706))',4326);
-
--- 114ms锛�180/3248
-select count(1) from bd.dlg_agnp a where ST_Intersects(geom,
-ST_PolygonFromText('POLYGON ((115.94927385452 32.3754479115071 0,121.989371092554 32.2766788010181 0,121.850621222894 29.6874200067864 0,115.9727267226 29.7835368627922 0,115.94927385452 32.3754479115071 0))', 4326))
-
--- 1101锛歡id >= 50 and name like '鍘�' and objectid < 5000.0 and pac = '360430'
-select count(*) from bd.dlg_agnp where gid >= 50 and name like '%鍘�%' and objectid < 5000 --and pac = '360430'
-and ST_Intersects(geom,
-ST_PolygonFromText('POLYGON ((115.94927385452 32.3754479115071 0,121.989371092554 32.2766788010181 0,121.850621222894 29.6874200067864 0,115.9727267226 29.7835368627922 0,115.94927385452 32.3754479115071 0))', 4326))
-
-select a.* from lf.sys_domain a inner join lf.sys_dict b on a.dom_name = b.domain_na
-where b.ns = 'bd' and b.tab = 'dlg_25w_aanp' and b.domain_na is not null;
-
-select * from lf.sys_dict where ns = 'bd' and tab = 'dlg_25w_aanp' and domain_na is not null;
-select * from lf.sys_domain where dom_name='dlg25gbcode';
-select * from bd.dlg_25w_aanp limit 10;
-
-select * from bd.dlg_25w_agnp
-where ST_Intersects(geom,
-ST_PolygonFromText('POLYGON ((115.94927385452 32.3754479115071 0,121.989371092554 32.2766788010181 0,121.850621222894 29.6874200067864 0,115.9727267226 29.7835368627922 0,115.94927385452 32.3754479115071 0))', 0))
-limit 10;
-select st_srid(geom) from bd.dlg_25w_agnp limit 1;
-select * from bd.dlg_25w_agnp where class != 'BB' limit 1;
-
-select a.* from lf.sys_domain a where exists (select * from lf.sys_dict b where b.ns = 'bd' and b.tab = 'dlg_25w_hyda') and a.dom_code like '210%' order by a.id
-select * from lf.sys_download a where create_user = 1 and type = 2;
-
-select a.*,fn_uname(a.create_user) uname,fn_get_fullname(a.depcode, 1) depName,fn_ver(a.verid) verName,fn_get_fullname(a.dircode, 2) dirName,
- (select coalesce(sum(dcount), 0) from lf.sys_download b inner join lf.sys_meta_down c on b.id = c.downid where c.metaid = a.id) "downCount",
- (select fn_uname(download_user) from lf.sys_download b inner join lf.sys_meta_down c on b.id = c.downid where c.metaid = a.id order by download_time desc limit 1) "lastUser",
- (select max(download_time) from lf.sys_download b inner join lf.sys_meta_down c on b.id = c.downid where c.metaid = a.id) "lastTime"
-from lf.sys_meta a
-order by id desc
-limit 10 offset 0;
---------------------------------------------------------- 08.鏌ヨ绌洪棿鏁版嵁
-select to_char(create_time, 'yyyy-mm-dd hh24:mi:ss') from lf.sys_meta;
-select to_timestamp('2018-08-15 12:10:10', 'yyyy-MM-dd hh24:mi:ss');
-select a.*,fn_uname(a.create_user) uname from lf.sys_token a
-select distinct ns,tab,fn_get_entity(tab) entity,tab_desc,tabletype from lf.sys_dict order by tab;
-
--- 3248锛歅OINT(95.8046 34.1386)
-select * from bd.dlg_agnp;
-select count(*) from bd.dlg_agnp;
-delete from bd.dlg_agnp;
-select ST_AsText(geom) from bd.dlg_agnp limit 1;
-
--- 7348锛歁ULTILINESTRING((113.6007 6.9434,113.6015 6.9444,113.6007 6.9434))
-select * from bd.dlg_25w_boul;
-select count(*) from bd.dlg_25w_boul;
-delete from bd.dlg_25w_boul;
-select ST_AsText(geom) from bd.dlg_25w_boul limit 1;
-
--- 156847锛歁ULTIPOLYGON(((108.6790 19.0108,108.6791 19.0104,108.6796 19.0104,108.6790 19.0108)))
-select * from bd.dlg_25w_resa;
-select count(*) from bd.dlg_25w_resa;
-delete from bd.dlg_25w_resa;
-select ST_AsText(geom) from bd.dlg_25w_resa limit 1;
-
--- MultiLineString MultiPolygon Point
-select GeometryType(geom) from bd.dlg_25w_boul where gid=20;
-select st_srid(geom) from bd.dlg_25w_boul where gid=20 limit 1;
-select ST_AsText(geom) from bd.dlg_agnp where gid=20;
-
-select * from lf.sys_dict where ns='bd' and tab='dlg_25w_boul' and field='geom';
-select * from lf.sys_meta; -- delete from lf.sys_meta where id<113;
-select * from lf.sys_meta_file; -- delete from lf.sys_meta_file where metaid<113;
-
-select * from lf.sys_meta_file a where metaid in (113,120,121,122);
-alter sequence bd.dlg_agnp_gid_seq restart with 4000;
-select max(gid) from bd.dlg_agnp;
---------------------------------------------------------- 09.绠＄嚎鍒嗘瀽
-select ST_SRID(geom), ST_GeometryType(geom) from bd.dlg_25w_hydl limit 1;
-select ST_SRID(geom), ST_GeometryType(geom) from bd.dlg_25w_lrdl limit 1;
-select ST_SRID(geom), ST_GeometryType(geom) from bd.dlg_25w_lrrl limit 1;
-select ST_SRID(geom), ST_GeometryType(geom) from bd.dlg_25w_hyda limit 1;
-select ST_SRID(geom), ST_GeometryType(geom) from bs.m_pipesegment limit 1;
-
-SELECT a.name as acrossName, b.remarks, b.pipename as pipeName,
- st_length( st_geographyfromtext( st_astext( st_intersection( ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) ) ) AS acrossLength,
- st_astext(b.geom) as wkt
-FROM bd.dlg_25w_hyda AS a, (SELECT * FROM bs.m_pipesegment WHERE segname = 'DD' ) AS b WHERE ST_Intersects(a.geom, b.geom);
-
-select row_number() over() as gid, a.name as acrossName, b.segname as segName, b.remarks, b.pipename as pipeName,
- cast( st_length( st_geographyfromtext( st_astext( st_intersection(ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) ) ) as decimal(12, 3) ) AS acrossLength,
- st_astext( st_intersection( ST_MakeValid(a.geom), ST_MakeValid(b.geom) ) ) as wkt
-from bd.dlg_25w_hyda as a, (select segname, remarks, pipename, geom from bs.m_pipesegment where gid = 24) as b
-where ST_Intersects(a.geom, b.geom);
-
-select *, st_astext(geom) as wkt from bs.m_pipesegment;
-select pipename,segname from bs.m_pipesegment where gid = 24;
---------------------------------------------------------- 10.鏌ヨ浣嶇疆
-select '鍥�' "key", cname "value" from bs.th_globe_country where ST_Intersects(ST_PointFromText('POINT(114.178955 38.991743)', 4490), geom)
-union all
-select '鐪�' "key", cname "value" from bs.th_province_area where ST_Intersects(ST_PointFromText('POINT(114.178955 38.991743)', 4490), geom)
-union all
-select '甯�' "key", cname "value" from bs.th_district_area where ST_Intersects(ST_PointFromText('POINT(114.178955 38.991743)', 4490), geom)
-union all
-select '鍘�' "key", cname "value" from bs.th_county_area where ST_Intersects(ST_PointFromText('POINT(114.178955 38.991743)', 4490), geom);
---------------------------------------------------------- 11.鏍规嵁鐢ㄦ埛ID鏌ヨ鏉冮檺
-select * from lf.sys_role a inner join lf.sys_role_user b on a.id=b.roleid inner join lf.sys_user c on b.userid=c.id
-where c.uid = 'kc';
-
-select distinct e.id,e.pid,e.cn_name,e.en_name,e.url,e.perms,e.type,e.css,e.icon,e.level,e.order_num,e.is_show
-from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
-inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
-inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
-inner join lf.sys_menu e on d.menuid = e.id
-where a.uid = 'kc'
-order by e.order_num;
-
-with recursive rs as(
- select a.* from lf.sys_menu a where a.id = 64
-union
- select a.* from lf.sys_menu a, rs b where a.pid = b.id
-)
-select * FROM rs where rs.id in (
- select *
- from lf.sys_user a inner join lf.sys_role_user b on a.id = b.userid
- inner join lf.sys_role_menu_auth c on b.roleid = c.roleid
- inner join lf.sys_menu_auth d on c.menu_auth_id = d.id
- inner join lf.sys_menu e on d.menuid = e.id
- inner join lf.sys_auth f on d.authid = f.id
- where a.uid = 'admin')
-order by order_num;
---------------------------------------------------------- A0 淇敼琛ㄧ粨鏋�
--- drop function fn_alter_tab_fields();
-create or replace function fn_alter_tab_fields()
-returns integer as $$
- declare
- rec record;
- tn varchar;
- cc integer := 0;
- begin
- for rec in (select ns, tab from lf.sys_dict group by ns, tab order by ns, tab)
- loop
- raise notice 'ns = %, tab = %.', rec.ns, rec.tab;
-
- tn := rec.tab;
- if length(tn) > 32 then
- tn := replace(tn, 'standard_frozen_depth_', 's_f_d_');
- tn := replace(tn, 'general_report_on_', 'g_r_o_');
- tn := replace(tn, 'prevention_and_control_', 'p_a_c_');
- tn := replace(tn, 'water_curtain_roadway_', 'w_c_r_');
- end if;
-
- execute format('alter table %s.%s alter column %s type varchar(24)', rec.ns, rec.tab, 'depid');
- execute format('alter table %s.%s alter column %s type varchar(24)', rec.ns, rec.tab, 'dirid');
-
- execute format('update %s.%s a set depid = (select code from lf.sys_dep b where b.id = cast(a.depid as integer))', rec.ns, rec.tab);
- execute format('update %s.%s a set dirid = (select code from lf.sys_dir b where b.id = cast(a.dirid as integer))', rec.ns, rec.tab);
-
- --execute format('drop index if exists %s.idx_%s_depid', rec.ns, rec.tab);
- execute format('create index idx_%s_depid on %s.%s (depid)', tn, rec.ns, rec.tab);
-
- --execute format('drop index if exists %s.idx_%s_dirid', rec.ns, rec.tab);
- execute format('create index idx_%s_dirid on %s.%s (dirid)', tn, rec.ns, rec.tab);
-
- cc := cc + 1;
- end loop;
-
- return cc;
- end;
-$$ language plpgsql;
-
-select fn_alter_tab_fields();
-
-select gid,depid,dirid from bd.b_borehole limit 10;
-select depid,dirid from bd.dlg_agnp group by depid,dirid;
-select depid,dirid from bd.dlg_25w_resa group by depid,dirid;
-select depid,dirid from bd.dlg_25w_boul group by depid,dirid;
-select * from lf.sys_dict where tab='dlg_25w_boul';
----------------------------------------------------------
diff --git "a/data/\345\214\2051\346\216\245\345\217\243\350\257\264\346\230\216.docx" "b/data/\345\214\2051\346\216\245\345\217\243\350\257\264\346\230\216.docx"
deleted file mode 100644
index fd5e58a..0000000
--- "a/data/\345\214\2051\346\216\245\345\217\243\350\257\264\346\230\216.docx"
+++ /dev/null
Binary files differ
diff --git "a/data/\346\225\260\346\215\256\351\207\217\347\273\237\350\256\241.docx" "b/data/\346\225\260\346\215\256\351\207\217\347\273\237\350\256\241.docx"
deleted file mode 100644
index 8875b21..0000000
--- "a/data/\346\225\260\346\215\256\351\207\217\347\273\237\350\256\241.docx"
+++ /dev/null
Binary files differ
diff --git "a/data/\346\225\260\346\215\256\351\207\217\347\273\237\350\256\241.xlsx" "b/data/\346\225\260\346\215\256\351\207\217\347\273\237\350\256\241.xlsx"
deleted file mode 100644
index 2239397..0000000
--- "a/data/\346\225\260\346\215\256\351\207\217\347\273\237\350\256\241.xlsx"
+++ /dev/null
Binary files differ
diff --git "a/data/\346\227\245\346\212\245.txt" "b/data/\346\227\245\346\212\245.txt"
deleted file mode 100644
index 4b63c9c..0000000
--- "a/data/\346\227\245\346\212\245.txt"
+++ /dev/null
@@ -1,80 +0,0 @@
-=========================================2023-01-17
-鏈懆锛�
-1 Web寮�鍙戯細
-1锛夌患鍚堝睍绀猴細
-
-1.8 瑙ｅ喅绠￠亾鍒嗘瀽鐨勪笅杞藉姛鑳藉紓甯�
-1.9 淇敼鍦ㄧ嚎鍑哄浘鍔熻兘
-
-2锛夋暟鎹鐞�
-1.10 缁熶竴鏁版嵁璐ㄦ鎺ュ彛璋冪敤閫昏緫
-1.11 瑙ｅ喅璐ㄦ鎶ュ憡涓嬭浇鍑洪敊
-1.12 瑙ｅ喅鏁版嵁妫�绱㈤儴鍒嗛〉闈㈣繍琛屾姤閿�
-1.13 淇敼鍏冩暟鎹鐞嗛〉闈�
-
-2 鏈嶅姟寮�鍙戯細
-2.1 淇敼鏁版嵁瀵煎嚭GDB搴撶殑鎺ュ彛
-2.2 淇敼鏁版嵁鍏ュ簱鐨勪笟鍔￠�昏緫
-2.3 涓嶧ME瀵规帴Excel鏁版嵁鍏ュ簱鍔熻兘
-2.4 寮�鍙慐xcel鏁版嵁鍚屾鐨勮Е鍙戝櫒
-2.5 灏嗕笟鍔℃帶鍒跺瓧娈典俊鎭啓鍏ME鎺ュ彛
-2.6 淇敼鏁版嵁璐ㄦ鎺ュ彛鐨勮皟鐢�
-2.7 缁熶竴淇敼FME璋冪敤鐨勬帴鍙ｈ鑼�
-2.8 淇敼GDAL鍒濆鍖栨椂鐨勭幆澧冨彉閲忚瀹�
-
-3 绯荤粺鍙戝竷锛�
-3.1 鍦ㄥ叕缃戠幆澧冧笅閮ㄧ讲绯荤粺
-3.2 閮ㄧ讲SG鐨勬墦鍖呫�佷笅杞界幆澧�
-3.3 瑙ｅ喅QGIS鍑哄浘鍑洪敊
-3.4 瑙ｅ喅搴旂敤绋嬪簭鍚姩鍑洪敊
-3.5 瑙ｅ喅GDAL绫诲簱杩愯寮傚父
-
-涓嬪懆锛�
-1 Web寮�鍙戯細
-缁熶竴淇敼鍚勯〉闈㈡牱寮�
-淇敼鏁版嵁涓婁紶鍔熻兘
-淇敼鏁版嵁妫�绱㈠姛鑳�
-淇敼鏉冮檺绠＄悊鍙婃巿鏉冨姛鑳�
-
-2 鏈嶅姟寮�鍙戯細
-淇敼鍏冩暟鎹墦鍖呭姛鑳�
-淇敼鏁版嵁鐢宠鍔熻兘
-淇敼鏁版嵁妫�绱㈡帴鍙�
-=========================================2023-02-02
-鏈懆锛�
-1 Web寮�鍙戯細
-
-2 鏈嶅姟寮�鍙戯細
-2.1 寮�鍙戝悓姝xcel鏁版嵁鍏ュ簱鐨勮Е鍙戝櫒鍜屽瓨鍌ㄨ繃绋�
-2.2 閫掑綊鏌ヨ鐢ㄦ埛鑿滃崟鎺ュ彛娣诲姞鏉冮檺杩囨护
-2.3 寮�鍙戣矾寰勫垎鏋愬姛鑳芥帴鍙�
-2.4 椤圭洰绠＄悊娣诲姞鏍规嵁鍦扮悊浣嶇疆鏌ヨ鍥�/鐪�/甯�/鍘夸俊鎭帴鍙�
-2.5 寮�鍙戞牴鎹崟浣岻D銆佽祫鏂欑被鍒�侀」鐩悕绉版煡璇㈠厓鏁版嵁鎺ュ彛
-2.6 寮�鍙戦�掑綊鏌ヨ澶氫釜鐩綍ID闆嗗悎鐨勫瓨鍌ㄨ繃绋�
-2.7 璧勬枡棣嗘坊鍔犳煡璇㈠熀纭�鍦扮悊绫诲埆銆佹煡璇笟鍔＄被鍒�佹煡璇㈤」鐩悕绉版帴鍙�
-2.8 鏁版嵁涓婁紶鍚庢坊鍔燝DB琛ㄧ殑鍥惧眰淇℃伅璁板綍
-2.9 杩佺ЩWebSDK鐨勮鍙湇鍔★紝鍑忓皯1涓珯鐐圭殑閮ㄧ讲
-2.10 瑙ｅ喅鍏冩暟鎹鐞嗘煡璇㈣〃鏁版嵁鎺ュ彛寮傚父
-
-3 绯荤粺鍙戝竷锛�
-3.1 閲嶆柊閮ㄧ讲绠￠亾鍩虹澶ф暟鎹钩鍙�
-3.2 淇敼閮ㄧ讲閰嶇疆锛屽厑璁稿叕缃戝拰鍐呯綉鍚屾椂璁块棶
-3.3 淇敼涓夌淮婕旂ず鏂规鍔熻兘
-3.4 瑙ｅ喅绠＄嚎鍒嗘瀽鐨勪笅杞藉姛鑳藉紓甯�
-3.5 瑙ｅ喅鍏冩暟鎹枃浠舵墦鍖呬笅杞藉紓甯�
-
-涓嬪懆锛�
-Web寮�鍙戯細
-1.淇敼璧勬枡棣嗙殑鏌ヨ妯″潡锛屾坊鍔犺〃缁熻鍔熻兘
-2.鏁版嵁涓婁紶娣诲姞甯﹂檮浠舵枃浠舵暟鎹殑涓婁紶
-3.瀹屽杽鏁版嵁鐢宠娴佺▼
-4.淇敼绯荤粺椤甸潰锛岃儗鏅垏鎹负鐧借壊涓婚
-
-鏈嶅姟寮�鍙�
-1.鏁版嵁涓婁紶寮�鍙慚PT鏂囦欢鑷姩鍙戝竷鍔熻兘
-2.寮�鍙戞牴鎹煡璇㈡潯浠剁粺璁¤〃璁板綍鏁扮殑瀛樺偍杩囩▼
-3.鎵╁睍鏁版嵁搴撹〃鏌ヨ鎺ュ彛锛屼互鏀寔澶嶆潅杩囨护鏉′欢
-
-绯荤粺鍙戝竷锛�
-1.鍙戝竷WMTS搴曞浘鏈嶅姟
-=========================================
diff --git "a/data/\346\234\215\345\212\241\345\234\260\345\235\200.txt" "b/data/\346\234\215\345\212\241\345\234\260\345\235\200.txt"
deleted file mode 100644
index a7db8a1..0000000
--- "a/data/\346\234\215\345\212\241\345\234\260\345\235\200.txt"
+++ /dev/null
@@ -1,33 +0,0 @@
------------------------------------------ 鍐呯綉鍦板潃 -----------------------------------------
-褰卞儚鍥撅細http://192.168.20.205/LFData/2d/tiles/img/{z}/{x}/{y}.png
- 绀轰緥锛歨ttp://192.168.20.205/LFData/2d/tiles/img/1/1/1.png
-
-绾垮垝鍥撅細http://192.168.20.205/LFData/2d/tiles/vec/{z}/{x}/{y}.png
- 绀轰緥锛歨ttp://192.168.20.205/LFData/2d/tiles/vec/1/1/1.png
-
-WMS鏈嶅姟锛歨ttp://192.168.20.205:8088/geoserver/LF/wms?service=WMS&version=1.1.1&request=GetCapabilities
- 绀轰緥锛歨ttp://192.168.20.205:8088/geoserver/LF/wms?service=WMS&version=1.1.0&request=GetMap&layers=LF:b_borehole&bbox=81.36712646484375,4.14111328125,132.69232177734375,49.16168212890625&width=768&height=673&srs=EPSG:4490&styles=&format=image/png
-
-楂樼▼鏈嶅姟锛歨ttp://192.168.20.205/LFData/dem
- 绀轰緥锛歨ttp://192.168.20.205/LFData/dem/layer.json
-
-WMTS锛歨ttp://192.168.20.106:12316/server/wmts/select/token/WMTSCapabilities.xml
-
-MPT锛歝hina_5m.397536.mpt@192.168.20.205/SG
-
------------------------------------------ 澶栫綉鍦板潃 -----------------------------------------
-褰卞儚鍥撅細http://103.85.165.99:8050/LFData/2d/tiles/img/{z}/{x}/{y}.png
- 绀轰緥锛歨ttp://103.85.165.99:8050/LFData/2d/tiles/img/1/1/1.png
-
-绾垮垝鍥撅細http://103.85.165.99:8050/LFData/2d/tiles/vec/{z}/{x}/{y}.png
- 绀轰緥锛歨ttp://103.85.165.99:8050/LFData/2d/tiles/vec/1/1/1.png
-
-WMS鏈嶅姟锛歨ttp://103.85.165.99:8052/geoserver/LF/wms?service=WMS&version=1.1.1&request=GetCapabilities
- 绀轰緥锛歨ttp://103.85.165.99:8052/geoserver/LF/wms?service=WMS&version=1.1.0&request=GetMap&layers=LF:b_borehole&bbox=81.36712646484375,4.14111328125,132.69232177734375,49.16168212890625&width=768&height=673&srs=EPSG:4490&styles=&format=image/png
-
-楂樼▼鏈嶅姟锛歨ttp://103.85.165.99:8050/LFData/dem
- 绀轰緥锛歨ttp://103.85.165.99:8050/LFData/dem/layer.json
-
-WMTS锛歨ttp://103.85.165.99:8052/server/wmts/select/token/WMTSCapabilities.xml
-
-MPT锛歝hina_5m.397536.mpt@103.85.165.99:8050/SG
diff --git "a/data/\346\234\215\345\212\241\350\260\203\347\224\250\351\207\217\347\273\237\350\256\241.docx" "b/data/\346\234\215\345\212\241\350\260\203\347\224\250\351\207\217\347\273\237\350\256\241.docx"
deleted file mode 100644
index 45c5419..0000000
--- "a/data/\346\234\215\345\212\241\350\260\203\347\224\250\351\207\217\347\273\237\350\256\241.docx"
+++ /dev/null
Binary files differ
diff --git "a/data/\346\234\215\345\212\241\350\260\203\347\224\250\351\207\217\347\273\237\350\256\241.xlsx" "b/data/\346\234\215\345\212\241\350\260\203\347\224\250\351\207\217\347\273\237\350\256\241.xlsx"
deleted file mode 100644
index 6f78040..0000000
--- "a/data/\346\234\215\345\212\241\350\260\203\347\224\250\351\207\217\347\273\237\350\256\241.xlsx"
+++ /dev/null
Binary files differ
diff --git "a/data/\347\224\250\346\210\267\346\265\201\351\207\217\347\273\237\350\256\241.docx" "b/data/\347\224\250\346\210\267\346\265\201\351\207\217\347\273\237\350\256\241.docx"
deleted file mode 100644
index bb45eb1..0000000
--- "a/data/\347\224\250\346\210\267\346\265\201\351\207\217\347\273\237\350\256\241.docx"
+++ /dev/null
Binary files differ
diff --git "a/data/\347\224\250\346\210\267\346\265\201\351\207\217\347\273\237\350\256\241.xlsx" "b/data/\347\224\250\346\210\267\346\265\201\351\207\217\347\273\237\350\256\241.xlsx"
deleted file mode 100644
index 6e64604..0000000
--- "a/data/\347\224\250\346\210\267\346\265\201\351\207\217\347\273\237\350\256\241.xlsx"
+++ /dev/null
Binary files differ
diff --git "a/data/\350\241\250\347\273\223\346\236\20420221128.xlsx" "b/data/\350\241\250\347\273\223\346\236\20420221128.xlsx"
deleted file mode 100644
index e6b533f..0000000
--- "a/data/\350\241\250\347\273\223\346\236\20420221128.xlsx"
+++ /dev/null
Binary files differ
diff --git "a/data/\350\241\250\347\273\223\346\236\204\345\217\230\346\233\264\350\257\264\346\230\216.txt" "b/data/\350\241\250\347\273\223\346\236\204\345\217\230\346\233\264\350\257\264\346\230\216.txt"
deleted file mode 100644
index ba3cedd..0000000
--- "a/data/\350\241\250\347\273\223\346\236\204\345\217\230\346\233\264\350\257\264\346\230\216.txt"
+++ /dev/null
@@ -1,31 +0,0 @@
-1.鐩綍琛� lf.sys_dir 鍜� 銆佸崟浣嶈〃 lf.sys_dep 娣诲姞浜� code 缂栫爜瀛楁锛屼紭鍖栭�掑綊鏌ヨ閫熷害锛岃〃缁撴瀯鍙戠敓浜嗗彉鍖栥��
-
-2.bd銆乥s銆乵d鏋舵瀯涓嬬殑鎵�鏈変笟鍔¤〃鐨� depid銆乨irid锛岀敱鏁存暟绫诲瀷鏀逛负瀛楃涓茬被鍨嬶紝depid 瀵瑰簲 lf.sys_dep.code锛宒irid 瀵瑰簲 lf.sys_dir.code銆�
-
-3.鐢ㄦ埛琛� lf.sys_user 娣诲姞浜� depcode锛屽搴� lf.sys_dep.code锛屼负鍑忓皯鍙樻洿 lf.sys_user.depid 浠嶇劧瀛樺湪銆�
-
-4.鍏冩暟鎹〃 lf.sys_meta 鐨� depid锛堟暣鏁帮級 鏀逛负 depcode锛堝瓧绗︼級瀵瑰簲 lf.sys_dep.code锛宒irid锛堟暣鏁帮級 鏀逛负 dircode锛堝瓧绗︼級瀵瑰簲 lf.sys_dir.code銆�
-娉細鏃у悕涓� 鍏冩暟鎹紝搴旂敤鎴疯姹傛敼涓� 鍏冩暟鎹��
-
-5.瀹屾暣鍚嶇О锛�
--- 鏌ヨ鍗曚綅瀹屾暣鍚嶏紝00030701 涓� lf.sys_dep.id = 55 鐨刢ode鍊�
-select fn_rec_query(55, 'dep'); 鍙互鏀逛负 select fn_get_fullname('00030701', 1);
-
--- 鏌ヨ鐩綍瀹屾暣鍚嶏紝01000000020000 涓� lf.sys_dir.id = 90 鐨刢ode鍊�
-select fn_rec_query(90, 'dir'); 鍙互鏀逛负 select fn_get_fullname('01000000020000', 2);
-
-6.閫掑綊鏌ヨ浼樺寲锛堜互鏌ヨ鍏冩暟鎹负渚嬶級锛�
-鏃э細select * from lf.sys_meta where depid = ANY(fn_rec_array(1, 'dep'));
-鏂帮細select * from lf.sys_meta where depcode like '00%'; -- 00 涓� lf.sys_dep.id = 1 鐨刢ode
-
-鏃э細select * from lf.sys_meta where dirid = ANY(fn_rec_array(57, 'dir'));
-鏂帮細select * from lf.sys_meta where dircode like '01%'; -- 01 涓� lf.sys_dir.id = 57 鐨刢ode
-
-娉細涓氬姟鏁版嵁琛ㄤ篃绫讳技锛屽锛�
-select * from bd.dlg_agnp where depid like '00%'; -- 瀛楁涓嶅彉
-select * from bd.dlg_agnp where dirid like '01%'; -- 瀛楁涓嶅彉
-
-7.Excel鍏ュ簱淇敼锛屽嵆 datax/task/run/琛ㄦ牸鍏ュ簱 鐨勫弬鏁板彉鏇达細
-dirid 锛� 鍙傛暟鍊硷紙绀轰緥锛夌敱 10,11 鏀逛负 0A,0B锛屽叾瀵瑰簲琛ㄥ瓧娈电被鍨嬬敱 鏁存暟 鏀逛负 瀛楃涓层��
-depid 锛屽弬鏁板�硷紙绀轰緥锛夌敱 1,2 鏀逛负 01,02锛屽叾瀵瑰簲琛ㄥ瓧娈电被鍨嬬敱 鏁存暟 鏀逛负 瀛楃涓层��
-
diff --git "a/data/\350\256\241\345\210\2220929.xlsx" "b/data/\350\256\241\345\210\2220929.xlsx"
deleted file mode 100644
index 363a36c..0000000
--- "a/data/\350\256\241\345\210\2220929.xlsx"
+++ /dev/null
Binary files differ
diff --git "a/data/\350\264\250\346\243\200\351\205\215\347\275\256\350\241\250\346\200\273.xlsx" "b/data/\350\264\250\346\243\200\351\205\215\347\275\256\350\241\250\346\200\273.xlsx"
deleted file mode 100644
index 55e7a7b..0000000
--- "a/data/\350\264\250\346\243\200\351\205\215\347\275\256\350\241\250\346\200\273.xlsx"
+++ /dev/null
Binary files differ
diff --git "a/data/\350\267\257\345\276\204\350\247\204\345\210\222_PG.txt" "b/data/\350\267\257\345\276\204\350\247\204\345\210\222_PG.txt"
deleted file mode 100644
index f168840..0000000
--- "a/data/\350\267\257\345\276\204\350\247\204\345\210\222_PG.txt"
+++ /dev/null
@@ -1,137 +0,0 @@
---1锛夊垱寤烘暟鎹〃锛�
---娣诲姞璧风偣id
-
-ALTER TABLE public.lrdl ADD COLUMN source integer;
-
---娣诲姞缁堢偣id
-
-ALTER TABLE public.lrdl ADD COLUMN target integer;
-
---娣诲姞閬撹矾鏉冮噸鍊�
-
-ALTER TABLE public.lrdl ADD COLUMN length double precision;
-
---涓簊ampledata琛ㄥ垱寤烘嫇鎵戝竷灞�锛屽嵆涓簊ource鍜宼arget瀛楁璧嬪��
-
-SELECT pgr_createTopology('public.lrdl',0.0001, 'geom', 'gid');
-
---涓簊ource鍜宼arget瀛楁鍒涘缓绱㈠紩
-
-CREATE INDEX source_idx ON public.lrdl("source");
-
-CREATE INDEX target_idx ON public.lrdl("target");
-
---涓簂ength璧嬪��
-
-update public.lrdl set length =st_length(geom);
-
---涓簉oad_xblk琛ㄦ坊鍔爎everse_cost瀛楁骞剁敤length鐨勫�艰祴鍊�
-
-ALTER TABLE public.lrdl ADD COLUMN reverse_cost double precision;
-
-UPDATE public.lrdl SET reverse_cost =length;
-
-
-
---2锛夊垱寤烘渶鐭矾寰勫嚱鏁帮細
---鍒犻櫎宸插瓨鍦ㄧ殑鍑芥暟
-DROP FUNCTION pgr_fromAtoB(tbl varchar,startx float, starty float,endx float,endy float);
-
---DROP FUNCTION pgr_fromAtoB(varchar, double precision, double precision
--- double precision, double precision);
---鍩轰簬浠绘剰涓ょ偣涔嬮棿鐨勬渶鐭矾寰勫垎鏋�
-CREATE OR REPLACE FUNCTION pgr_fromAtoB(
- IN tbl varchar,--鏁版嵁搴撹〃鍚�
- IN x1 double precision,--璧风偣x鍧愭爣
- IN y1 double precision,--璧风偣y鍧愭爣
- IN x2 double precision,--缁堢偣x鍧愭爣
- IN y2 double precision,--缁堢偣y鍧愭爣
- OUT seq integer,--閬撹矾搴忓彿
- OUT gid integer,
- OUT name text,--閬撹矾鍚�
- OUT heading double precision,
- OUT cost double precision,--娑堣��
- OUT geom geometry--閬撹矾鍑犱綍闆嗗悎
- )
-RETURNS SETOF record AS
-$BODY$
-DECLARE
-sql text;
-rec record;
-source integer;
-target integer;
-point integer;
-
-BEGIN
--- 鏌ヨ璺濈鍑哄彂鐐规渶杩戠殑閬撹矾鑺傜偣
-EXECUTE 'SELECT id::integer FROM '|| quote_ident(tbl) ||'_vertices_pgr
- ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
- || x1 || ' ' || y1 || ')'',4490) LIMIT 1' INTO rec;
- source := rec.id;
-
- -- 鏌ヨ璺濈鐩殑鍦版渶杩戠殑閬撹矾鑺傜偣
-EXECUTE 'SELECT id::integer FROM '|| quote_ident(tbl) ||'_vertices_pgr
- ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
- || x2 || ' ' || y2 || ')'',4490) LIMIT 1' INTO rec;
- target := rec.id;
-
--- 鏈�鐭矾寰勬煡璇�
-seq := 0;
-sql := 'SELECT gid, geom, node as name, cost, source, target,
- ST_Reverse(geom) AS flip_geom FROM ' ||
- 'pgr_dijkstra(''SELECT gid as id,
- source::integer,target::integer,'
- || 'length::float AS cost FROM '
- || quote_ident(tbl) || ''', '
- || source || ', ' || target
- || ' ,false) as di, '
- || quote_ident(tbl) || ' WHERE di.edge = gid ORDER BY seq';
-
-
--- Remember start point
-point := source;
-
-FOR rec IN EXECUTE sql
- LOOP
- -- Flip geometry (if required)
- IF ( point != rec.source ) THEN
- rec.geom := rec.flip_geom;
- point := rec.source;
- ELSE
- point := rec.target;
- END IF;
-
- -- Calculate heading (simplified)
- EXECUTE 'SELECT degrees( ST_Azimuth(
- ST_StartPoint(''' || rec.geom::text || '''),
- ST_EndPoint(''' || rec.geom::text || ''') ) )'
- INTO heading;
-
- -- Return record
- seq := seq + 1;
- gid := rec.gid;
- name := rec.name;
- cost := rec.cost;
- geom := rec.geom;
- RETURN NEXT;
- END LOOP;
- RETURN;
- END;
-$BODY$
-LANGUAGE 'plpgsql' VOLATILE STRICT;
-
---3) 鍒涘缓鑺傜偣琛細
-select pgr_createTopology('lrdl',0.1,source:='source',id:='gid',target:='target',the_geom:='geom',rows_where:='gid < 50000')
-
-
---4锛夋祴璇曪細
---闅忎究閫変袱涓偣娴嬭瘯涓�涓嬶紝OK锛屽凡缁忚繑鍥炰簡璺緞鐨勭嚎娈靛簭鍒椼��
-
-select * from pgr_fromatob('lrdl',116.78999,39.9468,116.80458,39.94758);
-
---鍥犱负鏈�缁堟垜浠渶瑕佸皢鏈�鐭矾寰勪紶鍒版闈㈢鏄剧ず锛屼负浜嗘柟渚匡紝浣跨敤ST_Union灏嗚繖浜涚嚎娈靛悎骞舵垚涓�涓畬鏁寸殑绾胯矾銆備娇鐢ㄦ柟娉曞涓�
-select ST_Union(geom) as route from pgr_fromAtoB('lrdl'::text,116.78999,39.9468,116.80458,39.94758) ;
-
---鍚堝苟鐨勮矾寰勬垜浠湅涓嶅嚭鏉ユ槸浠�涔堬紝涓嶈繃灏嗕粬杞垚wkt灏辫兘鐞嗚В浜嗭細
-
-select ST_astext(ST_Union(geom)) as route from pgr_fromAtoB('lrdl'::text,116.78999,39.9468,116.80458,39.94758) ;
diff --git "a/data/\351\233\206\346\210\220\347\224\250\346\210\267\346\211\200\351\234\200\346\216\245\345\217\243.docx" "b/data/\351\233\206\346\210\220\347\224\250\346\210\267\346\211\200\351\234\200\346\216\245\345\217\243.docx"
deleted file mode 100644
index 764de3d..0000000
--- "a/data/\351\233\206\346\210\220\347\224\250\346\210\267\346\211\200\351\234\200\346\216\245\345\217\243.docx"
+++ /dev/null
Binary files differ
diff --git "a/data/\351\241\271\347\233\256\345\256\236\346\226\275\350\256\241\345\210\222\357\274\210\350\236\215\345\220\210\357\274\211v03.xlsx" "b/data/\351\241\271\347\233\256\345\256\236\346\226\275\350\256\241\345\210\222\357\274\210\350\236\215\345\220\210\357\274\211v03.xlsx"
deleted file mode 100644
index b1eeda2..0000000
--- "a/data/\351\241\271\347\233\256\345\256\236\346\226\275\350\256\241\345\210\222\357\274\210\350\236\215\345\220\210\357\274\211v03.xlsx"
+++ /dev/null
Binary files differ
diff --git "a/data/\351\241\271\347\233\256\345\256\236\346\226\275\350\256\241\345\210\222\357\274\210\350\236\215\345\220\210\357\274\211v2.0.xlsx" "b/data/\351\241\271\347\233\256\345\256\236\346\226\275\350\256\241\345\210\222\357\274\210\350\236\215\345\220\210\357\274\211v2.0.xlsx"
deleted file mode 100644
index 1a784c5..0000000
--- "a/data/\351\241\271\347\233\256\345\256\236\346\226\275\350\256\241\345\210\222\357\274\210\350\236\215\345\220\210\357\274\211v2.0.xlsx"
+++ /dev/null
Binary files differ
--
Gitblit v1.9.3