190 lines
12 KiB
PL/PgSQL
190 lines
12 KiB
PL/PgSQL
-- ==============================================================================
|
||
-- RailwaySystem QA Bootstrap: 05b_fusion_iot_test_data.sql
|
||
-- 目标数据库: railwaysystem_fusion_qa_20261013 (schema: iotlocks + public)
|
||
-- 业务场景: 培训住宿与 IoT 智能门锁跨模式业务融合合成测试数据
|
||
-- 合规与门禁保证:
|
||
-- 1. 100% 虚拟 Mock 合成数据，不包含任何真实学员、人脸图像或生产门锁 SN
|
||
-- 2. 不连接真实 MQTT Broker，下发与回执由自动化测试与 Mock 双向驱动
|
||
-- 3. 【任务6核心要求】初始测试数据置为未授权/待同步状态 (sync_status=0, status=0)，
|
||
-- 绝不预置下发成功(sync_status=2)冒充完整链路，确保通过真实业务 API 和 Mock 回执推动
|
||
-- 4. 【任务4与5核心要求】外键类型严格对齐 BIGINT，列名与 Mapper 完全一致，
|
||
-- 利用部分唯一索引验证退宿 (accommodation_status=2) 与重新入住 (accommodation_status=1) 并存
|
||
-- ==============================================================================
|
||
|
||
BEGIN;
|
||
|
||
-- ==============================================================================
|
||
-- 1. IoT 物联基础设施底座 (schema: iotlocks)
|
||
-- ==============================================================================
|
||
|
||
-- 1.1 分组 (对应公寓楼宇/培训园区)
|
||
INSERT INTO iotlocks.lock_group
|
||
(group_id, group_code, group_name, dept_id, manager, phone, address, status, order_num, del_flag, create_by, create_time, remark)
|
||
VALUES
|
||
(1001, 'GRP_FUSION_TEST', '致远楼融合测试公寓', 112, '管理员', '18800000001', '沈阳段实训园区致远楼', '0', 1, '0', 'admin', CURRENT_TIMESTAMP, '融合测试专用设备分组')
|
||
ON CONFLICT (group_id) DO UPDATE SET
|
||
group_code = EXCLUDED.group_code,
|
||
group_name = EXCLUDED.group_name,
|
||
del_flag = '0';
|
||
|
||
-- 1.2 网关设备 (对齐 04 DDL 列名: gateway_ip)
|
||
INSERT INTO iotlocks.lock_gateway
|
||
(gateway_id, gateway_sn, gateway_name, group_id, gateway_ip, online_status, status, del_flag, create_by, create_time, remark)
|
||
VALUES
|
||
(1001, 'GW-FUSION-TEST-01', '致远楼1层测试LoRa网关', 1001, '192.168.10.201', '0', '0', '0', 'admin', CURRENT_TIMESTAMP, '融合测试网关')
|
||
ON CONFLICT (gateway_id) DO UPDATE SET
|
||
gateway_sn = EXCLUDED.gateway_sn,
|
||
gateway_name = EXCLUDED.gateway_name,
|
||
group_id = EXCLUDED.group_id,
|
||
gateway_ip = EXCLUDED.gateway_ip,
|
||
del_flag = '0';
|
||
|
||
-- 1.3 智能门锁设备 (对应宿舍房间 101 与 102)
|
||
INSERT INTO iotlocks.lock_device
|
||
(device_id, device_sn, device_name, gateway_id, group_id, room_no, dev_type, dev_ver, face_algo_version, online_status, status, del_flag, create_by, create_time, remark)
|
||
VALUES
|
||
(1001, 'LOCK-ZY-101', '致远楼101智能门锁', 1001, 1001, '101', '01', 'V2.1.0', '2.14.5_2.7.1_M3', '0', '0', '0', 'admin', CURRENT_TIMESTAMP, '宿舍101匹配智能门锁'),
|
||
(1002, 'LOCK-ZY-102', '致远楼102智能门锁', 1001, 1001, '102', '01', 'V2.1.0', '2.14.5_2.7.1_M3', '0', '0', '0', 'admin', CURRENT_TIMESTAMP, '宿舍102匹配智能门锁')
|
||
ON CONFLICT (device_id) DO UPDATE SET
|
||
device_sn = EXCLUDED.device_sn,
|
||
device_name = EXCLUDED.device_name,
|
||
gateway_id = EXCLUDED.gateway_id,
|
||
group_id = EXCLUDED.group_id,
|
||
room_no = EXCLUDED.room_no,
|
||
del_flag = '0';
|
||
|
||
|
||
-- ==============================================================================
|
||
-- 2. 培训业务主档与宿舍房间 (schema: public)
|
||
-- ==============================================================================
|
||
|
||
-- 2.1 宿舍主档 (lock_device_sn 与 iotlocks.lock_device.device_sn 100% 对应)
|
||
INSERT INTO public.train_dormitory_info
|
||
(dorm_id, dorm_building, dorm_floor, dorm_room, capacity, gender_type, dorm_status, lock_device_sn, create_by, create_time, del_flag, remark)
|
||
VALUES
|
||
('DORM-ZY-101', '致远楼', '1层', '101', 4, 1, 1, 'LOCK-ZY-101', 'admin', CURRENT_TIMESTAMP, '0', '男生宿舍101室(绑定门锁LOCK-ZY-101)'),
|
||
('DORM-ZY-102', '致远楼', '1层', '102', 4, 1, 1, 'LOCK-ZY-102', 'admin', CURRENT_TIMESTAMP, '0', '男生宿舍102室(绑定门锁LOCK-ZY-102)')
|
||
ON CONFLICT (dorm_id) DO UPDATE SET
|
||
lock_device_sn = EXCLUDED.lock_device_sn,
|
||
dorm_status = EXCLUDED.dorm_status,
|
||
del_flag = '0';
|
||
|
||
-- 2.2 开班计划
|
||
INSERT INTO public.train_class_plan
|
||
(plan_id, plan_name, start_time, end_time, report_deadline, train_address, recruit_num, apply_condition, plan_status, create_by, create_time, del_flag, remark)
|
||
VALUES
|
||
('PLAN-FUSION-01', '2026年公寓智能物联与门禁业务融合实训班', '2026-10-01 08:30:00', '2026-10-30 17:30:00', '2026-09-30 18:00:00', '致远楼实训园区', 20, '参训学员', 1, 'admin', CURRENT_TIMESTAMP, '0', '融合测试专用开班')
|
||
ON CONFLICT (plan_id) DO UPDATE SET
|
||
plan_name = EXCLUDED.plan_name,
|
||
plan_status = EXCLUDED.plan_status,
|
||
del_flag = '0';
|
||
|
||
-- 2.3 融合测试专属学员主档 (稳定唯一的 person_no)
|
||
INSERT INTO public.train_student
|
||
(s_id, s_name, s_gender, s_birth, org_name, s_phone, s_email, id_card_hash, id_card_last4, person_no, source_type, status, del_flag, create_by, create_time, remark)
|
||
VALUES
|
||
('S_FUSION_001', '刘强(融合测试)', '0', '1992-05-18', '沈阳房建公寓段沈阳车间', '18800000881', 'liuqiang@sict.railway.cn', 'e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855', '8811', 'AA000001', 1, 1, '0', 'admin', CURRENT_TIMESTAMP, '学员1-用于退宿与重新入住场景'),
|
||
('S_FUSION_002', '陈燕(融合测试)', '1', '1994-08-22', '沈阳房建公寓段大连车间', '18800000882', 'chenyan@sict.railway.cn', 'd41d8cd98f00b204e9800998ecf8427e', '8822', 'AA000002', 1, 1, '0', 'admin', CURRENT_TIMESTAMP, '学员2-用于调宿与端到端授权下发场景')
|
||
ON CONFLICT (s_id) DO UPDATE SET
|
||
s_name = EXCLUDED.s_name,
|
||
person_no = EXCLUDED.person_no,
|
||
s_phone = EXCLUDED.s_phone,
|
||
status = EXCLUDED.status,
|
||
del_flag = '0';
|
||
|
||
-- 2.4 参训记录 (使用正确列名 student_id，enrollment_id 为 BIGINT)
|
||
INSERT INTO public.train_enrollment
|
||
(enrollment_id, plan_id, student_id, source_type, enrollment_status, accommodation_required, join_time, create_by, create_time)
|
||
VALUES
|
||
(2001, 'PLAN-FUSION-01', 'S_FUSION_001', 1, 1, 1, '2026-09-30 14:00:00', 'admin', CURRENT_TIMESTAMP),
|
||
(2002, 'PLAN-FUSION-01', 'S_FUSION_002', 1, 1, 1, '2026-09-30 14:30:00', 'admin', CURRENT_TIMESTAMP)
|
||
ON CONFLICT (enrollment_id) DO UPDATE SET
|
||
student_id = EXCLUDED.student_id,
|
||
enrollment_status = EXCLUDED.enrollment_status;
|
||
|
||
|
||
-- ==============================================================================
|
||
-- 3. 住宿业务记录 (使用正确列名: accommodation_id, enrollment_id, dorm_id, bed_no, assigned_time, accommodation_status)
|
||
-- ==============================================================================
|
||
|
||
-- 学员1 (enrollment_id = 2001, AA000001):
|
||
-- - 记录 2001: 历史退宿记录 (accommodation_status = 2)
|
||
-- - 记录 2003: 重新入住记录 (accommodation_status = 1)
|
||
-- -> 证明部分唯一索引 uq_accommodation_active_enroll 正常放行历史退宿与再次入住
|
||
-- 学员2 (enrollment_id = 2002, AA000002):
|
||
-- - 记录 2002: 当前在住记录 (accommodation_status = 1)
|
||
INSERT INTO public.train_accommodation
|
||
(accommodation_id, enrollment_id, dorm_id, bed_no, assigned_time, accommodation_status, create_by, create_time, remark)
|
||
VALUES
|
||
(2001, 2001, 'DORM-ZY-101', '1号床', '2026-09-01 09:00:00', 2, 'admin', CURRENT_TIMESTAMP, '历史住宿记录-已退宿'),
|
||
(2003, 2001, 'DORM-ZY-101', '1号床', '2026-10-01 08:00:00', 1, 'admin', CURRENT_TIMESTAMP, '重新入住记录-当前在住'),
|
||
(2002, 2002, 'DORM-ZY-102', '1号床', '2026-10-01 08:30:00', 1, 'admin', CURRENT_TIMESTAMP, '当前分配入住记录')
|
||
ON CONFLICT (accommodation_id) DO UPDATE SET
|
||
dorm_id = EXCLUDED.dorm_id,
|
||
bed_no = EXCLUDED.bed_no,
|
||
accommodation_status = EXCLUDED.accommodation_status,
|
||
assigned_time = EXCLUDED.assigned_time;
|
||
|
||
|
||
-- ==============================================================================
|
||
-- 4. IoT 人脸凭证与门锁授权数据 (schema: iotlocks)
|
||
-- 【核心门禁要求】初始态为未授权/待提取/待下发，绝不预置成功状态冒充完整链路
|
||
-- ==============================================================================
|
||
|
||
-- 4.1 Mock 人脸凭证 (对齐 04 DDL 列名: photo_url, feature_data, feature_algo_version)
|
||
INSERT INTO iotlocks.lock_face
|
||
(face_id, person_no, person_name, person_type, photo_url, feature_data, extract_status, feature_algo_version, create_by, create_time, remark)
|
||
VALUES
|
||
(1001, 'AA000001', '刘强(融合测试)', '0', 'mock:base64:synthetic_face_image_001', NULL, '0', '2.14.5_2.7.1_M3', 'admin', CURRENT_TIMESTAMP, '初始未提取人脸-待提取特征'),
|
||
(1002, 'AA000002', '陈燕(融合测试)', '0', 'mock:base64:synthetic_face_image_002', NULL, '0', '2.14.5_2.7.1_M3', 'admin', CURRENT_TIMESTAMP, '初始未提取人脸-待提取特征')
|
||
ON CONFLICT (face_id) DO UPDATE SET
|
||
person_no = EXCLUDED.person_no,
|
||
person_name = EXCLUDED.person_name,
|
||
extract_status = EXCLUDED.extract_status;
|
||
|
||
-- 4.2 门锁授权记录 (对齐 04 DDL 与 LockAuth 实体: auth_no, device_sn, gateway_id, group_id, auth_type, sync_status, status)
|
||
-- 由业务端到端 API 发起下发并经 Mock MQTT 回执推进状态至成功
|
||
INSERT INTO iotlocks.lock_auth
|
||
(auth_id, auth_no, person_no, person_name, device_id, device_sn, gateway_id, group_id, auth_type, sync_status, status, begin_time, end_time, retry_count, del_flag, create_by, create_time, remark)
|
||
VALUES
|
||
(1001, 'AUTH202610130001', 'AA000001', '刘强(融合测试)', 1001, 'LOCK-ZY-101', 1001, 1001, '01', '0', '0', '2026-10-01 08:00:00', '2026-10-30 23:59:59', 0, '0', 'admin', CURRENT_TIMESTAMP, '学员1门锁101-初始待下发授权'),
|
||
(1002, 'AUTH202610130002', 'AA000002', '陈燕(融合测试)', 1002, 'LOCK-ZY-102', 1001, 1001, '01', '0', '0', '2026-10-01 08:00:00', '2026-10-30 23:59:59', 0, '0', 'admin', CURRENT_TIMESTAMP, '学员2门锁102-初始待下发授权')
|
||
ON CONFLICT (auth_id) DO UPDATE SET
|
||
auth_no = EXCLUDED.auth_no,
|
||
person_no = EXCLUDED.person_no,
|
||
device_id = EXCLUDED.device_id,
|
||
device_sn = EXCLUDED.device_sn,
|
||
status = EXCLUDED.status,
|
||
sync_status = EXCLUDED.sync_status;
|
||
|
||
|
||
-- ==============================================================================
|
||
-- 5. 序列号安全复位 (防止后续业务自增冲突)
|
||
-- ==============================================================================
|
||
DO $$
|
||
BEGIN
|
||
IF pg_get_serial_sequence('iotlocks.lock_group', 'group_id') IS NOT NULL THEN
|
||
PERFORM setval(pg_get_serial_sequence('iotlocks.lock_group', 'group_id'), greatest((SELECT coalesce(max(group_id), 0) FROM iotlocks.lock_group), 1001));
|
||
END IF;
|
||
IF pg_get_serial_sequence('iotlocks.lock_gateway', 'gateway_id') IS NOT NULL THEN
|
||
PERFORM setval(pg_get_serial_sequence('iotlocks.lock_gateway', 'gateway_id'), greatest((SELECT coalesce(max(gateway_id), 0) FROM iotlocks.lock_gateway), 1001));
|
||
END IF;
|
||
IF pg_get_serial_sequence('iotlocks.lock_device', 'device_id') IS NOT NULL THEN
|
||
PERFORM setval(pg_get_serial_sequence('iotlocks.lock_device', 'device_id'), greatest((SELECT coalesce(max(device_id), 0) FROM iotlocks.lock_device), 1002));
|
||
END IF;
|
||
IF pg_get_serial_sequence('iotlocks.lock_face', 'face_id') IS NOT NULL THEN
|
||
PERFORM setval(pg_get_serial_sequence('iotlocks.lock_face', 'face_id'), greatest((SELECT coalesce(max(face_id), 0) FROM iotlocks.lock_face), 1002));
|
||
END IF;
|
||
IF pg_get_serial_sequence('iotlocks.lock_auth', 'auth_id') IS NOT NULL THEN
|
||
PERFORM setval(pg_get_serial_sequence('iotlocks.lock_auth', 'auth_id'), greatest((SELECT coalesce(max(auth_id), 0) FROM iotlocks.lock_auth), 1002));
|
||
END IF;
|
||
IF pg_get_serial_sequence('public.train_enrollment', 'enrollment_id') IS NOT NULL THEN
|
||
PERFORM setval(pg_get_serial_sequence('public.train_enrollment', 'enrollment_id'), greatest((SELECT coalesce(max(enrollment_id), 0) FROM public.train_enrollment), 2002));
|
||
END IF;
|
||
IF pg_get_serial_sequence('public.train_accommodation', 'accommodation_id') IS NOT NULL THEN
|
||
PERFORM setval(pg_get_serial_sequence('public.train_accommodation', 'accommodation_id'), greatest((SELECT coalesce(max(accommodation_id), 0) FROM public.train_accommodation), 2003));
|
||
END IF;
|
||
END $$;
|
||
|
||
COMMIT;
|