1 <?php

     2 /**

     3  * Zend Framework

     4  *

     5  * LICENSE

     6  *

     7  * This source file is subject to the new BSD license that is bundled

     8  * with this package in the file LICENSE.txt.

     9  * It is also available through the world-wide-web at this URL:

    10  * http://framework.zend.com/license/new-bsd

    11  * If you did not receive a copy of the license and are unable to

    12  * obtain it through the world-wide-web, please send an email

    13  * to license@zend.com so we can send you a copy immediately.

    14  *

    15  * @category   Zend

    16  * @package    Zend_Db

    17  * @subpackage Adapter

    18  * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)

    19  * @license    http://framework.zend.com/license/new-bsd     New BSD License

    20  * @version    $Id: Mssql.php 20096 2010-01-06 02:05:09Z bkarwin $

    21  */

    22 

    23 

    24 /**

    25  * @see Zend_Db_Adapter_Pdo_Abstract

    26  */

    27 require_once 'Zend/Db/Adapter/Pdo/Abstract.php';

    28 

    29 

    30 /**

    31  * Class for connecting to Microsoft SQL Server databases and performing common operations.

    32  *

    33  * @category   Zend

    34  * @package    Zend_Db

    35  * @subpackage Adapter

    36  * @copyright  Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)

    37  * @license    http://framework.zend.com/license/new-bsd     New BSD License

    38  */

    39 class Zend_Db_Adapter_Pdo_Mssql extends Zend_Db_Adapter_Pdo_Abstract

    40 {

    41     /**

    42      * PDO type.

    43      *

    44      * @var string

    45      */

    46     protected $_pdoType = 'mssql';

    47 

    48     /**

    49      * Keys are UPPERCASE SQL datatypes or the constants

    50      * Zend_Db::INT_TYPE, Zend_Db::BIGINT_TYPE, or Zend_Db::FLOAT_TYPE.

    51      *

    52      * Values are:

    53      * 0 = 32-bit integer

    54      * 1 = 64-bit integer

    55      * 2 = float or decimal

    56      *

    57      * @var array Associative array of datatypes to values 0, 1, or 2.

    58      */

    59     protected $_numericDataTypes = array(

    60         Zend_Db::INT_TYPE    => Zend_Db::INT_TYPE,

    61         Zend_Db::BIGINT_TYPE => Zend_Db::BIGINT_TYPE,

    62         Zend_Db::FLOAT_TYPE  => Zend_Db::FLOAT_TYPE,

    63         'INT'                => Zend_Db::INT_TYPE,

    64         'SMALLINT'           => Zend_Db::INT_TYPE,

    65         'TINYINT'            => Zend_Db::INT_TYPE,

    66         'BIGINT'             => Zend_Db::BIGINT_TYPE,

    67         'DECIMAL'            => Zend_Db::FLOAT_TYPE,

    68         'FLOAT'              => Zend_Db::FLOAT_TYPE,

    69         'MONEY'              => Zend_Db::FLOAT_TYPE,

    70         'NUMERIC'            => Zend_Db::FLOAT_TYPE,

    71         'REAL'               => Zend_Db::FLOAT_TYPE,

    72         'SMALLMONEY'         => Zend_Db::FLOAT_TYPE

    73     );

    74 

    75     /**

    76      * Creates a PDO DSN for the adapter from $this->_config settings.

    77      *

    78      * @return string

    79      */

    80     protected function _dsn()

    81     {

    82         // baseline of DSN parts

    83         $dsn = $this->_config;

    84 

    85         // don't pass the username and password in the DSN

    86         unset($dsn['username']);

    87         unset($dsn['password']);

    88         unset($dsn['options']);

    89         unset($dsn['persistent']);

    90         unset($dsn['driver_options']);

    91 

    92         if (isset($dsn['port'])) {

    93             $seperator = ':';

    94             if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {

    95                 $seperator = ',';

    96             }

    97             $dsn['host'] .= $seperator . $dsn['port'];

    98             unset($dsn['port']);

    99         }

   100 

   101         // this driver supports multiple DSN prefixes

   102         // @see http://www.php.net/manual/en/ref.pdo-dblib.connection.php

   103         if (isset($dsn['pdoType'])) {

   104             switch (strtolower($dsn['pdoType'])) {

   105                 case 'freetds':

   106                 case 'sybase':

   107                     $this->_pdoType = 'sybase';

   108                     break;

   109                 case 'mssql':

   110                     $this->_pdoType = 'mssql';

   111                     break;

   112                 case 'dblib':

   113                 default:

   114                     $this->_pdoType = 'dblib';

   115                     break;

   116             }

   117             unset($dsn['pdoType']);

   118         }

   119 

   120         // use all remaining parts in the DSN

   121         foreach ($dsn as $key => $val) {

   122             $dsn[$key] = "$key=$val";

   123         }

   124 

   125         $dsn = $this->_pdoType . ':' . implode(';', $dsn);

   126         return $dsn;

   127     }

   128 

   129     /**

   130      * @return void

   131      */

   132     protected function _connect()

   133     {

   134         if ($this->_connection) {

   135             return;

   136         }

   137         parent::_connect();

   138         $this->_connection->exec('SET QUOTED_IDENTIFIER ON');

   139     }

   140 

   141     /**

   142      * Begin a transaction.

   143      *

   144      * It is necessary to override the abstract PDO transaction functions here, as

   145      * the PDO driver for MSSQL does not support transactions.

   146      */

   147     protected function _beginTransaction()

   148     {

   149         $this->_connect();

   150         $this->_connection->exec('BEGIN TRANSACTION');

   151         return true;

   152     }

   153 

   154     /**

   155      * Commit a transaction.

   156      *

   157      * It is necessary to override the abstract PDO transaction functions here, as

   158      * the PDO driver for MSSQL does not support transactions.

   159      */

   160     protected function _commit()

   161     {

   162         $this->_connect();

   163         $this->_connection->exec('COMMIT TRANSACTION');

   164         return true;

   165     }

   166 

   167     /**

   168      * Roll-back a transaction.

   169      *

   170      * It is necessary to override the abstract PDO transaction functions here, as

   171      * the PDO driver for MSSQL does not support transactions.

   172      */

   173     protected function _rollBack() {

   174         $this->_connect();

   175         $this->_connection->exec('ROLLBACK TRANSACTION');

   176         return true;

   177     }

   178 

   179     /**

   180      * Returns a list of the tables in the database.

   181      *

   182      * @return array

   183      */

   184     public function listTables()

   185     {

   186         $sql = "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name";

   187         return $this->fetchCol($sql);

   188     }

   189 

   190     /**

   191      * Returns the column descriptions for a table.

   192      *

   193      * The return value is an associative array keyed by the column name,

   194      * as returned by the RDBMS.

   195      *

   196      * The value of each array element is an associative array

   197      * with the following keys:

   198      *

   199      * SCHEMA_NAME      => string; name of database or schema

   200      * TABLE_NAME       => string;

   201      * COLUMN_NAME      => string; column name

   202      * COLUMN_POSITION  => number; ordinal position of column in table

   203      * DATA_TYPE        => string; SQL datatype name of column

   204      * DEFAULT          => string; default expression of column, null if none

   205      * NULLABLE         => boolean; true if column can have nulls

   206      * LENGTH           => number; length of CHAR/VARCHAR

   207      * SCALE            => number; scale of NUMERIC/DECIMAL

   208      * PRECISION        => number; precision of NUMERIC/DECIMAL

   209      * UNSIGNED         => boolean; unsigned property of an integer type

   210      * PRIMARY          => boolean; true if column is part of the primary key

   211      * PRIMARY_POSITION => integer; position of column in primary key

   212      * PRIMARY_AUTO     => integer; position of auto-generated column in primary key

   213      *

   214      * @todo Discover column primary key position.

   215      * @todo Discover integer unsigned property.

   216      *

   217      * @param string $tableName

   218      * @param string $schemaName OPTIONAL

   219      * @return array

   220      */

   221     public function describeTable($tableName, $schemaName = null)

   222     {

   223         if ($schemaName != null) {

   224             if (strpos($schemaName, '.') !== false) {

   225                 $result = explode('.', $schemaName);

   226                 $schemaName = $result[1];

   227             }

   228         }

   229         /**

   230          * Discover metadata information about this table.

   231          */

   232         $sql = "exec sp_columns @table_name = " . $this->quoteIdentifier($tableName, true);

   233         if ($schemaName != null) {

   234             $sql .= ", @table_owner = " . $this->quoteIdentifier($schemaName, true);

   235         }

   236 

   237         $stmt = $this->query($sql);

   238         $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);

   239 

   240         $table_name  = 2;

   241         $column_name = 3;

   242         $type_name   = 5;

   243         $precision   = 6;

   244         $length      = 7;

   245         $scale       = 8;

   246         $nullable    = 10;

   247         $column_def  = 12;

   248         $column_position = 16;

   249 

   250         /**

   251          * Discover primary key column(s) for this table.

   252          */

   253         $sql = "exec sp_pkeys @table_name = " . $this->quoteIdentifier($tableName, true);

   254         if ($schemaName != null) {

   255             $sql .= ", @table_owner = " . $this->quoteIdentifier($schemaName, true);

   256         }

   257 

   258         $stmt = $this->query($sql);

   259         $primaryKeysResult = $stmt->fetchAll(Zend_Db::FETCH_NUM);

   260         $primaryKeyColumn = array();

   261         $pkey_column_name = 3;

   262         $pkey_key_seq = 4;

   263         foreach ($primaryKeysResult as $pkeysRow) {

   264             $primaryKeyColumn[$pkeysRow[$pkey_column_name]] = $pkeysRow[$pkey_key_seq];

   265         }

   266 

   267         $desc = array();

   268         $p = 1;

   269         foreach ($result as $key => $row) {

   270             $identity = false;

   271             $words = explode(' ', $row[$type_name], 2);

   272             if (isset($words[0])) {

   273                 $type = $words[0];

   274                 if (isset($words[1])) {

   275                     $identity = (bool) preg_match('/identity/', $words[1]);

   276                 }

   277             }

   278 

   279             $isPrimary = array_key_exists($row[$column_name], $primaryKeyColumn);

   280             if ($isPrimary) {

   281                 $primaryPosition = $primaryKeyColumn[$row[$column_name]];

   282             } else {

   283                 $primaryPosition = null;

   284             }

   285 

   286             $desc[$this->foldCase($row[$column_name])] = array(

   287                 'SCHEMA_NAME'      => null, // @todo

   288                 'TABLE_NAME'       => $this->foldCase($row[$table_name]),

   289                 'COLUMN_NAME'      => $this->foldCase($row[$column_name]),

   290                 'COLUMN_POSITION'  => (int) $row[$column_position],

   291                 'DATA_TYPE'        => $type,

   292                 'DEFAULT'          => $row[$column_def],

   293                 'NULLABLE'         => (bool) $row[$nullable],

   294                 'LENGTH'           => $row[$length],

   295                 'SCALE'            => $row[$scale],

   296                 'PRECISION'        => $row[$precision],

   297                 'UNSIGNED'         => null, // @todo

   298                 'PRIMARY'          => $isPrimary,

   299                 'PRIMARY_POSITION' => $primaryPosition,

   300                 'IDENTITY'         => $identity

   301             );

   302         }

   303         return $desc;

   304     }

   305 

   306     /**

   307      * Adds an adapter-specific LIMIT clause to the SELECT statement.

   308      *

   309      * @link http://lists.bestpractical.com/pipermail/rt-devel/2005-June/007339.html

   310      *

   311      * @param string $sql

   312      * @param integer $count

   313      * @param integer $offset OPTIONAL

   314      * @throws Zend_Db_Adapter_Exception

   315      * @return string

   316      */

   317      public function limit($sql, $count, $offset = 0)

   318      {

   319         $count = intval($count);

   320         if ($count <= 0) {

   321             /** @see Zend_Db_Adapter_Exception */

   322             require_once 'Zend/Db/Adapter/Exception.php';

   323             throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");

   324         }

   325 

   326         $offset = intval($offset);

   327         if ($offset < 0) {

   328             /** @see Zend_Db_Adapter_Exception */

   329             require_once 'Zend/Db/Adapter/Exception.php';

   330             throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");

   331         }

   332 

   333         $sql = preg_replace(

   334             '/^SELECT\s+(DISTINCT\s)?/i',

   335             'SELECT $1TOP ' . ($count+$offset) . ' ',

   336             $sql

   337             );

   338 

   339         if ($offset > 0) {

   340             $orderby = stristr($sql, 'ORDER BY');

   341 

   342             if ($orderby !== false) {

   343                 $orderParts = explode(',', substr($orderby, 8));

   344                 $pregReplaceCount = null;

   345                 $orderbyInverseParts = array();

   346                 foreach ($orderParts as $orderPart) {

   347                     $orderPart = rtrim($orderPart);

   348                     $inv = preg_replace('/\s+desc$/i', ' ASC', $orderPart, 1, $pregReplaceCount);

   349                     if ($pregReplaceCount) {

   350                         $orderbyInverseParts[] = $inv;

   351                         continue;

   352                     }

   353                     $inv = preg_replace('/\s+asc$/i', ' DESC', $orderPart, 1, $pregReplaceCount);

   354                     if ($pregReplaceCount) {

   355                         $orderbyInverseParts[] = $inv;

   356                         continue;

   357                     } else {

   358                         $orderbyInverseParts[] = $orderPart . ' DESC';

   359                     }

   360                 }

   361 

   362                 $orderbyInverse = 'ORDER BY ' . implode(', ', $orderbyInverseParts);

   363             }

   364 

   365 

   366 

   367 

   368             $sql = 'SELECT * FROM (SELECT TOP ' . $count . ' * FROM (' . $sql . ') AS inner_tbl';

   369             if ($orderby !== false) {

   370                 $sql .= ' ' . $orderbyInverse . ' ';

   371             }

   372             $sql .= ') AS outer_tbl';

   373             if ($orderby !== false) {

   374                 $sql .= ' ' . $orderby;

   375             }

   376         }

   377 

   378         return $sql;

   379     }

   380 

   381     /**

   382      * Gets the last ID generated automatically by an IDENTITY/AUTOINCREMENT column.

   383      *

   384      * As a convention, on RDBMS brands that support sequences

   385      * (e.g. Oracle, PostgreSQL, DB2), this method forms the name of a sequence

   386      * from the arguments and returns the last id generated by that sequence.

   387      * On RDBMS brands that support IDENTITY/AUTOINCREMENT columns, this method

   388      * returns the last value generated for such a column, and the table name

   389      * argument is disregarded.

   390      *

   391      * Microsoft SQL Server does not support sequences, so the arguments to

   392      * this method are ignored.

   393      *

   394      * @param string $tableName   OPTIONAL Name of table.

   395      * @param string $primaryKey  OPTIONAL Name of primary key column.

   396      * @return string

   397      * @throws Zend_Db_Adapter_Exception

   398      */

   399     public function lastInsertId($tableName = null, $primaryKey = null)

   400     {

   401         $sql = 'SELECT SCOPE_IDENTITY()';

   402         return (int)$this->fetchOne($sql);

   403     }

   404 

   405     /**

   406      * Retrieve server version in PHP style

   407      * Pdo_Mssql doesn't support getAttribute(PDO::ATTR_SERVER_VERSION)

   408      * @return string

   409      */

   410     public function getServerVersion()

   411     {

   412         try {

   413             $stmt = $this->query("SELECT SERVERPROPERTY('productversion')");

   414             $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);

   415             if (count($result)) {

   416                 return $result[0][0];

   417             }

   418             return null;

   419         } catch (PDOException $e) {

   420             return null;

   421         }

   422     }

   423 }