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

SQL Anywhere 11.0.1 (日本語) » Mobile Link - クライアント管理 » Mobile Link 用 SQL Anywhere クライアント » スクリプト化されたアップロード

 

スクリプト化されたアップロードの例

この例は、競合を検出するスクリプト化されたアップロードの設定方法を示しています。例では、スクリプト化されたアップロードで必要な統合データベースとリモート・データベース、ストアド・プロシージャ、パブリケーション、サブスクリプションを作成します。この例は、参考にするだけでもかまいませんし、テキストをコピー・アンド・ペーストしてサンプルを実行することもできます。

統合データベースの作成

サンプル・ファイルを保持するディレクトリを作成します。ここでは、名前を scriptedupload とします。コマンド・プロンプトを開き、そのディレクトリに移動します。

(この例では、ファイル名を指定し、ファイルが現在のディレクトリにあるものと想定しています。実際のアプリケーションでは、ファイルのフル・パスを指定してください。)

次のコマンドを実行して、統合データベースを作成します。

dbinit consol.db

次のコマンドを実行して、統合データベースの ODBC データ・ソースを定義します。

dbdsn -w dsn_consol -y -c "uid=DBA;pwd=sql;dbf=consol.db;eng=consol"

データベースを Mobile Link 統合データベースとして使用するには、Mobile Link で使用するシステム・テーブル、ビュー、ストアド・プロシージャを追加する設定スクリプトを実行する必要があります。次のコマンドを実行し、統合データベースとして consol.db を設定します。

dbisql -c "dsn=dsn_consol" %sqlany11%\MobiLink\setup\syncsa.sql

Interactive SQL を開き、dsn_consol DSN を使用して consol.db に接続します。次の SQL 文を実行します。実行すると、統合データベースで employee テーブルが作成され、値をテーブルが挿入され、必要な同期スクリプトが作成されます。

CREATE TABLE employee (
   id      unsigned integer primary key,
   name    varchar( 256),
   salary  numeric( 9, 2 )
);

INSERT INTO employee VALUES( 100, 'smith', 225000 );
COMMIT;

CALL ml_add_table_script( 'default', 'employee', 'upload_insert',
       'INSERT INTO employee ( id, name, salary ) VALUES ( ?, ?, ? )' );

CALL ml_add_table_script( 'default', 'employee', 'upload_update',
       'UPDATE employee SET name = ?, salary = ? WHERE id = ?' );

CALL ml_add_table_script( 'default', 'employee', 'upload_delete',
       'DELETE FROM employee WHERE id = ?' );

CALL ml_add_table_script( 'default', 'employee', 'download_cursor',
       'SELECT * from employee' );
リモート・データベースの作成

サンプル・ディレクトリのコマンド・プロンプトで、次のコマンドを実行して、リモート・データベースを作成します。

dbinit remote.db

次のコマンドを実行して、ODBC データ・ソースを定義します。

dbdsn -w dsn_remote -y -c "uid=dba;pwd=sql;dbf=remote.db;eng=remote"

Interactive SQL で、dsn_remote DSN を使用して remote.db に接続します。次の文のセットを実行して、リモート・データベースでオブジェクトを作成します。

まず、同期させるテーブルを作成します。insert_time と delete_time カラムは同期されませんが、アップロードするローを指定するためにアップロード・ストアド・プロシージャで使用される情報が含まれます。

CREATE TABLE employee (
     id            unsigned integer primary key,
     name          varchar( 256),
     salary        numeric( 9, 2 ),
     insert_time   timestamp default '1900-01-01'
);

次に、ストアド・プロシージャと、アップロードを処理するその他の処理を定義する必要があります。更新、挿入、削除ごとに別々に定義します。

挿入の処理

まず、ローを挿入するときに、各ローに insert_time を設定するトリガを作成します。このタイムスタンプは、最後の同期以降にローが挿入されたかどうかを調べるのに使用されます。統合データベースからダウンロードされた挿入を dbmlsync が適用するときは、このトリガは起動しません。これは、この例の後半で、FireTriggers 拡張オプションがオフに設定されるからです。ダウンロードによって挿入されたローは、employee テーブルが作成されたときに定義されたデフォルト値 1900-01-01 を insert_time として取得します。ローが新しい挿入として処理され、次の同期中にアップロードされるのを防ぐために、この値は、開始進行状況値よりも前に設定する必要があります。

CREATE TRIGGER emp_ins AFTER INSERT ON employee
REFERENCING NEW AS newrow
FOR EACH ROW
BEGIN
    UPDATE employee SET insert_time = CURRENT TIMESTAMP
    WHERE id = newrow.id
