1//
2// SQLiteTest.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 "SQLiteTest.h"
12#include "Poco/CppUnit/TestCaller.h"
13#include "Poco/CppUnit/TestSuite.h"
14#include "Poco/SQL/Date.h"
15#include "Poco/SQL/Time.h"
16#include "Poco/SQL/LOB.h"
17#include "Poco/SQL/Statement.h"
18#include "Poco/SQL/RecordSet.h"
19#include "Poco/SQL/RowFilter.h"
20#include "Poco/SQL/JSONRowFormatter.h"
21#include "Poco/SQL/SQLChannel.h"
22#include "Poco/SQL/SessionFactory.h"
23#include "Poco/SQL/SQLite/Connector.h"
24#include "Poco/SQL/SQLite/Utility.h"
25#include "Poco/SQL/SQLite/Notifier.h"
26#include "Poco/Dynamic/Var.h"
27#include "Poco/SQL/TypeHandler.h"
28#include "Poco/Nullable.h"
29#include "Poco/SQL/Transaction.h"
30#include "Poco/SQL/SQLException.h"
31#include "Poco/SQL/SQLite/SQLiteException.h"
32#include "Poco/Tuple.h"
33#include "Poco/Any.h"
34#include "Poco/SharedPtr.h"
35#include "Poco/DynamicAny.h"
36#include "Poco/DateTime.h"
37#include "Poco/Logger.h"
38#include "Poco/Message.h"
39#include "Poco/Thread.h"
40#include "Poco/AutoPtr.h"
41#include "Poco/Exception.h"
42#include "Poco/RefCountedObject.h"
43#include "Poco/Stopwatch.h"
44#include "Poco/Delegate.h"
45#include <iostream>
46
47
48using namespace Poco::SQL::Keywords;
49using Poco::SQL::Session;
50using Poco::SQL::Statement;
51using Poco::SQL::RecordSet;
52using Poco::SQL::RowFilter;
53using Poco::SQL::JSONRowFormatter;
54using Poco::SQL::Column;
55using Poco::SQL::Row;
56using Poco::SQL::SQLChannel;
57using Poco::SQL::LimitException;
58using Poco::SQL::ConnectionFailedException;
59using Poco::SQL::CLOB;
60using Poco::SQL::Date;
61using Poco::SQL::Time;
62using Poco::SQL::Transaction;
63using Poco::SQL::AbstractExtractionVec;
64using Poco::SQL::AbstractExtractionVecVec;
65using Poco::SQL::AbstractBindingVec;
66using Poco::SQL::NotConnectedException;
67using Poco::SQL::SQLite::Notifier;
68using Poco::Nullable;
69using Poco::Tuple;
70using Poco::Any;
71using Poco::AnyCast;
72using Poco::DynamicAny;
73using Poco::DateTime;
74using Poco::Logger;
75using Poco::Message;
76using Poco::AutoPtr;
77using Poco::Thread;
78using Poco::format;
79using Poco::InvalidAccessException;
80using Poco::RangeException;
81using Poco::BadCastException;
82using Poco::NotFoundException;
83using Poco::NullPointerException;
84using Poco::TimeoutException;
85using Poco::NotImplementedException;
86using Poco::SQL::SQLite::ConstraintViolationException;
87using Poco::SQL::SQLite::ParameterCountMismatchException;
88using Poco::Int32;
89using Poco::Int64;
90using Poco::Dynamic::Var;
91using Poco::SQL::SQLite::Utility;
92using Poco::delegate;
93using Poco::RefCountedObject;
94using Poco::RCDC;
95
96
97class Person
98{
99public:
100 Person(){_age = 0;}
101 Person(const std::string& ln, const std::string& fn, const std::string& adr, int a):_lastName(ln), _firstName(fn), _address(adr), _age(a)
102 {
103 }
104 bool operator==(const Person& other) const
105 {
106 return _lastName == other._lastName && _firstName == other._firstName && _address == other._address && _age == other._age;
107 }
108
109 bool operator < (const Person& p) const
110 {
111 if (_age < p._age)
112 return true;
113 if (_lastName < p._lastName)
114 return true;
115 if (_firstName < p._firstName)
116 return true;
117 return (_address < p._address);
118 }
119
120 const std::string& operator () () const
121 /// This method is required so we can extract data to a map!
122 {
123 // we choose the lastName as examplary key
124 return _lastName;
125 }
126
127 const std::string& getLastName() const
128 {
129 return _lastName;
130 }
131
132 void setLastName(const std::string& lastName)
133 {
134 _lastName = lastName;
135 }
136
137 const std::string& getFirstName() const
138 {
139 return _firstName;
140 }
141
142 void setFirstName(const std::string& firstName)
143 {
144 _firstName = firstName;
145 }
146
147 const std::string& getAddress() const
148 {
149 return _address;
150 }
151
152 void setAddress(const std::string& address)
153 {
154 _address = address;
155 }
156
157 const int& getAge() const
158 {
159 return _age;
160 }
161
162 void setAge(const int& age)
163 {
164 _age = age;
165 }
166
167private:
168 std::string _lastName;
169 std::string _firstName;
170 std::string _address;
171 int _age;
172};
173
174
175namespace Poco {
176namespace SQL {
177
178
179template <>
180class TypeHandler<Person>
181{
182public:
183 static void bind(std::size_t pos, const Person& obj, AbstractBinder::Ptr pBinder, AbstractBinder::Direction dir)
184 {
185 // the table is defined as Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))
186 poco_assert_dbg (!pBinder.isNull());
187 pBinder->bind(pos++, obj.getLastName(), dir);
188 pBinder->bind(pos++, obj.getFirstName(), dir);
189 pBinder->bind(pos++, obj.getAddress(), dir);
190 pBinder->bind(pos++, obj.getAge(), dir);
191 }
192
193 static void prepare(std::size_t pos, const Person& obj, AbstractPreparator::Ptr pPrepare)
194 {
195 // no-op (SQLite is prepare-less connector)
196 }
197
198 static std::size_t size()
199 {
200 return 4;
201 }
202
203 static void extract(std::size_t pos, Person& obj, const Person& defVal, AbstractExtractor::Ptr pExt)
204 {
205 poco_assert_dbg (!pExt.isNull());
206 std::string lastName;
207 std::string firstName;
208 std::string address;
209 int age;
210
211 if (pExt->extract(pos++, lastName))
212 obj.setLastName(lastName);
213 else
214 obj.setLastName(defVal.getLastName());
215
216 if (pExt->extract(pos++, firstName))
217 obj.setFirstName(firstName);
218 else
219 obj.setFirstName(defVal.getFirstName());
220
221 if (pExt->extract(pos++, address))
222 obj.setAddress(address);
223 else
224 obj.setAddress(defVal.getAddress());
225
226 if (pExt->extract(pos++, age))
227 obj.setAge(age);
228 else
229 obj.setAge(defVal.getAge());
230 }
231
232private:
233 TypeHandler();
234 ~TypeHandler();
235 TypeHandler(const TypeHandler&);
236 TypeHandler& operator=(const TypeHandler&);
237};
238
239
240} } // namespace Poco::SQL
241
242
243int SQLiteTest::_insertCounter;
244int SQLiteTest::_updateCounter;
245int SQLiteTest::_deleteCounter;
246
247
248SQLiteTest::SQLiteTest(const std::string& name): CppUnit::TestCase(name)
249{
250 //poco_rcdc_reset;
251}
252
253
254SQLiteTest::~SQLiteTest()
255{
256 //poco_rcdc_dump_leak(std::cerr);
257}
258
259
260void SQLiteTest::testBinding()
261{
262 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
263 assertTrue (tmp.getConnectionTimeout() == Session::LOGIN_TIMEOUT_DEFAULT);
264 tmp.setConnectionTimeout(5);
265 assertTrue (tmp.getConnectionTimeout() == 5);
266 assertTrue (tmp.isConnected());
267 std::string tableName("Simpsons");
268 std::string lastName("Simpson");
269 std::string firstName("Bart");
270 std::string address("Springfield");
271 int age = 12;
272
273 std::string& rLastName(lastName);
274 std::string& rFirstName(firstName);
275 std::string& rAddress(address);
276 int& rAge = age;
277
278 const std::string& crLastName(lastName);
279 const std::string& crFirstName(firstName);
280 const std::string& crAddress(address);
281 const int& crAge = age;
282
283 int count = 0;
284 std::string result;
285
286 tmp << "DROP TABLE IF EXISTS Simpsons", now;
287 tmp << "CREATE TABLE IF NOT EXISTS Simpsons (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
288 tmp << "SELECT name FROM sqlite_master WHERE tbl_name=?", use(tableName), into(result), now;
289 assertTrue (result == tableName);
290
291 // following should not compile:
292 //tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", use("Simpson"), use("Bart"), use("Springfield"), use(age), now;
293 //tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", use(lastName), use(firstName), use(address), use(12), now;
294 //tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", useRef(lastName), useRef(firstName), useRef(address), useRef(12), now;
295
296 tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", useRef("Simpson"), useRef("Bart"), useRef("Springfield"), useRef(age), now;
297
298 tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", use(rLastName), use(rFirstName), use(rAddress), use(rAge), now;
299 tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", useRef(crLastName), useRef(crFirstName), useRef(crAddress), useRef(crAge), now;
300 tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", bind("Simpson"), bind("Bart"), bind("Springfield"), bind(12), now;
301 tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", bind(rLastName), bind(rFirstName), bind(rAddress), bind(rAge), now;
302 tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", bind(crLastName), bind(crFirstName), bind(crAddress), bind(crAge), now;
303
304 tmp << "SELECT COUNT(*) FROM Simpsons", into(count), now;
305 assertTrue (6 == count);
306}
307
308
309void SQLiteTest::testZeroRows()
310{
311 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
312 tmp << "DROP TABLE IF EXISTS ZeroTest", now;
313 tmp << "CREATE TABLE IF NOT EXISTS ZeroTest (zt INTEGER(3))", now;
314 Statement stmt = (tmp << "SELECT * FROM ZeroTest");
315 assertTrue (0 == stmt.execute());
316}
317
318
319void SQLiteTest::testSimpleAccess()
320{
321 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
322 assertTrue (tmp.isConnected());
323 std::string tableName("Person");
324 std::string lastName("lastname");
325 std::string firstName("firstname");
326 std::string address("Address");
327 int age = 133132;
328 int count = 0;
329 std::string result;
330 tmp << "DROP TABLE IF EXISTS Person", now;
331 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
332 tmp << "SELECT name FROM sqlite_master WHERE tbl_name=?", use(tableName), into(result), now;
333 assertTrue (result == tableName);
334
335 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(lastName), use(firstName), use(address), use(age), now;
336 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
337 assertTrue (count == 1);
338 tmp << "SELECT LastName FROM PERSON", into(result), now;
339 assertTrue (lastName == result);
340 tmp << "SELECT Age FROM PERSON", into(count), now;
341 assertTrue (count == age);
342 tmp << "UPDATE PERSON SET Age = -1", now;
343 tmp << "SELECT Age FROM PERSON", into(age), now;
344 assertTrue (-1 == age);
345 tmp.close();
346 assertTrue (!tmp.isConnected());
347}
348
349
350void SQLiteTest::testInMemory()
351{
352 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
353 assertTrue (tmp.isConnected());
354 std::string tableName("Person");
355 std::string lastName("lastname");
356 std::string firstName("firstname");
357 std::string address("Address");
358 int age = 133132;
359 int count = 0;
360 std::string result;
361 tmp << "DROP TABLE IF EXISTS Person", now;
362 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
363 tmp << "SELECT name FROM sqlite_master WHERE tbl_name=?", use(tableName), into(result), now;
364 assertTrue (result == tableName);
365
366 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(lastName), use(firstName), use(address), use(age), now;
367
368 // load db from file to memory
369 Session mem (Poco::SQL::SQLite::Connector::KEY, ":memory:");
370 assertTrue (Poco::SQL::SQLite::Utility::fileToMemory(mem, "dummy.db"));
371
372 mem << "SELECT COUNT(*) FROM PERSON", into(count), now;
373 assertTrue (count == 1);
374 mem << "SELECT LastName FROM PERSON", into(result), now;
375 assertTrue (lastName == result);
376 mem << "SELECT Age FROM PERSON", into(count), now;
377 assertTrue (count == age);
378 mem << "UPDATE PERSON SET Age = -1", now;
379 mem << "SELECT Age FROM PERSON", into(age), now;
380 assertTrue (-1 == age);
381
382 // save db from memory to file on the disk
383 Session dsk (Poco::SQL::SQLite::Connector::KEY, "dsk.db");
384 assertTrue (Poco::SQL::SQLite::Utility::memoryToFile("dsk.db", mem));
385
386 dsk << "SELECT COUNT(*) FROM PERSON", into(count), now;
387 assertTrue (count == 1);
388 dsk << "SELECT LastName FROM PERSON", into(result), now;
389 assertTrue (lastName == result);
390 dsk << "SELECT Age FROM PERSON", into(count), now;
391 assertTrue (count == age);
392 dsk << "UPDATE PERSON SET Age = -1", now;
393 dsk << "SELECT Age FROM PERSON", into(age), now;
394 assertTrue (-1 == age);
395
396 tmp.close();
397 mem.close();
398 dsk.close();
399
400 assertTrue (!tmp.isConnected());
401 assertTrue (!mem.isConnected());
402 assertTrue (!dsk.isConnected());
403}
404
405
406void SQLiteTest::testNullCharPointer()
407{
408 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
409 std::string lastName("lastname");
410 int age = 100;
411 int count = 100;
412 std::string result;
413 tmp << "DROP TABLE IF EXISTS Person", now;
414 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
415
416 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)",
417 bind(lastName),
418 bind("firstname"),
419 bind("Address"),
420 bind(0), now;
421
422 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
423 assertTrue (count == 1);
424 tmp << "SELECT LastName FROM PERSON", into(result), now;
425 assertTrue (lastName == result);
426 tmp << "SELECT Age FROM PERSON", into(age), now;
427 assertTrue (0 == age);
428
429 try
430 {
431 const char* pc = 0;
432 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)",
433 bind("lastname"),
434 bind("firstname"),
435 bind("Address"), bind(pc), now;
436 fail ("must fail");
437 } catch (NullPointerException&) { }
438
439 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
440 assertTrue (count == 1);
441 tmp << "SELECT LastName FROM PERSON", into(result), now;
442 assertTrue (lastName == result);
443 tmp << "SELECT Age FROM PERSON", into(age), now;
444 assertTrue (0 == age);
445}
446
447
448void SQLiteTest::testInsertCharPointer()
449{
450 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
451 std::string tableName("Person");
452 std::string lastName("lastname");
453 std::string firstName("firstname");
454 std::string address("Address");
455 int age = 133132;
456 int count = 0;
457 std::string result;
458 tmp << "DROP TABLE IF EXISTS Person", now;
459 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
460
461 const char* pc = 0;
462 try
463 {
464 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", bind(pc), now;
465 fail ("must fail");
466 } catch (NullPointerException&) { }
467
468 pc = (const char*) std::calloc(9, sizeof(char));
469 poco_check_ptr (pc);
470 std::strncpy((char*) pc, "lastname", 8);
471 Statement stmt = (tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)",
472 bind(pc),
473 bind("firstname"),
474 bind("Address"),
475 bind(133132));
476
477 std::free((void*) pc); pc = 0;
478 assertTrue (1 == stmt.execute());
479
480 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
481 assertTrue (count == 1);
482 tmp << "SELECT LastName FROM PERSON", into(result), now;
483 assertTrue (lastName == result);
484 tmp << "SELECT Age FROM PERSON", into(count), now;
485 assertTrue (count == age);
486}
487
488
489void SQLiteTest::testInsertCharPointer2()
490{
491 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
492 std::string tableName("Person");
493 std::string lastName("lastname");
494 std::string firstName("firstname");
495 std::string address("Address");
496 int age = 133132;
497 int count = 0;
498 std::string result;
499 tmp << "DROP TABLE IF EXISTS Person", now;
500 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
501
502 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)",
503 bind("lastname"),
504 bind("firstname"),
505 bind("Address"),
506 bind(133132), now;
507 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
508 assertTrue (count == 1);
509 Statement stmt1 = (tmp << "SELECT LastName FROM PERSON", into(result));
510 stmt1.execute();
511 assertTrue (lastName == result);
512 count = 0;
513 Statement stmt2 = (tmp << "SELECT Age FROM PERSON", into(count));
514 stmt2.execute();
515 assertTrue (count == age);
516}
517
518
519void SQLiteTest::testComplexType()
520{
521 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
522 Person p1("LN1", "FN1", "ADDR1", 1);
523 Person p2("LN2", "FN2", "ADDR2", 2);
524 tmp << "DROP TABLE IF EXISTS Person", now;
525 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
526 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(p1), now;
527 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(p2), now;
528 int count = 0;
529 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
530 assertTrue (count == 2);
531
532 Person c1;
533 Person c2;
534 tmp << "SELECT * FROM PERSON WHERE LASTNAME = :ln", into(c1), useRef(p1.getLastName()), now;
535 assertTrue (c1 == p1);
536
537 tmp << "DROP TABLE IF EXISTS Person", now;
538 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName1 VARCHAR(30), FirstName1 VARCHAR, Address1 VARCHAR, Age1 INTEGER(3),"
539 "LastName2 VARCHAR(30), FirstName2 VARCHAR, Address2 VARCHAR, Age2 INTEGER(3))", now;
540
541 Tuple<Person,Person> t(p1,p2);
542
543 tmp << "INSERT INTO PERSON VALUES(:ln1, :fn1, :ad1, :age1, :ln2, :fn2, :ad2, :age2)", use(t), now;
544
545 Tuple<Person,Person> ret;
546 assertTrue (ret != t);
547 tmp << "SELECT * FROM PERSON", into(ret), now;
548 assertTrue (ret == t);
549}
550
551
552
553void SQLiteTest::testSimpleAccessVector()
554{
555 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
556 std::vector<std::string> lastNames;
557 std::vector<std::string> firstNames;
558 std::vector<std::string> addresses;
559 std::vector<int> ages;
560 std::string tableName("Person");
561 lastNames.push_back("LN1");
562 lastNames.push_back("LN2");
563 firstNames.push_back("FN1");
564 firstNames.push_back("FN2");
565 addresses.push_back("ADDR1");
566 addresses.push_back("ADDR2");
567 ages.push_back(1);
568 ages.push_back(2);
569 int count = 0;
570 std::string result;
571 tmp << "DROP TABLE IF EXISTS Person", now;
572 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
573
574 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(lastNames), use(firstNames), use(addresses), use(ages), now;
575 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
576 assertTrue (count == 2);
577
578 std::vector<std::string> lastNamesR;
579 std::vector<std::string> firstNamesR;
580 std::vector<std::string> addressesR;
581 std::vector<int> agesR;
582 tmp << "SELECT * FROM PERSON", into(lastNamesR), into(firstNamesR), into(addressesR), into(agesR), now;
583 assertTrue (ages == agesR);
584 assertTrue (lastNames == lastNamesR);
585 assertTrue (firstNames == firstNamesR);
586 assertTrue (addresses == addressesR);
587}
588
589
590void SQLiteTest::testComplexTypeVector()
591{
592 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
593 std::vector<Person> people;
594 people.push_back(Person("LN1", "FN1", "ADDR1", 1));
595 people.push_back(Person("LN2", "FN2", "ADDR2", 2));
596 tmp << "DROP TABLE IF EXISTS Person", now;
597 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
598 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
599 int count = 0;
600 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
601 assertTrue (count == 2);
602
603 std::vector<Person> result;
604 tmp << "SELECT * FROM PERSON", into(result), now;
605 assertTrue (result == people);
606}
607
608
609void SQLiteTest::testSharedPtrComplexTypeVector()
610{
611 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
612 std::vector<Poco::SharedPtr<Person> > people;
613 people.push_back(new Person("LN1", "FN1", "ADDR1", 1));
614 people.push_back(new Person("LN2", "FN2", "ADDR2", 2));
615 tmp << "DROP TABLE IF EXISTS Person", now;
616 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
617 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
618 int count = 0;
619 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
620 assertTrue (count == 2);
621
622 std::vector<Poco::SharedPtr<Person> > result;
623 tmp << "SELECT * FROM PERSON", into(result), now;
624 assertTrue (*result[0] == *people[0]);
625 assertTrue (*result[1] == *people[1]);
626}
627
628
629void SQLiteTest::testInsertVector()
630{
631 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
632 std::vector<std::string> str;
633 str.push_back("s1");
634 str.push_back("s2");
635 str.push_back("s3");
636 str.push_back("s3");
637 int count = 100;
638 tmp << "DROP TABLE IF EXISTS Strings", now;
639 tmp << "CREATE TABLE IF NOT EXISTS Strings (str VARCHAR(30))", now;
640 {
641 Statement stmt((tmp << "INSERT INTO Strings VALUES(:str)", use(str)));
642 tmp << "SELECT COUNT(*) FROM Strings", into(count), now;
643 assertTrue (count == 0);
644 stmt.execute();
645 tmp << "SELECT COUNT(*) FROM Strings", into(count), now;
646 assertTrue (count == 4);
647 }
648 count = 0;
649 tmp << "SELECT COUNT(*) FROM Strings", into(count), now;
650 assertTrue (count == 4);
651}
652
653
654void SQLiteTest::testInsertEmptyVector()
655{
656 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
657 std::vector<std::string> str;
658
659 tmp << "DROP TABLE IF EXISTS Strings", now;
660 tmp << "CREATE TABLE IF NOT EXISTS Strings (str VARCHAR(30))", now;
661 try
662 {
663 tmp << "INSERT INTO Strings VALUES(:str)", use(str), now;
664 fail("empty collectons should not work");
665 }
666 catch (Poco::Exception&)
667 {
668 }
669}
670
671
672void SQLiteTest::testAffectedRows()
673{
674 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
675 std::vector<std::string> str;
676 str.push_back("s1");
677 str.push_back("s2");
678 str.push_back("s3");
679 str.push_back("s3");
680 int count = 100;
681 tmp << "DROP TABLE IF EXISTS Strings", now;
682 tmp << "CREATE TABLE IF NOT EXISTS Strings (str VARCHAR(30))", now;
683
684 Statement stmt((tmp << "INSERT INTO Strings VALUES(:str)", use(str)));
685 count = -1;
686 tmp << "SELECT COUNT(*) FROM Strings", into(count), now;
687 assertTrue (count == 0);
688 assertTrue (4 == stmt.execute());
689 tmp << "SELECT COUNT(*) FROM Strings", into(count), now;
690 assertTrue (count == 4);
691
692 Statement stmt0(tmp << "DELETE FROM Strings");
693 assertTrue (4 == stmt0.execute());
694 tmp << "SELECT COUNT(*) FROM Strings", into(count), now;
695 assertTrue (count == 0);
696
697 Statement stmt1((tmp << "SELECT * FROM Strings"));
698 tmp << "SELECT COUNT(*) FROM Strings", into(count), now;
699 assertTrue (count == 0);
700 assertTrue (0 == stmt1.execute());
701
702 Statement stmt2((tmp << "INSERT INTO Strings VALUES(:str)", use(str)));
703 count = -1;
704 tmp << "SELECT COUNT(*) FROM Strings", into(count), now;
705 assertTrue (count == 0);
706 assertTrue (4 == stmt2.execute());
707 tmp << "SELECT COUNT(*) FROM Strings", into(count), now;
708 assertTrue (count == 4);
709
710 Statement stmt3(tmp << "UPDATE Strings SET str = 's4' WHERE str = 's3'");
711 assertTrue (2 == stmt3.execute());
712
713 Statement stmt4(tmp << "DELETE FROM Strings WHERE str = 's1'");
714 assertTrue (1 == stmt4.execute());
715
716 Statement stmt5(tmp << "DELETE FROM Strings WHERE str = 'bad value'");
717 assertTrue (0 == stmt5.execute());
718
719 Statement stmt6(tmp << "DELETE FROM Strings");
720 assertTrue (3 == stmt6.execute());
721}
722
723
724void SQLiteTest::testInsertSingleBulk()
725{
726 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
727 tmp << "DROP TABLE IF EXISTS Strings", now;
728 tmp << "CREATE TABLE IF NOT EXISTS Strings (str INTEGER(10))", now;
729 std::size_t x = 0;
730 Statement stmt((tmp << "INSERT INTO Strings VALUES(:str)", use(x)));
731
732 for (std::size_t i = 0; x < 100; ++x)
733 {
734 i = stmt.execute();
735 assertTrue (1 == i);
736 }
737
738 int count = 0;
739 tmp << "SELECT COUNT(*) FROM Strings", into(count), now;
740 assertTrue (count == 100);
741 tmp << "SELECT SUM(str) FROM Strings", into(count), now;
742 assertTrue (count == ((0+99)*100/2));
743}
744
745
746void SQLiteTest::testInsertSingleBulkVec()
747{
748 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
749 tmp << "DROP TABLE IF EXISTS Strings", now;
750 tmp << "CREATE TABLE IF NOT EXISTS Strings (str INTEGER(10))", now;
751 std::vector<int> data;
752 data.push_back(0);
753 data.push_back(1);
754
755 Statement stmt((tmp << "INSERT INTO Strings VALUES(:str)", use(data)));
756
757 for (int x = 0; x < 100; x += 2)
758 {
759 data[0] = x;
760 data[1] = x+1;
761 stmt.execute();
762 }
763 int count = 0;
764 tmp << "SELECT COUNT(*) FROM Strings", into(count), now;
765 assertTrue (count == 100);
766 tmp << "SELECT SUM(str) FROM Strings", into(count), now;
767 assertTrue (count == ((0+99)*100/2));
768}
769
770
771void SQLiteTest::testLimit()
772{
773 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
774 tmp << "DROP TABLE IF EXISTS Strings", now;
775 tmp << "CREATE TABLE IF NOT EXISTS Strings (str INTEGER(10))", now;
776 std::vector<int> data;
777 for (int x = 0; x < 100; ++x)
778 {
779 data.push_back(x);
780 }
781
782 tmp << "INSERT INTO Strings VALUES(:str)", use(data), now;
783 std::vector<int> retData;
784 tmp << "SELECT * FROM Strings", into(retData), limit(50), now;
785 assertTrue (retData.size() == 50);
786 for (int x = 0; x < 50; ++x)
787 {
788 assertTrue (data[x] == retData[x]);
789 }
790}
791
792
793void SQLiteTest::testLimitZero()
794{
795 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
796 tmp << "DROP TABLE IF EXISTS Strings", now;
797 tmp << "CREATE TABLE IF NOT EXISTS Strings (str INTEGER(10))", now;
798 std::vector<int> data;
799 for (int x = 0; x < 100; ++x)
800 {
801 data.push_back(x);
802 }
803
804 tmp << "INSERT INTO Strings VALUES(:str)", use(data), now;
805 std::vector<int> retData;
806 tmp << "SELECT * FROM Strings", into(retData), limit(0), now; // stupid test, but at least we shouldn't crash
807 assertTrue (retData.size() == 0);
808}
809
810
811void SQLiteTest::testLimitOnce()
812{
813 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
814 tmp << "DROP TABLE IF EXISTS Strings", now;
815 tmp << "CREATE TABLE IF NOT EXISTS Strings (str INTEGER(10))", now;
816 std::vector<int> data;
817 for (int x = 0; x < 101; ++x)
818 {
819 data.push_back(x);
820 }
821
822 tmp << "INSERT INTO Strings VALUES(:str)", use(data), now;
823 std::vector<int> retData;
824 Statement stmt = (tmp << "SELECT * FROM Strings", into(retData), limit(50), now);
825 assertTrue (!stmt.done());
826 assertTrue (retData.size() == 50);
827 stmt.execute();
828 assertTrue (!stmt.done());
829 assertTrue (retData.size() == 100);
830 stmt.execute();
831 assertTrue (stmt.done());
832 assertTrue (retData.size() == 101);
833
834 for (int x = 0; x < 101; ++x)
835 {
836 assertTrue (data[x] == retData[x]);
837 }
838}
839
840
841void SQLiteTest::testLimitPrepare()
842{
843 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
844 tmp << "DROP TABLE IF EXISTS Strings", now;
845 tmp << "CREATE TABLE IF NOT EXISTS Strings (str INTEGER(10))", now;
846 std::vector<int> data;
847 for (int x = 0; x < 100; ++x)
848 {
849 data.push_back(x);
850 }
851
852 Statement stmtIns = (tmp << "INSERT INTO Strings VALUES(:str)", use(data));
853 assertTrue (100 == stmtIns.execute());
854
855 std::vector<int> retData;
856 Statement stmt = (tmp << "SELECT * FROM Strings", into(retData), limit(50));
857 assertTrue (retData.size() == 0);
858 assertTrue (!stmt.done());
859 std::size_t rows = stmt.execute();
860 assertTrue (50 == rows);
861 assertTrue (!stmt.done());
862 assertTrue (retData.size() == 50);
863 rows = stmt.execute();
864 assertTrue (50 == rows);
865 assertTrue (stmt.done());
866 assertTrue (retData.size() == 100);
867 rows = stmt.execute(); // will restart execution!
868 assertTrue (50 == rows);
869 assertTrue (!stmt.done());
870 assertTrue (retData.size() == 150);
871 for (int x = 0; x < 150; ++x)
872 {
873 assertTrue (data[x%100] == retData[x]);
874 }
875}
876
877
878
879void SQLiteTest::testPrepare()
880{
881 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
882 tmp << "DROP TABLE IF EXISTS Strings", now;
883 tmp << "CREATE TABLE IF NOT EXISTS Strings (str INTEGER(10))", now;
884 std::vector<int> data;
885 for (int x = 0; x < 100; x += 2)
886 {
887 data.push_back(x);
888 }
889
890 {
891 Statement stmt((tmp << "INSERT INTO Strings VALUES(:str)", use(data)));
892 }
893 // stmt should not have been executed when destroyed
894 int count = 100;
895 tmp << "SELECT COUNT(*) FROM Strings", into(count), now;
896 assertTrue (count == 0);
897}
898
899
900void SQLiteTest::testSetSimple()
901{
902 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
903 std::set<std::string> lastNames;
904 std::set<std::string> firstNames;
905 std::set<std::string> addresses;
906 std::set<int> ages;
907 std::string tableName("Person");
908 lastNames.insert("LN1");
909 lastNames.insert("LN2");
910 firstNames.insert("FN1");
911 firstNames.insert("FN2");
912 addresses.insert("ADDR1");
913 addresses.insert("ADDR2");
914 ages.insert(1);
915 ages.insert(2);
916 int count = 0;
917 std::string result;
918 tmp << "DROP TABLE IF EXISTS Person", now;
919 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
920
921 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(lastNames), use(firstNames), use(addresses), use(ages), now;
922 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
923 assertTrue (count == 2);
924
925 std::set<std::string> lastNamesR;
926 std::set<std::string> firstNamesR;
927 std::set<std::string> addressesR;
928 std::set<int> agesR;
929 tmp << "SELECT * FROM PERSON", into(lastNamesR), into(firstNamesR), into(addressesR), into(agesR), now;
930 assertTrue (ages == agesR);
931 assertTrue (lastNames == lastNamesR);
932 assertTrue (firstNames == firstNamesR);
933 assertTrue (addresses == addressesR);
934}
935
936
937void SQLiteTest::testSetComplex()
938{
939 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
940 std::set<Person> people;
941 people.insert(Person("LN1", "FN1", "ADDR1", 1));
942 people.insert(Person("LN2", "FN2", "ADDR2", 2));
943 tmp << "DROP TABLE IF EXISTS Person", now;
944 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
945 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
946 int count = 0;
947 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
948 assertTrue (count == 2);
949
950 std::set<Person> result;
951 tmp << "SELECT * FROM PERSON", into(result), now;
952 assertTrue (result == people);
953}
954
955
956void SQLiteTest::testSetComplexUnique()
957{
958 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
959 std::vector<Person> people;
960 Person p1("LN1", "FN1", "ADDR1", 1);
961 people.push_back(p1);
962 people.push_back(p1);
963 people.push_back(p1);
964 people.push_back(p1);
965 Person p2("LN2", "FN2", "ADDR2", 2);
966 people.push_back(p2);
967
968 tmp << "DROP TABLE IF EXISTS Person", now;
969 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
970 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
971 int count = 0;
972 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
973 assertTrue (count == 5);
974
975 std::set<Person> result;
976 tmp << "SELECT * FROM PERSON", into(result), now;
977 assertTrue (result.size() == 2);
978 assertTrue (*result.begin() == p1);
979 assertTrue (*++result.begin() == p2);
980}
981
982void SQLiteTest::testMultiSetSimple()
983{
984 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
985 std::multiset<std::string> lastNames;
986 std::multiset<std::string> firstNames;
987 std::multiset<std::string> addresses;
988 std::multiset<int> ages;
989 std::string tableName("Person");
990 lastNames.insert("LN1");
991 lastNames.insert("LN2");
992 firstNames.insert("FN1");
993 firstNames.insert("FN2");
994 addresses.insert("ADDR1");
995 addresses.insert("ADDR2");
996 ages.insert(1);
997 ages.insert(2);
998 int count = 0;
999 std::string result;
1000 tmp << "DROP TABLE IF EXISTS Person", now;
1001 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
1002
1003 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(lastNames), use(firstNames), use(addresses), use(ages), now;
1004 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
1005 assertTrue (count == 2);
1006
1007 std::multiset<std::string> lastNamesR;
1008 std::multiset<std::string> firstNamesR;
1009 std::multiset<std::string> addressesR;
1010 std::multiset<int> agesR;
1011 tmp << "SELECT * FROM PERSON", into(lastNamesR), into(firstNamesR), into(addressesR), into(agesR), now;
1012 assertTrue (ages.size() == agesR.size());
1013 assertTrue (lastNames.size() == lastNamesR.size());
1014 assertTrue (firstNames.size() == firstNamesR.size());
1015 assertTrue (addresses.size() == addressesR.size());
1016}
1017
1018
1019void SQLiteTest::testMultiSetComplex()
1020{
1021 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1022 std::multiset<Person> people;
1023 Person p1("LN1", "FN1", "ADDR1", 1);
1024 people.insert(p1);
1025 people.insert(p1);
1026 people.insert(p1);
1027 people.insert(p1);
1028 Person p2("LN2", "FN2", "ADDR2", 2);
1029 people.insert(p2);
1030
1031 tmp << "DROP TABLE IF EXISTS Person", now;
1032 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
1033 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
1034 int count = 0;
1035 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
1036 assertTrue (count == 5);
1037
1038 std::multiset<Person> result;
1039 tmp << "SELECT * FROM PERSON", into(result), now;
1040 assertTrue (result.size() == people.size());
1041}
1042
1043
1044void SQLiteTest::testMapComplex()
1045{
1046 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1047 std::map<std::string, Person> people;
1048 Person p1("LN1", "FN1", "ADDR1", 1);
1049 Person p2("LN2", "FN2", "ADDR2", 2);
1050 people.insert(std::make_pair("LN1", p1));
1051 people.insert(std::make_pair("LN2", p2));
1052 tmp << "DROP TABLE IF EXISTS Person", now;
1053 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
1054 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
1055 int count = 0;
1056 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
1057 assertTrue (count == 2);
1058
1059 std::map<std::string, Person> result;
1060 tmp << "SELECT * FROM PERSON", into(result), now;
1061 assertTrue (result == people);
1062}
1063
1064
1065void SQLiteTest::testMapComplexUnique()
1066{
1067 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1068 std::multimap<std::string, Person> people;
1069 Person p1("LN1", "FN1", "ADDR1", 1);
1070 Person p2("LN2", "FN2", "ADDR2", 2);
1071 people.insert(std::make_pair("LN1", p1));
1072 people.insert(std::make_pair("LN1", p1));
1073 people.insert(std::make_pair("LN1", p1));
1074 people.insert(std::make_pair("LN1", p1));
1075 people.insert(std::make_pair("LN2", p2));
1076 tmp << "DROP TABLE IF EXISTS Person", now;
1077 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
1078 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
1079 int count = 0;
1080 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
1081 assertTrue (count == 5);
1082
1083 std::map<std::string, Person> result;
1084 tmp << "SELECT * FROM PERSON", into(result), now;
1085 assertTrue (result.size() == 2);
1086}
1087
1088
1089void SQLiteTest::testMultiMapComplex()
1090{
1091 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1092 std::multimap<std::string, Person> people;
1093 Person p1("LN1", "FN1", "ADDR1", 1);
1094 Person p2("LN2", "FN2", "ADDR2", 2);
1095 people.insert(std::make_pair("LN1", p1));
1096 people.insert(std::make_pair("LN1", p1));
1097 people.insert(std::make_pair("LN1", p1));
1098 people.insert(std::make_pair("LN1", p1));
1099 people.insert(std::make_pair("LN2", p2));
1100 tmp << "DROP TABLE IF EXISTS Person", now;
1101 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
1102 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
1103 int count = 0;
1104 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
1105 assertTrue (count == 5);
1106
1107 std::multimap<std::string, Person> result;
1108 tmp << "SELECT * FROM PERSON", into(result), now;
1109 assertTrue (result.size() == people.size());
1110}
1111
1112
1113void SQLiteTest::testSelectIntoSingle()
1114{
1115 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1116 std::multimap<std::string, Person> people;
1117 Person p1("LN1", "FN1", "ADDR1", 1);
1118 Person p2("LN2", "FN2", "ADDR2", 2);
1119 people.insert(std::make_pair("LN1", p1));
1120 people.insert(std::make_pair("LN1", p2));
1121 tmp << "DROP TABLE IF EXISTS Person", now;
1122 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
1123 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
1124 int count = 0;
1125 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
1126 assertTrue (count == 2);
1127 Person result;
1128 tmp << "SELECT * FROM PERSON", into(result), limit(1), now; // will return 1 object into one single result
1129 assertTrue (result == p1);
1130}
1131
1132
1133void SQLiteTest::testSelectIntoSingleStep()
1134{
1135 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1136 std::multimap<std::string, Person> people;
1137 Person p1("LN1", "FN1", "ADDR1", 1);
1138 Person p2("LN2", "FN2", "ADDR2", 2);
1139 people.insert(std::make_pair("LN1", p1));
1140 people.insert(std::make_pair("LN1", p2));
1141 tmp << "DROP TABLE IF EXISTS Person", now;
1142 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
1143 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
1144 int count = 0;
1145 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
1146 assertTrue (count == 2);
1147 Person result;
1148 Statement stmt = (tmp << "SELECT * FROM PERSON", into(result), limit(1));
1149 stmt.execute();
1150 assertTrue (result == p1);
1151 assertTrue (!stmt.done());
1152 stmt.execute();
1153 assertTrue (result == p2);
1154 assertTrue (stmt.done());
1155}
1156
1157
1158void SQLiteTest::testSelectIntoSingleFail()
1159{
1160 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1161 std::multimap<std::string, Person> people;
1162 Person p1("LN1", "FN1", "ADDR1", 1);
1163 Person p2("LN2", "FN2", "ADDR2", 2);
1164 people.insert(std::make_pair("LN1", p1));
1165 people.insert(std::make_pair("LN1", p2));
1166 tmp << "DROP TABLE IF EXISTS Person", now;
1167 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
1168 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
1169 int count = 0;
1170 tmp << "SELECT COUNT(*) FROM PERSON", into(count), limit(2, true), now;
1171 assertTrue (count == 2);
1172 Person result;
1173 try
1174 {
1175 tmp << "SELECT * FROM PERSON", into(result), limit(1, true), now; // will fail now
1176 fail("hardLimit is set: must fail");
1177 }
1178 catch(Poco::SQL::LimitException&)
1179 {
1180 }
1181}
1182
1183
1184void SQLiteTest::testLowerLimitOk()
1185{
1186 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1187 std::multimap<std::string, Person> people;
1188 Person p1("LN1", "FN1", "ADDR1", 1);
1189 Person p2("LN2", "FN2", "ADDR2", 2);
1190 people.insert(std::make_pair("LN1", p1));
1191 people.insert(std::make_pair("LN1", p2));
1192 tmp << "DROP TABLE IF EXISTS Person", now;
1193 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
1194 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
1195 int count = 0;
1196 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
1197 assertTrue (count == 2);
1198 Person result;
1199 try
1200 {
1201 tmp << "SELECT * FROM PERSON", into(result), lowerLimit(2), now; // will return 2 objects into one single result but only room for one!
1202 fail("Not enough space for results");
1203 }
1204 catch(Poco::Exception&)
1205 {
1206 }
1207}
1208
1209
1210void SQLiteTest::testSingleSelect()
1211{
1212 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1213 std::multimap<std::string, Person> people;
1214 Person p1("LN1", "FN1", "ADDR1", 1);
1215 Person p2("LN2", "FN2", "ADDR2", 2);
1216 people.insert(std::make_pair("LN1", p1));
1217 people.insert(std::make_pair("LN1", p2));
1218 tmp << "DROP TABLE IF EXISTS Person", now;
1219 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
1220 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
1221 int count = 0;
1222 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
1223 assertTrue (count == 2);
1224 Person result;
1225 Statement stmt = (tmp << "SELECT * FROM PERSON", into(result), limit(1));
1226 stmt.execute();
1227 assertTrue (result == p1);
1228 assertTrue (!stmt.done());
1229 stmt.execute();
1230 assertTrue (result == p2);
1231 assertTrue (stmt.done());
1232}
1233
1234
1235void SQLiteTest::testLowerLimitFail()
1236{
1237 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1238 std::multimap<std::string, Person> people;
1239 Person p1("LN1", "FN1", "ADDR1", 1);
1240 Person p2("LN2", "FN2", "ADDR2", 2);
1241 people.insert(std::make_pair("LN1", p1));
1242 people.insert(std::make_pair("LN1", p2));
1243 tmp << "DROP TABLE IF EXISTS Person", now;
1244 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
1245 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
1246 int count = 0;
1247 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
1248 assertTrue (count == 2);
1249 Person result;
1250 try
1251 {
1252 tmp << "SELECT * FROM PERSON", into(result), lowerLimit(3), now; // will fail
1253 fail("should fail. not enough data");
1254 }
1255 catch(Poco::Exception&)
1256 {
1257 }
1258}
1259
1260
1261void SQLiteTest::testCombinedLimits()
1262{
1263 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1264 std::multimap<std::string, Person> people;
1265 Person p1("LN1", "FN1", "ADDR1", 1);
1266 Person p2("LN2", "FN2", "ADDR2", 2);
1267 people.insert(std::make_pair("LN1", p1));
1268 people.insert(std::make_pair("LN1", p2));
1269 tmp << "DROP TABLE IF EXISTS Person", now;
1270 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
1271 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
1272
1273 std::string a, b, c;
1274 Statement stmt = (tmp << "SELECT LastName, FirstName, Address FROM Person WHERE Address = 'invalid value'",
1275 into(a), into(b), into(c), limit(1));
1276 assertTrue (!stmt.done() && stmt.execute() == 0);
1277
1278 int count = 0;
1279 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
1280 assertTrue (count == 2);
1281 std::vector <Person> result;
1282 tmp << "SELECT * FROM PERSON", into(result), lowerLimit(2), upperLimit(2), now; // will return 2 objects
1283 assertTrue (result.size() == 2);
1284 assertTrue (result[0] == p1);
1285 assertTrue (result[1] == p2);
1286}
1287
1288
1289
1290void SQLiteTest::testRange()
1291{
1292 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1293 std::multimap<std::string, Person> people;
1294 Person p1("LN1", "FN1", "ADDR1", 1);
1295 Person p2("LN2", "FN2", "ADDR2", 2);
1296 people.insert(std::make_pair("LN1", p1));
1297 people.insert(std::make_pair("LN1", p2));
1298 tmp << "DROP TABLE IF EXISTS Person", now;
1299 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
1300 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
1301 int count = 0;
1302 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
1303 assertTrue (count == 2);
1304 std::vector <Person> result;
1305 tmp << "SELECT * FROM PERSON", into(result), range(2, 2), now; // will return 2 objects
1306 assertTrue (result.size() == 2);
1307 assertTrue (result[0] == p1);
1308 assertTrue (result[1] == p2);
1309}
1310
1311
1312void SQLiteTest::testCombinedIllegalLimits()
1313{
1314 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1315 std::multimap<std::string, Person> people;
1316 Person p1("LN1", "FN1", "ADDR1", 1);
1317 Person p2("LN2", "FN2", "ADDR2", 2);
1318 people.insert(std::make_pair("LN1", p1));
1319 people.insert(std::make_pair("LN1", p2));
1320 tmp << "DROP TABLE IF EXISTS Person", now;
1321 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
1322 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
1323 int count = 0;
1324 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
1325 assertTrue (count == 2);
1326 Person result;
1327 try
1328 {
1329 tmp << "SELECT * FROM PERSON", into(result), lowerLimit(3), upperLimit(2), now;
1330 fail("lower > upper is not allowed");
1331 }
1332 catch(LimitException&)
1333 {
1334 }
1335}
1336
1337
1338
1339void SQLiteTest::testIllegalRange()
1340{
1341 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1342 std::multimap<std::string, Person> people;
1343 Person p1("LN1", "FN1", "ADDR1", 1);
1344 Person p2("LN2", "FN2", "ADDR2", 2);
1345 people.insert(std::make_pair("LN1", p1));
1346 people.insert(std::make_pair("LN1", p2));
1347 tmp << "DROP TABLE IF EXISTS Person", now;
1348 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
1349 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;
1350 int count = 0;
1351 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
1352 assertTrue (count == 2);
1353 Person result;
1354 try
1355 {
1356 tmp << "SELECT * FROM PERSON", into(result), range(3, 2), now;
1357 fail("lower > upper is not allowed");
1358 }
1359 catch(LimitException&)
1360 {
1361 }
1362}
1363
1364
1365void SQLiteTest::testEmptyDB()
1366{
1367 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1368
1369 tmp << "DROP TABLE IF EXISTS Person", now;
1370 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
1371 int count = 0;
1372 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
1373 assertTrue (count == 0);
1374 Person result;
1375 Statement stmt = (tmp << "SELECT * FROM PERSON", into(result), limit(1));
1376 stmt.execute();
1377 assertTrue (result.getFirstName().empty());
1378 assertTrue (stmt.done());
1379}
1380
1381
1382void SQLiteTest::testNonexistingDB()
1383{
1384 try
1385 {
1386 Session tmp (Poco::SQL::SQLite::Connector::KEY, "foo/bar/nonexisting.db");
1387 fail("must fail");
1388 }
1389 catch(...)
1390 {
1391 }
1392}
1393
1394
1395void SQLiteTest::testCLOB()
1396{
1397 std::string lastName("lastname");
1398 std::string firstName("firstname");
1399 std::string address("Address");
1400 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1401 tmp << "DROP TABLE IF EXISTS Person", now;
1402 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Image BLOB)", now;
1403 CLOB img("0123456789", 10);
1404 int count = 0;
1405 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :img)", use(lastName), use(firstName), use(address), use(img), now;
1406 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
1407 assertTrue (count == 1);
1408 CLOB res;
1409 poco_assert (res.size() == 0);
1410
1411 tmp << "SELECT Image FROM Person WHERE LastName == :ln", bind("lastname"), into(res), now;
1412 poco_assert (res == img);
1413
1414 tmp << "DROP TABLE IF EXISTS BlobTest", now;
1415 std::vector<CLOB> resVec;
1416 const int arrSize = 10;
1417 char val[arrSize];
1418 for (int i = 0; i < arrSize; ++i)
1419 {
1420 val[i] = (char) (0x30 + i);
1421 }
1422
1423 for (int i = 0; i < arrSize; ++i)
1424 {
1425 tmp << "CREATE TABLE IF NOT EXISTS BlobTest (idx INTEGER(2), Image BLOB)", now;
1426 val[0] = (char) (0x30 + i);
1427 img.assignRaw(val, arrSize);
1428 tmp << "INSERT INTO BlobTest VALUES(?, ?)", use(i), use(img), now;
1429 }
1430 tmp << "SELECT Image FROM BlobTest", into(resVec), now;
1431 poco_assert (resVec.size() == arrSize);
1432 for (int i = 0; i < arrSize; ++i)
1433 {
1434 poco_assert (*resVec[i].begin() == (char) (0x30 + i));
1435 }
1436}
1437
1438
1439void SQLiteTest::testTuple10()
1440{
1441 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1442 tmp << "DROP TABLE IF EXISTS Tuples", now;
1443 tmp << "CREATE TABLE Tuples "
1444 "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER, int6 INTEGER, "
1445 "int7 INTEGER, int8 INTEGER, int9 INTEGER)", now;
1446
1447 Tuple<int,int,int,int,int,int,int,int,int,int> t(0,1,2,3,4,5,6,7,8,9);
1448
1449 tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?,?,?)", use(t), now;
1450
1451 Tuple<int,int,int,int,int,int,int,int,int,int> ret(-10,-11,-12,-13,-14,-15,-16,-17,-18,-19);
1452 assertTrue (ret != t);
1453 tmp << "SELECT * FROM Tuples", into(ret), now;
1454 assertTrue (ret == t);
1455}
1456
1457
1458void SQLiteTest::testTupleVector10()
1459{
1460 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1461 tmp << "DROP TABLE IF EXISTS Tuples", now;
1462 tmp << "CREATE TABLE Tuples "
1463 "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER, int6 INTEGER, "
1464 "int7 INTEGER, int8 INTEGER, int9 INTEGER)", now;
1465
1466 Tuple<int,int,int,int,int,int,int,int,int,int> t(0,1,2,3,4,5,6,7,8,9);
1467 Tuple<int,int,int,int,int,int,int,int,int,int> t10(10,11,12,13,14,15,16,17,18,19);
1468 Tuple<int,int,int,int,int,int,int,int,int,int> t100(100,101,102,103,104,105,106,107,108,109);
1469 std::vector<Tuple<int,int,int,int,int,int,int,int,int,int> > v;
1470 v.push_back(t);
1471 v.push_back(t10);
1472 v.push_back(t100);
1473
1474 tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?,?,?)", use(v), now;
1475
1476 int count = 0;
1477 tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
1478 assertTrue (v.size() == count);
1479
1480 std::vector<Tuple<int,int,int,int,int,int,int,int,int,int> > ret;
1481 assertTrue (ret != v);
1482 tmp << "SELECT * FROM Tuples", into(ret), now;
1483 assertTrue (ret == v);
1484}
1485
1486
1487void SQLiteTest::testTuple9()
1488{
1489 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1490 tmp << "DROP TABLE IF EXISTS Tuples", now;
1491 tmp << "CREATE TABLE Tuples "
1492 "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER, int6 INTEGER, "
1493 "int7 INTEGER, int8 INTEGER)", now;
1494
1495 Tuple<int,int,int,int,int,int,int,int,int> t(0,1,2,3,4,5,6,7,8);
1496
1497 tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?,?)", use(t), now;
1498
1499 Tuple<int,int,int,int,int,int,int,int,int> ret(-10,-11,-12,-13,-14,-15,-16,-17,-18);
1500 assertTrue (ret != t);
1501 tmp << "SELECT * FROM Tuples", into(ret), now;
1502 assertTrue (ret == t);
1503}
1504
1505
1506void SQLiteTest::testTupleVector9()
1507{
1508 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1509 tmp << "DROP TABLE IF EXISTS Tuples", now;
1510 tmp << "CREATE TABLE Tuples "
1511 "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER, int6 INTEGER, "
1512 "int7 INTEGER, int8 INTEGER)", now;
1513
1514 Tuple<int,int,int,int,int,int,int,int,int> t(0,1,2,3,4,5,6,7,8);
1515 Tuple<int,int,int,int,int,int,int,int,int> t10(10,11,12,13,14,15,16,17,18);
1516 Tuple<int,int,int,int,int,int,int,int,int> t100(100,101,102,103,104,105,106,107,108);
1517 std::vector<Tuple<int,int,int,int,int,int,int,int,int> > v;
1518 v.push_back(t);
1519 v.push_back(t10);
1520 v.push_back(t100);
1521
1522 tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?,?)", use(v), now;
1523
1524 int count = 0;
1525 tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
1526 assertTrue (v.size() == count);
1527
1528 std::vector<Tuple<int,int,int,int,int,int,int,int,int> > ret;
1529 assertTrue (ret != v);
1530 tmp << "SELECT * FROM Tuples", into(ret), now;
1531 assertTrue (ret == v);
1532}
1533
1534
1535void SQLiteTest::testTuple8()
1536{
1537 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1538 tmp << "DROP TABLE IF EXISTS Tuples", now;
1539 tmp << "CREATE TABLE Tuples "
1540 "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER, int6 INTEGER, "
1541 "int7 INTEGER)", now;
1542
1543 Tuple<int,int,int,int,int,int,int,int> t(0,1,2,3,4,5,6,7);
1544
1545 tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?)", use(t), now;
1546
1547 Tuple<int,int,int,int,int,int,int,int> ret(-10,-11,-12,-13,-14,-15,-16,-17);
1548 assertTrue (ret != t);
1549 tmp << "SELECT * FROM Tuples", into(ret), now;
1550 assertTrue (ret == t);
1551}
1552
1553
1554void SQLiteTest::testTupleVector8()
1555{
1556 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1557 tmp << "DROP TABLE IF EXISTS Tuples", now;
1558 tmp << "CREATE TABLE Tuples "
1559 "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER, int6 INTEGER, "
1560 "int7 INTEGER)", now;
1561
1562 Tuple<int,int,int,int,int,int,int,int> t(0,1,2,3,4,5,6,7);
1563 Tuple<int,int,int,int,int,int,int,int> t10(10,11,12,13,14,15,16,17);
1564 Tuple<int,int,int,int,int,int,int,int> t100(100,101,102,103,104,105,106,107);
1565 std::vector<Tuple<int,int,int,int,int,int,int,int> > v;
1566 v.push_back(t);
1567 v.push_back(t10);
1568 v.push_back(t100);
1569
1570 tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?)", use(v), now;
1571
1572 int count = 0;
1573 tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
1574 assertTrue (v.size() == count);
1575
1576 std::vector<Tuple<int,int,int,int,int,int,int,int> > ret;
1577 assertTrue (ret != v);
1578 tmp << "SELECT * FROM Tuples", into(ret), now;
1579 assertTrue (ret == v);
1580}
1581
1582
1583void SQLiteTest::testTuple7()
1584{
1585 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1586 tmp << "DROP TABLE IF EXISTS Tuples", now;
1587 tmp << "CREATE TABLE Tuples "
1588 "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER, int6 INTEGER)", now;
1589
1590 Tuple<int,int,int,int,int,int,int> t(0,1,2,3,4,5,6);
1591
1592 tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?)", use(t), now;
1593
1594 Tuple<int,int,int,int,int,int,int> ret(-10,-11,-12,-13,-14,-15,-16);
1595 assertTrue (ret != t);
1596 tmp << "SELECT * FROM Tuples", into(ret), now;
1597 assertTrue (ret == t);
1598}
1599
1600
1601void SQLiteTest::testTupleVector7()
1602{
1603 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1604 tmp << "DROP TABLE IF EXISTS Tuples", now;
1605 tmp << "CREATE TABLE Tuples "
1606 "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER, int6 INTEGER)", now;
1607
1608 Tuple<int,int,int,int,int,int,int> t(0,1,2,3,4,5,6);
1609 Tuple<int,int,int,int,int,int,int> t10(10,11,12,13,14,15,16);
1610 Tuple<int,int,int,int,int,int,int> t100(100,101,102,103,104,105,106);
1611 std::vector<Tuple<int,int,int,int,int,int,int> > v;
1612 v.push_back(t);
1613 v.push_back(t10);
1614 v.push_back(t100);
1615
1616 tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?)", use(v), now;
1617
1618 int count = 0;
1619 tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
1620 assertTrue (v.size() == count);
1621
1622 std::vector<Tuple<int,int,int,int,int,int,int> > ret;
1623 assertTrue (ret != v);
1624 tmp << "SELECT * FROM Tuples", into(ret), now;
1625 assertTrue (ret == v);
1626}
1627
1628
1629void SQLiteTest::testTuple6()
1630{
1631 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1632 tmp << "DROP TABLE IF EXISTS Tuples", now;
1633 tmp << "CREATE TABLE Tuples "
1634 "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER)", now;
1635
1636 Tuple<int,int,int,int,int,int> t(0,1,2,3,4,5);
1637
1638 tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?)", use(t), now;
1639
1640 Tuple<int,int,int,int,int,int> ret(-10,-11,-12,-13,-14,-15);
1641 assertTrue (ret != t);
1642 tmp << "SELECT * FROM Tuples", into(ret), now;
1643 assertTrue (ret == t);
1644}
1645
1646
1647void SQLiteTest::testTupleVector6()
1648{
1649 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1650 tmp << "DROP TABLE IF EXISTS Tuples", now;
1651 tmp << "CREATE TABLE Tuples "
1652 "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER)", now;
1653
1654 Tuple<int,int,int,int,int,int> t(0,1,2,3,4,5);
1655 Tuple<int,int,int,int,int,int> t10(10,11,12,13,14,15);
1656 Tuple<int,int,int,int,int,int> t100(100,101,102,103,104,105);
1657 std::vector<Tuple<int,int,int,int,int,int> > v;
1658 v.push_back(t);
1659 v.push_back(t10);
1660 v.push_back(t100);
1661
1662 tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?)", use(v), now;
1663
1664 int count = 0;
1665 tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
1666 assertTrue (v.size() == count);
1667
1668 std::vector<Tuple<int,int,int,int,int,int> > ret;
1669 assertTrue (ret != v);
1670 tmp << "SELECT * FROM Tuples", into(ret), now;
1671 assertTrue (ret == v);
1672}
1673
1674
1675void SQLiteTest::testTuple5()
1676{
1677 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1678 tmp << "DROP TABLE IF EXISTS Tuples", now;
1679 tmp << "CREATE TABLE Tuples "
1680 "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER)", now;
1681
1682 Tuple<int,int,int,int,int> t(0,1,2,3,4);
1683
1684 tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?)", use(t), now;
1685
1686 Tuple<int,int,int,int,int> ret(-10,-11,-12,-13,-14);
1687 assertTrue (ret != t);
1688 tmp << "SELECT * FROM Tuples", into(ret), now;
1689 assertTrue (ret == t);
1690}
1691
1692
1693void SQLiteTest::testTupleVector5()
1694{
1695 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1696 tmp << "DROP TABLE IF EXISTS Tuples", now;
1697 tmp << "CREATE TABLE Tuples "
1698 "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER)", now;
1699
1700 Tuple<int,int,int,int,int> t(0,1,2,3,4);
1701 Tuple<int,int,int,int,int> t10(10,11,12,13,14);
1702 Tuple<int,int,int,int,int> t100(100,101,102,103,104);
1703 std::vector<Tuple<int,int,int,int,int> > v;
1704 v.push_back(t);
1705 v.push_back(t10);
1706 v.push_back(t100);
1707
1708 tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?)", use(v), now;
1709
1710 int count = 0;
1711 tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
1712 assertTrue (v.size() == count);
1713
1714 std::vector<Tuple<int,int,int,int,int> > ret;
1715 assertTrue (ret != v);
1716 tmp << "SELECT * FROM Tuples", into(ret), now;
1717 assertTrue (ret == v);
1718}
1719
1720
1721void SQLiteTest::testTuple4()
1722{
1723 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1724 tmp << "DROP TABLE IF EXISTS Tuples", now;
1725 tmp << "CREATE TABLE Tuples "
1726 "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER)", now;
1727
1728 Tuple<int,int,int,int> t(0,1,2,3);
1729
1730 tmp << "INSERT INTO Tuples VALUES (?,?,?,?)", use(t), now;
1731
1732 Tuple<int,int,int,int> ret(-10,-11,-12,-13);
1733 assertTrue (ret != t);
1734 tmp << "SELECT * FROM Tuples", into(ret), now;
1735 assertTrue (ret == t);
1736}
1737
1738
1739void SQLiteTest::testTupleVector4()
1740{
1741 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1742 tmp << "DROP TABLE IF EXISTS Tuples", now;
1743 tmp << "CREATE TABLE Tuples "
1744 "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER)", now;
1745
1746 Tuple<int,int,int,int> t(0,1,2,3);
1747 Tuple<int,int,int,int> t10(10,11,12,13);
1748 Tuple<int,int,int,int> t100(100,101,102,103);
1749 std::vector<Tuple<int,int,int,int> > v;
1750 v.push_back(t);
1751 v.push_back(t10);
1752 v.push_back(t100);
1753
1754 tmp << "INSERT INTO Tuples VALUES (?,?,?,?)", use(v), now;
1755
1756 int count = 0;
1757 tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
1758 assertTrue (v.size() == count);
1759
1760 std::vector<Tuple<int,int,int,int> > ret;
1761 assertTrue (ret != v);
1762 tmp << "SELECT * FROM Tuples", into(ret), now;
1763 assertTrue (ret == v);
1764}
1765
1766
1767void SQLiteTest::testTuple3()
1768{
1769 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1770 tmp << "DROP TABLE IF EXISTS Tuples", now;
1771 tmp << "CREATE TABLE Tuples "
1772 "(int0 INTEGER, int1 INTEGER, int2 INTEGER)", now;
1773
1774 Tuple<int,int,int> t(0,1,2);
1775
1776 tmp << "INSERT INTO Tuples VALUES (?,?,?)", use(t), now;
1777
1778 Tuple<int,int,int> ret(-10,-11,-12);
1779 assertTrue (ret != t);
1780 tmp << "SELECT * FROM Tuples", into(ret), now;
1781 assertTrue (ret == t);
1782}
1783
1784
1785void SQLiteTest::testTupleVector3()
1786{
1787 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1788 tmp << "DROP TABLE IF EXISTS Tuples", now;
1789 tmp << "CREATE TABLE Tuples "
1790 "(int0 INTEGER, int1 INTEGER, int2 INTEGER)", now;
1791
1792 Tuple<int,int,int> t(0,1,2);
1793 Tuple<int,int,int> t10(10,11,12);
1794 Tuple<int,int,int> t100(100,101,102);
1795 std::vector<Tuple<int,int,int> > v;
1796 v.push_back(t);
1797 v.push_back(t10);
1798 v.push_back(t100);
1799
1800 tmp << "INSERT INTO Tuples VALUES (?,?,?)", use(v), now;
1801
1802 int count = 0;
1803 tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
1804 assertTrue (v.size() == count);
1805
1806 std::vector<Tuple<int,int,int> > ret;
1807 assertTrue (ret != v);
1808 tmp << "SELECT * FROM Tuples", into(ret), now;
1809 assertTrue (ret == v);
1810}
1811
1812
1813void SQLiteTest::testTuple2()
1814{
1815 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1816 tmp << "DROP TABLE IF EXISTS Tuples", now;
1817 tmp << "CREATE TABLE Tuples (int0 INTEGER, int1 INTEGER)", now;
1818
1819 Tuple<int,int> t(0,1);
1820
1821 tmp << "INSERT INTO Tuples VALUES (?,?)", use(t), now;
1822
1823 Tuple<int,int> ret(-10,-11);
1824 assertTrue (ret != t);
1825 tmp << "SELECT * FROM Tuples", into(ret), now;
1826 assertTrue (ret == t);
1827}
1828
1829
1830void SQLiteTest::testTupleVector2()
1831{
1832 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1833 tmp << "DROP TABLE IF EXISTS Tuples", now;
1834 tmp << "CREATE TABLE Tuples (int0 INTEGER, int1 INTEGER)", now;
1835
1836 Tuple<int,int> t(0,1);
1837 Tuple<int,int> t10(10,11);
1838 Tuple<int,int> t100(100,101);
1839 std::vector<Tuple<int,int> > v;
1840 v.push_back(t);
1841 v.push_back(t10);
1842 v.push_back(t100);
1843
1844 tmp << "INSERT INTO Tuples VALUES (?,?)", use(v), now;
1845
1846 int count = 0;
1847 tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
1848 assertTrue (v.size() == count);
1849
1850 std::vector<Tuple<int,int> > ret;
1851 assertTrue (ret != v);
1852 tmp << "SELECT * FROM Tuples", into(ret), now;
1853 assertTrue (ret == v);
1854}
1855
1856
1857void SQLiteTest::testTuple1()
1858{
1859 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1860 tmp << "DROP TABLE IF EXISTS Tuples", now;
1861 tmp << "CREATE TABLE Tuples (int0 INTEGER)", now;
1862
1863 Tuple<int> t(0);
1864
1865 tmp << "INSERT INTO Tuples VALUES (?)", use(t), now;
1866
1867 Tuple<int> ret(-10);
1868 assertTrue (ret != t);
1869 tmp << "SELECT * FROM Tuples", into(ret), now;
1870 assertTrue (ret == t);
1871}
1872
1873
1874void SQLiteTest::testTupleVector1()
1875{
1876 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1877 tmp << "DROP TABLE IF EXISTS Tuples", now;
1878 tmp << "CREATE TABLE Tuples (int0 INTEGER)", now;
1879
1880 Tuple<int> t(0);
1881 Tuple<int> t10(10);
1882 Tuple<int> t100(100);
1883 std::vector<Tuple<int> > v;
1884 v.push_back(t);
1885 v.push_back(t10);
1886 v.push_back(t100);
1887
1888 tmp << "INSERT INTO Tuples VALUES (?)", use(v), now;
1889
1890 int count = 0;
1891 tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
1892 assertTrue (v.size() == count);
1893
1894 std::vector<Tuple<int> > ret;
1895 assertTrue (ret != v);
1896 tmp << "SELECT * FROM Tuples", into(ret), now;
1897 assertTrue (ret == v);
1898}
1899
1900
1901void SQLiteTest::testDateTime()
1902{
1903 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1904 tmp << "DROP TABLE IF EXISTS DateTimes", now;
1905 tmp << "CREATE TABLE DateTimes (dt0 DATE)", now;
1906
1907 DateTime dt(1965, 6, 18, 5, 35, 1);
1908 tmp << "INSERT INTO DateTimes VALUES (?)", use(dt), now;
1909
1910 DateTime rdt;
1911 assertTrue (rdt != dt);
1912 tmp << "SELECT * FROM DateTimes", into(rdt), now;
1913 assertTrue (rdt == dt);
1914
1915 tmp << "DELETE FROM DateTimes", now;
1916
1917 Date d(dt);
1918 tmp << "INSERT INTO DateTimes VALUES (?)", use(d), now;
1919
1920 Date rd;
1921 assertTrue (rd != d);
1922 tmp << "SELECT * FROM DateTimes", into(rd), now;
1923 assertTrue (rd == d);
1924
1925 tmp << "DELETE FROM DateTimes", now;
1926
1927 Time t(dt);
1928 tmp << "INSERT INTO DateTimes VALUES (?)", use(t), now;
1929
1930 Time rt;
1931 assertTrue (rt != t);
1932 tmp << "SELECT * FROM DateTimes", into(rt), now;
1933 assertTrue (rt == t);
1934}
1935
1936#if defined(POCO_PTR_IS_64_BIT) && (POCO_PTR_IS_64_BIT == 1)
1937
1938void SQLiteTest::testInternalExtraction()
1939{
1940 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
1941 tmp << "DROP TABLE IF EXISTS Vectors", now;
1942 tmp << "CREATE TABLE Vectors (int0 INTEGER32, flt0 REAL, str0 VARCHAR, int1 INTEGER)", now;
1943
1944 std::vector<Tuple<int, double, std::string, Int64> > v;
1945 v.push_back(Tuple<int, double, std::string, Int64>(1, 1.5, "3", 1));
1946 v.push_back(Tuple<int, double, std::string, Int64>(2, 2.5, "4", 2));
1947 v.push_back(Tuple<int, double, std::string, Int64>(3, 3.5, "5", 3));
1948 v.push_back(Tuple<int, double, std::string, Int64>(4, 4.5, "6", Int64(std::numeric_limits<Int64>::max())));
1949
1950 tmp << "INSERT INTO Vectors VALUES (?,?,?,?)", use(v), now;
1951
1952 Statement stmt = (tmp << "SELECT * FROM Vectors", now);
1953 RecordSet rset(stmt);
1954 assertTrue (4 == rset.columnCount());
1955 assertTrue (4 == rset.rowCount());
1956
1957 RecordSet rset2(rset);
1958 assertTrue (4 == rset2.columnCount());
1959 assertTrue (4 == rset2.rowCount());
1960
1961 Int32 a = rset.value<Int32>(0,2);
1962 assertTrue (3 == a);
1963
1964 Int64 x = rset.value<Int64>(3, 2);
1965 assertTrue (3 == x);
1966
1967 x = rset.value<Int64>(3, 3);
1968 assertTrue (std::numeric_limits<Int64>::max() == x);
1969
1970 int c = rset2.value(0);
1971 assertTrue (1 == c);
1972
1973 Int32 b = rset2.value<Int32>("InT0",2);
1974 assertTrue (3 == b);
1975
1976 double d = rset.value<double>(1,0);
1977 assertTrue (1.5 == d);
1978
1979 std::string s = rset.value<std::string>(2,1);
1980 assertTrue ("4" == s);
1981
1982 typedef std::deque<Int32> IntDeq;
1983
1984 const Column<IntDeq>& col = rset.column<IntDeq>(0);
1985 assertTrue (col[0] == 1);
1986
1987 try { rset.column<IntDeq>(100); fail ("must fail"); }
1988 catch (RangeException&) { }
1989
1990 const Column<IntDeq>& col1 = rset.column<IntDeq>(0);
1991 assertTrue ("int0" == col1.name());
1992 Column<IntDeq>::Iterator it = col1.begin();
1993 Column<IntDeq>::Iterator itEnd = col1.end();
1994 int counter = 1;
1995 for (; it != itEnd; ++it, ++counter)
1996 assertTrue (counter == *it);
1997
1998 rset = (tmp << "SELECT COUNT(*) FROM Vectors", now);
1999 s = rset.value<std::string>(0,0);
2000 assertTrue ("4" == s);
2001
2002 stmt = (tmp << "DELETE FROM Vectors", now);
2003 rset = stmt;
2004
2005 try { rset.column<IntDeq>(0); fail ("must fail"); }
2006 catch (RangeException&) { }
2007}
2008
2009
2010void SQLiteTest::testAny()
2011{
2012 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2013 tmp << "DROP TABLE IF EXISTS Anys", now;
2014 tmp << "CREATE TABLE Anys (int0 INTEGER32, flt0 REAL, str0 VARCHAR, int1 INTEGER)", now;
2015
2016 Any i = Int32(42);
2017 Any f = double(42.5);
2018 Any s = std::string("42");
2019 Any i64 = std::numeric_limits<Int64>::max();
2020
2021 tmp << "INSERT INTO Anys VALUES (?, ?, ?, ?)", use(i), use(f), use(s), use(i64), now;
2022
2023 int count = 0;
2024 tmp << "SELECT COUNT(*) FROM Anys", into(count), now;
2025 assertTrue (1 == count);
2026
2027 i = 0;
2028 f = 0.0;
2029 s = std::string("");
2030 i64 = 0;
2031 tmp << "SELECT * FROM Anys", into(i), into(f), into(s), into(i64), now;
2032 assertTrue (AnyCast<Int32>(i) == 42);
2033 assertTrue (AnyCast<double>(f) == 42.5);
2034 assertTrue (AnyCast<std::string>(s) == "42");
2035 assertTrue (AnyCast<Int64>(i64) == std::numeric_limits<Int64>::max());
2036}
2037
2038#else // !POCO_PTR_IS_64_BIT
2039
2040void SQLiteTest::testInternalExtraction()
2041{
2042 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2043 tmp << "DROP TABLE IF EXISTS Vectors", now;
2044 tmp << "CREATE TABLE Vectors (int0 INTEGER32, flt0 REAL, str0 VARCHAR)", now;
2045
2046 std::vector<Tuple<int, double, std::string> > v;
2047 v.push_back(Tuple<int, double, std::string>(1, 1.5, "3"));
2048 v.push_back(Tuple<int, double, std::string>(2, 2.5, "4"));
2049 v.push_back(Tuple<int, double, std::string>(3, 3.5, "5"));
2050 v.push_back(Tuple<int, double, std::string>(4, 4.5, "6"));
2051
2052 tmp << "INSERT INTO Vectors VALUES (?,?,?)", use(v), now;
2053
2054 Statement stmt = (tmp << "SELECT * FROM Vectors", now);
2055 RecordSet rset(stmt);
2056 assertTrue (3 == rset.columnCount());
2057 assertTrue (4 == rset.rowCount());
2058
2059 RecordSet rset2(rset);
2060 assertTrue (3 == rset2.columnCount());
2061 assertTrue (4 == rset2.rowCount());
2062
2063 Int32 a = rset.value<Int32>(0,2);
2064 assertTrue (3 == a);
2065
2066 int c = rset2.value(0);
2067 assertTrue (1 == c);
2068
2069 Int32 b = rset2.value<Int32>("InT0",2);
2070 assertTrue (3 == b);
2071
2072 double d = rset.value<double>(1,0);
2073 assertTrue (1.5 == d);
2074
2075 std::string s = rset.value<std::string>(2,1);
2076 assertTrue ("4" == s);
2077
2078 typedef std::deque<Int32> IntDeq;
2079
2080 const Column<IntDeq>& col = rset.column<IntDeq>(0);
2081 assertTrue (col[0] == 1);
2082
2083 try { rset.column<IntDeq>(100); fail ("must fail"); }
2084 catch (RangeException&) { }
2085
2086 const Column<IntDeq>& col1 = rset.column<IntDeq>(0);
2087 assertTrue ("int0" == col1.name());
2088 Column<IntDeq>::Iterator it = col1.begin();
2089 Column<IntDeq>::Iterator itEnd = col1.end();
2090 int counter = 1;
2091 for (; it != itEnd; ++it, ++counter)
2092 assertTrue (counter == *it);
2093
2094 rset = (tmp << "SELECT COUNT(*) FROM Vectors", now);
2095 s = rset.value<std::string>(0,0);
2096 assertTrue ("4" == s);
2097
2098 stmt = (tmp << "DELETE FROM Vectors", now);
2099 rset = stmt;
2100
2101 try { rset.column<IntDeq>(0); fail ("must fail"); }
2102 catch (RangeException&) { }
2103}
2104
2105
2106void SQLiteTest::testAny()
2107{
2108 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2109 tmp << "DROP TABLE IF EXISTS Anys", now;
2110 tmp << "CREATE TABLE Anys (int0 INTEGER32, flt0 REAL, str0 VARCHAR)", now;
2111
2112 Any i = Int32(42);
2113 Any f = double(42.5);
2114 Any s = std::string("42");
2115
2116 tmp << "INSERT INTO Anys VALUES (?, ?, ?)", use(i), use(f), use(s), now;
2117
2118 int count = 0;
2119 tmp << "SELECT COUNT(*) FROM Anys", into(count), now;
2120 assertTrue (1 == count);
2121
2122 i = 0;
2123 f = 0.0;
2124 s = std::string("");
2125 tmp << "SELECT * FROM Anys", into(i), into(f), into(s), now;
2126 assertTrue (AnyCast<Int32>(i) == 42);
2127 assertTrue (AnyCast<double>(f) == 42.5);
2128 assertTrue (AnyCast<std::string>(s) == "42");
2129}
2130
2131#endif // POCO_PTR_IS_64_BIT
2132
2133
2134void SQLiteTest::testPrimaryKeyConstraint()
2135{
2136 Session ses (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2137 ses << "DROP TABLE IF EXISTS LogTest", now;
2138 ses << "CREATE TABLE LogTest (Id INTEGER PRIMARY KEY, Time INTEGER, Value INTEGER)", now;
2139 const double value = -200000000000.0;
2140 const Poco::Int64 timeIn = static_cast<Poco::Int64>(22329988776655.0);
2141 int id = 1;
2142
2143 ses.begin();
2144
2145 for(int i = 0; i < 10; i++)
2146 {
2147 try
2148 {
2149 ses << "INSERT INTO LogTest (Id, [Time], Value) VALUES (:id, :time, :value)", use(id), bind(timeIn), bind(value), now; //lint !e1058
2150 if (i > 0)
2151 fail("must fail");
2152 }
2153 catch(Poco::Exception&)
2154 {
2155 if (i == 0) // the very first insert must work
2156 throw;
2157 }
2158 }
2159
2160 ses.commit();
2161}
2162
2163
2164void SQLiteTest::testNullable()
2165{
2166 Session ses (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2167 ses << "DROP TABLE IF EXISTS NullableTest", now;
2168
2169 ses << "CREATE TABLE NullableTest (i INTEGER, r REAL, s VARCHAR, d DATETIME)", now;
2170
2171 ses << "INSERT INTO NullableTest VALUES(:i, :r, :s, :d)", use(null), use(null), use(null), use(null), now;
2172
2173 Nullable<int> i = 1;
2174 Nullable<double> f = 1.5;
2175 Nullable<std::string> s = std::string("abc");
2176 Nullable<DateTime> d = DateTime();
2177
2178 assertTrue (!i.isNull());
2179 assertTrue (!f.isNull());
2180 assertTrue (!s.isNull());
2181 assertTrue (!d.isNull());
2182
2183 ses << "SELECT i, r, s, d FROM NullableTest", into(i), into(f), into(s), into(d), now;
2184
2185 assertTrue (i.isNull());
2186 assertTrue (f.isNull());
2187 assertTrue (s.isNull());
2188 assertTrue (d.isNull());
2189
2190 RecordSet rs(ses, "SELECT * FROM NullableTest");
2191
2192 rs.moveFirst();
2193 assertTrue (rs.isNull("i"));
2194 assertTrue (rs.isNull("r"));
2195 assertTrue (rs.isNull("s"));
2196 assertTrue (rs.isNull("d"));
2197
2198 Var di = 1;
2199 Var df = 1.5;
2200 Var ds = "abc";
2201 Var dd = DateTime();
2202
2203 assertTrue (!di.isEmpty());
2204 assertTrue (!df.isEmpty());
2205 assertTrue (!ds.isEmpty());
2206 assertTrue (!dd.isEmpty());
2207
2208 ses << "SELECT i, r, s, d FROM NullableTest", into(di), into(df), into(ds), into(dd), now;
2209
2210 assertTrue (di.isEmpty());
2211 assertTrue (df.isEmpty());
2212 assertTrue (ds.isEmpty());
2213 assertTrue (dd.isEmpty());
2214}
2215
2216
2217void SQLiteTest::testNulls()
2218{
2219 Session ses (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2220 ses << "DROP TABLE IF EXISTS NullTest", now;
2221
2222 ses << "CREATE TABLE NullTest (i INTEGER NOT NULL)", now;
2223
2224 try
2225 {
2226 ses << "INSERT INTO NullTest VALUES(:i)", use(null), now;
2227 fail ("must fail");
2228 }catch (ConstraintViolationException&) { }
2229
2230 ses << "DROP TABLE IF EXISTS NullTest", now;
2231 ses << "CREATE TABLE NullTest (i INTEGER, r REAL, v VARCHAR)", now;
2232
2233 ses << "INSERT INTO NullTest VALUES(:i, :r, :v)", use(null), use(null), use(null), now;
2234
2235 int cnt = 0;
2236 ses << "SELECT count(*) FROM NullTest", into(cnt), now;
2237 assertTrue (cnt == 1);
2238
2239 std::string none;
2240 ses << "SELECT v FROM NullTest WHERE v = 'abc'", into(none), now;
2241 assertTrue (none.empty());
2242
2243 RecordSet rs(ses, "SELECT i, r, v, null as e FROM NullTest");
2244 rs.moveFirst();
2245 assertTrue (rs.isNull("i"));
2246 assertTrue (rs["i"].isEmpty());
2247 assertTrue (rs.isNull("r"));
2248 assertTrue (rs.isNull("v"));
2249 assertTrue (rs["v"].isEmpty());
2250 assertTrue (rs["e"].isEmpty());
2251
2252 assertTrue (rs[0].isEmpty());
2253 assertTrue (rs[1].isEmpty());
2254 assertTrue (rs[2].isEmpty());
2255 assertTrue (rs[3].isEmpty());
2256
2257 ses << "DROP TABLE IF EXISTS NullTest", now;
2258 ses << "CREATE TABLE NullTest (i INTEGER, r REAL, v VARCHAR)", now;
2259 int i = 1;
2260 double f = 1.2;
2261 std::string s = "123";
2262
2263 ses << "INSERT INTO NullTest (i, r, v) VALUES (:i, :r, :v)", use(i), use(f), use(s), now;
2264 rs = (ses << "SELECT * FROM NullTest", now);
2265 rs.moveFirst();
2266 assertTrue (!rs.isNull("i"));
2267 assertTrue (rs["i"] == 1);
2268 assertTrue (!rs.isNull("v"));
2269 assertTrue (!rs.isNull("r"));
2270 assertTrue (rs["v"] == "123");
2271
2272 ses << "UPDATE NullTest SET v = :n WHERE i == :i", use(null), use(i), now;
2273 i = 2;
2274 f = 3.4;
2275 ses << "INSERT INTO NullTest (i, r, v) VALUES (:i, :r, :v)", use(i), use(null), use(null), now;
2276 rs = (ses << "SELECT i, r, v FROM NullTest ORDER BY i ASC", now);
2277 rs.moveFirst();
2278 assertTrue (!rs.isNull("i"));
2279 assertTrue (rs["i"] == 1);
2280 assertTrue (!rs.isNull("r"));
2281 assertTrue (rs.isNull("v"));
2282 assertTrue (rs["v"].isEmpty());
2283
2284 assertTrue (rs.moveNext());
2285 assertTrue (!rs.isNull("i"));
2286 assertTrue (rs["i"] == 2);
2287 Poco::Int64 i64 = 0;
2288 assertTrue (rs.nvl("i", i64) == 2);
2289 assertTrue (rs.nvl("i", 123) == 2);
2290
2291 assertTrue (rs.isNull("r"));
2292 assertTrue (rs.nvl("r", 123) == 123);
2293 assertTrue (rs.nvl("r", 1.5) == 1.5);
2294
2295 assertTrue (rs.isNull("v"));
2296 assertTrue (rs["v"].isEmpty());
2297 assertTrue (rs.nvl("v", s) == "123");
2298}
2299
2300
2301void SQLiteTest::testRowIterator()
2302{
2303 Session ses (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2304 ses << "DROP TABLE IF EXISTS Vectors", now;
2305 ses << "CREATE TABLE Vectors (int0 INTEGER, flt0 REAL, str0 VARCHAR)", now;
2306
2307 std::vector<Tuple<int, double, std::string> > v;
2308 v.push_back(Tuple<int, double, std::string>(1, 1.5f, "3"));
2309 v.push_back(Tuple<int, double, std::string>(2, 2.5f, "4"));
2310 v.push_back(Tuple<int, double, std::string>(3, 3.5f, "5"));
2311 v.push_back(Tuple<int, double, std::string>(4, 4.5f, "6"));
2312
2313 ses << "INSERT INTO Vectors VALUES (?,?,?)", use(v), now;
2314
2315 RecordSet rset(ses, "SELECT * FROM Vectors");
2316
2317 std::ostringstream osLoop;
2318 RecordSet::ConstIterator it = rset.begin();
2319 RecordSet::ConstIterator end = rset.end();
2320 for (int i = 1; it != end; ++it, ++i)
2321 {
2322 assertTrue (it->get(0) == i);
2323 osLoop << *it;
2324 }
2325 assertTrue (!osLoop.str().empty());
2326
2327 std::ostringstream osCopy;
2328 std::copy(rset.begin(), rset.end(), std::ostream_iterator<Row>(osCopy));
2329 assertTrue (osLoop.str() == osCopy.str());
2330}
2331
2332
2333void SQLiteTest::testRowIteratorLimit()
2334{
2335 Session ses(Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2336 ses << "DROP TABLE IF EXISTS Vectors", now;
2337 ses << "CREATE TABLE Vectors (int0 INTEGER, flt0 REAL, str0 VARCHAR)", now;
2338
2339 std::vector<Tuple<int, double, std::string> > v;
2340 v.push_back(Tuple<int, double, std::string>(1, 1.5f, "3"));
2341 v.push_back(Tuple<int, double, std::string>(2, 2.5f, "4"));
2342 v.push_back(Tuple<int, double, std::string>(3, 3.5f, "5"));
2343 v.push_back(Tuple<int, double, std::string>(4, 4.5f, "6"));
2344
2345 ses << "INSERT INTO Vectors VALUES (?,?,?)", use(v), now;
2346
2347 int count = 0;
2348 Statement stmt = (ses << "select * from Vectors", Poco::SQL::Keywords::limit(1));
2349 while (!stmt.done())
2350 {
2351 stmt.execute(false);
2352 Poco::SQL::RecordSet rs(stmt);
2353 auto rowIt = rs.begin() + count;
2354 assertTrue (++count == rs.rowCount());
2355 assertTrue ((*rowIt)["int0"] == count);
2356
2357 int cnt = 0;
2358 for (rowIt = rs.begin(); rowIt != rs.end(); ++rowIt)
2359 assertTrue ((*rowIt)["int0"] == ++cnt);
2360 }
2361}
2362
2363
2364void SQLiteTest::testRowFilter()
2365{
2366 Session ses(Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2367 ses << "DROP TABLE IF EXISTS Vectors", now;
2368 ses << "CREATE TABLE Vectors (int0 INTEGER, flt0 REAL, str0 VARCHAR)", now;
2369
2370 std::vector<Tuple<int, double, std::string> > v;
2371 v.push_back(Tuple<int, double, std::string>(1, 1.5f, "3"));
2372 v.push_back(Tuple<int, double, std::string>(2, 2.5f, "4"));
2373 v.push_back(Tuple<int, double, std::string>(3, 3.5f, "5"));
2374 v.push_back(Tuple<int, double, std::string>(4, 4.5f, "6"));
2375
2376 ses << "INSERT INTO Vectors VALUES (?,?,?)", use(v), now;
2377
2378 Statement stmt = (ses << "select * from Vectors", now);
2379 RecordSet rset(stmt);
2380 assertTrue (rset.totalRowCount() == 4);
2381 RowFilter::Ptr pRF = new RowFilter(rset);
2382 assertTrue (pRF->isEmpty());
2383 std::string intFldName = "int0";
2384 pRF->add(intFldName, RowFilter::VALUE_EQUAL, 1);
2385 assertTrue (!pRF->isEmpty());
2386
2387 Var da;
2388 try
2389 {
2390 da = rset.value(0, 1);
2391 fail("must fail");
2392 }
2393 catch (InvalidAccessException&)
2394 {
2395 da = rset.value(0, 1, false);
2396 assertTrue (2 == da);
2397 da = rset.value(0, 0);
2398 assertTrue (1 == da);
2399 }
2400
2401 assertTrue (rset.rowCount() == 1);
2402 assertTrue (rset.moveFirst());
2403 assertTrue (1 == rset[intFldName]);
2404 assertTrue (!rset.moveNext());
2405 pRF->add("flt0", RowFilter::VALUE_LESS_THAN_OR_EQUAL, 3.5f);
2406 assertTrue (rset.rowCount() == 3);
2407 assertTrue (rset.moveNext());
2408 assertTrue (2.5 == rset["flt0"]);
2409 assertTrue (rset.moveNext());
2410 assertTrue (3.5 == rset["flt0"]);
2411 assertTrue (!rset.moveNext());
2412 pRF->add("str0", RowFilter::VALUE_EQUAL, 6);
2413 assertTrue (rset.rowCount() == 4);
2414 assertTrue (rset.moveLast());
2415 assertTrue ("6" == rset["str0"]);
2416 pRF->remove("flt0");
2417 assertTrue (rset.rowCount() == 2);
2418 assertTrue (rset.moveFirst());
2419 assertTrue ("3" == rset["str0"]);
2420 assertTrue (rset.moveNext());
2421 assertTrue ("6" == rset["str0"]);
2422 pRF->remove(intFldName);
2423 pRF->remove("str0");
2424 assertTrue (pRF->isEmpty());
2425 pRF->add("str0", "!=", 3);
2426 assertTrue (rset.rowCount() == 3);
2427
2428 RowFilter::Ptr pRF1 = new RowFilter(pRF, RowFilter::OP_AND);
2429 pRF1->add(intFldName, "==", 2);
2430 assertTrue (rset.rowCount() == 1);
2431 pRF1->add(intFldName, "<", 2);
2432 assertTrue (rset.rowCount() == 1);
2433 pRF1->add(intFldName, ">", 3);
2434 assertTrue (rset.rowCount() == 2);
2435 pRF->removeFilter(pRF1);
2436 pRF->remove("str0");
2437 assertTrue (pRF->isEmpty());
2438 assertTrue (rset.rowCount() == 4);
2439}
2440
2441
2442void SQLiteTest::testAsync()
2443{
2444 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2445 tmp << "DROP TABLE IF EXISTS Strings", now;
2446 tmp << "CREATE TABLE IF NOT EXISTS Strings (str INTEGER(10))", now;
2447
2448 int rowCount = 500;
2449 std::vector<int> data(rowCount);
2450 Statement stmt = (tmp << "INSERT INTO Strings VALUES(:str)", use(data));
2451 Statement::Result result = stmt.executeAsync();
2452 assertTrue (!stmt.isAsync());
2453 result.wait();
2454 assertTrue (500 == result.data());
2455
2456 Statement stmt1 = (tmp << "SELECT * FROM Strings", into(data), async, now);
2457 assertTrue (stmt1.isAsync());
2458 assertTrue (stmt1.wait() == rowCount);
2459
2460 stmt1.execute();
2461 try
2462 {
2463 stmt1.execute();
2464 fail ("must fail");
2465 }
2466 catch (InvalidAccessException&)
2467 {
2468 stmt1.wait();
2469 stmt1.execute();
2470 stmt1.wait();
2471 }
2472
2473 stmt = tmp << "SELECT * FROM Strings", into(data), async, now;
2474 assertTrue (stmt.isAsync());
2475 stmt.wait();
2476
2477 assertTrue (stmt.execute() == 0);
2478 assertTrue (stmt.isAsync());
2479 try
2480 {
2481 result = stmt.executeAsync();
2482 fail ("must fail");
2483 }
2484 catch (InvalidAccessException&)
2485 {
2486 stmt.wait();
2487 result = stmt.executeAsync();
2488 }
2489
2490 assertTrue (stmt.wait() == rowCount);
2491 assertTrue (result.data() == rowCount);
2492 stmt.setAsync(false);
2493 assertTrue (!stmt.isAsync());
2494 assertTrue (stmt.execute() == rowCount);
2495
2496 stmt = tmp << "SELECT * FROM Strings", into(data), sync, now;
2497 assertTrue (!stmt.isAsync());
2498 assertTrue (stmt.wait() == 0);
2499 assertTrue (stmt.execute() == rowCount);
2500 result = stmt.executeAsync();
2501 assertTrue (!stmt.isAsync());
2502 result.wait();
2503 assertTrue (result.data() == rowCount);
2504
2505 assertTrue (0 == rowCount % 10);
2506 int step = (int) (rowCount/10);
2507 data.clear();
2508 Statement stmt2 = (tmp << "SELECT * FROM Strings", into(data), async, limit(step));
2509 assertTrue (data.size() == 0);
2510 assertTrue (!stmt2.done());
2511 std::size_t rows = 0;
2512
2513 for (int i = 0; !stmt2.done(); i += step)
2514 {
2515 stmt2.execute();
2516 rows = stmt2.wait();
2517 assertTrue (step == rows);
2518 assertTrue (step + i == data.size());
2519 }
2520 assertTrue (stmt2.done());
2521 assertTrue (rowCount == data.size());
2522
2523 stmt2 = tmp << "SELECT * FROM Strings", reset;
2524 assertTrue (!stmt2.isAsync());
2525 assertTrue ("deque" == stmt2.getStorage());
2526 assertTrue (stmt2.execute() == rowCount);
2527}
2528
2529
2530void SQLiteTest::testDynamicAny()
2531{
2532 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2533 tmp << "DROP TABLE IF EXISTS Anys", now;
2534 tmp << "CREATE TABLE Anys (int0 INTEGER, flt0 REAL, str0 VARCHAR, empty INTEGER)", now;
2535
2536 DynamicAny i = Int32(42);
2537 DynamicAny f = double(42.5);
2538 DynamicAny s = std::string("42");
2539 DynamicAny e;
2540 assertTrue (e.isEmpty());
2541
2542 tmp << "INSERT INTO Anys VALUES (?, ?, ?, null)", use(i), use(f), use(s), now;
2543
2544 int count = 0;
2545 tmp << "SELECT COUNT(*) FROM Anys", into(count), now;
2546 assertTrue (1 == count);
2547
2548 i = 0;
2549 f = 0.0;
2550 s = std::string("");
2551 e = 1;
2552 assertTrue (!e.isEmpty());
2553 tmp << "SELECT * FROM Anys", into(i), into(f), into(s), into(e), now;
2554 assertTrue (42 == i);
2555 assertTrue (42.5 == f);
2556 assertTrue ("42" == s);
2557 assertTrue (e.isEmpty());
2558}
2559
2560
2561void SQLiteTest::testPair()
2562{
2563 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2564 assertTrue (tmp.isConnected());
2565 std::string tableName("Simpsons");
2566 std::pair<std::string, int> junior = std::make_pair("Junior", 12);
2567 std::pair<std::string, int> senior = std::make_pair("Senior", 99);
2568
2569 int count = 0;
2570 std::string result;
2571
2572 tmp << "DROP TABLE IF EXISTS Simpsons", now;
2573 tmp << "CREATE TABLE IF NOT EXISTS Simpsons (LastName VARCHAR(30), Age INTEGER(3))", now;
2574 tmp << "SELECT name FROM sqlite_master WHERE tbl_name=?", use(tableName), into(result), now;
2575 assertTrue (result == tableName);
2576
2577 // these are fine
2578 tmp << "INSERT INTO Simpsons VALUES(?, ?)", use(junior), now;
2579 tmp << "INSERT INTO Simpsons VALUES(?, ?)", useRef(senior), now;
2580
2581 tmp << "SELECT COUNT(*) FROM Simpsons", into(count), now;
2582 assertTrue (2 == count);
2583
2584 std::vector<std::pair<std::string, int> > ret;
2585 tmp << "SELECT * FROM Simpsons", into(ret), range(2,2), now;
2586 assertTrue (ret[0].second == 12 || ret[1].second == 12);
2587 assertTrue (ret[0].second == 99 || ret[1].second == 99);
2588 assertTrue (ret[0].first == "Junior" || ret[1].first == "Junior");
2589 assertTrue (ret[0].first == "Senior" || ret[1].first == "Senior");
2590}
2591
2592
2593void SQLiteTest::testSQLChannel()
2594{
2595 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2596 tmp << "DROP TABLE IF EXISTS T_POCO_LOG", now;
2597 tmp << "CREATE TABLE T_POCO_LOG (Source VARCHAR,"
2598 "Name VARCHAR,"
2599 "ProcessId INTEGER,"
2600 "Thread VARCHAR, "
2601 "ThreadId INTEGER,"
2602 "Priority INTEGER,"
2603 "Text VARCHAR,"
2604 "DateTime DATE)", now;
2605
2606 tmp << "DROP TABLE IF EXISTS T_POCO_LOG_ARCHIVE", now;
2607 tmp << "CREATE TABLE T_POCO_LOG_ARCHIVE (Source VARCHAR,"
2608 "Name VARCHAR,"
2609 "ProcessId INTEGER,"
2610 "Thread VARCHAR, "
2611 "ThreadId INTEGER,"
2612 "Priority INTEGER,"
2613 "Text VARCHAR,"
2614 "DateTime DATE)", now;
2615
2616 AutoPtr<SQLChannel> pChannel = new SQLChannel(Poco::SQL::SQLite::Connector::KEY, "dummy.db", "TestSQLChannel");
2617 pChannel->setProperty("keep", "2 seconds");
2618
2619 Message msgInf("InformationSource", "a Informational async message", Message::PRIO_INFORMATION);
2620 pChannel->log(msgInf);
2621 Message msgWarn("WarningSource", "b Warning async message", Message::PRIO_WARNING);
2622 pChannel->log(msgWarn);
2623 pChannel->wait();
2624
2625 pChannel->setProperty("async", "false");
2626 Message msgInfS("InformationSource", "c Informational sync message", Message::PRIO_INFORMATION);
2627 pChannel->log(msgInfS);
2628 Message msgWarnS("WarningSource", "d Warning sync message", Message::PRIO_WARNING);
2629 pChannel->log(msgWarnS);
2630
2631 RecordSet rs(tmp, "SELECT * FROM T_POCO_LOG ORDER by Text");
2632 assertTrue (4 == rs.rowCount());
2633 assertTrue ("InformationSource" == rs["Source"]);
2634 assertTrue ("a Informational async message" == rs["Text"]);
2635 rs.moveNext();
2636 assertTrue ("WarningSource" == rs["Source"]);
2637 assertTrue ("b Warning async message" == rs["Text"]);
2638 rs.moveNext();
2639 assertTrue ("InformationSource" == rs["Source"]);
2640 assertTrue ("c Informational sync message" == rs["Text"]);
2641 rs.moveNext();
2642 assertTrue ("WarningSource" == rs["Source"]);
2643 assertTrue ("d Warning sync message" == rs["Text"]);
2644
2645 Thread::sleep(3000);
2646
2647 Message msgInfA("InformationSource", "e Informational sync message", Message::PRIO_INFORMATION);
2648 pChannel->log(msgInfA);
2649 Message msgWarnA("WarningSource", "f Warning sync message", Message::PRIO_WARNING);
2650 pChannel->log(msgWarnA);
2651
2652 RecordSet rs1(tmp, "SELECT * FROM T_POCO_LOG_ARCHIVE");
2653 assertTrue (4 == rs1.rowCount());
2654
2655 pChannel->setProperty("keep", "");
2656 assertTrue ("forever" == pChannel->getProperty("keep"));
2657 RecordSet rs2(tmp, "SELECT * FROM T_POCO_LOG ORDER by Text");
2658 assertTrue (2 == rs2.rowCount());
2659 assertTrue ("InformationSource" == rs2["Source"]);
2660 assertTrue ("e Informational sync message" == rs2["Text"]);
2661 rs2.moveNext();
2662 assertTrue ("WarningSource" == rs2["Source"]);
2663 assertTrue ("f Warning sync message" == rs2["Text"]);
2664}
2665
2666
2667void SQLiteTest::testSQLLogger()
2668{
2669 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2670 tmp << "DROP TABLE IF EXISTS T_POCO_LOG", now;
2671 tmp << "CREATE TABLE T_POCO_LOG (Source VARCHAR,"
2672 "Name VARCHAR,"
2673 "ProcessId INTEGER,"
2674 "Thread VARCHAR, "
2675 "ThreadId INTEGER,"
2676 "Priority INTEGER,"
2677 "Text VARCHAR,"
2678 "DateTime DATE)", now;
2679
2680 {
2681 AutoPtr<SQLChannel> pChannel = new SQLChannel(Poco::SQL::SQLite::Connector::KEY, "dummy.db", "TestSQLChannel");
2682 Logger& root = Logger::root();
2683 root.setChannel(pChannel);
2684 root.setLevel(Message::PRIO_INFORMATION);
2685
2686 root.information("Informational message");
2687 root.warning("Warning message");
2688 root.debug("Debug message");
2689 }
2690
2691 Thread::sleep(100);
2692 RecordSet rs(tmp, "SELECT * FROM T_POCO_LOG ORDER by DateTime");
2693 assertTrue (2 == rs.rowCount());
2694 assertTrue ("TestSQLChannel" == rs["Source"]);
2695 assertTrue ("Informational message" == rs["Text"]);
2696 rs.moveNext();
2697 assertTrue ("TestSQLChannel" == rs["Source"]);
2698 assertTrue ("Warning message" == rs["Text"]);
2699}
2700
2701
2702void SQLiteTest::testExternalBindingAndExtraction()
2703{
2704 AbstractExtractionVecVec extractionVec;
2705 AbstractExtractionVec extraction;
2706 AbstractBindingVec binding;
2707
2708 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2709
2710 tmp << "DROP TABLE IF EXISTS Ints", now;
2711 tmp << "CREATE TABLE Ints (int0 INTEGER, int1 INTEGER, int2 INTEGER)", now;
2712
2713 int x = 1, y = 2, z = 3;
2714 binding.push_back(use(x));
2715 binding.push_back(use(y));
2716 binding.push_back(use(z));
2717
2718 tmp << "INSERT INTO Ints VALUES (?,?,?)", use(binding), now;
2719
2720 Poco::Int64 a = 0, b = 0, c = 0;
2721 extraction.push_back(into(a));
2722 extraction.push_back(into(b));
2723 extraction.push_back(into(c));
2724 tmp << "SELECT * FROM Ints", into(extraction), now;
2725 assertTrue (a == x);
2726 assertTrue (b == y);
2727 assertTrue (c == z);
2728
2729 a = 0, b = 0, c = 0;
2730 extractionVec.push_back(extraction);
2731 tmp << "SELECT * FROM Ints", into(extractionVec), now;
2732 assertTrue (a == x);
2733 assertTrue (b == y);
2734 assertTrue (c == z);
2735}
2736
2737
2738void SQLiteTest::testBindingCount()
2739{
2740 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2741
2742 tmp << "DROP TABLE IF EXISTS Ints", now;
2743 tmp << "CREATE TABLE Ints (int0 INTEGER)", now;
2744
2745 int i = 42;
2746 try { tmp << "INSERT INTO Ints VALUES (?)", now; fail("must fail"); }
2747 catch (ParameterCountMismatchException&) { }
2748 tmp << "INSERT INTO Ints VALUES (?)", use(i), now;
2749
2750 i = 0;
2751 try { tmp << "SELECT int0 from Ints where int0 = ?", into(i), now; fail("must fail"); }
2752 catch (ParameterCountMismatchException&) { }
2753 tmp << "SELECT int0 from Ints where int0 = ?", bind(42), into(i), now;
2754 assertTrue (42 == i);
2755
2756 tmp << "DROP TABLE IF EXISTS Ints", now;
2757 tmp << "CREATE TABLE Ints (int0 INTEGER, int1 INTEGER, int2 INTEGER)", now;
2758
2759 try { tmp << "INSERT INTO Ints VALUES (?,?,?)", bind(42), bind(42), now; fail("must fail"); }
2760 catch (ParameterCountMismatchException& ex) { }
2761}
2762
2763
2764void SQLiteTest::testMultipleResults()
2765{
2766 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2767
2768 tmp << "DROP TABLE IF EXISTS Person", now;
2769 tmp << "CREATE TABLE Person (LastName VARCHAR(30),"
2770 "FirstName VARCHAR(30),"
2771 "Address VARCHAR(30),"
2772 "Age INTEGER)", now;
2773
2774 typedef Tuple<std::string, std::string, std::string, Poco::UInt32> Person;
2775 std::vector<Person> people, people2;
2776 people.push_back(Person("Simpson", "Homer", "Springfield", 42));
2777 people.push_back(Person("Simpson", "Bart", "Springfield", 12));
2778 people.push_back(Person("Simpson", "Lisa", "Springfield", 10));
2779
2780 Person pHomer;
2781 int aHomer = 42, aLisa = 10;
2782 Poco::UInt32 aBart = 0;
2783
2784 Poco::UInt32 pos1 = 1;
2785 int pos2 = 2;
2786
2787 Statement stmt(tmp);
2788 stmt << "INSERT INTO Person VALUES (?, ?, ?, ?);"
2789 "SELECT * FROM Person WHERE Age = ?; "
2790 "SELECT Age FROM Person WHERE FirstName = 'Bart'; "
2791 "SELECT * FROM Person WHERE Age = ? OR Age = ? ORDER BY Age;"
2792 , use(people)
2793 , into(pHomer, from(0)), use(aHomer)
2794 , into(aBart, pos1)
2795 , into(people2, from(pos2)), use(aLisa), use(aHomer);
2796
2797 assertTrue (7 == stmt.execute());
2798 assertTrue (Person("Simpson", "Homer", "Springfield", 42) == pHomer);
2799 assertTrue (12 == aBart);
2800 assertTrue (2 == people2.size());
2801 assertTrue (Person("Simpson", "Lisa", "Springfield", 10) == people2[0]);
2802 assertTrue (Person("Simpson", "Homer", "Springfield", 42) == people2[1]);
2803}
2804
2805
2806void SQLiteTest::testReconnect()
2807{
2808 Session session (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2809
2810 session << "DROP TABLE IF EXISTS Person", now;
2811 session << "CREATE TABLE Person (LastName VARCHAR(30),"
2812 "FirstName VARCHAR(30),"
2813 "Address VARCHAR(30),"
2814 "Age INTEGER)", now;
2815
2816 std::string lastName = "lastName";
2817 std::string firstName("firstName");
2818 std::string address("Address");
2819 int age = 133132;
2820 int count = 0;
2821 std::string result;
2822
2823 session << "INSERT INTO PERSON VALUES (?,?,?,?)", use(lastName), use(firstName), use(address), use(age), now;
2824
2825 count = 0;
2826 session << "SELECT COUNT(*) FROM PERSON", into(count), now;
2827 assertTrue (count == 1);
2828
2829 assertTrue (session.isConnected());
2830 session.close();
2831 assertTrue (!session.isConnected());
2832 try
2833 {
2834 session << "SELECT LastName FROM PERSON", into(result), now;
2835 fail ("must fail");
2836 }
2837 catch(NotConnectedException&){ }
2838 assertTrue (!session.isConnected());
2839
2840 session.open();
2841 assertTrue (session.isConnected());
2842 session << "SELECT Age FROM PERSON", into(count), now;
2843 assertTrue (count == age);
2844 assertTrue (session.isConnected());
2845}
2846
2847
2848void SQLiteTest::testThreadModes()
2849{
2850 using namespace Poco::SQL::SQLite;
2851 typedef std::vector<int> ModeVec;
2852
2853 assertTrue (Utility::isThreadSafe());
2854 assertTrue (Utility::getThreadMode() == Utility::THREAD_MODE_SERIAL);
2855
2856 const int datasize = 100;
2857 ModeVec mode;
2858 mode.push_back(Utility::THREAD_MODE_SINGLE);
2859 mode.push_back(Utility::THREAD_MODE_MULTI);
2860 mode.push_back(Utility::THREAD_MODE_SERIAL);
2861
2862 Poco::Stopwatch sw;
2863 ModeVec::iterator it = mode.begin();
2864 ModeVec::iterator end = mode.end();
2865 for (; it != end; ++it)
2866 {
2867 sw.restart();
2868 assertTrue (Utility::setThreadMode(*it));
2869 {
2870 Session tmp (Connector::KEY, "dummy.db");
2871 std::vector<int> iv(datasize);
2872 int count = 0;
2873
2874 tmp << "DROP TABLE IF EXISTS Ints", now;
2875 tmp << "CREATE TABLE IF NOT EXISTS Ints (theInt INTEGER)", now;
2876 Statement stmt((tmp << "INSERT INTO Ints VALUES(?)", use(iv)));
2877 tmp << "SELECT COUNT(*) FROM Ints", into(count), now;
2878 assertTrue (count == 0);
2879 stmt.execute();
2880 tmp << "SELECT COUNT(*) FROM Ints", into(count), now;
2881 assertTrue (count == datasize);
2882 count = 0;
2883 tmp << "SELECT COUNT(*) FROM Ints", into(count), now;
2884 assertTrue (count == datasize);
2885 }
2886 sw.stop();
2887 std::cout << "Mode: " << ((*it == Utility::THREAD_MODE_SINGLE) ? "single,"
2888 :(*it == Utility::THREAD_MODE_MULTI) ? "multi,"
2889 :(*it == Utility::THREAD_MODE_SERIAL) ? "serial,"
2890 : "unknown,") << " Time: " << sw.elapsed() / 1000.0 << " [ms]" << std::endl;
2891 }
2892
2893 assertTrue (Utility::setThreadMode(Utility::THREAD_MODE_SERIAL));
2894 assertTrue (Utility::isThreadSafe());
2895 assertTrue (Utility::getThreadMode() == Utility::THREAD_MODE_SERIAL);
2896}
2897
2898
2899void SQLiteTest::sqliteUpdateCallbackFn(void* pVal, int opCode, const char* pDB, const char* pTable, Poco::Int64 row)
2900{
2901 poco_check_ptr(pVal);
2902 Poco::Int64* pV = reinterpret_cast<Poco::Int64*>(pVal);
2903 if (opCode == Utility::OPERATION_INSERT)
2904 {
2905 poco_assert (*pV == 2);
2906 poco_assert (row == 1);
2907 std::cout << "Inserted " << pDB << '.' << pTable << ", RowID=" << row << std::endl;
2908 ++_insertCounter;
2909 }
2910 else if (opCode == Utility::OPERATION_UPDATE)
2911 {
2912 poco_assert (*pV == 3);
2913 poco_assert (row == 1);
2914 std::cout << "Updated " << pDB << '.' << pTable << ", RowID=" << row << std::endl;
2915 ++_updateCounter;
2916 }
2917 else if (opCode == Utility::OPERATION_DELETE)
2918 {
2919 poco_assert (*pV == 4);
2920 poco_assert (row == 1);
2921 std::cout << "Deleted " << pDB << '.' << pTable << ", RowID=" << row << std::endl;
2922 ++_deleteCounter;
2923 }
2924}
2925
2926
2927void SQLiteTest::testUpdateCallback()
2928{
2929 // will be updated by callback
2930 _insertCounter = 0;
2931 _updateCounter = 0;
2932 _deleteCounter = 0;
2933
2934 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
2935 assertTrue (tmp.isConnected());
2936 Poco::Int64 val = 1;
2937 assertTrue (Utility::registerUpdateHandler(tmp, &sqliteUpdateCallbackFn, &val));
2938
2939 std::string tableName("Person");
2940 std::string lastName("lastname");
2941 std::string firstName("firstname");
2942 std::string address("Address");
2943 int age = 133132;
2944 int count = 0;
2945 std::string result;
2946 tmp << "DROP TABLE IF EXISTS Person", now;
2947 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
2948 tmp << "SELECT name FROM sqlite_master WHERE tbl_name=?", use(tableName), into(result), now;
2949 assertTrue (result == tableName);
2950
2951 // insert
2952 val = 2;
2953 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(lastName), use(firstName), use(address), use(age), now;
2954 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
2955 assertTrue (count == 1);
2956 assertTrue (_insertCounter == 1);
2957 tmp << "SELECT LastName FROM PERSON", into(result), now;
2958 assertTrue (lastName == result);
2959 tmp << "SELECT Age FROM PERSON", into(count), now;
2960 assertTrue (count == age);
2961
2962 // update
2963 val = 3;
2964 tmp << "UPDATE PERSON SET Age = -1", now;
2965 tmp << "SELECT Age FROM PERSON", into(age), now;
2966 assertTrue (-1 == age);
2967 assertTrue (_updateCounter == 1);
2968
2969 // delete
2970 val =4;
2971 tmp << "DELETE FROM Person WHERE Age = -1", now;
2972 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
2973 assertTrue (count == 0);
2974 assertTrue (_deleteCounter == 1);
2975
2976 // disarm callback and do the same drill
2977 assertTrue (Utility::registerUpdateHandler(tmp, (Utility::UpdateCallbackType) 0, &val));
2978
2979 tmp << "DROP TABLE IF EXISTS Person", now;
2980 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
2981 tmp << "SELECT name FROM sqlite_master WHERE tbl_name=?", use(tableName), into(result), now;
2982 assertTrue (result == tableName);
2983
2984 // must remain zero now
2985 _insertCounter = 0;
2986 _updateCounter = 0;
2987 _deleteCounter = 0;
2988
2989 // insert
2990 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(lastName), use(firstName), use(address), use(age), now;
2991 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
2992 assertTrue (count == 1);
2993 assertTrue (_insertCounter == 0);
2994 tmp << "SELECT LastName FROM PERSON", into(result), now;
2995 assertTrue (lastName == result);
2996 tmp << "SELECT Age FROM PERSON", into(count), now;
2997 assertTrue (count == age);
2998
2999 // update
3000 tmp << "UPDATE PERSON SET Age = -1", now;
3001 tmp << "SELECT Age FROM PERSON", into(age), now;
3002 assertTrue (-1 == age);
3003 assertTrue (_updateCounter == 0);
3004
3005 // delete
3006 tmp << "DELETE FROM Person WHERE Age = -1", now;
3007 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
3008 assertTrue (count == 0);
3009 assertTrue (_deleteCounter == 0);
3010
3011 tmp.close();
3012 assertTrue (!tmp.isConnected());
3013}
3014
3015
3016int SQLiteTest::sqliteCommitCallbackFn(void* pVal)
3017{
3018 poco_check_ptr(pVal);
3019 Poco::Int64* pV = reinterpret_cast<Poco::Int64*>(pVal);
3020 poco_assert ((*pV) == 1);
3021 ++(*pV);
3022 return 0;
3023}
3024
3025
3026void SQLiteTest::testCommitCallback()
3027{
3028 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
3029 assertTrue (tmp.isConnected());
3030 Poco::Int64 val = 1;
3031 assertTrue (Utility::registerUpdateHandler(tmp, &sqliteCommitCallbackFn, &val));
3032
3033 std::string tableName("Person");
3034 std::string lastName("lastname");
3035 std::string firstName("firstname");
3036 std::string address("Address");
3037 int age = 133132;
3038 std::string result;
3039 tmp.begin();
3040 tmp << "DROP TABLE IF EXISTS Person", now;
3041 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
3042 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(lastName), use(firstName), use(address), use(age), now;
3043 tmp.commit();
3044 assertTrue (val == 2);
3045
3046 assertTrue (Utility::registerUpdateHandler(tmp, (Utility::CommitCallbackType) 0, &val));
3047 val = 0;
3048 tmp.begin();
3049 tmp << "DROP TABLE IF EXISTS Person", now;
3050 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
3051 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(lastName), use(firstName), use(address), use(age), now;
3052 tmp.commit();
3053 assertTrue (val == 0);
3054
3055}
3056
3057
3058void SQLiteTest::sqliteRollbackCallbackFn(void* pVal)
3059{
3060 poco_check_ptr(pVal);
3061 Poco::Int64* pV = reinterpret_cast<Poco::Int64*>(pVal);
3062 poco_assert ((*pV) == 1);
3063 ++(*pV);
3064}
3065
3066
3067void SQLiteTest::testRollbackCallback()
3068{
3069 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
3070 assertTrue (tmp.isConnected());
3071 Poco::Int64 val = 1;
3072 assertTrue (Utility::registerUpdateHandler(tmp, &sqliteRollbackCallbackFn, &val));
3073
3074 std::string tableName("Person");
3075 std::string lastName("lastname");
3076 std::string firstName("firstname");
3077 std::string address("Address");
3078 int age = 133132;
3079 std::string result;
3080 tmp.begin();
3081 tmp << "DROP TABLE IF EXISTS Person", now;
3082 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
3083 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(lastName), use(firstName), use(address), use(age), now;
3084 tmp.rollback();
3085 assertTrue (val == 2);
3086
3087 assertTrue (Utility::registerUpdateHandler(tmp, (Utility::RollbackCallbackType) 0, &val));
3088 val = 0;
3089 tmp.begin();
3090 tmp << "DROP TABLE IF EXISTS Person", now;
3091 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
3092 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(lastName), use(firstName), use(address), use(age), now;
3093 tmp.rollback();
3094 assertTrue (val == 0);
3095}
3096
3097
3098void SQLiteTest::testNotifier()
3099{
3100 Session session (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
3101 assertTrue (session.isConnected());
3102 session << "DROP TABLE IF EXISTS Person", now;
3103 session << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
3104
3105 Notifier notifier(session);
3106 notifier.insert += delegate(this, &SQLiteTest::onInsert);
3107 notifier.update += delegate(this, &SQLiteTest::onUpdate);
3108 notifier.erase += delegate(this, &SQLiteTest::onDelete);
3109
3110 _insertCounter = 0;
3111 _updateCounter = 0;
3112 _deleteCounter = 0;
3113
3114 session << "INSERT INTO PERSON VALUES('Simpson', 'Bart', 'Springfield', 12)", now;
3115 assertTrue (_insertCounter == 1);
3116 assertTrue (notifier.getRow() == 1);
3117 session << "INSERT INTO PERSON VALUES('Simpson', 'Lisa', 'Springfield', 10)", now;
3118 assertTrue (_insertCounter == 2);
3119 assertTrue (notifier.getRow() == 2);
3120 session << "INSERT INTO PERSON VALUES('Simpson', 'Homer', 'Springfield', 42)", now;
3121 assertTrue (_insertCounter == 3);
3122 assertTrue (notifier.getRow() == 3);
3123
3124 session << "UPDATE PERSON SET Age = 11 WHERE FirstName = 'Bart'", now;
3125 assertTrue (_updateCounter == 1);
3126 assertTrue (notifier.getRow() == 1);
3127 session << "UPDATE PERSON SET Age = 9 WHERE FirstName = 'Lisa'", now;
3128 assertTrue (_updateCounter == 2);
3129 assertTrue (notifier.getRow() == 2);
3130 session << "UPDATE PERSON SET Age = 41 WHERE FirstName = 'Homer'", now;
3131 assertTrue (_updateCounter == 3);
3132 assertTrue (notifier.getRow() == 3);
3133
3134 assertTrue (notifier.getTable() == "Person");
3135
3136 notifier.setRow(0);
3137 // SQLite optimizes DELETE so here we must have
3138 // the WHERE clause to trigger per-row notifications
3139 session << "DELETE FROM PERSON WHERE 1=1", now;
3140 assertTrue (_deleteCounter == 3);
3141 assertTrue (notifier.getRow() == 3);
3142
3143 notifier.insert -= delegate(this, &SQLiteTest::onInsert);
3144 notifier.update -= delegate(this, &SQLiteTest::onUpdate);
3145 notifier.erase -= delegate(this, &SQLiteTest::onDelete);
3146
3147 notifier.disableUpdate();
3148
3149 notifier.setRow(0);
3150 _commitCounter = 0;
3151 notifier.commit += delegate(this, &SQLiteTest::onCommit);
3152 session.begin();
3153 session << "INSERT INTO PERSON VALUES('Simpson', 'Bart', 'Springfield', 12)", now;
3154 session << "INSERT INTO PERSON VALUES('Simpson', 'Lisa', 'Springfield', 10)", now;
3155 session << "INSERT INTO PERSON VALUES('Simpson', 'Homer', 'Springfield', 42)", now;
3156 session.commit();
3157 assertTrue (_commitCounter == 1);
3158 assertTrue (notifier.getRow() == 0);
3159 notifier.commit -= delegate(this, &SQLiteTest::onCommit);
3160
3161 session << "DELETE FROM PERSON", now;
3162
3163 notifier.setRow(0);
3164 _rollbackCounter = 0;
3165 notifier.rollback += delegate(this, &SQLiteTest::onRollback);
3166 session.begin();
3167 session << "INSERT INTO PERSON VALUES('Simpson', 'Bart', 'Springfield', 12)", now;
3168 session << "INSERT INTO PERSON VALUES('Simpson', 'Lisa', 'Springfield', 10)", now;
3169 session << "INSERT INTO PERSON VALUES('Simpson', 'Homer', 'Springfield', 42)", now;
3170 session.rollback();
3171 assertTrue (_rollbackCounter == 1);
3172 assertTrue (notifier.getRow() == 0);
3173 notifier.rollback -= delegate(this, &SQLiteTest::onRollback);
3174}
3175
3176
3177void SQLiteTest::onInsert(const void* pSender)
3178{
3179 Notifier* pN = reinterpret_cast<Notifier*>(const_cast<void*>(pSender));
3180 std::cout << "onInsert, row:" << pN->getRow() << std::endl;
3181 ++_insertCounter;
3182}
3183
3184
3185void SQLiteTest::onUpdate(const void* pSender)
3186{
3187 Notifier* pN = reinterpret_cast<Notifier*>(const_cast<void*>(pSender));
3188 std::cout << "onUpdate, row:" << pN->getRow() << std::endl;
3189 ++_updateCounter;
3190}
3191
3192
3193void SQLiteTest::onDelete(const void* pSender)
3194{
3195 Notifier* pN = reinterpret_cast<Notifier*>(const_cast<void*>(pSender));
3196 std::cout << "onDelete, row:" << pN->getRow() << std::endl;
3197 ++_deleteCounter;
3198}
3199
3200
3201void SQLiteTest::onCommit(const void* pSender)
3202{
3203 Notifier* pN = reinterpret_cast<Notifier*>(const_cast<void*>(pSender));
3204 std::cout << "onCommit, row:" << pN->getRow() << std::endl;
3205 ++_commitCounter;
3206}
3207
3208
3209void SQLiteTest::onRollback(const void* pSender)
3210{
3211 Notifier* pN = reinterpret_cast<Notifier*>(const_cast<void*>(pSender));
3212 std::cout << "onRollback, row:" << pN->getRow() << std::endl;
3213 ++_rollbackCounter;
3214}
3215
3216
3217void SQLiteTest::setTransactionIsolation(Session& session, Poco::UInt32 ti)
3218{
3219 if (session.hasTransactionIsolation(ti))
3220 {
3221 std::string funct = "setTransactionIsolation()";
3222
3223 try
3224 {
3225 Transaction t(session, false);
3226 t.setIsolation(ti);
3227
3228 assertTrue (ti == t.getIsolation());
3229 assertTrue (t.isIsolation(ti));
3230
3231 assertTrue (ti == session.getTransactionIsolation());
3232 assertTrue (session.isTransactionIsolation(ti));
3233 }
3234 catch(Poco::Exception& e){ std::cout << funct << ':' << e.displayText() << std::endl;}
3235 }
3236 else
3237 {
3238 std::cerr << '[' << name() << ']' << " Warning, transaction isolation not supported: ";
3239 switch (ti)
3240 {
3241 case Session::TRANSACTION_READ_COMMITTED:
3242 std::cerr << "READ COMMITTED"; break;
3243 case Session::TRANSACTION_READ_UNCOMMITTED:
3244 std::cerr << "READ UNCOMMITTED"; break;
3245 case Session::TRANSACTION_REPEATABLE_READ:
3246 std::cerr << "REPEATABLE READ"; break;
3247 case Session::TRANSACTION_SERIALIZABLE:
3248 std::cerr << "SERIALIZABLE"; break;
3249 default:
3250 std::cerr << "UNKNOWN"; break;
3251 }
3252 std::cerr << std::endl;
3253 }
3254}
3255
3256
3257void SQLiteTest::testSessionTransaction()
3258{
3259 Session session (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
3260 assertTrue (session.isConnected());
3261
3262 session << "DROP TABLE IF EXISTS Person", now;
3263 session << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
3264
3265 if (!session.canTransact())
3266 {
3267 std::cout << "Session not capable of transactions." << std::endl;
3268 return;
3269 }
3270
3271 Session local (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
3272 assertTrue (local.isConnected());
3273
3274 try
3275 {
3276 local.setFeature("autoCommit", true);
3277 fail ("Setting SQLite auto-commit explicitly must fail!");
3278 }
3279 catch (NotImplementedException&) { }
3280 assertTrue (local.getFeature("autoCommit"));
3281
3282 std::string funct = "transaction()";
3283 std::vector<std::string> lastNames;
3284 std::vector<std::string> firstNames;
3285 std::vector<std::string> addresses;
3286 std::vector<int> ages;
3287 std::string tableName("Person");
3288 lastNames.push_back("LN1");
3289 lastNames.push_back("LN2");
3290 firstNames.push_back("FN1");
3291 firstNames.push_back("FN2");
3292 addresses.push_back("ADDR1");
3293 addresses.push_back("ADDR2");
3294 ages.push_back(1);
3295 ages.push_back(2);
3296 int count = 0, locCount = 0;
3297 std::string result;
3298
3299 setTransactionIsolation(session, Session::TRANSACTION_READ_COMMITTED);
3300
3301 session.begin();
3302 assertTrue (!session.getFeature("autoCommit"));
3303 assertTrue (session.isTransaction());
3304 session << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now;
3305 assertTrue (session.isTransaction());
3306
3307 Statement stmt = (local << "SELECT COUNT(*) FROM Person", into(locCount), async, now);
3308
3309 session << "SELECT COUNT(*) FROM Person", into(count), now;
3310 assertTrue (2 == count);
3311 assertTrue (session.isTransaction());
3312 session.rollback();
3313 assertTrue (!session.isTransaction());
3314 assertTrue (session.getFeature("autoCommit"));
3315
3316 stmt.wait();
3317 assertTrue (0 == locCount);
3318
3319 session << "SELECT count(*) FROM Person", into(count), now;
3320 assertTrue (0 == count);
3321 assertTrue (!session.isTransaction());
3322
3323 session.begin();
3324 session << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now;
3325 assertTrue (session.isTransaction());
3326 assertTrue (!session.getFeature("autoCommit"));
3327
3328 Statement stmt1 = (local << "SELECT COUNT(*) FROM Person", into(locCount), now);
3329 assertTrue (0 == locCount);
3330
3331 session << "SELECT count(*) FROM Person", into(count), now;
3332 assertTrue (2 == count);
3333
3334 session.commit();
3335 assertTrue (!session.isTransaction());
3336 assertTrue (session.getFeature("autoCommit"));
3337
3338 session << "SELECT count(*) FROM Person", into(count), now;
3339 assertTrue (2 == count);
3340
3341 /* TODO: see http://www.sqlite.org/pragma.html#pragma_read_uncommitted
3342 setTransactionIsolation(session, Session::TRANSACTION_READ_UNCOMMITTED);
3343 */
3344
3345 session.close();
3346 assertTrue (!session.isConnected());
3347
3348 local.close();
3349 assertTrue (!local.isConnected());
3350}
3351
3352
3353void SQLiteTest::testTransaction()
3354{
3355 Session session (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
3356 assertTrue (session.isConnected());
3357
3358 session << "DROP TABLE IF EXISTS Person", now;
3359 session << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
3360
3361 if (!session.canTransact())
3362 {
3363 std::cout << "Session not transaction-capable." << std::endl;
3364 return;
3365 }
3366
3367 Session local(Poco::SQL::SQLite::Connector::KEY, "dummy.db");
3368
3369 setTransactionIsolation(session, Session::TRANSACTION_READ_COMMITTED);
3370
3371 std::string funct = "transaction()";
3372 std::vector<std::string> lastNames;
3373 std::vector<std::string> firstNames;
3374 std::vector<std::string> addresses;
3375 std::vector<int> ages;
3376 std::string tableName("Person");
3377 lastNames.push_back("LN1");
3378 lastNames.push_back("LN2");
3379 firstNames.push_back("FN1");
3380 firstNames.push_back("FN2");
3381 addresses.push_back("ADDR1");
3382 addresses.push_back("ADDR2");
3383 ages.push_back(1);
3384 ages.push_back(2);
3385 int count = 0, locCount = 0;
3386 std::string result;
3387
3388 session.setTransactionIsolation(Session::TRANSACTION_READ_COMMITTED);
3389
3390 {
3391 Transaction trans(session);
3392 assertTrue (trans.isActive());
3393 assertTrue (session.isTransaction());
3394
3395 session << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now;
3396
3397 assertTrue (session.isTransaction());
3398 assertTrue (trans.isActive());
3399
3400 session << "SELECT COUNT(*) FROM Person", into(count), now;
3401 assertTrue (2 == count);
3402 assertTrue (session.isTransaction());
3403 assertTrue (trans.isActive());
3404 // no explicit commit, so transaction RAII must roll back here
3405 }
3406 assertTrue (!session.isTransaction());
3407
3408 session << "SELECT count(*) FROM Person", into(count), now;
3409 assertTrue (0 == count);
3410 assertTrue (!session.isTransaction());
3411
3412 {
3413 Transaction trans(session);
3414 session << "INSERT INTO Person VALUES (?,?,?,?)", use(lastNames), use(firstNames), use(addresses), use(ages), now;
3415
3416 Statement stmt1 = (local << "SELECT COUNT(*) FROM Person", into(locCount), now);
3417
3418 assertTrue (session.isTransaction());
3419 assertTrue (trans.isActive());
3420 trans.commit();
3421 assertTrue (!session.isTransaction());
3422 assertTrue (!trans.isActive());
3423 assertTrue (0 == locCount);
3424 }
3425
3426 session << "SELECT count(*) FROM Person", into(count), now;
3427 assertTrue (2 == count);
3428 local << "SELECT count(*) FROM Person", into(count), now;
3429 assertTrue (2 == count);
3430
3431 session << "DELETE FROM Person", now;
3432
3433 std::string sql1 = format("INSERT INTO Person VALUES ('%s','%s','%s',%d)", lastNames[0], firstNames[0], addresses[0], ages[0]);
3434 std::string sql2 = format("INSERT INTO Person VALUES ('%s','%s','%s',%d)", lastNames[1], firstNames[1], addresses[1], ages[1]);
3435 std::vector<std::string> sql;
3436 sql.push_back(sql1);
3437 sql.push_back(sql2);
3438
3439 Transaction trans(session);
3440
3441 trans.execute(sql1, false);
3442 session << "SELECT count(*) FROM Person", into(count), now;
3443 assertTrue (1 == count);
3444 trans.execute(sql2, false);
3445 session << "SELECT count(*) FROM Person", into(count), now;
3446 assertTrue (2 == count);
3447
3448 Statement stmt2 = (local << "SELECT COUNT(*) FROM Person", into(locCount), now);
3449 assertTrue (0 == locCount);
3450
3451 trans.rollback();
3452
3453 session << "SELECT count(*) FROM Person", into(count), now;
3454 assertTrue (0 == count);
3455
3456 trans.execute(sql);
3457
3458 Statement stmt3 = (local << "SELECT COUNT(*) FROM Person", into(locCount), now);
3459 assertTrue (2 == locCount);
3460
3461 session << "SELECT count(*) FROM Person", into(count), now;
3462 assertTrue (2 == count);
3463
3464 session.close();
3465 assertTrue (!session.isConnected());
3466
3467 local.close();
3468 assertTrue (!local.isConnected());
3469}
3470
3471
3472struct TestCommitTransactor
3473{
3474 void operator () (Session& session) const
3475 {
3476 session << "INSERT INTO Person VALUES ('lastName','firstName','address',10)", now;
3477 }
3478};
3479
3480
3481struct TestRollbackTransactor
3482{
3483 void operator () (Session& session) const
3484 {
3485 session << "INSERT INTO Person VALUES ('lastName','firstName','address',10)", now;
3486 throw Poco::Exception("test");
3487 }
3488};
3489
3490
3491void SQLiteTest::testTransactor()
3492{
3493 Session session (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
3494 assertTrue (session.isConnected());
3495
3496 session << "DROP TABLE IF EXISTS Person", now;
3497 session << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
3498
3499 std::string funct = "transaction()";
3500 int count = 0;
3501
3502 assertTrue (session.getFeature("autoCommit"));
3503 session.setTransactionIsolation(Session::TRANSACTION_READ_COMMITTED);
3504
3505 TestCommitTransactor ct;
3506 Transaction t1(session, ct);
3507
3508 session << "SELECT count(*) FROM Person", into(count), now;
3509 assertTrue (1 == count);
3510
3511 session << "DELETE FROM Person", now;
3512 session << "SELECT count(*) FROM Person", into(count), now;
3513 assertTrue (0 == count);
3514
3515 try
3516 {
3517 TestRollbackTransactor rt;
3518 Transaction t(session, rt);
3519 fail ("must fail");
3520 } catch (Poco::Exception&) { }
3521
3522 session << "SELECT count(*) FROM Person", into(count), now;
3523 assertTrue (0 == count);
3524
3525 try
3526 {
3527 TestRollbackTransactor rt;
3528 Transaction t(session);
3529 t.transact(rt);
3530 fail ("must fail");
3531 } catch (Poco::Exception&) { }
3532
3533 session << "SELECT count(*) FROM Person", into(count), now;
3534 assertTrue (0 == count);
3535
3536 try
3537 {
3538 TestRollbackTransactor rt;
3539 Transaction t(session, false);
3540 t.transact(rt);
3541 fail ("must fail");
3542 } catch (Poco::Exception&) { }
3543
3544 session << "SELECT count(*) FROM Person", into(count), now;
3545 assertTrue (0 == count);
3546
3547 try
3548 {
3549 TestRollbackTransactor rt;
3550 Transaction t(session, true);
3551 t.transact(rt);
3552 fail ("must fail");
3553 } catch (Poco::Exception&) { }
3554
3555 session << "SELECT count(*) FROM Person", into(count), now;
3556 assertTrue (0 == count);
3557
3558 session.close();
3559 assertTrue (!session.isConnected());
3560}
3561
3562
3563void SQLiteTest::testFTS3()
3564{
3565#ifdef SQLITE_ENABLE_FTS3
3566 Session session(Poco::SQL::SQLite::Connector::KEY, "dummy.db");
3567 assertTrue (session.isConnected());
3568
3569 session << "DROP TABLE IF EXISTS docs", now;
3570 session << "CREATE VIRTUAL TABLE docs USING fts3()", now;
3571
3572 session << "INSERT INTO docs(docid, content) VALUES(1, 'a database is a software system')", now;
3573 session << "INSERT INTO docs(docid, content) VALUES(2, 'sqlite is a software system')", now;
3574 session << "INSERT INTO docs(docid, content) VALUES(3, 'sqlite is a database')", now;
3575
3576 int docid = 0;
3577 session << "SELECT docid FROM docs WHERE docs MATCH 'sqlite AND database'", into(docid), now;
3578 assertTrue (docid == 3);
3579
3580 docid = 0;
3581 session << "SELECT docid FROM docs WHERE docs MATCH 'database sqlite'", into(docid), now;
3582 assertTrue (docid == 3);
3583
3584 std::vector<int> docids;
3585 session << "SELECT docid FROM docs WHERE docs MATCH 'sqlite OR database' ORDER BY docid",
3586 into(docids), now;
3587 assertTrue (docids.size() == 3);
3588 assertTrue (docids[0] == 1);
3589 assertTrue (docids[1] == 2);
3590 assertTrue (docids[2] == 3);
3591
3592 std::string content;
3593 docid = 0;
3594 session << "SELECT docid, content FROM docs WHERE docs MATCH 'database NOT sqlite'",
3595 into(docid), into(content), now;
3596 assertTrue (docid == 1);
3597 assertTrue (content == "a database is a software system");
3598
3599 docid = 0;
3600 session << "SELECT count(*) FROM docs WHERE docs MATCH 'database and sqlite'", into(docid), now;
3601 assertTrue (docid == 0);
3602#else
3603 std::cout << "SQLite FTS not enabled, test not executed." << std::endl;
3604#endif // SQLITE_ENABLE_FTS3
3605}
3606
3607
3608void SQLiteTest::testJSONRowFormatter()
3609{
3610 Session tmp(Poco::SQL::SQLite::Connector::KEY, "dummy.db");
3611 assertTrue (tmp.isConnected());
3612 std::string lastName("Simpson");
3613 std::string firstName("Bart");
3614 std::string address("Springfield");
3615 int age = 12;
3616
3617 tmp << "DROP TABLE IF EXISTS Simpsons", now;
3618 tmp << "CREATE TABLE IF NOT EXISTS Simpsons (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;
3619
3620 checkJSON("SELECT * FROM Simpsons", "");
3621
3622 tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", use(lastName), use(firstName), use(address), use(age), now;
3623
3624 checkJSON("SELECT * FROM Simpsons",
3625 "{"
3626 "\"names\":[\"LastName\",\"FirstName\",\"Address\",\"Age\"],"
3627 "\"values\":[[\"Simpson\",\"Bart\",\"Springfield\",12]]"
3628 "}");
3629
3630 lastName = "Simpson";
3631 firstName ="Lisa";
3632 address ="Springfield";
3633 age = 10;
3634 tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", use(lastName), use(firstName), use(address), use(age), now;
3635
3636 checkJSON("SELECT * FROM Simpsons",
3637 "{"
3638 "\"names\":[\"LastName\",\"FirstName\",\"Address\",\"Age\"],"
3639 "\"values\":[[\"Simpson\",\"Bart\",\"Springfield\",12],"
3640 "[\"Simpson\",\"Lisa\",\"Springfield\",10]]"
3641 "}");
3642
3643 checkJSON("SELECT * FROM Simpsons",
3644 "{"
3645 "[[\"Simpson\",\"Bart\",\"Springfield\",12],"
3646 "[\"Simpson\",\"Lisa\",\"Springfield\",10]]"
3647 "}", JSONRowFormatter::JSON_FMT_MODE_SMALL);
3648
3649 checkJSON("SELECT * FROM Simpsons",
3650 "{\"count\":2,"
3651 "[[\"Simpson\",\"Bart\",\"Springfield\",12],"
3652 "[\"Simpson\",\"Lisa\",\"Springfield\",10]]"
3653 "}", JSONRowFormatter::JSON_FMT_MODE_ROW_COUNT);
3654
3655 checkJSON("SELECT * FROM Simpsons",
3656 "{"
3657 "\"names\":[\"LastName\",\"FirstName\",\"Address\",\"Age\"],"
3658 "\"values\":[[\"Simpson\",\"Bart\",\"Springfield\",12],"
3659 "[\"Simpson\",\"Lisa\",\"Springfield\",10]]"
3660 "}", JSONRowFormatter::JSON_FMT_MODE_COLUMN_NAMES);
3661
3662 checkJSON("SELECT * FROM Simpsons",
3663 "{\"count\":2,"
3664 "[{\"LastName\":\"Simpson\",\"FirstName\":\"Bart\",\"Address\":\"Springfield\",\"Age\":12},"
3665 "{\"LastName\":\"Simpson\",\"FirstName\":\"Lisa\",\"Address\":\"Springfield\",\"Age\":10}]"
3666 "}", JSONRowFormatter::JSON_FMT_MODE_FULL);
3667
3668}
3669
3670
3671void SQLiteTest::checkJSON(const std::string& sql, const std::string& json, int mode)
3672{
3673 Session tmp(Poco::SQL::SQLite::Connector::KEY, "dummy.db");
3674
3675 JSONRowFormatter jf;
3676 if (mode > 0)
3677 jf.setJSONMode(mode);
3678
3679 Statement stmt = (tmp << sql, format(jf), now);
3680 RecordSet rs(stmt);
3681 std::ostringstream ostr;
3682 ostr << rs;
3683 assertTrue (ostr.str() == json);
3684}
3685
3686
3687void SQLiteTest::setUp()
3688{
3689}
3690
3691
3692void SQLiteTest::tearDown()
3693{
3694}
3695
3696
3697void SQLiteTest::testIncrementVacuum()
3698{
3699 std::string lastName("lastname");
3700 std::string firstName("firstname");
3701 std::string address("Address");
3702
3703 Session tmp (Poco::SQL::SQLite::Connector::KEY, "dummy.db");
3704
3705 tmp << "PRAGMA auto_vacuum = 2", now;
3706 int pragma_mode = 0;
3707
3708 tmp << "PRAGMA auto_vacuum", into(pragma_mode), now;
3709 assertTrue (pragma_mode==2);
3710
3711 tmp << "DROP TABLE IF EXISTS Person", now;
3712 tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Image BLOB)", now;
3713 CLOB img("0123456789", 10);
3714 int count = 0;
3715
3716 for (int index = 0; index < 5000; ++index)
3717 tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :img)", use(lastName), use(firstName), use(address), use(img), now;
3718
3719 tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
3720 assertTrue (count == 5000);
3721
3722 // delete record
3723 Statement stmt0(tmp << "DELETE FROM PERSON");
3724 assertTrue (5000 == stmt0.execute());
3725
3726 tmp << "PRAGMA incremental_vacuum(1024);", now;
3727}
3728
3729void SQLiteTest::testIllegalFilePath()
3730{
3731 try
3732 {
3733 Session tmp (Poco::SQL::SQLite::Connector::KEY, "\\/some\\/illegal\\/path\\/dummy.db", 1);
3734 fail("must fail");
3735 }
3736 catch (ConnectionFailedException&)
3737 {
3738 }
3739}
3740
3741CppUnit::Test* SQLiteTest::suite()
3742{
3743 CppUnit::TestSuite* pSuite = new CppUnit::TestSuite("SQLiteTest");
3744
3745 CppUnit_addTest(pSuite, SQLiteTest, testBinding);
3746 CppUnit_addTest(pSuite, SQLiteTest, testZeroRows);
3747 CppUnit_addTest(pSuite, SQLiteTest, testSimpleAccess);
3748 CppUnit_addTest(pSuite, SQLiteTest, testInMemory);
3749 CppUnit_addTest(pSuite, SQLiteTest, testNullCharPointer);
3750 CppUnit_addTest(pSuite, SQLiteTest, testInsertCharPointer);
3751 CppUnit_addTest(pSuite, SQLiteTest, testInsertCharPointer2);
3752 CppUnit_addTest(pSuite, SQLiteTest, testComplexType);
3753 CppUnit_addTest(pSuite, SQLiteTest, testSimpleAccessVector);
3754 CppUnit_addTest(pSuite, SQLiteTest, testComplexTypeVector);
3755 CppUnit_addTest(pSuite, SQLiteTest, testSharedPtrComplexTypeVector);
3756 CppUnit_addTest(pSuite, SQLiteTest, testInsertVector);
3757 CppUnit_addTest(pSuite, SQLiteTest, testInsertEmptyVector);
3758 CppUnit_addTest(pSuite, SQLiteTest, testAffectedRows);
3759 CppUnit_addTest(pSuite, SQLiteTest, testInsertSingleBulk);
3760 CppUnit_addTest(pSuite, SQLiteTest, testInsertSingleBulkVec);
3761 CppUnit_addTest(pSuite, SQLiteTest, testLimit);
3762 CppUnit_addTest(pSuite, SQLiteTest, testLimitOnce);
3763 CppUnit_addTest(pSuite, SQLiteTest, testLimitPrepare);
3764 CppUnit_addTest(pSuite, SQLiteTest, testLimitZero);
3765 CppUnit_addTest(pSuite, SQLiteTest, testPrepare);
3766 CppUnit_addTest(pSuite, SQLiteTest, testSetSimple);
3767 CppUnit_addTest(pSuite, SQLiteTest, testSetComplex);
3768 CppUnit_addTest(pSuite, SQLiteTest, testSetComplexUnique);
3769 CppUnit_addTest(pSuite, SQLiteTest, testMultiSetSimple);
3770 CppUnit_addTest(pSuite, SQLiteTest, testMultiSetComplex);
3771 CppUnit_addTest(pSuite, SQLiteTest, testMapComplex);
3772 CppUnit_addTest(pSuite, SQLiteTest, testMapComplexUnique);
3773 CppUnit_addTest(pSuite, SQLiteTest, testMultiMapComplex);
3774 CppUnit_addTest(pSuite, SQLiteTest, testSelectIntoSingle);
3775 CppUnit_addTest(pSuite, SQLiteTest, testSelectIntoSingleStep);
3776 CppUnit_addTest(pSuite, SQLiteTest, testSelectIntoSingleFail);
3777 CppUnit_addTest(pSuite, SQLiteTest, testLowerLimitOk);
3778 CppUnit_addTest(pSuite, SQLiteTest, testLowerLimitFail);
3779 CppUnit_addTest(pSuite, SQLiteTest, testCombinedLimits);
3780 CppUnit_addTest(pSuite, SQLiteTest, testCombinedIllegalLimits);
3781 CppUnit_addTest(pSuite, SQLiteTest, testRange);
3782 CppUnit_addTest(pSuite, SQLiteTest, testIllegalRange);
3783 CppUnit_addTest(pSuite, SQLiteTest, testSingleSelect);
3784 CppUnit_addTest(pSuite, SQLiteTest, testEmptyDB);
3785 CppUnit_addTest(pSuite, SQLiteTest, testNonexistingDB);
3786 CppUnit_addTest(pSuite, SQLiteTest, testCLOB);
3787 CppUnit_addTest(pSuite, SQLiteTest, testTuple10);
3788 CppUnit_addTest(pSuite, SQLiteTest, testTupleVector10);
3789 CppUnit_addTest(pSuite, SQLiteTest, testTuple9);
3790 CppUnit_addTest(pSuite, SQLiteTest, testTupleVector9);
3791 CppUnit_addTest(pSuite, SQLiteTest, testTuple8);
3792 CppUnit_addTest(pSuite, SQLiteTest, testTupleVector8);
3793 CppUnit_addTest(pSuite, SQLiteTest, testTuple7);
3794 CppUnit_addTest(pSuite, SQLiteTest, testTupleVector7);
3795 CppUnit_addTest(pSuite, SQLiteTest, testTuple6);
3796 CppUnit_addTest(pSuite, SQLiteTest, testTupleVector6);
3797 CppUnit_addTest(pSuite, SQLiteTest, testTuple5);
3798 CppUnit_addTest(pSuite, SQLiteTest, testTupleVector5);
3799 CppUnit_addTest(pSuite, SQLiteTest, testTuple4);
3800 CppUnit_addTest(pSuite, SQLiteTest, testTupleVector4);
3801 CppUnit_addTest(pSuite, SQLiteTest, testTuple3);
3802 CppUnit_addTest(pSuite, SQLiteTest, testTupleVector3);
3803 CppUnit_addTest(pSuite, SQLiteTest, testTuple2);
3804 CppUnit_addTest(pSuite, SQLiteTest, testTupleVector2);
3805 CppUnit_addTest(pSuite, SQLiteTest, testTuple1);
3806 CppUnit_addTest(pSuite, SQLiteTest, testTupleVector1);
3807 CppUnit_addTest(pSuite, SQLiteTest, testDateTime);
3808 CppUnit_addTest(pSuite, SQLiteTest, testInternalExtraction);
3809 CppUnit_addTest(pSuite, SQLiteTest, testPrimaryKeyConstraint);
3810 CppUnit_addTest(pSuite, SQLiteTest, testNullable);
3811 CppUnit_addTest(pSuite, SQLiteTest, testNulls);
3812 CppUnit_addTest(pSuite, SQLiteTest, testRowIterator);
3813 CppUnit_addTest(pSuite, SQLiteTest, testRowIteratorLimit);
3814 CppUnit_addTest(pSuite, SQLiteTest, testRowFilter);
3815 CppUnit_addTest(pSuite, SQLiteTest, testAsync);
3816 CppUnit_addTest(pSuite, SQLiteTest, testAny);
3817 CppUnit_addTest(pSuite, SQLiteTest, testDynamicAny);
3818 CppUnit_addTest(pSuite, SQLiteTest, testSQLChannel);
3819 CppUnit_addTest(pSuite, SQLiteTest, testSQLLogger);
3820 CppUnit_addTest(pSuite, SQLiteTest, testExternalBindingAndExtraction);
3821 CppUnit_addTest(pSuite, SQLiteTest, testBindingCount);
3822 CppUnit_addTest(pSuite, SQLiteTest, testMultipleResults);
3823 CppUnit_addTest(pSuite, SQLiteTest, testPair);
3824 CppUnit_addTest(pSuite, SQLiteTest, testReconnect);
3825 CppUnit_addTest(pSuite, SQLiteTest, testThreadModes);
3826 CppUnit_addTest(pSuite, SQLiteTest, testUpdateCallback);
3827 CppUnit_addTest(pSuite, SQLiteTest, testCommitCallback);
3828 CppUnit_addTest(pSuite, SQLiteTest, testRollbackCallback);
3829 CppUnit_addTest(pSuite, SQLiteTest, testNotifier);
3830 CppUnit_addTest(pSuite, SQLiteTest, testSessionTransaction);
3831 CppUnit_addTest(pSuite, SQLiteTest, testTransaction);
3832 CppUnit_addTest(pSuite, SQLiteTest, testTransactor);
3833 CppUnit_addTest(pSuite, SQLiteTest, testFTS3);
3834 CppUnit_addTest(pSuite, SQLiteTest, testJSONRowFormatter);
3835 CppUnit_addTest(pSuite, SQLiteTest, testIllegalFilePath);
3836//
3837// FIXME dimanikulin
3838// CppUnit_addTest(pSuite, SQLiteTest, testIncrementVacuum);
3839//
3840 return pSuite;
3841}
3842