[aur-dev] [PATCH 1/2] Add "Bans" table to database schema

[canyonknight]

On Tue, Mar 19, 2013 at 8:21 PM, Dan McGee <dpmcgee at gmail.com> wrote:
> On Tue, Mar 19, 2013 at 7:17 PM, canyonknight <canyonknight at gmail.com> 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 <canyonknight at gmail.com>
>> ---
>>  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