00001 <?php
00002 /* SVN FILE: $Id: dbo__mssql_8php-source.html 675 2008-12-26 00:27:14Z gwoo $ */
00003 
00004 /**
00005  * MS SQL layer for DBO
00006  *
00007  * Long description for file
00008  *
00009  * PHP versions 4 and 5
00010  *
00011  * CakePHP(tm) :  Rapid Development Framework <http://www.cakephp.org/>
00012  * Copyright 2005-2008, Cake Software Foundation, Inc.
00013  *                              1785 E. Sahara Avenue, Suite 490-204
00014  *                              Las Vegas, Nevada 89104
00015  *
00016  * Licensed under The MIT License
00017  * Redistributions of files must retain the above copyright notice.
00018  *
00019  * @filesource
00020  * @copyright       Copyright 2005-2008, Cake Software Foundation, Inc.
00021  * @link                http://www.cakefoundation.org/projects/info/cakephp CakePHP(tm) Project
00022  * @package         cake
00023  * @subpackage      cake.cake.libs.model.dbo
00024  * @since           CakePHP(tm) v 0.10.5.1790
00025  * @version         $Revision: 675 $
00026  * @modifiedby      $LastChangedBy: gwoo $
00027  * @lastmodified    $Date: 2008-12-25 16:27:14 -0800 (Thu, 25 Dec 2008) $
00028  * @license         http://www.opensource.org/licenses/mit-license.php The MIT License
00029  */
00030 
00031 /**
00032  * Short description for class.
00033  *
00034  * Long description for class
00035  *
00036  * @package     cake
00037  * @subpackage  cake.cake.libs.model.dbo
00038  */
00039 class DboMssql extends DboSource {
00040 /**
00041  * Driver description
00042  *
00043  * @var string
00044  */
00045     var $description = "MS SQL DBO Driver";
00046 /**
00047  * Starting quote character for quoted identifiers
00048  *
00049  * @var string
00050  */
00051     var $startQuote = "[";
00052 /**
00053  * Ending quote character for quoted identifiers
00054  *
00055  * @var string
00056  */
00057     var $endQuote = "]";
00058  /**
00059  * Creates a map between field aliases and numeric indexes.  Workaround for the
00060  * SQL Server driver's 30-character column name limitation.
00061  *
00062  * @var array
00063  */
00064     var $__fieldMappings = array();
00065 /**
00066  * Base configuration settings for MS SQL driver
00067  *
00068  * @var array
00069  */
00070     var $_baseConfig = array(
00071         'persistent' => true,
00072         'host' => 'localhost',
00073         'login' => 'root',
00074         'password' => '',
00075         'database' => 'cake',
00076         'port' => '1433',
00077         'connect' => 'mssql_pconnect'
00078     );
00079 /**
00080  * MS SQL column definition
00081  *
00082  * @var array
00083  */
00084     var $columns = array(
00085         'primary_key' => array('name' => 'int IDENTITY (1, 1) NOT NULL'),
00086         'string'    => array('name' => 'varchar', 'limit' => '255'),
00087         'text'      => array('name' => 'text'),
00088         'integer'   => array('name' => 'int', 'formatter' => 'intval'),
00089         'float'     => array('name' => 'float', 'formatter' => 'floatval'),
00090         'datetime'  => array('name' => 'datetime', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
00091         'timestamp' => array('name' => 'timestamp', 'format' => 'Y-m-d H:i:s', 'formatter' => 'date'),
00092         'time'      => array('name' => 'datetime', 'format' => 'H:i:s', 'formatter' => 'date'),
00093         'date'      => array('name' => 'datetime', 'format' => 'Y-m-d', 'formatter' => 'date'),
00094         'binary'    => array('name' => 'image'),
00095         'boolean'   => array('name' => 'bit')
00096     );
00097 /**
00098  * MS SQL DBO driver constructor; sets SQL Server error reporting defaults
00099  *
00100  * @param array $config Configuration data from app/config/databases.php
00101  * @return boolean True if connected successfully, false on error
00102  */
00103     function __construct($config, $autoConnect = true) {
00104         if ($autoConnect) {
00105             if (!function_exists('mssql_min_message_severity')) {
00106                 trigger_error("PHP SQL Server interface is not installed, cannot continue.  For troubleshooting information, see http://php.net/mssql/", E_USER_WARNING);
00107             }
00108             mssql_min_message_severity(15);
00109             mssql_min_error_severity(2);
00110         }
00111         return parent::__construct($config, $autoConnect);
00112     }
00113 /**
00114  * Connects to the database using options in the given configuration array.
00115  *
00116  * @return boolean True if the database could be connected, else false
00117  */
00118     function connect() {
00119         $config = $this->config;
00120 
00121         $os = env('OS');
00122         if (!empty($os) && strpos($os, 'Windows') !== false) {
00123             $sep = ',';
00124         } else {
00125             $sep = ':';
00126         }
00127         $connect = 'mssql_connect';
00128         if ($config['persistent']) {
00129             $connect = 'mssql_pconnect';
00130         }
00131         $this->connected = false;
00132 
00133         if (is_numeric($config['port'])) {
00134             $port = $sep . $config['port']; // Port number
00135         } elseif ($config['port'] === null) {
00136             $port = '';                     // No port - SQL Server 2005
00137         } else {
00138             $port = '\\' . $config['port']; // Named pipe
00139         }
00140         $this->connection = $connect($config['host'] . $port, $config['login'], $config['password']);
00141 
00142         if (mssql_select_db($config['database'], $this->connection)) {
00143             $this->connected = true;
00144         }
00145     }
00146 /**
00147  * Disconnects from database.
00148  *
00149  * @return boolean True if the database could be disconnected, else false
00150  */
00151     function disconnect() {
00152         @mssql_free_result($this->results);
00153         $this->connected = !@mssql_close($this->connection);
00154         return !$this->connected;
00155     }
00156 /**
00157  * Executes given SQL statement.
00158  *
00159  * @param string $sql SQL statement
00160  * @return resource Result resource identifier
00161  * @access protected
00162  */
00163     function _execute($sql) {
00164         return mssql_query($sql, $this->connection);
00165     }
00166 /**
00167  * Returns an array of sources (tables) in the database.
00168  *
00169  * @return array Array of tablenames in the database
00170  */
00171     function listSources() {
00172         $cache = parent::listSources();
00173 
00174         if ($cache != null) {
00175             return $cache;
00176         }
00177 
00178         $result = $this->fetchAll('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES');
00179 
00180         if (!$result || empty($result)) {
00181             return array();
00182         } else {
00183             $tables = array();
00184 
00185             foreach ($result as $table) {
00186                 $tables[] = $table[0]['TABLE_NAME'];
00187             }
00188 
00189             parent::listSources($tables);
00190             return $tables;
00191         }
00192     }
00193 /**
00194  * Returns an array of the fields in given table name.
00195  *
00196  * @param Model $model Model object to describe
00197  * @return array Fields in table. Keys are name and type
00198  */
00199     function describe(&$model) {
00200         $cache = parent::describe($model);
00201 
00202         if ($cache != null) {
00203             return $cache;
00204         }
00205 
00206         $fields = false;
00207         $cols = $this->fetchAll("SELECT COLUMN_NAME as Field, DATA_TYPE as Type, COL_LENGTH('" . $this->fullTableName($model, false) . "', COLUMN_NAME) as Length, IS_NULLABLE As [Null], COLUMN_DEFAULT as [Default], COLUMNPROPERTY(OBJECT_ID('" . $this->fullTableName($model, false) . "'), COLUMN_NAME, 'IsIdentity') as [Key], NUMERIC_SCALE as Size FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" . $this->fullTableName($model, false) . "'", false);
00208 
00209         foreach ($cols as $column) {
00210             $fields[$column[0]['Field']] = array(
00211                 'type' => $this->column($column[0]['Type']),
00212                 'null' => (strtoupper($column[0]['Null']) == 'YES'),
00213                 'default' => $column[0]['Default'],
00214                 'length' => $this->length($column[0]['Type']),
00215             );
00216         }
00217         $this->__cacheDescription($this->fullTableName($model, false), $fields);
00218         return $fields;
00219     }
00220 /**
00221  * Returns a quoted and escaped string of $data for use in an SQL statement.
00222  *
00223  * @param string $data String to be prepared for use in an SQL statement
00224  * @param string $column The column into which this data will be inserted
00225  * @param boolean $safe Whether or not numeric data should be handled automagically if no column data is provided
00226  * @return string Quoted and escaped data
00227  */
00228     function value($data, $column = null, $safe = false) {
00229         $parent = parent::value($data, $column, $safe);
00230 
00231         if ($parent != null) {
00232             return $parent;
00233         }
00234         if ($data === null) {
00235             return 'NULL';
00236         }
00237         if ($data === '') {
00238             return "''";
00239         }
00240 
00241         switch($column) {
00242             case 'boolean':
00243                 $data = $this->boolean((bool)$data);
00244             break;
00245             case 'datetime':
00246                 if ($data && (($timestamp = strtotime($data)) !== false)) {
00247                     $data = date('Y-m-d\TH:i:s', $timestamp);
00248                 }
00249             break;
00250             default:
00251                 if (get_magic_quotes_gpc()) {
00252                     $data = stripslashes(str_replace("'", "''", $data));
00253                 } else {
00254                     $data = str_replace("'", "''", $data);
00255                 }
00256             break;
00257         }
00258 
00259         if (in_array($column, array('integer', 'float')) && is_numeric($data)) {
00260             return $data;
00261         }
00262         return "'" . $data . "'";
00263     }
00264 /**
00265  * Generates the fields list of an SQL query.
00266  *
00267  * @param Model $model
00268  * @param string $alias Alias tablename
00269  * @param mixed $fields
00270  * @return array
00271  */
00272     function fields(&$model, $alias = null, $fields = array(), $quote = true) {
00273         if (empty($alias)) {
00274             $alias = $model->name;
00275         }
00276         $fields = parent::fields($model, $alias, $fields, false);
00277         $count = count($fields);
00278 
00279         if ($count >= 1 && $fields[0] != '*' && strpos($fields[0], 'COUNT(*)') === false) {
00280             for ($i = 0; $i < $count; $i++) {
00281                 $dot = strrpos($fields[$i], '.');
00282                 $fieldAlias = count($this->__fieldMappings);
00283 
00284                 if ($dot === false && !preg_match('/\s+AS\s+/i', $fields[$i])) {
00285                     $this->__fieldMappings[$alias . '__' . $fieldAlias] = $alias . '.' . $fields[$i];
00286                     $fields[$i] = $this->name($alias) . '.' . $this->name($fields[$i]) . ' AS ' . $this->name($alias . '__' . $fieldAlias);
00287                 } elseif (!preg_match('/\s+AS\s+/i', $fields[$i])) {
00288                     $build = explode('.', $fields[$i]);
00289                     $this->__fieldMappings[$build[0] . '__' . $fieldAlias] = $build[0] . '.' . $build[1];
00290                     $fields[$i] = $this->name($build[0]) . '.' . $this->name($build[1]) . ' AS ' . $this->name($build[0] . '__' . $fieldAlias);
00291                 }
00292             }
00293         }
00294         return $fields;
00295     }
00296 /**
00297  * Begin a transaction
00298  *
00299  * @param unknown_type $model
00300  * @return boolean True on success, false on fail
00301  * (i.e. if the database/model does not support transactions).
00302  */
00303     function begin(&$model) {
00304         if (parent::begin($model)) {
00305             if ($this->execute('BEGIN TRANSACTION')) {
00306                 $this->_transactionStarted = true;
00307                 return true;
00308             }
00309         }
00310         return false;
00311     }
00312 /**
00313  * Commit a transaction
00314  *
00315  * @param unknown_type $model
00316  * @return boolean True on success, false on fail
00317  * (i.e. if the database/model does not support transactions,
00318  * or a transaction has not started).
00319  */
00320     function commit(&$model) {
00321         if (parent::commit($model)) {
00322             $this->_transactionStarted = false;
00323             return $this->execute('COMMIT');
00324         }
00325         return false;
00326     }
00327 /**
00328  * Rollback a transaction
00329  *
00330  * @param unknown_type $model
00331  * @return boolean True on success, false on fail
00332  * (i.e. if the database/model does not support transactions,
00333  * or a transaction has not started).
00334  */
00335     function rollback(&$model) {
00336         if (parent::rollback($model)) {
00337             return $this->execute('ROLLBACK');
00338         }
00339         return false;
00340     }
00341 /**
00342  * Removes Identity (primary key) column from update data before returning to parent
00343  *
00344  * @param Model $model
00345  * @param array $fields
00346  * @param array $values
00347  * @return array
00348  */
00349     function update(&$model, $fields = array(), $values = array()) {
00350         foreach ($fields as $i => $field) {
00351             if ($field == $model->primaryKey) {
00352                 unset ($fields[$i]);
00353                 unset ($values[$i]);
00354                 break;
00355             }
00356         }
00357         return parent::update($model, $fields, $values);
00358     }
00359 /**
00360  * Returns a formatted error message from previous database operation.
00361  *
00362  * @return string Error message with error number
00363  */
00364     function lastError() {
00365         $error = mssql_get_last_message($this->connection);
00366 
00367         if ($error) {
00368             if (strpos(strtolower($error), 'changed database') === false) {
00369                 return $error;
00370             }
00371         }
00372         return null;
00373     }
00374 /**
00375  * Returns number of affected rows in previous database operation. If no previous operation exists,
00376  * this returns false.
00377  *
00378  * @return int Number of affected rows
00379  */
00380     function lastAffected() {
00381         if ($this->_result) {
00382             return mssql_rows_affected($this->connection);
00383         }
00384         return null;
00385     }
00386 /**
00387  * Returns number of rows in previous resultset. If no previous resultset exists,
00388  * this returns false.
00389  *
00390  * @return int Number of rows in resultset
00391  */
00392     function lastNumRows() {
00393         if ($this->_result) {
00394             return @mssql_num_rows($this->_result);
00395         }
00396         return null;
00397     }
00398 /**
00399  * Returns the ID generated from the previous INSERT operation.
00400  *
00401  * @param unknown_type $source
00402  * @return in
00403  */
00404     function lastInsertId($source = null) {
00405         $id = $this->fetchRow('SELECT SCOPE_IDENTITY() AS insertID', false);
00406         return $id[0]['insertID'];
00407     }
00408 /**
00409  * Returns a limit statement in the correct format for the particular database.
00410  *
00411  * @param int $limit Limit of results returned
00412  * @param int $offset Offset from which to start results
00413  * @return string SQL limit/offset statement
00414  */
00415     function limit($limit, $offset = null) {
00416         if ($limit) {
00417             $rt = '';
00418             if (!strpos(strtolower($limit), 'top') || strpos(strtolower($limit), 'top') === 0) {
00419                 $rt = ' TOP';
00420             }
00421             $rt .= ' ' . $limit;
00422             if (is_int($offset) && $offset > 0) {
00423                 $rt .= ' OFFSET ' . $offset;
00424             }
00425             return $rt;
00426         }
00427         return null;
00428     }
00429 /**
00430  * Converts database-layer column types to basic types
00431  *
00432  * @param string $real Real database-layer column type (i.e. "varchar(255)")
00433  * @return string Abstract column type (i.e. "string")
00434  */
00435     function column($real) {
00436         if (is_array($real)) {
00437             $col = $real['name'];
00438 
00439             if (isset($real['limit'])) {
00440                 $col .= '(' . $real['limit'] . ')';
00441             }
00442             return $col;
00443         }
00444         $col                = str_replace(')', '', $real);
00445         $limit              = null;
00446         @list($col, $limit) = explode('(', $col);
00447 
00448         if (in_array($col, array('date', 'time', 'datetime', 'timestamp'))) {
00449             return $col;
00450         }
00451 
00452         if ($col == 'bit') {
00453             return 'boolean';
00454         }
00455 
00456         if (strpos($col, 'int') !== false || $col == 'numeric') {
00457             return 'integer';
00458         }
00459 
00460         if (strpos($col, 'char') !== false) {
00461             return 'string';
00462         }
00463 
00464         if (strpos($col, 'text') !== false) {
00465             return 'text';
00466         }
00467 
00468         if (strpos($col, 'binary') !== false || $col == 'image') {
00469             return 'binary';
00470         }
00471 
00472         if (in_array($col, array('float', 'real', 'decimal'))) {
00473             return 'float';
00474         }
00475         return 'text';
00476     }
00477 /**
00478  * Enter description here...
00479  *
00480  * @param unknown_type $results
00481  */
00482     function resultSet(&$results) {
00483         $this->results =& $results;
00484         $this->map = array();
00485         $num_fields = mssql_num_fields($results);
00486         $index = 0;
00487         $j = 0;
00488 
00489         while ($j < $num_fields) {
00490             $column = mssql_field_name($results, $j);
00491 
00492             if (strpos($column, '__')) {
00493                 if (isset($this->__fieldMappings[$column]) && strpos($this->__fieldMappings[$column], '.')) {
00494                     $map = explode('.', $this->__fieldMappings[$column]);
00495                 } elseif (isset($this->__fieldMappings[$column])) {
00496                     $map = array(0, $this->__fieldMappings[$column]);
00497                 } else {
00498                     $map = array(0, $column);
00499                 }
00500                 $this->map[$index++] = $map;
00501             } else {
00502                 $this->map[$index++] = array(0, $column);
00503             }
00504             $j++;
00505         }
00506     }
00507 /**
00508  * Builds final SQL statement
00509  *
00510  * @param array $data Query data
00511  * @return string
00512  */
00513     function renderStatement($data) {
00514         extract($data);
00515         if (preg_match('/offset\s+([0-9]+)/i', $limit, $offset)) {
00516             $limit = preg_replace('/\s*offset.*$/i', '', $limit);
00517             preg_match('/top\s+([0-9]+)/i', $limit, $limitVal);
00518             $offset = intval($offset[1]) + intval($limitVal[1]);
00519             $rOrder = $this->__switchSort($order);
00520             list($order2, $rOrder) = array($this->__mapFields($order), $this->__mapFields($rOrder));
00521             return "SELECT * FROM (SELECT {$limit} * FROM (SELECT TOP {$offset} {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$order}) AS Set1 {$rOrder}) AS Set2 {$order2}";
00522         } else {
00523             return "SELECT {$limit} {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$order}";
00524         }
00525     }
00526 /**
00527  * Reverses the sort direction of ORDER statements to get paging offsets to work correctly
00528  *
00529  * @param string $order
00530  * @return string
00531  * @access private
00532  */
00533     function __switchSort($order) {
00534         $order = preg_replace('/\s+ASC/i', '__tmp_asc__', $order);
00535         $order = preg_replace('/\s+DESC/i', ' ASC', $order);
00536         return preg_replace('/__tmp_asc__/', ' DESC', $order);
00537     }
00538 /**
00539  * Translates field names used for filtering and sorting to shortened names using the field map
00540  *
00541  * @param string $sql A snippet of SQL representing an ORDER or WHERE statement
00542  * @return string The value of $sql with field names replaced
00543  * @access private
00544  */
00545     function __mapFields($sql) {
00546         if (empty($sql) || empty($this->__fieldMappings)) {
00547             return $sql;
00548         }
00549         foreach ($this->__fieldMappings as $key => $val) {
00550             $sql = preg_replace('/' . preg_quote($val) . '/', $this->name($key), $sql);
00551             $sql = preg_replace('/' . preg_quote($this->name($val)) . '/', $this->name($key), $sql);
00552         }
00553         return $sql;
00554     }
00555 /**
00556  * Returns an array of all result rows for a given SQL query.
00557  * Returns false if no rows matched.
00558  *
00559  * @param string $sql SQL statement
00560  * @param boolean $cache Enables returning/storing cached query results
00561  * @return array Array of resultset rows, or false if no rows matched
00562  */
00563     function read(&$model, $queryData = array(), $recursive = null) {
00564         $results = parent::read($model, $queryData, $recursive);
00565         $this->__fieldMappings = array();
00566         $this->__fieldMapBase = null;
00567         return $results;
00568     }
00569 /**
00570  * Fetches the next row from the current result set
00571  *
00572  * @return unknown
00573  */
00574     function fetchResult() {
00575         if ($row = mssql_fetch_row($this->results)) {
00576             $resultRow = array();
00577             $i = 0;
00578 
00579             foreach ($row as $index => $field) {
00580                 list($table, $column) = $this->map[$index];
00581                 $resultRow[$table][$column] = $row[$index];
00582                 $i++;
00583             }
00584             return $resultRow;
00585         } else {
00586             return false;
00587         }
00588     }
00589 /**
00590  * Inserts multiple values into a join table
00591  *
00592  * @param string $table
00593  * @param string $fields
00594  * @param array $values
00595  */
00596     function insertMulti($table, $fields, $values) {
00597         $count = count($values);
00598         for ($x = 0; $x < $count; $x++) {
00599             $this->query("INSERT INTO {$table} ({$fields}) VALUES {$values[$x]}");
00600         }
00601     }
00602 
00603 }
00604 ?>