Picklog

[Feilor]

Hello rA community!
I want to propose a change in picklog db... why? It's simple, the picklog db only saves the char_id, if the player delete it we lose any reference and we can't find the account, therefore losing the power to find the account that wasn't suppoused to be.
My suggestion is to insert account_id on it or create a table to log when the the char gets deleted.

[Tokei]

That would be charlog on your main SQL database. Or your map-server logs (assuming you log the servers' console outputs).

[Feilor]

@Tokei i didn't knew about this table, thought all logs table was in log file, anyway... I take a look on it and this is a weird way to log things, i mean... if this is a char log, why there doesn't has a char_id? When we delete a char we has this message "Deleted char (CID 150368)", if we want to make things directly in querys, just can't because this is a varchar... If the emulator provide a better and clean log system we couldn't has any problem to find or make systems with those dbs

[Tokei]

You can use:

select * from `charlog` where `char_msg` like '%150368%'

(The table could use some updates though.)

[Feilor]

are you saying to use this if i want a better query:

select * from login l
JOIN acc_reg_num an ON an.account_id = l.account_id
JOIN `char` c ON c.account_id = l.account_id
JOIN picklog pl ON pl.char_id = c.char_id
JOIN charlog cl ON cl.account_id = l.account_id
WHERE c.char_id IN (SELET GROUP_CONCAT(',',pl.char_id) FROM pl WHERE cl.message like %ID%)

this is a bad example that i made, in this example if i want to use the ID to join and get character name and account_id i need to make alot of workarouns (i didn't the one that will split CID from char_id but i hope you get my point)
I'm not a expertise in SQL and this is a example that i made to apply what you said

[Tokei]

I'm a bit confused as to what you're trying to achieve. From my understanding, you wanted to retrieve the account ID from a deleted character ID, to which the following did the work:

select `account_id` from `charlog` where `char_msg` like 'Deleted char (CID 1369541%'

But now you're joining tables and I don't really see why you would want to do that. (Side note, joining the picklog table is a death wish, you should never do that.)

[Feilor]

The mainly thing here is to get all characters ids from account X even the deleted ones and join with pick to see how many item Y the account got, in my case i save the mac addres and hardware id so i want to get all characters that has the same mac address/hardware id

but the point here is... if we have a account_id in pick log, this work would be easier since we can skip some joins, plus, if we want to display logs to players in fluxcp it would be nice as well since less joins more performance...

[Digos]

This change proposed by Feilor should increase the performance in all queries related to items stolen since will not be necessary lookup the charlog table with a 'like' clause (it's so more slow than a query with =).

I would suggest also add a guild_id field in picklog since we already have an enum with a value that means the item was moved to the guild storage but currently is very hard to find out what guild was.

If the changes are made should be considered that currently the picklog table can be quite big in running servers so everyone must be careful on 'alter table' queries that will take much time to finish.
 

Edited February 5, 2018 by Digos