On Tue, Mar 19, 2013 at 8:21 PM, Dan McGee <[email protected]> wrote: > On Tue, Mar 19, 2013 at 7:17 PM, canyonknight <[email protected]> wrote: >> The "Bans" table creates a DB structure for the ability to ban IP >> addresses. It takes an IP address converted by ip2long(). It can easily >> be extended for other features such as time limits (for temporary bans). >> >> The table will eventually be able to be populated directly through >> the web interface by Trusted Users and Developers. >> >> Signed-off-by: canyonknight <[email protected]> >> --- >> UPGRADING | 11 +++++++++++ >> support/schema/aur-schema.sql | 9 +++++++++ >> 2 files changed, 20 insertions(+) >> >> diff --git a/UPGRADING b/UPGRADING >> index 9c05467..7d13c43 100644 >> --- a/UPGRADING >> +++ b/UPGRADING >> @@ -11,6 +11,17 @@ ALTER TABLE Users >> ADD COLUMN LastLoginIPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0; >> ---- >> >> +2. Add a new "Bans" table: >> + >> +---- >> +CREATE TABLE Bans ( >> + ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, >> + IPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, >> + PRIMARY KEY (ID), >> + UNIQUE (IPAddress) >> +) ENGINE = InnoDB; >> +---- >> + >> From 2.0.0 to 2.1.0 >> ------------------- >> >> diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql >> index fab40d6..2d268fa 100644 >> --- a/support/schema/aur-schema.sql >> +++ b/support/schema/aur-schema.sql >> @@ -213,3 +213,12 @@ CREATE TABLE IF NOT EXISTS TU_Votes ( >> FOREIGN KEY (VoteID) REFERENCES TU_VoteInfo(ID) ON DELETE CASCADE, >> FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE >> ) ENGINE = InnoDB; >> + >> +-- Malicious user banning >> +-- >> +CREATE TABLE Bans ( >> + ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, >> + IPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, >> + PRIMARY KEY (ID), >> + UNIQUE (IPAddress) >> +) ENGINE = InnoDB; > > Why bother with an ID column at all here, since your IPAddress column > is unique, an integer, and could be it's own primary key?
I had done it for insertion order purposes, but your timestamp idea is better. > > Finally, I would highly recommend adding a created timestamp in lieu > of an ID; it still gives insertion order but also relative time > spacing of said insertions, and would allow gradual aging out of old > entries if that was ever desired in the future. > > -Dan
