00001 <?php 00023 class DbPdoOracle extends DbPDO { 00024 00028 protected $db_rbdm = "oci"; 00029 00034 const TYPE_INTEGER = "INTEGER"; 00035 00040 const TYPE_DATE = "DATE"; 00041 00046 const TYPE_VARCHAR = "VARCHAR2"; 00047 00052 const TYPE_DECIMAL = "DECIMAL"; 00053 00058 const TYPE_DATETIME = "DATETIME"; 00059 00064 const TYPE_CHAR = "CHAR"; 00065 00066 00071 public function initialize(){ 00072 $this->pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, false); 00073 $this->exec("alter session set nls_date_format = 'YYYY-MM-DD'"); 00074 $this->begin(); 00075 } 00076 00083 public function limit($sql, $number){ 00084 if(!is_numeric($number)||$number<0){ 00085 return $sql; 00086 } 00087 if(eregi("ORDER[\t\n\r ]+BY", $sql)){ 00088 if(stripos($sql, "WHERE")){ 00089 return eregi_replace("ORDER[\t\n\r ]+BY", "AND ROWNUM <= $number ORDER BY", $sql); 00090 } else { 00091 return eregi_replace("ORDER[\t\n\r ]+BY", "WHERE ROWNUM <= $number ORDER BY", $sql); 00092 } 00093 } else { 00094 if(stripos($sql, "WHERE")){ 00095 return "$sql AND ROWNUM <= $number"; 00096 } else { 00097 return "$sql WHERE ROWNUM <= $number"; 00098 } 00099 } 00100 } 00101 00108 public function drop_table($table, $if_exists=true){ 00109 if($if_exists){ 00110 if($this->table_exists($table)){ 00111 return $this->query("DROP TABLE $table"); 00112 } else { 00113 return true; 00114 } 00115 } else { 00116 return $this->query("DROP TABLE $table"); 00117 } 00118 } 00119 00133 public function create_table($table, $definition, $index=array()){ 00134 $create_sql = "CREATE TABLE $table ("; 00135 if(!is_array($definition)){ 00136 new KumbiaException("Definición invalida para crear la tabla '$table'"); 00137 return false; 00138 } 00139 $create_lines = array(); 00140 $index = array(); 00141 $unique_index = array(); 00142 $primary = array(); 00143 $not_null = ""; 00144 $size = ""; 00145 foreach($definition as $field => $field_def){ 00146 if(isset($field_def['not_null'])){ 00147 $not_null = $field_def['not_null'] ? 'NOT NULL' : ''; 00148 } else { 00149 $not_null = ""; 00150 } 00151 if(isset($field_def['size'])){ 00152 $size = $field_def['size'] ? '('.$field_def['size'].')' : ''; 00153 } else { 00154 $size = ""; 00155 } 00156 if(isset($field_def['index'])){ 00157 if($field_def['index']){ 00158 $index[] = "INDEX($field)"; 00159 } 00160 } 00161 if(isset($field_def['unique_index'])){ 00162 if($field_def['unique_index']){ 00163 $index[] = "UNIQUE($field)"; 00164 } 00165 } 00166 if(isset($field_def['primary'])){ 00167 if($field_def['primary']){ 00168 $primary[] = "$field"; 00169 } 00170 } 00171 if(isset($field_def['auto'])){ 00172 if($field_def['auto']){ 00173 $this->query("CREATE SEQUENCE {$table}_{$field}_seq START WITH 1"); 00174 } 00175 } 00176 if(isset($field_def['extra'])){ 00177 $extra = $field_def['extra']; 00178 } else { 00179 $extra = ""; 00180 } 00181 $create_lines[] = "$field ".$field_def['type'].$size.' '.$not_null.' '.$extra; 00182 } 00183 $create_sql.= join(',', $create_lines); 00184 $last_lines = array(); 00185 if(count($primary)){ 00186 $last_lines[] = 'PRIMARY KEY('.join(",", $primary).')'; 00187 } 00188 if(count($index)){ 00189 $last_lines[] = join(',', $index); 00190 } 00191 if(count($unique_index)){ 00192 $last_lines[] = join(',', $unique_index); 00193 } 00194 if(count($last_lines)){ 00195 $create_sql.= ','.join(',', $last_lines).')'; 00196 } 00197 return $this->query($create_sql); 00198 00199 } 00200 00207 function list_tables(){ 00208 return $this->fetch_all("SELECT table_name FROM all_tables"); 00209 } 00210 00216 public function last_insert_id($table='', $primary_key=''){ 00220 if($table&&$primary_key){ 00221 $sequence = $table."_".$primary_key."_seq"; 00222 $value = $this->fetch_one("SELECT $sequence.CURRVAL FROM dual"); 00223 return $value[0]; 00224 } 00225 return false; 00226 } 00227 00234 function table_exists($table, $schema=''){ 00235 $num = $this->fetch_one("SELECT COUNT(*) FROM ALL_TABLES WHERE TABLE_NAME = '".strtoupper($table)."'"); 00236 return $num[0]; 00237 } 00238 00245 public function describe_table($table, $schema=''){ 00249 $describe = $this->fetch_all("SELECT LOWER(ALL_TAB_COLUMNS.COLUMN_NAME) AS FIELD, LOWER(ALL_TAB_COLUMNS.DATA_TYPE) AS TYPE, ALL_TAB_COLUMNS.DATA_LENGTH AS LENGTH, (SELECT COUNT(*) FROM ALL_CONS_COLUMNS WHERE TABLE_NAME = '".strtoupper($table)."' AND ALL_CONS_COLUMNS.COLUMN_NAME = ALL_TAB_COLUMNS.COLUMN_NAME AND ALL_CONS_COLUMNS.POSITION IS NOT NULL) AS KEY, ALL_TAB_COLUMNS.NULLABLE AS ISNULL FROM ALL_TAB_COLUMNS WHERE ALL_TAB_COLUMNS.TABLE_NAME = '".strtoupper($table)."'"); 00250 $final_describe = array(); 00251 foreach($describe as $key => $value){ 00252 $final_describe[] = array( 00253 "Field" => $value["field"], 00254 "Type" => $value["type"], 00255 "Null" => $value["isnull"] == "Y" ? "YES" : "NO", 00256 "Key" => $value["key"] == 1 ? "PRI" : "" 00257 ); 00258 } 00259 return $final_describe; 00260 } 00261 00262 }