-- 工艺知识库独立数据结构；不修改既有路线、参数和工序数据。
|
-- 使用业务 ID 关联现有主数据，不跨模块设置物理外键，便于项目间迁移。
|
CREATE TABLE IF NOT EXISTS mes_pd_knowledge (
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
code VARCHAR(64) NOT NULL COMMENT '知识编码',
|
name VARCHAR(200) NOT NULL COMMENT '知识名称',
|
version INT NOT NULL DEFAULT 1 COMMENT '知识版本',
|
product_item_id BIGINT DEFAULT NULL COMMENT '适用成品物料',
|
bom_id BIGINT DEFAULT NULL COMMENT '适用 BOM 子物料编号',
|
voltage_min DECIMAL(16,4) DEFAULT NULL COMMENT '最低电压，单位 kV',
|
voltage_max DECIMAL(16,4) DEFAULT NULL COMMENT '最高电压，单位 kV',
|
market VARCHAR(16) NOT NULL DEFAULT 'ALL' COMMENT 'ALL、DOMESTIC 或 EXPORT',
|
route_id BIGINT DEFAULT NULL COMMENT '参考工艺路线',
|
template_id BIGINT DEFAULT NULL COMMENT '参考参数模板',
|
description TEXT COMMENT '适用说明',
|
status TINYINT NOT NULL DEFAULT 0 COMMENT '0 草稿、1 已发布、2 停用',
|
creator VARCHAR(64) DEFAULT '',
|
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
updater VARCHAR(64) DEFAULT '',
|
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
deleted BIT(1) NOT NULL DEFAULT b'0',
|
PRIMARY KEY (id),
|
UNIQUE KEY uq_knowledge_code_version (code, version),
|
KEY idx_knowledge_match (status, market, product_item_id, deleted)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='工艺知识版本';
|
|
-- 同步为先前已创建的表补建唯一索引；如已有重复数据则应先人工处理，不能静默跳过。
|
SET @knowledge_unique_sql := IF(
|
(SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE()
|
AND table_name = 'mes_pd_knowledge' AND index_name = 'uq_knowledge_code_version') = 0,
|
'ALTER TABLE mes_pd_knowledge ADD UNIQUE KEY uq_knowledge_code_version (code, version)',
|
'SELECT 1');
|
PREPARE knowledge_unique_stmt FROM @knowledge_unique_sql;
|
EXECUTE knowledge_unique_stmt;
|
DEALLOCATE PREPARE knowledge_unique_stmt;
|
|
CREATE TABLE IF NOT EXISTS mes_pd_knowledge_standard (
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
knowledge_id BIGINT NOT NULL,
|
standard_code VARCHAR(128) NOT NULL COMMENT '适用标准编码',
|
standard_name VARCHAR(200) DEFAULT NULL COMMENT '适用标准名称',
|
creator VARCHAR(64) DEFAULT '', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
updater VARCHAR(64) DEFAULT '', update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
deleted BIT(1) NOT NULL DEFAULT b'0',
|
PRIMARY KEY (id), KEY idx_knowledge_standard_parent (knowledge_id, deleted),
|
KEY idx_knowledge_standard_code (standard_code, deleted)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='工艺知识适用标准';
|
|
CREATE TABLE IF NOT EXISTS mes_pd_knowledge_resource (
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
knowledge_id BIGINT NOT NULL,
|
process_id BIGINT DEFAULT NULL COMMENT '现有工序',
|
resource_type VARCHAR(32) NOT NULL COMMENT '资源类型',
|
resource_id BIGINT DEFAULT NULL COMMENT '现有资源编号',
|
capability_description VARCHAR(500) DEFAULT NULL COMMENT '能力及约束描述',
|
creator VARCHAR(64) DEFAULT '', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
updater VARCHAR(64) DEFAULT '', update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
deleted BIT(1) NOT NULL DEFAULT b'0',
|
PRIMARY KEY (id), KEY idx_knowledge_resource_parent (knowledge_id, deleted)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='工艺知识资源能力';
|
|
CREATE TABLE IF NOT EXISTS mes_pd_knowledge_reference (
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
knowledge_id BIGINT NOT NULL,
|
knowledge_version INT NOT NULL COMMENT '引用时版本',
|
target_type VARCHAR(32) NOT NULL COMMENT '引用目标类型',
|
target_id BIGINT NOT NULL COMMENT '引用目标编号',
|
creator VARCHAR(64) DEFAULT '', create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
updater VARCHAR(64) DEFAULT '', update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
deleted BIT(1) NOT NULL DEFAULT b'0',
|
PRIMARY KEY (id), UNIQUE KEY uq_knowledge_reference (knowledge_id, target_type, target_id),
|
KEY idx_knowledge_reference_source (knowledge_id, knowledge_version, deleted),
|
KEY idx_knowledge_reference_target (target_type, target_id, deleted)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='工艺知识引用记录';
|
|
SET @knowledge_reference_sql := IF(
|
(SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE()
|
AND table_name = 'mes_pd_knowledge_reference' AND index_name = 'uq_knowledge_reference') = 0,
|
'ALTER TABLE mes_pd_knowledge_reference ADD UNIQUE KEY uq_knowledge_reference (knowledge_id, target_type, target_id)',
|
'SELECT 1');
|
PREPARE knowledge_reference_stmt FROM @knowledge_reference_sql;
|
EXECUTE knowledge_reference_stmt;
|
DEALLOCATE PREPARE knowledge_reference_stmt;
|
