extDB (arma3 extension linux/windows)

[torndeco 128]

I currently looking at doing rewriting a new MySQL/MariaDB Database Extension.
Will also aim at making it back compatiable with SQL_CUSTOM_V2 files to ease switching over.
Am expecting to have it ready in under a weeks time.

Changes:
Switching over to MariaDB C Driver from MySQL C Driver.
Dropping SQLite Support, to my knowledge no-one was really using it at all.
Dropping RCon & Steam Support initially, but plan to re-add at later stage.

Improved performance by directly using the C Driver instead of library will avoid unnecessary memory allocations etc..
Support for all/most MySQL Datatypes & handle long text etc...
Support for proper raw SQL Statements & Procedures etc...

-----------------

License Changes:
The extension will be packaged together as a serversided addon licensed under
 Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License.
Note: This is the same license as ALIVE and lots of other addons...


If a project really needs to they can ask me for permission to repackage the extension.
As a rule of thumb, once your project is either one of Arma Public Licenses or an Open Source Initiative Approved License it should be fine.
If i don't answer that doesn't mean you automatically have permission.

Also Server Monetization once you follow BIS Rules is also fine.

[torndeco 128]

Would appreciate if anyone has a populated Database backup they can share.
Will help save me some time in testing, doesn't matter mission system etc.... Send via PM  thanks

If testing goes well, i will compile Linux & Windows versions & should be a new release tomorrow.
Currently the extension on windows is around 1.1MB in size :) & updated WIKI is almost done
Thanks
 

[Guest]

Hello torndeco,

I don't know if you are still using Poco library but can you consider supporting PostGre SQL support. ("A real database").

I know that MariaDB is a real improvement of MySQL but I'm very frustrated to use the MySQL "standards". As you shall know MySQL does not follow the SQL Norms (Only by ~60%) whereas Postgre is 90+%. Anyway you have more performance with a non-oriented file database.

It may be useless as most of the Arma queries are ridiculously small but yeah, why not.

[torndeco 128]

No i have dropped Poco Library altogether, that way i could support MySQL fully including LongText / Procedures.

But PostgreSQL does have a c/c++ connector and under a decent license, so it shouldn't take to much work to add it in.
The problem is no-one would use it, everyone is more familiar with MySQL.

If you are secreting working on an arma mod that will have public serverfiles & want to use PostgreSQL, then i could look at it adding it in.
But otherwise its not worth the work imo, but you never know i might get bored at some point.

[ozdeadmeat 12]

Myself and a few others I know do alot of DEV stuff with SQLite and some folks use it for simple stats tracking, leaving it it would be hugely appreciated. BTW, love your work and thank you for supplying IMO one of the most important extensions to the Arma 3 community. Life, Exile, Epoch, KOTH all wouldn't be as well received and as wide spread without your extension.

 

Is there any way you can put the wiki back online for us peasants who were just starting out with your extension until your new extension is available? I need SQLite myself so can't transfer yet.

[torndeco 128]

@ozdeadment
Thanks & Epoch use their own extension, not sure what KOFH is using :)

@Everyone
Anyway first test build for extDB3 is out if anyone wants to try/test it out.
Windows builds only atm, i need to install & setup a linux build enviroment again.

Recommend only for testing atm, atleast until the weekend.
There might be some bugs (alot got change code wise), but it should be good.

https://bitbucket.org/torndeco/extdb3/wiki/Home
https://bitbucket.org/torndeco/extdb3/downloads
 

[nesias 16]

@torndeco

thanks for releasing extdb3, but it looks like i already found a bug.

 

When calling extdb3 and retrieving data from a table, you need to add a " ] " to the answer string befor you can call compile it.

using sqf code like this:
"extDB3" callExtension "0:SQL1:SELECT * FROM test"
gets this return:
"[1,[[1,["testa", 1]],[2,["testb", 2]]]"
which probably should be:
"[1,[[1,["testa", 1]],[2,["testb", 2]]]]"

This bug happens before and after LOCK, but not if i use something like "9:LOCK" ("9:LOCAL_TIME" works fine).

 

Database Protocoll is "SQL" (SQL_Raw throws a unkown protocol error).

