You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
94 lines
2.9 KiB
SQL
94 lines
2.9 KiB
SQL
-- Backfill mappings for historical ZhiYun data.
|
|
-- Safe to run repeatedly. This script only backfills resources where the old
|
|
-- sync logic stored the ZhiYun id directly as the local id.
|
|
|
|
CREATE TABLE IF NOT EXISTS external_resource_mapping (
|
|
id VARCHAR(64) NOT NULL PRIMARY KEY,
|
|
source_system VARCHAR(32) NOT NULL COMMENT '外部来源系统',
|
|
resource_type VARCHAR(32) NOT NULL COMMENT '资源类型',
|
|
local_id VARCHAR(64) NOT NULL COMMENT '本系统ID',
|
|
external_id VARCHAR(128) NOT NULL COMMENT '外部系统ID',
|
|
external_code VARCHAR(128) NULL COMMENT '外部编码',
|
|
external_name VARCHAR(255) NULL COMMENT '外部名称快照',
|
|
create_time DATETIME NULL COMMENT '创建时间',
|
|
update_time DATETIME NULL COMMENT '更新时间',
|
|
UNIQUE KEY uk_external_resource (source_system, resource_type, external_id),
|
|
KEY idx_external_resource_local (resource_type, local_id)
|
|
) COMMENT='外部系统资源映射';
|
|
|
|
-- Historical ZhiYun sync stored school_id directly from ZhiYun schoolId.
|
|
INSERT INTO external_resource_mapping (
|
|
id, source_system, resource_type, local_id, external_id, external_code, external_name, create_time, update_time
|
|
)
|
|
SELECT
|
|
REPLACE(UUID(), '-', ''),
|
|
'ZHIYUN',
|
|
'SCHOOL',
|
|
s.school_id,
|
|
s.school_id,
|
|
s.school_id,
|
|
s.school_name,
|
|
NOW(),
|
|
NOW()
|
|
FROM school s
|
|
WHERE EXISTS (
|
|
SELECT 1
|
|
FROM userinfo u
|
|
WHERE u.school_id = s.school_id
|
|
AND u.code_from = '智云同步'
|
|
)
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM external_resource_mapping m
|
|
WHERE m.source_system = 'ZHIYUN'
|
|
AND m.resource_type = 'SCHOOL'
|
|
AND m.external_id = s.school_id
|
|
);
|
|
|
|
-- Historical ZhiYun student sync stored school_class_id directly from ZhiYun classId.
|
|
INSERT INTO external_resource_mapping (
|
|
id, source_system, resource_type, local_id, external_id, external_code, external_name, create_time, update_time
|
|
)
|
|
SELECT
|
|
REPLACE(UUID(), '-', ''),
|
|
'ZHIYUN',
|
|
'CLASS',
|
|
c.school_class_id,
|
|
c.school_class_id,
|
|
c.school_class_id,
|
|
c.class_name,
|
|
NOW(),
|
|
NOW()
|
|
FROM school_class c
|
|
WHERE EXISTS (
|
|
SELECT 1
|
|
FROM userinfo u
|
|
WHERE u.school_class_id = c.school_class_id
|
|
AND u.code_from = '智云同步'
|
|
)
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM external_resource_mapping m
|
|
WHERE m.source_system = 'ZHIYUN'
|
|
AND m.resource_type = 'CLASS'
|
|
AND m.external_id = c.school_class_id
|
|
);
|
|
|
|
-- Mark classes that came from historical ZhiYun student sync as administrative
|
|
-- ZhiYun classes. Local teaching classes are not touched by this condition.
|
|
UPDATE school_class c
|
|
SET c.class_type = 'ADMIN',
|
|
c.data_source = 'ZHIYUN'
|
|
WHERE EXISTS (
|
|
SELECT 1
|
|
FROM userinfo u
|
|
WHERE u.school_class_id = c.school_class_id
|
|
AND u.code_from = '智云同步'
|
|
);
|
|
|
|
-- Important:
|
|
-- Historical rows do not retain ZhiYun userId / collegeId / majorId anywhere
|
|
-- reliable. Do not fabricate USER / FACULTY / MAJOR mappings from username or
|
|
-- names. The updated sync code will add those mappings on the next full ZhiYun
|
|
-- sync because it receives the real external ids in ZYUserInfo.
|