Logo Search packages:      
Sourcecode: poco version File versions  Download package

SQLiteTest.cpp
//
// SQLiteTest.cpp
//
// $Id: //poco/1.3/Data/SQLite/testsuite/src/SQLiteTest.cpp#6 $
//
// Copyright (c) 2006, Applied Informatics Software Engineering GmbH.
// and Contributors.
//
// Permission is hereby granted, free of charge, to any person or organization
// obtaining a copy of the software and accompanying documentation covered by
// this license (the "Software") to use, reproduce, display, distribute,
// execute, and transmit the Software, and to prepare derivative works of the
// Software, and to permit third-parties to whom the Software is furnished to
// do so, all subject to the following:
// 
// The copyright notices in the Software and this entire statement, including
// the above license grant, this restriction and the following disclaimer,
// must be included in all copies of the Software, in whole or in part, and
// all derivative works of the Software, unless such copies or derivative
// works are solely in the form of machine-executable object code generated by
// a source language processor.
// 
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE, TITLE AND NON-INFRINGEMENT. IN NO EVENT
// SHALL THE COPYRIGHT HOLDERS OR ANYONE DISTRIBUTING THE SOFTWARE BE LIABLE
// FOR ANY DAMAGES OR OTHER LIABILITY, WHETHER IN CONTRACT, TORT OR OTHERWISE,
// ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
// DEALINGS IN THE SOFTWARE.
//


#include "SQLiteTest.h"
#include "CppUnit/TestCaller.h"
#include "CppUnit/TestSuite.h"
#include "Poco/Data/Common.h"
#include "Poco/Data/BLOB.h"
#include "Poco/Data/Statement.h"
#include "Poco/Data/RecordSet.h"
#include "Poco/Data/SQLite/Connector.h"
#include "Poco/Tuple.h"
#include "Poco/Any.h"
#include "Poco/Exception.h"
#include <iostream>
#include "Poco/File.h"
#include "Poco/Stopwatch.h"
#include "Poco/Data/SQLite/SQLiteException.h"


using namespace Poco::Data;
using Poco::Tuple;
using Poco::Any;
using Poco::AnyCast;
using Poco::InvalidAccessException;
using Poco::RangeException;
using Poco::BadCastException;
using Poco::Data::SQLite::ParameterCountMismatchException;


struct Person
{
      std::string lastName;
      std::string firstName;
      std::string address;
      int age;
      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);
      }

00086       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;
      }
};


namespace Poco {
namespace Data {


template <>
class TypeHandler<Person>
{
public:
      static void bind(std::size_t pos, const Person& obj, AbstractBinder* pBinder)
      {
            // the table is defined as Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))
            poco_assert_dbg (pBinder != 0);
            pBinder->bind(pos++, obj.lastName);
            pBinder->bind(pos++, obj.firstName);
            pBinder->bind(pos++, obj.address);
            pBinder->bind(pos++, obj.age);
      }

      static void prepare(std::size_t pos, const Person& obj, AbstractPreparation* pPrepare)
      {
            // the table is defined as Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Age INTEGER(3))
            poco_assert_dbg (pPrepare != 0);
            pPrepare->prepare(pos++, obj.lastName);
            pPrepare->prepare(pos++, obj.firstName);
            pPrepare->prepare(pos++, obj.address);
            pPrepare->prepare(pos++, obj.age);
      }

      static std::size_t size()
      {
            return 4;
      }

      static void extract(std::size_t pos, Person& obj, const Person& defVal, AbstractExtractor* pExt)
      {
            poco_assert_dbg (pExt != 0);
            std::string lastName;
            std::string firstName;
            std::string address;
            if (!pExt->extract(pos++, obj.lastName))
                  obj.lastName = defVal.lastName;
            if (!pExt->extract(pos++, obj.firstName))
                  obj.firstName = defVal.firstName;
            if (!pExt->extract(pos++, obj.address))
                  obj.address = defVal.address;
            if (!pExt->extract(pos++, obj.age))
                  obj.age = defVal.age;
      }

private:
      TypeHandler();
      ~TypeHandler();
      TypeHandler(const TypeHandler&);
      TypeHandler& operator=(const TypeHandler&);
};


} } // namespace Poco::Data


SQLiteTest::SQLiteTest(const std::string& name): CppUnit::TestCase(name)
{
      SQLite::Connector::registerConnector();
}


