diff options
author | Lukas Fleischer <archlinux@cryptocrack.de> | 2011-02-26 21:11:25 +0100 |
---|---|---|
committer | Lukas Fleischer <archlinux@cryptocrack.de> | 2011-02-26 21:11:25 +0100 |
commit | ac632980c3789cd99a80d61861b8820ddad14704 (patch) | |
tree | 776006cc1f9a605a57942ab4cf19c5392890e3c7 | |
parent | e72f02872fc1f81bdd609c061f08e69b34153c7e (diff) |
Add missing foreign keys and constraints to the DB.
Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
-rw-r--r-- | UPGRADING | 26 | ||||
-rw-r--r-- | support/schema/aur-schema.sql | 16 |
2 files changed, 37 insertions, 5 deletions
@@ -1,6 +1,32 @@ Upgrading ========= +From 1.8.0 to 1.8.1 +------------------- + +1. Drop foreign keys from the "Sessions" table: + +`ALTER TABLE Sessions DROP FOREIGN KEY Sessions_ibfk_1;` should work in most +cases. Otherwise, check the output of `SHOW CREATE TABLE Sessions;` and use the +foreign key name shown there. + +2. Run the following MySQL statements: + +---- +ALTER TABLE Sessions + ADD FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE; +ALTER TABLE PackageDepends + ADD FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE, + ADD FOREIGN KEY (DepPkgID) REFERENCES Packages(ID) ON DELETE CASCADE; +ALTER TABLE PackageSources + ADD FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE; +ALTER TABLE TU_VoteInfo + ADD FOREIGN KEY (SubmitterID) REFERENCES Users(ID) ON DELETE CASCADE; +ALTER TABLE TU_Votes + ADD FOREIGN KEY (VoteID) REFERENCES TU_VoteInfo(ID) ON DELETE CASCADE, + ADD FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE; +---- + From 1.7.0 to 1.8.0 ------------------- diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql index 3735b57..fbed3a4 100644 --- a/support/schema/aur-schema.sql +++ b/support/schema/aur-schema.sql @@ -55,7 +55,7 @@ CREATE TABLE Sessions ( UsersID INTEGER UNSIGNED NOT NULL, SessionID CHAR(32) NOT NULL, LastUpdateTS BIGINT UNSIGNED NOT NULL, - FOREIGN KEY (UsersID) REFERENCES Users(ID), + FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE, UNIQUE (SessionID) ); @@ -125,7 +125,9 @@ CREATE TABLE PackageDepends ( PackageID INTEGER UNSIGNED NOT NULL, DepPkgID INTEGER UNSIGNED NOT NULL, DepCondition VARCHAR(20), - INDEX (PackageID) + INDEX (PackageID), + FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE, + FOREIGN KEY (DepPkgID) REFERENCES Packages(ID) ON DELETE CASCADE ); @@ -134,7 +136,8 @@ CREATE TABLE PackageDepends ( CREATE TABLE PackageSources ( PackageID INTEGER UNSIGNED NOT NULL, Source VARCHAR(255) NOT NULL DEFAULT "/dev/null", - INDEX (PackageID) + INDEX (PackageID), + FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE ); @@ -198,12 +201,15 @@ CREATE TABLE IF NOT EXISTS TU_VoteInfo ( Yes tinyint(3) unsigned NOT NULL default '0', No tinyint(3) unsigned NOT NULL default '0', Abstain tinyint(3) unsigned NOT NULL default '0', - PRIMARY KEY (ID) + PRIMARY KEY (ID), + FOREIGN KEY (SubmitterID) REFERENCES Users(ID) ON DELETE CASCADE ); -- Individual vote records -- CREATE TABLE IF NOT EXISTS TU_Votes ( VoteID int(10) unsigned NOT NULL, - UserID 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 ); |