<?php
/**
* Defines a class to be used for Oracle databases
*
* @author Pablo Santiago Sánchez <hide@address.com>
* @copyright Copyright (c) 2008, Pablo Santiago Sánchez
* @license http://opensource.org/licenses/bsd-license.php BSD License
* @package pop
* @subpackage drivers
*/
/**
* Defines a class to be used for OCI databases
*
* @package pop
* @subpackage drivers
*/
class POCIDBDriver extends PGenericDBDriver
{
public $schema = "public";
public $separator = ".";
/**
* Filter the field for update
* @param $value
* @param $type
* @param $defaultvalue
* @return string
*/
public function filterUpdate($value, $type, $defaultval = null)
{
if (($type=="PDate" || $type=="PDatetime" || $type=="PTime"))
{
if ($type=="PDate")
return "to_timestamp('".$value."','YYYY-MM-DD')";
else if ($type=="PDatetime")
return "to_timestamp('".$value."','YYYY-MM-DD HH24:MI:SS')";
else if ($type=="PTime")
return "to_timestamp('".$value."','HH24:MI:SS')";
}
else
return parent::filterUpdate($value, $type, $defaultval);
}
/**
* Filter the field for insert
* @param $value
* @param $type
* @param $defaultvalue
* @return string
*/
public function filterInsert($value, $type, $defaultval = null)
{
if (($type=="PDate" || $type=="PDatetime" || $type=="PTime"))
{
$value = str_replace("-","/",$value);
if ($type=="PDate")
return "to_timestamp('".$value."','YYYY-MM-DD')";
else if ($type=="PDatetime")
return "to_timestamp('".$value."','YYYY-MM-DD HH24:MI:SS')";
else if ($type=="PTime")
return "to_timestamp('".$value."','HH24:MI:SS')";
}
else
return parent::filterInsert($value, $type, $defaultval);
}
/**
* Filter the field for select
* @param $tablename
* @param field
* @param $key
* @param $type
* @return string
*/
public function filterSelect($tablename, $field, $key, $type)
{
if ($type == "PDate")
return "to_char(".$tablename.".".$field.", 'YYYY-MM-DD') as ".$key;
else if ($type == "PDatetime")
return "to_char(".$tablename.".".$field.", 'YYYY-MM-DD HH24:MI:SS') as ".$key;
else if ($type == "PTime")
return "to_char(".$tablename.".".$field.", 'HH24:MI:SS') as ".$key;
else
return parent::filterSelect($tablename, $field, $key, $type);
}
/**
* Filter the result
* @param $key
* @return string
*/
public function filterResult($key)
{
return strtoupper($key);
}
/**
* Returns the last inserted itme id
* @param $pdodb
* @param $table
* @param $idname
* @return integer
*/
public function getLastId($pdodb, $table, $idname)
{
$seq_name = $table."_".$idname."_seq";
$sql = "select ".$seq_name.".currval as id from dual";
$rsId = POPDB::getConnection($pdodb)->query($sql);
foreach ($rsId as $row)
{
$value = (int)$row[0];
}
$rsId->closeCursor();
return $value;
}
/**
* Begin transaction
* @param $pdodb
* @return void
*/
public function beginTransaction($pdodb)
{
POPDB::getConnection($pdodb)->beginTransaction();
}
/**
* Commit transaction
* @param $pdodb
* @return void
*/
public function commit($pdodb)
{
POPDB::getConnection($pdodb)->commit();
}
/**
* Rollsback transaction
* @param $pdodb
* @return void
*/
public function rollBack($pdodb)
{
POPDB::getConnection($pdodb)->rollBack();
}
/**
* Returns the required string for the createTable method
* @return string
*/
public function identityField()
{
return " integer not null ";
}
/**
* Creates the required schema to avoid errors on creating tables
* @param $schema
* @return void
*/
public function createSchema($pdodb, $schema)
{
//Oracle is quite complex for schema creation
if (POPEnvironment::$debug)
echo "<pre><font color=#FF0000>REMEMBER: to avoid errors you must create your schemas manually before.</font></pre><br>";
}
/**
* Returns the required SQL for sequences
* @param $table
* @return void
*/
public function sequenceCreation($table)
{
$sqlid = "\ncreate sequence ".$table."_id_seq\n";
$sqlid .= "minvalue 1\n";
$sqlid .= "maxvalue 999999999999999999\n";
$sqlid .= "start with 1\n";
$sqlid .= "increment by 1\n";
$sqlid .= "nocache\n";
$sqlid .= "\ncreate trigger ".$table."_id_seq_t\n";
$sqlid .= "before insert on ".$table."\n";
$sqlid .= "for each row\n";
$sqlid .= "begin \n";
$sqlid .= "if :new.id is null then\n";
$sqlid .= "select ".$table."_id_seq.nextval into :new.id from dual;\n";
$sqlid .= "end if;\n";
$sqlid .= "end;\n";
$sqlid .= "\n";
return $sqlid;
}
/**
* Returns the required SQL for field creation
* @param $type
* @param $defaultval
* @param $size
* @return void
*/
public function fieldCreation($type, $defaultval, $size=null)
{
if ($type=="datetime" || $type=="time")
$sql_field = " timestamp ";
else if ($type=="text")
$sql_field = " clob ";
else
$sql_field = " ".$type." ";
if ($type=="varchar")
$sql_field .= "(".$size.") ";
if (is_null($defaultval))
$sql_field .= " default null";
else
{
if ($defaultval && $type!="datetime" && $type!="date" && $type!="time" && $type!="varchar" && $type!="text")
$sql_field .= " default ".$defaultval;
}
return $sql_field;
}
/**
* Returns the required SQL for propagation
* @return void
*/
public function createPropagation()
{
$sql = "\n\t\ton delete cascade";
return $sql;
}
/**
* Alter table
* @return void
*/
public function alterTable()
{
}
/**
* Drops a table
* @param $pdodb
* @param $schema
* @param $table
* @return void
*/
public function dropTable($pdodb, $schema, $table)
{
$sql = "select count(1) from all_tables where table_name = '".strtoupper($table)."'";
$rs = POPDB::getConnection($pdodb)->query($sql);
$result = $rs->fetchAll();
$rs->closeCursor();
if (POPEnvironment::$debug)
echo "<pre><font color=#009900>".$sql."</font></pre><br>";
foreach ($result as $row)
if($row[0])
{
if ($this->pop_parent_type == "Persist")
{
$sql = "SELECT COUNT(1) FROM all_triggers where trigger_name = '".strtoupper($table)."_ID_SEQ_T'";
$rst = POPDB::getConnection($pdodb)->query($sql);
$result_t = $rst->fetchAll();
$rst->closeCursor();
if (POPEnvironment::$debug)
echo "<pre><font color=#009900>".$sql."</font></pre><br>";
foreach ($result_t as $row_t)
{
if($row_t[0])
{
$sql = "drop trigger ".$table."_id_seq_t\n";
POPDB::getConnection($pdodb)->exec($sql);
if (POPEnvironment::$debug)
echo "<pre><font color=#009900>".$sql."</font></pre><br>";
}
}
$sql = "SELECT COUNT(1) FROM all_sequences where sequence_name = '".strtoupper($table)."_ID_SEQ'";
$rss = POPDB::getConnection($pdodb)->query($sql);
$result_s = $rst->fetchAll();
$rss->closeCursor();
if (POPEnvironment::$debug)
echo "<pre><font color=#009900>".$sql."</font></pre><br>";
foreach ($result_s as $row_s)
{
if($row_s[0])
{
$sql = "drop sequence ".$table."_id_seq\n";
POPDB::getConnection($pdodb)->exec($sql);
if (POPEnvironment::$debug)
echo "<pre><font color=#009900>".$sql."</font></pre><br>";
}
}
}
$sql = "drop table ".$table."\n";
POPDB::getConnection($pdodb)->exec($sql);
if (POPEnvironment::$debug)
echo "<pre><font color=#009900>".$sql."</font></pre><br>";
}
$sql = "";
}
}
/**
* Register the class for the driver
*/
POPDBDriverRegistry::addDriver("oci", "POCIDBDriver");
?>