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 for Oracle database.
 15:  *
 16:  * Driver options:
 17:  *   - database => the name of the local Oracle instance or the name of the entry in tnsnames.ora
 18:  *   - username (or user)
 19:  *   - password (or pass)
 20:  *   - charset => character encoding to set
 21:  *   - formatDate => how to format date in SQL (@see date)
 22:  *   - formatDateTime => how to format datetime in SQL (@see date)
 23:  *   - resource (resource) => existing connection resource
 24:  *   - lazy, profiler, result, substitutes, ... => see DibiConnection options
 25:  *
 26:  * @author     David Grudl
 27:  * @package    dibi\drivers
 28:  */
 29: class DibiOracleDriver extends DibiObject implements IDibiDriver, IDibiResultDriver, IDibiReflector
 30: {
 31:     /** @var resource  Connection resource */
 32:     private $connection;
 33: 
 34:     /** @var resource  Resultset resource */
 35:     private $resultSet;
 36: 
 37:     /** @var bool */
 38:     private $autocommit = TRUE;
 39: 
 40:     /** @var string  Date and datetime format */
 41:     private $fmtDate, $fmtDateTime;
 42: 
 43: 
 44: 
 45:     /**
 46:      * @throws DibiNotSupportedException
 47:      */
 48:     public function __construct()
 49:     {
 50:         if (!extension_loaded('oci8')) {
 51:             throw new DibiNotSupportedException("PHP extension 'oci8' is not loaded.");
 52:         }
 53:     }
 54: 
 55: 
 56: 
 57:     /**
 58:      * Connects to a database.
 59:      * @return void
 60:      * @throws DibiException
 61:      */
 62:     public function connect(array &$config)
 63:     {
 64:         $foo = & $config['charset'];
 65:         $this->fmtDate = isset($config['formatDate']) ? $config['formatDate'] : 'U';
 66:         $this->fmtDateTime = isset($config['formatDateTime']) ? $config['formatDateTime'] : 'U';
 67: 
 68:         if (isset($config['resource'])) {
 69:             $this->connection = $config['resource'];
 70:         } else {
 71:             $this->connection = @oci_new_connect($config['username'], $config['password'], $config['database'], $config['charset']); // intentionally @
 72:         }
 73: 
 74:         if (!$this->connection) {
 75:             $err = oci_error();
 76:             throw new DibiDriverException($err['message'], $err['code']);
 77:         }
 78:     }
 79: 
 80: 
 81: 
 82:     /**
 83:      * Disconnects from a database.
 84:      * @return void
 85:      */
 86:     public function disconnect()
 87:     {
 88:         oci_close($this->connection);
 89:     }
 90: 
 91: 
 92: 
 93:     /**
 94:      * Executes the SQL query.
 95:      * @param  string      SQL statement.
 96:      * @return IDibiResultDriver|NULL
 97:      * @throws DibiDriverException
 98:      */
 99:     public function query($sql)
100:     {
101:         $res = oci_parse($this->connection, $sql);
102:         if ($res) {
103:             oci_execute($res, $this->autocommit ? OCI_COMMIT_ON_SUCCESS : OCI_DEFAULT);
104:             $err = oci_error($res);
105:             if ($err) {
106:                 throw new DibiDriverException($err['message'], $err['code'], $sql);
107: 
108:             } elseif (is_resource($res)) {
109:                 return $this->createResultDriver($res);
110:             }
111:         } else {
112:             $err = oci_error($this->connection);
113:             throw new DibiDriverException($err['message'], $err['code'], $sql);
114:         }
115:     }
116: 
117: 
118: 
119:     /**
120:      * Gets the number of affected rows by the last INSERT, UPDATE or DELETE query.
121:      * @return int|FALSE  number of rows or FALSE on error
122:      */
123:     public function getAffectedRows()
124:     {
125:         throw new DibiNotImplementedException;
126:     }
127: 
128: 
129: 
130:     /**
131:      * Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query.
132:      * @return int|FALSE  int on success or FALSE on failure
133:      */
134:     public function getInsertId($sequence)
135:     {
136:         $row = $this->query("SELECT $sequence.CURRVAL AS ID FROM DUAL")->fetch(TRUE);
137:         return isset($row['ID']) ? (int) $row['ID'] : FALSE;
138:     }
139: 
140: 
141: 
142:     /**
143:      * Begins a transaction (if supported).
144:      * @param  string  optional savepoint name
145:      * @return void
146:      */
147:     public function begin($savepoint = NULL)
148:     {
149:         $this->autocommit = FALSE;
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 (!oci_commit($this->connection)) {
163:             $err = oci_error($this->connection);
164:             throw new DibiDriverException($err['message'], $err['code']);
165:         }
166:         $this->autocommit = TRUE;
167:     }
168: 
169: 
170: 
171:     /**
172:      * Rollback changes in a transaction.
173:      * @param  string  optional savepoint name
174:      * @return void
175:      * @throws DibiDriverException
176:      */
177:     public function rollback($savepoint = NULL)
178:     {
179:         if (!oci_rollback($this->connection)) {
180:             $err = oci_error($this->connection);
181:             throw new DibiDriverException($err['message'], $err['code']);
182:         }
183:         $this->autocommit = TRUE;
184:     }
185: 
186: 
187: 
188:     /**
189:      * Returns the connection resource.
190:      * @return mixed
191:      */
192:     public function getResource()
193:     {
194:         return $this->connection;
195:     }
196: 
197: 
198: 
199:     /**
200:      * Returns the connection reflector.
201:      * @return IDibiReflector
202:      */
203:     public function getReflector()
204:     {
205:         return $this;
206:     }
207: 
208: 
209: 
210:     /**
211:      * Result set driver factory.
212:      * @param  resource
213:      * @return IDibiResultDriver
214:      */
215:     public function createResultDriver($resource)
216:     {
217:         $res = clone $this;
218:         $res->resultSet = $resource;
219:         return $res;
220:     }
221: 
222: 
223: 
224:     /********************* SQL ****************d*g**/
225: 
226: 
227: 
228:     /**
229:      * Encodes data for use in a SQL statement.
230:      * @param  mixed     value
231:      * @param  string    type (dibi::TEXT, dibi::BOOL, ...)
232:      * @return string    encoded value
233:      * @throws InvalidArgumentException
234:      */
235:     public function escape($value, $type)
236:     {
237:         switch ($type) {
238:         case dibi::TEXT:
239:         case dibi::BINARY:
240:             return "'" . str_replace("'", "''", $value) . "'"; // TODO: not tested
241: 
242:         case dibi::IDENTIFIER:
243:             // @see http://download.oracle.com/docs/cd/B10500_01/server.920/a96540/sql_elements9a.htm
244:             return '"' . str_replace('"', '""', $value) . '"';
245: 
246:         case dibi::BOOL:
247:             return $value ? 1 : 0;
248: 
249:         case dibi::DATE:
250:             return $value instanceof DateTime ? $value->format($this->fmtDate) : date($this->fmtDate, $value);
251: 
252:         case dibi::DATETIME:
253:             return $value instanceof DateTime ? $value->format($this->fmtDateTime) : date($this->fmtDateTime, $value);
254: 
255:         default:
256:             throw new InvalidArgumentException('Unsupported type.');
257:         }
258:     }
259: 
260: 
261: 
262:     /**
263:      * Encodes string for use in a LIKE statement.
264:      * @param  string
265:      * @param  int
266:      * @return string
267:      */
268:     public function escapeLike($value, $pos)
269:     {
270:         $value = addcslashes(str_replace('\\', '\\\\', $value), "\x00\\%_");
271:         $value = str_replace("'", "''", $value);
272:         return ($pos <= 0 ? "'%" : "'") . $value . ($pos >= 0 ? "%'" : "'");
273:     }
274: 
275: 
276: 
277:     /**
278:      * Decodes data from result set.
279:      * @param  string    value
280:      * @param  string    type (dibi::BINARY)
281:      * @return string    decoded value
282:      * @throws InvalidArgumentException
283:      */
284:     public function unescape($value, $type)
285:     {
286:         if ($type === dibi::BINARY) {
287:             return $value;
288:         }
289:         throw new InvalidArgumentException('Unsupported type.');
290:     }
291: 
292: 
293: 
294:     /**
295:      * Injects LIMIT/OFFSET to the SQL query.
296:      * @param  string &$sql  The SQL query that will be modified.
297:      * @param  int $limit
298:      * @param  int $offset
299:      * @return void
300:      */
301:     public function applyLimit(&$sql, $limit, $offset)
302:     {
303:         if ($offset > 0) {
304:             // see http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
305:             $sql = 'SELECT * FROM (SELECT t.*, ROWNUM AS "__rnum" FROM (' . $sql . ') t ' . ($limit >= 0 ? 'WHERE ROWNUM <= ' . ((int) $offset + (int) $limit) : '') . ') WHERE "__rnum" > '. (int) $offset;
306: 
307:         } elseif ($limit >= 0) {
308:             $sql = 'SELECT * FROM (' . $sql . ') WHERE ROWNUM <= ' . (int) $limit;
309:         }
310:     }
311: 
312: 
313: 
314:     /********************* result set ****************d*g**/
315: 
316: 
317: 
318:     /**
319:      * Automatically frees the resources allocated for this result set.
320:      * @return void
321:      */
322:     public function __destruct()
323:     {
324:         $this->resultSet && @$this->free();
325:     }
326: 
327: 
328: 
329:     /**
330:      * Returns the number of rows in a result set.
331:      * @return int
332:      */
333:     public function getRowCount()
334:     {
335:         throw new DibiNotSupportedException('Row count is not available for unbuffered queries.');
336:     }
337: 
338: 
339: 
340:     /**
341:      * Fetches the row at current position and moves the internal cursor to the next position.
342:      * @param  bool     TRUE for associative array, FALSE for numeric
343:      * @return array    array on success, nonarray if no next record
344:      */
345:     public function fetch($assoc)
346:     {
347:         return oci_fetch_array($this->resultSet, ($assoc ? OCI_ASSOC : OCI_NUM) | OCI_RETURN_NULLS);
348:     }
349: 
350: 
351: 
352:     /**
353:      * Moves cursor position without fetching row.
354:      * @param  int      the 0-based cursor pos to seek to
355:      * @return boolean  TRUE on success, FALSE if unable to seek to specified record
356:      */
357:     public function seek($row)
358:     {
359:         throw new DibiNotImplementedException;
360:     }
361: 
362: 
363: 
364:     /**
365:      * Frees the resources allocated for this result set.
366:      * @return void
367:      */
368:     public function free()
369:     {
370:         oci_free_statement($this->resultSet);
371:         $this->resultSet = NULL;
372:     }
373: 
374: 
375: 
376:     /**
377:      * Returns metadata for all columns in a result set.
378:      * @return array
379:      */
380:     public function getResultColumns()
381:     {
382:         $count = oci_num_fields($this->resultSet);
383:         $columns = array();
384:         for ($i = 1; $i <= $count; $i++) {
385:             $columns[] = array(
386:                 'name' => oci_field_name($this->resultSet, $i),
387:                 'table' => NULL,
388:                 'fullname' => oci_field_name($this->resultSet, $i),
389:                 'nativetype'=> oci_field_type($this->resultSet, $i),
390:             );
391:         }
392:         return $columns;
393:     }
394: 
395: 
396: 
397:     /**
398:      * Returns the result set resource.
399:      * @return mixed
400:      */
401:     public function getResultResource()
402:     {
403:         return $this->resultSet;
404:     }
405: 
406: 
407: 
408:     /********************* IDibiReflector ****************d*g**/
409: 
410: 
411: 
412:     /**
413:      * Returns list of tables.
414:      * @return array
415:      */
416:     public function getTables()
417:     {
418:         $res = $this->query('SELECT * FROM cat');
419:         $tables = array();
420:         while ($row = $res->fetch(FALSE)) {
421:             if ($row[1] === 'TABLE' || $row[1] === 'VIEW') {
422:                 $tables[] = array(
423:                     'name' => $row[0],
424:                     'view' => $row[1] === 'VIEW',
425:                 );
426:             }
427:         }
428:         return $tables;
429:     }
430: 
431: 
432: 
433:     /**
434:      * Returns metadata for all columns in a table.
435:      * @param  string
436:      * @return array
437:      */
438:     public function getColumns($table)
439:     {
440:         throw new DibiNotImplementedException;
441:     }
442: 
443: 
444: 
445:     /**
446:      * Returns metadata for all indexes in a table.
447:      * @param  string
448:      * @return array
449:      */
450:     public function getIndexes($table)
451:     {
452:         throw new DibiNotImplementedException;
453:     }
454: 
455: 
456: 
457:     /**
458:      * Returns metadata for all foreign keys in a table.
459:      * @param  string
460:      * @return array
461:      */
462:     public function getForeignKeys($table)
463:     {
464:         throw new DibiNotImplementedException;
465:     }
466: 
467: }
468: