2 // $Id: PearDB_ffpgsql.php 8091 2011-06-01 13:44:50Z vargenau $
5 * Copyright (C) 2001-2009 $ThePhpWikiProgrammingTeam
6 * Copyright (C) 2010 Alain Peyrat, Alcatel-Lucent
8 * This file is part of PhpWiki.
10 * PhpWiki is free software; you can redistribute it and/or modify
11 * it under the terms of the GNU General Public License as published by
12 * the Free Software Foundation; either version 2 of the License, or
13 * (at your option) any later version.
15 * PhpWiki is distributed in the hope that it will be useful,
16 * but WITHOUT ANY WARRANTY; without even the implied warranty of
17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 * GNU General Public License for more details.
20 * You should have received a copy of the GNU General Public License along
21 * with PhpWiki; if not, write to the Free Software Foundation, Inc.,
22 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
26 * Standard Alcatel-Lucent disclaimer for contributing to open source
28 * "The Fusionforge backend ("Contribution") has not been tested and/or
29 * validated for release as or in products, combinations with products or
30 * other commercial use. Any use of the Contribution is entirely made at
31 * the user's own responsibility and the user can not rely on any features,
32 * functionalities or performances Alcatel-Lucent has attributed to the
35 * THE CONTRIBUTION BY ALCATEL-LUCENT IS PROVIDED AS IS, WITHOUT WARRANTY
36 * OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
37 * WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, COMPLIANCE,
38 * NON-INTERFERENCE AND/OR INTERWORKING WITH THE SOFTWARE TO WHICH THE
39 * CONTRIBUTION HAS BEEN MADE, TITLE AND NON-INFRINGEMENT. IN NO EVENT SHALL
40 * ALCATEL-LUCENT BE LIABLE FOR ANY DAMAGES OR OTHER LIABLITY, WHETHER IN
41 * CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
42 * CONTRIBUTION OR THE USE OR OTHER DEALINGS IN THE CONTRIBUTION, WHETHER
43 * TOGETHER WITH THE SOFTWARE TO WHICH THE CONTRIBUTION RELATES OR ON A STAND
48 require_once 'lib/ErrorManager.php';
49 require_once 'lib/WikiDB/backend/PearDB_pgsql.php';
51 class WikiDB_backend_PearDB_ffpgsql
52 extends WikiDB_backend_PearDB_pgsql
54 function WikiDB_backend_PearDB_ffpgsql($dbparams) {
55 $dbparams['dsn'] = str_replace('ffpgsql:', 'pgsql:', $dbparams['dsn']);
56 parent::WikiDB_backend_PearDB_pgsql($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() {
67 extract($this->_table_names);
70 return $dbh->getCol("SELECT substring(pagename from $p)"
71 . " FROM $nonempty_tbl, $page_tbl"
72 . " WHERE $nonempty_tbl.id=$page_tbl.id"
73 . " AND substring($page_tbl.pagename from 0 for $p) = '$pat'");
76 function numPages($filter=false, $exclude='') {
78 extract($this->_table_names);
81 return $dbh->getOne("SELECT count(*)"
82 . " FROM $nonempty_tbl, $page_tbl"
83 . " WHERE $nonempty_tbl.id=$page_tbl.id"
84 . " AND substring($page_tbl.pagename from 0 for $p) = '$pat'");
87 function get_pagedata($pagename) {
88 return parent::get_pagedata(PAGE_PREFIX.$pagename);
91 function update_pagedata($pagename, $newdata) {
93 $page_tbl = $this->_table_names['page_tbl'];
95 // Hits is the only thing we can update in a fast manner.
96 if (count($newdata) == 1 && isset($newdata['hits'])) {
97 // Note that this will fail silently if the page does not
98 // have a record in the page table. Since it's just the
99 // hit count, who cares?
100 $pagename = PAGE_PREFIX.$pagename;
101 $dbh->query(sprintf("UPDATE $page_tbl SET hits=%d WHERE pagename='%s'",
102 $newdata['hits'], $dbh->escapeSimple($pagename)));
106 $this->lock(array($page_tbl), true);
107 $data = $this->get_pagedata($pagename);
110 $this->_get_pageid($pagename, true); // Creates page record
113 if (isset($data['hits'])) {
114 $hits = (int)$data['hits'];
115 unset($data['hits']);
120 foreach ($newdata as $key => $val) {
123 else if (empty($val))
129 /* Portability issue -- not all DBMS supports huge strings
130 * so we need to 'bind' instead of building a simple SQL statment.
131 * Note that we do not need to escapeSimple when we bind
132 $dbh->query(sprintf("UPDATE $page_tbl"
133 . " SET hits=%d, pagedata='%s'"
134 . " WHERE pagename='%s'",
136 $dbh->escapeSimple($this->_serialize($data)),
137 $dbh->escapeSimple($pagename)));
139 $pagename = PAGE_PREFIX.$pagename;
140 $dbh->query("UPDATE $page_tbl"
141 . " SET hits=?, pagedata=?"
142 . " WHERE pagename=?",
143 array($hits, $this->_serialize($data), $pagename));
144 $this->unlock(array($page_tbl));
147 function get_latest_version($pagename) {
148 return parent::get_latest_version(PAGE_PREFIX.$pagename);
151 function get_previous_version($pagename, $version) {
152 return parent::get_previous_version(PAGE_PREFIX.$pagename, $version);
155 function get_versiondata($pagename, $version, $want_content = false) {
157 extract($this->_table_names);
158 extract($this->_expressions);
160 // assert(is_string($pagename) and $pagename != "");
161 // assert($version > 0);
163 //trigger_error("GET_REVISION $pagename $version $want_content", E_USER_NOTICE);
164 // FIXME: optimization: sometimes don't get page data?
166 $fields = $this->page_tbl_fields
167 . ",$page_tbl.pagedata as pagedata,"
168 . $this->version_tbl_fields;
171 $fields = $this->page_tbl_fields . ","
172 . "mtime, minor_edit, versiondata,"
173 . "$iscontent AS have_content";
176 $pagename = PAGE_PREFIX.$pagename;
177 $result = $dbh->getRow(sprintf("SELECT $fields"
178 . " FROM $page_tbl, $version_tbl"
179 . " WHERE $page_tbl.id=$version_tbl.id"
180 . " AND pagename='%s'"
182 $dbh->escapeSimple($pagename), $version),
185 return $this->_extract_version_data($result);
188 function get_cached_html($pagename) {
189 return parent::get_cached_html(PAGE_PREFIX.$pagename);
192 function set_cached_html($pagename, $data) {
193 return parent::set_cached_html(PAGE_PREFIX.$pagename, $data);
196 function _get_pageid($pagename, $create_if_missing = false) {
200 $cache =& $request->_dbi->_cache->_id_cache;
201 if (isset($cache[$pagename])) {
202 if ($cache[$pagename] or !$create_if_missing) {
203 return $cache[$pagename];
207 // attributes play this game.
208 if ($pagename === '') return 0;
211 $page_tbl = $this->_table_names['page_tbl'];
212 $pagename = PAGE_PREFIX.$pagename;
214 $query = sprintf("SELECT id FROM $page_tbl WHERE pagename='%s'",
215 $dbh->escapeSimple($pagename));
217 if (!$create_if_missing)
218 return $dbh->getOne($query);
220 $id = $dbh->getOne($query);
222 $this->lock(array($page_tbl), true); // write lock
223 $max_id = $dbh->getOne("SELECT MAX(id) FROM $page_tbl");
225 // requires createSequence and on mysql lock the interim table ->getSequenceName
226 //$id = $dbh->nextId($page_tbl . "_id");
227 $dbh->query(sprintf("INSERT INTO $page_tbl"
228 . " (id,pagename,hits)"
229 . " VALUES (%d,'%s',0)",
230 $id, $dbh->escapeSimple($pagename)));
231 $this->unlock(array($page_tbl));
236 function purge_page($pagename) {
238 extract($this->_table_names);
241 if ( ($id = $this->_get_pageid($pagename, false)) ) {
242 $dbh->query("DELETE FROM $nonempty_tbl WHERE id=$id");
243 $dbh->query("DELETE FROM $recent_tbl WHERE id=$id");
244 $dbh->query("DELETE FROM $version_tbl WHERE id=$id");
245 $dbh->query("DELETE FROM $link_tbl WHERE linkfrom=$id");
246 $nlinks = $dbh->getOne("SELECT COUNT(*) FROM $link_tbl WHERE linkto=$id");
248 // We're still in the link table (dangling link) so we can't delete this
250 $dbh->query("UPDATE $page_tbl SET hits=0, pagedata='' WHERE id=$id");
254 $dbh->query("DELETE FROM $page_tbl WHERE id=$id");
258 $result = -1; // already purged or not existing
264 function get_links($pagename, $reversed=true, $include_empty=false,
265 $sortby='', $limit='', $exclude='',
266 $want_relations = false)
269 extract($this->_table_names);
272 list($have,$want) = array('linkee', 'linker');
274 list($have,$want) = array('linker', 'linkee');
275 $orderby = $this->sortby($sortby, 'db', array('pagename'));
276 if ($orderby) $orderby = " ORDER BY $want." . $orderby;
277 if ($exclude) // array of pagenames
278 $exclude = " AND $want.pagename NOT IN ".$this->_sql_set($exclude);
285 $qpagename = $dbh->escapeSimple($pagename);
286 // MeV+APe 2007-11-14
287 // added "dummyname" so that database accepts "ORDER BY"
288 $sql = "SELECT DISTINCT $want.id AS id, substring($want.pagename from $p) AS pagename, $want.pagename AS dummyname,"
289 . ($want_relations ? " related.pagename as linkrelation" : " $want.hits AS hits")
291 . (!$include_empty ? "$nonempty_tbl, " : '')
292 . " $page_tbl linkee, $page_tbl linker, $link_tbl "
293 . ($want_relations ? " JOIN $page_tbl related ON ($link_tbl.relation=related.id)" : '')
294 . " WHERE linkfrom=linker.id AND linkto=linkee.id"
295 . " AND $have.pagename='$pat$qpagename'"
296 . " AND substring($want.pagename from 0 for $p) = '$pat'"
297 . (!$include_empty ? " AND $nonempty_tbl.id=$want.id" : "")
298 //. " GROUP BY $want.id"
302 // extract from,count from limit
303 list($from,$count) = $this->limit($limit);
304 $result = $dbh->limitQuery($sql, $from, $count);
306 $result = $dbh->query($sql);
309 return new WikiDB_backend_PearDB_iter($this, $result);
312 function get_all_pages($include_empty=false, $sortby='', $limit='', $exclude='') {
314 extract($this->_table_names);
319 $orderby = $this->sortby($sortby, 'db');
320 if ($orderby) $orderby = ' ORDER BY ' . $orderby;
321 if ($exclude) // array of pagenames
322 $exclude = " AND $page_tbl.pagename NOT IN ".$this->_sql_set($exclude);
326 if (strstr($orderby, 'mtime ')) { // multiple columns possible
327 if ($include_empty) {
329 . $this->page_tbl_fields
330 . " FROM $page_tbl, $recent_tbl, $version_tbl"
331 . " WHERE $page_tbl.id=$recent_tbl.id"
332 . " AND $page_tbl.id=$version_tbl.id AND latestversion=version"
333 . " AND substring($page_tbl.pagename from 0 for $p) = '$pat'"
339 . $this->page_tbl_fields
340 . " FROM $nonempty_tbl, $page_tbl, $recent_tbl, $version_tbl"
341 . " WHERE $nonempty_tbl.id=$page_tbl.id"
342 . " AND $page_tbl.id=$recent_tbl.id"
343 . " AND $page_tbl.id=$version_tbl.id AND latestversion=version"
344 . " AND substring($page_tbl.pagename from 0 for $p) = '$pat'"
349 if ($include_empty) {
351 . $this->page_tbl_fields
353 . ($exclude ? " WHERE $exclude" : '')
354 . ($exclude ? " AND " : " WHERE ")
355 . " substring($page_tbl.pagename from 0 for $p) = '$pat'"
360 . $this->page_tbl_fields
361 . " FROM $nonempty_tbl, $page_tbl"
362 . " WHERE $nonempty_tbl.id=$page_tbl.id"
363 . " AND substring($page_tbl.pagename from 0 for $p) = '$pat'"
368 if ($limit && $orderby) {
369 // extract from,count from limit
370 list($from,$count) = $this->limit($limit);
371 $result = $dbh->limitQuery($sql, $from, $count);
372 $options = array('limit_by_db' => 1);
374 $result = $dbh->query($sql);
375 $options = array('limit_by_db' => 0);
377 return new WikiDB_backend_PearDB_iter($this, $result, $options);
380 function most_popular($limit=20, $sortby='-hits') {
382 extract($this->_table_names);
390 $order = "hits DESC";
391 $where = " AND hits > 0";
394 if ($sortby != '-hits') {
395 if ($order = $this->sortby($sortby, 'db'))
396 $orderby = " ORDER BY " . $order;
398 $orderby = " ORDER BY $order";
400 //$limitclause = $limit ? " LIMIT $limit" : '';
402 . $this->page_tbl_fields
403 . " FROM $nonempty_tbl, $page_tbl"
404 . " WHERE $nonempty_tbl.id=$page_tbl.id"
405 . " AND substring($page_tbl.pagename from 0 for $p) = '$pat'"
409 list($from, $count) = $this->limit($limit);
410 $result = $dbh->limitQuery($sql, $from, $count);
412 $result = $dbh->query($sql);
415 return new WikiDB_backend_PearDB_iter($this, $result);
418 function most_recent($params) {
421 $include_minor_revisions = false;
422 $exclude_major_revisions = false;
423 $include_all_revisions = false;
427 extract($this->_table_names);
431 $pick[] = "mtime >= $since";
434 if ($include_all_revisions) {
435 // Include all revisions of each page.
436 $table = "$page_tbl, $version_tbl";
437 $join_clause = "$page_tbl.id=$version_tbl.id";
439 if ($exclude_major_revisions) {
440 // Include only minor revisions
441 $pick[] = "minor_edit <> 0";
443 elseif (!$include_minor_revisions) {
444 // Include only major revisions
445 $pick[] = "minor_edit = 0";
449 $table = "$page_tbl, $recent_tbl";
450 $join_clause = "$page_tbl.id=$recent_tbl.id";
451 $table .= ", $version_tbl";
452 $join_clause .= " AND $version_tbl.id=$page_tbl.id";
454 if ($exclude_major_revisions) {
455 // Include only most recent minor revision
456 $pick[] = 'version=latestminor';
458 elseif (!$include_minor_revisions) {
459 // Include only most recent major revision
460 $pick[] = 'version=latestmajor';
463 // Include only the latest revision (whether major or minor).
464 $pick[] ='version=latestversion';
472 // $limitclause = $limit ? " LIMIT $limit" : '';
473 $where_clause = $join_clause;
475 $where_clause .= " AND " . join(" AND ", $pick);
480 // FIXME: use SQL_BUFFER_RESULT for mysql?
482 . $this->page_tbl_fields . ", " . $this->version_tbl_fields
484 . " WHERE $where_clause"
485 . " AND substring($page_tbl.pagename from 0 for $p) = '$pat'"
486 . " ORDER BY mtime $order";
488 list($from, $count) = $this->limit($limit);
489 $result = $dbh->limitQuery($sql, $from, $count);
491 $result = $dbh->query($sql);
493 return new WikiDB_backend_PearDB_iter($this, $result);
496 function wanted_pages($exclude_from='', $exclude='', $sortby='', $limit='') {
498 extract($this->_table_names);
501 if ($orderby = $this->sortby($sortby, 'db', array('pagename','wantedfrom')))
502 $orderby = 'ORDER BY ' . $orderby;
504 if ($exclude_from) // array of pagenames
505 $exclude_from = " AND pp.pagename NOT IN ".$this->_sql_set($exclude_from);
506 if ($exclude) // array of pagenames
507 $exclude = " AND p.pagename NOT IN ".$this->_sql_set($exclude);
509 $p = strlen(PAGE_PREFIX)+1;
510 $sql = "SELECT substring(p.pagename from $p) AS wantedfrom, substring(pp.pagename from $p) AS pagename"
511 . " FROM $page_tbl p, $link_tbl linked"
512 . " LEFT JOIN $page_tbl pp ON linked.linkto = pp.id"
513 . " LEFT JOIN $nonempty_tbl ne ON linked.linkto = ne.id"
514 . " WHERE ne.id IS NULL"
515 . " AND p.id = linked.linkfrom"
516 . " AND substring(p.pagename from 0 for $p) = '$pat'"
517 . " AND substring(pp.pagename from 0 for $p) = '$pat'"
522 // oci8 error: WHERE NULL = NULL appended
523 list($from, $count) = $this->limit($limit);
524 $result = $dbh->limitQuery($sql, $from, $count * 3);
526 $result = $dbh->query($sql);
528 return new WikiDB_backend_PearDB_generic_iter($this, $result);
531 function rename_page ($pagename, $to) {
533 extract($this->_table_names);
536 if (($id = $this->_get_pageid($pagename, false)) ) {
537 if ($new = $this->_get_pageid($to, false)) {
539 // This page does not exist (already verified before), but exists in the page table.
540 // So we delete this page.
541 $dbh->query("DELETE FROM $nonempty_tbl WHERE id=$new");
542 $dbh->query("DELETE FROM $recent_tbl WHERE id=$new");
543 $dbh->query("DELETE FROM $version_tbl WHERE id=$new");
544 // We have to fix all referring tables to the old id
545 $dbh->query("UPDATE $link_tbl SET linkfrom=$id WHERE linkfrom=$new");
546 $dbh->query("UPDATE $link_tbl SET linkto=$id WHERE linkto=$new");
547 $dbh->query("DELETE FROM $page_tbl WHERE id=$new");
549 $dbh->query(sprintf("UPDATE $page_tbl SET pagename='%s' WHERE id=$id",
550 $dbh->escapeSimple(PAGE_PREFIX.$to)));
556 function is_wiki_page($pagename) {
557 return parent::is_wiki_page(PAGE_PREFIX.$pagename);
560 function increaseHitCount($pagename) {
561 return parent::increaseHitCount(PAGE_PREFIX.$pagename);
564 function _serialize($data) {
565 return WikiDB_backend_PearDB::_serialize($data);
570 * NOTE: Disable vacuum, wikiuser is not the table owner
572 function optimize() {
579 function text_search($search, $fulltext=false, $sortby='', $limit='',
583 extract($this->_table_names);
585 $len = strlen($pat)+1;
586 $orderby = $this->sortby($sortby, 'db');
587 if ($sortby and $orderby) $orderby = ' ORDER BY ' . $orderby;
589 $searchclass = get_class($this)."_search";
590 // no need to define it everywhere and then fallback. memory!
591 if (!class_exists($searchclass))
592 $searchclass = "WikiDB_backend_PearDB_search";
593 $searchobj = new $searchclass($search, $dbh);
595 $table = "$nonempty_tbl, $page_tbl";
596 $join_clause = "$nonempty_tbl.id=$page_tbl.id";
597 $fields = $this->page_tbl_fields;
600 $table .= ", $recent_tbl";
601 $join_clause .= " AND $page_tbl.id=$recent_tbl.id";
603 $table .= ", $version_tbl";
604 $join_clause .= " AND $page_tbl.id=$version_tbl.id AND latestversion=version";
606 $fields .= ", $page_tbl.pagedata as pagedata, " . $this->version_tbl_fields;
607 // TODO: title still ignored, need better rank and subselect
608 $callback = new WikiMethodCb($searchobj, "_fulltext_match_clause");
609 $search_string = $search->makeTsearch2SqlClauseObj($callback);
610 $search_string = str_replace(array("%"," "), array("","&"), $search_string);
611 $search_clause = "substring(plugin_wiki_page.pagename from 0 for $len) = '$pat') AND (";
613 $search_clause .= "idxFTI @@ to_tsquery('$search_string')";
615 $orderby = " ORDER BY ts_rank(idxFTI, to_tsquery('$search_string')) DESC";
617 $callback = new WikiMethodCb($searchobj, "_pagename_match_clause");
618 $search_clause = "substring(plugin_wiki_page.pagename from 0 for $len) = '$pat') AND (";
619 $search_clause .= $search->makeSqlClauseObj($callback);
622 $sql = "SELECT $fields FROM $table"
623 . " WHERE $join_clause"
624 . " AND ($search_clause)"
627 list($from, $count) = $this->limit($limit);
628 $result = $dbh->limitQuery($sql, $from, $count);
630 $result = $dbh->query($sql);
633 $iter = new WikiDB_backend_PearDB_iter($this, $result);
634 $iter->stoplisted = @$searchobj->stoplisted;
638 function exists_link($pagename, $link, $reversed=false) {
640 extract($this->_table_names);
643 list($have, $want) = array('linkee', 'linker');
645 list($have, $want) = array('linker', 'linkee');
646 $qpagename = $dbh->escapeSimple($pagename);
647 $qlink = $dbh->escapeSimple($link);
648 $row = $dbh->GetRow("SELECT $want.pagename as result"
649 . " FROM $link_tbl, $page_tbl linker, $page_tbl linkee, $nonempty_tbl"
650 . " WHERE linkfrom=linker.id AND linkto=linkee.id"
651 . " AND $have.pagename='$qpagename'"
652 . " AND $want.pagename='$qlink'"
654 return $row['result'] ? 1 : 0;
658 class WikiDB_backend_PearDB_ffpgsql_search
659 extends WikiDB_backend_PearDB_pgsql_search
661 function _pagename_match_clause($node) {
662 $word = $node->sql();
663 // @alu: use _quote maybe instead of direct pg_escape_string
664 $word = pg_escape_string($word);
665 $len = strlen(PAGE_PREFIX)+1;
666 if ($node->op == 'REGEX') { // posix regex extensions
667 return ($this->_case_exact
668 ? "substring(pagename from $len) ~* '$word'"
669 : "substring(pagename from $len) ~ '$word'");
671 return ($this->_case_exact
672 ? "substring(pagename from $len) LIKE '$word'"
673 : "substring(pagename from $len) ILIKE '$word'");
678 * use tsearch2. See schemas/psql-tsearch2.sql and /usr/share/postgresql/contrib/tsearch2.sql
679 * TODO: don't parse the words into nodes. rather replace "[ +]" with & and "-" with "!" and " or " with "|"
680 * tsearch2 query language: @@ "word | word", "word & word", ! word
681 * ~* '.*something that does not exist.*'
684 phrase search for "history lesson":
686 SELECT id FROM tab WHERE ts_idx_col @@ to_tsquery('history&lesson')
687 AND text_col ~* '.*history\\s+lesson.*';
689 The full-text index will still be used, and the regex will be used to
690 prune the results afterwards.
692 function _fulltext_match_clause($node) {
693 $word = strtolower($node->word);
694 $word = str_replace(" ", "&", $word); // phrase fix
696 // @alu: use _quote maybe instead of direct pg_escape_string
697 $word = pg_escape_string($word);
707 // c-hanging-comment-ender-p: nil
708 // indent-tabs-mode: nil