From 5fb7a74e23b2059ec0c1acb72d8d804adbf05c52 Mon Sep 17 00:00:00 2001
From: Lukas Fleischer <lfleischer@archlinux.org>
Date: Sat, 13 Jun 2015 15:27:28 +0200
Subject: Replace categories with keywords

Remove package base categories. Instead, users can now specify up to
twenty custom keywords that are taken into consideration when searching.

Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
---
 schema/aur-schema.sql | 44 ++++++++++----------------------------------
 1 file changed, 10 insertions(+), 34 deletions(-)

(limited to 'schema/aur-schema.sql')

diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql
index 1935cf0..9ac5d44 100644
--- a/schema/aur-schema.sql
+++ b/schema/aur-schema.sql
@@ -64,43 +64,11 @@ CREATE TABLE Sessions (
 ) ENGINE = InnoDB;
 
 
--- Categories for grouping packages when they reside in
--- Unsupported or the AUR - based on the categories defined
--- in 'extra'.
---
-CREATE TABLE PackageCategories (
-	ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
-	Category VARCHAR(32) NOT NULL,
-	PRIMARY KEY (ID)
-) ENGINE = InnoDB;
-INSERT INTO PackageCategories (Category) VALUES ('none');
-INSERT INTO PackageCategories (Category) VALUES ('daemons');
-INSERT INTO PackageCategories (Category) VALUES ('devel');
-INSERT INTO PackageCategories (Category) VALUES ('editors');
-INSERT INTO PackageCategories (Category) VALUES ('emulators');
-INSERT INTO PackageCategories (Category) VALUES ('games');
-INSERT INTO PackageCategories (Category) VALUES ('gnome');
-INSERT INTO PackageCategories (Category) VALUES ('i18n');
-INSERT INTO PackageCategories (Category) VALUES ('kde');
-INSERT INTO PackageCategories (Category) VALUES ('lib');
-INSERT INTO PackageCategories (Category) VALUES ('modules');
-INSERT INTO PackageCategories (Category) VALUES ('multimedia');
-INSERT INTO PackageCategories (Category) VALUES ('network');
-INSERT INTO PackageCategories (Category) VALUES ('office');
-INSERT INTO PackageCategories (Category) VALUES ('science');
-INSERT INTO PackageCategories (Category) VALUES ('system');
-INSERT INTO PackageCategories (Category) VALUES ('x11');
-INSERT INTO PackageCategories (Category) VALUES ('xfce');
-INSERT INTO PackageCategories (Category) VALUES ('fonts');
-INSERT INTO PackageCategories (Category) VALUES ('wayland');
-
-
 -- Information on package bases
 --
 CREATE TABLE PackageBases (
 	ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 	Name VARCHAR(255) NOT NULL,
-	CategoryID TINYINT UNSIGNED NOT NULL DEFAULT 1,
 	NumVotes INTEGER UNSIGNED NOT NULL DEFAULT 0,
 	Popularity DECIMAL(6,2) UNSIGNED NOT NULL DEFAULT 0,
 	OutOfDateTS BIGINT UNSIGNED NULL DEFAULT NULL,
@@ -111,12 +79,10 @@ CREATE TABLE PackageBases (
 	PackagerUID INTEGER UNSIGNED NULL DEFAULT NULL,      -- Last packager
 	PRIMARY KEY (ID),
 	UNIQUE (Name),
-	INDEX (CategoryID),
 	INDEX (NumVotes),
 	INDEX (SubmitterUID),
 	INDEX (MaintainerUID),
 	INDEX (PackagerUID),
-	FOREIGN KEY (CategoryID) REFERENCES PackageCategories(ID) ON DELETE NO ACTION,
 	-- deleting a user will cause packages to be orphaned, not deleted
 	FOREIGN KEY (SubmitterUID) REFERENCES Users(ID) ON DELETE SET NULL,
 	FOREIGN KEY (MaintainerUID) REFERENCES Users(ID) ON DELETE SET NULL,
@@ -124,6 +90,16 @@ CREATE TABLE PackageBases (
 ) ENGINE = InnoDB;
 
 
+-- Keywords of package bases
+--
+CREATE TABLE PackageKeywords (
+	PackageBaseID INTEGER UNSIGNED NOT NULL,
+	Keyword VARCHAR(255) NOT NULL DEFAULT '',
+	PRIMARY KEY (PackageBaseID, Keyword),
+	FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
+) ENGINE = InnoDB;
+
+
 -- Information about the actual packages
 --
 CREATE TABLE Packages (
-- 
cgit v1.3.1

