sqlitetest.cpp.svn-base

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

		data.push_back(x);
	}

	tmp << "INSERT INTO Strings VALUES(:str)", use(data), now;
	int count = 0;
	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;
	int count = 0;
	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;
	int count = 0;
	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;
	int count = 0;
	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