読者です 読者をやめる 読者になる 読者になる

SQLite3のサンプル

SQLite3のC言語APIとsqlite3コマンドをCentOS6.4で動作検証してみたときの備忘録。

環境

[kohchi@localhost sqlite]$ cat /etc/redhat-release 
CentOS release 6.4 (Final)
[kohchi@localhost sqlite]$ cat /proc/version 
Linux version 2.6.32-358.6.2.el6.x86_64 (mockbuild@c6b8.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-3) (GCC) ) #1 SMP Thu May 16 20:59:36 UTC 2013
[kohchi@localhost sqlite]$ rpm -qa | grep -i sqlite
qt-sqlite-4.6.2-26.el6_4.x86_64
sqlite-3.6.20-1.el6.x86_64
perl-DBD-SQLite-1.27-3.el6.x86_64
sqlite-devel-3.6.20-1.el6.x86_64

SQLiteはすでに入っていた。

テーブルの作成

[kohchi@localhost sqlite]$ cat create-table.sql
create table test0(type integer, name text, v0 integer, v1 integer);
[kohchi@localhost sqlite]$ sqlite3 test0.db < create-table.sql 
[kohchi@localhost sqlite]$ ls -alF
合計 16
drwxrwxr-x   2 kohchi kohchi 4096  124 22:32 2014 ./
drwx------. 37 kohchi kohchi 4096  124 22:23 2014 ../
-rw-rw-r--   1 kohchi kohchi   69  124 22:31 2014 create-table.sql
-rw-r--r--   1 kohchi kohchi 2048  124 22:32 2014 test0.db
[kohchi@localhost sqlite]$ sqlite3 test0.db
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from test0;
sqlite> insert into test0 values(0, 'テスト', 8000040000, 8000040000);
sqlite> insert into test0 values(1, 'テストG', 8000140001, 8000140001);
sqlite> select * from test0;
0|テスト|8000040000|8000040000
1|テストG|8000140001|8000140001
sqlite> .header on
sqlite> .mode column
sqlite> select * from test0;
type        name        v0          v1        
----------  ----------  ----------  ----------
0           テスト   8000040000  8000040000
1           テストG  8000140001  8000140001
sqlite> pragma encoding;
encoding  
----------
UTF-8     
sqlite> .quit
[kohchi@localhost sqlite]$ 

テーブルダンプ

.dumpメタコマンドでダンプできる。sqlite3コマンドの引数にコマンドやSQLを指定できるので以下のようにできるのでダンプしやすいかも。

[kohchi@localhost sqlite]$ sqlite3 test0.db .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test0(type integer, name text, v0 integer, v1 integer);
INSERT INTO "test0" VALUES(0,'テスト',8000040000,8000040000);
INSERT INTO "test0" VALUES(1,'テストG',8000140001,8000140001);
COMMIT;
[kohchi@localhost sqlite]$ sqlite3 -header -column test0.db "select * from test0"
type        name        v0          v1        
----------  ----------  ----------  ----------
0           テスト   8000040000  8000040000
1           テストG  8000140001  8000140001
[kohchi@localhost sqlite]$

C言語サンプル

以下がsqliteのC APIサンプル。留意点は以下のとおり。

  • トランザクションはsqlite3_exec()を使う。
  • where句で文字列評価する際、prepare statementの?の前後にシングルクォーテーションはいらない(あるとwhere句が成立しないみたい)。
  • where句のないselectの場合は最初だけsqlite3_reset()をしてsqlite3_stepを繰り返す。sqlite3_reset()を繰り返すと最初の選択行だけ取得し続ける(当たり前だが)。
  • sqlite3_column_int()はintを返すが、sqlite3_column_int64はsqlite3_int64を返す。CentOS6.4では/usr/include/sqlite3.hをみるとlong long intっぽい。
[kohchi@localhost sqlite]$ cat sample.c
/**
 * SQLite3のC言語サンプル
 */
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlite3.h>

