00001 <?php 00023 class DbPdoPgSQL extends DbPDO { 00024 00028 protected $db_rbdm = "pgsql"; 00029 00035 protected $db_port = 5432; 00036 00041 const TYPE_INTEGER = "INTEGER"; 00042 00047 const TYPE_DATE = "DATE"; 00048 00053 const TYPE_VARCHAR = "VARCHAR"; 00054 00059 const TYPE_DECIMAL = "DECIMAL"; 00060 00065 const TYPE_DATETIME = "DATETIME"; 00066 00071 const TYPE_CHAR = "CHAR"; 00072 00077 public function initialize(){ 00078 00079 } 00080 00086 public function last_insert_id($table='', $primary_key=''){ 00087 if(!$this->pdo){ 00088 return false; 00089 } 00090 return $this->pdo->lastInsertId("{$table}_{$primary_key}_seq"); 00091 } 00092 00099 function table_exists($table, $schema=''){ 00100 $table = addslashes(strtolower($table)); 00101 if(strpos($table, ".")){ 00102 list($schema, $table) = explode(".", $table); 00103 } 00104 if($schema==''){ 00105 $num = $this->fetch_one("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'public' AND TABLE_NAME ='$table'"); 00106 } else { 00107 $schema = addslashes(strtolower($schema)); 00108 $num = $this->fetch_one("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$schema' AND TABLE_NAME ='$table'"); 00109 } 00110 return $num[0]; 00111 } 00112 00119 public function limit($sql){ 00120 $params = Util::getParams(func_get_args()); 00121 $sql_new = $sql; 00122 00123 if(isset($params['limit']) && is_numeric($params['limit'])){ 00124 $sql_new.=" LIMIT $params[limit]"; 00125 } 00126 00127 if(isset($params['offset']) && is_numeric($params['offset'])){ 00128 $sql_new.=" OFFSET $params[offset]"; 00129 } 00130 00131 return $sql_new; 00132 } 00133 00140 public function drop_table($table, $if_exists=true){ 00141 if($if_exists){ 00142 if($this->table_exists($table)){ 00143 return $this->query("DROP TABLE $table"); 00144 } else { 00145 return true; 00146 } 00147 } else { 00148 return $this->query("DROP TABLE $table"); 00149 } 00150 } 00151 00165 public function create_table($table, $definition, $index=array()){ 00166 $create_sql = "CREATE TABLE $table ("; 00167 if(!is_array($definition)){ 00168 new KumbiaException("Definición invalida para crear la tabla '$table'"); 00169 return false; 00170 } 00171 $create_lines = array(); 00172 $index = array(); 00173 $unique_index = array(); 00174 $primary = array(); 00175 $not_null = ""; 00176 $size = ""; 00177 foreach($definition as $field => $field_def){ 00178 if(isset($field_def['not_null'])){ 00179 $not_null = $field_def['not_null'] ? 'NOT NULL' : ''; 00180 } else { 00181 $not_null = ""; 00182 } 00183 if(isset($field_def['size'])){ 00184 $size = $field_def['size'] ? '('.$field_def['size'].')' : ''; 00185 } else { 00186 $size = ""; 00187 } 00188 if(isset($field_def['index'])){ 00189 if($field_def['index']){ 00190 $index[] = "INDEX($field)"; 00191 } 00192 } 00193 if(isset($field_def['unique_index'])){ 00194 if($field_def['unique_index']){ 00195 $index[] = "UNIQUE($field)"; 00196 } 00197 } 00198 if(isset($field_def['primary'])){ 00199 if($field_def['primary']){ 00200 $primary[] = "$field"; 00201 } 00202 } 00203 if(isset($field_def['auto'])){ 00204 if($field_def['auto']){ 00205 $field_def['type'] = "SERIAL"; 00206 } 00207 } 00208 if(isset($field_def['extra'])){ 00209 $extra = $field_def['extra']; 00210 } else { 00211 $extra = ""; 00212 } 00213 $create_lines[] = "$field ".$field_def['type'].$size.' '.$not_null.' '.$extra; 00214 } 00215 $create_sql.= join(',', $create_lines); 00216 $last_lines = array(); 00217 if(count($primary)){ 00218 $last_lines[] = 'PRIMARY KEY('.join(",", $primary).')'; 00219 } 00220 if(count($index)){ 00221 $last_lines[] = join(',', $index); 00222 } 00223 if(count($unique_index)){ 00224 $last_lines[] = join(',', $unique_index); 00225 } 00226 if(count($last_lines)){ 00227 $create_sql.= ','.join(',', $last_lines).')'; 00228 } 00229 return $this->query($create_sql); 00230 00231 } 00232 00238 public function list_tables(){ 00239 return $this->fetch_all("SELECT c.relname AS table_name FROM pg_class c, pg_user u " 00240 ."WHERE c.relowner = u.usesysid AND c.relkind = 'r' " 00241 ."AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) " 00242 ."AND c.relname !~ '^(pg_|sql_)' UNION " 00243 ."SELECT c.relname AS table_name FROM pg_class c " 00244 ."WHERE c.relkind = 'r' " 00245 ."AND NOT EXISTS (SELECT 1 FROM pg_views WHERE viewname = c.relname) " 00246 ."AND NOT EXISTS (SELECT 1 FROM pg_user WHERE usesysid = c.relowner) " 00247 ."AND c.relname !~ '^pg_'"); 00248 } 00249 00256 public function describe_table($table, $schema=''){ 00257 $describe = $this->fetch_all("SELECT a.attname AS Field, t.typname AS Type, 00258 CASE WHEN attnotnull=false THEN 'YES' ELSE 'NO' END AS Null, 00259 CASE WHEN (select cc.contype FROM pg_catalog.pg_constraint cc WHERE 00260 cc.conrelid = c.oid AND cc.conkey[1] = a.attnum)='p' THEN 'PRI' ELSE '' 00261 END AS Key FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a, 00262 pg_catalog.pg_type t WHERE c.relname = '$table' AND c.oid = a.attrelid 00263 AND a.attnum > 0 AND t.oid = a.atttypid order by a.attnum"); 00264 $final_describe = array(); 00265 foreach($describe as $key => $value){ 00266 $final_describe[] = array( 00267 "Field" => $value["field"], 00268 "Type" => $value["type"], 00269 "Null" => $value["null"], 00270 "Key" => $value["key"] 00271 ); 00272 } 00273 return $final_describe; 00274 } 00275 00276 }