diff options
| -rw-r--r-- | support/schema/aur-schema.sql | 94 |
1 files changed, 49 insertions, 45 deletions
diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql index e147c47..37bbc01 100644 --- a/support/schema/aur-schema.sql +++ b/support/schema/aur-schema.sql @@ -1,36 +1,38 @@ -- The MySQL database layout for the AUR. Certain data -- is also included such as AccountTypes, PackageLocations, etc. -- +DROP DATABASE AUR; +CREATE DATABASE AUR; -- Define the Account Types for the AUR. -- CREATE TABLE AccountTypes ( - ID UNSIGNED TINYINT NOT NULL AUTO_INCREMENT, + ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, AccountType char(32) NOT NULL DEFAULT '', PRIMARY KEY (ID) ); -INSERT INTO TABLE (ID, AccountType) VALUES (1, 'User'); -INSERT INTO TABLE (ID, AccountType) VALUES (2, 'Trusted User'); -INSERT INTO TABLE (ID, AccountType) VALUES (3, 'Developer'); +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 UNSIGNED INTEGER NOT NULL AUTO_INCREMENT, - AccountTypeID UNSIGNED TINYINT NOT NULL DEFAULT 1, - Suspended UNSIGNED TINYINT NOT NULL DEFAULT 0, + ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + AccountTypeID TINYINT UNSIGNED NOT NULL DEFAULT 1, + Suspended TINYINT UNSIGNED NOT NULL DEFAULT 0, Email CHAR(64) NOT NULL, Passwd CHAR(32) NOT NULL, RealName CHAR(64) NOT NULL DEFAULT '', IRCNick CHAR(32) NOT NULL DEFAULT '', - LastVoted UNSIGNED BIGINT NOT NULL DEFAULT 0, - NewPkgNotify UNSIGNED TINYINT NOT NULL DEFAULT 0, + LastVoted BIGINT UNSIGNED NOT NULL DEFAULT 0, + NewPkgNotify TINYINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (ID), - UNIQUE INDEX Emailx (Email), - INDEX AccountTypeIDx (AccountTypeID), - INDEX NewPkgNotifyx (NewPkgNotify), - FOREIGN KEY AccountTypeIDr REFERENCES AccountTypes (ID) + UNIQUE (Email), + INDEX (AccountTypeID), + INDEX (NewPkgNotify), + FOREIGN KEY (AccountTypeID) REFERENCES AccountTypes(ID) ON DELETE NO ACTION ); -- A default developer account for testing purposes INSERT INTO Users (ID, AccountTypeID, Email, Passwd) VALUES ( @@ -40,10 +42,10 @@ INSERT INTO Users (ID, AccountTypeID, Email, Passwd) VALUES ( -- Track Users logging in/out of AUR web site. -- CREATE TABLE Sessions ( - UsersID UNSIGNED INTEGER NOT NULL, + UsersID INTEGER UNSIGNED NOT NULL, SessionID CHAR(32) NOT NULL, - LastUpdateTS UNSIGNED BIGINT NOT NULL, - FOREIGN KEY UsersIDr REFERENCES Users (ID) + LastUpdateTS BIGINT UNSIGNED NOT NULL, + FOREIGN KEY (UsersID) REFERENCES Users(ID) ); @@ -52,7 +54,7 @@ CREATE TABLE Sessions ( -- in 'extra'. -- CREATE TABLE PackageCategories ( - ID UNSIGNED TINYINT NOT NULL AUTO_INCREMENT, + ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, Category CHAR(32) NOT NULL, PRIMARY KEY (ID) ); @@ -78,7 +80,7 @@ INSERT INTO PackageCategories (Category) VALUES ('xfce'); -- The various repositories that a package could live in. -- CREATE TABLE PackageLocations ( - ID UNSIGNED TINYINT NOT NULL AUTO_INCREMENT, + ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, Location CHAR(32) NOT NULL, PRIMARY KEY (ID) ); @@ -92,48 +94,50 @@ INSERT INTO PackageLocations (ID, Location) VALUES (5, 'Unstable'); -- Information about the actual packages -- CREATE TABLE Packages ( - ID UNSIGNED INTEGER NOT NULL AUTO_INCREMENT, + ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, Name CHAR(32) NOT NULL, Version CHAR(32) NOT NULL DEFAULT '', - CategoryID UNSIGNED TINYINT NOT NULL, + CategoryID TINYINT UNSIGNED NOT NULL, Description CHAR(128) NOT NULL DEFAULT "An Arch Package", - URL CHAR(256) NOT NULL DEFAULT "http://www.archlinux.org", - Source CHAR(256) NOT NULL DEFAULT "/dev/null", - LocationID UNSIGNED TINYINT NOT NULL, - OutOfDate UNSIGNED TINYINT DEFAULT 0, - SubmittedTS UNSIGNED BIGINT NOT NULL, - SubmitterUID UNSIGNED INTEGER NOT NULL DEFAULT 0, - MaintainerUID UNSIGNED INTEGER NOT NULL DEFAULT 0, + URL CHAR(255) NOT NULL DEFAULT "http://www.archlinux.org", + Source CHAR(255) NOT NULL DEFAULT "/dev/null", + LocationID TINYINT UNSIGNED NOT NULL, + OutOfDate TINYINT UNSIGNED DEFAULT 0, + SubmittedTS BIGINT UNSIGNED NOT NULL, + SubmitterUID INTEGER UNSIGNED NOT NULL DEFAULT 0, + MaintainerUID INTEGER UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (ID), - UNIQUE INDEX Namex (Name), - INDEX CategoryIDx (CategoryID), - INDEX LocationIDx (LocationID), - INDEX OutOfDatex (OutOfDate), - INDEX SubmitterUIDx (SubmitterUID), - INDEX MaintainerUIDx (MaintainerUID), - FOREIGN KEY CategoryIDr REFERENCES PackageCategories (ID), - FOREIGN KEY LocationIDr REFERENCES PackageLocations (ID) - FOREIGN KEY SubmitterUIDr REFERENCES Users (ID) - FOREIGN KEY MaintainerUIDr REFERENCES Users (ID) + UNIQUE (Name), + INDEX (CategoryID), + INDEX (LocationID), + INDEX (OutOfDate), + INDEX (SubmitterUID), + INDEX (MaintainerUID), + FOREIGN KEY (CategoryID) REFERENCES PackageCategories(ID) ON DELETE NO ACTION, + FOREIGN KEY (LocationID) REFERENCES PackageLocations(ID) ON DELETE NO ACTION, + FOREIGN KEY (SubmitterUID) REFERENCES Users(ID) ON DELETE NO ACTION, + FOREIGN KEY (MaintainerUID) REFERENCES Users(ID) ON DELETE NO ACTION ); -- Track votes for packages -- CREATE TABLE PackageVotes ( - UsersID UNSIGNED INTEGER NOT NULL, - PackageID UNSIGNED INTEGER NOT NULL, - PRIMARY KEY (ID), - FOREIGN KEY UsersIDx REFERENCES Users (ID), - FOREIGN KEY PackageIDx REFERENCES Packages (ID) + 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 ); -- The individual files and their file system location. -- CREATE TABLE PackageContents ( - PackageID UNSIGNED INTEGER NOT NULL, - Path CHAR(256) NOT NULL, - INDEX PackageIDx (PackageID) + PackageID INTEGER UNSIGNED NOT NULL, + Path CHAR(255) NOT NULL, + INDEX (PackageID), + FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE ); |