3 require_once 'lib/ErrorManager.php';
4 require_once 'lib/WikiDB/backend/PearDB.php';
6 if (!defined("USE_BYTEA")) // see schemas/psql-initialize.sql
7 define("USE_BYTEA", true);
10 Since 1.3.12 changed to use:
16 class WikiDB_backend_PearDB_pgsql
17 extends WikiDB_backend_PearDB
21 * NOTE: Only the table owner can do this. Either fix the schema or setup autovacuum.
25 return true; // if the wikiuser is not the table owner
28 foreach ($this->_table_names as $table) {
29 $this->_dbh->query("VACUUM ANALYZE $table");
38 return pg_escape_bytea($s);
39 if (function_exists('pg_escape_string'))
40 return pg_escape_string($s);
42 return base64_encode($s);
48 return pg_unescape_bytea($s);
49 if (function_exists('pg_escape_string'))
52 return base64_decode($s);
55 // Until the binary escape problems on pear pgsql are solved */
56 function get_cached_html($pagename)
59 $page_tbl = $this->_table_names['page_tbl'];
60 $data = $dbh->GetOne(sprintf("SELECT cached_html FROM $page_tbl WHERE pagename='%s'",
61 $dbh->escapeSimple($pagename)));
62 if ($data) return $this->_unquote($data);
66 function set_cached_html($pagename, $data)
69 $page_tbl = $this->_table_names['page_tbl'];
71 $dbh->query(sprintf("UPDATE $page_tbl"
72 . " SET cached_html='%s'"
73 . " WHERE pagename='%s'",
75 $dbh->escapeSimple($pagename)));
77 $dbh->query("UPDATE $page_tbl"
78 . " SET cached_html=?"
79 . " WHERE pagename=?",
80 // PearDB does NOT use pg_escape_string()! Oh dear.
81 array($this->_quote($data), $pagename));
85 * Lock all tables we might use.
87 protected function _lock_tables($write_lock = true)
89 $this->_dbh->query("BEGIN");
95 protected function _unlock_tables()
97 $this->_dbh->query("COMMIT");
103 function _serialize($data)
107 assert(is_array($data));
108 return $this->_quote(serialize($data));
114 function _unserialize($data)
118 // Base64 encoded data does not contain colons.
119 // (only alphanumerics and '+' and '/'.)
120 if (substr($data, 0, 2) == 'a:')
121 return unserialize($data);
122 return unserialize($this->_unquote($data));
126 * Text search (title or full text)
128 public function text_search($search, $fulltext = false,
129 $sortby = '', $limit = '', $exclude = '')
132 extract($this->_table_names);
133 $orderby = $this->sortby($sortby, 'db');
134 if ($sortby and $orderby) $orderby = ' ORDER BY ' . $orderby;
136 $searchclass = get_class($this) . "_search";
137 // no need to define it everywhere and then fallback. memory!
138 if (!class_exists($searchclass))
139 $searchclass = "WikiDB_backend_PearDB_search";
140 $searchobj = new $searchclass($search, $dbh);
142 $table = "$nonempty_tbl, $page_tbl";
143 $join_clause = "$nonempty_tbl.id=$page_tbl.id";
144 $fields = $this->page_tbl_fields;
147 $table .= ", $recent_tbl";
148 $join_clause .= " AND $page_tbl.id=$recent_tbl.id";
150 $table .= ", $version_tbl";
151 $join_clause .= " AND $page_tbl.id=$version_tbl.id AND latestversion=version";
153 $fields .= ", $page_tbl.pagedata as pagedata, " . $this->version_tbl_fields;
154 // TODO: title still ignored, need better rank and subselect
155 $callback = new WikiMethodCb($searchobj, "_fulltext_match_clause");
156 $search_string = $search->makeTsearch2SqlClauseObj($callback);
157 $search_string = str_replace(array("%", " "), array("", "&"), $search_string);
158 $search_clause = "idxFTI @@ to_tsquery('$search_string')";
160 $orderby = " ORDER BY rank(idxFTI, to_tsquery('$search_string')) DESC";
162 $callback = new WikiMethodCb($searchobj, "_pagename_match_clause");
163 $search_clause = $search->makeSqlClauseObj($callback);
165 $sql = "SELECT $fields FROM $table"
166 . " WHERE $join_clause"
167 . " AND ($search_clause)"
170 list($from, $count) = $this->limit($limit);
171 $result = $dbh->limitQuery($sql, $from, $count);
173 $result = $dbh->query($sql);
176 $iter = new WikiDB_backend_PearDB_iter($this, $result);
177 $iter->stoplisted = @$searchobj->stoplisted;
183 class WikiDB_backend_PearDB_pgsql_search
184 extends WikiDB_backend_PearDB_search
186 function _pagename_match_clause($node)
188 $word = $node->sql();
189 if ($node->op == 'REGEX') { // posix regex extensions
190 return ($this->_case_exact
191 ? "pagename ~* '$word'"
192 : "pagename ~ '$word'");
194 return ($this->_case_exact
195 ? "pagename LIKE '$word'"
196 : "pagename ILIKE '$word'");
201 * use tsearch2. See schemas/psql-tsearch2.sql and /usr/share/postgresql/contrib/tsearch2.sql
202 * TODO: don't parse the words into nodes. rather replace "[ +]" with & and "-" with "!" and " or " with "|"
203 * tsearch2 query language: @@ "word | word", "word & word", ! word
204 * ~* '.*something that does not exist.*'
206 * phrase search for "history lesson":
208 * SELECT id FROM tab WHERE ts_idx_col @@ to_tsquery('history&lesson')
209 * AND text_col ~* '.*history\\s+lesson.*';
211 * The full-text index will still be used, and the regex will be used to
212 * prune the results afterwards.
214 function _fulltext_match_clause($node)
216 $word = strtolower($node->word);
217 $word = str_replace(" ", "&", $word); // phrase fix
220 // clause specified above.
221 // return $this->_pagename_match_clause($node) . " OR idxFTI @@ to_tsquery('$word')";
229 // c-hanging-comment-ender-p: nil
230 // indent-tabs-mode: nil