KumbiaPHP beta2-dev
Framework PHP en español
|
00001 <?php 00023 class DbPgSQL extends DbBase implements DbBaseInterface { 00029 public $id_connection; 00030 00036 public $last_result_query; 00037 00043 private $last_query; 00044 00050 public $last_error; 00051 00056 const DB_ASSOC = PGSQL_ASSOC; 00057 00062 const DB_BOTH = PGSQL_BOTH; 00063 00068 const DB_NUM = PGSQL_NUM; 00069 00070 00075 const TYPE_INTEGER = 'INTEGER'; 00076 00081 const TYPE_DATE = 'DATE'; 00082 00087 const TYPE_VARCHAR = 'VARCHAR'; 00088 00093 const TYPE_DECIMAL = 'DECIMAL'; 00094 00099 const TYPE_DATETIME = 'DATETIME'; 00100 00105 const TYPE_CHAR = 'CHAR'; 00112 public function connect($config){ 00113 00114 if(!extension_loaded('pgsql')){ 00115 throw new KumbiaException('Debe cargar la extensión de PHP llamada php_pgsql'); 00116 } 00117 00118 if(!isset($config['port']) || !$config['port']) { 00119 $config['port'] = 5432; 00120 } 00121 00122 if($this->id_connection = pg_connect("host={$config['host']} user={$config['username']} password={$config['password']} dbname={$config['name']} port={$config['port']}", PGSQL_CONNECT_FORCE_NEW)){ 00123 return true; 00124 } else { 00125 throw new KumbiaException($this->error("No se puede conectar a la base de datos")); 00126 } 00127 } 00128 00135 function query($sqlQuery){ 00136 $this->debug($sqlQuery); 00137 if($this->logger){ 00138 Logger::debug($sqlQuery); 00139 } 00140 if(!$this->id_connection){ 00141 $this->connect(); 00142 if(!$this->id_connection){ 00143 return false; 00144 } 00145 } 00146 $this->last_query = $sqlQuery; 00147 if($resultQuery = @pg_query($this->id_connection, $sqlQuery)){ 00148 $this->last_result_query = $resultQuery; 00149 return $resultQuery; 00150 } else { 00151 throw new KumbiaException($this->error(" al ejecutar <em>'$sqlQuery'</em>")); 00152 } 00153 } 00154 00158 function close(){ 00159 if($this->id_connection) { 00160 return pg_close($this->id_connection); 00161 } else { 00162 return false; 00163 } 00164 } 00165 00173 function fetch_array($resultQuery='', $opt=PGSQL_BOTH){ 00174 if(!$this->id_connection){ 00175 return false; 00176 } 00177 if(!$resultQuery){ 00178 $resultQuery = $this->last_result_query; 00179 if(!$resultQuery){ 00180 return false; 00181 } 00182 } 00183 return pg_fetch_array($resultQuery, NULL, $opt); 00184 } 00185 00191 function __construct($config){ 00192 $this->connect($config); 00193 } 00194 00198 function num_rows($resultQuery=''){ 00199 if(!$this->id_connection){ 00200 return false; 00201 } 00202 if(!$resultQuery){ 00203 $resultQuery = $this->last_result_query; 00204 if(!$resultQuery){ 00205 return false; 00206 } 00207 } 00208 if(($numberRows = pg_num_rows($resultQuery))!==false){ 00209 return $numberRows; 00210 } else { 00211 throw new KumbiaException($this->error()); 00212 } 00213 return false; 00214 } 00215 00223 function field_name($number, $resultQuery=''){ 00224 if(!$this->id_connection){ 00225 return false; 00226 } 00227 if(!$resultQuery){ 00228 $resultQuery = $this->last_result_query; 00229 if(!$resultQuery){ 00230 return false; 00231 } 00232 } 00233 if(($fieldName = pg_field_name($resultQuery, $number))!==false){ 00234 return $fieldName; 00235 } else { 00236 throw new KumbiaException($this->error()); 00237 } 00238 return false; 00239 } 00240 00241 00249 function data_seek($number, $resultQuery=''){ 00250 if(!$resultQuery){ 00251 $resultQuery = $this->last_result_query; 00252 if(!$resultQuery){ 00253 return false; 00254 } 00255 } 00256 if(($success = pg_result_seek($resultQuery, $number))!==false){ 00257 return $success; 00258 } else { 00259 throw new KumbiaException($this->error()); 00260 } 00261 return false; 00262 } 00263 00270 function affected_rows($resultQuery=''){ 00271 if(!$this->id_connection){ 00272 return false; 00273 } 00274 if(!$resultQuery){ 00275 $resultQuery = $this->last_result_query; 00276 if(!$resultQuery){ 00277 return false; 00278 } 00279 } 00280 if(($numberRows = pg_affected_rows($resultQuery))!==false){ 00281 return $numberRows; 00282 } else { 00283 throw new KumbiaException($this->error()); 00284 } 00285 return false; 00286 } 00287 00293 function error($err=''){ 00294 if(!$this->id_connection){ 00295 $this->last_error = @pg_last_error() ? @pg_last_error().$err : "[Error Desconocido en PostgreSQL \"$err\"]"; 00296 if($this->logger){ 00297 Logger::error($this->last_error); 00298 } 00299 return $this->last_error; 00300 } 00301 $this->last_error = @pg_last_error() ? @pg_last_error().$err : "[Error Desconocido en PostgreSQL: $err]"; 00302 $this->last_error.= $err; 00303 if($this->logger){ 00304 Logger::error($this->last_error); 00305 } 00306 return pg_last_error($this->id_connection).$err; 00307 } 00308 00314 function no_error(){ 00315 if(!$this->id_connection){ 00316 return false; 00317 } 00318 return "0"; //Codigo de Error? 00319 } 00320 00326 public function last_insert_id($table='', $primary_key=''){ 00327 if(!$this->id_connection){ 00328 return false; 00329 } 00330 $last_id = $this->fetch_one("SELECT CURRVAL('{$table}_{$primary_key}_seq')"); 00331 return $last_id[0]; 00332 } 00333 00340 function table_exists($table, $schema=''){ 00341 $table = addslashes(strtolower($table)); 00342 if(strpos($table, ".")){ 00343 list($schema, $table) = explode(".", $table); 00344 } 00345 if($schema==''){ 00346 $num = $this->fetch_one("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'public' AND TABLE_NAME ='$table'"); 00347 } else { 00348 $schema = addslashes(strtolower($schema)); 00349 $num = $this->fetch_one("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$schema' AND TABLE_NAME ='$table'"); 00350 } 00351 return $num[0]; 00352 } 00353 00360 public function limit($sql){ 00361 $params = Util::getParams(func_get_args()); 00362 $sql_new = $sql; 00363 00364 if(isset($params['limit']) && is_numeric($params['limit'])){ 00365 $sql_new.=" LIMIT $params[limit]"; 00366 } 00367 00368 if(isset($params['offset']) && is_numeric($params['offset'])){ 00369 $sql_new.=" OFFSET $params[offset]"; 00370 } 00371 00372 return $sql_new; 00373 } 00374 00381 public function drop_table($table, $if_exists=true){ 00382 if($if_exists){ 00383 if($this->table_exists($table)){ 00384 return $this->query("DROP TABLE $table"); 00385 } else { 00386 return true; 00387 } 00388 } else { 00389 return $this->query("DROP TABLE $table"); 00390 } 00391 } 00392 00406 public function create_table($table, $definition, $index=array()){ 00407 $create_sql = "CREATE TABLE $table ("; 00408 if(!is_array($definition)){ 00409 throw new KumbiaException("Definición invalida para crear la tabla '$table'"); 00410 } 00411 $create_lines = array(); 00412 $index = array(); 00413 $unique_index = array(); 00414 $primary = array(); 00415 $not_null = ""; 00416 $size = ""; 00417 foreach($definition as $field => $field_def){ 00418 if(isset($field_def['not_null'])){ 00419 $not_null = $field_def['not_null'] ? 'NOT NULL' : ''; 00420 } else { 00421 $not_null = ""; 00422 } 00423 if(isset($field_def['size'])){ 00424 $size = $field_def['size'] ? '('.$field_def['size'].')' : ''; 00425 } else { 00426 $size = ""; 00427 } 00428 if(isset($field_def['index'])){ 00429 if($field_def['index']){ 00430 $index[] = "INDEX($field)"; 00431 } 00432 } 00433 if(isset($field_def['unique_index'])){ 00434 if($field_def['unique_index']){ 00435 $index[] = "UNIQUE($field)"; 00436 } 00437 } 00438 if(isset($field_def['primary'])){ 00439 if($field_def['primary']){ 00440 $primary[] = "$field"; 00441 } 00442 } 00443 if(isset($field_def['auto'])){ 00444 if($field_def['auto']){ 00445 $field_def['type'] = "SERIAL"; 00446 } 00447 } 00448 if(isset($field_def['extra'])){ 00449 $extra = $field_def['extra']; 00450 } else { 00451 $extra = ""; 00452 } 00453 $create_lines[] = "$field ".$field_def['type'].$size.' '.$not_null.' '.$extra; 00454 } 00455 $create_sql.= join(',', $create_lines); 00456 $last_lines = array(); 00457 if(count($primary)){ 00458 $last_lines[] = 'PRIMARY KEY('.join(",", $primary).')'; 00459 } 00460 if(count($index)){ 00461 $last_lines[] = join(',', $index); 00462 } 00463 if(count($unique_index)){ 00464 $last_lines[] = join(',', $unique_index); 00465 } 00466 if(count($last_lines)){ 00467 $create_sql.= ','.join(',', $last_lines).')'; 00468 } 00469 return $this->query($create_sql); 00470 00471 } 00472 00478 public function list_tables(){ 00479 return $this->fetch_all("SELECT c.relname AS table FROM pg_class c, pg_user u " 00480 ."WHERE c.relowner = u.usesysid AND c.relkind = 'r' " 00481 ."AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) " 00482 ."AND c.relname !~ '^(pg_|sql_)' UNION " 00483 ."SELECT c.relname AS table_name FROM pg_class c " 00484 ."WHERE c.relkind = 'r' " 00485 ."AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) " 00486 ."AND NOT EXISTS (SELECT 1 FROM pg_user WHERE usesysid = c.relowner) " 00487 ."AND c.relname !~ '^pg_'"); 00488 } 00489 00496 public function describe_table($table, $schema=''){ 00497 $describe = $this->fetch_all("SELECT a.attname AS Field, t.typname AS Type, 00498 CASE WHEN attnotnull=false THEN 'YES' ELSE 'NO' END AS Null, 00499 CASE WHEN (select cc.contype FROM pg_catalog.pg_constraint cc WHERE 00500 cc.conrelid = c.oid AND cc.conkey[1] = a.attnum limit 1)='p' THEN 'PRI' ELSE '' 00501 END AS Key FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a, 00502 pg_catalog.pg_type t WHERE c.relname = '$table' AND c.oid = a.attrelid 00503 AND a.attnum > 0 AND t.oid = a.atttypid order by a.attnum"); 00504 $final_describe = array(); 00505 foreach($describe as $key => $value){ 00506 $final_describe[] = array( 00507 "Field" => $value["field"], 00508 "Type" => $value["type"], 00509 "Null" => $value["null"], 00510 "Key" => $value["key"] 00511 ); 00512 } 00513 return $final_describe; 00514 } 00515 00523 public function fetch_object($queryResult=null, $class='stdClass') 00524 { 00525 if(!$queryResult){ 00526 $queryResult = $this->last_result_query; 00527 } 00528 return pg_fetch_object($queryResult, null, $class); 00529 } 00530 }