1: <?php
  2: 
  3: /**
  4:  * This file is part of the "dibi" - smart database abstraction layer.
  5:  *
  6:  * Copyright (c) 2005 David Grudl (http://davidgrudl.com)
  7:  *
  8:  * For the full copyright and license information, please view
  9:  * the file license.txt that was distributed with this source code.
 10:  */
 11: 
 12: 
 13: /**
 14:  * The dibi driver interacting with databases via ODBC connections.
 15:  *
 16:  * Driver options:
 17:  *   - dsn => driver specific DSN
 18:  *   - username (or user)
 19:  *   - password (or pass)
 20:  *   - persistent (bool) => try to find a persistent link?
 21:  *   - resource (resource) => existing connection resource
 22:  *   - lazy, profiler, result, substitutes, ... => see DibiConnection options
 23:  *
 24:  * @author     David Grudl
 25:  * @package    dibi\drivers
 26:  */
 27: class DibiOdbcDriver extends DibiObject implements IDibiDriver, IDibiResultDriver, IDibiReflector
 28: {
 29:     /** @var resource  Connection resource */
 30:     private $connection;
 31: 
 32:     /** @var resource  Resultset resource */
 33:     private $resultSet;
 34: 
 35:     /** @var int|FALSE  Affected rows */
 36:     private $affectedRows = FALSE;
 37: 
 38:     /** @var int  Cursor */
 39:     private $row = 0;
 40: 
 41: 
 42: 
 43:     /**
 44:      * @throws DibiNotSupportedException
 45:      */
 46:     public function __construct()
 47:     {
 48:         if (!extension_loaded('odbc')) {
 49:             throw new DibiNotSupportedException("PHP extension 'odbc' is not loaded.");
 50:         }
 51:     }
 52: 
 53: 
 54: 
 55:     /**
 56:      * Connects to a database.
 57:      * @return void
 58:      * @throws DibiException
 59:      */
 60:     public function connect(array &$config)
 61:     {
 62:         if (isset($config['resource'])) {
 63:             $this->connection = $config['resource'];
 64:         } else {
 65:             // default values
 66:             if (!isset($config['username'])) $config['username'] = ini_get('odbc.default_user');
 67:             if (!isset($config['password'])) $config['password'] = ini_get('odbc.default_pw');
 68:             if (!isset($config['dsn'])) $config['dsn'] = ini_get('odbc.default_db');
 69: 
 70:             if (empty($config['persistent'])) {
 71:                 $this->connection = @odbc_connect($config['dsn'], $config['username'], $config['password']); // intentionally @
 72:             } else {
 73:                 $this->connection = @odbc_pconnect($config['dsn'], $config['username'], $config['password']); // intentionally @
 74:             }
 75:         }
 76: 
 77:         if (!is_resource($this->connection)) {
 78:             throw new DibiDriverException(odbc_errormsg() . ' ' . odbc_error());
 79:         }
 80:     }
 81: 
 82: 
 83: 
 84:     /**
 85:      * Disconnects from a database.
 86:      * @return void
 87:      */
 88:     public function disconnect()
 89:     {
 90:         odbc_close($this->connection);
 91:     }
 92: 
 93: 
 94: 
 95:     /**
 96:      * Executes the SQL query.
 97:      * @param  string      SQL statement.
 98:      * @return IDibiResultDriver|NULL
 99:      * @throws DibiDriverException
100:      */
101:     public function query($sql)
102:     {
103:         $this->affectedRows = FALSE;
104:         $res = @odbc_exec($this->connection, $sql); // intentionally @
105: 
106:         if ($res === FALSE) {
107:             throw new DibiDriverException(odbc_errormsg($this->connection) . ' ' . odbc_error($this->connection), 0, $sql);
108: 
109:         } elseif (is_resource($res)) {
110:             $this->affectedRows = odbc_num_rows($res);
111:             return $this->createResultDriver($res);
112:         }
113:     }
114: 
115: 
116: 
117:     /**
118:      * Gets the number of affected rows by the last INSERT, UPDATE or DELETE query.
119:      * @return int|FALSE  number of rows or FALSE on error
120:      */
121:     public function getAffectedRows()
122:     {
123:         return $this->affectedRows;
124:     }
125: 
126: 
127: 
128:     /**
129:      * Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query.
130:      * @return int|FALSE  int on success or FALSE on failure
131:      */
132:     public function getInsertId($sequence)
133:     {
134:         throw new DibiNotSupportedException('ODBC does not support autoincrementing.');
135:     }
136: 
137: 
138: 
139:     /**
140:      * Begins a transaction (if supported).
141:      * @param  string  optional savepoint name
142:      * @return void
143:      * @throws DibiDriverException
144:      */
145:     public function begin($savepoint = NULL)
146:     {
147:         if (!odbc_autocommit($this->connection, FALSE)) {
148:             throw new DibiDriverException(odbc_errormsg($this->connection) . ' ' . odbc_error($this->connection));
149:         }
150:     }
151: 
152: 
153: 
154:     /**
155:      * Commits statements in a transaction.
156:      * @param  string  optional savepoint name
157:      * @return void
158:      * @throws DibiDriverException
159:      */
160:     public function commit($savepoint = NULL)
161:     {
162:         if (!odbc_commit($this->connection)) {
163:             throw new DibiDriverException(odbc_errormsg($this->connection) . ' ' . odbc_error($this->connection));
164:         }
165:         odbc_autocommit($this->connection, TRUE);
166:     }
167: 
168: 
169: 
170:     /**
171:      * Rollback changes in a transaction.
172:      * @param  string  optional savepoint name
173:      * @return void
174:      * @throws DibiDriverException
175:      */
176:     public function rollback($savepoint = NULL)
177:     {
178:         if (!odbc_rollback($this->connection)) {
179:             throw new DibiDriverException(odbc_errormsg($this->connection) . ' ' . odbc_error($this->connection));
180:         }
181:         odbc_autocommit($this->connection, TRUE);
182:     }
183: 
184: 
185: 
186:     /**
187:      * Is in transaction?
188:      * @return bool
189:      */
190:     public function inTransaction()
191:     {
192:         return !odbc_autocommit($this->connection);
193:     }
194: 
195: 
196: 
197:     /**
198:      * Returns the connection resource.
199:      * @return mixed
200:      */
201:     public function getResource()
202:     {
203:         return $this->connection;
204:     }
205: 
206: 
207: 
208:     /**
209:      * Returns the connection reflector.
210:      * @return IDibiReflector
211:      */
212:     public function getReflector()
213:     {
214:         return $this;
215:     }
216: 
217: 
218: 
219:     /**
220:      * Result set driver factory.
221:      * @param  resource
222:      * @return IDibiResultDriver
223:      */
224:     public function createResultDriver($resource)
225:     {
226:         $res = clone $this;
227:         $res->resultSet = $resource;
228:         return $res;
229:     }
230: 
231: 
232: 
233:     /********************* SQL ****************d*g**/
234: 
235: 
236: 
237:     /**
238:      * Encodes data for use in a SQL statement.
239:      * @param  mixed     value
240:      * @param  string    type (dibi::TEXT, dibi::BOOL, ...)
241:      * @return string    encoded value
242:      * @throws InvalidArgumentException
243:      */
244:     public function escape($value, $type)
245:     {
246:         switch ($type) {
247:         case dibi::TEXT:
248:         case dibi::BINARY:
249:             return "'" . str_replace("'", "''", $value) . "'";
250: 
251:         case dibi::IDENTIFIER:
252:             return '[' . str_replace(array('[', ']'), array('[[', ']]'), $value) . ']';
253: 
254:         case dibi::BOOL:
255:             return $value ? 1 : 0;
256: 
257:         case dibi::DATE:
258:             return $value instanceof DateTime ? $value->format("#m/d/Y#") : date("#m/d/Y#", $value);
259: 
260:         case dibi::DATETIME:
261:             return $value instanceof DateTime ? $value->format("#m/d/Y H:i:s#") : date("#m/d/Y H:i:s#", $value);
262: 
263:         default:
264:             throw new InvalidArgumentException('Unsupported type.');
265:         }
266:     }
267: 
268: 
269: 
270:     /**
271:      * Encodes string for use in a LIKE statement.
272:      * @param  string
273:      * @param  int
274:      * @return string
275:      */
276:     public function escapeLike($value, $pos)
277:     {
278:         $value = strtr($value, array("'" => "''", '%' => '[%]', '_' => '[_]', '[' => '[[]'));
279:         return ($pos <= 0 ? "'%" : "'") . $value . ($pos >= 0 ? "%'" : "'");
280:     }
281: 
282: 
283: 
284:     /**
285:      * Decodes data from result set.
286:      * @param  string    value
287:      * @param  string    type (dibi::BINARY)
288:      * @return string    decoded value
289:      * @throws InvalidArgumentException
290:      */
291:     public function unescape($value, $type)
292:     {
293:         if ($type === dibi::BINARY) {
294:             return $value;
295:         }
296:         throw new InvalidArgumentException('Unsupported type.');
297:     }
298: 
299: 
300: 
301:     /**
302:      * Injects LIMIT/OFFSET to the SQL query.
303:      * @param  string &$sql  The SQL query that will be modified.
304:      * @param  int $limit
305:      * @param  int $offset
306:      * @return void
307:      */
308:     public function applyLimit(&$sql, $limit, $offset)
309:     {
310:         // offset support is missing
311:         if ($limit >= 0) {
312:             $sql = 'SELECT TOP ' . (int) $limit . ' * FROM (' . $sql . ')';
313:         }
314: 
315:         if ($offset) throw new DibiNotSupportedException('Offset is not implemented in driver odbc.');
316:     }
317: 
318: 
319: 
320:     /********************* result set ****************d*g**/
321: 
322: 
323: 
324:     /**
325:      * Automatically frees the resources allocated for this result set.
326:      * @return void
327:      */
328:     public function __destruct()
329:     {
330:         $this->resultSet && @$this->free();
331:     }
332: 
333: 
334: 
335:     /**
336:      * Returns the number of rows in a result set.
337:      * @return int
338:      */
339:     public function getRowCount()
340:     {
341:         // will return -1 with many drivers :-(
342:         return odbc_num_rows($this->resultSet);
343:     }
344: 
345: 
346: 
347:     /**
348:      * Fetches the row at current position and moves the internal cursor to the next position.
349:      * @param  bool     TRUE for associative array, FALSE for numeric
350:      * @return array    array on success, nonarray if no next record
351:      */
352:     public function fetch($assoc)
353:     {
354:         if ($assoc) {
355:             return odbc_fetch_array($this->resultSet, ++$this->row);
356:         } else {
357:             $set = $this->resultSet;
358:             if (!odbc_fetch_row($set, ++$this->row)) return FALSE;
359:             $count = odbc_num_fields($set);
360:             $cols = array();
361:             for ($i = 1; $i <= $count; $i++) $cols[] = odbc_result($set, $i);
362:             return $cols;
363:         }
364:     }
365: 
366: 
367: 
368:     /**
369:      * Moves cursor position without fetching row.
370:      * @param  int      the 0-based cursor pos to seek to
371:      * @return boolean  TRUE on success, FALSE if unable to seek to specified record
372:      */
373:     public function seek($row)
374:     {
375:         $this->row = $row;
376:         return TRUE;
377:     }
378: 
379: 
380: 
381:     /**
382:      * Frees the resources allocated for this result set.
383:      * @return void
384:      */
385:     public function free()
386:     {
387:         odbc_free_result($this->resultSet);
388:         $this->resultSet = NULL;
389:     }
390: 
391: 
392: 
393:     /**
394:      * Returns metadata for all columns in a result set.
395:      * @return array
396:      */
397:     public function getResultColumns()
398:     {
399:         $count = odbc_num_fields($this->resultSet);
400:         $columns = array();
401:         for ($i = 1; $i <= $count; $i++) {
402:             $columns[] = array(
403:                 'name'      => odbc_field_name($this->resultSet, $i),
404:                 'table'     => NULL,
405:                 'fullname'  => odbc_field_name($this->resultSet, $i),
406:                 'nativetype'=> odbc_field_type($this->resultSet, $i),
407:             );
408:         }
409:         return $columns;
410:     }
411: 
412: 
413: 
414:     /**
415:      * Returns the result set resource.
416:      * @return mixed
417:      */
418:     public function getResultResource()
419:     {
420:         return $this->resultSet;
421:     }
422: 
423: 
424: 
425:     /********************* IDibiReflector ****************d*g**/
426: 
427: 
428: 
429:     /**
430:      * Returns list of tables.
431:      * @return array
432:      */
433:     public function getTables()
434:     {
435:         $res = odbc_tables($this->connection);
436:         $tables = array();
437:         while ($row = odbc_fetch_array($res)) {
438:             if ($row['TABLE_TYPE'] === 'TABLE' || $row['TABLE_TYPE'] === 'VIEW') {
439:                 $tables[] = array(
440:                     'name' => $row['TABLE_NAME'],
441:                     'view' => $row['TABLE_TYPE'] === 'VIEW',
442:                 );
443:             }
444:         }
445:         odbc_free_result($res);
446:         return $tables;
447:     }
448: 
449: 
450: 
451:     /**
452:      * Returns metadata for all columns in a table.
453:      * @param  string
454:      * @return array
455:      */
456:     public function getColumns($table)
457:     {
458:         $res = odbc_columns($this->connection);
459:         $columns = array();
460:         while ($row = odbc_fetch_array($res)) {
461:             if ($row['TABLE_NAME'] === $table) {
462:                 $columns[] = array(
463:                     'name' => $row['COLUMN_NAME'],
464:                     'table' => $table,
465:                     'nativetype' => $row['TYPE_NAME'],
466:                     'size' => $row['COLUMN_SIZE'],
467:                     'nullable' => (bool) $row['NULLABLE'],
468:                     'default' => $row['COLUMN_DEF'],
469:                 );
470:             }
471:         }
472:         odbc_free_result($res);
473:         return $columns;
474:     }
475: 
476: 
477: 
478:     /**
479:      * Returns metadata for all indexes in a table.
480:      * @param  string
481:      * @return array
482:      */
483:     public function getIndexes($table)
484:     {
485:         throw new DibiNotImplementedException;
486:     }
487: 
488: 
489: 
490:     /**
491:      * Returns metadata for all foreign keys in a table.
492:      * @param  string
493:      * @return array
494:      */
495:     public function getForeignKeys($table)
496:     {
497:         throw new DibiNotImplementedException;
498:     }
499: 
500: }
501: