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_Mysqli extends Horde_Db_Adapter_Base
27: {
28: /**
29: * Mysqli database connection object.
30: * @var mysqli
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: * @var boolean
47: */
48: protected $_hasMysqliFetchAll = false;
49:
50:
51: /*##########################################################################
52: # Public
53: ##########################################################################*/
54:
55: /**
56: * Returns the human-readable name of the adapter. Use mixed case - one
57: * can always use downcase if needed.
58: *
59: * @return string
60: */
61: public function adapterName()
62: {
63: return 'MySQLi';
64: }
65:
66: /**
67: * Does this adapter support migrations? Backend specific, as the
68: * abstract adapter always returns +false+.
69: *
70: * @return boolean
71: */
72: public function supportsMigrations()
73: {
74: return true;
75: }
76:
77:
78: /*##########################################################################
79: # Connection Management
80: ##########################################################################*/
81:
82: /**
83: * Connect to the db
84: *
85: * MySQLi can connect using SSL if $config contains an 'ssl' sub-array
86: * containing the following keys:
87: * + key The path to the key file.
88: * + cert The path to the certificate file.
89: * + ca The path to the certificate authority file.
90: * + capath The path to a directory that contains trusted SSL
91: * CA certificates in pem format.
92: * + cipher The list of allowable ciphers for SSL encryption.
93: *
94: * Example of how to connect using SSL:
95: * <code>
96: * $config = array(
97: * 'username' => 'someuser',
98: * 'password' => 'apasswd',
99: * 'hostspec' => 'localhost',
100: * 'database' => 'thedb',
101: * 'ssl' => array(
102: * 'key' => 'client-key.pem',
103: * 'cert' => 'client-cert.pem',
104: * 'ca' => 'cacert.pem',
105: * 'capath' => '/path/to/ca/dir',
106: * 'cipher' => 'AES',
107: * ),
108: * );
109: *
110: * $db = new Horde_Db_Adapter_Mysqli($config);
111: * </code>
112: */
113: public function connect()
114: {
115: if ($this->_active) {
116: return;
117: }
118:
119: $config = $this->_parseConfig();
120:
121: if (!empty($config['ssl'])) {
122: $mysqli = mysqli_init();
123: $mysqli->ssl_set(
124: empty($config['ssl']['key']) ? null : $config['ssl']['key'],
125: empty($config['ssl']['cert']) ? null : $config['ssl']['cert'],
126: empty($config['ssl']['ca']) ? null : $config['ssl']['ca'],
127: empty($config['ssl']['capath']) ? null : $config['ssl']['capath'],
128: empty($config['ssl']['cipher']) ? null : $config['ssl']['cipher']
129: );
130: $mysqli->real_connect(
131: $config['host'], $config['username'], $config['password'],
132: $config['dbname'], $config['port'], $config['socket']);
133: } else {
134: $mysqli = new mysqli(
135: $config['host'], $config['username'], $config['password'],
136: $config['dbname'], $config['port'], $config['socket']);
137: }
138: if (mysqli_connect_errno()) {
139: throw new Horde_Db_Exception('Connect failed: (' . mysqli_connect_errno() . ') ' . mysqli_connect_error(), mysqli_connect_errno());
140: }
141:
142: // If supported, request real datatypes from MySQL instead of returning
143: // everything as a string.
144: if (defined('MYSQLI_OPT_INT_AND_FLOAT_NATIVE')) {
145: $mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);
146: }
147:
148: $this->_connection = $mysqli;
149: $this->_active = true;
150:
151: // Set the default charset. http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html
152: if (!empty($config['charset'])) {
153: $this->setCharset($config['charset']);
154: }
155:
156: $this->_hasMysqliFetchAll = function_exists('mysqli_fetch_all');
157: }
158:
159: /**
160: * Disconnect from db
161: */
162: public function disconnect()
163: {
164: if ($this->_connection) { $this->_connection->close(); }
165: $this->_connection = null;
166: $this->_active = false;
167: }
168:
169: /**
170: * Check if the connection is active
171: *
172: * @return boolean
173: */
174: public function isActive()
175: {
176: $this->_lastQuery = 'SELECT 1';
177: return isset($this->_connection) && $this->_connection->query('SELECT 1');
178: }
179:
180:
181: /*##########################################################################
182: # Quoting
183: ##########################################################################*/
184:
185: /**
186: * Quotes a string, escaping any ' (single quote) and \ (backslash)
187: * characters..
188: *
189: * @param string $string
190: * @return string
191: */
192: public function quoteString($string)
193: {
194: return "'".$this->_connection->real_escape_string($string)."'";
195: }
196:
197:
198: /*##########################################################################
199: # Database Statements
200: ##########################################################################*/
201:
202: /**
203: * Returns an array of records with the column names as keys, and
204: * column values as values.
205: *
206: * @param string $sql
207: * @param mixed $arg1 Either an array of bound parameters or a query name.
208: * @param string $arg2 If $arg1 contains bound parameters, the query name.
209: * @return array
210: */
211: public function select($sql, $arg1=null, $arg2=null)
212: {
213: return new Horde_Db_Adapter_Mysqli_Result($this, $sql, $arg1, $arg2);
214: }
215:
216: /**
217: * Returns an array of record hashes with the column names as keys and
218: * column values as values.
219: *
220: * @param string $sql
221: * @param mixed $arg1 Either an array of bound parameters or a query name.
222: * @param string $arg2 If $arg1 contains bound parameters, the query name.
223: */
224: public function selectAll($sql, $arg1=null, $arg2=null)
225: {
226: $result = $this->execute($sql, $arg1, $arg2);
227: if ($this->_hasMysqliFetchAll) {
228: return $result->fetch_all(MYSQLI_ASSOC);
229: } else {
230: $rows = array();
231: if ($result) {
232: while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
233: $rows[] = $row;
234: }
235: }
236: }
237: return $rows;
238: }
239:
240: /**
241: * Returns a record hash with the column names as keys and column values as
242: * values.
243: *
244: * @param string $sql A query.
245: * @param mixed $arg1 Either an array of bound parameters or a query name.
246: * @param string $arg2 If $arg1 contains bound parameters, the query name.
247: *
248: * @return array|boolean A record hash or false if no record found.
249: */
250: public function selectOne($sql, $arg1 = null, $arg2 = null)
251: {
252: $result = $this->execute($sql, $arg1, $arg2);
253: $result = $result ? $result->fetch_array(MYSQLI_ASSOC) : array();
254: return is_null($result) ? false : $result;
255: }
256:
257: /**
258: * Returns a single value from a record
259: *
260: * @param string $sql
261: * @param mixed $arg1 Either an array of bound parameters or a query name.
262: * @param string $arg2 If $arg1 contains bound parameters, the query name.
263: * @return string
264: */
265: public function selectValue($sql, $arg1=null, $arg2=null)
266: {
267: $result = $this->selectOne($sql, $arg1, $arg2);
268: return $result ? current($result) : null;
269: }
270:
271: /**
272: * Returns an array of the values of the first column in a select:
273: * select_values("SELECT id FROM companies LIMIT 3") => [1,2,3]
274: *
275: * @param string $sql
276: * @param mixed $arg1 Either an array of bound parameters or a query name.
277: * @param string $arg2 If $arg1 contains bound parameters, the query name.
278: */
279: public function selectValues($sql, $arg1=null, $arg2=null)
280: {
281: $values = array();
282: $result = $this->execute($sql, $arg1, $arg2);
283: if ($result) {
284: while ($row = $result->fetch_row()) {
285: $values[] = $row[0];
286: }
287: }
288: return $values;
289: }
290:
291: /**
292: * Executes the SQL statement in the context of this connection.
293: *
294: * @param string $sql
295: * @param mixed $arg1 Either an array of bound parameters or a query name.
296: * @param string $arg2 If $arg1 contains bound parameters, the query name.
297: */
298: public function execute($sql, $arg1=null, $arg2=null)
299: {
300: if (is_array($arg1)) {
301: $sql = $this->_replaceParameters($sql, $arg1);
302: $name = $arg2;
303: } else {
304: $name = $arg1;
305: }
306:
307: $t = new Horde_Support_Timer();
308: $t->push();
309:
310: $this->_lastQuery = $sql;
311: $stmt = $this->_connection->query($sql);
312: if (!$stmt) {
313: $this->_logInfo($sql, 'QUERY FAILED: ' . $this->_connection->error);
314: $this->_logInfo($sql, $name);
315: throw new Horde_Db_Exception('QUERY FAILED: ' . $this->_connection->error . "\n\n" . $sql,
316: $this->_errorCode($this->_connection->sqlstate, $this->_connection->errno));
317: }
318:
319: $this->_logInfo($sql, $name, $t->pop());
320: //@TODO if ($this->_connection->info) $this->_loginfo($sql, $this->_connection->info);
321: //@TODO also log warnings? http://php.net/mysqli.warning-count and http://php.net/mysqli.get-warnings
322:
323: $this->_rowCount = $this->_connection->affected_rows;
324: $this->_insertId = $this->_connection->insert_id;
325: return $stmt;
326: }
327:
328: /**
329: * Returns the last auto-generated ID from the affected table.
330: *
331: * @param string $sql
332: * @param mixed $arg1 Either an array of bound parameters or a query name.
333: * @param string $arg2 If $arg1 contains bound parameters, the query name.
334: * @param string $pk
335: * @param int $idValue
336: * @param string $sequenceName
337: */
338: public function insert($sql, $arg1=null, $arg2=null, $pk=null, $idValue=null, $sequenceName=null)
339: {
340: $this->execute($sql, $arg1, $arg2);
341: return isset($idValue) ? $idValue : $this->_insertId;
342: }
343:
344: /**
345: * Begins the transaction (and turns off auto-committing).
346: */
347: public function beginDbTransaction()
348: {
349: $this->_transactionStarted = true;
350: $this->_connection->autocommit(false);
351: }
352:
353: /**
354: * Commits the transaction (and turns on auto-committing).
355: */
356: public function commitDbTransaction()
357: {
358: parent::commitDbTransaction();
359: $this->_connection->autocommit(true);
360: }
361:
362: /**
363: * Rolls back the transaction (and turns on auto-committing). Must be
364: * done if the transaction block raises an exception or returns false.
365: */
366: public function rollbackDbTransaction()
367: {
368: parent::rollbackDbTransaction();
369: $this->_connection->autocommit(true);
370: }
371:
372:
373: /*##########################################################################
374: # Protected
375: ##########################################################################*/
376:
377: /**
378: * Return a standard error code
379: *
380: * @param string $sqlstate
381: * @param integer $errno
382: * @return integer
383: */
384: protected function _errorCode($sqlstate, $errno)
385: {
386: /*@TODO do something with standard sqlstate vs. MySQL error codes vs. whatever else*/
387: return $errno;
388: }
389:
390: /**
391: * Parse configuration array into options for MySQLi constructor.
392: *
393: * @throws Horde_Db_Exception
394: * @return array [host, username, password, dbname, port, socket]
395: */
396: protected function _parseConfig()
397: {
398: $this->_checkRequiredConfig(array('username'));
399:
400: $rails2mysqli = array('database' => 'dbname');
401: foreach ($rails2mysqli as $from => $to) {
402: if (isset($this->_config[$from])) {
403: $this->_config[$to] = $this->_config[$from];
404: unset($this->_config[$from]);
405: }
406: }
407:
408: if (!empty($this->_config['host']) &&
409: $this->_config['host'] == 'localhost') {
410: $this->_config['host'] = '127.0.0.1';
411: }
412:
413: if (isset($this->_config['port'])) {
414: if (empty($this->_config['host'])) {
415: throw new Horde_Db_Exception('Host is required if port is specified');
416: }
417: }
418:
419: $config = $this->_config;
420:
421: if (!isset($config['host'])) $config['host'] = null;
422: if (!isset($config['username'])) $config['username'] = null;
423: if (!isset($config['password'])) $config['password'] = null;
424: if (!isset($config['dbname'])) $config['dbname'] = null;
425: if (!isset($config['port'])) $config['port'] = null;
426: if (!isset($config['socket'])) $config['socket'] = null;
427:
428: return $config;
429: }
430:
431: }
432: