BeBot - An Anarchy Online and Age Of Conan chat automaton
Development => Coding and development discussion => Topic started by: Khalem on December 15, 2005, 11:25:50 pm
-
As you probably have gathered by now, i really loathe the current membertable and its roster update.
As such Ive started thinking about how to fix it.
First off, one of the issues have already been solved with the community supplied Whois cache. That takes care of both updating members information, gives a central whois function for the entire bot to use, reduces traffic aimed at funcoms servers, and allows for table sharing between bots. I'm still a bit partial to a file cache, but it would be a bit more complex, and it wouldn't really add anything notable.
Second, the members table.
As it stands today, combined with the whois cache, its wastefull duplication of data. Hence the table needs to be changed in any case. As such id like to get some feedback on what should go there.
My initial thoughts would be:
id - INT(11) - Unique auto increment
char_id - INT(11) - Foreign key with unique character ID for referencing the whois table
password - VARCHAR(64) - Allowing storing a password out of the box for use with webinterfaces and whatnot
password_salt - VARCHAR(5) - Allowing for salted passwords for increased security
last_seen - INT(11) - Timestamp for when someone was last seen logging ON
announce - INT(1) - Boolean true/false for if the user wants to receive !announce
massinvite - INT(1) - Boolean true/false for if the user wants to receive !massinv
type/status - INT(1) - Single integer denoting if the user is 1-Guest, 2-Member, extendable, allows guests table to be merged with the members table
added_by - VARCHAR(32) - Optional field for who added the member. Primarily for raidbot members and guests
notify/logon - INT(1) - Boolean true/false allow user to take himself off login notice (should not take the user off buddylist though as it would mess with last seen tracking)
Finally, the roster update needs to be rewritten.
There are two approaches to this one.
One is to periodically check the roster XML, determine if new members have been added or old members removed aside from those the bot has seen while being online. (Deleted characters for example). This one would probably be best done using md5sum comparison on old vs new roster. Although i haven't thought too much on how to do the actual comparisons yet to figure out who has been removed or who has been added
Second one is to simply don't care about the Roster XML, rely soly on the new Org. Msg. output and manual intervention to add/remove. This has the disadvantage of not picking up deleted characters as well as any changes happening if the bot happens to be offline. And if you are going to do an XML check on login, you've created the basics for the first approach in any case.
I'm sure Ive left something out in all this, but i think i got the important parts down.
Also something to consider is if the Logon comment should be moved into the members table as well as its a single entry per member always. That would make one less query on each login.
-
Some thoughts I got while reading your post:
With the current version of my whois cache you'll need a name entry in your members table too, as I have no user IDs or any other ID in the cache. With my out-of-bot update script it's actually impossible to add that, as the IDs are only available ingame (at least I haven't found them anywhere around the XML files).
Personally I'd add the logon messeges to the members table, there is no sense to put it into it's own table.
On the members table - the best way would be to do a daily roster update like now, and use the new join/leave notifications to update it over the runtime. If you don't do the roster update during FCs database update it won't lag the bot for long.
-
Hrm on the Whois cache.
Im actually contemplating on keeping the updating completely inside the bot.
It does give a tad more overhead, but it does allow all important information to be keept in the table. And due to the last updated field there should be no excessive traffic compared to the out of bot upgrader.
The only loss i see is the ability to schedule a full update run around the time you KNOW funcoms database will have been updated.
That could be worked around too of course, but i generally think handeling it in bot can be just aswell. Were not talking anything mission critical here in any case and we'd still have the cache to fall back on should funcom be slow or give no proper reply.
The in bot solution would also generate less traffic as only those entries that need to be updated are updated. In addition, a last access time should be used to allow the bot to expire stale whois cache entries that havent been access in say 3 months.
You do loose some small advantages of the current out of bot system, but you get to keep the major advantages.
As for the logon notice.. yeah, thats my thoughts too. In addition the module should probably be merged with the Roster_Guild anyways
-
Some additional thoughts on whois cache.
It might not be a problem anyways. The first addition of an entry to the whois cache table will be done by the bot. The out of bot script merely updates, hence there should be no problems whatsoever. Bot has character id there, outside bot script doesnt need it as it works soly from names.
-
My current script reads org roster first, to reduce overall load. So if one org member is in the cache, on the next update the whole org will be added.
Which would clash with the UIDs.
On the other hand, all members and guests of the bot need to be added by the bot anyways, so while doing that the bot could add the UIDs where needed in the cache.
-
yup, also i'd like to see less DELETE/INSERT and more UPDATEs in general.. as long as we are "update"ing the entries in the "whois" table, we should be able to add another column for charid without an issue..
i forget where i saw the delete/insert used but i think i might have used it myself somewhere.. need more coffee obviously i'm not making much sense ;)
-
Last visit to the private group could be useful aswell...
-
Last visit to the private group could be useful aswell...
I was thinking along the lines of:
if guildbot, logging on is last seen time, else last time joined privgroup. Im not sure a non guild bot offhand has any use for knowing the last time someone was online (but not in bot)
-
I was thinking about a !lastvisit command... and one use I can think is remove members that havent visited in say 3 months.
-
Been a while since this threat, but got a slight update:
To solve the problem of updates of the memberlist by the org roster available as xml and possible joins/leaves after the last update I added a table to track those changes.
In the table the I got character name, last change (join/leave/kick) and timestamp of last change.
After running the update using the xml data I parse that table, add people that joined but aren't on the xml list, and remove all that left or got kicked. To handle possible delays in the update of the xml data I use the changes of the last seven days for this modification.
Works really well.
-
Ok, last chance to comment on this one.
This is what i have now, although im sure ive managed to leave something out:
- id INT NOT NULL PRIMARY KEY, - Not sure we really need a separate id, but... oh well
- char_id INT NOT NULL, - Character id
- nickname VARCHAR(25), - Nickname, again, not sure we really need this here given we will be using whois table for character related lookups anyways, however it does simplify things a bit in some cases since we often lookup by nickname instead of char_id.
- password VARCHAR(64), - Not used by the bot really, but allows web interfaces etc to be done easilly
- password_salt VARCHAR(5), - And allow for password salts
- last_seen INT(11), Last seen online/pgroup
- last_raid INT(11), Last seen joining a raid
- recieve_announce INT(1) DEFAULT '1', - Recieve !announce
- recieve_invite INT(1) DEFAULT '1', - Recieve !massinv
- auto_invite INT(1), - Auto invite
- added_by VARCHAR(25), - Added to bot by
- added_at INT(11), - Added to bot at
- notify INT(1), - User is on buddylist/logon notice enabled
- user_level INT(1), - User is -1 banned, 0 not a member, 1 guest, 2 member, 3 admin
- admin_level INT(1) DEFAULT '0', - User admin level (old admin table info)
- alt VARCHAR(25), - User is an alt of x (old alts table info)
- logon VARCHAR(255), - User logon (old logon table info)
Whats the thoughts about also merging in the raid points table to the main users table?[/list]
-
Seeing all the info added into this list, go ahead and add the raid points too.
When working with them just make sure you always use the main for point modification (in case the old points_to_main is enabled).
-
wow that would rock, awesome, ;D
-
After some more thought, im going to keep the alts and raids table separate for now.
Merging the alts table would lead to having to store too many non members in the users table for my liking.
-
After some more thought, im going to keep the alts and raids table separate for now.
Merging the alts table would lead to having to store too many non members in the users table for my liking.
That is true, and i atm use same alts table for 3 bots.
-
The first version of the changes just hit SVN.
Only guild roster works right now.
Guest adding/removing has not been updated yet either.
Current version of the new users table (yes i know about the lack of indexes):
$db -> query("CREATE TABLE IF NOT EXISTS users
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
char_id INT NOT NULL UNIQUE,
nickname VARCHAR(25) UNIQUE,
password VARCHAR(64),
password_salt VARCHAR(5),
last_seen INT(11) DEFAULT '0',
last_raid INT(11) DEFAULT '0',
recieve_announce INT(1) DEFAULT '1',
recieve_invite INT(1) DEFAULT '1',
auto_invite INT(1) DEFAULT '0',
added_by VARCHAR(25),
added_at INT(11) DEFAULT '0',
deleted_by VARCHAR(25),
deleted_at INT(11),
banned_by VARCHAR(25),
banned_at INT(11),
notify INT(1) DEFAULT '0',
user_level INT(1) DEFAULT '0',
admin_level INT(1) DEFAULT '0',
updated_at INT(11) DEFAULT '0')");
-
I know I'm late to the game here, but for the members table:
- nickname VARCHAR(25) PRIMARY KEY, - Nickname, again, not sure we really need this here given we will be using whois table for character related lookups anyways, however it does simplify things a bit in some cases since we often lookup by nickname instead of char_id.
- password VARCHAR(64), - Not used by the bot really, but allows web interfaces etc to be done easilly
- password_salt VARCHAR(5), - And allow for password salts
- last_seen INT(11), Last seen online/pgroup
- account_num INT, Raid Point/Alt Account
- recieve_announce INT(1) DEFAULT '1', - Recieve !announce
- recieve_invite INT(1) DEFAULT '1', - Recieve !massinv
- auto_invite INT(1), - Auto invite
- added_by VARCHAR(25), - Added to bot by
- added_at INT(11), - Added to bot at
- notify INT(1), - User is on buddylist/logon notice enabled
- user_level INT(1), - User is -1 banned, 0 not a member, 1 guest, 2 member, 3 admin
- admin_level INT(1) DEFAULT '0', - User admin level (old admin table info)
- logon VARCHAR(255), - User logon (old logon table info)
- in_pgroup TINYINIT - is the user in the private group.
Numeric Character ID vs nickname vs autonumber ID:
Both the Character ID (provided by FunCom) and a nickname are unique per dimension, thus both values make fine primary keys. In my opinion, the numeric character id is redundant data. I haven't found a need for the numeric ID over the character Nickname, so I'd drop it. An autonumber ID is also not needed, as FunCom and provided us with mutiple options for a primary key already.
Raid Point/Alt Account Number:
Instead of tracking raid points and alts seperatly, how about assigning each user an account number. (A user being defined as a actual person who may have one to invinity characters) When alts are added, the account number is updated to the account number of the main character. So instead of a raid_points table, you would have a user_accounts table which holds attributes common to a group of characters (main+alts)
I've moved last_raid to this table as I think it should be a value common to the user, not the character.
- id int, primary key, auto_increment
- raid_points int
- raiding tinyinit
- main_nickname char
- last_raid INT(11), Last seen joining a raid
To get a listing of alts, you would simply do:
SELECT Nickname FROM MEMBERS WHERE account_num = X;
in_pgroup:
In Omnicom, we've extended our member table to include in_pgroup, which indicates who is in the bot's channel, since we were unable to track online with the buddy list due to the 1,000 character limit. There is probally a better way of doing this, but that's what got hacked in to solve an ongoing problem.
last seen
I was thinking along the lines of:
if guildbot, logging on is last seen time, else last time joined privgroup. Im not sure a non guild bot offhand has any use for knowing the last time someone was online (but not in bot)
Raid bot definitly has use. IE: if the user hasn't joined in X days, remove them from the bot's buddy list. Again, this might be something that should be tied into the raid account, because the member might be active, but they were raiding on an alt. As I stated above, I assoicate last_raid with the user account, not the character.
On Whois:
I'd like it if the whois table names match the keys in the XML. On the version of whois chace I'm running, character names are stored in the name column, not nickname.
The important whois fields are:
firstname - First (RP) name
nickname - Character name (IE: Glarawyn)
lastname - Last (RP) name
rank - org rank.
rank_name - name of org rank.
level - Level (1-220)
profession
gender
breed
defender_rank - Name of Defender Rank (AI Title)
defender_rank_id - Number of Devender Rank (AI Level #)
Due to the different org structures, rank isn't really a useful value to store. rank_name is the more useful value. Since org government type isn't available in the XML data, rank has no context at all and is useless data.
defender_rank seems redundant as well, as it should be a simple manner to translate defender_rank_id (aka AI Level) to defender_rank as it's a 1:1 relationship. ie: a defender rank id of 29 will alwyas be a defender_rank of Vanquisher. A core function could do the translation if needed, but on the other hand there is also value to having it in the whois database.
Hope that's helpful. :)
-
I thought about this some more over lunch, and in truth we can't depend on unique names or IDs from FunCom. In the event of character wipes or character deletions, a name can be assigned a new numeric ID from FunCom. For the most part, it should all work out fine, but there will be that rare exception...
-
The issues with CharacterID and Nickname not being truly unique is one of the main reasons i opted to go with a separate ID field (not to mention it makes for easier handling in a web app im working on)
in_pgroup will be part of the upcoming Online table.
account_num. Im a bit worn out atm and not thinking straight. But this would probably make sense to have in the members table.
As for the whois cache, funcom have me torn a bit because they use "name" for the single character and "nickname" in org listings.
As such i tossed a coin on what made the most sense, and imho nickname is a lot clearer than name, not to mention involved altering less code breaking the least possible.
Including the rank_id and defender_rank_id, the information is provided by the XML, and as such it should be present in the whois cache.
Why add a function to the bot to do what is already done for us, it doesn't make sense :)
-
As for the whois cache, funcom have me torn a bit because they use "name" for the single character and "nickname" in org listings.
As such i tossed a coin on what made the most sense, and imho nickname is a lot clearer than name, not to mention involved altering less code breaking the least possible.
I never noticed that...thanks FC. ::) I'm with you on nickname, it is clearer than name.
Including the rank_id and defender_rank_id, the information is provided by the XML, and as such it should be present in the whois cache.
Why add a function to the bot to do what is already done for us, it doesn't make sense :)
Well technically it's redundant, but yeah it's in the FunCom XML and nobody is going to be dealing with millions or billions of records so the minor redundancy doesn't matter much. A huge enterprise database would probally cut the character data, keep the integers, and leave it for the front end app developers to make pretty. :)