[Summary view]

Differences Between: [Versions 310 and 402] [Versions 311 and 402] [Versions 39 and 402] [Versions 400 and 402]

   1  <?php
   2  /**
   3   * Data Dictionary for Microsoft SQL Server native (mssqlnative)
   4  
   5   * FileDescription
   6   *
   7   * This file is part of ADOdb, a Database Abstraction Layer library for PHP.
   8   *
   9   * @package ADOdb
  10   * @link https://adodb.org Project's web site and documentation
  11   * @link https://github.com/ADOdb/ADOdb Source code and issue tracker
  12   *
  13   * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause
  14   * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option,
  15   * any later version. This means you can use it in proprietary products.
  16   * See the LICENSE.md file distributed with this source code for details.
  17   * @license BSD-3-Clause
  18   * @license LGPL-2.1-or-later
  19   *
  20   * @copyright 2000-2013 John Lim
  21   * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
  22   */
  23  
  24  /*
  25  In ADOdb, named quotes for MS SQL Server use ". From the MSSQL Docs:
  26  
  27  	 Note Delimiters are for identifiers only. Delimiters cannot be used for keywords,
  28  	 whether or not they are marked as reserved in SQL Server.
  29  
  30  	 Quoted identifiers are delimited by double quotation marks ("):
  31  	 SELECT * FROM "Blanks in Table Name"
  32  
  33  	 Bracketed identifiers are delimited by brackets ([ ]):
  34  	 SELECT * FROM [Blanks In Table Name]
  35  
  36  	 Quoted identifiers are valid only when the QUOTED_IDENTIFIER option is set to ON. By default,
  37  	 the Microsoft OLE DB Provider for SQL Server and SQL Server ODBC driver set QUOTED_IDENTIFIER ON
  38  	 when they connect.
  39  
  40  	 In Transact-SQL, the option can be set at various levels using SET QUOTED_IDENTIFIER,
  41  	 the quoted identifier option of sp_dboption, or the user options option of sp_configure.
  42  
  43  	 When SET ANSI_DEFAULTS is ON, SET QUOTED_IDENTIFIER is enabled.
  44  
  45  	 Syntax
  46  
  47  	 	 SET QUOTED_IDENTIFIER { ON | OFF }
  48  
  49  
  50  */
  51  
  52  // security - hide paths
  53  if (!defined('ADODB_DIR')) die();
  54  
  55  class ADODB2_mssqlnative extends ADODB_DataDict {
  56  	 var $databaseType = 'mssqlnative';
  57  	 var $dropIndex = /** @lang text */ 'DROP INDEX %1$s ON %2$s';
  58  	 var $renameTable = "EXEC sp_rename '%s','%s'";
  59  	 var $renameColumn = "EXEC sp_rename '%s.%s','%s'";
  60  	 var $typeX = 'TEXT';  ## Alternatively, set it to VARCHAR(4000)
  61  	 var $typeXL = 'TEXT';
  62  
  63  	 //var $alterCol = ' ALTER COLUMN ';
  64  
  65  	 public $blobAllowsDefaultValue = true;
  66  	 public $blobAllowsNotNull      = true;
  67  
  68  	function MetaType($t,$len=-1,$fieldobj=false)
  69  	 {
  70  	 	 if (is_object($t)) {
  71  	 	 	 $fieldobj = $t;
  72  	 	 	 $t = $fieldobj->type;
  73  	 	 }
  74  	 	 
  75  	 
  76  	 	 $t = strtoupper($t);
  77  	 	 
  78  	 	 if (array_key_exists($t,$this->connection->customActualTypes))
  79  	 	 	 return  $this->connection->customActualTypes[$t];
  80  	 	 
  81  	 	 $_typeConversion = array(
  82  	 	 	 -155 => 'D',
  83  	 	 	   93 => 'D',
  84  	 	 	 -154 => 'D',
  85  	 	 	   -2 => 'D',
  86  	 	 	   91 => 'D',
  87  
  88  	 	 	   12 => 'C',
  89  	 	 	    1 => 'C',
  90  	 	 	   -9 => 'C',
  91  	 	 	   -8 => 'C',
  92  
  93  	 	 	   -7 => 'L',
  94  	 	 	   -6 => 'I2',
  95  	 	 	   -5 => 'I8',
  96  	 	 	  -11 => 'I',
  97  	 	 	    4 => 'I',
  98  	 	 	    5 => 'I4',
  99  
 100  	 	 	   -1 => 'X',
 101  	 	 	  -10 => 'X',
 102  
 103  	 	 	    2 => 'N',
 104  	 	 	    3 => 'N',
 105  	 	 	    6 => 'N',
 106  	 	 	    7 => 'N',
 107  
 108  	 	 	 -152 => 'X',
 109  	 	 	 -151 => 'X',
 110  	 	 	   -4 => 'X',
 111  	 	 	   -3 => 'X'
 112  	 	 	 );
 113  
 114  	 	 if (isset($_typeConversion[$t])) {
 115  	 	 	 return $_typeConversion[$t];
 116  	 	 }
 117  
 118  	 	 return ADODB_DEFAULT_METATYPE;
 119  	 }
 120  
 121  	function ActualType($meta)
 122  	 {
 123  	 	 $DATE_TYPE = 'DATETIME';
 124  	 	 $meta = strtoupper($meta);
 125  	 	 
 126  	 	 /*
 127  	 	 * Add support for custom meta types. We do this
 128  	 	 * first, that allows us to override existing types
 129  	 	 */
 130  	 	 if (isset($this->connection->customMetaTypes[$meta]))
 131  	 	 	 return $this->connection->customMetaTypes[$meta]['actual'];
 132  	 	 
 133  	 	 switch(strtoupper($meta)) {
 134  
 135  	 	 case 'C': return 'VARCHAR';
 136  	 	 case 'XL': return (isset($this)) ? $this->typeXL : 'TEXT';
 137  	 	 case 'X': return (isset($this)) ? $this->typeX : 'TEXT'; ## could be varchar(8000), but we want compat with oracle
 138  	 	 case 'C2': return 'NVARCHAR';
 139  	 	 case 'X2': return 'NTEXT';
 140  
 141  	 	 case 'B': return 'IMAGE';
 142  
 143  	 	 case 'D': return $DATE_TYPE;
 144  	 	 case 'T': return 'TIME';
 145  	 	 case 'L': return 'BIT';
 146  
 147  	 	 case 'R':
 148  	 	 case 'I': return 'INT';
 149  	 	 case 'I1': return 'TINYINT';
 150  	 	 case 'I2': return 'SMALLINT';
 151  	 	 case 'I4': return 'INT';
 152  	 	 case 'I8': return 'BIGINT';
 153  
 154  	 	 case 'F': return 'REAL';
 155  	 	 case 'N': return 'NUMERIC';
 156  	 	 default:
 157  	 	 	 return $meta;
 158  	 	 }
 159  	 }
 160  
 161  
 162  	function AddColumnSQL($tabname, $flds)
 163  	 {
 164  	 	 $tabname = $this->TableName ($tabname);
 165  	 	 $f = array();
 166  	 	 list($lines,) = $this->_GenFields($flds);
 167  	 	 $s = "ALTER TABLE $tabname $this->addCol";
 168  	 	 foreach($lines as $v) {
 169  	 	 	 $f[] = "\n $v";
 170  	 	 }
 171  	 	 $s .= implode(', ',$f);
 172  	 	 $sql[] = $s;
 173  	 	 return $sql;
 174  	 }
 175  
 176  	 /**
 177  	  * Get a column's default constraint.
 178  	  *
 179  	  * @param string $tabname
 180  	  * @param string $colname
 181  	  * @return string|null The Constraint's name, or null if there is none.
 182  	  */
 183  	function defaultConstraintName($tabname, $colname)
 184  	 {
 185  	 	 $sql = "SELECT name FROM sys.default_constraints
 186  	 	 	 WHERE object_name(parent_object_id) = ?
 187  	 	 	 AND col_name(parent_object_id, parent_column_id) = ?";
 188  	 	 return $this->connection->getOne($sql, [$tabname, $colname]);
 189  	 }
 190  
 191  	function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 192  	 {
 193  	 	 $tabname = $this->TableName ($tabname);
 194  	 	 $sql = array();
 195  
 196  	 	 list($lines,,$idxs) = $this->_GenFields($flds);
 197  	 	 $alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
 198  	 	 foreach($lines as $v) {
 199  	 	 	 if ($not_null = preg_match('/NOT NULL/i',$v)) {
 200  	 	 	 	 $v = preg_replace('/NOT NULL/i','',$v);
 201  	 	 	 }
 202  	 	 	 if (preg_match('/^([^ ]+) .*DEFAULT (\'[^\']+\'|\"[^\"]+\"|[^ ]+)/',$v,$matches)) {
 203  	 	 	 	 list(,$colname,$default) = $matches;
 204  	 	 	 	 $v = preg_replace('/^' . preg_quote($colname) . '\s/', '', $v);
 205  	 	 	 	 $t = trim(str_replace('DEFAULT '.$default,'',$v));
 206  	 	 	 	 if ( $constraintname = $this->defaultConstraintName($tabname,$colname) ) {
 207  	 	 	 	 	 $sql[] = 'ALTER TABLE '.$tabname.' DROP CONSTRAINT '. $constraintname;
 208  	 	 	 	 }
 209  	 	 	 	 if ($not_null) {
 210  	 	 	 	 	 $sql[] = $alter . $colname . ' ' . $t  . ' NOT NULL';
 211  	 	 	 	 } else {
 212  	 	 	 	 	 $sql[] = $alter . $colname . ' ' . $t ;
 213  	 	 	 	 }
 214  	 	 	 	 $sql[] = 'ALTER TABLE ' . $tabname
 215  	 	 	 	 	 . ' ADD CONSTRAINT DF__' . $tabname . '__' .  $colname .  '__' . dechex(rand())
 216  	 	 	 	 	 . ' DEFAULT ' . $default . ' FOR ' . $colname;
 217  	 	 	 } else {
 218  	 	 	 	 $colname = strtok($v," ");
 219  	 	 	 	 if ( $constraintname = $this->defaultConstraintName($tabname,$colname) ) {
 220  	 	 	 	 	 $sql[] = 'ALTER TABLE '.$tabname.' DROP CONSTRAINT '. $constraintname;
 221  	 	 	 	 }
 222  	 	 	 	 if ($not_null) {
 223  	 	 	 	 	 $sql[] = $alter . $v  . ' NOT NULL';
 224  	 	 	 	 } else {
 225  	 	 	 	 	 $sql[] = $alter . $v;
 226  	 	 	 	 }
 227  	 	 	 }
 228  	 	 }
 229  	 	 if (is_array($idxs)) {
 230  	 	 	 foreach($idxs as $idx => $idxdef) {
 231  	 	 	 	 $sql_idxs = $this->CreateIndexSql($idx, $tabname, $idxdef['cols'], $idxdef['opts']);
 232  	 	 	 	 $sql = array_merge($sql, $sql_idxs);
 233  	 	 	 }
 234  	 	 }
 235  	 	 return $sql;
 236  	 }
 237  
 238  
 239  	 /**
 240  	  * Drop a column, syntax is ALTER TABLE table DROP COLUMN column,column
 241  	  *
 242  	  * @param string   $tabname      Table Name
 243  	  * @param string[] $flds         One, or an array of Fields To Drop
 244  	  * @param string   $tableflds    Throwaway value to make the function match the parent
 245  	  * @param string   $tableoptions Throway value to make the function match the parent
 246  	  *
 247  	  * @return string[]  The SQL necessary to drop the column
 248  	  */
 249  	function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 250  	 {
 251  	 	 $tabname = $this->TableName ($tabname);
 252  	 	 if (!is_array($flds)) {
 253  	 	 	 /** @noinspection PhpParamsInspection */
 254  	 	 	 $flds = explode(',', $flds);
 255  	 	 }
 256  	 	 $f = array();
 257  	 	 $s = 'ALTER TABLE ' . $tabname;
 258  	 	 foreach($flds as $v) {
 259  	 	 	 if ( $constraintname = $this->defaultConstraintName($tabname,$v) ) {
 260  	 	 	 	 $sql[] = 'ALTER TABLE ' . $tabname . ' DROP CONSTRAINT ' . $constraintname;
 261  	 	 	 }
 262  	 	 	 $f[] = ' DROP COLUMN ' . $this->NameQuote($v);
 263  	 	 }
 264  	 	 $s .= implode(', ',$f);
 265  	 	 $sql[] = $s;
 266  	 	 return $sql;
 267  	 }
 268  
 269  	 // return string must begin with space
 270  
 271  	 /** @noinspection DuplicatedCode */
 272  	function _CreateSuffix($fname,&$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
 273  	 {
 274  	 	 $suffix = '';
 275  	 	 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
 276  	 	 if ($fautoinc) $suffix .= ' IDENTITY(1,1)';
 277  	 	 if ($fnotnull) $suffix .= ' NOT NULL';
 278  	 	 else if ($suffix == '') $suffix .= ' NULL';
 279  	 	 if ($fconstraint) $suffix .= ' '.$fconstraint;
 280  	 	 return $suffix;
 281  	 }
 282  
 283  	 /** @noinspection DuplicatedCode */
 284  	function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
 285  	 {
 286  	 	 $sql = array();
 287  
 288  	 	 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
 289  	 	 	 $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
 290  	 	 	 if ( isset($idxoptions['DROP']) )
 291  	 	 	 	 return $sql;
 292  	 	 }
 293  
 294  	 	 if ( empty ($flds) ) {
 295  	 	 	 return $sql;
 296  	 	 }
 297  
 298  	 	 $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
 299  	 	 $clustered = isset($idxoptions['CLUSTERED']) ? ' CLUSTERED' : '';
 300  
 301  	 	 if ( is_array($flds) )
 302  	 	 	 $flds = implode(', ',$flds);
 303  	 	 $s = 'CREATE' . $unique . $clustered . ' INDEX ' . $idxname . ' ON ' . $tabname . ' (' . $flds . ')';
 304  
 305  	 	 if ( isset($idxoptions[$this->upperName]) )
 306  	 	 	 $s .= $idxoptions[$this->upperName];
 307  
 308  
 309  	 	 $sql[] = $s;
 310  
 311  	 	 return $sql;
 312  	 }
 313  
 314  
 315  	function _GetSize($ftype, $ty, $fsize, $fprec, $options=false)
 316  	 {
 317  	 	 switch ($ftype) {
 318  	 	 	 case 'INT':
 319  	 	 	 case 'SMALLINT':
 320  	 	 	 case 'TINYINT':
 321  	 	 	 case 'BIGINT':
 322  	 	 	 	 return $ftype;
 323  	 	 }
 324  	 	 if ($ty == 'T') {
 325  	 	 	 return $ftype;
 326  	 	 }
 327  	 	 return parent::_GetSize($ftype, $ty, $fsize, $fprec, $options);
 328  	 }
 329  }