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: Db2.php 20096 2010-01-06 02:05:09Z bkarwin $
 21:  */
 22: 
 23: 
 24: /** @see Zend_Db_Adapter_Pdo_Ibm */
 25: require_once 'Zend/Db/Adapter/Pdo/Ibm.php';
 26: 
 27: /** @see Zend_Db_Statement_Pdo_Ibm */
 28: require_once 'Zend/Db/Statement/Pdo/Ibm.php';
 29: 
 30: 
 31: /**
 32:  * @category   Zend
 33:  * @package    Zend_Db
 34:  * @subpackage Adapter
 35:  * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
 36:  * @license    http://framework.zend.com/license/new-bsd     New BSD License
 37:  */
 38: class Zend_Db_Adapter_Pdo_Ibm_Db2
 39: {
 40:     /**
 41:      * @var Zend_Db_Adapter_Abstract
 42:      */
 43:     protected $_adapter = null;
 44: 
 45:     /**
 46:      * Construct the data server class.
 47:      *
 48:      * It will be used to generate non-generic SQL
 49:      * for a particular data server
 50:      *
 51:      * @param Zend_Db_Adapter_Abstract $adapter
 52:      */
 53:     public function __construct($adapter)
 54:     {
 55:         $this->_adapter = $adapter;
 56:     }
 57: 
 58:     /**
 59:      * Returns a list of the tables in the database.
 60:      *
 61:      * @return array
 62:      */
 63:     public function listTables()
 64:     {
 65:         $sql = "SELECT tabname "
 66:         . "FROM SYSCAT.TABLES ";
 67:         return $this->_adapter->fetchCol($sql);
 68:     }
 69: 
 70:     /**
 71:      * DB2 catalog lookup for describe table
 72:      *
 73:      * @param string $tableName
 74:      * @param string $schemaName OPTIONAL
 75:      * @return array
 76:      */
 77:     public function describeTable($tableName, $schemaName = null)
 78:     {
 79:         $sql = "SELECT DISTINCT c.tabschema, c.tabname, c.colname, c.colno,
 80:                 c.typename, c.default, c.nulls, c.length, c.scale,
 81:                 c.identity, tc.type AS tabconsttype, k.colseq
 82:                 FROM syscat.columns c
 83:                 LEFT JOIN (syscat.keycoluse k JOIN syscat.tabconst tc
 84:                  ON (k.tabschema = tc.tabschema
 85:                    AND k.tabname = tc.tabname
 86:                    AND tc.type = 'P'))
 87:                  ON (c.tabschema = k.tabschema
 88:                  AND c.tabname = k.tabname
 89:                  AND c.colname = k.colname)
 90:             WHERE "
 91:             . $this->_adapter->quoteInto('UPPER(c.tabname) = UPPER(?)', $tableName);
 92:         if ($schemaName) {
 93:             $sql .= $this->_adapter->quoteInto(' AND UPPER(c.tabschema) = UPPER(?)', $schemaName);
 94:         }
 95:         $sql .= " ORDER BY c.colno";
 96: 
 97:         $desc = array();
 98:         $stmt = $this->_adapter->query($sql);
 99: 
100:         /**
101:          * To avoid case issues, fetch using FETCH_NUM
102:          */
103:         $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);
104: 
105:         /**
106:          * The ordering of columns is defined by the query so we can map
107:          * to variables to improve readability
108:          */
109:         $tabschema      = 0;
110:         $tabname        = 1;
111:         $colname        = 2;
112:         $colno          = 3;
113:         $typename       = 4;
114:         $default        = 5;
115:         $nulls          = 6;
116:         $length         = 7;
117:         $scale          = 8;
118:         $identityCol    = 9;
119:         $tabconstype    = 10;
120:         $colseq         = 11;
121: 
122:         foreach ($result as $key => $row) {
123:             list ($primary, $primaryPosition, $identity) = array(false, null, false);
124:             if ($row[$tabconstype] == 'P') {
125:                 $primary = true;
126:                 $primaryPosition = $row[$colseq];
127:             }
128:             /**
129:              * In IBM DB2, an column can be IDENTITY
130:              * even if it is not part of the PRIMARY KEY.
131:              */
132:             if ($row[$identityCol] == 'Y') {
133:                 $identity = true;
134:             }
135: 
136:             $desc[$this->_adapter->foldCase($row[$colname])] = array(
137:             'SCHEMA_NAME'      => $this->_adapter->foldCase($row[$tabschema]),
138:             'TABLE_NAME'       => $this->_adapter->foldCase($row[$tabname]),
139:             'COLUMN_NAME'      => $this->_adapter->foldCase($row[$colname]),
140:             'COLUMN_POSITION'  => $row[$colno]+1,
141:             'DATA_TYPE'        => $row[$typename],
142:             'DEFAULT'          => $row[$default],
143:             'NULLABLE'         => (bool) ($row[$nulls] == 'Y'),
144:             'LENGTH'           => $row[$length],
145:             'SCALE'            => $row[$scale],
146:             'PRECISION'        => ($row[$typename] == 'DECIMAL' ? $row[$length] : 0),
147:             'UNSIGNED'         => false,
148:             'PRIMARY'          => $primary,
149:             'PRIMARY_POSITION' => $primaryPosition,
150:             'IDENTITY'         => $identity
151:             );
152:         }
153: 
154:         return $desc;
155:     }
156: 
157:     /**
158:      * Adds a DB2-specific LIMIT clause to the SELECT statement.
159:      *
160:      * @param string $sql
161:      * @param integer $count
162:      * @param integer $offset OPTIONAL
163:      * @throws Zend_Db_Adapter_Exception
164:      * @return string
165:      */
166:     public function limit($sql, $count, $offset = 0)
167:     {
168:         $count = intval($count);
169:         if ($count < 0) {
170:             /** @see Zend_Db_Adapter_Exception */
171:             require_once 'Zend/Db/Adapter/Exception.php';
172:             throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");
173:         } else {
174:             $offset = intval($offset);
175:             if ($offset < 0) {
176:                 /** @see Zend_Db_Adapter_Exception */
177:                 require_once 'Zend/Db/Adapter/Exception.php';
178:                 throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");
179:             }
180: 
181:             if ($offset == 0 && $count > 0) {
182:                 $limit_sql = $sql . " FETCH FIRST $count ROWS ONLY";
183:                 return $limit_sql;
184:             }
185:             /**
186:              * DB2 does not implement the LIMIT clause as some RDBMS do.
187:              * We have to simulate it with subqueries and ROWNUM.
188:              * Unfortunately because we use the column wildcard "*",
189:              * this puts an extra column into the query result set.
190:              */
191:             $limit_sql = "SELECT z2.*
192:               FROM (
193:                   SELECT ROW_NUMBER() OVER() AS \"ZEND_DB_ROWNUM\", z1.*
194:                   FROM (
195:                       " . $sql . "
196:                   ) z1
197:               ) z2
198:               WHERE z2.zend_db_rownum BETWEEN " . ($offset+1) . " AND " . ($offset+$count);
199:         }
200:         return $limit_sql;
201:     }
202: 
203:     /**
204:      * DB2-specific last sequence id
205:      *
206:      * @param string $sequenceName
207:      * @return integer
208:      */
209:     public function lastSequenceId($sequenceName)
210:     {
211:         $sql = 'SELECT PREVVAL FOR '.$this->_adapter->quoteIdentifier($sequenceName).' AS VAL FROM SYSIBM.SYSDUMMY1';
212:         $value = $this->_adapter->fetchOne($sql);
213:         return $value;
214:     }
215: 
216:     /**
217:      * DB2-specific sequence id value
218:      *
219:      *  @param string $sequenceName
220:      *  @return integer
221:      */
222:     public function nextSequenceId($sequenceName)
223:     {
224:         $sql = 'SELECT NEXTVAL FOR '.$this->_adapter->quoteIdentifier($sequenceName).' AS VAL FROM SYSIBM.SYSDUMMY1';
225:         $value = $this->_adapter->fetchOne($sql);
226:         return $value;
227:     }
228: }
229: