· 6 years ago · Sep 02, 2019, 10:34 AM
1#include "sqlite3.h"
2
3#include <iostream>
4#include <memory>
5#include <string>
6
7struct SQLiteError {
8 int errcode;
9 std::string errmsg;
10 std::string sqlite3_errmsg;
11};
12
13std::string SQLiteErrCodeToMsg(int errCode) {
14 std::string msg{""};
15
16 switch (errCode){
17 case SQLITE_OK: msg = "Successful result"; break;
18 case SQLITE_ERROR: msg = "Generic error"; break;
19 case SQLITE_INTERNAL: msg = "Internal logic error in SQLite"; break;
20 case SQLITE_PERM: msg = "Access permission denied"; break;
21 case SQLITE_ABORT: msg = "Callback routine requested an abort"; break;
22 case SQLITE_BUSY: msg = "The database file is locked"; break;
23 case SQLITE_LOCKED: msg = "A table in the database is locked"; break;
24 case SQLITE_NOMEM: msg = "A malloc() failed"; break;
25 case SQLITE_READONLY: msg = "Attempt to write a readonly database"; break;
26 case SQLITE_INTERRUPT: msg = "Operation terminated by sqlite3_interrupt()"; break;
27 case SQLITE_IOERR: msg = "Some kind of disk I/O error occurred"; break;
28 case SQLITE_CORRUPT: msg = "The database disk image is malformed"; break;
29 case SQLITE_NOTFOUND: msg = "Unknown opcode in sqlite3_file_control()"; break;
30 case SQLITE_FULL: msg = "Insertion failed because database is full"; break;
31 case SQLITE_CANTOPEN: msg = "Unable to open the database file"; break;
32 case SQLITE_PROTOCOL: msg = "Database lock protocol error"; break;
33 case SQLITE_EMPTY: msg = "Internal use only"; break;
34 case SQLITE_SCHEMA: msg = "The database schema changed"; break;
35 case SQLITE_TOOBIG: msg = "String or BLOB exceeds size limit"; break;
36 case SQLITE_CONSTRAINT: msg = "Abort due to constraint violation"; break;
37 case SQLITE_MISMATCH: msg = "Data type mismatch"; break;
38 case SQLITE_MISUSE: msg = "Library used incorrectly"; break;
39 case SQLITE_NOLFS: msg = "Uses OS features not supported on host"; break;
40 case SQLITE_AUTH: msg = "Authorization denied"; break;
41 case SQLITE_FORMAT: msg = "Not used"; break;
42 case SQLITE_RANGE: msg = "2nd parameter to sqlite3_bind out of range"; break;
43 case SQLITE_NOTADB: msg = "File opened that is not a database file"; break;
44 case SQLITE_NOTICE: msg = "Notifications from sqlite3_log()"; break;
45 case SQLITE_WARNING: msg = "Warnings from sqlite3_log()"; break;
46 case SQLITE_ROW: msg = "sqlite3_step() has another row ready"; break;
47 case SQLITE_DONE: msg = "sqlite3_step() has finished executing"; break;
48 }
49
50 return msg;
51}
52
53void test01() {
54
55 sqlite3* sq{nullptr}; // sqlite3オブジェクト
56 int retCode;
57
58 // データベース接続(or生成)
59 //retCode = sqlite3_open("./aaa/test01.sqlite3", &sq); // エラーを起こす。存在しないフォルダを指定している
60 //retCode = sqlite3_open("", &sq); // 空白で一時ファイルができる。終了に削除される。
61 retCode = sqlite3_open(":memory:", &sq); // オンメモリで動作する
62 //retCode = sqlite3_open("file:hoge_data.db", &sq); // URI
63 //retCode = sqlite3_open_v2("file:///e:/test_datahoge_data.db", &sq, SQLITE_OPEN_URI | SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
64 if(retCode != SQLITE_OK){
65 // analyzing error-code.
66 SQLiteError sqErr{ retCode, SQLiteErrCodeToMsg(retCode), std::string(sqlite3_errmsg(sq)) };
67 throw sqErr;
68 }
69
70 // 処理いろいろ
71 std::cout << "proccesing." << std::endl;
72
73 // 接続解除
74 retCode = sqlite3_close(sq);
75 //retCode = sqlite3_close_v2(sq);
76 if(retCode != SQLITE_OK){
77 // analyzing error-code.
78 SQLiteError sqErr{ retCode, SQLiteErrCodeToMsg(retCode), std::string(sqlite3_errmsg(sq)) };
79 throw sqErr;
80 }
81}
82
83void test02() {
84 sqlite3* sq{ nullptr }; // sqlite3オブジェクト
85 int retCode;
86
87 // データベース接続
88 retCode = sqlite3_open("hogehoge.sqlite3", &sq);
89 if (retCode != SQLITE_OK) {
90 // analyzing error-code.
91 SQLiteError sqErr{
92 retCode,
93 SQLiteErrCodeToMsg(retCode),
94 std::string(sqlite3_errmsg(sq)) };
95 throw sqErr;
96 }
97
98 // オートコミット
99 sqlite3_get_autocommit(sq);
100
101 // ステートメント
102 sqlite3_stmt* stmt;
103
104 // テーブルを作成する
105 // 1. ステートメントを作る
106 retCode = sqlite3_prepare_v2(sq, "CREATE TABLE IF NOT EXISTS t_hoge (id INTEGER PRIMARY KEY AUTOINCREMENT, foo TEXT)", -1, &stmt, NULL);
107 if (retCode != SQLITE_OK) {
108 // analyzing error-code.
109 SQLiteError sqErr{
110 retCode,
111 SQLiteErrCodeToMsg(retCode),
112 std::string(sqlite3_errmsg(sq)) };
113 throw sqErr;
114 }
115
116 // 2. 実行する
117 bool runFlg = true;
118 while (runFlg) {
119 retCode = sqlite3_step(stmt);
120 if(retCode == SQLITE_DONE){
121 // 処理終了 抜ける
122 runFlg = false;
123 }
124 else {
125 // エラー？要テスト
126 }
127 }
128
129 // 書き込む
130 // 1. ステートメントを再利用 → リセットする
131 sqlite3_reset(stmt);
132 // 2. ステートメントを設定する
133 retCode = sqlite3_prepare_v2(sq, "INSERT INTO t_hoge (foo) VALUES (?)", -1, &stmt, NULL);
134 // 3. データをバインドする
135 std::string foo = u8"foobar";
136 retCode = sqlite3_bind_text(stmt, 1, foo.c_str(), foo.length(), SQLITE_STATIC);
137 // 4. 実行する
138 runFlg = true;
139 while (runFlg) {
140 retCode = sqlite3_step(stmt);
141 if (retCode == SQLITE_DONE) {
142 // 処理終了 抜ける
143 runFlg = false;
144 }
145 else {
146 // エラー？要テスト
147 std::cout << "insert error." << std::endl;
148 }
149 }
150
151 // 読み込む
152 // 1. ステートメントを再利用 → リセットする
153 sqlite3_reset(stmt);
154 // 2. ステートメントを設定する
155 retCode = sqlite3_prepare_v2(sq, "SELECT * FROM t_hoge", -1, &stmt, NULL);
156 // 3. 実行する
157 runFlg = true;
158 while (runFlg) {
159 retCode = sqlite3_step(stmt);
160 if(retCode == SQLITE_ROW){
161
162 int id = sqlite3_column_int(stmt, 0);
163 auto foo = sqlite3_column_text(stmt, 1);
164
165 std::cout << id << " : " << foo << std::endl;
166 }
167 else if (retCode == SQLITE_DONE) {
168 // 終了
169 runFlg = false;
170 }
171 else {
172 // error
173 std::cout << "error" << std::endl;
174 runFlg = false;
175 }
176 }
177
178 // ステートメントの解放
179 sqlite3_finalize(stmt);
180
181 // 接続解除
182 retCode = sqlite3_close_v2(sq); // prepare_v2()使ったらcloseもv2
183 if (retCode != SQLITE_OK) {
184 // analyzing error-code.
185 SQLiteError sqErr{ retCode, SQLiteErrCodeToMsg(retCode), std::string(sqlite3_errmsg(sq)) };
186 throw sqErr;
187 }
188
189}
190
191void testAPIs() {
192 std::cout << "testAPIs()." << std::endl;
193
194 try
195 {
196 // open, close
197 //test01();
198
199 // prepare
200 test02();
201 }
202 catch (const std::string& e) {
203 std::cout << e << std::endl;
204 }
205 catch (const SQLiteError &se)
206 {
207 std::cout << se.errcode << ": \t" << se.errmsg << "\n"
208 << "\t" << se.sqlite3_errmsg
209 << std::endl;
210 }
211}