Statistical Database Standard

[NotRabidWombat]

Any other comments or suggestions?

Otherwise I'll just implement it.

-NotRabidWombat

[Skrollster]

Nothing else but the ip-range ban thing that we have discussed...

[[NL]Pur]

Maby add a table with some info about the Hub itself

Once this standard has reached a mature status, I propose we submit it to other hub softwares. I was also going to suggest submitting to dcstats, but they appear to be shut down.

dcstats has new site: http://www.directconnect.se/
but since it's all swedish language there :( it is a very active board. Looks nice but i don't understand a word of it.

[Skrollster]

I can post it there... i'm quite active there as well as here...

[NotRabidWombat]

- I can post it there... i'm quite active there as well as here...

Let's wait just a little while longer.

- Maby add a table with some info about the Hub itself

Good idea. Will do.

- ip-range ban thing that we have discussed

Not sure how we would integrate this other than parsing the ban file at a specific interval. There is no way to get this event other than integrating with the hub software. We might as well wait until some hub softwares are ready and willing to include the database.

- Also, this would require hub owners to install mysql or else get a webspace somewhere else?

Eventually, you will be able to use any database software to meet these requirements. Typically, MySQL will be the best option because it is free and it is what is currently being used for development.

- Would it be possible to post some sort of requirements to get this up and running after its complete?

Database software.
DC Hub Software  *
-OR-
DC Client Software *

* - with scripting that supports database connections (lua 5, VB, etc)

That would be the minimum requirements. Obviously people will want to add in web interface (not through the hub), client with scripting, etc.

We've reached a bit of a gray area. We probably should create two seperate databases: one for runtime hub information, one for logging history. The history (users, chat, searches) can be generated from either hub software or client. The runtime can ONLY be generated by the hub.
As of now, we nearly have the history database complete. I am going to implement TWO methods of generating the data: one through PtokaX and one through BCDC. This should allow us to test on many diferent hubs.

-NotRabidWombat

[Skrollster]

sounds great...

[NotRabidWombat]

There is going to be a short delay of implementation.

Current Database Roadmap:

1) Create working BCDC++ interface to database. Goal enviroment is a computer on the same LAN as the hub computer.
2) Reimplement BCDC++ interface in Windows console application. This will supplement PtokaX and other hub software until database integration is possible.
2a) Create a hub script to interface to database. Again, I am aiming towards lua.

I am stuck on 1. I have my own version of BCDC++ that I can implement and test mysql on. However, I do not know when this support will be officially released. I do not intend to release my own version. This would be a conflict of interest with the developers because they have already implemented the neccessary changes.

In the meantime, I am making php scripts to create pretty charts and graphs. I have also discovered a nice ASP.NET charts and graphs tool (ya C#!).

If anyone comes up with more suggestions, questions, or complaints, post away.

-NotRabidWombat

[Skrollster]

Originally posted by NotRabidWombat
I am stuck on 1. I have my own version of BCDC++ that I can implement and test mysql on. However, I do not know when this support will be officially released. I do not intend to release my own version. This would be a conflict of interest with the developers because they have already implemented the neccessary changes.

You couldn't you make your script public, since i and probably a bunch more have, as you, compiled our own client witch support lua 5.0.2 and the other things needed..

[NotRabidWombat]

Ok. I will make the script public once I get rid of the bugs. Right now BCDC crashes with FURY ;-)

-NotRabidWombat

[Gnug615]

Hi NotRabidWombat,

This is a nice idea.  I've got a few thoughts regarding the data model and structure of the tables I'd like to get to you before you progress too far, but don't have time to detail them out right at the moment.

I'll try to post them here in the next day or two.

Take care,

Gnug615

[NotRabidWombat]

Gnug! Sure thing :-) I'm really busy trying to mod BCDC++ so go ahead and take your time.

-NotRabidWombat

[Skrollster]

Haven't read through w3c-logformat standard yet.. but someone mensioned it to me then i discussed the database standard...

http://www.w3.org/TR/WD-logfile

we maybe should consider w3c-logformat then we construct this standard...

just a thought.. NotRabidWombat I'll let you decide...

[NotRabidWombat]

