2 rcs_id('$Id: PearDB_mysql.php,v 1.26 2006-12-22 17:49:05 rurban Exp $');
4 require_once('lib/WikiDB/backend/PearDB.php');
6 // See http://sql-info.de/mysql/gotchas.html for mysql specific quirks.
8 // The slowest function overall is mysql_connect with [680ms]
9 // 2nd is db_mysql::simpleQuery with [257ms]
10 class WikiDB_backend_PearDB_mysql
11 extends WikiDB_backend_PearDB
16 function WikiDB_backend_PearDB_mysql($dbparams) {
17 $this->WikiDB_backend_PearDB($dbparams);
18 //$this->_serverinfo = $this->_dbh->ServerInfo();
19 $row = $this->_dbh->GetOne("SELECT version()");
20 if (!DB::isError($row) and !empty($row)) {
21 $arr = explode('.',$row);
22 $this->_serverinfo['version'] = (string)(($arr[0] * 100) + $arr[1]) .
23 "." . (integer)$arr[2];
24 if ($this->_serverinfo['version'] < 323.0) {
25 // Older MySQL's don't have CASE WHEN ... END
26 $this->_expressions['maxmajor'] = "MAX(IF(minor_edit=0,version,0))";
27 $this->_expressions['maxminor'] = "MAX(IF(minor_edit<>0,version,0))";
29 // esp. needed for utf databases
30 if ($this->_serverinfo['version'] > 401.0) {
32 $aliases = array('iso-8859-1' => 'latin1',
34 //http://dev.mysql.com/doc/mysql/en/charset-connection.html
35 if (isset($aliases[strtolower($charset)])) {
36 // mysql needs special unusual names and doesn't resolve aliases
37 mysql_query("SET NAMES '". $aliases[$charset] . "'");
39 mysql_query("SET NAMES '$charset'");
46 * Kill timed out processes. ( so far only called on about every 50-th save. )
49 if (empty($this->_dbparams['timeout'])) return;
50 $result = mysql_query("SHOW processlist");
51 while ($row = mysql_fetch_array($result)) {
52 if ($row["db"] == $this->_dbh->dsn['database']
53 and $row["User"] == $this->_dbh->dsn['username']
54 and $row["Time"] > $this->_dbparams['timeout']
55 and $row["Command"] == "Sleep")
57 $process_id = $row["Id"];
58 mysql_query("KILL $process_id");
64 * Create a new revision of a page.
66 function set_versiondata($pagename, $version, $data) {
68 $version_tbl = $this->_table_names['version_tbl'];
70 $minor_edit = (int) !empty($data['is_minor_edit']);
71 unset($data['is_minor_edit']);
73 $mtime = (int)$data['mtime'];
74 unset($data['mtime']);
75 assert(!empty($mtime));
77 @$content = (string) $data['%content'];
78 unset($data['%content']);
79 unset($data['%pagedata']);
82 $id = $this->_get_pageid($pagename, true);
83 // requires PRIMARY KEY (id,version)!
84 // VALUES supported since mysql-3.22.5
85 $dbh->query(sprintf("REPLACE INTO $version_tbl"
86 . " (id,version,mtime,minor_edit,content,versiondata)"
87 . " VALUES(%d,%d,%d,%d,'%s','%s')",
88 $id, $version, $mtime, $minor_edit,
89 $dbh->escapeSimple($content),
90 $dbh->escapeSimple($this->_serialize($data))
92 // real binding (prepare,execute) only since mysqli + PHP5
93 $this->_update_recent_table($id);
94 $this->_update_nonempty_table($id);
98 function _update_recent_table($pageid = false) {
100 extract($this->_table_names);
101 extract($this->_expressions);
103 $pageid = (int)$pageid;
105 // optimized: mysql can do this with one REPLACE INTO.
106 // supported in every (?) mysql version
107 // requires PRIMARY KEY (id)!
108 $dbh->query("REPLACE INTO $recent_tbl"
109 . " (id, latestversion, latestmajor, latestminor)"
110 . " SELECT id, $maxversion, $maxmajor, $maxminor"
111 . " FROM $version_tbl"
112 . ( $pageid ? " WHERE id=$pageid" : "")
116 /* ISNULL is mysql specific */
117 function wanted_pages($exclude_from='', $exclude='', $sortby=false, $limit=false) {
119 extract($this->_table_names);
120 if ($orderby = $this->sortby($sortby, 'db', array('pagename','wantedfrom')))
121 $orderby = 'ORDER BY ' . $orderby;
123 if ($exclude_from) // array of pagenames
124 $exclude_from = " AND pp.pagename NOT IN ".$this->_sql_set($exclude_from);
125 if ($exclude) // array of pagenames
126 $exclude = " AND p.pagename NOT IN ".$this->_sql_set($exclude);
128 $sql = "SELECT p.pagename, pp.pagename as wantedfrom"
129 . " FROM $page_tbl p, $link_tbl linked"
130 . " LEFT JOIN $page_tbl pp ON (linked.linkto = pp.id)"
131 . " LEFT JOIN $nonempty_tbl ne ON (linked.linkto = ne.id)"
132 . " WHERE ISNULL(ne.id)"
133 . " AND p.id = linked.linkfrom"
138 list($from, $count) = $this->limit($limit);
139 $result = $dbh->limitQuery($sql, $from, $count * 3);
141 $result = $dbh->query($sql);
143 return new WikiDB_backend_PearDB_generic_iter($this, $result);
146 /* // REPLACE will not delete empy pages, so it was removed --ru
147 function _update_nonempty_table($pageid = false) {
149 extract($this->_table_names);
151 $pageid = (int)$pageid;
153 // Optimized: mysql can do this with one REPLACE INTO.
154 // supported in every (?) mysql version
155 // requires PRIMARY KEY (id)
156 $dbh->query("REPLACE INTO $nonempty_tbl (id)"
157 . " SELECT $recent_tbl.id"
158 . " FROM $recent_tbl, $version_tbl"
159 . " WHERE $recent_tbl.id=$version_tbl.id"
160 . " AND version=latestversion"
162 . ( $pageid ? " AND $recent_tbl.id=$pageid" : ""));
169 function optimize() {
172 foreach ($this->_table_names as $table) {
173 $dbh->query("OPTIMIZE TABLE $table");
181 function _lock_tables($write_lock = true) {
182 $lock_type = $write_lock ? "WRITE" : "READ";
183 foreach ($this->_table_names as $table) {
184 $tables[] = "$table $lock_type";
186 $this->_dbh->query("LOCK TABLES " . join(",", $tables));
192 function _unlock_tables() {
193 $this->_dbh->query("UNLOCK TABLES");
196 function increaseHitCount($pagename) {
198 // Hits is the only thing we can update in a fast manner.
199 // Note that this will fail silently if the page does not
200 // have a record in the page table. Since it's just the
201 // hit count, who cares?
203 $dbh->query(sprintf("UPDATE LOW_PRIORITY %s SET hits=hits+1 WHERE pagename='%s' %s",
204 $this->_table_names['page_tbl'],
205 $dbh->escapeSimple($pagename),
206 ($this->_serverinfo['version'] >= 323.0) ? "LIMIT 1": ""));
212 class WikiDB_backend_PearDB_mysql_search
213 extends WikiDB_backend_PearDB_search
215 function _pagename_match_clause($node) {
216 $word = $node->sql();
217 if ($node->op == 'REGEX') { // posix regex extensions
218 return "pagename REGEXP '$word'";
220 return ($this->_case_exact
221 ? "pagename LIKE '$word'"
222 : "LOWER(pagename) LIKE '$word'");
227 // $Log: not supported by cvs2svn $
229 // (c-file-style: "gnu")
234 // c-hanging-comment-ender-p: nil
235 // indent-tabs-mode: nil