r84803 MediaWiki - Code Review archive

Repository:MediaWiki
Revision:< r84802‎ | r84803 | r84804 >
Date:16:04, 26 March 2011
Author:ashley
Status:deferred
Tags:
Comment:
SocialProfile: rewrite last raw SQL bits to use MW's Database stuff in UserStats. Somewhat related to bug #27732 (SocialProfile's PostgreSQL support sucks)
Modified paths:
  • /trunk/extensions/SocialProfile/UserStats/UserStatsClass.php (modified) (history)

Diff [purge]

Index: trunk/extensions/SocialProfile/UserStats/UserStatsClass.php
——@@ -237,31 +237,57 @@
238238 }
239239 }
240240
 241+ /**
 242+ * Update the amount of comments the user has submitted.
 243+ * Comment count is fetched from the Comments table, which is introduced by
 244+ * the extension with the same name.
 245+ */
241246 function updateCommentCount() {
242247 global $wgUser;
243248 if ( !$wgUser->isAnon() ) {
244249 $dbw = wfGetDB( DB_MASTER );
245 - $sql = "UPDATE {$dbw->tableName( 'user_stats' )} SET ";
246 - $sql .= 'stats_comment_count=';
247 - $sql .= "(SELECT COUNT(*) AS CommentCount FROM {$dbw->tableName( 'Comments' )} WHERE Comment_user_id = " . $this->user_id;
248 - $sql .= ")";
249 - $sql .= " WHERE stats_user_id = " . $this->user_id;
250 - $res = $dbw->query( $sql, __METHOD__ );
 250+ $comments = $dbw->select(
 251+ 'Comments',
 252+ 'COUNT(*) AS CommentCount',
 253+ array( 'Comment_user_id' => $this->user_id ),
 254+ __METHOD__
 255+ );
 256+ $res = $dbw->update(
 257+ 'user_stats',
 258+ array(
 259+ 'stats_comment_count' => $comments->CommentCount
 260+ ),
 261+ array( 'stats_user_id' => $this->user_id ),
 262+ __METHOD__
 263+ );
251264
252265 $this->clearCache();
253266 }
254267 }
255268
 269+ /**
 270+ * Update the amount of times the user has been added into someone's
 271+ * comment ignore list by fetching data from the Comments_block table,
 272+ * which is introduced by the Comments extension.
 273+ */
256274 function updateCommentIgnored() {
257275 global $wgUser;
258276 if ( !$wgUser->isAnon() ) {
259277 $dbw = wfGetDB( DB_MASTER );
260 - $sql = "UPDATE {$dbw->tableName( 'user_stats' )} SET ";
261 - $sql .= 'stats_comment_blocked=';
262 - $sql .= "(SELECT COUNT(*) AS CommentCount FROM {$dbw->tableName( 'Comments_block' )} WHERE cb_user_id_blocked = " . $this->user_id;
263 - $sql .= ")";
264 - $sql .= " WHERE stats_user_id = " . $this->user_id;
265 - $res = $dbw->query( $sql, __METHOD__ );
 278+ $blockedComments = $dbw->select(
 279+ 'Comments_block',
 280+ 'COUNT(*) AS CommentCount',
 281+ array( 'cb_user_id_blocked' => $this->user_id ),
 282+ __METHOD__
 283+ );
 284+ $res = $dbw->update(
 285+ 'user_stats',
 286+ array(
 287+ 'stats_comment_blocked' => $blockedComments->CommentCount
 288+ ),
 289+ array( 'stats_user_id' => $this->user_id ),
 290+ __METHOD__
 291+ );
266292
267293 $this->clearCache();
268294 }
——@@ -275,27 +301,46 @@
276302 global $wgUser;
277303 if ( !$wgUser->isAnon() ) {
278304 $dbw = wfGetDB( DB_MASTER );
279 - $sql = "UPDATE {$dbw->tableName( 'user_stats' )} SET ";
280 - $sql .= 'stats_edit_count=';
281 - $sql .= "(SELECT count(*) AS EditsCount FROM {$dbr->tableName( 'revision' )} WHERE rev_user = {$this->user_id} ";
282 - $sql .= ")";
283 - $sql .= " WHERE stats_user_id = " . $this->user_id;
284 - $res = $dbw->query( $sql, __METHOD__ );
 305+ $edits = $dbw->select(
 306+ 'revision',
 307+ 'COUNT(*) AS EditsCount',
 308+ array( 'rev_user' => $this->user_id ),
 309+ __METHOD__
 310+ );
 311+ $res = $dbw->update(
 312+ 'user_stats',
 313+ array(
 314+ 'stats_edit_count' => $edits->EditsCount
 315+ ),
 316+ array( 'stats_user_id' => $this->user_id ),
 317+ __METHOD__
 318+ );
285319
286320 $this->clearCache();
287321 }
288322 }
289323
 324+ /**
 325+ * Update the amount of votes for a given user.
 326+ * Vote count is fetched from the Vote table, which is introduced
 327+ * by a separate extension.
 328+ */
