Commit: ad8bc2d5ab343733e57622d0ca063ce96570509d Author: Peter Kokot <peterko...@gmail.com> Wed, 19 Dec 2018 01:47:19 +0100 Parents: 11fd2afda1fdbb0e0365886cd9cafc7b74dd525f Branches: master
Link: http://git.php.net/?p=web/bugs.git;a=commitdiff;h=ad8bc2d5ab343733e57622d0ca063ce96570509d Log: Move bugdb queries to repository Changed paths: M scripts/cron/email-assigned M scripts/cron/no-feedback M src/Repository/BugRepository.php M www/index.php M www/stats.php M www/vote.php
diff --git a/scripts/cron/email-assigned b/scripts/cron/email-assigned index c1977d4..60456a6 100755 --- a/scripts/cron/email-assigned +++ b/scripts/cron/email-assigned @@ -1,6 +1,8 @@ #!/usr/bin/env php <?php /* vim: set ft=phpbugdb noet ts=4 sw=4 : */ +use App\Repository\BugRepository; + require __DIR__ . '/../../include/prepend.php'; /* @@ -8,20 +10,7 @@ require __DIR__ . '/../../include/prepend.php'; is a little odd that way. 'No Feedback' was once a part of this, but no longer: https://news.php.net/php.webmaster/8828 */ -$sql = "SELECT id, package_name, bug_type, sdesc, status, assign, UNIX_TIMESTAMP(ts1) AS ts_opened, UNIX_TIMESTAMP(ts2) AS ts_changed - FROM `bugdb` - WHERE length(assign) > 1 - AND status IN ('Assigned', 'Open', 'Re-Opened', 'Feedback', 'Analyzed', 'Verified', 'Critical', 'Suspended') - ORDER BY id"; - -$res = $dbh->query($sql); - -// Gather up the data -while ($row = $res->fetch()) { - $data[$row['assign']][] = $row; -} - -foreach ($data as $assigned => $binfos) { +foreach ((new BugRepository($dbh))->findAllAssigned() as $assigned => $binfos) { $mbody = format_email_body($binfos); $email_user = $assigned . '@php.net'; diff --git a/scripts/cron/no-feedback b/scripts/cron/no-feedback index 55fb7d6..653ab8d 100755 --- a/scripts/cron/no-feedback +++ b/scripts/cron/no-feedback @@ -3,13 +3,11 @@ # this script closes bugs due to lack of feedback. +use App\Repository\BugRepository; use App\Repository\ReasonRepository; require __DIR__.'/../../include/prepend.php'; -# date interval to close after -$after = "7 DAY"; - # Set "input" array $in = ['status' => 'No Feedback']; @@ -18,15 +16,7 @@ $reasonRepository = new ReasonRepository($dbh); list($RESOLVE_REASONS, $FIX_VARIATIONS) = $reasonRepository->findByProject($site); -$res = $dbh->prepare(" - SELECT id, package_name, bug_type, email, passwd, sdesc, ldesc, php_version, - php_os, status, ts1, ts2, assign, UNIX_TIMESTAMP(ts1) AS submitted, - private, reporter_name, UNIX_TIMESTAMP(ts2) AS modified - FROM bugdb - WHERE status = 'Feedback' AND ts2 < DATE_SUB(NOW(), INTERVAL {$after}) -")->execute([]); - -while ($bug = $res->fetch()) +foreach ((new BugRepository($dbh))->findAllWithoutFeedback() as $bug) { list($mailto, $mailfrom, $bcc, $params) = get_package_mail($bug['package_name'], false, $bug['bug_type']); diff --git a/src/Repository/BugRepository.php b/src/Repository/BugRepository.php index 66ac511..5ef08f3 100644 --- a/src/Repository/BugRepository.php +++ b/src/Repository/BugRepository.php @@ -14,6 +14,11 @@ class BugRepository private $dbh; /** + * Days when bugs with no feedback get closed. + */ + private const FEEDBACK_PERIOD = 7; + + /** * Class constructor. */ public function __construct(\PDO $dbh) @@ -48,4 +53,125 @@ class BugRepository return $statement->fetch(); } + + /** + * Find random bug to resolve for a contributor. + */ + public function findRandom(): array + { + $sql = "SELECT id + FROM bugdb + WHERE status NOT IN('Closed', 'Not a bug', 'Duplicate', 'Spam', 'Wont fix', 'No Feedback') + AND private = 'N' + ORDER BY RAND() LIMIT 1 + "; + + $statement = $this->dbh->prepare($sql); + $statement->execute(); + + return $statement->fetch(\PDO::FETCH_NUM); + } + + /** + * Find all bugs that have someone assigned to them. + */ + public function findAllAssigned(): array + { + $sql = "SELECT id, package_name, bug_type, sdesc, status, assign, UNIX_TIMESTAMP(ts1) AS ts_opened, UNIX_TIMESTAMP(ts2) AS ts_changed + FROM `bugdb` + WHERE length(assign) > 1 + AND status IN ('Assigned', 'Open', 'Re-Opened', 'Feedback', 'Analyzed', 'Verified', 'Critical', 'Suspended') + ORDER BY id + "; + + $statement = $this->dbh->query($sql); + + $data = []; + + // Populate data with assign field as array key + while ($row = $statement->fetch()) { + $data[$row['assign']][] = $row; + } + + return $data; + } + + /** + * Find all bugs without feedback by given period time. + */ + public function findAllWithoutFeedback(int $feedbackPeriod = self::FEEDBACK_PERIOD): array + { + $sql = "SELECT id, package_name, bug_type, email, passwd, sdesc, ldesc, + php_version, php_os, status, ts1, ts2, assign, + UNIX_TIMESTAMP(ts1) AS submitted, private, reporter_name, + UNIX_TIMESTAMP(ts2) AS modified + FROM bugdb + WHERE status = 'Feedback' AND ts2 < DATE_SUB(NOW(), INTERVAL ? DAY) + "; + + $statement = $this->dbh->prepare($sql); + $statement->execute([$feedbackPeriod]); + + return $statement->fetchAll(); + } + + /** + * Find all bugs by given bug type. + */ + public function findAllByBugType(string $type = 'All'): array + { + $sql = 'SELECT b.package_name, b.status, COUNT(*) AS quant FROM bugdb AS b'; + + $arguments = []; + + if ($type !== 'All') { + $sql .= ' WHERE bug_type = ? '; + $arguments[] = $type; + } + + $sql .= ' GROUP BY b.package_name, b.status ORDER BY b.package_name, b.status'; + + $statement = $this->dbh->prepare($sql); + $statement->execute($arguments); + + return $statement->fetchAll(); + } + + /** + * Find bugs for grouping into PHP versions by given bug type. + */ + public function findPhpVersions(string $type = 'All'): array + { + $sql = "SELECT DATE_FORMAT(ts1, '%Y-%m') as d, + IF(b.php_version LIKE '%Git%', LEFT(b.php_version, LOCATE('Git', b.php_version)+2), b.php_version) AS formatted_version, + COUNT(*) AS quant + FROM bugdb AS b + WHERE ts1 >= CONCAT(YEAR(NOW())-1, '-', MONTH(NOW()), '-01 00:00:00') + "; + + $arguments = []; + + if ($type !== 'All') { + $sql .= ' AND bug_type = ? '; + $arguments[] = $type; + } + + $sql .= ' GROUP BY d, formatted_version ORDER BY d, quant'; + + $statement = $this->dbh->prepare($sql); + $statement->execute($arguments); + + return $statement->fetchAll(); + } + + /** + * Check if bug with given id exists. + */ + public function exists(int $id): bool + { + $statement = $this->dbh->prepare('SELECT 1 FROM bugdb WHERE id = ?'); + $statement->execute([$id]); + + return (bool)$statement->fetchColumn(); + } } diff --git a/www/index.php b/www/index.php index 40e56ac..03e4014 100644 --- a/www/index.php +++ b/www/index.php @@ -4,6 +4,8 @@ session_start(); /* The bug system home page */ +use App\Repository\BugRepository; + // Obtain common includes require_once '../include/prepend.php'; @@ -14,10 +16,7 @@ if ($id) { } if($_SERVER['REQUEST_URI'] == '/random') { - $query = "SELECT id FROM bugdb WHERE status NOT IN('Closed', 'Not a bug', 'Duplicate', 'Spam', 'Wont fix', 'No Feedback') AND private = 'N' ORDER BY RAND() LIMIT 1"; - - $result = $dbh->prepare($query)->execute(); - $id = $result->fetch(\PDO::FETCH_NUM); + $id = (new BugRepository($dbh))->findRandom(); redirect("bug.php?id={$id[0]}"); } diff --git a/www/stats.php b/www/stats.php index 1afc810..8cdd57a 100644 --- a/www/stats.php +++ b/www/stats.php @@ -1,5 +1,7 @@ <?php +use App\Repository\BugRepository; + session_start(); // Obtain common includes @@ -40,25 +42,10 @@ if (!array_key_exists($sort_by, $titles)) { $sort_by = 'Open'; } -$where = ''; -if (empty($_GET['bug_type']) || $_GET['bug_type'] == 'All') { - $bug_type = 'All'; -} else { - $bug_type = $_GET['bug_type']; - $where = ' AND bug_type = ' . $dbh->quote($bug_type); -} - -$query = " - SELECT b.package_name, b.status, COUNT(*) AS quant - FROM bugdb AS b - WHERE 1 = 1 {$where} - GROUP BY b.package_name, b.status - ORDER BY b.package_name, b.status -"; +$bug_type = $_GET['bug_type'] ?? 'All'; +$bugRepository = new BugRepository($dbh); -$result = $dbh->prepare($query)->execute(); - -while ($row = $result->fetch()) { +foreach ($bugRepository->findAllByBugType($bug_type) as $row) { $pkg_tmp[$row['status']][$row['package_name']] = $row['quant']; @$pkg_total[$row['package_name']] += $row['quant']; @$all[$row['status']] += $row['quant']; @@ -148,19 +135,8 @@ OUTPUT; echo "</table>\n<hr>\n<p><b>PHP Versions for recent bug reports:</b></p><div>"; -$query = " SELECT DATE_FORMAT(ts1, '%Y-%m') as d, - IF(b.php_version LIKE '%Git%', LEFT(b.php_version, LOCATE('Git', b.php_version)+2), b.php_version) AS formatted_version, - COUNT(*) AS quant - FROM bugdb AS b - WHERE ts1 >= CONCAT(YEAR(NOW())-1, '-', MONTH(NOW()), '-01 00:00:00') - {$where} - GROUP BY d, formatted_version - ORDER BY d, quant"; - -$result = $dbh->prepare($query)->execute(); - $last_date = null; -while ($row = $result->fetch()) { +foreach ($bugRepository->findPhpVersions($bug_type) as $row) { if ($row['d'] != $last_date) { if ($last_date !== null) { echo "</table>\n\n"; diff --git a/www/vote.php b/www/vote.php index e3e0a87..73f2a9c 100644 --- a/www/vote.php +++ b/www/vote.php @@ -1,5 +1,7 @@ <?php +use App\Repository\BugRepository; + // Obtain common includes require_once '../include/prepend.php'; @@ -20,7 +22,7 @@ $reproduced = (int) $_POST['reproduced']; $samever = isset($_POST['samever']) ? (int) $_POST['samever'] : 0; $sameos = isset($_POST['sameos']) ? (int) $_POST['sameos'] : 0; -if (!$dbh->prepare("SELECT id FROM bugdb WHERE id= ? LIMIT 1")->execute([$id])->fetch(\PDO::FETCH_NUM)[0]) { +if (!(new BugRepository($dbh))->exists($id)) { session_start(); // Authenticate
-- PHP Webmaster List Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
