/arma-extdb

Distribution pack for Dayz Epoch modders (shared mode)

Primary LanguageSQF

extDB - Distribution pack for Dayz Epoch modders

Addon for Connecting to MariaDB/MySQL Databases (distribution for A2 dayzEpoch 1.0.6.2)

This addon is not my work. iben AKA @infobeny gave me permission to keep it available for the DayZ Epoch Community!

Distribution Last update [2018-01-01], v0.3.1 || extDB version: extDB3 [1032] || (see changelog) STATUS: WIP (todo: readme, helper function introduction, reconnection string in case of connection failure, enhanced security model (randomize connection protocol ID's - storing to uiNamesapce...etc), debug console video tutorial(is anyone interested?))

Credits

extDB3 Author: Bryan "Tonic" Boardwine AKA @Torndeco

Virtual Garage: Addon (serverside files) used for test implementation - version by @oiad AKA salival

DayZ Epoch Mod: Developers, Collaborators, Contributors and Community

TOC

Introduction

@ todo:

  • A few words (focus group, minimum knowledge)
  • Available fnc for extDB communication (description, params, examples + enhanced log options)

Visual Overview for Noobs

extDB3 Visual Overview

When you see [number] in the text bellow, check the [number] in this visual overview for better context understanding...

Installation Checklist for SERVER OWNERS

Installation Checklist for ADDON DEVELOPERS

Details:

How to prepare @extDB configuration files?

  1. "Define your database" - extdb3-conf.ini ([8]/[9])
; ---------------------------------------------------------------------------
; Default database for Dayz Epoch
[epoch]
IP = 127.0.0.1
Port = 3306
Username = changeme
Password = changeme
Database = epoch

; ---------------------------------------------------------------------------
; Separate database for your addon
[my_database1]
IP = 127.0.0.1
Port = 3306
Username = changeme
Password = changeme
Database = my_database1

; ---------------------------------------------------------------------------
; ...additional databases goes bellow if needed...
  1. "Define CUSTOM_SQL settings and prepared statements / procedures for your addon" - yourAddonName-conf.ini ([6]/[7])
; ---------------------------------------------------------------------------
; Virtual Garage example

; ---------------------------------------------------------------------------
[Default]

Version = 1
Strip Chars = "[]\/\|;{}<>\'"
Strip Chars Mode = 0
Input SQF Parser = false
Number of Retrys = 5

; ---------------------------------------------------------------------------
[RemoveOldVG]
Prepared Statement = false

SQL1_1 = CALL RemoveOldVG;

; ---------------------------------------------------------------------------
[queryVehicle]

SQL1_1 = SELECT id, classname, Inventory, CharacterID, DateStored FROM garage WHERE PlayerUID=? ORDER BY DisplayName;

SQL1_INPUTS = 1
OUTPUT = 1,2-STRING,3,4,5-STRING

; ---------------------------------------------------------------------------
[spawnVehicle]
SQL1_1 = SELECT classname, CharacterID, Inventory, Hitpoints, Fuel, Damage, Colour, Colour2 FROM garage WHERE ID=?;

SQL1_INPUTS = 1
OUTPUT = 1-STRING,2,3,4,5,6,7-STRING,8-STRING

; ---------------------------------------------------------------------------
[storeVehicle]

SQL1_1 = INSERT INTO garage
SQL1_2 = (PlayerUID, Name, DisplayName, Classname, DateStored, CharacterID, Inventory, Hitpoints, Fuel, Damage, Colour, Colour2)
SQL1_3 = VALUES(?,?,?,?,?,?,?,?,?,?,?,?);

SQL1_INPUTS = 1,2,3,4,5,6,7,8,9,10,11,12

; ---------------------------------------------------------------------------
[deleteVehicle]

SQL1_1 = DELETE FROM garage WHERE ID=?;

SQL1_INPUTS = 1

How to prepare dayz_server files?

  1. "Enable your database" - define and include your database into database pool - EXTDB_DB_POOL ([12]/[14])
// ["database_cfg_name","database_name_alias"]
// ---
// "database_cfg_name"    : What's the name of your database? : "my_database1", "my_database2"
// "database_name_alias"  : Any string (friendly name for you) - used in log (so you don't need to use your real database name)
// ---

#define EXTDB_DB_POOL [ \
   ["my_database1","aliasName1"] \
  ,["my_database2","aliasName2"] \
]

// Important: Keep '#define' format, mainly '\' at the end of line (except the last one), otherwise parsing will fail!
  1. "Enable ability to talk to your database" - define and include session SQL_CUSTOM protocol - EXTDB_PS_POOL ([12]/[15])
// ["database_cfg_name","unique_SQL_CUSTOM_procol_name","addon-conf.ini","protocol_name_alias"]
// ---
// "database_cfg_name"              : The same name as in step 1 : "my_database1", "my_database2"
// "unique_SQL_CUSTOM_procol_name"  : Give the protocol unique name so we can use it for identification : "myProtocol111", "myProtocol222", "myProtocol333"
// "addon-conf.ini"                 : see [7]
// "protocol_name_alias"            : Any string (friendly name for you) - used in log (so you don't need to use your real name ID)
// ---

#define EXTDB_PS_POOL [ \
   ["my_database1","myProtocol111","addon1-conf.ini","protocolAlias1"] \
  ,["my_database1","myProtocol222","addon2-conf.ini","protocolAlias2"] \
  ,["my_database2","myProtocol333","addon3-conf.ini","protocolAlias3"] \
]

