4 require_once('lib/WikiDB/backend.php');
5 //require_once('lib/FileFinder.php');
6 //require_once('lib/ErrorManager.php');
8 class WikiDB_backend_PearDB
13 function WikiDB_backend_PearDB ($dbparams) {
14 // Find and include PEAR's DB.php. maybe we should force our private version again...
15 // if DB would have exported its version number, it would be easier.
16 @require_once('DB/common.php'); // Either our local pear copy or the system one
18 $name = check_php_version(5) ? "escapeSimple" : strtolower("escapeSimple");
19 // TODO: apparently some Pear::Db version adds LIMIT 1,0 to getOne(),
20 // which is invalid for "select version()"
21 if (!in_array($name, get_class_methods("DB_common"))) {
22 $finder = new FileFinder;
23 $dir = dirname(__FILE__)."/../../pear";
24 $finder->_prepend_to_include_path($dir);
25 include_once("$dir/DB/common.php"); // use our version instead.
26 if (!in_array($name, get_class_methods("DB_common"))) {
27 $pearFinder = new PearFileFinder("lib/pear");
28 $pearFinder->includeOnce('DB.php');
30 include_once("$dir/DB.php");
33 include_once("DB.php");
36 // Install filter to handle bogus error notices from buggy DB.php's.
37 // TODO: check the Pear_DB version, but how?
40 $ErrorManager->pushErrorHandler(new WikiMethodCb($this, '_pear_notice_filter'));
41 $this->_pearerrhandler = true;
44 // Open connection to database
45 $this->_dsn = $dbparams['dsn'];
46 $this->_dbparams = $dbparams;
47 $this->_lock_count = 0;
49 // persistent is usually a DSN option: we override it with a config value.
50 // phptype://username:password@hostspec/database?persistent=false
51 $dboptions = array('persistent' => DATABASE_PERSISTENT,
53 //if (preg_match('/^pgsql/', $this->_dsn)) $dboptions['persistent'] = false;
54 $this->_dbh = DB::connect($this->_dsn, $dboptions);
56 if (DB::isError($dbh)) {
57 trigger_error(sprintf("Can't connect to database: %s",
58 $this->_pear_error_message($dbh)),
59 isset($dbparams['_tryroot_from_upgrade']) // hack!
60 ? E_USER_WARNING : E_USER_ERROR);
61 if (isset($dbparams['_tryroot_from_upgrade']))
64 $dbh->setErrorHandling(PEAR_ERROR_CALLBACK,
65 array($this, '_pear_error_callback'));
66 $dbh->setFetchMode(DB_FETCHMODE_ASSOC);
68 $prefix = isset($dbparams['prefix']) ? $dbparams['prefix'] : '';
70 = array('page_tbl' => $prefix . 'page',
71 'version_tbl' => $prefix . 'version',
72 'link_tbl' => $prefix . 'link',
73 'recent_tbl' => $prefix . 'recent',
74 'nonempty_tbl' => $prefix . 'nonempty');
75 $page_tbl = $this->_table_names['page_tbl'];
76 $version_tbl = $this->_table_names['version_tbl'];
77 $this->page_tbl_fields = "$page_tbl.id AS id, $page_tbl.pagename AS pagename, $page_tbl.hits AS hits";
78 $this->version_tbl_fields = "$version_tbl.version AS version, $version_tbl.mtime AS mtime, ".
79 "$version_tbl.minor_edit AS minor_edit, $version_tbl.content AS content, $version_tbl.versiondata AS versiondata";
82 = array('maxmajor' => "MAX(CASE WHEN minor_edit=0 THEN version END)",
83 'maxminor' => "MAX(CASE WHEN minor_edit<>0 THEN version END)",
84 'maxversion' => "MAX(version)",
86 'iscontent' => "content<>''");
91 * Close database connection.
96 if ($this->_lock_count) {
97 trigger_error( "WARNING: database still locked " . '(lock_count = $this->_lock_count)' . "\n<br />",
100 $this->_dbh->setErrorHandling(PEAR_ERROR_PRINT); // prevent recursive loops.
101 $this->unlock('force');
103 $this->_dbh->disconnect();
105 if (!empty($this->_pearerrhandler)) {
106 $GLOBALS['ErrorManager']->popErrorHandler();
112 * Test fast wikipage.
114 function is_wiki_page($pagename) {
116 extract($this->_table_names);
117 return $dbh->getOne(sprintf("SELECT $page_tbl.id as id"
118 . " FROM $nonempty_tbl, $page_tbl"
119 . " WHERE $nonempty_tbl.id=$page_tbl.id"
120 . " AND pagename='%s'",
121 $dbh->escapeSimple($pagename)));
124 function get_all_pagenames() {
126 extract($this->_table_names);
127 return $dbh->getCol("SELECT pagename"
128 . " FROM $nonempty_tbl, $page_tbl"
129 . " WHERE $nonempty_tbl.id=$page_tbl.id");
132 function numPages($filter=false, $exclude='') {
134 extract($this->_table_names);
135 return $dbh->getOne("SELECT count(*)"
136 . " FROM $nonempty_tbl, $page_tbl"
137 . " WHERE $nonempty_tbl.id=$page_tbl.id");
140 function increaseHitCount($pagename) {
142 // Hits is the only thing we can update in a fast manner.
143 // Note that this will fail silently if the page does not
144 // have a record in the page table. Since it's just the
145 // hit count, who cares?
146 $dbh->query(sprintf("UPDATE %s SET hits=hits+1 WHERE pagename='%s'",
147 $this->_table_names['page_tbl'],
148 $dbh->escapeSimple($pagename)));
153 * Read page information from database.
155 function get_pagedata($pagename) {
157 //trigger_error("GET_PAGEDATA $pagename", E_USER_NOTICE);
158 $result = $dbh->getRow(sprintf("SELECT hits,pagedata FROM %s WHERE pagename='%s'",
159 $this->_table_names['page_tbl'],
160 $dbh->escapeSimple($pagename)),
162 return $result ? $this->_extract_page_data($result) : false;
165 function _extract_page_data($data) {
166 if (empty($data)) return array();
167 elseif (empty($data['pagedata'])) return $data;
169 $data = array_merge($data, $this->_unserialize($data['pagedata']));
170 unset($data['pagedata']);
175 function update_pagedata($pagename, $newdata) {
177 $page_tbl = $this->_table_names['page_tbl'];
179 // Hits is the only thing we can update in a fast manner.
180 if (count($newdata) == 1 && isset($newdata['hits'])) {
181 // Note that this will fail silently if the page does not
182 // have a record in the page table. Since it's just the
183 // hit count, who cares?
184 $dbh->query(sprintf("UPDATE $page_tbl SET hits=%d WHERE pagename='%s'",
185 $newdata['hits'], $dbh->escapeSimple($pagename)));
189 $this->lock(array($page_tbl), true);
190 $data = $this->get_pagedata($pagename);
193 $this->_get_pageid($pagename, true); // Creates page record
196 $hits = !empty($data['hits']) ? (int)$data['hits'] : 0;
197 unset($data['hits']);
199 foreach ($newdata as $key => $val) {
202 else if (empty($val))
208 /* Portability issue -- not all DBMS supports huge strings
209 * so we need to 'bind' instead of building a simple SQL statment.
210 * Note that we do not need to escapeSimple when we bind
211 $dbh->query(sprintf("UPDATE $page_tbl"
212 . " SET hits=%d, pagedata='%s'"
213 . " WHERE pagename='%s'",
215 $dbh->escapeSimple($this->_serialize($data)),
216 $dbh->escapeSimple($pagename)));
218 $dbh->query("UPDATE $page_tbl"
219 . " SET hits=?, pagedata=?"
220 . " WHERE pagename=?",
221 array($hits, $this->_serialize($data), $pagename));
222 $this->unlock(array($page_tbl));
225 function get_cached_html($pagename) {
227 $page_tbl = $this->_table_names['page_tbl'];
228 return $dbh->GetOne(sprintf("SELECT cached_html FROM $page_tbl WHERE pagename='%s'",
229 $dbh->escapeSimple($pagename)));
232 function set_cached_html($pagename, $data) {
234 $page_tbl = $this->_table_names['page_tbl'];
235 $dbh->query("UPDATE $page_tbl"
236 . " SET cached_html=?"
237 . " WHERE pagename=?",
238 array($data, $pagename));
241 function _get_pageid($pagename, $create_if_missing = false) {
245 $cache =& $request->_dbi->_cache->_id_cache;
246 if (isset($cache[$pagename])) {
247 if ($cache[$pagename] or !$create_if_missing) {
248 return $cache[$pagename];
252 // attributes play this game.
253 if ($pagename === '') return 0;
256 $page_tbl = $this->_table_names['page_tbl'];
258 $query = sprintf("SELECT id FROM $page_tbl WHERE pagename='%s'",
259 $dbh->escapeSimple($pagename));
261 if (!$create_if_missing)
262 return $dbh->getOne($query);
264 $id = $dbh->getOne($query);
266 $this->lock(array($page_tbl), true); // write lock
267 $max_id = $dbh->getOne("SELECT MAX(id) FROM $page_tbl");
269 // requires createSequence and on mysql lock the interim table ->getSequenceName
270 //$id = $dbh->nextId($page_tbl . "_id");
271 $dbh->query(sprintf("INSERT INTO $page_tbl"
272 . " (id,pagename,hits)"
273 . " VALUES (%d,'%s',0)",
274 $id, $dbh->escapeSimple($pagename)));
275 $this->unlock(array($page_tbl));
280 function get_latest_version($pagename) {
282 extract($this->_table_names);
284 (int)$dbh->getOne(sprintf("SELECT latestversion"
285 . " FROM $page_tbl, $recent_tbl"
286 . " WHERE $page_tbl.id=$recent_tbl.id"
287 . " AND pagename='%s'",
288 $dbh->escapeSimple($pagename)));
291 function get_previous_version($pagename, $version) {
293 extract($this->_table_names);
296 (int)$dbh->getOne(sprintf("SELECT version"
297 . " FROM $version_tbl, $page_tbl"
298 . " WHERE $version_tbl.id=$page_tbl.id"
299 . " AND pagename='%s'"
300 . " AND version < %d"
301 . " ORDER BY version DESC",
302 /* Non portable and useless anyway with getOne
305 $dbh->escapeSimple($pagename),
312 * @param $version int Which version to get.
314 * @return hash The version data, or false if specified version does not
317 function get_versiondata($pagename, $version, $want_content = false) {
319 extract($this->_table_names);
320 extract($this->_expressions);
322 assert(is_string($pagename) and $pagename != "");
323 assert($version > 0);
325 //trigger_error("GET_REVISION $pagename $version $want_content", E_USER_NOTICE);
326 // FIXME: optimization: sometimes don't get page data?
328 $fields = $this->page_tbl_fields
329 . ",$page_tbl.pagedata as pagedata,"
330 . $this->version_tbl_fields;
333 $fields = $this->page_tbl_fields . ","
334 . "mtime, minor_edit, versiondata,"
335 . "$iscontent AS have_content";
338 $result = $dbh->getRow(sprintf("SELECT $fields"
339 . " FROM $page_tbl, $version_tbl"
340 . " WHERE $page_tbl.id=$version_tbl.id"
341 . " AND pagename='%s'"
343 $dbh->escapeSimple($pagename), $version),
346 return $this->_extract_version_data($result);
349 function _extract_version_data($query_result) {
353 /* Earlier versions (<= 1.3.7) stored the version data in base64.
354 This could be done here or in upgrade.
356 if (!strstr($query_result['versiondata'], ":")) {
357 $query_result['versiondata'] =
358 base64_decode($query_result['versiondata']);
360 $data = $this->_unserialize($query_result['versiondata']);
362 $data['mtime'] = $query_result['mtime'];
363 $data['is_minor_edit'] = !empty($query_result['minor_edit']);
365 if (isset($query_result['content']))
366 $data['%content'] = $query_result['content'];
367 elseif ($query_result['have_content'])
368 $data['%content'] = true;
370 $data['%content'] = '';
372 // FIXME: this is ugly.
373 if (isset($query_result['pagedata'])) {
374 // Query also includes page data.
375 // We might as well send that back too...
376 unset($query_result['versiondata']);
377 $data['%pagedata'] = $this->_extract_page_data($query_result);
385 * Create a new revision of a page.
387 function set_versiondata($pagename, $version, $data) {
389 $version_tbl = $this->_table_names['version_tbl'];
391 $minor_edit = (int) !empty($data['is_minor_edit']);
392 unset($data['is_minor_edit']);
394 $mtime = (int)$data['mtime'];
395 unset($data['mtime']);
396 assert(!empty($mtime));
398 @$content = (string) $data['%content'];
399 unset($data['%content']);
401 unset($data['%pagedata']);
404 $id = $this->_get_pageid($pagename, true);
406 $dbh->query(sprintf("DELETE FROM $version_tbl"
407 . " WHERE id=%d AND version=%d",
409 // generic slow PearDB bind eh quoting.
410 $dbh->query("INSERT INTO $version_tbl"
411 . " (id,version,mtime,minor_edit,content,versiondata)"
412 . " VALUES(?, ?, ?, ?, ?, ?)",
413 array($id, $version, $mtime, $minor_edit, $content,
414 $this->_serialize($data)));
416 $this->_update_recent_table($id);
417 $this->_update_nonempty_table($id);
423 * Delete an old revision of a page.
425 function delete_versiondata($pagename, $version) {
427 extract($this->_table_names);
430 if ( ($id = $this->_get_pageid($pagename)) ) {
431 $dbh->query("DELETE FROM $version_tbl"
432 . " WHERE id=$id AND version=$version");
434 $this->_update_recent_table($id);
435 // This shouldn't be needed (as long as the latestversion
436 // never gets deleted.) But, let's be safe.
437 $this->_update_nonempty_table($id);
443 * Delete page from the database with backup possibility.
444 * i.e save_page('') and DELETE nonempty id
445 * Can be undone and is seen in RecentChanges.
447 /* // see parent backend.php
448 function delete_page($pagename) {
450 $user =& $GLOBALS['request']->_user;
451 $vdata = array('author' => $user->getId(),
452 'author_id' => $user->getAuthenticatedId(),
456 $version = $this->get_latest_version($pagename);
457 $this->set_versiondata($pagename, $version+1, $vdata);
458 $this->set_links($pagename, false);
459 $pagedata = get_pagedata($pagename);
460 $this->update_pagedata($pagename, array('hits' => $pagedata['hits']));
466 * Delete page completely from the database.
467 * I'm not sure if this is what we want. Maybe just delete the revisions
469 function purge_page($pagename) {
471 extract($this->_table_names);
474 if ( ($id = $this->_get_pageid($pagename, false)) ) {
475 $dbh->query("DELETE FROM $nonempty_tbl WHERE id=$id");
476 $dbh->query("DELETE FROM $recent_tbl WHERE id=$id");
477 $dbh->query("DELETE FROM $version_tbl WHERE id=$id");
478 $dbh->query("DELETE FROM $link_tbl WHERE linkfrom=$id");
479 $nlinks = $dbh->getOne("SELECT COUNT(*) FROM $link_tbl WHERE linkto=$id");
481 // We're still in the link table (dangling link) so we can't delete this
483 $dbh->query("UPDATE $page_tbl SET hits=0, pagedata='' WHERE id=$id");
487 $dbh->query("DELETE FROM $page_tbl WHERE id=$id");
490 $this->_update_recent_table();
491 $this->_update_nonempty_table();
493 $result = -1; // already purged or not existing
499 // The only thing we might be interested in updating which we can
500 // do fast in the flags (minor_edit). I think the default
501 // update_versiondata will work fine...
502 //function update_versiondata($pagename, $version, $data) {
505 function set_links($pagename, $links) {
506 // Update link table.
507 // FIXME: optimize: mysql can do this all in one big INSERT.
510 extract($this->_table_names);
513 $pageid = $this->_get_pageid($pagename, true);
515 $dbh->query("DELETE FROM $link_tbl WHERE linkfrom=$pageid");
518 foreach ($links as $link) {
519 $linkto = $link['linkto'];
520 if ($linkto === "") { // ignore attributes
523 if (isset($link['relation']))
524 $relation = $this->_get_pageid($link['relation'], true);
528 if (isset($linkseen[$linkto]) and !$relation)
531 $linkseen[$linkto] = true;
532 $linkid = $this->_get_pageid($linkto, true);
534 echo("No link for $linkto on page $pagename");
535 //printSimpleTrace(debug_backtrace());
536 trigger_error("No link for $linkto on page $pagename");
539 $dbh->query("INSERT INTO $link_tbl (linkfrom, linkto, relation)"
540 . " VALUES ($pageid, $linkid, $relation)");
548 * Find pages which link to or are linked from a page.
550 * TESTME relations: get_links is responsible to add the relation to the pagehash
551 * as 'linkrelation' key as pagename. See WikiDB_PageIterator::next
552 * if (isset($next['linkrelation']))
554 function get_links($pagename, $reversed=true, $include_empty=false,
555 $sortby='', $limit='', $exclude='',
556 $want_relations = false)
559 extract($this->_table_names);
562 list($have,$want) = array('linkee', 'linker');
564 list($have,$want) = array('linker', 'linkee');
565 $orderby = $this->sortby($sortby, 'db', array('pagename'));
566 if ($orderby) $orderby = " ORDER BY $want." . $orderby;
567 if ($exclude) // array of pagenames
568 $exclude = " AND $want.pagename NOT IN ".$this->_sql_set($exclude);
572 $qpagename = $dbh->escapeSimple($pagename);
573 $sql = "SELECT $want.id AS id, $want.pagename AS pagename, "
574 . ($want_relations ? " related.pagename as linkrelation" : " $want.hits AS hits")
576 . (!$include_empty ? "$nonempty_tbl, " : '')
577 . " $page_tbl linkee, $page_tbl linker, $link_tbl "
578 . ($want_relations ? " JOIN $page_tbl related ON ($link_tbl.relation=related.id)" : '')
579 . " WHERE linkfrom=linker.id AND linkto=linkee.id"
580 . " AND $have.pagename='$qpagename'"
581 . (!$include_empty ? " AND $nonempty_tbl.id=$want.id" : "")
582 //. " GROUP BY $want.id"
586 // extract from,count from limit
587 list($from,$count) = $this->limit($limit);
588 $result = $dbh->limitQuery($sql, $from, $count);
590 $result = $dbh->query($sql);
593 return new WikiDB_backend_PearDB_iter($this, $result);
597 * Find if a page links to another page
599 function exists_link($pagename, $link, $reversed=false) {
601 extract($this->_table_names);
604 list($have, $want) = array('linkee', 'linker');
606 list($have, $want) = array('linker', 'linkee');
607 $qpagename = $dbh->escapeSimple($pagename);
608 $qlink = $dbh->escapeSimple($link);
609 $row = $dbh->GetRow("SELECT CASE WHEN $want.pagename='$qlink' THEN 1 ELSE 0 END as result"
610 . " FROM $link_tbl, $page_tbl linker, $page_tbl linkee, $nonempty_tbl"
611 . " WHERE linkfrom=linker.id AND linkto=linkee.id"
612 . " AND $have.pagename='$qpagename'"
613 . " AND $want.pagename='$qlink'");
614 return $row['result'];
617 function get_all_pages($include_empty=false, $sortby='', $limit='', $exclude='') {
619 extract($this->_table_names);
620 $orderby = $this->sortby($sortby, 'db');
621 if ($orderby) $orderby = ' ORDER BY ' . $orderby;
622 if ($exclude) // array of pagenames
623 $exclude = " AND $page_tbl.pagename NOT IN ".$this->_sql_set($exclude);
627 if (strstr($orderby, 'mtime ')) { // multiple columns possible
628 if ($include_empty) {
630 . $this->page_tbl_fields
631 . " FROM $page_tbl, $recent_tbl, $version_tbl"
632 . " WHERE $page_tbl.id=$recent_tbl.id"
633 . " AND $page_tbl.id=$version_tbl.id AND latestversion=version"
639 . $this->page_tbl_fields
640 . " FROM $nonempty_tbl, $page_tbl, $recent_tbl, $version_tbl"
641 . " WHERE $nonempty_tbl.id=$page_tbl.id"
642 . " AND $page_tbl.id=$recent_tbl.id"
643 . " AND $page_tbl.id=$version_tbl.id AND latestversion=version"
648 if ($include_empty) {
650 . $this->page_tbl_fields
652 . ($exclude ? " WHERE $exclude" : '')
657 . $this->page_tbl_fields
658 . " FROM $nonempty_tbl, $page_tbl"
659 . " WHERE $nonempty_tbl.id=$page_tbl.id"
664 if ($limit && $orderby) {
665 // extract from,count from limit
666 list($from,$count) = $this->limit($limit);
667 $result = $dbh->limitQuery($sql, $from, $count);
668 $options = array('limit_by_db' => 1);
670 $result = $dbh->query($sql);
671 $options = array('limit_by_db' => 0);
673 return new WikiDB_backend_PearDB_iter($this, $result, $options);
680 function text_search($search, $fulltext=false, $sortby='', $limit='',
684 extract($this->_table_names);
685 $orderby = $this->sortby($sortby, 'db');
686 if ($orderby) $orderby = ' ORDER BY ' . $orderby;
687 //else " ORDER BY rank($field, to_tsquery('$searchon')) DESC";
689 $searchclass = get_class($this)."_search";
690 // no need to define it everywhere and then fallback. memory!
691 if (!class_exists($searchclass))
692 $searchclass = "WikiDB_backend_PearDB_search";
693 $searchobj = new $searchclass($search, $dbh);
695 $table = "$nonempty_tbl, $page_tbl";
696 $join_clause = "$nonempty_tbl.id=$page_tbl.id";
697 $fields = $this->page_tbl_fields;
700 $table .= ", $recent_tbl";
701 $join_clause .= " AND $page_tbl.id=$recent_tbl.id";
703 $table .= ", $version_tbl";
704 $join_clause .= " AND $page_tbl.id=$version_tbl.id AND latestversion=version";
706 $fields .= ", $page_tbl.pagedata as pagedata, " . $this->version_tbl_fields;
707 $callback = new WikiMethodCb($searchobj, "_fulltext_match_clause");
709 $callback = new WikiMethodCb($searchobj, "_pagename_match_clause");
711 $search_clause = $search->makeSqlClauseObj($callback);
713 $sql = "SELECT $fields FROM $table"
714 . " WHERE $join_clause"
715 . " AND ($search_clause)"
718 list($from, $count) = $this->limit($limit);
719 $result = $dbh->limitQuery($sql, $from, $count);
721 $result = $dbh->query($sql);
724 $iter = new WikiDB_backend_PearDB_iter($this, $result);
725 $iter->stoplisted = @$searchobj->stoplisted;
729 //Todo: check if the better Mysql MATCH operator is supported,
730 // (ranked search) and also google like expressions.
731 function _sql_match_clause($word) {
732 $word = preg_replace('/(?=[%_\\\\])/', "\\", $word);
733 $word = $this->_dbh->escapeSimple($word);
734 //$page_tbl = $this->_table_names['page_tbl'];
735 //Note: Mysql 4.1.0 has a bug which fails with binary fields.
736 // e.g. if word is lowercased.
737 // http://bugs.mysql.com/bug.php?id=1491
738 return "LOWER(pagename) LIKE '%$word%'";
740 function _sql_casematch_clause($word) {
741 $word = preg_replace('/(?=[%_\\\\])/', "\\", $word);
742 $word = $this->_dbh->escapeSimple($word);
743 return "pagename LIKE '%$word%'";
745 function _fullsearch_sql_match_clause($word) {
746 $word = preg_replace('/(?=[%_\\\\])/', "\\", $word);
747 $word = $this->_dbh->escapeSimple($word);
748 //$page_tbl = $this->_table_names['page_tbl'];
749 //Mysql 4.1.1 has a bug which fails here if word is lowercased.
750 return "LOWER(pagename) LIKE '%$word%' OR content LIKE '%$word%'";
752 function _fullsearch_sql_casematch_clause($word) {
753 $word = preg_replace('/(?=[%_\\\\])/', "\\", $word);
754 $word = $this->_dbh->escapeSimple($word);
755 return "pagename LIKE '%$word%' OR content LIKE '%$word%'";
759 * Find highest or lowest hit counts.
761 function most_popular($limit=20, $sortby='-hits') {
763 extract($this->_table_names);
769 $order = "hits DESC";
770 $where = " AND hits > 0";
773 if ($sortby != '-hits') {
774 if ($order = $this->sortby($sortby, 'db'))
775 $orderby = " ORDER BY " . $order;
777 $orderby = " ORDER BY $order";
779 //$limitclause = $limit ? " LIMIT $limit" : '';
781 . $this->page_tbl_fields
782 . " FROM $nonempty_tbl, $page_tbl"
783 . " WHERE $nonempty_tbl.id=$page_tbl.id"
787 list($from, $count) = $this->limit($limit);
788 $result = $dbh->limitQuery($sql, $from, $count);
790 $result = $dbh->query($sql);
793 return new WikiDB_backend_PearDB_iter($this, $result);
797 * Find recent changes.
799 function most_recent($params) {
802 $include_minor_revisions = false;
803 $exclude_major_revisions = false;
804 $include_all_revisions = false;
808 extract($this->_table_names);
812 $pick[] = "mtime >= $since";
815 if ($include_all_revisions) {
816 // Include all revisions of each page.
817 $table = "$page_tbl, $version_tbl";
818 $join_clause = "$page_tbl.id=$version_tbl.id";
820 if ($exclude_major_revisions) {
821 // Include only minor revisions
822 $pick[] = "minor_edit <> 0";
824 elseif (!$include_minor_revisions) {
825 // Include only major revisions
826 $pick[] = "minor_edit = 0";
830 $table = "$page_tbl, $recent_tbl";
831 $join_clause = "$page_tbl.id=$recent_tbl.id";
832 $table .= ", $version_tbl";
833 $join_clause .= " AND $version_tbl.id=$page_tbl.id";
835 if ($exclude_major_revisions) {
836 // Include only most recent minor revision
837 $pick[] = 'version=latestminor';
839 elseif (!$include_minor_revisions) {
840 // Include only most recent major revision
841 $pick[] = 'version=latestmajor';
844 // Include only the latest revision (whether major or minor).
845 $pick[] ='version=latestversion';
853 // $limitclause = $limit ? " LIMIT $limit" : '';
854 $where_clause = $join_clause;
856 $where_clause .= " AND " . join(" AND ", $pick);
858 // FIXME: use SQL_BUFFER_RESULT for mysql?
860 . $this->page_tbl_fields . ", " . $this->version_tbl_fields
862 . " WHERE $where_clause"
863 . " ORDER BY mtime $order";
865 list($from, $count) = $this->limit($limit);
866 $result = $dbh->limitQuery($sql, $from, $count);
868 $result = $dbh->query($sql);
870 return new WikiDB_backend_PearDB_iter($this, $result);
874 * Find referenced empty pages.
876 function wanted_pages($exclude_from='', $exclude='', $sortby='', $limit='') {
878 extract($this->_table_names);
879 if ($orderby = $this->sortby($sortby, 'db', array('pagename','wantedfrom')))
880 $orderby = 'ORDER BY ' . $orderby;
882 if ($exclude_from) // array of pagenames
883 $exclude_from = " AND pp.pagename NOT IN ".$this->_sql_set($exclude_from);
884 if ($exclude) // array of pagenames
885 $exclude = " AND p.pagename NOT IN ".$this->_sql_set($exclude);
886 $sql = "SELECT p.pagename, pp.pagename AS wantedfrom"
887 . " FROM $page_tbl p, $link_tbl linked"
888 . " LEFT JOIN $page_tbl pp ON linked.linkto = pp.id"
889 . " LEFT JOIN $nonempty_tbl ne ON linked.linkto = ne.id"
890 . " WHERE ne.id IS NULL"
891 . " AND p.id = linked.linkfrom"
896 // oci8 error: WHERE NULL = NULL appended
897 list($from, $count) = $this->limit($limit);
898 $result = $dbh->limitQuery($sql, $from, $count * 3);
900 $result = $dbh->query($sql);
902 return new WikiDB_backend_PearDB_generic_iter($this, $result);
905 function _sql_set(&$pagenames) {
907 foreach ($pagenames as $p) {
908 $s .= ("'".$this->_dbh->escapeSimple($p)."',");
910 return substr($s,0,-1).")";
914 * Rename page in the database.
916 function rename_page ($pagename, $to) {
918 extract($this->_table_names);
921 if (($id = $this->_get_pageid($pagename, false)) ) {
922 if ($new = $this->_get_pageid($to, false)) {
924 // This page does not exist (already verified before), but exists in the page table.
925 // So we delete this page.
926 $dbh->query("DELETE FROM $nonempty_tbl WHERE id=$new");
927 $dbh->query("DELETE FROM $recent_tbl WHERE id=$new");
928 $dbh->query("DELETE FROM $version_tbl WHERE id=$new");
929 // We have to fix all referring tables to the old id
930 $dbh->query("UPDATE $link_tbl SET linkfrom=$id WHERE linkfrom=$new");
931 $dbh->query("UPDATE $link_tbl SET linkto=$id WHERE linkto=$new");
932 $dbh->query("DELETE FROM $page_tbl WHERE id=$new");
934 $dbh->query(sprintf("UPDATE $page_tbl SET pagename='%s' WHERE id=$id",
935 $dbh->escapeSimple($to)));
941 function _update_recent_table($pageid = false) {
943 extract($this->_table_names);
944 extract($this->_expressions);
946 $pageid = (int)$pageid;
949 $dbh->query("DELETE FROM $recent_tbl"
950 . ( $pageid ? " WHERE id=$pageid" : ""));
951 $dbh->query( "INSERT INTO $recent_tbl"
952 . " (id, latestversion, latestmajor, latestminor)"
953 . " SELECT id, $maxversion, $maxmajor, $maxminor"
954 . " FROM $version_tbl"
955 . ( $pageid ? " WHERE id=$pageid" : "")
960 function _update_nonempty_table($pageid = false) {
962 extract($this->_table_names);
964 $pageid = (int)$pageid;
966 extract($this->_expressions);
968 $dbh->query("DELETE FROM $nonempty_tbl"
969 . ( $pageid ? " WHERE id=$pageid" : ""));
970 $dbh->query("INSERT INTO $nonempty_tbl (id)"
971 . " SELECT $recent_tbl.id"
972 . " FROM $recent_tbl, $version_tbl"
973 . " WHERE $recent_tbl.id=$version_tbl.id"
974 . " AND version=latestversion"
975 // We have some specifics here (Oracle)
976 //. " AND content<>''"
977 . " AND content $notempty"
978 . ( $pageid ? " AND $recent_tbl.id=$pageid" : ""));
985 * Grab a write lock on the tables in the SQL database.
987 * Calls can be nested. The tables won't be unlocked until
988 * _unlock_database() is called as many times as _lock_database().
992 function lock($tables = false, $write_lock = true) {
993 if ($this->_lock_count++ == 0)
994 $this->_lock_tables($write_lock);
998 * Actually lock the required tables.
1000 function _lock_tables($write_lock) {
1001 trigger_error("virtual", E_USER_ERROR);
1005 * Release a write lock on the tables in the SQL database.
1009 * @param $force boolean Unlock even if not every call to lock() has been matched
1010 * by a call to unlock().
1012 * @see _lock_database
1014 function unlock($tables = false, $force = false) {
1015 if ($this->_lock_count == 0)
1017 if (--$this->_lock_count <= 0 || $force) {
1018 $this->_unlock_tables();
1019 $this->_lock_count = 0;
1024 * Actually unlock the required tables.
1026 function _unlock_tables($write_lock) {
1027 trigger_error("virtual", E_USER_ERROR);
1034 function _serialize($data) {
1037 assert(is_array($data));
1038 return serialize($data);
1044 function _unserialize($data) {
1045 return empty($data) ? array() : unserialize($data);
1049 * Callback for PEAR (DB) errors.
1053 * @param A PEAR_error object.
1055 function _pear_error_callback($error) {
1056 if ($this->_is_false_error($error))
1059 $this->_dbh->setErrorHandling(PEAR_ERROR_PRINT); // prevent recursive loops.
1061 trigger_error($this->_pear_error_message($error), E_USER_ERROR);
1065 * Detect false errors messages from PEAR DB.
1067 * The version of PEAR DB which ships with PHP 4.0.6 has a bug in that
1068 * it doesn't recognize "LOCK" and "UNLOCK" as SQL commands which don't
1069 * return any data. (So when a "LOCK" command doesn't return any data,
1070 * DB reports it as an error, when in fact, it's not.)
1073 * @return bool True iff error is not really an error.
1075 function _is_false_error($error) {
1076 if ($error->getCode() != DB_ERROR)
1079 $query = $this->_dbh->last_query;
1081 if (! preg_match('/^\s*"?(INSERT|UPDATE|DELETE|REPLACE|CREATE'
1082 . '|DROP|ALTER|GRANT|REVOKE|LOCK|UNLOCK)\s/', $query)) {
1083 // Last query was not of the sort which doesn't return any data.
1084 //" <--kludge for brain-dead syntax coloring
1088 if (! in_array('ismanip', get_class_methods('DB'))) {
1089 // Pear shipped with PHP 4.0.4pl1 (and before, presumably)
1090 // does not have the DB::isManip method.
1094 if (DB::isManip($query)) {
1095 // If Pear thinks it's an isManip then it wouldn't have thrown
1096 // the error we're testing for....
1103 function _pear_error_message($error) {
1104 $class = get_class($this);
1105 $message = "$class: fatal database error\n"
1106 . "\t" . $error->getMessage() . "\n"
1107 . "\t(" . $error->getDebugInfo() . ")\n";
1109 // Prevent password from being exposed during a connection error
1110 $safe_dsn = preg_replace('| ( :// .*? ) : .* (?=@) |xs',
1111 '\\1:XXXXXXXX', $this->_dsn);
1112 return str_replace($this->_dsn, $safe_dsn, $message);
1116 * Filter PHP errors notices from PEAR DB code.
1118 * The PEAR DB code which ships with PHP 4.0.6 produces spurious
1119 * errors and notices. This is an error callback (for use with
1120 * ErrorManager which will filter out those spurious messages.)
1121 * @see _is_false_error, ErrorManager
1124 function _pear_notice_filter($err) {
1125 return ( $err->isNotice()
1126 && preg_match('|DB[/\\\\]common.php$|', $err->errfile)
1127 && $err->errline == 126
1128 && preg_match('/Undefined offset: +0\b/', $err->errstr) );
1131 /* some variables and functions for DB backend abstraction (action=upgrade) */
1132 function database () {
1133 return $this->_dbh->dsn['database'];
1135 function backendType() {
1136 return $this->_dbh->phptype;
1138 function connection() {
1139 return $this->_dbh->connection;
1141 function getRow($query) {
1142 return $this->_dbh->getRow($query);
1145 function listOfTables() {
1146 return $this->_dbh->getListOf('tables');
1148 function listOfFields($database,$table) {
1149 if ($this->backendType() == 'mysql') {
1151 assert(!empty($database));
1152 assert(!empty($table));
1153 $result = mysql_list_fields($database, $table, $this->_dbh->connection) or
1154 trigger_error(__FILE__.':'.__LINE__.' '.mysql_error(), E_USER_WARNING);
1155 if (!$result) return array();
1156 $columns = mysql_num_fields($result);
1157 for ($i = 0; $i < $columns; $i++) {
1158 $fields[] = mysql_field_name($result, $i);
1160 mysql_free_result($result);
1163 // TODO: try ADODB version?
1164 trigger_error("Unsupported dbtype and backend. Either switch to ADODB or check it manually.");
1171 * This class is a generic iterator.
1173 * WikiDB_backend_PearDB_iter only iterates over things that have
1174 * 'pagename', 'pagedata', etc. etc.
1176 * Probably WikiDB_backend_PearDB_iter and this class should be merged
1177 * (most of the code is cut-and-paste :-( ), but I am trying to make
1178 * changes that could be merged easily.
1180 * @author: Dan Frankowski
1182 class WikiDB_backend_PearDB_generic_iter
1183 extends WikiDB_backend_iterator
1185 function WikiDB_backend_PearDB_generic_iter($backend, $query_result, $field_list = NULL) {
1186 if (DB::isError($query_result)) {
1187 // This shouldn't happen, I thought.
1188 $backend->_pear_error_callback($query_result);
1191 $this->_backend = &$backend;
1192 $this->_result = $query_result;
1193 $this->_options = $field_list;
1197 if (!$this->_result)
1199 return $this->_result->numRows();
1203 if (!$this->_result)
1206 $record = $this->_result->fetchRow(DB_FETCHMODE_ASSOC);
1216 if ($this->_result) {
1217 $this->_result->MoveFirst();
1222 if ($this->_result) {
1223 $this->_result->free();
1224 $this->_result = false;
1228 function asArray () {
1230 while ($page = $this->next())
1236 class WikiDB_backend_PearDB_iter
1237 extends WikiDB_backend_PearDB_generic_iter
1241 $backend = &$this->_backend;
1242 if (!$this->_result)
1245 $record = $this->_result->fetchRow(DB_FETCHMODE_ASSOC);
1251 $pagedata = $backend->_extract_page_data($record);
1252 $rec = array('pagename' => $record['pagename'],
1253 'pagedata' => $pagedata);
1255 if (!empty($record['version'])) {
1256 $rec['versiondata'] = $backend->_extract_version_data($record);
1257 $rec['version'] = $record['version'];
1264 class WikiDB_backend_PearDB_search extends WikiDB_backend_search_sql
1266 // no surrounding quotes because we know it's a string
1267 // function _quote($word) { return $this->_dbh->addq($word); }
1273 // c-basic-offset: 4
1274 // c-hanging-comment-ender-p: nil
1275 // indent-tabs-mode: nil