summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
-rw-r--r--support/schema/aur-schema.sql94
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
);