// Important: Keep '#define' format, mainly '\' at the end of line (except the last one), otherwise parsing will fail!
// Note: You can define protocol for already existing database (scenario: you didn't create new database, just new table for existing one)
  1. Optional: "Enable ability to receive exclusive log" - define and include session LOG protocol - EXTDB_LG_POOL ([12]/[16])
// ["database_cfg_name","unique_LOG_procol_name","unique_logfile_name"]
// ---
// "database_cfg_name"       : The same name as in step 1 : "my_database1", "my_database2"
// "unique_LOG_procol_name"  : Give the protocol unique name so we can use it for identification : "myLOGProtocol111", "myLOGProtocol222", "myLOGProtocol333"
// "unique_logfile_name"     : Choose the name for your logfile (`logs\Y\M\D\logfile.log`): "addon1log", "addon2log", "addon3log"
// ---

#define EXTDB_PS_POOL [ \
   ["my_database1","myLOGProtocol111","addon1log"] \
  ,["my_database1","myLOGProtocol222","addon2log"] \
  ,["my_database2","myLOGProtocol333","addon3log"] \
]

// Important: Keep '#define' format, mainly '\' at the end of line (except the last one), otherwise parsing will fail!
// Note: You can define multiple types of logs for your addon, i.e. "SPECIALTRADING", "VIRTUALGARAGE" etc.
  1. Optional: "Enable to run your stored procedures on server start/restart" - create and include stored procedure(s) - EXTDB_PR_POOL ([12]/[17])
-- Example scenario: let's say you want to create procedure for removing virtual garage vehicles older then 35 days
-- and you want to run this procedure each restart (info: alternative - events).
-- Remember: running on server start/restart is one option - you can use it dynamically if needed.

