|
--------------------------------------------------------- 补充字段
|
-- 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，bs.* 284，lf.* 34，md.* 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", (select sum(sizes) from lf.sys_meta b where b.dircode like a.code || '%') "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 ('测绘（ESV）', '勘察（EGE）', '地灾（EGD）', '洞库（EGD）') 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 ('基础测绘', '基础地灾', '基础勘察', '合规数据', '管理数据', '测绘（ESV）', '勘察（EGE）', '地灾（EGD）', '洞库（EGD）')
|
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 ('基础测绘', '基础地灾', '基础勘察', '合规数据', '管理数据', '测绘（ESV）', '勘察（EGE）', '地灾（EGD）', '洞库（EGD）')
|
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 ('基础地灾', '基础勘察', '合规数据', '管理数据', '测绘（ESV）', '勘察（EGE）', '地灾（EGD）', '洞库（EGD）')
|
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：gid >= 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：POINT(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：MULTILINESTRING((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：MULTIPOLYGON(((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';
|
---------------------------------------------------------
|
