/halogy/database/drivers/mssql/mssql_driver.php
PHP | 667 lines | 495 code | 39 blank | 133 comment | 12 complexity | bd7902228ef2ac2858aea79f15e1c4fb MD5 | raw file
1<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); 2/** 3 * CodeIgniter 4 * 5 * An open source application development framework for PHP 4.3.2 or newer 6 * 7 * @package CodeIgniter 8 * @author ExpressionEngine Dev Team 9 * @copyright Copyright (c) 2008 - 2009, EllisLab, Inc. 10 * @license http://codeigniter.com/user_guide/license.html 11 * @link http://codeigniter.com 12 * @since Version 1.0 13 * @filesource 14 */ 15 16// ------------------------------------------------------------------------ 17 18/** 19 * MS SQL Database Adapter Class 20 * 21 * Note: _DB is an extender class that the app controller 22 * creates dynamically based on whether the active record 23 * class is being used or not. 24 * 25 * @package CodeIgniter 26 * @subpackage Drivers 27 * @category Database 28 * @author ExpressionEngine Dev Team 29 * @link http://codeigniter.com/user_guide/database/ 30 */ 31class CI_DB_mssql_driver extends CI_DB { 32 33 var $dbdriver = 'mssql'; 34 35 // The character used for escaping 36 var $_escape_char = ''; 37 38 // clause and character used for LIKE escape sequences 39 var $_like_escape_str = " ESCAPE '%s' "; 40 var $_like_escape_chr = '!'; 41 42 /** 43 * The syntax to count rows is slightly different across different 44 * database engines, so this string appears in each driver and is 45 * used for the count_all() and count_all_results() functions. 46 */ 47 var $_count_string = "SELECT COUNT(*) AS "; 48 var $_random_keyword = ' ASC'; // not currently supported 49 50 /** 51 * Non-persistent database connection 52 * 53 * @access private called by the base class 54 * @return resource 55 */ 56 function db_connect() 57 { 58 if ($this->port != '') 59 { 60 $this->hostname .= ','.$this->port; 61 } 62 63 return @mssql_connect($this->hostname, $this->username, $this->password); 64 } 65 66 // -------------------------------------------------------------------- 67 68 /** 69 * Persistent database connection 70 * 71 * @access private called by the base class 72 * @return resource 73 */ 74 function db_pconnect() 75 { 76 if ($this->port != '') 77 { 78 $this->hostname .= ','.$this->port; 79 } 80 81 return @mssql_pconnect($this->hostname, $this->username, $this->password); 82 } 83 84 // -------------------------------------------------------------------- 85 86 /** 87 * Reconnect 88 * 89 * Keep / reestablish the db connection if no queries have been 90 * sent for a length of time exceeding the server's idle timeout 91 * 92 * @access public 93 * @return void 94 */ 95 function reconnect() 96 { 97 // not implemented in MSSQL 98 } 99 100 // -------------------------------------------------------------------- 101 102 /** 103 * Select the database 104 * 105 * @access private called by the base class 106 * @return resource 107 */ 108 function db_select() 109 { 110 // Note: The brackets are required in the event that the DB name 111 // contains reserved characters 112 return @mssql_select_db('['.$this->database.']', $this->conn_id); 113 } 114 115 // -------------------------------------------------------------------- 116 117 /** 118 * Set client character set 119 * 120 * @access public 121 * @param string 122 * @param string 123 * @return resource 124 */ 125 function db_set_charset($charset, $collation) 126 { 127 // @todo - add support if needed 128 return TRUE; 129 } 130 131 // -------------------------------------------------------------------- 132 133 /** 134 * Execute the query 135 * 136 * @access private called by the base class 137 * @param string an SQL query 138 * @return resource 139 */ 140 function _execute($sql) 141 { 142 $sql = $this->_prep_query($sql); 143 return @mssql_query($sql, $this->conn_id); 144 } 145 146 // -------------------------------------------------------------------- 147 148 /** 149 * Prep the query 150 * 151 * If needed, each database adapter can prep the query string 152 * 153 * @access private called by execute() 154 * @param string an SQL query 155 * @return string 156 */ 157 function _prep_query($sql) 158 { 159 return $sql; 160 } 161 162 // -------------------------------------------------------------------- 163 164 /** 165 * Begin Transaction 166 * 167 * @access public 168 * @return bool 169 */ 170 function trans_begin($test_mode = FALSE) 171 { 172 if ( ! $this->trans_enabled) 173 { 174 return TRUE; 175 } 176 177 // When transactions are nested we only begin/commit/rollback the outermost ones 178 if ($this->_trans_depth > 0) 179 { 180 return TRUE; 181 } 182 183 // Reset the transaction failure flag. 184 // If the $test_mode flag is set to TRUE transactions will be rolled back 185 // even if the queries produce a successful result. 186 $this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE; 187 188 $this->simple_query('BEGIN TRAN'); 189 return TRUE; 190 } 191 192 // -------------------------------------------------------------------- 193 194 /** 195 * Commit Transaction 196 * 197 * @access public 198 * @return bool 199 */ 200 function trans_commit() 201 { 202 if ( ! $this->trans_enabled) 203 { 204 return TRUE; 205 } 206 207 // When transactions are nested we only begin/commit/rollback the outermost ones 208 if ($this->_trans_depth > 0) 209 { 210 return TRUE; 211 } 212 213 $this->simple_query('COMMIT TRAN'); 214 return TRUE; 215 } 216 217 // -------------------------------------------------------------------- 218 219 /** 220 * Rollback Transaction 221 * 222 * @access public 223 * @return bool 224 */ 225 function trans_rollback() 226 { 227 if ( ! $this->trans_enabled) 228 { 229 return TRUE; 230 } 231 232 // When transactions are nested we only begin/commit/rollback the outermost ones 233 if ($this->_trans_depth > 0) 234 { 235 return TRUE; 236 } 237 238 $this->simple_query('ROLLBACK TRAN'); 239 return TRUE; 240 } 241 242 // -------------------------------------------------------------------- 243 244 /** 245 * Escape String 246 * 247 * @access public 248 * @param string 249 * @param bool whether or not the string will be used in a LIKE condition 250 * @return string 251 */ 252 function escape_str($str, $like = FALSE) 253 { 254 if (is_array($str)) 255 { 256 foreach($str as $key => $val) 257 { 258 $str[$key] = $this->escape_str($val, $like); 259 } 260 261 return $str; 262 } 263 264 // Access the CI object 265 $CI =& get_instance(); 266 267 // Escape single quotes 268 $str = str_replace("'", "''", $CI->input->_remove_invisible_characters($str)); 269 270 // escape LIKE condition wildcards 271 if ($like === TRUE) 272 { 273 $str = str_replace( array('%', '_', $this->_like_escape_chr), 274 array($this->_like_escape_chr.'%', $this->_like_escape_chr.'_', $this->_like_escape_chr.$this->_like_escape_chr), 275 $str); 276 } 277 278 return $str; 279 } 280 281 // -------------------------------------------------------------------- 282 283 /** 284 * Affected Rows 285 * 286 * @access public 287 * @return integer 288 */ 289 function affected_rows() 290 { 291 return @mssql_rows_affected($this->conn_id); 292 } 293 294 // -------------------------------------------------------------------- 295 296 /** 297 * Insert ID 298 * 299 * Returns the last id created in the Identity column. 300 * 301 * @access public 302 * @return integer 303 */ 304 function insert_id() 305 { 306 $ver = self::_parse_major_version($this->version()); 307 $sql = ($ver >= 8 ? "SELECT SCOPE_IDENTITY() AS last_id" : "SELECT @@IDENTITY AS last_id"); 308 $query = $this->query($sql); 309 $row = $query->row(); 310 return $row->last_id; 311 } 312 313 // -------------------------------------------------------------------- 314 315 /** 316 * Parse major version 317 * 318 * Grabs the major version number from the 319 * database server version string passed in. 320 * 321 * @access private 322 * @param string $version 323 * @return int16 major version number 324 */ 325 function _parse_major_version($version) 326 { 327 preg_match('/([0-9]+)\.([0-9]+)\.([0-9]+)/', $version, $ver_info); 328 return $ver_info[1]; // return the major version b/c that's all we're interested in. 329 } 330 331 // -------------------------------------------------------------------- 332 333 /** 334 * Version number query string 335 * 336 * @access public 337 * @return string 338 */ 339 function _version() 340 { 341 return "SELECT @@VERSION AS ver"; 342 } 343 344 // -------------------------------------------------------------------- 345 346 /** 347 * "Count All" query 348 * 349 * Generates a platform-specific query string that counts all records in 350 * the specified database 351 * 352 * @access public 353 * @param string 354 * @return string 355 */ 356 function count_all($table = '') 357 { 358 if ($table == '') 359 { 360 return 0; 361 } 362 363 $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows') . " FROM " . $this->_protect_identifiers($table, TRUE, NULL, FALSE)); 364 365 if ($query->num_rows() == 0) 366 { 367 return 0; 368 } 369 370 $row = $query->row(); 371 return (int) $row->numrows; 372 } 373 374 // -------------------------------------------------------------------- 375 376 /** 377 * List table query 378 * 379 * Generates a platform-specific query string so that the table names can be fetched 380 * 381 * @access private 382 * @param boolean 383 * @return string 384 */ 385 function _list_tables($prefix_limit = FALSE) 386 { 387 $sql = "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name"; 388 389 // for future compatibility 390 if ($prefix_limit !== FALSE AND $this->dbprefix != '') 391 { 392 //$sql .= " LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_char); 393 return FALSE; // not currently supported 394 } 395 396 return $sql; 397 } 398 399 // -------------------------------------------------------------------- 400 401 /** 402 * List column query 403 * 404 * Generates a platform-specific query string so that the column names can be fetched 405 * 406 * @access private 407 * @param string the table name 408 * @return string 409 */ 410 function _list_columns($table = '') 411 { 412 return "SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$table."'"; 413 } 414 415 // -------------------------------------------------------------------- 416 417 /** 418 * Field data query 419 * 420 * Generates a platform-specific query so that the column data can be retrieved 421 * 422 * @access public 423 * @param string the table name 424 * @return object 425 */ 426 function _field_data($table) 427 { 428 return "SELECT TOP 1 * FROM ".$table; 429 } 430 431 // -------------------------------------------------------------------- 432 433 /** 434 * The error message string 435 * 436 * @access private 437 * @return string 438 */ 439 function _error_message() 440 { 441 return mssql_get_last_message(); 442 } 443 444 // -------------------------------------------------------------------- 445 446 /** 447 * The error message number 448 * 449 * @access private 450 * @return integer 451 */ 452 function _error_number() 453 { 454 // Are error numbers supported? 455 return ''; 456 } 457 458 // -------------------------------------------------------------------- 459 460 /** 461 * Escape the SQL Identifiers 462 * 463 * This function escapes column and table names 464 * 465 * @access private 466 * @param string 467 * @return string 468 */ 469 function _escape_identifiers($item) 470 { 471 if ($this->_escape_char == '') 472 { 473 return $item; 474 } 475 476 foreach ($this->_reserved_identifiers as $id) 477 { 478 if (strpos($item, '.'.$id) !== FALSE) 479 { 480 $str = $this->_escape_char. str_replace('.', $this->_escape_char.'.', $item); 481 482 // remove duplicates if the user already included the escape 483 return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str); 484 } 485 } 486 487 if (strpos($item, '.') !== FALSE) 488 { 489 $str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char; 490 } 491 else 492 { 493 $str = $this->_escape_char.$item.$this->_escape_char; 494 } 495 496 // remove duplicates if the user already included the escape 497 return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str); 498 } 499 500 // -------------------------------------------------------------------- 501 502 /** 503 * From Tables 504 * 505 * This function implicitly groups FROM tables so there is no confusion 506 * about operator precedence in harmony with SQL standards 507 * 508 * @access public 509 * @param type 510 * @return type 511 */ 512 function _from_tables($tables) 513 { 514 if ( ! is_array($tables)) 515 { 516 $tables = array($tables); 517 } 518 519 return implode(', ', $tables); 520 } 521 522 // -------------------------------------------------------------------- 523 524 /** 525 * Insert statement 526 * 527 * Generates a platform-specific insert string from the supplied data 528 * 529 * @access public 530 * @param string the table name 531 * @param array the insert keys 532 * @param array the insert values 533 * @return string 534 */ 535 function _insert($table, $keys, $values) 536 { 537 return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")"; 538 } 539 540 // -------------------------------------------------------------------- 541 542 /** 543 * Update statement 544 * 545 * Generates a platform-specific update string from the supplied data 546 * 547 * @access public 548 * @param string the table name 549 * @param array the update data 550 * @param array the where clause 551 * @param array the orderby clause 552 * @param array the limit clause 553 * @return string 554 */ 555 function _update($table, $values, $where, $orderby = array(), $limit = FALSE) 556 { 557 foreach($values as $key => $val) 558 { 559 $valstr[] = $key." = ".$val; 560 } 561 562 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit; 563 564 $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):''; 565 566 $sql = "UPDATE ".$table." SET ".implode(', ', $valstr); 567 568 $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : ''; 569 570 $sql .= $orderby.$limit; 571 572 return $sql; 573 } 574 575 576 // -------------------------------------------------------------------- 577 578 /** 579 * Truncate statement 580 * 581 * Generates a platform-specific truncate string from the supplied data 582 * If the database does not support the truncate() command 583 * This function maps to "DELETE FROM table" 584 * 585 * @access public 586 * @param string the table name 587 * @return string 588 */ 589 function _truncate($table) 590 { 591 return "TRUNCATE ".$table; 592 } 593 594 // -------------------------------------------------------------------- 595 596 /** 597 * Delete statement 598 * 599 * Generates a platform-specific delete string from the supplied data 600 * 601 * @access public 602 * @param string the table name 603 * @param array the where clause 604 * @param string the limit clause 605 * @return string 606 */ 607 function _delete($table, $where = array(), $like = array(), $limit = FALSE) 608 { 609 $conditions = ''; 610 611 if (count($where) > 0 OR count($like) > 0) 612 { 613 $conditions = "\nWHERE "; 614 $conditions .= implode("\n", $this->ar_where); 615 616 if (count($where) > 0 && count($like) > 0) 617 { 618 $conditions .= " AND "; 619 } 620 $conditions .= implode("\n", $like); 621 } 622 623 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit; 624 625 return "DELETE FROM ".$table.$conditions.$limit; 626 } 627 628 // -------------------------------------------------------------------- 629 630 /** 631 * Limit string 632 * 633 * Generates a platform-specific LIMIT clause 634 * 635 * @access public 636 * @param string the sql query string 637 * @param integer the number of rows to limit the query to 638 * @param integer the offset value 639 * @return string 640 */ 641 function _limit($sql, $limit, $offset) 642 { 643 $i = $limit + $offset; 644 645 return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql); 646 } 647 648 // -------------------------------------------------------------------- 649 650 /** 651 * Close DB Connection 652 * 653 * @access public 654 * @param resource 655 * @return void 656 */ 657 function _close($conn_id) 658 { 659 @mssql_close($conn_id); 660 } 661 662} 663 664 665 666/* End of file mssql_driver.php */ 667/* Location: ./system/database/drivers/mssql/mssql_driver.php */