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.

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

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.

Last visit to the private group could be useful aswell...

I was thinking about a !lastvisit command... and one use I can think is remove members that havent visited in say 3 months.

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]

wow that would rock, awesome,

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.