create sequence lf.sys_meta_id_seq increment by 1 minvalue 1 maxvalue 2147483647 start 1 cache 1;
|
alter sequence lf.sys_layer_id_seq restart with 1;
|
----------------------------------------------------------------------------------------------------- 01.菜单表
|
-- DROP TABLE IF EXISTS lf.sys_menu;
|
create table lf.sys_menu(
|
id serial primary key,
|
pid integer,
|
cn_name varchar(50),
|
en_name varchar(50),
|
url varchar(256),
|
perms varchar(512),
|
type smallint,
|
css varchar(30),
|
icon varchar(256),
|
level integer,
|
order_num integer,
|
is_show smallint,
|
create_user integer,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone,
|
bak varchar(1024)
|
);
|
comment on table lf.sys_menu is '菜单表';
|
comment on column lf.sys_menu.id is '主键ID';
|
comment on column lf.sys_menu.pid is '父ID：0-根节点';
|
comment on column lf.sys_menu.cn_name is '中文名称';
|
comment on column lf.sys_menu.en_name is '英文名称';
|
comment on column lf.sys_menu.url is '菜单Url';
|
comment on column lf.sys_menu.perms is '授权：多个用逗号分隔，如 user:list,user:create';
|
comment on column lf.sys_menu.type is '类型：0-目录,1-菜单,2-按钮';
|
comment on column lf.sys_menu.css is '样式表';
|
comment on column lf.sys_menu.icon is '图标';
|
comment on column lf.sys_menu.level is '层级：0-根节点';
|
comment on column lf.sys_menu.order_num is '排序号';
|
comment on column lf.sys_menu.is_show is '是否显示';
|
comment on column lf.sys_menu.create_user is '创建人ID';
|
comment on column lf.sys_menu.create_time is '创建时间';
|
comment on column lf.sys_menu.update_user is '更新人ID';
|
comment on column lf.sys_menu.update_time is '更新时间';
|
comment on column lf.sys_menu.bak is '备注';
|
--alter table lf.sys_menu add css varchar(30);
|
--alter sequence lf.sys_menu_id_seq restart with 44;
|
|
select * from lf.sys_menu order by order_num;
|
----------------------------------------------------------------------------------------------------- 02.单位表
|
-- DROP TABLE IF EXISTS lf.sys_dep;
|
create table lf.sys_dep(
|
id serial primary key,
|
pid integer,
|
name varchar(150),
|
sname varchar(50),
|
code varchar(50),
|
uncode varchar(50),
|
addr varchar(300),
|
contact varchar(50),
|
fax varchar(50),
|
email varchar(50),
|
post varchar(20),
|
website varchar(1024),
|
level integer,
|
order_num integer,
|
create_user integer,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone,
|
bak varchar(1024)
|
);
|
comment on table lf.sys_dep is '单位表';
|
comment on column lf.sys_dep.id is '主键ID';
|
comment on column lf.sys_dep.pid is '父ID：0-根节点';
|
comment on column lf.sys_dep.name is '名称';
|
comment on column lf.sys_dep.sname is '简称';
|
comment on column lf.sys_dep.code is '编码';
|
comment on column lf.sys_dep.uncode is '机构代码';
|
comment on column lf.sys_dep.addr is '地址';
|
comment on column lf.sys_dep.contact is '联系方式';
|
comment on column lf.sys_dep.fax is '传真';
|
comment on column lf.sys_dep.email is '电子邮件';
|
comment on column lf.sys_dep.post is '邮政编码';
|
comment on column lf.sys_dep.website is '网站地址';
|
comment on column lf.sys_dep.level is '层级：0-根节点';
|
comment on column lf.sys_dep.order_num is '排序号';
|
comment on column lf.sys_dep.create_user is '创建人ID';
|
comment on column lf.sys_dep.create_time is '创建时间';
|
comment on column lf.sys_dep.update_user is '更新人ID';
|
comment on column lf.sys_dep.update_time is '更新时间';
|
comment on column lf.sys_dep.bak is '备注';
|
-- alter sequence lf.sys_dep_id_seq restart with 45;
|
-- alter table lf.sys_dep rename short to sname;
|
-- update lf.sys_dep set name='中国xxx工程有限公司' where name='中国石油天然气管道工程有限公司';
|
|
select * from lf.sys_dep order by id;
|
----------------------------------------------------------------------------------------------------- 03.目录表
|
-- DROP TABLE IF EXISTS lf.sys_dir;
|
create table lf.sys_dir(
|
id serial primary key,
|
pid integer,
|
name varchar(150),
|
code varchar(50),
|
descr varchar(1024),
|
level integer,
|
order_num integer,
|
create_user integer,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone,
|
bak varchar(1024)
|
);
|
comment on table lf.sys_dir is '目录表';
|
comment on column lf.sys_dir.id is '主键ID';
|
comment on column lf.sys_dir.pid is '父ID：0-根节点';
|
comment on column lf.sys_dir.name is '名称';
|
comment on column lf.sys_dir.code is '目录编码';
|
comment on column lf.sys_dir.descr is '目录说明';
|
comment on column lf.sys_dir.level is '层级：0-根节点';
|
comment on column lf.sys_dir.order_num is '排序号';
|
comment on column lf.sys_dir.create_user is '创建人ID';
|
comment on column lf.sys_dir.create_time is '创建时间';
|
comment on column lf.sys_dir.update_user is '更新人ID';
|
comment on column lf.sys_dir.update_time is '更新时间';
|
comment on column lf.sys_dir.bak is '备注';
|
--alter sequence lf.sys_dir_id_seq restart with 124;
|
|
select * from lf.sys_dir order by id;
|
----------------------------------------------------------------------------------------------------- 04.用户表
|
-- DROP TABLE IF EXISTS lf.sys_user;
|
create table lf.sys_user(
|
id serial primary key,
|
depid integer default 1,
|
uid varchar(20) unique,
|
uname varchar(50),
|
pwd varchar(200),
|
salt varchar(20),
|
sex smallint default 1,
|
native varchar(50) default '汉',
|
contact varchar(50),
|
job varchar(50),
|
email varchar(50),
|
addr varchar(300),
|
edu varchar(20),
|
idcard varchar(20),
|
status smallint default 0,
|
create_user integer,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone,
|
bak varchar(1024)
|
);
|
comment on table lf.sys_user is '用户表';
|
comment on column lf.sys_user.id is '主键ID';
|
comment on column lf.sys_user.depid is '单位ID';
|
comment on column lf.sys_user.uid is '用户ID';
|
comment on column lf.sys_user.uname is '用户名';
|
comment on column lf.sys_user.pwd is 'MD5密码';
|
comment on column lf.sys_user.salt is 'MD5盐';
|
comment on column lf.sys_user.sex is '性别：0-女,1-男,-1-未知';
|
comment on column lf.sys_user.native is '籍贯';
|
comment on column lf.sys_user.contact is '联系方式';
|
comment on column lf.sys_user.job is '工作';
|
comment on column lf.sys_user.email is '电子邮件';
|
comment on column lf.sys_user.addr is '地址';
|
comment on column lf.sys_user.edu is '教育';
|
comment on column lf.sys_user.idcard is '证件号';
|
comment on column lf.sys_user.status is '状态：0-正常,1-禁用,2-删除,3-申请,4-拒批';
|
comment on column lf.sys_user.create_user is '创建人ID';
|
comment on column lf.sys_user.create_time is '创建时间';
|
comment on column lf.sys_user.update_user is '更新人ID';
|
comment on column lf.sys_user.update_time is '更新时间';
|
comment on column lf.sys_user.bak is '备注';
|
/* insert into lf.sys_user (depid,uid,uname,pwd,create_user) values (1,'admin','管理员','000000',1);
|
insert into lf.sys_user (depid,uid,uname,pwd,create_user) values (20,'xl','线路室','000000',1);
|
insert into lf.sys_user (depid,uid,uname,pwd,create_user) values (21,'cky','穿跨越室','000000',1);
|
insert into lf.sys_user (depid,uid,uname,pwd,create_user) values (22,'tj','土建室','000000',1);
|
insert into lf.sys_user (depid,uid,uname,pwd,create_user) values (38,'kc','勘察室','000000',1);
|
insert into lf.sys_user (id,depid,uid,uname,pwd,create_user) values (0,1,'guest','访客','',1);
|
update lf.sys_user set pwd='b37f70636f1164e86cc8796201737933f65af63918d8442b'; */
|
|
select * from lf.sys_user order by id;
|
----------------------------------------------------------------------------------------------------- 05.登录日志表
|
-- DROP TABLE IF EXISTS lf.sys_login;
|
create table lf.sys_login(
|
id serial8,
|
appid integer default 1,
|
ip varchar(30),
|
type smallint default 1,
|
status smallint default 1,
|
descr varchar(100),
|
userid integer default 1,
|
optime timestamp(6) without time zone default now()
|
) partition by range(optime);
|
|
create table lf.sys_login_2022 partition of lf.sys_login for values from ('2022-01-01') to ('2023-01-01');
|
create INDEX index_sys_login_2022_optime on lf.sys_login_2022 (optime);
|
create table lf.sys_login_2023 partition of lf.sys_login for values from ('2023-01-01') to ('2024-01-01');
|
create INDEX index_sys_login_2023_optime on lf.sys_login_2023 (optime);
|
create table lf.sys_login_2024 partition of lf.sys_login for values from ('2024-01-01') to ('2025-01-01');
|
create INDEX index_sys_login_2024_optime on lf.sys_login_2024 (optime);
|
create table lf.sys_login_2025 partition of lf.sys_login for values from ('2025-01-01') to ('2026-01-01');
|
create INDEX index_sys_login_2025_optime on lf.sys_login_2025 (optime);
|
create table lf.sys_login_2026 partition of lf.sys_login for values from ('2026-01-01') to ('2027-01-01');
|
create INDEX index_sys_login_2026_optime on lf.sys_login_2026 (optime);
|
create table lf.sys_login_2027 partition of lf.sys_login for values from ('2027-01-01') to ('2028-01-01');
|
create INDEX index_sys_login_2027_optime on lf.sys_login_2027 (optime);
|
|
comment on table lf.sys_login is '登录日志表';
|
comment on column lf.sys_login.id is '主键ID';
|
comment on column lf.sys_login.appid is '应用程序ID：1-管道基础大数据平台,2-外部系统集成';
|
comment on column lf.sys_login.ip is 'IP地址';
|
comment on column lf.sys_login.type is '类别：1-登录,2-校验,3-登出';
|
comment on column lf.sys_login.status is '状态：1-成功,0-失败';
|
comment on column lf.sys_login.descr is '描述';
|
comment on column lf.sys_login.userid is '登记人ID';
|
comment on column lf.sys_login.optime is '登记时间';
|
/* insert into lf.sys_login (appid,ip,userid) values (1,'192.168.20.106',1);
|
insert into lf.sys_login (appid,ip,userid,optime) values (1,'192.168.20.106',1,'2024-01-02');
|
delete from lf.sys_login_2024; */
|
|
select * from lf.sys_login; select * from lf.sys_login_2022; select * from lf.sys_login_2024;
|
select a.*,b.uname from lf.sys_login a inner join lf.sys_user b on a.userid = b.id;
|
----------------------------------------------------------------------------------------------------- 06.操作日志表
|
-- DROP TABLE IF EXISTS lf.sys_operate;
|
create table lf.sys_operate(
|
id serial8,
|
modular1 varchar(50),
|
modular2 varchar(50),
|
url varchar(1024),
|
ip varchar(30),
|
exec bigint,
|
clazz varchar(100),
|
type smallint default 1,
|
userid integer default 1,
|
optime timestamp(6) without time zone default now(),
|
bak varchar(1024)
|
) partition by range(optime);
|
|
create table lf.sys_operate_2022 partition of lf.sys_operate for values from ('2022-01-01') to ('2023-01-01');
|
create INDEX index_sys_operate_2022_optime on lf.sys_operate_2022 (optime);
|
create table lf.sys_operate_2023 partition of lf.sys_operate for values from ('2023-01-01') to ('2024-01-01');
|
create INDEX index_sys_operate_2023_optime on lf.sys_operate_2023 (optime);
|
create table lf.sys_operate_2024 partition of lf.sys_operate for values from ('2024-01-01') to ('2025-01-01');
|
create INDEX index_sys_operate_2024_optime on lf.sys_operate_2024 (optime);
|
create table lf.sys_operate_2025 partition of lf.sys_operate for values from ('2025-01-01') to ('2026-01-01');
|
create INDEX index_sys_operate_2025_optime on lf.sys_operate_2025 (optime);
|
create table lf.sys_operate_2026 partition of lf.sys_operate for values from ('2026-01-01') to ('2027-01-01');
|
create INDEX index_sys_operate_2026_optime on lf.sys_operate_2026 (optime);
|
create table lf.sys_operate_2027 partition of lf.sys_operate for values from ('2027-01-01') to ('2028-01-01');
|
create INDEX index_sys_operate_2027_optime on lf.sys_operate_2027 (optime);
|
|
comment on table lf.sys_operate is '操作日志表';
|
comment on column lf.sys_operate.id is '主键ID';
|
comment on column lf.sys_operate.modular1 is '一级模块：如 数据管理';
|
comment on column lf.sys_operate.modular2 is '二级模块：如 目录管理';
|
comment on column lf.sys_operate.url is '操作网址';
|
comment on column lf.sys_operate.ip is 'IP地址';
|
comment on column lf.sys_operate.exec is '操作时间：单位为毫秒';
|
comment on column lf.sys_operate.clazz is '类名\方法名';
|
comment on column lf.sys_operate.type is '类别：0-其它,1-查看,2-新增,3-修改,4-删除,5-上传,6-下载';
|
comment on column lf.sys_operate.userid is '操作人ID';
|
comment on column lf.sys_operate.optime is '操作时间';
|
comment on column lf.sys_operate.bak is '备注';
|
/* insert into lf.sys_operate (modular,url,ip,type) values ('数据管理\目录管理','data/manage/update','192.168.20.106',5);
|
insert into lf.sys_operate (modular,url,ip,type,optime) values ('数据管理\目录管理','data/manage/delete','192.168.20.107',4,'2024-01-02');
|
delete from lf.sys_operate_2024; */
|
|
alter table lf.sys_operate add column modular1 varchar(50); alter table lf.sys_operate drop column modular;
|
select split_part(modular,'\',1),split_part(modular,'\',2) from lf.sys_operate;
|
update lf.sys_operate set modular1=split_part(modular,'\',1),modular2=split_part(modular,'\',2);
|
|
select a.*,b.uname from lf.sys_operate a inner join lf.sys_user b on a.userid = b.id;
|
select * from lf.sys_operate; select * from lf.sys_operate_2022; select * from lf.sys_operate_2024;
|
----------------------------------------------------------------------------------------------------- 07.令牌表
|
-- DROP TABLE IF EXISTS lf.sys_token;
|
create table lf.sys_token(
|
id serial8,
|
token varchar(40),
|
duration integer default 240,
|
expire timestamp(6) without time zone default now()::timestamp + '4 hour',
|
type smallint default 0,
|
ip varchar(30),
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone
|
) partition by range(create_time);
|
|
create table lf.sys_token_2022 partition of lf.sys_token for values from ('2022-01-01') to ('2023-01-01');
|
create INDEX index_sys_token_2022_create_time on lf.sys_token_2022 (create_time);
|
create table lf.sys_token_2023 partition of lf.sys_token for values from ('2023-01-01') to ('2024-01-01');
|
create INDEX index_sys_token_2023_create_time on lf.sys_token_2023 (create_time);
|
create table lf.sys_token_2024 partition of lf.sys_token for values from ('2024-01-01') to ('2025-01-01');
|
create INDEX index_sys_token_2024_create_time on lf.sys_token_2024 (create_time);
|
create table lf.sys_token_2025 partition of lf.sys_token for values from ('2025-01-01') to ('2026-01-01');
|
create INDEX index_sys_token_2025_create_time on lf.sys_token_2025 (create_time);
|
create table lf.sys_token_2026 partition of lf.sys_token for values from ('2026-01-01') to ('2027-01-01');
|
create INDEX index_sys_token_2026_create_time on lf.sys_token_2026 (create_time);
|
create table lf.sys_token_2027 partition of lf.sys_token for values from ('2027-01-01') to ('2028-01-01');
|
create INDEX index_sys_token_2027_create_time on lf.sys_token_2027 (create_time);
|
|
comment on table lf.sys_token is '令牌表';
|
comment on column lf.sys_token.id is '主键ID';
|
comment on column lf.sys_token.token is '令牌';
|
comment on column lf.sys_token.duration is '使用时间：默认240分钟(4小时)';
|
comment on column lf.sys_token.expire is '失效时间';
|
comment on column lf.sys_token.type is '类型：0-临时,1-固定';
|
comment on column lf.sys_token.ip is 'IP地址';
|
comment on column lf.sys_token.create_user is '创建人ID';
|
comment on column lf.sys_token.create_time is '创建时间';
|
comment on column lf.sys_token.update_user is '更新人ID';
|
comment on column lf.sys_token.update_time is '更新时间';
|
/* insert into lf.sys_token (token,ip) values (new_guid(),'192.168.20.106');
|
insert into lf.sys_token (token,ip,expire,create_time) values (new_guid(),'192.168.20.107',now()::timestamp + '1 year 4 hour',now()::timestamp + '1 year');
|
delete from lf.sys_token_2023; */
|
|
select * from lf.sys_token where token='91cf74dc-1d03-4937-983f-88810589b112' and expire > now() limit 1;
|
select * from lf.sys_user where id=(select create_user from lf.sys_token where token='91cf74dc-1d03-4937-983f-88810589b112' and expire > now() limit 1);
|
|
select * from lf.sys_token; select * from lf.sys_token_2022; select * from lf.sys_token_2023; select now()::timestamp + '1 year 4 hour';
|
----------------------------------------------------------------------------------------------------- 08.资源表
|
-- DROP TABLE IF EXISTS lf.sys_res;
|
create table lf.sys_res(
|
id serial primary key,
|
name varchar(50),
|
server varchar(300),
|
source varchar(300),
|
depid integer default 1,
|
dirid integer default 1,
|
code varchar(30),
|
descr varchar(200),
|
img varchar(40),
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone,
|
bak varchar(1024)
|
);
|
comment on table lf.sys_res is '资源表';
|
comment on column lf.sys_res.id is '主键ID';
|
comment on column lf.sys_res.name is '名称';
|
comment on column lf.sys_res.server is '服务地址';
|
comment on column lf.sys_res.source is '原始地址';
|
comment on column lf.sys_res.depid is '单位ID';
|
comment on column lf.sys_res.dirid is '目录ID';
|
comment on column lf.sys_res.code is '编码';
|
comment on column lf.sys_res.descr is '描述';
|
comment on column lf.sys_res.img is '预览图';
|
comment on column lf.sys_res.create_user is '创建人ID';
|
comment on column lf.sys_res.create_time is '创建时间';
|
comment on column lf.sys_res.update_user is '更新人ID';
|
comment on column lf.sys_res.update_time is '更新时间';
|
comment on column lf.sys_res.bak is '备注';
|
/* insert into lf.sys_res (name) values ('天地图影像');
|
insert into lf.sys_res (name) values ('天地图矢量'); */
|
|
select * from lf.sys_res;
|
----------------------------------------------------------------------------------------------------- 09.资源操作表
|
-- DROP TABLE IF EXISTS lf.sys_res_op;
|
create table lf.sys_res_op(
|
id serial8,
|
resid integer,
|
type smallint default 1,
|
ip varchar(30),
|
userid integer default 1,
|
optime timestamp(6) without time zone default now()
|
) partition by range(optime);
|
|
create table lf.sys_res_op_2022 partition of lf.sys_res_op for values from ('2022-01-01') to ('2023-01-01');
|
create INDEX index_sys_res_op_2022_optime on lf.sys_res_op_2022 (optime);
|
create table lf.sys_res_op_2023 partition of lf.sys_res_op for values from ('2023-01-01') to ('2024-01-01');
|
create INDEX index_sys_res_op_2023_optime on lf.sys_res_op_2023 (optime);
|
create table lf.sys_res_op_2024 partition of lf.sys_res_op for values from ('2024-01-01') to ('2025-01-01');
|
create INDEX index_sys_res_op_2024_optime on lf.sys_res_op_2024 (optime);
|
create table lf.sys_res_op_2025 partition of lf.sys_res_op for values from ('2025-01-01') to ('2026-01-01');
|
create INDEX index_sys_res_op_2025_optime on lf.sys_res_op_2025 (optime);
|
create table lf.sys_res_op_2026 partition of lf.sys_res_op for values from ('2026-01-01') to ('2027-01-01');
|
create INDEX index_sys_res_op_2026_optime on lf.sys_res_op_2026 (optime);
|
create table lf.sys_res_op_2027 partition of lf.sys_res_op for values from ('2027-01-01') to ('2028-01-01');
|
create INDEX index_sys_res_op_2027_optime on lf.sys_res_op_2027 (optime);
|
|
comment on table lf.sys_res_op is '资源操作表';
|
comment on column lf.sys_res_op.id is '主键ID';
|
comment on column lf.sys_res_op.resid is '资源ID';
|
comment on column lf.sys_res_op.type is '操作类型：1-查看,2-新增,3-修改,4-删除,5-上传,6-下载,7-统计';
|
comment on column lf.sys_res_op.ip is 'IP地址';
|
comment on column lf.sys_res_op.userid is '操作人ID';
|
comment on column lf.sys_res_op.optime is '创建时间';
|
/* insert into lf.sys_res_op (resid,ip,type) values (1,'192.168.20.106',5);
|
insert into lf.sys_res_op (resid,ip,type) values (1,'192.168.20.106',1);
|
insert into lf.sys_res_op (resid,ip,type,optime) values (1,'192.168.20.107',4,'2024-01-02');
|
delete from lf.sys_res_op_2024; */
|
|
select a.*,b.uname,c.name from lf.sys_res_op a inner join lf.sys_user b on a.userid = b.id inner join lf.sys_res c on a.resid=c.id;
|
|
select * from lf.sys_res_op; select * from lf.sys_res_op_2022; select * from lf.sys_res_op_2024;
|
----------------------------------------------------------------------------------------------------- 10.权限表
|
-- DROP TABLE IF EXISTS lf.sys_auth;
|
create table lf.sys_auth(
|
id serial primary key,
|
name varchar(100),
|
tag varchar(100),
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone,
|
bak varchar(1024)
|
);
|
comment on table lf.sys_auth is '权限表';
|
comment on column lf.sys_auth.id is '主键ID';
|
comment on column lf.sys_auth.name is '名称';
|
comment on column lf.sys_auth.tag is '标识';
|
comment on column lf.sys_auth.create_user is '创建人ID';
|
comment on column lf.sys_auth.create_time is '创建时间';
|
comment on column lf.sys_auth.update_user is '更新人ID';
|
comment on column lf.sys_auth.update_time is '更新时间';
|
comment on column lf.sys_auth.bak is '备注';
|
/* insert into lf.sys_auth (name,tag) values ('查看','/select');
|
insert into lf.sys_auth (name,tag) values ('新增','/insert');
|
insert into lf.sys_auth (name,tag) values ('修改','/update');
|
insert into lf.sys_auth (name,tag) values ('删除','/delete');
|
insert into lf.sys_auth (name,tag) values ('上传','/upload');
|
insert into lf.sys_auth (name,tag) values ('下载','/download');
|
insert into lf.sys_auth (name,tag) values ('统计','/count');
|
alter sequence lf.sys_auth_id_seq restart with 8; */
|
|
select * from lf.sys_auth;
|
----------------------------------------------------------------------------------------------------- 11.角色表
|
-- DROP TABLE IF EXISTS lf.sys_role;
|
create table lf.sys_role(
|
id serial primary key,
|
depid integer default 1,
|
name varchar(100),
|
descr varchar(100),
|
is_admin integer default 0,
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone,
|
bak varchar(1024)
|
);
|
comment on table lf.sys_role is '角色表';
|
comment on column lf.sys_role.id is '主键ID';
|
comment on column lf.sys_role.depid is '单位ID';
|
comment on column lf.sys_role.name is '名称';
|
comment on column lf.sys_role.descr is '描述';
|
comment on column lf.sys_role.is_admin is '是/否为管理员：0-普通会员，1-超级管理员，2-数据管理员';
|
comment on column lf.sys_role.create_user is '创建人ID';
|
comment on column lf.sys_role.create_time is '创建时间';
|
comment on column lf.sys_role.update_user is '更新人ID';
|
comment on column lf.sys_role.update_time is '更新时间';
|
comment on column lf.sys_role.bak is '备注';
|
--insert into lf.sys_role (id,depid,name,descr,is_admin) values (1,1,'Admin','系统管理员',1);
|
|
select * from lf.sys_role;
|
select a.*,b.name depname from lf.sys_role a inner join lf.sys_dep b on a.depid = b.id order by a.id limit 10 offset 0;
|
----------------------------------------------------------------------------------------------------- 12.角色-用户表
|
-- DROP TABLE IF EXISTS lf.sys_role_user;
|
create table lf.sys_role_user(
|
id serial primary key,
|
roleid integer,
|
userid integer,
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone
|
);
|
comment on table lf.sys_role_user is '角色-用户表';
|
comment on column lf.sys_role_user.id is '主键ID';
|
comment on column lf.sys_role_user.roleid is '角色ID';
|
comment on column lf.sys_role_user.userid is '用户ID';
|
comment on column lf.sys_role_user.create_user is '创建人ID';
|
comment on column lf.sys_role_user.create_time is '创建时间';
|
comment on column lf.sys_role_user.update_user is '更新人ID';
|
comment on column lf.sys_role_user.update_time is '更新时间';
|
--insert into lf.sys_role_user (roleid,userid) values (1,1);
|
|
select * from lf.sys_role_user;
|
----------------------------------------------------------------------------------------------------- 13.角色-资源表
|
-- DROP TABLE IF EXISTS lf.sys_role_res;
|
create table lf.sys_role_res(
|
id serial primary key,
|
roleid integer,
|
resid integer,
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone
|
);
|
comment on table lf.sys_role_res is '角色-资源表';
|
comment on column lf.sys_role_res.id is '主键ID';
|
comment on column lf.sys_role_res.roleid is '角色ID';
|
comment on column lf.sys_role_res.resid is '资源ID';
|
comment on column lf.sys_role_res.create_user is '创建人ID';
|
comment on column lf.sys_role_res.create_time is '创建时间';
|
comment on column lf.sys_role_res.update_user is '更新人ID';
|
comment on column lf.sys_role_res.update_time is '更新时间';
|
--insert into lf.sys_role_res (roleid,resid) values (1,1);
|
|
select * from lf.sys_role_res;
|
----------------------------------------------------------------------------------------------------- 14.菜单-权限表
|
-- DROP TABLE IF EXISTS lf.sys_menu_auth;
|
create table lf.sys_menu_auth(
|
id serial primary key,
|
menuid integer,
|
authid integer,
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone
|
);
|
comment on table lf.sys_menu_auth is '菜单-权限表';
|
comment on column lf.sys_menu_auth.id is '主键ID';
|
comment on column lf.sys_menu_auth.menuid is '菜单ID';
|
comment on column lf.sys_menu_auth.authid is '权限ID';
|
comment on column lf.sys_menu_auth.create_user is '创建人ID';
|
comment on column lf.sys_menu_auth.create_time is '创建时间';
|
comment on column lf.sys_menu_auth.update_user is '更新人ID';
|
comment on column lf.sys_menu_auth.update_time is '更新时间';
|
--insert into lf.sys_menu_auth (menuid,authid) values (1,1),(1,2),(1,3),(1,4),(1,5);
|
|
select * from lf.sys_menu_auth;
|
----------------------------------------------------------------------------------------------------- 15.角色-菜单-权限表
|
-- DROP TABLE IF EXISTS lf.sys_role_menu_auth;
|
create table lf.sys_role_menu_auth(
|
id serial primary key,
|
roleid integer,
|
menu_auth_id integer,
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone
|
);
|
comment on table lf.sys_role_menu_auth is '角色-菜单表';
|
comment on column lf.sys_role_menu_auth.id is '主键ID';
|
comment on column lf.sys_role_menu_auth.roleid is '角色ID';
|
comment on column lf.sys_role_menu_auth.menu_auth_id is '菜单-权限ID';
|
comment on column lf.sys_role_menu_auth.create_user is '创建人ID';
|
comment on column lf.sys_role_menu_auth.create_time is '创建时间';
|
comment on column lf.sys_role_menu_auth.update_user is '更新人ID';
|
comment on column lf.sys_role_menu_auth.update_time is '更新时间';
|
--insert into lf.sys_role_menu_auth (roleid,menu_auth_id) values (1,1);
|
|
select * from lf.sys_role_menu_auth;
|
----------------------------------------------------------------------------------------------------- 16.元数据表
|
-- DROP TABLE IF EXISTS lf.sys_meta;
|
create table lf.sys_meta (
|
id serial primary key,
|
eventid varchar(38) default new_guid(),
|
metaid integer default 0,
|
dirid integer default 0,
|
depid integer default 0,
|
verid integer default 0,
|
name varchar(256),
|
type varchar(100),
|
guid varchar(38) default new_guid(),
|
path varchar(512),
|
sizes float8 default 0,
|
tab varchar(50),
|
layer varchar(50),
|
rows integer,
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone,
|
bak varchar(1024),
|
geom geometry(MultiPolygon, 4490)
|
);
|
-- drop index index_sys_meta_geom;
|
create index index_sys_meta_geom on lf.sys_meta using GIST (geom);
|
comment on table lf.sys_meta is '元数据表';
|
comment on column lf.sys_meta.id is '主键ID';
|
comment on column lf.sys_meta.eventid is 'GUID';
|
comment on column lf.sys_meta.metaid is '父元数据ID：0-没有';
|
comment on column lf.sys_meta.dirid is '目录ID';
|
comment on column lf.sys_meta.depid is '单位ID';
|
comment on column lf.sys_meta.verid is '版本ID';
|
comment on column lf.sys_meta.name is '名称';
|
comment on column lf.sys_meta.type is '类型:file-文件，shp-ShapeFile，gdb-GDB，zip-压缩包';
|
comment on column lf.sys_meta.guid is '文件GUID';
|
comment on column lf.sys_meta.path is '存储路径';
|
comment on column lf.sys_meta.sizes is '大小：单位MB';
|
comment on column lf.sys_meta.tab is '表名';
|
comment on column lf.sys_meta.layer is '图层';
|
comment on column lf.sys_meta.rows is '行数';
|
comment on column lf.sys_meta.create_user is '创建人ID';
|
comment on column lf.sys_meta.create_time is '创建时间';
|
comment on column lf.sys_meta.update_user is '更新人ID';
|
comment on column lf.sys_meta.update_time is '更新时间';
|
comment on column lf.sys_meta.bak is '备注';
|
comment on column lf.sys_meta.geom is '空间位置';
|
-- insert into lf.sys_meta(dirid,depid,geom,sizes,name,path) values (1,1,ST_PolygonFromText('MultiPolygon (((100 0,120 0,120 20,100 20,100 0)))', 4490),0.001,'a.jpg','c:\a.jpg');
|
|
select a.*,st_astext(geom) from lf.sys_meta a; select id,name,dirid,depid,verid,type,sizes,create_user,create_time,update_user,update_time,st_astext(geom) from lf.sys_meta;
|
----------------------------------------------------------------------------------------------------- 17.附件表
|
-- DROP TABLE IF EXISTS lf.sys_attach;
|
create table lf.sys_attach(
|
id serial primary key,
|
name varchar(256),
|
tab varchar(100),
|
tab_guid varchar(40),
|
guid varchar(40) default new_guid(),
|
path varchar(512),
|
sizes float8 default 0;
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone
|
);
|
comment on table lf.sys_attach is '附件表';
|
comment on column lf.sys_attach.id is '主键ID';
|
comment on column lf.sys_attach.name is '文件名';
|
comment on column lf.sys_attach.tab is '表名：如 public.data_dir';
|
comment on column lf.sys_attach.tab_guid is '表ID';
|
comment on column lf.sys_attach.guid is '文件ID';
|
comment on column lf.sys_attach.path is '存储路径';
|
comment on column lf.sys_attach.sizes is '文件大小：单位MB';
|
comment on column lf.sys_attach.create_user is '创建人ID';
|
comment on column lf.sys_attach.create_time is '创建时间';
|
comment on column lf.sys_attach.update_user is '更新人ID';
|
comment on column lf.sys_attach.update_time is '更新时间';
|
--insert into lf.sys_attach (name,tab,path) values ('a.jpg','public.data_dir','c:\a.jpg');
|
--alter table lf.sys_attach alter column sizes type float8;
|
|
select * from lf.sys_attach; select length(guid) from lf.sys_attach limit 1; select * from lf.sys_attach;
|
----------------------------------------------------------------------------------------------------- 18.版本表
|
-- DROP TABLE IF EXISTS lf.sys_ver;
|
create table lf.sys_ver(
|
id serial primary key,
|
dirid integer,
|
name varchar(50),
|
descr varchar(256),
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone
|
);
|
comment on table lf.sys_ver is '附件表';
|
comment on column lf.sys_ver.id is '主键ID';
|
comment on column lf.sys_ver.dirid is '目录ID';
|
comment on column lf.sys_ver.name is '名称';
|
comment on column lf.sys_ver.descr is '描述';
|
comment on column lf.sys_ver.create_user is '创建人ID';
|
comment on column lf.sys_ver.create_time is '创建时间';
|
comment on column lf.sys_ver.update_user is '更新人ID';
|
comment on column lf.sys_ver.update_time is '更新时间';
|
insert into lf.sys_ver (id,dirid,name) values (0,0,'默认');
|
--insert into lf.sys_ver (dirid,name) values (1003,'2020-02-01');
|
|
select * from lf.sys_ver order by id;
|
----------------------------------------------------------------------------------------------------- 19.数据字典表
|
-- DROP TABLE IF EXISTS lf.sys_dict;
|
create table lf.sys_dict(
|
id serial primary key,
|
ns varchar(20) default 'lf',
|
tab varchar(100),
|
tab_desc varchar(100),
|
field varchar(100),
|
alias varchar(100),
|
type varchar(100),
|
len integer default 0,
|
precision smallint default 0,
|
order_num smallint default 0,
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone,
|
tabletype varchar(20),
|
unit varchar(50),
|
domain_na varchar(100),
|
showtype smallint,
|
editable smallint,
|
status smallint default 0,
|
bak varchar(1024)
|
);
|
comment on table lf.sys_dict is '附件表';
|
comment on column lf.sys_dict.id is '主键ID';
|
comment on column lf.sys_dict.ns is '表空间';
|
comment on column lf.sys_dict.tab is '表名';
|
comment on column lf.sys_dict.tab_desc is '表名描述';
|
comment on column lf.sys_dict.field is '字段名';
|
comment on column lf.sys_dict.alias is '别名';
|
comment on column lf.sys_dict.type is '类型';
|
comment on column lf.sys_dict.len is '长度';
|
comment on column lf.sys_dict.precision is '精度';
|
comment on column lf.sys_dict.order_num is '序号';
|
comment on column lf.sys_dict.create_user is '创建人ID';
|
comment on column lf.sys_dict.create_time is '创建时间';
|
comment on column lf.sys_dict.update_user is '更新人ID';
|
comment on column lf.sys_dict.update_time is '更新时间';
|
comment on column lf.sys_dict.tabletype is '表类型';
|
comment on column lf.sys_dict.unit is '单位';
|
comment on column lf.sys_dict.domain_na is '值域名称';
|
comment on column lf.sys_dict.showtype is '显示类型';
|
comment on column lf.sys_dict.editable is '是否可编辑';
|
comment on column lf.sys_dict.status is '状态：0-正常，1-删除，-1-废弃';
|
comment on column lf.sys_dict.bak is '备注';
|
-- alter sequence lf.sys_dict_id_seq restart with 300;
|
|
select count(*) from lf.sys_dict where status=0 and tab='sys_user';
|
select * from lf.sys_dict order by ns,tab,order_num limit 10 offset 0;
|
select distinct ns,tab,fn_get_entity(tab) entity,tab_desc,tabletype from lf.sys_dict order by tab;
|
----------------------------------------------------------------------------------------------------- 20.样式表
|
-- DROP TABLE IF EXISTS lf.sys_style;
|
create table lf.sys_style(
|
id serial primary key,
|
name varchar(50),
|
type varchar(20),
|
dirid integer default 1,
|
depid integer default 1,
|
ver varchar(20) default 'v1.0',
|
status smallint default 1,
|
precision varchar(50),
|
descr varchar(256),
|
fname varchar(50),
|
file_guid varchar(40) default new_guid(),
|
vname varchar(50),
|
view_guid varchar(40) default new_guid(),
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone,
|
bak varchar(1024)
|
);
|
comment on table lf.sys_style is '样式表';
|
comment on column lf.sys_style.id is '主键ID';
|
comment on column lf.sys_style.name is '名称';
|
comment on column lf.sys_style.type is '类型';
|
comment on column lf.sys_style.dirid is '目录ID';
|
comment on column lf.sys_style.depid is '单位ID';
|
comment on column lf.sys_style.ver is '版本';
|
comment on column lf.sys_style.status is '状态：0-停用,1-启用';
|
comment on column lf.sys_style.precision is '精度：如 1:1000';
|
comment on column lf.sys_style.descr is '描述';
|
comment on column lf.sys_style.fname is '样式文件名';
|
comment on column lf.sys_style.file_guid is '样式文件ID';
|
comment on column lf.sys_style.vname is '预览文件名';
|
comment on column lf.sys_style.view_guid is '预览文件ID';
|
comment on column lf.sys_style.create_user is '创建人ID';
|
comment on column lf.sys_style.create_time is '创建时间';
|
comment on column lf.sys_style.update_user is '更新人ID';
|
comment on column lf.sys_style.update_time is '更新时间';
|
comment on column lf.sys_style.bak is '备注';
|
--insert into lf.sys_style (name,type,precision,descr) values ('line.edp','edp','1:1000','管道中心线样式文件');
|
|
select count(*) from lf.sys_style where name like '%.edp';
|
select * from lf.sys_style order by ns,tab,order_num limit 10 offset 0;
|
----------------------------------------------------------------------------------------------------- 21.下载记录表
|
-- DROP TABLE IF EXISTS lf.sys_download;
|
create table lf.sys_download(
|
id serial primary key,
|
name varchar(50),
|
type smallint default 1,
|
depid integer default 1,
|
sizes float8 default 0,
|
dcount integer default 0,
|
pwd varchar(200),
|
url varchar(512),
|
descr varchar(256),
|
guid varchar(40) default new_guid(),
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
download_user integer,
|
download_time timestamp(6) without time zone,
|
geom geometry default null,
|
bak varchar(1024)
|
CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2),
|
CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POLYGON'::text OR geom IS NULL),
|
CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 4490)
|
);
|
create index index_sys_download_geom on lf.sys_download using gist (geom);
|
comment on table lf.sys_download is '下载记录表';
|
comment on column lf.sys_download.id is '主键ID';
|
comment on column lf.sys_download.name is '名称';
|
comment on column lf.sys_download.type is '类型：1-Shp文件，2-专题图，3-元数据，4-业务数据，5-管道分析';
|
comment on column lf.sys_download.depid is '单位ID';
|
comment on column lf.sys_download.sizes is '文件大小：单位MB';
|
comment on column lf.sys_download.dcount is '下载次数';
|
comment on column lf.sys_download.pwd is 'MD5';
|
comment on column lf.sys_download.url is '下载地址';
|
comment on column lf.sys_download.descr is '描述';
|
comment on column lf.sys_download.guid is '文件ID';
|
comment on column lf.sys_download.create_user is '创建人ID';
|
comment on column lf.sys_download.create_time is '创建时间';
|
comment on column lf.sys_download.download_user is '下载人ID';
|
comment on column lf.sys_download.download_time is '下载时间';
|
comment on column lf.sys_download.bak is '备注';
|
comment on column lf.sys_download.geom is '空间位置';
|
-- insert into lf.sys_download (name,pwd,url,descr) values ('2022-09-27','123456','','测试下载');
|
|
select * from lf.sys_download order by id;
|
----------------------------------------------------------------------------------------------------- 22.消息通知表
|
-- DROP TABLE IF EXISTS lf.sys_msg;
|
create table lf.sys_msg(
|
id serial primary key,
|
title varchar(50),
|
msg varchar(200),
|
guid varchar(40),
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone
|
);
|
comment on table lf.sys_msg is '消息通知表';
|
comment on column lf.sys_msg.id is '主键ID';
|
comment on column lf.sys_msg.title is '标题';
|
comment on column lf.sys_msg.msg is '消息内容';
|
comment on column lf.sys_msg.guid is '消息ID';
|
comment on column lf.sys_msg.create_user is '创建人ID';
|
comment on column lf.sys_msg.create_time is '创建时间';
|
comment on column lf.sys_msg.update_user is '接受人ID';
|
comment on column lf.sys_msg.update_time is '接受时间';
|
--insert into lf.sys_msg (title,msg,guid,update_user) values ('2022-09-26','数据下载',new_guid(),2);
|
|
select * from lf.sys_msg;
|
----------------------------------------------------------------------------------------------------- 23.参数设置表
|
-- DROP TABLE IF EXISTS lf.sys_args;
|
create table lf.sys_args(
|
id serial primary key,
|
name varchar(50),
|
cvalue integer default 0,
|
dvalue integer default 0,
|
min_value integer default 0,
|
max_value integer default 0,
|
times integer default 1,
|
descr varchar(256),
|
mark varchar(50),
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone
|
);
|
comment on table lf.sys_args is '参数设置表';
|
comment on column lf.sys_args.id is '主键ID';
|
comment on column lf.sys_args.name is '名称';
|
comment on column lf.sys_args.cvalue is '当前值';
|
comment on column lf.sys_args.dvalue is '默认值';
|
comment on column lf.sys_args.min_value is '最小值';
|
comment on column lf.sys_args.max_value is '最大值';
|
comment on column lf.sys_args.times is '倍数';
|
comment on column lf.sys_args.descr is '描述';
|
comment on column lf.sys_args.mark is '标识';
|
comment on column lf.sys_args.create_user is '创建人ID';
|
comment on column lf.sys_args.create_time is '创建时间';
|
comment on column lf.sys_args.update_user is '更新人ID';
|
comment on column lf.sys_args.update_time is '更新时间';
|
/* insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('密码出错次数',5,5,3,20,1,'默认用户密码连续输入出错5次，就暂时拒绝登录。','PWD_ERR_COUNT');
|
insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('密码出错等待时间',5,5,1,60,1,'默认用户密码连续输入多次出错时，5分钟内拒绝登录。','PWD_ERR_TIME');
|
insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('自动登出时间',15,15,3,1440,1,'默认用户15分钟不操作，就自动登出系统。','AUTO_LOGOUT');
|
insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('令牌有效期',240,240,60,1440,1,'令牌默认有效期为240分钟，超时将失效。','TOKEN_EXPIRE');
|
insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('缓存有效期',240,240,60,1440,1,'缓存默认有效期为240分钟，超时将失效。','CACHE_EXPIRE');
|
insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('最大文件数',2000,2000,500,2500,1,'单个文件夹下默认最大存储文件的数目为2000个。','MAX_FILES');
|
insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('Cookie有效期',240,240,60,43200,60,'Cookie默认有效期为240分钟，超时将失效。','COOKIE_MAX_AGE');
|
insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('附件大小',50,50,5,1024,1048576,'单个附件大小默认最大为50MB。','MAX_FILE_SIZE');
|
insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('用户访问量',5000,5000,5,1000000,1,'当超出用户访问量时，就拒绝登录。','MAX_USER_LOGIN');
|
insert into lf.sys_args (name,cvalue,dvalue,min_value,max_value,times,descr,mark) values ('服务上限',5000,5000,5,1000000,1,'当超出服务上限时，就拒绝访问。','MAX_SERVERS'); */
|
|
select * from lf.sys_args order by id;
|
----------------------------------------------------------------------------------------------------- 24.黑名单表
|
-- DROP TABLE IF EXISTS lf.sys_blacklist;
|
create table lf.sys_blacklist(
|
id serial primary key,
|
ip varchar(50) unique,
|
type smallint default 1,
|
visit integer default 0,
|
descr varchar(50),
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone
|
);
|
comment on table lf.sys_blacklist is '黑名单表';
|
comment on column lf.sys_blacklist.id is '主键ID';
|
comment on column lf.sys_blacklist.ip is 'IP地址';
|
comment on column lf.sys_blacklist.type is '类别：1-黑名单,2-白名单';
|
comment on column lf.sys_blacklist.visit is '访问次数';
|
comment on column lf.sys_blacklist.descr is '描述';
|
comment on column lf.sys_blacklist.create_user is '创建人ID';
|
comment on column lf.sys_blacklist.create_time is '创建时间';
|
comment on column lf.sys_blacklist.update_user is '更新人ID';
|
comment on column lf.sys_blacklist.update_time is '更新时间';
|
/* insert into lf.sys_blacklist (ip,type) values ('192.168.20.1',1);
|
insert into lf.sys_blacklist (ip,type) values ('192.168.20.205',2);
|
insert into lf.sys_blacklist (ip,type) values ('127.0.0.1',2);
|
insert into lf.sys_blacklist (ip,type) values ('localhost',2);
|
insert into lf.sys_blacklist (ip,type) values ('0:0:0:0:0:0:0:1',2);*/
|
|
select * from lf.sys_blacklist;
|
----------------------------------------------------------------------------------------------------- 25.标绘表
|
-- DROP TABLE IF EXISTS lf.sys_mark;
|
create table lf.sys_mark(
|
id serial primary key,
|
name varchar(50),
|
wkt text,
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone
|
);
|
comment on table lf.sys_mark is '标绘表';
|
comment on column lf.sys_mark.id is '主键ID';
|
comment on column lf.sys_mark.name is '名称';
|
comment on column lf.sys_mark.wkt is 'WKT';
|
comment on column lf.sys_mark.create_user is '创建人ID';
|
comment on column lf.sys_mark.create_time is '创建时间';
|
comment on column lf.sys_mark.update_user is '更新人ID';
|
comment on column lf.sys_mark.update_time is '更新时间';
|
|
select * from lf.sys_mark;
|
----------------------------------------------------------------------------------------------------- 26.值域表
|
-- drop table if exists lf.sys_domain;
|
create table if not exists lf.sys_domain(
|
id serial primary key,
|
dom_desc varchar(100),
|
dom_name varchar(50),
|
dom_code varchar(50),
|
code_desc varchar(50),
|
level integer,
|
orderid integer,
|
bsm varchar(50),
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone,
|
bak varchar(1024)
|
);
|
comment on table lf.sys_domain is '值域表';
|
comment on column lf.sys_domain.id is '主键id';
|
comment on column lf.sys_domain.dom_desc is '值域表描述';
|
comment on column lf.sys_domain.dom_name is '值域表名';
|
comment on column lf.sys_domain.dom_code is '编码';
|
comment on column lf.sys_domain.code_desc is '编码描述';
|
comment on column lf.sys_domain.level is '层级';
|
comment on column lf.sys_domain.orderid is '序号';
|
comment on column lf.sys_domain.bsm is '标识码';
|
comment on column lf.sys_domain.create_user is '创建人id';
|
comment on column lf.sys_domain.create_time is '创建时间';
|
comment on column lf.sys_domain.update_user is '更新人id';
|
comment on column lf.sys_domain.update_time is '更新时间';
|
comment on column lf.sys_domain.bak is '备注';
|
|
select * from lf.sys_domain order by dom_name,orderid;
|
----------------------------------------------------------------------------------------------------- 27.报告模板表
|
-- drop table if exists lf.sys_report;
|
create table if not exists lf.sys_report (
|
id serial primary key,
|
name varchar(100),
|
type varchar(50),
|
code varchar(50),
|
fname varchar(256),
|
guid varchar(38),
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone,
|
bak varchar(1024)
|
);
|
comment on table lf.sys_report is '报告模板表';
|
comment on column lf.sys_report.id is '主键ID';
|
comment on column lf.sys_report.name is '名称';
|
comment on column lf.sys_report.type is '文件类型：1-Word模板，2-Excel模板';
|
comment on column lf.sys_report.code is '编码';
|
comment on column lf.sys_report.fname is '文件名';
|
comment on column lf.sys_report.guid is '文件GUID';
|
comment on column lf.sys_report.create_user is '创建人id';
|
comment on column lf.sys_report.create_time is '创建时间';
|
comment on column lf.sys_report.update_user is '更新人id';
|
comment on column lf.sys_report.update_time is '更新时间';
|
comment on column lf.sys_report.bak is '备注';
|
-- insert into lf.sys_report (name,type,guid,code) values ('统计用户量',2,'1\aa.xlsx','');
|
|
select * from lf.sys_report;
|
----------------------------------------------------------------------------------------------------- 28.下载日志表
|
-- drop table if exists lf.sys_downlog;
|
create table lf.sys_downlog (
|
id serial primary key,
|
downid integer default 0,
|
ip varchar(30),
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone
|
);
|
comment on table lf.sys_downlog is '下载日志表';
|
comment on column lf.sys_downlog.id is '主键id';
|
comment on column lf.sys_downlog.ip is 'IP地址';
|
comment on column lf.sys_downlog.create_user is '创建人id';
|
comment on column lf.sys_downlog.create_time is '创建时间';
|
comment on column lf.sys_downlog.update_user is '更新人id';
|
comment on column lf.sys_downlog.update_time is '更新时间';
|
-- insert into lf.sys_downlog(downid,ip,create_user) values (100,'192.168.20.39',1);
|
|
select * from lf.sys_downlog;
|
----------------------------------------------------------------------------------------------------- 29.数据申请表
|
-- drop table if exists lf.sys_apply;
|
create table lf.sys_apply (
|
id serial primary key,
|
userid integer default 0,
|
depids varchar(500),
|
tabs varchar(2000),
|
entities varchar(2000),
|
wkt varchar(4000),
|
pwd varchar(200),
|
status integer default 0,
|
count integer default 0,
|
descr varchar(50),
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone
|
);
|
comment on table lf.sys_apply is '数据申请表';
|
comment on column lf.sys_apply.id is '主键ID';
|
comment on column lf.sys_apply.userid is '用户ID';
|
comment on column lf.sys_apply.depids is '单位ID';
|
comment on column lf.sys_apply.tabs is '表名';
|
comment on column lf.sys_apply.entities is '实体名';
|
comment on column lf.sys_apply.wkt is 'WKT';
|
comment on column lf.sys_apply.pwd is '密码';
|
comment on column lf.sys_apply.status is '状态：-10-作废，-1-打回，0~9-审核中，10-通过';
|
comment on column lf.sys_apply.count is '审核总数';
|
comment on column lf.sys_apply.descr is '描述';
|
comment on column lf.sys_apply.create_user is '创建人id';
|
comment on column lf.sys_apply.create_time is '创建时间';
|
comment on column lf.sys_apply.update_user is '更新人id';
|
comment on column lf.sys_apply.update_time is '更新时间';
|
|
select * from lf.sys_apply;
|
----------------------------------------------------------------------------------------------------- 30.申请流程表
|
-- drop table if exists lf.sys_flow;
|
create table lf.sys_flow (
|
id serial primary key,
|
applyid integer,
|
depid integer,
|
userid integer,
|
status integer default 0,
|
descr varchar(50),
|
create_user integer default 1,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone
|
);
|
comment on table lf.sys_flow is '申请流程表';
|
comment on column lf.sys_flow.id is '主键id';
|
comment on column lf.sys_flow.applyid is '申请ID';
|
comment on column lf.sys_flow.depid is '单位ID';
|
comment on column lf.sys_flow.userid is '用户ID';
|
comment on column lf.sys_flow.status is '状态：-1-打回，0-待审核，1-通过';
|
comment on column lf.sys_flow.descr is '描述';
|
comment on column lf.sys_flow.create_user is '创建人id';
|
comment on column lf.sys_flow.create_time is '创建时间';
|
comment on column lf.sys_flow.update_user is '更新人id';
|
comment on column lf.sys_flow.update_time is '更新时间';
|
|
select * from lf.sys_flow;
|
----------------------------------------------------------------------------------------------------- 31.FME日志表
|
create table lf.sys_fme_log (
|
id serial primary key,
|
parentid varchar(38),
|
dirpath varchar(1024),
|
pg_ns varchar(10),
|
tcmc varchar(150),
|
tcdm varchar(50),
|
count integer default 0,
|
create_time timestamp(6) without time zone default now()
|
);
|
comment on table lf.sys_fme_log is 'FME日志表';
|
comment on column lf.sys_fme_log.id is '主键ID';
|
comment on column lf.sys_fme_log.parentid is '父表GUID';
|
comment on column lf.sys_fme_log.dirpath is '文件路径';
|
comment on column lf.sys_fme_log.pg_ns is '表架构';
|
comment on column lf.sys_fme_log.tcmc is '图层名称';
|
comment on column lf.sys_fme_log.tcdm is '表名';
|
comment on column lf.sys_fme_log.count is '行数';
|
comment on column lf.sys_fme_log.create_time is '创建时间';
|
|
select * from lf.sys_fme_log; -- delete from lf.sys_fme_log;
|
----------------------------------------------------------------------------------------------------- 32.图层表
|
-- DROP TABLE IF EXISTS lf.sys_layer;
|
create table lf.sys_layer(
|
id serial primary key,
|
pid integer,
|
cn_name varchar(50),
|
en_name varchar(50),
|
url varchar(512),
|
test_url varchar(512),
|
type smallint,
|
icon varchar(256),
|
level integer,
|
order_num integer,
|
is_show smallint,
|
create_user integer,
|
create_time timestamp(6) without time zone default now(),
|
update_user integer,
|
update_time timestamp(6) without time zone,
|
bak varchar(1024)
|
);
|
comment on table lf.sys_layer is '图层表';
|
comment on column lf.sys_layer.id is '主键ID';
|
comment on column lf.sys_layer.pid is '父ID：0-根节点';
|
comment on column lf.sys_layer.cn_name is '中文名称';
|
comment on column lf.sys_layer.en_name is '英文名称';
|
comment on column lf.sys_layer.url is '图层地址';
|
comment on column lf.sys_layer.test_url is '测试地址';
|
comment on column lf.sys_layer.type is '类型：1-图层组,2-图层';
|
comment on column lf.sys_layer.icon is '图标';
|
comment on column lf.sys_layer.level is '层级：0-根节点';
|
comment on column lf.sys_layer.order_num is '排序号';
|
comment on column lf.sys_layer.is_show is '是否显示';
|
comment on column lf.sys_layer.create_user is '创建人ID';
|
comment on column lf.sys_layer.create_time is '创建时间';
|
comment on column lf.sys_layer.update_user is '更新人ID';
|
comment on column lf.sys_layer.update_time is '更新时间';
|
comment on column lf.sys_layer.bak is '备注';
|
|
select * from lf.sys_layer;
|
----------------------------------------------------------------------------------------------------- 33.服务日志表
|
-- DROP TABLE IF EXISTS lf.sys_serve_log;
|
create table lf.sys_serve_log(
|
id serial8,
|
resid integer,
|
name varchar(100),
|
type varchar(50),
|
url varchar(4096),
|
create_user integer,
|
create_time timestamp(6) without time zone default now(),
|
bak varchar(1024)
|
) partition by range(create_time);
|
|
create table lf.sys_serve_log_2023 partition of lf.sys_serve_log for values from ('2023-01-01') to ('2024-01-01');
|
create INDEX index_sys_serve_log_2023_optime on lf.sys_serve_log_2023 (create_time);
|
create table lf.sys_serve_log_2024 partition of lf.sys_serve_log for values from ('2024-01-01') to ('2025-01-01');
|
create INDEX index_sys_serve_log_2024_optime on lf.sys_serve_log_2024 (create_time);
|
create table lf.sys_serve_log_2025 partition of lf.sys_serve_log for values from ('2025-01-01') to ('2026-01-01');
|
create INDEX index_sys_serve_log_2025_optime on lf.sys_serve_log_2025 (create_time);
|
create table lf.sys_serve_log_2026 partition of lf.sys_serve_log for values from ('2026-01-01') to ('2027-01-01');
|
create INDEX index_sys_serve_log_2026_optime on lf.sys_serve_log_2026 (create_time);
|
create table lf.sys_serve_log_2027 partition of lf.sys_serve_log for values from ('2027-01-01') to ('2028-01-01');
|
create INDEX index_sys_serve_log_2027_optime on lf.sys_serve_log_2027 (create_time);
|
|
comment on table lf.sys_serve_log is '服务日志表';
|
comment on column lf.sys_serve_log.id is '主键ID';
|
comment on column lf.sys_serve_log.resid is '服务ID';
|
comment on column lf.sys_serve_log.name is '名称';
|
comment on column lf.sys_serve_log.type is '类型';
|
comment on column lf.sys_serve_log.url is '图层地址';
|
comment on column lf.sys_serve_log.create_user is '访问人ID';
|
comment on column lf.sys_serve_log.create_time is '访问时间';
|
comment on column lf.sys_serve_log.bak is '备注';
|
/* insert into lf.sys_serve_log (resid,name,type,url) values (1,'全国影像图','01','http://192.168.20.205/LFData/2d/tiles/img/{z}/{x}/{y}.png');
|
insert into lf.sys_serve_log (resid,name,type,url) values (2,'全国矢量图','02','http://192.168.20.205/LFData/2d/tiles/vec/{z}/{x}/{y}.png'); */
|
|
select * from lf.sys_serve_log;
|
----------------------------------------------------------------------------------------------------- vacuum analyze md.md_zxcg;
|
