----------------------------------------------------------------------------------------------------- 0.GUID生成函数
|
CREATE or REPLACE FUNCTION new_guid()
|
RETURNS "pg_catalog"."varchar" AS $BODY$
|
DECLARE
|
v_seed_value varchar(32);
|
BEGIN
|
select md5(inet_client_addr()::varchar || timeofday() || inet_server_addr()::varchar || to_hex(inet_client_port()))
|
into v_seed_value;
|
|
return (substr(v_seed_value,1,8) || '-' ||
|
substr(v_seed_value,9,4) || '-' ||
|
substr(v_seed_value,13,4) || '-' ||
|
substr(v_seed_value,17,4) || '-' ||
|
substr(v_seed_value,21,12));
|
END;
|
$BODY$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
|
|
select new_guid();
|
----------------------------------------------------------------------------------------------------- 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 sequence lf.sys_menu_id_seq restart with 200;
|
--alter table lf.sys_menu alter column bak type varchar(1024);
|
--update lf.sys_menu set bak=null,create_time=now() where 1=1;
|
|
select * from lf.sys_menu;
|
----------------------------------------------------------------------------------------------------- 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 100;
|
--alter table lf.sys_dep rename short to sname;
|
--alter table lf.sys_dep alter column bak type varchar(1024);
|
--update lf.sys_dep set bak=null,create_time=now() where 1=1;
|
|
select * from lf.sys_dep;
|
----------------------------------------------------------------------------------------------------- 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 '备注';
|
|
--select * from public.data_dir;
|
--update lf.sys_dir set bak=null,create_time=now(),code=id where 1=1;
|
|
select * from lf.sys_dir;
|
----------------------------------------------------------------------------------------------------- 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 '备注';
|
|
-- alter table lf.sys_user add unique(uid);
|
/*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);*/
|
|
select * from lf.sys_user;
|
----------------------------------------------------------------------------------------------------- 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,
|
browser 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.browser 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;
|
----------------------------------------------------------------------------------------------------- 6.操作日志表
|
-- DROP TABLE IF EXISTS lf.sys_operate;
|
create table lf.sys_operate(
|
id serial8,
|
modular varchar(60),
|
url varchar(256),
|
ip varchar(30),
|
type smallint default 1,
|
userid integer default 1,
|
optime timestamp(6) without time zone default now()
|
) 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.modular is '操作模块,如:数据管理\目录管理';
|
comment on column lf.sys_operate.url is '操作网址';
|
comment on column lf.sys_operate.ip is 'IP地址';
|
comment on column lf.sys_operate.type is '类别:1-查看,2-新增,3-修改,4-删除,5-上传,6-下载';
|
comment on column lf.sys_operate.userid is '操作人ID';
|
comment on column lf.sys_operate.optime 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');*/
|
|
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 '使用时间,默认4小时,240分钟';
|
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 '更新时间';
|
|
--select now()::timestamp + '1 year 4 hour';
|
/*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;
|
select * from lf.sys_token_2022;
|
select * from lf.sys_token_2023;
|
----------------------------------------------------------------------------------------------------- 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,optime) values (1,'192.168.20.107',4,'2024-01-02');*/
|
|
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;
|
----------------------------------------------------------------------------------------------------- 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_role_menu;
|
create table lf.sys_role_menu(
|
id serial primary key,
|
roleid integer,
|
menuid 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 is '角色-菜单表';
|
comment on column lf.sys_role_menu.id is '主键ID';
|
comment on column lf.sys_role_menu.roleid is '角色ID';
|
comment on column lf.sys_role_menu.menuid is '菜单ID';
|
comment on column lf.sys_role_menu.create_user is '创建人ID';
|
comment on column lf.sys_role_menu.create_time is '创建时间';
|
comment on column lf.sys_role_menu.update_user is '更新人ID';
|
comment on column lf.sys_role_menu.update_time is '更新时间';
|
|
--insert into lf.sys_role_menu (roleid,menuid) values (1,1);
|
|
select * from lf.sys_role_menu;
|
----------------------------------------------------------------------------------------------------- 15.菜单-权限表
|
-- 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;
|
----------------------------------------------------------------------------------------------------- 16.元数据表
|
-- DROP TABLE IF EXISTS lf.sys_meta;
|
create table lf.sys_meta(
|
id serial primary key,
|
dirid integer,
|
depid integer,
|
name varchar(256),
|
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.descr is '描述';
|
comment on column lf.sys_meta.guid is 'GUID';
|
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;
|
select * 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 'GUID';
|
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 'GUID';
|
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.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;
|
|
----------------------------------------------------------------------------------------------------- 21.样式表
|
-- DROP TABLE IF EXISTS lf.sys_style;
|
create table lf.sys_style(
|
id serial primary key,
|
name varchar(50),
|
guid varchar(40) default new_guid(),
|
fileid integer,
|
viewid integer,
|
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,
|
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.guid is 'GUID';
|
comment on column lf.sys_style.fileid is '样式文件ID';
|
comment on column lf.sys_style.viewid is '预览文件ID';
|
comment on column lf.sys_style.descr is '描述';
|
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,fileid,viewid) values ('pp.edp',1,1);
|
|
select * from lf.sys_style;
|
----------------------------------------------------------------------------------------------------- 22.专题图出图表
|
-- DROP TABLE IF EXISTS lf.sys_drawing;
|
|
----------------------------------------------------------------------------------------------------- 23.下载记录表
|
-- DROP TABLE IF EXISTS lf.sys_download;
|
|
----------------------------------------------------------------------------------------------------- 24.消息通知表
|
-- DROP TABLE IF EXISTS lf.sys_msg;
|
|
----------------------------------------------------------------------------------------------------- 25.参数设置表
|
-- 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,
|
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_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.descr 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_ver.update_time is '更新时间';
|
|
/*insert into lf.sys_args (name,cvalue,dvalue,descr) values ('自动登出时间',15,15,'默认用户15分钟不操作，就自动登出系统。');
|
insert into lf.sys_args (name,cvalue,dvalue,descr) values ('令牌有效期',240,240,'临时令牌默认有效期为240分钟（4小时），超时将失效。');
|
insert into lf.sys_args (name,cvalue,dvalue,descr) values ('缓存有效期',1440,1440,'缓存默认有效期为1440分钟（1天），超时将失效。');*/
|
|
select * from lf.sys_args;
|
------------------------------------------------------------------------------------------------------------------
|
