SQLite-優れた組み込みデータベース（パート3）

最初の部分は序論です。
2番目の部分はクイックスタートです。

第三部-微妙さと機能。



この部分は、あらゆる種類のSQLite機能の寄せ集めです。 ここで（私の意見では）最も重要なトピックを集めましたが、これなしではSQLiteのnを理解することは不可能です。

繰り返しますが、多くの情報があるため、記事の形式は次のようになります。興味深いトピックへの簡単な紹介と、詳細が記載されているホームサイトへのリンクです。 悲しいかな、このサイトは英語です。

マルチスレッドアプリケーションでのSQLiteの使用

SQLiteは、シングルスレッドバージョン（コンパイルパラメーターSQLITE_THREADSAFE = 0 ）で構築できます。

このバリアントでは、同期コードが完全に存在しないため、複数のストリームから同時に使用することはできません。 なんで？ 猛烈なスピードで。

sqlite3_threadsafe（）を呼び出すことでマルチスレッドがあるかどうかを確認できます。0が返される場合、これはシングルスレッドのSQLiteです。

デフォルトでは、SQLiteはスレッド化サポート（sqlite3.dll）でコンパイルされます。

マルチスレッドSQLiteを使用するには、シリアル化とマルチスレッドの2つの方法があります。

シリアル 化 （接続を開くときにSQLITE_OPEN_FULLMUTEXフラグを指定する必要があります）。 このモードでは、スレッドは任意の方法でSQLite呼び出しをプルでき、制限はありません。 ただし、すべての呼び出しは互いにブロックし、厳密に順番に処理されます。

マルチスレッド （ SQLITE_OPEN_NOMUTEX ）。 このモードでは、複数のスレッドから同じ接続を同時に使用することはできません（ただし、異なるスレッドが同時に異なる接続を使用することは許可されています）。 通常、このモードが使用されます。

さらに詳しく

データ形式

SQLiteデータベースは、（テキスト）データをUTF-8またはUTF-16で保存できます。

APIコールのセットは、UTF-8を受け取るコール（ sqlite3_XXX ）とUTF-16を受け取るコール（ sqlite3_XXX16 ）で構成されます。

インターフェイスと接続のデータ型が一致しない場合、変換はその場で実行されます。

常にUTF-8を使用します。

UNICODEサポート

デフォルトでは、サポートはありません。 sqlite3_create_collat​​ionを使用して、独自の照合（比較メソッド）を作成する必要があります。
そして、 www.sqlite.org/c3ref/create_function.htmlを介して（）、upper（）、lower（）などの組み込み関数を定義します。

そのようなプロジェクト「Unicodeの国際コンポーネント」 、ICUがあります。

また、SQLite DLLをすでに構築している人もいます。

SQLiteでICUを使用する方法。

ハブに関する記事を見つけました

データ型と値の比較

既に述べたように、SQLIteを使用すると、任意の列に任意の値を書き込むことができます。

データベース内の値は、次のストレージタイプのいずれかに属します。
NULL
INTEGER （1、2、3、4、6または8バイトを使用）、
REAL （浮動小数点数、IEEE形式の8バイト）、
TEXT （データベースデータ形式の文字列、通常はUTF-8）、
BLOB （バイナリデータをそのまま保存）。

さまざまなタイプの値のソート順：
-少なくともNULL （別のNULLを含む）。
-INTEGERおよびREALは TEXTおよびBLOBよりも小さく、算術的に比較されます。
-TEXTはどのBLOBよりも小さく、照合に基づいて相互に比較されます。
-BLOBはmemcmp（）を介して互いに比較されます。

SQLiteは、いくつかの場所で暗黙的な型変換をその場で実行します。
-列に値を入力するとき（列のタイプによって変換の推奨が設定されます）。
-値を互いに比較する場合。

列には、 TEXT 、 NUMERIC 、 INTEGER 、 REAL 、 NONEという タイプキャストの推奨事項があります。

BLOB値とNULL値は、常に「現状のまま」の列に入力されます。

