95 lines
4.2 KiB
PL/PgSQL
95 lines
4.2 KiB
PL/PgSQL
-- 创建settings配置表
|
||
CREATE TABLE IF NOT EXISTS settings (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
key VARCHAR(255) NOT NULL UNIQUE,
|
||
value TEXT,
|
||
value_type VARCHAR(50) NOT NULL DEFAULT 'string', -- string, number, boolean, json
|
||
description TEXT,
|
||
category VARCHAR(100) DEFAULT 'general',
|
||
is_encrypted BOOLEAN DEFAULT FALSE,
|
||
is_system BOOLEAN DEFAULT FALSE, -- 系统配置，不允许删除
|
||
is_editable BOOLEAN DEFAULT TRUE, -- 是否允许编辑
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
created_by UUID,
|
||
updated_by UUID
|
||
);
|
||
|
||
-- 添加字段注释
|
||
COMMENT ON COLUMN settings.id IS '配置项唯一标识符';
|
||
COMMENT ON COLUMN settings.key IS '配置键名，全局唯一';
|
||
COMMENT ON COLUMN settings.value IS '配置值';
|
||
COMMENT ON COLUMN settings.value_type IS '值类型：string, number, boolean, json';
|
||
COMMENT ON COLUMN settings.description IS '配置项描述';
|
||
COMMENT ON COLUMN settings.category IS '配置分类，便于分组管理';
|
||
COMMENT ON COLUMN settings.is_encrypted IS '是否为加密值';
|
||
COMMENT ON COLUMN settings.is_system IS '是否为系统配置';
|
||
COMMENT ON COLUMN settings.is_editable IS '是否允许编辑';
|
||
COMMENT ON COLUMN settings.created_by IS '创建者ID';
|
||
COMMENT ON COLUMN settings.updated_by IS '更新者ID';
|
||
|
||
-- 创建索引
|
||
CREATE INDEX IF NOT EXISTS idx_settings_key ON settings(key);
|
||
CREATE INDEX IF NOT EXISTS idx_settings_category ON settings(category);
|
||
CREATE INDEX IF NOT EXISTS idx_settings_is_system ON settings(is_system);
|
||
CREATE INDEX IF NOT EXISTS idx_settings_created_at ON settings(created_at);
|
||
|
||
-- 创建复合索引
|
||
CREATE INDEX IF NOT EXISTS idx_settings_category_key ON settings(category, key);
|
||
|
||
-- 为表创建更新时间触发器
|
||
CREATE TRIGGER update_settings_updated_at
|
||
BEFORE UPDATE ON settings
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
-- 插入一些默认配置
|
||
INSERT INTO settings (key, value, value_type, description, category, is_system) VALUES
|
||
('app.name', 'MMAP System', 'string', '应用名称', 'app', true),
|
||
('app.version', '1.0.0', 'string', '应用版本', 'app', true),
|
||
('app.debug', 'false', 'boolean', '调试模式', 'app', true),
|
||
('app.timezone', 'UTC', 'string', '系统时区', 'app', true),
|
||
('database.max_connections', '10', 'number', '数据库最大连接数', 'database', true),
|
||
('database.connection_timeout', '30', 'number', '数据库连接超时时间(秒)', 'database', true),
|
||
('kafka.max_retries', '3', 'number', 'Kafka最大重试次数', 'kafka', true),
|
||
('kafka.retry_delay', '1000', 'number', 'Kafka重试延迟(毫秒)', 'kafka', true),
|
||
('security.session_timeout', '3600', 'number', '会话超时时间(秒)', 'security', true),
|
||
('security.max_login_attempts', '5', 'number', '最大登录尝试次数', 'security', true),
|
||
('logging.level', 'info', 'string', '日志级别', 'logging', true),
|
||
('logging.max_files', '10', 'number', '最大日志文件数', 'logging', true),
|
||
('cache.ttl', '300', 'number', '缓存生存时间(秒)', 'cache', true),
|
||
('cache.max_size', '1000', 'number', '缓存最大条目数', 'cache', true)
|
||
ON CONFLICT (key) DO NOTHING;
|
||
|
||
-- 创建配置历史表（可选，用于审计）
|
||
CREATE TABLE IF NOT EXISTS settings_history (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
setting_id UUID NOT NULL REFERENCES settings(id) ON DELETE CASCADE,
|
||
old_value TEXT,
|
||
new_value TEXT,
|
||
changed_by UUID,
|
||
change_reason TEXT,
|
||
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 为历史表创建索引
|
||
CREATE INDEX IF NOT EXISTS idx_settings_history_setting_id ON settings_history(setting_id);
|
||
CREATE INDEX IF NOT EXISTS idx_settings_history_created_at ON settings_history(created_at);
|
||
|
||
-- 创建配置变更触发器
|
||
CREATE OR REPLACE FUNCTION log_setting_change()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
IF OLD.value IS DISTINCT FROM NEW.value THEN
|
||
INSERT INTO settings_history (setting_id, old_value, new_value, changed_by)
|
||
VALUES (NEW.id, OLD.value, NEW.value, NEW.updated_by);
|
||
END IF;
|
||
RETURN NEW;
|
||
END;
|
||
$$ language 'plpgsql';
|
||
|
||
-- 为settings表创建变更日志触发器
|
||
CREATE TRIGGER log_settings_changes
|
||
AFTER UPDATE ON settings
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION log_setting_change(); |