xiaoshu-app/legacy-sync-bridge/database/install.sql

IF OBJECT_ID(N'dbo.XiaoshuSyncCommandLog', N'U') IS NULL
BEGIN
  CREATE TABLE dbo.XiaoshuSyncCommandLog (
    Id bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
    IdempotencyKey nvarchar(128) NOT NULL,
    Operation nvarchar(100) NOT NULL,
    Actor nvarchar(200) NOT NULL,
    Reason nvarchar(500) NOT NULL,
    PayloadJson nvarchar(max) NOT NULL,
    ResultJson nvarchar(max) NULL,
    State varchar(20) NOT NULL,
    CreatedAt datetime2(3) NOT NULL CONSTRAINT DF_XiaoshuSyncCommandLog_CreatedAt DEFAULT SYSUTCDATETIME(),
    CompletedAt datetime2(3) NULL,
    CONSTRAINT UQ_XiaoshuSyncCommandLog_Idempotency UNIQUE (IdempotencyKey)
  );
END;
GO

IF OBJECT_ID(N'dbo.XiaoshuSyncChangeLog', N'U') IS NULL
BEGIN
  CREATE TABLE dbo.XiaoshuSyncChangeLog (
    Id bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Dataset nvarchar(64) NOT NULL,
    LegacyKey bigint NOT NULL,
    ItemKey bigint NULL,
    Operation char(1) NOT NULL,
    ChangedAt datetime2(3) NOT NULL CONSTRAINT DF_XiaoshuSyncChangeLog_ChangedAt DEFAULT SYSUTCDATETIME(),
    CONSTRAINT CK_XiaoshuSyncChangeLog_Operation CHECK (Operation IN ('I','U','D'))
  );
  CREATE INDEX IX_XiaoshuSyncChangeLog_Dataset_Id ON dbo.XiaoshuSyncChangeLog(Dataset,Id) INCLUDE(LegacyKey,ItemKey,Operation,ChangedAt);
END;
GO

CREATE OR ALTER TRIGGER dbo.XiaoshuSync_ZL_User_Changes ON dbo.ZL_User AFTER INSERT,UPDATE,DELETE AS
BEGIN
  SET NOCOUNT ON;
  INSERT dbo.XiaoshuSyncChangeLog(Dataset,LegacyKey,Operation)
  SELECT N'users',COALESCE(i.UserID,d.UserID),CASE WHEN d.UserID IS NULL THEN 'I' WHEN i.UserID IS NULL THEN 'D' ELSE 'U' END
  FROM inserted i FULL OUTER JOIN deleted d ON d.UserID=i.UserID;
END;
GO

CREATE OR ALTER TRIGGER dbo.XiaoshuSync_ZL_Node_Changes ON dbo.ZL_Node AFTER INSERT,UPDATE,DELETE AS
BEGIN
  SET NOCOUNT ON;
  INSERT dbo.XiaoshuSyncChangeLog(Dataset,LegacyKey,Operation)
  SELECT N'nodes',COALESCE(i.NodeID,d.NodeID),CASE WHEN d.NodeID IS NULL THEN 'I' WHEN i.NodeID IS NULL THEN 'D' ELSE 'U' END
  FROM inserted i FULL OUTER JOIN deleted d ON d.NodeID=i.NodeID;
END;
GO

CREATE OR ALTER TRIGGER dbo.XiaoshuSync_ZL_CommonModel_Changes ON dbo.ZL_CommonModel AFTER INSERT,UPDATE,DELETE AS
BEGIN
  SET NOCOUNT ON;
  INSERT dbo.XiaoshuSyncChangeLog(Dataset,LegacyKey,ItemKey,Operation)
  SELECT CASE COALESCE(i.ModelID,d.ModelID)
      WHEN 58 THEN N'course-bindings' WHEN 54 THEN N'appointments' WHEN 59 THEN N'lessons'
      WHEN 56 THEN N'learning-records' WHEN 53 THEN N'practice-records' WHEN 60 THEN N'memory-records'
      WHEN 61 THEN N'assessments' WHEN 52 THEN N'vocabulary'
      WHEN 2 THEN CASE WHEN COALESCE(i.NodeID,d.NodeID)=3 THEN N'special-training' END
    END,
    COALESCE(i.GeneralID,d.GeneralID),COALESCE(i.ItemID,d.ItemID),
    CASE WHEN d.GeneralID IS NULL THEN 'I' WHEN i.GeneralID IS NULL THEN 'D' ELSE 'U' END
  FROM inserted i FULL OUTER JOIN deleted d ON d.GeneralID=i.GeneralID
  WHERE COALESCE(i.ModelID,d.ModelID) IN (2,52,53,54,56,58,59,60,61)
    AND (COALESCE(i.ModelID,d.ModelID)<>2 OR COALESCE(i.NodeID,d.NodeID)=3);
