1<?php 2 3// Pandora FMS - http://pandorafms.com 4// ================================================== 5// Copyright (c) 2005-2011 Artica Soluciones Tecnologicas 6// Please see http://pandorafms.org for full contribution list 7 8// This program is free software; you can redistribute it and/or 9// modify it under the terms of the GNU Lesser General Public License 10// as published by the Free Software Foundation; version 2 11 12// This program is distributed in the hope that it will be useful, 13// but WITHOUT ANY WARRANTY; without even the implied warranty of 14// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 15// GNU General Public License for more details. 16 17function oracle_connect_db($host = null, $db = null, $user = null, $pass = null, $port = null, $new_connection = true) { 18 global $config; 19 20 if ($host === null) 21 $host = $config["dbhost"]; 22 if ($db === null) 23 $db = $config["dbname"]; 24 if ($user === null) 25 $user = $config["dbuser"]; 26 if ($pass === null) 27 $pass = $config["dbpass"]; 28 if ($port === null) 29 $port = $config["dbport"]; 30 31 // Non-persistent connection: This will help to avoid mysql errors like "has gone away" or locking problems 32 // If you want persistent connections change it to oci_pconnect(). 33 if ($new_connection) 34 $connect_id = oci_new_connect($user, $pass, '//' . $host . ':' . $port . '/' . $db); 35 else 36 $connect_id = oci_connect($user, $pass, '//' . $host . ':' . $port . '/' . $db); 37 38 if (! $connect_id) { 39 return false; 40 } 41 42 // Set date and timestamp formats for this session 43 $datetime_tz_format = oci_parse($connect_id , 'alter session set NLS_TIMESTAMP_TZ_FORMAT =\'YYYY-MM-DD HH24:MI:SS\''); 44 $datetime_format = oci_parse($connect_id , 'alter session set NLS_TIMESTAMP_FORMAT =\'YYYY-MM-DD HH24:MI:SS\''); 45 $date_format = oci_parse($connect_id , 'alter session set NLS_DATE_FORMAT =\'YYYY-MM-DD HH24:MI:SS\''); 46 $decimal_separator = oci_parse($connect_id , 'alter session set NLS_NUMERIC_CHARACTERS =\'.,\''); 47 48 oci_execute($datetime_tz_format); 49 oci_execute($datetime_format); 50 oci_execute($date_format); 51 oci_execute($decimal_separator); 52 53 oci_free_statement($datetime_tz_format); 54 oci_free_statement($datetime_format); 55 oci_free_statement($date_format); 56 oci_free_statement($decimal_separator); 57 58 return $connect_id; 59} 60 61/** 62 * Get the first value of the first row of a table in the database. 63 * 64 * @param string Field name to get 65 * @param string Table to retrieve the data 66 * @param string Field to filter elements 67 * @param string Condition the field must have 68 * 69 * @return mixed Value of first column of the first row. False if there were no row. 70 */ 71function oracle_db_get_value ($field, $table, $field_search = 1, $condition = 1, $search_history_db = false) { 72 73 if (is_int ($condition)) { 74 $sql = sprintf ("SELECT * 75 FROM (SELECT %s FROM %s WHERE %s = %d) 76 WHERE rownum < 2", 77 $field, $table, $field_search, $condition); 78 } 79 else if (is_float ($condition) || is_double ($condition)) { 80 $sql = sprintf ("SELECT * 81 FROM (SELECT %s FROM %s WHERE %s = %f) 82 WHERE rownum < 2", 83 $field, $table, $field_search, $condition); 84 } 85 else { 86 $sql = sprintf ("SELECT * 87 FROM (SELECT %s FROM %s WHERE %s = '%s') 88 WHERE rownum < 2", 89 $field, $table, $field_search, $condition); 90 } 91 92 $result = db_get_all_rows_sql ($sql, $search_history_db); 93 94 if ($result === false) 95 return false; 96 97 $row = array_shift($result); 98 $value = array_shift($row); 99 100 if ($value === null) 101 return false; 102 103 return $value; 104} 105 106/** 107 * Get the first row of a database query into a table. 108 * 109 * The SQL statement executed would be something like: 110 * "SELECT (*||$fields) FROM $table WHERE $field_search = $condition" 111 * 112 * @param string Table to get the row 113 * @param string Field to filter elements 114 * @param string Condition the field must have. 115 * @param mixed Fields to select (array or string or false/empty for *) 116 * 117 * @return mixed The first row of a database query or false. 118 */ 119function oracle_db_get_row ($table, $field_search, $condition, $fields = false) { 120 if (empty ($fields)) { 121 $fields = '*'; 122 } 123 else { 124 if (is_array ($fields)) 125 $fields = implode (',', $fields); 126 else if (! is_string ($fields)) 127 return false; 128 } 129 130 if (is_int ($condition)) { 131 $sql = sprintf ('SELECT * FROM (SELECT %s FROM %s WHERE %s = %d) WHERE rownum < 2', 132 $fields, $table, $field_search, $condition); 133 } 134 else if (is_float ($condition) || is_double ($condition)) { 135 $sql = sprintf ("SELECT * FROM (SELECT %s FROM %s WHERE \"%s\" = %f) WHERE rownum < 2", 136 $fields, $table, $field_search, $condition); 137 } 138 else { 139 $sql = sprintf ("SELECT * FROM (SELECT %s FROM %s WHERE %s = '%s') WHERE rownum < 2", 140 $fields, $table, $field_search, $condition); 141 } 142 $result = db_get_all_rows_sql ($sql); 143 144 if ($result === false) 145 return false; 146 147 return $result[0]; 148} 149 150function oracle_db_get_all_rows_sql ($sql, $search_history_db = false, $cache = true, $dbconnection = false) { 151 global $config; 152 153 $history = array (); 154 155 if ($dbconnection === false) { 156 $dbconnection = $config['dbconnection']; 157 } 158 159 // To disable globally SQL cache depending on global variable. 160 // Used in several critical places like Metaconsole trans-server queries 161 if (isset($config["dbcache"])) 162 $cache = $config["dbcache"]; 163 164 // Read from the history DB if necessary 165 if ($search_history_db && $config['history_db_enabled'] == 1) { 166 $cache = false; 167 $history = false; 168 169 // Connect to the history DB 170 if (! isset ($config['history_db_connection']) || $config['history_db_connection'] === false) { 171 $config['history_db_connection'] = db_connect($config['history_db_host'], $config['history_db_name'], $config['history_db_user'], io_output_password($config['history_db_pass']), $config['history_db_port'], false); 172 } 173 if ($config['history_db_connection'] !== false) { 174 $history = oracle_db_process_sql ($sql, 'affected_rows', $config['history_db_connection'], false); 175 } 176 177 if ($history === false) { 178 $history = array (); 179 } 180 } 181 182 $return = oracle_db_process_sql ($sql, 'affected_rows', $dbconnection, $cache); 183 if ($return === false) { 184 $return = array (); 185 } 186 187 // Append result to the history DB data 188 if (! empty ($return)) { 189 foreach ($return as $row) { 190 array_push ($history, $row); 191 } 192 } 193 194 if (! empty ($history)) 195 return $history; 196 //Return false, check with === or !== 197 return false; 198} 199 200/** 201 * This function comes back with an array in case of SELECT 202 * in case of UPDATE, DELETE etc. with affected rows 203 * an empty array in case of SELECT without results 204 * Queries that return data will be cached so queries don't get repeated 205 * 206 * @param string SQL statement to execute 207 * 208 * @param string What type of info to return in case of INSERT/UPDATE. 209 * 'affected_rows' will return mysql_affected_rows (default value) 210 * 'insert_id' will return the ID of an autoincrement value 211 * 'info' will return the full (debug) information of a query 212 * 213 * @param bool Set autocommit transaction mode true/false 214 * 215 * @return mixed An array with the rows, columns and values in a multidimensional array or false in error 216 */ 217// TODO: Return debug info of the query 218function oracle_db_process_sql($sql, $rettype = "affected_rows", $dbconnection = '', $cache = true, &$status = null, $autocommit = true) { 219 global $config; 220 global $sql_cache; 221 222 $retval = array(); 223 224 if ($sql == '') 225 return false; 226 227 if ($cache && ! empty ($sql_cache[$sql])) { 228 $retval = $sql_cache[$sql]; 229 $sql_cache['saved']++; 230 db_add_database_debug_trace ($sql); 231 } 232 else { 233 $id = 0; 234 $parse_query = explode(' ', trim(preg_replace('/\s\s+/', ' ', $sql))); 235 $table_name = preg_replace('/\((\w*|,\w*)*\)|\(\w*|,\w*/', '', preg_replace('/\s/', '', $parse_query[2])); 236 $type = explode(' ', strtoupper(trim($sql))); 237 238 $start = microtime (true); 239 240 if (empty($dbconnection)) { 241 $dbconnection = $config['dbconnection']; 242 } 243 244 if ($type[0] == 'INSERT') { 245 $query = oci_parse($dbconnection, 'BEGIN insert_id(:table_name, :sql, :out); END;'); 246 } 247 // Prevent execution of insert_id stored procedure 248 else if ($type[0] == '/INSERT') { 249 $query = oci_parse($dbconnection, substr($sql,1)); 250 } 251 else { 252 $query = oci_parse($dbconnection, $sql); 253 } 254 255 //If query is an insert retrieve Id field 256 if ($type[0] == 'INSERT') { 257 oci_bind_by_name($query, ":table_name", $table_name, 32); 258 oci_bind_by_name($query, ":sql", $sql, -1); 259 oci_bind_by_name($query, ":out", $id, 40); 260 } 261 262 if (!$autocommit) { 263 $result = oci_execute($query, OCI_NO_AUTO_COMMIT); 264 } 265 else { 266 $result = oci_execute($query); 267 } 268 $time = microtime (true) - $start; 269 270 $config['oracle_error_query'] = null; 271 if ($result === false) { 272 $backtrace = debug_backtrace (); 273 $e = oci_error($query); 274 275 $config['oracle_error_query'] = $query; 276 277 278 $error = sprintf ('%s (\'%s\') in <strong>%s</strong> on line %d', 279 htmlentities($e['message'], ENT_QUOTES), $sql, $backtrace[0]['file'], $backtrace[0]['line']); 280 db_add_database_debug_trace ($sql, htmlentities($e['message'], ENT_QUOTES)); 281 282 set_error_handler ('db_sql_error_handler'); 283 trigger_error ($error); 284 restore_error_handler (); 285 286 return false; 287 } 288 else { 289 $status = oci_statement_type($query); 290 $rows = oci_num_rows($query); 291 292 if ($status !== 'SELECT') { //The query NOT IS a select 293 if ($rettype == "insert_id") { 294 $result = $id; 295 } 296 elseif ($rettype == "info") { 297 //TODO: return debug information of the query $result = pg_result_status($result, PGSQL_STATUS_STRING); 298 $result = ''; 299 } 300 else { 301 $result = $rows; 302 } 303 db_add_database_debug_trace ($sql, $result, $rows, 304 array ('time' => $time)); 305 306 return $result; 307 } 308 else { //The query IS a select. 309 db_add_database_debug_trace ($sql, 0, $rows, array ('time' => $time)); 310 while ($row = oci_fetch_assoc($query)) { 311 $i = 1; 312 $result_temp = array(); 313 foreach ($row as $key => $value) { 314 $column_type = oci_field_type($query, $key); 315 // Support for Clob fields larger than 4000bytes 316 //if ($sql == 'SELECT * FROM tgrupo ORDER BY dbms_lob.substr(nombre,4000,1) ASC') echo $i .' '.$column_type.' '.$key.'<br>'; 317 if ($column_type == 'CLOB') { 318 $column_name = oci_field_name($query, $i); 319 // Protect against a NULL CLOB 320 if (is_object($row[$column_name])) { 321 $clob_data = $row[$column_name]->load(); 322 $row[$column_name]->free(); 323 $value = $clob_data; 324 } 325 else { 326 $value = ''; 327 } 328 } 329 $result_temp[strtolower($key)] = $value; 330 $i++; 331 } 332 array_push($retval, $result_temp); 333 //array_push($retval, $row); 334 } 335 336 if ($cache === true) 337 $sql_cache[$sql] = $retval; 338 oci_free_statement ($query); 339 } 340 } 341 } 342 343 if (! empty ($retval)) { 344 return $retval; 345 } 346 347 //Return false, check with === or !== 348 return false; 349} 350 351/** 352 * Get all the rows in a table of the database. 353 * 354 * @param string Database table name. 355 * @param string Field to order by. 356 * @param string $order The type of order, by default 'ASC'. 357 * 358 * @return mixed A matrix with all the values in the table 359 */ 360function oracle_db_get_all_rows_in_table($table, $order_field = "", $order = 'ASC') { 361 if ($order_field != "") { 362 363 // Clob fields are not allowed in ORDER BY statements, they need cast to varchar2 datatype 364 $type = db_get_value_filter ('data_type', 'user_tab_columns', 365 array ('table_name' => strtoupper($table), 'column_name' => strtoupper($order_field)), 'AND'); 366 if ($type == 'CLOB') { 367 return db_get_all_rows_sql ('SELECT * 368 FROM ' . $table . ' 369 ORDER BY dbms_lob.substr(' . $order_field . ',4000,1) ' . $order); 370 } 371 else { 372 return db_get_all_rows_sql ('SELECT * 373 FROM ' . $table . ' 374 ORDER BY ' . $order_field . ' ' . $order); 375 } 376 } 377 else { 378 return db_get_all_rows_sql ('SELECT * FROM ' . $table); 379 } 380} 381 382/** 383 * Inserts strings into database 384 * 385 * The number of values should be the same or a positive integer multiple as the number of rows 386 * If you have an associate array (eg. array ("row1" => "value1")) you can use this function with ($table, array_keys ($array), $array) in it's options 387 * All arrays and values should have been cleaned before passing. It's not neccessary to add quotes. 388 * 389 * @param string Table to insert into 390 * @param mixed A single value or array of values to insert (can be a multiple amount of rows) 391 * @param bool Whether to do autocommit or not 392 * 393 * @return mixed False in case of error or invalid values passed. Affected rows otherwise 394 */ 395function oracle_db_process_sql_insert($table, $values, $autocommit = true) { 396 //Empty rows or values not processed 397 if (empty ($values)) 398 return false; 399 400 $values = (array) $values; 401 402 $query = sprintf ('INSERT INTO %s ', $table); 403 $fields = array (); 404 $values_str = ''; 405 $i = 1; 406 $max = count ($values); 407 foreach ($values as $field => $value) { 408 array_push ($fields, $field); 409 410 if (is_null ($value)) { 411 $values_str .= "NULL"; 412 } 413 elseif (is_int ($value) || is_bool ($value)) { 414 $values_str .= sprintf("%d", $value); 415 } 416 else if (is_float ($value) || is_double ($value)) { 417 $values_str .= sprintf("%f", $value); 418 } 419 else if (substr($value,0,1) == '#') { 420 $values_str .= sprintf("%s", substr($value,1)); 421 } 422 else { 423 $values_str .= sprintf("'%s'", $value); 424 } 425 426 if ($i < $max) { 427 $values_str .= ","; 428 } 429 $i++; 430 } 431 432 $query .= '(' . implode(', ', $fields) . ')'; 433 434 $query .= ' VALUES (' . $values_str . ')'; 435 $status = ''; 436 437 return db_process_sql($query, 'insert_id', '', true, $status, $autocommit); 438} 439 440/** 441 * Escape string to set it properly to use in sql queries 442 * 443 * @param string String to be cleaned. 444 * 445 * @return string String cleaned. 446 */ 447function oracle_escape_string_sql($string) { 448 return str_replace(array('"', "'", '\\'), array('\\"', '\\\'', '\\\\'), $string); 449} 450 451function oracle_encapsule_fields_with_same_name_to_instructions($field) { 452 $return = $field; 453 454 if (is_string($return)) { 455 if ($return[0] !== '"') { 456 // The columns declared without quotes are converted to uppercase in oracle. 457 // A column named asd is equal to asd, ASD or "ASD", but no to "asd". 458 $return = '"' . $return . '"'; 459 } 460 } 461 462 return $return; 463} 464 465/** 466 * Get the first value of the first row of a table in the database from an 467 * array with filter conditions. 468 * 469 * Example: 470 <code> 471 db_get_value_filter ('name', 'talert_templates', 472 array ('value' => 2, 'type' => 'equal')); 473 // Equivalent to: 474 // SELECT name FROM talert_templates WHERE value = 2 AND type = 'equal' LIMIT 1 475 // In oracle sintax: 476 // SELECT name FROM talert_templates WHERE value = 2 AND type = 'equal' AND rownum < 2 477 478 db_get_value_filter ('description', 'talert_templates', 479 array ('name' => 'My alert', 'type' => 'regex'), 'OR'); 480 // Equivalent to: 481 // SELECT description FROM talert_templates WHERE name = 'My alert' OR type = 'equal' LIMIT 1 482 // In oracle sintax: 483 // SELECT description FROM talert_templates WHERE name = 'My alert' OR type = 'equal' AND rownum < 2 484 </code> 485 * 486 * @param string Field name to get 487 * @param string Table to retrieve the data 488 * @param array Conditions to filter the element. See db_format_array_where_clause_sql() 489 * for the format 490 * @param string Join operator for the elements in the filter. 491 * 492 * @return mixed Value of first column of the first row. False if there were no row. 493 */ 494function oracle_db_get_value_filter ($field, $table, $filter, $where_join = 'AND', $search_history_db = false) { 495 if (! is_array ($filter) || empty ($filter)) 496 return false; 497 498 /* Avoid limit and offset if given */ 499 unset ($filter['limit']); 500 unset ($filter['offset']); 501 502 $sql = sprintf ("SELECT * FROM (SELECT %s FROM %s WHERE %s) WHERE rownum < 2", 503 $field, $table, 504 db_format_array_where_clause_sql ($filter, $where_join)); 505 $result = db_get_all_rows_sql ($sql, $search_history_db); 506 507 if ($result === false) 508 return false; 509 510 $row = array_shift($result); 511 $value = array_shift($row); 512 513 if ($value === null) 514 return false; 515 516 return $value; 517} 518 519/** 520 * Formats an array of values into a SQL where clause string. 521 * 522 * This function is useful to generate a WHERE clause for a SQL sentence from 523 * a list of values. Example code: 524 <code> 525 $values = array (); 526 $values['name'] = "Name"; 527 $values['description'] = "Long description"; 528 $values['limit'] = $config['block_size']; // Assume it's 20 529 $sql = 'SELECT * FROM table WHERE '.db_format_array_where_clause_sql ($values); 530 echo $sql; 531 </code> 532 * Will return: 533 * <code> 534 * SELECT * FROM table WHERE `name` = "Name" AND `description` = "Long description" LIMIT 20 535 * This in Oracle Sql sintaxis is translate to: 536 * SELECT * FROM table WHERE name = "Name" AND description = "Long description" AND rownum <= 20 537 * </code> 538 * 539 * @param array Values to be formatted in an array indexed by the field name. 540 * There are special parameters such as 'order' and 'limit' that will be used 541 * as ORDER and LIMIT clauses respectively. Since LIMIT is 542 * numeric, ORDER can receive a field name or a SQL function and a the ASC or 543 * DESC clause. Examples: 544 <code> 545 $values = array (); 546 $values['value'] = 10; 547 $sql = 'SELECT * FROM table WHERE '.db_format_array_where_clause_sql ($values); 548 // SELECT * FROM table WHERE VALUE = 10 549 550 $values = array (); 551 $values['value'] = 10; 552 $values['order'] = 'name DESC'; 553 $sql = 'SELECT * FROM table WHERE '.db_format_array_where_clause_sql ($values); 554 // SELECT * FROM table WHERE VALUE = 10 ORDER BY name DESC 555 556 557 IMPORTANT!!! OFFSET is not allowed in this function because Oracle needs to recode the complete query. 558 use oracle_recode_query() function instead 559 * 560 * @return string Values joined into an SQL string that can fits into the WHERE 561 * clause of an SQL sentence. 562 */ 563function oracle_db_format_array_where_clause_sql ($values, $join = 'AND', $prefix = false) { 564 565 $fields = array (); 566 567 if (! is_array ($values)) { 568 return ''; 569 } 570 571 $query = ''; 572 $limit = ''; 573 $order = ''; 574 $group = ''; 575 if (isset($values['offset'])) { 576 return ''; 577 } 578 579 if (isset ($values['limit'])) { 580 $limit = sprintf (' AND rownum <= %d', $values['limit']); 581 unset ($values['limit']); 582 } 583 584 if (isset ($values['order'])) { 585 if (is_array($values['order'])) { 586 if (!isset($values['order']['order'])) { 587 $orderTexts = array(); 588 foreach ($values['order'] as $orderItem) { 589 $orderTexts[] = $orderItem['field'] . ' ' . $orderItem['order']; 590 } 591 $order = ' ORDER BY ' . implode(', ', $orderTexts); 592 } 593 else { 594 $order = sprintf (' ORDER BY %s %s', $values['order']['field'], $values['order']['order']); 595 } 596 } 597 else { 598 $order = sprintf (' ORDER BY %s', $values['order']); 599 } 600 unset ($values['order']); 601 } 602 603 if (isset ($values['group'])) { 604 $group = sprintf (' GROUP BY %s', $values['group']); 605 unset ($values['group']); 606 } 607 608 $i = 1; 609 $max = count ($values); 610 foreach ($values as $field => $value) { 611 if ($i == 1) { 612 $query .= ' ( '; 613 } 614 if ($field == '1' AND $value == '1') { 615 $query .= sprintf("'%s' = '%s'", $field, $value); 616 617 if ($i < $max) { 618 $query .= ' '.$join.' '; 619 } 620 if ($i == $max) { 621 $query .= ' ) '; 622 } 623 $i++; 624 continue; 625 } 626 else if (is_numeric ($field)) { 627 /* User provide the exact operation to do */ 628 $query .= $value; 629 630 if ($i < $max) { 631 $query .= ' '.$join.' '; 632 } 633 if ($i == $max) { 634 $query .= ' ) '; 635 } 636 $i++; 637 continue; 638 } 639 640 if (is_null ($value)) { 641 $query .= sprintf ("%s IS NULL", $field); 642 } 643 elseif (is_int ($value) || is_bool ($value)) { 644 $query .= sprintf ("%s = %d", $field, $value); 645 } 646 else if (is_float ($value) || is_double ($value)) { 647 $query .= sprintf ("%s = %f", $field, $value); 648 } 649 elseif (is_array ($value)) { 650 $query .= sprintf ("%s IN ('%s')", $field, implode ("', '", $value)); 651 } 652 else { 653 if ($value[0] == ">") { 654 $value = substr($value,1,strlen($value)-1); 655 656 if (is_nan($value)) 657 $query .= sprintf ("%s > '%s'", $field, $value); 658 else 659 $query .= sprintf ("%s > %s", $field, $value); 660 } 661 else if ($value[0] == "<") { 662 if ($value[1] == ">") { 663 $value = substr($value,2,strlen($value)-2); 664 665 if (is_nan($value)) 666 $query .= sprintf ("%s <> '%s'", $field, $value); 667 else 668 $query .= sprintf ("%s <> %s", $field, $value); 669 } 670 else { 671 $value = substr($value,1,strlen($value)-1); 672 673 if (is_nan($value)) 674 $query .= sprintf ("%s < '%s'", $field, $value); 675 else 676 $query .= sprintf ("%s < %s", $field, $value); 677 } 678 } 679 else if ($value[0] == '%') { 680 $query .= sprintf ("%s LIKE '%s'", $field, $value); 681 } 682 else { 683 $query .= sprintf ("%s = '%s'", $field, $value); 684 } 685 } 686 687 if ($i < $max) { 688 $query .= ' '.$join.' '; 689 } 690 if ($i == $max) { 691 $query .= ' ) '; 692 } 693 $i++; 694 } 695 return (! empty ($query) ? $prefix: '').$query.$limit.$group.$order; 696} 697 698/** 699 * Formats an SQL query to use LIMIT and OFFSET Mysql like statements in Oracle. 700 * 701 * This function is useful to generate an SQL sentence from 702 * a list of values. Example code: 703 <code> 704 * @param string Join operator. AND by default. 705 * @param string A prefix to be added to the string. It's useful when 706 * offset could be given to avoid this cases: 707 <code> 708 $values = array (); 709 $values['limit'] = 10; 710 $values['offset'] = 20; 711 $sql = 'SELECT * FROM table WHERE '.db_format_array_where_clause_sql ($values); 712 // Wrong SQL: SELECT * FROM table WHERE LIMIT 10 OFFSET 20 713 714 $values = array (); 715 $values['limit'] = 10; 716 $values['offset'] = 20; 717 $sql = 'SELECT * FROM table WHERE '.db_format_array_where_clause_sql ($values, 'AND', 'WHERE'); 718 // Good SQL: SELECT * FROM table LIMIT 10 OFFSET 20 719 // This in Oracle Sql sintaxis is translate to: 720 // SELECT * FROM (SELECT ROWNUM AS rnum, a.* FROM (SELECT * FROM table) a) WHERE rnum > 20 AND rnum <= 30 721 722 $values = array (); 723 $values['value'] = 5; 724 $values['limit'] = 10; 725 $values['offset'] = 20; 726 $sql = 'SELECT * FROM table WHERE '.db_format_array_where_clause_sql ($values, 'AND', 'WHERE'); 727 // Good SQL: SELECT * FROM table WHERE value = 5 LIMIT 10 OFFSET 20 728 // This in Oracle Sql sintaxis is translate to: 729 // SELECT * FROM (SELECT ROWNUM AS rnum, a.* FROM (SELECT * FROM table WHERE value = 5) a) WHERE rnum > 20 AND rnum <= 30; 730 </code> 731 732 * @param string Sql from SELECT to WHERE reserved words: SELECT * FROM mytable WHERE 733 * @param array Conditions to filter the element. See db_format_array_where_clause_sql() 734 * for the format. LIMIT + OFFSET are allowed in this function: 735 736 <code> 737 $values = array(); 738 $values['limit'] = x; 739 $values['offset'] = y; 740 </code> 741 742 * @param string Join operator for the elements in the filter. 743 * @param bool Whether to return Sql or execute. Note that if you return data in a string format then after execute the query you have 744 * to discard RNUM column. 745 * 746 * @return string Values joined into an SQL string that fits Oracle SQL sintax 747 * clause of an SQL sentence. 748 **/ 749function oracle_recode_query ($sql, $values, $join = 'AND', $return = true) { 750 $fields = array (); 751 752 if (! is_array ($values) || empty($sql)) { 753 return ''; 754 } 755 756 $query = ''; 757 $limit = ''; 758 $offset = ''; 759 $order = ''; 760 $group = ''; 761 $pre_query = ''; 762 $post_query = ''; 763 // LIMIT + OFFSET options have to be recoded into a subquery 764 if (isset ($values['limit']) && isset($values['offset'])) { 765 $down = $values['offset']; 766 $top = $values['offset'] + $values['limit']; 767 $pre_query = 'SELECT * FROM (SELECT ROWNUM AS rnum, a.* FROM ('; 768 $post_query = sprintf(") a) WHERE rnum > %d AND rnum <= %d", $down, $top); 769 unset ($values['limit']); 770 unset ($values['offset']); 771 } 772 else if (isset ($values['limit'])) { 773 $limit = sprintf (' AND rownum <= %d', $values['limit']); 774 unset ($values['limit']); 775 } 776 // OFFSET without LIMIT option is not supported 777 else if (isset ($values['offset'])) { 778 unset ($values['offset']); 779 } 780 781 if (isset ($values['order'])) { 782 if (is_array($values['order'])) { 783 if (!isset($values['order']['order'])) { 784 $orderTexts = array(); 785 foreach ($values['order'] as $orderItem) { 786 $orderTexts[] = $orderItem['field'] . ' ' . $orderItem['order']; 787 } 788 $order = ' ORDER BY ' . implode(', ', $orderTexts); 789 } 790 else { 791 $order = sprintf (' ORDER BY %s %s', $values['order']['field'], $values['order']['order']); 792 } 793 } 794 else { 795 $order = sprintf (' ORDER BY %s', $values['order']); 796 } 797 unset ($values['order']); 798 } 799 800 if (isset ($values['group'])) { 801 $group = sprintf (' GROUP BY %s', $values['group']); 802 unset ($values['group']); 803 } 804 805 $i = 1; 806 $max = count ($values); 807 foreach ($values as $field => $value) { 808 if ($i == 1) { 809 $query .= ' ( '; 810 } 811 812 if (is_numeric ($field)) { 813 /* User provide the exact operation to do */ 814 $query .= $value; 815 816 if ($i < $max) { 817 $query .= ' '.$join.' '; 818 } 819 $i++; 820 continue; 821 } 822 823 if (is_null ($value)) { 824 $query .= sprintf ("%s IS NULL", $field); 825 } 826 elseif (is_int ($value) || is_bool ($value)) { 827 $query .= sprintf ("%s = %d", $field, $value); 828 } 829 else if (is_float ($value) || is_double ($value)) { 830 $query .= sprintf ("%s = %f", $field, $value); 831 } 832 elseif (is_array ($value)) { 833 $query .= sprintf ("%s IN ('%s')", $field, implode ("', '", $value)); 834 } 835 else { 836 if ($value[0] == ">") { 837 $value = substr($value,1,strlen($value) - 1); 838 $query .= sprintf ("%s > '%s'", $field, $value); 839 } 840 else if ($value[0] == "<") { 841 if ($value[1] == ">") { 842 $value = substr($value,2,strlen($value) - 2); 843 $query .= sprintf ("%s <> '%s'", $field, $value); 844 } 845 else { 846 $value = substr($value,1,strlen($value) - 1); 847 $query .= sprintf ("%s < '%s'", $field, $value); 848 } 849 } 850 else if ($value[0] == '%') { 851 $query .= sprintf ("%s LIKE '%s'", $field, $value); 852 } 853 else { 854 $query .= sprintf ("%s = '%s'", $field, $value); 855 } 856 } 857 858 if ($i < $max) { 859 $query .= ' '.$join.' '; 860 } 861 if ($i == $max) { 862 $query .= ' ) '; 863 } 864 $i++; 865 } 866 867 $result = $pre_query.$sql.$query.$limit.$group.$order.$post_query; 868 if ($return) { 869 return $result; 870 } 871 else { 872 873 $result = oracle_db_process_sql($result); 874 if ($result !== false) { 875 for ($i=0; $i < count($result); $i++) { 876 unset($result[$i]['RNUM']); 877 } 878 } 879 return $result; 880 } 881} 882 883/** 884 * Get the first value of the first row of a table result from query. 885 * 886 * @param string SQL select statement to execute. 887 * 888 * @return the first value of the first row of a table result from query. 889 * 890 */ 891function oracle_db_get_value_sql($sql, $dbconnection = false) { 892 $sql = "SELECT * FROM (" . $sql . ") WHERE rownum < 2"; 893 $result = oracle_db_get_all_rows_sql ($sql, false, true, $dbconnection); 894 895 if ($result === false) 896 return false; 897 898 $row = array_shift($result); 899 $value = array_shift($row); 900 901 if ($value === null) 902 return false; 903 904 return $value; 905} 906 907/** 908 * Get the first row of an SQL database query. 909 * 910 * @param string SQL select statement to execute. 911 * 912 * @return mixed The first row of the result or false 913 */ 914function oracle_db_get_row_sql ($sql, $search_history_db = false) { 915 $sql = "SELECT * FROM (" . $sql . ") WHERE rownum < 2"; 916 $result = oracle_db_get_all_rows_sql($sql, $search_history_db); 917 918 if ($result === false) 919 return false; 920 921 return $result[0]; 922} 923 924/** 925 * Get the row of a table in the database using a complex filter. 926 * 927 * @param string Table to retrieve the data (warning: not cleaned) 928 * @param mixed Filters elements. It can be an indexed array 929 * (keys would be the field name and value the expected value, and would be 930 * joined with an AND operator) or a string, including any SQL clause (without 931 * the WHERE keyword). Example: 932 <code> 933 Both are similars: 934 db_get_row_filter ('table', array ('disabled', 0)); 935 db_get_row_filter ('table', 'disabled = 0'); 936 937 Both are similars: 938 db_get_row_filter ('table', array ('disabled' => 0, 'history_data' => 0), 'name, description', 'OR'); 939 db_get_row_filter ('table', 'disabled = 0 OR history_data = 0', 'name, description'); 940 db_get_row_filter ('table', array ('disabled' => 0, 'history_data' => 0), array ('name', 'description'), 'OR'); 941 </code> 942 * @param mixed Fields of the table to retrieve. Can be an array or a coma 943 * separated string. All fields are retrieved by default 944 * @param string Condition to join the filters (AND, OR). 945 * 946 * @return mixed Array of the row or false in case of error. 947 */ 948function oracle_db_get_row_filter ($table, $filter, $fields = false, $where_join = 'AND') { 949 if (empty ($fields)) { 950 $fields = '*'; 951 } 952 else { 953 if (is_array ($fields)) 954 $fields = implode (',', $fields); 955 else if (! is_string ($fields)) 956 return false; 957 } 958 959 if (is_array ($filter)) 960 $filter = db_format_array_where_clause_sql ($filter, $where_join, ' WHERE '); 961 else if (is_string ($filter)) 962 $filter = 'WHERE '.$filter; 963 else 964 $filter = ''; 965 966 $sql = sprintf ('SELECT %s FROM %s %s', $fields, $table, $filter); 967 968 return db_get_row_sql ($sql); 969} 970 971/** 972 * Get all the rows of a table in the database that matches a filter. 973 * 974 * @param string Table to retrieve the data (warning: not cleaned) 975 * @param mixed Filters elements. It can be an indexed array 976 * (keys would be the field name and value the expected value, and would be 977 * joined with an AND operator) or a string, including any SQL clause (without 978 * the WHERE keyword). Example: 979 * <code> 980 * Both are similars: 981 * db_get_all_rows_filter ('table', array ('disabled', 0)); 982 * db_get_all_rows_filter ('table', 'disabled = 0'); 983 * 984 * Both are similars: 985 * db_get_all_rows_filter ('table', array ('disabled' => 0, 'history_data' => 0), 'name', 'OR'); 986 * db_get_all_rows_filter ('table', 'disabled = 0 OR history_data = 0', 'name'); 987 * </code> 988 * @param mixed Fields of the table to retrieve. Can be an array or a coma 989 * separated string. All fields are retrieved by default 990 * @param string Condition of the filter (AND, OR). 991 * @param bool $returnSQL Return a string with SQL instead the data, by default false. 992 * 993 * @return mixed Array of the row or false in case of error. 994 */ 995function oracle_db_get_all_rows_filter ($table, $filter = array(), $fields = false, $where_join = 'AND', $search_history_db = false, $returnSQL = false) { 996 //TODO: Validate and clean fields 997 if (empty($fields)) { 998 $fields = '*'; 999 } 1000 elseif (is_array($fields)) { 1001 $fields = implode(' , ', $fields); 1002 } 1003 elseif (!is_string($fields)) { 1004 return false; 1005 } 1006 1007 //TODO: Validate and clean filter options 1008 if (is_array ($filter)) { 1009 $filter = db_format_array_where_clause_sql ($filter, $where_join, ' WHERE '); 1010 } 1011 elseif (is_string ($filter)) { 1012 $filter = 'WHERE '.$filter; 1013 } 1014 else { 1015 $filter = ''; 1016 } 1017 1018 $sql = sprintf ('SELECT %s FROM %s %s', $fields, $table, $filter); 1019 1020 if ($returnSQL) 1021 return $sql; 1022 else 1023 return db_get_all_rows_sql ($sql, $search_history_db); 1024} 1025 1026/** 1027 * Return the count of rows of query. 1028 * 1029 * @param $sql 1030 * @return integer The count of rows of query. 1031 */ 1032function oracle_db_get_num_rows ($sql) { 1033 global $config; 1034 1035 $type = explode(' ',strtoupper(trim($sql))); 1036 if ($type[0] == 'SELECT') { 1037 $sql = "SELECT count(*) as NUM FROM (" . $sql . ")"; 1038 } 1039 $query = oci_parse($config['dbconnection'], $sql); 1040 oci_execute($query); 1041 if ($type[0] == 'SELECT') { 1042 $row = oci_fetch_assoc($query); 1043 $rows = $row['NUM']; 1044 } 1045 else { 1046 $rows = oci_num_rows($query); 1047 } 1048 1049 oci_free_statement($query); 1050 1051 return $rows; 1052} 1053 1054/** 1055 * Get all the rows in a table of the database filtering from a field. 1056 * 1057 * @param string Database table name. 1058 * @param string Field of the table. 1059 * @param string Condition the field must have to be selected. 1060 * @param string Field to order by. 1061 * 1062 * @return mixed A matrix with all the values in the table that matches the condition in the field or false 1063 */ 1064function oracle_db_get_all_rows_field_filter ($table, $field, $condition, $order_field = "") { 1065 if (is_int ($condition) || is_bool ($condition)) { 1066 $sql = sprintf ("SELECT * 1067 FROM %s 1068 WHERE %s = %d", $table, $field, $condition); 1069 } 1070 else if (is_float ($condition) || is_double ($condition)) { 1071 $sql = sprintf ("SELECT * 1072 FROM %s 1073 WHERE %s = %f", $table, $field, $condition); 1074 } 1075 else { 1076 $sql = sprintf ("SELECT * 1077 FROM %s 1078 WHERE %s = '%s'", $table, $field, $condition); 1079 } 1080 1081 if ($order_field != "") 1082 $sql .= sprintf (" ORDER BY %s", $order_field); 1083 1084 return db_get_all_rows_sql ($sql); 1085} 1086 1087/** 1088 * Get all the rows in a table of the database filtering from a field. 1089 * 1090 * @param string Database table name. 1091 * @param string Field of the table. 1092 * 1093 * @return mixed A matrix with all the values in the table that matches the condition in the field 1094 */ 1095function oracle_db_get_all_fields_in_table ($table, $field = '', $condition = '', $order_field = '') { 1096 $sql = sprintf ("SELECT * FROM %s", $table); 1097 1098 if ($condition != '') { 1099 $sql .= sprintf (" WHERE %s = '%s'", $field, $condition); 1100 } 1101 1102 if ($order_field != "") 1103 $sql .= sprintf (" ORDER BY %s", $order_field); 1104 1105 return db_get_all_rows_sql ($sql); 1106} 1107 1108/** 1109 * Formats an array of values into a SQL string. 1110 * 1111 * This function is useful to generate an UPDATE SQL sentence from a list of 1112 * values. Example code: 1113 * 1114 * <code> 1115 * $values = array (); 1116 * $values['name'] = "Name"; 1117 * $values['description'] = "Long description"; 1118 * $sql = 'UPDATE table SET '.db_format_array_to_update_sql ($values).' WHERE id=1'; 1119 * echo $sql; 1120 * </code> 1121 * Will return: 1122 * <code> 1123 * UPDATE table SET name = "Name", description = "Long description" WHERE id=1 1124 * </code> 1125 * 1126 * @param array Values to be formatted in an array indexed by the field name. 1127 * 1128 * @return string Values joined into an SQL string that can fits into an UPDATE 1129 * sentence. 1130 */ 1131function oracle_db_format_array_to_update_sql ($values) { 1132 $fields = array (); 1133 1134 foreach ($values as $field => $value) { 1135 if (is_numeric($field)) { 1136 array_push ($fields, $value); 1137 continue; 1138 } 1139 else if ($field[0] == "`") { 1140 $field = str_replace('`', '', $field); 1141 } 1142 1143 if ($value === NULL) { 1144 $sql = sprintf ("%s = NULL", $field); 1145 } 1146 elseif (is_int ($value) || is_bool ($value)) { 1147 $sql = sprintf ("%s = %d", $field, $value); 1148 } 1149 elseif (is_float ($value) || is_double ($value)) { 1150 $sql = sprintf ("%s = %f", $field, $value); 1151 } 1152 else { 1153 /* String */ 1154 if (isset ($value[0]) && $value[0] == '`') 1155 /* Don't round with quotes if it references a field */ 1156 $sql = sprintf ("%s = %s", $field, str_replace('`', '', $value)); 1157 else if (substr($value, 0,1) == '#') { 1158 $sql = sprintf ("%s = %s", $field, substr($value,1)); 1159 } 1160 else { 1161 $sql = sprintf ("%s = '%s'", $field, $value); 1162 } 1163 } 1164 array_push ($fields, $sql); 1165 } 1166 1167 return implode (", ", $fields); 1168} 1169 1170/** 1171 * Updates a database record. 1172 * 1173 * All values should be cleaned before passing. Quoting isn't necessary. 1174 * Examples: 1175 * 1176 * <code> 1177 * db_process_sql_update ('table', array ('field' => 1), array ('id' => $id)); 1178 * db_process_sql_update ('table', array ('field' => 1), array ('id' => $id, 'name' => $name)); 1179 * db_process_sql_update ('table', array ('field' => 1), array ('id' => $id, 'name' => $name), 'OR'); 1180 * db_process_sql_update ('table', array ('field' => 2), 'id in (1, 2, 3) OR id > 10'); 1181 * </code> 1182 * 1183 * @param string Table to insert into 1184 * @param array An associative array of values to update 1185 * @param mixed An associative array of field and value matches. Will be joined 1186 * with operator specified by $where_join. A custom string can also be provided. 1187 * If nothing is provided, the update will affect all rows. 1188 * @param string When a $where parameter is given, this will work as the glue 1189 * between the fields. "AND" operator will be use by default. Other values might 1190 * be "OR", "AND NOT", "XOR" 1191 * 1192 * @return mixed False in case of error or invalid values passed. Affected rows otherwise 1193 */ 1194function oracle_db_process_sql_update($table, $values, $where = false, $where_join = 'AND', $autocommit = true) { 1195 $query = sprintf ("UPDATE %s SET %s", 1196 $table, 1197 db_format_array_to_update_sql ($values)); 1198 1199 if ($where) { 1200 if (is_string ($where)) { 1201 // No clean, the caller should make sure all input is clean, this is a raw function 1202 $query .= " WHERE " . $where; 1203 } 1204 else if (is_array ($where)) { 1205 $query .= db_format_array_where_clause_sql ($where, $where_join, ' WHERE '); 1206 } 1207 } 1208 $status = ''; 1209 1210 return db_process_sql ($query, "affected_rows", '', true, $status, $autocommit); 1211} 1212 1213/** 1214 * Delete database records. 1215 * 1216 * All values should be cleaned before passing. Quoting isn't necessary. 1217 * Examples: 1218 * 1219 * <code> 1220 * db_process_sql_delete ('table', array ('id' => 1)); 1221 * // DELETE FROM table WHERE id = 1 1222 * db_process_sql_delete ('table', array ('id' => 1, 'name' => 'example')); 1223 * // DELETE FROM table WHERE id = 1 AND name = 'example' 1224 * db_process_sql_delete ('table', array ('id' => 1, 'name' => 'example'), 'OR'); 1225 * // DELETE FROM table WHERE id = 1 OR name = 'example' 1226 * db_process_sql_delete ('table', 'id in (1, 2, 3) OR id > 10'); 1227 * // DELETE FROM table WHERE id in (1, 2, 3) OR id > 10 1228 * </code> 1229 * 1230 * @param string Table to insert into 1231 * @param array An associative array of values to update 1232 * @param mixed An associative array of field and value matches. Will be joined 1233 * with operator specified by $where_join. A custom string can also be provided. 1234 * If nothing is provided, the update will affect all rows. 1235 * @param string When a $where parameter is given, this will work as the glue 1236 * between the fields. "AND" operator will be use by default. Other values might 1237 * be "OR", "AND NOT", "XOR" 1238 * 1239 * @return mixed False in case of error or invalid values passed. Affected rows otherwise 1240 */ 1241function oracle_db_process_sql_delete($table, $where, $where_join = 'AND') { 1242 if (empty ($where)) 1243 /* Should avoid any mistake that lead to deleting all data */ 1244 return false; 1245 1246 $query = sprintf ("DELETE FROM %s WHERE ", $table); 1247 1248 if ($where) { 1249 if (is_string ($where)) { 1250 /* FIXME: Should we clean the string for sanity? 1251 Who cares if this is deleting data... */ 1252 $query .= $where; 1253 } 1254 else if (is_array ($where)) { 1255 $query .= db_format_array_where_clause_sql ($where, $where_join); 1256 } 1257 } 1258 1259 return db_process_sql ($query); 1260} 1261 1262function oracle_db_process_sql_delete_temp ($table, $where, $where_join = 'AND') { 1263 if (empty ($where)) 1264 /* Should avoid any mistake that lead to deleting all data */ 1265 return false; 1266 1267 $query = sprintf ("DELETE FROM %s WHERE ", $table); 1268 1269 if ($where) { 1270 if (is_string ($where)) { 1271 /* FIXME: Should we clean the string for sanity? 1272 Who cares if this is deleting data... */ 1273 $query .= $where; 1274 } 1275 else if (is_array ($where)) { 1276 $query .= db_format_array_where_clause_sql ($where, $where_join); 1277 } 1278 } 1279 1280 $result = ''; 1281 1282 return db_process_sql ($query, "affected_rows", '', true, $result, false); 1283} 1284 1285 1286/** 1287 * Get row by row the DB by SQL query. The first time pass the SQL query and 1288 * rest of times pass none for iterate in table and extract row by row, and 1289 * the end return false. 1290 * 1291 * @param bool $new Default true, if true start to query. 1292 * @param resource $result The resource of oracle for access to query. 1293 * @param string $sql 1294 * @return mixed The row or false in error. 1295 */ 1296function oracle_db_get_all_row_by_steps_sql($new = true, &$result, $sql = null) { 1297 global $config; 1298 1299 if ($new == true) { 1300 $result = oci_parse($config['dbconnection'], $sql); 1301 oci_execute($result); 1302 } 1303 $row = oci_fetch_assoc($result); 1304 1305 $result_temp = array(); 1306 if ($row) { 1307 foreach ($row as $key => $value) { 1308 $column_type = oci_field_type($result, $key); 1309 // Support for Clob field larger than 4000bytes 1310 if ($column_type == 'CLOB') { 1311 $column_name = oci_field_name($result, $key); 1312 $column_name = oci_field_name($result, $key); 1313 // protect against a NULL CLOB 1314 if (is_object($row[$column_name])) { 1315 $clob_data = $row[$column_name]->load(); 1316 $row[$column_name]->free(); 1317 $value = $clob_data; 1318 } 1319 else { 1320 $value = ''; 1321 } 1322 } 1323 $result_temp[strtolower($key)] = $value; 1324 } 1325 } 1326 1327 if (!$row) { 1328 oci_free_statement($result); 1329 } 1330 1331// return $row; 1332 return $result_temp; 1333} 1334 1335/** 1336 * Starts a database transaction. 1337 */ 1338function oracle_db_process_sql_begin() { 1339 global $config; 1340 1341 $query = oci_parse($config['dbconnection'], 'SET TRANSACTION READ WRITE'); 1342 oci_execute($query); 1343 oci_free_statement($query); 1344} 1345 1346/** 1347 * Commits a database transaction. 1348 */ 1349function oracle_db_process_sql_commit() { 1350 global $config; 1351 1352 oci_commit($config['dbconnection']); 1353} 1354 1355/** 1356 * Rollbacks a database transaction. 1357 */ 1358function oracle_db_process_sql_rollback() { 1359 global $config; 1360 1361 oci_rollback($config['dbconnection']); 1362} 1363 1364/** 1365 * Put quotes if magic_quotes protection 1366 * 1367 * @param string Text string to be protected with quotes if magic_quotes protection is disabled 1368 */ 1369function oracle_safe_sql_string($string) { 1370 if (get_magic_quotes_gpc () == 0) 1371 return $string; 1372 1373 return oracle_escape_string_sql($string); 1374} 1375 1376/** 1377 * Get last error. 1378 * 1379 * @return string Return the string error. 1380 */ 1381function oracle_db_get_last_error() { 1382 global $config; 1383 1384 if (empty($config['oracle_error_query'])) { 1385 return null; 1386 } 1387 1388 $ora_erno = oci_error($config['oracle_error_query']); 1389 1390 return $ora_erno['message']; 1391} 1392 1393/** 1394 * This function gets the time from either system or sql based on preference and returns it 1395 * 1396 * @return int Unix timestamp 1397 */ 1398function oracle_get_system_time() { 1399 global $config; 1400 1401 static $time = 0; 1402 1403 if ($time != 0) 1404 return $time; 1405 1406 if ($config["timesource"] == "sql") { 1407 $time = db_get_sql ("SELECT ceil((sysdate - to_date('19700101000000','YYYYMMDDHH24MISS')) * (" . SECONDS_1DAY . ")) as dt FROM dual"); 1408 if (empty ($time)) { 1409 return time (); 1410 } 1411 return $time; 1412 } 1413 else { 1414 return time (); 1415 } 1416} 1417 1418/** 1419 * Get the type of field. 1420 * 1421 * @param string $table The table to examine the type of field. 1422 * @param integer $field The field order in table. 1423 * 1424 * @return mixed Return the type name or False in error case. 1425 */ 1426function oracle_db_get_type_field_table($table, $field) { 1427 global $config; 1428 1429 $query = oci_parse($config['dbconnection'], 1430 "SELECT * FROM " . $table . " WHERE rownum < 2"); 1431 oci_execute($query); 1432 1433 $type = oci_field_type($query, $field+1); 1434 oci_free_statement($query); 1435 1436 return $type; 1437} 1438 1439/** 1440 * Get all field names of a table and recode fields 1441 * for clob datatype as "dbms_lob.substr(<field>, 4000 ,1) as <field>". 1442 * 1443 * @param string $table The table to retrieve all column names. 1444 * @param integer $return_mode Whether to return as array (by default) or as comma separated string. 1445 * 1446 * @return mixed Return an array/string of table fields or false if something goes wrong. 1447 */ 1448function oracle_list_all_field_table($table_name, $return_mode = 'array') { 1449 if (empty($table_name)) { 1450 return false; 1451 } 1452 1453 $fields_info = db_get_all_rows_field_filter('user_tab_columns', 'table_name', strtoupper($table_name)); 1454 if (empty($fields_info)) { 1455 return false; 1456 } 1457 $field_list = array(); 1458 foreach ($fields_info as $field) { 1459 if ($field['data_type'] == 'CLOB') { 1460 $new_field = 'dbms_lob.substr(' . $field['table_name'] . '.' . $field['column_name'] . ', 4000, 1) as ' . strtolower($field['column_name']); 1461 $field_list[] = $new_field; 1462 } 1463 else { 1464 $field_list[] = strtolower($field['table_name'] . '.' . $field['column_name']); 1465 } 1466 } 1467 // Return as comma separated string 1468 if ($return_mode == 'string') { 1469 return implode(',', $field_list); 1470 } 1471 // Return as array 1472 else { 1473 return $field_list; 1474 } 1475} 1476 1477/** 1478 * Get the element count of a table. 1479 * 1480 * @param string $sql SQL query to get the element count. 1481 * 1482 * @return int Return the number of elements in the table. 1483 */ 1484function oracle_db_get_table_count($sql, $search_history_db = false) { 1485 global $config; 1486 1487 $history_count = 0; 1488 $count = oracle_db_get_value_sql ($sql); 1489 if ($count === false) { 1490 $count = 0; 1491 } 1492 1493 // Search the history DB for matches 1494 if ($search_history_db && $config['history_db_enabled'] == 1) { 1495 1496 // Connect to the history DB 1497 if (! isset ($config['history_db_connection']) || $config['history_db_connection'] === false) { 1498 $config['history_db_connection'] = oracle_connect_db ($config['history_db_host'], $config['history_db_name'], $config['history_db_user'], io_output_password($config['history_db_pass']), $config['history_db_port'], false); 1499 } 1500 if ($config['history_db_connection'] !== false) { 1501 $history_count = oracle_db_get_value_sql ($sql, $config['history_db_connection']); 1502 if ($history_count === false) { 1503 $history_count = 0; 1504 } 1505 } 1506 } 1507 1508 $count += $history_count; 1509 1510 return $count; 1511} 1512 1513/** 1514 * Process a file with an oracle schema sentences. 1515 * Based on the function which installs the pandoradb.sql schema. 1516 * 1517 * @param string $path File path. 1518 * @param bool $handle_error Whether to handle the oci_execute errors or throw an exception. 1519 * 1520 * @return bool Return the final status of the operation. 1521 */ 1522function oracle_db_process_file ($path, $handle_error = true) { 1523 global $config; 1524 1525 if (file_exists($path)) { 1526 $file_content = file($path); 1527 1528 $query = ""; 1529 $plsql_block = false; 1530 1531 // Begin the transaction 1532 oracle_db_process_sql_begin(); 1533 1534 $datetime_tz_format = oci_parse($connection, 'alter session set NLS_TIMESTAMP_TZ_FORMAT =\'YYYY-MM-DD HH24:MI:SS\''); 1535 $datetime_format = oci_parse($connection, 'alter session set NLS_TIMESTAMP_FORMAT =\'YYYY-MM-DD HH24:MI:SS\''); 1536 $date_format = oci_parse($connection, 'alter session set NLS_DATE_FORMAT =\'YYYY-MM-DD HH24:MI:SS\''); 1537 $decimal_separator = oci_parse($connection, 'alter session set NLS_NUMERIC_CHARACTERS =\',.\''); 1538 1539 oci_execute($datetime_tz_format); 1540 oci_execute($datetime_format); 1541 oci_execute($date_format); 1542 oci_execute($decimal_separator); 1543 1544 oci_free_statement($datetime_tz_format); 1545 oci_free_statement($datetime_format); 1546 oci_free_statement($date_format); 1547 oci_free_statement($decimal_separator); 1548 1549 foreach ($file_content as $sql_line) { 1550 $clean_line = trim($sql_line); 1551 $comment = preg_match("/^(\s|\t)*--.*$/", $clean_line); 1552 if ($comment) { 1553 continue; 1554 } 1555 1556 if (empty($clean_line)) { 1557 continue; 1558 } 1559 1560 //Support for PL/SQL blocks 1561 if (preg_match("/^BEGIN$/", $clean_line)) { 1562 $query .= $clean_line . ' '; 1563 $plsql_block = true; 1564 } 1565 else{ 1566 $query .= $clean_line; 1567 } 1568 1569 //Check query's end with a back slash and any returns in the end of line or if it's a PL/SQL block 'END;;' string 1570 if ((preg_match("/;[\040]*\$/", $clean_line) && !$plsql_block) || 1571 (preg_match("/^END;;[\040]*\$/", $clean_line) && $plsql_block)) { 1572 $plsql_block = false; 1573 //Execute and clean buffer 1574 1575 //Delete the last semicolon from current query 1576 $query = substr($query, 0, strlen($query) - 1); 1577 $sql = oci_parse($config['dbconnection'], $query); 1578 $result = oci_execute($sql, OCI_NO_AUTO_COMMIT); 1579 1580 if (!$result) { 1581 // Error. Rollback the transaction 1582 oracle_db_process_sql_rollback(); 1583 1584 $e = oci_error($sql); 1585 1586 // Handle the error 1587 if ($handle_error) { 1588 $backtrace = debug_backtrace(); 1589 $error = sprintf('%s (\'%s\') in <strong>%s</strong> on line %d', 1590 htmlentities($e['message'], ENT_QUOTES), $query, $backtrace[0]['file'], $backtrace[0]['line']); 1591 db_add_database_debug_trace ($query, htmlentities($e['message'], ENT_QUOTES)); 1592 set_error_handler('db_sql_error_handler'); 1593 trigger_error($error); 1594 restore_error_handler(); 1595 1596 return false; 1597 } 1598 // Throw an exception with the error message 1599 else { 1600 throw new Exception($e['message']); 1601 } 1602 } 1603 1604 $query = ""; 1605 oci_free_statement($sql); 1606 } 1607 } 1608 1609 // No errors. Commit the transaction 1610 oracle_db_process_sql_commit(); 1611 1612 return true; 1613 } 1614 else { 1615 return false; 1616 } 1617} 1618 1619function oracle_format_float_to_php($val) { 1620 return floatval(str_replace(',', '.', $val)); 1621} 1622?> 1623