451 lines
9.0 KiB
Markdown
451 lines
9.0 KiB
Markdown
|
||
|
||
```sql
|
||
/* ---------------------------------------------------- */
|
||
/* Generated by Enterprise Architect Version 10.10 */
|
||
/* Created On : 22-1月-2026 10:57:00 */
|
||
/* DBMS : PostgreSQL */
|
||
/* ---------------------------------------------------- */
|
||
|
||
/* Drop Sequences for Autonumber Columns */
|
||
|
||
|
||
|
||
DROP SEQUENCE IF EXISTS cg_template_id_seq
|
||
;
|
||
|
||
/* Drop Tables */
|
||
|
||
DROP TABLE IF EXISTS cg_fun_item CASCADE
|
||
;
|
||
|
||
DROP TABLE IF EXISTS cg_fun_module CASCADE
|
||
;
|
||
|
||
DROP TABLE IF EXISTS cg_fun_operation CASCADE
|
||
;
|
||
|
||
DROP TABLE IF EXISTS cg_menu CASCADE
|
||
;
|
||
|
||
DROP TABLE IF EXISTS cg_role CASCADE
|
||
;
|
||
|
||
DROP TABLE IF EXISTS cg_role_fun CASCADE
|
||
;
|
||
|
||
DROP TABLE IF EXISTS cg_template CASCADE
|
||
;
|
||
|
||
/* Create Tables */
|
||
|
||
CREATE TABLE cg_fun_item
|
||
(
|
||
id bigint NOT NULL, -- id
|
||
module_id bigint NOT NULL, -- 模块ID
|
||
item_id bigint NOT NULL, -- 功能ID
|
||
item_name varchar(50) NOT NULL, -- 功能名称
|
||
item_code varchar(64) NOT NULL, -- 功能编码
|
||
is_tenant boolean NOT NULL DEFAULT true, -- 是否租户
|
||
describe varchar(250) NULL, -- 说明
|
||
sort_order smallint NOT NULL DEFAULT 0 -- 排序
|
||
)
|
||
;
|
||
|
||
CREATE TABLE cg_fun_module
|
||
(
|
||
id bigint NOT NULL, -- id
|
||
module_code varchar(16) NOT NULL, -- 模块编码
|
||
module_name varchar(8) NOT NULL, -- 模块名称
|
||
package_name varchar(250) NOT NULL, -- 包名称
|
||
api_package_name varchar(250) NOT NULL, -- 参数包名
|
||
sort_order smallint NOT NULL DEFAULT 0, -- 排序
|
||
describe varchar(250) NULL -- 描述
|
||
)
|
||
;
|
||
|
||
CREATE TABLE cg_fun_operation
|
||
(
|
||
id bigint NOT NULL, -- id
|
||
module_id bigint NOT NULL, -- 模块ID
|
||
item_id bigint NOT NULL, -- 功能ID(表主键ID)
|
||
is_green_light boolean NOT NULL DEFAULT false, -- 直接放行
|
||
operation_id bigint NOT NULL, -- 操作ID
|
||
operation_code varchar(32) NULL, -- 操作编码
|
||
fun_name varchar(16) NOT NULL, -- 操作名称
|
||
fun_type smallint NOT NULL, -- 操作类型>>查>>改>>删>>增
|
||
request_type smallint NOT NULL, -- 请求类型
|
||
is_req_params boolean NOT NULL DEFAULT true, -- 是否需要请求参数
|
||
is_res_params boolean NOT NULL DEFAULT true, -- 是否需要响应参数
|
||
is_page boolean NOT NULL DEFAULT true, -- 是否分页
|
||
path_params varchar(16) NULL, -- 路径参数（与请求参数true互斥）
|
||
usable_config json NOT NULL DEFAULT '[]', -- 可配置数据类型json[]
|
||
field_config json NOT NULL DEFAULT '[]', -- 可配置字段
|
||
sort_order smallint NOT NULL DEFAULT 0, -- 排序
|
||
describe varchar(250) NULL -- 说明说明
|
||
)
|
||
;
|
||
|
||
CREATE TABLE cg_menu
|
||
(
|
||
id bigint NOT NULL, -- 主键ID
|
||
client_type smallint NOT NULL, -- 客户端类型：0 PC端，1 小程序端，2 H5端
|
||
menu_name varchar(32) NOT NULL, -- 菜单名称
|
||
parent_id bigint NOT NULL DEFAULT 0, -- 父菜单ID
|
||
fun_id bigint NULL, -- 操作ID/来自操作表
|
||
menu_type smallint NOT NULL DEFAULT 0, -- 0菜单1按钮
|
||
path varchar(64) NULL, -- 路由路径
|
||
page_path varchar(64) NULL,
|
||
icon varchar(64) NULL, -- 菜单图标
|
||
is_tenant boolean NOT NULL DEFAULT true, -- 是否租户
|
||
is_hide boolean NOT NULL DEFAULT false, -- 是否隐藏
|
||
sort_order smallint NOT NULL DEFAULT 0 -- 排序
|
||
)
|
||
;
|
||
|
||
CREATE TABLE cg_role
|
||
(
|
||
id bigint NOT NULL, -- 主键ID
|
||
role_name varchar(32) NOT NULL, -- 角色名称
|
||
role_type smallint NOT NULL -- 角色类型 0平台 1套餐
|
||
)
|
||
;
|
||
|
||
CREATE TABLE cg_role_fun
|
||
(
|
||
id bigint NOT NULL, -- 主键ID
|
||
role_id bigint NOT NULL, -- 角色ID
|
||
fun_id bigint NOT NULL, -- 操作ID
|
||
data_scope smallint NOT NULL DEFAULT 0, -- 数据权限默认:0无
|
||
assign_data_scope json NOT NULL DEFAULT '[]', -- 指定的数据权限范围
|
||
exclude_field json NOT NULL DEFAULT '[]', -- 排除的字段
|
||
client_type smallserial NOT NULL -- 客户端类型：0 PC端，1 小程序端，2 H5端
|
||
)
|
||
;
|
||
|
||
CREATE TABLE cg_template
|
||
(
|
||
id bigint NOT NULL DEFAULT NEXTVAL(('"cg_template_id_seq"'::text)::regclass), -- 主键ID
|
||
is_use boolean NOT NULL DEFAULT false, -- 是否使用
|
||
template_name varchar(32) NOT NULL, -- 模板名称
|
||
template_type smallint NOT NULL, -- 模板类型
|
||
content text NOT NULL -- 正文
|
||
)
|
||
;
|
||
|
||
/* Create Primary Keys, Indexes, Uniques, Checks */
|
||
|
||
ALTER TABLE cg_fun_item ADD CONSTRAINT "PK_cg_fun_item"
|
||
PRIMARY KEY (id)
|
||
;
|
||
|
||
ALTER TABLE cg_fun_item
|
||
ADD CONSTRAINT u_module_item_code UNIQUE (module_id,item_id,item_code)
|
||
;
|
||
|
||
ALTER TABLE cg_fun_item
|
||
ADD CONSTRAINT u_module_item_id UNIQUE (module_id,item_id)
|
||
;
|
||
|
||
ALTER TABLE cg_fun_module ADD CONSTRAINT "PK_cg_fun_module"
|
||
PRIMARY KEY (id)
|
||
;
|
||
|
||
ALTER TABLE cg_fun_operation ADD CONSTRAINT "PK_cg_fun_operation"
|
||
PRIMARY KEY (id)
|
||
;
|
||
|
||
ALTER TABLE cg_fun_operation
|
||
ADD CONSTRAINT u_module_item_opration UNIQUE (module_id,item_id,operation_code)
|
||
;
|
||
|
||
ALTER TABLE cg_fun_operation
|
||
ADD CONSTRAINT u_module_item_operation_id UNIQUE (module_id,item_id,operation_id)
|
||
;
|
||
|
||
ALTER TABLE cg_menu ADD CONSTRAINT "PK_cg_menu"
|
||
PRIMARY KEY (id)
|
||
;
|
||
|
||
ALTER TABLE cg_role ADD CONSTRAINT "PK_cg_role"
|
||
PRIMARY KEY (id)
|
||
;
|
||
|
||
ALTER TABLE cg_role_fun ADD CONSTRAINT "PK_cg_role_fun"
|
||
PRIMARY KEY (id)
|
||
;
|
||
|
||
ALTER TABLE cg_template ADD CONSTRAINT "PK_cg_template"
|
||
PRIMARY KEY (id)
|
||
;
|
||
|
||
/* Create Table Comments, Sequences for Autonumber Columns */
|
||
|
||
COMMENT ON TABLE cg_fun_item
|
||
IS '功能项配置'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_item.id
|
||
IS 'id'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_item.module_id
|
||
IS '模块ID'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_item.item_id
|
||
IS '功能ID'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_item.item_name
|
||
IS '功能名称'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_item.item_code
|
||
IS '功能编码'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_item.is_tenant
|
||
IS '是否租户'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_item.describe
|
||
IS '说明'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_item.sort_order
|
||
IS '排序'
|
||
;
|
||
|
||
COMMENT ON TABLE cg_fun_module
|
||
IS '模块配置'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_module.id
|
||
IS 'id'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_module.module_code
|
||
IS '模块编码'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_module.module_name
|
||
IS '模块名称'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_module.package_name
|
||
IS '包名称'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_module.api_package_name
|
||
IS '参数包名'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_module.sort_order
|
||
IS '排序'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_module.describe
|
||
IS '描述'
|
||
;
|
||
|
||
COMMENT ON TABLE cg_fun_operation
|
||
IS '操作配置'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_operation.id
|
||
IS 'id'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_operation.module_id
|
||
IS '模块ID'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_operation.item_id
|
||
IS '功能ID(表主键ID)'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_operation.is_green_light
|
||
IS '直接放行'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_operation.operation_id
|
||
IS '操作ID'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_operation.operation_code
|
||
IS '操作编码'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_operation.fun_name
|
||
IS '操作名称'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_operation.fun_type
|
||
IS '操作类型>>查>>改>>删>>增'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_operation.request_type
|
||
IS '请求类型'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_operation.is_req_params
|
||
IS '是否需要请求参数'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_operation.is_res_params
|
||
IS '是否需要响应参数'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_operation.is_page
|
||
IS '是否分页'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_operation.path_params
|
||
IS '路径参数（与请求参数true互斥）'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_operation.usable_config
|
||
IS '可配置数据类型json[]'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_operation.field_config
|
||
IS '可配置字段'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_operation.sort_order
|
||
IS '排序'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_fun_operation.describe
|
||
IS '说明说明'
|
||
;
|
||
|
||
COMMENT ON TABLE cg_menu
|
||
IS '菜单'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_menu.id
|
||
IS '主键ID'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_menu.client_type
|
||
IS '客户端类型：0 PC端，1 小程序端，2 H5端'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_menu.menu_name
|
||
IS '菜单名称'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_menu.parent_id
|
||
IS '父菜单ID'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_menu.fun_id
|
||
IS '操作ID/来自操作表'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_menu.menu_type
|
||
IS '0菜单1按钮'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_menu.path
|
||
IS '路由路径'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_menu.icon
|
||
IS '菜单图标'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_menu.is_tenant
|
||
IS '是否租户'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_menu.is_hide
|
||
IS '是否隐藏'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_menu.sort_order
|
||
IS '排序'
|
||
;
|
||
|
||
COMMENT ON TABLE cg_role
|
||
IS '角色表'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_role.id
|
||
IS '主键ID'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_role.role_name
|
||
IS '角色名称'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_role.role_type
|
||
IS '角色类型 0平台 1套餐'
|
||
;
|
||
|
||
COMMENT ON TABLE cg_role_fun
|
||
IS '角色权限表'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_role_fun.id
|
||
IS '主键ID'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_role_fun.role_id
|
||
IS '角色ID'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_role_fun.fun_id
|
||
IS '操作ID'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_role_fun.data_scope
|
||
IS '数据权限默认:0无'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_role_fun.assign_data_scope
|
||
IS '指定的数据权限范围'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_role_fun.exclude_field
|
||
IS '排除的字段'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_role_fun.client_type
|
||
IS '客户端类型：0 PC端，1 小程序端，2 H5端'
|
||
;
|
||
|
||
|
||
|
||
COMMENT ON COLUMN cg_template.id
|
||
IS '主键ID'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_template.is_use
|
||
IS '是否使用'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_template.template_name
|
||
IS '模板名称'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_template.template_type
|
||
IS '模板类型'
|
||
;
|
||
|
||
COMMENT ON COLUMN cg_template.content
|
||
IS '正文'
|
||
;
|
||
|
||
CREATE SEQUENCE cg_template_id_seq INCREMENT 1 START 1
|
||
;
|
||
|
||
```
|
||
|
||
|
||
|
||
|
||
|