2 rcs_id('$Id: PearDB_pgsql.php,v 1.22 2005-11-16 07:36:22 rurban Exp $');
4 require_once('lib/ErrorManager.php');
5 require_once('lib/WikiDB/backend/PearDB.php');
7 if (!defined("USE_BYTEA")) // see schemas/psql-initialize.sql
8 define("USE_BYTEA", false);
9 //define("USE_BYTEA", false);
12 Since 1.3.12 changed to use:
18 class WikiDB_backend_PearDB_pgsql
19 extends WikiDB_backend_PearDB
21 function WikiDB_backend_PearDB_pgsql($dbparams) {
22 // The pgsql handler of (at least my version of) the PEAR::DB
23 // library generates three warnings when a database is opened:
25 // Undefined index: options
26 // Undefined index: tty
27 // Undefined index: port
29 // This stuff is all just to catch and ignore these warnings,
30 // so that they don't get reported to the user. (They are
31 // not consequential.)
34 $ErrorManager->pushErrorHandler(new WikiMethodCb($this,'_pgsql_open_error'));
35 $this->WikiDB_backend_PearDB($dbparams);
36 $ErrorManager->popErrorHandler();
39 function _pgsql_open_error($error) {
40 if (preg_match('/^Undefined\s+index:\s+(options|tty|port)/',
42 return true; // Ignore error
50 foreach ($this->_table_names as $table) {
51 $this->_dbh->query("VACUUM ANALYZE $table");
58 return pg_escape_bytea($s);
59 if (function_exists('pg_escape_string'))
60 return pg_escape_string($s);
62 return base64_encode($s);
65 function _unquote($s) {
67 return pg_unescape_bytea($s);
68 if (function_exists('pg_escape_string'))
71 return base64_decode($s);
74 // Until the binary escape problems on pear pgsql are solved */
75 function get_cached_html($pagename) {
77 $page_tbl = $this->_table_names['page_tbl'];
78 $data = $dbh->GetOne(sprintf("SELECT cached_html FROM $page_tbl WHERE pagename='%s'",
79 $dbh->escapeSimple($pagename)));
80 if ($data) return $this->_unquote($data);
84 function set_cached_html($pagename, $data) {
86 $page_tbl = $this->_table_names['page_tbl'];
88 $sth = $dbh->query(sprintf("UPDATE $page_tbl"
89 . " SET cached_html='%s'"
90 . " WHERE pagename='%s'",
92 $dbh->escapeSimple($pagename)));
94 $sth = $dbh->query("UPDATE $page_tbl"
95 . " SET cached_html=?"
96 . " WHERE pagename=?",
97 // PearDB does NOT use pg_escape_string()! Oh dear.
98 array($this->_quote($data), $pagename));
102 * Create a new revision of a page.
104 function set_versiondata($pagename, $version, $data) {
106 $version_tbl = $this->_table_names['version_tbl'];
108 $minor_edit = (int) !empty($data['is_minor_edit']);
109 unset($data['is_minor_edit']);
111 $mtime = (int)$data['mtime'];
112 unset($data['mtime']);
113 assert(!empty($mtime));
115 @$content = (string) $data['%content'];
116 unset($data['%content']);
117 unset($data['%pagedata']);
120 $id = $this->_get_pageid($pagename, true);
121 $dbh->query(sprintf("DELETE FROM version WHERE id=%d AND version=%d", $id, $version));
122 $dbh->query(sprintf("INSERT INTO version (id,version,mtime,minor_edit,content,versiondata)" .
123 " VALUES (%d, %d, %d, %d, '%s', '%s')",
124 $id, $version, $mtime, $minor_edit,
125 $this->_quote($content),
126 $this->_serialize($data)));
127 $dbh->query(sprintf("SELECT update_recent (%d, %d)", $id, $version));
132 * Delete an old revision of a page.
134 function delete_versiondata($pagename, $version) {
136 $dbh->query(sprintf("SELECT delete_versiondata (%d, %d)", $id, $version));
140 * Rename page in the database.
142 function rename_page ($pagename, $to) {
144 extract($this->_table_names);
147 if (($id = $this->_get_pageid($pagename, false)) ) {
148 if ($new = $this->_get_pageid($to, false)) {
150 // This page does not exist (already verified before), but exists in the page table.
151 // So we delete this page.
152 $dbh->query("DELETE FROM $page_tbl WHERE id=$new");
153 $dbh->query("DELETE FROM $version_tbl WHERE id=$new");
154 $dbh->query("DELETE FROM $recent_tbl WHERE id=$new");
155 $dbh->query("DELETE FROM $nonempty_tbl WHERE id=$new");
156 // We have to fix all referring tables to the old id
157 $dbh->query("UPDATE $link_tbl SET linkfrom=$id WHERE linkfrom=$new");
158 $dbh->query("UPDATE $link_tbl SET linkto=$id WHERE linkto=$new");
160 $dbh->query(sprintf("UPDATE $page_tbl SET pagename='%s' WHERE id=$id",
161 $dbh->escapeSimple($to)));
168 * Lock all tables we might use.
170 function _lock_tables($write_lock = true) {
171 $this->_dbh->query("BEGIN");
177 function _unlock_tables() {
178 $this->_dbh->query("COMMIT");
184 function _serialize($data) {
187 assert(is_array($data));
188 return $this->_quote(serialize($data));
194 function _unserialize($data) {
197 // Base64 encoded data does not contain colons.
198 // (only alphanumerics and '+' and '/'.)
199 if (substr($data,0,2) == 'a:')
200 return unserialize($data);
201 return unserialize($this->_unquote($data));
207 function text_search($search, $fulltext=false, $sortby=false, $limit=false,
211 extract($this->_table_names);
212 $orderby = $this->sortby($sortby, 'db');
213 if ($sortby and $orderby) $orderby = ' ORDER BY ' . $orderby;
215 $searchclass = get_class($this)."_search";
216 // no need to define it everywhere and then fallback. memory!
217 if (!class_exists($searchclass))
218 $searchclass = "WikiDB_backend_PearDB_search";
219 $searchobj = new $searchclass($search, $dbh);
221 $table = "$nonempty_tbl, $page_tbl";
222 $join_clause = "$nonempty_tbl.id=$page_tbl.id";
223 $fields = $this->page_tbl_fields;
226 $table .= ", $recent_tbl";
227 $join_clause .= " AND $page_tbl.id=$recent_tbl.id";
229 $table .= ", $version_tbl";
230 $join_clause .= " AND $page_tbl.id=$version_tbl.id AND latestversion=version";
232 $fields .= ", $page_tbl.pagedata as pagedata, " . $this->version_tbl_fields;
233 $callback = new WikiMethodCb($searchobj, "_fulltext_match_clause");
234 $search_string = $search->makeTsearch2SqlClauseObj($callback);
235 $search_string = str_replace(array("%"," "), array("","&"), $search_string);
236 $search_clause = "idxFTI @@ to_tsquery('$search_string')";
238 $orderby = " ORDER BY rank(idxFTI, to_tsquery('$search_string')) DESC";
240 $callback = new WikiMethodCb($searchobj, "_pagename_match_clause");
241 $search_clause = $search->makeSqlClauseObj($callback);
244 $sql = "SELECT $fields FROM $table"
245 . " WHERE $join_clause"
246 . " AND ($search_clause)"
249 list($from, $count) = $this->limit($limit);
250 $result = $dbh->limitQuery($sql, $from, $count);
252 $result = $dbh->query($sql);
255 $iter = new WikiDB_backend_PearDB_iter($this, $result);
256 $iter->stoplisted = @$searchobj->stoplisted;
262 class WikiDB_backend_PearDB_pgsql_search
263 extends WikiDB_backend_PearDB_search
265 function _pagename_match_clause($node) {
266 $word = $node->sql();
267 if ($node->op == 'REGEX') { // posix regex extensions
268 return ($this->_case_exact
269 ? "pagename ~* '$word'"
270 : "pagename ~ '$word'");
272 return ($this->_case_exact
273 ? "pagename LIKE '$word'"
274 : "pagename ILIKE '$word'");
280 select * from stat('select idxfti from version') order by ndoc desc, nentry desc, word limit 10;
282 -----------------+------+--------
285 phpwikidocument | 62 | 62
296 * use tsearch2. See schemas/psql-tsearch2.sql and /usr/share/postgresql/contrib/tsearch2.sql
297 * TODO: don't parse the words into nodes. rather replace "[ +]" with & and "-" with "!" and " or " with "|"
298 * tsearch2 query language: @@ "word | word", "word & word", ! word
299 * ~* '.*something that does not exist.*'
302 phrase search for "history lesson":
304 SELECT id FROM tab WHERE ts_idx_col @@ to_tsquery('history&lesson')
305 AND text_col ~* '.*history\\s+lesson.*';
307 The full-text index will still be used, and the regex will be used to
308 prune the results afterwards.
310 function _fulltext_match_clause($node) {
311 $word = strtolower($node->word);
312 $word = str_replace(" ", "&", $word); // phrase fix
315 return $this->_pagename_match_clause($node) . " OR idxFTI @@ to_tsquery('$word')";
319 // (c-file-style: "gnu")
324 // c-hanging-comment-ender-p: nil
325 // indent-tabs-mode: nil