sqlitetest.cpp

很好用的网络封装库,不熟悉网络编程的人也可以使用。使用风格良好的标准c++编写。

//
// SQLiteTest.cpp
//
// $Id: //poco/1.3/Data/SQLite/testsuite/src/SQLiteTest.cpp#3 $
//
// 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);
	}

	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;
		int age = 0;
		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::testInsertCharPointer()
{
	Session tmp (SessionFactory::instance().create(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)", use("lastname"), use("firstname"), use("Address"), use(133132), 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);
}



void SQLiteTest::testInsertCharPointer2()
{
	Session tmp (SessionFactory::instance().create(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)", use("lastname"), use("firstname"), use("Address"), use(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 (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);
}



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;

	int count = 100;
	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)
	{