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: Ids.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_Ids
 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 systables ";
 67: 
 68:         return $this->_adapter->fetchCol($sql);
 69:     }
 70: 
 71:     /**
 72:      * IDS catalog lookup for describe table
 73:      *
 74:      * @param string $tableName
 75:      * @param string $schemaName OPTIONAL
 76:      * @return array
 77:      */
 78:     public function describeTable($tableName, $schemaName = null)
 79:     {
 80:         // this is still a work in progress
 81: 
 82:         $sql= "SELECT DISTINCT t.owner, t.tabname, c.colname, c.colno, c.coltype,
 83:                d.default, c.collength, t.tabid
 84:                FROM syscolumns c
 85:                JOIN systables t ON c.tabid = t.tabid
 86:                LEFT JOIN sysdefaults d ON c.tabid = d.tabid AND c.colno = d.colno
 87:                WHERE "
 88:                 . $this->_adapter->quoteInto('UPPER(t.tabname) = UPPER(?)', $tableName);
 89:         if ($schemaName) {
 90:             $sql .= $this->_adapter->quoteInto(' AND UPPER(t.owner) = UPPER(?)', $schemaName);
 91:         }
 92:         $sql .= " ORDER BY c.colno";
 93: 
 94:         $desc = array();
 95:         $stmt = $this->_adapter->query($sql);
 96: 
 97:         $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);
 98: 
 99:         /**
100:          * The ordering of columns is defined by the query so we can map
101:          * to variables to improve readability
102:          */
103:         $tabschema      = 0;
104:         $tabname        = 1;
105:         $colname        = 2;
106:         $colno          = 3;
107:         $typename       = 4;
108:         $default        = 5;
109:         $length         = 6;
110:         $tabid          = 7;
111: 
112:         $primaryCols = null;
113: 
114:         foreach ($result as $key => $row) {
115:             $primary = false;
116:             $primaryPosition = null;
117: 
118:             if (!$primaryCols) {
119:                 $primaryCols = $this->_getPrimaryInfo($row[$tabid]);
120:             }
121: 
122:             if (array_key_exists($row[$colno], $primaryCols)) {
123:                 $primary = true;
124:                 $primaryPosition = $primaryCols[$row[$colno]];
125:             }
126: 
127:             $identity = false;
128:             if ($row[$typename] == 6 + 256 ||
129:                 $row[$typename] == 18 + 256) {
130:                 $identity = true;
131:             }
132: 
133:             $desc[$this->_adapter->foldCase($row[$colname])] = array (
134:                 'SCHEMA_NAME'       => $this->_adapter->foldCase($row[$tabschema]),
135:                 'TABLE_NAME'        => $this->_adapter->foldCase($row[$tabname]),
136:                 'COLUMN_NAME'       => $this->_adapter->foldCase($row[$colname]),
137:                 'COLUMN_POSITION'   => $row[$colno],
138:                 'DATA_TYPE'         => $this->_getDataType($row[$typename]),
139:                 'DEFAULT'           => $row[$default],
140:                 'NULLABLE'          => (bool) !($row[$typename] - 256 >= 0),
141:                 'LENGTH'            => $row[$length],
142:                 'SCALE'             => ($row[$typename] == 5 ? $row[$length]&255 : 0),
143:                 'PRECISION'         => ($row[$typename] == 5 ? (int)($row[$length]/256) : 0),
144:                 'UNSIGNED'          => false,
145:                 'PRIMARY'           => $primary,
146:                 'PRIMARY_POSITION'  => $primaryPosition,
147:                 'IDENTITY'          => $identity
148:             );
149:         }
150: 
151:         return $desc;
152:     }
153: 
154:     /**
155:      * Map number representation of a data type
156:      * to a string
157:      *
158:      * @param int $typeNo
159:      * @return string
160:      */
161:     protected function _getDataType($typeNo)
162:     {
163:         $typemap = array(
164:             0       => "CHAR",
165:             1       => "SMALLINT",
166:             2       => "INTEGER",
167:             3       => "FLOAT",
168:             4       => "SMALLFLOAT",
169:             5       => "DECIMAL",
170:             6       => "SERIAL",
171:             7       => "DATE",
172:             8       => "MONEY",
173:             9       => "NULL",
174:             10      => "DATETIME",
175:             11      => "BYTE",
176:             12      => "TEXT",
177:             13      => "VARCHAR",
178:             14      => "INTERVAL",
179:             15      => "NCHAR",
180:             16      => "NVARCHAR",
181:             17      => "INT8",
182:             18      => "SERIAL8",
183:             19      => "SET",
184:             20      => "MULTISET",
185:             21      => "LIST",
186:             22      => "Unnamed ROW",
187:             40      => "Variable-length opaque type",
188:             4118    => "Named ROW"
189:         );
190: 
191:         if ($typeNo - 256 >= 0) {
192:             $typeNo = $typeNo - 256;
193:         }
194: 
195:         return $typemap[$typeNo];
196:     }
197: 
198:     /**
199:      * Helper method to retrieve primary key column
200:      * and column location
201:      *
202:      * @param int $tabid
203:      * @return array
204:      */
205:     protected function _getPrimaryInfo($tabid)
206:     {
207:         $sql = "SELECT i.part1, i.part2, i.part3, i.part4, i.part5, i.part6,
208:                 i.part7, i.part8, i.part9, i.part10, i.part11, i.part12,
209:                 i.part13, i.part14, i.part15, i.part16
210:                 FROM sysindexes i
211:                 JOIN sysconstraints c ON c.idxname = i.idxname
212:                 WHERE i.tabid = " . $tabid . " AND c.constrtype = 'P'";
213: 
214:         $stmt = $this->_adapter->query($sql);
215:         $results = $stmt->fetchAll();
216: 
217:         $cols = array();
218: 
219:         // this should return only 1 row
220:         // unless there is no primary key,
221:         // in which case, the empty array is returned
222:         if ($results) {
223:             $row = $results[0];
224:         } else {
225:             return $cols;
226:         }
227: 
228:         $position = 0;
229:         foreach ($row as $key => $colno) {
230:             $position++;
231:             if ($colno == 0) {
232:                 return $cols;
233:             } else {
234:                 $cols[$colno] = $position;
235:             }
236:         }
237:     }
238: 
239:     /**
240:      * Adds an IDS-specific LIMIT clause to the SELECT statement.
241:      *
242:      * @param string $sql
243:      * @param integer $count
244:      * @param integer $offset OPTIONAL
245:      * @throws Zend_Db_Adapter_Exception
246:      * @return string
247:      */
248:     public function limit($sql, $count, $offset = 0)
249:     {
250:         $count = intval($count);
251:         if ($count < 0) {
252:             /** @see Zend_Db_Adapter_Exception */
253:             require_once 'Zend/Db/Adapter/Exception.php';
254:             throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");
255:         } else if ($count == 0) {
256:               $limit_sql = str_ireplace("SELECT", "SELECT * FROM (SELECT", $sql);
257:               $limit_sql .= ") WHERE 0 = 1";
258:         } else {
259:             $offset = intval($offset);
260:             if ($offset < 0) {
261:                 /** @see Zend_Db_Adapter_Exception */
262:                 require_once 'Zend/Db/Adapter/Exception.php';
263:                 throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");
264:             }
265:             if ($offset == 0) {
266:                 $limit_sql = str_ireplace("SELECT", "SELECT FIRST $count", $sql);
267:             } else {
268:                 $limit_sql = str_ireplace("SELECT", "SELECT SKIP $offset LIMIT $count", $sql);
269:             }
270:         }
271:         return $limit_sql;
272:     }
273: 
274:     /**
275:      * IDS-specific last sequence id
276:      *
277:      * @param string $sequenceName
278:      * @return integer
279:      */
280:     public function lastSequenceId($sequenceName)
281:     {
282:         $sql = 'SELECT '.$this->_adapter->quoteIdentifier($sequenceName).'.CURRVAL FROM '
283:                .'systables WHERE tabid = 1';
284:         $value = $this->_adapter->fetchOne($sql);
285:         return $value;
286:     }
287: 
288:      /**
289:      * IDS-specific sequence id value
290:      *
291:      *  @param string $sequenceName
292:      *  @return integer
293:      */
294:     public function nextSequenceId($sequenceName)
295:     {
296:         $sql = 'SELECT '.$this->_adapter->quoteIdentifier($sequenceName).'.NEXTVAL FROM '
297:                .'systables WHERE tabid = 1';
298:         $value = $this->_adapter->fetchOne($sql);
299:         return $value;
300:     }
301: }
302: