SQLite：ホットデータバックアップの作成

SQLiteには、オンザフライでデータベースをバックアップするメカニズムがあります。 多くの開発者は、何らかの理由でこれについて知りません。 このメカニズムはプリミティブですが、多くの状況に適しています。 この記事では、この非常に組み込みのバックアップ機能について説明し、独自のバックアップメカニズムのアーキテクチャを提案します。 複雑なデータ複製を手配する必要がある場合は、まあ、または少なくとも移動する方向を指示してください。







一般的に、最も簡単なオプションから始める必要があります。 SQLiteデータベースは単一のファイルです（デフォルトでは、 DELETEログモード）。 アプリケーションは、すべてのトランザクションを定期的に完了し、データベースへのすべての接続を閉じ、データベースファイルを予約にコピーするだけです。 データベースファイルが100 MB未満の場合、最新のコンピューターでのこのアクションには数秒かかります。 また、高速化することもできます-ファイルをメモリに読み込み（「スナップショット」を取得）、データベースを動作させ、別のストリームでゆっくりと内容をディスク上のファイルにダンプします。 驚くほど多くの人に。

オンラインバックアップAPI

ただし、この方法でメモリ内のベースをコピーすることはできません。 したがって、 オンラインバックアップAPIです。 これは、その場でバックアップを作成するためのSQLite APIです。 すべてが非常に単純に配置されます。 sqlite3_backup_init関数は、バックアッププロセスを開始します。

sqlite3_backup *sqlite3_backup_init( sqlite3 *pDest, /*     */ const char *zDestName, /*    */ sqlite3 *pSource, /*     */ const char *zSourceName /*    */ );
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

パラメーターでは、ソースデータベースと宛先データベースへの接続のオブジェクトが転送されます（メインデータベースの場合は「main」 、一時データベースの場合は「temp」 、またはATTACHステートメント経由で接続するときに使用されるエイリアス値として渡されます）。 バックアップ管理オブジェクトが返されます（0が返された場合、宛先データベースへの接続でエラーを確認する必要があります）。これは、最初のパラメーターとして残りのAPI関数に渡す必要があります。 これで、ページごとに実行されるバックアップを実行できます。 nPageページ（またはnPage = -1の場合はすべて）から部分をコピーするには、 sqlite3_backup_step関数を呼び出す必要があります。

  int sqlite3_backup_step(sqlite3_backup *p, int nPage);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この関数がSQLITE_DONEを返した場合、バックアップは完了し、すべてのページがコピーされます。 コードSQLITE_OK、SQLITE_BUSY、SQLITE_LOCKEDを受け取った場合、コピーは完了しませんが、正常に続行できます-sqlite3_backup_step（）関数も呼び出す必要があります。 その他の戻りコードは、エラーが発生したことを示します。 SQLITE_DONEコードを受け取った場合、 sqlite3_backup_finish（）を呼び出す必要があります。

  int sqlite3_backup_finish(sqlite3_backup *p);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして、バックアップが正常に完了したことを喜びながら、安らかに眠ります。 バックアップの現在のステータスに関する情報を取得するには、次の機能を使用します。

  int sqlite3_backup_remaining(sqlite3_backup *p); //    int sqlite3_backup_pagecount(sqlite3_backup *p); //   
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

パスカルの擬似コード上のSOME_PAGE_COUNTページのブロックでDstのSrcデータベースをバックアップするための完全なアルゴリズムは、次のようになります。

  Backup = sqlite3_backup_init(Dst, 'main', Src, 'main'); if Backup = nil then Abort; try repeat case sqlite3_backup_step(Backup, SOME_PAGE_COUNT) of SQLITE_DONE: break; SQLITE_OK, SQLITE_BUSY, SQLITE_LOCKED: continue; else Abort; end; Write(' ', sqlite3_backup_remaining(Backup) * 100 div sqlite3_backup_pagecount(Backup), '%'); Sleep(SOME_TIME); until false; finally sqlite3_backup_finish(Backup) end;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このAPIを使用すると、SQLiteはソースデータベースを一切ブロックしません。 データを読み取るだけでなく、更新することもできます。 バッチでコピー（ nPage > 0、つまり、 sqlite3_backup_step（）への1回の呼び出しで一度にすべてのページではない）とデータベースが変更された場合はどうなりますか？ コピーを再開してください！ SQLiteは、透過的にデータベースの最初からページのコピーを開始します。 言い換えると、ソースベースが急速に変化する場合、予約の完了をまったく待たない可能性があります。 良い知らせがあります。 バックアップと同じ接続を介してソースデータベースが変更された場合（別のスレッドからでも）、SQLiteは変更を宛先データベースに透過的に複製し、バックアップは再開されません。



