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;
----------------------------------------------------------------------------------------------------- 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 '父ID：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 '创建人ID';
comment on column lf.sys_menu.create_time is '创建时间';
comment on column lf.sys_menu.update_user is '更新人ID';
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(50),
  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 '父ID：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 '创建人ID';
comment on column lf.sys_dep.create_time is '创建时间';
comment on column lf.sys_dep.update_user is '更新人ID';
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),
  code varchar(50),
  descr 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_dir is '目录表';
comment on column lf.sys_dir.id is '主键ID';
comment on column lf.sys_dir.pid is '父ID：0-根节点';
comment on column lf.sys_dir.name is '名称';
comment on column lf.sys_dir.code is '目录编码';
comment on column lf.sys_dir.descr is '目录说明';
comment on column lf.sys_dir.level is '层级：0-根节点';
comment on column lf.sys_dir.order_num is '排序号';
comment on column lf.sys_dir.create_user is '创建人ID';
comment on column lf.sys_dir.create_time is '创建时间';
comment on column lf.sys_dir.update_user is '更新人ID';
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,
  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.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 '创建人ID';
comment on column lf.sys_user.create_time is '创建时间';
comment on column lf.sys_user.update_user is '更新人ID';
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 '登记人ID';
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 '操作人ID';
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 '创建人ID';
comment on column lf.sys_token.create_time is '创建时间';
comment on column lf.sys_token.update_user is '更新人ID';
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 '创建人ID';
comment on column lf.sys_res.create_time is '创建时间';
comment on column lf.sys_res.update_user is '更新人ID';
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 '操作人ID';
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 '创建人ID';
comment on column lf.sys_auth.create_time is '创建时间';
comment on column lf.sys_auth.update_user is '更新人ID';
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-数据管理员';
comment on column lf.sys_role.create_user is '创建人ID';
comment on column lf.sys_role.create_time is '创建时间';
comment on column lf.sys_role.update_user is '更新人ID';
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 '创建人ID';
comment on column lf.sys_role_user.create_time is '创建时间';
comment on column lf.sys_role_user.update_user is '更新人ID';
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 '创建人ID';
comment on column lf.sys_role_res.create_time is '创建时间';
comment on column lf.sys_role_res.update_user is '更新人ID';
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 '创建人ID';
comment on column lf.sys_menu_auth.create_time is '创建时间';
comment on column lf.sys_menu_auth.update_user is '更新人ID';
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 '创建人ID';
comment on column lf.sys_role_menu_auth.create_time is '创建时间';
comment on column lf.sys_role_menu_auth.update_user is '更新人ID';
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,
  dirid integer default 0,
  depid integer default 0,
  verid integer 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 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.dirid is '目录ID';
comment on column lf.sys_meta.depid is '单位ID';
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-文件，shp-ShapeFile，gdb-GDB，zip-压缩包';
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 '大小：单位MB';
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 '创建人ID';
comment on column lf.sys_meta.create_time is '创建时间';
comment on column lf.sys_meta.update_user is '更新人ID';
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 '表ID';
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 '文件大小：单位MB';
comment on column lf.sys_attach.create_user is '创建人ID';
comment on column lf.sys_attach.create_time is '创建时间';
comment on column lf.sys_attach.update_user is '更新人ID';
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 '创建人ID';
comment on column lf.sys_ver.create_time is '创建时间';
comment on column lf.sys_ver.update_user is '更新人ID';
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,
  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 '创建人ID';
