きままにブログ

プログラミングを主とした私のメモ帳です。寂しいのでコメントください笑

SQLite3のテストだよ!

C++ SQLite3

SQLite3

数あるSQLアプリケーションの中でもダントツで早く! 簡単に! 使えるものです。まずはじめにC++(というかC言語)で利用するために適当なデーターベースを作っておきます。test.dbとしてファイルを保存しました。なんでもいいです。

続いてテーブルを作ります。SQLiteについてはSQLite入門を参考にしました。

create table mytable(ID integer, NAME text)

でテーブルを作ります。

ところでSQLiteは文を実行するごとに毎回トランザクションを実行します。これだと動作が遅くなるので、手動でトランザクションを実行させ、他のアプリケーションからのデーターベース操作をブロックします。こちらはBEGINコマンドでトランザクションを開始し、すべての操作が終わったらCOMMITコマンドで更新します。(適当)

API覚書

さて、SQLについては実はよく知らないんですけど() とりあえずSQLite3をコンパイルしてリンクして使ってみました。まずはじめに今回使うAPIを利用順に提示していきます。

sqlite3_open

int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);

こいつはfilenameを指定してデーターベースを開くあるいは作成する関数です。だいたいまずはじめに実行します。sqlite3*型へのポインタを渡して書き換えてインスタンスを生成します。まあエラーコードを返すのに戻り値を消費してしまったから引数で書き換えようとしたんですね; C++なら参照で書きたかったかな。std::tupleとかでもいいですね。(余談)

sqlite3_exec

The sqlite3_exec() interface is a convenience wrapper around sqlite3_prepare_v2(), sqlite3_step(), and sqlite3_finalize(), that allows an application to run multiple statements of SQL without having to use a lot of C code.

こいつはSQL文(文字列)を渡してSQLを実際に実行する関数です。この際、sqlite3_openで得られたsqlite3*型のインスタンスを渡す必要があります。その他の引数は次のようになっています。この関数は後に紹介する詳細な関数をすべてセットにしたものです。単にコマンドを実行するだけならこちらを用いると便利です。

int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);

callbackはコールバック関数で、それに渡す引数が次のvoid*型です。errmsgはエラーメッセージ出力用ですね。callback, parameter, errmsgは省略可能です。その際はNULLを渡します。(nullptrとは書いてなかったです)

If an sqlite3_exec() callback returns non-zero, the sqlite3_exec() routine returns SQLITE_ABORT without invoking the callback again and without running any subsequent SQL statements.

The 2nd argument to the sqlite3_exec() callback function is the number of columns in the result. The 3rd argument to the sqlite3_exec() callback is an array of pointers to strings obtained as if from sqlite3_column_text(), one for each column. If an element of a result row is NULL then the corresponding string pointer for the sqlite3_exec() callback is a NULL pointer. The 4th argument to the sqlite3_exec() callback is an array of pointers to strings where each entry represents the name of corresponding result column as obtained from sqlite3_column_name().

コールバック関数の戻り値はint型ですが、0でない数を返すとsqlite3_exec関数は失敗し、SQLITE_ABORTを返します。つまり常に0を返しておけばよいです。コールバック関数の第1引数は先程のパラメータであり、第2引数が列の数、第3引数はsqlite3_column_textで得られるデータ列、第4引数はsqlite3_column_nameで得られるID列となっています。テキストデータでないもの、例えば整数等は文字列に変換されてデータ列に格納されています。

sqlite3_prepare_v2

sqlite3_execでもSQLは実行できますが、より詳細に実行する場合はこの関数でSQLをバイトコードにコンパイルし、後々処理していきます。v2はversion2のことでversion1もあるのですが新しいプログラムではこちらが推奨されています。

int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);

dbおよびzSqlは先程と同様です。nByteはSQL文(文字列)の最大サイズを指定しますが、負の数を指定することで適当に処理してくれます。

ppStmtはsqlite3_stmt*型へのポインタを指定します。sqlite3_stmt*を宣言し、こいつへのポインタを渡せばOKです。pzTailは文字列zSqlの途中のポインタを指定することができます。すると、そのポインタ以降から実行されます。最初から実行する場合はNULLを指定することができます。

sqlite3_bind_int

SQL文の中に文字列を入れたい場合、sprintfなどで文字列をいじらなくても簡単に設定する機能があります。こちらがこの関数でint型を埋め込むことができます。sqlite3_prepare_v2関数のzSql引数に指定したSQL文のなかにプレイスホルダー「?」を入れることでこれが置き換わります!