290329 function updateVoteCount() {
291330 global $wgUser;
292331 if ( !$wgUser->isAnon() ) {
293332 $dbw = wfGetDB( DB_MASTER );
294 - $sql = "UPDATE {$dbw->tableName( 'user_stats' )} SET ";
295 - $sql .= 'stats_vote_count=';
296 - $sql .= "(SELECT count(*) AS VoteCount FROM {$dbw->tableName( 'Vote' )} WHERE vote_user_id = {$this->user_id} ";
297 - $sql .= ")";
298 - $sql .= " WHERE stats_user_id = " . $this->user_id;
299 - $res = $dbw->query( $sql, __METHOD__ );
 333+ $votes = $dbw->select(
 334+ 'Vote',
 335+ 'COUNT(*) AS VoteCount',
 336+ array( 'vote_user_id' => $this->user_id ),
 337+ __METHOD__
 338+ );
 339+ $res = $dbw->update(
 340+ 'user_stats',
 341+ array( 'stats_vote_count' => $votes->VoteCount ),
 342+ array( 'stats_user_id' => $this->user_id ),
 343+ __METHOD__
 344+ );
300345
301346 $this->clearCache();
302347 }
——@@ -309,61 +354,121 @@
310355 * comment scores
311356 */
312357 function updateCommentScoreRec( $voteType ) {
313 - global $wgUser;
314358 if ( $this->user_id != 0 ) {
315359 $dbw = wfGetDB( DB_MASTER );
316 - $sql = "UPDATE {$dbw->tableName( 'user_stats' )} SET ";
 360+
317361 if ( $voteType == 1 ) {
318 - $sql .= 'stats_comment_score_positive_rec=';
 362+ $columnName = 'stats_comment_score_positive_rec';
319363 } else {
320 - $sql .= 'stats_comment_score_negative_rec=';
 364+ $columnName = 'stats_comment_score_negative_rec';
321365 }
322 - $sql .= "(SELECT COUNT(*) AS CommentVoteCount FROM {$dbw->tableName( 'Comments_Vote' )} WHERE Comment_Vote_ID IN (
323 - SELECT CommentID FROM {$dbw->tableName( 'Comments' )} WHERE Comment_user_id = " . $this->user_id . ") AND Comment_Vote_Score=" . $voteType;
324 - $sql .= ')';
325 - $sql .= ' WHERE stats_user_id = ' . $this->user_id;
326 - $res = $dbw->query( $sql, __METHOD__ );
327366
 367+ $commentIDs = $dbw->select(
 368+ 'Comments',
 369+ 'CommentID',
 370+ array( 'Comment_user_id' => $voteType ),
 371+ __METHOD__
 372+ );
 373+
 374+ $ids = array();
 375+ foreach ( $commentIDs as $commentID ) {
 376+ $ids[] = $commentID;
 377+ }
 378+
 379+ $comments = $dbw->select(
 380+ 'Comments_Vote',
 381+ 'COUNT(*) AS CommentVoteCount',
 382+ array(
 383+ 'Comment_Vote_ID IN ' . implode( ',', $ids ),
 384+ 'Comment_Vote_Score' => $voteType
 385+ ),
 386+ __METHOD__
 387+ );
 388+
 389+ $res = $dbw->update(
 390+ 'user_stats',
 391+ array( $columnName => $comments->CommentVoteCount ),
 392+ array( 'stats_user_id' => $this->user_id ),
 393+ __METHOD__
 394+ );
 395+
328396 $this->clearCache();
329397 }
330398 }
331399
 400+ /**
 401+ * Updates the amount of created opinions by the current user.
 402+ * Currently this is ugly, unused and a leftover from ArmchairGM days.
 403+ */
