diff options
Diffstat (limited to 'schema')
| -rw-r--r-- | schema/aur-schema.sql | 32 | ||||
| -rwxr-xr-x | schema/gendummydata.py | 16 |
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) |