Channel stats and MySQL

pelefanten · Post by **pelefanten** » Sat Apr 19, 2003 6:19 pm

I'm making a module that is pritty much like stats.mod but uses MySQL. However, I'd like to see if it works "for real". If your intrested take a look at http://york.gose.org/~cau/ .

Any feedback is welcome.

Galadhrim · Post by **Galadhrim** » Sun Apr 20, 2003 8:50 am

It might be good to look for threads with the same subject. EvilMonkey has posted another thread, also about MySQL, thursday. This way the forum is getting slow...

ppslim · Post by **ppslim** » Sun Apr 20, 2003 9:19 am

The whole idea of a seperating a thread, is because it is on a different subject.

If we where to follow your scheme, every post should be made under one long subject/thread, seeign as they are all about eggdrop.

The forum is not slow. You may consider a traceroute to the forum server, just to see where your bottlenecks are taking place.

EEggy · Post by **EEggy** » Sun Apr 20, 2003 10:55 am

Hi pelefanten,

i had sent ya an email, it was returned(host unkown), could you please give me your correct email addy?

do i need to install mysql server?? ...how do i check if its already on the shell??

thanks

pelefanten · Post by **pelefanten** » Sun Apr 20, 2003 3:20 pm

Sorry about the emailhost, just registered the domain and i should get it any day now... Until then I can be reached at pelefanten@linux.se

The makefile uses mysql_config to link to the mysql-libs, you should be able to do:
$ mysql_config --libs --cflags

EEggy · Post by **EEggy** » Sun Apr 20, 2003 3:57 pm

Thanks for the reply, but my shell provider doesn't support MYSQL, so don't know what to do?

thanks

ppslim · Post by **ppslim** » Sun Apr 20, 2003 4:13 pm

If the shell provider does not support MySQL, then there is nothing that you can do.

Unless you are able to use a external MySQL server, which isn't wise, due to the ammount of traffic over such a unstable link, then you should look at the other stats projects available.

Rob · Post by **Rob** » Mon Apr 21, 2003 3:31 pm

This is an excellent module, it works so far with my eggdrop and ive started work on making a custom php pages of my own.

Nice one.

Rob · Post by **Rob** » Mon Apr 21, 2003 8:24 pm

Got the php side of it done, what about implanting some more information such as whos opped, voiced, amount of bans, chan modes and total user count or things along that line.

pelefanten · Post by **pelefanten** » Tue Apr 22, 2003 7:03 pm

That is a great idea. I'm currently working on a userfile for the module, so it wont need the eggdrop userfile. I will probably look at those functionalities in a later version...

ppslim · Post by **ppslim** » Wed Apr 23, 2003 4:26 am

The tables are not very generic.

I can fully understand the prefix given for a table, and would suspect that using the bots handle as default (when blank or not set) would do.

On top, why is it one table per channel. This is a bit overkill.

At this moment, the userlist is based in eggdrops internal userlist. Considering a userbase is not gonna go above 300 (usualy), this amount of records should be fine.

Even 5 300 users channels (once a new user system is in place) would be handled with ease. 1500 records in one table. So long as data is optimised, you don't even need to index the records as only the selects reap the real benifits.

Is the userfile gonna be files or SQL based?

If file, thats fine, but why stray away from SQL, when there are benefits.

SQL will allow for a single table, with no prefix, to be used accross multiple stats collecting bots. This will allow 3 bots that cover (eg) 15 channels, that are not in the same two channels (IE, all in unique channels) to collect data for each user, while making sure that if userA in stats tableA is the same as userA in stats tableB.

I understand this will mean user records can become out of sync. If you use a HOOK_5MINUTLEY leaving the bot out of sync for a possible 5 mins, ot HOOK_MINUTLEY if you are that scared.

A select stament of "WHERE record_change > X", where X is a single record stored in the bot, and not against each user, and will store the time that the HOOK was last called.

While all this breaks the conventions of your allready existing system, tightening it up now doesn't break things. As you noted, it's beta.

pelefanten · Post by **pelefanten** » Wed Apr 23, 2003 9:55 am

I'm doing a SQL based user file, the users are still stored in one table w/ their stats and a new table is needed to stor all hostmasks. The reason I'm doing a set of tables per channel is simply i couldnt come up w/ another solution (and still keep a primary key), but if any of you out there has ideas i'd be glad if you shared them w/ me.

It's a very intresting point you make that several bots could use the same userfile. I can see how you think w/ one table for several channels used by more than one bot, but I still think you will end up with atleast two onther tables, one for the channels and another one for hostmasks. However, it might be better than 4tables / channel w/ independent userfiles as I'm doing it now.

ppslim · Post by **ppslim** » Wed Apr 23, 2003 11:11 am

THis looks like it can be accomplished with 4 tables.

1: Contains the handle/nickname/primary name of the user. It also contains a ID code.

2: Contains the channel names with a ID code.

3: Stores all stats. You do a update or insert based on the presance. You update using the ID number of the user and the ID number of the chan.

4: Stores all hostmasks against the IS of the user.

Why like this. Numbers are easier to search than text. Hostmasks are a consuming item, and should be the only text lookup needed.

The only issue with all this. You need to do a bit more of you won tracking in the module (which will hve had to be done for speed anyhow).

pelefanten · Post by **pelefanten** » Wed Apr 23, 2003 1:57 pm

Werry intresting design.

About hostmask lookup I belive there is no "reversed" wildcard SELECT ... LIKE method, so the only way is to SELECT all and go through untill you find a matching host, every time (someone joins the channel)

This doesnt sound like a very efficient method to me though, is there by chanse any other way to do host lookup?

ppslim · Post by **ppslim** » Thu Apr 24, 2003 4:41 am

You are quite correct, but it isn't as in-efficient as you believe.

Taking eggdrops userfile for example. If you have 300 users, each with 3 hostmasks. You have to check 900 hosts, with the risk of not finding it.

When using a SQL method, results can be pre-filtered. Filter the results down to the ISP level, and you reduce the load required to calculate a match in eggdrop.

SELECT uid, hostm FROM statsmod_userhosts WHERE hostm LIKE "%!%@%.isp.com";

I don't have time to think it through properly (I have allready reworded this post 6 times). Head spinning, belly churning, need sleep.