TEXT列には、 TEXT値が「そのまま」入力され、 INTEGERとREALの値が行になります。
NUMERIC 、 INTEGER列では、数値は「そのまま」書き込まれ、_mogut_の場合、行は数値になります（つまり、「ロスレス」逆変換が許可されます）。
REAL列の場合、ルールはINTEGER （ NUMERIC ）に似ています。 違いは、すべての数値が浮動小数点形式であることです。
NONE列には、値が「そのまま」入力されます（特に指定しない限り、このタイプがデフォルトで使用されます）。

異なるタイプの値を互いに比較する場合、追加のタイプ変換を実行できます。

数値を文字列と比較するときに、文字列を「ロスレス」数値に変換できる場合、数値になります。

さらに詳しく

ここで、SQLiteの一意のインデックスには、任意の数のNULL値が存在する可能性があることに注意してください（Oracleは同意し、MS SQLは同意しません）。

インメモリデータベース

sqlite3_open（）呼び出しでファイル名を"：memory："として渡すと、SQLiteはメモリ内に新しい（クリーンな）データベースへの接続を作成します 。

この接続は、使用ロジックによると、ファイル内のデータベースへの接続とまったく区別できません。同じSQLコマンドのセットを使用できます。

残念ながら、メモリ内の同じデータベースへの2つの接続を開く方法はありません。

UPD：すでに判明していますが、メモリ内の1つのデータベースへの2つの接続を開くことができます。

rc = sqlite3_open("file:memdb1?mode=memory&cache=shared", &db); 

 ATTACH DATABASE 'file:memdb1?mode=memory&cache=shared' AS aux1; 

詳細をご覧ください。

複数のデータベースに同時に参加する

データベースへの接続を開くには、 sqlite3_open（）呼び出しを使用します。

いつでも、SQL ATTACH DATABASEコマンドを使用して、開いている接続に最大10個のデータベースを接続できます。

 sqlite3_open('foo.sqlite3', &db); //       "foo.sqlite3" sqlite3_exec(&db, "ATTACH 'bar.sqlite3' AS bar", ... ); //  "bar.sqlite3" 

これで、db1.sqlite3ファイル内のすべてのデータベーステーブルが透過的に使用可能になりました。

名前の競合を解決するには、添付ファイル名を使用する必要があります（メインベースは"main"と呼ばれます）。

 SELECT * FROM main.my_table UNION SELECT * FROM bar.my_table 

メモリ内の新しいデータベースをデータベースに接続し、キャッシュなどに使用することを妨げるものは何もありません。

 sqlite3_open('foo.sqlite3', &db); //       "foo.sqlite3" sqlite3_exec(&db, "ATTACH ':memory:' AS mem", ... ); //      

さらに詳しく

これは非常に便利な機能です。 接続されたデータベースは、メインデータベースと同じデータ形式である必要があります。そうでない場合はエラーになります。

一時データベース

sqlite3_open（）でファイル名の代わりに空の文字列を渡すと、ディスク上のファイルに一時データベースが作成されます。 さらに、データベースへの接続を閉じた後、ディスクから削除されます。

PRAGMAコマンドによるデータベースの微調整

PRAGMA SQLコマンドは、接続またはデータベース自体のすべての種類の設定を設定するために使用されます。

  PRAGMA name; //     name PRAGMA name = value; //   name  value 

接続のセットアップ（当然）は、開いた直後と使用前に行う必要があります。

すべてのパラメータの詳細な説明はこちらです。

私は最も重要な事柄について説明します。

 PRAGMA page_size = bytes; //   ;   -       ,       (  4096) PRAGMA cache_size = -kibibytes; //      ,     2000   PRAGMA encoding = "UTF-8"; //   ,   UTF-8 PRAGMA foreign_keys = 1; //   foreign keys,   -  PRAGMA journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF; //   , .  PRAGMA synchronous = 0 | OFF | 1 | NORMAL | 2 | FULL; //   , .  

トランザクションのログとコミット

そのため、このトピックについて説明します。マスターすると、すぐにSQLiteのマスターの第3レベルに進みます。

SQLiteは、データベース（ ACID ）のデータの整合性を注意深く監視し、 トランザクションを介してデータを変更するメカニズムを実装します。

トランザクションについて簡単に説明すると、トランザクションは完全にロールアップされるか、完全にロールバックされます。 中間状態はありません。

