1 //
2 // ODBCSQLiteTest.cpp
3 //
4 // Copyright (c) 2006, Applied Informatics Software Engineering GmbH.
5 // and Contributors.
6 //
7 // SPDX-License-Identifier: BSL-1.0
8 //
9
10
11 #include "ODBCSQLiteTest.h"
12 #include "CppUnit/TestCaller.h"
13 #include "CppUnit/TestSuite.h"
14 #include "Poco/String.h"
15 #include "Poco/Format.h"
16 #include "Poco/Exception.h"
17 #include "Poco/Data/LOB.h"
18 #include "Poco/Data/StatementImpl.h"
19 #include "Poco/Data/ODBC/Connector.h"
20 #include "Poco/Data/ODBC/Utility.h"
21 #include "Poco/Data/ODBC/Diagnostics.h"
22 #include "Poco/Data/ODBC/ODBCException.h"
23 #include "Poco/Data/ODBC/ODBCStatementImpl.h"
24 #include <sqltypes.h>
25 #include <iostream>
26
27
28 using namespace Poco::Data::Keywords;
29 using Poco::Data::ODBC::Utility;
30 using Poco::Data::ODBC::ConnectionException;
31 using Poco::Data::ODBC::StatementException;
32 using Poco::Data::ODBC::StatementDiagnostics;
33 using Poco::format;
34 using Poco::NotFoundException;
35
36
37 #define SQLITE_ODBC_DRIVER "SQLite3 ODBC Driver"
38 #define SQLITE_DSN "PocoDataSQLiteTest"
39 #define SQLITE_DB "dummy.db"
40
41
42 ODBCTest::SessionPtr ODBCSQLiteTest::_pSession;
43 ODBCTest::ExecPtr ODBCSQLiteTest::_pExecutor;
44 std::string ODBCSQLiteTest::_driver = SQLITE_ODBC_DRIVER;
45 std::string ODBCSQLiteTest::_dsn = SQLITE_DSN;
46 std::string ODBCSQLiteTest::_uid = "";
47 std::string ODBCSQLiteTest::_pwd = "";
48 std::string ODBCSQLiteTest::_connectString = "Driver=" SQLITE_ODBC_DRIVER
49 ";Database=" SQLITE_DB ";";
50
51
ODBCSQLiteTest(const std::string & name)52 ODBCSQLiteTest::ODBCSQLiteTest(const std::string& name):
53 ODBCTest(name, _pSession, _pExecutor, _dsn, _uid, _pwd, _connectString)
54 {
55 }
56
57
~ODBCSQLiteTest()58 ODBCSQLiteTest::~ODBCSQLiteTest()
59 {
60 }
61
62
testBareboneODBC()63 void ODBCSQLiteTest::testBareboneODBC()
64 {
65 std::string tableCreateString = "CREATE TABLE Test "
66 "(First VARCHAR(30),"
67 "Second VARCHAR(30),"
68 "Third BLOB,"
69 "Fourth INTEGER,"
70 "Fifth REAL,"
71 "Sixth TIMESTAMP)";
72
73 executor().bareboneODBCTest(dbConnString(), tableCreateString, SQLExecutor::PB_IMMEDIATE, SQLExecutor::DE_MANUAL);
74 executor().bareboneODBCTest(dbConnString(), tableCreateString, SQLExecutor::PB_IMMEDIATE, SQLExecutor::DE_BOUND);
75 executor().bareboneODBCTest(dbConnString(), tableCreateString, SQLExecutor::PB_AT_EXEC, SQLExecutor::DE_MANUAL);
76 executor().bareboneODBCTest(dbConnString(), tableCreateString, SQLExecutor::PB_AT_EXEC, SQLExecutor::DE_BOUND);
77
78 tableCreateString = "CREATE TABLE Test "
79 "(First VARCHAR(30),"
80 "Second VARCHAR(30),"
81 "Third BLOB,"
82 "Fourth INTEGER,"
83 "Fifth REAL,"
84 "Sixth DATETIME)";
85
86 executor().bareboneODBCTest(dbConnString(), tableCreateString, SQLExecutor::PB_IMMEDIATE, SQLExecutor::DE_MANUAL);
87 executor().bareboneODBCTest(dbConnString(), tableCreateString, SQLExecutor::PB_IMMEDIATE, SQLExecutor::DE_BOUND);
88 executor().bareboneODBCTest(dbConnString(), tableCreateString, SQLExecutor::PB_AT_EXEC, SQLExecutor::DE_MANUAL);
89 executor().bareboneODBCTest(dbConnString(), tableCreateString, SQLExecutor::PB_AT_EXEC, SQLExecutor::DE_BOUND);
90
91 tableCreateString = "CREATE TABLE Test "
92 "(First VARCHAR(30),"
93 "Second VARCHAR(30),"
94 "Third BLOB,"
95 "Fourth INTEGER,"
96 "Fifth REAL,"
97 "Sixth DATE)";
98
99 executor().bareboneODBCTest(dbConnString(), tableCreateString, SQLExecutor::PB_IMMEDIATE, SQLExecutor::DE_MANUAL);
100 executor().bareboneODBCTest(dbConnString(), tableCreateString, SQLExecutor::PB_IMMEDIATE, SQLExecutor::DE_BOUND);
101 executor().bareboneODBCTest(dbConnString(), tableCreateString, SQLExecutor::PB_AT_EXEC, SQLExecutor::DE_MANUAL);
102 executor().bareboneODBCTest(dbConnString(), tableCreateString, SQLExecutor::PB_AT_EXEC, SQLExecutor::DE_BOUND);
103 }
104
105
testAffectedRows()106 void ODBCSQLiteTest::testAffectedRows()
107 {
108 if (!_pSession) fail ("Test not available.");
109
110 for (int i = 0; i < 8;)
111 {
112 recreateStringsTable();
113 _pSession->setFeature("autoBind", bindValue(i));
114 _pSession->setFeature("autoExtract", bindValue(i+1));
115 // see SQLiteStatementImpl::affectedRows() documentation for explanation
116 // why "WHERE 1" is necessary here
117 _pExecutor->affectedRows("WHERE 1");
118 i += 2;
119 }
120 }
121
122
testNull()123 void ODBCSQLiteTest::testNull()
124 {
125 if (!_pSession) fail ("Test not available.");
126
127 // test for NOT NULL violation exception
128 for (int i = 0; i < 8;)
129 {
130 recreateNullsTable("NOT NULL");
131 session().setFeature("autoBind", bindValue(i));
132 session().setFeature("autoExtract", bindValue(i+1));
133 _pExecutor->notNulls("HY000");
134 i += 2;
135 }
136 }
137
138
dropObject(const std::string & type,const std::string & name)139 void ODBCSQLiteTest::dropObject(const std::string& type, const std::string& name)
140 {
141 try
142 {
143 session() << format("DROP %s %s", type, name), now;
144 }
145 catch (StatementException& ex)
146 {
147 bool ignoreError = false;
148 const StatementDiagnostics::FieldVec& flds = ex.diagnostics().fields();
149 StatementDiagnostics::Iterator it = flds.begin();
150 for (; it != flds.end(); ++it)
151 {
152 if (1 == it->_nativeError)//(no such table)
153 {
154 ignoreError = true;
155 break;
156 }
157 }
158
159 if (!ignoreError)
160 {
161 std::cout << ex.toString() << std::endl;
162 throw;
163 }
164 }
165 }
166
167
recreateNullableTable()168 void ODBCSQLiteTest::recreateNullableTable()
169 {
170 dropObject("TABLE", "NullableTest");
171 try { *_pSession << "CREATE TABLE NullableTest (EmptyString VARCHAR(30) NULL, EmptyInteger INTEGER NULL, EmptyFloat REAL NULL , EmptyDateTime TIMESTAMP NULL)", now; }
172 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail ("recreatePersonTable()"); }
173 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail ("recreatePersonTable()"); }
174 }
175
176
recreatePersonTable()177 void ODBCSQLiteTest::recreatePersonTable()
178 {
179 dropObject("TABLE", "Person");
180 try { session() << "CREATE TABLE Person (LastName VARCHAR(30), FirstName VARCHAR(30), Address VARCHAR(30), Age INTEGER)", now; }
181 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail ("recreatePersonTable()"); }
182 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail ("recreatePersonTable()"); }
183 }
184
185
recreatePersonBLOBTable()186 void ODBCSQLiteTest::recreatePersonBLOBTable()
187 {
188 dropObject("TABLE", "Person");
189 try { session() << "CREATE TABLE Person (LastName VARCHAR(30), FirstName VARCHAR(30), Address VARCHAR(30), Image BLOB)", now; }
190 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail ("recreatePersonBLOBTable()"); }
191 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail ("recreatePersonBLOBTable()"); }
192 }
193
194
recreatePersonDateTimeTable()195 void ODBCSQLiteTest::recreatePersonDateTimeTable()
196 {
197 dropObject("TABLE", "Person");
198 try { session() << "CREATE TABLE Person (LastName VARCHAR(30), FirstName VARCHAR(30), Address VARCHAR(30), Born TIMESTAMP)", now; }
199 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail ("recreatePersonDateTimeTable()"); }
200 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail ("recreatePersonDateTimeTable()"); }
201 }
202
203
recreateIntsTable()204 void ODBCSQLiteTest::recreateIntsTable()
205 {
206 dropObject("TABLE", "Strings");
207 try { session() << "CREATE TABLE Strings (str INTEGER)", now; }
208 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail ("recreateIntsTable()"); }
209 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail ("recreateIntsTable()"); }
210 }
211
212
recreateStringsTable()213 void ODBCSQLiteTest::recreateStringsTable()
214 {
215 dropObject("TABLE", "Strings");
216 try { session() << "CREATE TABLE Strings (str VARCHAR(30))", now; }
217 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail ("recreateStringsTable()"); }
218 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail ("recreateStringsTable()"); }
219 }
220
221
recreateFloatsTable()222 void ODBCSQLiteTest::recreateFloatsTable()
223 {
224 dropObject("TABLE", "Strings");
225 try { session() << "CREATE TABLE Strings (str REAL)", now; }
226 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail ("recreateFloatsTable()"); }
227 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail ("recreateFloatsTable()"); }
228 }
229
230
recreateTuplesTable()231 void ODBCSQLiteTest::recreateTuplesTable()
232 {
233 dropObject("TABLE", "Tuples");
234 try { session() << "CREATE TABLE Tuples "
235 "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER, int6 INTEGER, "
236 "int7 INTEGER, int8 INTEGER, int9 INTEGER, int10 INTEGER, int11 INTEGER, int12 INTEGER, int13 INTEGER,"
237 "int14 INTEGER, int15 INTEGER, int16 INTEGER, int17 INTEGER, int18 INTEGER, int19 INTEGER)", now; }
238 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail ("recreateTuplesTable()"); }
239 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail ("recreateTuplesTable()"); }
240 }
241
242
recreateVectorsTable()243 void ODBCSQLiteTest::recreateVectorsTable()
244 {
245 dropObject("TABLE", "Vectors");
246 try { session() << "CREATE TABLE Vectors (int0 INTEGER, flt0 REAL, str0 VARCHAR)", now; }
247 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail ("recreateVectorsTable()"); }
248 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail ("recreateVectorsTable()"); }
249 }
250
251
recreateAnysTable()252 void ODBCSQLiteTest::recreateAnysTable()
253 {
254 dropObject("TABLE", "Anys");
255 try { session() << "CREATE TABLE Anys (int0 INTEGER, flt0 REAL, str0 VARCHAR)", now; }
256 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail ("recreateAnysTable()"); }
257 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail ("recreateAnysTable()"); }
258 }
259
260
recreateNullsTable(const std::string & notNull)261 void ODBCSQLiteTest::recreateNullsTable(const std::string& notNull)
262 {
263 dropObject("TABLE", "NullTest");
264 try { session() << format("CREATE TABLE NullTest (i INTEGER %s, r REAL %s, v VARCHAR(30) %s)",
265 notNull,
266 notNull,
267 notNull), now; }
268 catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail ("recreateNullsTable()"); }
269 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail ("recreateNullsTable()"); }
270 }
271
272
recreateMiscTable()273 void ODBCSQLiteTest::recreateMiscTable()
274 {
275 dropObject("TABLE", "MiscTest");
276 try
277 {
278 // SQLite fails with BLOB bulk operations
279 session() << "CREATE TABLE MiscTest "
280 "(First VARCHAR(30),"
281 //"Second BLOB,"
282 "Third INTEGER,"
283 "Fourth REAL,"
284 "Fifth DATETIME)", now;
285 } catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail ("recreateMiscTable()"); }
286 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail ("recreateMiscTable()"); }
287 }
288
289
recreateLogTable()290 void ODBCSQLiteTest::recreateLogTable()
291 {
292 dropObject("TABLE", "T_POCO_LOG");
293 dropObject("TABLE", "T_POCO_LOG_ARCHIVE");
294
295 try
296 {
297 std::string sql = "CREATE TABLE %s "
298 "(Source VARCHAR,"
299 "Name VARCHAR,"
300 "ProcessId INTEGER,"
301 "Thread VARCHAR, "
302 "ThreadId INTEGER,"
303 "Priority INTEGER,"
304 "Text VARCHAR,"
305 "DateTime DATETIME)";
306
307 session() << sql, "T_POCO_LOG", now;
308 session() << sql, "T_POCO_LOG_ARCHIVE", now;
309
310 } catch(ConnectionException& ce){ std::cout << ce.toString() << std::endl; fail ("recreateLogTable()"); }
311 catch(StatementException& se){ std::cout << se.toString() << std::endl; fail ("recreateLogTable()"); }
312 }
313
314
suite()315 CppUnit::Test* ODBCSQLiteTest::suite()
316 {
317 if ((_pSession = init(_driver, _dsn, _uid, _pwd, _connectString)))
318 {
319 std::cout << "*** Connected to [" << _driver << "] test database." << std::endl;
320
321 _pExecutor = new SQLExecutor(_driver + " SQL Executor", _pSession);
322
323 CppUnit::TestSuite* pSuite = new CppUnit::TestSuite("ODBCSQLiteTest");
324
325 CppUnit_addTest(pSuite, ODBCSQLiteTest, testBareboneODBC);
326 CppUnit_addTest(pSuite, ODBCSQLiteTest, testZeroRows);
327 CppUnit_addTest(pSuite, ODBCSQLiteTest, testSimpleAccess);
328 CppUnit_addTest(pSuite, ODBCSQLiteTest, testComplexType);
329 CppUnit_addTest(pSuite, ODBCSQLiteTest, testSimpleAccessVector);
330 CppUnit_addTest(pSuite, ODBCSQLiteTest, testComplexTypeVector);
331 CppUnit_addTest(pSuite, ODBCSQLiteTest, testSharedPtrComplexTypeVector);
332 CppUnit_addTest(pSuite, ODBCSQLiteTest, testAutoPtrComplexTypeVector);
333 CppUnit_addTest(pSuite, ODBCSQLiteTest, testInsertVector);
334 CppUnit_addTest(pSuite, ODBCSQLiteTest, testInsertEmptyVector);
335 CppUnit_addTest(pSuite, ODBCSQLiteTest, testSimpleAccessList);
336 CppUnit_addTest(pSuite, ODBCSQLiteTest, testComplexTypeList);
337 CppUnit_addTest(pSuite, ODBCSQLiteTest, testInsertList);
338 CppUnit_addTest(pSuite, ODBCSQLiteTest, testInsertEmptyList);
339 CppUnit_addTest(pSuite, ODBCSQLiteTest, testSimpleAccessDeque);
340 CppUnit_addTest(pSuite, ODBCSQLiteTest, testComplexTypeDeque);
341 CppUnit_addTest(pSuite, ODBCSQLiteTest, testInsertDeque);
342 CppUnit_addTest(pSuite, ODBCSQLiteTest, testInsertEmptyDeque);
343 CppUnit_addTest(pSuite, ODBCSQLiteTest, testAffectedRows);
344 CppUnit_addTest(pSuite, ODBCSQLiteTest, testInsertSingleBulk);
345 CppUnit_addTest(pSuite, ODBCSQLiteTest, testInsertSingleBulkVec);
346 CppUnit_addTest(pSuite, ODBCSQLiteTest, testLimit);
347 CppUnit_addTest(pSuite, ODBCSQLiteTest, testLimitOnce);
348 CppUnit_addTest(pSuite, ODBCSQLiteTest, testLimitPrepare);
349 CppUnit_addTest(pSuite, ODBCSQLiteTest, testLimitZero);
350 CppUnit_addTest(pSuite, ODBCSQLiteTest, testPrepare);
351 CppUnit_addTest(pSuite, ODBCSQLiteTest, testSetSimple);
352 CppUnit_addTest(pSuite, ODBCSQLiteTest, testSetComplex);
353 CppUnit_addTest(pSuite, ODBCSQLiteTest, testSetComplexUnique);
354 CppUnit_addTest(pSuite, ODBCSQLiteTest, testMultiSetSimple);
355 CppUnit_addTest(pSuite, ODBCSQLiteTest, testMultiSetComplex);
356 CppUnit_addTest(pSuite, ODBCSQLiteTest, testMapComplex);
357 CppUnit_addTest(pSuite, ODBCSQLiteTest, testMapComplexUnique);
358 CppUnit_addTest(pSuite, ODBCSQLiteTest, testMultiMapComplex);
359 CppUnit_addTest(pSuite, ODBCSQLiteTest, testSelectIntoSingle);
360 CppUnit_addTest(pSuite, ODBCSQLiteTest, testSelectIntoSingleStep);
361 CppUnit_addTest(pSuite, ODBCSQLiteTest, testSelectIntoSingleFail);
362 CppUnit_addTest(pSuite, ODBCSQLiteTest, testLowerLimitOk);
363 CppUnit_addTest(pSuite, ODBCSQLiteTest, testLowerLimitFail);
364 CppUnit_addTest(pSuite, ODBCSQLiteTest, testCombinedLimits);
365 CppUnit_addTest(pSuite, ODBCSQLiteTest, testCombinedIllegalLimits);
366 CppUnit_addTest(pSuite, ODBCSQLiteTest, testRange);
367 CppUnit_addTest(pSuite, ODBCSQLiteTest, testIllegalRange);
368 CppUnit_addTest(pSuite, ODBCSQLiteTest, testSingleSelect);
369 CppUnit_addTest(pSuite, ODBCSQLiteTest, testEmptyDB);
370 CppUnit_addTest(pSuite, ODBCSQLiteTest, testBLOB);
371 CppUnit_addTest(pSuite, ODBCSQLiteTest, testBLOBContainer);
372 CppUnit_addTest(pSuite, ODBCSQLiteTest, testBLOBStmt);
373 CppUnit_addTest(pSuite, ODBCSQLiteTest, testDateTime);
374 CppUnit_addTest(pSuite, ODBCSQLiteTest, testFloat);
375 CppUnit_addTest(pSuite, ODBCSQLiteTest, testDouble);
376 CppUnit_addTest(pSuite, ODBCSQLiteTest, testTuple);
377 CppUnit_addTest(pSuite, ODBCSQLiteTest, testTupleVector);
378 CppUnit_addTest(pSuite, ODBCSQLiteTest, testInternalExtraction);
379 CppUnit_addTest(pSuite, ODBCSQLiteTest, testFilter);
380 CppUnit_addTest(pSuite, ODBCSQLiteTest, testInternalStorageType);
381 CppUnit_addTest(pSuite, ODBCSQLiteTest, testNull);
382 CppUnit_addTest(pSuite, ODBCSQLiteTest, testRowIterator);
383 CppUnit_addTest(pSuite, ODBCSQLiteTest, testAsync);
384 CppUnit_addTest(pSuite, ODBCSQLiteTest, testAny);
385 CppUnit_addTest(pSuite, ODBCSQLiteTest, testDynamicAny);
386 CppUnit_addTest(pSuite, ODBCSQLiteTest, testSQLChannel);
387 CppUnit_addTest(pSuite, ODBCSQLiteTest, testSQLLogger);
388 CppUnit_addTest(pSuite, ODBCSQLiteTest, testSessionTransaction);
389 CppUnit_addTest(pSuite, ODBCSQLiteTest, testTransaction);
390 CppUnit_addTest(pSuite, ODBCSQLiteTest, testTransactor);
391 CppUnit_addTest(pSuite, ODBCSQLiteTest, testReconnect);
392
393 return pSuite;
394 }
395
396 return 0;
397 }
398