//

// SQLiteTest.cpp

//

// $Id: //poco/Main/Data/SQLite/testsuite/src/SQLiteTest.cpp#7 $

//

// Copyright (c) 2006, Applied Informatics Software Engineering GmbH.

// and Contributors.

//

// SPDX-License-Identifier: BSL-1.0

//

#include "SQLiteTest.h"

#include "CppUnit/TestCaller.h"

#include "CppUnit/TestSuite.h"

#include "Poco/Data/Date.h"

#include "Poco/Data/Time.h"

#include "Poco/Data/LOB.h"

#include "Poco/Data/Statement.h"

#include "Poco/Data/RecordSet.h"

#include "Poco/Data/JSONRowFormatter.h"

#include "Poco/Data/SQLChannel.h"

#include "Poco/Data/SessionFactory.h"

#include "Poco/Data/SQLite/Connector.h"

#include "Poco/Data/SQLite/Utility.h"

#include "Poco/Data/SQLite/Notifier.h"

#include "Poco/Dynamic/Var.h"

#include "Poco/Data/TypeHandler.h"

#include "Poco/Nullable.h"

#include "Poco/Data/Transaction.h"

#include "Poco/Data/DataException.h"

#include "Poco/Data/SQLite/SQLiteException.h"

#include "Poco/Tuple.h"

#include "Poco/Any.h"

#include "Poco/SharedPtr.h"

#include "Poco/DynamicAny.h"

#include "Poco/DateTime.h"

#include "Poco/Logger.h"

#include "Poco/Message.h"

#include "Poco/Thread.h"

#include "Poco/AutoPtr.h"

#include "Poco/Exception.h"

#include "Poco/RefCountedObject.h"

#include "Poco/Stopwatch.h"

#include "Poco/Delegate.h"

#include <iostream>

using namespace Poco::Data::Keywords;

using Poco::Data::Session;

using Poco::Data::Statement;

using Poco::Data::RecordSet;

using Poco::Data::JSONRowFormatter;

using Poco::Data::Column;

using Poco::Data::Row;

using Poco::Data::SQLChannel;

using Poco::Data::LimitException;

using Poco::Data::CLOB;

using Poco::Data::Date;

using Poco::Data::Time;

using Poco::Data::Transaction;

using Poco::Data::AbstractExtractionVec;

using Poco::Data::AbstractExtractionVecVec;

using Poco::Data::AbstractBindingVec;

using Poco::Data::NotConnectedException;

using Poco::Data::SQLite::Notifier;

using Poco::Nullable;

using Poco::Tuple;

using Poco::Any;

using Poco::AnyCast;

using Poco::DynamicAny;

using Poco::DateTime;

using Poco::Logger;

using Poco::Message;

using Poco::AutoPtr;

using Poco::Thread;

using Poco::format;

using Poco::InvalidAccessException;

using Poco::RangeException;

using Poco::BadCastException;

using Poco::NotFoundException;

using Poco::NullPointerException;

using Poco::TimeoutException;

using Poco::NotImplementedException;

using Poco::Data::SQLite::ConstraintViolationException;

using Poco::Data::SQLite::ParameterCountMismatchException;

using Poco::Int32;

using Poco::Dynamic::Var;

using Poco::Data::SQLite::Utility;

using Poco::delegate;

class Person

{

public:

Person(){_age = 0;}

Person(const std::string& ln, const std::string& fn, const std::string& adr, int a):_lastName(ln), _firstName(fn), _address(adr), _age(a)

{

}

bool operator==(const Person& other) const

{

return _lastName == other._lastName && _firstName == other._firstName && _address == other._address && _age == other._age;

}

bool operator < (const Person& p) const

{

if (_age < p._age)

return true;

if (_lastName < p._lastName)

return true;

if (_firstName < p._firstName)

return true;

return (_address < p._address);

}

const std::string& operator () () const

/// This method is required so we can extract data to a map!

{

// we choose the lastName as examplary key

return _lastName;

}

const std::string& getLastName() const

{

return _lastName;

}

void setLastName(const std::string& lastName)

{

_lastName = lastName;

}

const std::string& getFirstName() const

{

return _firstName;

}

void setFirstName(const std::string& firstName)

{

_firstName = firstName;

}

const std::string& getAddress() const

{

return _address;

}

void setAddress(const std::string& address)

{

_address = address;

}

const int& getAge() const

{

return _age;

}

void setAge(const int& age)

{

_age = age;

}

private:

std::string _lastName;

std::string _firstName;

std::string _address;

int _age;

};