SQLite Online Backup APIの利点-ソースデータベースは読み取りのためにブロックされず、単一の接続を介して更新される場合、データベースへの書き込みは干渉しません。 データベースが非常に大きく、異なるアプリケーションから頻繁に更新される場合はどうすればよいですか？ データ複製システムの作成について考える時が来ました。 実際、ここでは新しいものは何も発明されません。トリガーの助けを借りて、どのレコードが変更されたかを追跡し、変更の履歴を保持します。 テーブルには、一意のレコード番号を含むROWID列が含まれているという事実を利用します。 変更を定期的に別のデータベースに転送します。 これがどのように行われるかを詳細に示してください。

シンプルなデータ複製スキーマ

したがって、ソースデータベースでは、変更の影響を受けるレコードのアカウンティングテーブルを作成する必要があります。

  CREATE TABLE IF NOT EXISTS system_replicate_table(name TEXT UNIQUE); CREATE TABLE IF NOT EXISTS system_replicate_record(name TEXT, id INTEGER, PRIMARY KEY(name,id) );
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Fooテーブルでレプリケーションを有効にするには、 system_replicate_tableに追加します ：

  INSERT OR IGNORE INTO system_replicate_table(name) VALUES ('Foo');
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして、そのためのレプリケーショントリガーを作成します。

 function CreateTrigger(const Operation, TableName: string): string; begin result := Format('CREATE TRIGGER IF NOT EXISTS system_trigger_%s_replicate_%s AFTER %s ON %s FOR EACH ROW BEGIN ', [ TableName, Operation, Operation, TableName ]); if (Operation = 'INSERT') or (Operation = 'UPDATE') then result := result + Format('INSERT OR IGNORE INTO system_replicate_record(name, id) VALUES("%s", NEW.ROWID); ', [ TableName ]); if (Operation = 'DELETE') or (Operation = 'UPDATE') then result := result + Format('INSERT OR IGNORE INTO system_replicate_record(name, id) VALUES("%s", OLD.ROWID); ', [ TableName ]); result := result + ' END; '; end; Execute( CreateTrigger('INSERT', 'Foo') ); Execute( CreateTrigger('UPDATE', 'Foo') ); Execute( CreateTrigger('DELETE', 'Foo') );
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

トリガーは簡単です。 それらがまだない場合は、影響を受けるテーブルエントリのROWIDをsystem_replicate_recordに追加します （SQLiteトリガーのNEWおよびOLDについて自分で読んでください）。 したがって、興味深いテーブルに従ってレプリケーションを有効にし、ソースデータベースの操作を開始します。 データの変更はトリガーによって追跡されます。 ある時点（時間間隔またはsystem_replicate_recordのレコード数）で、データを複製します。つまり、変更を転送します。 変更されたレコードをFooテーブルから宛先データベースに複製する方法は？ これは、レプリケーションの最も難しい部分です。 すべてのデータベースオブジェクトのSQLを含むsqlite_masterシステムテーブルを使用します。 このSQLはオブジェクト作成ステートメントです（つまり、テーブルFooには「CREATE TABLE Foo（...）」があります）。

Fooテーブルをコピーするためのアルゴリズム（まだDstデータベースにない場合）。

1） SQLテーブルを取得します。

  SELECT sql FROM sqlite_master WHERE tbl_name = 'Foo' and type = 'table';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

宛先データベースで「そのまま」実行します（宛先データベースへの接続を単にexecuteメソッドに渡します）。

2）すべてのレコードを選択し、データを転送します（正確に後ほど転送する方法）。

  SELECT rowid as rowid, * FROM Foo
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

