コンテンツへスキップ

SQLite覚え書き

公開日 SQLite覚え書き への2件のコメント

C/C++環境でsqliteを使用する際の覚書です。

環境

  • Ubuntu18.04 (4.15.0-36-generic, 64bit)
  • gcc 7.3.0 (Ubuntu 7.3.0-27ubuntu1~18.04)
  • sqlite3 3.22

 

C/C++ API使い方

基本的な使い方の覚書です。

DB接続と切断

sqlite3_open()もしくはsqlite3_open_v2()でデータベースに接続します。ファイル名がUTF-16の場合はsqlite3_open16()を使用します。使い終わったらsqlite_close_v2()で切断。


#include <sqlite3.h>

int main()
{
sqlite3* db;

// open db connection
int rtn = sqlite3_open_v2(
"test_bench.db",
&db,
SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE,
nullptr);
if(rtn != SQLITE_OK){
cerr << sqlite3_errmsg(db) << endl;
}

/* exec query */

// close db connection 
sqlite_close_v2(db);

return 0;
}

詳細は以下のドキュメントを参照してください。

クエリの実行

sqlite3_exec()を使う場合

sqlite3_exec()は内部的にはprepared_statementを生成して実行しています(sqlite3_prepare,sqlite3_bind_xxx,sqlite3_stepのWrapper)。”CREATE TABLE”のような初回しか実行しないようなクエリを実行するのに便利です。

  int ret = sqlite3_exec(db, "CREATE TABLE nodes_tbl(id INTEGER PRIMARY KEY, lon REAL, lat REAL);", nullptr, nullptr, nullptr);

この関数は毎回内部でクエリの変換が行われます。そのため、繰り返し何度も実行するようなクエリではprepared_statementを使う方がパフォーマンスが向上します。

詳細は以下のドキュメントを参照してください。

prepared_statementを使う

prepared_statementを生成してクエリを実行します。値はsqlite3_bind_xxxx()を使ってbind出来ます（IN等の可変個となるものは不可）。


// 乱数生成
std::random_device rd;
std::mt19937_64 mt(rd());
std::uniform_real_distribution<double> random_lon(0,359.999);
std::uniform_real_distribution<double> random_lat(-90.0, 90.0);
std::uniform_int_distribution<uint64_t> random_id(0, 100000000);

// INSERT
sqlite3_stmt* pStmt;
sqlite3_prepare_v2(db, "INSERT INTO nodes_tbl(id, lon, lat) VALUES(?, ?, ?);", -1, &pStmt, nullptr);

for(int64_t i=0; i<100000000; i++)
{
	sqlite3_bind_int64(pStmt, 1, i);
 	sqlite3_bind_double(pStmt, 2, random_lon(mt));
 	sqlite3_bind_double(pStmt, 3, random_lat(mt));

 	while(sqlite3_step(pStmt) == SQLITE_BUSY){}
 	sqlite3_reset(pStmt);
 	sqlite3_clear_bindings(pStmt);
}

sqlite3_finalize(pStmt);

// SELECT
sqlite3_prepare_v2(db, "SELECT id, lon, lat FROM nodes_tbl WHERE id=?;", -1, &pStmt, nullptr);

for(uint64_t i=0; i<100000; i++)
{
	sqlite3_bind_int64(pStmt, 1, random_id(mt));

	while(sqlite3_step(pStmt) == SQLITE_ROW)
{
	int64_t id = sqlite3_column_int64(pStmt, 0);
	double lon = sqlite3_column_double(pStmt, 1);
	double lat = sqlite3_column_double(pStmt, 2);
	
	/* some process */
}
	sqlite3_reset(pStmt);
	sqlite3_clear_bindings(pStmt);
}

sqlite3_finalize(pStmt);

prepared_statementは1回実行するたびに初期化する必要があります。sqlite3_reset()はprepared_statementを実行可能な状態にリセットしますが、bindしたデータはそのまま保持されます。sqlite3_clear_bindings()を実行するとbindしたデータがクリアされます。また、sqlite3_bind_xxxx()は他と違ってインデックスが1から始まるので注意。

