1: <?php
2: /**
3: * Copyright 2007 Maintainable Software, LLC
4: * Copyright 2008-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: * @author Mike Naberezny <mike@maintainable.com>
17: * @author Derek DeVries <derek@maintainable.com>
18: * @author Chuck Hagenbuch <chuck@horde.org>
19: * @license http://www.horde.org/licenses/bsd
20: * @category Horde
21: * @package Db
22: * @subpackage Adapter
23: */
24: abstract class Horde_Db_Adapter_Base implements Horde_Db_Adapter
25: {
26: /**
27: * Config options.
28: *
29: * @var array
30: */
31: protected $_config = array();
32:
33: /**
34: * DB connection.
35: *
36: * @var mixed
37: */
38: protected $_connection = null;
39:
40: /**
41: * Has a transaction been started?
42: *
43: * @var boolean
44: */
45: protected $_transactionStarted = false;
46:
47: /**
48: * The last query sent to the database.
49: *
50: * @var string
51: */
52: protected $_lastQuery;
53:
54: /**
55: * Row count of last action.
56: *
57: * @var integer
58: */
59: protected $_rowCount = null;
60:
61: /**
62: * Runtime of last query.
63: *
64: * @var integer
65: */
66: protected $_runtime;
67:
68: /**
69: * Is connection active?
70: *
71: * @var boolean
72: */
73: protected $_active = null;
74:
75: /**
76: * Cache object.
77: *
78: * @var Horde_Cache
79: */
80: protected $_cache;
81:
82: /**
83: * Log object.
84: *
85: * @var Horde_Log_Logger
86: */
87: protected $_logger;
88:
89: /**
90: * Schema object.
91: *
92: * @var Horde_Db_Adapter_Base_Schema
93: */
94: protected $_schema = null;
95:
96: /**
97: * Schema class to use.
98: *
99: * @var string
100: */
101: protected $_schemaClass = null;
102:
103: /**
104: * List of schema methods.
105: *
106: * @var array
107: */
108: protected $_schemaMethods = array();
109:
110:
111: /*##########################################################################
112: # Construct/Destruct
113: ##########################################################################*/
114:
115: /**
116: * Constructor.
117: *
118: * @param array $config Configuration options and optional objects:
119: * <pre>
120: * 'charset' - (string) TODO
121: * </pre>
122: */
123: public function __construct($config)
124: {
125: /* Can't set cache/logger in constructor - these objects may use DB
126: * for storage. Add stubs for now - they have to be manually set
127: * later with setCache() and setLogger(). */
128: $this->_cache = new Horde_Support_Stub();
129: $this->_logger = new Horde_Support_Stub();
130:
131: // Default to UTF-8
132: if (!isset($config['charset'])) {
133: $config['charset'] = 'UTF-8';
134: }
135:
136: $this->_config = $config;
137: $this->_runtime = 0;
138:
139: if (!$this->_schemaClass) {
140: $this->_schemaClass = __CLASS__ . '_Schema';
141: }
142:
143: $this->connect();
144: }
145:
146: /**
147: * Free any resources that are open.
148: */
149: public function __destruct()
150: {
151: $this->disconnect();
152: }
153:
154: /**
155: * Serialize callback.
156: */
157: public function __sleep()
158: {
159: return array_diff(array_keys(get_class_vars(__CLASS__)), array('_active', '_connection'));
160: }
161:
162: /**
163: * Unserialize callback.
164: */
165: public function __wakeup()
166: {
167: $this->_schema->setAdapter($this);
168: $this->connect();
169: }
170:
171: /**
172: * Returns an adaptor option set through the constructor.
173: *
174: * @param string $option The option to return.
175: *
176: * @return mixed The option value or null if option doesn't exist or is
177: * not set.
178: */
179: public function getOption($option)
180: {
181: return isset($this->_config[$option]) ? $this->_config[$option] : null;
182: }
183:
184: /*##########################################################################
185: # Dependency setters/getters
186: ##########################################################################*/
187:
188: /**
189: * Set a cache object.
190: *
191: * @inject
192: *
193: * @var Horde_Cache $logger The cache object.
194: */
195: public function setCache(Horde_Cache $cache)
196: {
197: $this->_cache = $cache;
198: }
199:
200: /**
201: * @return Horde_Cache
202: */
203: public function getCache()
204: {
205: return $this->_cache;
206: }
207:
208: /**
209: * Set a logger object.
210: *
211: * @inject
212: *
213: * @var Horde_Log_Logger $logger The logger object.
214: */
215: public function setLogger(Horde_Log_Logger $logger)
216: {
217: $this->_logger = $logger;
218: }
219:
220: /**
221: * return Horde_Log_Logger
222: */
223: public function getLogger()
224: {
225: return $this->_logger;
226: }
227:
228:
229: /*##########################################################################
230: # Object composition
231: ##########################################################################*/
232:
233: /**
234: * Delegate calls to the schema object.
235: *
236: * @param string $method
237: * @param array $args
238: *
239: * @return mixed TODO
240: * @throws BadMethodCallException
241: */
242: public function __call($method, $args)
243: {
244: if (!$this->_schema) {
245: // Create the database-specific (but not adapter specific) schema
246: // object.
247: $this->_schema = new $this->_schemaClass($this, array(
248: 'cache' => $this->_cache,
249: 'logger' => $this->_logger
250: ));
251: $this->_schemaMethods = array_flip(get_class_methods($this->_schema));
252: }
253:
254: if (isset($this->_schemaMethods[$method])) {
255: return call_user_func_array(array($this->_schema, $method), $args);
256: }
257:
258: $support = new Horde_Support_Backtrace();
259: $context = $support->getContext(1);
260: $caller = $context['function'];
261: if (isset($context['class'])) {
262: $caller = $context['class'] . '::' . $caller;
263: }
264: throw new BadMethodCallException('Call to undeclared method "' . get_class($this) . '::' . $method . '" from "' . $caller . '"');
265: }
266:
267:
268: /*##########################################################################
269: # Public
270: ##########################################################################*/
271:
272: /**
273: * Returns the human-readable name of the adapter. Use mixed case - one
274: * can always use downcase if needed.
275: *
276: * @return string
277: */
278: public function adapterName()
279: {
280: return 'Base';
281: }
282:
283: /**
284: * Does this adapter support migrations? Backend specific, as the
285: * abstract adapter always returns +false+.
286: *
287: * @return boolean
288: */
289: public function supportsMigrations()
290: {
291: return false;
292: }
293:
294: /**
295: * Does this adapter support using DISTINCT within COUNT? This is +true+
296: * for all adapters except sqlite.
297: *
298: * @return boolean
299: */
300: public function supportsCountDistinct()
301: {
302: return true;
303: }
304:
305: /**
306: * Should primary key values be selected from their corresponding
307: * sequence before the insert statement? If true, next_sequence_value
308: * is called before each insert to set the record's primary key.
309: * This is false for all adapters but Firebird.
310: *
311: * @return boolean
312: */
313: public function prefetchPrimaryKey($tableName = null)
314: {
315: return false;
316: }
317:
318: /**
319: * Get the last query run
320: *
321: * @return string
322: */
323: public function getLastQuery()
324: {
325: return $this->_lastQuery;
326: }
327:
328: /**
329: * Reset the timer
330: *
331: * @return integer
332: */
333: public function resetRuntime()
334: {
335: $runtime = $this->_runtime;
336: $this->_runtime = 0;
337:
338: return $this->_runtime;
339: }
340:
341:
342: /*##########################################################################
343: # Connection Management
344: ##########################################################################*/
345:
346: /**
347: * Is the connection active?
348: *
349: * @return boolean
350: */
351: public function isActive()
352: {
353: return $this->_active && $this->_connection;
354: }
355:
356: /**
357: * Reconnect to the db.
358: */
359: public function reconnect()
360: {
361: $this->disconnect();
362: $this->connect();
363: }
364:
365: /**
366: * Disconnect from db.
367: */
368: public function disconnect()
369: {
370: $this->_connection = null;
371: $this->_active = false;
372: }
373:
374: /**
375: * Provides access to the underlying database connection. Useful for when
376: * you need to call a proprietary method such as postgresql's
377: * lo_* methods.
378: *
379: * @return resource
380: */
381: public function rawConnection()
382: {
383: return $this->_connection;
384: }
385:
386:
387: /*##########################################################################
388: # Database Statements
389: ##########################################################################*/
390:
391: /**
392: * Returns an array of records with the column names as keys, and
393: * column values as values.
394: *
395: * @param string $sql SQL statement.
396: * @param mixed $arg1 Either an array of bound parameters or a query
397: * name.
398: * @param string $arg2 If $arg1 contains bound parameters, the query
399: * name.
400: *
401: * @return PDOStatement
402: * @throws Horde_Db_Exception
403: */
404: public function select($sql, $arg1 = null, $arg2 = null)
405: {
406: return $this->execute($sql, $arg1, $arg2);
407: }
408:
409: /**
410: * Returns an array of record hashes with the column names as keys and
411: * column values as values.
412: *
413: * @param string $sql SQL statement.
414: * @param mixed $arg1 Either an array of bound parameters or a query
415: * name.
416: * @param string $arg2 If $arg1 contains bound parameters, the query
417: * name.
418: *
419: * @return array
420: * @throws Horde_Db_Exception
421: */
422: public function selectAll($sql, $arg1 = null, $arg2 = null)
423: {
424: $rows = array();
425: $result = $this->select($sql, $arg1, $arg2);
426: if ($result) {
427: foreach ($result as $row) {
428: $rows[] = $row;
429: }
430: }
431: return $rows;
432: }
433:
434: /**
435: * Returns a record hash with the column names as keys and column values
436: * as values.
437: *
438: * @param string $sql SQL statement.
439: * @param mixed $arg1 Either an array of bound parameters or a query
440: * name.
441: * @param string $arg2 If $arg1 contains bound parameters, the query
442: * name.
443: *
444: * @return array
445: * @throws Horde_Db_Exception
446: */
447: public function selectOne($sql, $arg1 = null, $arg2 = null)
448: {
449: $result = $this->selectAll($sql, $arg1, $arg2);
450: return $result
451: ? next($result)
452: : array();
453: }
454:
455: /**
456: * Returns a single value from a record
457: *
458: * @param string $sql SQL statement.
459: * @param mixed $arg1 Either an array of bound parameters or a query
460: * name.
461: * @param string $arg2 If $arg1 contains bound parameters, the query
462: * name.
463: *
464: * @return string
465: * @throws Horde_Db_Exception
466: */
467: public function selectValue($sql, $arg1 = null, $arg2 = null)
468: {
469: $result = $this->selectOne($sql, $arg1, $arg2);
470:
471: return $result
472: ? next($result)
473: : null;
474: }
475:
476: /**
477: * Returns an array of the values of the first column in a select:
478: * selectValues("SELECT id FROM companies LIMIT 3") => [1,2,3]
479: *
480: * @param string $sql SQL statement.
481: * @param mixed $arg1 Either an array of bound parameters or a query
482: * name.
483: * @param string $arg2 If $arg1 contains bound parameters, the query
484: * name.
485: *
486: * @return array
487: * @throws Horde_Db_Exception
488: */
489: public function selectValues($sql, $arg1 = null, $arg2 = null)
490: {
491: $result = $this->selectAll($sql, $arg1, $arg2);
492: $values = array();
493: foreach ($result as $row) {
494: $values[] = next($row);
495: }
496: return $values;
497: }
498:
499: /**
500: * Returns an array where the keys are the first column of a select, and the
501: * values are the second column:
502: *
503: * selectAssoc("SELECT id, name FROM companies LIMIT 3") => [1 => 'Ford', 2 => 'GM', 3 => 'Chrysler']
504: *
505: * @param string $sql SQL statement.
506: * @param mixed $arg1 Either an array of bound parameters or a query
507: * name.
508: * @param string $arg2 If $arg1 contains bound parameters, the query
509: * name.
510: *
511: * @return array
512: * @throws Horde_Db_Exception
513: */
514: public function selectAssoc($sql, $arg1 = null, $arg2 = null)
515: {
516: $result = $this->selectAll($sql, $arg1, $arg2);
517: $values = array();
518: foreach ($result as $row) {
519: $values[current($row)] = next($row);
520: }
521: return $values;
522: }
523:
524: /**
525: * Executes the SQL statement in the context of this connection.
526: *
527: * @param string $sql SQL statement.
528: * @param mixed $arg1 Either an array of bound parameters or a query
529: * name.
530: * @param string $arg2 If $arg1 contains bound parameters, the query
531: * name.
532: *
533: * @return PDOStatement
534: * @throws Horde_Db_Exception
535: */
536: public function execute($sql, $arg1 = null, $arg2 = null)
537: {
538: if (!$this->isActive()) { $this->reconnect(); }
539:
540: if (is_array($arg1)) {
541: $sql = $this->_replaceParameters($sql, $arg1);
542: $name = $arg2;
543: } else {
544: $name = $arg1;
545: }
546:
547: $t = new Horde_Support_Timer;
548: $t->push();
549:
550: try {
551: $this->_lastQuery = $sql;
552: $stmt = $this->_connection->query($sql);
553: } catch (Exception $e) {
554: $this->_logError($sql, 'QUERY FAILED: ' . $e->getMessage());
555: $this->_logInfo($sql, $name);
556: throw new Horde_Db_Exception($e);
557: }
558:
559: $this->_logInfo($sql, $name, $t->pop());
560: $this->_rowCount = $stmt ? $stmt->rowCount() : 0;
561:
562: return $stmt;
563: }
564:
565: /**
566: * Inserts a row into a table.
567: *
568: * @param string $sql SQL statement.
569: * @param array|string $arg1 Either an array of bound parameters or a
570: * query name.
571: * @param string $arg2 If $arg1 contains bound parameters, the
572: * query name.
573: * @param string $pk The primary key column.
574: * @param integer $idValue The primary key value. This parameter is
575: * required if the primary key is inserted
576: * manually.
577: * @param string $sequenceName The sequence name.
578: *
579: * @return integer Last inserted ID.
580: * @throws Horde_Db_Exception
581: */
582: public function insert($sql, $arg1 = null, $arg2 = null, $pk = null,
583: $idValue = null, $sequenceName = null)
584: {
585: $this->execute($sql, $arg1, $arg2);
586:
587: return $idValue
588: ? $idValue
589: : $this->_connection->lastInsertId($sequenceName);
590: }
591:
592: /**
593: * Executes the update statement and returns the number of rows affected.
594: *
595: * @param string $sql SQL statement.
596: * @param mixed $arg1 Either an array of bound parameters or a query
597: * name.
598: * @param string $arg2 If $arg1 contains bound parameters, the query
599: * name.
600: *
601: * @return integer Number of rows affected.
602: * @throws Horde_Db_Exception
603: */
604: public function update($sql, $arg1 = null, $arg2 = null)
605: {
606: $this->execute($sql, $arg1, $arg2);
607: return $this->_rowCount;
608: }
609:
610: /**
611: * Executes the delete statement and returns the number of rows affected.
612: *
613: * @param string $sql SQL statement.
614: * @param mixed $arg1 Either an array of bound parameters or a query
615: * name.
616: * @param string $arg2 If $arg1 contains bound parameters, the query
617: * name.
618: *
619: * @return integer Number of rows affected.
620: * @throws Horde_Db_Exception
621: */
622: public function delete($sql, $arg1 = null, $arg2 = null)
623: {
624: $this->execute($sql, $arg1, $arg2);
625: return $this->_rowCount;
626: }
627:
628: /**
629: * Check if a transaction has been started.
630: *
631: * @return boolean True if transaction has been started.
632: */
633: public function transactionStarted()
634: {
635: return $this->_transactionStarted;
636: }
637:
638: /**
639: * Begins the transaction (and turns off auto-committing).
640: */
641: public function beginDbTransaction()
642: {
643: $this->_transactionStarted = true;
644: $this->_connection->beginTransaction();
645: }
646:
647: /**
648: * Commits the transaction (and turns on auto-committing).
649: */
650: public function commitDbTransaction()
651: {
652: $this->_connection->commit();
653: $this->_transactionStarted = false;
654: }
655:
656: /**
657: * Rolls back the transaction (and turns on auto-committing). Must be
658: * done if the transaction block raises an exception or returns false.
659: */
660: public function rollbackDbTransaction()
661: {
662: if (!$this->_transactionStarted) {
663: return;
664: }
665:
666: $this->_connection->rollBack();
667: $this->_transactionStarted = false;
668: }
669:
670: /**
671: * Appends LIMIT and OFFSET options to a SQL statement.
672: *
673: * @param string $sql SQL statement.
674: * @param array $options Hash with 'limit' and (optional) 'offset' values.
675: *
676: * @return string
677: */
678: public function addLimitOffset($sql, $options)
679: {
680: if (isset($options['limit']) && $limit = $options['limit']) {
681: if (isset($options['offset']) && $offset = $options['offset']) {
682: $sql .= " LIMIT $offset, $limit";
683: } else {
684: $sql .= " LIMIT $limit";
685: }
686: }
687: return $sql;
688: }
689:
690: /**
691: * TODO
692: */
693: public function sanitizeLimit($limit)
694: {
695: return (strpos($limit, ',') !== false)
696: ? implode(',', array_map('intval', explode(',', $limit)))
697: : intval($limit);
698: }
699:
700: /**
701: * Appends a locking clause to an SQL statement.
702: * This method *modifies* the +sql+ parameter.
703: *
704: * # SELECT * FROM suppliers FOR UPDATE
705: * add_lock! 'SELECT * FROM suppliers', :lock => true
706: * add_lock! 'SELECT * FROM suppliers', :lock => ' FOR UPDATE'
707: *
708: * @param string &$sql SQL statment.
709: * @param array $options TODO.
710: */
711: public function addLock(&$sql, array $options = array())
712: {
713: $sql .= (isset($options['lock']) && is_string($options['lock']))
714: ? ' ' . $options['lock']
715: : ' FOR UPDATE';
716: }
717:
718: /**
719: * Inserts the given fixture into the table. Overridden in adapters that
720: * require something beyond a simple insert (eg. Oracle).
721: *
722: * @param TODO $fixture TODO
723: * @param TODO $tableName TODO
724: *
725: * @return TODO
726: */
727: public function insertFixture($fixture, $tableName)
728: {
729: /*@TODO*/
730: return $this->execute("INSERT INTO #{quote_table_name(table_name)} (#{fixture.key_list}) VALUES (#{fixture.value_list})", 'Fixture Insert');
731: }
732:
733: /**
734: * TODO
735: *
736: * @param string $tableName TODO
737: *
738: * @return string TODO
739: */
740: public function emptyInsertStatement($tableName)
741: {
742: return 'INSERT INTO ' . $this->quoteTableName($tableName) . ' VALUES(DEFAULT)';
743: }
744:
745:
746: /*##########################################################################
747: # Protected
748: ##########################################################################*/
749:
750: /**
751: * Checks if required configuration keys are present.
752: *
753: * @param array $required Required configuration keys.
754: *
755: * @throws Horde_Db_Exception if a required key is missing.
756: */
757: protected function _checkRequiredConfig(array $required)
758: {
759: $diff = array_diff_key(array_flip($required), $this->_config);
760: if (!empty($diff)) {
761: $msg = 'Required config missing: ' . implode(', ', array_keys($diff));
762: throw new Horde_Db_Exception($msg);
763: }
764: }
765:
766: /**
767: * Replace ? in a SQL statement with quoted values from $args
768: *
769: * @param string $sql SQL statement.
770: * @param array $args TODO
771: *
772: * @return string Modified SQL statement.
773: * @throws Horde_Db_Exception
774: */
775: protected function _replaceParameters($sql, array $args)
776: {
777: $paramCount = substr_count($sql, '?');
778: if (count($args) != $paramCount) {
779: $this->_logError('Parameter count mismatch: ' . $sql, 'Horde_Db_Adapter_Base::_replaceParameters');
780: throw new Horde_Db_Exception('Parameter count mismatch');
781: }
782:
783: $sqlPieces = explode('?', $sql);
784: $sql = array_shift($sqlPieces);
785: while (count($sqlPieces)) {
786: $sql .= $this->quote(array_shift($args)) . array_shift($sqlPieces);
787: }
788: return $sql;
789: }
790:
791: /**
792: * Logs the SQL query for debugging.
793: *
794: * @param string $sql SQL statement.
795: * @param string $name TODO
796: * @param float $runtime Runtime interval.
797: */
798: protected function _logInfo($sql, $name, $runtime = null)
799: {
800: if ($this->_logger) {
801: $name = (empty($name) ? '' : $name)
802: . (empty($runtime) ? '' : sprintf(" (%.4fs)", $runtime));
803: $this->_logger->debug($this->_formatLogEntry($name, $sql));
804: }
805: }
806:
807: protected function _logError($error, $name, $runtime = null)
808: {
809: if ($this->_logger) {
810: $name = (empty($name) ? '' : $name)
811: . (empty($runtime) ? '' : sprintf(" (%.4fs)", $runtime));
812: $this->_logger->err($this->_formatLogEntry($name, $error));
813: }
814: }
815:
816: /**
817: * Formats the log entry.
818: *
819: * @param string $message Message.
820: * @param string $sql SQL statment.
821: *
822: * @return string Formatted log entry.
823: */
824: protected function _formatLogEntry($message, $sql)
825: {
826: return "SQL $message \n\t" . wordwrap(preg_replace("/\s+/", ' ', $sql), 70, "\n\t ", 1);
827: }
828: }
829: