machine6fd 11 Posted October 29, 2014 Anyone have either a mission file i could look at that can help me understand this a little better? or maybe some example sqf that is working? Share this post Link to post Share on other sites
torndeco 128 Posted November 1, 2014 @machine6fd still working on my rewrite of wasteland abit... I have a wip on github tonight/tomorrow. If you understand the SQL Statements / Database side and its just talking to extDB that is confusing... Just give me a shout on Skype & will help you out etc @Everyone else I really shouldn't need to mention this, don't forget to use the following options in your server.cfg examples @ https://community.bistudio.com/wiki/server.cfg Its to prevent people from reading your mysql details etc... allowedLoadFileExtensions[] allowedPreprocessFileExtensions[] allowedHTMLLoadExtensions[] Share this post Link to post Share on other sites
rimblock 0 Posted November 8, 2014 The strip code is only ran against the $INPUT_ values...But i will look at adding options to log / return error out if it encounters a bad character. Not sure if will make it into next version or not (would need to make / use a different function). Hi man, Did you get any further with this. Finally have a bit more time to look at my mod again and have just been upgrading from v18 -> v20. Thanks. Share this post Link to post Share on other sites
torndeco 128 Posted November 8, 2014 Not yet.. been inactive abit with extDB lately. There is a new Poco Stable Release with support for building using VS2013 So planning / hoping to get a new build out this weekend.. Ideally if i can will look at Add - Report bad chars encountered - See about expanding DB_CUSTOM features - Backport Poco 1.5 support for MySQL DateTime Datatype - Update to latest Boost Library Windows builds using VS2013 instead of current build system using VS2010 Share this post Link to post Share on other sites
torndeco 128 Posted November 9, 2014 (edited) Version 21 Just some minor changes.. Main thing is Windows Builds are now built using VS2013.. If you have issues make sure you tried installing http://www.microsoft.com/en-us/download/details.aspx?id=40784 Also the tbb + tbb malloc dlls have been updated/changed. ADDED: STRIP+LOG / STRIP+ERROR Options for Bad Chars in DB_CUSTOM_V3. FIXED: Issue if you select NONE for action for Bad Chars Action for DB_CUSTOM_V3. FIXED: Issue with wrong timestamp for filename, been off by a hour should be fixed now (via newer Poco). REMOVED: ODBC Support Removed, can be re-added if someone needs it. <--- Windows Binarys are now built using VS2013 + Update Various Libraries i.e Poco Boost MySQL Client Library. Linux Binarys updated Boost / Poco etc... Edited November 9, 2014 by Torndeco Share this post Link to post Share on other sites
rimblock 0 Posted November 12, 2014 Version 21Just some minor changes.. Main thing is Windows Builds are now built using VS2013.. If you have issues make sure you tried installing http://www.microsoft.com/en-us/download/details.aspx?id=40784 Also the tbb + tbb malloc dlls have been updated/changed. ADDED: STRIP+LOG / STRIP+ERROR Options for Bad Chars in DB_CUSTOM_V3. FIXED: Issue if you select NONE for action for Bad Chars Action for DB_CUSTOM_V3. FIXED: Issue with wrong timestamp for filename, been off by a hour should be fixed now (via newer Poco). REMOVED: ODBC Support Removed, can be re-added if someone needs it. <--- Windows Binarys are now built using VS2013 + Update Various Libraries i.e Poco Boost MySQL Client Library. Linux Binarys updated Boost / Poco etc... Hi, When you say strip and error, does this mean the code with a banned character(s) will not get to the DB (my preference) ?. I am guessing (have not tried myself yet) that you can load multiple custom sql .ini files with different aliases by using multiple DB_CUSTOM_v3 calls ?. So, for example, I could have one custom sql file for player sql calls another for vehicle sql calls and another for generic sql calls etc all loaded at the same time. I am just thinking of the best way to allow others to add-on to our mod using extDB without having to merge in to a single large custom sql .ini file. Thanks Share this post Link to post Share on other sites
torndeco 128 Posted November 12, 2014 (edited) @RimBlock Exactly you can have multiple custom DB_CUSTOM_V3, they just need to called different aliases Also you need to do this before calling 9:LOCK STRIP Strip the character(s) out. Will run SQL Statement. STRIP+LOG Log it in extDB logs. Strip the character(s) out. Will run SQL Statement STRIP+ERROR Log it in extDB logs. Return [0,"Error Bad Char Found"]. Will not run SQL Statement Main thing to consider with STRIP+ERROR is when you are saving Player Names in a SQL Query etc... Edited November 14, 2014 by Torndeco Share this post Link to post Share on other sites
rimblock 0 Posted November 14, 2014 Great, just what I wanted to know. Thanks Share this post Link to post Share on other sites
rimblock 0 Posted November 21, 2014 (edited) Getting an interesting return when calling a sql block that does not exist in the custom sql .ini file from Arma3. Running the code through the extDB-test gives what I would expect though. Through extDB-test.exe 9:DATABASE:DominionDB extDB: Database Type: MySQL extDB: Database Session Pool Started extDB: [1] 9:VERSION extDB: 21 9:ADD:DB_CUSTOM_V3:CUSTOM_SQL:testsql extDB: [1] 2:CUSTOM_SQL:GetPlayerCharID:76561197970856120:1 extDB: [2,"1"] extDB: DB_CUSTOM_V3: Trace: GetPlayerCharID:76561197970856120:1 5:1 extDB: [1,[0,"Error No Custom Call Not Found"]] Via Arma 3 Query: 2:CUSTOM_SQL:GetPlayerCharID:76561197970856120:1 Return: [2,"1749605807"] Using parsenumber to try and turn "1749605807" in to a number I end up with 1.74961e+009 Query: 5:1.74961e+009 Return: [0,"Error Invalid Message"] 1. Where is 1749605807 coming from ?. The number is always the same and is the first call to the DB after Arma 3 server restart. 2. Can you take the return key number in as a string and convert in the .dll to a number which would help to avoid the number format change for large numbers. Whilst this issue should never come up usually but I am working on error handling at the moment and would like to be able to manage issues correctly. Update: Sorted out the number conversion. Would still like to know why it is starting so high and if the return key is being sent back as a string then why not accept it back as a string for getting the results. Edited November 21, 2014 by RimBlock Share this post Link to post Share on other sites
torndeco 128 Posted November 21, 2014 Just store the unique ID as a string... Since the unique ID is sent back to extension as a string, there is no need to use parseNumber on it. All you are doing is adding extra work for Server. By converting it into a number + back to a string when fetching the result ------ Unique ID starting number is a randomize when extension is loaded... Its just a security feature Share this post Link to post Share on other sites
torndeco 128 Posted November 29, 2014 Just a small status update.. Actually back doing abit of coding etc :) Upcoming changes for new release this weekend are 1) Prepared SQL Statement Support (DB_CUSTOM_V5) This should prevent against SQL Injection Attacks & also be a performance boost for SQL Statements that are called more than once. This will requires some custom changes to Poco Library, will have seperate Github to show changes etc, for those that build from source. Mainly the Poco Data Library code changes are so we can keep cache of called Prepared SQL Statements for each Database Connection (since they are database connections are dynamic based on config & db usage). 2) Also adding the ability to define to add quotes etc, when retrieving values from Database. i.e no more needed to have quotes in Database Fields etc... 3) Update to Latest Poco... Main change of note is optimization on Windows Poco Library Builds is turned on /O2 Share this post Link to post Share on other sites
torndeco 128 Posted December 7, 2014 (edited) Right i am gonna get some needed sleep Will upload custom Poco Library Code Changes + compile new extDB Windows + Linux versions later today. Just a week late :j: DB_CUSTOM_V5 uses prepared statements, required some changes to Poco Statements + SessionPool so i could cache the Statements per Session. Sample of new format of config file [insertAntihackLog] SQL1_1 = INSERT INTO AntihackLog (Time, ServerID, uid, guid, name, hackType, hackValue) SQL1_2 = VALUES (NOW(), ?, ?, ?, ?, ?, ?); Number of Inputs = 5 SQL1_INPUTS = 1, 2, 2-BEGUID, 3-STRING, 4-STRING, 5-STRING More examples of INPUTS / OUTPUTS SQL1_INPUTS = 1, 2, 2-BEGUID, 3-STRING, 4-STRING, 5-STRING SQL2_INPUTS = 2-STRING-NOCHECK, 1-BEGUID-STRING OUTPUTS = x-NOCHECK, x-CHECK, x-BEGUID, x-STRING, x, x-STRING-CHECK Yes you can combine different options or override sanitize checks per value if needed... I will look @ doing the same for strip options later on. Will still support DB_CUSTOM_V3 for atleast another month, so there is no rush to update. But its recommended -------------- Other news Upcoming Poco 1.6 is still bugged for Linux Versions so no updating for that atm :( Believe its related to https://github.com/pocoproject/poco/issues/479 But i haven't had time or been able to debug the problem yet. Will finally add configureable VAC Banning (using RCON Commands) support to extDB. Should be done by next week 90% done already for months now. Edited December 7, 2014 by Torndeco Share this post Link to post Share on other sites
lodac 10 Posted December 7, 2014 Good work Torndecco! Share this post Link to post Share on other sites
torndeco 128 Posted December 7, 2014 (edited) Version 23 Finally got another release done, just a week late. Main Changes are the following New Protocol DB_CUSTOM_V5, uses cached Prepared Statements + Options for INPUTS + OPTION :cool: extdb-conf.ini + extDB/ directory can now be located in same directory as extension, fallback location is Arma3 Directory For example now you can have @extDB/extdb-conf.ini @extDB/extDB @extDB2/extdb-conf.ini @extDB2/extDB Other Changes Updated Poco Library to 1.4.7p1 + custom changes. Just some minor fixes, and Poco Library now has optimization turned on for Windows Builds Custom changes are in github, basicly done to allow caching of Prepared Statements per DB Session. (used for DB_CUSTOM_V5) Known Issues Test Applications require tbb + tbbmalloc + extdb-conf.ini + extDB/ in the same directory as executable. Note there was a Version 22 Test Build, but was on github. I just never got around to testing it. https://github.com/Torndeco/extdb/tree/stable/release/windows https://github.com/Torndeco/extdb/tree/stable/release/linux Edited December 14, 2014 by Torndeco Share this post Link to post Share on other sites
spitfire007 10 Posted December 9, 2014 (edited) Thanks for the xmas present Torndeco ! I am trying to set this up for A3Wasteland. But I have had a little trouble understanding just what files go where though, and just what versions I should be using. At the moment I am not sure where tbb.dll should be ? It should be in the root Arma3 Directory and it is used for cmakelists ? ... I think. I am calling tbbmalloc.dll with arma3server.exe -profiles=c:\Games\Arma3\A3Master -port=2302 -config=CONFIG_Vanilla.cfg -world=empty -maxMem=8192 -malloc=tbbmalloc -enableHT -mod=@extDB But ... as you can see (below) it is not accepting that as the allocator for some reason .... (maybe unrelated to extDB). It is in the normal arma dll directory with all the other malloc's.... I have even tried just leaving yours only there as per the malloc read me .. but it still fails for some reason. == arma3server.exe -profiles=c:\Games\Arma3\A3Master -port=2302 -config=CONFIG_Vanilla.cfg -world=empty -maxMem=8192 -malloc=tbbmalloc -enableHT -mod=@extDB Original output filename: Arma3Retail_Server Exe timestamp: 2014/12/04 11:03:24 Current time: 2014/12/10 02:02:52 Type: Public Branch: Stable Version: 1.36.128579 Allocator: C:\Games\Arma3\A3Master\dll\tbb4malloc_bi.dll Edit: .. still don't know why the incorrect malloc is loading. My connection to MySQL is ok according to my extdb logs. So it's finding the extb-config.ini I guess ... In your git instructions it says. Copy all files/folders (u can ignore the *.exe) to /path/to/arma3Edit your arma3 launch parameters add @extDB to your mod line. Edit /path/to/arma3/extdb-conf.ini and edit your settings etc... Even the malloc ? because when I move it to the root arma3 path the connection fails. In the correct dll path it connects. No leave it in Arma3path/dll Which file do I "Edit /path/to/arma3/extdb-confi.ini" ? No edit needed just put it in Arma3path/extDB Do I need to do anything other than leave your customised a3wasteland.ini in the default extDB/db_custom directory ? NO.. just leave it there. How do I setup the MySQL schema for wasteland ... ? I am a noob at that so any pointers would be helpful. Or even a sql dump ? A real step by step overview of just what is needed to install this on an A3Wasteland server would be even better Thanks again for your efforts. Edit: Ok... now I am getting really confused. I overlooked that I did not put the @ in the extDB directories name .... (using v23).... so I did that. Now I look more carefully at the logs I see it's not loading as an addon. The error I now get in RPT is ... 4:01:34 "extDB: Error Database Setup: [0,"Failed to Load Protocol"]" It made a new extdb directory with db_custom (now empty as I moved everything to @extDB) and logs. The log is now saying. [2014-Dec-10 04:01:34.854514]: ThreadID 0x0000051c: extDB: Version: 20 [2014-Dec-10 04:01:34.855531]: ThreadID 0x0000051c: extDB: Found extdb-conf.ini [2014-Dec-10 04:01:34.855531]: ThreadID 0x0000051c: extDB: Creating Worker Thread +1 [2014-Dec-10 04:01:34.855531]: ThreadID 0x0000051c: extDB: Creating Worker Thread +1 [2014-Dec-10 04:01:34.855531]: ThreadID 0x0000051c: extDB: Creating Worker Thread +1 [2014-Dec-10 04:01:34.855531]: ThreadID 0x0000051c: extDB: Creating Worker Thread +1 [2014-Dec-10 04:01:34.855531]: ThreadID 0x0000051c: extDB: Creating Worker Thread +1 [2014-Dec-10 04:01:34.855531]: ThreadID 0x0000051c: extDB: Creating Worker Thread +1 [2014-Dec-10 04:01:34.855531]: ThreadID 0x0000051c: extDB: Creating Worker Thread +1 [2014-Dec-10 04:01:34.856519]: ThreadID 0x0000051c: extDB: Creating Worker Thread +1 [2014-Dec-10 04:01:34.874520]: ThreadID 0x0000051c: extDB: Database Type: MySQL [2014-Dec-10 04:01:34.877533]: ThreadID 0x0000051c: extDB: Database Session Pool Started [2014-Dec-10 04:01:34.896526]: ThreadID 0x0000051c: extDB: DB_CUSTOM_V3: No Template File Found: extDB\db_custom\a3wasteland.ini Edit: Ok.... fixed for all the above code .... don't using @extDB as an addon.... it's NOT an addon ? ... I think ? Just keep it all in extDB in the root directory of Arma3. The only problem I have now ... is setting up the schema database in MySQL... a dump file would be REALLY handy :-) Edited December 10, 2014 by spitfire007 BRAIN NOW WORKING AS RED BULL HAS KICKED IN Share this post Link to post Share on other sites
torndeco 128 Posted December 10, 2014 @spitfire007 extDB is a Database Connector in simplest of terms its not made just for A3Wasteland... For example Altis Life RPG uses it + afew other versions of Life Missions... Some Epoch Arma2 Servers are using it for custom content etc... Go to a3wasteland forums for support, but last i checked there is no offical support for extDB yet (its still a WIP) Share this post Link to post Share on other sites
torndeco 128 Posted December 14, 2014 Version 24 This is mostly a bug fix release and just some new minor features. RCon features pushed back to next release version. ADDED: 9:LOCK_STATUS returns [1] if Locked or [0] if Unlocked ADDED: MISC:RANDOM_STRING + MISC:RANDOM_UNIQUE_STRING returns array of randomize strings.MISC:RANDOM_STRING:<Number of Strings>:<Length of Strings>MISC:RANDOM_UNIQUE_STRING :<Number of Strings>:<Length of Strings> FIXED: DB_CUSTOM_V5 Output Option is now working correctly. FIXED: extdb-conf.ini (& /extDB in same directory) file can now be located in custom path i.e @extDB, instead of Arma3 Root Directory Share this post Link to post Share on other sites
torndeco 128 Posted December 18, 2014 (edited) Upcoming Changes for Version 25 There will be 2 Different Builds for extDB2, swear i want to give me a headache.... extDB: Old Stable Builds extDB2: New Unstable Builds extDB2 will be built using Poco Library 1.5/1.6, (finally got around to debugging a deadlocking issue). Reason for renaming extension is Poco 1.5/1.6 has fixed detecting MySQL Text Datatypes as Text. This will break any mission / mod that is using Auto String Detect on extDB i.e like Altis Life RPG etc... Benefits of updating to Poco 1.5/1.6 MySQL support for Large Text Datatypes (without it allocating 4gb straight away and causing a segfault) MySQL support for Date/Time Proper Procedure Support MongoDB + JSON Support (Might not make it into v25) Will also add VAC Bans Checking, returning results + logging support (be in both versions) Note: RCon Support may not make it into v25, if it does will be expermential extDB will still get some bug fixes + new features were possible backported in the near term. Edited December 18, 2014 by Torndeco Share this post Link to post Share on other sites
yourry 14 Posted December 20, 2014 I work on setting up your system for DB persistence mod Reign Of Jurassic. However, I have a little trouble understanding how your orders. Is someone a template mission to serve me an example? Thank you Share this post Link to post Share on other sites
torndeco 128 Posted December 20, 2014 (edited) @Yourry There are afew different methods of using extDB to access Database... Kinda allows me to add changes without breaking stuff as much. DB_CUSTOM_V5.. No mission available as an example, but its similar method as DB_CUSTOM_V3. Just the implementation + db_conf.ini thats is different. Advantages its the safest method, since it uses Prepared Statements + will be more feature rich. Recommended method atm DB_CUSTOM_V3.. You could look @ A3wasteland, https://github.com/A3Wasteland But Agent Rev is using DB_CUSTOM_V3 + doing something that was never intended. So its slightly different than intended method of use. Or you could look @ https://github.com/Torndeco/A3Wasteland_Framework (i kinda gave up on this rewrite) DB_RAW https://github.com/TAWTonic/Altis-Life Altis Life RPG uses DB_RAW_V2, downside is all the making SQL Safe against injection is done via SQF Code. ------------------ I am normally on Skype. Torndeco is nick, you can find me in the Script Makers / Dedicated Server / Linux Server / Tool Makers Channel Send me a PM, and can answer any questions you got or help you out where you are stuck on Edited December 20, 2014 by Torndeco Share this post Link to post Share on other sites
yourry 14 Posted December 21, 2014 Thank you for your reply and links. I'll see how the system works from the links. I would ask you some questions may be later. Anyway thank you for your help and your work on extDB. Share this post Link to post Share on other sites
rimblock 0 Posted December 21, 2014 Just a FYi, you may have already fixed it. Version: 21 Protocol: DB_CUSTOM_V3 Issue: If not enough input parameters are supplied the call still returns with "1" (1=OK). The error is logged in the extDB log file but the return to the code does not indicate any form of error. Share this post Link to post Share on other sites
yourry 14 Posted December 21, 2014 I do not really understand where I made a mistake. My values entered in the database are not good: My database is implemented with the data values in the db_custom: [playerInfoWriteOnDB] SQL1_1 = INSERT INTO players_save (playeriud, name) VALUES ('$INPUT_1', '$INPUT_STRING_2'); Number of Inputs = 2 Here is the command that requires writing in the DB: _result = [format["playerInfoWriteOnDB:%1:%2", _playerUID, _playerName], 2] call extDB_fnc_async; I took the code for the "extDB_fnc_async.sqf" function Tonic and placed a diag_log to know the command: ... _key = "extDB" callExtension format["%1:%2:%3",_mode, (call roj_server_extDB_ID), _queryStmt]; diag_log format ["extDB_fnc_async.sqf->query: %1:%2:%3",_mode, (call roj_server_extDB_ID), _queryStmt]; //--- DEBUG ... And therefore in the server logs that I have : 18:38:11 "extDB_fnc_async.sqf->query: 2:159963:playerInfoWriteOnDB:12345678910:=[TTK]= Yourry" I do not understand where I made a mistake. You have a track? Thank you Share this post Link to post Share on other sites
torndeco 128 Posted December 21, 2014 @RimBlock Yeah someone just came across that bug like around 2 days ago... Theres is a hotfix build for Windows @ https://github.com/Torndeco/extdb/tree/dev/release/windows/test Got another issue i need to reproduce and fix atm :( @Yourry Sorry about late reply had to get my local code into a compiling state. Your really should grab skype ( i hate it myself, but arma community uses it sigh) be able to help out faster. You just mixed up DB_CUSTOM_V3 + DB_CUSTOM_V5 config files. Example of DB_CUSTOM_V5 https://github.com/Torndeco/extdb/blob/stable/release/windows/25%20Test/extDB/db_custom/example.ini#L219-L225 Example of DB_CUSTOM_V3 https://github.com/Torndeco/extdb/blob/21/release/windows/21/extDB/db_custom/example-a3wasteland.ini So if you change your config to [playerInfoWriteOnDB] SQL1_1 = INSERT INTO players_save (playeriud, name) VALUES (?, ?); SQL1_INPUTS = 1, 2-STRING Number of Inputs = 2 Basicly V5 = Prepared Statements, plus they are cached per Session. So there is really only some overhead first time they are ran. Share this post Link to post Share on other sites
rimblock 0 Posted December 22, 2014 @RimBlockYeah someone just came across that bug like around 2 days ago... Theres is a hotfix build for Windows @ https://github.com/Torndeco/extdb/tree/dev/release/windows/test Got another issue i need to reproduce and fix atm :( Thanks. Not a show stopper for me and as I am building the sqf and sql stuff from the ground up I tend to do things that the more established mods have got past (silly code / sql mistakes for example). Note the response from the extention is "1" and not "[1[]]". The actual issue that caused it was not enough inputs suppllied and then an issue with the sql code (inserting a timestamp with now() in the insert statement but didn't name the column). As I said, silly mistakes. Will be happy to upgrade and help test extDB2 when you believe it is in a reasonible state to do so. Just let me know (here or on GitHub). Share this post Link to post Share on other sites
