Overview
I have recently begun work on a lua script to add statistical hub information to a database. The purposes of this database are:
1) An efficient method of storing live hub data to be retrieved through multiple mediums.
2) Assist in optimizing the potential of PtokaX by removing the need of a built-in web interface.
3) Shift the tendency of logging hub activity through text files.
4) Demonstrate a clear need for either Lua 5.0 OR the Lua 4.0.1 load module patch. See here (http://lua-users.org/wiki/LuaBinaryModules). This will give PtokaX database support.
Discussion
A database is an excellent solution to managing hub data. They offer quick data searching and access (depending on your settings) along with efficient storage. Maintaining tables of users, searches, or other information in memory can clog the system and impair the hub itself. Just as well, writing linear text logs can result in massive files with no basic organization (ie: long search times and no easy way to delete entries).
I propose we develop and maintain a standard database structure.
The structure would, of course, be applicable to ALL hubs since they represent they same data. This suggests the removal of this feature from hub specific code to permit its own evolution. Shifting database support away from a built-in feature can also gives hub owners my control (enabling, disabling, fine-tuning). The database server could be another computer that also handles the web interface (rather than the web templates of PtokaX).
The structure would also allow for modular (and interchangeable parts). Assuming that the database meets the standard, there are countless ways of interacting with the data.
The stucture would also provide an effective means for other scripts to use for logging information. This will assist in generating standardized functions for setting and getting this information.
Example:
I am currently working on a php interface to display user stats in graphical form with the jpGraph library. Once I have finished this project, my php functions that create the images could be used with any other database that follows the standard.
Another individual could develop a web administration interface or log analysis. Problems such as removing old user accounts or cleaning log data can be automated. The possibilities are boundless.
Development
I would like to begin this thread as an open discussion of the development. The discussion should be strictly limited to the database standard. Lua implementation or any other module used for accessing the database should NOT be discussed (these should be separate threads that note that they adhere to this standard). I am looking for ideas, comments, and recommendations to improve efficiency and serve the needs of the general hub owner.
As of now, I am a self-appointed mediator (short of the moderators). I foresee other interested and dedicated individuals claiming the leadership when I can no longer support it. For now, I am all you have :P
Standard
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.
-NotRabidWombat
isn't hub time or time of the last visitor needed for cleaning the database??
@ least this is what i use on a.i.
sure love this idea, and ptax should 2.
in my eyes it's easyer 2 change 2 the patched 4.01 or even better 5.0 lua then 2 make a whole new datatbase himself.
more later, just waking up here.
plop
@skrollster: i agree with wombat, could quote rockford fosgale here "when 2 much is just enough".
it looks nice 2 have sutch a history but beside that it's totaly useless.
about the cleaning: a.i. uses the seen table 2 clean out users who haven't been in the hub for x weeks, so the database doesn't grow 2 the size of yours.
2 give you an idea, just before i gave odin (my beta tester) the 1st version of a.i. which had this cleaner i asked him 2 send me his database.
then it had over 10.000 users, now with the cleaner it's just around 2.000.
@all: why have different fields for email/tag/description??
how much do you use those things??
a.i. only stores the full infostring as it's send 2 the hub, so it's as light as posible on saving/updating.
in my eyes it's more important 2 optmize the saving then optimizing for loading/viewing, saving is done much more then loading/viewing.
a thing 2 add might be counters for succesfull/failed connects.
last is more important as some "loosers" keep the window opend for hours while they keep trying 2 connect 2 the hub, but hub kicks them for not folowing the rules.
it can be used 2 ban those guys 2 reduce the load on the hub.
the max size of the fields is a must have, seen ppl in my database with nicks over 256 characters.
those totaly screw up my webinterface.
agree on the redirects.
but why users and past_users?
on many ocasions there is no need for users are they are in the hub so that info is avail...
profile's haven't been changed so far on the 0.3.3.0 beta.
plop
@skrollster:
I also missed your question:
"and what about ip-range bans?"
I would assume that banning ip-ranges is something that would be pre-configured by the admin. Since the goal of the database is to record live, I do not see their purpose being include. Also, I'm not sure all hub softwares support banning IP ranges.
-NotRabidWombat
nick lenght and description are protected in dc++, but email isn't.
couldn't find the backup with those insane long nicks but i did found a email addy of 216 characters.
after some playing i found it is posible 2 make them even longer (ptokax fails above 256, string gets cut down 2 lenght).
plop
nick and description length are both limited 2 make 25 characters in dc++ (without tag).
open dc hub uses a max lenght of 100 for the email addy.
those are reasonable 2 use in my eyes.
just tryed dcgui-qt and there the nick is unlimited, just it seems 2 cause more trouble for dcgui then it does 2 ptokax.
dcgui shoots out errors here since this test. lol
plop
yep, can't see the need 2 grap data from sql when it's allready avail in the hub, only when it's not avail like logging/stats it's interesting.
personaly i still favour the idea of saving only the raw infostring instead of seperate fields, the bot has much less work on it then.
compair the amount of times you want sutch info with the amount of new users joining (or trying to join) your hub.
or save 1 long thing or do x strfind's and save x short things.
strlen(user.sMyInfoString) and strlen(user.sName) can limit far enough.
maby we should look @ how verlihub handles it, heared much good things about that.
gone install it 2morrow here.
plop
@plop
- Seperate fields are a more efficient means of storage, particularly share size
- SELECT statements will be more specific for searches (ie: users with shares > X or users who use DC++ 0.305 )
- SQL does not have an extensive regex.
- You only need on strfind to extract all fields. I'd much rather perform one regex expression than one for each SELECT.
Working on v0.2 with comments of the past 2 days.
*** DELETE ME ***
-NotRabidWombat
whouldn't it be faster if your store nicknames as a HASH value ?
something simple like:
A = 1
B = 2
etc.
and the date/time in c++ you have a function that returns amount of seconds after 1970. That way you also have a value there. Don't know if that excist in the programming language you are using.
--------------------------------------------------------------
I was few weeks back making a database client in c++
in combination with bde, also was trying too use SQL.
I had 1 table with all user logons and logoffs and there time/data. The only problem i had with 1 table was that i couldn't query for the online users atm :(
Somehow the SELECT DISTINCT always apply's on every field shown :( and not just on the first field.
So i'm temporarely stopped that project.
QuoteOriginally posted by NotRabidWombat
@plop
- Seperate fields are a more efficient means of storage, particularly share size
- SELECT statements will be more specific for searches (ie: users with shares > X or users who use DC++ 0.305 )
- SQL does not have an extensive regex.
- You only need on strfind to extract all fields. I'd much rather perform one regex expression than one for each SELECT.
Working on v0.2 with comments of the past 2 days.
-NotRabidWombat
ok you convinced me, your right (as always. lol).
this way there are more way's 2 search, a.i. only has 2, ip and name.
plop
Quote<++ V:0.25,M:A,H:100/10/10,S:2000,O:2000>
slighty offtopic but dc++ 0.306 has a max. of 100slots
i don't know if every version of dc++ has it.
51 CHARS !! <== do you know howmuch more info you can store in here
why are we using these tags again :S
QuoteOriginally posted by [NL]Pur
Quote<++ V:0.25,M:A,H:100/10/10,S:2000,O:2000>
slighty offtopic but dc++ 0.306 has a max. of 100slots
i don't know if every version of dc++ has it.
51 CHARS !! <== do you know howmuch more info you can store in here
why are we using these tags again :S
2000 slots works here on dc++ 0.306.
plop
u sure ?
unmodifided version?
if i use the scroller next too the upload slot box, i can't get it higher then 100.
I noticed now i can type in 999, but 4 digits it won't accept.
Then it's beeping.
ah yes that works ^^
maby save the tag as follows:
an example value could be
in the case of dc++ this is has value null
examples: V: H: O:
the value that after the
property comes.
Good developement going on here. Any idea when its due to complete? Also, this would require hub owners to install mysql or else get a webspace somewhere else?
Would it be possible to post some sort of requirements to get this up and running after its complete?
Just being curious, as many hub owners doesn't have mysql installed on their servers.
we're still waiting for v0.2 that rabidwombat is developing :)
Database Standard v0.2
The current hub events we are interested in:
- User Connects
- User Updates MyINFO
- User Disconnects
- User Sends Search
- User Sends Chat *
- User is registered
- User is kicked
- User is banned
- User is unbanned
* PM has purposely been ignored. I do not intend to get into a heated debate
regarding censorship and panopticons so if someone wants this, it's not that
hard to add. The database standard will not include it.
The current role of the database:
- A table for each user to include important information.
- A table of connected users.
- A table of past users.
- A table of searches.
- A table of chat messages.
- A table of registered users **
- A table of kicks.
- A table of bans.
** This complicates the matter of the database. Rather than being entirely
statistical, we are now including hub information. This is the first push
towards having hubs run entirely from a database. It was chosen since it was
the most relevant (ie: including more information about registered users).
The table of current users also supports hub integration since this could be
used to access all current user information.
I have made some addaptations and implemented as many suggestions as possible.
When replying please quote sections along with your proposed alteration, and
reasoning.
I have added a comment before each table suggesting the hub to database
interaction to help clarify what is historical and what is runtime.
After writing this I have reread the thread. The following questions/comments
are significant:
- nickban can be done without having the user in the hub, even with out the user
ever entered the hub ever, shouldn't this be recorded?
Why would an admin want to ban a nick before the user even entered the hub? Ok.
So if they decided to do so, a row with the corresponding nickname would have
be added to users. This row would have a NULL value for IP. This would be a
small change to the database design, BUT a big sidenote to anyone that wants to
use the database.
- nick length and description are protected in dc++, but email isn't.
This smells a lot like buffer overflow to me. I think we should start making these
length limits to solidify the DC protocol. It's OK if we go over a little for now
with VARCHAR because it will resize to the length unlike CHAR.
- damn, that makes the Myinfo string quite long.. [...] should we realy keep it
all in the database??
No. (at least in the historical section)
- Then it's beeping.
Hit it with a rock.
HUB_CREATE.sql
DROP TABLE IF EXISTS chat;
DROP TABLE IF EXISTS searches;
DROP TABLE IF EXISTS bans;
DROP TABLE IF EXISTS kicks;
DROP TABLE IF EXISTS past_users;
DROP TABLE IF EXISTS current_users;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS registered_users;
-- HUB: INSERT, UPDATE, DELETE
CREATE TABLE registered_users (
NickName VARCHAR(40) ,
-- NickName = NULL for Profile -1. the nobody user
Profile TINYINT NOT NULL,
Password CHAR(32) NOT NULL, -- for md5 encoding
-- Password will be part of hub integration
PRIMARY KEY(NickName)
);
-- HUB: INSERT
CREATE TABLE users (
UserID INT UNSIGNED NOT NULL AUTO_INCREMENT ,
NickName VARCHAR(40) NOT NULL,
IP INT NOT NULL,
PRIMARY KEY(UserID),
FOREIGN KEY(NickName) REFERENCES registered_user
);
-- HUB: INSERT
CREATE TABLE tag_info (
TagInfoID INT UNSIGNED NOT NULL AUTO_INCREMENT ,
Version FLOAT UNSIGNED NOT NULL,
Slots INT UNSIGNED NOT NULL,
Connection ENUM('A','P'),
-- these are the number of connection
Hub_Client INT UNSIGNED NOT NULL,
Hub_Reg INT UNSIGNED NOT NULL,
Hub_Op INT UNSIGNED NOT NULL,
Cap INT UNSIGNED NOT NULL,
-- kBps, 0 = uncapped (or unknown)
-- anything else that would be useful?
-- I'd rather not get into open/closed slots
-- since there is no easy way to access this
-- information without polling
PRIMARY KEY(TagInfoID)
);
-- HUB: INSERT, UPDATE
CREATE TABLE client_info (
ClientInfoID INT UNSIGNED NOT NULL AUTO_INCREMENT ,
ClientType ENUM('DC++', 'oDC', 'BCDC', 'NMDC', 'DC-GUI', 'DC-Pro', 'Other'),
-- obviously this list is not complete. Just presenting the concept
TagInfoID INT UNSIGNED ,
-- NULL is no tag information
PRIMARY KEY(ClientInfoID),
FOREIGN KEY(TagInfoID) REFERENCES tag_info
);
-- HUB: INSERT, UPDATE, DELETE
CREATE TABLE current_users (
UserID INT UNSIGNED NOT NULL,
Login DATETIME NOT NULL,
ShareSize BIGINT UNSIGNED NOT NULL,
Description VARCHAR(100) NOT NULL,
-- Description does NOT include tag info
Profile TINYINT NOT NULL,
ClientInfoID INT UNSIGNED NOT NULL
-- Added these to support next phase of planning
Email VARCHAR(100) NOT NULL,
Connection VARCHAR(20) NOT NULL,
-- Connection can be simplified to an enum or a single byte
-- maybe later
PRIMARY KEY(UserID),
FOREIGN KEY(UserID) REFERENCES users,
FOREIGN KEY(ClientInfoID) REFERENCES client_info
);
-- HUB: INSERT
CREATE TABLE past_users (
UserID INT UNSIGNED NOT NULL,
Login DATETIME NOT NULL,
Logout DATETIME NOT NULL,
ShareSize BIGINT UNSIGNED NOT NULL,
Description VARCHAR(100) NOT NULL,
Profile TINYINT NOT NULL,
ClientInfoID INT UNSIGNED NOT NULL,
-- Connection and Email have been omitted
PRIMARY KEY(UserID, Login),
FOREIGN KEY(UserID) REFERENCES users,
FOREIGN KEY(ClientInfoID) REFERENCES client_info
);
-- HUB: INSERT, UPDATE
CREATE TABLE kicks (
UserID INT UNSIGNED NOT NULL,
Time DATETIME NOT NULL,
Until DATETIME NOT NULL,
OpID INT UNSIGNED NOT NULL,
Reason VARCHAR(100) NOT NULL,
PRIMARY KEY(UserID, Time),
FOREIGN KEY(UserID) REFERENCES users,
FOREIGN KEY(OpID) REFERENCES users
);
-- HUB: INSERT, UPDATE
CREATE TABLE bans (
UserID INT UNSIGNED NOT NULL,
Time DATETIME NOT NULL,
Until DATETIME ,
-- NULL is forever
OpID INT UNSIGNED NOT NULL,
Reason VARCHAR(100) NOT NULL,
Type ENUM('Nick','IP','Both'),
PRIMARY KEY(UserID, Time),
FOREIGN KEY(UserID) REFERENCES users,
FOREIGN KEY(OpID) REFERENCES users
);
-- HUB: INSERT
CREATE TABLE searches (
UserID INT UNSIGNED NOT NULL,
SearchString VARCHAR(255) NOT NULL,
Count INT UNSIGNED NOT NULL,
PRIMARY KEY(UserID, SearchString),
FOREIGN KEY(UserID) REFERENCES users
);
-- HUB: INSERT
CREATE TABLE chat (
ChatID INT UNSIGNED NOT NULL AUTO_INCREMENT ,
UserID INT UNSIGNED NOT NULL,
ChatText TEXT NOT NULL,
Time DATETIME NOT NULL,
PRIMARY KEY(ChatID),
FOREIGN KEY(UserID) REFERENCES users
);
Sorry for the long delay. This code is all concept. I have not checked it syntactically yet.
By the way, does anyone know how to keep tabs without using the CODE tag?
-NotRabidWombat
QuoteOriginally posted by NotRabidWombat
- nickban can be done without having the user in the hub, even with out the user
ever entered the hub ever, shouldn't this be recorded?
Why would an admin want to ban a nick before the user even entered the hub? Ok.
So if they decided to do so, a row with the corresponding nickname would have
be added to users. This row would have a NULL value for IP. This would be a
small change to the database design, BUT a big sidenote to anyone that wants to
use the database.
Lets say you have a large network, as in my case 16 hubs, and a user do something that makes him not welcome any where in the network with that nick.. or you maybe want to nickban NotRabidWombat in all hubs that you are in if you always use a prefix so that no one get cheated.. there is a lot of reasons to nick ban a user that never has entered the hub IMHO
-- HUB: INSERT
CREATE TABLE client_info (
ClientInfoID INT UNSIGNED NOT NULL AUTO_INCREMENT ,
ClientType ENUM('DC++', 'oDC', 'BCDC', 'NMDC', 'DC-GUI', 'DC-Pro', 'Other'),
-- obviously this list is not complete. Just presenting the concept
TagInfoID INT UNSIGNED ,
-- NULL is no tag information
PRIMARY KEY(ClientInfoID),
FOREIGN KEY(TagInfoID) REFERENCES tag_info
);
-- HUB: INSERT, UPDATE, DELETE
CREATE TABLE current_users (
UserID INT UNSIGNED NOT NULL,
Login DATETIME NOT NULL,
ShareSize BIGINT UNSIGNED NOT NULL,
Description VARCHAR(100) NOT NULL,
-- Description does NOT include tag info
Profile TINYINT NOT NULL,
ClientInfoID INT UNSIGNED NOT NULL
-- Added these to support next phase of planning
Email VARCHAR(100) NOT NULL,
Connection VARCHAR(20) NOT NULL,
-- Connection can be simplified to an enum or a single byte
-- maybe later
PRIMARY KEY(UserID),
FOREIGN KEY(UserID) REFERENCES users,
FOREIGN KEY(ClientInfoID) REFERENCES client_info
);
-- HUB: INSERT
CREATE TABLE past_users (
UserID INT UNSIGNED NOT NULL,
Login DATETIME NOT NULL,
Logout DATETIME NOT NULL,
ShareSize BIGINT UNSIGNED NOT NULL,
Description VARCHAR(100) NOT NULL,
Profile TINYINT NOT NULL,
ClientInfoID INT UNSIGNED NOT NULL,
-- Connection and Email have been omitted
PRIMARY KEY(UserID, Login),
FOREIGN KEY(UserID) REFERENCES users,
FOREIGN KEY(ClientInfoID) REFERENCES client_info
);
Doesn't this give us clientinfo that doesn't have any relationship if a user changes there myinfo tag then they still are in the hub=
QuoteBy the way, does anyone know how to keep tabs without using the CODE tag?
write it in word and use the same font as at the webpage, then it should look the same i guess.. or use wordpad and the same font..
haven't tested it though
- Doesn't this give us clientinfo that doesn't have any relationship if a user changes there myinfo tag then they still are in the hub
The strategy behind the TagInfo (and ClientInfo) is the same as the strategy behind the users table. If the row exists, reuse it, otherwise make a new one. So if a user changes to a tag that does not exist in the database, we simply insert it and changes the foreign keys.
BUT, you made me notice something. If the above is true, then the hub MUST be able to update the ClientInfo table.
Thanks for the tip on the delete post. Sorry to whoever I made delete them for me :-\
Why would writing the document in word keep the tabs? I typically right it in another text editor with tabbing and they are removed when I post.
-NotRabidWombat
Database Standard v0.2.1
The client_info table was pretty useless. It has been ommitted.
DROP TABLE IF EXISTS chat;
DROP TABLE IF EXISTS searches;
DROP TABLE IF EXISTS bans;
DROP TABLE IF EXISTS kicks;
DROP TABLE IF EXISTS past_users;
DROP TABLE IF EXISTS current_users;
DROP TABLE IF EXISTS tag_info;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS registered_users;
-- HUB: INSERT, UPDATE, DELETE
CREATE TABLE registered_users (
NickName VARCHAR(40) ,
-- NickName = NULL for Profile -1. the nobody user
Profile TINYINT NOT NULL,
Password CHAR(32) NOT NULL, -- for md5 encoding
-- Password will be part of hub integration
PRIMARY KEY(NickName)
);
-- HUB: INSERT
CREATE TABLE users (
UserID INT UNSIGNED NOT NULL AUTO_INCREMENT ,
NickName VARCHAR(40) NOT NULL,
IP INT NOT NULL,
PRIMARY KEY(UserID),
FOREIGN KEY(NickName) REFERENCES registered_user
);
-- HUB: INSERT
CREATE TABLE tag_info (
TagInfoID INT UNSIGNED NOT NULL AUTO_INCREMENT ,
Version FLOAT UNSIGNED NOT NULL,
Slots INT UNSIGNED NOT NULL,
Connection ENUM('A','P'),
-- these are the number of connection
Hub_Client INT UNSIGNED NOT NULL,
Hub_Reg INT UNSIGNED NOT NULL,
Hub_Op INT UNSIGNED NOT NULL,
Cap INT UNSIGNED NOT NULL,
-- kBps, 0 = uncapped (or unknown)
-- anything else that would be useful?
-- I'd rather not get into open/closed slots
-- since there is no easy way to access this
-- information without polling
PRIMARY KEY(TagInfoID)
);
-- HUB: INSERT, UPDATE, DELETE
CREATE TABLE current_users (
UserID INT UNSIGNED NOT NULL,
Login DATETIME NOT NULL,
ShareSize BIGINT UNSIGNED NOT NULL,
Description VARCHAR(100) NOT NULL,
-- Description does NOT include tag info
Profile TINYINT NOT NULL,
ClientType ENUM('DC++', 'oDC', 'BCDC', 'NMDC', 'DC-GUI', 'DC-Pro', 'Other'),
TagInfoID INT UNSIGNED ,
-- NULL is no tag information
-- Added these to support next phase of planning
Email VARCHAR(100) NOT NULL,
Connection VARCHAR(20) NOT NULL,
-- Connection can be simplified to an enum or a single byte
-- maybe later
PRIMARY KEY(UserID),
FOREIGN KEY(UserID) REFERENCES users,
FOREIGN KEY(TagInfoID) REFERENCES tag_info
);
-- HUB: INSERT
CREATE TABLE past_users (
UserID INT UNSIGNED NOT NULL,
Login DATETIME NOT NULL,
Logout DATETIME NOT NULL,
ShareSize BIGINT UNSIGNED NOT NULL,
Description VARCHAR(100) NOT NULL,
Profile TINYINT NOT NULL,
ClientType ENUM('DC++', 'oDC', 'BCDC', 'NMDC', 'DC-GUI', 'DC-Pro', 'Other'),
TagInfoID INT UNSIGNED ,
-- NULL is no tag information
-- Connection and Email have been omitted
PRIMARY KEY(UserID, Login),
FOREIGN KEY(UserID) REFERENCES users,
FOREIGN KEY(TagInfoID) REFERENCES tag_info
);
-- HUB: INSERT, UPDATE
CREATE TABLE kicks (
UserID INT UNSIGNED NOT NULL,
Time DATETIME NOT NULL,
Until DATETIME NOT NULL,
OpID INT UNSIGNED NOT NULL,
Reason VARCHAR(100) NOT NULL,
PRIMARY KEY(UserID, Time),
FOREIGN KEY(UserID) REFERENCES users,
FOREIGN KEY(OpID) REFERENCES users
);
-- HUB: INSERT, UPDATE
CREATE TABLE bans (
UserID INT UNSIGNED NOT NULL,
Time DATETIME NOT NULL,
Until DATETIME ,
-- NULL is forever
OpID INT UNSIGNED NOT NULL,
Reason VARCHAR(100) NOT NULL,
Type ENUM('Nick','IP','Both'),
PRIMARY KEY(UserID, Time),
FOREIGN KEY(UserID) REFERENCES users,
FOREIGN KEY(OpID) REFERENCES users
);
-- HUB: INSERT
CREATE TABLE searches (
UserID INT UNSIGNED NOT NULL,
SearchString VARCHAR(255) NOT NULL,
Count INT UNSIGNED NOT NULL,
PRIMARY KEY(UserID, SearchString),
FOREIGN KEY(UserID) REFERENCES users
);
-- HUB: INSERT
CREATE TABLE chat (
ChatID INT UNSIGNED NOT NULL AUTO_INCREMENT ,
UserID INT UNSIGNED NOT NULL,
ChatText TEXT NOT NULL,
Time DATETIME NOT NULL,
PRIMARY KEY(ChatID),
FOREIGN KEY(UserID) REFERENCES users
);
-NotRabidWombat
been thinking a bit.. doesn't this:
' Hub_Client INT UNSIGNED NOT NULL,
' Hub_Reg INT UNSIGNED NOT NULL,
' Hub_Op INT UNSIGNED NOT NULL,
means that you need to have a new dc++ klient with the "new"(H:x/x/x) hub tag, and not the old one (H:x)
what about users that still uses the older dc++ klients?
No.
We will assume that all connections are unregistered user if they use the old format. BUT, no one should be using the old format because those version had an exploit.
OR
We could just permit NULL for OP and REG.
I prefer the first option.
-NotRabidWombat
what exploit are you referring too ?
DC++ versions before 0.300 (or something like that) had an exploit to allow users access to your files.
-NotRabidWombat
Any other comments or suggestions?
Otherwise I'll just implement it.
-NotRabidWombat
Nothing else but the ip-range ban thing that we have discussed...
Maby add a table with some info about the Hub itself
QuoteOnce 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.
I can post it there... i'm quite active there as well as here...
- 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
sounds great...
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
QuoteOriginally 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..
Ok. I will make the script public once I get rid of the bugs. Right now BCDC crashes with FURY ;-)
-NotRabidWombat
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
Gnug! Sure thing :-) I'm really busy trying to mod BCDC++ so go ahead and take your time.
-NotRabidWombat
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...
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
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
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
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
QuoteHowever, 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?
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
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++.
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
Big thanks to Corayzon for hosting this for me.
This version of BCDC++ supports loadlib.
Download (ftp://cslave.no-ip.org/bcdc++/bcdcplusplus-loadlib+luasql-release.zip)
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 (http://lua-users.org/wiki/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.
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/ (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.
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/ (http://www.phpmyadmin.net/home_page/)
to download (http://www.phpmyadmin.net/home_page/downloads.php#2.5.6)
but also this tool is wrighten in php so you will also need that .http://www.php.net/ (http://www.php.net/)
now you can save alot of time by useing this >>http://www.easyphp.org/ (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/ (http://www.canowhoopass.com/guides/easyphp/)
hope it helps :)
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 :).
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.
Actually that is my preferred method of testing/working with MySql. There is a command prompt, but there are also some other steps you should take to make sure you have a secure database.
http://www.analysisandsolutions.com/code/mybasic.htm
If you're going to have another computer add data, go to this link:
http://www.uknnet.com/lua/index.php
Once you have everything working through these.
1) copy the create code we have here into mysql\bin\CreateHub.sql
2) open your mysql prompt
3) create a new database
4) use Your_database_name;
5) source CreateHub.sql;
6) show tables;
Now you're on your way. You'll probably want to read some Sql tutorials. Just google it ;-)
-NotRabidWombat
okey thanks, I will try to set one up, if I should go insane on the way, you should see the MySQL go up in smoke sooner or later ;).
is that above link you posted dead or is it just me ?
QuoteThis version of BCDC++ supports loadlib.
Download
Just you.
-NotRabidWombat