int sqlite3_bind_int(sqlite3_stmt*, int, int);

The second argument is the index of the SQL parameter to be set. The leftmost SQL parameter has an index of 1.

第2引数はprepareで作ったstmtに対して、プレースホルダーの番号を1オリジンで指定します。後々取得は0オリジンなので注意が必要です。

The third argument is the value to bind to the parameter.

そして第3引数に数値を入れます。

sqlite3_bind_text

今度はテキストデータをバインドします。

int sqlite3_bind_text(sqlite3_stmt*,int,const char*,int,void(*)(void*));

第3引数以降が変わっています。第3引数はテキストのパラメータです。

If the fourth parameter to sqlite3_bind_text() or sqlite3_bind_text16() is negative, then the length of the string is the number of bytes up to the first zero terminator.

第4引数は文字列の扱いの時と同様に負の数を指定すれば自動的に処理してくれます。

The fifth argument to the BLOB and string binding interfaces is a destructor used to dispose of the BLOB or string after SQLite has finished with it. (中略) If the fifth argument has the value SQLITE_TRANSIENT, then SQLite makes its own private copy of the data immediately, before the sqlite3_bind_*() routine returns.

第5引数は文字列等のリソースの破棄に関する関数ポインタを指定します。今回はSQLITE_TRANSIENTをセットし、SQLが内部でコピーを取り適切に処理させます。

sqlite3_step

After a prepared statement has been prepared using either sqlite3_prepare_v2() or sqlite3_prepare16_v2() or one of the legacy interfaces sqlite3_prepare() or sqlite3_prepare16(), this function must be called one or more times to evaluate the statement.

stmtを実際に実行します。

int sqlite3_step(sqlite3_stmt*);

The details of the behavior of the sqlite3_step() interface depend on whether the statement was prepared using the newer "v2" interface sqlite3_prepare_v2() and sqlite3_prepare16_v2() or the older legacy interface sqlite3_prepare() and sqlite3_prepare16(). The use of the new "v2" interface is recommended for new applications but the legacy interface will continue to be supported.

今回はv2のprepare関数を使っていますが、prepare関数によって挙動が異なるようです。

In the legacy interface, the return value will be either SQLITE_BUSY, SQLITE_DONE, SQLITE_ROW, SQLITE_ERROR, or SQLITE_MISUSE. With the "v2" interface, any of the other result codes or extended result codes might be returned as well.

具体的には戻り値が格段に増えています。

SQLITE_BUSY means that the database engine was unable to acquire the database locks it needs to do its job. If the statement is a COMMIT or occurs outside of an explicit transaction, then you can retry the statement. If the statement is not a COMMIT and occurs within an explicit transaction then you should rollback the transaction before continuing.

SQLITE_BUSYはロックされていたりして実行できない場合に返されます。もしもstmtがCOMMITや自明なトランザクション外で起こった場合、再実行します。もしもそれ以外であった場合はトランザクションを開始する前までロールバックしなければなりません。

SQLITE_DONE means that the statement has finished executing successfully. sqlite3_step() should not be called again on this virtual machine without first calling sqlite3_reset() to reset the virtual machine back to its initial state.

SQLITE_DONEは完了した場合に返されます。これが返った後に再びstep関数を実行してはいけません。もしも実行する場合はreset関数を実行して初期状態に戻ってから実行します。

If the SQL statement being executed returns any data, then SQLITE_ROW is returned each time a new row of data is ready for processing by the caller. The values may be accessed using the column access functions. sqlite3_step() is called again to retrieve the next row of data.

複数の値を返してくる場合、SQLITE_ROWが返ります。これが実行されると行のデータが準備され、行を取得する関数によってデータにアクセスできます。次の行にアクセスするには再びsqlite3_step関数を実行します。

For all versions of SQLite up to and including 3.6.23.1, a call to sqlite3_reset() was required after sqlite3_step() returned anything other than SQLITE_ROW before any subsequent invocation of sqlite3_step().

もしも戻り値がSQLITE_ROWだった場合、次のstep関数を実行する前に次に述べるreset関数を実行しなければなりません。

sqlite3_column_int

もしもstep関数がSQLITE_ROWを返した場合は行の取得となりますのでそれぞれの列から値を取り出してくる必要があります。そのひとつがこの関数でint型として取得できます。