END;
GO

DECLARE @AddonTriggers TABLE(TableName sysname,ModelID int,Dataset nvarchar(64));
INSERT @AddonTriggers(TableName,ModelID,Dataset) VALUES
  (N'ZL_C_kcbd',58,N'course-bindings'),(N'ZL_C_order',54,N'appointments'),
  (N'ZL_C_skjl',59,N'lessons'),(N'ZL_C_ss',56,N'learning-records'),
  (N'ZL_C_lxjl',53,N'practice-records'),(N'ZL_C_gywjl',60,N'memory-records'),
  (N'ZL_C_cpda',61,N'assessments'),(N'ZL_C_ck',52,N'vocabulary'),
  (N'ZL_C_Article',2,N'special-training');
DECLARE @TableName sysname,@ModelID int,@Dataset nvarchar(64),@TriggerSql nvarchar(max);
DECLARE addon_cursor CURSOR LOCAL FAST_FORWARD FOR SELECT TableName,ModelID,Dataset FROM @AddonTriggers;
OPEN addon_cursor; FETCH NEXT FROM addon_cursor INTO @TableName,@ModelID,@Dataset;
WHILE @@FETCH_STATUS=0
BEGIN
  SET @TriggerSql=N'CREATE OR ALTER TRIGGER dbo.'+QUOTENAME(N'XiaoshuSync_'+@TableName+N'_Changes')+N' ON dbo.'+QUOTENAME(@TableName)+N' AFTER INSERT,UPDATE,DELETE AS
  BEGIN
    SET NOCOUNT ON;
    WITH changed AS (SELECT ID FROM inserted UNION SELECT ID FROM deleted)
    INSERT dbo.XiaoshuSyncChangeLog(Dataset,LegacyKey,ItemKey,Operation)
    SELECT N'''+REPLACE(@Dataset,'''','''''')+N''',c.GeneralID,c.ItemID,''U''
    FROM changed x INNER JOIN dbo.ZL_CommonModel c ON c.ItemID=x.ID AND c.ModelID='+CONVERT(nvarchar(20),@ModelID)+
    CASE WHEN @ModelID=2 THEN N' AND c.NodeID=3' ELSE N'' END+N';
  END;';
  EXEC sys.sp_executesql @TriggerSql;
  FETCH NEXT FROM addon_cursor INTO @TableName,@ModelID,@Dataset;
END;
CLOSE addon_cursor; DEALLOCATE addon_cursor;
GO

CREATE OR ALTER PROCEDURE dbo.XiaoshuSync_ExecuteCommand
  @Operation nvarchar(100), @Actor nvarchar(200), @Reason nvarchar(500),
  @IdempotencyKey nvarchar(128), @ExpectedUpdatedAt nvarchar(50) = NULL,
  @PayloadJson nvarchar(max), @ResultJson nvarchar(max) OUTPUT
AS
BEGIN
  SET NOCOUNT ON; SET XACT_ABORT ON;
  IF ISJSON(@PayloadJson) <> 1 THROW 51000, N'写入负载必须是有效 JSON', 1;
  BEGIN TRANSACTION;
  EXEC sys.sp_getapplock @Resource=N'xiaoshu-sync-command:'+@IdempotencyKey,@LockMode='Exclusive',@LockOwner='Transaction',@LockTimeout=10000;
  SELECT @ResultJson=ResultJson FROM dbo.XiaoshuSyncCommandLog WHERE IdempotencyKey=@IdempotencyKey AND State='completed';
  IF @ResultJson IS NOT NULL BEGIN COMMIT; RETURN; END;
  IF NOT EXISTS (SELECT 1 FROM dbo.XiaoshuSyncCommandLog WHERE IdempotencyKey=@IdempotencyKey)
    INSERT dbo.XiaoshuSyncCommandLog(IdempotencyKey,Operation,Actor,Reason,PayloadJson,State) VALUES(@IdempotencyKey,@Operation,@Actor,@Reason,@PayloadJson,'processing');

  /* 在此调用旧站 BLL/正式存储过程，与本日志同一事务并回写 ResultJson。 */
  ROLLBACK;
  THROW 51001, N'该写入操作尚未与旧站业务逻辑绑定，已安全拒绝', 1;
END;
GO