詳細は以下のドキュメントを参照してください。

blobの扱い

SQLiteのblob型はバイナリデータをそのまま格納出来ます。操作方法も他とほとんど同じですが、sqlite3_bind_blob()の第5引数に注意が必要です。第5引数はbindするデータが不変(いわゆるconstな変数)なのか一時変数なのなのかを指定します。設定可能な値は以下のとおりです。

  • SQLITE_STATIC
  • SQLITE_TRANSIENT

SQLITE_TRANSIENTを指定すると一時変数として判断され、変数内容がすぐにコピーされます。それに対してSQLITE_STATICを指定するとコピーされず、sqliteがデータベースに書き出す際に変数を直接参照します。もし変数がスコープを外れるなどして破棄されると正常に書き込めません。大抵は一時変数だと思いますので、SQLITE_TRANSIENTを指定するのが無難です。


// insert
sqlite3_bind_int(pStmtInsertData, 1, id);
sqlite3_bind_blob(pStmtInsertData, 2, pData, dataSize, SQLITE_TRANSIENT);

while(sqlite3_step(m_pStmtInsertData) == SQLITE_BUSY);


// select
sqlite3_stmt *pStmt;
sqlite3_prepare_v2(db, querySelectData, -1, &pStmt, nullptr);
sqlite3_bind_int(pStmt, 1, m_FileID);

while(sqlite3_step(pStmt) == SQLITE_ROW){
int size = sqlite3_column_bytes(pStmt, 0);
const char* buf = static_cast<const char*>(sqlite3_column_blob(pStmt, 0));
}

詳細は以下のドキュメントを参照してください。

エラーハンドリング

SQLiteのエラー処理についてです。エラーは関数の戻り値で判定出来ます。また、以下の関数を呼ぶと直近のエラーに関するエラーコード及びエラーメッセージが取得出来ます。

  • int sqlite3_errorcode(sqlite3 *db)
  • const char *sqlite3_errmsg(sqlite3 *db)

int rtn = sqlite3_open("test_bench.db", &amp;amp;db);
if(rtn != SQLITE_OK){
cerr << “[ERR] Failed to open db:” << sqlite3_errmsg(db) << endl;
}

また、関数によっては引数にエラーメッセージを返すものがあります。


char *errmsg = nullptr;
int ret = sqlite3_exec(db, “SELECT * FROM nodes_tbl”, nullptr, nullptr, &errmsg)
if(errmsg != nullptr)
{
cerr << “[ERR]:” << errmsg << endl;
sqlite3_free(errmsg);
}

sqlite3_errmsg()で取得した内容はsqliteモジュール内部で管理されているので、何もする必要はありません。逆に、sqlite3_exec()など引数にセットされたエラーメッセージは不要になった段階でsqlite3_free()で開放する必要があります。

詳細は以下のドキュメントを参照してください。

sqliteの動作設定について

マルチスレッド関連の動作モード

概要

sqliteにはマルチスレッドに関連する動作モードが3つあります。

  • Single-threadモード
    シングルスレッド用。全ての排他制御が無効となる。
  • Multi-threadモード
    マルチスレッド用。
    同じDBに対して複数のconnectionを作って操作する場合に使用する。connectionとprepared_statementの排他制御が無効となるので、同じコネクションを別スレッドで使いまわすSigleton的な使い方は不可（同時アクセスすると壊れる可能性がある）。
  • Serializedモード
    マルチスレッド用。全ての排他制御が有効となる。connection等を複数のスレッドで使いまわすような使い方でも動作が保証される。

C/C++での使い方

C/C++ではsqlite3_config()で動作モードを設定します。設定しなかった場合のデフォルト値はsqliteのコンパイルオプションに依存します。

  • SQLITE_CONFIG_SINGLETHREAD
  • SQLITE_CONFIG_MULTITHREAD
  • SQLITE_CONFIG_SERIALIZED

// set Serialized mode
sqlite3_config(SQLITE_CONFIG_SERIALIZED);

データベース毎に動作モードを設定したいときはsqlite3_open_v2()の第三引数にフラグを指定します。

  • Multi-threadモード:SQLITE_OPEN_NOMUTEX
  • Serializedモード:SQLITE_OPEN_FULLMUTEX

sqlite3 *db;

int rtn = sqlite3_open_v2(
“./test.db”, 
&db, 
SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX, 
nullptr);

詳細は以下を参照してください。

各動作モードのパフォーマンス

各動作モードでどれぐらいパフォーマンスが変化するのかベンチマークを取ってみました。内容は単純でINSERTやSELECTを規定回数繰り返すプログラムを走らせ、完了までの時間をtimeで測定しています。結果は以下のようになりました。

  • real:プログラムの呼び出しから終了までにかかった実時間
  • user:プログラム自体の処理時間
  • sys:kernelの処理時間
  • others:I/O待ちなどの時間

INSERTでは予想通り排他制御を全く行わないSingle-threadモードが一番速いようです。SELECTだけだと排他制御は関係ないので変わりありません（SELECTは処理時間のほとんどがI/O待ちで埋まってますね）。

Memory-Mapped I/O

概要

sqliteはデフォルトではread()/write()を使ってデータベース（ファイル）にアクセスします。より高速にアクセスしたい場合はmmapを使用するように設定出来ます。ただし、必ずしも速くなるわけではないこと、何らかの原因でメモリ上にマップされたデータが破壊されるとデータベース本体（ファイル）も破損する可能性があることに注意が必要です。

C/C++での使い方

sqlite3_config()でSQLITE_CONFIG_MMAP_SIZEを設定するとmmapアクセスが有効になります。第1引数はmmapバッファのデフォルトサイズ、第2引数はmmapバッファの上限値です。0を指定するとmmap無効（デフォルト）、-1などの負数を指定するとsqlite3のデフォルト値（コンパイルオプションに依存）が設定されます。


int64_t default_mmap_size = 256 * 1024 * 1024;
int64_t max_mmap_size = 512 * 1024 * 1024;

sqlite3_config(SQLITE_CONFIG_MMAP_SIZE, default_mmap_size, max_mmap_size);

コネクションごとに設定したい場合は、PRAGMA mmap_sizeを使用します。設定値はmmapバッファのデフォルトサイズを指定します。


// enable mmap
sqlite3_exec(db, “PRAGMA mmap_size=268435456”, null);

// disable mmap
sqlite3_exec(db, “PRAGMA mmap_size=0”, null);

詳細は以下のドキュメントを参照してください。

mmapのパフォーマンス

mmapを無効/有効にしてベンチマーク測定を行い結果を比較してみました。条件は以下のとおりです。

    • 動作モード:Serializedモード
    • レコード数100,000,000件(サイズ:2.5GByte)のデータベースからランダムに100,000件をSELECTする
    • MMAPバッファサイズ:次の4通りで試行
      • 0MByte(mmap無効)
      • 256MByte
      • 512MByte
      • 1024MByte

結果は以下のようになりました。ベンチマークに使ったプログラム/クエリはIDをランダムに生成して1件毎にselectしているせいか処理時間のほとんどがI/O待ちです。mmapを有効にしてバッファサイズを増やすとI/O待ち時間が改善しているのが分かりますね。

 

 

cmakeでライブラリにsqliteを追加する方法

以下の行をCMakeLists.txtに追記するだけです。

  • target_link_libraries(xxxx sqlite3)

 

参考

この記事は以下の内容を参考にさせていただきました。

More from my site

2 thoughts on “SQLite覚え書き”

  1. neo nabezoco

    SQLite を始めましたが，中断してしまっています。マルチスレッドは皆目，チンプンカンプンです。いろいろと教えてください。

    返信 ↓
    1. suke 投稿作成者

      こんにちは。

      私も同じような感じなのです（汗）。とあるプログラムを改造するのに必要になって触りだしたところです。
      理解が進んだらこの記事を充実させていきたいですね。

      返信 ↓

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。