Arma2MySQL

[firefly2442 6]

This program acts as an intermediary between Arma2/Arma3 and a MySQL or SQLite database. It uses the

Arma2NET wrapper system. It is written in C#. The program has to be run on the same system as your Arma2/Arma3 server, however, the MySQL database can be either local or remote. The SQLite database runs on the local machine.

Arma2NETMySQLPlugin:

https://github.com/firefly2442/Arma2NETMySQLPlugin

You can download compiled versions of the plugin here (the current version is v0.1.0.0-stable):

https://www.dropbox.com/sh/ngix8aunt9nnwj7/ZHt1agEeM0?lst

The beta testing version is: Arma2NETMySQLPlugin_v0.2.0.0-beta1.zip. This uses the new (under development) version of Arma2NET and is Windows only (at this time). There are some interface changes so you will need to make adjustments to existing missions that use the current stable 0.1 version. There is updated documentation in the zip folder itself. You should not need to install the MySQL or SQLite dependencies as they are already provided. There are also some minor security improvements and the example mission is updated to reflect this. I'm working on a Linux port but have been quite busy lately so it might be awhile. As always, testing is most appreciated.

There is also a tutorial video available for download in the above link. It is slightly old and only shows Arma 2 instructions, but the instructions and setup for Arma 3 are essentially the same.

Arma 2 Example Mission (currently deprecated, see the Arma 3 example instead):

https://github.com/firefly2442/Arma2NetMySQLPlugin-ExampleMission

Arma 3 Example Mission:

https://github.com/firefly2442/Arma2NetMySQLPlugin-Arma3-ExampleMission

History:

This program has had a long history. Initially, the program used JayArma2Lib because DLL injections were the only way to

pass information between Arma2 and the outside. Eventually, BIS added callExtension

which allowed for a more structured (non-hacky) way to pass information. JayArma2Lib switched to this and things continued. Development

was done here and an example mission was

here. Technically, all that code should still work but the bad

part was all the formatting and ugly stuff that had to be done in game via the SQF scripting in order to get the data into a reasonable

format. It involved iterating through strings and creating arrays and generally was still pretty hacky. Then,

Arma2NET came along and things got better. This wrapper system allowed us to

return nice results back to Arma2 and not have to deal with all the formatting nonsense. I ended up just copying the bits and pieces

that were needed from the past project and creating a new application. Development is all done via the Github pages. In addition,

the Persistent Database Scripts

system as created by [KH]JMan currently uses builds of this program

for saving data.

Development initially started over at AlphaSquad (thanks guys, you rock!).

http://www.alphasquad.net/forum/viewtopic.php?f=71&t=3039

Please read the documentation to get started. The plugin and each mission have a readme file. There is a lot of information in the readme and it should have everything you need to get started. If something is confusing or you don't understand the readme, please let me know.

Translations of the readme to other languages has started.

If you have questions of problems related to this program specifically, please post here. All problems related to missions or mods that use this program should be posted in their appropriate threads. Thanks!

Edited August 14, 2014 by firefly2442

[eddieck 10]

The main problem I see with persistent data systems in ArmA is that anyone can change the data. (Even if you disallow certain queries, someone with a script hack can still modify the data of other players.)

Other than that, this looks like a cool project, at least if it's used on private/passworded servers.

For a safer persistence system, has anyone looked into using sendUDPMessage and setting up a separate stat save system? You could then enforce certain rules on data modification (i.e. cross-reference with BE RCon and only allow users to modify their own data). Data retrieval could be done using the same methods (clipboard, JA2L, etc.).

[firefly2442 6]

So all .sqf connections and calls have to be done on the server side. So the average Joe player isn't able to make a connection. It is also protected by the mysql stored procedures. If the query doesn't exist, it doesn't run. Now that still leaves the possibility of sql-injections and such but my main concern at the time was just getting this thing running.

I hadn't heard of this UDPMessage command, that looks promising as well.

[eddieck 10]

So all .sqf connections and calls have to be done on the server side. So the average Joe player isn't able to make a connection. It is also protected by the mysql stored procedures.

Even if you have a changeScore PVEH (for example), don't you still have to pass on an identifier (player ID or unit name) for the player whose score you want to change? If so, what I mentioned would still be possible.

Assuming you are using a PVEH to trigger the update, I don't believe there's a way to determine which client sent the PV update. I may be wrong on this, however. (The wiki shows two arguments for the PVEH: variable name and new value.)

The ideal solution would be for BIS to add an "updater" argument for PVEHs.

[kremator 1065]

Perhaps look at security holes later .... for now open the possibilities of function right up!

[brightness 12]

I am saying - If this get working, I am going to dance and it will make most of my ideas happen!

EDIT: Question mains still, will I able to create a function "UCP" aka you can purchase weapons/set your gear from the website and it saves in the DB and then when you go in game - It will retrieve data about your user and see if it fits your username and give you the set gear that u have given in the site

Edited December 26, 2011 by brightness

[firefly2442 6]

Absolutely, that's the idea. While I didn't write an online interface, it would be relatively simple to have something where you could just manipulate the database and pick/edit your gear. The idea is to be able to modify it both in-game and outside.

I should stress that this is just an example. The application is generic enough so that you can create your own databases and stored procedures and really store whatever information you want. It's up to the person making the missions on how to use it.

[brightness 12]

Perfect, I will take my time sir and make a UCP :) When it comes out in full version ^^ And hand a example to people out there if its necessarily.

EDIT: I think I am going to boost of my VB studio CD and fresh up my C# coding and get this going ^^ If I want something to happen, I have to get my boot on it!

Also - Linux support possibility? (Idea)

ISSUE: I found out when I dled the GIT that I am missing mysqldata, is it a plugin you have or you scripted it?

DOH Found out I need mysql connector ^^ My fault :P

Edited December 27, 2011 by brightness

[firefly2442 6]

Yes, so Linux support might be possible with Mono:

http://www.mono-project.com/Main_Page

I never tried it but the C# code itself is pretty simple. I bet it might work.

[brightness 12]

I will try that on the new dedi server we will get in next day or I will try on VWare but at moment I have a issue when I "build" the C# code, I get 3 errors:

Error 2 'MySql.Data.MySqlClient.MySqlCommand' does not contain a definition for 'Clone' and no extension method 'Clone' accepting a first argument of type 'MySql.Data.MySqlClient.MySqlCommand' could be found (are you missing a using directive or an assembly reference?) C:\msysgit\msysgit\arma2-mysql\Arma2SQL.MySQLProvider\MySQLProvider.cs 130 51 Arma2SQL.MySQLProvider

Error 3 'MySql.Data.MySqlClient.MySqlCommand' does not contain a definition for 'Clone' and no extension method 'Clone' accepting a first argument of type 'MySql.Data.MySqlClient.MySqlCommand' could be found (are you missing a using directive or an assembly reference?) C:\msysgit\msysgit\arma2-mysql\Arma2SQL.MySQLProvider\MySQLProvider.cs 142 53 Arma2SQL.MySQLProvider

I haven't looked into it as I had to go but when I look at the code it looks fine, I am un sure if its because of my .NET(4.0 & 1.1) or mysql connector version(1.10)

EDIT: Fixed all this - Looked into it and I am not hitting in any issues right now except what JMAN reported in but he gave me a fix for it.

I am still trying to find the "Variables" for the class because I am trying to set the gear he choose from the loadout ^^

And right now I find 0 explanation of each variable

Edited December 27, 2011 by brightness

[[kh]jman 49]

Absolutely, that's the idea. While I didn't write an online interface, it would be relatively simple to have something where you could just manipulate the database and pick/edit your gear. The idea is to be able to modify it both in-game and outside.

Indeed. I have already written this for Arma2MySQL. See here and here. We have been testing and using my scripts very successfully now for a few months.

I have written all the .sqf scripts needed to be dropped into a mission to save persistent data. There are still some issues that need to be sorted plus some frontend ajax and forms that still need to be done. I have been working on this on my own for over 7 months now so I am a little reluctent to make my work so far public however it seems that what you want to achieve I have already pretty much done 80% of. I have spent many, many hours on these. With time more could be added.

With this in mind how about we maybe merge these 2 projects together. The more devs the better, I am finding less and less time to finish all this on my own. I have an SVN with all my .sqf code available plus a full mysql DB schema. I had plans to port this to Arma3 in some form in the future.

Edited December 28, 2011 by [KH]Jman

[firefly2442 6]

Yeah totally. I didn't realize you had gotten that far with the system you are working on, I don't keep up with the forums very much. That's awesome.

