C++ db::Result类代码示例

bool DBObject::find(long long objectId)
{
	MutexLocker lock(_mutex);

	if (_connection == NULL)
	{
		ERROR_MSG("Object is not connected to the database.");
		return false;
	}

	if (objectId == 0) {
		ERROR_MSG("Invalid object_id 0 passed to find");
		return false;
	}

	// find the object in the database for the given object_id
	DB::Statement statement = _connection->prepare(
				"select id from object where id=%lld",
				objectId);
	if (!statement.isValid()) {
		ERROR_MSG("Preparing object selection statement failed");
		return false;
	}

	DB::Result result = _connection->perform(statement);
	if (result.getLongLong(1) != objectId) {
		ERROR_MSG("Failed to find object with id %lld",objectId);
		return false;
	}

	_objectId = objectId;
	return true;
}

void test_a_db_with_a_connection_with_tables::will_not_insert_non_existing_attribute_on_update()
{
	DB::Statement statement;
	DB::Result result;

	// Insert new object
	statement = connection->prepare(
				"insert into object default values");
	CPPUNIT_ASSERT(statement.isValid());
	CPPUNIT_ASSERT(connection->execute(statement));
	long long object_id = connection->lastInsertRowId();
	CPPUNIT_ASSERT(object_id != 0);

	// Updating an attribute before it is created will succeed, but will not insert an attribute.
	statement = connection->prepare(
				"update attribute_boolean set value=1 where type=%d and object_id=%lld",
				1237,
				object_id);
	CPPUNIT_ASSERT(statement.isValid());
	CPPUNIT_ASSERT(connection->execute(statement));

	// Retrieve the boolean value from the attribute should fail
	statement = connection->prepare(
				"select value from attribute_boolean as t where t.type=%d and t.object_id=%lld",
				1237,
				object_id);
	CPPUNIT_ASSERT(statement.isValid());
	result = connection->perform(statement);
	CPPUNIT_ASSERT(!result.isValid());
}

void test_a_db_with_a_connection_with_tables::can_update_text_attribute_bound_value()
{
	can_insert_records();

	// query all objects
	DB::Statement statement = connection->prepare("select id from object");
	CPPUNIT_ASSERT(statement.isValid());
	DB::Result result = connection->perform(statement);
	CPPUNIT_ASSERT(result.isValid());

	long long object_id = result.getLongLong(1); // field indices start at 1

	statement = connection->prepare(
				"update attribute_text set value=? where type=%d and object_id=%lld",
				1234,
				object_id);
	CPPUNIT_ASSERT(statement.isValid());

	std::string msg("testing quote ' and accents é.");

	CPPUNIT_ASSERT(DB::Bindings(statement).bindText(1,msg.c_str(),msg.size(),NULL));
	CPPUNIT_ASSERT(connection->execute(statement));

	statement = connection->prepare(
				"select value from attribute_text as t where t.type=%d and t.object_id=%lld",
				1234,
				object_id);
	result = connection->perform(statement);
	CPPUNIT_ASSERT_EQUAL(std::string(result.getString(1)), msg);
}

void test_a_db_with_a_connection_with_tables::can_update_integer_attribute_bound_value()
{
	// insert new object
	DB::Statement statement = connection->prepare(
				"insert into object default values");
	CPPUNIT_ASSERT(statement.isValid());
	CPPUNIT_ASSERT(connection->execute(statement));
	long long object_id = connection->lastInsertRowId();
	CPPUNIT_ASSERT(object_id != 0);

	// insert integer attribute
	statement = connection->prepare(
				"insert into attribute_integer (value,type,object_id) values (%lld,%d,%lld)",
				1111,
				1235,
				object_id);
	CPPUNIT_ASSERT(statement.isValid());
	CPPUNIT_ASSERT(connection->execute(statement));

	// prepare update integer attribute statement
	statement = connection->prepare(
				"update attribute_integer set value=? where type=%d and object_id=%lld",
				1235,
				object_id);
	CPPUNIT_ASSERT(statement.isValid());

	// bind long long value to the parameter an update the record
	CPPUNIT_ASSERT(DB::Bindings(statement).bindInt64(1,2222));
	CPPUNIT_ASSERT(connection->execute(statement));

	// Retrieve the value from the record
	DB::Statement retrieveStmt = connection->prepare(
				"select value from attribute_integer as t where t.type=%d and t.object_id=%lld",
				1235,
				object_id);
	CPPUNIT_ASSERT(retrieveStmt.isValid());
	DB::Result result = connection->perform(retrieveStmt);
	CPPUNIT_ASSERT_EQUAL(result.getLongLong(1), (long long)2222);

	// verify that binding to a parameter before resetting the statement will fail.
	DB::LogErrorHandler eh = DB::setLogErrorHandler(dummy_print);
	DB::Bindings bindings(statement);
	CPPUNIT_ASSERT(!bindings.bindInt(1,3333));
	DB::setLogErrorHandler(eh);

	// reset statement and bind another value to the statement
	CPPUNIT_ASSERT(bindings.reset());
	CPPUNIT_ASSERT(bindings.bindInt(1,3333));

	// perform the update statement again with the newly bound value
	CPPUNIT_ASSERT(connection->execute(statement));

	// reset the retrieve statement and perform it again to get the latest value of the integer attribute
	CPPUNIT_ASSERT(retrieveStmt.reset());
	result = connection->perform(retrieveStmt);
	CPPUNIT_ASSERT(result.isValid());
	CPPUNIT_ASSERT_EQUAL(result.getLongLong(1), (long long)3333);
}