SQLiteTest::~SQLiteTest()
{
      SQLite::Connector::unregisterConnector();
}


void SQLiteTest::testSimpleAccess()
{
      Session tmp (SessionFactory::instance().create(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.close();
      assert (!tmp.isConnected());
}


void SQLiteTest::testComplexType()
{
      Session tmp (SessionFactory::instance().create(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), use(p1.lastName), 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 (SessionFactory::instance().create(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 (SessionFactory::instance().create(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::testInsertVector()
{
      Session tmp (SessionFactory::instance().create(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 (SessionFactory::instance().create(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::testInsertSingleBulk()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Strings", now;
      tmp << "CREATE TABLE IF NOT EXISTS Strings (str INTEGER(10))", now;
      int x = 0;
      Statement stmt((tmp << "INSERT INTO Strings VALUES(:str)", use(x)));

      for (x = 0; x < 100; ++x)
      {
            int i = stmt.execute();
            assert (i == 0);
      }
      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 (SessionFactory::instance().create(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 (SessionFactory::instance().create(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 (SessionFactory::instance().create(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 (SessionFactory::instance().create(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 (SessionFactory::instance().create(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;
      Statement stmt = (tmp << "SELECT * FROM Strings", into(retData), limit(50));
      assert (retData.size() == 0);
      assert (!stmt.done());
      stmt.execute();
      assert (!stmt.done());
      assert (retData.size() == 50);
      stmt.execute();
      assert (stmt.done());
      assert (retData.size() == 100);
      stmt.execute(); // will restart execution!
      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 (SessionFactory::instance().create(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 (SessionFactory::instance().create(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 (SessionFactory::instance().create(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 (SessionFactory::instance().create(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 (SessionFactory::instance().create(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;
      std::multiset<int> agesR;
      tmp << "SELECT * FROM PERSON", into(lastNamesR), into(firstNamesR), into(addressesR), into(agesR), now;
      assert (ages.size() == agesR.size());
      assert (lastNames.size() == lastNamesR.size());
      assert (firstNames.size() == firstNamesR.size());
      assert (addresses.size() == addressesR.size());
}


void SQLiteTest::testMultiSetComplex()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      std::multiset<Person> people;
      Person p1("LN1", "FN1", "ADDR1", 1);
      people.insert(p1);
      people.insert(p1);
      people.insert(p1);
      people.insert(p1);
      Person p2("LN2", "FN2", "ADDR2", 2);
      people.insert(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::multiset<Person> result;
      tmp << "SELECT * FROM PERSON", into(result), now;
      assert (result.size() == people.size());
}


void SQLiteTest::testMapComplex()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      std::map<std::string, Person> people;
      Person p1("LN1", "FN1", "ADDR1", 1);
      Person p2("LN2", "FN2", "ADDR2", 2);
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN2", 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 == 2);

      std::map<std::string, Person> result;
      tmp << "SELECT * FROM PERSON", into(result), now;
      assert (result == people);
}


void SQLiteTest::testMapComplexUnique()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      std::multimap<std::string, Person> people;
      Person p1("LN1", "FN1", "ADDR1", 1);
      Person p2("LN2", "FN2", "ADDR2", 2);
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN2", 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::map<std::string, Person> result;
      tmp << "SELECT * FROM PERSON", into(result), now;
      assert (result.size() == 2);
}


void SQLiteTest::testMultiMapComplex()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      std::multimap<std::string, Person> people;
      Person p1("LN1", "FN1", "ADDR1", 1);
      Person p2("LN2", "FN2", "ADDR2", 2);
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN2", 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::multimap<std::string, Person> result;
      tmp << "SELECT * FROM PERSON", into(result), now;
      assert (result.size() == people.size());
}


void SQLiteTest::testSelectIntoSingle()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      std::multimap<std::string, Person> people;
      Person p1("LN1", "FN1", "ADDR1", 1);
      Person p2("LN2", "FN2", "ADDR2", 2);
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN1", 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 == 2);
      Person result;
      tmp << "SELECT * FROM PERSON", into(result), limit(1), now; // will return 1 object into one single result
      assert (result == p1);
}


void SQLiteTest::testSelectIntoSingleStep()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      std::multimap<std::string, Person> people;
      Person p1("LN1", "FN1", "ADDR1", 1);
      Person p2("LN2", "FN2", "ADDR2", 2);
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN1", 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 == 2);
      Person result;
      Statement stmt = (tmp << "SELECT * FROM PERSON", into(result), limit(1)); 
      stmt.execute();
      assert (result == p1);
      assert (!stmt.done());
      stmt.execute();
      assert (result == p2);
      assert (stmt.done());
}


void SQLiteTest::testSelectIntoSingleFail()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      std::multimap<std::string, Person> people;
      Person p1("LN1", "FN1", "ADDR1", 1);
      Person p2("LN2", "FN2", "ADDR2", 2);
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN1", 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), limit(2, true), now;
      assert (count == 2);
      Person result;
      try
      {
            tmp << "SELECT * FROM PERSON", into(result), limit(1, true), now; // will fail now
            fail("hardLimit is set: must fail");
      }
      catch(Poco::Data::LimitException&)
      {
      }
}


void SQLiteTest::testLowerLimitOk()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      std::multimap<std::string, Person> people;
      Person p1("LN1", "FN1", "ADDR1", 1);
      Person p2("LN2", "FN2", "ADDR2", 2);
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN1", 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 == 2);
      Person result;
      try
      {
            tmp << "SELECT * FROM PERSON", into(result), lowerLimit(2), now; // will return 2 objects into one single result but only room for one!
            fail("Not enough space for results");
      }
      catch(Poco::Exception&)
      {
      }
}


void SQLiteTest::testSingleSelect()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      std::multimap<std::string, Person> people;
      Person p1("LN1", "FN1", "ADDR1", 1);
      Person p2("LN2", "FN2", "ADDR2", 2);
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN1", 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 == 2);
      Person result;
      Statement stmt = (tmp << "SELECT * FROM PERSON", into(result), limit(1));
      stmt.execute();
      assert (result == p1);
      assert (!stmt.done());
      stmt.execute();
      assert (result == p2);
      assert (stmt.done());
}


void SQLiteTest::testLowerLimitFail()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      std::multimap<std::string, Person> people;
      Person p1("LN1", "FN1", "ADDR1", 1);
      Person p2("LN2", "FN2", "ADDR2", 2);
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN1", 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 == 2);
      Person result;
      try
      {
            tmp << "SELECT * FROM PERSON", into(result), lowerLimit(3), now; // will fail
            fail("should fail. not enough data");
      }
      catch(Poco::Exception&)
      {
      }
}


void SQLiteTest::testCombinedLimits()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      std::multimap<std::string, Person> people;
      Person p1("LN1", "FN1", "ADDR1", 1);
      Person p2("LN2", "FN2", "ADDR2", 2);
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN1", 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 == 2);
      std::vector <Person> result;
      tmp << "SELECT * FROM PERSON", into(result), lowerLimit(2), upperLimit(2), now; // will return 2 objects
      assert (result.size() == 2);
      assert (result[0] == p1);
      assert (result[1] == p2);
}



void SQLiteTest::testRange()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      std::multimap<std::string, Person> people;
      Person p1("LN1", "FN1", "ADDR1", 1);
      Person p2("LN2", "FN2", "ADDR2", 2);
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN1", 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 == 2);
      std::vector <Person> result;
      tmp << "SELECT * FROM PERSON", into(result), range(2, 2), now; // will return 2 objects
      assert (result.size() == 2);
      assert (result[0] == p1);
      assert (result[1] == p2);
}


void SQLiteTest::testCombinedIllegalLimits()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      std::multimap<std::string, Person> people;
      Person p1("LN1", "FN1", "ADDR1", 1);
      Person p2("LN2", "FN2", "ADDR2", 2);
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN1", 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 == 2);
      Person result;
      try
      {
            tmp << "SELECT * FROM PERSON", into(result), lowerLimit(3), upperLimit(2), now;
            fail("lower > upper is not allowed");
      }
      catch(LimitException&)
      {
      }
}



void SQLiteTest::testIllegalRange()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      std::multimap<std::string, Person> people;
      Person p1("LN1", "FN1", "ADDR1", 1);
      Person p2("LN2", "FN2", "ADDR2", 2);
      people.insert(std::make_pair("LN1", p1));
      people.insert(std::make_pair("LN1", 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 == 2);
      Person result;
      try
      {
            tmp << "SELECT * FROM PERSON", into(result), range(3, 2), now;
            fail("lower > upper is not allowed");
      }
      catch(LimitException&)
      {
      }
}


void SQLiteTest::testEmptyDB()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      
      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;
      int count = 0;
      tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
      assert (count == 0);
      Person result;
      Statement stmt = (tmp << "SELECT * FROM PERSON", into(result), limit(1));
      stmt.execute();
      assert (result.firstName.empty());
      assert (stmt.done());
}


void SQLiteTest::testBLOB()
{
      std::string lastName("lastname");
      std::string firstName("firstname");
      std::string address("Address");
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Person", now;
      tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Image BLOB)", now;
      BLOB img("0123456789", 10);
      int count = 0;
      tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :img)", use(lastName), use(firstName), use(address), use(img), now;
      tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
      assert (count == 1);
      BLOB res;
      poco_assert (res.size() == 0);
      tmp << "SELECT Image FROM Person WHERE LastName == :ln", use("lastname"), into(res), now;
      poco_assert (res == img);
}


void SQLiteTest::testBLOBStmt()
{
      // the following test will fail becuase we use a temporary object as parameter to use
      /*
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Person", now;
      tmp << "CREATE TABLE IF NOT EXISTS Person (LastName VARCHAR(30), FirstName VARCHAR, Address VARCHAR, Image BLOB)", now;
      BLOB img("0123456789", 10);
      int count = 0;
      Statement ins = (tmp << "INSERT INTO PERSON VALUES(:ln, :fn, :ad, :img)", use("lastname"), use("firstname"), use("Address"), use(BLOB("0123456789", 10)));
      ins.execute();
      tmp << "SELECT COUNT(*) FROM PERSON", into(count), now;
      assert (count == 1);
      BLOB res;
      poco_assert (res.size() == 0);
      Statement stmt = (tmp << "SELECT Image FROM Person WHERE LastName == :ln", use("lastname"), into(res));
      stmt.execute();
      poco_assert (res == img);
      */
}


void SQLiteTest::testTuple10()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples "
            "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER, int6 INTEGER, "
            "int7 INTEGER, int8 INTEGER, int9 INTEGER)", now;

      Tuple<int,int,int,int,int,int,int,int,int,int> t(0,1,2,3,4,5,6,7,8,9);

      tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?,?,?)", use(t), now;

      Tuple<int,int,int,int,int,int,int,int,int,int> ret(-10,-11,-12,-13,-14,-15,-16,-17,-18,-19);
      assert (ret != t);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == t);
}


void SQLiteTest::testTupleVector10()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples "
            "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER, int6 INTEGER, "
            "int7 INTEGER, int8 INTEGER, int9 INTEGER)", now;

      Tuple<int,int,int,int,int,int,int,int,int,int> t(0,1,2,3,4,5,6,7,8,9);
      Tuple<int,int,int,int,int,int,int,int,int,int> t10(10,11,12,13,14,15,16,17,18,19);
      Tuple<int,int,int,int,int,int,int,int,int,int> t100(100,101,102,103,104,105,106,107,108,109);
      std::vector<Tuple<int,int,int,int,int,int,int,int,int,int> > v;
      v.push_back(t);
      v.push_back(t10);
      v.push_back(t100);

      tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?,?,?)", use(v), now;

      int count = 0;
      tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
      assert (v.size() == count);

      std::vector<Tuple<int,int,int,int,int,int,int,int,int,int> > ret;
      assert (ret != v);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == v);
}


void SQLiteTest::testTuple9()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples "
            "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER, int6 INTEGER, "
            "int7 INTEGER, int8 INTEGER)", now;

      Tuple<int,int,int,int,int,int,int,int,int> t(0,1,2,3,4,5,6,7,8);

      tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?,?)", use(t), now;

      Tuple<int,int,int,int,int,int,int,int,int> ret(-10,-11,-12,-13,-14,-15,-16,-17,-18);
      assert (ret != t);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == t);
}


void SQLiteTest::testTupleVector9()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples "
            "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER, int6 INTEGER, "
            "int7 INTEGER, int8 INTEGER)", now;

      Tuple<int,int,int,int,int,int,int,int,int> t(0,1,2,3,4,5,6,7,8);
      Tuple<int,int,int,int,int,int,int,int,int> t10(10,11,12,13,14,15,16,17,18);
      Tuple<int,int,int,int,int,int,int,int,int> t100(100,101,102,103,104,105,106,107,108);
      std::vector<Tuple<int,int,int,int,int,int,int,int,int> > v;
      v.push_back(t);
      v.push_back(t10);
      v.push_back(t100);

      tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?,?)", use(v), now;

      int count = 0;
      tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
      assert (v.size() == count);

      std::vector<Tuple<int,int,int,int,int,int,int,int,int> > ret;
      assert (ret != v);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == v);
}


void SQLiteTest::testTuple8()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples "
            "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER, int6 INTEGER, "
            "int7 INTEGER)", now;

      Tuple<int,int,int,int,int,int,int,int> t(0,1,2,3,4,5,6,7);

      tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?)", use(t), now;

      Tuple<int,int,int,int,int,int,int,int> ret(-10,-11,-12,-13,-14,-15,-16,-17);
      assert (ret != t);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == t);
}


void SQLiteTest::testTupleVector8()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples "
            "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER, int6 INTEGER, "
            "int7 INTEGER)", now;

      Tuple<int,int,int,int,int,int,int,int> t(0,1,2,3,4,5,6,7);
      Tuple<int,int,int,int,int,int,int,int> t10(10,11,12,13,14,15,16,17);
      Tuple<int,int,int,int,int,int,int,int> t100(100,101,102,103,104,105,106,107);
      std::vector<Tuple<int,int,int,int,int,int,int,int> > v;
      v.push_back(t);
      v.push_back(t10);
      v.push_back(t100);

      tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?,?)", use(v), now;

      int count = 0;
      tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
      assert (v.size() == count);

      std::vector<Tuple<int,int,int,int,int,int,int,int> > ret;
      assert (ret != v);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == v);
}