Logging could be done through a hub software using a standard form. That would also be VERY useful to establish between all hubs. However, acting on log files in realtime applcations (web interface, scripts, etc) isn't very efficient.

Also, the log file would probably be every packet that the hub soft sends and receives. This is a different collection of data than the database.

Maybe whoever mentioned it made some good points to use a log to database method. Could you post them?

-NotRabidWombat

[NotRabidWombat]

BUSY.

In my spare time I'm still having difficulties correctly adding loadlib support to BCDC++.

Outlook: Grim, until I am finished with school.

-NotRabidWombat

[Skrollster]

been thinkin a bit about the ip-history...

IP    INT   NOT NULL,

how do we do a ip-range search?

i'd like to maybe split the ip and save it in 4 diffrent columns... to be able to search for 213.114.* (WHERE IP1 == 213 AND IP2 == 114) don't know about this for sure, hope you got a good solution rabidwombat

[NotRabidWombat]

In your example, the current solution would be:

(213 << 24 + 114 << 16) = 3581018112
(213 << 24 + 115 << 16 - 1) = 3581083647

(WHERE IP >= 3581018112 AND IP <= 3581083647)

However, working like this becomes tricky when you want something like:
213.114.*.5

I'm not sure why someone would want that particular range.

Changing the IP to four 8 bytes values makes searching for a single address a pain though.

I will think about the matter more. Any comments are welcome.

-NotRabidWombat

[[NL]Pur]

However, working like this becomes tricky when you want something like:
213.114.*.5

i can't see a reason for that,


computing it first to big numbers normally work good :)



What's the status on the bcdc thing? Are you going soon throw it out of the window?

[NotRabidWombat]

I was waiting for someone to present a counter argument to my statement.

The status is idle. I am currently in the middle of finals and a full time job (part of an internship program at school). I finish everything on May 6, so I will begin work then.

Sorry for the delay.

I believe Skrollster is working to get another hubsoft to meet the standard.

-NotRabidWombat

[NotRabidWombat]

My appologies for the VERY long delay. Now that I have finished my finals and several other projects, I have found some time to give BCDC++ the lovin' it so deserves. I now have a fully functional BCDC++ that can load binary lua modules. The MySQL of LuaSql works perfectly and I began implementing the script to fill the database tonight.

As expected, I did discover many problems that went overlooked. We can discuss them later (I am tired :D ).

I would like to share my creation with other developers but need someone to host it for me. The first copy is about 6.5 mb since it was compiled as debug ( I want to make sure I didn't break anything  ;) ). The next one will be much smaller. PM me if you have some webspace and preferably an ftp to submit it through.

Also, could someone contact the developers? I would like to keep them informed about the changes and see if they would like to implement them into their main source.

Again, sorry for the long wait.

-NotRabidWombat

P.S. - The actual development of the script will go into another thread. Same goes for new version of BCDC++.

[NotRabidWombat]

This is the current Pseudo Code for the SQL Queries.

//////////////////////////////////////////////////////////////////////////
Event: New User Connected
Arguments: sNickName, iIP, tMyINFO, ...
Min Query Count: 3
Max Query Count: 7
Comments:
 - RabidWombat - Change "INSERT INTO users..." query to return the
	autoincrement. 
 - RabidWombat - Someone should do an analysis of DC Users to find out:
	1) How often does IP/Nick get reused? - My guess is VERY often
	2) How often does DC++ tag get reused? - I'm not so sure about
		this one. Specially because of the HUGE query.

SELECT UserID FROM users WHERE NickName='$sNickName' AND IP=$iIP;

if not $UserID returned
	INSERT INTO users(NickName, IP) VALUES ('$sNickName',$iIP);
	SELECT UserID FROM users WHERE NickName='$sNickName' AND IP=$iIP;
end

