KumbiaPHP beta2-dev
Framework PHP en español
|
00001 <?php 00002 // @see DbPdo Padre de Drivers Pdo 00003 require_once CORE_PATH . 'libs/db/adapters/pdo.php'; 00004 00026 class DbPdoMsSQL extends DbPDO { 00027 00031 protected $db_rbdm = "odbc"; 00032 00037 const TYPE_INTEGER = "INTEGER"; 00038 00043 const TYPE_DATE = "SMALLDATETIME"; 00044 00049 const TYPE_VARCHAR = "VARCHAR"; 00050 00055 const TYPE_DECIMAL = "DECIMAL"; 00056 00061 const TYPE_DATETIME = "DATETIME"; 00062 00067 const TYPE_CHAR = "CHAR"; 00068 00073 public function initialize(){ 00077 //$this->exec("SET IDENTITY_INSERT ON"); 00078 } 00079 00086 public function table_exists($table, $schema=''){ 00087 $table = addslashes("$table"); 00088 $num = $this->fetch_one("SELECT COUNT(*) FROM sysobjects WHERE type = 'U' AND name = '$table'"); 00089 return $num[0]; 00090 } 00091 00098 public function limit($sql, $number){ 00099 if(!is_numeric($number)){ 00100 return $sql; 00101 } 00102 $orderby = stristr($sql, 'ORDER BY'); 00103 if($orderby!==false){ 00104 $sort = (stripos($orderby, 'desc') !== false) ? 'desc' : 'asc'; 00105 $order = str_ireplace('ORDER BY', '', $orderby); 00106 $order = trim(preg_replace('/ASC|DESC/i', '', $order)); 00107 } 00108 $sql = preg_replace('/^SELECT\s/i', 'SELECT TOP '.($number).' ', $sql); 00109 $sql = 'SELECT * FROM (SELECT TOP '.$number. ' * FROM ('.$sql.') AS itable'; 00110 if($orderby !== false) { 00111 $sql.= ' ORDER BY '.$order.' '; 00112 $sql.= (stripos($sort, 'asc') !== false) ? 'DESC' : 'ASC'; 00113 } 00114 $sql.= ') AS otable'; 00115 if ($orderby!==false) { 00116 $sql.=' ORDER BY '.$order.' '.$sort; 00117 } 00118 return $sql; 00119 00120 } 00121 00128 public function drop_table($table, $if_exists=true){ 00129 if($if_exists){ 00130 if($this->table_exists($table)){ 00131 return $this->query("DROP TABLE $table"); 00132 } else { 00133 return true; 00134 } 00135 } else { 00136 return $this->query("DROP TABLE $table"); 00137 } 00138 } 00139 00153 public function create_table($table, $definition, $index=array()){ 00154 $create_sql = "CREATE TABLE $table ("; 00155 if(!is_array($definition)){ 00156 new KumbiaException("Definición invalida para crear la tabla '$table'"); 00157 return false; 00158 } 00159 $create_lines = array(); 00160 $index = array(); 00161 $unique_index = array(); 00162 $primary = array(); 00163 $not_null = ""; 00164 $size = ""; 00165 foreach($definition as $field => $field_def){ 00166 if(isset($field_def['not_null'])){ 00167 $not_null = $field_def['not_null'] ? 'NOT NULL' : ''; 00168 } else { 00169 $not_null = ""; 00170 } 00171 if(isset($field_def['size'])){ 00172 $size = $field_def['size'] ? '('.$field_def['size'].')' : ''; 00173 } else { 00174 $size = ""; 00175 } 00176 if(isset($field_def['index'])){ 00177 if($field_def['index']){ 00178 $index[] = "INDEX($field)"; 00179 } 00180 } 00181 if(isset($field_def['unique_index'])){ 00182 if($field_def['unique_index']){ 00183 $index[] = "UNIQUE($field)"; 00184 } 00185 } 00186 if(isset($field_def['primary'])){ 00187 if($field_def['primary']){ 00188 $primary[] = "$field"; 00189 } 00190 } 00191 if(isset($field_def['auto'])){ 00192 if($field_def['auto']){ 00193 $field_def['extra'] = isset($field_def['extra']) ? $field_def['extra']." IDENTITY" : "IDENTITY"; 00194 } 00195 } 00196 if(isset($field_def['extra'])){ 00197 $extra = $field_def['extra']; 00198 } else { 00199 $extra = ""; 00200 } 00201 $create_lines[] = "$field ".$field_def['type'].$size.' '.$not_null.' '.$extra; 00202 } 00203 $create_sql.= join(',', $create_lines); 00204 $last_lines = array(); 00205 if(count($primary)){ 00206 $last_lines[] = 'PRIMARY KEY('.join(",", $primary).')'; 00207 } 00208 if(count($index)){ 00209 $last_lines[] = join(',', $index); 00210 } 00211 if(count($unique_index)){ 00212 $last_lines[] = join(',', $unique_index); 00213 } 00214 if(count($last_lines)){ 00215 $create_sql.= ','.join(',', $last_lines).')'; 00216 } 00217 return $this->query($create_sql); 00218 00219 } 00220 00226 public function list_tables(){ 00227 return $this->fetch_all("SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name"); 00228 } 00229 00236 public function describe_table($table, $schema=''){ 00237 $describe_table = $this->fetch_all("exec sp_columns @table_name = '$table'"); 00238 $final_describe = array(); 00239 foreach($describe_table as $field){ 00240 $final_describe[] = array( 00241 "Field" => $field["COLUMN_NAME"], 00242 "Type" => $field['LENGTH'] ? $field["TYPE_NAME"] : $field["TYPE_NAME"]."(".$field['LENGTH'].")", 00243 "Null" => $field['NULLABLE'] == 1 ? "YES" : "NO" 00244 ); 00245 } 00246 $describe_keys = $this->fetch_all("exec sp_pkeys @table_name = '$table'"); 00247 foreach($describe_keys as $field){ 00248 for($i=0;$i<=count($final_describe)-1;$i++){ 00249 if($final_describe[$i]['Field']==$field['COLUMN_NAME']){ 00250 $final_describe[$i]['Key'] = 'PRI'; 00251 } else { 00252 $final_describe[$i]['Key'] = ""; 00253 } 00254 } 00255 } 00256 return $final_describe; 00257 } 00258 00264 public function last_insert_id($table='', $primary_key=''){ 00268 $num = $this->fetch_one("SELECT MAX($primary_key) FROM $table"); 00269 return (int) $num[0]; 00270 } 00271 00272 00273 }