75 lines
3.5 KiB
SQL
75 lines
3.5 KiB
SQL
-- Existing database must already contain sys_user and component from cadworld.sql.
|
|
-- No DROP, data rewrite or Flyway history changes. Safe to rerun after a partial upgrade.
|
|
SET NAMES utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS interface_definition_v2 (
|
|
id VARCHAR(64) NOT NULL PRIMARY KEY,
|
|
name VARCHAR(128) NOT NULL,
|
|
description VARCHAR(500) NULL,
|
|
geometry_revision INT NOT NULL,
|
|
geometry_json JSON NOT NULL,
|
|
tolerances_json JSON NOT NULL,
|
|
connectable_targets_json JSON NULL,
|
|
display_forward_json JSON NULL,
|
|
source_asset_key VARCHAR(512) NULL,
|
|
source_geometry_hash VARCHAR(128) NULL,
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='接口定义';
|
|
|
|
CREATE TABLE IF NOT EXISTS interface_definition_revision (
|
|
id VARCHAR(96) NOT NULL PRIMARY KEY,
|
|
definition_id VARCHAR(64) NOT NULL,
|
|
geometry_revision INT NOT NULL,
|
|
definition_json JSON NOT NULL,
|
|
UNIQUE KEY uk_interface_geometry_revision (definition_id, geometry_revision)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='接口历史几何修订';
|
|
|
|
CREATE TABLE IF NOT EXISTS component_asset (
|
|
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
component_id BIGINT NOT NULL,
|
|
object_key VARCHAR(512) NOT NULL,
|
|
relative_path VARCHAR(512) NOT NULL,
|
|
asset_kind VARCHAR(32) NOT NULL,
|
|
content_type VARCHAR(128) NULL,
|
|
size BIGINT NOT NULL DEFAULT 0,
|
|
sha256 VARCHAR(128) NULL,
|
|
is_primary TINYINT NOT NULL DEFAULT 0,
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|
CONSTRAINT fk_component_asset_component FOREIGN KEY (component_id) REFERENCES component(id) ON DELETE CASCADE,
|
|
UNIQUE KEY uk_component_asset_object_key (object_key),
|
|
KEY idx_component_asset_component (component_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='组件资源索引';
|
|
|
|
CREATE TABLE IF NOT EXISTS sys_operation_log (
|
|
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
module VARCHAR(32) NOT NULL,
|
|
action VARCHAR(32) NOT NULL,
|
|
target_id VARCHAR(512) NULL,
|
|
operator_id BIGINT NOT NULL,
|
|
operator_name VARCHAR(64) NOT NULL,
|
|
request_path VARCHAR(512) NOT NULL,
|
|
request_method VARCHAR(16) NOT NULL,
|
|
client_ip VARCHAR(64) NULL,
|
|
request_id VARCHAR(64) NULL,
|
|
operated_at DATETIME(3) NOT NULL,
|
|
duration_ms BIGINT NOT NULL,
|
|
success TINYINT NOT NULL,
|
|
http_status INT NOT NULL,
|
|
error_summary VARCHAR(1000) NULL,
|
|
KEY idx_operation_module_time (module, operated_at),
|
|
KEY idx_operation_operator_time (operator_id, operated_at),
|
|
KEY idx_operation_result_time (success, operated_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='组件库和接口库管理操作日志';
|
|
|
|
-- Keep the phone unique index, including logically deleted users.
|
|
SET @cadworld_deleted_ddl = IF(
|
|
EXISTS (SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sys_user' AND COLUMN_NAME = 'deleted'),
|
|
'SELECT 1',
|
|
'ALTER TABLE sys_user ADD COLUMN deleted TINYINT NOT NULL DEFAULT 0 COMMENT ''逻辑删除:0正常,1删除''');
|
|
PREPARE cadworld_deleted_statement FROM @cadworld_deleted_ddl;
|
|
EXECUTE cadworld_deleted_statement;
|
|
DEALLOCATE PREPARE cadworld_deleted_statement;
|