void test_a_db_with_a_connection::can_perform_statements()
{
	DB::Statement statement = connection->prepare("PRAGMA database_list;");
	CPPUNIT_ASSERT(statement.isValid());
	DB::Result result = connection->perform(statement);
	CPPUNIT_ASSERT(result.isValid());
	// only expect a single row in the result, so nextRow should now fail
	CPPUNIT_ASSERT(!result.nextRow());
}

void test_a_db_with_a_connection_with_tables::can_retrieve_records()
{
	can_insert_records();

	DB::Statement statement = connection->prepare(
				"select value from attribute_text as t where t.type=%d",
				1234);
	DB::Result result = connection->perform(statement);
	CPPUNIT_ASSERT_EQUAL(std::string(result.getString(1)), std::string("testing testing testing"));
}

std::vector<Event> Event::select(std::string cond, int limit){
    DB::Result r = DB::query("SELECT type, source_id, source_name, target_id, target_name, track_id, track_title, message, date AT TIME ZONE 'UTC', id FROM events" + (cond.empty()?"":" WHERE "+cond) + " ORDER BY date DESC" + (limit?" LIMIT "+number(limit):""));
    std::vector<Event> events(r.size());
    for(unsigned i=0; i<r.size(); i++){
        Type t = r[i][0] == "publish"  ? Publish
             : r[i][0] == "comment"  ? Comment
             : r[i][0] == "favorite" ? Favorite : Follow;
        events[i] = Event(t, User(number(r[i][1]),r[i][2]), User(number(r[i][3]),r[i][4]), Track(number(r[i][5]),r[i][6]), r[i][7], r[i][8], number(r[i][9]));
    }
    return events;
}

void test_a_db_with_a_connection_with_tables::can_update_boolean_attribute_bound_value()
{
	//SQLite doesn't have a boolean data type, use 0 (false) and 1 (true)

	DB::Statement statement;
	DB::Result result;

	// Insert new object
	statement = connection->prepare(
				"insert into object default values");
	CPPUNIT_ASSERT(statement.isValid());
	CPPUNIT_ASSERT(connection->execute(statement));
	long long object_id = connection->lastInsertRowId();
	CPPUNIT_ASSERT(object_id != 0);

	// insert boolean attribute
	statement = connection->prepare(
				"insert into attribute_boolean (value,type,object_id) values (1,%d,%lld)",
				1237,
				object_id);
	CPPUNIT_ASSERT(statement.isValid());
	CPPUNIT_ASSERT(connection->execute(statement));

	// prepare update boolean attribute statement
	statement = connection->prepare(
				"update attribute_boolean set value=? where type=%d and object_id=%lld",
				1237,
				object_id);
	CPPUNIT_ASSERT(statement.isValid());

	// Bind 0 (false) to the first parameter
	CPPUNIT_ASSERT(DB::Bindings(statement).bindInt(1,0));

	// Execute the statement to update the attribute value.
	CPPUNIT_ASSERT(connection->execute(statement));

	// Retrieve the boolean value from the attribute
	statement = connection->prepare(
				"select value from attribute_boolean as t where t.type=%d and t.object_id=%lld",
				1237,
				object_id);
	CPPUNIT_ASSERT(statement.isValid());
	result = connection->perform(statement);
	CPPUNIT_ASSERT(result.isValid());

	// check that the retrieved value matches the original value
	CPPUNIT_ASSERT_EQUAL(result.getInt(1), 0);
}