int main(int argc, char **argv) {
	int i, ret = 0, isCommit;
	long long v0, v1;
	const unsigned char *name;
	char tmpName[1024], *dbname;
	sqlite3 *db = NULL;
	sqlite3_stmt *insertStmt = NULL;
	sqlite3_stmt *selectStmt = NULL;
	sqlite3_stmt *selectAllStmt = NULL;
	char *insertSQL = "INSERT INTO test0(name, v0, v1) VALUES(?, ?, ?)";
	char *selectSQL = "SELECT name,v0,v1 FROM test0 where name = ?";
	char *selectAllSQL = "SELECT name,v0,v1 FROM test0";

	if (argc != 3) {
		fprintf(stderr, "usage: %s dbname {0|1}\n", *argv);
		return 1;
	}
	dbname = *++argv;
	isCommit = atoi(*++argv);

	ret = sqlite3_open(dbname, &db);
	if (ret != SQLITE_OK) {
		fprintf(stderr, "%s open error: %s\n",
				dbname, sqlite3_errmsg(db));
		return 1;
	}
	sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);

	ret = sqlite3_prepare_v2(db, insertSQL, -1, &insertStmt, NULL);
	if (ret != SQLITE_OK) {
		fprintf(stderr, "prepare error: %s\n", sqlite3_errmsg(db));
		isCommit = 0;
		goto err;
	}

	ret = sqlite3_prepare_v2(db, selectSQL, -1, &selectStmt, NULL);
	if (ret != SQLITE_OK) {
		fprintf(stderr, "prepare error: %s\n", sqlite3_errmsg(db));
		isCommit = 0;
		goto err;
	}

	ret = sqlite3_prepare_v2(db, selectAllSQL, -1, &selectAllStmt, NULL);
	if (ret != SQLITE_OK) {
		fprintf(stderr, "prepare error: %s\n", sqlite3_errmsg(db));
		isCommit = 0;
		goto err;
	}

	/* insert */
	for (i = 0; i < 2; i++) {
		sqlite3_reset(insertStmt);
		memset(tmpName, '\0', sizeof(tmpName));
		sprintf(tmpName, "tt%02d", i);
		sqlite3_bind_text(insertStmt, 1, tmpName, -1, SQLITE_STATIC);
		sqlite3_bind_int64(insertStmt, 2, 8000081000 + i);
		sqlite3_bind_int64(insertStmt, 3, 8000091000 + i);
		ret = sqlite3_step(insertStmt);
		if (ret != SQLITE_DONE) {
			fprintf(stderr, "insert error: %s\n",
					sqlite3_errmsg(db));
			isCommit = 0;
			goto err;
		}
	}

	/* select */
	for (i = 0; i < 2; i++) {
		sqlite3_reset(selectStmt);
		memset(tmpName, '\0', sizeof(tmpName));
		sprintf(tmpName, "tt%02d", i);
		sqlite3_bind_text(selectStmt, 1, tmpName, -1, SQLITE_STATIC);
		ret = sqlite3_step(selectStmt);
		if (ret == SQLITE_ROW) {
			name = sqlite3_column_text(selectStmt, 0);
			v0 = sqlite3_column_int64(selectStmt, 1);
			v1 = sqlite3_column_int64(selectStmt, 2);
			fprintf(stdout, "name = [%s] v0 = [%ld] v1 = [%ld]\n",
					name, v0, v1);
		}
	}

	/* select ALL */
	sqlite3_reset(selectAllStmt);
	do {
		ret = sqlite3_step(selectAllStmt);
		if (ret == SQLITE_ROW) {
			name = sqlite3_column_text(selectAllStmt, 0);
			v0 = sqlite3_column_int64(selectAllStmt, 1);
			v1 = sqlite3_column_int64(selectAllStmt, 2);
			fprintf(stdout, "select ALL: name = [%s] v0 = [%ld] v1 = [%ld]\n",
					name, v0, v1);
		}
	} while (ret == SQLITE_ROW);

	if (ret != SQLITE_DONE) {
		fprintf(stderr, "select error: %s\n", sqlite3_errmsg(db));
		isCommit = 0;
		goto err;
	}

	ret = 0;
err:
	sqlite3_finalize(insertStmt);
	sqlite3_finalize(selectStmt);
	sqlite3_finalize(selectAllStmt);
	sqlite3_exec(db, isCommit ? "COMMIT" : "ROLLBACK", NULL, NULL, NULL);
	sqlite3_close(db);

	return ret;
}
[kohchi@localhost sqlite]$ cc -g -o sample sample.c -lsqlite3
[kohchi@localhost sqlite]$

コミットしない場合のテスト

[kohchi@localhost sqlite]$ ./sample test0.db 0
name = [tt00] v0 = [8000081000] v1 = [8000091000]
name = [tt01] v0 = [8000081001] v1 = [8000091001]
select ALL: name = [テスト] v0 = [8000040000] v1 = [8000040000]
select ALL: name = [テストG] v0 = [8000140001] v1 = [8000140001]
select ALL: name = [tt00] v0 = [8000081000] v1 = [8000091000]
select ALL: name = [tt01] v0 = [8000081001] v1 = [8000091001]
[kohchi@localhost sqlite]$ sqlite3 test0.db
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .header on
sqlite> .mode column
sqlite> select * from test0;
type        name        v0          v1        
----------  ----------  ----------  ----------
0           テスト   8000040000  8000040000
1           テストG  8000140001  8000140001
sqlite> .quit
[kohchi@localhost sqlite]$

コミットした場合のテスト

[kohchi@localhost sqlite]$ ./sample test0.db 1
name = [tt00] v0 = [8000081000] v1 = [8000091000]
name = [tt01] v0 = [8000081001] v1 = [8000091001]
select ALL: name = [テスト] v0 = [8000040000] v1 = [8000040000]
select ALL: name = [テストG] v0 = [8000140001] v1 = [8000140001]
select ALL: name = [tt00] v0 = [8000081000] v1 = [8000091000]
select ALL: name = [tt01] v0 = [8000081001] v1 = [8000091001]
[kohchi@localhost sqlite]$ sqlite3 test0.db
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .header on
sqlite> .mode column
sqlite> select * from test0;
type        name        v0          v1        
----------  ----------  ----------  ----------
0           テスト   8000040000  8000040000
1           テストG  8000140001  8000140001
            tt00        8000081000  8000091000
            tt01        8000081001  8000091001
sqlite> delete from test0 where name like 'tt0%';
sqlite> select * from test0;
type        name        v0          v1        
----------  ----------  ----------  ----------
0           テスト   8000040000  8000040000
1           テストG  8000140001  8000140001
sqlite> .schema
CREATE TABLE test0(type integer, name text, v0 integer, v1 integer);
sqlite> .tables
test0
sqlite> .quit
[kohchi@localhost sqlite]$ 

参照したサイトは以下。

http://idocsq.net/page/505
http://br.0r2.info/2010/06/sqliteprepared-statementblobc1-sqlite.html
http://d.hatena.ne.jp/Crest/20120507/1336404257