namespace Poco {

namespace Data {

template <>

class TypeHandler<Person>

{

public:

static void bind(std::size_t pos, const Person& obj, AbstractBinder::Ptr pBinder, AbstractBinder::Direction dir)

{

// the table is defined as Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))

poco_assert_dbg (!pBinder.isNull());

pBinder->bind(pos++, obj.getLastName(), dir);

pBinder->bind(pos++, obj.getFirstName(), dir);

pBinder->bind(pos++, obj.getAddress(), dir);

pBinder->bind(pos++, obj.getAge(), dir);

}

static void prepare(std::size_t pos, const Person& obj, AbstractPreparator::Ptr pPrepare)

{

// no-op (SQLite is prepare-less connector)

}

static std::size_t size()

{

return 4;

}

static void extract(std::size_t pos, Person& obj, const Person& defVal, AbstractExtractor::Ptr pExt)

{

poco_assert_dbg (!pExt.isNull());

std::string lastName;

std::string firstName;

std::string address;

int age;

if (pExt->extract(pos++, lastName))

obj.setLastName(lastName);

else

obj.setLastName(defVal.getLastName());

if (pExt->extract(pos++, firstName))

obj.setFirstName(firstName);

else

obj.setFirstName(defVal.getFirstName());

if (pExt->extract(pos++, address))

obj.setAddress(address);

else

obj.setAddress(defVal.getAddress());

if (pExt->extract(pos++, age))

obj.setAge(age);

else

obj.setAge(defVal.getAge());

}

private:

TypeHandler();

~TypeHandler();

TypeHandler(const TypeHandler&);

TypeHandler& operator=(const TypeHandler&);

};

} } // namespace Poco::Data

int SQLiteTest::_insertCounter;

int SQLiteTest::_updateCounter;

int SQLiteTest::_deleteCounter;

SQLiteTest::SQLiteTest(const std::string& name): CppUnit::TestCase(name)

{

}

SQLiteTest::~SQLiteTest()

{

}

void SQLiteTest::testBinding()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

assert (tmp.isConnected());

std::string tableName("Simpsons");

std::string lastName("Simpson");

std::string firstName("Bart");

std::string address("Springfield");

int age = 12;

std::string& rLastName(lastName);

std::string& rFirstName(firstName);

std::string& rAddress(address);

int& rAge = age;

const std::string& crLastName(lastName);

const std::string& crFirstName(firstName);

const std::string& crAddress(address);

const int& crAge = age;

int count = 0;

std::string result;

tmp << "DROP TABLE IF EXISTS Simpsons", now;

tmp << "CREATE TABLE IF NOT EXISTS Simpsons (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;

tmp << "SELECT name FROM sqlite_master WHERE tbl_name=?", use(tableName), into(result), now;

assert (result == tableName);

// following should not compile:

//tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", use("Simpson"), use("Bart"), use("Springfield"), use(age), now;

//tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", use(lastName), use(firstName), use(address), use(12), now;

//tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", useRef(lastName), useRef(firstName), useRef(address), useRef(12), now;

tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", useRef("Simpson"), useRef("Bart"), useRef("Springfield"), useRef(age), now;

tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", use(rLastName), use(rFirstName), use(rAddress), use(rAge), now;

tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", useRef(crLastName), useRef(crFirstName), useRef(crAddress), useRef(crAge), now;

tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", bind("Simpson"), bind("Bart"), bind("Springfield"), bind(12), now;

tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", bind(rLastName), bind(rFirstName), bind(rAddress), bind(rAge), now;

tmp << "INSERT INTO Simpsons VALUES(?, ?, ?, ?)", bind(crLastName), bind(crFirstName), bind(crAddress), bind(crAge), now;

tmp << "SELECT COUNT(*) FROM Simpsons", into(count), now;