3）インデックスとトリガーも転送する必要がある場合、宛先データベースでSQLを実行します。この方法で取得します（システムインデックスとトリガーを除く）。

  SELECT sql FROM sqlite_master WHERE tbl_name = 'Foo' and (type = "index" or type = "trigger") and not name LIKE 'system_%' and not name LIKE 'sqlite_%'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

FooテーブルをSrcデータベースからDstデータベースに複製するためのアルゴリズム。

1） DstにテーブルFooがまだない場合、 Srcからそこにコピーし（上記を参照）、 5）に進みます。

2）それ以外の場合、影響を受けるレコードのROWIDを選択します 。

  SELECT id FROM sqlite_replicate_record WHERE name = 'Foo';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

コンマを介して長い文字列Rに結合します。 Rに"123,256,334,4700、..."のようなものが入るはずです 。

（行を追加してこの操作を行うことを考えないでください！必要に応じてバッファを選択し、展開します。ROWIDは8バイトの符号付き整数であることも覚えておいてください） 。

3） DstデータベースのFooテーブルからこれらのエントリを削除します。

  DELETE FROM [Foo] WHERE rowid IN (<   R>);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

4） SrcデータベースのFooからデータを選択し、 Dstデータベースにコピーします（実際のデータのコピーについては少し後で）。

  SELECT rowid as rowid, * FROM [Foo] WHERE rowid IN (SELECT id FROM system_replicate_record WHERE name = 'Foo');
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

5） Srcデータベースのレプリケーションテーブルを消去します。

  DELETE FROM sqlite_replicate_record WHERE name = 'Foo';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

データをコピーする方法を理解することは私たちに残っています。 それにはもう少しプログラム的なシャーマニズムが必要です。 レコードは、次のクエリによって選択されます。

  SELECT rowid as rowid, * FROM [Foo] [ WHERE ... ]
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この方法でのみ、 ROWIDが取得されること（および「ROWID」という名前を持つこと）を保証できます。 抽出されたレコードごとに、SQL挿入ステートメント（UTF-8エンコード）を作成します。

  INSERT INTO [Foo](<>) VALUES(<>)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

選択したレコードのすべての列をバイパスし、列名を「 <names> 」部分に追加し、値を「 <values >」部分にコンマで区切って追加します。 列名は「[」と「]」で囲まれている必要があります。 値はSQLリテラルとして表される必要があります。 ご存じのように、SQLiteには次の値タイプがあります。

  SQLITE_INTEGER = 1; SQLITE_FLOAT = 2; SQLITE_TEXT = 3; SQLITE_BLOB = 4; SQLITE_NULL = 5;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

それぞれをSQLリテラルとして取得する方法を学ぶ必要があります。 リテラルSQLITE_NULLは"null"です。 リテラルSQLITE_INTEGERは、整数（64ビット）の文字列表現、1234567890： "1234567890"です。 リテラルSQLITE_FLOATは、小数部と整数部の区切り文字としてドットを使用した実数のストリング表現です。123.456789： "123.456789" 。 文字列（ SQLITE_TEXT ）をリテラルに変換するには、その中のすべての単一引用符を2倍にし、結果を単一引用符 "Hello、Mc'Duck"： "'Hello、Mc''Duck'"で囲みます。 残りのBLOB 。 SQLiteのSQLITE_BLOB （バイナリデータ）リテラルの形式は「x'A0B1C2 ... '」です。「A0」は最初のバイトの16進コード、「B1」は2番目のバイトの16進コードなどです。



以上です。 レコードが完全にコピーされる、データレプリケーションの最も単純な作業バージョンについて説明しました。 もちろん、最適化のためのフィールドがあります。 Dstデータベース内のすべての変更をトランザクションにラップすると便利です。 挿入ステートメントを作成するとき、列名を持つパーツを一度作成して再利用できます。



示されているアーキテクチャは、スキーマ複製をサポートしていません。 元のテーブルにフィールドを追加して変更すると、レプリケーションが中断されます。 宛先データベースのテーブルを削除して（再度完全にコピーされるようにする）、同期スキームを追加してレプリケーションを複雑にする必要があります。 同じ考慮事項が、新しく作成されたインデックスとトリガーに適用されます。



PS。 最新のマネージャーを使用して、SQLiteデータベースを管理します。