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 1月 24 22:32 2014 ./ drwx------. 37 kohchi kohchi 4096 1月 24 22:23 2014 ../ -rw-rw-r-- 1 kohchi kohchi 69 1月 24 22:31 2014 create-table.sql -rw-r--r-- 1 kohchi kohchi 2048 1月 24 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
