From ee940c906d8170f5326d9364fe0dbf3a2d6f4ae5 Mon Sep 17 00:00:00 2001 From: Brion Vibber Date: Sun, 9 May 2004 01:30:34 +0000 Subject: [PATCH] Replace ugly abuse of log pages to store cached results of query pages with a table that stores up to 1000 rows for each 'expensive' special page. This can be paged through, reducing the annoyingness of 50 result limits on the old system. Rebuilding the cache can be forced by adding "recache=1" URL parameter, but this will need to be refined with some sort of controls to prevent abuse. Added the user realname update to the installer's updater list, also. --- config/index.php | 3 + includes/QueryPage.php | 125 ++++++++++++---------- includes/SpecialAncientpages.php | 21 ++-- includes/SpecialLongpages.php | 18 ++-- includes/SpecialNewpages.php | 37 ++++--- includes/SpecialPopularpages.php | 19 ++-- includes/SpecialShortpages.php | 22 ++-- includes/SpecialWantedpages.php | 25 +++-- languages/Language.php | 3 +- maintenance/archives/patch-list.txt | 7 ++ maintenance/archives/patch-querycache.sql | 9 ++ maintenance/tables.sql | 8 ++ maintenance/updaters.inc | 11 ++ 13 files changed, 202 insertions(+), 106 deletions(-) create mode 100644 maintenance/archives/patch-querycache.sql diff --git a/config/index.php b/config/index.php index bd88567926..e2010a6011 100644 --- a/config/index.php +++ b/config/index.php @@ -395,6 +395,9 @@ if( $conf->posted && ( 0 == count( $errs ) ) ) { do_linkscc_update(); flush(); do_hitcounter_update(); flush(); do_recentchanges_update(); flush(); + echo "FIXME: need the link table change here\n"; + do_user_real_name_update(); flush(); + do_querycache_update(); flush(); initialiseMessages(); flush(); chdir( "config" ); diff --git a/includes/QueryPage.php b/includes/QueryPage.php index d0f250e7ae..5b68965c57 100644 --- a/includes/QueryPage.php +++ b/includes/QueryPage.php @@ -1,6 +1,5 @@ sortDescending() ? "DESC" : "") + . " LIMIT {$offset}, {$limit}"; } # Is this query expensive (for some definition of expensive)? Then we @@ -40,29 +59,43 @@ class QueryPage { # This is the actual workhorse. It does everything needed to make a # real, honest-to-gosh query page. - + function doQuery( $offset, $limit ) { - global $wgUser, $wgOut, $wgLang, $wgMiserMode; + global $wgUser, $wgOut, $wgLang, $wgRequest; + global $wgMiserMode; $sname = $this->getName(); $fname = get_class($this) . "::doQuery"; + $sql = $this->getSQL( $offset, $limit ); $wgOut->setSyndicated( true ); - if ( $this->isExpensive( ) ) { - $vsp = $wgLang->getValidSpecialPages(); - $logpage = new LogPage( "!" . $vsp[$sname] ); - $logpage->mUpdateRecentChanges = false; - - if ( $wgMiserMode ) { - $logpage->showAsDisabledPage(); - return; + if ( $this->isExpensive() ) { + $type = wfStrencode( $sname ); + $recache = $wgRequest->getBool( "recache" ); + if( $recache ) { + # Clear out any old cached data + $res = wfQuery( "DELETE FROM querycache WHERE qc_type='$type'", DB_WRITE, $fname ); + + # Save results into the querycache table + $maxstored = 1000; + $res = wfQuery( + "INSERT INTO querycache(qc_type,qc_namespace,qc_title,qc_value) " . + $this->getSQL() . + $this->getOrderLimit( 0, $maxstored ), + DB_WRITE, $fname ); + } + if( $wgMiserMode || $recache ) { + $sql = + "SELECT qc_type as type, qc_namespace as namespace,qc_title as title, qc_value as value + FROM querycache WHERE qc_type='$type'"; + } + if( $wgMiserMode ) { + $wgOut->addWikiText( wfMsg( "perfcached" ) ); } } - $sql = $this->getSQL( $offset, $limit ); - - $res = wfQuery( $sql, DB_READ, $fname ); + $res = wfQuery( $sql . $this->getOrderLimit( $offset, $limit ), DB_READ, $fname ); $num = wfNumRows($res); @@ -86,12 +119,6 @@ class QueryPage { $s .= ""; $wgOut->addHTML( $s ); $wgOut->addHTML( "

