sqlitetest.cpp

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

	{
		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);