Database entries/calls for coin systems

[eraser1]

It would be nice to have an entry in player_data for money that is "in the bank", and it would be great to have an entry in character_data for money that is "carried by the player" (that would be lost/lootable on death).

[XerxesIV]

I would second that

[BigEgg17]

Don't know if it is possible, but while the HiveEXT is being rewritten, it would be awesome if we could define the table and database that the banking_data table is in. Would make it much easier to Hive servers.

[icomrade]

The Epoch HiveEXT isn't being re-written, I'm just adding some things and fixing some stuff while I'm at it. Edit: I should add @BigEgg17 your request shouldn't be that hard if @Windmolders is willing, publishing or privately sending the source for the Single Currency DLL would help.

[icomrade]

I'm in the process of doing this, but I'm unsure of the difference between BankSaldo and BankMoney columns in the banking_data table.

Also, I should note I don't intend on having a separate table for banking data, since it's essentially just a duplicate of the player_data table. I've also decided to add a separate column for coins to the object_Data table and I've updated the hive to save/read coins that are on the character but this already appears to be available in certain version of the SC mod.

[eraser1]

As mentioned in the OP, that's all we need. A column in player_data, and a column in character_data.

[icomrade]

https://github.com/icomrade/DayZhiveEpoch/commit/dd506298633c46ab485c0ecf271034e55d1fe4c4

[ndavalos]

Thanks a ton, saves me a bunch of work. I'm going to figure out something to convert the old coins stored in inventory to the new fields later on. I'm just going to write a quick and dirty executable to do it most likely, and run the upgrade script for 106. Edit: figured out how to convert old character data when they log in, and have fixes for moving coins to the right field on spawning objects

[icomrade]

you could actually do this in SQF but it would be painfully slow but it would only need to be run once, although many server admins would probably just leave the conversion code in their server which would increase load times. too bad MySQL doesn't support regex, this would be very easy then

[ndavalos]

@icomrade actually, this should convert the inventory coins on characters to the new field: update character_data t1, (SELECT characterid, Cast(CASE WHEN Substring_index(inventory, ']', -2) = ']' THEN 0 WHEN Locate('e', REPLACE(REPLACE(REPLACE(Substring_index(inventory, ']', -2), ']', ''), '[', ''), ',', '')) > 0 THEN Cast(LEFT(REPLACE(REPLACE(REPLACE(Substring_index( inventory, ']', -2), ']', ''), '[', ''), ',', ''), Length(REPLACE(REPLACE(REPLACE( Substring_index(inventory, ']', -2), ']', ''), '[', ''), ',', ''))

• Locate('e', Reverse(REPLACE(REPLACE(REPLACE(Substring_index(inventory, ']', -2), ']', ''), '[', ''), ',', '')))) AS DECIMAL(11, 2)) * Pow(10, Cast(Substring_index( REPLACE( REPLACE(REPLACE(Substring_index(inventory, ']', -2), ']', ''), '[', ''), ',', '' ), 'e', -1) AS UNSIGNED)) ELSE Cast(REPLACE(REPLACE(REPLACE( Substring_index( inventory, ']', -2), ']', ''), '[', ''), ',', '') AS UNSIGNED INTEGER) end AS UNSIGNED) AS thedata FROM character_data) t2 set t1.coins = t2.thedata where t1.characterid = t2.characterid;

update character_data set inventory = concat(LEFT(inventory, LENGTH(inventory) - LOCATE(',', REVERSE(inventory))),',""]');

The last update should reformat the inventory properly.

[ndavalos]

Just one thing, some improperly formatted inventory fields will throw a warning about truncating integer values when it comes up "", I did a subquery to try to suppress this, but it didn't catch them all. This occurs for inventories that don't have coins stored in them already.

Going to write a conversion for object_data later.

[ndavalos]

I believe MySQL does support regex actually. http://dev.mysql.com/doc/refman/5.7/en/regexp.html

[ndavalos]

This should convert object_data properly: update object_data t1, ( SELECT objectid, Cast(CASE WHEN Substring_index(inventory, ']', -2) = ']' THEN 0 WHEN Locate('e', REPLACE(REPLACE(REPLACE(Substring_index(inventory, ']', -2), ']', ''), '[', ''), ',', '')) > 0 THEN Cast(LEFT(REPLACE(REPLACE(REPLACE(Substring_index( inventory, ']', -2), ']', ''), '[', ''), ',', ''), Length(REPLACE(REPLACE(REPLACE( Substring_index(inventory, ']', -2), ']', ''), '[', ''), ',', ''))

• Locate('e', Reverse(REPLACE(REPLACE(REPLACE(Substring_index(inventory, ']', -2), ']', ''), '[', ''), ',', '')))) AS DECIMAL(11, 2)) * Pow(10, Cast(Substring_index( REPLACE ( REPLACE(REPLACE(Substring_index(inventory, ']', -2), ']', ''), '[', '') , ',', '' ), 'e', -1) AS UNSIGNED)) ELSE Cast(REPLACE(REPLACE(REPLACE( Substring_index( inventory, ']', -2), ']', ''), '[', ''), ',', '') AS UNSIGNED INTEGER) end AS UNSIGNED) AS thedata FROM object_data where inventory <> '[]' and inventory <> '[[[],[]],[[],[]],[[],[]]]' and classname not like '%plastic%' and classname not like '%door%' ) t2 set t1.StorageCoins = t2.thedata WHERE t1.objectid = t1.objectid;