トランザクションを明示的に使用しない場合（ BEGIN; ...; COMMIT; ）、 暗黙的なトランザクションが常に作成されます。 コマンドを実行する前に開始し、直後にコミットします。

したがって、ついでに、SQLiteの「遅さ」についての苦情。 SQLiteは1秒あたり最大5万件のレコードを挿入することもできますが、1秒あたり最大50件を超えるトランザクションを記録することはできません。

そのため、暗黙的なトランザクションを使用してレコードをすばやく挿入することはできません。

デフォルト設定では、SQLiteは操作中に電源がオフになった場合でもデータベースの整合性を保証します。

この驚くべき動作は、 ロギング （特別なファイル）と、ディスクへの変更を同期するための独創的なメカニズムによって実現されます。

データベース内のデータを簡単に更新するには、次のようにします。

-SQLiteは、データベースを変更する前に、データベースから変更したページを別のファイル（ログ）に保存します。つまり、単にそこにコピーします。
-ページのコピーが作成されたことを確認すると、SQLiteはデータベースの変更を開始します。
-データベースへのすべての変更が「ディスクに到達」し、データベースが完全になったことを確認すると、SQLiteはログを消去します。

トランザクションメカニズムの原子性はここで詳細に説明されます 。

SQLiteがデータベースへの接続を開き、ログが既に存在することを確認すると、データベースが不完全な状態にあることを認識し、最後のトランザクションを自動的にロールバックします。

つまり、実際には、障害後のデータベース回復メカニズムはSQLiteに組み込まれており、ユーザーにとってシームレスに機能します。

デフォルトでは、ログは削除モードになっています。

 PRAGMA journal_mode = DELETE 

これは、トランザクションが完了した後にログファイルが削除されることを意味します。 このモードでログファイルがあるという事実は、SQLiteにとってトランザクションが完了していないことを意味し、データベースを復元する必要があります。 ログファイルには、「-journal」が追加されるデータベースファイルの名前が付けられます。

TRUNCATEモードでは、ログファイルはゼロに切り捨てられます（一部のシステムでは、ファイルを削除するよりも速く動作します）。

PERSISTモードでは、ログファイルの先頭がゼロで詰まっています（サイズは変更されず、多くのスペースを占有します）。

MEMORYモードでは、ログファイルはメモリに保持され、これは迅速に機能しますが、障害が発生した場合のデータベースの回復を保証しません（ディスク上にデータのコピーはありません）。

または、ジャーナルを完全に無効にすることができます（ PRAGMA journal_mode = OFF ）。 この状況では、トランザクションのロールバック（ ROLLBACKコマンド）が動作を停止し、プログラムがクラッシュした場合にデータベースが破損する可能性があります。

メモリ内のデータベースの場合、ログモードはMEMORYまたはOFFのいずれかのみです。

少し戻りましょう。 SQLiteは、データベースが常に完全であることをどのように「確認」しますか？

最新のシステムでは、巧妙なキャッシングを使用してパフォーマンスが向上し、ディスクへの書き込みが遅れることがあります。

SQLiteがデータベースへの書き込みを終了し、ログファイルを消去してトランザクションをコミットしたことを示したいとします。

データベースが更新される前にファイルが消去された場合はどうなりますか？

この期間中に電源がオフになると、ログはそこになく、データベースはまだ完全ではありません-データ損失！

つまり、独創的な変更コミットメカニズムは、ディスクシステムとOSからのいくつかの保証に依存する必要があります。

PRAGMA同期は、「パラノイア」SQLiteの度合いをこの効果に設定します。

OFFモード（または0）は、SQLiteはデータをOSに転送した直後（つまり、対応するOS APIを呼び出した直後）にディスク上で固定されていると見なします。

つまり、アプリケーションがクラッシュした場合 （OSが引き続き動作するため）には整合性が保証されます が、OSのクラッシュや停電の場合には保証されません。

NORMAL同期モード（または1）は、OSクラッシュ時およびほぼすべての停電時の整合性を保証します。 電力が最も不適切な瞬間に失われると、ベースが劣化する可能性がゼロではありません。 これは、パフォーマンスと信頼性の点で、一種の中程度の妥協モードです。

