Main Page   Compound List   File List   Compound Members   File Members  

postgres.php

Go to the documentation of this file.
00001 <?php
00002 ############################################################################
00003 #    Copyright (C) 2004 by Paolo Armani & Stefano Menegon                  #
00004 #    paolo.armani@gmail.com   menegon@itc.it                               #
00005 #                                                                          #
00006 #    This program is free software; you can redistribute it and#or modify  #
00007 #    it under the terms of the GNU Library General Public License as       #
00008 #    published by the Free Software Foundation; either version 2 of the    #
00009 #    License, or (at your option) any later version.                       #
00010 #                                                                          #
00011 #    This program is distributed in the hope that it will be useful,       #
00012 #    but WITHOUT ANY WARRANTY; without even the implied warranty of        #
00013 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         #
00014 #    GNU General Public License for more details.                          #
00015 #                                                                          #
00016 #    You should have received a copy of the GNU Library General Public     #
00017 #    License along with this program; if not, write to the                 #
00018 #    Free Software Foundation, Inc.,                                       #
00019 #    59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.             #
00020 ############################################################################
00021 
00023 
00035 function dati_sql($array, $not_null=0, $is_expression=0, $dati='', $default_not_null=0){
00036     if(is_array($array)){
00037         foreach($array as $key=>$val){
00038             if(is_array($dati) && in_array($key, $dati)||(!is_array($dati))){
00039 
00040               if(is_array($not_null)){
00041                 if(array_key_exists($key, $not_null)){
00042                   $nn=$not_null[$key];
00043                 }
00044                 else{
00045                   $nn=$default_not_null;
00046                 }
00047               }
00048               else{
00049                 $nn=$not_null;
00050               }
00051               
00052               if(is_array($is_expression)){
00053                 if($is_expression[$key]!=''){
00054                   $is_exp=$is_expression[$key];
00055                 }
00056                 else{
00057                   $is_exp=0;
00058                 }
00059               }
00060               else{
00061                 $is_exp=$is_expression[$key];
00062               }
00063               
00064               if($is_exp){
00065                 if($array[$key]=='')
00066                   $array[$key]='NULL';
00067               }
00068               else{
00069                 $array[$key]=(($array[$key]=="") && ($nn==0))?'NULL':("'".pg_escape_string($array[$key])."'");
00070               }
00071             }
00072         }
00073     }
00074     return $array;
00075 }
00076 
00077 
00079 
00092 function insert_sql($tbSchema='public', $table_name, $array, $not_null=0, $is_expression=0, $dati='', $default_not_null=0){
00093   $array=dati_sql($array, $not_null,  $is_expression, $dati, $default_not_null);
00094   $no_primo=0;
00095   $sql=$tbSchema
00096      ?('INSERT INTO "'.pg_escape_string($tbSchema).'"."'.pg_escape_string($table_name).'" (')
00097      :('INSERT INTO "'.pg_escape_string($table_name).'" (');
00098   $sql1='';
00099   
00100   if(is_array($array)){
00101       foreach($array as $key=>$val){
00102           if(is_array($dati) && in_array($key, $dati)||(!is_array($dati))){
00103               if($no_primo){$sql.=', '; $sql1.=', ';}else{$no_primo=1;};
00104               $sql.='"'.pg_escape_string($key).'"';
00105               $sql1.=$array["$key"];
00106           };
00107       };
00108   };
00109   $sql.=') VALUES('.$sql1.');';
00110   return $sql;
00111 };
00112 
00113 
00115 
00128 function update_sql($tbSchema='public', $table_name, $array, $not_null=0, $is_expression=0, $dati='', $default_not_null=0){
00129   $array=dati_sql($array, $not_null,  $is_expression, $dati, $default_not_null);
00130   $no_primo=0;
00131   $sql=$tbSchema
00132      ?('UPDATE "'.pg_escape_string($tbSchema).'"."'.pg_escape_string($table_name).'" SET ')
00133      :('UPDATE "'.pg_escape_string($table_name).'" SET ');
00134   if(is_array($array)){
00135       foreach($array as $key=>$val){
00136           if(is_array($dati) && in_array($key, $dati)||(!is_array($dati))){
00137               if($no_primo){$sql.=', ';}else{$no_primo=1;};
00138               $sql.='"'.pg_escape_string($key).'"='.$array[$key];
00139           };
00140       };
00141   };
00142   return $sql;
00143 };
00144 
00145 
00147 /*! \brief format sql where query
00148  *
00149  *formatta i dati aggiungendo apici se servono o settando a NULL le variabili vuote, o lasciandole vuote se co specificato dal terzo parametro della funzione
00150  *
00151  * \param $array sql data array
00152  * \param $operator='=' one operator for all or associative array of operators 
00153  * \param $logic='AND' logic opertor to use
00154  * \param $not_null=0 var or array that indicates if all or a variable could be a void string or must be NULL
00155  * \param $dati='' name of key of $array to parse (default all)
00156  * \param $tbName='' var or array of table names of all or each field(used in query with more fields of different columns that haves the same name)
00157  * \param $default_not_null=0 if you set $not_null as an array, but not for all the values, this set remanent values
00158  * \param $is_expression=0 var or array that indicates if all or a variable is a sql expression or a value to be enclose into 2 "
00159  * \return parsed sql query  
00160  */
00161 
00162 function where_sql($array, $operator='=',  $logic='AND', $tbSchema='public', $tbName='', $not_null=0, $is_expression=0, $dati='', $default_not_null=0){
00163   $array=dati_sql($array, $not_null,  $is_expression, $dati, $default_not_null);
00164   $no_primo=0;
00165   $sql='';
00166   if(is_array($array)){
00167       foreach($array as $key=>$val){
00168         if($val!="''"){
00169           if(is_array($dati) && in_array($key, $dati)||(!is_array($dati))){
00170             if($no_primo){$sql.=" $logic ";}else{$sql=' WHERE '; $no_primo=1;};
00171             if(is_array($tbName)){
00172               $sql.=$tbSchema
00173                  ? (($tbName[$key]=='')?'':('"'.pg_escape_string($tbSchema).'"."'.pg_escape_string($tbName[$key]).'".'))
00174                  :(($tbName[$key]=='')?'':('"'.pg_escape_string($tbName[$key]).'".'));
00175             }
00176             else{
00177               $bo.=$tbSchema
00178                  ? (($tbName=='')?'':('"'.pg_escape_string($tbSchema).'"."'.pg_escape_string($tbName).'".'))
00179                  :(($tbName[$key]=='')?'':('"'.pg_escape_string($tbName).'".'));
00180               $sql.=$bo;
00181               $bo='';
00182             }
00183             $sql.='"'.pg_escape_string($key).'"'.(is_array($operator)?$operator["$key"]:$operator).$array["$key"];
00184           };
00185         };
00186       };
00187   };
00188   return $sql;
00189 };
00190 
00191 
00192 
00194 
00198 function isopostgis($coord,$type){
00199   $resString="";
00200   switch($type){
00201   case 'POINT':
00202     if($coord["east"]>0 && $coord["north"]>0)
00203       $resString='POINT('. $coord["east"] . " " . $coord["north"] . ')';
00204     break;
00205   case 'POLYGON':
00206   case 'LINE':
00207     $resString='';
00208   break;
00209   }
00210   return $resString;
00211 }
00212 
00214 
00223 function get_table_attributes($conn,$table, $field = '', $result_type1=PGSQL_ASSOC, $result_type2=PGSQL_BOTH, $namespace= 'public' ) {
00224   $fields=Array();
00225   if ($field == '') {
00226     $sql = "SELECT
00227                   a.attname, t.typname as type, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, 
00228                   (SELECT adsrc FROM pg_attrdef adef WHERE a.attrelid=adef.adrelid AND a.attnum=adef.adnum) AS adsrc
00229             FROM
00230                   pg_attribute a, pg_class".
00231      ($namespace?" left join pg_namespace on (relnamespace = pg_namespace.oid )":'').
00232                  ", pg_type t
00233             WHERE
00234                   pg_class.relname = '{$table}'".($namespace?" AND pg_namespace.nspname = '{$namespace}'":'')." AND a.attnum > 0 AND a.attrelid = pg_class.oid AND a.atttypid = t.oid
00235             ORDER BY a.attnum";
00236   }
00237   else {
00238     $sql = "SELECT
00239                   a.attname, t.typname as type, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef,
00240                   (SELECT adsrc FROM pg_attrdef adef WHERE a.attrelid=adef.adrelid AND a.attnum=adef.adnum) AS adsrc
00241             FROM
00242                   pg_attribute a ,".
00243      ($namespace?"pg_class left join pg_namespace on (relnamespace = pg_namespace.oid ),":'').
00244                  "pg_class c,
00245                   pg_type t
00246             WHERE
00247                   c.relname = '{$table}''".($namespace?" AND pg_namespace.nspname = '{$namespace}'":'')." AND a.attname='{$field}' AND a.attrelid = c.oid AND a.atttypid = t.oid
00248             ORDER BY a.attnum";
00249   }
00250   
00251   $res = pg_query($conn,$sql);
00252   
00253   //  $fields = pg_fetch_all($res);
00254   
00255   $num=pg_num_rows($res);
00256   for($i=0;$i<$num;$i++){
00257     $row = pg_fetch_array($res,  $i, $result_type2);
00258     if($row["type"]=='geometry'){
00259       $sql2="SELECT type, srid from geometry_columns 
00260              WHERE f_table_name='$table' 
00261                AND f_geometry_column='".$row['attname']."'";
00262       $res2 = pg_query($conn,$sql2);
00263       if(pg_num_rows($res2)){
00264         $row["geotype"]=pg_fetch_result($res2, 0, 'type');
00265         $row["geosrid"]=pg_fetch_result($res2, 0, 'srid');
00266       }
00267     }
00268     switch($result_type1){
00269     case PGSQL_ASSOC:  $fields[current($row)] = $row; break;
00270     case PGSQL_BOTH:  $fields[current($row)] = $row;
00271     default:       $fields[] = $row;
00272     }
00273   } 
00274   return $fields;
00275 }
00276 
00277 
00279 
00288 function get_foreign_keys($conn, $child=true, $table, $field = '', $namespace= 'public') {
00289   $query="
00290     select
00291     nspname as schema, class.relname as tb_child, att.attname as cl_child, refclass.relname as tb_parent, refatt.attname as cl_parent
00292     from pg_constraint
00293 
00294     join pg_namespace on connamespace=pg_namespace.oid
00295 
00296     join pg_class as class on conrelid=class.oid
00297     join pg_class as refclass on confrelid=refclass.oid
00298 
00299     join pg_attribute as att on (att.attrelid=pg_constraint.conrelid and att.attnum=pg_constraint.conkey[1])
00300     join pg_attribute as refatt on (refatt.attrelid=pg_constraint.confrelid and refatt.attnum=pg_constraint.confkey[1])
00301 
00302     where contype='f' and ";
00303   
00304   $query.=$namespace?("nspname='".pg_escape_string($namespace)."' and "):'';
00305   $query.=$child?'refclass.relname':'class.relname';
00306   $query.="='".pg_escape_string($table)."'";
00307   if($field){
00308     if(!is_array($field)) $field=array($field);
00309     foreach ($field as $key=>$val){
00310       $query.=$child?'and refatt.attname':'and att.attname';
00311       $query.="='".pg_escape_string($val)."'";
00312     }
00313   }
00314   $res = pg_query($conn,$query);
00315   
00316   $fields = pg_fetch_all($res);
00317   return $fields;
00318   
00319 }
00320 
00322 
00328 function prepare_array_select($connection,$querySQL){
00329   $result=pg_query($connection,$querySQL);
00330   $arr=transpose(pg_fetch_all($result));
00331   reset($arr);
00332   $key1=key($arr);
00333   next($arr);
00334   $key2=key($arr);
00335   return array_to_hash($arr[$key1],$arr[$key2]);
00336 }; 
00337 
00339 
00345 function get_attributes_notnull($attArray){
00346   $could_be_void=Array();  
00347   foreach($attArray as $key=>$value){
00348     $could_be_void[]= ($value["attnotnull"]=='t')?0:1;    
00349   }
00350   return $could_be_void;
00351 }
00352 
00354 
00360 function get_attributes_default($attArray){
00361   $default=Array();  
00362   foreach($attArray as $key=>$value){
00363     $default[$key]= ($value["atthasdef"]=='t')?$value["adsrc"]:'';    
00364   }
00365   return $default;
00366 }
00367 
00368 
00369 
00371 
00376 function get_tables($connection, $pg_catalog=false){
00377   $sql=$pg_catalog?'':' WHERE schemaname!=\'pg_catalog\'';
00378   $result=pg_query($connection, "SELECT * FROM pg_tables $sql order by schemaname, tablename;");
00379   return pg_fetch_all($result);
00380 }
00381 
00382 
00384 
00390 function get_inh_tables($connection, $schema, $table){
00391   $sql='select pg_namespace1.nspname as child_schema, pg_class1.relname as child_table, pg_namespace.nspname as parent_schema, pg_class.relname as parent_table from pg_inherits join pg_class as pg_class1 on(inhrelid=pg_class1.oid)join pg_class on(inhparent=pg_class.oid) join pg_namespace as pg_namespace1 on pg_class1.relnamespace=pg_namespace1.oid join pg_namespace on pg_class.relnamespace=pg_namespace.oid';
00392   if($schema) {
00393     $sql.=' WHERE  pg_class.relname=\''.$table.'\' AND pg_namespace.nspname=\''.$schema.'\'';
00394   }
00395   $sql.=' ORDER BY pg_namespace.nspname, pg_class.relname;';
00396   $result=pg_query($connection, $sql);
00397   return pg_fetch_all($result);
00398 }
00399 
00400 ?>

Generated on Mon Nov 29 01:21:58 2004 for phpPgWeb by doxygen1.2.18