update object_data set inventory = concat(LEFT(inventory, LENGTH(inventory) - LOCATE(',', REVERSE(inventory))),']') where inventory <> '[]' and inventory <> '[[[],[]],[[],[]],[[],[]]]' and classname not like '%plastic%' and classname not like '%door%';

[ndavalos]

And for converting the database, the restructure script needs a change: ALTER TABLE player_data ADD PlayerCoins bigint(20) NOT NULL DEFAULT "0"; ALTER TABLE player_data ADD BankCoins bigint(20) NOT NULL DEFAULT "0"; ALTER TABLE object_data ADD StorageCoins bigint(20) NOT NULL DEFAULT "0"; ALTER TABLE character_data CHANGE CashMoney Coins bigint(20) NOT NULL DEFAULT "0"; UPDATE player_data t1, banking_data t2 SET t1.PlayerCoins = t2.bankMoney, t1.BankCoins = t2.BankSaldo WHERE t1.PlayerUID = t2.PlayerUID

At least in the version of MySQL I'm running, "ALTER COLUMN" to change a column name doesn't work, you'll get a syntax error. Changing it to "CHANGE" works :) Not sure if it's a version difference or not.

[icomrade]

Just a heads up there's currently an error I missed when syncing the previous commit, once I fix it I'll upload the new DLL

[ndavalos]

Looks like the object_data update needs a little work, have a few objects that got messed up with the backpack array.

[icomrade]

should be good with https://github.com/icomrade/DayZhiveEpoch/commit/33938bcb51a11bacb87ebbe276a796896fe8feab and https://github.com/icomrade/DayZhiveEpoch/commit/5e5e18ec7de5807c52aaadb3851c7bb1b195a8fe

Edit: New DLL Uploaded as well as dayz_server.pbo

[ndavalos]

@icomrade I'm trying to figure out what the intent of having bankcoins and playercoins is. Is one a bank limit and one the actual amount the player has in the bank?

[oiad]

Bankcoins are for coins in a bank playercoins are for coins a player has on their person, i.e for someone to loot when they die I guess.

[ndavalos]

Well there is a field on character for coins being carried, so I was assuming global was the bank limit.

[icomrade]

Yeah the ones in player_data are global, but can also be used for whatever you want, the bankcoins column is the banksaldo table from banking_Data as mentioned above and in one of the commits I didn't want to remove that column since I didn't know what it was used for. I suppose I named it bank coins since bank saldo translate to bank balance.

The ones in character_data are per-life coins stored on your currently living or previously living characters.

[ndavalos]

Ok that works, basically in the process of rewriting zupa's coins, figured I'd check intentions. This time around I'm trying to customize as little as possible from the base epoch code :)

[icomrade]

Alright just leave a comment if you have any suggestions or issues with the way I'm handling saving coins

[ndavalos]

@icomrade I'm getting some errors out of player_sync/hive whether or not I have single currency turned on, errors in hive and the section from my rpt with the player sync: http://server.dawnpatroldayz.com/hive_errors.txt

[icomrade]

Do you have a Coins column in Character_Data? I think it's case sensitive as well.

[ndavalos]

Yeah, it's in there, mysql isn't case sensitive. I ran the latest sql upgrade script. Getting you a character table and player table in a sec.

[ndavalos]

http://server.dawnpatroldayz.com/character_data.txt http://server.dawnpatroldayz.com/player_data.txt

[icomrade]

I'll have to look into this tomorrow I haven't gotten any errors with 201. Let me know if there's any more issues or detailed steps to reproduce. I remember an issue on here about MySQL being case sensitive in certain linux distros.

[ndavalos]

Linux yeah, case sensitivity is usually an issue with everything :) I'm running on Windows. This is with or without single currency, the only difference I have in player_sync is extra logging for the rpt to troubleshoot this. The call to player sync from the client is also unaltered. Really the only thing I did was add zsc (which appears to be working after I gutted it and rewrote a small bit of what was left), and the actions to fn_selfactions.

[ndavalos]

You know what, before you look at it let me recompile the epoch source pbos, or just drop the ones from the test build into my mod folder. I'm wondering if this isn't a problem with that. I'll update you tomorrow on that.

[oiad]

My FreeBSD mysql install is definitely case sensitive, my actual server on GTX servers mysql is Windows based and is not case sensitive, so you are definitely right there.

On 5/08/2016 4:56 p.m., icomrade wrote:

I'll have to look into this tomorrow I haven't gotten any errors with

