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