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

[Dan McGee]

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?

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