201. Let me know if there's any more issues or detailed steps to reproduce. I remember an issue on here about MySQL being case sensitive in certain linux distros.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/EpochModTeam/DayZ-Epoch/issues/1715#issuecomment-237755172, or mute the thread https://github.com/notifications/unsubscribe-auth/APuIVTMQK8RQ8wRs085abSvRnVW23dcZks5qcsKPgaJpZM4JYuK3.

[ndavalos]

@icomrade it turned out to be either the medical or current state from a converted character. Probably should just default those fields to something with the conversion script, the old format is breaking it. It wasn't before, but it's not handling it now, which is fine honestly.

[icomrade]

@ndavalos your object_table update query results in some objects that have errors in the inventory field. Using the DB you sent me I get errors with objects with IDs 149225 149300 149301 149401 150411 150412 150958 155873 158018 158026 158034 158035 158037 159975 164253

It appears an extra ] is getting added in some instances, here's the input/output from on of the objects: in: [[["M9SD","ItemToolbox","ItemCrowbar","ItemEtool","NVGoggles","Binocular_Vector","ItemCompass","ItemHatchet_DZE","m107","SCAR_L_STD_EGLM_RCO","PK_DZ","ItemGPS"],[2,2,2,2,2,2,2,2,2,2,2,2]],[["30Rnd_556x45_Stanag","100Rnd_762x54_PK","1Rnd_HE_M203","HandGrenade_East","10Rnd_127x99_m107","ItemAntibiotic","ItemPainkiller","ItemBloodbag","ItemMorphine","ItemBandage","15Rnd_9x19_M9SD","ItemBriefcase100oz"],[10,10,10,10,10,5,10,10,10,10,10,1]],[["DZ_LargeGunBag_EP1"],[10]]] out: [[["M9SD","ItemToolbox","ItemCrowbar","ItemEtool","NVGoggles","Binocular_Vector","ItemCompass","ItemHatchet_DZE","m107","SCAR_L_STD_EGLM_RCO","PK_DZ","ItemGPS"],[2,2,2,2,2,2,2,2,2,2,2,2]],[["30Rnd_556x45_Stanag","100Rnd_762x54_PK","1Rnd_HE_M203","HandGrenade_East","10Rnd_127x99_m107","ItemAntibiotic","ItemPainkiller","ItemBloodbag","ItemMorphine","ItemBandage","15Rnd_9x19_M9SD","ItemBriefcase100oz"],[10,10,10,10,10,5,10,10,10,10,10,1]],[["DZ_LargeGunBag_EP1"]]

[ndavalos]

@icomrade it all got resolved with the character after my last comment, I took care of the bad object_data stuff few days ago, so no worries :) Thanks for looking though.

There updateGlobalCoins seems to be missing from hive altogether. Player sync is calling 205, but what's assigned to 205 in hive doesn't exist. It's causing the character's instance to get set to -1, which makes you spawn with a random location on the next login. Maybe I'm missing something somewhere, but I can't find that function outside of HiveExtApp.cpp and HiveExtApp.h

[icomrade]

do you mind posting the update object_data sql queries?

The hive 205 child is updateGlobalCoins which sends PlayerID, ServerID, PlayerCoins, and BankCoins to updatePlayerCoins, you can see the function here https://github.com/icomrade/DayZhiveEpoch/blob/master/Hive/Source/HiveLib/DataSource/SqlCharDataSource.cpp#L406-L438

[ndavalos]

@icomrade I'm just got the hive you posted 4 hours ago and I'm going to run it with a trace and look at the mysql logs. It appears everything hive is doing is setting instanceid = -1 (the version from the 4th) I'll update you shortly.

[ndavalos]

@icomrade yep, almost everything hive is doing is updating instance to -1 http://www.randomhost.org/hivelog.zip

Included is a hive trace, mysql logs and server rpt with extra diag_log action happening.

[icomrade]

If you are not using the hive to stream objects the serverID will not be set properly, since child 302 sends the ID to the hive, otherwise it's set to -1.

[ndavalos]

304 is using -1 as the server instance: Hive: 18:10:36 HiveExt(0): [Debug] Original params: |CHILD:304:216605:| 18:10:36 HiveExt(0): [Information] Method: 304 Params: 216605: 18:10:36 HiveExt(0): [Information] Result: ["PASS"] 18:10:36 Database(1): [Trace] PreparedRequest [1 ms] SQL: 'DELETE FROM Object_DATA WHERE ObjectID = ? AND Instance = ? VALUES(216605, -1)'

MySQL:= ? 10 Execute DELETE FROM Object_DATA WHERE ObjectID = 216605 AND Instance = -1

It doesn't even call hive with the server instance from server_deleteObj.sqf, so something is defaulting that or something. I'm not sure where it's going wrong. _key = format["CHILD:304:%1:",_id]; _key call server_hiveWrite; diag_log format["DELETE: Player %1 deleted object with ID: %2",_activatingPlayer,_id];

dayZ_instance appears to still have it's original value.

[ndavalos]

Ah I see what you mean.

[icomrade]

Completed, closing since no issues were reported in almost 2 weeks. Use the testing issue thread if anything is found