1: <?php
2: /**
3: * Zend Framework
4: *
5: * LICENSE
6: *
7: * This source file is subject to the new BSD license that is bundled
8: * with this package in the file LICENSE.txt.
9: * It is also available through the world-wide-web at this URL:
10: * http://framework.zend.com/license/new-bsd
11: * If you did not receive a copy of the license and are unable to
12: * obtain it through the world-wide-web, please send an email
13: * to license@zend.com so we can send you a copy immediately.
14: *
15: * @category Zend
16: * @package Zend_Db
17: * @subpackage Adapter
18: * @copyright Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
19: * @license http://framework.zend.com/license/new-bsd New BSD License
20: * @version $Id: Abstract.php 23252 2010-10-26 12:48:32Z matthew $
21: */
22:
23:
24: /**
25: * @see Zend_Db
26: */
27: require_once 'Zend/Db.php';
28:
29: /**
30: * @see Zend_Db_Select
31: */
32: require_once 'Zend/Db/Select.php';
33:
34: /**
35: * Class for connecting to SQL databases and performing common operations.
36: *
37: * @category Zend
38: * @package Zend_Db
39: * @subpackage Adapter
40: * @copyright Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
41: * @license http://framework.zend.com/license/new-bsd New BSD License
42: */
43: abstract class Zend_Db_Adapter_Abstract
44: {
45:
46: /**
47: * User-provided configuration
48: *
49: * @var array
50: */
51: protected $_config = array();
52:
53: /**
54: * Fetch mode
55: *
56: * @var integer
57: */
58: protected $_fetchMode = Zend_Db::FETCH_ASSOC;
59:
60: /**
61: * Query profiler object, of type Zend_Db_Profiler
62: * or a subclass of that.
63: *
64: * @var Zend_Db_Profiler
65: */
66: protected $_profiler;
67:
68: /**
69: * Default class name for a DB statement.
70: *
71: * @var string
72: */
73: protected $_defaultStmtClass = 'Zend_Db_Statement';
74:
75: /**
76: * Default class name for the profiler object.
77: *
78: * @var string
79: */
80: protected $_defaultProfilerClass = 'Zend_Db_Profiler';
81:
82: /**
83: * Database connection
84: *
85: * @var object|resource|null
86: */
87: protected $_connection = null;
88:
89: /**
90: * Specifies the case of column names retrieved in queries
91: * Options
92: * Zend_Db::CASE_NATURAL (default)
93: * Zend_Db::CASE_LOWER
94: * Zend_Db::CASE_UPPER
95: *
96: * @var integer
97: */
98: protected $_caseFolding = Zend_Db::CASE_NATURAL;
99:
100: /**
101: * Specifies whether the adapter automatically quotes identifiers.
102: * If true, most SQL generated by Zend_Db classes applies
103: * identifier quoting automatically.
104: * If false, developer must quote identifiers themselves
105: * by calling quoteIdentifier().
106: *
107: * @var bool
108: */
109: protected $_autoQuoteIdentifiers = true;
110:
111: /**
112: * Keys are UPPERCASE SQL datatypes or the constants
113: * Zend_Db::INT_TYPE, Zend_Db::BIGINT_TYPE, or Zend_Db::FLOAT_TYPE.
114: *
115: * Values are:
116: * 0 = 32-bit integer
117: * 1 = 64-bit integer
118: * 2 = float or decimal
119: *
120: * @var array Associative array of datatypes to values 0, 1, or 2.
121: */
122: protected $_numericDataTypes = array(
123: Zend_Db::INT_TYPE => Zend_Db::INT_TYPE,
124: Zend_Db::BIGINT_TYPE => Zend_Db::BIGINT_TYPE,
125: Zend_Db::FLOAT_TYPE => Zend_Db::FLOAT_TYPE
126: );
127:
128: /** Weither or not that object can get serialized
129: *
130: * @var bool
131: */
132: protected $_allowSerialization = true;
133:
134: /**
135: * Weither or not the database should be reconnected
136: * to that adapter when waking up
137: *
138: * @var bool
139: */
140: protected $_autoReconnectOnUnserialize = false;
141:
142: /**
143: * Constructor.
144: *
145: * $config is an array of key/value pairs or an instance of Zend_Config
146: * containing configuration options. These options are common to most adapters:
147: *
148: * dbname => (string) The name of the database to user
149: * username => (string) Connect to the database as this username.
150: * password => (string) Password associated with the username.
151: * host => (string) What host to connect to, defaults to localhost
152: *
153: * Some options are used on a case-by-case basis by adapters:
154: *
155: * port => (string) The port of the database
156: * persistent => (boolean) Whether to use a persistent connection or not, defaults to false
157: * protocol => (string) The network protocol, defaults to TCPIP
158: * caseFolding => (int) style of case-alteration used for identifiers
159: *
160: * @param array|Zend_Config $config An array or instance of Zend_Config having configuration data
161: * @throws Zend_Db_Adapter_Exception
162: */
163: public function __construct($config)
164: {
165: /*
166: * Verify that adapter parameters are in an array.
167: */
168: if (!is_array($config)) {
169: /*
170: * Convert Zend_Config argument to a plain array.
171: */
172: if ($config instanceof Zend_Config) {
173: $config = $config->toArray();
174: } else {
175: /**
176: * @see Zend_Db_Adapter_Exception
177: */
178: require_once 'Zend/Db/Adapter/Exception.php';
179: throw new Zend_Db_Adapter_Exception('Adapter parameters must be in an array or a Zend_Config object');
180: }
181: }
182:
183: $this->_checkRequiredOptions($config);
184:
185: $options = array(
186: Zend_Db::CASE_FOLDING => $this->_caseFolding,
187: Zend_Db::AUTO_QUOTE_IDENTIFIERS => $this->_autoQuoteIdentifiers,
188: Zend_Db::FETCH_MODE => $this->_fetchMode,
189: );
190: $driverOptions = array();
191:
192: /*
193: * normalize the config and merge it with the defaults
194: */
195: if (array_key_exists('options', $config)) {
196: // can't use array_merge() because keys might be integers
197: foreach ((array) $config['options'] as $key => $value) {
198: $options[$key] = $value;
199: }
200: }
201: if (array_key_exists('driver_options', $config)) {
202: if (!empty($config['driver_options'])) {
203: // can't use array_merge() because keys might be integers
204: foreach ((array) $config['driver_options'] as $key => $value) {
205: $driverOptions[$key] = $value;
206: }
207: }
208: }
209:
210: if (!isset($config['charset'])) {
211: $config['charset'] = null;
212: }
213:
214: if (!isset($config['persistent'])) {
215: $config['persistent'] = false;
216: }
217:
218: $this->_config = array_merge($this->_config, $config);
219: $this->_config['options'] = $options;
220: $this->_config['driver_options'] = $driverOptions;
221:
222:
223: // obtain the case setting, if there is one
224: if (array_key_exists(Zend_Db::CASE_FOLDING, $options)) {
225: $case = (int) $options[Zend_Db::CASE_FOLDING];
226: switch ($case) {
227: case Zend_Db::CASE_LOWER:
228: case Zend_Db::CASE_UPPER:
229: case Zend_Db::CASE_NATURAL:
230: $this->_caseFolding = $case;
231: break;
232: default:
233: /** @see Zend_Db_Adapter_Exception */
234: require_once 'Zend/Db/Adapter/Exception.php';
235: throw new Zend_Db_Adapter_Exception('Case must be one of the following constants: '
236: . 'Zend_Db::CASE_NATURAL, Zend_Db::CASE_LOWER, Zend_Db::CASE_UPPER');
237: }
238: }
239:
240: if (array_key_exists(Zend_Db::FETCH_MODE, $options)) {
241: if (is_string($options[Zend_Db::FETCH_MODE])) {
242: $constant = 'Zend_Db::FETCH_' . strtoupper($options[Zend_Db::FETCH_MODE]);
243: if(defined($constant)) {
244: $options[Zend_Db::FETCH_MODE] = constant($constant);
245: }
246: }
247: $this->setFetchMode((int) $options[Zend_Db::FETCH_MODE]);
248: }
249:
250: // obtain quoting property if there is one
251: if (array_key_exists(Zend_Db::AUTO_QUOTE_IDENTIFIERS, $options)) {
252: $this->_autoQuoteIdentifiers = (bool) $options[Zend_Db::AUTO_QUOTE_IDENTIFIERS];
253: }
254:
255: // obtain allow serialization property if there is one
256: if (array_key_exists(Zend_Db::ALLOW_SERIALIZATION, $options)) {
257: $this->_allowSerialization = (bool) $options[Zend_Db::ALLOW_SERIALIZATION];
258: }
259:
260: // obtain auto reconnect on unserialize property if there is one
261: if (array_key_exists(Zend_Db::AUTO_RECONNECT_ON_UNSERIALIZE, $options)) {
262: $this->_autoReconnectOnUnserialize = (bool) $options[Zend_Db::AUTO_RECONNECT_ON_UNSERIALIZE];
263: }
264:
265: // create a profiler object
266: $profiler = false;
267: if (array_key_exists(Zend_Db::PROFILER, $this->_config)) {
268: $profiler = $this->_config[Zend_Db::PROFILER];
269: unset($this->_config[Zend_Db::PROFILER]);
270: }
271: $this->setProfiler($profiler);
272: }
273:
274: /**
275: * Check for config options that are mandatory.
276: * Throw exceptions if any are missing.
277: *
278: * @param array $config
279: * @throws Zend_Db_Adapter_Exception
280: */
281: protected function _checkRequiredOptions(array $config)
282: {
283: // we need at least a dbname
284: if (! array_key_exists('dbname', $config)) {
285: /** @see Zend_Db_Adapter_Exception */
286: require_once 'Zend/Db/Adapter/Exception.php';
287: throw new Zend_Db_Adapter_Exception("Configuration array must have a key for 'dbname' that names the database instance");
288: }
289:
290: if (! array_key_exists('password', $config)) {
291: /**
292: * @see Zend_Db_Adapter_Exception
293: */
294: require_once 'Zend/Db/Adapter/Exception.php';
295: throw new Zend_Db_Adapter_Exception("Configuration array must have a key for 'password' for login credentials");
296: }
297:
298: if (! array_key_exists('username', $config)) {
299: /**
300: * @see Zend_Db_Adapter_Exception
301: */
302: require_once 'Zend/Db/Adapter/Exception.php';
303: throw new Zend_Db_Adapter_Exception("Configuration array must have a key for 'username' for login credentials");
304: }
305: }
306:
307: /**
308: * Returns the underlying database connection object or resource.
309: * If not presently connected, this initiates the connection.
310: *
311: * @return object|resource|null
312: */
313: public function getConnection()
314: {
315: $this->_connect();
316: return $this->_connection;
317: }
318:
319: /**
320: * Returns the configuration variables in this adapter.
321: *
322: * @return array
323: */
324: public function getConfig()
325: {
326: return $this->_config;
327: }
328:
329: /**
330: * Set the adapter's profiler object.
331: *
332: * The argument may be a boolean, an associative array, an instance of
333: * Zend_Db_Profiler, or an instance of Zend_Config.
334: *
335: * A boolean argument sets the profiler to enabled if true, or disabled if
336: * false. The profiler class is the adapter's default profiler class,
337: * Zend_Db_Profiler.
338: *
339: * An instance of Zend_Db_Profiler sets the adapter's instance to that
340: * object. The profiler is enabled and disabled separately.
341: *
342: * An associative array argument may contain any of the keys 'enabled',
343: * 'class', and 'instance'. The 'enabled' and 'instance' keys correspond to the
344: * boolean and object types documented above. The 'class' key is used to name a
345: * class to use for a custom profiler. The class must be Zend_Db_Profiler or a
346: * subclass. The class is instantiated with no constructor arguments. The 'class'
347: * option is ignored when the 'instance' option is supplied.
348: *
349: * An object of type Zend_Config may contain the properties 'enabled', 'class', and
350: * 'instance', just as if an associative array had been passed instead.
351: *
352: * @param Zend_Db_Profiler|Zend_Config|array|boolean $profiler
353: * @return Zend_Db_Adapter_Abstract Provides a fluent interface
354: * @throws Zend_Db_Profiler_Exception if the object instance or class specified
355: * is not Zend_Db_Profiler or an extension of that class.
356: */
357: public function setProfiler($profiler)
358: {
359: $enabled = null;
360: $profilerClass = $this->_defaultProfilerClass;
361: $profilerInstance = null;
362:
363: if ($profilerIsObject = is_object($profiler)) {
364: if ($profiler instanceof Zend_Db_Profiler) {
365: $profilerInstance = $profiler;
366: } else if ($profiler instanceof Zend_Config) {
367: $profiler = $profiler->toArray();
368: } else {
369: /**
370: * @see Zend_Db_Profiler_Exception
371: */
372: require_once 'Zend/Db/Profiler/Exception.php';
373: throw new Zend_Db_Profiler_Exception('Profiler argument must be an instance of either Zend_Db_Profiler'
374: . ' or Zend_Config when provided as an object');
375: }
376: }
377:
378: if (is_array($profiler)) {
379: if (isset($profiler['enabled'])) {
380: $enabled = (bool) $profiler['enabled'];
381: }
382: if (isset($profiler['class'])) {
383: $profilerClass = $profiler['class'];
384: }
385: if (isset($profiler['instance'])) {
386: $profilerInstance = $profiler['instance'];
387: }
388: } else if (!$profilerIsObject) {
389: $enabled = (bool) $profiler;
390: }
391:
392: if ($profilerInstance === null) {
393: if (!class_exists($profilerClass)) {
394: require_once 'Zend/Loader.php';
395: Zend_Loader::loadClass($profilerClass);
396: }
397: $profilerInstance = new $profilerClass();
398: }
399:
400: if (!$profilerInstance instanceof Zend_Db_Profiler) {
401: /** @see Zend_Db_Profiler_Exception */
402: require_once 'Zend/Db/Profiler/Exception.php';
403: throw new Zend_Db_Profiler_Exception('Class ' . get_class($profilerInstance) . ' does not extend '
404: . 'Zend_Db_Profiler');
405: }
406:
407: if (null !== $enabled) {
408: $profilerInstance->setEnabled($enabled);
409: }
410:
411: $this->_profiler = $profilerInstance;
412:
413: return $this;
414: }
415:
416:
417: /**
418: * Returns the profiler for this adapter.
419: *
420: * @return Zend_Db_Profiler
421: */
422: public function getProfiler()
423: {
424: return $this->_profiler;
425: }
426:
427: /**
428: * Get the default statement class.
429: *
430: * @return string
431: */
432: public function getStatementClass()
433: {
434: return $this->_defaultStmtClass;
435: }
436:
437: /**
438: * Set the default statement class.
439: *
440: * @return Zend_Db_Adapter_Abstract Fluent interface
441: */
442: public function setStatementClass($class)
443: {
444: $this->_defaultStmtClass = $class;
445: return $this;
446: }
447:
448: /**
449: * Prepares and executes an SQL statement with bound data.
450: *
451: * @param mixed $sql The SQL statement with placeholders.
452: * May be a string or Zend_Db_Select.
453: * @param mixed $bind An array of data to bind to the placeholders.
454: * @return Zend_Db_Statement_Interface
455: */
456: public function query($sql, $bind = array())
457: {
458: // connect to the database if needed
459: $this->_connect();
460:
461: // is the $sql a Zend_Db_Select object?
462: if ($sql instanceof Zend_Db_Select) {
463: if (empty($bind)) {
464: $bind = $sql->getBind();
465: }
466:
467: $sql = $sql->assemble();
468: }
469:
470: // make sure $bind to an array;
471: // don't use (array) typecasting because
472: // because $bind may be a Zend_Db_Expr object
473: if (!is_array($bind)) {
474: $bind = array($bind);
475: }
476:
477: // prepare and execute the statement with profiling
478: $stmt = $this->prepare($sql);
479: $stmt->execute($bind);
480:
481: // return the results embedded in the prepared statement object
482: $stmt->setFetchMode($this->_fetchMode);
483: return $stmt;
484: }
485:
486: /**
487: * Leave autocommit mode and begin a transaction.
488: *
489: * @return Zend_Db_Adapter_Abstract
490: */
491: public function beginTransaction()
492: {
493: $this->_connect();
494: $q = $this->_profiler->queryStart('begin', Zend_Db_Profiler::TRANSACTION);
495: $this->_beginTransaction();
496: $this->_profiler->queryEnd($q);
497: return $this;
498: }
499:
500: /**
501: * Commit a transaction and return to autocommit mode.
502: *
503: * @return Zend_Db_Adapter_Abstract
504: */
505: public function commit()
506: {
507: $this->_connect();
508: $q = $this->_profiler->queryStart('commit', Zend_Db_Profiler::TRANSACTION);
509: $this->_commit();
510: $this->_profiler->queryEnd($q);
511: return $this;
512: }
513:
514: /**
515: * Roll back a transaction and return to autocommit mode.
516: *
517: * @return Zend_Db_Adapter_Abstract
518: */
519: public function rollBack()
520: {
521: $this->_connect();
522: $q = $this->_profiler->queryStart('rollback', Zend_Db_Profiler::TRANSACTION);
523: $this->_rollBack();
524: $this->_profiler->queryEnd($q);
525: return $this;
526: }
527:
528: /**
529: * Inserts a table row with specified data.
530: *
531: * @param mixed $table The table to insert data into.
532: * @param array $bind Column-value pairs.
533: * @return int The number of affected rows.
534: */
535: public function insert($table, array $bind)
536: {
537: // extract and quote col names from the array keys
538: $cols = array();
539: $vals = array();
540: $i = 0;
541: foreach ($bind as $col => $val) {
542: $cols[] = $this->quoteIdentifier($col, true);
543: if ($val instanceof Zend_Db_Expr) {
544: $vals[] = $val->__toString();
545: unset($bind[$col]);
546: } else {
547: if ($this->supportsParameters('positional')) {
548: $vals[] = '?';
549: } else {
550: if ($this->supportsParameters('named')) {
551: unset($bind[$col]);
552: $bind[':col'.$i] = $val;
553: $vals[] = ':col'.$i;
554: $i++;
555: } else {
556: /** @see Zend_Db_Adapter_Exception */
557: require_once 'Zend/Db/Adapter/Exception.php';
558: throw new Zend_Db_Adapter_Exception(get_class($this) ." doesn't support positional or named binding");
559: }
560: }
561: }
562: }
563:
564: // build the statement
565: $sql = "INSERT INTO "
566: . $this->quoteIdentifier($table, true)
567: . ' (' . implode(', ', $cols) . ') '
568: . 'VALUES (' . implode(', ', $vals) . ')';
569:
570: // execute the statement and return the number of affected rows
571: if ($this->supportsParameters('positional')) {
572: $bind = array_values($bind);
573: }
574: $stmt = $this->query($sql, $bind);
575: $result = $stmt->rowCount();
576: return $result;
577: }
578:
579: /**
580: * Updates table rows with specified data based on a WHERE clause.
581: *
582: * @param mixed $table The table to update.
583: * @param array $bind Column-value pairs.
584: * @param mixed $where UPDATE WHERE clause(s).
585: * @return int The number of affected rows.
586: */
587: public function update($table, array $bind, $where = '')
588: {
589: /**
590: * Build "col = ?" pairs for the statement,
591: * except for Zend_Db_Expr which is treated literally.
592: */
593: $set = array();
594: $i = 0;
595: foreach ($bind as $col => $val) {
596: if ($val instanceof Zend_Db_Expr) {
597: $val = $val->__toString();
598: unset($bind[$col]);
599: } else {
600: if ($this->supportsParameters('positional')) {
601: $val = '?';
602: } else {
603: if ($this->supportsParameters('named')) {
604: unset($bind[$col]);
605: $bind[':col'.$i] = $val;
606: $val = ':col'.$i;
607: $i++;
608: } else {
609: /** @see Zend_Db_Adapter_Exception */
610: require_once 'Zend/Db/Adapter/Exception.php';
611: throw new Zend_Db_Adapter_Exception(get_class($this) ." doesn't support positional or named binding");
612: }
613: }
614: }
615: $set[] = $this->quoteIdentifier($col, true) . ' = ' . $val;
616: }
617:
618: $where = $this->_whereExpr($where);
619:
620: /**
621: * Build the UPDATE statement
622: */
623: $sql = "UPDATE "
624: . $this->quoteIdentifier($table, true)
625: . ' SET ' . implode(', ', $set)
626: . (($where) ? " WHERE $where" : '');
627:
628: /**
629: * Execute the statement and return the number of affected rows
630: */
631: if ($this->supportsParameters('positional')) {
632: $stmt = $this->query($sql, array_values($bind));
633: } else {
634: $stmt = $this->query($sql, $bind);
635: }
636: $result = $stmt->rowCount();
637: return $result;
638: }
639:
640: /**
641: * Deletes table rows based on a WHERE clause.
642: *
643: * @param mixed $table The table to update.
644: * @param mixed $where DELETE WHERE clause(s).
645: * @return int The number of affected rows.
646: */
647: public function delete($table, $where = '')
648: {
649: $where = $this->_whereExpr($where);
650:
651: /**
652: * Build the DELETE statement
653: */
654: $sql = "DELETE FROM "
655: . $this->quoteIdentifier($table, true)
656: . (($where) ? " WHERE $where" : '');
657:
658: /**
659: * Execute the statement and return the number of affected rows
660: */
661: $stmt = $this->query($sql);
662: $result = $stmt->rowCount();
663: return $result;
664: }
665:
666: /**
667: * Convert an array, string, or Zend_Db_Expr object
668: * into a string to put in a WHERE clause.
669: *
670: * @param mixed $where
671: * @return string
672: */
673: protected function _whereExpr($where)
674: {
675: if (empty($where)) {
676: return $where;
677: }
678: if (!is_array($where)) {
679: $where = array($where);
680: }
681: foreach ($where as $cond => &$term) {
682: // is $cond an int? (i.e. Not a condition)
683: if (is_int($cond)) {
684: // $term is the full condition
685: if ($term instanceof Zend_Db_Expr) {
686: $term = $term->__toString();
687: }
688: } else {
689: // $cond is the condition with placeholder,
690: // and $term is quoted into the condition
691: $term = $this->quoteInto($cond, $term);
692: }
693: $term = '(' . $term . ')';
694: }
695:
696: $where = implode(' AND ', $where);
697: return $where;
698: }
699:
700: /**
701: * Creates and returns a new Zend_Db_Select object for this adapter.
702: *
703: * @return Zend_Db_Select
704: */
705: public function select()
706: {
707: return new Zend_Db_Select($this);
708: }
709:
710: /**
711: * Get the fetch mode.
712: *
713: * @return int
714: */
715: public function getFetchMode()
716: {
717: return $this->_fetchMode;
718: }
719:
720: /**
721: * Fetches all SQL result rows as a sequential array.
722: * Uses the current fetchMode for the adapter.
723: *
724: * @param string|Zend_Db_Select $sql An SQL SELECT statement.
725: * @param mixed $bind Data to bind into SELECT placeholders.
726: * @param mixed $fetchMode Override current fetch mode.
727: * @return array
728: */
729: public function fetchAll($sql, $bind = array(), $fetchMode = null)
730: {
731: if ($fetchMode === null) {
732: $fetchMode = $this->_fetchMode;
733: }
734: $stmt = $this->query($sql, $bind);
735: $result = $stmt->fetchAll($fetchMode);
736: return $result;
737: }
738:
739: /**
740: * Fetches the first row of the SQL result.
741: * Uses the current fetchMode for the adapter.
742: *
743: * @param string|Zend_Db_Select $sql An SQL SELECT statement.
744: * @param mixed $bind Data to bind into SELECT placeholders.
745: * @param mixed $fetchMode Override current fetch mode.
746: * @return array
747: */
748: public function fetchRow($sql, $bind = array(), $fetchMode = null)
749: {
750: if ($fetchMode === null) {
751: $fetchMode = $this->_fetchMode;
752: }
753: $stmt = $this->query($sql, $bind);
754: $result = $stmt->fetch($fetchMode);
755: return $result;
756: }
757:
758: /**
759: * Fetches all SQL result rows as an associative array.
760: *
761: * The first column is the key, the entire row array is the
762: * value. You should construct the query to be sure that
763: * the first column contains unique values, or else
764: * rows with duplicate values in the first column will
765: * overwrite previous data.
766: *
767: * @param string|Zend_Db_Select $sql An SQL SELECT statement.
768: * @param mixed $bind Data to bind into SELECT placeholders.
769: * @return array
770: */
771: public function fetchAssoc($sql, $bind = array())
772: {
773: $stmt = $this->query($sql, $bind);
774: $data = array();
775: while ($row = $stmt->fetch(Zend_Db::FETCH_ASSOC)) {
776: $tmp = array_values(array_slice($row, 0, 1));
777: $data[$tmp[0]] = $row;
778: }
779: return $data;
780: }
781:
782: /**
783: * Fetches the first column of all SQL result rows as an array.
784: *
785: * @param string|Zend_Db_Select $sql An SQL SELECT statement.
786: * @param mixed $bind Data to bind into SELECT placeholders.
787: * @return array
788: */
789: public function fetchCol($sql, $bind = array())
790: {
791: $stmt = $this->query($sql, $bind);
792: $result = $stmt->fetchAll(Zend_Db::FETCH_COLUMN, 0);
793: return $result;
794: }
795:
796: /**
797: * Fetches all SQL result rows as an array of key-value pairs.
798: *
799: * The first column is the key, the second column is the
800: * value.
801: *
802: * @param string|Zend_Db_Select $sql An SQL SELECT statement.
803: * @param mixed $bind Data to bind into SELECT placeholders.
804: * @return array
805: */
806: public function fetchPairs($sql, $bind = array())
807: {
808: $stmt = $this->query($sql, $bind);
809: $data = array();
810: while ($row = $stmt->fetch(Zend_Db::FETCH_NUM)) {
811: $data[$row[0]] = $row[1];
812: }
813: return $data;
814: }
815:
816: /**
817: * Fetches the first column of the first row of the SQL result.
818: *
819: * @param string|Zend_Db_Select $sql An SQL SELECT statement.
820: * @param mixed $bind Data to bind into SELECT placeholders.
821: * @return string
822: */
823: public function fetchOne($sql, $bind = array())
824: {
825: $stmt = $this->query($sql, $bind);
826: $result = $stmt->fetchColumn(0);
827: return $result;
828: }
829:
830: /**
831: * Quote a raw string.
832: *
833: * @param string $value Raw string
834: * @return string Quoted string
835: */
836: protected function _quote($value)
837: {
838: if (is_int($value)) {
839: return $value;
840: } elseif (is_float($value)) {
841: return sprintf('%F', $value);
842: }
843: return "'" . addcslashes($value, "\000\n\r\\'\"\032") . "'";
844: }
845:
846: /**
847: * Safely quotes a value for an SQL statement.
848: *
849: * If an array is passed as the value, the array values are quoted
850: * and then returned as a comma-separated string.
851: *
852: * @param mixed $value The value to quote.
853: * @param mixed $type OPTIONAL the SQL datatype name, or constant, or null.
854: * @return mixed An SQL-safe quoted value (or string of separated values).
855: */
856: public function quote($value, $type = null)
857: {
858: $this->_connect();
859:
860: if ($value instanceof Zend_Db_Select) {
861: return '(' . $value->assemble() . ')';
862: }
863:
864: if ($value instanceof Zend_Db_Expr) {
865: return $value->__toString();
866: }
867:
868: if (is_array($value)) {
869: foreach ($value as &$val) {
870: $val = $this->quote($val, $type);
871: }
872: return implode(', ', $value);
873: }
874:
875: if ($type !== null && array_key_exists($type = strtoupper($type), $this->_numericDataTypes)) {
876: $quotedValue = '0';
877: switch ($this->_numericDataTypes[$type]) {
878: case Zend_Db::INT_TYPE: // 32-bit integer
879: $quotedValue = (string) intval($value);
880: break;
881: case Zend_Db::BIGINT_TYPE: // 64-bit integer
882: // ANSI SQL-style hex literals (e.g. x'[\dA-F]+')
883: // are not supported here, because these are string
884: // literals, not numeric literals.
885: if (preg_match('/^(
886: [+-]? # optional sign
887: (?:
888: 0[Xx][\da-fA-F]+ # ODBC-style hexadecimal
889: |\d+ # decimal or octal, or MySQL ZEROFILL decimal
890: (?:[eE][+-]?\d+)? # optional exponent on decimals or octals
891: )
892: )/x',
893: (string) $value, $matches)) {
894: $quotedValue = $matches[1];
895: }
896: break;
897: case Zend_Db::FLOAT_TYPE: // float or decimal
898: $quotedValue = sprintf('%F', $value);
899: }
900: return $quotedValue;
901: }
902:
903: return $this->_quote($value);
904: }
905:
906: /**
907: * Quotes a value and places into a piece of text at a placeholder.
908: *
909: * The placeholder is a question-mark; all placeholders will be replaced
910: * with the quoted value. For example:
911: *
912: * <code>
913: * $text = "WHERE date < ?";
914: * $date = "2005-01-02";
915: * $safe = $sql->quoteInto($text, $date);
916: * // $safe = "WHERE date < '2005-01-02'"
917: * </code>
918: *
919: * @param string $text The text with a placeholder.
920: * @param mixed $value The value to quote.
921: * @param string $type OPTIONAL SQL datatype
922: * @param integer $count OPTIONAL count of placeholders to replace
923: * @return string An SQL-safe quoted value placed into the original text.
924: */
925: public function quoteInto($text, $value, $type = null, $count = null)
926: {
927: if ($count === null) {
928: return str_replace('?', $this->quote($value, $type), $text);
929: } else {
930: while ($count > 0) {
931: if (strpos($text, '?') !== false) {
932: $text = substr_replace($text, $this->quote($value, $type), strpos($text, '?'), 1);
933: }
934: --$count;
935: }
936: return $text;
937: }
938: }
939:
940: /**
941: * Quotes an identifier.
942: *
943: * Accepts a string representing a qualified indentifier. For Example:
944: * <code>
945: * $adapter->quoteIdentifier('myschema.mytable')
946: * </code>
947: * Returns: "myschema"."mytable"
948: *
949: * Or, an array of one or more identifiers that may form a qualified identifier:
950: * <code>
951: * $adapter->quoteIdentifier(array('myschema','my.table'))
952: * </code>
953: * Returns: "myschema"."my.table"
954: *
955: * The actual quote character surrounding the identifiers may vary depending on
956: * the adapter.
957: *
958: * @param string|array|Zend_Db_Expr $ident The identifier.
959: * @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
960: * @return string The quoted identifier.
961: */
962: public function quoteIdentifier($ident, $auto=false)
963: {
964: return $this->_quoteIdentifierAs($ident, null, $auto);
965: }
966:
967: /**
968: * Quote a column identifier and alias.
969: *
970: * @param string|array|Zend_Db_Expr $ident The identifier or expression.
971: * @param string $alias An alias for the column.
972: * @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
973: * @return string The quoted identifier and alias.
974: */
975: public function quoteColumnAs($ident, $alias, $auto=false)
976: {
977: return $this->_quoteIdentifierAs($ident, $alias, $auto);
978: }
979:
980: /**
981: * Quote a table identifier and alias.
982: *
983: * @param string|array|Zend_Db_Expr $ident The identifier or expression.
984: * @param string $alias An alias for the table.
985: * @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
986: * @return string The quoted identifier and alias.
987: */
988: public function quoteTableAs($ident, $alias = null, $auto = false)
989: {
990: return $this->_quoteIdentifierAs($ident, $alias, $auto);
991: }
992:
993: /**
994: * Quote an identifier and an optional alias.
995: *
996: * @param string|array|Zend_Db_Expr $ident The identifier or expression.
997: * @param string $alias An optional alias.
998: * @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
999: * @param string $as The string to add between the identifier/expression and the alias.
1000: * @return string The quoted identifier and alias.
1001: */
1002: protected function _quoteIdentifierAs($ident, $alias = null, $auto = false, $as = ' AS ')
1003: {
1004: if ($ident instanceof Zend_Db_Expr) {
1005: $quoted = $ident->__toString();
1006: } elseif ($ident instanceof Zend_Db_Select) {
1007: $quoted = '(' . $ident->assemble() . ')';
1008: } else {
1009: if (is_string($ident)) {
1010: $ident = explode('.', $ident);
1011: }
1012: if (is_array($ident)) {
1013: $segments = array();
1014: foreach ($ident as $segment) {
1015: if ($segment instanceof Zend_Db_Expr) {
1016: $segments[] = $segment->__toString();
1017: } else {
1018: $segments[] = $this->_quoteIdentifier($segment, $auto);
1019: }
1020: }
1021: if ($alias !== null && end($ident) == $alias) {
1022: $alias = null;
1023: }
1024: $quoted = implode('.', $segments);
1025: } else {
1026: $quoted = $this->_quoteIdentifier($ident, $auto);
1027: }
1028: }
1029: if ($alias !== null) {
1030: $quoted .= $as . $this->_quoteIdentifier($alias, $auto);
1031: }
1032: return $quoted;
1033: }
1034:
1035: /**
1036: * Quote an identifier.
1037: *
1038: * @param string $value The identifier or expression.
1039: * @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
1040: * @return string The quoted identifier and alias.
1041: */
1042: protected function _quoteIdentifier($value, $auto=false)
1043: {
1044: if ($auto === false || $this->_autoQuoteIdentifiers === true) {
1045: $q = $this->getQuoteIdentifierSymbol();
1046: return ($q . str_replace("$q", "$q$q", $value) . $q);
1047: }
1048: return $value;
1049: }
1050:
1051: /**
1052: * Returns the symbol the adapter uses for delimited identifiers.
1053: *
1054: * @return string
1055: */
1056: public function getQuoteIdentifierSymbol()
1057: {
1058: return '"';
1059: }
1060:
1061: /**
1062: * Return the most recent value from the specified sequence in the database.
1063: * This is supported only on RDBMS brands that support sequences
1064: * (e.g. Oracle, PostgreSQL, DB2). Other RDBMS brands return null.
1065: *
1066: * @param string $sequenceName
1067: * @return string
1068: */
1069: public function lastSequenceId($sequenceName)
1070: {
1071: return null;
1072: }
1073:
1074: /**
1075: * Generate a new value from the specified sequence in the database, and return it.
1076: * This is supported only on RDBMS brands that support sequences
1077: * (e.g. Oracle, PostgreSQL, DB2). Other RDBMS brands return null.
1078: *
1079: * @param string $sequenceName
1080: * @return string
1081: */
1082: public function nextSequenceId($sequenceName)
1083: {
1084: return null;
1085: }
1086:
1087: /**
1088: * Helper method to change the case of the strings used
1089: * when returning result sets in FETCH_ASSOC and FETCH_BOTH
1090: * modes.
1091: *
1092: * This is not intended to be used by application code,
1093: * but the method must be public so the Statement class
1094: * can invoke it.
1095: *
1096: * @param string $key
1097: * @return string
1098: */
1099: public function foldCase($key)
1100: {
1101: switch ($this->_caseFolding) {
1102: case Zend_Db::CASE_LOWER:
1103: $value = strtolower((string) $key);
1104: break;
1105: case Zend_Db::CASE_UPPER:
1106: $value = strtoupper((string) $key);
1107: break;
1108: case Zend_Db::CASE_NATURAL:
1109: default:
1110: $value = (string) $key;
1111: }
1112: return $value;
1113: }
1114:
1115: /**
1116: * called when object is getting serialized
1117: * This disconnects the DB object that cant be serialized
1118: *
1119: * @throws Zend_Db_Adapter_Exception
1120: * @return array
1121: */
1122: public function __sleep()
1123: {
1124: if ($this->_allowSerialization == false) {
1125: /** @see Zend_Db_Adapter_Exception */
1126: require_once 'Zend/Db/Adapter/Exception.php';
1127: throw new Zend_Db_Adapter_Exception(get_class($this) ." is not allowed to be serialized");
1128: }
1129: $this->_connection = false;
1130: return array_keys(array_diff_key(get_object_vars($this), array('_connection'=>false)));
1131: }
1132:
1133: /**
1134: * called when object is getting unserialized
1135: *
1136: * @return void
1137: */
1138: public function __wakeup()
1139: {
1140: if ($this->_autoReconnectOnUnserialize == true) {
1141: $this->getConnection();
1142: }
1143: }
1144:
1145: /**
1146: * Abstract Methods
1147: */
1148:
1149: /**
1150: * Returns a list of the tables in the database.
1151: *
1152: * @return array
1153: */
1154: abstract public function listTables();
1155:
1156: /**
1157: * Returns the column descriptions for a table.
1158: *
1159: * The return value is an associative array keyed by the column name,
1160: * as returned by the RDBMS.
1161: *
1162: * The value of each array element is an associative array
1163: * with the following keys:
1164: *
1165: * SCHEMA_NAME => string; name of database or schema
1166: * TABLE_NAME => string;
1167: * COLUMN_NAME => string; column name
1168: * COLUMN_POSITION => number; ordinal position of column in table
1169: * DATA_TYPE => string; SQL datatype name of column
1170: * DEFAULT => string; default expression of column, null if none
1171: * NULLABLE => boolean; true if column can have nulls
1172: * LENGTH => number; length of CHAR/VARCHAR
1173: * SCALE => number; scale of NUMERIC/DECIMAL
1174: * PRECISION => number; precision of NUMERIC/DECIMAL
1175: * UNSIGNED => boolean; unsigned property of an integer type
1176: * PRIMARY => boolean; true if column is part of the primary key
1177: * PRIMARY_POSITION => integer; position of column in primary key
1178: *
1179: * @param string $tableName
1180: * @param string $schemaName OPTIONAL
1181: * @return array
1182: */
1183: abstract public function describeTable($tableName, $schemaName = null);
1184:
1185: /**
1186: * Creates a connection to the database.
1187: *
1188: * @return void
1189: */
1190: abstract protected function _connect();
1191:
1192: /**
1193: * Test if a connection is active
1194: *
1195: * @return boolean
1196: */
1197: abstract public function isConnected();
1198:
1199: /**
1200: * Force the connection to close.
1201: *
1202: * @return void
1203: */
1204: abstract public function closeConnection();
1205:
1206: /**
1207: * Prepare a statement and return a PDOStatement-like object.
1208: *
1209: * @param string|Zend_Db_Select $sql SQL query
1210: * @return Zend_Db_Statement|PDOStatement
1211: */
1212: abstract public function prepare($sql);
1213:
1214: /**
1215: * Gets the last ID generated automatically by an IDENTITY/AUTOINCREMENT column.
1216: *
1217: * As a convention, on RDBMS brands that support sequences
1218: * (e.g. Oracle, PostgreSQL, DB2), this method forms the name of a sequence
1219: * from the arguments and returns the last id generated by that sequence.
1220: * On RDBMS brands that support IDENTITY/AUTOINCREMENT columns, this method
1221: * returns the last value generated for such a column, and the table name
1222: * argument is disregarded.
1223: *
1224: * @param string $tableName OPTIONAL Name of table.
1225: * @param string $primaryKey OPTIONAL Name of primary key column.
1226: * @return string
1227: */
1228: abstract public function lastInsertId($tableName = null, $primaryKey = null);
1229:
1230: /**
1231: * Begin a transaction.
1232: */
1233: abstract protected function _beginTransaction();
1234:
1235: /**
1236: * Commit a transaction.
1237: */
1238: abstract protected function _commit();
1239:
1240: /**
1241: * Roll-back a transaction.
1242: */
1243: abstract protected function _rollBack();
1244:
1245: /**
1246: * Set the fetch mode.
1247: *
1248: * @param integer $mode
1249: * @return void
1250: * @throws Zend_Db_Adapter_Exception
1251: */
1252: abstract public function setFetchMode($mode);
1253:
1254: /**
1255: * Adds an adapter-specific LIMIT clause to the SELECT statement.
1256: *
1257: * @param mixed $sql
1258: * @param integer $count
1259: * @param integer $offset
1260: * @return string
1261: */
1262: abstract public function limit($sql, $count, $offset = 0);
1263:
1264: /**
1265: * Check if the adapter supports real SQL parameters.
1266: *
1267: * @param string $type 'positional' or 'named'
1268: * @return bool
1269: */
1270: abstract public function supportsParameters($type);
1271:
1272: /**
1273: * Retrieve server version in PHP style
1274: *
1275: * @return string
1276: */
1277: abstract public function getServerVersion();
1278: }
1279: