extDB (arma3 extension linux/windows)

[Greatman 10]

On 2/23/2017 at 4:34 PM, torndeco said:

It will help to have some knowledge of SQL Queries & SQF.
Otherwise it will  get confusing fairly fast. In that case i would nearly recommend (just no update for upcoming 64bit, but the source code is there)

But i don't think he is been active in awhile :(

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

If you do know some SQL etc or think you be able to learn it...

Just note @extDB3, does not implement code to talk / retreive data from the extension.
This is due to several ways to implement this.

extDB3 is very similar to extDB2, i tend to the number in the name if there is a breaking change.

So the best thing is to look at other addons/missions that have implemented it
Then if you got questions try give me a shout on arma discord (might take awhile to respond, but you will need to know some SQF/SQL)

-------------------
A3Wasteland uses a mixture of defining defining SQL in .ini file & generating the rest of query in SQF. (don't recommend, is very confusing & abit more complicated queries)
Exile has a good implementation, uses pretty much all prepared statements. (secure)
AltisLifeRPG uses SQL Querys, but is prob alot easier when starting off. (not secure)

There is also 

Note: There is a test command line program, that lets you test sending the extDB3 text. Can be abit easier to use initially as you don't need to be in arma.

 

Thanks torndeco, I am especially good at SQL, I have used it extensively, but I am very new to SQF , I have no idea where to begin with it! or even if I should :D

[torndeco 128]

The main thing with extDB3 that confuses people is there is a setup stage.
This is to make it flexible, but is also confusing aswell.

 

"extDB3" callExtension "9:ADD_DATABASE:DB";
// This will connect to a database using the settings from [DB]   in extdb3-conf.ini

// Next there are two different methods of sending queries etc
// There is normal SQL Queries, (not secure if someone manages to remote execute code on armaserver)
// Or there is prepared statements etc that are predefined in .ini file.

// So we have database setup, we just need to tell the extension which method we are using.

"extDB3" callExtension "9:ADD_DATABASE_PROTOCOL:DB:SQL:SQL_QUERY";
// This will use database connection DB + the method/protocol SQL, and we are calling it SQL_QUERY.


"extDB3" callExtension "9:LOCK";
// Then we are going to lock the extension. This is security incase someone remote executes code on server.
// It basically disables the setup stage.

//"extDB3" callExtension "9:LOCK:1234";
//"extDB3" callExtension "9:UNLOCK:1234";
//"extDB3" callExtension "9:RESET";
// Note if you use a password, then you can unlock + reset the extension later.
// Useful for testing or restarting / loading different missions.


Ok the extension is finally setup and we can actually do a SQL Query  (SYNC / BLOCKING)
_result = "extDB3" callExtension "0:SQL_QUERY:SELECT * FROM PLAYERS";
_result = call compile _result;

Note:
SQL_QUERY / DB names above can pretty much be whatever you like. Some frameworks even randomize this.
It is also possible to connect to multiple databases at same time aswell.
Also all the error messages return to arma from the extension are generic.
To get proper info you need to check @extDB3/logs for more detailed info

I also updated / simplified the sqf examples at 
https://bitbucket.org/torndeco/extdb3/src/08a15a65b9e5b7a0807fb31166434962ce2daddd/sqf_examples/sqf/?at=default

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

Above is just some rough examples to get you started etc...
There is also a test program in the optional directory.
It is basically a commandline version of the extension.
It is so you can type in the commands and see the returned output, without having to use arma.
Makes it easier to understand intially imo, versus typing sqf code etc...

After you get that part figured out.
You should have a mess around with retreiving multiple part messages
ExileServer code / AltisLife / a3wasteland and afew other frameworks will have this implemented.
And look at https://bitbucket.org/torndeco/extdb3/wiki/extDB3 - General

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

Few other things you might not know or be like why.

Few things arma callExtensions are blocking...
That is why its generally better to tell an extension todo something.
It returns an id + you poll the id until the job is done.
This is normally what people refer to async extension.

This way you don't block arma engine for long period of time.

Arma callExtensions can only return so much (2k chars in arma2, or 10k chars in arma3).
That is why there is multi-part messages, to handle when your returned output is longer than the limit.

 

[torndeco 128]

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

Fixed issue were some systems locale number formatting uses different char instead of . for floats / decimal numbers etc
This basically means if you had for example
German locale on the system for number formatting it was returning
[1,[1,0]]
Instead of 
[1,[1.0]]
 

Changelog

• Fixed 9:LOCAL_TIME + 9:UTC_TIME offset not working i.e 9:LOCAL_TIME:-2  etc
• Fixed some systems locale number formating using , instead of . for floats.

 

[xjoker_ 25]

@torndeco

Hi , i have a server using the first extDB (not extDB2 or 3).

I know that you stopped working on it but is there a way to make a 64bit version of extDB.dll ? 

[torndeco 128]

I am sorry i only support extDB3.
Not to mention there are outstanding bugs in extDB that can crash a server and some performance bugs with extDB2.

It is relatively easy enough to port extDB SQL Protocol to extDB3, which is what the life servers were using.
If you are unable to update your mission or addon, am afraid you will need to use the 32bit server. Players can still connect to the server using 64bit clients

[xjoker_ 25]

Ok thanks for the answer. 

I'll stay in 32bit serverside for now. 

 

To port extDB to extDB3 i guess I have to move all my sql requests from all my files to the .ini (sql_custom folder) ? 

[torndeco 128]

Nope you can stil do raw SQL Querys if you like.
Example how to do it, its almost the same.
 

https://bitbucket.org/torndeco/extdb3/wiki/Contents

Note: If it is a life server, its is prob worth looking at https://github.com/AsYetUntitled/Framework aswell

[xjoker_ 25]

hum.. ok so what do you think i'll have to change ? only replace @extDB to @extDB3 and change the extdb3-conf.ini ?

It can't be that easy

[torndeco 128]

Just linked you to sqf post with example code sqf. Its up to you change the setup stage.
Like i said its very similar.
If you get stuck look at altis life repo for changes etc
Goodluck

[arnaldoestevao 0]

Please any help me !

After last update i need update to extDB3 !

10:54:04 SteamAPI initialization failed. Steam features won't be accessible!
10:54:04 Source set
10:54:04 Initializing stats manager.
10:54:04 Stats config disabled.
10:54:04 sessionID: 55ccf4e464b116ca82610844d920d1f2ed4bdaad
10:54:04 ../lib/Network/networkServer.cpp ClearNetServer:NOT IMPLEMENTED - briefing!
Cannot open file '/home/steam/steamcmd/arma3/@extDB3\addons\extdb3'
10:54:04 Critical:Destroying running thread!
10:54:04 Critical:Destroying running thread!
10:54:04 Critical:Destroying running thread!
10:54:04 Critical:Destroying running thread!
10:54:04 Critical:Destroying running thread!
10:54:04 Critical:Destroying running thread!

i create a simbolic link
ln -s extDB3.pbo extdb3.pbo

but extDB3 not work

10:57:27 "---------------------------------------------------------------------"
Call extension 'extDB3' could not be found
10:57:27 "extDB3 Failed to Load, Check Requirements @ https://bitbucket.org/torndeco/extdb3/wiki/Installation"
10:57:27 ""
10:57:27 "If you are running this on a client, Battleye will random block extensions. Try Disable Battleye"
10:57:27 "---------------------------------------------------------------------"
10:57:27 "---------------------------------------------------------------------"

please help me !

[torndeco 128]

There is a bug with callExtension on arma3server for linux with 1.68.
 

[10:53 PM] Torndeco: Ok workaround is to rename extensions , it isn't nice but ohwell
[10:54 PM] Torndeco: For example
/home/user/arma3/extension.so
->
/home/user/arma3\extension.so    (dont forget to escape the backslash)
[10:55 PM] Torndeco: Depending on extensions you will may need to relocate config files etc

Hopefully BIS will release a fix soon

[brutalzic 0]

@torndeco Is the output of the RAW protocol planned?

[torndeco 128]

7 minutes ago, brutalzic said:

@torndeco Is the output of the RAW protocol planned?

https://bitbucket.org/torndeco/extdb3/wiki/extDB3 - SQL

[Thomas Delassalle 0]

It is really different from the former of the blow I do not manage to run him!

[Thomas Delassalle 0]

@torndeco

 

Here is my old functioning. if (!(["DATABASE", "SQL_RAW_V2", "ADD_QUOTES"] call EXTDB2_fnc_init)) exitWith {};

 

Thus it is not more possible to use him ?

[torndeco 128]

There is very little difference between SQL_RAW_V2 + SQL.
Its is just slightly different setup stage
For example

9:ADD_DATABASE:<DATABASE>:<DATABASE_NAME>
9:ADD_DATABASE_PROTOCOL:<DATABASE_NAME>:SQL:SQL:TEXT
0:TEST:SELECT * FROM PLAYERS

Atm i don't have any SQF code like EXTDB2_fnc_init written.
Look at the examples at https://bitbucket.org/torndeco/extdb3/src/96421b87ca61f8075daa57f6c8c64389318e788e/sqf_examples/sqf/?at=default

Also if you get stuck you could look at altis life rpg they switched from extDB2 SQL_RAW_V2 -> extDB3 SQL
 

[Thomas Delassalle 0]

@torndeco

 

Indeed this code do not come from you! 

Here is how I proceed:

@life_server - > init_server.sqf

If (! (["DATABASE", "SQL_RAW_V2", "ADD_QUOTES"] call EXTDB2_fnc_init)) exitWith {};

Then @life_server - > Fonctions - > Extdb - > init.sqf

 

https://paste.titin.fr/#033aaf.cs

[torndeco 128]

You should look at altis life github and look at there changes.
It is currently using extDB3
Goodluck

[torndeco 128]

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

This is pretty much a single bug fix release.
 

Changelog

• SQL_CUSTOM: Fixed issue with counting the number of required inputs when using multiple SQL Statements. 
• Lowercased extDB3.pbo, due to armaserver 1.68 issue on linux.

[3.JgKp James 10]

Hello torndeco,

 

as a clan we are relying heavily on your extDB2, at the moment we are in the process of changing to extDB3. Thank you a lot for your efforts and time, we appreciate it, be assured!

 

If you need a supporting hand on the sqf helper functions, I would be more than willing to assist you.

 

For example, I'm already using my own helper functions to extract from a query with several nested arrays the desired output and to check for each query the error code and return error message if there was an error detected.

 

Greetings

[3.JgKp]James

[brutalzic 0]

On 20/03/2017 at 1:25 AM, torndeco said:

https://bitbucket.org/torndeco/extdb3/wiki/extDB3 - SQL

Very thanks for your work ... and your answers !

[torndeco 128]

On 3/25/2017 at 9:34 AM, 3.JgKp James said:

Hello torndeco,

 

as a clan we are relying heavily on your extDB2, at the moment we are in the process of changing to extDB3. Thank you a lot for your efforts and time, we appreciate it, be assured!

 

If you need a supporting hand on the sqf helper functions, I would be more than willing to assist you.

 

For example, I'm already using my own helper functions to extract from a query with several nested arrays the desired output and to check for each query the error code and return error message if there was an error detected.

 

Greetings

[3.JgKp]James

Nice to hear, if you like just send me the sqf or make an issue / pull request on bitbucket etc
At the very least i can put the code up in the examples directory, really should have that linked somewhere in the wiki section

[Guest]

I'm using ExtDB3 1028 and seens the RAW SQL TEXT propertie is not working, i still get text data with no quotes.

 

	BRPVP_ProtocolRawText = "PRT" + str round random 10000000;
	"extDB3" callExtension ("9:ADD_DATABASE_PROTOCOL:brpvp_altis:SQL:" + BRPVP_ProtocolRawText + ":TEXT");

 

[torndeco 128]

You only get quotes around VARCHARS etc, TEXT = No quotes 
The idea is you store long arrays in TEXT Datatypes and shorter stuff like names / classnames in a VARCHAR
https://bitbucket.org/torndeco/extdb3/wiki/extDB3 - SQL
Altis Life RPG uses this method since extDB, and they upgraded to extDB3 so it should be working fine.

If you better control etc, recommended you use SQL_CUSTOM with prepared statements etc

[Guest]

I got it torndeco, i really was storing player names in text format and will convert then to var char type.