END;

次に、アップロード用に挿入されたすべてのローを結果セットとして返すプロシージャを作成します。このプロシージャは、最後に正常にアップロードされてから (insert_time に基づいて) 挿入された後、続いて削除されなかったたすべてのローを返します。最後の正常なアップロードの時間は、#hook_dict テーブルの開始進行状況値から判別します。この例では、dbmlsync 拡張オプション LockTables にデフォルト設定を使用して、同期対象のテーブルをロックします。したがって、終了進行状況後に挿入されたローを除外する必要がありません。テーブルのロックによって、アップロードの構築中に、操作が終了進行状況後に発生することが防止されます。

CREATE PROCEDURE employee_insert()
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 inserts all rows inserted after the start_time
    // that were not subsequently deleted
    SELECT id, name, salary
    FROM employee e
    WHERE insert_time > start_time AND
       NOT EXISTS( SELECT id FROM employee_delete ed  WHERE ed.id = e.id );

END;
更新の処理

アップロードを処理するには、アップロード構築中、開始進行状況値に基づいて正しい更新前イメージを使用する必要があります。

まず、更新されたローの更新前イメージを保持するテーブルを作成します。スクリプト化されたアップロードを生成するときには、更新前イメージが使用されます。

CREATE TABLE employee_preimages (
   id           unsigned integer NOT NULL,
   name         varchar( 256),
   salary       numeric( 9, 2 ),
   img_time     timestamp default CURRENT TIMESTAMP,
   primary key( id, img_time )
);

次に、各ローが更新されるときに、更新前イメージを保存するトリガを作成します。挿入トリガと同様、このトリガもダウンロードでは起動しません。

このトリガは、ローが更新されるたびに更新前イメージを保存します (ただし、2 つの更新が非常に近く、タイムスタンプが同じ場合を除く)。これは一見、無駄に見えるので、ローの更新前イメージがテーブルにない場合のみ保存し、sp_hook_dbmlsync_upload_end フックに依存して、更新前イメージがアップロードされた後に削除しがちです。

しかし、sp_hook_dbmlsync_upload_end フックは、この目的では確実ではありません。アップロードを送信した後で受信確認される前に、ハードウェアまたはソフトウェアの障害により dbmlsync が停止した場合、フックが呼び出されない可能性があります。その結果、ローが正常にアップロードされても、ローは更新前イメージ テーブルから削除されません。また、通信障害が発生すると、dbmlsync はサーバからアップロードの受信確認を受信できない場合があります。この場合、フックに渡されるアップロード・ステータスは「不明」になります。このステータスでは、フックは、更新前イメージ・テーブルがクリーンアップされたのかそのままなのかを判別できません。複数の更新前イメージを保存すると、アップロードが構築されるときに、開始進行状況値に基づいて正しい更新前イメージが常に選択されます。

CREATE TRIGGER emp_upd AFTER UPDATE OF name,salary ON employee
   REFERENCING OLD AS oldrow
   FOR EACH ROW
BEGIN
   INSERT INTO employee_preimages ON EXISTING SKIP VALUES(
      oldrow.id, oldrow.name, oldrow.salary, CURRENT TIMESTAMP );
END;

次に、更新を処理するアップロード・プロシージャを作成します。このストアド・プロシージャは、他のスクリプトの 2 倍のカラムを持つ結果セットを 1 つ返します。これには、更新前イメージ (Mobile Link サーバから最後に受信したローと、正常にアップロードされたローの値) と更新後イメージ (統合データベースに入力される値) が含まれます。

更新前イメージは、start_progress 後に記録された employee_preimages の一番最初の値セットです。この例では、削除されてから再度挿入された既存のローは、正しく処理されません。より完全なソリューションでは、これらのローは更新としてアップロードされます。