assert (6 == count);

}

void SQLiteTest::testZeroRows()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

tmp << "DROP TABLE IF EXISTS ZeroTest", now;

tmp << "CREATE TABLE IF NOT EXISTS ZeroTest (zt INTEGER(3))", now;

Statement stmt = (tmp << "SELECT * FROM ZeroTest");

assert(0 == stmt.execute());

}

void SQLiteTest::testSimpleAccess()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

assert (tmp.isConnected());

std::string tableName("Person");

std::string lastName("lastname");

std::string firstName("firstname");

std::string address("Address");

int age = 133132;

int count = 0;

std::string result;

tmp << "DROP TABLE IF EXISTS Person", now;

tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;

tmp << "SELECT name FROM sqlite_master WHERE tbl_name=?", use(tableName), into(result), now;

assert (result == tableName);

tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(lastName), use(firstName), use(address), use(age), now;

tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;

assert (count == 1);

tmp << "SELECT LastName FROM PERSON", into(result), now;

assert (lastName == result);

tmp << "SELECT Age FROM PERSON", into(count), now;

assert (count == age);

tmp << "UPDATE PERSON SET Age = -1", now;

tmp << "SELECT Age FROM PERSON", into(age), now;

assert (-1 == age);

tmp.close();

assert (!tmp.isConnected());

}

void SQLiteTest::testInMemory()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

assert (tmp.isConnected());

std::string tableName("Person");

std::string lastName("lastname");

std::string firstName("firstname");

std::string address("Address");

int age = 133132;

int count = 0;

std::string result;

tmp << "DROP TABLE IF EXISTS Person", now;

tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;

tmp << "SELECT name FROM sqlite_master WHERE tbl_name=?", use(tableName), into(result), now;

assert (result == tableName);

tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(lastName), use(firstName), use(address), use(age), now;

// load db from file to memory

Session mem (Poco::Data::SQLite::Connector::KEY, ":memory:");

assert (Poco::Data::SQLite::Utility::fileToMemory(mem, "dummy.db"));

mem << "SELECT COUNT(*) FROM PERSON", into(count), now;

assert (count == 1);

mem << "SELECT LastName FROM PERSON", into(result), now;

assert (lastName == result);

mem << "SELECT Age FROM PERSON", into(count), now;

assert (count == age);

mem << "UPDATE PERSON SET Age = -1", now;

mem << "SELECT Age FROM PERSON", into(age), now;

assert (-1 == age);

// save db from memory to file on the disk

Session dsk (Poco::Data::SQLite::Connector::KEY, "dsk.db");

assert (Poco::Data::SQLite::Utility::memoryToFile("dsk.db", mem));

dsk << "SELECT COUNT(*) FROM PERSON", into(count), now;

assert (count == 1);

dsk << "SELECT LastName FROM PERSON", into(result), now;

assert (lastName == result);

dsk << "SELECT Age FROM PERSON", into(count), now;

assert (count == age);

dsk << "UPDATE PERSON SET Age = -1", now;

dsk << "SELECT Age FROM PERSON", into(age), now;

assert (-1 == age);

tmp.close();

mem.close();

dsk.close();

assert (!tmp.isConnected());

assert (!mem.isConnected());

assert (!dsk.isConnected());

}

void SQLiteTest::testNullCharPointer()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

std::string lastName("lastname");

int age = 100;

int count = 100;

std::string result;

tmp << "DROP TABLE IF EXISTS Person", now;

tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;

tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)",

bind(lastName),

bind("firstname"),

bind("Address"),

bind(0), now;

tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;

assert (count == 1);

tmp << "SELECT LastName FROM PERSON", into(result), now;

assert (lastName == result);

tmp << "SELECT Age FROM PERSON", into(age), now;

assert (0 == age);

try

{

const char* pc = 0;

tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)",

bind("lastname"),

bind("firstname"),

bind("Address"), bind(pc), now;

fail ("must fail");

} catch (NullPointerException&) { }

tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;

assert (count == 1);

tmp << "SELECT LastName FROM PERSON", into(result), now;

assert (lastName == result);

tmp << "SELECT Age FROM PERSON", into(age), now;

assert (0 == age);

}

