2 // $Id: ADODB_mysql.php 7956 2011-03-03 17:08:31Z vargenau $
4 require_once('lib/WikiDB/backend/ADODB.php');
7 * PROBLEM: mysql seems to be the simpliest (or most stupid) db on earth.
9 * See http://sql-info.de/mysql/gotchas.html for mysql specific quirks.
11 * Whenever a table is write-locked, you cannot even write to other unrelated
12 * tables. So it seems that we have to lock all tables!
13 * As workaround we try it with application locks, uniquely named locks,
14 * to prevent from concurrent writes of locks with the same name.
15 * The lock name is a strcat of the involved tables.
17 * See also http://use.perl.org/~Smylers/journal/34246 for strict mode and warnings.
19 define('DO_APP_LOCK',true);
20 define('DO_FULL_LOCK',false);
23 * WikiDB layer for ADODB-mysql, called by lib/WikiDB/ADODB.php.
24 * Now with support for the newer ADODB library, the ADODB extension library
25 * and more database drivers.
26 * To use transactions use the mysqlt driver: "mysqlt:..."
28 * @author: Lawrence Akka, Reini Urban
30 class WikiDB_backend_ADODB_mysql
31 extends WikiDB_backend_ADODB
36 function WikiDB_backend_ADODB_mysql($dbparams) {
37 $this->WikiDB_backend_ADODB($dbparams);
38 if (!$this->_dbh->_connectionID) return;
40 $this->_serverinfo = $this->_dbh->ServerInfo();
41 if (!empty($this->_serverinfo['version'])) {
42 $arr = explode('.',$this->_serverinfo['version']);
43 $this->_serverinfo['version'] = (string)(($arr[0] * 100) + $arr[1]) . "." . (integer)$arr[2];
45 if ($this->_serverinfo['version'] < 323.0) {
46 // Older MySQL's don't have CASE WHEN ... END
47 $this->_expressions['maxmajor'] = "MAX(IF(minor_edit=0,version,0))";
48 $this->_expressions['maxminor'] = "MAX(IF(minor_edit<>0,version,0))";
51 // esp. needed for utf databases
52 if ($this->_serverinfo['version'] > 401.0) {
54 $aliases = array('iso-8859-1' => 'latin1',
56 //http://dev.mysql.com/doc/mysql/en/charset-connection.html
57 if (isset($aliases[strtolower($charset)])) {
58 // mysql needs special unusual names and doesn't resolve aliases
59 mysql_query("SET NAMES '". $aliases[$charset] . "'");
61 mysql_query("SET NAMES '$charset'");
67 * Kill timed out processes. ( so far only called on about every 50-th save. )
70 if (empty($this->_dbparams['timeout'])) return;
71 $result = mysql_query("SHOW processlist");
72 while ($row = mysql_fetch_array($result)) {
73 if ($row["db"] == $this->_dsn['database']
74 and $row["User"] == $this->_dsn['username']
75 and $row["Time"] > $this->_dbparams['timeout']
76 and $row["Command"] == "Sleep")
78 $process_id = $row["Id"];
79 mysql_query("KILL $process_id");
90 foreach ($this->_table_names as $table) {
91 $dbh->Execute("OPTIMIZE TABLE $table");
97 * Lock tables. As fine-grained application lock, which locks only the
98 * same transaction (conflicting updates and edits), and as full table
101 * New: which tables as params,
102 * support nested locks via app locks
104 function _lock_tables($tables, $write_lock = true) {
105 if (!$tables) return;
107 $lock = join('-',$tables);
108 $result = $this->_dbh->GetRow("SELECT GET_LOCK('$lock',10)");
109 if (!$result or $result[0] == 0) {
110 trigger_error( "WARNING: Couldn't obtain application lock " . $lock . "\n<br />",
116 // if this is not enough:
117 $lock_type = $write_lock ? "WRITE" : "READ";
118 foreach ($this->_table_names as $key => $table) {
119 $locks[] = "$table $lock_type";
121 $this->_dbh->Execute("LOCK TABLES " . join(",", $locks));
127 * Support nested locks
129 function _unlock_tables($tables) {
131 $this->_dbh->Execute("UNLOCK TABLES");
135 $lock = join('-',$tables);
136 $result = $this->_dbh->Execute("SELECT RELEASE_LOCK('$lock')");
139 // if this is not enough:
140 $this->_dbh->Execute("UNLOCK TABLES");
144 function increaseHitCount($pagename) {
146 // Hits is the only thing we can update in a fast manner.
147 // Note that this will fail silently if the page does not
148 // have a record in the page table. Since it's just the
149 // hit count, who cares?
151 $dbh->Execute(sprintf("UPDATE LOW_PRIORITY %s SET hits=hits+1 WHERE pagename=%s %s",
152 $this->_table_names['page_tbl'],
153 $dbh->qstr($pagename),
154 ($this->_serverinfo['version'] >= 323.0) ? "LIMIT 1": "")
159 function _get_pageid($pagename, $create_if_missing = false) {
163 $cache =& $request->_dbi->_cache->_id_cache;
164 if (isset($cache[$pagename])) {
165 if ($cache[$pagename] or !$create_if_missing) {
166 return $cache[$pagename];
170 // attributes play this game.
171 if ($pagename === '') return 0;
174 $page_tbl = $this->_table_names['page_tbl'];
175 $query = sprintf("SELECT id FROM $page_tbl WHERE pagename=%s",
176 $dbh->qstr($pagename));
177 if (! $create_if_missing ) {
178 $row = $dbh->GetRow($query);
179 return $row ? $row[0] : false;
181 $row = $dbh->GetRow($query);
183 // have auto-incrementing, atomic version
184 $rs = $dbh->Execute(sprintf("INSERT INTO $page_tbl"
186 . " VALUES(NULL,%s)",
187 $dbh->qstr($pagename)));
188 $id = $dbh->_insertid();
197 * Create a new revision of a page.
199 function set_versiondata($pagename, $version, $data) {
201 $version_tbl = $this->_table_names['version_tbl'];
203 $minor_edit = (int) !empty($data['is_minor_edit']);
204 unset($data['is_minor_edit']);
206 $mtime = (int)$data['mtime'];
207 unset($data['mtime']);
208 assert(!empty($mtime));
210 @$content = (string) $data['%content'];
211 unset($data['%content']);
212 unset($data['%pagedata']);
214 $this->lock(array('page','recent','version','nonempty'));
216 $dbh->CommitLock($version_tbl);
217 $id = $this->_get_pageid($pagename, true);
218 $backend_type = $this->backendType();
219 // optimize: mysql can do this with one REPLACE INTO.
220 $rs = $dbh->Execute(sprintf("REPLACE INTO $version_tbl"
221 . " (id,version,mtime,minor_edit,content,versiondata)"
222 . " VALUES(%d,%d,%d,%d,%s,%s)",
223 $id, $version, $mtime, $minor_edit,
224 $dbh->qstr($content),
225 $dbh->qstr($this->_serialize($data))));
226 $this->_update_recent_table($id);
227 $this->_update_nonempty_table($id);
228 if ($rs) $dbh->CommitTrans( );
229 else $dbh->RollbackTrans( );
230 $this->unlock(array('page','recent','version','nonempty'));
239 // c-hanging-comment-ender-p: nil
240 // indent-tabs-mode: nil