11

13693261870

2022-12-21 6355ba4d8a6edc7afb125063ddbe89126a1af784

11

 data/db_fn.sql

@@ -3,69 +3,48 @@
select count(1) from pg_stat_activity;

select c.relname "tab", cast(obj_description(c.oid) as varchar) "desc", a.attnum "num", a.attname "col",
t.typname "type",concat_ws('', t.typname,SUBSTRING(format_type(a.atttypid, a.atttypmod) from '(.*)')) "type2", d.description "bak"
-- select *
t.typname "type",concat_ws('', t.typname,SUBSTRING(format_type(a.atttypid, a.atttypmod) from '(.*)')) "type2", d.description "bak" -- select *
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 reltype>0 and relnamespace in (29257,20582)--135502,69701
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';
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';
----------------------------------------------------------------------------------------------------- a.递归查询
--------------------------------------------------------- 查询菜单
select * from lf.sys_menu order by order_num;

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;
--------------------------------------------------------- 查询单位
select * from lf.sys_dep order by order_num;
) 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;
--------------------------------------------------------- 数据目录
select * from lf.sys_dir order by order_num;
) 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;
) select * FROM rs order by order_num; -- 数据目录
----------------------------------------------------------------------------------------------------- b.查询表结构
select * from pg_tables;
select * from pg_class order by relnamespace;
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
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; 
----------------------------------------------------------------------------------------------------- c.GUID生成函数
CREATE or REPLACE FUNCTION new_guid()
RETURNS "pg_catalog"."varchar" AS $BODY$
DECLARE
create or replace function new_guid()
returns "pg_catalog"."varchar" as $body$
declare
  v_seed_value varchar(32);
BEGIN
begin
  select md5(inet_client_addr()::varchar || timeofday() || inet_server_addr()::varchar || to_hex(inet_client_port()))
    into v_seed_value;

@@ -74,8 +53,8 @@
          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;
end;
$body$ language 'plpgsql' volatile security definer;

select new_guid();
----------------------------------------------------------------------------------------------------- d.递归查询函数
@@ -107,12 +86,7 @@
$$ language plpgsql;

select fn_rec_query(1, 'dep'); select fn_rec_query(21, 'dep');
select * from lf.sys_dep order by id;

select fn_rec_query(10, 'dir'); select fn_rec_query(28, 'dir');
select * from lf.sys_dir order by id;

select a.*, fn_rec_query(a.depid, 'dep') depName from lf.sys_user a order by a.id;
----------------------------------------------------------------------------------------------------- e.查询用户名
-- execute format('select uname from lf.sys_user where id = %s', id) into str;
-- drop function fn_uname(id integer);
@@ -131,8 +105,7 @@
  end;
$$ language plpgsql;

select fn_uname(null);
select uname from lf.sys_user where id = 1 limit 1;
select fn_uname(null); select fn_uname(1);
----------------------------------------------------------------------------------------------------- e.查询版本名
-- drop function fn_ver(id integer);
create or replace function fn_ver(id integer)
@@ -151,7 +124,6 @@
$$ language plpgsql;

select fn_ver(0);
select a.*,fn_ver(a.verid) ver from lf.sys_meta a;
----------------------------------------------------------------------------------------------------- f.递归查询ID数组
-- drop function fn_rec_array(id integer, tab varchar);
create or replace function fn_rec_array(id integer, tab varchar)
@@ -172,8 +144,7 @@
  end;
$$ language plpgsql;