void SQLiteTest::testInsertCharPointer()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

std::string tableName("Person");

std::string lastName("lastname");

std::string firstName("firstname");

std::string address("Address");

int age = 133132;

int count = 0;

std::string result;

tmp << "DROP TABLE IF EXISTS Person", now;

tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;

const char* pc = 0;

try

{

tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", bind(pc), now;

fail ("must fail");

} catch (NullPointerException&) { }

pc = (const char*) std::calloc(9, sizeof(char));

poco_check_ptr (pc);

std::strncpy((char*) pc, "lastname", 8);

Statement stmt = (tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)",

bind(pc),

bind("firstname"),

bind("Address"),

bind(133132));

std::free((void*) pc); pc = 0;

assert (1 == stmt.execute());

tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;

assert (count == 1);

tmp << "SELECT LastName FROM PERSON", into(result), now;

assert (lastName == result);

tmp << "SELECT Age FROM PERSON", into(count), now;

assert (count == age);

}

void SQLiteTest::testInsertCharPointer2()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

std::string tableName("Person");

std::string lastName("lastname");

std::string firstName("firstname");

std::string address("Address");

int age = 133132;

int count = 0;

std::string result;

tmp << "DROP TABLE IF EXISTS Person", now;

tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;

tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)",

bind("lastname"),

bind("firstname"),

bind("Address"),

bind(133132), now;

tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;

assert (count == 1);

Statement stmt1 = (tmp << "SELECT LastName FROM PERSON", into(result));

stmt1.execute();

assert (lastName == result);

count = 0;

Statement stmt2 = (tmp << "SELECT Age FROM PERSON", into(count));

stmt2.execute();

assert (count == age);

}

void SQLiteTest::testComplexType()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

Person p1("LN1", "FN1", "ADDR1", 1);

Person p2("LN2", "FN2", "ADDR2", 2);

tmp << "DROP TABLE IF EXISTS Person", now;

tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;

tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(p1), now;

tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(p2), now;

int count = 0;

tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;

assert (count == 2);

Person c1;

Person c2;

tmp << "SELECT * FROM PERSON WHERE LASTNAME = :ln", into(c1), useRef(p1.getLastName()), now;

assert (c1 == p1);

tmp << "DROP TABLE IF EXISTS Person", now;

tmp << "CREATE TABLE IF NOT EXISTS Person (LastName1 VARCHAR(30), FirstName1 VARCHAR, Address1 VARCHAR, Age1 INTEGER(3),"

"LastName2 VARCHAR(30), FirstName2 VARCHAR, Address2 VARCHAR, Age2 INTEGER(3))", now;

Tuple<Person,Person> t(p1,p2);

tmp << "INSERT INTO PERSON VALUES(:ln1, :fn1, :ad1, :age1, :ln2, :fn2, :ad2, :age2)", use(t), now;

Tuple<Person,Person> ret;

assert (ret != t);

tmp << "SELECT * FROM PERSON", into(ret), now;

assert (ret == t);

}

void SQLiteTest::testSimpleAccessVector()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

std::vector<std::string> lastNames;

std::vector<std::string> firstNames;

std::vector<std::string> addresses;

std::vector<int> ages;

std::string tableName("Person");

lastNames.push_back("LN1");

lastNames.push_back("LN2");

firstNames.push_back("FN1");

firstNames.push_back("FN2");

addresses.push_back("ADDR1");

addresses.push_back("ADDR2");

ages.push_back(1);

ages.push_back(2);

int count = 0;

std::string result;

tmp << "DROP TABLE IF EXISTS Person", now;

tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;

tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(lastNames), use(firstNames), use(addresses), use(ages), now;

tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;

assert (count == 2);

std::vector<std::string> lastNamesR;

std::vector<std::string> firstNamesR;

std::vector<std::string> addressesR;

std::vector<int> agesR;

tmp << "SELECT * FROM PERSON", into(lastNamesR), into(firstNamesR), into(addressesR), into(agesR), now;

assert (ages == agesR);

assert (lastNames == lastNamesR);

assert (firstNames == firstNamesR);

assert (addresses == addressesR);

}

void SQLiteTest::testComplexTypeVector()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