-- Create procedure using bellow code:
DROP PROCEDURE IF EXISTS `RemoveOldVG`;
DELIMITER ;;
CREATE PROCEDURE `RemoveOldVG`()
COMMENT 'Removes old Virtual Garage Vehicles'
BEGIN
  DELETE FROM
    `garage`
  WHERE `DateStamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 35 DAY);
END
;;

-- ...additional procedures goes bellow...
// Once you stored "RemoveOldVG" procedure, continue with 'EXTDB-cfg-admin' file:

// ["callType","SQL_CUSTOM_procol_name_from_step2_above","stored_procedure_name","procedure_alias"]
// ---
// "callType"                                 : Mostly you want to use type 1 : For more information visit extDB3 wiki...
// "SQL_CUSTOM_procol_name_from_step2_above"  : Already defined 'SQL_CUSTOM' session protocol for your addon : i.e. "myProtocol111"
// "stored_procedure_name"                    : Procedure config name as was defined in your addon config [7]
// "procedure_alias"                          : Any string (friendly name for you) - used in log (so you don't need to use your real procedure ID)
// ---

#define EXTDB_PR_POOL [ \
   [1,"myProtocol111","RemoveOldVG","VG Cleanup"] \
]

// Important:
// - Keep '#define' format, mainly '\' at the end of line (except the last one), otherwise parsing will fail!
// - Do not forget set 'Prepared Statement = false' for stored procedures in addon's config file [7]
  1. "Initiate extDB engine: Open server_functions.sqf and insert line: ([3])
call compile preprocessFileLineNumbers "\z\addons\dayz_server\extDB\EXTDB_init.sqf";
  1. Now you can use extDB engine in your addon - see VG addon implementation example bellow:
// ---------------------------------------------------------------------------
// :: Query vehicle
_result = [2,"VGAR",(format ["queryVehicle:%1",_playerUID])] call EXTDB_fnc_APICall;

// ---------------------------------------------------------------------------
// :: Store vehicle
_locTime = [] call EXTDB_fnc_getLocalTime;
_displayName = ((getText(configFile >> "cfgVehicles" >> _class >> "displayName")) call EXTDB_fnc_sanitize);

_query = format [
   "storeVehicle:%1:%2:%3:%4:%5:%6:%7:%8:%9:%10:%11:%12"
  ,_playerUID,_name,_displayName,_class,_dateFormat,_charID,_inventory,_array,_fuel,_damage,_colour,_colour2
];
_null = [1,"VGAR",_query] call EXTDB_fnc_APICall;

// ---------------------------------------------------------------------------
// :: Spawn vehicle
_result = [2,"VGAR",(format ["spawnVehicle:%1",_id])] call EXTDB_fnc_APICall;
_null = [1,"VGAR",(format ["deleteVehicle:%1",_id])] call EXTDB_fnc_APICall;

// ---------------------------------------------------------------------------
// :: Exclusive log
  _m = format[
     "GARAGE: %1 (%2) retrieved %3 @%4 %5"
    ,if (alive _player) then {name _player} else {"DeadPlayer"},getPlayerUID _player,_class,mapGridPosition (getPosATL _player),getPosATL _player
  ];
  _null = ["server_spawnVehicle","VIRGA",_m] call EXTDB_fnc_callEXTLog;
  1. For more info see section Tips

Putting all together: Virtual Garage Case Study

Goal:

  • Update old extDB to version extDB3;
  • Change current exclusive extDB VG implementation to "shared mode", so other modders can benefit from it;
  • Enhance VG code security by switching from raw sql statements to config prepared statements;
  • Use default epoch hive calls only if it's neccessary;
  • Write VG log activity into separate logfile for better overview (prevent "missed in RPT mess");

Prerequisities:

  • Virtual Garage addon already installed - only server-side files are needed for upgrade;
  • extDB3 addon - Distribution pack for Dayz Epoch modders - this repo/arma_server_root.

Starting Point

  • Virtual Garage is using default epoch database - table garage;
  • We are working in the local testing env (localhost).

Step by Step

--- (01) ---

  • Prepare files for upgrade
    • Download this repository - save it to your desktop;

--- (02) ---

; Note: [vehicle_manager] is your CONFIG DATABASE NAME you are gonna use across all further steps.
;       It doesn't have to be the same as 'Database = database_name'!
; ---------------------------------------------------------------------------
[vehicle_manager]

IP = 127.0.0.1
Port = 3306
Username = vehmng
Password = N507HluCoX
Database = vehicle_manager
; In this file are stored all procedures and prepared statements used across VG server-side files.
; If you will use procedure 'RemoveOldVG', remember - procedure has to already exists in your database (see 'How to prepare `dayz_server` files?' / 4).
; ---------------------------------------------------------------------------
[RemoveOldVG]
Prepared Statement = false

SQL1_1 = CALL RemoveOldVG;
  • Copy @extDB related files into arma server root
    • From arma_server_root copy to you arma server root:
      • folder @extDB (modifications are already done from previous step);
      • tbbmalloc.dll (if you already running Epoch 1.0.6.2 with new DLL files, you can skip this step);

--- (03) ---

// :: (A) Add your database info into pool
// :: ["database_cfg_name","database_name_alias"]
// ---------------------------------------------------------------------------
#define EXTDB_DB_POOL [ \
   ["vehicle_manager","myCodeNameForDB"] \
]

// :: You can delete rest of definitions in the file you don't need (it's just an example there)
// :: (B) Define VG specific 'SQL_CUSTOM' session protocol
// :: ["database_cfg_name","unique_SQL_CUSTOM_procol_name","addon-conf.ini","protocol_name_alias"]
// ---------------------------------------------------------------------------
#define EXTDB_PS_POOL [ \
   ["vehicle_manager","VGAR","vg-conf.ini","Garage"] \
]

// :: You can delete rest of definitions in the file you don't need (it's just an example there)
// :: (C) Define VG specific 'LOG' session protocol
// :: ["database_cfg_name","unique_LOG_procol_name","unique_logfile_name"]
// :: VG logs will be stored : 'arma_root/@extDB/YEAR/MONTH/DAY/virtual_garage.log'
// ---------------------------------------------------------------------------
#define EXTDB_LG_POOL [ \
   ["vehicle_manager","VIRGA","virtual_garage"] \
]

// :: You can delete rest of definitions in the file you don't need (it's just an example there)
// :: (D) In case you created any stored procedure(s), set your execution request here (otherwise leave it alone)
// :: ["callType","SQL_CUSTOM_procol_name_from_above","stored_procedure_name","procedure_alias"]
// ---------------------------------------------------------------------------
#define EXTDB_PR_POOL [ \
   [1,"VGAR","RemoveOldVG","VG Cleanup"] \
]

// :: You can delete rest of definitions in the file you don't need (it's just an example there)
  • Configure @Dayz_Epoch_Server addon - initiate extDB engine 
call compile preprocessFileLineNumbers "\z\addons\dayz_server\extDB\EXTDB_init.sqf";
  • Upgrade Virtual Garage server-side files
    • Open @Dayz_Epoch_Server/addons/dayz_server/compile/garage folder.
    • Remove fn_async.sqf file.
    • Replace rest of files with files from this repo, or merge them using diff tool.
    • When you're done, pack and copy whole folder @Dayz_Epoch_Server to your local arma server root, so path will look like:
      arma server root/@DayZ_Epoch_Server/.

--- (04) ---

TIPS

TIP 01: Use power of stored root path

You can move day_server\extDB wherever you like without changing paths.

Root path is autodiscovered on init and stored into EXTDBROOT variable. If you will need to add your custom helper functions - all you need to do is just use it (see EXTDB_init.sqf).

TIP 02: Use debug options

Before you push your addon to live server, stay a little while with your test server and get the most info you can get:

  • Test your protocols, queries etc. using console - no need to go online with Arma!

    • Open debug_files folder and copy to your local arma server root:
      • extdb3-conf.ini: Make sure it already has correct info.
      • extDB3.dll
      • extDB3-test.exe
    • Make sure, you are running Epoch 1.0.6.2 tbbmalloc.dll library [2] in local arma server root.
    • Open arma_server_root/@extDB folder and, again, copy whole folder sql_custom to your local arma server root:
      • Open it in your editor for later - you will write your stored procedures in there (and test them in console...)
    • That's it. Now you can start console from your local arma server root and run extDB3-test.exe.
      • Important:
        • Remember: as a minimum, you need to connect to database and create session protocol to be able to talk to database.
        • When you're testing, you will need to reset connection from time to time (using system command "9:RESET" - mostly when you change your SQL code inside sql_custom folder) - Do not forget set Allow Reset = true in extdb3-conf.ini (We are still in arma server root, right?).
        • As a start, you can check this debug console system commands test file and this general call types info file

  • Get trace level debug info in live game using debug DLL

  • Get detailed info about extDB engine initialization

    • If you wish enable handy debug RPT info (...or on screen debug info where applicable), make sure these definitions are uncommented:
      • #define __ROOTDBG__ - prints autodiscovered addon root path to RPT (Where is it?)
      • #define __EXTDBGEN__ - prints debug info to RPT (results, params errors etc.) (Where is it?)
      • #define __EXTDBINT__ - prints debug info to RPT + on screen info (systemChat) (Where is it?)
    • See bellow how RPT log looks like when #define __ROOTDBG__ and #define __EXTDBGEN__ are enabled:
      Scenario: We are initiating 2x databases; 3x session protocols; 2x log protocols (no error mode)
    • See more debug outputs in different scenarios
// ---------------------------------------------------------------------------
// RPT log:
10:47:53 "=== [EXTDB, [EXTDB_init]] || DEBUG :: [EXTDB_fnc_init_ROOTer] >> Addon relative root >> [z\addons\dayz_server\extDB]"
10:47:53 "=== [EXTDB, [SYSTEM]] || DEBUG :: [EXTDB_connect] >> Connection procedure initiated..."
10:47:53 "=== [EXTDB, [SYSTEM]] || DEBUG :: [EXTDB_connect] >> INFO >> DB pool processing started..."
10:47:53 "=== [EXTDB, [SYSTEM]] || DEBUG :: [EXTDB_connect] >> INFO >> Connection to database [0-myCodeNameForDB1] set."
10:47:53 "=== [EXTDB, [SYSTEM]] || DEBUG :: [EXTDB_connect] >> INFO >> Connection to database [1-myCodeNameForDB2] set."
10:47:53 "=== [EXTDB, [SYSTEM]] || DEBUG :: [EXTDB_connect] >> INFO >> PS pool protocol started..."
10:47:53 "=== [EXTDB, [SYSTEM]] || DEBUG :: [EXTDB_connect] >> INFO >> Session protocol [0-Garage] set."
10:47:53 "=== [EXTDB, [SYSTEM]] || DEBUG :: [EXTDB_connect] >> INFO >> Session protocol [1-protocolAlias2] set."
10:47:53 "=== [EXTDB, [SYSTEM]] || DEBUG :: [EXTDB_connect] >> INFO >> Session protocol [2-protocolAlias3] set."
10:47:53 "=== [EXTDB, [SYSTEM]] || DEBUG :: [EXTDB_connect] >> INFO >> LOG pool protocol started..."
10:47:53 "=== [EXTDB, [SYSTEM]] || DEBUG :: [EXTDB_connect] >> INFO >> LOG protocol [0-VIRGA] set."
10:47:53 "=== [EXTDB, [SYSTEM]] || DEBUG :: [EXTDB_connect] >> INFO >> LOG protocol [1-NEWAD] set."
10:47:53 "=== [EXTDB, [SYSTEM]] || DEBUG :: [EXTDB_connect] >> INFO >> Locking API..."
10:47:53 "=== [EXTDB, [SYSTEM]] || DEBUG :: [EXTDB_connect] >> INFO >> Result: [2/2] databas(es) connected [3/3] session protocol(s) set [2/2] log protocol(s) set."
10:47:53 "=== [EXTDB, [SYSTEM]] || DEBUG :: [EXTDB_connect] >> INFO >> Connection procedure finished in [0.275002 seconds] with status [1]."
10:47:53 "=== [EXTDB, [SYSTEM]] || DEBUG :: [EXTDB_runDBCleanup] >> INFO >> DB cleaning procedure initiated..."
10:47:53 "=== [EXTDB, [SYSTEM]] || DEBUG :: [EXTDB_runDBCleanup] >> INFO >> Cleaning procedure [0-VG Cleanup] fired..."
10:47:53 "=== [EXTDB, [SYSTEM]] || DEBUG :: [EXTDB_runDBCleanup] >> INFO >> Cleaning procedure finished in [0 seconds] with status [true]."

Changelog

Date Version Description
[2017-11-23] v0.1 Initial release
[2017-11-28] v0.2 Added debug src files;
TIP 01: 'Use power of stored root path'; TIP 02: 'Use debug options' - incl. examples
[2017-12-31] v0.3 Updated info for Epoch 1.0.6.2 [tbbmalloc (x86) Intel TBB 2018 Update 2]
[2018-01-01] v0.3.1 Updated virtual garage serverside files to the latest Epoch 1.0.6.2