1: <?php
2: /**
3: * Copyright 2007 Maintainable Software, LLC
4: * Copyright 2006-2012 Horde LLC (http://www.horde.org/)
5: *
6: * @author Mike Naberezny <mike@maintainable.com>
7: * @author Derek DeVries <derek@maintainable.com>
8: * @author Chuck Hagenbuch <chuck@horde.org>
9: * @license http://www.horde.org/licenses/bsd
10: * @category Horde
11: * @package Db
12: * @subpackage Adapter
13: */
14:
15: /**
16: * MySQL Improved Horde_Db_Adapter
17: *
18: * @author Mike Naberezny <mike@maintainable.com>
19: * @author Derek DeVries <derek@maintainable.com>
20: * @author Chuck Hagenbuch <chuck@horde.org>
21: * @license http://www.horde.org/licenses/bsd
22: * @category Horde
23: * @package Db
24: * @subpackage Adapter
25: */
26: class Horde_Db_Adapter_Mysql extends Horde_Db_Adapter_Base
27: {
28: /**
29: * Mysql database connection handle.
30: * @var resource
31: */
32: protected $_connection = null;
33:
34: /**
35: * Last auto-generated insert_id
36: * @var integer
37: */
38: protected $_insertId;
39:
40: /**
41: * @var string
42: */
43: protected $_schemaClass = 'Horde_Db_Adapter_Mysql_Schema';
44:
45:
46: /*##########################################################################
47: # Public
48: ##########################################################################*/
49:
50: /**
51: * Returns the human-readable name of the adapter. Use mixed case - one
52: * can always use downcase if needed.
53: *
54: * @return string
55: */
56: public function adapterName()
57: {
58: return 'MySQL';
59: }
60:
61: /**
62: * Does this adapter support migrations? Backend specific, as the
63: * abstract adapter always returns +false+.
64: *
65: * @return boolean
66: */
67: public function supportsMigrations()
68: {
69: return true;
70: }
71:
72:
73: /*##########################################################################
74: # Connection Management
75: ##########################################################################*/
76:
77: /**
78: * Connect to the db
79: */
80: public function connect()
81: {
82: if ($this->_active) {
83: return;
84: }
85:
86: $config = $this->_parseConfig();
87:
88: $oldTrackErrors = ini_set('track_errors', 1);
89: $mysql = @mysql_connect($config['host'], $config['username'], $config['password']);
90: ini_set('track_errors', $oldTrackErrors);
91:
92: if (!$mysql) {
93: throw new Horde_Db_Exception('Connect failed: ' . $php_errormsg);
94: }
95: if (!mysql_select_db($config['dbname'])) {
96: throw new Horde_Db_Exception('Could not select database: ' . $config['dbname']);
97: }
98:
99: $this->_connection = $mysql;
100: $this->_active = true;
101:
102: // Set the default charset. http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html
103: if (!empty($config['charset'])) {
104: $this->setCharset($config['charset']);
105: }
106: }
107:
108: /**
109: * Disconnect from db
110: */
111: public function disconnect()
112: {
113: if ($this->_connection) { @mysql_close($this->_connection); }
114: $this->_connection = null;
115: $this->_active = false;
116: }
117:
118: /**
119: * Check if the connection is active
120: *
121: * @return boolean
122: */
123: public function isActive()
124: {
125: return isset($this->_connection) && @mysql_ping($this->_connection);
126: }
127:
128:
129: /*##########################################################################
130: # Quoting
131: ##########################################################################*/
132:
133: /**
134: * Quotes a string, escaping any ' (single quote) and \ (backslash)
135: * characters..
136: *
137: * @param string $string
138: * @return string
139: */
140: public function quoteString($string)
141: {
142: return "'" . mysql_real_escape_string($string, $this->_connection) . "'";
143: }
144:
145:
146: /*##########################################################################
147: # Database Statements
148: ##########################################################################*/
149:
150: /**
151: * Returns an array of records with the column names as keys, and
152: * column values as values.
153: *
154: * @param string $sql
155: * @param mixed $arg1 Either an array of bound parameters or a query name.
156: * @param string $arg2 If $arg1 contains bound parameters, the query name.
157: * @return array
158: */
159: public function select($sql, $arg1 = null, $arg2 = null)
160: {
161: return new Horde_Db_Adapter_Mysql_Result($this, $sql, $arg1, $arg2);
162: }
163:
164: /**
165: * Returns an array of record hashes with the column names as keys and
166: * column values as values.
167: *
168: * @param string $sql
169: * @param mixed $arg1 Either an array of bound parameters or a query name.
170: * @param string $arg2 If $arg1 contains bound parameters, the query name.
171: */
172: public function selectAll($sql, $arg1 = null, $arg2 = null)
173: {
174: $result = $this->execute($sql, $arg1, $arg2);
175: $rows = array();
176: if ($result) {
177: while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
178: $rows[] = $row;
179: }
180: }
181: return $rows;
182: }
183:
184: /**
185: * Returns a record hash with the column names as keys and column values as
186: * values.
187: *
188: * @param string $sql A query.
189: * @param mixed $arg1 Either an array of bound parameters or a query name.
190: * @param string $arg2 If $arg1 contains bound parameters, the query name.
191: *
192: * @return array|boolean A record hash or false if no record found.
193: */
194: public function selectOne($sql, $arg1 = null, $arg2 = null)
195: {
196: $result = $this->execute($sql, $arg1, $arg2);
197: return $result ? mysql_fetch_array($result, MYSQL_ASSOC) : array();
198: }
199:
200: /**
201: * Returns a single value from a record
202: *
203: * @param string $sql
204: * @param mixed $arg1 Either an array of bound parameters or a query name.
205: * @param string $arg2 If $arg1 contains bound parameters, the query name.
206: * @return string
207: */
208: public function selectValue($sql, $arg1=null, $arg2=null)
209: {
210: $result = $this->selectOne($sql, $arg1, $arg2);
211: return $result ? current($result) : null;
212: }
213:
214: /**
215: * Returns an array of the values of the first column in a select:
216: * select_values("SELECT id FROM companies LIMIT 3") => [1,2,3]
217: *
218: * @param string $sql
219: * @param mixed $arg1 Either an array of bound parameters or a query name.
220: * @param string $arg2 If $arg1 contains bound parameters, the query name.
221: */
222: public function selectValues($sql, $arg1=null, $arg2=null)
223: {
224: $values = array();
225: $result = $this->execute($sql, $arg1, $arg2);
226: if ($result) {
227: while ($row = mysql_fetch_row($result)) {
228: $values[] = $row[0];
229: }
230: }
231: return $values;
232: }
233:
234: /**
235: * Executes the SQL statement in the context of this connection.
236: *
237: * @param string $sql
238: * @param mixed $arg1 Either an array of bound parameters or a query name.
239: * @param string $arg2 If $arg1 contains bound parameters, the query name.
240: */
241: public function execute($sql, $arg1=null, $arg2=null)
242: {
243: if (is_array($arg1)) {
244: $sql = $this->_replaceParameters($sql, $arg1);
245: $name = $arg2;
246: } else {
247: $name = $arg1;
248: }
249:
250: $t = new Horde_Support_Timer();
251: $t->push();
252:
253: $this->_lastQuery = $sql;
254: $stmt = mysql_query($sql, $this->_connection);
255: if (!$stmt) {
256: $this->_logInfo($sql, 'QUERY FAILED: ' . mysql_error($this->_connection));
257: $this->_logInfo($sql, $name);
258: throw new Horde_Db_Exception('QUERY FAILED: ' . mysql_error($this->_connection) . "\n\n" . $sql,
259: $this->_errorCode(null, mysql_errno($this->_connection)));
260: }
261:
262: $this->_logInfo($sql, $name, $t->pop());
263:
264: $this->_rowCount = mysql_affected_rows($this->_connection);
265: $this->_insertId = mysql_insert_id($this->_connection);
266: return $stmt;
267: }
268:
269: /**
270: * Returns the last auto-generated ID from the affected table.
271: *
272: * @param string $sql
273: * @param mixed $arg1 Either an array of bound parameters or a query name.
274: * @param string $arg2 If $arg1 contains bound parameters, the query name.
275: * @param string $pk
276: * @param int $idValue
277: * @param string $sequenceName
278: */
279: public function insert($sql, $arg1 = null, $arg2 = null, $pk = null, $idValue = null, $sequenceName = null)
280: {
281: $this->execute($sql, $arg1, $arg2);
282: return isset($idValue) ? $idValue : $this->_insertId;
283: }
284:
285: /**
286: * Begins the transaction (and turns off auto-committing).
287: */
288: public function beginDbTransaction()
289: {
290: $this->_transactionStarted = true;
291: $this->_lastQuery = 'SET AUTOCOMMIT=0; BEGIN';
292: @mysql_query('SET AUTOCOMMIT=0', $this->_connection) && @mysql_query('BEGIN', $this->_connection);
293: }
294:
295: /**
296: * Commits the transaction (and turns on auto-committing).
297: */
298: public function commitDbTransaction()
299: {
300: $this->_lastQuery = 'COMMIT; SET AUTOCOMMIT=1';
301: @mysql_query('COMMIT', $this->_connection) && @mysql_query('SET AUTOCOMMIT=1', $this->_connection);
302: $this->_transactionStarted = false;
303: }
304:
305: /**
306: * Rolls back the transaction (and turns on auto-committing). Must be
307: * done if the transaction block raises an exception or returns false.
308: */
309: public function rollbackDbTransaction()
310: {
311: if (!$this->_transactionStarted) {
312: return;
313: }
314:
315: $this->_lastQuery = 'ROLLBACK; SET AUTOCOMMIT=1';
316: @mysql_query('ROLLBACK', $this->_connection) && @mysql_query('SET AUTOCOMMIT=1', $this->_connection);
317: $this->_transactionStarted = false;
318: }
319:
320:
321: /*##########################################################################
322: # Protected
323: ##########################################################################*/
324:
325: /**
326: * Return a standard error code
327: *
328: * @param string $sqlstate
329: * @param integer $errno
330: * @return integer
331: */
332: protected function _errorCode($sqlstate, $errno)
333: {
334: /*@TODO do something with standard sqlstate vs. MySQL error codes vs. whatever else*/
335: return $errno;
336: }
337:
338: /**
339: * Parse configuration array into options for mysql_connect
340: *
341: * @throws Horde_Db_Exception
342: * @return array [host, username, password, dbname]
343: */
344: protected function _parseConfig()
345: {
346: $this->_checkRequiredConfig(array('username'));
347:
348: $rails2mysqli = array('database' => 'dbname');
349: foreach ($rails2mysqli as $from => $to) {
350: if (isset($this->_config[$from])) {
351: $this->_config[$to] = $this->_config[$from];
352: unset($this->_config[$from]);
353: }
354: }
355:
356: if (!empty($this->_config['host']) &&
357: $this->_config['host'] == 'localhost') {
358: $this->_config['host'] = '127.0.0.1';
359: }
360:
361: if (isset($this->_config['port'])) {
362: if (empty($this->_config['host'])) {
363: throw new Horde_Db_Exception('Host is required if port is specified');
364: }
365: $this->_config['host'] .= ':' . $this->_config['port'];
366: unset($this->_config['port']);
367: }
368:
369: if (!empty($this->_config['socket'])) {
370: if (!empty($this->_config['host'])) {
371: throw new Horde_Db_Exception('Can only specify host or socket, not both');
372: }
373: $this->_config['host'] = ':' . $this->_config['socket'];
374: unset($this->_config['socket']);
375: }
376:
377: $config = $this->_config;
378:
379: if (!isset($config['host'])) $config['host'] = null;
380: if (!isset($config['username'])) $config['username'] = null;
381: if (!isset($config['password'])) $config['password'] = null;
382: if (!isset($config['dbname'])) $config['dbname'] = null;
383:
384: return $config;
385: }
386:
387: }
388: