235 lines
8.9 KiB
MySQL
235 lines
8.9 KiB
MySQL
|
-- 初始化数据库SCHEMA(所有轨道交通信号系统仿真的表、类型等都在rtss SCHEMA下)
|
||
|
|
CREATE SCHEMA rtss;
|
||
|
|
|
||
|
|
-- 创建草稿数据表
|
||
|
|
CREATE TABLE
|
||
|
|
rtss.draft_data (
|
||
|
|
id SERIAL PRIMARY KEY, -- id 自增主键
|
||
|
|
name VARCHAR(128) NOT NULL, -- 草稿名称
|
||
|
|
data_type INT NOT NULL, -- 数据类型
|
||
|
|
data BYTEA, -- 草稿数据
|
||
|
|
default_release_data_id INT NULL, -- 默认发布数据id
|
||
|
|
user_id INT NOT NULL, -- 创建用户id
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT 'now()', -- 创建时间
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT 'now()', -- 更新时间
|
||
|
|
UNIQUE (name, user_id) -- 一个用户的草稿名称唯一
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 创建草稿数据用户索引
|
||
|
|
CREATE INDEX ON rtss.draft_data (user_id);
|
||
|
|
|
||
|
|
-- 创建草稿数据类型索引
|
||
|
|
CREATE INDEX ON rtss.draft_data (data_type);
|
||
|
|
|
||
|
|
-- 注释草稿数据表
|
||
|
|
COMMENT ON TABLE rtss.draft_data IS '草稿数据表';
|
||
|
|
|
||
|
|
-- 注释草稿数据表字段
|
||
|
|
COMMENT ON COLUMN rtss.draft_data.id IS 'id 自增主键';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.draft_data.name IS '草稿名称';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.draft_data.data_type IS '数据类型';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.draft_data.data IS '草稿数据';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.draft_data.user_id IS '创建用户id';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.draft_data.created_at IS '创建时间';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.draft_data.updated_at IS '更新时间';
|
||
|
|
|
||
|
|
-- 创建发布数据表
|
||
|
|
CREATE TABLE
|
||
|
|
rtss.release_data (
|
||
|
|
id SERIAL PRIMARY KEY, -- id 自增主键
|
||
|
|
name VARCHAR(128) NOT NULL UNIQUE, -- 发布数据名称(数据唯一标识)
|
||
|
|
data_type INT NOT NULL, -- 数据类型
|
||
|
|
used_version_id INT NULL, -- 使用的版本数据id
|
||
|
|
user_id INT NOT NULL, -- 发布/更新用户id
|
||
|
|
is_published BOOLEAN NOT NULL DEFAULT TRUE, -- 是否上架
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT 'now()', -- 创建时间
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT 'now()' -- 更新时间
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 注释发布数据表
|
||
|
|
COMMENT ON TABLE rtss.release_data IS '发布数据表';
|
||
|
|
|
||
|
|
-- 注释发布数据表字段
|
||
|
|
COMMENT ON COLUMN rtss.release_data.id IS 'id 自增主键';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.release_data.name IS '发布数据名称(数据唯一标识)';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.release_data.data_type IS '数据类型';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.release_data.used_version_id IS '使用的版本数据id';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.release_data.user_id IS '发布/更新用户id';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.release_data.is_published IS '是否上架';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.release_data.created_at IS '创建时间';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.release_data.updated_at IS '更新时间';
|
||
|
|
|
||
|
|
-- 创建发布数据版本表
|
||
|
|
CREATE TABLE
|
||
|
|
rtss.release_data_version (
|
||
|
|
id SERIAL PRIMARY KEY, -- id 自增主键
|
||
|
|
release_data_id INT NOT NULL, -- 发布数据id
|
||
|
|
data BYTEA NOT NULL, -- 数据
|
||
|
|
version SERIAL NOT NULL, -- 版本号
|
||
|
|
description TEXT NOT NULL, -- 版本描述
|
||
|
|
user_id INT NOT NULL, -- 发布用户id
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT 'now()', -- 创建时间
|
||
|
|
FOREIGN KEY (release_data_id) REFERENCES rtss.release_data (id) ON DELETE CASCADE
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 创建发布数据当前版本外键
|
||
|
|
ALTER TABLE rtss.release_data ADD FOREIGN KEY (used_version_id) REFERENCES rtss.release_data_version (id) ON DELETE SET NULL;
|
||
|
|
|
||
|
|
-- 创建草稿数据默认发布数据外键
|
||
|
|
ALTER TABLE rtss.draft_data ADD FOREIGN KEY (default_release_data_id) REFERENCES rtss.release_data (id) ON DELETE SET NULL;
|
||
|
|
|
||
|
|
-- 注释发布数据版本表
|
||
|
|
COMMENT ON TABLE rtss.release_data_version IS '发布数据版本表';
|
||
|
|
|
||
|
|
-- 注释发布数据版本表字段
|
||
|
|
COMMENT ON COLUMN rtss.release_data_version.id IS 'id 自增主键';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.release_data_version.release_data_id IS '发布数据id';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.release_data_version.data IS '数据';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.release_data_version.version IS '版本号';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.release_data_version.description IS '版本描述';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.release_data_version.user_id IS '发布用户id';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.release_data_version.created_at IS '创建时间';
|
||
|
|
|
||
|
|
-- 创建feature表
|
||
|
|
CREATE TABLE
|
||
|
|
rtss.feature (
|
||
|
|
id SERIAL PRIMARY KEY, -- id 自增主键
|
||
|
|
feature_type INT NOT NULL, -- feature类型
|
||
|
|
name VARCHAR(128) NOT NULL UNIQUE, -- feature名称
|
||
|
|
description TEXT NOT NULL, -- feature描述
|
||
|
|
is_published BOOLEAN NOT NULL DEFAULT TRUE, -- 是否上架
|
||
|
|
creator_id INT NOT NULL, -- 创建用户id
|
||
|
|
updater_id INT NOT NULL, -- 更新用户id
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT 'now()', -- 创建时间
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT 'now()' -- 更新时间
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 注释仿真feature表
|
||
|
|
COMMENT ON TABLE rtss.feature IS 'feature表';
|
||
|
|
|
||
|
|
-- 注释仿真feature表字段
|
||
|
|
COMMENT ON COLUMN rtss.feature.id IS 'id 自增主键';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature.feature_type IS 'feature类型';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature.name IS 'feature名称';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature.description IS 'feature描述';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature.is_published IS '是否上架';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature.creator_id IS '创建用户id';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature.created_at IS '创建时间';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature.updated_at IS '更新时间';
|
||
|
|
|
||
|
|
-- 创建仿真feature和发布数据关联表
|
||
|
|
CREATE TABLE
|
||
|
|
rtss.feature_release_data (
|
||
|
|
feature_id INT NOT NULL, -- 仿真feature id
|
||
|
|
release_data_id INT NOT NULL, -- 发布数据id
|
||
|
|
PRIMARY KEY (feature_id, release_data_id),
|
||
|
|
FOREIGN KEY (feature_id) REFERENCES rtss.feature (id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (release_data_id) REFERENCES rtss.release_data (id) ON DELETE CASCADE
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 注释仿真feature和发布数据关联表
|
||
|
|
COMMENT ON TABLE rtss.feature_release_data IS '仿真feature和发布数据关联表';
|
||
|
|
|
||
|
|
-- 注释仿真feature和发布数据关联表字段
|
||
|
|
COMMENT ON COLUMN rtss.feature_release_data.feature_id IS '仿真feature id';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature_release_data.release_data_id IS '发布数据id';
|
||
|
|
|
||
|
|
-- 创建feature group表
|
||
|
|
CREATE TABLE
|
||
|
|
rtss.feature_group (
|
||
|
|
id SERIAL PRIMARY KEY, -- id 自增主键
|
||
|
|
name VARCHAR(128) NOT NULL UNIQUE, -- feature group名称
|
||
|
|
description TEXT NOT NULL, -- feature group描述
|
||
|
|
is_published BOOLEAN NOT NULL DEFAULT TRUE, -- 是否上架
|
||
|
|
creator_id INT NOT NULL, -- 创建用户id
|
||
|
|
updater_id INT NOT NULL, -- 更新用户id
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT 'now()', -- 创建时间
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT 'now()' -- 更新时间
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 注释仿真feature group表
|
||
|
|
COMMENT ON TABLE rtss.feature_group IS 'feature group表';
|
||
|
|
|
||
|
|
-- 注释仿真feature group表字段
|
||
|
|
COMMENT ON COLUMN rtss.feature_group.id IS 'id 自增主键';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature_group.name IS 'feature group名称';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature_group.description IS 'feature group描述';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature_group.is_published IS '是否上架';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature_group.creator_id IS '创建用户id';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature_group.created_at IS '创建时间';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature_group.updated_at IS '更新时间';
|
||
|
|
|
||
|
|
-- 创建feature group和feature关联表
|
||
|
|
CREATE TABLE
|
||
|
|
rtss.feature_group_feature (
|
||
|
|
feature_group_id INT NOT NULL, -- feature group id
|
||
|
|
feature_id INT NOT NULL, -- feature id
|
||
|
|
PRIMARY KEY (feature_id, feature_group_id),
|
||
|
|
FOREIGN KEY (feature_id) REFERENCES rtss.feature (id) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (feature_group_id) REFERENCES rtss.feature_group (id) ON DELETE CASCADE
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 注释仿真feature group和feature关联表
|
||
|
|
COMMENT ON TABLE rtss.feature_group_feature IS '仿真feature group和feature关联表';
|
||
|
|
|
||
|
|
-- 创建用户feature配置表
|
||
|
|
CREATE TABLE
|
||
|
|
rtss.feature_config (
|
||
|
|
id SERIAL PRIMARY KEY, -- id 自增主键
|
||
|
|
user_id INT NOT NULL, -- 用户id
|
||
|
|
feature_id INT NOT NULL, -- 仿真feature id
|
||
|
|
config BYTEA NOT NULL, -- 配置
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT 'now()', -- 创建时间
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT 'now()', -- 更新时间
|
||
|
|
FOREIGN KEY (feature_id) REFERENCES rtss.feature (id) ON DELETE CASCADE
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 注释用户feature配置表
|
||
|
|
COMMENT ON TABLE rtss.feature_config IS '用户feature配置表';
|
||
|
|
|
||
|
|
-- 注释用户feature配置表字段
|
||
|
|
COMMENT ON COLUMN rtss.feature_config.id IS 'id 自增主键';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature_config.user_id IS '用户id';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature_config.feature_id IS '仿真feature id';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature_config.config IS '配置';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature_config.created_at IS '创建时间';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN rtss.feature_config.updated_at IS '更新时间';
|