[Sommaire] [Imprimer]

   1  <?php
   2  /* SVN FILE: $Id: dbo_mssql.php 4450 2007-02-05 05:18:05Z phpnut $ */
   3  
   4  /**
   5   * MS SQL layer for DBO
   6   *
   7   * Long description for file
   8   *
   9   * PHP versions 4 and 5
  10   *
  11   * CakePHP(tm) :  Rapid Development Framework <http://www.cakephp.org/>
  12   * Copyright 2005-2007, Cake Software Foundation, Inc.
  13   *                                1785 E. Sahara Avenue, Suite 490-204
  14   *                                Las Vegas, Nevada 89104
  15   *
  16   * Licensed under The MIT License
  17   * Redistributions of files must retain the above copyright notice.
  18   *
  19   * @filesource
  20   * @copyright        Copyright 2005-2007, Cake Software Foundation, Inc.
  21   * @link                http://www.cakefoundation.org/projects/info/cakephp CakePHP(tm) Project
  22   * @package            cake
  23   * @subpackage        cake.cake.libs.model.dbo
  24   * @since            CakePHP(tm) v 0.10.5.1790
  25   * @version            $Revision: 4450 $
  26   * @modifiedby        $LastChangedBy: phpnut $
  27   * @lastmodified    $Date: 2007-02-04 23:18:05 -0600 (Sun, 04 Feb 2007) $
  28   * @license            http://www.opensource.org/licenses/mit-license.php The MIT License
  29   */
  30  
  31  /**
  32   * Include DBO.
  33   */
  34  uses ('model' . DS . 'datasources' . DS . 'dbo_source');
  35  
  36  /**
  37   * Short description for class.
  38   *
  39   * Long description for class
  40   *
  41   * @package        cake
  42   * @subpackage    cake.cake.libs.model.dbo
  43   */
  44  class DboMssql extends DboSource {
  45  /**
  46   * Enter description here...
  47   *
  48   * @var unknown_type
  49   */
  50      var $description = "MS SQL DBO Driver";
  51  
  52  /**
  53   * Enter description here...
  54   *
  55   * @var unknown_type
  56   */
  57      var $startQuote = "[";
  58  /**
  59   * Enter description here...
  60   *
  61   * @var unknown_type
  62   */
  63      var $endQuote = "]";
  64  /**
  65   * Enter description here...
  66   *
  67   * @var unknown_type
  68   */
  69      var $goofyLimit = true;
  70  /**
  71   * Creates a map between field aliases and numeric indexes.  Workaround for the
  72   * SQL Server driver's 30-character column name limitation.
  73   *
  74   * @var array
  75   */
  76      var $__fieldMappings = array();
  77  /**
  78   * Base configuration settings for MS SQL driver
  79   *
  80   * @var array
  81   */
  82      var $_baseConfig = array(
  83          'persistent' => true,
  84          'host' => 'localhost',
  85          'login' => 'root',
  86          'password' => '',
  87          'database' => 'cake',
  88          'port' => '1433',
  89          'connect' => 'mssql_pconnect'
  90      );
  91  /**
  92   * MS SQL column definition
  93   *
  94   * @var array
  95   */
  96      var $columns = array(
  97          'primary_key' => array('name' => 'int IDENTITY (1, 1) NOT NULL'),
  98          'string'    => array('name'  => 'varchar', 'limit' => '255'),
  99          'text'        => array('name' => 'text'),
 100          'integer'    => array('name'      => 'int', 'limit'     => '11', 'formatter' => 'intval'),
 101          'float'        => array('name'      => 'float', 'formatter' => 'floatval'),
 102          'datetime'    => array('name' => 'datetime', 'format'    => 'Y-m-d H:i:s', 'formatter' => 'date'),
 103          'timestamp' => array('name'      => 'timestamp', 'format'    => 'Y-m-d H:i:s', 'formatter' => 'date'),
 104          'time'        => array('name' => 'datetime', 'format'    => 'H:i:s', 'formatter' => 'date'),
 105          'date'        => array('name' => 'datetime', 'format'    => 'Y-m-d', 'formatter' => 'date'),
 106          'binary'    => array('name' => 'image'),
 107          'boolean'    => array('name' => 'bit')
 108      );
 109  /**
 110   * MS SQL DBO driver constructor; sets SQL Server error reporting defaults
 111   *
 112   * @param array $config Configuration data from app/config/databases.php
 113   * @return boolean True if connected successfully, false on error
 114   */
 115  	function __construct($config) {
 116          mssql_min_message_severity(15);
 117          mssql_min_error_severity(2);
 118          return parent::__construct($config);
 119      }
 120  /**
 121   * Connects to the database using options in the given configuration array.
 122   *
 123   * @return boolean True if the database could be connected, else false
 124   */
 125  	function connect() {
 126          $config = $this->config;
 127          $connect = $config['connect'];
 128  
 129          $os = env('OS');
 130          if (!empty($os) && strpos($os, 'Windows') !== false) {
 131              $sep = ',';
 132          } else {
 133              $sep = ':';
 134          }
 135  
 136          $this->connected = false;
 137  
 138          if (is_numeric($config['port'])) {
 139              $port = $sep . $config['port'];    // Port number
 140          } elseif ($config['port'] === null) {
 141              $port = '';                        // No port - SQL Server 2005
 142          } else {
 143              $port = '\\' . $config['port'];    // Named pipe
 144          }
 145  
 146          $this->connection = $connect($config['host'] . $port, $config['login'], $config['password']);
 147  
 148          if (mssql_select_db($config['database'], $this->connection)) {
 149              $this->connected = true;
 150          }
 151      }
 152  
 153  /**
 154   * Disconnects from database.
 155   *
 156   * @return boolean True if the database could be disconnected, else false
 157   */
 158  	function disconnect() {
 159          return @mssql_close($this->connection);
 160      }
 161  /**
 162   * Executes given SQL statement.
 163   *
 164   * @param string $sql SQL statement
 165   * @return resource Result resource identifier
 166   * @access protected
 167   */
 168  	function _execute($sql) {
 169          return mssql_query($sql, $this->connection);
 170      }
 171  /**
 172   * Returns a row from given resultset as an array .
 173   *
 174   * @param bool $assoc Associative array only, or both?
 175   * @return array The fetched row as an array
 176   */
 177  	function fetchRow($assoc = false) {
 178          if (is_resource($this->_result)) {
 179              $this->resultSet($this->_result);
 180              $resultRow = $this->fetchResult();
 181              return $resultRow;
 182          } else {
 183              return null;
 184          }
 185      }
 186  
 187  /**
 188   * Returns an array of sources (tables) in the database.
 189   *
 190   * @return array Array of tablenames in the database
 191   */
 192  	function listSources() {
 193          $cache = parent::listSources();
 194  
 195          if ($cache != null) {
 196              return $cache;
 197          }
 198  
 199          $result = $this->fetchAll('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES');
 200  
 201          if (!$result || empty($result)) {
 202              return array();
 203          } else {
 204              $tables=array();
 205  
 206              foreach($result as $table) {
 207                  $tables[] = $table[0]['TABLE_NAME'];
 208              }
 209  
 210              parent::listSources($tables);
 211              return $tables;
 212          }
 213      }
 214  
 215  /**
 216   * Returns an array of the fields in given table name.
 217   *
 218   * @param Model $model Model object to describe
 219   * @return array Fields in table. Keys are name and type
 220   */
 221  	function describe(&$model) {
 222          $cache = parent::describe($model);
 223  
 224          if ($cache != null) {
 225              return $cache;
 226          }
 227  
 228          $fields = false;
 229          $cols = $this->fetchAll("SELECT COLUMN_NAME as Field, DATA_TYPE as Type, COL_LENGTH('" . $model->tablePrefix . $model->table . "', COLUMN_NAME) as Length, IS_NULLABLE As [Null], COLUMN_DEFAULT as [Default], COLUMNPROPERTY(OBJECT_ID('" . $model->tablePrefix . $model->table . "'), COLUMN_NAME, 'IsIdentity') as [Key], NUMERIC_SCALE as Size FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" . $model->tablePrefix . $model->table . "'", false);
 230  
 231          foreach($cols as $column) {
 232              $field = array('name' => $column[0]['Field'], 'type' => $this->column($column[0]['Type']), 'null' => $column[0]['Null']);
 233              $fields[] = $field;
 234          }
 235          $this->__cacheDescription($model->tablePrefix . $model->table, $fields);
 236          return $fields;
 237      }
 238  
 239  /**
 240   * Returns a quoted name of $data for use in an SQL statement.
 241   *
 242   * @param string $data Name (table.field) to be prepared for use in an SQL statement
 243   * @return string Quoted for MS SQL
 244   */
 245  	function name($data) {
 246          if ($data == '*') {
 247              return '*';
 248          }
 249  
 250          $pos = strpos($data, '[');
 251          if ($pos === false) {
 252              $data = '[' . r('.', '].[', $data) . ']';
 253          }
 254  
 255          $data = r(']]', ']', r('[[', '[', $data));
 256          return $data;
 257      }
 258  
 259  /**
 260   * Returns a quoted and escaped string of $data for use in an SQL statement.
 261   *
 262   * @param string $data String to be prepared for use in an SQL statement
 263   * @param string $column The column into which this data will be inserted
 264   * @param boolean $safe Whether or not numeric data should be handled automagically if no column data is provided
 265   * @return string Quoted and escaped data
 266   */
 267  	function value($data, $column = null, $safe = false) {
 268          $parent = parent::value($data, $column, $safe);
 269  
 270          if ($parent != null) {
 271              return $parent;
 272          }
 273          if ($data === null) {
 274              return 'NULL';
 275          }
 276          if ($data === '') {
 277              return "''";
 278          }
 279  
 280          switch($column) {
 281              case 'boolean':
 282                  $data = $this->boolean((bool)$data);
 283              break;
 284              default:
 285                  if (get_magic_quotes_gpc()) {
 286                      $data = stripslashes(r("'", "''", $data));
 287                  } else {
 288                      $data = r("'", "''", $data);
 289                  }
 290              break;
 291          }
 292          return "'" . $data . "'";
 293      }
 294  
 295  /**
 296   * Generates the fields list of an SQL query.
 297   *
 298   * @param Model $model
 299   * @param string $alias Alias tablename
 300   * @param mixed $fields
 301   * @return array
 302   */
 303  	function fields(&$model, $alias, $fields) {
 304          if (is_array($fields)) {
 305              $fields = $fields;
 306          } else {
 307              if ($fields != null) {
 308                  if (strpos($fields, ',')) {
 309                      $fields = explode(',', $fields);
 310                  } else {
 311                      $fields = array($fields);
 312                  }
 313  
 314                  $fields = array_map('trim', $fields);
 315              } else {
 316                  foreach($model->_tableInfo->value as $field) {
 317                      $fields[] = $field['name'];
 318                  }
 319              }
 320          }
 321  
 322          $count = count($fields);
 323  
 324          if ($count >= 1 && $fields[0] != '*' && strpos($fields[0], 'COUNT(*)') === false) {
 325              for($i = 0; $i < $count; $i++) {
 326                  $dot = strrpos($fields[$i], '.');
 327                  $fieldAlias = count($this->__fieldMappings);
 328  
 329                  if ($dot === false) {
 330                      $this->__fieldMappings[] = $fields[$i];
 331                      $fields[$i] = $this->name($alias) . '.' . $this->name($fields[$i]) . ' AS ' . $this->name($alias . '__' . $fieldAlias);
 332                  } else {
 333                      $build = explode('.', $fields[$i]);
 334                      $this->__fieldMappings[] = $build[0];
 335                      $fields[$i] = $this->name($build[0]) . '.' . $this->name($build[1]) . ' AS ' . $this->name($build[0] . '__' . $fieldAlias);
 336                  }
 337              }
 338          }
 339          return $fields;
 340      }
 341  
 342  /**
 343   * Begin a transaction
 344   *
 345   * @param unknown_type $model
 346   * @return boolean True on success, false on fail
 347   * (i.e. if the database/model does not support transactions).
 348   */
 349  	function begin(&$model) {
 350          if (parent::begin($model)) {
 351              if ($this->execute('BEGIN TRANSACTION')) {
 352                  $this->__transactionStarted = true;
 353                  return true;
 354              }
 355          }
 356          return false;
 357      }
 358  
 359  /**
 360   * Commit a transaction
 361   *
 362   * @param unknown_type $model
 363   * @return boolean True on success, false on fail
 364   * (i.e. if the database/model does not support transactions,
 365   * or a transaction has not started).
 366   */
 367  	function commit(&$model) {
 368          if (parent::commit($model)) {
 369              $this->__transactionStarted = false;
 370              return $this->execute('COMMIT');
 371          }
 372          return false;
 373      }
 374  
 375  /**
 376   * Rollback a transaction
 377   *
 378   * @param unknown_type $model
 379   * @return boolean True on success, false on fail
 380   * (i.e. if the database/model does not support transactions,
 381   * or a transaction has not started).
 382   */
 383  	function rollback(&$model) {
 384          if (parent::rollback($model)) {
 385              return $this->execute('ROLLBACK');
 386          }
 387          return false;
 388      }
 389  
 390  /**
 391   * Removes Identity (primary key) column from update data before returning to parent
 392   *
 393   * @param Model $model
 394   * @param array $fields
 395   * @param array $values
 396   * @return array
 397   */
 398  	function update(&$model, $fields = array(), $values = array()) {
 399          foreach($fields as $i => $field) {
 400              if ($field == $model->primaryKey) {
 401                  unset ($fields[$i]);
 402                  unset ($values[$i]);
 403                  break;
 404              }
 405          }
 406  
 407          return parent::update($model, $fields, $values);
 408      }
 409  
 410  /**
 411   * Returns a formatted error message from previous database operation.
 412   *
 413   * @return string Error message with error number
 414   */
 415  	function lastError() {
 416          $error = mssql_get_last_message($this->connection);
 417  
 418          if ($error) {
 419              if (strpos('changed database', low($error)) !== false) {
 420                  return $error;
 421              }
 422          }
 423  
 424          return null;
 425      }
 426  
 427  /**
 428   * Returns number of affected rows in previous database operation. If no previous operation exists,
 429   * this returns false.
 430   *
 431   * @return int Number of affected rows
 432   */
 433  	function lastAffected() {
 434          if ($this->_result) {
 435              return mssql_rows_affected($this->connection);
 436          }
 437          return null;
 438      }
 439  
 440  /**
 441   * Returns number of rows in previous resultset. If no previous resultset exists,
 442   * this returns false.
 443   *
 444   * @return int Number of rows in resultset
 445   */
 446  	function lastNumRows() {
 447          if ($this->_result) {
 448              return @mssql_num_rows($this->_result);
 449          }
 450          return null;
 451      }
 452  
 453  /**
 454   * Returns the ID generated from the previous INSERT operation.
 455   *
 456   * @param unknown_type $source
 457   * @return in
 458   */
 459  	function lastInsertId($source = null) {
 460          $id = $this->fetchAll('SELECT SCOPE_IDENTITY() AS insertID', false);
 461          return $id[0][0]['insertID'];
 462      }
 463  
 464  /**
 465   * Returns a limit statement in the correct format for the particular database.
 466   *
 467   * @param int $limit Limit of results returned
 468   * @param int $offset Offset from which to start results
 469   * @return string SQL limit/offset statement
 470   */
 471  	function limit($limit, $offset = null) {
 472          if ($limit) {
 473              $rt = '';
 474              if (!strpos(strtolower($limit), 'top') || strpos(strtolower($limit), 'top') === 0) {
 475                  $rt = ' TOP';
 476              }
 477  
 478              $rt .= ' ' . $limit;
 479              return $rt;
 480          }
 481  
 482          return null;
 483      }
 484  
 485  /**
 486   * Converts database-layer column types to basic types
 487   *
 488   * @param string $real Real database-layer column type (i.e. "varchar(255)")
 489   * @return string Abstract column type (i.e. "string")
 490   */
 491  	function column($real) {
 492          if (is_array($real)) {
 493              $col = $real['name'];
 494  
 495              if (isset($real['limit'])) {
 496                  $col .= '(' . $real['limit'] . ')';
 497              }
 498  
 499              return $col;
 500          }
 501          $col               =r(')', '', $real);
 502          $limit             =null;
 503          @list($col, $limit)=explode('(', $col);
 504  
 505          if (in_array($col, array('date', 'time', 'datetime', 'timestamp'))) {
 506              return $col;
 507          }
 508  
 509          if ($col == 'bit') {
 510              return 'boolean';
 511          }
 512  
 513          if (strpos($col, 'int') !== false || $col == 'numeric') {
 514              return 'integer';
 515          }
 516  
 517          if (strpos($col, 'char') !== false) {
 518              return 'string';
 519          }
 520  
 521          if (strpos($col, 'text') !== false) {
 522              return 'text';
 523          }
 524  
 525          if (strpos($col, 'binary') !== false || $col == 'image') {
 526              return 'binary';
 527          }
 528  
 529          if (in_array($col, array('float', 'real', 'decimal'))) {
 530              return 'float';
 531          }
 532          return 'text';
 533      }
 534  
 535  /**
 536   * Enter description here...
 537   *
 538   * @param unknown_type $results
 539   */
 540  	function resultSet(&$results) {
 541          $this->results =& $results;
 542          $this->map = array();
 543          $num_fields = mssql_num_fields($results);
 544          $index = 0;
 545          $j = 0;
 546  
 547          while($j < $num_fields) {
 548              $column = mssql_field_name($results, $j);
 549  
 550              if (strpos($column, '__')) {
 551                  $map = explode('__', $column);
 552                  if (is_numeric($map[1])) {
 553                      $map[1] = $this->__fieldMappings[intval($map[1])];
 554                  }
 555  
 556                  $this->map[$index++] = $map;
 557              } else {
 558                  $this->map[$index++] = array(0, $column);
 559              }
 560  
 561              $j++;
 562          }
 563      }
 564  /**
 565   * Returns an array of all result rows for a given SQL query.
 566   * Returns false if no rows matched.
 567   *
 568   * @param string $sql SQL statement
 569   * @param boolean $cache Enables returning/storing cached query results
 570   * @return array Array of resultset rows, or false if no rows matched
 571   */
 572  	function read(&$model, $queryData = array(), $recursive = null) {
 573          $results = parent::read($model, $queryData, $recursive);
 574          $this->__fieldMappings = array();
 575          return $results;
 576      }
 577  /**
 578   * Fetches the next row from the current result set
 579   *
 580   * @return unknown
 581   */
 582  	function fetchResult() {
 583          if ($row = mssql_fetch_row($this->results)) {
 584              $resultRow = array();
 585              $i = 0;
 586  
 587              foreach($row as $index => $field) {
 588                  list($table, $column) = $this->map[$index];
 589                  $resultRow[$table][$column] = $row[$index];
 590                  $i++;
 591              }
 592  
 593              return $resultRow;
 594          } else {
 595              return false;
 596          }
 597      }
 598  
 599  	function buildSchemaQuery($schema) {
 600          $search = array('{AUTOINCREMENT}', '{PRIMARY}', '{UNSIGNED}', '{FULLTEXT}', '{BOOLEAN}', '{UTF_8}');
 601  
 602          $replace = array('int(11) not null auto_increment', 'primary key', 'unsigned', 'FULLTEXT',
 603          'enum (\'true\', \'false\') NOT NULL default \'true\'', '/*!40100 CHARACTER SET utf8 COLLATE utf8_unicode_ci */');
 604  
 605          $query = trim(r($search, $replace, $schema));
 606          return $query;
 607      }
 608  }
 609  
 610  ?>