00001 <?php
00023 class DbOracle extends DbBase implements DbBaseInterface {
00024
00030 public $id_connection;
00031
00037 public $last_result_query;
00038
00044 private $last_query;
00050 public $lastError;
00051
00057 private $autocommit = false;
00058
00064 private $num_rows = false;
00065
00070 const DB_ASSOC = OCI_ASSOC;
00071
00072
00077 const DB_BOTH = OCI_BOTH;
00078
00083 const DB_NUM = OCI_NUM;
00084
00089 const TYPE_INTEGER = 'INTEGER';
00090
00095 const TYPE_DATE = 'DATE';
00096
00101 const TYPE_VARCHAR = 'VARCHAR2';
00102
00107 const TYPE_DECIMAL = 'DECIMAL';
00108
00113 const TYPE_DATETIME = 'DATETIME';
00114
00119 const TYPE_CHAR = 'CHAR';
00120
00127 function connect($config){
00128
00129 if(!extension_loaded('oci8')){
00130 throw new KumbiaException('Debe cargar la extensión de PHP llamada php_oci8');
00131 return false;
00132 }
00133
00134 if($this->id_connection = @oci_pconnect($config['username'], $config['password'], "//{$config['host']}/{$config['name']}")){
00138 $this->query("alter session set nls_date_format = 'YYYY-MM-DD'");
00139 return true;
00140 } else {
00141 throw new KumbiaException($this->error($php_errormsg), false);
00142 }
00143 }
00144
00151 function query($sqlQuery){
00152 $this->debug($sqlQuery);
00153 if($this->logger){
00154 Logger::debug($sqlQuery);
00155 }
00156 if(!$this->id_connection){
00157 $this->connect();
00158 if(!$this->id_connection){
00159 return false;
00160 }
00161 }
00162 $this->num_rows = false;
00163 $this->lastQuery = $sqlQuery;
00164 $resultQuery = @oci_parse($this->id_connection, $sqlQuery);
00165 if($resultQuery){
00166 $this->last_result_query = $resultQuery;
00167 } else {
00168 $this->last_result_query = false;
00169 throw new KumbiaException($this->error($php_errormsg), $this->no_error());
00170 return false;
00171 }
00172 if($this->autocommit){
00173 $commit = OCI_COMMIT_ON_SUCCESS;
00174 } else {
00175 $commit = OCI_DEFAULT;
00176 }
00177
00178 if(!@oci_execute($resultQuery, $commit)){
00179 $this->last_result_query = false;
00180 throw new KumbiaException($this->error($php_errormsg), $this->no_error());
00181 return false;
00182 }
00183 return $resultQuery;
00184 }
00185
00189 function close(){
00190 if($this->id_connection) {
00191 return oci_close($this->id_connection);
00192 }
00193 }
00194
00202 function fetch_array($resultQuery='', $opt=''){
00203 if($opt==='') $opt = db::DB_BOTH;
00204 if(!$this->id_connection){
00205 return false;
00206 }
00207 if(!$resultQuery){
00208 $resultQuery = $this->last_result_query;
00209 if(!$resultQuery){
00210 return false;
00211 }
00212 }
00213 $result = oci_fetch_array($resultQuery, $opt);
00214 if(is_array($result)){
00215 $result_to_lower = array();
00216 foreach($result as $key => $value){
00217 $result_to_lower[strtolower($key)] = $value;
00218 }
00219 return $result_to_lower;
00220 } else {
00221 return false;
00222 }
00223 return false;
00224 }
00225
00231 function __construct($config){
00232 $this->connect($config);
00233 }
00234
00238 function num_rows($resultQuery=''){
00239 if(!$this->id_connection){
00240 return false;
00241 }
00242 if(!$resultQuery){
00243 $resultQuery = $this->last_result_query;
00244 if(!$resultQuery){
00245 throw new KumbiaException($this->error('Resource invalido para db::num_rows'), $this->no_error());
00246 return false;
00247 }
00248 }
00252
00253
00254
00255
00256
00257 if($this->autocommit){
00258 $commit = OCI_COMMIT_ON_SUCCESS;
00259 } else {
00260 $commit = OCI_DEFAULT;
00261 }
00262 if(!@oci_execute($resultQuery, $commit)){
00263 $this->last_result_query = false;
00264 throw new KumbiaException($this->error($php_errormsg." al ejecutar <i>'{$this->lastQuery}'</i>"), $this->no_error());
00265 return false;
00266 }
00267 $tmp = array();
00268 $this->num_rows = oci_fetch_all($resultQuery, $tmp);
00269 unset($tmp);
00270 @oci_execute($resultQuery, $commit);
00271 return $this->num_rows;
00272 }
00273
00281 function field_name($number, $resultQuery=''){
00282 if(!$this->id_connection){
00283 return false;
00284 }
00285 if(!$resultQuery){
00286 $resultQuery = $this->last_result_query;
00287 if(!$resultQuery){
00288 throw new KumbiaException($this->error('Resource invalido para db::field_name'), $this->no_error());
00289 return false;
00290 }
00291 }
00292
00293 if(($fieldName = oci_field_name($resultQuery, $number+1))!==false){
00294 return strtolower($fieldName);
00295 } else {
00296 throw new KumbiaException($this->error(), $this->no_error());
00297 return false;
00298 }
00299 return false;
00300 }
00301
00302
00310 function data_seek($number, $resultQuery=''){
00311 if(!$resultQuery){
00312 $resultQuery = $this->last_result_query;
00313 if(!$resultQuery){
00314 throw new KumbiaException($this->error('Resource invalido para db::data_seek'), $this->no_error());
00315 return false;
00316 }
00317 }
00318 if($this->autocommit){
00319 $commit = OCI_COMMIT_ON_SUCCESS;
00320 } else {
00321 $commit = OCI_DEFAULT;
00322 }
00323 if(!@oci_execute($resultQuery, $commit)){
00324 throw new KumbiaException($this->error($php_errormsg." al ejecutar <i>'{$this->lastQuery}'</i>"), $this->no_error());
00325 return false;
00326 }
00327 if($number){
00328 for($i=0;$i<=$number-1;$i++){
00329 if(!oci_fetch_row($resultQuery)){
00330 return false;
00331 }
00332 }
00333 } else {
00334 return true;
00335 }
00336 return true;
00337 }
00338
00345 function affected_rows($resultQuery=''){
00346 if(!$this->id_connection){
00347 return false;
00348 }
00349 if(!$resultQuery){
00350 $resultQuery = $this->last_result_query;
00351 if(!$resultQuery){
00352 return false;
00353 }
00354 }
00355 if(($numberRows = oci_num_rows($resultQuery))!==false){
00356 return $numberRows;
00357 } else {
00358 throw new KumbiaException($this->error('Resource invalido para db::affected_rows'), $this->no_error());
00359 return false;
00360 }
00361 return false;
00362 }
00363
00369 function error($err=''){
00370 if(!$this->id_connection){
00371 $error = oci_error() ? oci_error() : "[Error Desconocido en Oracle]";
00372 if(is_array($error)){
00373 $error['message'].=" > $err ";
00374 return $error['message'];
00375 } else {
00376 $error.=" $php_errormsg ";
00377 return $error;
00378 }
00379 }
00380 $error = oci_error($this->id_connection);
00381 if($error){
00382 $error['message'].=" > $err ";
00383 } else {
00384 $error['message'] = $err;
00385 }
00386 return $error['message'];
00387 }
00388
00394 function no_error(){
00395 if(!$this->id_connection){
00396 $error = oci_error() ? oci_error() : "0";
00397 if(is_array($error)){
00398 return $error['code'];
00399 } else {
00400 return $error;
00401 }
00402 }
00403 $error = oci_error($this->id_connection);
00404 return $error['code'];
00405 }
00406
00413 public function limit($sql, $number){
00414 if(!is_numeric($number)||$number<0){
00415 return $sql;
00416 }
00417 if(eregi("ORDER[\t\n\r ]+BY", $sql)){
00418 if(stripos($sql, "WHERE")){
00419 return eregi_replace("ORDER[\t\n\r ]+BY", "AND ROWNUM <= $number ORDER BY", $sql);
00420 } else {
00421 return eregi_replace("ORDER[\t\n\r ]+BY", "WHERE ROWNUM <= $number ORDER BY", $sql);
00422 }
00423 } else {
00424 if(stripos($sql, "WHERE")){
00425 return "$sql AND ROWNUM <= $number";
00426 } else {
00427 return "$sql WHERE ROWNUM <= $number";
00428 }
00429 }
00430 }
00431
00438 public function drop_table($table, $if_exists=true){
00439 if($if_exists){
00440 if($this->table_exists($table)){
00441 return $this->query("DROP TABLE $table");
00442 } else {
00443 return true;
00444 }
00445 } else {
00446 return $this->query("DROP TABLE $table");
00447 }
00448 }
00449
00463 public function create_table($table, $definition, $index=array()){
00464 $create_sql = "CREATE TABLE $table (";
00465 if(!is_array($definition)){
00466 new KumbiaException("Definición invalida para crear la tabla '$table'");
00467 return false;
00468 }
00469 $create_lines = array();
00470 $index = array();
00471 $unique_index = array();
00472 $primary = array();
00473 $not_null = "";
00474 $size = "";
00475 foreach($definition as $field => $field_def){
00476 if(isset($field_def['not_null'])){
00477 $not_null = $field_def['not_null'] ? 'NOT NULL' : '';
00478 } else {
00479 $not_null = "";
00480 }
00481 if(isset($field_def['size'])){
00482 $size = $field_def['size'] ? '('.$field_def['size'].')' : '';
00483 } else {
00484 $size = "";
00485 }
00486 if(isset($field_def['index'])){
00487 if($field_def['index']){
00488 $index[] = "INDEX($field)";
00489 }
00490 }
00491 if(isset($field_def['unique_index'])){
00492 if($field_def['unique_index']){
00493 $index[] = "UNIQUE($field)";
00494 }
00495 }
00496 if(isset($field_def['primary'])){
00497 if($field_def['primary']){
00498 $primary[] = "$field";
00499 }
00500 }
00501 if(isset($field_def['auto'])){
00502 if($field_def['auto']){
00503 $this->query("CREATE SEQUENCE {$table}_{$field}_seq START WITH 1");
00504 }
00505 }
00506 if(isset($field_def['extra'])){
00507 $extra = $field_def['extra'];
00508 } else {
00509 $extra = "";
00510 }
00511 $create_lines[] = "$field ".$field_def['type'].$size.' '.$not_null.' '.$extra;
00512 }
00513 $create_sql.= join(',', $create_lines);
00514 $last_lines = array();
00515 if(count($primary)){
00516 $last_lines[] = 'PRIMARY KEY('.join(",", $primary).')';
00517 }
00518 if(count($index)){
00519 $last_lines[] = join(',', $index);
00520 }
00521 if(count($unique_index)){
00522 $last_lines[] = join(',', $unique_index);
00523 }
00524 if(count($last_lines)){
00525 $create_sql.= ','.join(',', $last_lines).')';
00526 }
00527 return $this->query($create_sql);
00528
00529 }
00530
00537 function list_tables(){
00538 return $this->fetch_all("SELECT table_name FROM all_tables");
00539 }
00540
00546 public function last_insert_id($table='', $primary_key=''){
00547 if(!$this->id_connection){
00548 return false;
00549 }
00553 if($table&&$primary_key){
00554 $sequence = $table."_".$primary_key."_seq";
00555 $value = $this->fetch_one("SELECT $sequence.CURRVAL FROM dual");
00556 return $value[0];
00557 }
00558 return false;
00559 }
00560
00567 function table_exists($table, $schema=''){
00568 $num = $this->fetch_one("SELECT COUNT(*) FROM ALL_TABLES WHERE TABLE_NAME = '".strtoupper($table)."'");
00569 return $num[0];
00570 }
00571
00578 public function describe_table($table, $schema=''){
00582 $describe = $this->fetch_all("SELECT LOWER(ALL_TAB_COLUMNS.COLUMN_NAME) AS FIELD, LOWER(ALL_TAB_COLUMNS.DATA_TYPE) AS TYPE, ALL_TAB_COLUMNS.DATA_LENGTH AS LENGTH, (SELECT COUNT(*) FROM ALL_CONS_COLUMNS WHERE TABLE_NAME = '".strtoupper($table)."' AND ALL_CONS_COLUMNS.COLUMN_NAME = ALL_TAB_COLUMNS.COLUMN_NAME AND ALL_CONS_COLUMNS.POSITION IS NOT NULL) AS KEY, ALL_TAB_COLUMNS.NULLABLE AS ISNULL FROM ALL_TAB_COLUMNS WHERE ALL_TAB_COLUMNS.TABLE_NAME = '".strtoupper($table)."'");
00583 $final_describe = array();
00584 foreach($describe as $key => $value){
00585 $final_describe[] = array(
00586 "Field" => $value["field"],
00587 "Type" => $value["type"],
00588 "Null" => $value["isnull"] == "Y" ? "YES" : "NO",
00589 "Key" => $value["key"] == 1 ? "PRI" : ""
00590 );
00591 }
00592 return $final_describe;
00593 }
00594
00599 public function begin(){
00600
00601
00602 return true;
00603 }
00604
00605 }
00606
00607 ?>