Store comments on a per-package base basis

Move comments from the Packages table to PackageBases. Sharing comments
makes sense since they almost always refer to a source package.

Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>

2 files changed, 7 insertions, 7 deletions

diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql
index a885b7a..9426a61 100644
--- a/schema/aur-schema.sql
+++ b/schema/aur-schema.sql
@@ -171,28 +171,28 @@ CREATE UNIQUE INDEX VoteUsersIDPackageID ON PackageVotes (UsersID, PackageBaseID
 --
 CREATE TABLE PackageComments (
 	ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
-	PackageID INTEGER UNSIGNED NOT NULL,
+	PackageBaseID 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),
+	INDEX (PackageBaseID),
 	FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE SET NULL,
 	FOREIGN KEY (DelUsersID) REFERENCES Users(ID) ON DELETE CASCADE,
-	FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE
+	FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
 ) ENGINE = InnoDB;
 
 -- Comment addition notifications
 --
 CREATE TABLE CommentNotify (
-	PkgID INTEGER UNSIGNED NOT NULL,
+	PackageBaseID INTEGER UNSIGNED NOT NULL,
 	UserID INTEGER UNSIGNED NOT NULL,
-	FOREIGN KEY (PkgID) REFERENCES Packages(ID) ON DELETE CASCADE,
+	FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE,
 	FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE
 ) ENGINE = InnoDB;
-CREATE UNIQUE INDEX NotifyUserIDPkgID ON CommentNotify (UserID, PkgID);
+CREATE UNIQUE INDEX NotifyUserIDPkgID ON CommentNotify (UserID, PackageBaseID);
 
 -- Package name blacklist
 --
diff --git a/schema/gendummydata.py b/schema/gendummydata.py
index c2cb388..bc0ede8 100755
--- a/schema/gendummydata.py
+++ b/schema/gendummydata.py
@@ -216,7 +216,7 @@ for p in list(seen_pkgs.keys()):
 	num_comments = random.randrange(PKG_CMNTS[0], PKG_CMNTS[1])
 	for i in range(0, num_comments):
 		now = NOW + random.randrange(400, 86400*3)
-		s = ("INSERT INTO PackageComments (PackageID, UsersID,"
+		s = ("INSERT INTO PackageComments (PackageBaseID, UsersID,"
 			 " Comments, CommentTS) VALUES (%d, %d, '%s', %d);\n")
 		s = s % (seen_pkgs[p], genUID(), genFortune(), now)
 		out.write(s)