Loading...   

[Show Table of Contents]


§Old Style Database Query

Old style RunQuery based database queries resulted in highly nested, tightly mysql coupled, memory leak prone code.

An example old style query, from database.cpp - uint8 Database::GetSkillCap(uint8 skillid, uint8 in_race, uint8 in_class, uint16 in_level)

uint8 Database::GetSkillCap(uint8 skillid, uint8 in_race, uint8 in_class, uint16 in_level)
{
	uint8 skill_level = 0, skill_formula = 0;
	uint16 base_cap = 0, skill_cap = 0, skill_cap2 = 0, skill_cap3 = 0;
	char errbuf[MYSQL_ERRMSG_SIZE];
	char *query = 0;
	uint32	affected_rows = 0;
	MYSQL_RES *result;
	MYSQL_ROW row;
	//Fetch the data from DB.
	if (RunQuery(query, MakeAnyLenString(&query, "SELECT level, formula, pre50cap, post50cap, post60cap from skillcaps where skill = %i && class = %i", skillid, in_class), errbuf, &result, &affected_rows))
	{
		if (affected_rows != 0)
		{
			row = mysql_fetch_row(result);
			skill_level = atoi(row[0]);
			skill_formula = atoi(row[1]);
			skill_cap = atoi(row[2]);
			if (atoi(row[3]) > skill_cap)
				skill_cap2 = (atoi(row[3])-skill_cap)/10; //Split the post-50 skill cap into difference between pre-50 cap and post-50 cap / 10 to determine amount of points per level.
			skill_cap3 = atoi(row[4]);
		}
		delete[] query;
		mysql_free_result(result);
	}

	int race_skill = GetRaceSkill(skillid,in_race);

	if (race_skill > 0 && (race_skill > skill_cap || skill_cap == 0 || in_level < skill_level))
		return race_skill;

	if (skill_cap == 0) //Can't train this skill at all.
		return 255; //Untrainable

	if (in_level < skill_level)
		return 254; //Untrained

	//Determine pre-51 level-based cap
	if (skill_formula > 0)
		base_cap = in_level*skill_formula+skill_formula;
	if (base_cap > skill_cap || skill_formula == 0)
		base_cap = skill_cap;
	//If post 50, add post 50 cap to base cap.
	if (in_level > 50 && skill_cap2 > 0)
		base_cap += skill_cap2*(in_level-50);
	//No cap should ever go above its post50cap
	if (skill_cap3 > 0 && base_cap > skill_cap3)
		base_cap = skill_cap3;
	//Base cap is now the max value at the person's level, return it!
	return base_cap;
}

As you can see an errbuf, *result, affectedrows/rowcount and row need to be created and freed correctly based on each branch of the code. This, along with correct releasing of query can be a large source of memory leaks.

§New Style Database Queries

The newer style database handles correct release / acquire behavior for the myql results. rows are returned in an stl style iterator and are valid in all stl algorithms.

the above code can be rewriten in the new style as:

uint8 Database::GetSkillCap(uint8 skillid, uint8 in_race, uint8 in_class, uint16 in_level)
{
	uint8 skill_level = 0, skill_formula = 0;
	uint16 base_cap = 0, skill_cap = 0, skill_cap2 = 0, skill_cap3 = 0;
	

	//Fetch the data from DB.
	std::string query = StringFormat("SELECT level, formula, pre50cap, post50cap, post60cap from skillcaps where skill = %i && class = %i", skillid, in_class);
	auto results = QueryDatabase(query);

	if (results.Success() && results.RowsAffected() != 0)
	{
		auto row = results.begin();
		skill_level = atoi(row[0]);
		skill_formula = atoi(row[1]);
		skill_cap = atoi(row[2]);
		if (atoi(row[3]) > skill_cap)
			skill_cap2 = (atoi(row[3])-skill_cap)/10; //Split the post-50 skill cap into difference between pre-50 cap and post-50 cap / 10 to determine amount of points per level.
		skill_cap3 = atoi(row[4]);
	}

	int race_skill = GetRaceSkill(skillid,in_race);

	if (race_skill > 0 && (race_skill > skill_cap || skill_cap == 0 || in_level < skill_level))
		return race_skill;

	if (skill_cap == 0) //Can't train this skill at all.
		return 255; //Untrainable

	if (in_level < skill_level)
		return 254; //Untrained

	//Determine pre-51 level-based cap
	if (skill_formula > 0)
		base_cap = in_level*skill_formula+skill_formula;
	if (base_cap > skill_cap || skill_formula == 0)
		base_cap = skill_cap;

	//If post 50, add post 50 cap to base cap.
	if (in_level > 50 && skill_cap2 > 0)
		base_cap += skill_cap2*(in_level-50);

	//No cap should ever go above its post50cap
	if (skill_cap3 > 0 && base_cap > skill_cap3)
		base_cap = skill_cap3;

	//Base cap is now the max value at the person's level, return it!
	return base_cap;
}

First thing to notice is that this new code correctly releases the query in all cases (and not just under the successful query case), the next thing to notice is that there is no need to free up the results, it's handled automatically. The same applies to the error buffer (which had space reserved in the original version but was never used). In this version the error message is not only handled automatically, it's also only reserved when an error actually occurs.

 

§Translating the old broken to the new hotness

No need to create:

  • char errbuf[MYSQL_ERRMSG_SIZE];
  • MYSQL_RES *result;
  • MYSQL_ROW row;
  • uint32 row_count = 0; / mysql_num_rows
  • uint32 affected_rows = 0;
  • uint32 last_insert_id =0;
  • uint32 error_num = 0;

These are all handled by the MySQLRequestResult class which is the return type of QueryDatabase(const char* query, bool retryOnFailureOnce = true)

The old RunQuery returns true on success and false on failure. It's sugested in the new style to use:

auto results = QueryDatabase(query);

and use results.Success() to get the success or failure of the query.

rows are returned in an stl style, a single row query can get the single row through:

auto row = results.begin();

while multi row queries are traditionally done with a while looping and requesting of rows, in the new style it's done with the standard stl for iterator pattern:

for (auto row = results.begin(); row != results.end(); ++row)
{
   // use row[0] here
}

when range based for behavior goes live this is the suggested format:

for (auto row : results)
{
   // use row[0] here
}
  • affected_rows is replaced with results.RowsAffected();
  • last_insert_id is replaced with results.LastInsertedID();
  • error_num is replaced with results.ErrorNumber();
  • row_count / mysql_num_rows is replaced with results.RowCount();
  • errbuf is replaced with results.ErrorMessage();

§Benefits and the future

In general, using the new style query provides improved code quality and reduces the amount of effort for the programmer. There is currently a second form of this query for char * which should always be used when working with BLOB's, when BLOB's are removed so will the alternate char* query form be removed.