comment on column lf.sys_dict.create_time is '创建时间';
comment on column lf.sys_dict.update_user is '更新人ID';
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 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 '创建人ID';
comment on column lf.sys_style.create_time is '创建时间';
comment on column lf.sys_style.update_user is '更新人ID';
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 default null,
  bak varchar(1024)
  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)
);
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-管道分析';
comment on column lf.sys_download.depid is '单位ID';
comment on column lf.sys_download.sizes is '文件大小：单位MB';
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 '创建人ID';
comment on column lf.sys_download.create_time is '创建时间';
comment on column lf.sys_download.download_user is '下载人ID';
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;
----------------------------------------------------------------------------------------------------- 22.消息通知表
-- DROP TABLE IF EXISTS lf.sys_msg;
create table lf.sys_msg(
  id serial primary key,
  title varchar(50),
  msg varchar(200),
  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_msg is '消息通知表';
comment on column lf.sys_msg.id is '主键ID';
comment on column lf.sys_msg.title is '标题';
comment on column lf.sys_msg.msg is '消息内容';
comment on column lf.sys_msg.guid is '消息ID';
comment on column lf.sys_msg.create_user is '创建人ID';
comment on column lf.sys_msg.create_time is '创建时间';
comment on column lf.sys_msg.update_user is '接受人ID';
comment on column lf.sys_msg.update_time is '接受时间';
--insert into lf.sys_msg (title,msg,guid,update_user) values ('2022-09-26','数据下载',new_guid(),2);

select * from lf.sys_msg;
----------------------------------------------------------------------------------------------------- 23.参数设置表
-- 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 '创建人ID';
comment on column lf.sys_args.create_time is '创建时间';
comment on column lf.sys_args.update_user is '更新人ID';
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;
----------------------------------------------------------------------------------------------------- 24.黑名单表
-- 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 '创建人ID';
comment on column lf.sys_blacklist.create_time is '创建时间';
comment on column lf.sys_blacklist.update_user is '更新人ID';
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;
----------------------------------------------------------------------------------------------------- 25.标绘表
-- 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 '创建人ID';
comment on column lf.sys_mark.create_time is '创建时间';
comment on column lf.sys_mark.update_user is '更新人ID';
comment on column lf.sys_mark.update_time is '更新时间';

select * from lf.sys_mark;
----------------------------------------------------------------------------------------------------- 26.值域表
-- 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 '创建人id';
comment on column lf.sys_domain.create_time is '创建时间';
comment on column lf.sys_domain.update_user is '更新人id';
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;
----------------------------------------------------------------------------------------------------- 27.报告模板表
-- 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 '创建人id';
comment on column lf.sys_report.create_time is '创建时间';
comment on column lf.sys_report.update_user is '更新人id';
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;
----------------------------------------------------------------------------------------------------- 28.下载日志表
-- 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 '创建人id';
comment on column lf.sys_downlog.create_time is '创建时间';
comment on column lf.sys_downlog.update_user is '更新人id';
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;
----------------------------------------------------------------------------------------------------- 29.数据申请表
-- drop table if exists lf.sys_apply;
create table lf.sys_apply (
    id serial primary key,
	userid integer default 0,
	depids varchar(500),
	tabs varchar(2000),
	entities varchar(2000),
	wkt varchar(4000),
	pwd varchar(200),
	status integer default 0,
	count 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_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.depids is '单位ID';
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.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.create_user is '创建人id';
comment on column lf.sys_apply.create_time is '创建时间';
comment on column lf.sys_apply.update_user is '更新人id';
comment on column lf.sys_apply.update_time is '更新时间';

select * from lf.sys_apply;
----------------------------------------------------------------------------------------------------- 30.申请流程表
-- 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 '创建人id';
comment on column lf.sys_flow.create_time is '创建时间';
comment on column lf.sys_flow.update_user is '更新人id';
comment on column lf.sys_flow.update_time is '更新时间';

select * from lf.sys_flow;
----------------------------------------------------------------------------------------------------- 31.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()
);
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 '创建时间';

select * from lf.sys_fme_log; -- delete from lf.sys_fme_log;
----------------------------------------------------------------------------------------------------- 32.图层表
-- DROP TABLE IF EXISTS lf.sys_layer;
create table lf.sys_layer(
  id serial primary key,
  pid 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,
  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 '父ID：0-根节点';
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.create_user is '创建人ID';
comment on column lf.sys_layer.create_time is '创建时间';
comment on column lf.sys_layer.update_user is '更新人ID';
comment on column lf.sys_layer.update_time is '更新时间';
comment on column lf.sys_layer.bak is '备注';

select * from lf.sys_layer;
----------------------------------------------------------------------------------------------------- 33.服务日志表
-- 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 '访问人ID';
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;
----------------------------------------------------------------------------------------------------- vacuum analyze md.md_zxcg;