MySQL and ArmA2

[Rocket]

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.