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 --- web/lib/aurjson.class.php | 11 +++-- web/lib/credentials.inc.php | 4 +- web/lib/pkgbasefuncs.inc.php | 110 +++++++++++++++++-------------------------- web/lib/pkgfuncs.inc.php | 34 ++++--------- 4 files changed, 63 insertions(+), 96 deletions(-) (limited to 'web/lib') 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; -- cgit v1.2.3-70-g09d2