[Summary view]

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

   1  <?php
   2  
   3  /**
   4    @version   v5.20.16  12-Jan-2020
   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  	function MetaType($t,$len=-1,$fieldobj=false)
  57  	 {
  58  	 	 if (is_object($t)) {
  59  	 	 	 $fieldobj = $t;
  60  	 	 	 $t = $fieldobj->type;
  61  	 	 	 $len = $fieldobj->max_length;
  62  	 	 }
  63  
  64  	 	 $_typeConversion = array(
  65  	 	 	 -155 => 'D',
  66  	 	 	   93 => 'D',
  67  	 	 	 -154 => 'D',
  68  	 	 	   -2 => 'D',
  69  	 	 	   91 => 'D',
  70  
  71  	 	 	   12 => 'C',
  72  	 	 	    1 => 'C',
  73  	 	 	   -9 => 'C',
  74  	 	 	   -8 => 'C',
  75  
  76  	 	 	   -7 => 'L',
  77  	 	 	   -6 => 'I2',
  78  	 	 	   -5 => 'I8',
  79  	 	 	  -11 => 'I',
  80  	 	 	    4 => 'I',
  81  	 	 	    5 => 'I4',
  82  
  83  	 	 	   -1 => 'X',
  84  	 	 	  -10 => 'X',
  85  
  86  	 	 	    2 => 'N',
  87  	 	 	    3 => 'N',
  88  	 	 	    6 => 'N',
  89  	 	 	    7 => 'N',
  90  
  91  	 	 	 -152 => 'X',
  92  	 	 	 -151 => 'X',
  93  	 	 	   -4 => 'X',
  94  	 	 	   -3 => 'X'
  95  	 	 	 );
  96  
  97  	 	 return $_typeConversion($t);
  98  
  99  	 }
 100  
 101  	function ActualType($meta)
 102  	 {
 103  	 	 $DATE_TYPE = 'DATETIME';
 104  
 105  	 	 switch(strtoupper($meta)) {
 106  
 107  	 	 case 'C': return 'VARCHAR';
 108  	 	 case 'XL': return (isset($this)) ? $this->typeXL : 'TEXT';
 109  	 	 case 'X': return (isset($this)) ? $this->typeX : 'TEXT'; ## could be varchar(8000), but we want compat with oracle
 110  	 	 case 'C2': return 'NVARCHAR';
 111  	 	 case 'X2': return 'NTEXT';
 112  
 113  	 	 case 'B': return 'IMAGE';
 114  
 115  	 	 case 'D': return $DATE_TYPE;
 116  	 	 case 'T': return 'TIME';
 117  	 	 case 'L': return 'BIT';
 118  
 119  	 	 case 'R':
 120  	 	 case 'I': return 'INT';
 121  	 	 case 'I1': return 'TINYINT';
 122  	 	 case 'I2': return 'SMALLINT';
 123  	 	 case 'I4': return 'INT';
 124  	 	 case 'I8': return 'BIGINT';
 125  
 126  	 	 case 'F': return 'REAL';
 127  	 	 case 'N': return 'NUMERIC';
 128  	 	 default:
 129  	 	 	 print "RETURN $meta";
 130  	 	 	 return $meta;
 131  	 	 }
 132  	 }
 133  
 134  
 135  	function AddColumnSQL($tabname, $flds)
 136  	 {
 137  	 	 $tabname = $this->TableName ($tabname);
 138  	 	 $f = array();
 139  	 	 list($lines,$pkey) = $this->_GenFields($flds);
 140  	 	 $s = "ALTER TABLE $tabname $this->addCol";
 141  	 	 foreach($lines as $v) {
 142  	 	 	 $f[] = "\n $v";
 143  	 	 }
 144  	 	 $s .= implode(', ',$f);
 145  	 	 $sql[] = $s;
 146  	 	 return $sql;
 147  	 }
 148  
 149  	function DefaultConstraintname($tabname, $colname)
 150  	 {
 151  	 	 $constraintname = false;
 152  	 	 $rs = $this->connection->Execute(
 153  	 	 	 "SELECT name FROM sys.default_constraints
 154  	 	 	 WHERE object_name(parent_object_id) = '$tabname'
 155  	 	 	 AND col_name(parent_object_id, parent_column_id) = '$colname'"
 156  	 	 );
 157  	 	 if ( is_object($rs) ) {
 158  	 	 	 $row = $rs->FetchRow();
 159  	 	 	 $constraintname = $row['name'];
 160  	 	 }
 161  	 	 return $constraintname;
 162  	 }
 163    
 164  	function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 165  	 {
 166  	 	 $tabname = $this->TableName ($tabname);
 167  	 	 $sql = array();
 168  
 169  	 	 list($lines,$pkey,$idxs) = $this->_GenFields($flds);
 170  	 	 $alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
 171  	 	 foreach($lines as $v) {
 172  	 	 	 $not_null = false;
 173  	 	 	 if ($not_null = preg_match('/NOT NULL/i',$v)) {
 174  	 	 	 	 $v = preg_replace('/NOT NULL/i','',$v);
 175  	 	 	 }
 176  	 	 	 if (preg_match('/^([^ ]+) .*DEFAULT (\'[^\']+\'|\"[^\"]+\"|[^ ]+)/',$v,$matches)) {
 177  	 	 	 	 list(,$colname,$default) = $matches;
 178  	 	 	 	 $v = preg_replace('/^' . preg_quote($colname) . '\s/', '', $v);
 179  	 	 	 	 $t = trim(str_replace('DEFAULT '.$default,'',$v));
 180  	 	 	 	 if ( $constraintname = $this->DefaultConstraintname($tabname,$colname) ) {
 181  	 	 	 	 	 $sql[] = 'ALTER TABLE '.$tabname.' DROP CONSTRAINT '. $constraintname;
 182  	 	 	 	 }
 183  	 	 	 	 if ($not_null) {
 184  	 	 	 	 	 $sql[] = $alter . $colname . ' ' . $t  . ' NOT NULL';
 185  	 	 	 	 } else {
 186  	 	 	 	 	 $sql[] = $alter . $colname . ' ' . $t ;
 187  	 	 	 	 }
 188  	 	 	 	 $sql[] = 'ALTER TABLE ' . $tabname
 189  	 	 	 	 	 . ' ADD CONSTRAINT DF__' . $tabname . '__' .  $colname .  '__' . dechex(rand())
 190  	 	 	 	 	 . ' DEFAULT ' . $default . ' FOR ' . $colname;
 191  	 	 	 } else {
 192  	 	 	 	 $colname = strtok($v," ");
 193  	 	 	 	 if ( $constraintname = $this->DefaultConstraintname($tabname,$colname) ) {
 194  	 	 	 	 	 $sql[] = 'ALTER TABLE '.$tabname.' DROP CONSTRAINT '. $constraintname;
 195  	 	 	 	 }
 196  	 	 	 	 if ($not_null) {
 197  	 	 	 	 	 $sql[] = $alter . $v  . ' NOT NULL';
 198  	 	 	 	 } else {
 199  	 	 	 	 	 $sql[] = $alter . $v;
 200  	 	 	 	 }
 201  	 	 	 }
 202  	 	 }
 203  	 	 if (is_array($idxs)) {
 204  	 	 	 foreach($idxs as $idx => $idxdef) {
 205  	 	 	 	 $sql_idxs = $this->CreateIndexSql($idx, $tabname, $idxdef['cols'], $idxdef['opts']);
 206  	 	 	 	 $sql = array_merge($sql, $sql_idxs);
 207  	 	 	 }
 208  	 	 }
 209  	 	 return $sql;
 210  	 }
 211  
 212  
 213  	 /**
 214  	  * Drop a column, syntax is ALTER TABLE table DROP COLUMN column,column
 215  	  *
 216  	  * @param string   $tabname      Table Name
 217  	  * @param string[] $flds         One, or an array of Fields To Drop
 218  	  * @param string   $tableflds    Throwaway value to make the function match the parent
 219  	  * @param string   $tableoptions Throway value to make the function match the parent
 220  	  *
 221  	  * @return string  The SQL necessary to drop the column
 222  	  */
 223  	function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 224  	 {
 225  	 	 $tabname = $this->TableName ($tabname);
 226  	 	 if (!is_array($flds))
 227  	 	 	 $flds = explode(',',$flds);
 228  	 	 $f = array();
 229  	 	 $s = 'ALTER TABLE ' . $tabname;
 230  	 	 foreach($flds as $v) {
 231  	 	 	 if ( $constraintname = $this->DefaultConstraintname($tabname,$v) ) {
 232  	 	 	 	 $sql[] = 'ALTER TABLE ' . $tabname . ' DROP CONSTRAINT ' . $constraintname;
 233  	 	 	 }
 234  	 	 	 $f[] = ' DROP COLUMN ' . $this->NameQuote($v);
 235  	 	 }
 236  	 	 $s .= implode(', ',$f);
 237  	 	 $sql[] = $s;
 238  	 	 return $sql;
 239  	 }
 240  
 241  	 // return string must begin with space
 242  	function _CreateSuffix($fname,&$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
 243  	 {
 244  	 	 $suffix = '';
 245  	 	 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
 246  	 	 if ($fautoinc) $suffix .= ' IDENTITY(1,1)';
 247  	 	 if ($fnotnull) $suffix .= ' NOT NULL';
 248  	 	 else if ($suffix == '') $suffix .= ' NULL';
 249  	 	 if ($fconstraint) $suffix .= ' '.$fconstraint;
 250  	 	 return $suffix;
 251  	 }
 252  
 253  	 /*
 254  CREATE TABLE
 255      [ database_name.[ owner ] . | owner. ] table_name
 256      ( { < column_definition >
 257          | column_name AS computed_column_expression
 258          | < table_constraint > ::= [ CONSTRAINT constraint_name ] }
 259  
 260              | [ { PRIMARY KEY | UNIQUE } [ ,...n ]
 261      )
 262  
 263  [ ON { filegroup | DEFAULT } ]
 264  [ TEXTIMAGE_ON { filegroup | DEFAULT } ]
 265  
 266  < column_definition > ::= { column_name data_type }
 267      [ COLLATE < collation_name > ]
 268      [ [ DEFAULT constant_expression ]
 269          | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
 270      ]
 271      [ ROWGUIDCOL]
 272      [ < column_constraint > ] [ ...n ]
 273  
 274  < column_constraint > ::= [ CONSTRAINT constraint_name ]
 275      { [ NULL | NOT NULL ]
 276          | [ { PRIMARY KEY | UNIQUE }
 277              [ CLUSTERED | NONCLUSTERED ]
 278              [ WITH FILLFACTOR = fillfactor ]
 279              [ON {filegroup | DEFAULT} ] ]
 280          ]
 281          | [ [ FOREIGN KEY ]
 282              REFERENCES ref_table [ ( ref_column ) ]
 283              [ ON DELETE { CASCADE | NO ACTION } ]
 284              [ ON UPDATE { CASCADE | NO ACTION } ]
 285              [ NOT FOR REPLICATION ]
 286          ]
 287          | CHECK [ NOT FOR REPLICATION ]
 288          ( logical_expression )
 289      }
 290  
 291  < table_constraint > ::= [ CONSTRAINT constraint_name ]
 292      { [ { PRIMARY KEY | UNIQUE }
 293          [ CLUSTERED | NONCLUSTERED ]
 294          { ( column [ ASC | DESC ] [ ,...n ] ) }
 295          [ WITH FILLFACTOR = fillfactor ]
 296          [ ON { filegroup | DEFAULT } ]
 297      ]
 298      | FOREIGN KEY
 299          [ ( column [ ,...n ] ) ]
 300          REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
 301          [ ON DELETE { CASCADE | NO ACTION } ]
 302          [ ON UPDATE { CASCADE | NO ACTION } ]
 303          [ NOT FOR REPLICATION ]
 304      | CHECK [ NOT FOR REPLICATION ]
 305          ( search_conditions )
 306      }
 307  
 308  
 309  	 */
 310  
 311  	 /*
 312  	 CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
 313      ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
 314  	 	 [ WITH < index_option > [ ,...n] ]
 315  	 	 [ ON filegroup ]
 316  	 	 < index_option > :: =
 317  	 	     { PAD_INDEX |
 318  	 	         FILLFACTOR = fillfactor |
 319  	 	         IGNORE_DUP_KEY |
 320  	 	         DROP_EXISTING |
 321  	 	     STATISTICS_NORECOMPUTE |
 322  	 	     SORT_IN_TEMPDB
 323  	 	 }
 324  */
 325  	function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
 326  	 {
 327  	 	 $sql = array();
 328  
 329  	 	 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
 330  	 	 	 $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
 331  	 	 	 if ( isset($idxoptions['DROP']) )
 332  	 	 	 	 return $sql;
 333  	 	 }
 334  
 335  	 	 if ( empty ($flds) ) {
 336  	 	 	 return $sql;
 337  	 	 }
 338  
 339  	 	 $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
 340  	 	 $clustered = isset($idxoptions['CLUSTERED']) ? ' CLUSTERED' : '';
 341  
 342  	 	 if ( is_array($flds) )
 343  	 	 	 $flds = implode(', ',$flds);
 344  	 	 $s = 'CREATE' . $unique . $clustered . ' INDEX ' . $idxname . ' ON ' . $tabname . ' (' . $flds . ')';
 345  
 346  	 	 if ( isset($idxoptions[$this->upperName]) )
 347  	 	 	 $s .= $idxoptions[$this->upperName];
 348  
 349  
 350  	 	 $sql[] = $s;
 351  
 352  	 	 return $sql;
 353  	 }
 354  
 355  
 356  	function _GetSize($ftype, $ty, $fsize, $fprec)
 357  	 {
 358  	 	 switch ($ftype) {
 359  	 	 case 'INT':
 360  	 	 case 'SMALLINT':
 361  	 	 case 'TINYINT':
 362  	 	 case 'BIGINT':
 363  	 	 	 return $ftype;
 364  	 	 }
 365      	 if ($ty == 'T') return $ftype;
 366      	 return parent::_GetSize($ftype, $ty, $fsize, $fprec);
 367  
 368  	 }
 369  }