Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.1 » MobiLink - 客户端管理 » 用于 MobiLink 的 SQL Anywhere 客户端 » 脚本式上载 » 教程:使用脚本式上载

 

第 5 课:处理删除

本课假定您已完成前面的所有课程。 请参见第 1 课:创建统一数据库

 ♦ 处理删除
  1. 使用连接到远程数据库的 Interactive SQL 实例,创建一个表,此表用于维护已删除行的列表:

    CREATE TABLE employee_delete (
        id           unsigned integer  primary key NOT NULL,
        name         varchar( 256 ),
        salary       numeric( 9, 2 ),
        delete_time  timestamp
    );
  2. 接下来,创建一个触发器,此触发器在从 employee 表中删除行时填充 employee_delete 表。

    CREATE TRIGGER emp_del AFTER DELETE ON employee
    REFERENCING OLD AS delrow
    FOR EACH ROW
    BEGIN
         INSERT INTO employee_delete 
    VALUES( delrow.id, delrow.name, delrow.salary, CURRENT TIMESTAMP );
    END;

    下载时不调用此触发器,因为稍后会将 dbmlsync 扩展选项 FireTriggers 设置为 false。请注意,此触发器假设不会再插入已删除行;所以它不处理多次删除的行。

  3. 下一个 SQL 语句创建一个用于处理删除的上载过程。



    CREATE PROCEDURE employee_delete()
    RESULT( id  unsigned integer,
              name varchar( 256),
              salary numeric( 9,2 )
          )
    BEGIN
        DECLARE start_time timestamp;
    
        SELECT value
        INTO start_time
        FROM #hook_dict
        WHERE name = 'start progress as timestamp';
    
       // Upload as a delete all rows that were deleted after the 
       // start_time that were not inserted after the start_time.
       // If a row was updated before it was deleted, then the row
       // to be deleted is the pre-image of the update.
        SELECT IF ep.id IS NULL THEN ed.id ELSE ep.id ENDIF,
               IF ep.id IS NULL THEN ed.name ELSE ep.name ENDIF,
               IF ep.id IS NULL THEN ed.salary ELSE ep.salary ENDIF
        FROM employee_delete ed LEFT OUTER JOIN employee_preimages ep
              ON( ed.id = ep.id AND ep.img_time > start_time )
        WHERE
          // Only upload deletes that occurred since the last sync.
          ed.delete_time > start_time
          // Don't upload a delete for rows that were inserted since 
          // the last upload and then deleted.
        AND NOT EXISTS ( 
          SELECT id
             FROM employee e
             WHERE e.id = ep.id AND e.insert_time > start_time )
        // Select the earliest preimage after the start time.
        AND ( ep.id IS NULL OR ep.img_time = (SELECT MIN( img_time )
                                              FROM employee_preimages
                                              WHERE id = ep.id
                                               AND img_time > start_time ) );
    END;

    此存储过程返回一个包含统一数据库上要删除行的结果集。此存储过程使用 employee_preimages 表,这样如果对行进行更新然后再删除,上载的删除映像是成功下载或上载的最后一个映像。

  4. 前进至第 6 课:清除前映像表