00001 <?php
00023 class DbPgSQL extends DbBase implements DbBaseInterface {
00024
00030 public $id_connection;
00031
00037 public $last_result_query;
00038
00044 private $last_query;
00045
00051 public $last_error;
00052
00057 const DB_ASSOC = PGSQL_ASSOC;
00058
00063 const DB_BOTH = PGSQL_BOTH;
00064
00069 const DB_NUM = PGSQL_NUM;
00070
00071
00076 const TYPE_INTEGER = 'INTEGER';
00077
00082 const TYPE_DATE = 'DATE';
00083
00088 const TYPE_VARCHAR = 'VARCHAR';
00089
00094 const TYPE_DECIMAL = 'DECIMAL';
00095
00100 const TYPE_DATETIME = 'DATETIME';
00101
00106 const TYPE_CHAR = 'CHAR';
00113 public function connect($config){
00114
00115 if(!extension_loaded('pgsql')){
00116 throw new KumbiaException('Debe cargar la extensión de PHP llamada php_pgsql');
00117 return false;
00118 }
00119
00120 if(!isset($config['port']) || !$config['port']) {
00121 $config['port'] = 5432;
00122 }
00123
00124 if($this->id_connection = pg_connect("host={$config['host']} user={$config['username']} password={$config['password']} dbname={$config['name']} port={$config['port']}")){
00125 return true;
00126 } else {
00127 throw new KumbiaException($this->error("No se puede conectar a la base de datos"), $this->no_error(), false);
00128 }
00129 }
00130
00137 function query($sqlQuery){
00138 $this->debug($sqlQuery);
00139 if($this->logger){
00140 Logger::debug($sqlQuery);
00141 }
00142 if(!$this->id_connection){
00143 $this->connect();
00144 if(!$this->id_connection){
00145 return false;
00146 }
00147 }
00148 $this->last_query = $sqlQuery;
00149 if($resultQuery = @pg_query($this->id_connection, $sqlQuery)){
00150 $this->last_result_query = $resultQuery;
00151 return $resultQuery;
00152 } else {
00153 throw new KumbiaException($this->error(" al ejecutar <i>'$sqlQuery'</i>"), $this->no_error());
00154 return false;
00155 }
00156 }
00157
00161 function close(){
00162 if($this->id_connection) {
00163 return pg_close($this->id_connection);
00164 } else {
00165 return false;
00166 }
00167 }
00168
00176 function fetch_array($resultQuery='', $opt=''){
00177 if($opt==='') $opt = db::DB_BOTH;
00178 if(!$this->id_connection){
00179 return false;
00180 }
00181 if(!$resultQuery){
00182 $resultQuery = $this->last_result_query;
00183 if(!$resultQuery){
00184 return false;
00185 }
00186 }
00187 return pg_fetch_array($resultQuery, NULL, $opt);
00188 }
00189
00195 function __construct($config){
00196 $this->connect($config);
00197 }
00198
00202 function num_rows($resultQuery=''){
00203 if(!$this->id_connection){
00204 return false;
00205 }
00206 if(!$resultQuery){
00207 $resultQuery = $this->last_result_query;
00208 if(!$resultQuery){
00209 return false;
00210 }
00211 }
00212 if(($numberRows = pg_num_rows($resultQuery))!==false){
00213 return $numberRows;
00214 } else {
00215 throw new KumbiaException($this->error(), $this->no_error());
00216 return false;
00217 }
00218 return false;
00219 }
00220
00228 function field_name($number, $resultQuery=''){
00229 if(!$this->id_connection){
00230 return false;
00231 }
00232 if(!$resultQuery){
00233 $resultQuery = $this->last_result_query;
00234 if(!$resultQuery){
00235 return false;
00236 }
00237 }
00238 if(($fieldName = pg_field_name($resultQuery, $number))!==false){
00239 return $fieldName;
00240 } else {
00241 throw new KumbiaException($this->error(), $this->no_error());
00242 return false;
00243 }
00244 return false;
00245 }
00246
00247
00255 function data_seek($number, $resultQuery=''){
00256 if(!$resultQuery){
00257 $resultQuery = $this->last_result_query;
00258 if(!$resultQuery){
00259 return false;
00260 }
00261 }
00262 if(($success = pg_result_seek($resultQuery, $number))!==false){
00263 return $success;
00264 } else {
00265 throw new KumbiaException($this->error(), $this->no_error());
00266 return false;
00267 }
00268 return false;
00269 }
00270
00277 function affected_rows($resultQuery=''){
00278 if(!$this->id_connection){
00279 return false;
00280 }
00281 if(!$resultQuery){
00282 $resultQuery = $this->last_result_query;
00283 if(!$resultQuery){
00284 return false;
00285 }
00286 }
00287 if(($numberRows = pg_affected_rows($resultQuery))!==false){
00288 return $numberRows;
00289 } else {
00290 throw new KumbiaException($this->error(), $this->no_error());
00291 return false;
00292 }
00293 return false;
00294 }
00295
00301 function error($err=''){
00302 if(!$this->id_connection){
00303 $this->last_error = @pg_last_error() ? @pg_last_error().$err : "[Error Desconocido en PostgreSQL \"$err\"]";
00304 if($this->logger){
00305 Logger::error($this->last_error);
00306 }
00307 return $this->last_error;
00308 }
00309 $this->last_error = @pg_last_error() ? @pg_last_error().$err : "[Error Desconocido en PostgreSQL: $err]";
00310 $this->last_error.= $err;
00311 if($this->logger){
00312 Logger::error($this->last_error);
00313 }
00314 return pg_last_error($this->id_connection).$err;
00315 }
00316
00322 function no_error(){
00323 if(!$this->id_connection){
00324 return false;
00325 }
00326 return "0";
00327 }
00328
00334 public function last_insert_id($table='', $primary_key=''){
00335 if(!$this->id_connection){
00336 return false;
00337 }
00338 $last_id = $this->fetch_one("SELECT CURRVAL('{$table}_{$primary_key}_seq')");
00339 return $last_id[0];
00340 }
00341
00348 function table_exists($table, $schema=''){
00349 $table = addslashes(strtolower($table));
00350 if(strpos($table, ".")){
00351 list($schema, $table) = explode(".", $table);
00352 }
00353 if($schema==''){
00354 $num = $this->fetch_one("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'public' AND TABLE_NAME ='$table'");
00355 } else {
00356 $schema = addslashes(strtolower($schema));
00357 $num = $this->fetch_one("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$schema' AND TABLE_NAME ='$table'");
00358 }
00359 return $num[0];
00360 }
00361
00368 public function limit($sql){
00369 $params = Util::getParams(func_get_args());
00370 $sql_new = $sql;
00371
00372 if(isset($params['limit']) && is_numeric($params['limit'])){
00373 $sql_new.=" LIMIT $params[limit]";
00374 }
00375
00376 if(isset($params['offset']) && is_numeric($params['offset'])){
00377 $sql_new.=" OFFSET $params[offset]";
00378 }
00379
00380 return $sql_new;
00381 }
00382
00389 public function drop_table($table, $if_exists=true){
00390 if($if_exists){
00391 if($this->table_exists($table)){
00392 return $this->query("DROP TABLE $table");
00393 } else {
00394 return true;
00395 }
00396 } else {
00397 return $this->query("DROP TABLE $table");
00398 }
00399 }
00400
00414 public function create_table($table, $definition, $index=array()){
00415 $create_sql = "CREATE TABLE $table (";
00416 if(!is_array($definition)){
00417 new KumbiaException("Definición invalida para crear la tabla '$table'");
00418 return false;
00419 }
00420 $create_lines = array();
00421 $index = array();
00422 $unique_index = array();
00423 $primary = array();
00424 $not_null = "";
00425 $size = "";
00426 foreach($definition as $field => $field_def){
00427 if(isset($field_def['not_null'])){
00428 $not_null = $field_def['not_null'] ? 'NOT NULL' : '';
00429 } else {
00430 $not_null = "";
00431 }
00432 if(isset($field_def['size'])){
00433 $size = $field_def['size'] ? '('.$field_def['size'].')' : '';
00434 } else {
00435 $size = "";
00436 }
00437 if(isset($field_def['index'])){
00438 if($field_def['index']){
00439 $index[] = "INDEX($field)";
00440 }
00441 }
00442 if(isset($field_def['unique_index'])){
00443 if($field_def['unique_index']){
00444 $index[] = "UNIQUE($field)";
00445 }
00446 }
00447 if(isset($field_def['primary'])){
00448 if($field_def['primary']){
00449 $primary[] = "$field";
00450 }
00451 }
00452 if(isset($field_def['auto'])){
00453 if($field_def['auto']){
00454 $field_def['type'] = "SERIAL";
00455 }
00456 }
00457 if(isset($field_def['extra'])){
00458 $extra = $field_def['extra'];
00459 } else {
00460 $extra = "";
00461 }
00462 $create_lines[] = "$field ".$field_def['type'].$size.' '.$not_null.' '.$extra;
00463 }
00464 $create_sql.= join(',', $create_lines);
00465 $last_lines = array();
00466 if(count($primary)){
00467 $last_lines[] = 'PRIMARY KEY('.join(",", $primary).')';
00468 }
00469 if(count($index)){
00470 $last_lines[] = join(',', $index);
00471 }
00472 if(count($unique_index)){
00473 $last_lines[] = join(',', $unique_index);
00474 }
00475 if(count($last_lines)){
00476 $create_sql.= ','.join(',', $last_lines).')';
00477 }
00478 return $this->query($create_sql);
00479
00480 }
00481
00487 public function list_tables(){
00488 return $this->fetch_all("SELECT c.relname AS table FROM pg_class c, pg_user u "
00489 ."WHERE c.relowner = u.usesysid AND c.relkind = 'r' "
00490 ."AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) "
00491 ."AND c.relname !~ '^(pg_|sql_)' UNION "
00492 ."SELECT c.relname AS table_name FROM pg_class c "
00493 ."WHERE c.relkind = 'r' "
00494 ."AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) "
00495 ."AND NOT EXISTS (SELECT 1 FROM pg_user WHERE usesysid = c.relowner) "
00496 ."AND c.relname !~ '^pg_'");
00497 }
00498
00505 public function describe_table($table, $schema=''){
00506 $describe = $this->fetch_all("SELECT a.attname AS Field, t.typname AS Type,
00507 CASE WHEN attnotnull=false THEN 'YES' ELSE 'NO' END AS Null,
00508 CASE WHEN (select cc.contype FROM pg_catalog.pg_constraint cc WHERE
00509 cc.conrelid = c.oid AND cc.conkey[1] = a.attnum limit 1)='p' THEN 'PRI' ELSE ''
00510 END AS Key FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a,
00511 pg_catalog.pg_type t WHERE c.relname = '$table' AND c.oid = a.attrelid
00512 AND a.attnum > 0 AND t.oid = a.atttypid order by a.attnum");
00513 $final_describe = array();
00514 foreach($describe as $key => $value){
00515 $final_describe[] = array(
00516 "Field" => $value["field"],
00517 "Type" => $value["type"],
00518 "Null" => $value["null"],
00519 "Key" => $value["key"]
00520 );
00521 }
00522 return $final_describe;
00523 }
00524 }