个人开发3

发现之前的数据库有些事情不够细致，在之前的数据库的基础上进行了优化
-- 演练执行记录表（核心表）
CREATE TABLE drill_execution (
execution_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '执行记录ID',
task_id INT UNSIGNED NOT NULL COMMENT '关联任务ID',
step_name VARCHAR(100) NOT NULL COMMENT '执行步骤名称',
start_time DATETIME NOT NULL COMMENT '步骤开始时间',
end_time DATETIME COMMENT '步骤结束时间',
duration INT UNSIGNED GENERATED ALWAYS AS (TIMESTAMPDIFF(SECOND, start_time, end_time)) STORED COMMENT '持续时长（秒）',
operator_id INT UNSIGNED NOT NULL COMMENT '操作人ID',
safety_confirmed TINYINT(1) DEFAULT 0 COMMENT '安全确认状态（0未确认/1已确认）',
safety_confirm_time DATETIME COMMENT '安全确认时间',
log_file VARCHAR(255) COMMENT '过程日志文件路径',
status ENUM('未开始','进行中','已暂停','已结束') DEFAULT '未开始' COMMENT '执行状态',
FOREIGN KEY (task_id) REFERENCES drill_task(task_id) ON DELETE CASCADE,
INDEX idx_operator (operator_id),
INDEX idx_execution_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='演练过程执行记录表';

-- 新增安全确认记录表（扩展功能）
CREATE TABLE safety_confirmation (
confirm_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
execution_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
confirm_time DATETIME DEFAULT CURRENT_TIMESTAMP,
audio_duration INT DEFAULT 60 COMMENT '音频播放时长（秒）',
FOREIGN KEY (execution_id) REFERENCES drill_execution(execution_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
JPA映射也进行略微修改
@Entity
@Table(name = "drill_execution")
@Data
public class DrillExecution {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long executionId;

@ManyToOne
@JoinColumn(name = "task_id", nullable = false)
private DrillTask task;

private String stepName;
private LocalDateTime startTime;
private LocalDateTime endTime;

@Column(insertable = false, updatable = false)
private Integer duration;

private Long operatorId;
private Boolean safetyConfirmed;
private LocalDateTime safetyConfirmTime;

private String logFile;

@Enumerated(EnumType.STRING)
private ExecutionStatus status;

}

public enum ExecutionStatus {
NOT_STARTED, IN_PROGRESS, PAUSED, COMPLETED
}
今日心得：对数据库的感触更深了一点，数据库的结构真不是看起来那么简单，实际上进行学习有着很多的问题。