4 * Copyright (C) 2001-2009 $ThePhpWikiProgrammingTeam
5 * Copyright (C) 2010 Alain Peyrat, Alcatel-Lucent
7 * This file is part of PhpWiki.
9 * PhpWiki is free software; you can redistribute it and/or modify
10 * it under the terms of the GNU General Public License as published by
11 * the Free Software Foundation; either version 2 of the License, or
12 * (at your option) any later version.
14 * PhpWiki is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 * GNU General Public License for more details.
19 * You should have received a copy of the GNU General Public License along
20 * with PhpWiki; if not, write to the Free Software Foundation, Inc.,
21 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
25 * Standard Alcatel-Lucent disclaimer for contributing to open source
27 * "The Fusionforge backend ("Contribution") has not been tested and/or
28 * validated for release as or in products, combinations with products or
29 * other commercial use. Any use of the Contribution is entirely made at
30 * the user's own responsibility and the user can not rely on any features,
31 * functionalities or performances Alcatel-Lucent has attributed to the
34 * THE CONTRIBUTION BY ALCATEL-LUCENT IS PROVIDED AS IS, WITHOUT WARRANTY
35 * OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
36 * WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, COMPLIANCE,
37 * NON-INTERFERENCE AND/OR INTERWORKING WITH THE SOFTWARE TO WHICH THE
38 * CONTRIBUTION HAS BEEN MADE, TITLE AND NON-INFRINGEMENT. IN NO EVENT SHALL
39 * ALCATEL-LUCENT BE LIABLE FOR ANY DAMAGES OR OTHER LIABLITY, WHETHER IN
40 * CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
41 * CONTRIBUTION OR THE USE OR OTHER DEALINGS IN THE CONTRIBUTION, WHETHER
42 * TOGETHER WITH THE SOFTWARE TO WHICH THE CONTRIBUTION RELATES OR ON A STAND
46 require_once 'lib/ErrorManager.php';
47 require_once 'lib/WikiDB/backend/PearDB_pgsql.php';
49 class WikiDB_backend_PearDB_ffpgsql
50 extends WikiDB_backend_PearDB_pgsql
52 function __construct($dbparams)
54 $dbparams['dsn'] = str_replace('ffpgsql:', 'pgsql:', $dbparams['dsn']);
55 parent::__construct($dbparams);
58 $p = strlen($page_prefix) + 1;
59 $page_tbl = $this->_table_names['page_tbl'];
60 $this->page_tbl_fields = "$page_tbl.id AS id, substring($page_tbl.pagename from $p) AS pagename, $page_tbl.hits AS hits";
62 pg_set_client_encoding("iso-8859-1");
65 function get_all_pagenames()
68 extract($this->_table_names);
70 $p = strlen($page_prefix) + 1;
71 return $dbh->getCol("SELECT substring(pagename from $p)"
72 . " FROM $nonempty_tbl, $page_tbl"
73 . " WHERE $nonempty_tbl.id=$page_tbl.id"
74 . " AND substring($page_tbl.pagename from 0 for $p) = '$page_prefix'");
78 * filter (nonempty pages) currently ignored
80 function numPages($filter = false, $exclude = '')
83 extract($this->_table_names);
85 $p = strlen($page_prefix) + 1;
86 return $dbh->getOne("SELECT count(*)"
87 . " FROM $nonempty_tbl, $page_tbl"
88 . " WHERE $nonempty_tbl.id=$page_tbl.id"
89 . " AND substring($page_tbl.pagename from 0 for $p) = '$page_prefix'");
93 * Read page information from database.
95 function get_pagedata($pagename)
98 return parent::get_pagedata($page_prefix . $pagename);
101 function update_pagedata($pagename, $newdata)
104 $page_tbl = $this->_table_names['page_tbl'];
106 // Hits is the only thing we can update in a fast manner.
107 if (count($newdata) == 1 && isset($newdata['hits'])) {
108 // Note that this will fail silently if the page does not
109 // have a record in the page table. Since it's just the
110 // hit count, who cares?
112 $pagename = $page_prefix . $pagename;
113 $dbh->query(sprintf("UPDATE $page_tbl SET hits=%d WHERE pagename='%s'",
114 $newdata['hits'], $dbh->escapeSimple($pagename)));
118 $this->lock(array($page_tbl), true);
119 $data = $this->get_pagedata($pagename);
122 $this->_get_pageid($pagename, true); // Creates page record
125 if (isset($data['hits'])) {
126 $hits = (int)$data['hits'];
127 unset($data['hits']);
132 foreach ($newdata as $key => $val) {
135 else if (empty($val))
141 /* Portability issue -- not all DBMS supports huge strings
142 * so we need to 'bind' instead of building a simple SQL statment.
143 * Note that we do not need to escapeSimple when we bind
144 $dbh->query(sprintf("UPDATE $page_tbl"
145 . " SET hits=%d, pagedata='%s'"
146 . " WHERE pagename='%s'",
148 $dbh->escapeSimple($this->_serialize($data)),
149 $dbh->escapeSimple($pagename)));
152 $pagename = $page_prefix . $pagename;
153 $dbh->query("UPDATE $page_tbl"
154 . " SET hits=?, pagedata=?"
155 . " WHERE pagename=?",
156 array($hits, $this->_serialize($data), $pagename));
157 $this->unlock(array($page_tbl));
160 function get_latest_version($pagename)
163 return parent::get_latest_version($page_prefix . $pagename);
166 function get_previous_version($pagename, $version)
169 return parent::get_previous_version($page_prefix . $pagename, $version);
175 * @param string $pagename Name of the page
176 * @param int $version Which version to get
177 * @param bool $want_content Do we need content?
179 * @return array hash The version data, or false if specified version does not
182 function get_versiondata($pagename, $version, $want_content = false)
185 extract($this->_table_names);
186 extract($this->_expressions);
188 // assert(is_string($pagename) and $pagename != "");
189 // assert($version > 0);
191 // FIXME: optimization: sometimes don't get page data?
193 $fields = $this->page_tbl_fields
194 . ",$page_tbl.pagedata AS pagedata,"
195 . $this->version_tbl_fields;
197 $fields = $this->page_tbl_fields . ","
198 . "mtime, minor_edit, versiondata,"
199 . "$iscontent AS have_content";
203 $pagename = $page_prefix . $pagename;
204 $result = $dbh->getRow(sprintf("SELECT $fields"
205 . " FROM $page_tbl, $version_tbl"
206 . " WHERE $page_tbl.id=$version_tbl.id"
207 . " AND pagename='%s'"
209 $dbh->escapeSimple($pagename), $version),
212 return $this->_extract_version_data($result);
215 function get_cached_html($pagename)
218 return parent::get_cached_html($page_prefix . $pagename);
221 function set_cached_html($pagename, $data)
224 parent::set_cached_html($page_prefix . $pagename, $data);
227 function _get_pageid($pagename, $create_if_missing = false)
232 $cache =& $request->_dbi->_cache->_id_cache;
233 if (isset($cache[$pagename])) {
234 if ($cache[$pagename] or !$create_if_missing) {
235 return $cache[$pagename];
239 // attributes play this game.
240 if ($pagename === '') return 0;
243 $page_tbl = $this->_table_names['page_tbl'];
245 $pagename = $page_prefix . $pagename;
247 $query = sprintf("SELECT id FROM $page_tbl WHERE pagename='%s'",
248 $dbh->escapeSimple($pagename));
250 if (!$create_if_missing)
251 return $dbh->getOne($query);
253 $id = $dbh->getOne($query);
255 $this->lock(array($page_tbl), true); // write lock
256 $max_id = $dbh->getOne("SELECT MAX(id) FROM $page_tbl");
258 // requires createSequence and on mysql lock the interim table ->getSequenceName
259 //$id = $dbh->nextId($page_tbl . "_id");
260 $dbh->query(sprintf("INSERT INTO $page_tbl"
261 . " (id,pagename,hits)"
262 . " VALUES (%d,'%s',0)",
263 $id, $dbh->escapeSimple($pagename)));
264 $this->unlock(array($page_tbl));
270 * Delete page completely from the database.
272 function purge_page($pagename)
275 extract($this->_table_names);
278 if (($id = $this->_get_pageid($pagename, false))) {
279 $dbh->query("DELETE FROM $nonempty_tbl WHERE id=$id");
280 $dbh->query("DELETE FROM $recent_tbl WHERE id=$id");
281 $dbh->query("DELETE FROM $version_tbl WHERE id=$id");
282 $dbh->query("DELETE FROM $link_tbl WHERE linkfrom=$id");
283 $nlinks = $dbh->getOne("SELECT COUNT(*) FROM $link_tbl WHERE linkto=$id");
285 // We're still in the link table (dangling link) so we can't delete this
287 $dbh->query("UPDATE $page_tbl SET hits=0, pagedata='' WHERE id=$id");
290 $dbh->query("DELETE FROM $page_tbl WHERE id=$id");
294 $result = -1; // already purged or not existing
301 * Find pages which link to or are linked from a page.
303 * TESTME relations: get_links is responsible to add the relation to the pagehash
304 * as 'linkrelation' key as pagename. See WikiDB_PageIterator::next
305 * if (isset($next['linkrelation']))
307 function get_links($pagename, $reversed = true, $include_empty = false,
308 $sortby = '', $limit = '', $exclude = '',
309 $want_relations = false)
312 extract($this->_table_names);
315 list($have, $want) = array('linkee', 'linker');
317 list($have, $want) = array('linker', 'linkee');
318 $orderby = $this->sortby($sortby, 'db', array('pagename'));
319 if ($orderby) $orderby = " ORDER BY $want." . $orderby;
320 if ($exclude) // array of pagenames
321 $exclude = " AND $want.pagename NOT IN " . $this->_sql_set($exclude);
326 $p = strlen($page_prefix) + 1;
328 $qpagename = $dbh->escapeSimple($pagename);
329 // MeV+APe 2007-11-14
330 // added "dummyname" so that database accepts "ORDER BY"
331 $sql = "SELECT DISTINCT $want.id AS id, substring($want.pagename from $p) AS pagename, $want.pagename AS dummyname,"
332 . ($want_relations ? " related.pagename as linkrelation" : " $want.hits AS hits")
334 . (!$include_empty ? "$nonempty_tbl, " : '')
335 . " $page_tbl linkee, $page_tbl linker, $link_tbl "
336 . ($want_relations ? " JOIN $page_tbl related ON ($link_tbl.relation=related.id)" : '')
337 . " WHERE linkfrom=linker.id AND linkto=linkee.id"
338 . " AND $have.pagename='$page_prefix$qpagename'"
339 . " AND substring($want.pagename from 0 for $p) = '$page_prefix'"
340 . (!$include_empty ? " AND $nonempty_tbl.id=$want.id" : "")
341 //. " GROUP BY $want.id"
345 // extract from,count from limit
346 list($from, $count) = $this->limit($limit);
347 $result = $dbh->limitQuery($sql, $from, $count);
349 $result = $dbh->query($sql);
352 return new WikiDB_backend_PearDB_iter($this, $result);
355 public function get_all_pages($include_empty = false,
356 $sortby = '', $limit = '', $exclude = '')
359 extract($this->_table_names);
362 $p = strlen($page_prefix) + 1;
364 $orderby = $this->sortby($sortby, 'db');
365 if ($orderby) $orderby = ' ORDER BY ' . $orderby;
366 if ($exclude) // array of pagenames
367 $exclude = " AND $page_tbl.pagename NOT IN " . $this->_sql_set($exclude);
371 if (strstr($orderby, 'mtime ')) { // multiple columns possible
372 if ($include_empty) {
374 . $this->page_tbl_fields
375 . " FROM $page_tbl, $recent_tbl, $version_tbl"
376 . " WHERE $page_tbl.id=$recent_tbl.id"
377 . " AND $page_tbl.id=$version_tbl.id AND latestversion=version"
378 . " AND substring($page_tbl.pagename from 0 for $p) = '$page_prefix'"
383 . $this->page_tbl_fields
384 . " FROM $nonempty_tbl, $page_tbl, $recent_tbl, $version_tbl"
385 . " WHERE $nonempty_tbl.id=$page_tbl.id"
386 . " AND $page_tbl.id=$recent_tbl.id"
387 . " AND $page_tbl.id=$version_tbl.id AND latestversion=version"
388 . " AND substring($page_tbl.pagename from 0 for $p) = '$page_prefix'"
393 if ($include_empty) {
395 . $this->page_tbl_fields
397 . ($exclude ? " WHERE $exclude" : '')
398 . ($exclude ? " AND " : " WHERE ")
399 . " substring($page_tbl.pagename from 0 for $p) = '$page_prefix'"
403 . $this->page_tbl_fields
404 . " FROM $nonempty_tbl, $page_tbl"
405 . " WHERE $nonempty_tbl.id=$page_tbl.id"
406 . " AND substring($page_tbl.pagename from 0 for $p) = '$page_prefix'"
411 if ($limit && $orderby) {
412 // extract from,count from limit
413 list($from, $count) = $this->limit($limit);
414 $result = $dbh->limitQuery($sql, $from, $count);
415 $options = array('limit_by_db' => 1);
417 $result = $dbh->query($sql);
418 $options = array('limit_by_db' => 0);
420 return new WikiDB_backend_PearDB_iter($this, $result, $options);
424 * Find highest or lowest hit counts.
426 public function most_popular($limit = 20, $sortby = '-hits')
429 extract($this->_table_names);
431 $p = strlen($page_prefix) + 1;
437 $order = "hits DESC";
438 $where = " AND hits > 0";
441 if ($sortby != '-hits') {
442 if ($order = $this->sortby($sortby, 'db'))
443 $orderby = " ORDER BY " . $order;
445 $orderby = " ORDER BY $order";
447 //$limitclause = $limit ? " LIMIT $limit" : '';
449 . $this->page_tbl_fields
450 . " FROM $nonempty_tbl, $page_tbl"
451 . " WHERE $nonempty_tbl.id=$page_tbl.id"
452 . " AND substring($page_tbl.pagename from 0 for $p) = '$page_prefix'"
456 list($from, $count) = $this->limit($limit);
457 $result = $dbh->limitQuery($sql, $from, $count);
459 $result = $dbh->query($sql);
461 return new WikiDB_backend_PearDB_iter($this, $result);
465 * Find recent changes.
467 public function most_recent($params)
471 $include_minor_revisions = false;
472 $exclude_major_revisions = false;
473 $include_all_revisions = false;
477 extract($this->_table_names);
481 $pick[] = "mtime >= $since";
483 if ($include_all_revisions) {
484 // Include all revisions of each page.
485 $table = "$page_tbl, $version_tbl";
486 $join_clause = "$page_tbl.id=$version_tbl.id";
488 if ($exclude_major_revisions) {
489 // Include only minor revisions
490 $pick[] = "minor_edit <> 0";
491 } elseif (!$include_minor_revisions) {
492 // Include only major revisions
493 $pick[] = "minor_edit = 0";
496 $table = "$page_tbl, $recent_tbl";
497 $join_clause = "$page_tbl.id=$recent_tbl.id";
498 $table .= ", $version_tbl";
499 $join_clause .= " AND $version_tbl.id=$page_tbl.id";
501 if ($exclude_major_revisions) {
502 // Include only most recent minor revision
503 $pick[] = 'version=latestminor';
504 } elseif (!$include_minor_revisions) {
505 // Include only most recent major revision
506 $pick[] = 'version=latestmajor';
508 // Include only the latest revision (whether major or minor).
509 $pick[] = 'version=latestversion';
517 // $limitclause = $limit ? " LIMIT $limit" : '';
518 $where_clause = $join_clause;
520 $where_clause .= " AND " . join(" AND ", $pick);
523 $p = strlen($page_prefix) + 1;
525 // FIXME: use SQL_BUFFER_RESULT for mysql?
527 . $this->page_tbl_fields . ", " . $this->version_tbl_fields
529 . " WHERE $where_clause"
530 . " AND substring($page_tbl.pagename from 0 for $p) = '$page_prefix'"
531 . " ORDER BY mtime $order";
533 list($from, $count) = $this->limit($limit);
534 $result = $dbh->limitQuery($sql, $from, $count);
536 $result = $dbh->query($sql);
538 return new WikiDB_backend_PearDB_iter($this, $result);
542 * Find referenced empty pages.
544 function wanted_pages($exclude_from = '', $exclude = '', $sortby = '', $limit = '')
547 extract($this->_table_names);
549 $p = strlen($page_prefix) + 1;
550 if ($orderby = $this->sortby($sortby, 'db', array('pagename', 'wantedfrom')))
551 $orderby = 'ORDER BY ' . $orderby;
553 if ($exclude_from) // array of pagenames
554 $exclude_from = " AND pp.pagename NOT IN " . $this->_sql_set($exclude_from);
555 if ($exclude) // array of pagenames
556 $exclude = " AND p.pagename NOT IN " . $this->_sql_set($exclude);
557 $sql = "SELECT substring(p.pagename from $p) AS wantedfrom, substring(pp.pagename from $p) AS pagename"
558 . " FROM $page_tbl p, $link_tbl linked"
559 . " LEFT JOIN $page_tbl pp ON linked.linkto = pp.id"
560 . " LEFT JOIN $nonempty_tbl ne ON linked.linkto = ne.id"
561 . " WHERE ne.id IS NULL"
562 . " AND p.id = linked.linkfrom"
563 . " AND substring(p.pagename from 0 for $p) = '$page_prefix'"
564 . " AND substring(pp.pagename from 0 for $p) = '$page_prefix'"
569 list($from, $count) = $this->limit($limit);
570 $result = $dbh->limitQuery($sql, $from, $count * 3);
572 $result = $dbh->query($sql);
574 return new WikiDB_backend_PearDB_generic_iter($this, $result);
578 * Rename page in the database.
580 function rename_page($pagename, $to)
583 extract($this->_table_names);
586 if (($id = $this->_get_pageid($pagename, false))) {
587 if ($new = $this->_get_pageid($to, false)) {
589 // This page does not exist (already verified before), but exists in the page table.
590 // So we delete this page.
591 $dbh->query("DELETE FROM $nonempty_tbl WHERE id=$new");
592 $dbh->query("DELETE FROM $recent_tbl WHERE id=$new");
593 $dbh->query("DELETE FROM $version_tbl WHERE id=$new");
594 // We have to fix all referring tables to the old id
595 $dbh->query("UPDATE $link_tbl SET linkfrom=$id WHERE linkfrom=$new");
596 $dbh->query("UPDATE $link_tbl SET linkto=$id WHERE linkto=$new");
597 $dbh->query("DELETE FROM $page_tbl WHERE id=$new");
600 $dbh->query(sprintf("UPDATE $page_tbl SET pagename='%s' WHERE id=$id",
601 $dbh->escapeSimple($page_prefix . $to)));
607 function is_wiki_page($pagename)
610 return parent::is_wiki_page($page_prefix . $pagename);
613 function increaseHitCount($pagename)
616 parent::increaseHitCount($page_prefix . $pagename);
622 function _serialize($data)
624 return WikiDB_backend_PearDB::_serialize($data);
629 * NOTE: Disable vacuum, wikiuser is not the table owner
637 * Text search (title or full text)
639 public function text_search($search, $fulltext = false,
640 $sortby = '', $limit = '', $exclude = '')
643 extract($this->_table_names);
645 $len = strlen($page_prefix) + 1;
646 $orderby = $this->sortby($sortby, 'db');
647 if ($sortby and $orderby) $orderby = ' ORDER BY ' . $orderby;
649 $searchclass = get_class($this) . "_search";
650 // no need to define it everywhere and then fallback. memory!
651 if (!class_exists($searchclass))
652 $searchclass = "WikiDB_backend_PearDB_search";
653 $searchobj = new $searchclass($search, $dbh);
655 $table = "$nonempty_tbl, $page_tbl";
656 $join_clause = "$nonempty_tbl.id=$page_tbl.id";
657 $fields = $this->page_tbl_fields;
660 $table .= ", $recent_tbl";
661 $join_clause .= " AND $page_tbl.id=$recent_tbl.id";
663 $table .= ", $version_tbl";
664 $join_clause .= " AND $page_tbl.id=$version_tbl.id AND latestversion=version";
666 $fields .= ", $page_tbl.pagedata as pagedata, " . $this->version_tbl_fields;
667 // TODO: title still ignored, need better rank and subselect
668 $callback = new WikiMethodCb($searchobj, "_fulltext_match_clause");
669 $search_string = $search->makeTsearch2SqlClauseObj($callback);
670 $search_string = str_replace('%', '', $search_string);
671 $search_clause = "substring(plugin_wiki_page.pagename from 0 for $len) = '$page_prefix') AND (";
673 $search_clause .= "idxFTI @@ plainto_tsquery('english', '$search_string')";
675 $orderby = " ORDER BY ts_rank(idxFTI, plainto_tsquery('english', '$search_string')) DESC";
677 $callback = new WikiMethodCb($searchobj, "_pagename_match_clause");
678 $search_clause = "substring(plugin_wiki_page.pagename from 0 for $len) = '$page_prefix') AND (";
679 $search_clause .= $search->makeSqlClauseObj($callback);
682 $sql = "SELECT $fields FROM $table"
683 . " WHERE $join_clause"
684 . " AND ($search_clause)"
687 list($from, $count) = $this->limit($limit);
688 $result = $dbh->limitQuery($sql, $from, $count);
690 $result = $dbh->query($sql);
693 $iter = new WikiDB_backend_PearDB_iter($this, $result);
694 $iter->stoplisted = @$searchobj->stoplisted;
698 function exists_link($pagename, $link, $reversed = false)
701 extract($this->_table_names);
704 list($have, $want) = array('linkee', 'linker');
706 list($have, $want) = array('linker', 'linkee');
707 $qpagename = $dbh->escapeSimple($pagename);
708 $qlink = $dbh->escapeSimple($link);
709 $row = $dbh->GetRow("SELECT $want.pagename as result"
710 . " FROM $link_tbl, $page_tbl linker, $page_tbl linkee, $nonempty_tbl"
711 . " WHERE linkfrom=linker.id AND linkto=linkee.id"
712 . " AND $have.pagename='$qpagename'"
713 . " AND $want.pagename='$qlink'"
715 return $row['result'] ? 1 : 0;
719 class WikiDB_backend_PearDB_ffpgsql_search
720 extends WikiDB_backend_PearDB_pgsql_search
722 function _pagename_match_clause($node)
724 $word = $node->sql();
725 // @alu: use _quote maybe instead of direct pg_escape_string
726 $word = pg_escape_string($word);
728 $len = strlen($page_prefix) + 1;
729 if ($node->op == 'REGEX') { // posix regex extensions
730 return ($this->_case_exact
731 ? "substring(pagename from $len) ~* '$word'"
732 : "substring(pagename from $len) ~ '$word'");
734 return ($this->_case_exact
735 ? "substring(pagename from $len) LIKE '$word'"
736 : "substring(pagename from $len) ILIKE '$word'");
741 * use tsearch2. See schemas/psql-tsearch2.sql and /usr/share/postgresql/contrib/tsearch2.sql
742 * TODO: don't parse the words into nodes. rather replace "[ +]" with & and "-" with "!" and " or " with "|"
743 * tsearch2 query language: @@ "word | word", "word & word", ! word
744 * ~* '.*something that does not exist.*'
746 * phrase search for "history lesson":
748 * SELECT id FROM tab WHERE ts_idx_col @@ to_tsquery('history&lesson')
749 * AND text_col ~* '.*history\\s+lesson.*';
751 * The full-text index will still be used, and the regex will be used to
752 * prune the results afterwards.
754 function _fulltext_match_clause($node)
756 $word = strtolower($node->word);
757 // $word = str_replace(" ", "&", $word); // phrase fix
759 // @alu: use _quote maybe instead of direct pg_escape_string
760 $word = pg_escape_string($word);
770 // c-hanging-comment-ender-p: nil
771 // indent-tabs-mode: nil