From a3cb83875f8a8f0e578d18881dc4bcd312f7e11b Mon Sep 17 00:00:00 2001 From: rurban Date: Sat, 20 Nov 2004 17:35:58 +0000 Subject: [PATCH] improved WantedPages SQL backends PageList::sortby new 3rd arg valid_fields (override db fields) WantedPages sql pager inexact for performance reasons: assume 3 wantedfrom per page, to be correct, no getTotal() support exclude argument for get_all_pages, new _sql_set() git-svn-id: svn://svn.code.sf.net/p/phpwiki/code/trunk@4157 96ab9672-09ca-45d6-a79d-3d69d39ca109 --- lib/PageList.php | 22 +++++--- lib/WikiDB.php | 37 +++++++++---- lib/WikiDB/backend.php | 10 +++- lib/WikiDB/backend/ADODB.php | 59 +++++++++++++++++++-- lib/WikiDB/backend/PearDB.php | 51 ++++++++++++++++-- lib/WikiDB/backend/dbaBase.php | 16 +++--- lib/WikiDB/backend/dumb/WantedPagesIter.php | 57 ++++++++++++++++++++ lib/plugin/WantedPages.php | 48 ++++++++++------- 8 files changed, 250 insertions(+), 50 deletions(-) create mode 100644 lib/WikiDB/backend/dumb/WantedPagesIter.php diff --git a/lib/PageList.php b/lib/PageList.php index ca64a7884..1dad06c3a 100644 --- a/lib/PageList.php +++ b/lib/PageList.php @@ -1,4 +1,4 @@ -sortby($col, $action); + $result = $result && $this->sortby($col, $action, $valid_fields); else - $result[] = $this->sortby($col, $action); + $result[] = $this->sortby($col, $action, $valid_fields); } // 'check' returns true/false for every col. return true if all are true. // i.e. the unsupported 'every' operator in functional languages. @@ -790,8 +790,9 @@ class PageList { and strstr($request->getArg('sortby'),$column))); } elseif ($action == 'db') { // Performance enhancement: use native DB sort if possible. - if (method_exists($request->_dbi->_backend, 'sortable_columns') - and (in_array($column, $request->_dbi->_backend->sortable_columns()))) { + if (($valid_fields and in_array($column, $valid_fields)) + or (method_exists($request->_dbi->_backend, 'sortable_columns') + and (in_array($column, $request->_dbi->_backend->sortable_columns())))) { // omit this sort method from the _sortPages call at rendering // asc or desc: +pagename, -pagename return $column . ($order == '+' ? ' ASC' : ' DESC'); @@ -804,6 +805,7 @@ class PageList { // echo implode(":",explodeList("Test*",array("xx","Test1","Test2"))); function explodePageList($input, $include_empty = false, $sortby=false, $limit=false) { + //TODO: need an SQL optimization here // expand wildcards from list of all pages if (preg_match('/[\?\*]/',$input)) { $dbi = $GLOBALS['request']->getDbh(); @@ -1431,6 +1433,14 @@ extends PageList { } // $Log: not supported by cvs2svn $ +// Revision 1.120 2004/11/20 11:28:49 rurban +// fix a yet unused PageList customPageListColumns bug (merge class not decl to _types) +// change WantedPages to use PageList +// change WantedPages to print the list of referenced pages, not just the count. +// the old version was renamed to WantedPagesOld +// fix and add handling of most standard PageList arguments (limit, exclude, ...) +// TODO: pagename sorting, dumb/WantedPagesIter and SQL optimization +// // Revision 1.119 2004/11/11 14:34:11 rurban // minor clarifications // diff --git a/lib/WikiDB.php b/lib/WikiDB.php index cfc8eaf9b..3307d49d3 100644 --- a/lib/WikiDB.php +++ b/lib/WikiDB.php @@ -1,5 +1,5 @@ setArg('limit',$limit); - $GLOBALS['request']->setArg('paging','auto'); - } - $result = $this->_backend->get_all_pages($include_empty, $sortby, $limit); + if (USECACHE) { + $mem = ini_get("memory_limit"); + if ($mem and !$limit and !isWindows() and !check_php_version(4,3)) { + $limit = 450; + $GLOBALS['request']->setArg('limit',$limit); + $GLOBALS['request']->setArg('paging','auto'); + } + } + $result = $this->_backend->get_all_pages($include_empty, $sortby, $limit, $exclude); return new WikiDB_PageIterator($this, $result, array('include_empty' => $include_empty, + 'exclude' => $exclude, 'limit' => $limit)); } @@ -304,7 +307,7 @@ class WikiDB { $count = $this->_backend->numPages($filter, $exclude); else { // FIXME: exclude ignored. - $iter = $this->getAllPages($filter); + $iter = $this->getAllPages($filter, false, false, $exclude); $count = $iter->count(); $iter->free(); } @@ -405,6 +408,17 @@ class WikiDB { return new WikiDB_PageRevisionIterator($this, $result); } + /** + * @access public + * + * @return Iterator A generic iterator containing rows of (duplicate) pagename, wantedfrom. + */ + function wantedPages($exclude_from='', $exclude='', $sortby=false, $limit=false) { + return $this->_backend->wanted_pages($exclude_from, $exclude, $sortby, $limit); + //return new WikiDB_PageIterator($this, $result); + } + + /** * Call the appropriate backend method. * @@ -2076,6 +2090,9 @@ function _sql_debuglog_shutdown_function() { } // $Log: not supported by cvs2svn $ +// Revision 1.105 2004/11/20 09:16:27 rurban +// Fix bad-style Cut&Paste programming errors, detected by Charles Corrigan. +// // Revision 1.104 2004/11/19 19:22:03 rurban // ModeratePage part1: change status // diff --git a/lib/WikiDB/backend.php b/lib/WikiDB/backend.php index aeeb3422a..7ed4a84bb 100644 --- a/lib/WikiDB/backend.php +++ b/lib/WikiDB/backend.php @@ -1,5 +1,5 @@ get_all_pages(true,false,false,$exclude_from); + return new WikiDB_backend_dumb_WantedPagesIter($this, $allpages, $exclude, $sortby, $limit); + } + /** * Lock backend database. * diff --git a/lib/WikiDB/backend/ADODB.php b/lib/WikiDB/backend/ADODB.php index 97a9a94e3..0da3e4a2a 100644 --- a/lib/WikiDB/backend/ADODB.php +++ b/lib/WikiDB/backend/ADODB.php @@ -1,5 +1,5 @@ page_tbl_field_list); } - function get_all_pages($include_empty=false, $sortby=false, $limit=false) { + function get_all_pages($include_empty=false, $sortby=false, $limit=false, $exclude='') { $dbh = &$this->_dbh; extract($this->_table_names); $orderby = $this->sortby($sortby, 'db'); if ($orderby) $orderby = 'ORDER BY ' . $orderby; + //TODO: convert comma-sep glob to sql-style: _sql_match_clause + if ($exclude) $exclude = "pagename not like ".$this->_dbh->qstr($exclude); //$dbh->SetFetchMode(ADODB_FETCH_ASSOC); if (strstr($orderby, 'mtime ')) { // was ' mtime' if ($include_empty) { @@ -561,6 +563,7 @@ extends WikiDB_backend ." FROM $page_tbl, $recent_tbl, $version_tbl" . " WHERE $page_tbl.id=$recent_tbl.id" . " AND $page_tbl.id=$version_tbl.id AND latestversion=version" + . $exclude . " $orderby"; } else { @@ -570,18 +573,22 @@ extends WikiDB_backend . " WHERE $nonempty_tbl.id=$page_tbl.id" . " AND $page_tbl.id=$recent_tbl.id" . " AND $page_tbl.id=$version_tbl.id AND latestversion=version" + . $exclude . " $orderby"; } } else { if ($include_empty) { $sql = "SELECT " . $this->page_tbl_fields - . " FROM $page_tbl $orderby"; + . " FROM $page_tbl" + . $exclude ? " WHERE $exclude" : '' + . " $orderby"; } else { $sql = "SELECT " . $this->page_tbl_fields . " FROM $nonempty_tbl, $page_tbl" . " WHERE $nonempty_tbl.id=$page_tbl.id" + . $exclude . " $orderby"; } } @@ -755,6 +762,49 @@ extends WikiDB_backend array_merge($this->page_tbl_field_list, $this->version_tbl_field_list)); } + /** + * Find referenced empty pages. + */ + function wanted_pages($exclude_from='', $exclude='', $sortby=false, $limit=false) { + $dbh = &$this->_dbh; + extract($this->_table_names); + if ($orderby = $this->sortby($sortby, 'db', array('pagename','wantedfrom'))) + $orderby = 'ORDER BY ' . $orderby; + + if ($exclude_from) // array of pagenames + $exclude_from = " AND linked.pagename NOT IN ".$this->_sql_set($exclude_from); + if ($exclude) // array of pagenames + $exclude = " AND $page_tbl.pagename NOT IN ".$this->_sql_set($exclude); + + $limit = $limit ? $limit : -1; + /* + all empty pages, independent of linkstatus: + select pagename as empty from page left join nonempty using(id) where isnull(nonempty.id); + only all empty pages, which have a linkto: + select page.pagename, linked.pagename as wantedfrom from link, page as linked + left join page on(link.linkto=page.id) left join nonempty on(link.linkto=nonempty.id) + where isnull(nonempty.id) and linked.id=link.linkfrom; + */ + $sql = "SELECT $page_tbl.pagename,linked.pagename as wantedfrom" + . " FROM $link_tbl,$page_tbl as linked " + . " LEFT JOIN $page_tbl ON($link_tbl.linkto=$page_tbl.id)" + . " LEFT JOIN $nonempty_tbl ON($link_tbl.linkto=$nonempty_tbl.id)" + . " WHERE ISNULL($nonempty_tbl.id) AND linked.id=$link_tbl.linkfrom" + . $exclude_from + . $exclude + . $orderby; + $result = $dbh->SelectLimit($sql, $limit); + return new WikiDB_backend_ADODB_iter($this, $result, array('pagename','wantedfrom')); + } + + function _sql_set(&$pagenames) { + $s = '('; + foreach ($pagenames as $p) { + $s .= ($this->_dbh->qstr($p).","); + } + return substr($s,0,-1).")"; + } + /** * Rename page in the database. */ @@ -1190,6 +1240,9 @@ extends WikiDB_backend_ADODB_generic_iter } // $Log: not supported by cvs2svn $ +// Revision 1.52 2004/11/17 20:07:17 rurban +// just whitespace +// // Revision 1.51 2004/11/15 15:57:37 rurban // silent cache warning // diff --git a/lib/WikiDB/backend/PearDB.php b/lib/WikiDB/backend/PearDB.php index 7a18b1711..767a92922 100644 --- a/lib/WikiDB/backend/PearDB.php +++ b/lib/WikiDB/backend/PearDB.php @@ -1,5 +1,5 @@ limit($limit); + $result = $dbh->limitQuery($sql, $from, $count); + } else { + $result = $dbh->query($sql); + } + return new WikiDB_backend_PearDB_iter($this, $result); + } + /** + * Find referenced empty pages. + */ + function wanted_pages($exclude_from='', $exclude='', $sortby=false, $limit=false) { + $dbh = &$this->_dbh; + extract($this->_table_names); + if ($orderby = $this->sortby($sortby, 'db', array('pagename','wantedfrom'))) + $orderby = 'ORDER BY ' . $orderby; + + if ($exclude_from) // array of pagenames + $exclude_from = " AND linked.pagename NOT IN ".$this->_sql_set($exclude_from); + if ($exclude) // array of pagenames + $exclude = " AND $page_tbl.pagename NOT IN ".$this->_sql_set($exclude); + + $sql = "SELECT $page_tbl.pagename,linked.pagename as wantedfrom" + . " FROM $link_tbl,$page_tbl as linked " + . " LEFT JOIN $page_tbl ON($link_tbl.linkto=$page_tbl.id)" + . " LEFT JOIN $nonempty_tbl ON($link_tbl.linkto=$nonempty_tbl.id)" + . " WHERE ISNULL($nonempty_tbl.id) AND linked.id=$link_tbl.linkfrom" + . $exclude_from + . $exclude + . $orderby; if ($limit) { - $result = $dbh->limitQuery($sql, 0, $limit); + list($from, $count) = $this->limit($limit); + $result = $dbh->limitQuery($sql, $from, $count * 3); } else { $result = $dbh->query($sql); } + return new WikiDB_backend_PearDB_generic_iter($this, $result); + } - return new WikiDB_backend_PearDB_iter($this, $result); + function _sql_set(&$pagenames) { + $s = '('; + foreach ($pagenames as $p) { + $s .= ("'".$this->_dbh->escapeSimple($p)."',"); + } + return substr($s,0,-1).")"; } /** @@ -1042,6 +1080,13 @@ extends WikiDB_backend_PearDB_generic_iter } // $Log: not supported by cvs2svn $ +// Revision 1.67 2004/11/10 19:32:24 rurban +// * optimize increaseHitCount, esp. for mysql. +// * prepend dirs to the include_path (phpwiki_dir for faster searches) +// * Pear_DB version logic (awful but needed) +// * fix broken ADODB quote +// * _extract_page_data simplification +// // Revision 1.66 2004/11/10 15:29:21 rurban // * requires newer Pear_DB (as the internal one): quote() uses now escapeSimple for strings // * ACCESS_LOG_SQL: fix cause request not yet initialized diff --git a/lib/WikiDB/backend/dbaBase.php b/lib/WikiDB/backend/dbaBase.php index 019474733..7959e4721 100644 --- a/lib/WikiDB/backend/dbaBase.php +++ b/lib/WikiDB/backend/dbaBase.php @@ -1,4 +1,4 @@ -set($pagename, (int)$latest . ':' . (int)$flags . ":$pagedata"); } - //FIXME: support limit - function get_all_pages($include_empty = false, $sortby=false, $limit=false) { + //FIXME: support limit, exclude + function get_all_pages($include_empty = false, $sortby=false, $limit=false, $exclude=false) { $pagedb = &$this->_pagedb; $pages = array(); for ($page = $pagedb->firstkey(); $page!== false; $page = $pagedb->nextkey()) { @@ -221,7 +221,8 @@ extends WikiDB_backend assert(!empty($page)); continue; } - + if ($exclude and in_array($page,$exclude)) continue; + if ($limit and count($pages) > $limit) break; if (!$include_empty) { if (!($data = $pagedb->get($page))) continue; list($latestversion,$flags,) = explode(':', $data, 3); @@ -231,7 +232,7 @@ extends WikiDB_backend } $pages[] = $page; } - $sortby = $this->sortby($sortby, 'db'); + $sortby = $this->sortby($sortby, 'db', array('pagename','mtime')); if ($sortby and !strstr($sortby, "hits ")) { // check for which column to sortby usort($pages, 'WikiDB_backend_dbaBase_sortby_'.str_replace(' ','_',$sortby)); } @@ -309,7 +310,10 @@ extends WikiDB_backend_iterator function count() { return count($this->_pages); } - + function asArray() { + reset($this->_pages); + return $this->_pages; + } function free() { $this->_pages = array(); } diff --git a/lib/WikiDB/backend/dumb/WantedPagesIter.php b/lib/WikiDB/backend/dumb/WantedPagesIter.php new file mode 100644 index 000000000..882d04895 --- /dev/null +++ b/lib/WikiDB/backend/dumb/WantedPagesIter.php @@ -0,0 +1,57 @@ +_allpages = $all_pages; + $this->_allpages_array = $all_pages->asArray(); + $this->_backend = &$backend; + if (!is_array($exclude)) + $this->exclude = $exclude ? PageList::explodePageList($exclude) : array(); + else + $this->exclude = $exclude; + } + + function next() { + while ($page = $this->_allpages->next()) { + $pagename = $page['pagename']; + $links = $this->_backend->get_links($pagename, false); + while ($link = $links->next()) { + if ($this->exclude and in_array($link['pagename'], $this->exclude)) continue; + // better membership for a pageiterator??? + if (! in_array($link['pagename'], $this->_allpages_array)) { + $links->free(); + $link['wantedfrom'] = $pagename; + return $link; + } + } + $links->free(); + } + return false; + } + + function free() { + unset($this->_allpages_array); + $this->_allpages->free(); + } +} + +// For emacs users +// Local Variables: +// mode: php +// tab-width: 8 +// c-basic-offset: 4 +// c-hanging-comment-ender-p: nil +// indent-tabs-mode: nil +// End: +?> \ No newline at end of file diff --git a/lib/plugin/WantedPages.php b/lib/plugin/WantedPages.php index 5262fcc9c..444e81a57 100644 --- a/lib/plugin/WantedPages.php +++ b/lib/plugin/WantedPages.php @@ -1,5 +1,5 @@ '[pagename]', // just for a single page. 'noheader' => false, - 'exclude_from' => _("PgsrcTranslation").','._("InterWikiMap"))); + 'exclude_from' => _("PgsrcTranslation").','._("InterWikiMap"), + 'limit' => '100', + 'paging' => 'auto')); } // info arg allows multiple columns @@ -76,19 +78,19 @@ extends WikiPlugin $pagelist->_wpagelist = array(); if (!$page) { - $allpages_iter = $dbi->getAllPages(false, false); - while ($page_handle = $allpages_iter->next()) { - $name = $page_handle->getName(); - if (in_array($name, $exclude_from)) continue; - $links = $page_handle->getPageLinks(true); // include_empty - while ($link_handle = $links->next()) { - if (! $dbi->isWikiPage($linkname = $link_handle->getName())) { - $pagelist->addPage($linkname); - //if (!array_key_exists($linkname, $this->_wpagelist)) - $pagelist->_wpagelist[$linkname][] = $name; - } - } + list($offset, $maxcount) = $pagelist->limit($limit); + $wanted_iter = $dbi->wantedPages($exclude_from, $exclude, $sortby, $limit); + while ($row = $wanted_iter->next()) { + $wanted = $row['pagename']; + $wantedfrom = $row['wantedfrom']; + // ignore duplicates: + if (empty($pagelist->_wpagelist[$wanted])) + $pagelist->addPage($wanted); + $pagelist->_wpagelist[$wanted][] = $wantedfrom; } + $wanted_iter->free(); + // update limit, but it's still a hack. + $pagelist->_options['limit'] = "$offset," . min($pagelist->getTotal(), $maxcount); } elseif ($dbi->isWikiPage($page)) { //only get WantedPages links for one page $page_handle = $dbi->getPage($page); @@ -108,11 +110,9 @@ extends WikiPlugin }*/ if (!$noheader) { if ($page) - $pagelist->setCaption(sprintf(_("Wanted Pages for %s (%d total):"), - $page, count($pagelist->_wpagelist))); + $pagelist->setCaption(sprintf(_("Wanted Pages for %s:"), $page)); else - $pagelist->setCaption(sprintf(_("Wanted Pages in this wiki (%d total):"), - count($pagelist->_wpagelist))); + $pagelist->setCaption(sprintf(_("Wanted Pages in this wiki:"))); } // reference obviously doesn't work, so force an update to add _wpagelist to parentobj if (isset($pagelist->_columns[1]) and $pagelist->_columns[1]->_field == 'wanted') @@ -131,7 +131,7 @@ class _PageList_Column_WantedPages_wanted extends _PageList_Column { $html = false; foreach($this->parentobj->_wpagelist[$page->getName()] as $page) { if ($html) - $html->pushContent(',', WikiLink($page)); + $html->pushContent(', ', WikiLink($page)); else $html = HTML(WikiLink($page)); } @@ -140,6 +140,14 @@ class _PageList_Column_WantedPages_wanted extends _PageList_Column { } // $Log: not supported by cvs2svn $ +// Revision 1.13 2004/11/20 11:28:49 rurban +// fix a yet unused PageList customPageListColumns bug (merge class not decl to _types) +// change WantedPages to use PageList +// change WantedPages to print the list of referenced pages, not just the count. +// the old version was renamed to WantedPagesOld +// fix and add handling of most standard PageList arguments (limit, exclude, ...) +// TODO: pagename sorting, dumb/WantedPagesIter and SQL optimization +// // Revision 1.12 2004/10/04 23:39:34 rurban // just aesthetics // -- 2.45.0