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