select fn_rec_array(1, 'dep');
select fn_rec_array(10, 'dir');
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'));
----------------------------------------------------------------------------------------------------- g.获取实体名
create or replace function fn_get_entity(tab varchar)
@@ -226,7 +197,7 @@
----------------------------------------------------------------------------------------------------- i.联合查询
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 * 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'))
@@ -360,15 +331,22 @@
select * from bd.dlg_25w_boul where gid<11; -- delete from bd.dlg_25w_boul where gid>10; alter sequence bd.dlg_25w_boul_gid_seq restart with 20;
select * from bd.dlg_25w_resa where gid<12; -- delete from bd.dlg_25w_resa where gid>11; alter sequence bd.dlg_25w_resa_gid_seq restart with 20;
select * from bd.dlg_agnp where gid<13;     -- delete from bd.dlg_agnp where gid>12;     alter sequence bd.dlg_agnp_gid_seq restart with 20;
select gb,name,classes,pinyin,pac,bsm,geom,eventid,dirid,depid,verid,createtime,createuser,updateuser,updatetime from bd.dlg_agnp

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 10;
select gb,name,classes,pinyin,pac,bsm,geom,eventid,dirid,depid,verid,createtime,createuser,updateuser,updatetime from bd.dlg_agnp;
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 10;

select ns,tab,tab_desc,field,type from lf.sys_dict;
select type from lf.sys_dict group by type;
select * from lf.sys_dict where ns='bd' and tab='dlg_agnp';
select * from lf.sys_token where create_time >= '2022-09-29' and create_time <= '2022-09-30';












 src/main/java/com/lf/server/controller/all/BaseQueryController.java

@@ -225,6 +225,33 @@
    }

    @SysLog()
    @ApiOperation(value = "根据表名分页查询表")
    @GetMapping(value = "/selectTabsByPage")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "tab", value = "表名", dataType = "String", paramType = "query", example = "dlg_"),
            @ApiImplicitParam(name = "pageSize", value = "每页条数", dataType = "Integer", paramType = "query", example = "10"),
            @ApiImplicitParam(name = "pageIndex", value = "分页数（从1开始）", dataType = "Integer", paramType = "query", example = "1")
    })
    public ResponseMsg<List<TabEntity>> selectTabsByPage(String tab, Integer pageSize, Integer pageIndex) {
        try {
            if (pageSize < 1 || pageIndex < 1) {
                return fail("每页页数或分页数小于1", null);
            }

            int count = baseQueryService.selectTabsForCount(tab);
            if (count == 0) {
                return success(0, null);
            }

            List<TabEntity> rs = baseQueryService.selectTabsByPage(tab, pageSize, pageSize * (pageIndex - 1));

            return success(count, rs);
        } catch (Exception ex) {
            return fail(ex.getMessage(), null);
        }
    }

    @SysLog()
    @ApiOperation(value = "查询字段信息")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "name", value = "映射名称", dataType = "String", paramType = "query", example = "dlg25wAanp")

 src/main/java/com/lf/server/mapper/all/BaseQueryMapper.java

@@ -40,6 +40,24 @@
    public List<TabEntity> selectTabs();

    /**
     * 根据表名查询记录数
     *
     * @param tab 表名
     * @return 记录数
     */
    public Integer selectTabsForCount(String tab);

    /**
     * 根据表名分页查询
     *
     * @param tab    表名
     * @param limit  记录数
     * @param offset 偏移量
     * @return
     */
    public List<TabEntity> selectTabsByPage(String tab, Integer limit, Integer offset);

    /**
     * 查询字段信息
     *
     * @param ns  名称空间

 src/main/java/com/lf/server/mapper/data/DictMapper.java

@@ -28,7 +28,7 @@
     *
     * @param ns     表空间
     * @param tab    表名
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/data/DomainMapper.java

@@ -30,7 +30,7 @@
     * @param ns     名称空间
     * @param tab    表名
     * @param name   名称
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/data/DownloadMapper.java

@@ -25,7 +25,7 @@
     * 分页查询
     *
     * @param name   名称
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */
@@ -47,7 +47,7 @@
     * @param createUser 用户ID
     * @param type       类别
     * @param name       名称
     * @param limit      记录表
     * @param limit      记录数
     * @param offset     偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/data/MetaFileMapper.java

@@ -25,7 +25,7 @@
     * 分页查询
     *
     * @param name   文件名
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/data/MetaMapper.java

@@ -28,7 +28,7 @@
     *
     * @param dirid  目录ID
     * @param name   名称
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */
@@ -50,7 +50,7 @@
     * @param name       名称
     * @param createUser 用户ID
     * @param types      文件类型
     * @param limit      记录表
     * @param limit      记录数
     * @param offset     偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/data/StyleMapper.java