std::vector<Person> people;

people.push_back(Person("LN1", "FN1", "ADDR1", 1));

people.push_back(Person("LN2", "FN2", "ADDR2", 2));

tmp << "DROP TABLE IF EXISTS Person", now;

tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;

tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;

int count = 0;

tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;

assert (count == 2);

std::vector<Person> result;

tmp << "SELECT * FROM PERSON", into(result), now;

assert (result == people);

}

void SQLiteTest::testSharedPtrComplexTypeVector()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

std::vector<Poco::SharedPtr<Person> > people;

people.push_back(new Person("LN1", "FN1", "ADDR1", 1));

people.push_back(new Person("LN2", "FN2", "ADDR2", 2));

tmp << "DROP TABLE IF EXISTS Person", now;

tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;

tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;

int count = 0;

tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;

assert (count == 2);

std::vector<Poco::SharedPtr<Person> > result;

tmp << "SELECT * FROM PERSON", into(result), now;

assert (*result[0] == *people[0]);

assert (*result[1] == *people[1]);

}

void SQLiteTest::testInsertVector()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

std::vector<std::string> str;

str.push_back("s1");

str.push_back("s2");

str.push_back("s3");

str.push_back("s3");

int count = 100;

tmp << "DROP TABLE IF EXISTS Strings", now;

tmp << "CREATE TABLE IF NOT EXISTS Strings (str VARCHAR(30))", now;

{

Statement stmt((tmp << "INSERT INTO Strings VALUES(:str)", use(str)));

tmp << "SELECT COUNT(*) FROM Strings", into(count), now;

assert (count == 0);

stmt.execute();

tmp << "SELECT COUNT(*) FROM Strings", into(count), now;

assert (count == 4);

}

count = 0;

tmp << "SELECT COUNT(*) FROM Strings", into(count), now;

assert (count == 4);

}

void SQLiteTest::testInsertEmptyVector()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

std::vector<std::string> str;

tmp << "DROP TABLE IF EXISTS Strings", now;

tmp << "CREATE TABLE IF NOT EXISTS Strings (str VARCHAR(30))", now;

try

{

tmp << "INSERT INTO Strings VALUES(:str)", use(str), now;

fail("empty collectons should not work");

}

catch (Poco::Exception&)

{

}

}

void SQLiteTest::testAffectedRows()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

std::vector<std::string> str;

str.push_back("s1");

str.push_back("s2");

str.push_back("s3");

str.push_back("s3");

int count = 100;

tmp << "DROP TABLE IF EXISTS Strings", now;

tmp << "CREATE TABLE IF NOT EXISTS Strings (str VARCHAR(30))", now;

Statement stmt((tmp << "INSERT INTO Strings VALUES(:str)", use(str)));

count = -1;

tmp << "SELECT COUNT(*) FROM Strings", into(count), now;

assert (count == 0);

assert (4 == stmt.execute());

tmp << "SELECT COUNT(*) FROM Strings", into(count), now;

assert (count == 4);

Statement stmt0(tmp << "DELETE FROM Strings");

assert (4 == stmt0.execute());

tmp << "SELECT COUNT(*) FROM Strings", into(count), now;

assert (count == 0);

Statement stmt1((tmp << "SELECT * FROM Strings"));

tmp << "SELECT COUNT(*) FROM Strings", into(count), now;

assert (count == 0);

assert (0 == stmt1.execute());

Statement stmt2((tmp << "INSERT INTO Strings VALUES(:str)", use(str)));

count = -1;

tmp << "SELECT COUNT(*) FROM Strings", into(count), now;

assert (count == 0);

assert (4 == stmt2.execute());

tmp << "SELECT COUNT(*) FROM Strings", into(count), now;

assert (count == 4);

Statement stmt3(tmp << "UPDATE Strings SET str = 's4' WHERE str = 's3'");

assert (2 == stmt3.execute());

Statement stmt4(tmp << "DELETE FROM Strings WHERE str = 's1'");

assert (1 == stmt4.execute());

Statement stmt5(tmp << "DELETE FROM Strings WHERE str = 'bad value'");

assert (0 == stmt5.execute());

