system/database/drivers/mssql/mssql_driver.php

   1 <?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
   2 /**
   3  * CodeIgniter
   4  *
   5  * An open source application development framework for PHP 5.1.6 or newer
   6  *
   7  * @package             CodeIgniter
   8  * @author              ExpressionEngine Dev Team
   9  * @copyright   Copyright (c) 2008 - 2011, 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  */
  31 class 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                 // Escape single quotes
 265                 $str = str_replace("'", "''", remove_invisible_characters($str));
 266
 267                 // escape LIKE condition wildcards
 268                 if ($like === TRUE)
 269                 {
 270                         $str = str_replace(
 271                                 array($this->_like_escape_chr, '%', '_'),
 272                                 array($this->_like_escape_chr.$this->_like_escape_chr, $this->_like_escape_chr.'%', $this->_like_escape_chr.'_'),
 273                                 $str
 274                         );
 275                 }
 276
 277                 return $str;
 278         }
 279
 280         // --------------------------------------------------------------------
 281
 282         /**
 283          * Affected Rows
 284          *
 285          * @access      public
 286          * @return      integer
 287          */
 288         function affected_rows()
 289         {
 290                 return @mssql_rows_affected($this->conn_id);
 291         }
 292
 293         // --------------------------------------------------------------------
 294
 295         /**
 296         * Insert ID
 297         *
 298         * Returns the last id created in the Identity column.
 299         *
 300         * @access public
 301         * @return integer
 302         */
 303         function insert_id()
 304         {
 305                 $ver = self::_parse_major_version($this->version());
 306                 $sql = ($ver >= 8 ? "SELECT SCOPE_IDENTITY() AS last_id" : "SELECT @@IDENTITY AS last_id");
 307                 $query = $this->query($sql);
 308                 $row = $query->row();
 309                 return $row->last_id;
 310         }
 311
 312         // --------------------------------------------------------------------
 313
 314         /**
 315         * Parse major version
 316         *
 317         * Grabs the major version number from the
 318         * database server version string passed in.
 319         *
 320         * @access private
 321         * @param string $version
 322         * @return int16 major version number
 323         */
 324         function _parse_major_version($version)
 325         {
 326                 preg_match('/([0-9]+)\.([0-9]+)\.([0-9]+)/', $version, $ver_info);
 327                 return $ver_info[1]; // return the major version b/c that's all we're interested in.
 328         }
 329
 330         // --------------------------------------------------------------------
 331
 332         /**
 333         * Version number query string
 334         *
 335         * @access public
 336         * @return string
 337         */
 338         function _version()
 339         {
 340                 return "SELECT @@VERSION AS ver";
 341         }
 342
 343         // --------------------------------------------------------------------
 344
 345         /**
 346          * "Count All" query
 347          *
 348          * Generates a platform-specific query string that counts all records in
 349          * the specified database
 350          *
 351          * @access      public
 352          * @param       string
 353          * @return      string
 354          */
 355         function count_all($table = '')
 356         {
 357                 if ($table == '')
 358                 {
 359                         return 0;
 360                 }
 361
 362                 $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows') . " FROM " . $this->_protect_identifiers($table, TRUE, NULL, FALSE));
 363
 364                 if ($query->num_rows() == 0)
 365                 {
 366                         return 0;
 367                 }
 368
 369                 $row = $query->row();
 370                 $this->_reset_select();
 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_chr);
 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 */