[Summary view] [Print] [Text view]

   1  <?
   2  /**
   3  * @file database.inc.php 2006-04-17 andrew $
   4  * @package Focus/SIS
   5  * @copyright Copyright (C) 2006 Andrew Schmadeke. All rights reserved.
   6  * @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.txt
   7  * Focus/SIS is free software. This version may have been modified pursuant
   8  * to the GNU General Public License, and as distributed it includes or
   9  * is derivative of works licensed under the GNU General Public License or
  10  * other free or open source software licenses.
  11  * See COPYRIGHT.txt for copyright notices and details.
  12  */
  13  
  14  // Establish DB connection.
  15  function db_start()
  16  {    global $DatabaseServer,$DatabaseUsername,$DatabasePassword,$DatabaseName,$DatabasePort,$DatabaseType;
  17      
  18      switch($DatabaseType)
  19      {
  20          case 'oracle':
  21              $connection = @ocilogon($DatabaseUsername,$DatabasePassword,$DatabaseServer);
  22          break;        
  23          case 'postgres':
  24              if($DatabaseServer!='localhost')
  25                  $connectstring = "host=$DatabaseServer port=$DatabasePort ";
  26              $connectstring .= "dbname=$DatabaseName user=$DatabaseUsername";
  27              if(!empty($DatabasePassword))
  28                  $connectstring.=" password=$DatabasePassword";
  29              $connection = pg_connect($connectstring);
  30          break;
  31          case 'mysql':
  32              $connection = mysql_connect($DatabaseServer,$DatabaseUsername,$DatabasePassword);
  33              mysql_select_db($DatabaseName);
  34          break;
  35      }
  36      
  37      // Error code for both.
  38      if($connection === false)
  39      {
  40          switch($DatabaseType)
  41          {
  42              case 'oracle':
  43                  $errors=OciError();
  44                  $errormessage = $errors['message'];
  45              break;
  46              case 'postgres':
  47                  $errormessage = pg_last_error($connection);
  48              break;
  49              case 'mysql':
  50                  $errormessage = mysql_error($connection);
  51              break;
  52          }
  53          db_show_error("","Could not Connect to Database: $DatabaseServer",$errstring);
  54      }
  55      return $connection;
  56  }
  57  
  58  // This function connects, and does the passed query, then returns a connection identifier.
  59  // Not receiving the return == unusable search.
  60  //        ie, $processable_results = DBQuery("select * from students");
  61  function DBQuery($sql)
  62  {    global $DatabaseType,$_FOCUS;
  63  
  64      $connection = db_start();
  65  
  66      switch($DatabaseType)
  67      {
  68          case 'oracle':
  69              $result = @ociparse($connection, $sql);
  70              if($result === false) 
  71              {
  72                  $errors = OCIError($connection);
  73                  db_show_error($sql,"DB Parse Failed.", $errors['message']);
  74              }
  75              if(!@OciExecute($result))
  76              {
  77                  $errors = OCIError($result);
  78                  db_show_error($sql,"DB Execute Failed.", $errors['message']);
  79              }
  80              OciCommit($connection);
  81              OciLogoff($connection);
  82          break;
  83          case 'postgres':
  84              $sql = ereg_replace("([,\(=])[\r\n\t ]*''",'\\1NULL',$sql);
  85              $result = @pg_exec($connection,$sql);
  86              if($result===false)
  87              {
  88                  $errstring = pg_last_error($connection);
  89                  db_show_error($sql,"DB Execute Failed.", $errstring);
  90              }
  91          break;
  92          case 'mysql':
  93              $result = mysql_query($sql) or die(db_show_error($sql,"DB Execute Failed.",mysql_error()));
  94          break;
  95      }
  96      return $result;
  97  }
  98  
  99  // return next row.
 100  function db_fetch_row($result)
 101  {    global $DatabaseType;
 102      
 103      switch($DatabaseType)
 104      {
 105          case 'oracle':
 106              OCIFetchInto($result,$row,OCI_ASSOC+OCI_RETURN_NULLS);
 107              $return = $row;
 108          break;
 109          case 'postgres':
 110              $return=@pg_fetch_array($result);
 111              if(is_array($return))
 112              {
 113                  foreach($return as $key => $value)
 114                  {
 115                      if(is_int($key))
 116                          unset($return[$key]);
 117                  }
 118              }
 119          break;
 120          case 'mysql':
 121              $return = mysql_fetch_array($result);
 122              if(is_array($return))
 123              {
 124                  foreach($return as $key => $value)
 125                  {
 126                      if(is_int($key))
 127                          unset($return[$key]);
 128                  }
 129              }
 130          break;
 131      }
 132      return @array_change_key_case($return,CASE_UPPER);
 133  }
 134  
 135  // returns code to go into SQL statement for accessing the next value of a sequenc    function db_seq_nextval($seqname)
 136  function db_seq_nextval($seqname)
 137  {    global $DatabaseType;
 138  
 139      if(strtolower($DatabaseType)=='oracle')
 140          $seq=$seqname.".nextval";
 141      elseif(strtolower($DatabaseType)=='postgres')
 142          $seq="nextval('".$seqname."')";
 143  
 144      return $seq;
 145  }
 146  
 147  // start transaction
 148  function db_trans_start($connection)
 149  {    global $DatabaseType;
 150      
 151      if(strtolower($DatabaseType)=="postres") 
 152          db_trans_query($connection,"BEGIN WORK");
 153  }
 154  
 155  // run query on transaction -- if failure, runs rollback.
 156  function db_trans_query($connection,$sql) 
 157  {    global $DatabaseType;
 158  
 159      if(strtolower($DatabaseType)=="oracle") 
 160      {
 161          $parse = ociparse($connection,$sql);
 162          if($parse===false)
 163          {
 164              db_trans_rollback($connection);
 165              db_show_error($sql,"DB Transaction Parse Failed.");
 166          }
 167          $result=OciExecute($parse,OCI_DEFAULT);
 168          if ($result===false) 
 169          {
 170              db_trans_rollback($connection);
 171              db_show_error($sql,"DB Transaction Execute Failed.");
 172          }
 173          $result=$parse;
 174      } 
 175      elseif(strtolower($DatabaseType)=="postgres") 
 176      {
 177          $sql = ereg_replace("([,\(=])[\r\n\t ]*''",'\\1NULL',$sql);
 178          $result = pg_query($connection,$sql);
 179          if($result===false) 
 180          {
 181              db_trans_rollback($connection);
 182              db_show_error($sql,"DB Transaction Execute Failed.");
 183          }
 184      }
 185  
 186      return $result;
 187  }
 188  
 189  // rollback commands.
 190  function db_trans_rollback($connection) 
 191  {    global $DatabaseType;
 192  
 193      if (strtolower($DatabaseType)=="oracle")
 194          OCIRollback($connection);
 195      elseif(strtolower($DatabaseType)=="postgres")
 196          pg_query($connection,"ROLLBACK");
 197  }
 198  
 199  // commit changes.
 200  function db_trans_commit($connection) 
 201  {    global $DatabaseType;
 202  
 203      if(strtolower($DatabaseType)=="oracle")
 204          OCICommit($connection);
 205      elseif(strtolower($DatabaseType)=="postgres")
 206          pg_query($connection,"COMMIT");
 207  }
 208  
 209  // keyword mapping.
 210  if(strtolower($DatabaseType)=='oracle') 
 211      define("FROM_DUAL"," FROM DUAL ");
 212  else
 213      define("FROM_DUAL"," ");
 214  
 215  // DECODE and CASE-WHEN support
 216  function db_case($array) 
 217  {    global $DatabaseType;
 218  
 219      $counter=0;
 220      if(strtolower($DatabaseType)=="postgres") 
 221      {
 222          $array_count=count($array);
 223          $string = " CASE WHEN $array[0] =";
 224          $counter++;
 225          $arr_count = count($array);            
 226          for($i=1;$i<$arr_count;$i++) 
 227          {
 228              $value = $array[$i];
 229          
 230              if($value=="''" && substr($string,-1)=='=')
 231              {
 232                  $value = ' IS NULL';
 233                  $string = substr($string,0,-1);
 234              }
 235              
 236              $string.="$value";
 237              if($counter==($array_count-2) && $array_count%2==0)
 238                  $string.=" ELSE ";
 239              elseif($counter==($array_count-1))
 240                  $string.=" END ";
 241              elseif($counter%2==0)
 242                  $string.=" WHEN $array[0]=";
 243              elseif($counter%2==1)
 244                  $string.=" THEN ";
 245  
 246              $counter++;
 247          }
 248      }
 249      else 
 250      {
 251          $string=" decode( ";
 252          foreach($array as $value)
 253              $string.="$value,";
 254          $string[strlen($string)-1]=")";
 255          $string.=" ";
 256      }
 257      return $string;
 258  }
 259  
 260  // String position.
 261  function db_strpos($args) 
 262  {    global $DatabaseType;
 263  
 264      if(strtolower($DatabaseType)=="postgres")
 265          $ret = 'strpos(';
 266      else
 267          $ret = 'instr(';
 268  
 269      foreach($args as $value)
 270          $ret .= $value . ',';
 271      $ret = substr($ret,0,-1) . ')';    
 272      
 273      return $ret;
 274  }
 275  
 276  // CONVERT VARCHAR TO NUMERIC
 277  function db_to_number($text)
 278  {    global $DatabaseType;
 279  
 280      if(strtolower($DatabaseType)=="postgres")
 281          return '('.$text.')::text::float::numeric';
 282      else
 283          return 'to_number('.$text.')';
 284  }
 285  
 286  // returns an array with the field names for the specified table as key with subkeys 
 287  // of SIZE, TYPE, SCALE and NULL.  TYPE: varchar, numeric, etc. 
 288  function db_properties($table)
 289  {    global $DatabaseType,$DatabaseUsername;
 290  
 291      switch($DatabaseType)
 292      {
 293          case 'oracle':
 294              $sql="SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,
 295                  DATA_SCALE, NULLABLE, DATA_DEFAULT 
 296                  FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='".strtoupper($table)."'
 297                  AND OWNER='".strtoupper($DatabaseUsername)."' ORDER BY COLUMN_ID";
 298              $result = DBQuery($sql);
 299              while($row=db_fetch_row($result))
 300              {
 301                  if($row['DATA_TYPE']=='VARCHAR2')
 302                  {
 303                      $properties[$row['COLUMN_NAME']]['TYPE'] = "VARCHAR";
 304                      $properties[$row['COLUMN_NAME']]['SIZE'] = $row['DATA_LENGTH'];
 305                  }
 306                  elseif($row['DATA_TYPE']=='NUMBER')
 307                  {
 308                      $properties[$row['COLUMN_NAME']]['TYPE'] = "NUMERIC";
 309                      $properties[$row['COLUMN_NAME']]['SIZE'] = $row['DATA_PRECISION'];
 310                      $properties[$row['COLUMN_NAME']]['SCALE'] = $row['DATA_SCALE'];
 311                  }
 312                  else
 313                  {
 314                      $properties[$row['COLUMN_NAME']]['TYPE'] = $row['DATA_TYPE'];
 315                      $properties[$row['COLUMN_NAME']]['SIZE'] = $row['DATA_LENGTH'];
 316                      $properties[$row['COLUMN_NAME']]['SCALE'] = $row['DATA_SCALE'];
 317                  }
 318                  $properties[$row['COLUMN_NAME']]['NULL'] = $row['NULLABLE'];
 319              }            
 320          break;
 321          case 'postgres':
 322              $sql = "SELECT a.attnum,a.attname AS field,t.typname AS type,
 323                      a.attlen AS length,a.atttypmod AS lengthvar,
 324                      a.attnotnull AS notnull 
 325                  FROM pg_class c, pg_attribute a, pg_type t 
 326                  WHERE c.relname = '".strtolower($table)."' 
 327                      and a.attnum > 0 and a.attrelid = c.oid 
 328                      and a.atttypid = t.oid ORDER BY a.attnum";
 329              $result = DBQuery($sql);
 330              while($row = db_fetch_row($result)) 
 331              {
 332                  $properties[strtoupper($row['FIELD'])]['TYPE'] = strtoupper($row['TYPE']);
 333                  if(strtoupper($row['TYPE'])=="NUMERIC")
 334                  {
 335                      $properties[strtoupper($row['FIELD'])]['SIZE'] = ($row['LENGTHVAR'] >> 16) & 0xffff;
 336                      $properties[strtoupper($row['FIELD'])]['SCALE'] = ($row['LENGTHVAR'] -4) & 0xffff;
 337                  }
 338                  else
 339                  {
 340                      if($row['LENGTH']>0)
 341                          $properties[strtoupper($row['FIELD'])]['SIZE'] = $row['LENGTH'];
 342                      elseif($row['LENGTHVAR']>0)
 343                          $properties[strtoupper($row['FIELD'])]['SIZE'] = $row['LENGTHVAR']-4;
 344                  }
 345                  if ($row['NOTNULL']=='t')
 346                      $properties[strtoupper($row['FIELD'])]['NULL'] = "N";
 347                  else
 348                      $properties[strtoupper($row['FIELD'])]['NULL'] = "Y";
 349              }
 350          break;
 351          case 'mysql':
 352              $result = DBQuery("SHOW COLUMNS FROM $table");
 353              while($row = db_fetch_row($result))
 354              {
 355                  $properties[strtoupper($row['FIELD'])]['TYPE'] = strtoupper($row['TYPE'],strpos($row['TYPE'],'('));
 356                  if(!$pos = strpos($row['TYPE'],','))
 357                      $pos = strpos($row['TYPE'],')');
 358                  else
 359                      $properties[strtoupper($row['FIELD'])]['SCALE'] = substr($row['TYPE'],$pos+1);
 360  
 361                  $properties[strtoupper($row['FIELD'])]['SIZE'] = substr($row['TYPE'],strpos($row['TYPE'],'(')+1,$pos);
 362                  
 363                  if($row['NULL']!='')
 364                      $properties[strtoupper($row['FIELD'])]['NULL'] = "Y";
 365                  else
 366                      $properties[strtoupper($row['FIELD'])]['NULL'] = "N";
 367              }
 368          break;
 369      }
 370      return $properties;
 371  }
 372  
 373  function db_show_error($sql,$failnote,$additional='')
 374  {    global $FocusTitle;
 375  
 376      PopTable('header','We have a small problem ...');
 377      echo "
 378          <TABLE CELLSPACING=10 BORDER=0>
 379              <TD align=right><b>Date:</TD>
 380              <TD><pre>".date("m/d/Y h:i:s")."</pre></TD>
 381          </TR><TR>
 382              <TD align=right><b>Failure Notice:</b></TD>
 383              <TD><pre> $failnote </pre></TD>
 384          </TR><TR>
 385              <TD align=right><b>Additional Information:</b></TD>
 386              <TD>$additional</TD>
 387          </TR>
 388          </TABLE>";
 389      //Something you have asked the system to do has thrown a database error.  A system administrator has been notified, and the problem will be fixed as soon as possible.  It might be that changing the input parameters sent to this program will cause it to run properly.  Thanks for your patience.
 390      PopTable('footer');
 391      echo "<!-- SQL STATEMENT: \n\n $sql \n\n -->";
 392      
 393      $notify_addresses='focus@focus-sis.org';
 394      $notify_subject="Focus/SIS Database Error";
 395      $message="System: $FocusTitle \n";
 396      $message.="Date: ".date("m/d/Y h:i:s")."\n";
 397      $message.="Page: ".$_SERVER['PHP_SELF'].' '.ProgramTitle()." \n\n";
 398      $message.="Failure Notice:  $failnote \n";
 399      $message.="Additional Info: $additional \n";
 400      $message.="\n $sql \n";
 401      $message.="Request Array: \n".ShowVar($_REQUEST,'Y', 'N');
 402      $message.="\n\nSession Array: \n".ShowVar($_SESSION,'Y', 'N');
 403      mail($notify_addresses,$notify_subject,$message);
 404      
 405      die();
 406  }
 407  ?>