int sqlite3_column_int(sqlite3_stmt*, int iCol);

stmt, 及び列番号iColを指定することで値を取得できます。ただし、列番号は0オリジンであることに注意が必要です。

sqlite3_column_text

const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);

テキストの取得も列番号を指定するだけです。

sqlite3_reset

The sqlite3_reset() function is called to reset a prepared statement object back to its initial state, ready to be re-executed. Any SQL statement variables that had values bound to them using the sqlite3_bind_*() API retain their values. Use sqlite3_clear_bindings() to reset the bindings.

この関数はstmtオブジェクトをリセットし再び実行できる状態に戻します。ただし、sqlite3_bind_*系のAPIはそれぞれ内部データを別で保持しているのでこれらをクリアするにはsqlite3_clear_bindings関数を使わなければなりません。

sqlite3_finalize

The sqlite3_finalize() function is called to delete a prepared statement. If the most recent evaluation of the statement encountered no errors or if the statement is never been evaluated, then sqlite3_finalize() returns SQLITE_OK. If the most recent evaluation of statement S failed, then sqlite3_finalize(S) returns the appropriate error code or extended error code.

stmtを初期化します。直前のSQLが正常に実行されればSQLITE_OKを返し、そうでなければエラーコードを返します。

The application must finalize every prepared statement in order to avoid resource leaks. It is a grievous error for the application to try to use a prepared statement after it has been finalized. Any use of a prepared statement after it has been finalized can result in undefined and undesirable behavior such as segfaults and heap corruption.

リソースリークに繋がるおそれがあるのでSQLite3を利用する際は必ず毎回prepared関数で生成したstmtに対してfinalize関数を実行します。

sqlite3_close

The sqlite3_close() and sqlite3_close_v2() routines are destructors for the sqlite3 object. Calls to sqlite3_close() and sqlite3_close_v2() return SQLITE_OK if the sqlite3 object is successfully destroyed and all associated resources are deallocated.

最後にデータベースを閉じます。関連するリソースは全て解放されます。

実行例

int main() {
	sqlite3* db;

	printf("開く:\n");
	sqlite3_open("test.db", &db);
	
	// 一覧の取得
	{
		sqlite3_exec(db, "select ID, NAME from mytable;", 
					 [](void* p, int n, char** text_list, char** name_list) -> int {
			for (int i = 0; i < n; ++i) {
				printf("%s : %s\t", name_list[i], text_list[i]);
			}
			printf("\n");
			return 0;
		}, nullptr, nullptr);
	}

	printf("トランザクション開始: \n");
	{
		int ret = sqlite3_exec(db, "begin;", nullptr, nullptr, nullptr);
	}

	printf("追加:\n");
	{
		sqlite3_stmt* stmt;
		{
			char str[] = "insert into mytable values(?, ?);";
			int ret = sqlite3_prepare_v2(db, str, -1, &stmt, nullptr);
		}

		int id;
		char name[32];
		printf("(ID,NAME)を入力してね: ");
		scanf_s("%d,%s", &id, name, 32);

		sqlite3_bind_int(stmt, 1, id);
		sqlite3_bind_text(stmt, 2, name, -1, SQLITE_TRANSIENT);

		while (true) {
			int ret = sqlite3_step(stmt);

			if (ret == SQLITE_BUSY) {
				continue;
			}
			else if (ret == SQLITE_DONE) {
				break;
			}
		}

		sqlite3_finalize(stmt);
	}

	// コミット
	printf("コミット: \n");
	{
		int ret = sqlite3_exec(db, "commit;", nullptr, nullptr, nullptr);
	}
	
	printf("検索:\n");
	{
		sqlite3_stmt* stmt;
		{
			char str[] = "select ID, NAME from mytable;";
			sqlite3_prepare_v2(db, str, strlen(str), &stmt, nullptr);
		}

		while (true) {
			int ret = sqlite3_step(stmt);

			if (ret == SQLITE_BUSY) {
				continue;
			}
			else if (ret == SQLITE_DONE) {
				break;
			}
			else if (ret == SQLITE_ROW) {
				int  id = sqlite3_column_int(stmt, 0);
				const unsigned char *name = sqlite3_column_text(stmt, 1);
				printf("ID=%d, NAME=%s\n", id, name);
			}
		}

		// stmt を開放
		sqlite3_finalize(stmt);
	}

	printf("終了:\n");
	sqlite3_close(db);

	getchar();
	getchar();
	return 0;
}