Jump to content

Picklog


Feilor

Recommended Posts


  • Group:  Members
  • Topic Count:  37
  • Topics Per Day:  0.01
  • Content Count:  135
  • Reputation:   17
  • Joined:  12/31/11
  • Last Seen:  

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.

Link to comment
Share on other sites


  • Group:  Members
  • Topic Count:  16
  • Topics Per Day:  0.00
  • Content Count:  657
  • Reputation:   662
  • Joined:  11/12/12
  • Last Seen:  

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

Link to comment
Share on other sites


  • Group:  Members
  • Topic Count:  37
  • Topics Per Day:  0.01
  • Content Count:  135
  • Reputation:   17
  • Joined:  12/31/11
  • Last Seen:  

@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

Link to comment
Share on other sites


  • Group:  Members
  • Topic Count:  16
  • Topics Per Day:  0.00
  • Content Count:  657
  • Reputation:   662
  • Joined:  11/12/12
  • Last Seen:  

You can use:

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

(The table could use some updates though.)

  • Upvote 1
Link to comment
Share on other sites


  • Group:  Members
  • Topic Count:  37
  • Topics Per Day:  0.01
  • Content Count:  135
  • Reputation:   17
  • Joined:  12/31/11
  • Last Seen:  

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

Link to comment
Share on other sites


  • Group:  Members
  • Topic Count:  16
  • Topics Per Day:  0.00
  • Content Count:  657
  • Reputation:   662
  • Joined:  11/12/12
  • Last Seen:  

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.)

Link to comment
Share on other sites


  • Group:  Members
  • Topic Count:  37
  • Topics Per Day:  0.01
  • Content Count:  135
  • Reputation:   17
  • Joined:  12/31/11
  • Last Seen:  

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...

Link to comment
Share on other sites


  • Group:  Members
  • Topic Count:  12
  • Topics Per Day:  0.00
  • Content Count:  88
  • Reputation:   23
  • Joined:  01/30/12
  • Last Seen:  

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 by Digos
  • Upvote 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...