[Summary view]

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

   1  <?php
   2  
   3  /**
   4    @version   v5.21.0  2021-02-27
   5    @copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
   6    @copyright (c) 2014      Damien Regad, Mark Newnham and the ADOdb community
   7    Released under both BSD license and Lesser GPL library license.
   8    Whenever there is any discrepancy between the two licenses,
   9    the BSD license will take precedence.
  10  
  11    Set tabs to 4 for best viewing.
  12  
  13  */
  14  
  15  /*
  16  In ADOdb, named quotes for MS SQL Server use ". From the MSSQL Docs:
  17  
  18  	 Note Delimiters are for identifiers only. Delimiters cannot be used for keywords,
  19  	 whether or not they are marked as reserved in SQL Server.
  20  
  21  	 Quoted identifiers are delimited by double quotation marks ("):
  22  	 SELECT * FROM "Blanks in Table Name"
  23  
  24  	 Bracketed identifiers are delimited by brackets ([ ]):
  25  	 SELECT * FROM [Blanks In Table Name]
  26  
  27  	 Quoted identifiers are valid only when the QUOTED_IDENTIFIER option is set to ON. By default,
  28  	 the Microsoft OLE DB Provider for SQL Server and SQL Server ODBC driver set QUOTED_IDENTIFIER ON
  29  	 when they connect.
  30  
  31  	 In Transact-SQL, the option can be set at various levels using SET QUOTED_IDENTIFIER,
  32  	 the quoted identifier option of sp_dboption, or the user options option of sp_configure.
  33  
  34  	 When SET ANSI_DEFAULTS is ON, SET QUOTED_IDENTIFIER is enabled.
  35  
  36  	 Syntax
  37  
  38  	 	 SET QUOTED_IDENTIFIER { ON | OFF }
  39  
  40  
  41  */
  42  
  43  // security - hide paths
  44  if (!defined('ADODB_DIR')) die();
  45  
  46  class ADODB2_mssqlnative extends ADODB_DataDict {
  47  	 var $databaseType = 'mssqlnative';
  48  	 var $dropIndex = 'DROP INDEX %1$s ON %2$s';
  49  	 var $renameTable = "EXEC sp_rename '%s','%s'";
  50  	 var $renameColumn = "EXEC sp_rename '%s.%s','%s'";
  51  	 var $typeX = 'TEXT';  ## Alternatively, set it to VARCHAR(4000)
  52  	 var $typeXL = 'TEXT';
  53  
  54  	 //var $alterCol = ' ALTER COLUMN ';
  55  
  56  	 public $blobAllowsDefaultValue = true;
  57  	 public $blobAllowsNotNull      = true;
  58  
  59  	function MetaType($t,$len=-1,$fieldobj=false)
  60  	 {
  61  	 	 if (is_object($t)) {
  62  	 	 	 $fieldobj = $t;
  63  	 	 	 $t = $fieldobj->type;
  64  	 	 	 $len = $fieldobj->max_length;
  65  	 	 }
  66  
  67  	 	 $_typeConversion = array(
  68  	 	 	 -155 => 'D',
  69  	 	 	   93 => 'D',
  70  	 	 	 -154 => 'D',
  71  	 	 	   -2 => 'D',
  72  	 	 	   91 => 'D',
  73  
  74  	 	 	   12 => 'C',
  75  	 	 	    1 => 'C',
  76  	 	 	   -9 => 'C',
  77  	 	 	   -8 => 'C',
  78  
  79  	 	 	   -7 => 'L',
  80  	 	 	   -6 => 'I2',
  81  	 	 	   -5 => 'I8',
  82  	 	 	  -11 => 'I',
  83  	 	 	    4 => 'I',
  84  	 	 	    5 => 'I4',
  85  
  86  	 	 	   -1 => 'X',
  87  	 	 	  -10 => 'X',
  88  
  89  	 	 	    2 => 'N',
  90  	 	 	    3 => 'N',
  91  	 	 	    6 => 'N',
  92  	 	 	    7 => 'N',
  93  
  94  	 	 	 -152 => 'X',
  95  	 	 	 -151 => 'X',
  96  	 	 	   -4 => 'X',
  97  	 	 	   -3 => 'X'
  98  	 	 	 );
  99  
 100  	 	 if (isset($_typeConversion[$t]))
 101  	 	 return $_typeConversion[$t];
 102  	 	 
 103  	 	 return ADODB_DEFAULT_METATYPE;
 104  
 105  	 }
 106  
 107  	function ActualType($meta)
 108  	 {
 109  	 	 $DATE_TYPE = 'DATETIME';
 110  
 111  	 	 switch(strtoupper($meta)) {
 112  
 113  	 	 case 'C': return 'VARCHAR';
 114  	 	 case 'XL': return (isset($this)) ? $this->typeXL : 'TEXT';
 115  	 	 case 'X': return (isset($this)) ? $this->typeX : 'TEXT'; ## could be varchar(8000), but we want compat with oracle
 116  	 	 case 'C2': return 'NVARCHAR';
 117  	 	 case 'X2': return 'NTEXT';
 118  
 119  	 	 case 'B': return 'IMAGE';
 120  
 121  	 	 case 'D': return $DATE_TYPE;
 122  	 	 case 'T': return 'TIME';
 123  	 	 case 'L': return 'BIT';
 124  
 125  	 	 case 'R':
 126  	 	 case 'I': return 'INT';
 127  	 	 case 'I1': return 'TINYINT';
 128  	 	 case 'I2': return 'SMALLINT';
 129  	 	 case 'I4': return 'INT';
 130  	 	 case 'I8': return 'BIGINT';
 131  
 132  	 	 case 'F': return 'REAL';
 133  	 	 case 'N': return 'NUMERIC';
 134  	 	 default:
 135  	 	 	 return $meta;
 136  	 	 }
 137  	 }
 138  
 139  
 140  	function AddColumnSQL($tabname, $flds)
 141  	 {
 142  	 	 $tabname = $this->TableName ($tabname);
 143  	 	 $f = array();
 144  	 	 list($lines,$pkey) = $this->_GenFields($flds);
 145  	 	 $s = "ALTER TABLE $tabname $this->addCol";
 146  	 	 foreach($lines as $v) {
 147  	 	 	 $f[] = "\n $v";
 148  	 	 }
 149  	 	 $s .= implode(', ',$f);
 150  	 	 $sql[] = $s;
 151  	 	 return $sql;
 152  	 }
 153  
 154  	function DefaultConstraintname($tabname, $colname)
 155  	 {
 156  	 	 $constraintname = false;
 157  	 	 $rs = $this->connection->Execute(
 158  	 	 	 "SELECT name FROM sys.default_constraints
 159  	 	 	 WHERE object_name(parent_object_id) = '$tabname'
 160  	 	 	 AND col_name(parent_object_id, parent_column_id) = '$colname'"
 161  	 	 );
 162  	 	 if ( is_object($rs) ) {
 163  	 	 	 $row = $rs->FetchRow();
 164  	 	 	 $constraintname = $row['name'];
 165  	 	 }
 166  	 	 return $constraintname;
 167  	 }
 168    
 169  	function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 170  	 {
 171  	 	 $tabname = $this->TableName ($tabname);
 172  	 	 $sql = array();
 173  
 174  	 	 list($lines,$pkey,$idxs) = $this->_GenFields($flds);
 175  	 	 $alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
 176  	 	 foreach($lines as $v) {
 177  	 	 	 $not_null = false;
 178  	 	 	 if ($not_null = preg_match('/NOT NULL/i',$v)) {
 179  	 	 	 	 $v = preg_replace('/NOT NULL/i','',$v);
 180  	 	 	 }
 181  	 	 	 if (preg_match('/^([^ ]+) .*DEFAULT (\'[^\']+\'|\"[^\"]+\"|[^ ]+)/',$v,$matches)) {
 182  	 	 	 	 list(,$colname,$default) = $matches;
 183  	 	 	 	 $v = preg_replace('/^' . preg_quote($colname) . '\s/', '', $v);
 184  	 	 	 	 $t = trim(str_replace('DEFAULT '.$default,'',$v));
 185  	 	 	 	 if ( $constraintname = $this->DefaultConstraintname($tabname,$colname) ) {
 186  	 	 	 	 	 $sql[] = 'ALTER TABLE '.$tabname.' DROP CONSTRAINT '. $constraintname;
 187  	 	 	 	 }
 188  	 	 	 	 if ($not_null) {
 189  	 	 	 	 	 $sql[] = $alter . $colname . ' ' . $t  . ' NOT NULL';
 190  	 	 	 	 } else {
 191  	 	 	 	 	 $sql[] = $alter . $colname . ' ' . $t ;
 192  	 	 	 	 }
 193  	 	 	 	 $sql[] = 'ALTER TABLE ' . $tabname
 194  	 	 	 	 	 . ' ADD CONSTRAINT DF__' . $tabname . '__' .  $colname .  '__' . dechex(rand())
 195  	 	 	 	 	 . ' DEFAULT ' . $default . ' FOR ' . $colname;
 196  	 	 	 } else {
 197  	 	 	 	 $colname = strtok($v," ");
 198  	 	 	 	 if ( $constraintname = $this->DefaultConstraintname($tabname,$colname) ) {
 199  	 	 	 	 	 $sql[] = 'ALTER TABLE '.$tabname.' DROP CONSTRAINT '. $constraintname;
 200  	 	 	 	 }
 201  	 	 	 	 if ($not_null) {
 202  	 	 	 	 	 $sql[] = $alter . $v  . ' NOT NULL';
 203  	 	 	 	 } else {
 204  	 	 	 	 	 $sql[] = $alter . $v;
 205  	 	 	 	 }
 206  	 	 	 }
 207  	 	 }
 208  	 	 if (is_array($idxs)) {
 209  	 	 	 foreach($idxs as $idx => $idxdef) {
 210  	 	 	 	 $sql_idxs = $this->CreateIndexSql($idx, $tabname, $idxdef['cols'], $idxdef['opts']);
 211  	 	 	 	 $sql = array_merge($sql, $sql_idxs);
 212  	 	 	 }
 213  	 	 }
 214  	 	 return $sql;
 215  	 }
 216  
 217  
 218  	 /**
 219  	  * Drop a column, syntax is ALTER TABLE table DROP COLUMN column,column
 220  	  *
 221  	  * @param string   $tabname      Table Name
 222  	  * @param string[] $flds         One, or an array of Fields To Drop
 223  	  * @param string   $tableflds    Throwaway value to make the function match the parent
 224  	  * @param string   $tableoptions Throway value to make the function match the parent
 225  	  *
 226  	  * @return string  The SQL necessary to drop the column
 227  	  */
 228  	function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 229  	 {
 230  	 	 $tabname = $this->TableName ($tabname);
 231  	 	 if (!is_array($flds))
 232  	 	 	 $flds = explode(',',$flds);
 233  	 	 $f = array();
 234  	 	 $s = 'ALTER TABLE ' . $tabname;
 235  	 	 foreach($flds as $v) {
 236  	 	 	 if ( $constraintname = $this->DefaultConstraintname($tabname,$v) ) {
 237  	 	 	 	 $sql[] = 'ALTER TABLE ' . $tabname . ' DROP CONSTRAINT ' . $constraintname;
 238  	 	 	 }
 239  	 	 	 $f[] = ' DROP COLUMN ' . $this->NameQuote($v);
 240  	 	 }
 241  	 	 $s .= implode(', ',$f);
 242  	 	 $sql[] = $s;
 243  	 	 return $sql;
 244  	 }
 245  
 246  	 // return string must begin with space
 247  	function _CreateSuffix($fname,&$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
 248  	 {
 249  	 	 $suffix = '';
 250  	 	 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
 251  	 	 if ($fautoinc) $suffix .= ' IDENTITY(1,1)';
 252  	 	 if ($fnotnull) $suffix .= ' NOT NULL';
 253  	 	 else if ($suffix == '') $suffix .= ' NULL';
 254  	 	 if ($fconstraint) $suffix .= ' '.$fconstraint;
 255  	 	 return $suffix;
 256  	 }
 257  
 258  	 /*
 259  CREATE TABLE
 260      [ database_name.[ owner ] . | owner. ] table_name
 261      ( { < column_definition >
 262          | column_name AS computed_column_expression
 263          | < table_constraint > ::= [ CONSTRAINT constraint_name ] }
 264  
 265              | [ { PRIMARY KEY | UNIQUE } [ ,...n ]
 266      )
 267  
 268  [ ON { filegroup | DEFAULT } ]
 269  [ TEXTIMAGE_ON { filegroup | DEFAULT } ]
 270  
 271  < column_definition > ::= { column_name data_type }
 272      [ COLLATE < collation_name > ]
 273      [ [ DEFAULT constant_expression ]
 274          | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
 275      ]
 276      [ ROWGUIDCOL]
 277      [ < column_constraint > ] [ ...n ]
 278  
 279  < column_constraint > ::= [ CONSTRAINT constraint_name ]
 280      { [ NULL | NOT NULL ]
 281          | [ { PRIMARY KEY | UNIQUE }
 282              [ CLUSTERED | NONCLUSTERED ]
 283              [ WITH FILLFACTOR = fillfactor ]
 284              [ON {filegroup | DEFAULT} ] ]
 285          ]
 286          | [ [ FOREIGN KEY ]
 287              REFERENCES ref_table [ ( ref_column ) ]
 288              [ ON DELETE { CASCADE | NO ACTION } ]
 289              [ ON UPDATE { CASCADE | NO ACTION } ]
 290              [ NOT FOR REPLICATION ]
 291          ]
 292          | CHECK [ NOT FOR REPLICATION ]
 293          ( logical_expression )
 294      }
 295  
 296  < table_constraint > ::= [ CONSTRAINT constraint_name ]
 297      { [ { PRIMARY KEY | UNIQUE }
 298          [ CLUSTERED | NONCLUSTERED ]
 299          { ( column [ ASC | DESC ] [ ,...n ] ) }
 300          [ WITH FILLFACTOR = fillfactor ]
 301          [ ON { filegroup | DEFAULT } ]
 302      ]
 303      | FOREIGN KEY
 304          [ ( column [ ,...n ] ) ]
 305          REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
 306          [ ON DELETE { CASCADE | NO ACTION } ]
 307          [ ON UPDATE { CASCADE | NO ACTION } ]
 308          [ NOT FOR REPLICATION ]
 309      | CHECK [ NOT FOR REPLICATION ]
 310          ( search_conditions )
 311      }
 312  
 313  
 314  	 */
 315  
 316  	 /*
 317  	 CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
 318      ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
 319  	 	 [ WITH < index_option > [ ,...n] ]
 320  	 	 [ ON filegroup ]
 321  	 	 < index_option > :: =
 322  	 	     { PAD_INDEX |
 323  	 	         FILLFACTOR = fillfactor |
 324  	 	         IGNORE_DUP_KEY |
 325  	 	         DROP_EXISTING |
 326  	 	     STATISTICS_NORECOMPUTE |
 327  	 	     SORT_IN_TEMPDB
 328  	 	 }
 329  */
 330  	function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
 331  	 {
 332  	 	 $sql = array();
 333  
 334  	 	 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
 335  	 	 	 $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
 336  	 	 	 if ( isset($idxoptions['DROP']) )
 337  	 	 	 	 return $sql;
 338  	 	 }
 339  
 340  	 	 if ( empty ($flds) ) {
 341  	 	 	 return $sql;
 342  	 	 }
 343  
 344  	 	 $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
 345  	 	 $clustered = isset($idxoptions['CLUSTERED']) ? ' CLUSTERED' : '';
 346  
 347  	 	 if ( is_array($flds) )
 348  	 	 	 $flds = implode(', ',$flds);
 349  	 	 $s = 'CREATE' . $unique . $clustered . ' INDEX ' . $idxname . ' ON ' . $tabname . ' (' . $flds . ')';
 350  
 351  	 	 if ( isset($idxoptions[$this->upperName]) )
 352  	 	 	 $s .= $idxoptions[$this->upperName];
 353  
 354  
 355  	 	 $sql[] = $s;
 356  
 357  	 	 return $sql;
 358  	 }
 359  
 360  
 361  	function _GetSize($ftype, $ty, $fsize, $fprec,$options=false)
 362  	 {
 363  	 	 switch ($ftype) {
 364  	 	 case 'INT':
 365  	 	 case 'SMALLINT':
 366  	 	 case 'TINYINT':
 367  	 	 case 'BIGINT':
 368  	 	 	 return $ftype;
 369  	 	 }
 370      	 if ($ty == 'T') return $ftype;
 371      	 return parent::_GetSize($ftype, $ty, $fsize, $fprec, $options);
 372  
 373  	 }
 374  }