----------------------------------------------------------------------------------------------------- 1.菜单表
-- 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,
  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.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 alter column bak type varchar(1024);
--alter sequence lf.sys_menu_id_seq restart with 45;

select * from lf.sys_menu order by id;
----------------------------------------------------------------------------------------------------- 2.单位表
-- 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; alter table lf.sys_dep alter column bak type varchar(1024);

select * from lf.sys_dep order by id;
----------------------------------------------------------------------------------------------------- 3.目录表
-- 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;
----------------------------------------------------------------------------------------------------- 4.用户表
-- 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;
----------------------------------------------------------------------------------------------------- 5.登录日志表
-- 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_2022 (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_2022 (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_2022 (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_2022 (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_2022 (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');*/

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;
----------------------------------------------------------------------------------------------------- 6.操作日志表
-- 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_2022 (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_2022 (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_2022 (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_2022 (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_2022 (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');*/

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;
----------------------------------------------------------------------------------------------------- 7.令牌表
-- 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_2022 (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_2022 (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_2022 (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_2022 (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_2022 (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');*/

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';
----------------------------------------------------------------------------------------------------- 8.资源表
-- 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 ('天地图影像');

select * from lf.sys_res;
----------------------------------------------------------------------------------------------------- 9.资源操作表
-- 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_2022 (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_2022 (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_2022 (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_2022 (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_2022 (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-下载';
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');*/

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');*/

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),
  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.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 (depid,name,descr) values (1,'Admin','系统管理员');

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);

select * from lf.sys_menu_auth;
----------------------------------------------------------------------------------------------------- 14.角色-菜单-权限表
-- 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,
  dirid integer,
  depid integer,
  name varchar(256),
  format varchar(100),
  fsize bigint default 0,
  cs varchar(50),
  fscale varchar(50),
  resolution varchar(50),
  gather timestamp(6) without time zone,
  descr varchar(256),
  guid varchar(40) default null,
  create_user integer default 1,
  create_time timestamp(6) without time zone default now(),
  update_user integer,
  update_time timestamp(6) without time zone,
  geo geometry default null,
  CONSTRAINT enforce_dims_geom CHECK (st_ndims(geo) = 2),
  CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geo) = 'POLYGON'::text OR geo IS NULL),
  CONSTRAINT enforce_srid_geom CHECK (st_srid(geo) = 4490)
);
create index index_sys_meta_geo on lf.sys_meta using gist (geo);
comment on table lf.sys_meta is '元数据表';
comment on column lf.sys_meta.id is '主键ID';
comment on column lf.sys_meta.dirid is '目录ID';
comment on column lf.sys_meta.depid is '单位ID';
comment on column lf.sys_meta.name is '名称';
comment on column lf.sys_meta.format is '格式';
comment on column lf.sys_meta.fsize is '大小：单位为kb';
comment on column lf.sys_meta.cs is '坐标系';
comment on column lf.sys_meta.fscale is '比例尺';
comment on column lf.sys_meta.resolution is '分辨率';
comment on column lf.sys_meta.gather is '采集日期';
comment on column lf.sys_meta.descr is '描述';
comment on column lf.sys_meta.guid is '文件ID';
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.geo is '空间';
--insert into lf.sys_meta(dirid,depid,name,geo) values (1,1,'Test',ST_PolygonFromText('POLYGON ((100 0,120 0,120 20,100 20,100 0))',4490));

select id,dirid,depid,name,descr,guid,create_user,create_time,update_user,update_time,st_astext(geo) from lf.sys_meta;
----------------------------------------------------------------------------------------------------- 17.元数据文件表
-- DROP TABLE IF EXISTS lf.sys_meta_file;
create table lf.sys_meta_file(
  id serial primary key,
  metaid integer,
  name varchar(256),
  guid varchar(40) default new_guid(),
  path varchar(512),
  fileid 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
);
comment on table lf.sys_meta_file is '元数据文件表';
comment on column lf.sys_meta_file.id is '主键ID';
comment on column lf.sys_meta_file.metaid is '元数据ID';
comment on column lf.sys_meta_file.name is '文件名';
comment on column lf.sys_meta_file.guid is '文件ID';
comment on column lf.sys_meta_file.path is '存储路径';
comment on column lf.sys_meta_file.fileid is '父文件ID：0-没有';
comment on column lf.sys_meta_file.create_user is '创建人ID';
comment on column lf.sys_meta_file.create_time is '创建时间';
comment on column lf.sys_meta_file.update_user is '更新人ID';
comment on column lf.sys_meta_file.update_time is '更新时间';
--insert into lf.sys_meta_file (metaid,name,path) values (1,'a.jpg','c:\a.jpg');

select * from lf.sys_meta_file;
----------------------------------------------------------------------------------------------------- 18.附件表
-- DROP TABLE IF EXISTS lf.sys_attach;
create table lf.sys_attach(
  id serial primary key,
  name varchar(256),
  tab varchar(100),
  guid varchar(40) default new_guid(),
  path varchar(512),
  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.guid is '文件ID';
comment on column lf.sys_attach.path is '存储路径';
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');

select length(guid) from lf.sys_attach limit 1; select * from lf.sys_attach;
----------------------------------------------------------------------------------------------------- 19.版本表
-- 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 (dirid,name) values (1003,'2020-02-01');

select * from lf.sys_ver;
----------------------------------------------------------------------------------------------------- 20.数据字典表
-- 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,
  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.bak is '备注';
/* alter sequence lf.sys_dict_id_seq restart with 300;
update lf.sys_dict set create_user=1,create_time=now() where 1=1;
update lf.sys_dict set bak=null where bak=' '; */

select count(*) from lf.sys_dict where tab='sys_user';
select * from lf.sys_dict order by ns,tab,order_num limit 10 offset 0;
----------------------------------------------------------------------------------------------------- 21.样式表
-- 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),
  file_guid varchar(40) default new_guid(),
  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.file_guid is '样式文件ID';
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;
----------------------------------------------------------------------------------------------------- 22.下载记录表
-- 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,
  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,
  geo geometry default null,
  bak varchar(1024)
  CONSTRAINT enforce_dims_geom CHECK (st_ndims(geo) = 2),
  CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geo) = 'POLYGON'::text OR geo IS NULL),
  CONSTRAINT enforce_srid_geom CHECK (st_srid(geo) = 4490)
);
create index index_sys_download_geo on lf.sys_download using gist (geo);
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-下载数据,2-下载专题图';
comment on column lf.sys_download.depid is '单位ID';
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.geo is '空间';
comment on column lf.sys_download.bak is '备注';
-- insert into lf.sys_download (name,pwd,url,descr) values ('2022-09-27','123456','','测试下载');

select * from lf.sys_download;
----------------------------------------------------------------------------------------------------- 23.消息通知表
-- 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;
----------------------------------------------------------------------------------------------------- 24.参数设置表
-- 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,
  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.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,descr,mark) values ('自动登出时间',15,15,3,1440,'默认用户15分钟不操作，就自动登出系统。','AUTO_LOGOUT');
insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,descr,mark) values ('令牌有效期',240,240,60,1440,'令牌默认有效期为240分钟，超时将失效。','TOKEN_EXPIRE');
insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,descr,mark) values ('缓存有效期',240,240,60,1440,'缓存默认有效期为240分钟，超时将失效。','CACHE_EXPIRE');
insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,descr,mark) values ('最大文件数',2000,2000,500,2500,'单个文件夹下默认最大存储文件的数目为2000个。','MAX_FILES');
insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,descr,mark) values ('Cookie有效期',240,240,60,43200,'Cookie默认有效期为240分钟，超时将失效。','COOKIE_MAX_AGE');
insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,descr,mark) values ('附件大小',50,50,5,1024,'单个附件大小默认最大为50MB。','MAX_FILE_SIZE'); */

select * from lf.sys_args;
----------------------------------------------------------------------------------------------------- 25.黑名单表
-- DROP TABLE IF EXISTS lf.sys_blacklist;
create table lf.sys_blacklist(
  id serial primary key,
  ip varchar(50),
  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.99',1);
insert into lf.sys_blacklist (ip,type) values ('127.0.0.1',2); */

select * from lf.sys_blacklist;
-----------------------------------------------------------------------------------------------------