<?php
/*
 * Copyright 2002 - 2003 by Gero Kohnert
 *
 *  CVS Info:  $Id: db_oci.pinc,v 1.16 2005/05/03 13:18:50 saraj Exp $
 *  $Author: saraj $
 */

require_once 'db.pinc';
#
# Register parse functions for datetime
#
$tutos['dateformat'][] = 'ociformat1';

/**
 * check for string delivered in postgres format
 */
function ociformat1(&$d,$str) {
  $regs = array();
  if ( ereg( "^([0-9]{4})-([0-9]{1,2})-([0-9]{1,2}) ([0-9]{1,2}):([0-9]{1,2}):([0-9]{1,2})$", $str, $regs ) ) {
    # Init via DB    1999-11-27 12:34:56 see NLS_DATE_FORMAT
    $d->year  = (integer)$regs[1];
    $d->month = (integer)$regs[2];
    $d->day   = (integer)$regs[3];
    $d->hour  = (integer)$regs[4];
    $d->min   = (integer)$regs[5];
    $d->sec   = (integer)$regs[6];
    $d->format = 13;
  }
  return;
}

/** ---------------------------------------------------------------------------
 * oracle database connection class
 *
 * @package DB
 */
class db_oci extends tutos_db {
  /* ---------------------------------------------------------------------------
   */
  function db_oci(&$db) {
    global $tutos;

    parent::init($db);

    $tutos[use_check_dbacl] = 0;
    $this->txtlen = 4000;
    $this->openfunc = 'OciLogon';
  }
  /**
   * open the DB connection
   */
  function open() {
    global $tutos;

    putenv("ORACLE_SID=".$this->db->name);
    putenv("ORACLE_HOME=".$this->db->home);
#    putenv("NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1");

    $of = $this->openfunc;
    $c = @$of($this->db->user,$this->db->pass,$this->db->name);
    if ($c == false) {
      Fatal_Error("Opening the ORACLE OCI database ".$this->getFullname() ,$this->error() );
    }
    $this->conn = &$c;

    $stmt = OciParse($this->conn,"ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'",OCI_COMMIT_ON_SUCCESS);
    OciExecute($stmt);
    OCIFreeStatement ($stmt);
    $stmt = OciParse($this->conn,"ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '. '");
    OciExecute($stmt);
    OCIFreeStatement ($stmt);
    $stmt = OciParse ($this->conn,"call DBMS_APPLICATION_INFO.SET_CLIENT_INFO('TUTOS ". $tutos[version] ." (http://www.tutos.org)')");
    OciExecute($stmt);
    OCIFreeStatement ($stmt);
    $stmt = OciParse ($this->conn,"call DBMS_APPLICATION_INFO.SET_MODULE('". $_SERVER['PHP_SELF']."','".$_SERVER['REMOTE_ADDR']."')");
    OciExecute($stmt);
    OCIFreeStatement ($stmt);
  }
  /**
   * close the DB connection
   */
  function close() {
    $this->timezone("");
    OciCommit($this->conn);
    OciLogOff($this->conn);
    $this->conn = -1;
  }
  /**
   * return more info about server client etc
   */
  function moreinfo() {
    return "Server:". OCIServerVersion($this->conn);
  }
  /* ---------------------------------------------------------------------------
   */
  function notime() {
    #return ("'1970-1-1 00:00:00'");
    return "null";
  }
  /* ---------------------------------------------------------------------------
   */
  function now() {
    return "sysdate";
  }
  /* ---------------------------------------------------------------------------
   */
  function exec($query,$showerror = 1) {
    if ( $this->conn == -1 ) {
      $msg = "No Connection";
      return -1;
    }

    $x = new result_oci($this);

    # Parse will fail on nonexistant tables
    $p = ociparse($this->conn,$query);
    if ( false == $p ) {
      if ($showerror == 1) {
        Fatal_Error("Exec ". $this->conn ." !PARSE|". $this->error(),$query);
      } else {
        $this->lasterror =  $this->error();
        return -1;
      }
    }
    $x->cur = &$p;    

    $this->lastquery = $query;
    $ex = @ociexecute($p);
    if ( false == $ex ) {
      if ($showerror == 1) {
        Fatal_Error("Exec ". $this->conn ." !EXEC|". $this->error($p),$query);
      } else {
        OCIFreeStatement ($p);
        $this->lasterror =  $this->error();
        return -1;
      }
    }
    $this->lasterror = "";

    $numcols = ocinumcols($p);

    $x->result = array();

    $row = 0;
    if ( $numcols > 0 ) {
      $c = array();
      while (  @OciFetchInto($p,&$c,OCI_ASSOC | OCI_RETURN_NULLS) ) {
        $x->result[$row] = $c;
        $row++;
        $c = array();
      }
    } else {
      OCIFreeStatement ($p);
    }

    return $x;
  }
  /* ---------------------------------------------------------------------------
   */
  function nextid($name = "id") {
    $query = "SELECT ". $this->prefix . $name .".nextval FROM dual";
    $r = $this->exec($query);
    if ( 1 != $r->numrows()) {
      Fatal_Error("got no id",$query);
    }
    $id = $r->get(0, "nextval");
    $r->free();
    return $id;
  }
  /* ---------------------------------------------------------------------------
   */
  function setSeqID($id,$name = "id") {
    $query = "DROP SEQUENCE ". $this->prefix . $name;
    $this->exec($query);

    $query = "CREATE SEQUENCE ". $this->prefix . $name ." start with ". $id;
    $this->exec($query);

    return $id;
  }
  /* ---------------------------------------------------------------------------
   */
  function getSeqID($name = "id") {
    $query = "SELECT ". $this->prefix . $name .".currval FROM dual";
    $r = $this->exec($query);
    if ( 1 != $r->numrows()) {
      Fatal_Error("got no id",$query);
    }
    $id = $r->get(0, "currval");
    $r->free();
    return $id;
  }
  /* ---------------------------------------------------------------------------
   */
  function string($t) {
    $t = str_replace("'","''",$t);
    return "'". $t ."'";
  }
  /* ---------------------------------------------------------------------------
   */
  function text($t) {
    if ( $this->txtlen > 0 ) {
      $t = str_replace("'","''",substr($t,0,$this->txtlen));
    }
    return "'". $t ."'";
  }
  /* ---------------------------------------------------------------------------
   * give Database formatted datetimestring for d
   */
  function datetime(&$d) {
    if ( $d->notime == 1) {
      return "null";
    }
    $t = new DateTime();
    $t->setDateTimeTS($d->ts_def);  // set the fields in default TZ
    $t->ts_def = $t->ts;
    $x = sprintf("to_Date('%04d-%02d-%02d %02d:%02d:%02d','YYYY-MM-DD HH24:MI:SS')",$t->year,$t->month,$t->day,$t->hour,$t->min,$t->sec);
    return $x;
  }
  /* ---------------------------------------------------------------------------
   * give Database formatted datestring for d
   */
  function date(&$d) {
    if ( $d->notime == 1) {
      return "null";
    }
 
    $t = new DateTime();
    $t->setDateTimeTS($d->ts_def);  // set the fields in default TZ
    $t->ts_def = $t->ts;
    $x = sprintf("to_Date('%04d-%02d-%02d','YYYY-MM-DD')",$t->year,$t->month,$t->day);
    return $x;
  }
  /* ---------------------------------------------------------------------------
   * case insensitive string compare
   */
  function like($fld1,$name,$fld2 = "",$fld3 = "") {
    $fld = $fld1;
    if ( $fld2 != "" ) {
      $fld .= "|| ' ' ||". $fld2;
    }
    if ( $fld3 != "" ) {
      $fld .= "|| ' ' ||". $fld3;
    }
    $name = eregi_replace(" ","%",$name);
    $name = eregi_replace("'","?",$name);
    return (" UPPER(". $fld .") like UPPER('%". $name ."%') ");
  }
  /* ---------------------------------------------------------------------------
   * case sensitive strict string compare
   */
  function like2($fld,$name) {
    #$name = eregi_replace(" ","%",$name);
    $name = eregi_replace("'","''",$name);
    return (" ". $fld ." = '". $name ."' ");
  }
  /* ---------------------------------------------------------------------------
   */
  function password($pw) {
    if ( $this->db->crypt != 1 ) {
      return "'". $pw ."'";
    }
    return "'". crypt($pw,"271166") ."'";
  }
  /* ---------------------------------------------------------------------------
   */
  function begin($t) {
#    $r = $this->Exec("BEGIN ". $t );
#    $r = $this->Exec("BEGIN");
    return "X";
  }
  /* ---------------------------------------------------------------------------
   */
  function commit($t) {
#    $r = $this->Exec("COMMIT ". $t);
#    $r = $this->Exec("COMMIT");
#    $r = $this->Exec("END ");
    return "Y";
  }
  /* ---------------------------------------------------------------------------
   */
  function abort($t) {
    return "";
  }
  /* ---------------------------------------------------------------------------
   */
  function lock($t) {
    return "";
  }
  /* ---------------------------------------------------------------------------
   */
  function timezone($tz) {
    global $tutos;

    if ( !empty($tz)) {
      putenv ("TZ=".$tz);
      strftime("%H:%M %Z");
    } else {
      putenv ("TZ=". $tutos[defaultTZ]);
      strftime("%H:%M %Z");
    }
  }
  /* ---------------------------------------------------------------------------
   */
  function getFullname() {
    return "ORACLE OCI:". $this->db->name;
  }
  /* ---------------------------------------------------------------------------
   * set persistent mode
   * 0 = no
   * 1 = yes
   */
  function setPersistent($mode) {
    if ( $mode == 1 ) {
      $this->openfunc = 'OciPLogon';
    } else {
      $this->openfunc = 'OciLogon';
    }
  }
  /* ---------------------------------------------------------------------------
   */
  function error($r = "") {
    if ( $r != "" ) {
      $x = OCIError($r);
      return "DBerror: ". $this->getFullName() ." |". $x['code'] .": ". $x['message'] ."|";
    } else {
      $x = OCIError();
      return "DBerror: ". $this->getFullName() ." |". $x['code'] .": ". $x['message'] ."|";
    }
  }
  /**
   * Clear a table
   * tbl = name of table
   * truncate a table
   */
  function truncatetable($tbl) {
    $q = "TRUNCATE ". $this->prefix . $tbl;
    return $this->exec($q,0);    
  }
  /**
   * tbl = array with table description
   */
  function droptable(&$tbl) {
    if (is_array($tbl)) {
      $q = "DROP TABLE ". $this->prefix . $tbl[name] ;
    } else {
      $q = "DROP TABLE ". $this->prefix . $tbl ;
    }
    return $this->exec($q,0);    
  }
  /**
   * tbl = array with table description
   */
  function createtable(&$tbl) {
    $comment = array();
    $q = "CREATE TABLE ". $this->prefix . $tbl[name] ;
    $pre = " (";
    foreach ($tbl as $i => $f) {
      if (! is_array($f) ) {
        continue;
      }
      if ($f[type] == "ID_TYPE") {
        $t = "number(30,0)";
      } else if ($f[type] == "TS_TYPE") {
        $t = "date";
      } else if ($f[type] == "TX_TYPE") {
        $t = "varchar2(". $this->txtlen .")";
      } else if ($f[type] == "VARCHAR") {
        $t = "varchar2(". $f[size] .")";
      } else {
        $t = eregi_replace("varchar","varchar2",$f[type]);
      }
      $c = $f[constraints];
      $c = ereg_replace("PK","NN primary key",$c);
      $c = ereg_replace("U","unique",$c);
      $c = ereg_replace("NN","not null",$c);
      $q .= $pre. $this->colname($i) ." ". $t ." ". $c;
      $pre = ",";
      $comment[] = "COMMENT ON COLUMN ". $this->prefix . $tbl[name] .".".$this->colname($i) ." IS '". $f[Desc] ."'";
    }
    $q .= ")";

    $r = $this->exec($q,0);
    if ($r != -1 ) {
      $q = " COMMENT ON TABLE ". $this->prefix . $tbl[name] ." IS '". $tbl[Desc] ."'";
      $this->exec($q,0);
      foreach ($comment as $f ) {
        $this->exec($f,0);
      }
    }
    return $r;
  }
  /**
   * rename a table
   * oldtbl = current table descriptopn
   * newname = new table name
   */
  function renametable(&$oldtbl,$newname) {
    $q = "ALTER TABLE ". $this->prefix . $oldtbl[name] ." RENAME TO " . $this->prefix . $newname;
    return $this->exec($q,0);    
  }
  /**
   * tbl = array with index description
   */
  function dropindex(&$idx) {
    $q = "DROP INDEX ". $this->prefix . $idx[name] ;
    return $this->exec($q,0);    
  }
  /**
   * tbl = array with table description
   */
  function createindex(&$idx) {
    if ( ! isset($idx[type]) ) {
      $idx[type] = "";
    }
    $q = "CREATE ".$idx[type] ." INDEX ". $this->prefix . $idx[name] ." ON ". $this->prefix . $idx[table];

    $pre = "(";
    @reset($idx[column]);
    while ( list ($i,$f) = @each ($idx[column]) ) {
     $q .= $pre . $this->colname($f);
     $pre = ",";
    }
    $q .= ")";
    return $this->exec($q,0);
  }
  /**
   * col = array with column description
   */
  function createcolumn($tblname,$colname,&$col) {
    $q = "ALTER TABLE ". $this->prefix . $tblname ." ADD (";

    if ($col[type] == "ID_TYPE") {
      $t = "number(30,0)";
    } else if ($col[type] == "TS_TYPE") {
      $t = "date";
    } else if ($col[type] == "TX_TYPE") {
      $t = "varchar2(". $this->txtlen .")";
    } else if ($col[type] == "VARCHAR") {
      $t = "varchar2(". $col[size] .")";
    } else {
      $t = eregi_replace("varchar","varchar2",$col[type]);
    }
    $c = $col[constraints];
    $c = ereg_replace("PK","primary key",$c);
    $c = ereg_replace("U","unique",$c);
    $c = ereg_replace("NN","not null",$c);
    $q .= $this->colname($colname) ." ". $t ." ". $c;
    $q .= ")";
    $r = $this->exec($q,0);

    $q = " COMMENT ON COLUMN ". $this->prefix .  $tblname .".". $this->colname($colname) ." IS '". $col[Desc] ."'";
    $this->exec($q,0);
    return $r;
  }
  /**
   * name = name of sequencer
   */
  function dropsequence(&$seq) {
     $q = "drop sequence ". $this->prefix . $seq[name];
     return $this->Exec($q,0);
  }
  /**
   * name = name of sequencer
   */
  function createsequence(&$seq) {
     $q = "create sequence ". $this->prefix . $seq[name] ." increment by 1 start with ". $seq['start'] ." cache 10";
     $r = $this->Exec($q,0);
     return $r;
  }
  /**
   * get the phrase to limit a selection to a specific range
   */
  function getRange($q,$start,$cnt) {
    return "
      select outer.* from (
         select inner.*,rownum as tutos_rn from (". $q .") inner
        where rownum <= ". ($start + $cnt) ."
      ) outer
      where tutos_rn > ".$start;
  }
  /**
   * do we need a count(*) to get the number of possible results in a select
   */
  function needCount() {
    return true;   
  }
  /**
   * method to call from check service
   */
  function checkservice() {
    global $table,$sequence,$tableidx,$tutos;

    $now = new DateTime();
    $diff = $now->getTimestamp() - $this->db->lastanalyze->getTimeStamp();

    # only once a day
    if ( $diff < 86400 ) return;

    # read the table definitions of modules
    @include_once $tutos['base'] ."/db/table.pinc";
    foreach ($tutos[modules] as $r => $x) {
      if (isset($tutos[modules][$r][file]) && file_exists(dirname($tutos['base'] ."/". $tutos[modules][$r][file]) ."/mtable.pinc") ) {
        @include_once dirname($tutos['base'] ."/". $tutos[modules][$r][file]) ."/mtable.pinc";
      }
    }

#    foreach ($table as $i => $f) {
#      $q = "optimize ". $f[name];
#      $this->Exec($q,0);
#   }
    # see http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96533/stats.htm#26713
    $cur = Ora_Do ($this->conn,"call DBMS_STATS.GATHER_SCHEMA_STATS('". $this->db->user ."')");
    Ora_Close($cur);

    $this->db->setLastAnalyze($now);
    $msg = $this->db->save();
    return $msg;
  }
  /* ---------------------------------------------------------------------------
   */
  function gettype() {
    return "OracleOCI";
  }
}
/*
 * Postgres Resultset
 */
class result_oci extends result {
  var $rs;
  var $cur;

  /* ---------------------------------------------------------------------------
   */
  function get($pos,$fld) {
    # Copy the whole thing (force a string != null)
    return "".$this->result[$pos-$this->offset][strtoupper($fld)];
  }
  /* ---------------------------------------------------------------------------
   */
  function numrows() {
    if ($this->rowcnt != -1) return $this->rowcnt;
    if ( ! isset($this->result) ) {
      return 0;
    }
    return count($this->result);
  }
  /* ---------------------------------------------------------------------------
   */
  function numfields() {
    return count($this->result[0]);
  }
  /* ---------------------------------------------------------------------------
   */
  function fieldname($nr) {
    return OciColumnName($this->cur,$nr);
  }
  /* ---------------------------------------------------------------------------
   */
  function fieldtype($nr) {
    return OciColumnType($this->cur,$nr);
  }
  /* ---------------------------------------------------------------------------
   */
  function free() {
    @OciFreeStatement($this->cur);
    unset($this->result);
    return;
  }

}
?>