MySQL and ArmA2

[rocket 9]

Since formally announcing the USEC Revolution system I've been inundated with requests for how to do this. Here's a really quick outline of how to do it, using JayArmA2Lib. More detail will be provided when the entire source is released.

The system is made up of the following:

• ArmA2 Dedicated Server Install
  
• JayArmA2Lib
  
• MySQL 5.1
  
• Custom Console App: "Mother"
  
• C++ MySQL Connector
  

Mission Transactions

Inside the USEC Revolution system, "transactions" are created which are passed to Mother and processed. One option, would be to directly pass SQL but I preferred to leave the SQL out, for security reasons.

Transactions are sent in a string, and then broken into pieces to be processed by mother. Here is an example of it breaking apart the transactions:

/* Read from pipe	*/
std::string output = GetPipeString();
cout << "Received...";
cout << output << endl;

/* FINISHED PROCESSING TRANSACTION */
std::vector<std::string> transKey = TransactionDecode(output);
std::string trCode = transKey[1];
int trCodeInt = atoi(trCode.c_str());

This uses my "TransactionDecode" functions:

std::vector<std::string> TransactionDecode(std::string input)
{
std::vector<std::string> transKey;
boost::split(transKey, input, boost::is_any_of(":"));
//transKey.pop_back(); //Remove garbage from the end
return transKey;
}

This uses my "GetPipeString" function:

std::string GetPipeString()
{
/*	
	READ FROM PIPE
	Example:	std::string output = GetPipeString();
*/
char	buf[bUFSIZE];			//buffer for receiving
DWORD	dwBytesRead;
std::stringstream	ss;//create a stringstream
std::string		output;
ReadFile (hPipe,buf, BUFSIZE, &dwBytesRead, NULL);
ss << buf;//add number to the stream
output = ss.str();
output.erase(output.end() - 1);	//remove the garbage character
return output;
}

Here is a sample transaction in the mission, generated by the SERVER (clients can generate requests to the server, who then passes them on) whenever the location of a vehicle needs to be updated:

_key = format["CHILD:302:1:%1:%2:%3:%4:%5:%6:%7:%8:%9:%10:",USEC_RegionID,_vehID,_locX,_locY,_locZ,_dir,0,0,USEC_LocID,USEC_LocType];
_result = [_key,false] spawn USEC_Fnc_MotherRequest;

The USEC_Fnc_MotherRequest is as follows:

/***********************************************************
REQUEST FROM MOTHER
- Function
- result = [_key] call fnc_usec_mother_request;
************************************************************/
private["_key","_pipe","_result","_resultArray","_buildings","_stream","_data"];
_key = _this select 0;
_read = _this select 1;
_resultArray = [];

if(!IsServer) then ExitWith;
sleep 0.5;
//Wait until not in use
waitUntil{!(USEC_MotherInUse)};
USEC_MotherInUse = true;
scopeName "usePipe";
while {true} do {
	scopeName "pipeWaiting5";
	_pipe = [uSEC_MotherPipe1] call jayarma2lib_fnc_openpipe;
	if ((_pipe != "_JERR_PIPE_INVALID")) then {breakOut "pipeWaiting5";};
	sleep 0.01;
};
//Send Key
_ret = [_pipe, _key] call jayarma2lib_fnc_writepipe;

//Wait until MOTHER replies
while {true} do {
	scopeName "pipeWaiting6";
	_data = [_pipe] call jayarma2lib_fnc_readpipe;
	if (!(isNil "_data") and (_data != "_JERR_NULL")) then {breakOut "pipeWaiting6";};
	sleep 0.01;
};

if((_read) and (_data == "_JERR_FALSE")) then {
//Retry the read
	[_pipe] call jayarma2lib_fnc_closepipe;
	breakTo "usePipe";
};

//Release Mother
//waitUntil{USEC_SystemMessage == ""};
//USEC_SystemMessage = format["SYSTEM: Read: %1, Received: %2",_key,_data];
//PublicVariable "USEC_SystemMessage";
sleep 0.5;
[_pipe] call jayarma2lib_fnc_closepipe;
sleep 0.5;
USEC_MotherInUse = false;

//Return information
_resultArray = call compile format ["%1",_data];
_resultArray;

Mother

The "core" of this system is called Mother. This is in reference to the "Mother" system in the Alien movies. Essentially, Mother creates a named pipe, which can be shared with JayArmA2Lib. Think of this as a dedicated telephone system between ArmA2 and a custom application.

