pgsql.php

Ir a la documentación de este archivo.

<?php
class DbPgSQL extends DbBase implements DbBaseInterface {
        public $id_connection;

        public $last_result_query;

        private $last_query;

        public $last_error;

        const DB_ASSOC = PGSQL_ASSOC;

        const DB_BOTH = PGSQL_BOTH;

        const DB_NUM = PGSQL_NUM;


        const TYPE_INTEGER = 'INTEGER';

        const TYPE_DATE = 'DATE';

        const TYPE_VARCHAR = 'VARCHAR';

        const TYPE_DECIMAL = 'DECIMAL';

        const TYPE_DATETIME = 'DATETIME';

        const TYPE_CHAR = 'CHAR';
        public function connect($config){

                if(!extension_loaded('pgsql')){
                        throw new KumbiaException('Debe cargar la extensión de PHP llamada php_pgsql');
                }
                
                if(!isset($config['port']) || !$config['port']) {
                        $config['port'] = 5432;
                }

                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)){
                        return true;
                } else {
                        throw new KumbiaException($this->error("No se puede conectar a la base de datos"));
                }
        }

        function query($sqlQuery){
                $this->debug($sqlQuery);
        if($this->logger){
            Logger::debug($sqlQuery);
        }
                if(!$this->id_connection){
                        $this->connect();
                        if(!$this->id_connection){
                                return false;
                        }
                }
                $this->last_query = $sqlQuery;
                if($resultQuery = @pg_query($this->id_connection, $sqlQuery)){
                        $this->last_result_query = $resultQuery;
                        return $resultQuery;
                } else {
                        throw new KumbiaException($this->error(" al ejecutar <em>'$sqlQuery'</em>"));
                }
        }

        function close(){
                if($this->id_connection) {
                        return pg_close($this->id_connection);
                } else {
                        return false;
                }
        }

        function fetch_array($resultQuery='', $opt=PGSQL_BOTH){
                if(!$this->id_connection){
                        return false;
                }
                if(!$resultQuery){
                        $resultQuery = $this->last_result_query;
                        if(!$resultQuery){
                                return false;
                        }
                }
                return pg_fetch_array($resultQuery, NULL, $opt);
        }

        function __construct($config){
                $this->connect($config);
        }

        function num_rows($resultQuery=''){
                if(!$this->id_connection){
                        return false;
                }
                if(!$resultQuery){
                        $resultQuery = $this->last_result_query;
                        if(!$resultQuery){
                                return false;
                        }
                }
                if(($numberRows = pg_num_rows($resultQuery))!==false){
                        return $numberRows;
                } else {
                        throw new KumbiaException($this->error());
                }
                return false;
        }

        function field_name($number, $resultQuery=''){
                if(!$this->id_connection){
                        return false;
                }
                if(!$resultQuery){
                        $resultQuery = $this->last_result_query;
                        if(!$resultQuery){
                                return false;
                        }
                }
                if(($fieldName = pg_field_name($resultQuery, $number))!==false){
                        return $fieldName;
                } else {
                        throw new KumbiaException($this->error());
                }
                return false;
        }


        function data_seek($number, $resultQuery=''){
                if(!$resultQuery){
                        $resultQuery = $this->last_result_query;
                        if(!$resultQuery){
                                return false;
                        }
                }
                if(($success = pg_result_seek($resultQuery, $number))!==false){
                        return $success;
                } else {
                        throw new KumbiaException($this->error());
                }
                return false;
        }

        function affected_rows($resultQuery=''){
                if(!$this->id_connection){
                        return false;
                }
                if(!$resultQuery){
                        $resultQuery = $this->last_result_query;
                        if(!$resultQuery){
                                return false;
                        }
                }
                if(($numberRows = pg_affected_rows($resultQuery))!==false){
                        return $numberRows;
                } else {
                        throw new KumbiaException($this->error());
                }
                return false;
        }

        function error($err=''){
                if(!$this->id_connection){
                    $this->last_error = @pg_last_error() ? @pg_last_error().$err : "[Error Desconocido en PostgreSQL \"$err\"]";
            if($this->logger){
                Logger::error($this->last_error);
            }
                        return $this->last_error;
                }
                $this->last_error = @pg_last_error() ? @pg_last_error().$err : "[Error Desconocido en PostgreSQL: $err]";
                $this->last_error.= $err;
        if($this->logger){
            Logger::error($this->last_error);
        }
                return pg_last_error($this->id_connection).$err;
        }

        function no_error(){
                if(!$this->id_connection){
                        return false;
                }
                return "0"; //Codigo de Error?
        }

        public function last_insert_id($table='', $primary_key=''){
                if(!$this->id_connection){
                        return false;
                }
                $last_id = $this->fetch_one("SELECT CURRVAL('{$table}_{$primary_key}_seq')");
                return $last_id[0];
        }

        function table_exists($table, $schema=''){
                $table = addslashes(strtolower($table));
                if(strpos($table, ".")){
                        list($schema, $table) = explode(".", $table);
                }
                if($schema==''){
                        $num = $this->fetch_one("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'public' AND TABLE_NAME ='$table'");
                } else {
                        $schema = addslashes(strtolower($schema));
                        $num = $this->fetch_one("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$schema' AND TABLE_NAME ='$table'");
                }
                return $num[0];
        }

        public function limit($sql){
                $params = Util::getParams(func_get_args());
                $sql_new = $sql;
        
                if(isset($params['limit']) && is_numeric($params['limit'])){
                        $sql_new.=" LIMIT $params[limit]";
                }
                
                if(isset($params['offset']) && is_numeric($params['offset'])){
                        $sql_new.=" OFFSET $params[offset]";
                }
                
                return $sql_new;
        }

        public function drop_table($table, $if_exists=true){
                if($if_exists){
                        if($this->table_exists($table)){
                                return $this->query("DROP TABLE $table");
                        } else {
                                return true;
                        }
                } else {
                        return $this->query("DROP TABLE $table");
                }
        }

        public function create_table($table, $definition, $index=array()){
                $create_sql = "CREATE TABLE $table (";
                if(!is_array($definition)){
                        throw new KumbiaException("Definición invalida para crear la tabla '$table'");
                }
                $create_lines = array();
                $index = array();
                $unique_index = array();
                $primary = array();
                $not_null = "";
                $size = "";
                foreach($definition as $field => $field_def){
                        if(isset($field_def['not_null'])){
                                $not_null = $field_def['not_null'] ? 'NOT NULL' : '';
                        } else {
                                $not_null = "";
                        }
                        if(isset($field_def['size'])){
                                $size = $field_def['size'] ? '('.$field_def['size'].')' : '';
                        } else {
                                $size = "";
                        }
                        if(isset($field_def['index'])){
                                if($field_def['index']){
                                        $index[] = "INDEX($field)";
                                }
                        }
                        if(isset($field_def['unique_index'])){
                                if($field_def['unique_index']){
                                        $index[] = "UNIQUE($field)";
                                }
                        }
                        if(isset($field_def['primary'])){
                                if($field_def['primary']){
                                        $primary[] = "$field";
                                }
                        }
                        if(isset($field_def['auto'])){
                                if($field_def['auto']){
                                        $field_def['type'] = "SERIAL";
                                }
                        }
                        if(isset($field_def['extra'])){
                                $extra = $field_def['extra'];
                        } else {
                                $extra = "";
                        }
                        $create_lines[] = "$field ".$field_def['type'].$size.' '.$not_null.' '.$extra;
                }
                $create_sql.= join(',', $create_lines);
                $last_lines = array();
                if(count($primary)){
                        $last_lines[] = 'PRIMARY KEY('.join(",", $primary).')';
                }
                if(count($index)){
                        $last_lines[] = join(',', $index);
                }
                if(count($unique_index)){
                        $last_lines[] = join(',', $unique_index);
                }
                if(count($last_lines)){
                        $create_sql.= ','.join(',', $last_lines).')';
                }
                return $this->query($create_sql);

        }

        public function list_tables(){
                return $this->fetch_all("SELECT c.relname AS table FROM pg_class c, pg_user u "
             ."WHERE c.relowner = u.usesysid AND c.relkind = 'r' "
             ."AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) "
             ."AND c.relname !~ '^(pg_|sql_)' UNION "
             ."SELECT c.relname AS table_name FROM pg_class c "
             ."WHERE c.relkind = 'r' "
             ."AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) "
             ."AND NOT EXISTS (SELECT 1 FROM pg_user WHERE usesysid = c.relowner) "
             ."AND c.relname !~ '^pg_'");
        }

        public function describe_table($table, $schema=''){
                $describe = $this->fetch_all("SELECT a.attname AS Field, t.typname AS Type,
                                CASE WHEN attnotnull=false THEN 'YES' ELSE 'NO' END AS Null,
                                CASE WHEN (select cc.contype FROM pg_catalog.pg_constraint cc WHERE
                                cc.conrelid = c.oid AND cc.conkey[1] = a.attnum limit 1)='p' THEN 'PRI' ELSE ''
                                END AS Key FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a,
                                pg_catalog.pg_type t WHERE c.relname = '$table' AND c.oid = a.attrelid
                                AND a.attnum > 0 AND t.oid = a.atttypid order by a.attnum");
                $final_describe = array();
                foreach($describe as $key => $value){
                        $final_describe[] = array(
                                "Field" => $value["field"],
                                "Type" => $value["type"],
                                "Null" => $value["null"],
                                "Key" => $value["key"]
                        );
                }
                return $final_describe;
        }
        
        public function fetch_object($queryResult=null, $class='stdClass')
        {
                if(!$queryResult){
                        $queryResult = $this->last_result_query;
                }
                return pg_fetch_object($queryResult, null, $class);
        }
}