Database Server is MySQL 5.7.11 Win64 (x86_64) (Community Version).

Arma 3 Version is 1.58.135742 .

OS is Win7 64bit.

Only Mod used is extDB3.

Battleeye is switched off.

 

p.s.:

I almost forgot another one, after starting arma i got a little popup saying "Script \system\fn_preInit.sqf not found".

So far i can confirm that the file is included in the .pbo and there is an empty entry in "Functions Viewer", but Arma can't find it.

[torndeco 128]

@nesias

Thanks those stupid bugs are fixed now, new version @
https://bitbucket.org/torndeco/extdb3/downloads

[Guest]

Sorry if it does not make sense, but what query will be run in MySQL with this statement in Arma 3?

"extDB3" callExtension '0:SQL1:INSERT INTO players (backpack) VALUES(''["",[[],[],[]]'') WHERE ID = 5'

This:

INSERT INTO players (backpack) VALUES('["",[[],[],[]]') WHERE ID = 5

Or this?

INSERT INTO players (backpack) VALUES('[",[[],[],[]]') WHERE ID = 5

Thankyou.

[torndeco 128]

Recommend you use SQL_CUSTOM, its alot more powerful and safer.
You can use either SQL or Prepared Statements, not to mention you can use mysql_escape for raw SQL.

[Default]
Version = 1  
;; Used incase there is ever a breaking change, or to inform user in log about a new feature.
Strip Chars = ";[]"
;; List of characters to strip out  
Strip Chars Mode = 0
;; 0 = Strip Bad Chars, 1 = Strip + Log Bad Chars, 2 = Return Error & Log Bad Chars  
;;     Note: Logging on works when sending data to database.


[updatePlayerBackpack]
Prepared Statement = true
;;   If set to false, uses SQL instead of Prepared Statement
;; Return InsertID = false
;; Strip Chars = ""
;; Strip Chars Mode = 0
;;    Incase you want to override the Strip Chars from [Default]

SQL1_1 = UPDATE players SET backpack = ? WHERE id = ?
SQL1_INPUTS = 2,1

[updatePlayerBackpack2]
;;Example of Raw SQL Version
Prepared Statement = false
;;   If set to false, uses SQL instead of Prepared Statement
;; Return InsertID = false
;; Strip Chars = ""
;; Strip Chars Mode = 0
;;    Incase you want to override the Strip Chars from [Default]

SQL1_1 = UPDATE players SET backpack = "$CUSTOM_2$" WHERE id = $CUSTOM_1$
SQL1_INPUTS = 2-mysql_escape,1-mysql_escape

"extDB3" callExtension format["0:SQL_CUSTOM:updatePlayerBackpack:%1:%2",_id,_backpack];  // Sync
"extDB3" callExtension format["1:SQL_CUSTOM:updatePlayerBackpack:%1:%2",_id,_backpack];  // Aysnc = faster

[Guest]

My Arma 3 Server is crashing with those configuration/settings. Help is apreciated.

 

My @extDB3\extdb3-conf.ini file:

[Main]

Version = 1

Randomize Config File = false

Allow Reset = false

Thread = 0;


[Log]

Flush = true;


[brpvp]

IP = 127.0.0.1

Port = 3306

Username = root

Password = ********

Database = brpvp

My @extDB3\sql_custom\brpvp.ini file:

[Default]

Version = 1  

Strip Chars = ";[]"

Strip Chars Mode = 0


[createPlayer]

Prepared Statement = true

SQL1_1 = INSERT INTO players (steamID,exp,model,gear) VALUES(?,?,?,?)

SQL1_INPUTS = 1,2,3,4

The code on server side to load database and database protocol:

"extDB3" callExtension "9:ADD_DATABASE:brpvp";

"extDB3" callExtension "9:ADD_DATABASE_PROTOCOL:brpvp:SQL_CUSTOM:P100:brpvp.ini";

The code to add a new player to the data base (it crash here):

"extDB3" callExtension format["0:P100:createPlayer:%1:%2:%3:%4",_steamId,_xp,_model,_gear];

[nesias 16]

Okay, looks like i have 2 or 3 Bugs.

ExtDB3 Version is 1.001.

 

First one:

I can't get UNLOCK to work, everytime i try to unlock using a code (empty "9:UNLOCK:", correct code or wrong code) i get an empty string as return and its still locked.

All tries without a code (just "9:UNLOCK") will return an error message (as it should).

 

Second one:

Every time i try to retrieve a message using the "4:<ID>" methode i only get "[3]" as answer, im not sure if this is a bug or if something is wrong on my side. If somebody else encounters this, please say so. (Protocol is SQL)

 

Third one:

There is no message if i try to use ADD_DATABASE with a non existing or existing config entry. I only get a message if the DB is already connected.

 

@torndeco

One question, would you prefer to get these bug reports here or should we use the issue function of bitbucket?

[torndeco 128]

If you want in future try put bug reports @ https://bitbucket.org/torndeco/extdb3/issues?status=new&status=open
But thanks for reports


@donnovan

That should be fixed now, new version up on https://bitbucket.org/torndeco/extdb3/downloads

[torndeco 128]

@nesias
Unlocking issues & Add Database will be fixed later today.
Literally  just compiled a new version afew minutes ago

But i need more info for your other issue, to replicate it

Replace @extDB3\extDB3.dll with the one from If you look in Optional\Debug Files\extDB3-debug.dll
i.e you will need to rename file or change your code to "extDB2-debug" callExtension

Then replicate your issue when trying to fetch results.
That way i can see exactly how you are calling it

Thanks.

[nesias 16]

@nesias

Unlocking issues & Add Database will be fixed later today.

Literally  just compiled a new version afew minutes ago

But i need more info for your other issue, to replicate it

Replace @extDB3\extDB3.dll with the one from If you look in Optional\Debug Files\extDB3-debug.dll

i.e you will need to rename file or change your code to "extDB2-debug" callExtension

Then replicate your issue when trying to fetch results.

That way i can see exactly how you are calling it

Thanks.

 

EDIT:

Okay i do have an idea why it happens. (Still Version 1.001)

[07:19:08:134360 +02:00] [Thread 8120] extDB3: Input from Server: 9:VERSION
[07:19:08:134360 +02:00] [Thread 8120] extDB3: Output to Server: 1.001
[07:19:33:476809 +02:00] [Thread 8120] extDB3: Input from Server: 9:VERSION
[07:19:33:476809 +02:00] [Thread 8120] extDB3: Output to Server: 1.001
[07:19:38:576101 +02:00] [Thread 8120] extDB3: Input from Server: 9:LOCK_STATUS
[07:19:38:576101 +02:00] [Thread 8120] extDB3: Output to Server: [0]
[07:19:38:577101 +02:00] [Thread 8120] extDB3: Input from Server: 9:ADD_DATABASE:DB_1:DB_A
[07:19:38:582101 +02:00] [Thread 8120] extDB3: Output to Server:
[07:19:38:582101 +02:00] [Thread 8120] extDB3: Input from Server: 9:ADD_DATABASE_PROTOCOL:DB_A:SQL:SQL_A:TEXT
[07:19:38:582101 +02:00] [Thread 8120] extDB3: SQL: Initialized: Add Quotes around TEXT Datatypes: true
[07:19:38:582101 +02:00] [Thread 8120] extDB3: SQL: Initialized: NULL = ""
[07:19:38:582101 +02:00] [Thread 8120] extDB3: Output to Server: [1]
[07:20:11:527986 +02:00] [Thread 8120] extDB3: Input from Server: 2:SQL_A:SELECT * FROM market_test.storage;
[07:20:11:527986 +02:00] [Thread 8120] extDB3: Output to Server: [2,"100"]
[07:20:11:527986 +02:00] [Thread 2984] extDB3: SQL: Trace: Input: SELECT * FROM market_test.storage;
[07:20:11:528986 +02:00] [Thread 2984] extDB3: SQL: Trace: Result: [1,[[1,"oil",1000,10,10000,"playera","001"],[2,"beton",10,1,10,"playerb","001"]]]
[07:20:19:155422 +02:00] [Thread 8120] extDB3: Input from Server: 4:100
[07:20:19:155422 +02:00] [Thread 8120] extDB3: Output to Server: [1,[[1,"oil",1000,10,10000,"playera","001"],[2,"beton",10,1,10,"playerb","001"]]]
[07:21:00:654795 +02:00] [Thread 8120] extDB3: Input from Server: 9:RESET
[07:21:00:695798 +02:00] [Thread 8120] extDB3: Output to Server: [1]
[07:21:05:135052 +02:00] [Thread 8120] extDB3: Input from Server: 9:LOCK_STATUS
[07:21:05:135052 +02:00] [Thread 8120] extDB3: Output to Server: [0]
[07:21:05:135052 +02:00] [Thread 8120] extDB3: Input from Server: 9:ADD_DATABASE:DB_1:DB_A
[07:21:05:136052 +02:00] [Thread 8120] extDB3: Output to Server:
[07:21:05:136052 +02:00] [Thread 8120] extDB3: Input from Server: 9:ADD_DATABASE_PROTOCOL:DB_A:SQL:SQL_A:TEXT
[07:21:05:136052 +02:00] [Thread 8120] extDB3: SQL: Initialized: Add Quotes around TEXT Datatypes: true
[07:21:05:136052 +02:00] [Thread 8120] extDB3: SQL: Initialized: NULL = ""
[07:21:05:136052 +02:00] [Thread 8120] extDB3: Output to Server: [1]
[07:21:09:802319 +02:00] [Thread 8120] extDB3: Input from Server: SELECT * FROM market_test.storage;
[07:21:09:802319 +02:00] [Thread 8120] extDB3: Error Invalid Message: SELECT * FROM market_test.storage;
[07:21:09:802319 +02:00] [Thread 8120] extDB3: Output to Server: [0,"Error Invalid Message"]
[07:21:18:087793 +02:00] [Thread 8120] extDB3: Input from Server: 2:SQL_A:SELECT * FROM market_test.storage;
[07:21:18:087793 +02:00] [Thread 8120] extDB3: Output to Server: [2,"101"]
[07:21:23:292090 +02:00] [Thread 8120] extDB3: Input from Server: 4:101
[07:21:23:292090 +02:00] [Thread 8120] extDB3: Output to Server: [3]
[07:21:30:363495 +02:00] [Thread 8120] extDB3: Input from Server: 4:101
[07:21:30:363495 +02:00] [Thread 8120] extDB3: Output to Server: [3]
[07:21:31:036533 +02:00] [Thread 8120] extDB3: Input from Server: 4:101
[07:21:31:036533 +02:00] [Thread 8120] extDB3: Output to Server: [3]
[07:26:48:265678 +02:00] [Thread 8120] extDB3: Input from Server: 2:SQL_A:SELECT * FROM market_test.storage;
[07:26:48:265678 +02:00] [Thread 8120] extDB3: Output to Server: [2,"102"]
[07:26:54:258020 +02:00] [Thread 8120] extDB3: Input from Server: 4:102
[07:26:54:258020 +02:00] [Thread 8120] extDB3: Output to Server: [3]
[07:26:56:072124 +02:00] [Thread 8120] extDB3: Input from Server: 4:102
[07:26:56:072124 +02:00] [Thread 8120] extDB3: Output to Server: [3]
[07:26:57:152186 +02:00] [Thread 8120] extDB3: Input from Server: 4:102
[07:26:57:152186 +02:00] [Thread 8120] extDB3: Output to Server: [3]

Looks like RESET is somehow causing it. I have a test map for a script and at the end of each test run i used RESET before switching back to Editor so the next time my Init script won't throw an error.

 

EDIT: I have tested it with the v1.002 Debug dll and it is still happening after RESET. Using a script which will try to get any data out resulted in an +20k line big log file with no result except [3].

Edited May 21, 2016 by nesias

[torndeco 128]

Ok new version 1.0.0.3  is up

9:RESET is now fixed

9:ADD_DATABASE now returns output for all scenarios

Known Issues
9:UNLOCK will be fixed later today.

 

Thanks for testing / finding the different bugs...

[Guest]

Hi,

 

Here is the table players:

 

Here a example of data:

armaNaMao is "weapon in hand", the player have not a weapon on his hand on the above example.

