44 lines
2.8 KiB
PL/PgSQL
44 lines
2.8 KiB
PL/PgSQL
|
BEGIN;
|
||
|
|
ALTER TABLE device_activation_job ADD COLUMN config_local_generation bigint, ADD COLUMN config_ack_boot_id uuid;
|
||
|
|
ALTER TABLE device_binding_claim ADD CONSTRAINT onboarding_claim_user_tenant_fk FOREIGN KEY(user_id,tenant_id) REFERENCES user_account(id,tenant_id);
|
||
|
|
|
||
|
|
CREATE FUNCTION project_onboarding_unbind() RETURNS trigger LANGUAGE plpgsql AS $$
|
||
|
|
DECLARE d device%ROWTYPE;
|
||
|
|
BEGIN
|
||
|
|
IF OLD.unbound_at IS NULL AND NEW.unbound_at IS NOT NULL THEN
|
||
|
|
SELECT * INTO d FROM device WHERE id=NEW.device_id FOR UPDATE;
|
||
|
|
IF d.enrollment_mode='physical_confirm' THEN
|
||
|
|
UPDATE hardware_agent_provider_credential SET status='SUSPENDED',updated_at=now() WHERE device_id=d.id AND status='ACTIVE';
|
||
|
|
UPDATE hardware_agent_device_binding SET status='DISABLED',updated_at=now() WHERE device_id=d.id AND status='ACTIVE';
|
||
|
|
UPDATE device_activation_job SET config_local_generation=NULL,config_ack_boot_id=NULL WHERE device_id=d.id;
|
||
|
|
IF d.activation_state NOT IN ('FACTORY','CLAIMABLE','LOST','REVOKED') THEN
|
||
|
|
UPDATE device SET activation_state='CLAIMABLE',activation_revision=activation_revision+1,activation_updated_at=now(),activation_reason='binding-closed',version=version+1,updated_at=now() WHERE id=d.id;
|
||
|
|
INSERT INTO device_activation_transition(tenant_id,device_id,from_state,to_state,reason,source,actor_type,activation_revision)
|
||
|
|
VALUES(d.tenant_id,d.id,d.activation_state,'CLAIMABLE','归属已由授权业务关闭','binding-generation-trigger','system',d.activation_revision+1);
|
||
|
|
END IF;
|
||
|
|
END IF;
|
||
|
|
END IF;
|
||
|
|
RETURN NEW;
|
||
|
|
END $$;
|
||
|
|
CREATE TRIGGER onboarding_unbind_projection AFTER UPDATE ON device_binding FOR EACH ROW EXECUTE FUNCTION project_onboarding_unbind();
|
||
|
|
|
||
|
|
CREATE FUNCTION guard_onboarding_identity() RETURNS trigger LANGUAGE plpgsql AS $$
|
||
|
|
BEGIN
|
||
|
|
IF OLD.enrollment_mode='physical_confirm' THEN
|
||
|
|
IF NEW.enrollment_mode<>OLD.enrollment_mode OR NEW.factory_device_id IS DISTINCT FROM OLD.factory_device_id OR NEW.factory_mac IS DISTINCT FROM OLD.factory_mac THEN
|
||
|
|
RAISE EXCEPTION 'ONBOARDING_IDENTITY_IMMUTABLE' USING ERRCODE='P0001';
|
||
|
|
END IF;
|
||
|
|
IF NEW.status<>OLD.status OR NEW.activation_state IN ('LOST','REVOKED') AND NEW.activation_state<>OLD.activation_state THEN
|
||
|
|
NEW.onboarding_revision:=NEW.onboarding_revision+1;
|
||
|
|
IF NEW.status<>'registered' OR NEW.activation_state IN ('LOST','REVOKED') THEN
|
||
|
|
UPDATE device_binding_claim SET status='CANCELLED',completed_at=now() WHERE device_id=OLD.id AND status='PENDING';
|
||
|
|
UPDATE device_setup_session SET closed_at=COALESCE(closed_at,now()),close_reason='DEVICE_DISABLED' WHERE device_id=OLD.id;
|
||
|
|
UPDATE onboarding_web_session SET revoked_at=COALESCE(revoked_at,now()) WHERE setup_id IN (SELECT id FROM device_setup_session WHERE device_id=OLD.id);
|
||
|
|
END IF;
|
||
|
|
END IF;
|
||
|
|
END IF;
|
||
|
|
RETURN NEW;
|
||
|
|
END $$;
|
||
|
|
CREATE TRIGGER onboarding_identity_guard BEFORE UPDATE ON device FOR EACH ROW EXECUTE FUNCTION guard_onboarding_identity();
|
||
|
|
COMMIT;
|