Statistical Database Standard

[NotRabidWombat]

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. 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

[plop]

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

[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

[NotRabidWombat]

@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

[plop]

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

[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

[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

[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.

*** DELETE ME ***

-NotRabidWombat

[[NL]Pur]

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.

[plop]

Originally 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

[[NL]Pur]

<++ 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

[plop]

Originally posted by [NL]Pur

<++ 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

[[NL]Pur]

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.

[[NL]Pur]

ah yes that works ^^

[[NL]Pur]

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.

[BlazeXxX]

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.

[[NL]Pur]

we're still waiting for v0.2 that rabidwombat is developing :)

[NotRabidWombat]

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

[Skrollster]

Originally 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=

By 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

[NotRabidWombat]

- 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

[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

[Skrollster]

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?

[NotRabidWombat]

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

[[NL]Pur]

what exploit are you referring too ?

[NotRabidWombat]

DC++ versions before 0.300 (or something like that) had an exploit to allow users access to your files.

-NotRabidWombat