collapse collapse
* User Info
 
 
Welcome, Guest. Please login or register.
* Search

* Board Stats
  • stats Total Members: 989
  • stats Total Posts: 18363
  • stats Total Topics: 2500
  • stats Total Categories: 7
  • stats Total Boards: 35
  • stats Most Online: 1144

Author Topic: Members table thoughts/brainstorming  (Read 5803 times)

0 Members and 1 Guest are viewing this topic.

Offline Khalem

  • BeBot Founder
  • Administrator
  • ********
  • Posts: 1169
  • Karma: +0/-0
    • http://www.ancarim.com
Members table thoughts/brainstorming
« 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.

BeBot Founder and Fixer Kingpin

Offline Alreadythere

  • BeBot Maintainer
  • BeBot Hero
  • ******
  • Posts: 1288
  • Karma: +0/-0
Re: Members table thoughts/brainstorming
« Reply #1 on: December 15, 2005, 11:42:27 pm »
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.

Offline Khalem

  • BeBot Founder
  • Administrator
  • ********
  • Posts: 1169
  • Karma: +0/-0
    • http://www.ancarim.com
Re: Members table thoughts/brainstorming
« Reply #2 on: December 15, 2005, 11:59:17 pm »
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
BeBot Founder and Fixer Kingpin

Offline Khalem

  • BeBot Founder
  • Administrator
  • ********
  • Posts: 1169
  • Karma: +0/-0
    • http://www.ancarim.com
Re: Members table thoughts/brainstorming
« Reply #3 on: December 16, 2005, 08:49:20 am »
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.
BeBot Founder and Fixer Kingpin

Offline Alreadythere

  • BeBot Maintainer
  • BeBot Hero
  • ******
  • Posts: 1288
  • Karma: +0/-0
Re: Members table thoughts/brainstorming
« Reply #4 on: December 16, 2005, 11:15:53 am »
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.

Offline Akarah

  • Contributor
  • *******
  • Posts: 72
  • Karma: +0/-0
    • http://synergyfactor.net/
Re: Members table thoughts/brainstorming
« Reply #5 on: December 16, 2005, 06:05:27 pm »
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 ;)

Offline Wanuarmi

  • Contributor
  • *******
  • Posts: 121
  • Karma: +0/-0
Re: Members table thoughts/brainstorming
« Reply #6 on: January 23, 2006, 01:59:15 am »
Last visit to the private group could be useful aswell...

Offline Khalem

  • BeBot Founder
  • Administrator
  • ********
  • Posts: 1169
  • Karma: +0/-0
    • http://www.ancarim.com
Re: Members table thoughts/brainstorming
« Reply #7 on: January 23, 2006, 04:26:48 am »
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)
BeBot Founder and Fixer Kingpin

Offline Wanuarmi

  • Contributor
  • *******
  • Posts: 121
  • Karma: +0/-0
Re: Members table thoughts/brainstorming
« Reply #8 on: January 24, 2006, 07:31:59 pm »
I was thinking about a !lastvisit command... and one use I can think is remove members that havent visited in say 3 months.

Offline Alreadythere

  • BeBot Maintainer
  • BeBot Hero
  • ******
  • Posts: 1288
  • Karma: +0/-0
Re: Members table thoughts/brainstorming
« Reply #9 on: June 26, 2006, 11:54:17 pm »
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.

Offline Khalem

  • BeBot Founder
  • Administrator
  • ********
  • Posts: 1169
  • Karma: +0/-0
    • http://www.ancarim.com
Re: Members table thoughts/brainstorming
« Reply #10 on: June 27, 2006, 01:27:52 pm »
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]
« Last Edit: June 27, 2006, 01:30:41 pm by Khalem »
BeBot Founder and Fixer Kingpin

Offline Alreadythere

  • BeBot Maintainer
  • BeBot Hero
  • ******
  • Posts: 1288
  • Karma: +0/-0
Re: Members table thoughts/brainstorming
« Reply #11 on: June 27, 2006, 02:49:38 pm »
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).

Offline Pharexys

  • BeBot Apprentice
  • ***
  • Posts: 117
  • Karma: +0/-0
Re: Members table thoughts/brainstorming
« Reply #12 on: June 27, 2006, 06:06:21 pm »
wow that would rock, awesome, ;D

Offline Khalem

  • BeBot Founder
  • Administrator
  • ********
  • Posts: 1169
  • Karma: +0/-0
    • http://www.ancarim.com
Re: Members table thoughts/brainstorming
« Reply #13 on: June 28, 2006, 04:51:46 pm »
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.
BeBot Founder and Fixer Kingpin

Offline Pharexys

  • BeBot Apprentice
  • ***
  • Posts: 117
  • Karma: +0/-0
Re: Members table thoughts/brainstorming
« Reply #14 on: June 28, 2006, 05:47:43 pm »
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.

 

* Recent Posts
[AoC] special char for items module by bitnykk
[February 09, 2024, 09:41:18 pm]


0.8.x updates for AoC by bitnykk
[January 30, 2024, 11:16:08 pm]


0.8.x updates for AO by bitnykk
[January 30, 2024, 11:15:37 pm]


BeBot still alive & kicking ! by bitnykk
[December 17, 2023, 12:58:44 am]


Bebot and Rasberry by bitnykk
[November 29, 2023, 11:04:14 pm]

* Who's Online
  • Dot Guests: 789
  • Dot Hidden: 0
  • Dot Users: 0

There aren't any users online.
* Forum Staff
bitnykk admin bitnykk
Administrator
Khalem admin Khalem
Administrator
WeZoN gmod WeZoN
Global Moderator
SimplePortal 2.3.7 © 2008-2024, SimplePortal