332404 function updateCreatedOpinionsCount() {
333405 global $wgUser, $wgOut;
334406 if ( !$wgUser->isAnon() && $this->user_id ) {
335407 $ctg = 'Opinions by User ' . ( $this->user_name );
336408 $parser = new Parser();
337 - $ctgTitle = Title::newFromText( $parser->transformMsg( trim( $ctg ), $wgOut->parserOptions() ) );
 409+ $ctgTitle = Title::newFromText(
 410+ $parser->transformMsg( trim( $ctg ), $wgOut->parserOptions() )
 411+ );
338412 $ctgTitle = $ctgTitle->getDBkey();
339413 $dbw = wfGetDB( DB_MASTER );
340 - $sql = "UPDATE {$dbw->tableName( 'user_stats' )} SET stats_opinions_created=";
341 - $sql .= "(SELECT count(*) AS CreatedOpinions FROM {$dbw->tableName( 'page' )}
342 - INNER JOIN {$dbw->tableName( 'categorylinks' )} ON page_id = cl_from
343 - WHERE (cl_to) = " . $dbw->addQuotes( $ctgTitle ) . ' ';
344 - $sql .= ')';
345 - $sql .= ' WHERE stats_user_id = ' . $this->user_id;
346414
347 - $res = $dbw->query( $sql, __METHOD__ );
 415+ $opinions = $dbw->select(
 416+ array( 'page', 'categorylinks' ),
 417+ array( 'COUNT(*) AS CreatedOpinions' ),
 418+ array( 'cl_to' => $ctgTitle ),
 419+ __METHOD__,
 420+ array(),
 421+ array(
 422+ 'categorylinks' => array( 'INNER JOIN', 'page_id = cl_from' ),
 423+ )
 424+ );
348425
 426+ $res = $dbw->update(
 427+ 'user_stats',
 428+ array( 'stats_opinions_created' => $opinions->CreatedOpinions ),
 429+ array( 'stats_user_id' => $this->user_id ),
 430+ __METHOD__
 431+ );
 432+
349433 $this->clearCache();
350434 }
351435 }
352436
 437+ /**
 438+ * Updates the amount of published opinions by the current user.
 439+ * Currently this is ugly, unused and a leftover from ArmchairGM days.
 440+ */
353441 function updatePublishedOpinionsCount() {
354442 global $wgOut;
 443+
355444 $parser = new Parser();
356445 $dbw = wfGetDB( DB_MASTER );
357446 $ctg = 'Opinions by User ' . ( $this->user_name );
358 - $ctgTitle = Title::newFromText( $parser->transformMsg( trim( $ctg ), $wgOut->parserOptions() ) );
 447+ $ctgTitle = Title::newFromText(
 448+ $parser->transformMsg( trim( $ctg ), $wgOut->parserOptions() )
 449+ );
359450 $ctgTitle = $ctgTitle->getDBkey();
360 - $sql = "UPDATE {$dbw->tableName( 'user_stats' )} SET stats_opinions_published = ";
361 - $sql .= "(SELECT count(*) AS PromotedOpinions FROM {$dbw->tableName( 'page' )} INNER JOIN {$dbw->tableName( 'categorylinks' )} ON page_id = cl_from
362 - INNER JOIN published_page ON page_id=published_page_id
363 - WHERE (cl_to) = " . $dbw->addQuotes( $ctgTitle ) . ' AND published_type=1';
364 - $sql .= ')';
365 - $sql .= ' WHERE stats_user_id = ' . $this->user_id;
366 - $res = $dbw->query( $sql, __METHOD__ );
367451
 452+ $opinions = $dbw->select(
 453+ array( 'page', 'categorylinks', 'published_page' ),
 454+ array( 'COUNT(*) AS PromotedOpinions' ),
 455+ array( 'cl_to' => $ctgTitle, 'published_type' => 1 ),
 456+ __METHOD__,
 457+ array(),
 458+ array(
 459+ 'categorylinks' => array( 'INNER JOIN', 'page_id = cl_from' ),
 460+ 'published_page' => array(
 461+ 'INNER JOIN', 'page_id = published_page_id'
 462+ ),
 463+ )
 464+ );
 465+
 466+ $res = $dbw->update(
 467+ 'user_stats',
 468+ array( 'stats_opinions_published' => $opinions->PromotedOpinions ),
 469+ array( 'stats_user_id' => $this->user_id ),
 470+ __METHOD__
 471+ );
 472+
368473 $this->clearCache();
369474 }
370475
——@@ -382,60 +487,88 @@
383488 } else {
384489 $col = 'stats_foe_count';
385490 }
386 - $sql = "UPDATE LOW_PRIORITY {$dbw->tableName( 'user_stats' )} SET {$col}=
387 - (SELECT COUNT(*) AS rel_count FROM {$dbw->tableName( 'user_relationship' )} WHERE
388 - r_user_id = {$this->user_id} AND r_type={$relType}
389 - )
390 - WHERE stats_user_id = {$this->user_id}";
391 - $res = $dbw->query( $sql, __METHOD__ );
 491+ $relationships = $dbw->select(
 492+ 'user_relationship',
 493+ 'COUNT(*) AS rel_count',
 494+ array( 'r_user_id' => $this->user_id, 'r_type' => $relType ),
 495+ __METHOD__
 496+ );
 497+ $res = $dbw->update(
 498+ 'user_stats',
 499+ array( $col => $relationships->rel_count ),
 500+ array( 'stats_user_id' => $this->user_id ),
 501+ __METHOD__,
 502+ array( 'LOW_PRIORITY' )
 503+ );