void Pages::registration(Document *doc){

    if(path != "/register")
        return;

    std::string name = cgi("name"), email = cgi("email"), pw = cgi("pw");

    if(Session::user())
        doc->redirect("/");

    else if(cgi.getEnvironment().getRequestMethod() != "POST")
        form(doc);

    else if(name.empty())
        form(doc, "Please specify a display name.");

    else if(email.empty())
        form(doc, "Please specify an email address.");
    else if(!validEmail(email))
        form(doc, "Invalid email address.");

    else if(pw.empty())
        form(doc, "Please specify a password.");
    else if(pw != cgi("pwconf"))
        form(doc, "Passwords mismatch.");

    else{

        if(DB::query("SELECT EXISTS (SELECT 1 FROM users WHERE lower(name) = lower($1) OR lower(email) = lower($2))", name, email)[0][0] == "t")
            return form(doc, "Sorry, name or email already in use.");

        DB::Result r = DB::query(
            "INSERT INTO users (name, password, email, registration, last_login) "
            "VALUES ($1, crypt($2, gen_salt('bf')), $3, 'now', 'now') "
            "RETURNING id", name, pw, email);

        if(r.empty())
            return form(doc, "Erm, something went wrong. Please try again.");

        User u = User(number(r[0][0]), name);
        log("New user: " + u.name + " (" + number(u.id) + ")");
        doc->addHttp("Set-Cookie: sid=" + Session::login(u) + ";Max-Age=2592000\n"); // 30 days
        doc->redirect(u.url() + "?welcome=1");

    }

}

void test_a_db_with_a_connection_with_tables::can_update_blob_attribute_bound_value()
{
	// insert new object
	DB::Statement statement = connection->prepare(
				"insert into object default values");
	CPPUNIT_ASSERT(statement.isValid());
	CPPUNIT_ASSERT(connection->execute(statement));
	long long object_id = connection->lastInsertRowId();
	CPPUNIT_ASSERT(object_id != 0);

	// insert blob attribute
	statement = connection->prepare(
				"insert into attribute_blob (value,type,object_id) values (X'012345',%d,%lld)",
				1236,
				object_id);
	CPPUNIT_ASSERT(statement.isValid());
	CPPUNIT_ASSERT(connection->execute(statement));

	// prepare update blob attribute statement
	statement = connection->prepare(
				"update attribute_blob set value=? where type=%d and object_id=%lld",
				1236,
				object_id);
	CPPUNIT_ASSERT(statement.isValid());

	// bind blob (with embedded zero!) to the parameter
	const char data[] = {10,11,0,12,13,14,15,16};
	std::string msg(data,sizeof(data));
	CPPUNIT_ASSERT(DB::Bindings(statement).bindBlob(1,msg.data(),msg.size(),NULL));

	// update the blob value of the attribute
	CPPUNIT_ASSERT(connection->execute(statement));

	// retrieve the blob value from the attribute
	statement = connection->prepare(
				"select value from attribute_blob as t where t.type=%d and t.object_id=%lld",
				1236,
				object_id);
	CPPUNIT_ASSERT(statement.isValid());
	DB::Result result = connection->perform(statement);
	CPPUNIT_ASSERT(result.isValid());

	// check that the retrieved blob value matches the original data.
	CPPUNIT_ASSERT_EQUAL(result.getFieldLength(1), sizeof(data));
	std::string msgstored((const char *)result.getBinary(1),result.getFieldLength(1));
	CPPUNIT_ASSERT_EQUAL(msg, msgstored);
}