CREATE PROCEDURE employee_update()
RESULT(
       preimage_id  unsigned integer,
       preimage_name varchar( 256),
       preimage_salary numeric( 9,2 ),
       postimage_id  unsigned integer,
       postimage_name varchar( 256),
       postimage_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 an update all rows that have been updated since 
    // start_time that were not newly inserted or deleted.
    SELECT ep.id, ep.name, ep.salary, e.id, e.name, e.salary
    FROM employee e JOIN employee_preimages ep 
        ON ( e.id = ep.id )
    // Do not select rows inserted since the start time. These should be
    // uploaded as inserts.
    WHERE insert_time <= start_time 
      // Do not upload deleted rows.
      AND NOT EXISTS( SELECT id FROM employee_delete ed  WHERE ed.id = e.id )
      // Select the earliest pre-image after the start time.
      AND ep.img_time = ( SELECT MIN( img_time )
            FROM employee_preimages
            WHERE id = ep.id
            AND img_time > start_time );
END;
削除の処理

まず、削除されたローのリストを維持するテーブルを作成します。

CREATE TABLE employee_delete (
    id           unsigned integer  primary key NOT NULL,
    name         varchar( 256 ),
    salary       numeric( 9, 2 ),
    delete_time  timestamp
);

次に、employee テーブルからローが削除されるときに employee_delete テーブルを移植するトリガを作成します。後で dbmlsync 拡張オプション FireTriggers を false に設定するので、このトリガは、ダウンロード中は呼び出されません。このトリガは、削除されたローは再度挿入されることはないことを前提としています。したがって、複数回削除されるローは処理されません。

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;

次の SQL 文は、削除を処理するアップロード・プロシージャを作成します。ストアド・プロシージャは、統合データベースで削除するローが含まれる結果セットを返します。ストアド・プロシージャは employee_preimages テーブルを使用するので、ローが更新された後に削除されると、削除用にアップロードされるイメージは、最後に正常にダウンロードまたはアップロードされたイメージになります。

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_preimage と employee_delete テーブルをクリーンアップする upload_end フックを作成します。この例では、同期中にテーブルがロックされるように、dbmlsync 拡張オプション LockTables にデフォルト設定を使用します。したがって、テーブルのローに対して、end_progress 後に発生した操作が実行される心配がありません。ロックにより、このような操作が発生するのを防ぐことができます。

CREATE PROCEDURE sp_hook_dbmlsync_upload_end()
BEGIN
    DECLARE val   varchar(256);
    
    SELECT value
    INTO val 
    FROM #hook_dict
    WHERE name = 'upload status';
    
    IF val = 'committed' THEN
      DELETE FROM employee_delete;
      DELETE FROM employee_preimages;
    END IF;
END;
パブリケーション、Mobile Link ユーザ、サブスクリプションの作成

pub1 と呼ばれるパブリケーションでは、スクリプト化されたアップロードの構文 (WITH SCRIPTED UPLOAD) が使用されます。このパブリケーションによって、employee テーブルのアーティクルが作成され、スクリプト化されたアップロード用に作成したばかりの 3 つのストアド・プロシージャが登録されます。u1 という Mobile Link ユーザと、v1 と pub1 の間のサブスクリプションが作成されます。拡張オプション FireTriggers はオフに設定されるので、ダウンロードが適用されているときはリモート・データベースでトリガが起動されません。これにより、次の同期中に、ダウンロードされた変更がアップロードされるのを防ぐことができます。

CREATE PUBLICATION pub1 WITH SCRIPTED UPLOAD (
TABLE employee( id, name, salary ) USING (
   PROCEDURE employee_insert FOR UPLOAD INSERT, 
   PROCEDURE employee_update FOR UPLOAD UPDATE, 
   PROCEDURE employee_delete FOR UPLOAD DELETE, 
      )
)

CREATE SYNCHRONIZATION USER u1;

CREATE SYNCHRONIZATION SUBSCRIPTION TO pub1 FOR u1
TYPE 'tcpip'
ADDRESS 'host=localhost'
OPTION FireTriggers='off';
スクリプト化されたアップロードの実行

リモート・データベースに接続し、スクリプト化されたアップロードを使用して、同期するデータを挿入します。たとえば、Interactive SQL のリモート・データベースに対して次の SQL 文を実行します。

INSERT INTO employee(id, name, salary) VALUES( 7, 'black', 700 );
INSERT INTO employee(id, name, salary) VALUES( 8, 'anderson', 800 );
INSERT INTO employee(id, name, salary) VALUES( 9, 'dilon', 900 );
INSERT INTO employee(id, name, salary) VALUES( 10, 'dwit', 1000 );
INSERT INTO employee(id, name, salary) VALUES( 11, 'dwit', 1100 );
COMMIT;

コマンド・プロンプトで、Mobile Link サーバを起動します。

mlsrv11 -c "dsn=dsn_consol" -o mlserver.mls -v+ -dl -zu+

dbmlsync を使用して同期を開始します。

dbmlsync -c "dsn=dsn_remote" -k -uo -o remote.mlc -v+

これで、挿入がアップロードされたことを確認できます。

例のクリーンアップ

例を完了後コンピュータをクリーンアップするには、次の手順を実行します。

mlstop -h -w
dbstop -y -c eng=consol
dbstop -y -c eng=remote

dberase -y consol.db
dberase -y remote.db

del remote.mlc mlserver.mls