Statement stmt6(tmp << "DELETE FROM Strings");

assert (3 == stmt6.execute());

}

void SQLiteTest::testInsertSingleBulk()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

tmp << "DROP TABLE IF EXISTS Strings", now;

tmp << "CREATE TABLE IF NOT EXISTS Strings (str INTEGER(10))", now;

std::size_t x = 0;

Statement stmt((tmp << "INSERT INTO Strings VALUES(:str)", use(x)));

for (std::size_t i = 0; x < 100; ++x)

{

i = stmt.execute();

assert (1 == i);

}

int count = 0;

tmp << "SELECT COUNT(*) FROM Strings", into(count), now;

assert (count == 100);

tmp << "SELECT SUM(str) FROM Strings", into(count), now;

assert (count == ((0+99)*100/2));

}

void SQLiteTest::testInsertSingleBulkVec()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

tmp << "DROP TABLE IF EXISTS Strings", now;

tmp << "CREATE TABLE IF NOT EXISTS Strings (str INTEGER(10))", now;

std::vector<int> data;

data.push_back(0);

data.push_back(1);

Statement stmt((tmp << "INSERT INTO Strings VALUES(:str)", use(data)));

for (int x = 0; x < 100; x += 2)

{

data[0] = x;

data[1] = x+1;

stmt.execute();

}

int count = 0;

tmp << "SELECT COUNT(*) FROM Strings", into(count), now;

assert (count == 100);

tmp << "SELECT SUM(str) FROM Strings", into(count), now;

assert (count == ((0+99)*100/2));

}

void SQLiteTest::testLimit()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

tmp << "DROP TABLE IF EXISTS Strings", now;

tmp << "CREATE TABLE IF NOT EXISTS Strings (str INTEGER(10))", now;

std::vector<int> data;

for (int x = 0; x < 100; ++x)

{

data.push_back(x);

}

tmp << "INSERT INTO Strings VALUES(:str)", use(data), now;

std::vector<int> retData;

tmp << "SELECT * FROM Strings", into(retData), limit(50), now;

assert (retData.size() == 50);

for (int x = 0; x < 50; ++x)

{

assert(data[x] == retData[x]);

}

}

void SQLiteTest::testLimitZero()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

tmp << "DROP TABLE IF EXISTS Strings", now;

tmp << "CREATE TABLE IF NOT EXISTS Strings (str INTEGER(10))", now;

std::vector<int> data;

for (int x = 0; x < 100; ++x)

{

data.push_back(x);

}

tmp << "INSERT INTO Strings VALUES(:str)", use(data), now;

std::vector<int> retData;

tmp << "SELECT * FROM Strings", into(retData), limit(0), now; // stupid test, but at least we shouldn't crash

assert (retData.size() == 0);

}

void SQLiteTest::testLimitOnce()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

tmp << "DROP TABLE IF EXISTS Strings", now;

tmp << "CREATE TABLE IF NOT EXISTS Strings (str INTEGER(10))", now;

std::vector<int> data;

for (int x = 0; x < 101; ++x)

{

data.push_back(x);

}

tmp << "INSERT INTO Strings VALUES(:str)", use(data), now;

std::vector<int> retData;

Statement stmt = (tmp << "SELECT * FROM Strings", into(retData), limit(50), now);

assert (!stmt.done());

assert (retData.size() == 50);

stmt.execute();

assert (!stmt.done());

assert (retData.size() == 100);

stmt.execute();

assert (stmt.done());

assert (retData.size() == 101);

for (int x = 0; x < 101; ++x)

{

assert(data[x] == retData[x]);

}

}

void SQLiteTest::testLimitPrepare()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

tmp << "DROP TABLE IF EXISTS Strings", now;

tmp << "CREATE TABLE IF NOT EXISTS Strings (str INTEGER(10))", now;

std::vector<int> data;

for (int x = 0; x < 100; ++x)

{

data.push_back(x);

}

Statement stmtIns = (tmp << "INSERT INTO Strings VALUES(:str)", use(data));

assert (100 == stmtIns.execute());

std::vector<int> retData;

Statement stmt = (tmp << "SELECT * FROM Strings", into(retData), limit(50));