What needs to be finished up on your end? It sounds like yours is much closer to seeing the light of day at this point. :)

For Arma3: this is an interesting little tidbit:

https://twitter.com/#!/Arma3official/status/147720004727611392

---------- Post added at 05:28 AM ---------- Previous post was at 05:26 AM ----------

I am still trying to find the "Variables" for the class because I am trying to set the gear he choose from the loadout ^^

And right now I find 0 explanation of each variable

Hmm, not sure what you mean, can you elaborate?

[[kh]jman 49]

Thats great, ok I will have a word with Sickboy over at DH about merging our two projects into one and calling it something like 'Arma2MYSQL and PersistentDB'. Then I will upload all my code/scripts to the new DH repository ready for the new devs to get their teeth into it. I will also add the compiled Arma2MYSQL binaries + support files to the new repo since the old Arma2MYSQL only contains the source atm.

On the A3 subject, yes I was aware of the upcoming Java support. I have also heard on the grapevine that A3 may also include a DB API but that is certainly not confirmed at this stage by anyone at BIS.

Edited December 28, 2011 by [KH]Jman

[brightness 12]

---------- Post added at 05:28 AM ---------- Previous post was at 05:26 AM ----------

[/color]

Hmm, not sure what you mean, can you elaborate?

You replied it on my PM ^^ Made my day easier :P

[[kh]jman 49]

Ok Sickboy has very kindly given us a new project so we can start to migrate our two projects over to this new one and then we can remove our old projects.

The new project link is:

http://dev-heaven.net/projects/a2-sql-persistent-db

[kremator 1065]

This is very exciting news guys. Good to see such wonderful cooperation and sharing!

[[kh]jman 49]

I'll get my source files up asap as well as some example missions, docs and the DB schema. I'll also add the compiled a2mysql binaries + support files for the Windows server.

If anyone has the skills and would like to contribute and join the dev team plz ask.

[[kh]jman 49]

For anyone who would like to have a play with the files I have uploaded them to: http://dev-heaven.net/projects/a2-sql-persistent-db/files

Please feel free to chip in with additions/improvements as I'm loosing the will to finish this on my own. Please remember use it at your own risk.

Edited December 31, 2011 by [KH]Jman

[[kh]jman 49]

Brightness has very kindly come on board. He's added the git repo with all the files needed. You will find them here.

[firefly2442 6]

Awesome. I've started looking at them. I'll lurk over at the KH teamspeak server, maybe someone can pop in if you have a minute.

[firefly2442 6]

An interesting comment here:

https://dev-heaven.net/issues/9436#note-27

[[kh]jman 49]

Dwarden's comment refers to the new call extension API thats present in beta 1.60.87662 and above.

It's implemented in the latest jay2lib and working with our project just fine :)

[brightness 12]

Jman;2084381']Dwarden's comment refers to the new call extension API thats present in beta 1.60.87662 and above.

It's implemented in the latest jay2lib and working with our project just fine :)

I confirm that, its been running smooth.

JMAN I was thinking about implement in the PDB UCP a way for the admins to insert guns into their database where the player can select his gear.

Sounds good??

[[kh]jman 49]

There is an edit player loadout feature in the UCP which is pretty much finished (ACE backpack and weapon on back support needs adding), it's based on the logged in user type privilages, i.e demo privilage: view user data only, user privilage: view/edit user data, administrator privilage: view/edit all user's data. You can set an xml file in the UCP which is specific to a mission which filters the available loadout pool of weapons, ammo and equipment that a user can choose from. It's all undocumented at the moment (though if you look through the php/jquery code you'll see how it works) so I'll have to take you through it on Teamspeak at some point then you should be able to complete/extend/fix it etc... There is a screenshot of the edit player loadout here

Edited January 9, 2012 by [KH]Jman

[brightness 12]

I was thinking about making the loadout system in SQL as I am working on a armoury system for my community with currency system which is still under progress but what my idea was that adding the weapons in a table and print it out in a box, example:

$grabdata = mysql_query("SELECT * FROM loadouts");

while ($row = mysql_fetch_array($grabdata, MYSQL_NUM)) {

//EXAMPLE : FORM CODE HERE WITH ROW[0]

}

and then read from "retrieveloadout.php" and get each row name and insert/update the player data which is possible, right?