void test_a_db_with_a_connection_with_tables::can_update_real_attribute_bound_value()
{
	// insert new object
	DB::Statement statement = connection->prepare(
				"insert into object default values");
	CPPUNIT_ASSERT(statement.isValid());
	CPPUNIT_ASSERT(connection->execute(statement));
	long long object_id = connection->lastInsertRowId();
	CPPUNIT_ASSERT(object_id != 0);

	// insert real value
	statement = connection->prepare(
				"insert into attribute_real (value,type,object_id) values(%f,%d,%lld)",
				1.238,
				1238,
				object_id);
	CPPUNIT_ASSERT(statement.isValid());
	CPPUNIT_ASSERT(connection->execute(statement));

	// prepare update real attribute statement
	statement = connection->prepare(
				"update attribute_real set value=? where type=%d and object_id=%lld",
				1238,
				object_id);
	CPPUNIT_ASSERT(statement.isValid());

	// Bind 3333.3333 to the first parameter
	CPPUNIT_ASSERT(DB::Bindings(statement).bindDouble(1,3333.3333));

	// Execute the statement to update the attribute value
	CPPUNIT_ASSERT(connection->execute(statement));

	// Retrieve the double value from the attribute
	statement = connection->prepare(
				"select value from attribute_real as t where t.type=%d and t.object_id=%lld",
				1238,
				object_id);
	CPPUNIT_ASSERT(statement.isValid());
	DB::Result result = connection->perform(statement);
	CPPUNIT_ASSERT(result.isValid());

	// check that the retrieved value matches the original value.
	CPPUNIT_ASSERT_DOUBLES_EQUAL(result.getDouble(1), 3333.3333, 0.00001);
}

ExtendedTrack::ExtendedTrack(int tid){

    id = 0;
    if(tid<=0) return;

    DB::Result r = DB::query(
        "SELECT tracks.title, tracks.user_id, users.name, users.email, tracks.visible, tracks.date, extract(epoch from tracks.date),"
        " tracks.notes, tracks.airable, tracks.license, array_to_string(tracks.tags, ',') FROM tracks, users "
        "WHERE tracks.id = " + number(tid) + " AND tracks.user_id = users.id");

    if(!r.empty()){

        id = tid;
        title = r[0][0];
        artist.id = number(r[0][1]);
        artist.name = r[0][2];
        artist.email = r[0][3];
        visible = r[0][4] == "t";
        date = r[0][5];
        timestamp = r[0][6];

        // Ext
        notes = r[0][7];
        airable = r[0][8] == "t";
        license = r[0][9];

        // Tags
        std::string tstr = r[0][10];
        std::string buf;
        for(std::string::const_iterator i=tstr.begin(); i!=tstr.end(); i++){
            if(*i == ','){
                if(!buf.empty()){
                    tags.push_back(buf);
                    buf.clear();
                }
            }
            else buf += *i;
        }
        if(!buf.empty()) tags.push_back(buf); // last tag

    }

}

void test_a_db_with_a_connection_with_tables::can_cascade_delete_objects_and_attributes()
{
	can_insert_records();

	DB::Statement statement = connection->prepare("select id from object");
	DB::Result result = connection->perform(statement);
	CPPUNIT_ASSERT(result.isValid());

	long long object_id = result.getLongLong(1);

	statement = connection->prepare("delete from object where id=%lld",object_id);
	CPPUNIT_ASSERT(connection->execute(statement));

	statement = connection->prepare("select * from attribute_text where object_id=%lld",object_id);
	result = connection->perform(statement);

	// Check cascade delete was successful.
	CPPUNIT_ASSERT(!result.isValid());
}

void test_a_db_with_a_connection_with_tables::can_update_text_attribute()
{
	can_insert_records();

	// query all objects
	DB::Statement statement = connection->prepare("select id from object");
	CPPUNIT_ASSERT(statement.isValid());
	DB::Result result = connection->perform(statement);
	CPPUNIT_ASSERT(result.isValid());

	long long object_id = result.getLongLong(1); // field indices start at 1

	statement = connection->prepare(
				"update attribute_text set value='test test test' where type=%d and object_id=%lld",
				1234,
				object_id);
	CPPUNIT_ASSERT(statement.isValid());
	CPPUNIT_ASSERT(connection->execute(statement));
}

void test_a_db_with_a_connection_with_tables_with_a_second_connection_open::supports_transactions_with_other_connections_open()
{
	CPPUNIT_ASSERT(connection2->beginTransactionRO());

	supports_transactions();

	// Retrieve the double value from the attribute
	DB::Statement statement = connection2->prepare(
				"select value from attribute_real as t where t.type=%d and t.object_id=%lld",
				1238,
				connection->lastInsertRowId());
	CPPUNIT_ASSERT(statement.isValid());
	DB::Result result = connection2->perform(statement);
	CPPUNIT_ASSERT(result.isValid());

	// check that the retrieved value matches the original value.
	CPPUNIT_ASSERT_DOUBLES_EQUAL(result.getDouble(1), 3333.3333, 0.00001);

	CPPUNIT_ASSERT(connection2->commitTransaction());
}