Jump to content
Sign in to follow this  
rocket

MySQL and ArmA2

Recommended Posts

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 by Rocket

Share this post


Link to post
Share on other sites

Please sign in to comment

You will be able to leave a comment after signing in



Sign In Now
Sign in to follow this  

×