|
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 } |