Kerberos-Datenbank Anbindung

extdb3-conf.ini

[Main]
Version = 1

Randomize Config File = false
;; Randomizes Config File after loading.
;;   Recommend to turn on, if you have enabled filepatching on arma.

Allow Reset = true
;; Allows 9:RESET, usefull for development work

Thread = 0;
;; Option to force number of worker threads for extDB3.
;;   Min = 2, Max = 6

[Log]
Flush = true;
;; Flush logfile after each update.
;;    Option really only usefull if running DEBUG BUILD

[KerberosDB]
IP = ***.***.***.***
Port = ****
Username = ******
Password = ******
Database = kerberos

fnc_bigData.sqf

/*
 *  Author: Dorbedo
 *
 *  Description:
 *      internal function to return big Data from a pipe
 *
 *  Parameter(s):
 *      0 : STRING - Key from a open pipe
 *
 *  Returns:
 *      ANY - The big value
 *
 */
#include "script_component.hpp"

_this params ["_key"];
private _return = "";
private "_pipe";
while {true} do {
    _pipe = "extdb3" callExtension format["5:%1",_key];
    If (_pipe isEqualTo "") exitWith {};
    _result = _result + _pipe;
};
call compile _result;

fnc_connectToDB.sqf

/*
 *  Author: Dorbedo
 *
 *  Description:
 *      Connect to Kerberos DB
 *
 *  Parameter(s):
 *      none
 *
 *  Returns:
 *      BOOL - is Connected
 *
 */
#include "script_component.hpp"

private _result = "extdb3" callExtension "9:VERSION";
If (_result isEqualTo "") exitWith {
    ERROR("No extDB3 extension loaded");
    false;
};


_result = call compile ("extdb3" callExtension "9:LOCK_STATUS");
If ((_result select 0) == 1) exitWith {
    ERROR("Database is locked");
    false;
};

// reset the connection
"extdb3" callExtension "9:RESET";

_result = call compile ("extdb3" callExtension "9:ADD_DATABASE:KerberosDB");
If ((_result select 0) isEqualTo 0) exitWith {
    ERROR("Can not connect to Database");
    false;
};

LOG("Connected to Database");

// Security
GVAR(sessionID) = str(round(random(999999)));
If !(isMultiplayer) then {
    GVAR(sessionID) = QUOTE(PREFIX);
};
_result = call compile ("extdb3" callExtension (format["9:ADD_DATABASE_PROTOCOL:KerberosDB:SQL_CUSTOM:%1:kerberos.ini",GVAR(sessionID)]));

If !((_result select 0) isEqualTo 1) exitWith {
    ERROR("Protocols not loaded");
    false;
};
LOG("Protocols loaded");

GVAR(initialized) = true;
true;

fnc_getSingleValue.sqf

/*
 *  Author: Dorbedo
 *
 *  Description:
 *      get a Value from DB
 *
 *  Parameter(s):
 *      ARRAY - DB-Query array - [protocol,value1,value2,....]
 *
 *  Returns:
 *      ANY - return of protocol
 *
 */
#include "script_component.hpp"

If (isNil "_this") exitWith {};

private _query = [0,GVAR(sessionID)];
_query append _this;
_query = _query joinString ":";
private _return = call compile ("extdb3" callExtension _query);

switch (_return select 0) do {
    case 0 : {ERROR(format[ARR_2("Database Error: %1",(_result select 1))]);};
    case 2 : {_return = (_return select 1) call FUNC(bigData);};
};
((_return select 1) select 0);

fnc_getTime.sqf

/*
 *  Author: Dorbedo
 *
 *  Description:
 *      returns the current time
 *
 *  Parameter(s):
 *      none
 *
 *  Returns:
 *      ARRAY - [YY:MM:DD:HH:MM:SS]
 *
 */
#include "script_component.hpp"

private _return = call compile ("extdb3" callExtension "9:LOCAL_TIME");
(_return select 1);

fnc_getValue.sqf

/*
 *  Author: Dorbedo
 *
 *  Description:
 *      get a Value from DB
 *
 *  Parameter(s):
 *      ARRAY - DB-Query array - [protocol,value1,value2,....]
 *
 *  Returns:
 *      ANY - return of protocol
 *
 */
#include "script_component.hpp"

If (isNil "_this") exitWith {};

private _query = [0,GVAR(sessionID)];
_query append _this;
_query = _query joinString ":";
private _return = call compile ("extdb3" callExtension _query);

switch (_return select 0) do {
    case 0 : {ERROR(format[ARR_2("Database Error: %1",(_result select 1))]);};
    case 2 : {_return = (_return select 1) call FUNC(bigData);};
};
(_return select 1);

fnc_sendNoReturn.sqf

/*
 *  Author: Dorbedo
 *
 *  Description:
 *      send data into the database
 *      aka "fire and forget"
 *
 *  Parameter(s):
 *      ARRAY - DB-Query array - [protocol,value1,value2,....]
 *
 *  Returns:
 *      none
 *
 */
#include "script_component.hpp"

If (isNil "_this") exitWith {};

private _query = [1,GVAR(sessionID)];
_query append _this;
_query = _query joinString ":";
"extdb3" callExtension _query;
nil;

fnc_sendWithReturn.sqf

/*
 *  Author: Dorbedo
 *
 *  Description:
 *      sets a single Value to DB
 *
 *  Parameter(s):
 *      ARRAY - DB-Query array - [protocol,value1,value2,....]
 *
 *  Returns:
 *      ANY - return of protocol
 *
 */
#include "script_component.hpp"

If (isNil "_this") exitWith {};

