SQL Structure

[Xgear 44]

1.- Rename column names to not-reserved names

On some SQL tables, some columns have names that are reserved words in some programing languages (such as time, date, int, class, etc)

I'd like to suggest renaming these common columns to something a little bit more specific without depending on reserved names, some tables already work with more specific column names (such as branch_log for example having branch_date), which would also help standarize parts of the SQL code whenever needed.

 

2.- Drop branch_log

I just went through the code quickly and noticed branch_log is not used for anything. And even if it was used, with the current schema it'd provide no more information than pick_log would. Is there a point on keeping it?

 

3.- Merge zenylog into picklog

I could see zenylog being on a separate table slightly useful, however as the logs tables are only used to search for information by the users, it could serve its duty better if it was joined in picklog, as it'd only require one one query rather than two when trying to search for movement into someone's inventory. 

 

4.- Move charlog into log. (Shooting in the dark here )

I am really shooting high here, I know. a lot of people is against making the char server set another connection to the log database when there really isnt much interaction for it. 

I havn't done my homework here in terms of studying how "expensive" resource-wise it'd be to do this, however, in terms of structure and usefulness, having the database in the main database makes the charlog not-so-useful. 

Perhaps instead of setting a new connection from the char server, make the char server send a signal back to the login server/map server (which it already does on most things) with a bit more information telling them what to log. 

 

I think I had another idea but it kind of slipped my mind right now -_-'

 

 

P.S.: Just noticed I posted this here, should probabbly be on Suggestions, so if someone could move it it'd be great. 

As a P.S.2: Shouldnt Development Discussion be Staff-Started, Staff-approved and have topics be moved here by the Staff when needed? Should be more of a discussion/feedback center than a suggestions forum (Noticed a few topics that feel more like a feature request than actual development/core feedback )

Edited June 23, 2013 by Xgear

[Gepard 55]

1. Is there any particular reason for not using C reserved words in SQL? I agree on more descriptive column names, but this should be done carefully, because many tools, like control panels, will break.

 

2. Branch log is used, see pc.c:pc_isUseitem, very end of function

	//Dead Branch & Bloody Branch & Porings Box	// FIXME: outdated, use constants or database	if( nameid == 604 || nameid == 12103 || nameid == 12109 )		logs->branch(sd);

3. I agree on that. Picklog doesn't seem to be very useful either, because usually one row is just half of the "action", for example trades consists of two rows.

I'd suggest to rework picklog so it has about double the number of columns it has now and tracks both ends of the action involved. There should be "from" part and "to" part. Each part should include: type (monster, player, npc etc), id (char_id, npc_id, mob_id), location, datetime. And there should be "item" part with all relevant item data, (zeny amount if merged with zenylog) and action type (trade, pickup, monster drop, etc). Items dropped on the floor should be logged when they disappear or are picked up, and "from" data could be saved in server's memory until it's added to sql log, or inserted immediately with just row_id to update stored in memory.

 

4. Agree, even if it's just for consistency sake. Log tables should be in log database.

Edited June 24, 2013 by Gepard

[Ind 945]

3. I agree on that. Picklog doesn't seem to be very useful either, because usually one row is just half of the "action", for example trades consists of two rows.

I'd suggest to rework picklog so it has about double the number of columns it has now and tracks both ends of the action involved. There should be "from" part and "to" part. Each part should include: type (monster, player, npc etc), id (char_id, npc_id, mob_id), location, datetime. And there should be "item" part with all relevant item data, (zeny amount if merged with zenylog) and action type (trade, pickup, monster drop, etc). Items dropped on the floor should be logged when they disappear or are picked up, and "from" data could be saved in server's memory until it's added to sql log, or inserted immediately with just row_id to update stored in memory.

I agree, wouldn't it be better if trade logs were given its own table though? since its the one case where extra columns would be most used

As a P.S.2: Shouldnt Development Discussion be Staff-Started, Staff-approved and have topics be moved here by the Staff when needed? Should be more of a discussion/feedback center than a suggestions forum (Noticed a few topics that feel more like a feature request than actual development/core feedback )

aye

[Xgear 44]

@Gepard

I figured the CPs would be brought into discussion, this might not be a good answer, however I do not believe the emulator should be built around CPs, it should be the other way around. 

As for not a good reason, a large amount of MySQL words are reserved words in most languages, the suggestion was mainly focused towards MySQL reserved words Found here). As for other programming languages, I'd say its good practice? Not to mention when having more descriptive column names you'd avoid issues with reserved words on its most.

 

As for logbranch, weird, didn't notice, it still holds on information at all that can be of any use (Looks like a poor implementation of picklog) I still think it should probably be either dropped or heavily improved, so it can serve a purpose.

 

@Ind, Wouldn't it be better to simply add another column, like target/destination? Log branches could target the mob that was spawned, loot/steal could be the mobid, Trades provide the receipt and so on. Only one I see being different would be NPC's since afaik there is no ID on them or easy way to identify them constantly (Guess IDs would change as they are loaded/reloaded). However, knowing from which NPC an item came/went to could be pretty useful at times. 

Something like that could have some consistency/rganization issues if thrown into a single table though, which could be its major drawback I guess. 

Edited June 25, 2013 by Xgear

[Emistry 144]

i like gepard idea too ... eventually it will increase the readability of the trade log ..and others....

[cJei 4]

3. I agree on that. Picklog doesn't seem to be very useful either, because usually one row is just half of the "action", for example trades consists of two rows.

I'd suggest to rework picklog so it has about double the number of columns it has now and tracks both ends of the action involved. There should be "from" part and "to" part. Each part should include: type (monster, player, npc etc), id (char_id, npc_id, mob_id), location, datetime. And there should be "item" part with all relevant item data, (zeny amount if merged with zenylog) and action type (trade, pickup, monster drop, etc). Items dropped on the floor should be logged when they disappear or are picked up, and "from" data could be saved in server's memory until it's added to sql log, or inserted immediately with just row_id to update stored in memory.

 

4. Agree, even if it's just for consistency sake. Log tables should be in log database.

 

+1 I really agree to this for easier tracking of logs :3