FULLモードは、いつでもどこでも、どんな事故でも完全性を保証します。 しかし、特定の場所では期待の一時停止が行われるため、もちろん、よりゆっくりと動作します。 そして、これがデフォルトのモードです。

したがって、 WALのような雑誌のトピックのみが未到達のままでした。

WALログモード

デフォルトでは、データベースログモードは常にDELETEに 「戻ります」。 データベースへの接続を開き、モードをPERSISTに設定したとします。 データを変更し、接続を閉じました。

ディスク上にログファイルがあります（最初はゼロです）。

データベースへの接続を再度開きます。 この接続でログモードを設定しない場合、再度DELETEで機能します。 データを更新するとすぐに、トランザクションコミットメカニズムによってログファイルが消去されます。

WALログモードの動作は異なります-「永続的」です。 データベースをWALモードにするとすぐに、データベースが別のログモードに明確に変更されるまで、このモードのままになります。

なぜそれが必要なのですか？

当初、SQLiteは組み込みデータベースとして設計されました。 データへの同時アクセスを共有するアーキテクチャは基本的なものでした。同時に、複数の接続がデータベースを読み取ることができますが、一度に書き込みできる接続は1つだけです。 これは、少なくとも、書き込み接続がデータベースがリーダーから「リリース」されるのを待っていることを意味します。 「ビジー」データベースに書き込もうとすると、アプリケーションはSQLITE_BUSYエラーを受け取ります（ SQLITE_LOCKEDと混同しないでください ！）。 ファイルロックAPIを介してアクセスを共有するためのこのメカニズムが実現されます（ネットワークドライブではうまく機能しないため、SQLiteは推奨されません。 詳細

WAL（ 先行書き込みロギング ）モードでは、データベースの「リーダー」とデータベースの「ライター」が互いに干渉することはなくなりました。つまり、読み取り中にデータを変更できます。 要するに、これはすべてが存在する大規模で深刻なDBMSへの一歩です。 また、WALのSQLiteの方が高速であると主張されています。

しかし、欠点もあります：
-OSからの追加のnishtyakiが必要です（unixとWindowsにはこれらのnishtyakiがあります）。
-データベースはいくつかのファイル（ファイル"XXX-wal"および"XXX-shm" ）を取ります。
-大規模なトランザクションではうまく機能しません（条件付きで、トランザクションが50 MBを超える場合）。
-このようなデータベースを読み取り専用モードで開くことはできません。
-追加のチェックポイント操作が発生します。

実際、WALモードでは、データベースデータはデータベースとログファイルの間で共有されます。 チェックポイント操作は、データをデータベースに転送します。 デフォルトでは、ログが1000データベースページを使用している場合、これは自動的に行われます。
つまり、高速なCOMMITがあり、突然COMMITがそれについて考え、チェックポイントを開始しました。 この動作が望ましくない場合は、手動でチェックポイントを実行できます（すべてが落ち着いている場合）。これは別のプロセスで実行できます。

制限

SQLiteは、その小型化にもかかわらず、実際にはフィールド、テーブル、またはデータベースのサイズに深刻な制限を課していません。

デフォルトでは、BLOBまたは文字列値は1 GBを占有でき、これは1レコードのサイズ制限と同じです（2 ^ 31-1、SQLITE_MAX_LENGTHパラメーターに上げることができます）。

列数：2000（32767まで上げることができます、SQLITE_MAX_COLUMN）。

SQLステートメントのサイズ：1 MB（1073741824バイト、SQLITE_MAX_SQL_LENGTH）。

同時結合：64テーブル。

接続にベースを接続します：10（最大62、SQLITE_MAX_ATTACHED）

データベースの最大ページ数：1073741823（最大2147483646、SQLITE_MAX_PAGE_COUNT）。

ページサイズを65,636バイトに設定した場合、最大データベースサイズは約14テラバイトになります。

テーブル内のエントリの最大数は2 ^ 64-1ですが、実際には、もちろん、サイズ制限は早くなります。

詳細sqlite.org/limits.html

UDP：SQLite Optimization Links： 1 2 android-1 android-2

続く