void SQLiteTest::testTuple7()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples "
            "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER, int6 INTEGER)", now;

      Tuple<int,int,int,int,int,int,int> t(0,1,2,3,4,5,6);

      tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?)", use(t), now;

      Tuple<int,int,int,int,int,int,int> ret(-10,-11,-12,-13,-14,-15,-16);
      assert (ret != t);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == t);
}


void SQLiteTest::testTupleVector7()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples "
            "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER, int6 INTEGER)", now;

      Tuple<int,int,int,int,int,int,int> t(0,1,2,3,4,5,6);
      Tuple<int,int,int,int,int,int,int> t10(10,11,12,13,14,15,16);
      Tuple<int,int,int,int,int,int,int> t100(100,101,102,103,104,105,106);
      std::vector<Tuple<int,int,int,int,int,int,int> > v;
      v.push_back(t);
      v.push_back(t10);
      v.push_back(t100);

      tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?,?)", use(v), now;

      int count = 0;
      tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
      assert (v.size() == count);

      std::vector<Tuple<int,int,int,int,int,int,int> > ret;
      assert (ret != v);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == v);
}


void SQLiteTest::testTuple6()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples "
            "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER)", now;

      Tuple<int,int,int,int,int,int> t(0,1,2,3,4,5);

      tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?)", use(t), now;

      Tuple<int,int,int,int,int,int> ret(-10,-11,-12,-13,-14,-15);
      assert (ret != t);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == t);
}