{$sl}

\n" ); - - # Saving cache - - if ( $this->isExpensive() && $offset == 0 && $limit >= 50 ) { - $logpage->replaceContent( $s ); - } } # Similar to above, but packaging in a syndicated feed instead of a web page @@ -122,23 +149,15 @@ class QueryPage { # Override for custom handling. If the titles/links are ok, just do feedItemDesc() function feedResult( $row ) { - if( isset( $row->cur_title ) ) { - $title = Title::MakeTitle( $row->cur_namespace, $row->cur_title ); - } elseif( isset( $row->old_title ) ) { - $title = Title::MakeTitle( $row->old_namespace, $row->old_title ); - } elseif( isset( $row->rc_title ) ) { - $title = Title::MakeTitle( $row->rc_namespace, $row->rc_title ); - } else { + if( !isset( $row->title ) ) { return NULL; } + $title = Title::MakeTitle( IntVal( $row->namespace ), $row->title ); if( $title ) { - $date = ""; - if( isset( $row->cur_timestamp ) ) { - $date = $row->cur_timestamp; - } elseif( isset( $row->old_timestamp ) ) { - $date = $row->old_timestamp; - } elseif( isset( $row->rc_cur_timestamp ) ) { - $date = $row->rc_cur_timestamp; + if( isset( $row->timestamp ) ) { + $date = $row->timestamp; + } else { + $date = ""; } $comments = ""; @@ -161,34 +180,25 @@ class QueryPage { function feedItemDesc( $row ) { $text = ""; - if( isset( $row->cur_comment ) ) { - $text = $row->cur_comment; - } elseif( isset( $row->old_comment ) ) { - $text = $row->old_comment; - } elseif( isset( $row->rc_comment ) ) { - $text = $row->rc_comment; + if( isset( $row->comment ) ) { + $text = htmlspecialchars( $row->comment ); + } else { + $text = ""; } - $text = htmlspecialchars( $text ); - if( isset( $row->cur_text ) ) { + if( isset( $row->text ) ) { $text = "

" . htmlspecialchars( wfMsg( "summary" ) ) . ": " . $text . "

\n
\n
" . - nl2br( $row->cur_text ) . "
";; + nl2br( htmlspecialchars( $row->text ) ) . "";; } return $text; } function feedItemAuthor( $row ) { - /* old code - $fields = array( "cur_user_text", "old_user_text", "rc_user_text" ); - foreach( $fields as $field ) { - if( isset( $row->$field ) ) return $row->field; + if( isset( $row->user_text ) ) { + return $row->user_text; + } else { + return ""; } - - new code follow, that's an ugly hack to fix things: */ - if( isset( $row->cur_user_text ) ) return $row->cur_user_text; - if( isset( $row->old_user_text ) ) return $row->old_user_text; - if( isset( $row->rc_user_text ) ) return $row->rc_user_text; - return ""; } function feedTitle() { @@ -216,7 +226,8 @@ class QueryPage { class PageQueryPage extends QueryPage { function formatResult( $skin, $result ) { - return $skin->makeKnownLink( $result->cur_title, "" ); + $nt = Title::makeTitle( $result->namespace, $result->title ); + return $skin->makeKnownLinkObj( $nt, "" ); } } diff --git a/includes/SpecialAncientpages.php b/includes/SpecialAncientpages.php index 1b9a897bba..52bc0058b3 100644 --- a/includes/SpecialAncientpages.php +++ b/includes/SpecialAncientpages.php @@ -12,18 +12,25 @@ class AncientPagesPage extends QueryPage { return parent::isExpensive() ; } - function getSQL( $offset, $limit ) { - return "SELECT cur_title, cur_timestamp " . - "FROM cur USE INDEX (cur_timestamp) " . - "WHERE cur_namespace=0 AND cur_is_redirect=0 " . - " ORDER BY cur_timestamp LIMIT {$offset}, {$limit}"; + function getSQL() { + return + "SELECT 'Ancientpages' as type, + cur_namespace as namespace, + cur_title as title, + UNIX_TIMESTAMP(cur_timestamp) as value + FROM cur USE INDEX (cur_timestamp) + WHERE cur_namespace=0 AND cur_is_redirect=0"; + } + + function sortDescending() { + return false; } function formatResult( $skin, $result ) { global $wgLang; - $d = $wgLang->timeanddate( $result->cur_timestamp, true ); - $link = $skin->makeKnownLink( $result->cur_title, "" ); + $d = $wgLang->timeanddate( wfUnix2Timestamp( $result->value ), true ); + $link = $skin->makeKnownLink( $result->title, "" ); return "{$link} ({$d})"; } } diff --git a/includes/SpecialLongpages.php b/includes/SpecialLongpages.php index a02120ce03..6139fdb58c 100644 --- a/includes/SpecialLongpages.php +++ b/includes/SpecialLongpages.php @@ -9,19 +9,23 @@ class LongPagesPage extends QueryPage { } function isExpensive() { - return 1; + return true; } - function getSQL( $offset, $limit ) { - return "SELECT cur_title, LENGTH(cur_text) AS len FROM cur " . - "WHERE cur_namespace=0 AND cur_is_redirect=0 ORDER BY len DESC " . - "LIMIT {$offset}, {$limit}"; + function getSQL() { + return + "SELECT 'Longpages' as type, + cur_namespace as namespace, + cur_title as title, + LENGTH(cur_text) AS value + FROM cur + WHERE cur_namespace=0 AND cur_is_redirect=0"; } function formatResult( $skin, $result ) { global $wgLang; - $nb = wfMsg( "nbytes", $wgLang->formatNum( $result->len ) ); - $link = $skin->makeKnownLink( $result->cur_title, "" ); + $nb = wfMsg( "nbytes", $wgLang->formatNum( $result->value ) ); + $link = $skin->makeKnownLink( $result->title, "" ); return "{$link} ({$nb})"; } } diff --git a/includes/SpecialNewpages.php b/includes/SpecialNewpages.php index b23bc91cb2..77940e96eb 100644 --- a/includes/SpecialNewpages.php +++ b/includes/SpecialNewpages.php @@ -9,33 +9,46 @@ class NewPagesPage extends QueryPage { } function isExpensive() { - return parent::isExpensive(); + # Indexed on RC, and will *not* work with querycache yet. + return false; + #return parent::isExpensive(); } function getSQL( $offset, $limit ) { - return "SELECT rc_namespace AS cur_namespace, rc_title AS cur_title,rc_user AS cur_user,rc_user_text AS cur_user_text,rc_comment as cur_comment," . - "rc_timestamp AS cur_timestamp,length(cur_text) as cur_length,cur_text FROM recentchanges,cur " . - "WHERE rc_cur_id=cur_id AND rc_new=1 AND rc_namespace=0 AND cur_is_redirect=0 " . - "ORDER BY rc_timestamp DESC LIMIT {$offset}, {$limit}"; + return + "SELECT 'Newpages' as type, + rc_namespace AS namespace, + rc_title AS title, + rc_cur_id AS value, + + rc_user AS user, + rc_user_text AS user_text, + rc_comment as comment, + rc_timestamp AS timestamp, + length(cur_text) as length, + cur_text as text + FROM recentchanges,cur + WHERE rc_cur_id=cur_id AND rc_new=1 + AND rc_namespace=0 AND cur_is_redirect=0"; } function formatResult( $skin, $result ) { global $wgLang; - $u = $result->cur_user; - $ut = $result->cur_user_text; + $u = $result->user; + $ut = $result->user_text; - $length = wfmsg( "nbytes", $wgLang->formatNum( $result->cur_length ) ); - $c = $skin->formatComment($result->cur_comment ); + $length = wfMsg( "nbytes", $wgLang->formatNum( $result->length ) ); + $c = $skin->formatComment($result->comment ); if ( 0 == $u ) { # not by a logged-in user $ul = $ut; } else { - $ul = $skin->makeLink( $wgLang->getNsText(2) . ":{$ut}", $ut ); + $ul = $skin->makeLink( $wgLang->getNsText(NS_USER) . ":{$ut}", $ut ); } - $d = $wgLang->timeanddate( $result->cur_timestamp, true ); - $link = $skin->makeKnownLink( $result->cur_title, "" ); + $d = $wgLang->timeanddate( $result->timestamp, true ); + $link = $skin->makeKnownLink( $result->title, "" ); $s = "{$d} {$link} ({$length}) . . {$ul}"; if ( "" != $c && "*" != $c ) { diff --git a/includes/SpecialPopularpages.php b/includes/SpecialPopularpages.php index bed3b34289..e69743314c 100644 --- a/includes/SpecialPopularpages.php +++ b/includes/SpecialPopularpages.php @@ -9,19 +9,24 @@ class PopularPagesPage extends QueryPage { } function isExpensive() { - return 1; + # cur_counter is not indexed + return true; } - function getSQL( $offset, $limit ) { - return "SELECT cur_title, cur_counter FROM cur " . - "WHERE cur_namespace=0 AND cur_is_redirect=0 ORDER BY " . - "cur_counter DESC LIMIT {$offset}, {$limit}"; + function getSQL() { + return + "SELECT 'Popularpages' as type, + cur_namespace as namespace, + cur_title as title, + cur_counter as value + FROM cur + WHERE cur_namespace=0 AND cur_is_redirect=0"; } function formatResult( $skin, $result ) { global $wgLang; - $link = $skin->makeKnownLink( $result->cur_title, "" ); - $nv = wfMsg( "nviews", $wgLang->formatNum( $result->cur_counter ) ); + $link = $skin->makeKnownLink( $result->title, "" ); + $nv = wfMsg( "nviews", $wgLang->formatNum( $result->value ) ); return "{$link} ({$nv})"; } } diff --git a/includes/SpecialShortpages.php b/includes/SpecialShortpages.php index ae23023902..560b02b8c1 100644 --- a/includes/SpecialShortpages.php +++ b/includes/SpecialShortpages.php @@ -9,19 +9,27 @@ class ShortPagesPage extends QueryPage { } function isExpensive() { - return 1; + return true; } - function getSQL( $offset, $limit ) { - return "SELECT cur_title, LENGTH(cur_text) AS len FROM cur " . - "WHERE cur_namespace=0 AND cur_is_redirect=0 ORDER BY len " . - "LIMIT {$offset}, {$limit}"; + function getSQL() { + return + "SELECT 'Shortpages' as type, + cur_namespace as namespace, + cur_title as title, + LENGTH(cur_text) AS value + FROM cur + WHERE cur_namespace=0 AND cur_is_redirect=0"; + } + + function sortDescending() { + return false; } function formatResult( $skin, $result ) { global $wgLang; - $nb = wfMsg( "nbytes", $wgLang->formatNum( $result->len ) ); - $link = $skin->makeKnownLink( $result->cur_title, "" ); + $nb = wfMsg( "nbytes", $wgLang->formatNum( $result->value ) ); + $link = $skin->makeKnownLink( $result->title, "" ); return "{$link} ({$nb})"; } } diff --git a/includes/SpecialWantedpages.php b/includes/SpecialWantedpages.php index b3f5f9a97c..bf2d630096 100644 --- a/includes/SpecialWantedpages.php +++ b/includes/SpecialWantedpages.php @@ -9,22 +9,31 @@ class WantedPagesPage extends QueryPage { } function isExpensive() { - return 1; + return true; } - function getSQL( $offset, $limit ) { - return "SELECT bl_to, COUNT( DISTINCT bl_from ) as nlinks " . - "FROM brokenlinks GROUP BY bl_to HAVING nlinks > 1 " . - "ORDER BY nlinks DESC LIMIT {$offset}, {$limit}"; + function getSQL() { + # We cheat and return the full-text from bl_to in the title. + # In the future, a pre-parsed name will be available. + return + "SELECT 'Wantedpages' as type, + 0 as namespace, + bl_to as title, + COUNT(DISTINCT bl_from) as value + FROM brokenlinks + GROUP BY bl_to + HAVING value > 1"; } function formatResult( $skin, $result ) { global $wgLang; - $nt = Title::newFromDBkey( $result->bl_to ); - + $nt = Title::newFromDBkey( $result->title ); + if( is_null( $nt ) ) { + return ""; + } $plink = $skin->makeBrokenLink( $nt->getPrefixedText(), "" ); - $nl = wfMsg( "nlinks", $result->nlinks ); + $nl = wfMsg( "nlinks", $result->value ); $nlink = $skin->makeKnownLink( $wgLang->specialPage( "Whatlinkshere" ), $nl, "target=" . $nt->getPrefixedURL() ); diff --git a/languages/Language.php b/languages/Language.php index eb97fc9ce3..b51b08fc88 100644 --- a/languages/Language.php +++ b/languages/Language.php @@ -643,7 +643,8 @@ an incorrectly linked inter-language or inter-wiki title.", "perfdisabled" => "Sorry! This feature has been temporarily disabled because it slows the database down to the point that no one can use the wiki.", -"perfdisabledsub" => "Here's a saved copy from $1:", +"perfdisabledsub" => "Here's a saved copy from $1:", # obsolete? +"perfcached" => "The following data is cached and may not be completely up to date:", "wrong_wfQuery_params" => "Incorrect parameters to wfQuery()
Function: $1
Query: $2 diff --git a/maintenance/archives/patch-list.txt b/maintenance/archives/patch-list.txt index dc2d13a15d..cae48cddea 100644 --- a/maintenance/archives/patch-list.txt +++ b/maintenance/archives/patch-list.txt @@ -168,3 +168,10 @@ patch-ipb_expiry.sql everywhere. **Rebuild your links after this with refreshLinks.php** patch-linktables.sql + + +* 2004-04: Add user_real_name field +patch-user-realname.sql + +* 2004-05-08: Add querycache table for caching special pages +patch-querycache.sql diff --git a/maintenance/archives/patch-querycache.sql b/maintenance/archives/patch-querycache.sql new file mode 100644 index 0000000000..d4a1b8e1e4 --- /dev/null +++ b/maintenance/archives/patch-querycache.sql @@ -0,0 +1,9 @@ +-- Used for caching expensive grouped queries + +CREATE TABLE querycache ( + qc_type char(32) NOT NULL, + qc_value int(5) unsigned NOT NULL default '0', + qc_namespace tinyint(2) unsigned NOT NULL default '0', + qc_title char(255) binary NOT NULL default '', + KEY (qc_type,qc_value) +); diff --git a/maintenance/tables.sql b/maintenance/tables.sql index 1db28daaf5..b98e22591c 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -209,3 +209,11 @@ CREATE TABLE interwiki ( UNIQUE KEY iw_prefix (iw_prefix) ); +-- Used for caching expensive grouped queries +CREATE TABLE querycache ( + qc_type char(32) NOT NULL, + qc_value int(5) unsigned NOT NULL default '0', + qc_namespace tinyint(2) unsigned NOT NULL default '0', + qc_title char(255) binary NOT NULL default '', + KEY (qc_type,qc_value) +); diff --git a/maintenance/updaters.inc b/maintenance/updaters.inc index feb9e420b5..7ecb39c7b9 100644 --- a/maintenance/updaters.inc +++ b/maintenance/updaters.inc @@ -134,4 +134,15 @@ function do_user_real_name_update() { } } +function do_querycache_update() { + global $wgDatabase; + if( $wgDatabase->tableExists( "querycache" ) ) { + echo "...have special page querycache table.\n"; + } else { + echo "Adding querycache table for slow special pages... "; + dbsource( "maintenance/archives/patch-querycache.sql", $wgDatabase ); + echo "ok\n"; + } +} + ?> \ No newline at end of file -- 2.20.1