ultimaAtualizacao is "date of last update", not used in the mod.

 

I have this Query:
[getPlayer]
Prepared Statement = true
SQL1_1 = SELECT inventario,backpack,posicao,saude,modelo,armaNaMao,amigos,exp,id FROM players WHERE steamKey = ?
SQL1_INPUTS = 1
OUTPUT = 1,2,3,4,5,6-STRING,7,8,9

 

Here is the callExtension call:

_key = format ["0:P100:getPlayer:%1",_pId];
_result = "extDB3" callExtension _key;

_result is "[1,[]]"

 

Here is the error on extDB3 log file:

[17:29:40:321324 -3:00] [Thread 32164] extDB3: SQL: Error MariaDBStatementException2: SQL Invalid Number Number of Inputs Got 1 Expected 0
[17:29:40:321324 -3:00] [Thread 32164] extDB3: SQL: Error MariaDBStatementException2: Input: getPlayer:76561197975554600

[torndeco 128]

If you can in future, make a bug report @ https://bitbucket.org/torndeco/extdb3/issues?status=new&status=open
Makes it easier to follow and ask follow up questions.
Plus include the full log file from extDB3, not a snippet (its a pet hate of mine, everyone does it)

This way i can see the version info & what calls you did to the extension beforehand.
Can come in handy when trying to replicate a problem

[Guest]

torndeco,

 

I will do that.

 

The problem was longtext datatype. I changed to text and its working now.

[torndeco 128]

Weird i thought i had it throw a warning in the log files about Long Text when using Prepared Statements.
I actually re-created the database with Text Values :P
But I double check whats going on there later tonight.


Anyway text is really long enough for 99% of things arma related.
 

Type | Maximum length
-----------+-------------------------------------
TINYTEXT | 255 (2 8−1) bytes
TEXT | 65,535 (216−1) bytes = 64 KiB
MEDIUMTEXT | 16,777,215 (224−1) bytes = 16 MiB
LONGTEXT | 4,294,967,295 (232−1) bytes = 4 GiB

Due to technical details i recommend to only use Medium Text / Long Text Fields with SQL & not Prepared Statements....
This is because the memory is pre-allocated beforehand with prepared statements.
So if you have a query fetching multiple Medium Fields, they will take up 16MB in memory for each value for a split second. Which can become an issue
This shouldn't be an issue with SQL Statements, or just use TEXT Values (which are big enough imo).

-------------------------------------------------------------------

Anyway new version is pushed
Improved Handling MySQL Error Output for SQL_CUSTOM, it wasn't outputing mysql error info in some scenarios.
Fixed 9:LOCK:qwerty   9:UNLOCK:qwery

https://bitbucket.org/torndeco/extdb3/downloads

[Guest]

I will keep it as text datatype, will not use mediun or long text.

[Chewz 23]

Can I just thank you on behalf of the ArmA community Torndeco for developing ExtDB3... without you I have no idea what I would do for a MySQL database integration for my mods. I just hope this time some people respect your requests...

[Guest]

About the license.

 

My mod (not released yet) client side and server side in under APL-SA. It allow derivative work.

ExtDB3 not allow derivatives.

If i modify extDB3 i can go for FOSS exception and distribute the server side with extDB3.

Indeed i don't want to modify extDB3 but use it as is... can i distribute it with my mod server side under APL-SA?

 

Also, as Atribution on APL-SA i would like to the derivative works to have "BRPVP" name as part of their work public tittle (a form of credits). This goes against extDB3 lisence?

[torndeco 128]

@donnovan 

If you are releasing as APL-SA go for it, you can repackage the unaltered extension in your addon.
Just need to include the extDB3-License-FLOSS-Exception.txt  aswell.

Thanks

 

[Col. Ben Sherman 3]

I can't find the linux version, anyone? and also why would you delete previous versions like extDB2 for example? Like I don't get it sure your doing a new one, improved and probably working way better but still, like for me I had the extDB2 version before and a about half a year ago I reinstalled my server and all of the sudden I have to rebuild my entire mission-framework to get it to work with extDB3 for no reason.

 

But yea, I'd love to get the linux version for this plugin, thanks anyways!