if user has a tag 
	SELECT TagInfoID FROM tag_info WHERE Version=$tMyINFO.Version AND 
		Slots=$tMyINFO.Slots AND Connection='$tMyINFO.Connection' AND
		Hub_Client=$tMyINFO.Hub_Client AND Hub_Reg=$tMyINFO.Hub_Reg
		AND Hub_Op=$tMyINFO.Hub_Op AND Cap=$tMyINFO.Cap;

	if not $TagInfo returned
		INSERT INTO TagInfoID(...;
		SELECT TagInfoID FROM tag_info WHERE ...;
	end
end

INSERT INTO current_users VALUES ( $UserID, ...

//////////////////////////////////////////////////////////////////////////
Event: User Disconnected
Arguments: sNickName, iIP, ...
Min Query Count: 1
Max Query Count: 3
Comments:
 - RabidWombat - Might want to add a check in current_users for the 
	following scenario:
	(BCDC++ is being used to add data to the database)
	1) BCDC++ client connects to hub, recieves nicklist and begins to
		add clients to the database.
	2) Before ClientA disconnects before sending $Hello & $MyINFO, so
		BCDC++ clients gets $Quit before adding to current_users

SELECT UserID FROM users WHERE NickName='$sNickName' AND IP=$iIP;

if $UserID returned
	INSERT INTO past_users VALUES ( $UserID, ....
	DELETE FROM current_users WHERE UserID=$UserID;
end

//////////////////////////////////////////////////////////////////////////
Event: Search
Arguments: sNickName, iIP, sSearchString, ...
Min Query Count: 1
Max Query Count: 3
Comments: 

SELECT UserID FROM user WHERE NickName='$sNickName' AND IP=$iIP;

if $UserID returned
	SELECT Count FROM searches WHERE UserID=$UserID AND 
		SearchString='$sSearchString';
	if $Count returned
		$Count = $Count + 1;
		UPDATE searches SET Count=$Count WHERE UserID=$UserID
			AND SearchString='$sSearchString';
	else
		INSERT INTO searches VALUES (...
	end
end

//////////////////////////////////////////////////////////////////////////
Event: Chat
Arguments: sNickName, iIP, sChat, ...
Min Query Count: 1
Max Query Count: 2
Comments: 

SELECT UserID FROM user WHERE NickName='$sNickName' AND IP=$iIP;

if $UserID returned
	INSERT INTO chat(UserID, ChatText, Time) VALUES ($UserID, ...
end

I am very displeased with the number of queries that must be performed. I am very interested whether the steps taken to save space in the database (table users and table tag_info) are worth the effort.

Any thoughts or comments while I ponder the situation?

-NotRabidWombat

[NotRabidWombat]

Big thanks to Corayzon for hosting this for me.

This version of BCDC++ supports loadlib.

Download

Changelog:
 - Lua library is now a shared dll.
 - LuaSql ODBC library has been removed
 - LuaSql MySql dll has been added with script demonstrating how to load dlls through lua.
 - Lua Library and proper header files are included to compile your own lua binary modules see: CreatingBinaryExtensionModules. NOTE: The Lua API (lua_dofile, lua_dobuffer, and lua_dostring) are NOT included in the lua lib or dll.

-NotRabidWombat

P.S. - This is simply a notification to help others who would like to develop the standard. Please reply with question and comments in the second thread I started.

[chill]

if someone had some time, and could show how to set up a mySQL DB, I would really appreciate it, just show me something simple like how to add soem items and query them. Well I got it installed and stuff.But don't know if its working.

got the db from here

http://dev.mysql.com/get/Downloads/MySQL-3.23/mysql-3.23.58-win.zip/from/ftp://netmirror.org/mysql.com/

maybe someone can install it also and show the steps to a database.

[kbeh273300]

you need a server like apache or something like that and a thing called phpmyadmin witch runs out of your server directory

http://www.phpmyadmin.net/home_page/

to download


but also this tool is wrighten in php so you will also need that .http://www.php.net/


now you can save alot of time by useing this >>http://www.easyphp.org/   this will install everything you will need.  also this in the help page for it>>>
http://www.canowhoopass.com/guides/easyphp/

hope it helps :)

[chill]

hmm.. well you help to confuse me, lol

why is there mysql for xp if you need a apache.

and there is nothing where I can open a command promt, and enter some stuff and get out some stuff from the db, sorry for the questions but this is totally new for me, and I already sorta don't like it, first you need this then that...

I know why I like LUA, you only need one binary :).

[kbeh273300]

well what you can do is go to were it is installed and look in the folder named data

it is most likly here c:\mysql\data

once inside the data folder just make a new folder called whatever you want the name of the db to be.as far as entering tables and such i only know how to use it through phpmyadmin maybe someone else knows how.