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
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 ?>
