diff options
Diffstat (limited to 'schema/aur-schema.sql')
| -rw-r--r-- | schema/aur-schema.sql | 228 |
1 files changed, 228 insertions, 0 deletions
diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql new file mode 100644 index 00000000..c01701c8 --- /dev/null +++ b/schema/aur-schema.sql @@ -0,0 +1,228 @@ +-- The MySQL database layout for the AUR. Certain data +-- is also included such as AccountTypes, etc. +-- +DROP DATABASE IF EXISTS AUR; +CREATE DATABASE AUR DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; +USE AUR; + +-- Define the Account Types for the AUR. +-- +CREATE TABLE AccountTypes ( + ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, + AccountType VARCHAR(32) NOT NULL DEFAULT '', + PRIMARY KEY (ID) +) ENGINE = InnoDB; +INSERT INTO AccountTypes (ID, AccountType) VALUES (1, 'User'); +INSERT INTO AccountTypes (ID, AccountType) VALUES (2, 'Trusted User'); +INSERT INTO AccountTypes (ID, AccountType) VALUES (3, 'Developer'); + + +-- User information for each user regardless of type. +-- +CREATE TABLE Users ( + ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + AccountTypeID TINYINT UNSIGNED NOT NULL DEFAULT 1, + Suspended TINYINT UNSIGNED NOT NULL DEFAULT 0, + Username VARCHAR(32) NOT NULL, + Email VARCHAR(64) NOT NULL, + Passwd CHAR(32) NOT NULL, + Salt CHAR(32) NOT NULL DEFAULT '', + ResetKey CHAR(32) NOT NULL DEFAULT '', + RealName VARCHAR(64) NOT NULL DEFAULT '', + LangPreference VARCHAR(5) NOT NULL DEFAULT 'en', + IRCNick VARCHAR(32) NOT NULL DEFAULT '', + PGPKey VARCHAR(40) NULL DEFAULT NULL, + LastVoted BIGINT UNSIGNED NOT NULL DEFAULT 0, + LastLogin BIGINT UNSIGNED NOT NULL DEFAULT 0, + LastLoginIPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, + InactivityTS BIGINT UNSIGNED NOT NULL DEFAULT 0, + RegistrationTS TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (ID), + UNIQUE (Username), + UNIQUE (Email), + INDEX (AccountTypeID), + FOREIGN KEY (AccountTypeID) REFERENCES AccountTypes(ID) ON DELETE NO ACTION +) ENGINE = InnoDB; +-- A default developer account for testing purposes +INSERT INTO Users (ID, AccountTypeID, Username, Email, Passwd) VALUES ( + 1, 3, 'dev', 'dev@localhost', MD5('dev')); +INSERT INTO Users (ID, AccountTypeID, Username, Email, Passwd) VALUES ( + 2, 2, 'tu', 'tu@localhost', MD5('tu')); +INSERT INTO Users (ID, AccountTypeID, Username, Email, Passwd) VALUES ( + 3, 1, 'user', 'user@localhost', MD5('user')); + + +-- Track Users logging in/out of AUR web site. +-- +CREATE TABLE Sessions ( + UsersID INTEGER UNSIGNED NOT NULL, + SessionID CHAR(32) NOT NULL, + LastUpdateTS BIGINT UNSIGNED NOT NULL, + FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE, + UNIQUE (SessionID) +) ENGINE = InnoDB; + + +-- Categories for grouping packages when they reside in +-- Unsupported or the AUR - based on the categories defined +-- in 'extra'. +-- +CREATE TABLE PackageCategories ( + ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, + Category VARCHAR(32) NOT NULL, + PRIMARY KEY (ID) +) ENGINE = InnoDB; +INSERT INTO PackageCategories (Category) VALUES ('none'); +INSERT INTO PackageCategories (Category) VALUES ('daemons'); +INSERT INTO PackageCategories (Category) VALUES ('devel'); +INSERT INTO PackageCategories (Category) VALUES ('editors'); +INSERT INTO PackageCategories (Category) VALUES ('emulators'); +INSERT INTO PackageCategories (Category) VALUES ('games'); +INSERT INTO PackageCategories (Category) VALUES ('gnome'); +INSERT INTO PackageCategories (Category) VALUES ('i18n'); +INSERT INTO PackageCategories (Category) VALUES ('kde'); +INSERT INTO PackageCategories (Category) VALUES ('lib'); +INSERT INTO PackageCategories (Category) VALUES ('modules'); +INSERT INTO PackageCategories (Category) VALUES ('multimedia'); +INSERT INTO PackageCategories (Category) VALUES ('network'); +INSERT INTO PackageCategories (Category) VALUES ('office'); +INSERT INTO PackageCategories (Category) VALUES ('science'); +INSERT INTO PackageCategories (Category) VALUES ('system'); +INSERT INTO PackageCategories (Category) VALUES ('x11'); +INSERT INTO PackageCategories (Category) VALUES ('xfce'); +INSERT INTO PackageCategories (Category) VALUES ('fonts'); + + +-- Information about the actual packages +-- +CREATE TABLE Packages ( + ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + Name VARCHAR(64) NOT NULL, + Version VARCHAR(32) NOT NULL DEFAULT '', + CategoryID TINYINT UNSIGNED NOT NULL DEFAULT 1, + Description VARCHAR(255) NOT NULL DEFAULT "An Arch Package", + URL VARCHAR(255) NOT NULL DEFAULT "https://www.archlinux.org", + License VARCHAR(40) NOT NULL DEFAULT '', + NumVotes INTEGER UNSIGNED NOT NULL DEFAULT 0, + OutOfDateTS BIGINT UNSIGNED NULL DEFAULT NULL, + SubmittedTS BIGINT UNSIGNED NOT NULL, + ModifiedTS BIGINT UNSIGNED NOT NULL, + SubmitterUID INTEGER UNSIGNED NULL DEFAULT NULL, -- who submitted it? + MaintainerUID INTEGER UNSIGNED NULL DEFAULT NULL, -- User + PRIMARY KEY (ID), + UNIQUE (Name), + INDEX (CategoryID), + INDEX (NumVotes), + INDEX (SubmitterUID), + INDEX (MaintainerUID), + FOREIGN KEY (CategoryID) REFERENCES PackageCategories(ID) ON DELETE NO ACTION, + -- deleting a user will cause packages to be orphaned, not deleted + FOREIGN KEY (SubmitterUID) REFERENCES Users(ID) ON DELETE SET NULL, + FOREIGN KEY (MaintainerUID) REFERENCES Users(ID) ON DELETE SET NULL +) ENGINE = InnoDB; + + +-- Track which dependencies a package has +-- +CREATE TABLE PackageDepends ( + PackageID INTEGER UNSIGNED NOT NULL, + DepName VARCHAR(64) NOT NULL, + DepCondition VARCHAR(20), + INDEX (PackageID), + INDEX (DepName), + FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE +) ENGINE = InnoDB; + + +-- Track which sources a package has +-- +CREATE TABLE PackageSources ( + PackageID INTEGER UNSIGNED NOT NULL, + Source VARCHAR(255) NOT NULL DEFAULT "/dev/null", + INDEX (PackageID), + FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE +) ENGINE = InnoDB; + + +-- Track votes for packages +-- +CREATE TABLE PackageVotes ( + UsersID INTEGER UNSIGNED NOT NULL, + PackageID INTEGER UNSIGNED NOT NULL, + INDEX (UsersID), + INDEX (PackageID), + FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE, + FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE +) ENGINE = InnoDB; +CREATE UNIQUE INDEX VoteUsersIDPackageID ON PackageVotes (UsersID, PackageID); + +-- Record comments for packages +-- +CREATE TABLE PackageComments ( + ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, + PackageID INTEGER UNSIGNED NOT NULL, + UsersID INTEGER UNSIGNED NULL DEFAULT NULL, + Comments TEXT NOT NULL DEFAULT '', + CommentTS BIGINT UNSIGNED NOT NULL DEFAULT 0, + DelUsersID INTEGER UNSIGNED NULL DEFAULT NULL, + PRIMARY KEY (ID), + INDEX (UsersID), + INDEX (PackageID), + FOREIGN KEY (UsersID) REFERENCES Users(ID) ON SET NULL, + FOREIGN KEY (DelUsersID) REFERENCES Users(ID) ON DELETE CASCADE, + FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE +) ENGINE = InnoDB; + +-- Comment addition notifications +-- +CREATE TABLE CommentNotify ( + PkgID INTEGER UNSIGNED NOT NULL, + UserID INTEGER UNSIGNED NOT NULL, + FOREIGN KEY (PkgID) REFERENCES Packages(ID) ON DELETE CASCADE, + FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE +) ENGINE = InnoDB; +CREATE UNIQUE INDEX NotifyUserIDPkgID ON CommentNotify (UserID, PkgID); + +-- Package name blacklist +-- +CREATE TABLE PackageBlacklist ( + ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + Name VARCHAR(64) NOT NULL, + PRIMARY KEY (ID), + UNIQUE (Name) +) ENGINE = InnoDB; + +-- Vote information +-- +CREATE TABLE IF NOT EXISTS TU_VoteInfo ( + ID int(10) unsigned NOT NULL auto_increment, + Agenda text NOT NULL, + User VARCHAR(32) NOT NULL, + Submitted bigint(20) unsigned NOT NULL, + End bigint(20) unsigned NOT NULL, + Quorum decimal(2, 2) unsigned NOT NULL, + SubmitterID int(10) unsigned NOT NULL, + Yes tinyint(3) unsigned NOT NULL default '0', + No tinyint(3) unsigned NOT NULL default '0', + Abstain tinyint(3) unsigned NOT NULL default '0', + ActiveTUs tinyint(3) unsigned NOT NULL default '0', + PRIMARY KEY (ID), + FOREIGN KEY (SubmitterID) REFERENCES Users(ID) ON DELETE CASCADE +) ENGINE = InnoDB; + +-- Individual vote records +-- +CREATE TABLE IF NOT EXISTS TU_Votes ( + VoteID int(10) unsigned NOT NULL, + UserID int(10) unsigned NOT NULL, + 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 ( + IPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, + BanTS TIMESTAMP NOT NULL, + PRIMARY KEY (IPAddress) +) ENGINE = InnoDB; |