Add provisional support for package bases

This adds a PackageBases table to the database schema and moves the
following fields from the Packages table to PackageBases:

* CategoryID
* NumVotes
* OutOfDateTS
* SubmittedTS
* ModifiedTS
* SubmitterUID
* MaintainerUID

It also fixes all database accesses to comply with the new layout.

Having a separate PackageBases table is the first step to split package
support. By now, we create one PackageBases entry per package (where the
package base has the same name as the corresponding package). When
adding full support for split packages later, the package base name will
be derived from the pkgbase variable and a single package base will be
shared amongst all packages built from one source package.

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

2 files changed, 32 insertions, 16 deletions

diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql
index 932c1d3..a885b7a 100644
--- a/schema/aur-schema.sql
+++ b/schema/aur-schema.sql
@@ -92,16 +92,12 @@ INSERT INTO PackageCategories (Category) VALUES ('xfce');
 INSERT INTO PackageCategories (Category) VALUES ('fonts');
 
 
--- Information about the actual packages
+-- Information on package bases
 --
-CREATE TABLE Packages (
+CREATE TABLE PackageBases (
 	ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 	Name VARCHAR(64) NOT NULL,
-	Version VARCHAR(32) NOT NULL DEFAULT '',
 	CategoryID TINYINT UNSIGNED NOT NULL DEFAULT 1,
-	Description VARCHAR(255) NOT NULL DEFAULT "An Arch Package",
-	URL VARCHAR(255) NOT NULL DEFAULT "https://www.archlinux.org",
-	License VARCHAR(40) NOT NULL DEFAULT '',
 	NumVotes INTEGER UNSIGNED NOT NULL DEFAULT 0,
 	OutOfDateTS BIGINT UNSIGNED NULL DEFAULT NULL,
 	SubmittedTS BIGINT UNSIGNED NOT NULL,
@@ -121,6 +117,22 @@ CREATE TABLE Packages (
 ) ENGINE = InnoDB;
 
 
+-- Information about the actual packages
+--
+CREATE TABLE Packages (
+	ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+	PackageBaseID INTEGER UNSIGNED NOT NULL,
+	Name VARCHAR(64) NOT NULL,
+	Version VARCHAR(32) NOT NULL DEFAULT '',
+	Description VARCHAR(255) NOT NULL DEFAULT "An Arch Package",
+	URL VARCHAR(255) NOT NULL DEFAULT "https://www.archlinux.org",
+	License VARCHAR(40) NOT NULL DEFAULT '',
+	PRIMARY KEY (ID),
+	UNIQUE (Name),
+	FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
+) ENGINE = InnoDB;
+
+
 -- Track which dependencies a package has
 --
 CREATE TABLE PackageDepends (
@@ -147,13 +159,13 @@ CREATE TABLE PackageSources (
 --
 CREATE TABLE PackageVotes (
 	UsersID INTEGER UNSIGNED NOT NULL,
-	PackageID INTEGER UNSIGNED NOT NULL,
+	PackageBaseID INTEGER UNSIGNED NOT NULL,
 	INDEX (UsersID),
-	INDEX (PackageID),
+	INDEX (PackageBaseID),
 	FOREIGN KEY (UsersID) 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;
-CREATE UNIQUE INDEX VoteUsersIDPackageID ON PackageVotes (UsersID, PackageID);
+CREATE UNIQUE INDEX VoteUsersIDPackageID ON PackageVotes (UsersID, PackageBaseID);
 
 -- Record comments for packages
 --
diff --git a/schema/gendummydata.py b/schema/gendummydata.py
index 361d1f9..c2cb388 100755
--- a/schema/gendummydata.py
+++ b/schema/gendummydata.py
@@ -199,12 +199,16 @@ for p in list(seen_pkgs.keys()):
 
 	uuid = genUID() # the submitter/user
 
-	s = ("INSERT INTO Packages (ID, Name, Version, CategoryID,"
-		 " SubmittedTS, SubmitterUID, MaintainerUID) VALUES "
-		 " (%d, '%s', '%s', %d, %d, %d, %s);\n")
-	s = s % (seen_pkgs[p], p, genVersion(), genCategory(), NOW, uuid, muid)
+	s = ("INSERT INTO PackageBases (ID, Name, CategoryID, SubmittedTS, "
+         "SubmitterUID, MaintainerUID) VALUES (%d, '%s', %d, %d, %d, %s);\n")
+	s = s % (seen_pkgs[p], p, genCategory(), NOW, uuid, muid)
+	out.write(s)
 
+	s = ("INSERT INTO Packages (ID, PackageBaseID, Name, Version) VALUES "
+         "(%d, %d, '%s', '%s');\n")
+	s = s % (seen_pkgs[p], seen_pkgs[p], p, genVersion())
 	out.write(s)
+
 	count += 1
 
 	# create random comments for this package
@@ -228,7 +232,7 @@ for u in user_keys:
 	for v in range(num_votes):
 		pkg = random.randrange(1, len(seen_pkgs) + 1)
 		if pkg not in pkgvote:
-			s = ("INSERT INTO PackageVotes (UsersID, PackageID)"
+			s = ("INSERT INTO PackageVotes (UsersID, PackageBaseID)"
 				 " VALUES (%d, %d);\n")
 			s = s % (seen_users[u], pkg)
 			pkgvote[pkg] = 1
@@ -240,7 +244,7 @@ for u in user_keys:
 # Update statements for package votes
 #
 for p in list(track_votes.keys()):
-	s = "UPDATE Packages SET NumVotes = %d WHERE ID = %d;\n"
+	s = "UPDATE PackageBases SET NumVotes = %d WHERE ID = %d;\n"
 	s = s % (track_votes[p], p)
 	out.write(s)