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