void SQLiteTest::testTupleVector6()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples "
            "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER, int5 INTEGER)", now;

      Tuple<int,int,int,int,int,int> t(0,1,2,3,4,5);
      Tuple<int,int,int,int,int,int> t10(10,11,12,13,14,15);
      Tuple<int,int,int,int,int,int> t100(100,101,102,103,104,105);
      std::vector<Tuple<int,int,int,int,int,int> > v;
      v.push_back(t);
      v.push_back(t10);
      v.push_back(t100);

      tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?,?)", use(v), now;

      int count = 0;
      tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
      assert (v.size() == count);

      std::vector<Tuple<int,int,int,int,int,int> > ret;
      assert (ret != v);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == v);
}


void SQLiteTest::testTuple5()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples "
            "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER)", now;

      Tuple<int,int,int,int,int> t(0,1,2,3,4);

      tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?)", use(t), now;

      Tuple<int,int,int,int,int> ret(-10,-11,-12,-13,-14);
      assert (ret != t);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == t);
}


void SQLiteTest::testTupleVector5()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples "
            "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER, int4 INTEGER)", now;

      Tuple<int,int,int,int,int> t(0,1,2,3,4);
      Tuple<int,int,int,int,int> t10(10,11,12,13,14);
      Tuple<int,int,int,int,int> t100(100,101,102,103,104);
      std::vector<Tuple<int,int,int,int,int> > v;
      v.push_back(t);
      v.push_back(t10);
      v.push_back(t100);

      tmp << "INSERT INTO Tuples VALUES (?,?,?,?,?)", use(v), now;

      int count = 0;
      tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
      assert (v.size() == count);

      std::vector<Tuple<int,int,int,int,int> > ret;
      assert (ret != v);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == v);
}