392504 }
393505 }
394506
395507 /**
396 - * Updates the amount of received gifts if the user isn't an anon
 508+ * Updates the amount of received gifts if the user isn't an anon.
397509 */
398510 function updateGiftCountRec() {
399511 global $wgUser;
400512 if ( !$wgUser->isAnon() ) {
401513 $dbw = wfGetDB( DB_MASTER );
402 - $sql = "UPDATE LOW_PRIORITY {$dbw->tableName( 'user_stats' )} SET stats_gifts_rec_count=
403 - (SELECT COUNT(*) AS gift_count FROM {$dbw->tableName( 'user_gift' )} WHERE
404 - ug_user_id_to = {$this->user_id}
405 - )
406 - WHERE stats_user_id = {$this->user_id}";
407 -
408 - $res = $dbw->query( $sql, __METHOD__ );
 514+ $gifts = $dbw->select(
 515+ 'user_gift',
 516+ 'COUNT(*) AS gift_count',
 517+ array( 'ug_user_id_to' => $this->user_id ),
 518+ __METHOD__
 519+ );
 520+ $res = $dbw->update(
 521+ 'user_stats',
 522+ array( 'stats_gifts_rec_count' => $gifts->gift_count ),
 523+ array( 'stats_user_id' => $this->user_id ),
 524+ __METHOD__,
 525+ array( 'LOW_PRIORITY' )
 526+ );
409527 }
410528 }
411529
412530 /**
413 - * Updates the amount of sent gifts if the user isn't an anon
 531+ * Updates the amount of sent gifts if the user isn't an anon.
414532 */
415533 function updateGiftCountSent() {
416534 global $wgUser;
417535 if ( !$wgUser->isAnon() ) {
418536 $dbw = wfGetDB( DB_MASTER );
419 - $sql = "UPDATE LOW_PRIORITY {$dbw->tableName( 'user_stats' )} SET stats_gifts_sent_count=
420 - (SELECT COUNT(*) AS gift_count FROM {$dbw->tableName( 'user_gift' )} WHERE
421 - ug_user_id_from = {$this->user_id}
422 - )
423 - WHERE stats_user_id = {$this->user_id} ";
424 -
425 - $res = $dbw->query( $sql, __METHOD__ );
 537+ $gifts = $dbw->select(
 538+ 'user_gift',
 539+ 'COUNT(*) AS gift_count',
 540+ array( 'ug_user_id_from' => $this->user_id ),
 541+ __METHOD__
 542+ );
 543+ $res = $dbw->update(
 544+ 'user_stats',
 545+ array( 'stats_gifts_sent_count' => $gifts->gift_count ),
 546+ array( 'stats_user_id' => $this->user_id ),
 547+ __METHOD__,
 548+ array( 'LOW_PRIORITY' )
 549+ );
426550 }
427551 }
428552
 553+ /**
 554+ * Update the amount of users our user has referred to the wiki.
 555+ */
429556 public function updateReferralComplete() {
430557 global $wgUser;
431558 if ( !$wgUser->isAnon() ) {
432559 $dbw = wfGetDB( DB_MASTER );
433 - $sql = "UPDATE LOW_PRIORITY {$dbw->tableName( 'user_stats' )} SET stats_referrals_completed=
434 - (SELECT COUNT(*) AS thecount FROM {$dbw->tableName( 'user_register_track' )} WHERE
435 - ur_user_id_referral = {$this->user_id}
436 - )
437 - WHERE stats_user_id = {$this->user_id} ";
438 -
439 - $res = $dbw->query( $sql, __METHOD__ );
 560+ $referrals = $dbw->select(
 561+ 'user_register_track',
 562+ 'COUNT(*) AS thecount',
 563+ array( 'ur_user_id_referral' => $this->user_id ),
 564+ __METHOD__
 565+ );
 566+ $res = $dbw->update(
 567+ 'user_stats',
 568+ array( 'stats_referrals_completed' => $referrals->thecount ),
 569+ array( 'stats_user_id' => $this->user_id ),
 570+ __METHOD__,
 571+ array( 'LOW_PRIORITY' )
 572+ );
440573 }
441574 }
442575

Follow-up revisions

RevisionCommit summaryAuthorDate
r92016SocialProfile: fix epic fail times two from r84803. Thanks to Skizzerz for th...ashley22:40, 12 July 2011

Status & tagging log