1: <?php
  2: /* SVN FILE: $Id$ */
  3: /**
  4:  * Firebird/Interbase layer for DBO
  5:  *
  6:  * Long description for file
  7:  *
  8:  * PHP versions 4 and 5
  9:  *
 10:  * CakePHP(tm) : Rapid Development Framework (http://cakephp.org)
 11:  * Copyright 2005-2012, Cake Software Foundation, Inc. (http://cakefoundation.org)
 12:  *
 13:  * Licensed under The MIT License
 14:  * Redistributions of files must retain the above copyright notice.
 15:  *
 16:  * @copyright     Copyright 2005-2012, Cake Software Foundation, Inc. (http://cakefoundation.org)
 17:  * @link          http://cakephp.org CakePHP(tm) Project
 18:  * @package       cake
 19:  * @subpackage    cake.cake.libs.model.dbo
 20:  * @since         CakePHP(tm) v 1.2.0.5152
 21:  * @version       $Revision$
 22:  * @modifiedby    $LastChangedBy$
 23:  * @lastmodified  $Date$
 24:  * @license       http://www.opensource.org/licenses/mit-license.php The MIT License
 25:  */
 26: /**
 27:  * Short description for class.
 28:  *
 29:  * Long description for class
 30:  *
 31:  * @package       cake
 32:  * @subpackage    cake.cake.libs.model.dbo
 33:  */
 34: class DboFirebird extends DboSource {
 35: /**
 36:  * Enter description here...
 37:  *
 38:  * @var unknown_type
 39:  */
 40:     var $description = "Firebird/Interbase DBO Driver";
 41: /**
 42:  * Saves the original table name
 43:  *
 44:  * @var unknown_type
 45:  */
 46:     var $modeltmp = array();
 47: /**
 48:  * Enter description here...
 49:  *
 50:  * @var unknown_type
 51:  */
 52:     var $startQuote = "\'";
 53: /**
 54:  * Enter description here...
 55:  *
 56:  * @var unknown_type
 57:  */
 58:     var $endQuote = "\'";
 59: /**
 60:  * Enter description here...
 61:  *
 62:  * @var unknown_type
 63:  */
 64:     var $alias = ' ';
 65: /**
 66:  * Enter description here...
 67:  *
 68:  * @var unknown_type
 69:  */
 70:     var $goofyLimit = true;
 71: /**
 72:  * Creates a map between field aliases and numeric indexes.
 73:  *
 74:  * @var array
 75:  */
 76:     var $__fieldMappings = array();
 77: /**
 78:  * Base configuration settings for Firebird driver
 79:  *
 80:  * @var array
 81:  */
 82:     var $_baseConfig = array(
 83:         'persistent' => true,
 84:         'host' => 'localhost',
 85:         'login' => 'SYSDBA',
 86:         'password' => 'masterkey',
 87:         'database' => 'c:\\CAKE.FDB',
 88:         'port' => '3050',
 89:         'connect' => 'ibase_connect'
 90:     );
 91: /**
 92:  * Firebird column definition
 93:  *
 94:  * @var array
 95:  */
 96:     var $columns = array(
 97:         'primary_key' => array('name' => 'IDENTITY (1, 1) NOT NULL'),
 98:         'string'    => array('name'  => 'varchar', 'limit' => '255'),
 99:         'text'      => array('name' => 'BLOB SUB_TYPE 1 SEGMENT SIZE 100 CHARACTER SET NONE'),
100:         'integer'   => array('name' => 'integer'),
101:         'float'     => array('name' => 'float', 'formatter' => 'floatval'),
102:         'datetime'  => array('name' => 'timestamp', 'format'    => 'd.m.Y H:i:s', 'formatter' => 'date'),
103:         'timestamp' => array('name' => 'timestamp', 'format'     => 'd.m.Y H:i:s', 'formatter' => 'date'),
104:         'time'      => array('name' => 'time', 'format'    => 'H:i:s', 'formatter' => 'date'),
105:         'date'      => array('name' => 'date', 'format'    => 'd.m.Y', 'formatter' => 'date'),
106:         'binary'    => array('name' => 'blob'),
107:         'boolean'   => array('name' => 'smallint')
108:     );
109: /**
110:  * Firebird Transaction commands.
111:  *
112:  * @var array
113:  **/
114:     var $_commands = array(
115:         'begin'    => 'SET TRANSACTION',
116:         'commit'   => 'COMMIT',
117:         'rollback' => 'ROLLBACK'
118:     );
119: /**
120:  * Connects to the database using options in the given configuration array.
121:  *
122:  * @return boolean True if the database could be connected, else false
123:  */
124:     function connect() {
125:         $config = $this->config;
126:         $connect = $config['connect'];
127: 
128:         $this->connected = false;
129: 
130:         $this->connection = $connect($config['host'] . ':' . $config['database'], $config['login'], $config['password']);
131:         $this->connected = true;
132:     }
133: /**
134:  * Check that the interbase extension is loaded
135:  *
136:  * @return boolean
137:  **/
138:     function enabled() {
139:         return extension_loaded('interbase');
140:     }
141: /**
142:  * Disconnects from database.
143:  *
144:  * @return boolean True if the database could be disconnected, else false
145:  */
146:     function disconnect() {
147:         $this->connected = false;
148:         return @ibase_close($this->connection);
149:     }
150: /**
151:  * Executes given SQL statement.
152:  *
153:  * @param string $sql SQL statement
154:  * @return resource Result resource identifier
155:  * @access protected
156:  */
157:     function _execute($sql) {
158:         return @ibase_query($this->connection,  $sql);
159:     }
160: /**
161:  * Returns a row from given resultset as an array .
162:  *
163:  * @return array The fetched row as an array
164:  */
165:     function fetchRow() {
166:         if ($this->hasResult()) {
167:             $this->resultSet($this->_result);
168:             $resultRow = $this->fetchResult();
169:             return $resultRow;
170:         } else {
171:             return null;
172:         }
173:     }
174: /**
175:  * Returns an array of sources (tables) in the database.
176:  *
177:  * @return array Array of tablenames in the database
178:  */
179:     function listSources() {
180:         $cache = parent::listSources();
181: 
182:         if ($cache != null) {
183:             return $cache;
184:         }
185:         $sql = "select RDB" . "$" . "RELATION_NAME as name
186:                 FROM RDB" ."$" . "RELATIONS
187:                 Where RDB" . "$" . "SYSTEM_FLAG =0";
188: 
189:         $result = @ibase_query($this->connection,$sql);
190:         $tables = array();
191:         while ($row = ibase_fetch_row ($result)) {
192:             $tables[] = strtolower(trim($row[0]));
193:         }
194:         parent::listSources($tables);
195:         return $tables;
196:     }
197: /**
198:  * Returns an array of the fields in given table name.
199:  *
200:  * @param Model $model Model object to describe
201:  * @return array Fields in table. Keys are name and type
202:  */
203:     function describe(&$model) {
204:         $this->modeltmp[$model->table] = $model->alias;
205:         $cache = parent::describe($model);
206: 
207:         if ($cache != null) {
208:             return $cache;
209:         }
210:         $fields = false;
211:         $sql = "SELECT * FROM " . $this->fullTableName($model, false);
212:         $rs = ibase_query($sql);
213:         $coln = ibase_num_fields($rs);
214:         $fields = false;
215: 
216:         for ($i = 0; $i < $coln; $i++) {
217:             $col_info = ibase_field_info($rs, $i);
218:             $fields[strtolower($col_info['name'])] = array(
219:                     'type' => $this->column($col_info['type']),
220:                     'null' => '',
221:                     'length' => $col_info['length']
222:                 );
223:         }
224:         $this->__cacheDescription($this->fullTableName($model, false), $fields);
225:         return $fields;
226:     }
227: /**
228:  * Returns a quoted name of $data for use in an SQL statement.
229:  *
230:  * @param string $data Name (table.field) to be prepared for use in an SQL statement
231:  * @return string Quoted for Firebird
232:  */
233:     function name($data) {
234:         if ($data == '*') {
235:                 return '*';
236:         }
237:         $pos = strpos($data, '"');
238: 
239:         if ($pos === false) {
240:             if (!strpos($data, ".")) {
241:                 $data = '"' . strtoupper($data) . '"';
242:             } else {
243:                 $build = explode('.', $data);
244:                 $data = '"' . strtoupper($build[0]) . '"."' . strtoupper($build[1]) . '"';
245:             }
246:         }
247:         return $data;
248:     }
249: /**
250:  * Returns a quoted and escaped string of $data for use in an SQL statement.
251:  *
252:  * @param string $data String to be prepared for use in an SQL statement
253:  * @param string $column The column into which this data will be inserted
254:  * @param boolean $safe Whether or not numeric data should be handled automagically if no column data is provided
255:  * @return string Quoted and escaped data
256:  */
257:     function value($data, $column = null, $safe = false) {
258:         $parent = parent::value($data, $column, $safe);
259: 
260:         if ($parent != null) {
261:             return $parent;
262:         }
263:         if ($data === null) {
264:             return 'NULL';
265:         }
266:         if ($data === '') {
267:             return "''";
268:         }
269: 
270:         switch($column) {
271:             case 'boolean':
272:                 $data = $this->boolean((bool)$data);
273:             break;
274:             default:
275:                 if (get_magic_quotes_gpc()) {
276:                     $data = stripslashes(str_replace("'", "''", $data));
277:                 } else {
278:                     $data = str_replace("'", "''", $data);
279:                 }
280:             break;
281:         }
282:         return "'" . $data . "'";
283:     }
284: /**
285:  * Removes Identity (primary key) column from update data before returning to parent
286:  *
287:  * @param Model $model
288:  * @param array $fields
289:  * @param array $values
290:  * @return array
291:  */
292:     function update(&$model, $fields = array(), $values = array()) {
293:         foreach ($fields as $i => $field) {
294:             if ($field == $model->primaryKey) {
295:                 unset ($fields[$i]);
296:                 unset ($values[$i]);
297:                 break;
298:             }
299:         }
300:         return parent::update($model, $fields, $values);
301:     }
302: /**
303:  * Returns a formatted error message from previous database operation.
304:  *
305:  * @return string Error message with error number
306:  */
307:     function lastError() {
308:         $error = ibase_errmsg();
309: 
310:         if ($error !== false) {
311:             return $error;
312:         }
313:         return null;
314:     }
315: /**
316:  * Returns number of affected rows in previous database operation. If no previous operation exists,
317:  * this returns false.
318:  *
319:  * @return integer Number of affected rows
320:  */
321:     function lastAffected() {
322:         if ($this->_result) {
323:             return ibase_affected_rows($this->connection);
324:         }
325:         return null;
326:     }
327: /**
328:  * Returns number of rows in previous resultset. If no previous resultset exists,
329:  * this returns false.
330:  *
331:  * @return integer Number of rows in resultset
332:  */
333:     function lastNumRows() {
334:         return $this->_result? /*ibase_affected_rows($this->_result)*/ 1: false;
335:     }
336: /**
337:  * Returns the ID generated from the previous INSERT operation.
338:  *
339:  * @param unknown_type $source
340:  * @return in
341:  */
342:     function lastInsertId($source = null, $field = 'id') {
343:         $query = "SELECT RDB\$TRIGGER_SOURCE
344:         FROM RDB\$TRIGGERS WHERE RDB\$RELATION_NAME = '".  strtoupper($source) .  "' AND
345:         RDB\$SYSTEM_FLAG IS NULL AND  RDB\$TRIGGER_TYPE = 1 ";
346: 
347:         $result = @ibase_query($this->connection,$query);
348:         $generator = "";
349: 
350:         while ($row = ibase_fetch_row($result, IBASE_TEXT)) {
351:             if (strpos($row[0], "NEW." . strtoupper($field))) {
352:                 $pos = strpos($row[0], "GEN_ID(");
353: 
354:                 if ($pos > 0) {
355:                     $pos2 = strpos($row[0],",",$pos + 7);
356: 
357:                     if ($pos2 > 0) {
358:                         $generator = substr($row[0], $pos +7, $pos2 - $pos- 7);
359:                     }
360:                 }
361:                 break;
362:             }
363:         }
364: 
365:         if (!empty($generator)) {
366:             $sql = "SELECT GEN_ID(". $generator  . ",0) AS maxi FROM RDB" . "$" . "DATABASE";
367:             $res = $this->rawQuery($sql);
368:             $data = $this->fetchRow($res);
369:             return $data['maxi'];
370:         } else {
371:             return false;
372:         }
373:     }
374: /**
375:  * Returns a limit statement in the correct format for the particular database.
376:  *
377:  * @param integer $limit Limit of results returned
378:  * @param integer $offset Offset from which to start results
379:  * @return string SQL limit/offset statement
380:  */
381:     function limit($limit, $offset = null) {
382:         if ($limit) {
383:             $rt = '';
384: 
385:             if (!strpos(strtolower($limit), 'top') || strpos(strtolower($limit), 'top') === 0) {
386:                 $rt = ' FIRST';
387:             }
388:             $rt .= ' ' . $limit;
389: 
390:             if (is_int($offset) && $offset > 0) {
391:                 $rt .= ' SKIP ' . $offset;
392:             }
393:             return $rt;
394:         }
395:         return null;
396:     }
397: /**
398:  * Converts database-layer column types to basic types
399:  *
400:  * @param string $real Real database-layer column type (i.e. "varchar(255)")
401:  * @return string Abstract column type (i.e. "string")
402:  */
403:     function column($real) {
404:         if (is_array($real)) {
405:             $col = $real['name'];
406: 
407:             if (isset($real['limit'])) {
408:                 $col .= '(' . $real['limit'] . ')';
409:             }
410:             return $col;
411:         }
412: 
413:         $col = str_replace(')', '', $real);
414:         $limit = null;
415:         if (strpos($col, '(') !== false) {
416:             list($col, $limit) = explode('(', $col);
417:         }
418: 
419:         if (in_array($col, array('DATE', 'TIME'))) {
420:             return strtolower($col);
421:         }
422:         if ($col == 'TIMESTAMP') {
423:             return 'datetime';
424:         }
425:         if ($col == 'SMALLINT') {
426:             return 'boolean';
427:         }
428:         if (strpos($col, 'int') !== false || $col == 'numeric' || $col == 'INTEGER') {
429:             return 'integer';
430:         }
431:         if (strpos($col, 'char') !== false) {
432:             return 'string';
433:         }
434:         if (strpos($col, 'text') !== false) {
435:             return 'text';
436:         }
437:         if (strpos($col, 'VARCHAR') !== false) {
438:             return 'string';
439:         }
440:         if (strpos($col, 'BLOB') !== false) {
441:             return 'text';
442:         }
443:         if (in_array($col, array('FLOAT', 'NUMERIC', 'DECIMAL'))) {
444:             return 'float';
445:         }
446:         return 'text';
447:     }
448: /**
449:  * Enter description here...
450:  *
451:  * @param unknown_type $results
452:  */
453:     function resultSet(&$results) {
454:         $this->results =& $results;
455:         $this->map = array();
456:         $num_fields = ibase_num_fields($results);
457:         $index = 0;
458:         $j = 0;
459: 
460:         while ($j < $num_fields) {
461:             $column = ibase_field_info($results, $j);
462:             if (!empty($column[2])) {
463:                 $this->map[$index++] = array(ucfirst(strtolower($this->modeltmp[strtolower($column[2])])), strtolower($column[1]));
464:             } else {
465:                 $this->map[$index++] = array(0, strtolower($column[1]));
466:             }
467:             $j++;
468:         }
469:     }
470: /**
471:  * Builds final SQL statement
472:  *
473:  * @param string $type Query type
474:  * @param array $data Query data
475:  * @return string
476:  */
477:     function renderStatement($type, $data) {
478:         extract($data);
479: 
480:         if (strtolower($type) == 'select') {
481:             if (preg_match('/offset\s+([0-9]+)/i', $limit, $offset)) {
482:                 $limit = preg_replace('/\s*offset.*$/i', '', $limit);
483:                 preg_match('/top\s+([0-9]+)/i', $limit, $limitVal);
484:                 $offset = intval($offset[1]) + intval($limitVal[1]);
485:                 $rOrder = $this->__switchSort($order);
486:                 list($order2, $rOrder) = array($this->__mapFields($order), $this->__mapFields($rOrder));
487:                 return "SELECT * FROM (SELECT {$limit} * FROM (SELECT TOP {$offset} {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$order}) AS Set1 {$rOrder}) AS Set2 {$order2}";
488:             } else {
489:                 return "SELECT {$limit} {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$order}";
490:             }
491:         } else {
492:             return parent::renderStatement($type, $data);
493:         }
494:     }
495: /**
496:  * Fetches the next row from the current result set
497:  *
498:  * @return unknown
499:  */
500:     function fetchResult() {
501:         if ($row = ibase_fetch_row($this->results, IBASE_TEXT)) {
502:             $resultRow = array();
503:             $i = 0;
504: 
505:             foreach ($row as $index => $field) {
506:                 list($table, $column) = $this->map[$index];
507: 
508:                 if (trim($table) == "") {
509:                     $resultRow[0][$column] = $row[$index];
510:                 } else {
511:                     $resultRow[$table][$column] = $row[$index];
512:                     $i++;
513:                 }
514:             }
515:             return $resultRow;
516:         } else {
517:             return false;
518:         }
519:     }
520: }
521: ?>