void SQLiteTest::testTuple4()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples "
            "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER)", now;

      Tuple<int,int,int,int> t(0,1,2,3);

      tmp << "INSERT INTO Tuples VALUES (?,?,?,?)", use(t), now;

      Tuple<int,int,int,int> ret(-10,-11,-12,-13);
      assert (ret != t);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == t);
}


void SQLiteTest::testTupleVector4()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples "
            "(int0 INTEGER, int1 INTEGER, int2 INTEGER, int3 INTEGER)", now;

      Tuple<int,int,int,int> t(0,1,2,3);
      Tuple<int,int,int,int> t10(10,11,12,13);
      Tuple<int,int,int,int> t100(100,101,102,103);
      std::vector<Tuple<int,int,int,int> > v;
      v.push_back(t);
      v.push_back(t10);
      v.push_back(t100);

      tmp << "INSERT INTO Tuples VALUES (?,?,?,?)", use(v), now;

      int count = 0;
      tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
      assert (v.size() == count);

      std::vector<Tuple<int,int,int,int> > ret;
      assert (ret != v);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == v);
}


void SQLiteTest::testTuple3()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples "
            "(int0 INTEGER, int1 INTEGER, int2 INTEGER)", now;

      Tuple<int,int,int> t(0,1,2);

      tmp << "INSERT INTO Tuples VALUES (?,?,?)", use(t), now;

      Tuple<int,int,int> ret(-10,-11,-12);
      assert (ret != t);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == t);
}


void SQLiteTest::testTupleVector3()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples "
            "(int0 INTEGER, int1 INTEGER, int2 INTEGER)", now;

      Tuple<int,int,int> t(0,1,2);
      Tuple<int,int,int> t10(10,11,12);
      Tuple<int,int,int> t100(100,101,102);
      std::vector<Tuple<int,int,int> > v;
      v.push_back(t);
      v.push_back(t10);
      v.push_back(t100);

      tmp << "INSERT INTO Tuples VALUES (?,?,?)", use(v), now;

      int count = 0;
      tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
      assert (v.size() == count);

      std::vector<Tuple<int,int,int> > ret;
      assert (ret != v);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == v);
}


void SQLiteTest::testTuple2()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples (int0 INTEGER, int1 INTEGER)", now;

      Tuple<int,int> t(0,1);

      tmp << "INSERT INTO Tuples VALUES (?,?)", use(t), now;

      Tuple<int,int> ret(-10,-11);
      assert (ret != t);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == t);
}


void SQLiteTest::testTupleVector2()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples (int0 INTEGER, int1 INTEGER)", now;

      Tuple<int,int> t(0,1);
      Tuple<int,int> t10(10,11);
      Tuple<int,int> t100(100,101);
      std::vector<Tuple<int,int> > v;
      v.push_back(t);
      v.push_back(t10);
      v.push_back(t100);

      tmp << "INSERT INTO Tuples VALUES (?,?)", use(v), now;

      int count = 0;
      tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
      assert (v.size() == count);

      std::vector<Tuple<int,int> > ret;
      assert (ret != v);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == v);
}


void SQLiteTest::testTuple1()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples (int0 INTEGER)", now;

      Tuple<int> t(0);

      tmp << "INSERT INTO Tuples VALUES (?)", use(t), now;

      Tuple<int> ret(-10);
      assert (ret != t);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == t);
}


void SQLiteTest::testTupleVector1()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Tuples", now;
      tmp << "CREATE TABLE Tuples (int0 INTEGER)", now;

      Tuple<int> t(0);
      Tuple<int> t10(10);
      Tuple<int> t100(100);
      std::vector<Tuple<int> > v;
      v.push_back(t);
      v.push_back(t10);
      v.push_back(t100);

      tmp << "INSERT INTO Tuples VALUES (?)", use(v), now;

      int count = 0;
      tmp << "SELECT COUNT(*) FROM Tuples", into(count), now;
      assert (v.size() == count);

      std::vector<Tuple<int> > ret;
      assert (ret != v);
      tmp << "SELECT * FROM Tuples", into(ret), now;
      assert (ret == v);
}


void SQLiteTest::testInternalExtraction()
{
      Session tmp (SessionFactory::instance().create(SQLite::Connector::KEY, "dummy.db"));
      tmp << "DROP TABLE IF EXISTS Vectors", now;
      tmp << "CREATE TABLE Vectors (int0 INTEGER, flt0 REAL, str0 VARCHAR)", now;

      std::vector<Tuple<int, double, std::string> > v;
      v.push_back(Tuple<int, double, std::string>(1, 1.5, "3"));
      v.push_back(Tuple<int, double, std::string>(2, 2.5, "4"));
      v.push_back(Tuple<int, double, std::string>(3, 3.5, "5"));
      v.push_back(Tuple<int, double, std::string>(4, 4.5, "6"));

      tmp << "INSERT INTO Vectors VALUES (?,?,?)", use(v), now;

      Statement stmt = (tmp << "SELECT * FROM Vectors", now);
      RecordSet rset(stmt);
      assert (3 == rset.columnCount());
      assert (4 == rset.rowCount());

      RecordSet rset2(rset);
      assert (3 == rset2.columnCount());
      assert (4 == rset2.rowCount());

      int a = rset.value<int>(0,2);
      assert (3 == a);

      int b = rset2.value<int>("InT0",2);
      assert (3 == b);

      double d = rset.value<double>(1,0);
      assert (1.5 == d);

      std::string s = rset.value<std::string>(2,1);
      assert ("4" == s);
      
      const Column<int>& col = rset.column<int>(0);
      assert (col[0] == 1);

      try { const Column<int>& col1 = rset.column<int>(100); fail ("must fail"); }
      catch (RangeException&) { }

      const Column<int>& col1 = rset.column<int>(0);
      assert ("int0" == col1.name());
      Column<int>::Iterator it = col1.begin();
      Column<int>::Iterator itEnd = col1.end();
      int counter = 1;
      for (; it != itEnd; ++it, ++counter)
            assert (counter == *it);

      rset = (tmp << "SELECT COUNT(*) FROM Vectors", now);
      s = rset.value<std::string>(0,0);
      assert ("4" == s);
      
      stmt = (tmp << "DELETE FROM Vectors", now);
      rset = stmt;

      try { const Column<int>& col1 = rset.column<int>(0); fail ("must fail"); }
      catch (RangeException&) { }
}


void SQLiteTest::testBindingCount()
{
      Session tmp (SQLite::Connector::KEY, "dummy.db");

      tmp << "DROP TABLE IF EXISTS Ints", now;
      tmp << "CREATE TABLE Ints (int0 INTEGER)", now;

      int i = 42;
      try   { tmp << "INSERT INTO Ints VALUES (?)", now; } 
      catch (ParameterCountMismatchException&) { }

      try   { tmp << "INSERT INTO Ints VALUES (?)", use(i), use(i), now; }
      catch (ParameterCountMismatchException&) { }
      tmp << "INSERT INTO Ints VALUES (?)", use(i), now;
      
      int j = 0;
      try   { tmp << "SELECT int0 from Ints where int0 = ?", into(i), now; }
      catch (ParameterCountMismatchException&) { }
      tmp << "SELECT int0 from Ints where int0 = ?", use(i), into(j), now;
      assert (42 == j);
}


void SQLiteTest::setUp()
{
}


void SQLiteTest::tearDown()
{
}


CppUnit::Test* SQLiteTest::suite()
{
      CppUnit::TestSuite* pSuite = new CppUnit::TestSuite("SQLiteTest");

      CppUnit_addTest(pSuite, SQLiteTest, testSimpleAccess);
      CppUnit_addTest(pSuite, SQLiteTest, testComplexType);
      CppUnit_addTest(pSuite, SQLiteTest, testSimpleAccessVector);
      CppUnit_addTest(pSuite, SQLiteTest, testComplexTypeVector);
      CppUnit_addTest(pSuite, SQLiteTest, testInsertVector);
      CppUnit_addTest(pSuite, SQLiteTest, testInsertEmptyVector);
      CppUnit_addTest(pSuite, SQLiteTest, testInsertSingleBulk);
      CppUnit_addTest(pSuite, SQLiteTest, testInsertSingleBulkVec);
      CppUnit_addTest(pSuite, SQLiteTest, testLimit);
      CppUnit_addTest(pSuite, SQLiteTest, testLimitOnce);
      CppUnit_addTest(pSuite, SQLiteTest, testLimitPrepare);
      CppUnit_addTest(pSuite, SQLiteTest, testLimitZero);
      CppUnit_addTest(pSuite, SQLiteTest, testPrepare);
      CppUnit_addTest(pSuite, SQLiteTest, testSetSimple);
      CppUnit_addTest(pSuite, SQLiteTest, testSetComplex);
      CppUnit_addTest(pSuite, SQLiteTest, testSetComplexUnique);
      CppUnit_addTest(pSuite, SQLiteTest, testMultiSetSimple);
      CppUnit_addTest(pSuite, SQLiteTest, testMultiSetComplex);
      CppUnit_addTest(pSuite, SQLiteTest, testMapComplex);
      CppUnit_addTest(pSuite, SQLiteTest, testMapComplexUnique);
      CppUnit_addTest(pSuite, SQLiteTest, testMultiMapComplex);
      CppUnit_addTest(pSuite, SQLiteTest, testSelectIntoSingle);
      CppUnit_addTest(pSuite, SQLiteTest, testSelectIntoSingleStep);
      CppUnit_addTest(pSuite, SQLiteTest, testSelectIntoSingleFail);
      CppUnit_addTest(pSuite, SQLiteTest, testLowerLimitOk);
      CppUnit_addTest(pSuite, SQLiteTest, testLowerLimitFail);
      CppUnit_addTest(pSuite, SQLiteTest, testCombinedLimits);
      CppUnit_addTest(pSuite, SQLiteTest, testCombinedIllegalLimits);
      CppUnit_addTest(pSuite, SQLiteTest, testRange);
      CppUnit_addTest(pSuite, SQLiteTest, testIllegalRange);
      CppUnit_addTest(pSuite, SQLiteTest, testSingleSelect);
      CppUnit_addTest(pSuite, SQLiteTest, testEmptyDB);
      CppUnit_addTest(pSuite, SQLiteTest, testBLOB);
      CppUnit_addTest(pSuite, SQLiteTest, testBLOBStmt);
      CppUnit_addTest(pSuite, SQLiteTest, testTuple10);
      CppUnit_addTest(pSuite, SQLiteTest, testTupleVector10);
      CppUnit_addTest(pSuite, SQLiteTest, testTuple9);
      CppUnit_addTest(pSuite, SQLiteTest, testTupleVector9);
      CppUnit_addTest(pSuite, SQLiteTest, testTuple8);
      CppUnit_addTest(pSuite, SQLiteTest, testTupleVector8);
      CppUnit_addTest(pSuite, SQLiteTest, testTuple7);
      CppUnit_addTest(pSuite, SQLiteTest, testTupleVector7);
      CppUnit_addTest(pSuite, SQLiteTest, testTuple6);
      CppUnit_addTest(pSuite, SQLiteTest, testTupleVector6);
      CppUnit_addTest(pSuite, SQLiteTest, testTuple5);
      CppUnit_addTest(pSuite, SQLiteTest, testTupleVector5);
      CppUnit_addTest(pSuite, SQLiteTest, testTuple4);
      CppUnit_addTest(pSuite, SQLiteTest, testTupleVector4);
      CppUnit_addTest(pSuite, SQLiteTest, testTuple3);
      CppUnit_addTest(pSuite, SQLiteTest, testTupleVector3);
      CppUnit_addTest(pSuite, SQLiteTest, testTuple2);
      CppUnit_addTest(pSuite, SQLiteTest, testTupleVector2);
      CppUnit_addTest(pSuite, SQLiteTest, testTuple1);
      CppUnit_addTest(pSuite, SQLiteTest, testTupleVector1);
      CppUnit_addTest(pSuite, SQLiteTest, testInternalExtraction);
      CppUnit_addTest(pSuite, SQLiteTest, testBindingCount);

      return pSuite;
}

Generated by  Doxygen 1.6.0   Back to index