|
0
|
1 #include "gtest/gtest.h"
|
|
|
2
|
|
|
3 #include "../Core/Toolbox.h"
|
|
|
4 #include "../Core/SQLite/Connection.h"
|
|
|
5 #include "../Core/SQLite/Statement.h"
|
|
|
6 #include "../Core/SQLite/Transaction.h"
|
|
|
7
|
|
|
8 #include <sqlite3.h>
|
|
|
9
|
|
63
|
10 using namespace Orthanc;
|
|
0
|
11
|
|
|
12
|
|
|
13
|
|
|
14 /********************************************************************
|
|
|
15 ** Tests from
|
|
|
16 ** http://src.chromium.org/viewvc/chrome/trunk/src/sql/connection_unittest.cc
|
|
|
17 ********************************************************************/
|
|
|
18
|
|
|
19 class SQLConnectionTest : public testing::Test
|
|
|
20 {
|
|
|
21 public:
|
|
|
22 SQLConnectionTest()
|
|
|
23 {
|
|
|
24 }
|
|
|
25
|
|
|
26 virtual ~SQLConnectionTest()
|
|
|
27 {
|
|
|
28 }
|
|
|
29
|
|
|
30 virtual void SetUp()
|
|
|
31 {
|
|
|
32 db_.OpenInMemory();
|
|
|
33 }
|
|
|
34
|
|
|
35 virtual void TearDown()
|
|
|
36 {
|
|
|
37 db_.Close();
|
|
|
38 }
|
|
|
39
|
|
|
40 SQLite::Connection& db()
|
|
|
41 {
|
|
|
42 return db_;
|
|
|
43 }
|
|
|
44
|
|
|
45 private:
|
|
|
46 SQLite::Connection db_;
|
|
|
47 };
|
|
|
48
|
|
|
49
|
|
|
50
|
|
|
51 TEST_F(SQLConnectionTest, Execute)
|
|
|
52 {
|
|
|
53 // Valid statement should return true.
|
|
|
54 ASSERT_TRUE(db().Execute("CREATE TABLE foo (a, b)"));
|
|
|
55 EXPECT_EQ(SQLITE_OK, db().GetErrorCode());
|
|
|
56
|
|
|
57 // Invalid statement should fail.
|
|
|
58 ASSERT_EQ(SQLITE_ERROR,
|
|
|
59 db().ExecuteAndReturnErrorCode("CREATE TAB foo (a, b"));
|
|
|
60 EXPECT_EQ(SQLITE_ERROR, db().GetErrorCode());
|
|
|
61 }
|
|
|
62
|
|
|
63 TEST_F(SQLConnectionTest, ExecuteWithErrorCode) {
|
|
|
64 ASSERT_EQ(SQLITE_OK,
|
|
|
65 db().ExecuteAndReturnErrorCode("CREATE TABLE foo (a, b)"));
|
|
|
66 ASSERT_EQ(SQLITE_ERROR,
|
|
|
67 db().ExecuteAndReturnErrorCode("CREATE TABLE TABLE"));
|
|
|
68 ASSERT_EQ(SQLITE_ERROR,
|
|
|
69 db().ExecuteAndReturnErrorCode(
|
|
|
70 "INSERT INTO foo(a, b) VALUES (1, 2, 3, 4)"));
|
|
|
71 }
|
|
|
72
|
|
|
73 TEST_F(SQLConnectionTest, CachedStatement) {
|
|
|
74 SQLite::StatementId id1("foo", 12);
|
|
|
75 ASSERT_TRUE(db().Execute("CREATE TABLE foo (a, b)"));
|
|
|
76 ASSERT_TRUE(db().Execute("INSERT INTO foo(a, b) VALUES (12, 13)"));
|
|
|
77
|
|
|
78 // Create a new cached statement.
|
|
|
79 {
|
|
|
80 SQLite::Statement s(db(), id1, "SELECT a FROM foo");
|
|
|
81 ASSERT_TRUE(s.Step());
|
|
|
82 EXPECT_EQ(12, s.ColumnInt(0));
|
|
|
83 }
|
|
|
84
|
|
|
85 // The statement should be cached still.
|
|
|
86 EXPECT_TRUE(db().HasCachedStatement(id1));
|
|
|
87
|
|
|
88 {
|
|
|
89 // Get the same statement using different SQL. This should ignore our
|
|
|
90 // SQL and use the cached one (so it will be valid).
|
|
|
91 SQLite::Statement s(db(), id1, "something invalid(");
|
|
|
92 ASSERT_TRUE(s.Step());
|
|
|
93 EXPECT_EQ(12, s.ColumnInt(0));
|
|
|
94 }
|
|
|
95
|
|
|
96 // Make sure other statements aren't marked as cached.
|
|
|
97 EXPECT_FALSE(db().HasCachedStatement(SQLITE_FROM_HERE));
|
|
|
98 }
|
|
|
99
|
|
|
100 TEST_F(SQLConnectionTest, IsSQLValidTest) {
|
|
|
101 ASSERT_TRUE(db().Execute("CREATE TABLE foo (a, b)"));
|
|
|
102 ASSERT_TRUE(db().IsSQLValid("SELECT a FROM foo"));
|
|
|
103 ASSERT_FALSE(db().IsSQLValid("SELECT no_exist FROM foo"));
|
|
|
104 }
|
|
|
105
|
|
|
106
|
|
|
107
|
|
|
108 TEST_F(SQLConnectionTest, DoesStuffExist) {
|
|
|
109 // Test DoesTableExist.
|
|
|
110 EXPECT_FALSE(db().DoesTableExist("foo"));
|
|
|
111 ASSERT_TRUE(db().Execute("CREATE TABLE foo (a, b)"));
|
|
|
112 EXPECT_TRUE(db().DoesTableExist("foo"));
|
|
|
113
|
|
|
114 // Should be case sensitive.
|
|
|
115 EXPECT_FALSE(db().DoesTableExist("FOO"));
|
|
|
116
|
|
|
117 // Test DoesColumnExist.
|
|
|
118 EXPECT_FALSE(db().DoesColumnExist("foo", "bar"));
|
|
|
119 EXPECT_TRUE(db().DoesColumnExist("foo", "a"));
|
|
|
120
|
|
|
121 // Testing for a column on a nonexistent table.
|
|
|
122 EXPECT_FALSE(db().DoesColumnExist("bar", "b"));
|
|
|
123 }
|
|
|
124
|
|
|
125 TEST_F(SQLConnectionTest, GetLastInsertRowId) {
|
|
|
126 ASSERT_TRUE(db().Execute("CREATE TABLE foo (id INTEGER PRIMARY KEY, value)"));
|
|
|
127
|
|
|
128 ASSERT_TRUE(db().Execute("INSERT INTO foo (value) VALUES (12)"));
|
|
|
129
|
|
|
130 // Last insert row ID should be valid.
|
|
|
131 int64_t row = db().GetLastInsertRowId();
|
|
|
132 EXPECT_LT(0, row);
|
|
|
133
|
|
|
134 // It should be the primary key of the row we just inserted.
|
|
|
135 SQLite::Statement s(db(), "SELECT value FROM foo WHERE id=?");
|
|
|
136 s.BindInt64(0, row);
|
|
|
137 ASSERT_TRUE(s.Step());
|
|
|
138 EXPECT_EQ(12, s.ColumnInt(0));
|
|
|
139 }
|
|
|
140
|
|
|
141 TEST_F(SQLConnectionTest, Rollback) {
|
|
|
142 ASSERT_TRUE(db().BeginTransaction());
|
|
|
143 ASSERT_TRUE(db().BeginTransaction());
|
|
|
144 EXPECT_EQ(2, db().GetTransactionNesting());
|
|
|
145 db().RollbackTransaction();
|
|
|
146 EXPECT_FALSE(db().CommitTransaction());
|
|
|
147 EXPECT_TRUE(db().BeginTransaction());
|
|
|
148 }
|
|
|
149
|
|
|
150
|
|
|
151
|
|
|
152
|
|
|
153 /********************************************************************
|
|
|
154 ** Tests from
|
|
|
155 ** http://src.chromium.org/viewvc/chrome/trunk/src/sql/statement_unittest.cc
|
|
|
156 ********************************************************************/
|
|
|
157
|
|
|
158 class SQLStatementTest : public SQLConnectionTest
|
|
|
159 {
|
|
|
160 };
|
|
|
161
|
|
|
162
|
|
|
163 TEST_F(SQLStatementTest, Run) {
|
|
|
164 ASSERT_TRUE(db().Execute("CREATE TABLE foo (a, b)"));
|
|
|
165 ASSERT_TRUE(db().Execute("INSERT INTO foo (a, b) VALUES (3, 12)"));
|
|
|
166
|
|
|
167 SQLite::Statement s(db(), "SELECT b FROM foo WHERE a=?");
|
|
|
168 // Stepping it won't work since we haven't bound the value.
|
|
|
169 EXPECT_FALSE(s.Step());
|
|
|
170
|
|
|
171 // Run should fail since this produces output, and we should use Step(). This
|
|
|
172 // gets a bit wonky since sqlite says this is OK so succeeded is set.
|
|
|
173 s.Reset(true);
|
|
|
174 s.BindInt(0, 3);
|
|
|
175 EXPECT_FALSE(s.Run());
|
|
|
176 EXPECT_EQ(SQLITE_ROW, db().GetErrorCode());
|
|
|
177
|
|
|
178 // Resetting it should put it back to the previous state (not runnable).
|
|
|
179 s.Reset(true);
|
|
|
180
|
|
|
181 // Binding and stepping should produce one row.
|
|
|
182 s.BindInt(0, 3);
|
|
|
183 EXPECT_TRUE(s.Step());
|
|
|
184 EXPECT_EQ(12, s.ColumnInt(0));
|
|
|
185 EXPECT_FALSE(s.Step());
|
|
|
186 }
|
|
|
187
|
|
|
188 TEST_F(SQLStatementTest, BasicErrorCallback) {
|
|
|
189 ASSERT_TRUE(db().Execute("CREATE TABLE foo (a INTEGER PRIMARY KEY, b)"));
|
|
|
190 // Insert in the foo table the primary key. It is an error to insert
|
|
|
191 // something other than an number. This error causes the error callback
|
|
|
192 // handler to be called with SQLITE_MISMATCH as error code.
|
|
|
193 SQLite::Statement s(db(), "INSERT INTO foo (a) VALUES (?)");
|
|
|
194 s.BindCString(0, "bad bad");
|
|
63
|
195 EXPECT_THROW(s.Run(), OrthancException);
|
|
0
|
196 }
|
|
|
197
|
|
|
198 TEST_F(SQLStatementTest, Reset) {
|
|
|
199 ASSERT_TRUE(db().Execute("CREATE TABLE foo (a, b)"));
|
|
|
200 ASSERT_TRUE(db().Execute("INSERT INTO foo (a, b) VALUES (3, 12)"));
|
|
|
201 ASSERT_TRUE(db().Execute("INSERT INTO foo (a, b) VALUES (4, 13)"));
|
|
|
202
|
|
|
203 SQLite::Statement s(db(), "SELECT b FROM foo WHERE a = ? ");
|
|
|
204 s.BindInt(0, 3);
|
|
|
205 ASSERT_TRUE(s.Step());
|
|
|
206 EXPECT_EQ(12, s.ColumnInt(0));
|
|
|
207 ASSERT_FALSE(s.Step());
|
|
|
208
|
|
|
209 s.Reset(false);
|
|
|
210 // Verify that we can get all rows again.
|
|
|
211 ASSERT_TRUE(s.Step());
|
|
|
212 EXPECT_EQ(12, s.ColumnInt(0));
|
|
|
213 EXPECT_FALSE(s.Step());
|
|
|
214
|
|
|
215 s.Reset(true);
|
|
|
216 ASSERT_FALSE(s.Step());
|
|
|
217 }
|
|
|
218
|
|
|
219
|
|
|
220
|
|
|
221
|
|
|
222
|
|
|
223
|
|
|
224
|
|
|
225 /********************************************************************
|
|
|
226 ** Tests from
|
|
|
227 ** http://src.chromium.org/viewvc/chrome/trunk/src/sql/transaction_unittest.cc
|
|
|
228 ********************************************************************/
|
|
|
229
|
|
|
230 class SQLTransactionTest : public SQLConnectionTest
|
|
|
231 {
|
|
|
232 public:
|
|
|
233 virtual void SetUp()
|
|
|
234 {
|
|
|
235 SQLConnectionTest::SetUp();
|
|
|
236 ASSERT_TRUE(db().Execute("CREATE TABLE foo (a, b)"));
|
|
|
237 }
|
|
|
238
|
|
|
239 // Returns the number of rows in table "foo".
|
|
|
240 int CountFoo()
|
|
|
241 {
|
|
|
242 SQLite::Statement count(db(), "SELECT count(*) FROM foo");
|
|
|
243 count.Step();
|
|
|
244 return count.ColumnInt(0);
|
|
|
245 }
|
|
|
246 };
|
|
|
247
|
|
|
248
|
|
|
249 TEST_F(SQLTransactionTest, Commit) {
|
|
|
250 {
|
|
|
251 SQLite::Transaction t(db());
|
|
|
252 EXPECT_FALSE(t.IsOpen());
|
|
|
253 t.Begin();
|
|
|
254 EXPECT_TRUE(t.IsOpen());
|
|
|
255
|
|
|
256 EXPECT_TRUE(db().Execute("INSERT INTO foo (a, b) VALUES (1, 2)"));
|
|
|
257
|
|
|
258 t.Commit();
|
|
|
259 EXPECT_FALSE(t.IsOpen());
|
|
|
260 }
|
|
|
261
|
|
|
262 EXPECT_EQ(1, CountFoo());
|
|
|
263 }
|
|
|
264
|
|
|
265 TEST_F(SQLTransactionTest, Rollback) {
|
|
|
266 // Test some basic initialization, and that rollback runs when you exit the
|
|
|
267 // scope.
|
|
|
268 {
|
|
|
269 SQLite::Transaction t(db());
|
|
|
270 EXPECT_FALSE(t.IsOpen());
|
|
|
271 t.Begin();
|
|
|
272 EXPECT_TRUE(t.IsOpen());
|
|
|
273
|
|
|
274 EXPECT_TRUE(db().Execute("INSERT INTO foo (a, b) VALUES (1, 2)"));
|
|
|
275 }
|
|
|
276
|
|
|
277 // Nothing should have been committed since it was implicitly rolled back.
|
|
|
278 EXPECT_EQ(0, CountFoo());
|
|
|
279
|
|
|
280 // Test explicit rollback.
|
|
|
281 SQLite::Transaction t2(db());
|
|
|
282 EXPECT_FALSE(t2.IsOpen());
|
|
|
283 t2.Begin();
|
|
|
284
|
|
|
285 EXPECT_TRUE(db().Execute("INSERT INTO foo (a, b) VALUES (1, 2)"));
|
|
|
286 t2.Rollback();
|
|
|
287 EXPECT_FALSE(t2.IsOpen());
|
|
|
288
|
|
|
289 // Nothing should have been committed since it was explicitly rolled back.
|
|
|
290 EXPECT_EQ(0, CountFoo());
|
|
|
291 }
|
|
|
292
|
|
|
293 // Rolling back any part of a transaction should roll back all of them.
|
|
|
294 TEST_F(SQLTransactionTest, NestedRollback) {
|
|
|
295 EXPECT_EQ(0, db().GetTransactionNesting());
|
|
|
296
|
|
|
297 // Outermost transaction.
|
|
|
298 {
|
|
|
299 SQLite::Transaction outer(db());
|
|
|
300 outer.Begin();
|
|
|
301 EXPECT_EQ(1, db().GetTransactionNesting());
|
|
|
302
|
|
|
303 // The first inner one gets committed.
|
|
|
304 {
|
|
|
305 SQLite::Transaction inner1(db());
|
|
|
306 inner1.Begin();
|
|
|
307 EXPECT_TRUE(db().Execute("INSERT INTO foo (a, b) VALUES (1, 2)"));
|
|
|
308 EXPECT_EQ(2, db().GetTransactionNesting());
|
|
|
309
|
|
|
310 inner1.Commit();
|
|
|
311 EXPECT_EQ(1, db().GetTransactionNesting());
|
|
|
312 }
|
|
|
313
|
|
|
314 // One row should have gotten inserted.
|
|
|
315 EXPECT_EQ(1, CountFoo());
|
|
|
316
|
|
|
317 // The second inner one gets rolled back.
|
|
|
318 {
|
|
|
319 SQLite::Transaction inner2(db());
|
|
|
320 inner2.Begin();
|
|
|
321 EXPECT_TRUE(db().Execute("INSERT INTO foo (a, b) VALUES (1, 2)"));
|
|
|
322 EXPECT_EQ(2, db().GetTransactionNesting());
|
|
|
323
|
|
|
324 inner2.Rollback();
|
|
|
325 EXPECT_EQ(1, db().GetTransactionNesting());
|
|
|
326 }
|
|
|
327
|
|
|
328 // A third inner one will fail in Begin since one has already been rolled
|
|
|
329 // back.
|
|
|
330 EXPECT_EQ(1, db().GetTransactionNesting());
|
|
|
331 {
|
|
|
332 SQLite::Transaction inner3(db());
|
|
63
|
333 EXPECT_THROW(inner3.Begin(), OrthancException);
|
|
0
|
334 EXPECT_EQ(1, db().GetTransactionNesting());
|
|
|
335 }
|
|
|
336 }
|
|
|
337 EXPECT_EQ(0, db().GetTransactionNesting());
|
|
|
338 EXPECT_EQ(0, CountFoo());
|
|
|
339 }
|
