From d35cf67f7ba24a6c8f6c27f6f016b6f28c8e9f9b Mon Sep 17 00:00:00 2001 From: Lukas Fleischer Date: Fri, 4 Apr 2014 21:57:01 +0200 Subject: 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 --- schema/aur-schema.sql | 12 ++++++------ schema/gendummydata.py | 2 +- 2 files changed, 7 insertions(+), 7 deletions(-) (limited to 'schema') 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) -- cgit v1.2.3-54-g00ecf