Now, let me be clear. Named Pipes are a reasonably advanced concept, and to get MySQL working you are going to need to have an excellent knowledge of named pipes and a sound knowledge of C++, but it isn't impossible to achieve for a dedicated individual with some coding knowledge and, alot of patience, in a few weeks.

Here is a sample of the processing of a transaction. This transaction streams in buildings into ArmA2 on server restart, creating the persistent world:

case 204 : 
/* Stream Region Buildings */
cout << "Streaming Buildings...";
try {
	//Fetch Region Items
	stmt = con->createStatement();
	res = stmt->executeQuery("SELECT ItemID, ItemClass, ItemX, ItemY, ItemZ, ItemDir, ItemHealth, ItemFuel FROM Region_ITEMS WHERE((Region_ITEMS.RegionID = " + transKey[3] + ") AND (Region_ITEMS.ItemHealth > 0));");
	while (res->next()) {
		ItemID = res->getInt("ItemID");
		ItemClass = res->getString("ItemClass");
		ItemX = res->getDouble("ItemX");
		ItemY = res->getDouble("ItemY");
		ItemZ = res->getDouble("ItemZ");
		ItemDir = res->getDouble("ItemDir");
		ItemHealth = res->getDouble("ItemHealth");
		ItemFuel = res->getDouble("ItemFuel");
		//Add building to string
		trBuilding = "['" + ItemClass + "',[" + convertDbl(ItemX) + "," + convertDbl(ItemY) + "," + convertDbl(ItemZ) + "]," + convertDbl(ItemDir) + "," + convertDbl(ItemHealth) + "," + convertInt(ItemID) + "," + convertDbl(ItemFuel) + "]";

		//Send building to ArmA2
		result = PutPipeString(trBuilding);

		//Wait for response
		std::string trResultStr = GetPipeString();
		while (trResultStr.length() == 0) {
			trResultStr = GetPipeString();
			Sleep(sysStream);
		};
		cout << "OK!..";
	};
	//Default outcome is pass only
	tOutput = "['PASS']";
} catch (sql::SQLException &e) {
	//Process mySQL error
	tOutput = "['ERROR']";
	printSQLException(e,__LINE__);
}	
break;

The Mother-to-Mission "Handshake"

This is one of the hardest bits. Named pipes can be a bit trickey to syncronize.

Here is an example of a handshake mixed with JayArmA2Lib:

/* STREAM INVENTORY */
//Wait for mother then open
_pipe = [uSEC_MotherPipe1] call jayarma2lib_fnc_openpipe;

//Send the key
_key = format["CHILD:208:%1:Airport:",LHA_NumID,LHA_Type];
_ret = [_pipe, _key] call jayarma2lib_fnc_writepipe;	
_data = "";

sleep 0.5;

//Wait until MOTHER replies
while {true} do {
	_data = "";
	scopeName "pipeWaiting10";
	_data = [_pipe] call jayarma2lib_fnc_readpipe;
	if (!(isNil "_data") and (_data != "_JERR_NULL")) then {breakOut "pipeWaiting10";};
	sleep 0.01;
};
sleep 0.1;

//Process result
_result = call compile format ["%1",_data];
_status = _result select 0;
_countr = 0;

while { _status != "PASS" } do {
	if (count _result > 1) then {
		_countr = _countr + 1;
		//Parse Array
		_type = _result select 0;
		_qty = _result select 1;
		_class = "cfgWeapons";
		_typeW = isClass (configFile >> "cfgWeapons" >> _type);
		_typeM = isClass (configFile >> "cfgAmmo" >> _item);
		if (_typeW) then {
			_class = "cfgWeapons";
		} else {
			_class = "cfgMagazines";
		};
		if (_typeM) then {
			_type = "cfgMagazines";
		};
		//Create it
		USEC_LogisticsItems = USEC_LogisticsItems + [_type];
		USEC_LogisticsDetail = USEC_LogisticsDetail + [[_class,_qty]];
	};
	_ret = [_pipe, "['OK']"] call jayarma2lib_fnc_writepipe;
	sleep 0.01;			
	//Grab next item				
	while {true} do {
		scopeName "pipeWaiting11";
		_data = [_pipe] call jayarma2lib_fnc_readpipe;
		if (!(isNil "_data") and (_data != "_JERR_NULL")) then {breakOut "pipeWaiting11";};
		sleep 0.01;
	};
	//Process result
	_result = call compile format ["%1",_data];
	_status = _result select 0;
};

I'll expand this into a wiki article when time allows, in the meantime, any questions please post them below and I will do my best to answer.

Edited September 28, 2011 by Rocket