assert (retData.size() == 0);

assert (!stmt.done());

std::size_t rows = stmt.execute();

assert (50 == rows);

assert (!stmt.done());

assert (retData.size() == 50);

rows = stmt.execute();

assert (50 == rows);

assert (stmt.done());

assert (retData.size() == 100);

rows = stmt.execute(); // will restart execution!

assert (50 == rows);

assert (!stmt.done());

assert (retData.size() == 150);

for (int x = 0; x < 150; ++x)

{

assert(data[x%100] == retData[x]);

}

}

void SQLiteTest::testPrepare()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

tmp << "DROP TABLE IF EXISTS Strings", now;

tmp << "CREATE TABLE IF NOT EXISTS Strings (str INTEGER(10))", now;

std::vector<int> data;

for (int x = 0; x < 100; x += 2)

{

data.push_back(x);

}

{

Statement stmt((tmp << "INSERT INTO Strings VALUES(:str)", use(data)));

}

// stmt should not have been executed when destroyed

int count = 100;

tmp << "SELECT COUNT(*) FROM Strings", into(count), now;

assert (count == 0);

}

void SQLiteTest::testSetSimple()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

std::set<std::string> lastNames;

std::set<std::string> firstNames;

std::set<std::string> addresses;

std::set<int> ages;

std::string tableName("Person");

lastNames.insert("LN1");

lastNames.insert("LN2");

firstNames.insert("FN1");

firstNames.insert("FN2");

addresses.insert("ADDR1");

addresses.insert("ADDR2");

ages.insert(1);

ages.insert(2);

int count = 0;

std::string result;

tmp << "DROP TABLE IF EXISTS Person", now;

tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;

tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(lastNames), use(firstNames), use(addresses), use(ages), now;

tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;

assert (count == 2);

std::set<std::string> lastNamesR;

std::set<std::string> firstNamesR;

std::set<std::string> addressesR;

std::set<int> agesR;

tmp << "SELECT * FROM PERSON", into(lastNamesR), into(firstNamesR), into(addressesR), into(agesR), now;

assert (ages == agesR);

assert (lastNames == lastNamesR);

assert (firstNames == firstNamesR);

assert (addresses == addressesR);

}

void SQLiteTest::testSetComplex()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

std::set<Person> people;

people.insert(Person("LN1", "FN1", "ADDR1", 1));

people.insert(Person("LN2", "FN2", "ADDR2", 2));

tmp << "DROP TABLE IF EXISTS Person", now;

tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;

tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;

int count = 0;

tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;

assert (count == 2);

std::set<Person> result;

tmp << "SELECT * FROM PERSON", into(result), now;

assert (result == people);

}

void SQLiteTest::testSetComplexUnique()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

std::vector<Person> people;

Person p1("LN1", "FN1", "ADDR1", 1);

people.push_back(p1);

people.push_back(p1);

people.push_back(p1);

people.push_back(p1);

Person p2("LN2", "FN2", "ADDR2", 2);

people.push_back(p2);

tmp << "DROP TABLE IF EXISTS Person", now;

tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;

tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(people), now;

int count = 0;

tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;

assert (count == 5);

std::set<Person> result;

tmp << "SELECT * FROM PERSON", into(result), now;

assert (result.size() == 2);

assert (*result.begin() == p1);

assert (*++result.begin() == p2);

}

void SQLiteTest::testMultiSetSimple()

{

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

std::multiset<std::string> lastNames;

std::multiset<std::string> firstNames;

std::multiset<std::string> addresses;

std::multiset<int> ages;

std::string tableName("Person");

lastNames.insert("LN1");

lastNames.insert("LN2");

firstNames.insert("FN1");

firstNames.insert("FN2");

addresses.insert("ADDR1");

addresses.insert("ADDR2");

ages.insert(1);

ages.insert(2);

int count = 0;

std::string result;

tmp << "DROP TABLE IF EXISTS Person", now;

tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))", now;

tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :age)", use(lastNames), use(firstNames), use(addresses), use(ages), now;

tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;

assert (count == 2);

std::multiset<std::string> lastNamesR;

std::multiset<std::string> firstNamesR;

std::multiset<std::string> addressesR;