From 5fb7a74e23b2059ec0c1acb72d8d804adbf05c52 Mon Sep 17 00:00:00 2001 From: Lukas Fleischer 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 --- schema/aur-schema.sql | 44 ++++----------- upgrading/4.0.0.txt | 22 +++++++- web/html/pkgbase.php | 4 +- web/lib/aurjson.class.php | 11 ++-- web/lib/credentials.inc.php | 4 +- web/lib/pkgbasefuncs.inc.php | 110 +++++++++++++++--------------------- web/lib/pkgfuncs.inc.php | 34 ++++------- web/template/pkg_details.php | 25 +++----- web/template/pkg_search_form.php | 14 ----- web/template/pkg_search_results.php | 2 - web/template/pkgbase_details.php | 25 +++----- 11 files changed, 112 insertions(+), 183 deletions(-) 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 ( diff --git a/upgrading/4.0.0.txt b/upgrading/4.0.0.txt index aa1e535..fd04c44 100644 --- a/upgrading/4.0.0.txt +++ b/upgrading/4.0.0.txt @@ -38,4 +38,24 @@ ALTER TABLE PackageBases ADD COLUMN Popularity DECIMAL(6,2) UNSIGNED NOT NULL DEFAULT 0; ---- -6. (optional) Setup cgit to browse the Git repositories via HTTP. +6. Drop the category ID foreign key from the PackageBases table: + +`ALTER TABLE PackageBases DROP FOREIGN KEY PackageBases_ibfk_1;` should +work in most cases. Otherwise, check the output of `SHOW CREATE TABLE +PackageBases;` and use the foreign key name shown there. + +7. Replace the package base categories with keywords: + +---- +ALTER TABLE PackageBases DROP COLUMN CategoryID; +DROP TABLE PackageCategories; + +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; +---- + +8. (optional) Setup cgit to browse the Git repositories via HTTP. diff --git a/web/html/pkgbase.php b/web/html/pkgbase.php index c834f8b..5179d0c 100644 --- a/web/html/pkgbase.php +++ b/web/html/pkgbase.php @@ -96,8 +96,8 @@ if (check_token()) { list($ret, $output) = pkgbase_notify($ids, false); } elseif (current_action("do_DeleteComment")) { list($ret, $output) = pkgbase_delete_comment(); - } elseif (current_action("do_ChangeCategory")) { - list($ret, $output) = pkgbase_change_category($base_id); + } elseif (current_action("do_SetKeywords")) { + list($ret, $output) = pkgbase_set_keywords($base_id, preg_split("/[\s,;]+/", $_POST['keywords'], -1, PREG_SPLIT_NO_EMPTY)); } elseif (current_action("do_FileRequest")) { list($ret, $output) = pkgreq_file($ids, $_POST['type'], $_POST['merge_into'], $_POST['comments']); } elseif (current_action("do_CloseRequest")) { diff --git a/web/lib/aurjson.class.php b/web/lib/aurjson.class.php index f25954c..c896b04 100644 --- a/web/lib/aurjson.class.php +++ b/web/lib/aurjson.class.php @@ -19,7 +19,7 @@ class AurJSON { private static $fields_v1 = array( 'Packages.ID', 'Packages.Name', 'PackageBases.ID AS PackageBaseID', - 'PackageBases.Name AS PackageBase', 'Version', 'CategoryID', + 'PackageBases.Name AS PackageBase', 'Version', 'Description', 'URL', 'NumVotes', 'OutOfDateTS AS OutOfDate', 'Users.UserName AS Maintainer', 'SubmittedTS AS FirstSubmitted', 'ModifiedTS AS LastModified', @@ -28,13 +28,13 @@ class AurJSON { private static $fields_v2 = array( 'Packages.ID', 'Packages.Name', 'PackageBases.ID AS PackageBaseID', - 'PackageBases.Name AS PackageBase', 'Version', 'CategoryID', + 'PackageBases.Name AS PackageBase', 'Version', 'Description', 'URL', 'NumVotes', 'OutOfDateTS AS OutOfDate', 'Users.UserName AS Maintainer', 'SubmittedTS AS FirstSubmitted', 'ModifiedTS AS LastModified' ); private static $numeric_fields = array( - 'ID', 'PackageBaseID', 'CategoryID', 'NumVotes', 'OutOfDate', + 'ID', 'PackageBaseID', 'NumVotes', 'OutOfDate', 'FirstSubmitted', 'LastModified' ); @@ -62,7 +62,7 @@ class AurJSON { if (isset($http_data['v'])) { $this->version = intval($http_data['v']); } - if ($this->version < 1 || $this->version > 3) { + if ($this->version < 1 || $this->version > 4) { return $this->json_error('Invalid version specified.'); } @@ -229,6 +229,9 @@ class AurJSON { while ($row = $result->fetch(PDO::FETCH_ASSOC)) { $resultcount++; $row['URLPath'] = sprintf(config_get('options', 'snapshot_uri'), urlencode($row['PackageBase'])); + if ($this->version < 4) { + $row['CategoryID'] = 1; + } /* * Unfortunately, mysql_fetch_assoc() returns diff --git a/web/lib/credentials.inc.php b/web/lib/credentials.inc.php index b813b90..614f2d9 100644 --- a/web/lib/credentials.inc.php +++ b/web/lib/credentials.inc.php @@ -8,7 +8,7 @@ define("CRED_ACCOUNT_SEARCH", 5); define("CRED_COMMENT_DELETE", 6); define("CRED_COMMENT_VIEW_DELETED", 22); define("CRED_PKGBASE_ADOPT", 7); -define("CRED_PKGBASE_CHANGE_CATEGORY", 8); +define("CRED_PKGBASE_SET_KEYWORDS", 8); define("CRED_PKGBASE_DELETE", 9); define("CRED_PKGBASE_DISOWN", 10); define("CRED_PKGBASE_EDIT_COMAINTAINERS", 24); @@ -60,7 +60,7 @@ function has_credential($credential, $approved_users=array()) { case CRED_COMMENT_DELETE: case CRED_COMMENT_VIEW_DELETED: case CRED_PKGBASE_ADOPT: - case CRED_PKGBASE_CHANGE_CATEGORY: + case CRED_PKGBASE_SET_KEYWORDS: case CRED_PKGBASE_DELETE: case CRED_PKGBASE_EDIT_COMAINTAINERS: case CRED_PKGBASE_DISOWN: diff --git a/web/lib/pkgbasefuncs.inc.php b/web/lib/pkgbasefuncs.inc.php index 327b7f9..c8c99eb 100644 --- a/web/lib/pkgbasefuncs.inc.php +++ b/web/lib/pkgbasefuncs.inc.php @@ -2,25 +2,6 @@ include_once("pkgreqfuncs.inc.php"); -/** - * Get all package categories stored in the database - * - * @param \PDO An already established database connection - * - * @return array All package categories - */ -function pkgbase_categories() { - $dbh = DB::connect(); - $q = "SELECT * FROM PackageCategories WHERE ID != 1 "; - $q.= "ORDER BY Category ASC"; - $result = $dbh->query($q); - if (!$result) { - return null; - } - - return $result->fetchAll(PDO::FETCH_KEY_PAIR); -} - /** * Get the number of non-deleted comments for a specific package base * @@ -186,17 +167,15 @@ function pkgbase_get_details($base_id) { $dbh = DB::connect(); $q = "SELECT PackageBases.ID, PackageBases.Name, "; - $q.= "PackageBases.CategoryID, PackageBases.NumVotes, "; + $q.= "PackageBases.NumVotes, "; $q.= "PackageBases.OutOfDateTS, PackageBases.SubmittedTS, "; $q.= "PackageBases.ModifiedTS, PackageBases.SubmitterUID, "; $q.= "PackageBases.MaintainerUID, PackageBases.PackagerUID, "; - $q.= "PackageCategories.Category, "; $q.= "(SELECT COUNT(*) FROM PackageRequests "; $q.= " WHERE PackageRequests.PackageBaseID = PackageBases.ID "; $q.= " AND PackageRequests.Status = 0) AS RequestCount "; - $q.= "FROM PackageBases, PackageCategories "; - $q.= "WHERE PackageBases.CategoryID = PackageCategories.ID "; - $q.= "AND PackageBases.ID = " . intval($base_id); + $q.= "FROM PackageBases "; + $q.= "WHERE PackageBases.ID = " . intval($base_id); $result = $dbh->query($q); $row = array(); @@ -933,63 +912,62 @@ function pkgbase_delete_comment() { } /** - * Change package base category + * Get a list of package base keywords * - * @param int Package base ID of the package base to modify + * @param int $base_id The package base ID to retrieve the keywords for * - * @return array Tuple of success/failure indicator and error message + * @return array An array of keywords */ -function pkgbase_change_category($base_id) { - $uid = uid_from_sid($_COOKIE["AURSID"]); - if (!$uid) { - return array(false, __("You must be logged in before you can edit package information.")); - } - - if (isset($_POST["category_id"])) { - $category_id = $_POST["category_id"]; - } else { - return array(false, __("Missing category ID.")); - } - +function pkgbase_get_keywords($base_id) { $dbh = DB::connect(); - $catArray = pkgbase_categories($dbh); - if (!array_key_exists($category_id, $catArray)) { - return array(false, __("Invalid category ID.")); - } - - $base_id = intval($base_id); - - /* Verify package ownership. */ - $q = "SELECT MaintainerUID FROM PackageBases WHERE ID = " . $base_id; + $q = "SELECT Keyword FROM PackageKeywords "; + $q .= "WHERE PackageBaseID = " . intval($base_id) . " "; + $q .= "ORDER BY Keyword ASC"; $result = $dbh->query($q); - if ($result) { - $row = $result->fetch(PDO::FETCH_ASSOC); - } - if (!$result || !has_credential(CRED_PKGBASE_CHANGE_CATEGORY, array($row["MaintainerUID"]))) { - return array(false, __("You are not allowed to change this package category.")); + if ($result) { + return $result->fetchAll(PDO::FETCH_COLUMN, 0); + } else { + return array(); } - - $q = "UPDATE PackageBases "; - $q.= "SET CategoryID = ".intval($category_id)." "; - $q.= "WHERE ID = ".intval($base_id); - $dbh->exec($q); - return array(true, __("Package category changed.")); } /** - * Change the category a package base belongs to + * Update the list of keywords of a package base * - * @param int $base_id The package base ID to change the category for - * @param int $category_id The new category ID for the package + * @param int $base_id The package base ID to update the keywords of + * @param array $users Array of keywords * - * @return void + * @return array Tuple of success/failure indicator and error message */ -function pkgbase_update_category($base_id, $category_id) { +function pkgbase_set_keywords($base_id, $keywords) { + $base_id = intval($base_id); + + if (!has_credential(CRED_PKGBASE_SET_KEYWORDS, array(pkgbase_maintainer_uid($base_id)))) { + return array(false, __("You are not allowed to edit the keywords of this package base.")); + } + + /* Remove empty and duplicate user names. */ + $keywords = array_unique(array_filter(array_map('trim', $keywords))); + $dbh = DB::connect(); - $q = sprintf("UPDATE PackageBases SET CategoryID = %d WHERE ID = %d", - $category_id, $base_id); + + $q = sprintf("DELETE FROM PackageKeywords WHERE PackageBaseID = %d", $base_id); $dbh->exec($q); + + $i = 0; + foreach ($keywords as $keyword) { + $q = sprintf("INSERT INTO PackageKeywords (PackageBaseID, Keyword) VALUES (%d, %s)", $base_id, $dbh->quote($keyword)); + var_dump($q); + $dbh->exec($q); + + $i++; + if ($i >= 20) { + break; + } + } + + return array(true, __("The package base keywords have been updated.")); } /** diff --git a/web/lib/pkgfuncs.inc.php b/web/lib/pkgfuncs.inc.php index f527578..35b0e4e 100644 --- a/web/lib/pkgfuncs.inc.php +++ b/web/lib/pkgfuncs.inc.php @@ -400,17 +400,16 @@ function pkg_get_details($id=0) { $dbh = DB::connect(); $q = "SELECT Packages.*, PackageBases.ID AS BaseID, "; - $q.= "PackageBases.Name AS BaseName, PackageBases.CategoryID, "; + $q.= "PackageBases.Name AS BaseName, "; $q.= "PackageBases.NumVotes, PackageBases.OutOfDateTS, "; $q.= "PackageBases.SubmittedTS, PackageBases.ModifiedTS, "; $q.= "PackageBases.SubmitterUID, PackageBases.MaintainerUID, "; - $q.= "PackageBases.PackagerUID, PackageCategories.Category, "; + $q.= "PackageBases.PackagerUID, "; $q.= "(SELECT COUNT(*) FROM PackageRequests "; $q.= " WHERE PackageRequests.PackageBaseID = Packages.PackageBaseID "; $q.= " AND PackageRequests.Status = 0) AS RequestCount "; - $q.= "FROM Packages, PackageBases, PackageCategories "; + $q.= "FROM Packages, PackageBases "; $q.= "WHERE PackageBases.ID = Packages.PackageBaseID "; - $q.= "AND PackageBases.CategoryID = PackageCategories.ID "; $q.= "AND Packages.ID = " . intval($id); $result = $dbh->query($q); @@ -475,14 +474,12 @@ function pkg_display_details($id=0, $row, $SID="") { * request vars: * O - starting result number * PP - number of search hits per page - * C - package category ID number * K - package search string * SO - search hit sort order: * values: a - ascending * d - descending * SB - sort search hits by: - * values: c - package category - * n - package name + * values: n - package name * v - number of votes * m - maintainer username * SeB- property that search string (K) represents @@ -516,7 +513,6 @@ function pkg_search_page($SID="") { */ if ($SID) $myuid = uid_from_sid($SID); - $cats = pkgbase_categories($dbh); /* Sanitize paging variables. */ if (isset($_GET['O'])) { @@ -543,16 +539,13 @@ function pkg_search_page($SID="") { PackageVotes.UsersID AS Voted, "; } $q_select .= "Users.Username AS Maintainer, - PackageCategories.Category, Packages.Name, Packages.Version, Packages.Description, PackageBases.NumVotes, PackageBases.Popularity, Packages.ID, Packages.PackageBaseID, PackageBases.OutOfDateTS "; $q_from = "FROM Packages LEFT JOIN PackageBases ON (PackageBases.ID = Packages.PackageBaseID) - LEFT JOIN Users ON (PackageBases.MaintainerUID = Users.ID) - LEFT JOIN PackageCategories - ON (PackageBases.CategoryID = PackageCategories.ID) "; + LEFT JOIN Users ON (PackageBases.MaintainerUID = Users.ID) "; if ($SID) { /* This is not needed for the total row count query. */ $q_from_extra = "LEFT JOIN PackageVotes @@ -564,13 +557,6 @@ function pkg_search_page($SID="") { } $q_where = 'WHERE PackageBases.PackagerUID IS NOT NULL '; - /* - * TODO: Possibly do string matching on category to make request - * variable values more sensible. - */ - if (isset($_GET["C"]) && intval($_GET["C"])) { - $q_where .= "AND PackageBases.CategoryID = ".intval($_GET["C"])." "; - } if (isset($_GET['K'])) { if (isset($_GET["SeB"]) && $_GET["SeB"] == "m") { @@ -600,7 +586,7 @@ function pkg_search_page($SID="") { $q_where .= "AND (PackageBases.Name = " . $dbh->quote($_GET['K']) . ") "; } else { - /* Search by name and description (default). */ + /* Keyword search (default). */ $count = 0; $q_keywords = ""; $op = ""; @@ -624,7 +610,10 @@ function pkg_search_page($SID="") { $term = "%" . addcslashes($term, '%_') . "%"; $q_keywords .= $op . " (Packages.Name LIKE " . $dbh->quote($term) . " OR "; - $q_keywords .= "Description LIKE " . $dbh->quote($term) . ") "; + $q_keywords .= "Description LIKE " . $dbh->quote($term) . " OR "; + $q_keywords .= "EXISTS (SELECT * FROM PackageKeywords WHERE "; + $q_keywords .= "PackageKeywords.PackageBaseID = Packages.PackageBaseID AND "; + $q_keywords .= "PackageKeywords.Keyword LIKE " . $dbh->quote($term) . ")) "; $count++; if ($count >= 20) { @@ -657,9 +646,6 @@ function pkg_search_page($SID="") { $q_sort = "ORDER BY "; $sort_by = isset($_GET["SB"]) ? $_GET["SB"] : ''; switch ($sort_by) { - case 'c': - $q_sort .= "CategoryID " . $order . ", "; - break; case 'v': $q_sort .= "NumVotes " . $order . ", "; break; diff --git a/web/template/pkg_details.php b/web/template/pkg_details.php index e50f029..cc2f055 100644 --- a/web/template/pkg_details.php +++ b/web/template/pkg_details.php @@ -11,7 +11,7 @@ $uid = uid_from_sid($SID); $pkgid = intval($row['ID']); $base_id = intval($row['BaseID']); -$catarr = pkgbase_categories(); +$keywords = pkgbase_get_keywords($base_id); $submitter = username_from_id($row["SubmitterUID"]); $maintainer = username_from_id($row["MaintainerUID"]); @@ -188,34 +188,25 @@ $sources = pkg_sources($row["ID"]); - - + +
- + - - + "/> +
- - - - + 0): ?> diff --git a/web/template/pkg_search_form.php b/web/template/pkg_search_form.php index 683675b..2d03cfa 100644 --- a/web/template/pkg_search_form.php +++ b/web/template/pkg_search_form.php @@ -19,7 +19,6 @@ $outdated_flags = array( $sortby = array( 'n' => __('Name'), - 'c' => __('Category'), 'v' => __('Votes'), 'p' => __('Popularity'), 'w' => __('Voted'), @@ -44,19 +43,6 @@ $per_page = array(50, 100, 250);
-
- - -
]" value="1" /> - "> class="flagged"> diff --git a/web/template/pkgbase_details.php b/web/template/pkgbase_details.php index 2ef8788..2522f34 100644 --- a/web/template/pkgbase_details.php +++ b/web/template/pkgbase_details.php @@ -10,7 +10,7 @@ $uid = uid_from_sid($SID); $base_id = intval($row['ID']); -$catarr = pkgbase_categories(); +$keywords = pkgbase_get_keywords($base_id); $submitter = username_from_id($row["SubmitterUID"]); $maintainer = username_from_id($row["MaintainerUID"]); @@ -127,34 +127,25 @@ $pkgs = pkgbase_get_pkgnames($base_id); - - + +
- + - - + "/> +
- - - - + -- cgit v1.2.3-54-g00ecf