diff --git a/resources/sql/autopatches/20140716.imagemacrousage.sql b/resources/sql/autopatches/20140716.imagemacrousage.sql new file mode 100644 --- /dev/null +++ b/resources/sql/autopatches/20140716.imagemacrousage.sql @@ -0,0 +1,21 @@ +CREATE TABLE {$NAMESPACE}_file.file_imagemacrousage ( + id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, + macroPHID VARCHAR(64) NOT NULL COLLATE utf8_bin, + authorPHID VARCHAR(64) NOT NULL COLLATE utf8_bin, + commentPHID VARCHAR(64) NOT NULL COLLATE utf8_bin, + revisionPHID VARCHAR(64) NOT NULL COLLATE utf8_bin, + dateCreated INT UNSIGNED NOT NULL, + dateModified INT UNSIGNED NOT NULL, + + KEY `key_created` (dateCreated) + +) ENGINE=InnoDB, COLLATE utf8_general_ci; + +CREATE TABLE {$NAMESPACE}_file.file_imagemacrousagecursors ( + id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, + cursorType VARCHAR(64) NOT NULL COLLATE utf8_bin, + cursorId INT UNSIGNED NOT NULL +) ENGINE=InnoDB, COLLATE utf8_general_ci; + +INSERT INTO {$NAMESPACE}_file.file_imagemacrousagecursors (cursorType, cursorId) +VALUES ('differential_transaction_comment', 0) diff --git a/src/__phutil_library_map__.php b/src/__phutil_library_map__.php --- a/src/__phutil_library_map__.php +++ b/src/__phutil_library_map__.php @@ -1586,6 +1586,7 @@ 'PhabricatorFileEditor' => 'applications/files/editor/PhabricatorFileEditor.php', 'PhabricatorFileFilePHIDType' => 'applications/files/phid/PhabricatorFileFilePHIDType.php', 'PhabricatorFileImageMacro' => 'applications/macro/storage/PhabricatorFileImageMacro.php', + 'PhabricatorFileImageMacroUsage' => 'applications/macro/storage/PhabricatorFileImageMacroUsage.php', 'PhabricatorFileInfoController' => 'applications/files/controller/PhabricatorFileInfoController.php', 'PhabricatorFileLinkListView' => 'view/layout/PhabricatorFileLinkListView.php', 'PhabricatorFileLinkView' => 'view/layout/PhabricatorFileLinkView.php', @@ -4409,6 +4410,7 @@ 'PhabricatorFlaggableInterface', 'PhabricatorPolicyInterface', ), + 'PhabricatorFileImageMacroUsage' => 'PhabricatorFileDAO', 'PhabricatorFileInfoController' => 'PhabricatorFileController', 'PhabricatorFileLinkListView' => 'AphrontView', 'PhabricatorFileLinkView' => 'AphrontView', diff --git a/src/applications/macro/query/PhabricatorMacroQuery.php b/src/applications/macro/query/PhabricatorMacroQuery.php --- a/src/applications/macro/query/PhabricatorMacroQuery.php +++ b/src/applications/macro/query/PhabricatorMacroQuery.php @@ -3,9 +3,11 @@ final class PhabricatorMacroQuery extends PhabricatorCursorPagedPolicyAwareQuery { + private $byPopularity; private $ids; private $phids; private $authors; + private $abusers; private $names; private $nameLike; private $dateCreatedAfter; @@ -38,6 +40,11 @@ return $options; } + public function withPopularitySort($use_popularity_sort) { + $this->byPopularity = $use_popularity_sort; + return $this; + } + public function withIDs(array $ids) { $this->ids = $ids; return $this; @@ -53,6 +60,11 @@ return $this; } + public function withAbuserPHIDs(array $abusers) { + $this->abusers = $abusers; + return $this; + } + public function withNameLike($name) { $this->nameLike = $name; return $this; @@ -87,15 +99,78 @@ $macro_table = new PhabricatorFileImageMacro(); $conn = $macro_table->establishConnection('r'); - $rows = queryfx_all( + if ($this->byPopularity) { + $rows = queryfx_all( + $conn, + 'SELECT m.*, COUNT(n.id) as popularity FROM %T m %Q %Q %Q %Q %Q %Q', + $macro_table->getTableName(), + $this->buildJoinClause($conn), + $this->buildWhereClause($conn), + $this->buildGroupClause($conn), + $this->buildHavingClause($conn), + $this->buildOrderClause($conn), + $this->buildLimitClause($conn)); + } else { + $rows = queryfx_all( + $conn, + 'SELECT m.* FROM %T m %Q %Q %Q', + $macro_table->getTableName(), + $this->buildWhereClause($conn), + $this->buildOrderClause($conn), + $this->buildLimitClause($conn)); + } + + $macros = $macro_table->loadAllFromArray($rows); + if ($this->byPopularity) { + $macros_by_id = mpull($macros, null, 'getId'); + + $macro_usage_table = new PhabricatorFileImageMacroUsage(); + $author_popularity_rows = queryfx_all( + $conn, + 'SELECT macroPHID, authorPHID, COUNT(*) as authorUses FROM %T + GROUP BY macroPHID, authorPHID + ORDER BY macroPHID, authorUses', + $macro_usage_table->getTableName()); + $by_macro = ipull($author_popularity_rows, null, 'macroPHID'); + + foreach ($rows as $row) { + $macro_id = $row['id']; + $macro_phid = $row['phid']; + $author_phid = isset($by_macro[$macro_phid]) ? + $by_macro[$macro_phid]['authorPHID'] : null; + $author_uses = isset($by_macro[$macro_phid]) ? + $by_macro[$macro_phid]['authorUses'] : null; + $macros_by_id[$macro_id]->setPopularity( + $row['popularity'], + $author_phid, + $author_uses); + } + } + return $macros; + } + + + private function buildHavingClause(AphrontDatabaseConnection $conn) { + $where = $this->buildPagingClause($conn); + if ($where != null) { + return 'HAVING '.$where; + } + return ''; + } + + private function buildJoinClause(AphrontDatabaseConnection $conn) { + assert($this->byPopularity); + $macro_usage_table = new PhabricatorFileImageMacroUsage(); + $macro_usage_table->populate(); + + $joins = array(); + $joins[] = qsprintf( $conn, - 'SELECT m.* FROM %T m %Q %Q %Q', - $macro_table->getTableName(), - $this->buildWhereClause($conn), - $this->buildOrderClause($conn), - $this->buildLimitClause($conn)); + 'LEFT JOIN %T n ON m.phid = n.macroPHID', + $macro_usage_table->getTableName()); + + return implode(' ', $joins); - return $macro_table->loadAllFromArray($rows); } protected function buildWhereClause(AphrontDatabaseConnection $conn) { @@ -122,6 +197,13 @@ $this->authors); } + if ($this->abusers && $this->byPopularity) { + $where[] = qsprintf( + $conn, + 'n.authorPHID IN (%Ls)', + $this->abusers); + } + if ($this->nameLike) { $where[] = qsprintf( $conn, @@ -190,11 +272,22 @@ } } - $where[] = $this->buildPagingClause($conn); + if (!$this->byPopularity) { + $where[] = $this->buildPagingClause($conn); + } return $this->formatWhereClause($where); } + private function buildGroupClause(AphrontDatabaseConnection $conn) { + $group = array(); + $group[] = qsprintf( + $conn, + 'GROUP BY m.phid'); + + return implode(' ', $group); + } + protected function didFilterPage(array $macros) { $file_phids = mpull($macros, 'getFilePHID'); $files = id(new PhabricatorFileQuery()) @@ -217,7 +310,50 @@ } protected function getPagingColumn() { - return 'm.id'; + $popularity_order = 'popularity '. + ($this->getBeforeID() ? 'ASC' : 'DESC').', m.id'; + return $this->byPopularity ? $popularity_order : 'm.id'; + } + + protected function getPagingValue($macro) { + if ($this->byPopularity) { + $popularity = $macro->getPopularity(); + return (string)$popularity['popularity'].','.(string)$macro->getId(); + } + return PhabricatorCursorPagedPolicyAwareQuery::getPagingValue($macro); + } + + protected function buildPagingClause( + AphrontDatabaseConnection $conn_r) { + + if ($this->byPopularity) { + if ($this->getBeforeID()) { + $strs = explode(',', $this->getBeforeID()); + $beforePopularity = (int)$strs[0]; + $beforeId = (int)$strs[1]; + + return qsprintf( + $conn_r, + 'popularity > %d OR (popularity = %d AND m.id > %d)', + $beforePopularity, + $beforePopularity, + $beforeId); + } else if ($this->getAfterID()) { + $strs = explode(',', $this->getAfterID()); + $afterPopularity = (int)$strs[0]; + $afterId = (int)$strs[1]; + + return qsprintf( + $conn_r, + 'popularity < %d OR (popularity = %d AND m.id < %d)', + $afterPopularity, + $afterPopularity, + $afterId); + } + + return null; + } + return PhabricatorCursorPagedPolicyAwareQuery::buildPagingClause($conn_r); } public function getQueryApplicationClass() { diff --git a/src/applications/macro/query/PhabricatorMacroSearchEngine.php b/src/applications/macro/query/PhabricatorMacroSearchEngine.php --- a/src/applications/macro/query/PhabricatorMacroSearchEngine.php +++ b/src/applications/macro/query/PhabricatorMacroSearchEngine.php @@ -17,6 +17,12 @@ 'authorPHIDs', $this->readUsersFromRequest($request, 'authors')); + $abuser_phids = $this->readUsersFromRequest($request, 'abusers'); + $saved->setParameter( + 'abuserPHIDs', + $abuser_phids); + $saved->setParameter('popularitySort', !empty($abuser_phids)); + $saved->setParameter('status', $request->getStr('status')); $saved->setParameter('names', $request->getStrList('names')); $saved->setParameter('nameLike', $request->getStr('nameLike')); @@ -31,7 +37,9 @@ $query = id(new PhabricatorMacroQuery()) ->withIDs($saved->getParameter('ids', array())) ->withPHIDs($saved->getParameter('phids', array())) - ->withAuthorPHIDs($saved->getParameter('authorPHIDs', array())); + ->withAuthorPHIDs($saved->getParameter('authorPHIDs', array())) + ->withAbuserPHIDs($saved->getParameter('abuserPHIDs', array())) + ->withPopularitySort($saved->getParameter('popularitySort', false)); $status = $saved->getParameter('status'); $options = PhabricatorMacroQuery::getStatusOptions(); @@ -79,6 +87,12 @@ ->withPHIDs($phids) ->execute(); + $abuser_phids = $saved_query->getParameter('abuserPHIDs', array()); + $abuser_handles = id(new PhabricatorHandleQuery()) + ->setViewer($this->requireViewer()) + ->withPHIDs($abuser_phids) + ->execute(); + $status = $saved_query->getParameter('status'); $names = implode(', ', $saved_query->getParameter('names', array())); $like = $saved_query->getParameter('nameLike'); @@ -98,6 +112,12 @@ ->setLabel(pht('Authors')) ->setValue($author_handles)) ->appendChild( + id(new AphrontFormTokenizerControl()) + ->setDatasource(new PhabricatorPeopleDatasource()) + ->setName('abusers') + ->setLabel(pht('Abused By')) + ->setValue($abuser_handles)) + ->appendChild( id(new AphrontFormTextControl()) ->setName('nameLike') ->setLabel(pht('Name Contains')) @@ -130,11 +150,13 @@ public function getBuiltinQueryNames() { $names = array( 'active' => pht('Active'), - 'all' => pht('All'), + 'recent' => pht('Recent'), + 'popular' => pht('Popular'), ); if ($this->requireViewer()->isLoggedIn()) { $names['authored'] = pht('Authored'); + $names['favorites'] = pht('Favorites'); } return $names; @@ -147,7 +169,7 @@ switch ($query_key) { case 'active': return $query; - case 'all': + case 'recent': return $query->setParameter( 'status', PhabricatorMacroQuery::STATUS_ANY); @@ -155,6 +177,25 @@ return $query->setParameter( 'authorPHIDs', array($this->requireViewer()->getPHID())); + case 'popular': + return $query + ->setParameter( + 'status', + PhabricatorMacroQuery::STATUS_ANY) + ->setParameter( + 'popularitySort', + true); + case 'favorites': + return $query + ->setParameter( + 'status', + PhabricatorMacroQuery::STATUS_ANY) + ->setParameter( + 'popularitySort', + true) + ->setParameter( + 'abuserPHIDs', + array($this->requireViewer()->getPHID())); } return parent::buildSavedQueryFromBuiltin($query_key); @@ -163,7 +204,9 @@ protected function getRequiredHandlePHIDsForResultList( array $macros, PhabricatorSavedQuery $query) { - return mpull($macros, 'getAuthorPHID'); + return array_merge( + mpull($macros, 'getAuthorPHID'), + ipull(mpull($macros, 'getPopularity'), 'top_abuser')); } protected function renderResultList( @@ -207,6 +250,30 @@ pht('Created by %s', $author_handle->renderLink())); } + $popularity = $macro->getPopularity(); + if ($popularity != null) { + $popularizer_phid = $popularity['top_abuser']; + + $item->appendChild( + phutil_tag( + 'div', + array(), + pht('Popularity %d', $popularity['popularity']))); + + $message = $popularity['popularity'] == 0 ? + pht('Wow. This macro must suck.') : + pht('Abused most by %s (%d time%s)', + $handles[$popularizer_phid]->renderLink(), + $popularity['top_abuser_count'], + $popularity['top_abuser_count'] != 1 ? 's' : ''); + $item->appendChild( + phutil_tag( + 'div', + array(), + $message)); + } + + $item->setURI($this->getApplicationURI('/view/'.$macro->getID().'/')); $item->setDisabled($macro->getisDisabled()); $item->setHeader($macro->getName()); diff --git a/src/applications/macro/storage/PhabricatorFileImageMacro.php b/src/applications/macro/storage/PhabricatorFileImageMacro.php --- a/src/applications/macro/storage/PhabricatorFileImageMacro.php +++ b/src/applications/macro/storage/PhabricatorFileImageMacro.php @@ -17,6 +17,9 @@ private $file = self::ATTACHABLE; private $audio = self::ATTACHABLE; + private $popularity = null; + private $topAbuser = null; + private $topAbuserCount = null; const AUDIO_BEHAVIOR_NONE = 'audio:none'; const AUDIO_BEHAVIOR_ONCE = 'audio:once'; @@ -59,6 +62,23 @@ return parent::save(); } + public function setPopularity($popularity, $top_abuser, $top_abuser_count) { + $this->popularity = $popularity; + $this->topAbuser = $top_abuser; + $this->topAbuserCount = $top_abuser_count; + } + + public function getPopularity() { + if ($this->popularity == null) { + return null; + } else { + return array( + 'popularity' => $this->popularity, + 'top_abuser' => $this->topAbuser, + 'top_abuser_count' => $this->topAbuserCount); + } + } + /* -( PhabricatorApplicationTransactionInterface )------------------------- */ diff --git a/src/applications/macro/storage/PhabricatorFileImageMacroUsage.php b/src/applications/macro/storage/PhabricatorFileImageMacroUsage.php new file mode 100644 --- /dev/null +++ b/src/applications/macro/storage/PhabricatorFileImageMacroUsage.php @@ -0,0 +1,144 @@ +establishConnection('w'); + $rows = queryfx_all( + $conn, + 'SELECT m.* FROM %T m WHERE (m.isDisabled = 0)', + $macro_table->getTableName()); + $all_macros = $macro_table->loadAllFromArray($rows); + $macros_by_name = mpull($all_macros, 'getPHID', 'getName'); + + $this->loadFromDifferential($conn, $macros_by_name); + } + + /*********************/ + /* Cursor management */ + /*********************/ + + private function grabCursor($conn, $cursor_type) { + // Read cursors positions so we can only update recent changes + // Use mysql write locking transactions around the cursor table + $conn->beginWriteLocking(); + $rows = queryfx_all( + $conn, + 'SELECT * FROM %T WHERE cursorType = %s FOR UPDATE', + 'file_imagemacrousagecursors', + $cursor_type); + + assert(count($rows) == 1); + return $rows[0]['cursorId']; + } + + private function releaseCursor($conn) { + $conn->endWriteLocking(); + } + + /******************************************************/ + /* Update our macro usage table and change the cursor */ + /******************************************************/ + + private function updateTableAndCursor($conn, $values_clause, $cursor_type, + $new_cursor_value) { + // This is just preprocessing data from one table, so it's safe to write + // on a read pathway + $guard = AphrontWriteGuard::beginScopedUnguardedWrites(); + if ($values_clause) { + queryfx( + $conn, + 'INSERT INTO %T + (macroPHID, authorPHID, commentPHID, revisionPHID, + dateCreated, dateModified) + VALUES %Q', + $this->getTableName(), + $values_clause); + } + + // Update cursor to end transaction + // Update cursors positions in the differential table + queryfx( + $conn, + 'UPDATE %T SET cursorId = %d + WHERE cursorType = %s', + 'file_imagemacrousagecursors', + $new_cursor_value, + $cursor_type); + unset($guard); + } + + /**********************/ + /* App-specific loads */ + /**********************/ + + private function loadFromDifferential($conn, $macros_by_name) { + $comments_table = new DifferentialTransactionComment(); + $cursor_type = $comments_table->getTableName(); + $differential_conn_r = $comments_table->establishConnection('r'); + + // Loop limits queries to 1000 in order to avoid hitting mysql packet + // limits Only run loop up to 50 times so we don't time out the request. + for ($i = 0; $i <= 50; $i++) { + // Pull all the comments from differential (different database) + $cursor_pos = $this->grabCursor($conn, $cursor_type); + + $rows = queryfx_all( + $differential_conn_r, + 'SELECT id, transactionPHID, authorPHID, content, revisionPHID, + dateCreated, dateModified + FROM %T + WHERE id > %d + ORDER BY id + LIMIT 1000', + $comments_table->getTableName(), + $cursor_pos); + + if (empty($rows)) { + $this->releaseCursor($conn); + return; + } + + // Look for macros in the comments. Generate list of things to put in + // macrousage table + $rows_to_write = array(); + foreach ($rows as $row) { + $results = array(); + preg_match_all( + '@^\s*([a-zA-Z0-9:_\-]+)$@m', + $row['content'], + $results); + + foreach ($results[1] as $result) { + if (!empty($macros_by_name[$result])) { + $to_write = $row; + $to_write['macroPHID'] = $macros_by_name[$result]; + unset($to_write['content']); + $rows_to_write[] = $to_write; + } + } + } + + // Append to our wonderful macrousage table + $values = array(); + foreach ($rows_to_write as $row) { + $values[] = qsprintf( + $conn, + '(%s, %s, %s, %s, %d, %d)', + $row['macroPHID'], + $row['authorPHID'], + $row['transactionPHID'], + $row['revisionPHID'], + $row['dateCreated'], + $row['dateModified']); + } + $values_clause = implode(',', $values); + + $lastrow = end($rows); + $this->updateTableAndCursor($conn, $values_clause, $cursor_type, + $lastrow['id']); + $this->releaseCursor($conn); + } + } +}