@@ -26,7 +26,7 @@
     * 分页查询
     *
     * @param name   样式名
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/data/VerMapper.java

@@ -28,7 +28,7 @@
     *
     * @param name 名称
     * @param depName 名称
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/show/MarkMapper.java

@@ -25,7 +25,7 @@
     * 分页查询
     *
     * @param uid    用户ID
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/sys/ArgsMapper.java

@@ -25,7 +25,7 @@
     * 分页查询
     *
     * @param name   名称
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/sys/AttachMapper.java

@@ -25,7 +25,7 @@
     * 分页查询
     *
     * @param name   文件名
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/sys/AuthMapper.java

@@ -26,7 +26,7 @@
     * 分页查询
     *
     * @param name   名称
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/sys/BlacklistMapper.java

@@ -27,7 +27,7 @@
     *
     * @param ip     IP地址
     * @param type   类别
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/sys/MenuAuthMapper.java

@@ -25,7 +25,7 @@
     * 分页查询
     *
     * @param menuid 菜单ID
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/sys/MsgMapper.java

@@ -25,7 +25,7 @@
     * 分页查询
     *
     * @param title   标题
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/sys/ResMapper.java

@@ -26,7 +26,7 @@
     * 分页查询
     *
     * @param name   表名
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/sys/RoleMapper.java

@@ -28,7 +28,7 @@
     *
     * @param name   名称
     * @param depid  单位ID
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/sys/RoleMenuAuthMapper.java

@@ -25,7 +25,7 @@
     * 分页查询
     *
     * @param roleid 角色ID
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/sys/RoleResMapper.java

@@ -25,7 +25,7 @@
     * 分页查询
     *
     * @param roleid   角色ID
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/sys/RoleUserMapper.java

@@ -25,7 +25,7 @@
     * 分页查询
     *
     * @param roleid   角色ID
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/mapper/sys/UserMapper.java

@@ -26,7 +26,7 @@
     * 分页查询
     *
     * @param uname  用戶名
     * @param limit  记录表
     * @param limit  记录数
     * @param offset 偏移量
     * @return 列表
     */

 src/main/java/com/lf/server/service/all/BaseQueryService.java

@@ -222,6 +222,20 @@
    }

    @Override
    public Integer selectTabsForCount(String tab) {
        tab = StringHelper.getLikeStr(tab);

        return baseQueryMapper.selectTabsForCount(tab);
    }

    @Override
    public List<TabEntity> selectTabsByPage(String tab, Integer limit, Integer offset) {
        tab = StringHelper.getLikeStr(tab);

        return baseQueryMapper.selectTabsByPage(tab, limit, offset);
    }

    @Override
    public List<DictEntity> selectFields(String ns, String tab) {
        return baseQueryMapper.selectFields(ns, tab);
    }

 src/main/resources/mapper/all/BaseQueryMapper.xml

@@ -25,6 +25,26 @@
      select distinct ns,tab,fn_get_entity(tab) entity,tab_desc,tableType from lf.sys_dict order by tab
    </select>

    <select id="selectTabsForCount" resultType="java.lang.Integer">
        select count(distinct tab) from lf.sys_dict
        <where>
            <if test="tab != null">
                tab like #{tab} or tab_desc like #{tab}
            </if>
        </where>
    </select>

    <select id="selectTabsByPage" resultType="com.lf.server.entity.ctrl.TabEntity">
      select distinct ns,tab,fn_get_entity(tab) entity,tab_desc,tableType from lf.sys_dict
      <where>
        <if test="tab != null">
            tab like #{tab} or tab_desc like #{tab}
        </if>
      </where>
      order by tab
      limit #{limit} offset #{offset}
    </select>

    <select id="selectFields" resultType="com.lf.server.entity.data.DictEntity">
      select * from lf.sys_dict where ns = #{ns} and tab = #{tab} order by order_num
    </select>