private _query = [0,GVAR(sessionID)];
_query append _this;
_query = _query joinString ":";
private _return = call compile ("extdb3" callExtension _query);
((_return select 1) select 0)

kerberos.ini

[Default]

Version = 1
Strip Chars = "`/\|;{}<>'"
Strip Chars Mode = 0


; --------------------------------------------------------------------------------
; DB CONSTRUCTOR
; --------------------------------------------------------------------------------

; --------------------------------------------------------------------------------
[constructTablePlayers]

SQL1_1 = CREATE TABLE IF NOT EXISTS players (
SQL1_2 = id int(12) NOT NULL AUTO_INCREMENT,
SQL1_3 = playeruid varchar(100) NOT NULL,
SQL1_4 = name varchar(50) DEFAULT NULL,
SQL1_5 = alias varchar(50) DEFAULT ' ',
SQL1_6 = assignement ENUM('Public','Brig13') NOT NULL DEFAULT 'Public',
SQL1_7 = comment varchar(255) DEFAULT NULL,
SQL1_8 = isallowedtofly tinyint(1) DEFAULT 0 NOT NULL,
SQL1_9 = isbanned tinyint(1) DEFAULT 0 NOT NULL,
SQL1_10 = banreason varchar(100) DEFAULT NULL,
SQL1_11 = lastConnect timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
SQL1_12 = PRIMARY KEY (id),
SQL1_13 = UNIQUE KEY playeruid (playeruid)
SQL1_14 = ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Prepared Statement = false
; --------------------------------------------------------------------------------

; --------------------------------------------------------------------------------
; DB TEST VALUES
; --------------------------------------------------------------------------------

; --------------------------------------------------------------------------------
[testplayers]

SQL1_1 = INSERT INTO players (id,playeruid,name,alias,isallowedtofly,isbanned)
SQL1_2 = VALUES(1,'testUID1','Max Mustermann','Max',0,0);
SQL2_1 = INSERT INTO players (id,playeruid,name,alias,isallowedtofly,isbanned)
SQL2_2 = VALUES(2,'testUID2','Erika Mustermann','ERIKA',1,0);
SQL3_1 = INSERT INTO players (id,playeruid,name,alias,isallowedtofly,isbanned)
SQL3_2 = VALUES(3,'testUID3','Peter Mustermann','Peter',1,0);

Prepared Statement = false
; --------------------------------------------------------------------------------

; --------------------------------------------------------------------------------
; DB DESTRUCTOR
; --------------------------------------------------------------------------------

; --------------------------------------------------------------------------------
[destructTablePlayers]

SQL1_1 = DROP TABLE IF EXISTS players

Prepared Statement = false

; --------------------------------------------------------------------------------

; --------------------------------------------------------------------------------
; PLAYER
; --------------------------------------------------------------------------------

; --------------------------------------------------------------------------------
[getPlayerData]

SQL1_1 = SELECT * FROM players;

Prepared Statement = false

; --------------------------------------------------------------------------------

[isPlayerWhitelisted]

SQL1_1 = SELECT DISTINCT isallowedtofly FROM players WHERE playeruid = '$CUSTOM_1$' LIMIT 1;
SQL1_INPUTS = 1

OUTPUT = 1-bool

Prepared Statement = false

; --------------------------------------------------------------------------------

[allPlayerWhitelisted]

SQL1_1 = SELECT DISTINCT playeruid FROM player WHERE isallowedtofly = 1;

OUTPUT = 1

Prepared Statement = false

; --------------------------------------------------------------------------------

[UpdateTime]
SQL1_1 = UPDATE players SET lastConnect = CURRENT_TIMESTAMP WHERE playeruid = $CUSTOM_1$

SQL1_INPUTS = 1-string2

Prepared Statement = false

; --------------------------------------------------------------------------------

[insertOrUpdatePlayerInfo]
SQL1_1 = INSERT INTO players (playeruid,name)
SQL1_2 = VALUES($CUSTOM_1$,$CUSTOM_2$)
SQL1_3 = On DUPLICATE KEY UPDATE name = $CUSTOM_2$, lastConnect = CURRENT_TIMESTAMP;

SQL1_INPUTS = 1-string2,2-string2

Prepared Statement = false

; --------------------------------------------------------------------------------

XEH_PREINIT.sqf

/*
 *  Author: Dorbedo
 *
 *  Description:
 *      preinit for component database
 *
 */
#include "script_component.hpp"

ADDON = false;

PREP(bigData);
PREP(connectToDB);
PREP(getSingleValue);
PREP(getTime);
PREP(getValue);
PREP(sendNoReturn);
PREP(sendWithReturn);

ADDON = true;

If (!isServer) exitWith {};

GVAR(initialized) = false;

call FUNC(connectToDB);
["constructTablePlayers"] call FUNC(sendNoReturn);

XEH_serverpostinit_whitelist.sqf

/*
 *  Author: Dorbedo
 *
 *  Description:
 *      ServerPostInit - used for the whitelist
 *
 */
#include "script_component.hpp"

EGVAR(player,reserved_pilot_slot) = true;

[QGVAR(pilot_whitelist), "onPlayerConnected", {
    If (([_uid, "HC"] call CBA_fnc_find)>-1) exitWith {}; /// Ignore Headless CLients
    ["insertOrUpdatePlayerInfo",_uid,_name] call EFUNC(database,getSingleValue);
    private _return = ["isPlayerWhitelisted",_uid] call EFUNC(database,getSingleValue);
    If (IS_ARRAY(_return) && {_return select 0}) then {
        _owner publicVariableClient QEGVAR(player,reserved_pilot_slot) ;
    };
}] call BIS_fnc_addStackedEventHandler;