[Summary view]

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

   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  	 	 $_typeConversion = array(
  76  	 	 	 -155 => 'D',
  77  	 	 	   93 => 'D',
  78  	 	 	 -154 => 'D',
  79  	 	 	   -2 => 'D',
  80  	 	 	   91 => 'D',
  81  
  82  	 	 	   12 => 'C',
  83  	 	 	    1 => 'C',
  84  	 	 	   -9 => 'C',
  85  	 	 	   -8 => 'C',
  86  
  87  	 	 	   -7 => 'L',
  88  	 	 	   -6 => 'I2',
  89  	 	 	   -5 => 'I8',
  90  	 	 	  -11 => 'I',
  91  	 	 	    4 => 'I',
  92  	 	 	    5 => 'I4',
  93  
  94  	 	 	   -1 => 'X',
  95  	 	 	  -10 => 'X',
  96  
  97  	 	 	    2 => 'N',
  98  	 	 	    3 => 'N',
  99  	 	 	    6 => 'N',
 100  	 	 	    7 => 'N',
 101  
 102  	 	 	 -152 => 'X',
 103  	 	 	 -151 => 'X',
 104  	 	 	   -4 => 'X',
 105  	 	 	   -3 => 'X'
 106  	 	 	 );
 107  
 108  	 	 if (isset($_typeConversion[$t])) {
 109  	 	 	 return $_typeConversion[$t];
 110  	 	 }
 111  
 112  	 	 return ADODB_DEFAULT_METATYPE;
 113  	 }
 114  
 115  	function ActualType($meta)
 116  	 {
 117  	 	 $DATE_TYPE = 'DATETIME';
 118  
 119  	 	 switch(strtoupper($meta)) {
 120  
 121  	 	 case 'C': return 'VARCHAR';
 122  	 	 case 'XL': return (isset($this)) ? $this->typeXL : 'TEXT';
 123  	 	 case 'X': return (isset($this)) ? $this->typeX : 'TEXT'; ## could be varchar(8000), but we want compat with oracle
 124  	 	 case 'C2': return 'NVARCHAR';
 125  	 	 case 'X2': return 'NTEXT';
 126  
 127  	 	 case 'B': return 'IMAGE';
 128  
 129  	 	 case 'D': return $DATE_TYPE;
 130  	 	 case 'T': return 'TIME';
 131  	 	 case 'L': return 'BIT';
 132  
 133  	 	 case 'R':
 134  	 	 case 'I': return 'INT';
 135  	 	 case 'I1': return 'TINYINT';
 136  	 	 case 'I2': return 'SMALLINT';
 137  	 	 case 'I4': return 'INT';
 138  	 	 case 'I8': return 'BIGINT';
 139  
 140  	 	 case 'F': return 'REAL';
 141  	 	 case 'N': return 'NUMERIC';
 142  	 	 default:
 143  	 	 	 return $meta;
 144  	 	 }
 145  	 }
 146  
 147  
 148  	function AddColumnSQL($tabname, $flds)
 149  	 {
 150  	 	 $tabname = $this->TableName ($tabname);
 151  	 	 $f = array();
 152  	 	 list($lines,) = $this->_GenFields($flds);
 153  	 	 $s = "ALTER TABLE $tabname $this->addCol";
 154  	 	 foreach($lines as $v) {
 155  	 	 	 $f[] = "\n $v";
 156  	 	 }
 157  	 	 $s .= implode(', ',$f);
 158  	 	 $sql[] = $s;
 159  	 	 return $sql;
 160  	 }
 161  
 162  	 /**
 163  	  * Get a column's default constraint.
 164  	  *
 165  	  * @param string $tabname
 166  	  * @param string $colname
 167  	  * @return string|null The Constraint's name, or null if there is none.
 168  	  */
 169  	function defaultConstraintName($tabname, $colname)
 170  	 {
 171  	 	 $sql = "SELECT name FROM sys.default_constraints
 172  	 	 	 WHERE object_name(parent_object_id) = ?
 173  	 	 	 AND col_name(parent_object_id, parent_column_id) = ?";
 174  	 	 return $this->connection->getOne($sql, [$tabname, $colname]);
 175  	 }
 176  
 177  	function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 178  	 {
 179  	 	 $tabname = $this->TableName ($tabname);
 180  	 	 $sql = array();
 181  
 182  	 	 list($lines,,$idxs) = $this->_GenFields($flds);
 183  	 	 $alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
 184  	 	 foreach($lines as $v) {
 185  	 	 	 if ($not_null = preg_match('/NOT NULL/i',$v)) {
 186  	 	 	 	 $v = preg_replace('/NOT NULL/i','',$v);
 187  	 	 	 }
 188  	 	 	 if (preg_match('/^([^ ]+) .*DEFAULT (\'[^\']+\'|\"[^\"]+\"|[^ ]+)/',$v,$matches)) {
 189  	 	 	 	 list(,$colname,$default) = $matches;
 190  	 	 	 	 $v = preg_replace('/^' . preg_quote($colname) . '\s/', '', $v);
 191  	 	 	 	 $t = trim(str_replace('DEFAULT '.$default,'',$v));
 192  	 	 	 	 if ( $constraintname = $this->defaultConstraintName($tabname,$colname) ) {
 193  	 	 	 	 	 $sql[] = 'ALTER TABLE '.$tabname.' DROP CONSTRAINT '. $constraintname;
 194  	 	 	 	 }
 195  	 	 	 	 if ($not_null) {
 196  	 	 	 	 	 $sql[] = $alter . $colname . ' ' . $t  . ' NOT NULL';
 197  	 	 	 	 } else {
 198  	 	 	 	 	 $sql[] = $alter . $colname . ' ' . $t ;
 199  	 	 	 	 }
 200  	 	 	 	 $sql[] = 'ALTER TABLE ' . $tabname
 201  	 	 	 	 	 . ' ADD CONSTRAINT DF__' . $tabname . '__' .  $colname .  '__' . dechex(rand())
 202  	 	 	 	 	 . ' DEFAULT ' . $default . ' FOR ' . $colname;
 203  	 	 	 } else {
 204  	 	 	 	 $colname = strtok($v," ");
 205  	 	 	 	 if ( $constraintname = $this->defaultConstraintName($tabname,$colname) ) {
 206  	 	 	 	 	 $sql[] = 'ALTER TABLE '.$tabname.' DROP CONSTRAINT '. $constraintname;
 207  	 	 	 	 }
 208  	 	 	 	 if ($not_null) {
 209  	 	 	 	 	 $sql[] = $alter . $v  . ' NOT NULL';
 210  	 	 	 	 } else {
 211  	 	 	 	 	 $sql[] = $alter . $v;
 212  	 	 	 	 }
 213  	 	 	 }
 214  	 	 }
 215  	 	 if (is_array($idxs)) {
 216  	 	 	 foreach($idxs as $idx => $idxdef) {
 217  	 	 	 	 $sql_idxs = $this->CreateIndexSql($idx, $tabname, $idxdef['cols'], $idxdef['opts']);
 218  	 	 	 	 $sql = array_merge($sql, $sql_idxs);
 219  	 	 	 }
 220  	 	 }
 221  	 	 return $sql;
 222  	 }
 223  
 224  
 225  	 /**
 226  	  * Drop a column, syntax is ALTER TABLE table DROP COLUMN column,column
 227  	  *
 228  	  * @param string   $tabname      Table Name
 229  	  * @param string[] $flds         One, or an array of Fields To Drop
 230  	  * @param string   $tableflds    Throwaway value to make the function match the parent
 231  	  * @param string   $tableoptions Throway value to make the function match the parent
 232  	  *
 233  	  * @return string[]  The SQL necessary to drop the column
 234  	  */
 235  	function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 236  	 {
 237  	 	 $tabname = $this->TableName ($tabname);
 238  	 	 if (!is_array($flds)) {
 239  	 	 	 /** @noinspection PhpParamsInspection */
 240  	 	 	 $flds = explode(',', $flds);
 241  	 	 }
 242  	 	 $f = array();
 243  	 	 $s = 'ALTER TABLE ' . $tabname;
 244  	 	 foreach($flds as $v) {
 245  	 	 	 if ( $constraintname = $this->defaultConstraintName($tabname,$v) ) {
 246  	 	 	 	 $sql[] = 'ALTER TABLE ' . $tabname . ' DROP CONSTRAINT ' . $constraintname;
 247  	 	 	 }
 248  	 	 	 $f[] = ' DROP COLUMN ' . $this->NameQuote($v);
 249  	 	 }
 250  	 	 $s .= implode(', ',$f);
 251  	 	 $sql[] = $s;
 252  	 	 return $sql;
 253  	 }
 254  
 255  	 // return string must begin with space
 256  
 257  	 /** @noinspection DuplicatedCode */
 258  	function _CreateSuffix($fname,&$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
 259  	 {
 260  	 	 $suffix = '';
 261  	 	 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
 262  	 	 if ($fautoinc) $suffix .= ' IDENTITY(1,1)';
 263  	 	 if ($fnotnull) $suffix .= ' NOT NULL';
 264  	 	 else if ($suffix == '') $suffix .= ' NULL';
 265  	 	 if ($fconstraint) $suffix .= ' '.$fconstraint;
 266  	 	 return $suffix;
 267  	 }
 268  
 269  	 /** @noinspection DuplicatedCode */
 270  	function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
 271  	 {
 272  	 	 $sql = array();
 273  
 274  	 	 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
 275  	 	 	 $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
 276  	 	 	 if ( isset($idxoptions['DROP']) )
 277  	 	 	 	 return $sql;
 278  	 	 }
 279  
 280  	 	 if ( empty ($flds) ) {
 281  	 	 	 return $sql;
 282  	 	 }
 283  
 284  	 	 $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
 285  	 	 $clustered = isset($idxoptions['CLUSTERED']) ? ' CLUSTERED' : '';
 286  
 287  	 	 if ( is_array($flds) )
 288  	 	 	 $flds = implode(', ',$flds);
 289  	 	 $s = 'CREATE' . $unique . $clustered . ' INDEX ' . $idxname . ' ON ' . $tabname . ' (' . $flds . ')';
 290  
 291  	 	 if ( isset($idxoptions[$this->upperName]) )
 292  	 	 	 $s .= $idxoptions[$this->upperName];
 293  
 294  
 295  	 	 $sql[] = $s;
 296  
 297  	 	 return $sql;
 298  	 }
 299  
 300  
 301  	function _GetSize($ftype, $ty, $fsize, $fprec, $options=false)
 302  	 {
 303  	 	 switch ($ftype) {
 304  	 	 	 case 'INT':
 305  	 	 	 case 'SMALLINT':
 306  	 	 	 case 'TINYINT':
 307  	 	 	 case 'BIGINT':
 308  	 	 	 	 return $ftype;
 309  	 	 }
 310  	 	 if ($ty == 'T') {
 311  	 	 	 return $ftype;
 312  	 	 }
 313  	 	 return parent::_GetSize($ftype, $ty, $fsize, $fprec, $options);
 314  	 }
 315  }