Intercept Database (SQL Database Extension)

[Dedmen 2716]

Here we go with my next Intercept based mod.

(This post is WIP, I need to clean this up more. Just wanted to get the word out first)

 

I actually had this idea in my head back when I started contributing to Intercept but never build it.

So here it is, a tightly integrated database plugin using Intercept.
It's designed to be intuitive to use just like if you were just scripting SQF, unlike other Database extensions.

Of course, it's also designed to be fast, which the asynchronous queries with callbacks and the removal of the need to callExtension already nicely accomplishes.

 

There are several advantages over a callExtension approach:

• No need to serialize things into strings, and deserialize them in the extension
• No need to parse returned values from string into whatever you need which can be expensive (parseSimpleArray, call compile)
• No string size limitations neither on arguments nor on query results
• No need to constantly poll the extension if you are waiting for a asynchronous result

 

 

I also wanted to make it safe to use which is why I went the route of using statements and binding parameters to it.

Where the "?" are your parameters that you add later on. Ofc you don't have to use this if you don't want to.

Prepare a query statement:

DB_Connection = dbCreateConnection ["ip", 3306, "user", "password", "db"]; //Connect to database
_query = dbPrepareQuery 'SELECT intel_missionen.`name` FROM intel_missionen WHERE intel_missionen.id > ?'; //Prepare a statement with one variable

Then you can bind values to it  (supports bool, string and float) and execute the query

_query dbBindValue 400;
_result = DB_Connection dbExecuteAsync _query;

Queries can be copied with already bound values at any point. So you can prepare queries in global variables to quickly and efficiently reuse them.

 

Here you also see one of the main Features, "Async". In order to not freeze the game or constantly poll the extension asking whether it's done yet, I have implemented a callback mechanism and also integrated it with the script scheduler.

So you can do this for example

_result dbBindCallback [{
	params ["_result", "_args"];

	DB_RES = [dbResultToArray _result, _args];
	systemChat "got result!";
}, 1];

To bind a callback to your query, the callback will automatically be executed once the query completed and returned it's data.

The callback will immediately execute if the result is already ready (Not yet implemented)

You will also be able to bind multiple callbacks, imagine it working like addEventHandler, just that you can also pass your own arguments to it. (Not implemented yet, currently only one callback)

 

If you are running scripts in scheduled environment you can just do this

_query spawn {
    _result = DB_Connection dbExecute _this;
    //Do stuff with _result now
};

This will just suspend the script like a sleep or waitUntil does, but without constantly calling a extension and wasting precious fps.

 

 

Of course DB connection details, and queries can also be loaded from a config file.
For that I chose YAML because it's easy to use

 

accounts:
 maindb: #production db, don't break things here!
  ip: 127.0.0.1
  username: root
  password: lulz
  database: production
  port: 3306 #optional
 testdb: #testserver
  ip: 127.0.0.2
  username: root
  password: lulz
  database: production
  port: 3306 #optional

statements:
 insertStuff: INSERT INTO table (a,b,c) VALUES (?,?,?)
 deleteStuff: DELETE FROM table WHERE a=?
 longQuery: >
  SELECT stuff
  FROM table
  WHERE
  isThisALongQuery=1 AND
  queriesCanBeMultiline=1 AND
  thatsWhyILikeYAML=5;

And use the values from the config in your script:

_connection = dbCreateConnection "testdb"; //load the connection from config
_query = dbPrepareQueryConfig ["deleteStuff", [15]]; //Preparing a query and binding values can be done in a single command too
_connection dbExecuteAsync _query;

 

A quick look at performance measurements from January:
 

Spoiler

dbCreateConnection "maindb"; 0.0013ms (connection is only established at first query)

dbPrepareQueryConfig ["getMissionName", [400]]; 0.0021ms

dbPrepareQueryConfig "getMissionName"; 0.0008ms

dbPrepareQuery "SELECT intel_missionen.nameFROM intel_missionen WHERE intel_missionen.id > ?"; 0.0008ms (to be expected)

dbPrepareQuery ["SELECT intel_missionen.nameFROM intel_missionen WHERE intel_missionen.id > ?", [400]]; 0.0021ms (to be expected, It's does the same stuff as the config variants)

DB_connection dbExecute DB_query; 59.76ms well my ping to the server is 20ms..

DB_connection dbExecuteAsync DB_query; 0.0036ms (Hint, Don't try this in debug console performance test on a live server, 10k queries are not a nice thing to do :D)

 

 

The documentation can be found here: https://intercept-database.readthedocs.io/en/latest/index.html

 

 

The main problem with Intercept based mods is that they aren't Battleye whitelisted, that's no problem here though as battleye doesn't check for whitelist on serverside, and this mod only runs on serverside.

 

SQLite and some other database engine support might be added too, though I will only do that if there are enough people that need it and if I have the time to do it.

 

If you have ideas and comments throw them at me.

 

 

Github

Releases on Github

(Working windows builds are already present, Linux builds not yet on github they still need a couple hours of work put into them.)

 

[gc8 977]

Hey dedmen, looks great!

Noob question though, are you able to create new SQF commands? Because I don't understand dbCreateConnection etc otherwise.

thx

[Dedmen 2716]

3 minutes ago, gc8 said:

Hey dedmen, looks great!

Noob question though, are you able to create new SQF commands? Because I don't understand dbCreateConnection etc otherwise.

thx

Yes. That's the main feature making a Intercept based mod provides to me. And also the main reason for making this mod.

[Theowningone 1]

Looks great!

Also, I'd like to register my interest in PGSQL support.

[Dragon_GER 19]

Very cool stuff dedmen, i was waiting for this post as soon i saw your github repo 😄

Im going to try it out soon, doing some whitelisting and stats logging stuff!

[xeno 234]

So far I had no luck getting it to work, I simply can not establish a database connection.

 

The same database works fine with extDB3, using the same connection params with interceptDB it only results in "No connection".

It doesn't matter if I use a config YAML file or an array with the DB params.

 

There is no feedback why the connection fails neither in the RPT nor in a separate log file which makes it impossible to find out what's going wrong (intercept itself works as RPT entries show)

 

I use mariaDB.

[Dedmen 2716]

23 hours ago, xeno said:

So far I had no luck getting it to work, I simply can not establish a database connection.

 

The same database works fine with extDB3, using the same connection params with interceptDB it only results in "No connection".

It doesn't matter if I use a config YAML file or an array with the DB params.

 

There is no feedback why the connection fails neither in the RPT nor in a separate log file which makes it impossible to find out what's going wrong (intercept itself works as RPT entries show)

 

I use mariaDB.

Can you show the script code? How are you executing queries?

Just creating a connection doesn't automatically connect, it only connects on the first query being done.
And if the query is being executed asynchronously then a second internal connection will be created as copy of the one you had in your script. The copy will then connect to the database, and the one in script won't.
That's just a early release quirk that I need to find a acceptable solution to. Have you tried executing queries and they didn't work?

 

If a query fails it prints the error to RPT.

 

 

A command to actually establish the connection and ping the server is being worked on: https://github.com/intercept/intercept-database/wiki/Commands#dbping-connection

[xeno 234]

Got it working, finally...

 

I guess it was just getting used to it compared to extDB3.

 

[Dedmen 2716]

There is certainly still lots of work to be done.
I implemented dbIsConnected and dbPing commands yesterday (not yet released) and I hope I can find time this weekend to fix the more pressing issues like:
- dbConnected being false even after you executed a async query (but tbh no idea how to really solve this, or rather how to solve this correctly)

- Xeno's request to automatically parse strings from the database into array/bool/number.

 

Maybe if I feel like it I'll do:
- try/catch exception handling in script for SQL errors
- Ability to specify number of worker threads (currently there is one hardcoded worker thread per connection)
- Ability to have SQL errors neither throw exceptions, nor abort and print to RPT, but instead return a result with a error flag and commands to retrieve error information from the result. Currently if error occurs, callback doesn't get called.

- Ability to set a global error handler script per connection (this one should be fairly trivial to implement)

 

Question to potential users, does anyone need to have every possible configuration available to be done via script? I'm thinking about making some things config file only as it is easier to implement.
Like
- SQL connection options

- Whether a connection throws exception, aborts to rpt or still returns a result when error happens. Maybe you want to rather configure that per query? That would also be fairly simple to do in config, but cumbersome in script.
- Number of worker threads, that's definitely something I want config only

 

I'm thinking most people will be using the config file anyway.
Oh btw I forgot to add that to the wiki, there is a dbReloadConfig command to reload the config on the fly. It will automatically update all even already prepared queries. No need to update your variables if you are storing queries.

 

A bit of technical background if anyone is interested:
A query that was prepared from a config entry, just stores the name of the config entry inside itself. The actual query string is only retrieved from config just before execution. That's why you can alter the config in-between and have it take effect immediately.

[Dedmen 2716]

Okey here we go with the first update. I implemented all the feedback I got so far.

 

https://github.com/intercept/intercept-database/releases/tag/1.0

Changelog:

Added dbPing command (Just executes a "SELECT 1" synchronously (freezes game) and returns true if the query successfully returned 1, making 100% sure the DB connection is working)
Added dbIsConnected command (Also returns true if same credentials are connected in a worker thread)
Added dbResultToParsedArray command (Tries to parse all strings from database into Arma types. Parses "[1,2,3]" to plain [1,2,3 ] in the result. @xeno)
Added dbAddErrorHandler command (Adds global error handler that is called on any query/database error)
Added dbVersion command (Returns the current version as a string. Now "1.0")
Stringified query variable now returns query content.

Plugin dll is now digitally signed.

 

All info on commands is of course also available on the wiki: https://github.com/intercept/intercept-database/wiki/Commands

Linux version is still WIP, didn't have time to work on that today.

Feel free to open tickets on Github, no matter if Feature Request or bug report.

 

On 3/26/2019 at 7:08 PM, Theowningone said:

Also, I'd like to register my interest in PGSQL support.

I created a ticket on github. So far looks easy to implement, but no time guarantees.

 

 

EDIT: dbPing is broken. whoops.

[Dedmen 2716]

And here comes version 1.1

https://github.com/intercept/intercept-database/releases/tag/1.1

Changelog:

Fixed dbPing crashing the game (soooorry that it took so long :D)

Added error handling in lots of places, some invalid things now throw script errors like normal SQF commands would do too.

Added config option to disable creating queries in scripts, meaning you can only load statements from config if it's enabled

Added dbLoadSchema command which executes a sql file which can be defined in config. This can be used to create the database tables that you need. Probably interesting for @xeno

 

For dbLoadSchema though it seems that "DELIMITER" doesn't work. Might fix that somehow in the future but it looks to not be trivial. But I assume loading stored procedures won't work for now.

 

[xjoker_ 25]

Is it possible to run multiple queries in one call ?

[Dedmen 2716]

41 minutes ago, xjoker_ said:

Is it possible to run multiple queries in one call ?

Yes.. I think.. It should be.

 

I tried

(dbCreateConnection "maindb") dbExecute (dbPrepareQuery "SELECT 1; SELECT 1")

And it worked fine. Not sure if toArray works to get results out if you have multiple result sets.

Definitely something I want to fix though.

[xjoker_ 25]

Yeah my question wasn't really about getting results out of it. 

I mean if I have to 

UPDATE players SET a = ? WHERE steam_id = ? ; 

for each connected players 

Do i have to make a loop or can i do that in 1 line with many parameters ?

Do you think it would make a difference in terms of performance ?

[Dedmen 2716]

8 hours ago, xjoker_ said:

Do i have to make a loop or can i do that in 1 line with many parameters ?

Do you think it would make a difference in terms of performance ?

You'd have to build the full query string and then call prepareQuery for it.
Yes performance wise one big block will be more efficient.

 

Something like this:

private _query = allPlayers apply { "UPDATE players SET a = ? WHERE steam_id = ?" }

private _parameters = []; //This can probably be solved more efficiently
{
    _parameters append [name _x, getPlayerUID _x]
} forEach allPlayers;

private _preparedQuery = dbPrepareQuery [_query joinString ";", _parameters];

 

Ofc with this you can't use config queries, which means you cannot disable dynamic queries which you might see as a security issue.

Also you should wrap all of that in a transaction or disable autocommit and commit manually.

 

Actually thinking about it.. This will be more efficient yes, but if you just throw out a bunch of async queries then I think it might not actually matter.

[Dedmen 2716]

And here comes release 1.2

https://github.com/intercept/intercept-database/releases/tag/1.2

 

Changelog:

Fixed crash if number of provided bind values doesn't match expected number (now throws script error)
Fixed crash on race condition in async queries (async queries randomly crashed arma)
Fixed TEXT types being ignored in results
Fixed crash on DATETIME with null value (in general null values will now be better supported, return "NULL" now)

Updated to Intercept API v2 (No DB relevant change, but Intercept core needs to be updated)

 

[Terrano. 0]

when i trying createConnection is returns - <not connected>, maybe i'm doing something wrong?

dbCreateConnection ["127.0.0.1",2302,"arma3","","altislife"];

using version 1.2

[Dedmen 2716]

16 hours ago, Terrano. said:

when i trying createConnection is returns - <not connected>, maybe i'm doing something wrong?

No that's correct.

https://github.com/intercept/intercept-database/wiki/Commands#dbcreateconnection-configname

 

While we're at it..

New documentation! And anyone can now make Pull Requests to improve it.

https://intercept-database.rtfd.io/

 

[Dedmen 2716]

And another very small, yet very important update

https://github.com/intercept/intercept-database/releases/tag/1.3

 

Fixed a "prepared statement" leak. It would cause your database to cease working after you execute 16382 (default mariaDB value) queries, because it would never delete the statements and the database has a limit on open statements

Also the callbacks for async queries would never fire, don't know how I could miss that.

 

 

[Terrano. 0]

@Dedmen, i have a problem with usage 😞

 

i use that code:

ConnectionMySQL = dbCreateConnection ["127.0.0.1",3306,"arma3","","test"];
diag_log str[ConnectionMySQL];

dbIsConnected ConnectionMySQL;
diag_log str[ConnectionMySQL];

query1 = dbPrepareQuery "SELECT count(*) FROM players";
diag_log str[ConnectionMySQL dbExecuteAsync query1];

but on log's i getting that error: 

2019/06/21,  0:32:23 "[TODO]"
2019/06/21,  0:32:23 Error in expression <interceptOnFrame>
2019/06/21,  0:32:23   Error position: <interceptOnFrame>
2019/06/21,  0:32:23   Error Intercept-DB exception Host 'ibattle.org' is not allowed to connect to this MariaDB server
at
SELECT count(*) FROM players
2019/06/21,  0:34:01 "[<not connected>]"
2019/06/21,  0:34:01 "[<not connected>]"
2019/06/21,  0:34:01 "[TODO]"

 

[Dedmen 2716]

9 hours ago, Terrano. said:

Host 'ibattle.org' is not allowed to connect to this MariaDB server

Well. There you have it. Your user doesn't have permission to connect to that server.

 

9 hours ago, Terrano. said:

dbIsConnected ConnectionMySQL;

Returns false because the connection is only made when the first query is executed.

[Terrano. 0]

4 hours ago, Dedmen said:

Well. There you have it. Your user doesn't have permission to connect to that server.

 

Returns false because the connection is only made when the first query is executed.

that user have all permissions to work with database, but i don't understand - why needed 

ibattle.org

extDB works with that user, maybe i need to create new user for interceptDB?

[Dedmen 2716]

5 hours ago, Terrano. said:

that user have all permissions to work with database, but i don't understand - why needed 

ibattle.org

extDB works with that user, maybe i need to create new user for interceptDB?

It's an error directly from your database, and it tells you that the user you are logging in with doesn't have permission. I can't help you with that.

[xjoker_ 25]

@Dedmen

Just a suggestion, I don't know if it's hard or not to integrate, but extDB3 return empty string for null values

so it may be interesting to have a global param in config to choose if you want to get nil or "" with interceptDB

that way it would be easier to migrate from one to the other

[Dedmen 2716]

1 hour ago, xjoker_ said:

have a global param in config to choose if you want to get nil or ""

Yep. No problemo to implement, I personally think nil is more obvious but can do that.. Or... Maybe..
I could add a "dbNull" type, that converts to "" as string and also returns null on isNull. Would that be even better? you get isNull AND empty string in one thing.

Btw I added support for passing nil/null types as bindValues which will translate to null. But I didn't make a public release for that yet.