📊 数据库初始化脚本

📝 MySQL 脚本

-- 创建数据库
CREATE DATABASE IF NOT EXISTS `wueasy_operation_log` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建用户并授权
CREATE USER IF NOT EXISTS 'wueasy_operation_log'@'%' IDENTIFIED BY 'wueasy_operation_log_123456';
GRANT ALL PRIVILEGES ON `wueasy_operation_log`.* TO 'wueasy_operation_log'@'%';
FLUSH PRIVILEGES;

-- 创建表结构
CREATE TABLE `log_table_config` (
  `id` varchar(40) NOT NULL COMMENT '主键ID',
  `business_code` varchar(128) NOT NULL COMMENT '业务类型代码',
  `table_name` varchar(128) NOT NULL COMMENT '表名',
  `time_query_field` varchar(64) DEFAULT NULL COMMENT '时间查询字段名',
  `unique_field` varchar(64) DEFAULT NULL COMMENT '唯一字段列',
  `description` varchar(255) DEFAULT NULL COMMENT '表描述',
  `status` tinyint(4) DEFAULT '1' COMMENT '状态：1-启用，0-禁用',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_business_code` (`business_code`),
  UNIQUE KEY `uk_table_name` (`table_name`),
  KEY `idx_status` (`status`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB COMMENT='日志表配置主表';

CREATE TABLE `log_field_config` (
  `id` varchar(40) NOT NULL COMMENT '主键ID',
  `table_config_id` varchar(40) NOT NULL COMMENT '表配置ID',
  `field_name` varchar(128) NOT NULL COMMENT '字段名',
  `field_type` varchar(50) NOT NULL COMMENT '字段类型：string, int, float, datetime',
  `data_source` varchar(50) NOT NULL COMMENT '数据来源：param, auto, fixed',
  `default_value` varchar(255) DEFAULT NULL COMMENT '默认值',
  `fixed_value` varchar(255) DEFAULT NULL COMMENT '指定值',
  `is_required` tinyint(4) DEFAULT '0' COMMENT '是否必填：1-是，0-否',
  `description` varchar(500) DEFAULT NULL COMMENT '字段描述',
  `date_format` varchar(50) DEFAULT NULL COMMENT '日期格式化',
  `sort_order` int(11) DEFAULT '0' COMMENT '排序顺序',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_table_field` (`table_config_id`,`field_name`),
  KEY `idx_table_config_id` (`table_config_id`),
  KEY `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB COMMENT='日志字段配置表';

📊 PostgreSQL 脚本

-- 创建数据库
CREATE DATABASE wueasy_operation_log;

-- 创建用户
CREATE USER wueasy_operation_log WITH PASSWORD 'wueasy_operation_log_123456';

-- 授权
GRANT ALL PRIVILEGES ON DATABASE wueasy_operation_log TO wueasy_operation_log;
GRANT CONNECT ON DATABASE wueasy_operation_log TO wueasy_operation_log;
GRANT USAGE ON SCHEMA public TO wueasy_operation_log;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO wueasy_operation_log;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO wueasy_operation_log;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO wueasy_operation_log;


-- 创建表结构
CREATE TABLE log_table_config (
  id VARCHAR(40) NOT NULL,
  business_code VARCHAR(128) NOT NULL,
  table_name VARCHAR(128) NOT NULL,
  time_query_field VARCHAR(64),
  unique_field VARCHAR(64),
  description VARCHAR(255),
  status SMALLINT DEFAULT 1,
  create_time TIMESTAMP,
  update_time TIMESTAMP,
  PRIMARY KEY (id),
  CONSTRAINT uk_business_code UNIQUE (business_code),
  CONSTRAINT uk_table_name UNIQUE (table_name)
);

-- 创建索引
CREATE INDEX idx_status ON log_table_config (status);
CREATE INDEX idx_create_time ON log_table_config (create_time);

-- 添加表注释
COMMENT ON TABLE log_table_config IS '日志表配置主表';
COMMENT ON COLUMN log_table_config.id IS '主键ID';
COMMENT ON COLUMN log_table_config.business_code IS '业务类型代码';
COMMENT ON COLUMN log_table_config.table_name IS '表名';
COMMENT ON COLUMN log_table_config.time_query_field IS '时间查询字段名';
COMMENT ON COLUMN log_table_config.unique_field IS '唯一字段列';
COMMENT ON COLUMN log_table_config.description IS '表描述';
COMMENT ON COLUMN log_table_config.status IS '状态：1-启用，0-禁用';
COMMENT ON COLUMN log_table_config.create_time IS '创建时间';
COMMENT ON COLUMN log_table_config.update_time IS '更新时间';

CREATE TABLE log_field_config (
  id VARCHAR(40) NOT NULL,
  table_config_id VARCHAR(40) NOT NULL,
  field_name VARCHAR(128) NOT NULL,
  field_type VARCHAR(50) NOT NULL,
  data_source VARCHAR(50) NOT NULL,
  default_value VARCHAR(255),
  fixed_value VARCHAR(255),
  is_required SMALLINT DEFAULT 0,
  description VARCHAR(500),
  date_format VARCHAR(50),
  sort_order INTEGER DEFAULT 0,
  create_time TIMESTAMP,
  update_time TIMESTAMP,
  PRIMARY KEY (id),
  CONSTRAINT uk_table_field UNIQUE (table_config_id, field_name),
  CONSTRAINT fk_table_config FOREIGN KEY (table_config_id) REFERENCES log_table_config (id) ON DELETE CASCADE
);

-- 创建索引
CREATE INDEX idx_table_config_id ON log_field_config (table_config_id);
CREATE INDEX idx_sort_order ON log_field_config (sort_order);

-- 添加表注释
COMMENT ON TABLE log_field_config IS '日志字段配置表';
COMMENT ON COLUMN log_field_config.id IS '主键ID';
COMMENT ON COLUMN log_field_config.table_config_id IS '表配置ID';
COMMENT ON COLUMN log_field_config.field_name IS '字段名';
COMMENT ON COLUMN log_field_config.field_type IS '字段类型：string, int, float, datetime';
COMMENT ON COLUMN log_field_config.data_source IS '数据来源：param, auto, fixed';
COMMENT ON COLUMN log_field_config.default_value IS '默认值';
COMMENT ON COLUMN log_field_config.fixed_value IS '指定值';
COMMENT ON COLUMN log_field_config.is_required IS '是否必填：1-是，0-否';
COMMENT ON COLUMN log_field_config.description IS '字段描述';
COMMENT ON COLUMN log_field_config.date_format IS '日期格式化';
COMMENT ON COLUMN log_field_config.sort_order IS '排序顺序';
COMMENT ON COLUMN log_field_config.create_time IS '创建时间';
COMMENT ON COLUMN log_field_config.update_time IS '更新时间';