1 <?php
2 /**
3 * @package FrameworkOnFramework
4 * @subpackage database
5 * @copyright Copyright (C) 2010-2016 Nicholas K. Dionysopoulos / Akeeba Ltd. All rights reserved.
6 * @license GNU General Public License version 2 or later; see LICENSE.txt
7 *
8 * This file is adapted from the Joomla! Platform. It is used to iterate a database cursor returning FOFTable objects
9 * instead of plain stdClass objects
10 */
11
12 // Protect from unauthorized access
13 defined('FOF_INCLUDED') or die;
14
15 /**
16 * Oracle database driver
17 *
18 * @see http://php.net/pdo
19 * @since 12.1
20 */
21 class FOFDatabaseDriverOracle extends FOFDatabaseDriverPdo
22 {
23 /**
24 * The name of the database driver.
25 *
26 * @var string
27 * @since 12.1
28 */
29 public $name = 'oracle';
30
31 /**
32 * The type of the database server family supported by this driver.
33 *
34 * @var string
35 * @since CMS 3.5.0
36 */
37 public $serverType = 'oracle';
38
39 /**
40 * The character(s) used to quote SQL statement names such as table names or field names,
41 * etc. The child classes should define this as necessary. If a single character string the
42 * same character is used for both sides of the quoted name, else the first character will be
43 * used for the opening quote and the second for the closing quote.
44 *
45 * @var string
46 * @since 12.1
47 */
48 protected $nameQuote = '"';
49
50 /**
51 * Returns the current dateformat
52 *
53 * @var string
54 * @since 12.1
55 */
56 protected $dateformat;
57
58 /**
59 * Returns the current character set
60 *
61 * @var string
62 * @since 12.1
63 */
64 protected $charset;
65
66 /**
67 * Constructor.
68 *
69 * @param array $options List of options used to configure the connection
70 *
71 * @since 12.1
72 */
73 public function __construct($options)
74 {
75 $options['driver'] = 'oci';
76 $options['charset'] = (isset($options['charset'])) ? $options['charset'] : 'AL32UTF8';
77 $options['dateformat'] = (isset($options['dateformat'])) ? $options['dateformat'] : 'RRRR-MM-DD HH24:MI:SS';
78
79 $this->charset = $options['charset'];
80 $this->dateformat = $options['dateformat'];
81
82 // Finalize initialisation
83 parent::__construct($options);
84 }
85
86 /**
87 * Destructor.
88 *
89 * @since 12.1
90 */
91 public function __destruct()
92 {
93 $this->freeResult();
94 unset($this->connection);
95 }
96
97 /**
98 * Connects to the database if needed.
99 *
100 * @return void Returns void if the database connected successfully.
101 *
102 * @since 12.1
103 * @throws RuntimeException
104 */
105 public function connect()
106 {
107 if ($this->connection)
108 {
109 return;
110 }
111
112 parent::connect();
113
114 if (isset($this->options['schema']))
115 {
116 $this->setQuery('ALTER SESSION SET CURRENT_SCHEMA = ' . $this->quoteName($this->options['schema']))->execute();
117 }
118
119 $this->setDateFormat($this->dateformat);
120 }
121
122 /**
123 * Disconnects the database.
124 *
125 * @return void
126 *
127 * @since 12.1
128 */
129 public function disconnect()
130 {
131 // Close the connection.
132 $this->freeResult();
133 unset($this->connection);
134 }
135
136 /**
137 * Drops a table from the database.
138 *
139 * Note: The IF EXISTS flag is unused in the Oracle driver.
140 *
141 * @param string $tableName The name of the database table to drop.
142 * @param boolean $ifExists Optionally specify that the table must exist before it is dropped.
143 *
144 * @return FOFDatabaseDriverOracle Returns this object to support chaining.
145 *
146 * @since 12.1
147 */
148 public function dropTable($tableName, $ifExists = true)
149 {
150 $this->connect();
151
152 $query = $this->getQuery(true)
153 ->setQuery('DROP TABLE :tableName');
154 $query->bind(':tableName', $tableName);
155
156 $this->setQuery($query);
157
158 $this->execute();
159
160 return $this;
161 }
162
163 /**
164 * Method to get the database collation in use by sampling a text field of a table in the database.
165 *
166 * @return mixed The collation in use by the database or boolean false if not supported.
167 *
168 * @since 12.1
169 */
170 public function getCollation()
171 {
172 return $this->charset;
173 }
174
175 /**
176 * Method to get the database connection collation, as reported by the driver. If the connector doesn't support
177 * reporting this value please return an empty string.
178 *
179 * @return string
180 */
181 public function getConnectionCollation()
182 {
183 return $this->charset;
184 }
185
186 /**
187 * Get a query to run and verify the database is operational.
188 *
189 * @return string The query to check the health of the DB.
190 *
191 * @since 12.2
192 */
193 public function getConnectedQuery()
194 {
195 return 'SELECT 1 FROM dual';
196 }
197
198 /**
199 * Returns the current date format
200 * This method should be useful in the case that
201 * somebody actually wants to use a different
202 * date format and needs to check what the current
203 * one is to see if it needs to be changed.
204 *
205 * @return string The current date format
206 *
207 * @since 12.1
208 */
209 public function getDateFormat()
210 {
211 return $this->dateformat;
212 }
213
214 /**
215 * Shows the table CREATE statement that creates the given tables.
216 *
217 * Note: You must have the correct privileges before this method
218 * will return usable results!
219 *
220 * @param mixed $tables A table name or a list of table names.
221 *
222 * @return array A list of the create SQL for the tables.
223 *
224 * @since 12.1
225 * @throws RuntimeException
226 */
227 public function getTableCreate($tables)
228 {
229 $this->connect();
230
231 $result = array();
232 $query = $this->getQuery(true)
233 ->select('dbms_metadata.get_ddl(:type, :tableName)')
234 ->from('dual')
235 ->bind(':type', 'TABLE');
236
237 // Sanitize input to an array and iterate over the list.
238 settype($tables, 'array');
239
240 foreach ($tables as $table)
241 {
242 $query->bind(':tableName', $table);
243 $this->setQuery($query);
244 $statement = (string) $this->loadResult();
245 $result[$table] = $statement;
246 }
247
248 return $result;
249 }
250
251 /**
252 * Retrieves field information about a given table.
253 *
254 * @param string $table The name of the database table.
255 * @param boolean $typeOnly True to only return field types.
256 *
257 * @return array An array of fields for the database table.
258 *
259 * @since 12.1
260 * @throws RuntimeException
261 */
262 public function getTableColumns($table, $typeOnly = true)
263 {
264 $this->connect();
265
266 $columns = array();
267 $query = $this->getQuery(true);
268
269 $fieldCasing = $this->getOption(PDO::ATTR_CASE);
270
271 $this->setOption(PDO::ATTR_CASE, PDO::CASE_UPPER);
272
273 $table = strtoupper($table);
274
275 $query->select('*');
276 $query->from('ALL_TAB_COLUMNS');
277 $query->where('table_name = :tableName');
278
279 $prefixedTable = str_replace('#__', strtoupper($this->tablePrefix), $table);
280 $query->bind(':tableName', $prefixedTable);
281 $this->setQuery($query);
282 $fields = $this->loadObjectList();
283
284 if ($typeOnly)
285 {
286 foreach ($fields as $field)
287 {
288 $columns[$field->COLUMN_NAME] = $field->DATA_TYPE;
289 }
290 }
291 else
292 {
293 foreach ($fields as $field)
294 {
295 $columns[$field->COLUMN_NAME] = $field;
296 $columns[$field->COLUMN_NAME]->Default = null;
297 }
298 }
299
300 $this->setOption(PDO::ATTR_CASE, $fieldCasing);
301
302 return $columns;
303 }
304
305 /**
306 * Get the details list of keys for a table.
307 *
308 * @param string $table The name of the table.
309 *
310 * @return array An array of the column specification for the table.
311 *
312 * @since 12.1
313 * @throws RuntimeException
314 */
315 public function getTableKeys($table)
316 {
317 $this->connect();
318
319 $query = $this->getQuery(true);
320
321 $fieldCasing = $this->getOption(PDO::ATTR_CASE);
322
323 $this->setOption(PDO::ATTR_CASE, PDO::CASE_UPPER);
324
325 $table = strtoupper($table);
326 $query->select('*')
327 ->from('ALL_CONSTRAINTS')
328 ->where('table_name = :tableName')
329 ->bind(':tableName', $table);
330
331 $this->setQuery($query);
332 $keys = $this->loadObjectList();
333
334 $this->setOption(PDO::ATTR_CASE, $fieldCasing);
335
336 return $keys;
337 }
338
339 /**
340 * Method to get an array of all tables in the database (schema).
341 *
342 * @param string $databaseName The database (schema) name
343 * @param boolean $includeDatabaseName Whether to include the schema name in the results
344 *
345 * @return array An array of all the tables in the database.
346 *
347 * @since 12.1
348 * @throws RuntimeException
349 */
350 public function getTableList($databaseName = null, $includeDatabaseName = false)
351 {
352 $this->connect();
353
354 $query = $this->getQuery(true);
355
356 if ($includeDatabaseName)
357 {
358 $query->select('owner, table_name');
359 }
360 else
361 {
362 $query->select('table_name');
363 }
364
365 $query->from('all_tables');
366
367 if ($databaseName)
368 {
369 $query->where('owner = :database')
370 ->bind(':database', $databaseName);
371 }
372
373 $query->order('table_name');
374
375 $this->setQuery($query);
376
377 if ($includeDatabaseName)
378 {
379 $tables = $this->loadAssocList();
380 }
381 else
382 {
383 $tables = $this->loadColumn();
384 }
385
386 return $tables;
387 }
388
389 /**
390 * Get the version of the database connector.
391 *
392 * @return string The database connector version.
393 *
394 * @since 12.1
395 */
396 public function getVersion()
397 {
398 $this->connect();
399
400 $this->setQuery("select value from nls_database_parameters where parameter = 'NLS_RDBMS_VERSION'");
401
402 return $this->loadResult();
403 }
404
405 /**
406 * Select a database for use.
407 *
408 * @param string $database The name of the database to select for use.
409 *
410 * @return boolean True if the database was successfully selected.
411 *
412 * @since 12.1
413 * @throws RuntimeException
414 */
415 public function select($database)
416 {
417 $this->connect();
418
419 return true;
420 }
421
422 /**
423 * Sets the Oracle Date Format for the session
424 * Default date format for Oracle is = DD-MON-RR
425 * The default date format for this driver is:
426 * 'RRRR-MM-DD HH24:MI:SS' since it is the format
427 * that matches the MySQL one used within most Joomla
428 * tables.
429 *
430 * @param string $dateFormat Oracle Date Format String
431 *
432 * @return boolean
433 *
434 * @since 12.1
435 */
436 public function setDateFormat($dateFormat = 'DD-MON-RR')
437 {
438 $this->connect();
439
440 $this->setQuery("ALTER SESSION SET NLS_DATE_FORMAT = '$dateFormat'");
441
442 if (!$this->execute())
443 {
444 return false;
445 }
446
447 $this->setQuery("ALTER SESSION SET NLS_TIMESTAMP_FORMAT = '$dateFormat'");
448
449 if (!$this->execute())
450 {
451 return false;
452 }
453
454 $this->dateformat = $dateFormat;
455
456 return true;
457 }
458
459 /**
460 * Set the connection to use UTF-8 character encoding.
461 *
462 * Returns false automatically for the Oracle driver since
463 * you can only set the character set when the connection
464 * is created.
465 *
466 * @return boolean True on success.
467 *
468 * @since 12.1
469 */
470 public function setUtf()
471 {
472 return false;
473 }
474
475 /**
476 * Locks a table in the database.
477 *
478 * @param string $table The name of the table to unlock.
479 *
480 * @return FOFDatabaseDriverOracle Returns this object to support chaining.
481 *
482 * @since 12.1
483 * @throws RuntimeException
484 */
485 public function lockTable($table)
486 {
487 $this->setQuery('LOCK TABLE ' . $this->quoteName($table) . ' IN EXCLUSIVE MODE')->execute();
488
489 return $this;
490 }
491
492 /**
493 * Renames a table in the database.
494 *
495 * @param string $oldTable The name of the table to be renamed
496 * @param string $newTable The new name for the table.
497 * @param string $backup Not used by Oracle.
498 * @param string $prefix Not used by Oracle.
499 *
500 * @return FOFDatabaseDriverOracle Returns this object to support chaining.
501 *
502 * @since 12.1
503 * @throws RuntimeException
504 */
505 public function renameTable($oldTable, $newTable, $backup = null, $prefix = null)
506 {
507 $this->setQuery('RENAME ' . $oldTable . ' TO ' . $newTable)->execute();
508
509 return $this;
510 }
511
512 /**
513 * Unlocks tables in the database.
514 *
515 * @return FOFDatabaseDriverOracle Returns this object to support chaining.
516 *
517 * @since 12.1
518 * @throws RuntimeException
519 */
520 public function unlockTables()
521 {
522 $this->setQuery('COMMIT')->execute();
523
524 return $this;
525 }
526
527 /**
528 * Test to see if the PDO ODBC connector is available.
529 *
530 * @return boolean True on success, false otherwise.
531 *
532 * @since 12.1
533 */
534 public static function isSupported()
535 {
536 return class_exists('PDO') && in_array('oci', PDO::getAvailableDrivers());
537 }
538
539 /**
540 * This function replaces a string identifier <var>$prefix</var> with the string held is the
541 * <var>tablePrefix</var> class variable.
542 *
543 * @param string $query The SQL statement to prepare.
544 * @param string $prefix The common table prefix.
545 *
546 * @return string The processed SQL statement.
547 *
548 * @since 11.1
549 */
550 public function replacePrefix($query, $prefix = '#__')
551 {
552 $startPos = 0;
553 $quoteChar = "'";
554 $literal = '';
555
556 $query = trim($query);
557 $n = strlen($query);
558
559 while ($startPos < $n)
560 {
561 $ip = strpos($query, $prefix, $startPos);
562
563 if ($ip === false)
564 {
565 break;
566 }
567
568 $j = strpos($query, "'", $startPos);
569
570 if ($j === false)
571 {
572 $j = $n;
573 }
574
575 $literal .= str_replace($prefix, $this->tablePrefix, substr($query, $startPos, $j - $startPos));
576 $startPos = $j;
577
578 $j = $startPos + 1;
579
580 if ($j >= $n)
581 {
582 break;
583 }
584
585 // Quote comes first, find end of quote
586 while (true)
587 {
588 $k = strpos($query, $quoteChar, $j);
589 $escaped = false;
590
591 if ($k === false)
592 {
593 break;
594 }
595
596 $l = $k - 1;
597
598 while ($l >= 0 && $query{$l} == '\\')
599 {
600 $l--;
601 $escaped = !$escaped;
602 }
603
604 if ($escaped)
605 {
606 $j = $k + 1;
607 continue;
608 }
609
610 break;
611 }
612
613 if ($k === false)
614 {
615 // Error in the query - no end quote; ignore it
616 break;
617 }
618
619 $literal .= substr($query, $startPos, $k - $startPos + 1);
620 $startPos = $k + 1;
621 }
622
623 if ($startPos < $n)
624 {
625 $literal .= substr($query, $startPos, $n - $startPos);
626 }
627
628 return $literal;
629 }
630
631 /**
632 * Method to commit a transaction.
633 *
634 * @param boolean $toSavepoint If true, commit to the last savepoint.
635 *
636 * @return void
637 *
638 * @since 12.3
639 * @throws RuntimeException
640 */
641 public function transactionCommit($toSavepoint = false)
642 {
643 $this->connect();
644
645 if (!$toSavepoint || $this->transactionDepth <= 1)
646 {
647 parent::transactionCommit($toSavepoint);
648 }
649 else
650 {
651 $this->transactionDepth--;
652 }
653 }
654
655 /**
656 * Method to roll back a transaction.
657 *
658 * @param boolean $toSavepoint If true, rollback to the last savepoint.
659 *
660 * @return void
661 *
662 * @since 12.3
663 * @throws RuntimeException
664 */
665 public function transactionRollback($toSavepoint = false)
666 {
667 $this->connect();
668
669 if (!$toSavepoint || $this->transactionDepth <= 1)
670 {
671 parent::transactionRollback($toSavepoint);
672 }
673 else
674 {
675 $savepoint = 'SP_' . ($this->transactionDepth - 1);
676 $this->setQuery('ROLLBACK TO SAVEPOINT ' . $this->quoteName($savepoint));
677
678 if ($this->execute())
679 {
680 $this->transactionDepth--;
681 }
682 }
683 }
684
685 /**
686 * Method to initialize a transaction.
687 *
688 * @param boolean $asSavepoint If true and a transaction is already active, a savepoint will be created.
689 *
690 * @return void
691 *
692 * @since 12.3
693 * @throws RuntimeException
694 */
695 public function transactionStart($asSavepoint = false)
696 {
697 $this->connect();
698
699 if (!$asSavepoint || !$this->transactionDepth)
700 {
701 return parent::transactionStart($asSavepoint);
702 }
703
704 $savepoint = 'SP_' . $this->transactionDepth;
705 $this->setQuery('SAVEPOINT ' . $this->quoteName($savepoint));
706
707 if ($this->execute())
708 {
709 $this->transactionDepth++;
710 }
711 }
712 }
713