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