[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_mssql extends ADODB_DataDict {
  47  	 var $databaseType = 'mssql';
  48  	 var $dropIndex = 'DROP INDEX %2$s.%1$s';
  49  	 var $renameTable = "EXEC sp_rename '%s','%s'";
  50  	 var $renameColumn = "EXEC sp_rename '%s.%s','%s'";
  51  
  52  	 var $typeX = 'TEXT';  ## Alternatively, set it to VARCHAR(4000)
  53  	 var $typeXL = 'TEXT';
  54  
  55  	 //var $alterCol = ' ALTER COLUMN ';
  56  
  57  	function MetaType($t,$len=-1,$fieldobj=false)
  58  	 {
  59  	 	 if (is_object($t)) {
  60  	 	 	 $fieldobj = $t;
  61  	 	 	 $t = $fieldobj->type;
  62  	 	 	 $len = $fieldobj->max_length;
  63  	 	 }
  64  
  65  	 	 $len = -1; // mysql max_length is not accurate
  66  	 	 switch (strtoupper($t)) {
  67  	 	 case 'R':
  68  	 	 case 'INT':
  69  	 	 case 'INTEGER': return  'I';
  70  	 	 case 'BIT':
  71  	 	 case 'TINYINT': return  'I1';
  72  	 	 case 'SMALLINT': return 'I2';
  73  	 	 case 'BIGINT':  return  'I8';
  74  	 	 case 'SMALLDATETIME': return 'T';
  75  	 	 case 'REAL':
  76  	 	 case 'FLOAT': return 'F';
  77  	 	 default: return parent::MetaType($t,$len,$fieldobj);
  78  	 	 }
  79  	 }
  80  
  81  	function ActualType($meta)
  82  	 {
  83  	 	 switch(strtoupper($meta)) {
  84  
  85  	 	 case 'C': return 'VARCHAR';
  86  	 	 case 'XL': return (isset($this)) ? $this->typeXL : 'TEXT';
  87  	 	 case 'X': return (isset($this)) ? $this->typeX : 'TEXT'; ## could be varchar(8000), but we want compat with oracle
  88  	 	 case 'C2': return 'NVARCHAR';
  89  	 	 case 'X2': return 'NTEXT';
  90  
  91  	 	 case 'B': return 'IMAGE';
  92  
  93  	 	 case 'D': return 'DATETIME';
  94  
  95  	 	 case 'TS':
  96  	 	 case 'T': return 'DATETIME';
  97  	 	 case 'L': return 'BIT';
  98  
  99  	 	 case 'R':
 100  	 	 case 'I': return 'INT';
 101  	 	 case 'I1': return 'TINYINT';
 102  	 	 case 'I2': return 'SMALLINT';
 103  	 	 case 'I4': return 'INT';
 104  	 	 case 'I8': return 'BIGINT';
 105  
 106  	 	 case 'F': return 'REAL';
 107  	 	 case 'N': return 'NUMERIC';
 108  	 	 default:
 109  	 	 	 return $meta;
 110  	 	 }
 111  	 }
 112  
 113  
 114  	function AddColumnSQL($tabname, $flds)
 115  	 {
 116  	 	 $tabname = $this->TableName ($tabname);
 117  	 	 $f = array();
 118  	 	 list($lines,$pkey) = $this->_GenFields($flds);
 119  	 	 $s = "ALTER TABLE $tabname $this->addCol";
 120  	 	 foreach($lines as $v) {
 121  	 	 	 $f[] = "\n $v";
 122  	 	 }
 123  	 	 $s .= implode(', ',$f);
 124  	 	 $sql[] = $s;
 125  	 	 return $sql;
 126  	 }
 127  
 128  	 /*
 129  	 function AlterColumnSQL($tabname, $flds, $tableflds='', $tableoptions='')
 130  	 {
 131  	 	 $tabname = $this->TableName ($tabname);
 132  	 	 $sql = array();
 133  	 	 list($lines,$pkey) = $this->_GenFields($flds);
 134  	 	 foreach($lines as $v) {
 135  	 	 	 $sql[] = "ALTER TABLE $tabname $this->alterCol $v";
 136  	 	 }
 137  
 138  	 	 return $sql;
 139  	 }
 140  	 */
 141  
 142  	function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 143  	 {
 144  	 	 $tabname = $this->TableName ($tabname);
 145  	 	 if (!is_array($flds))
 146  	 	 	 $flds = explode(',',$flds);
 147  	 	 $f = array();
 148  	 	 $s = 'ALTER TABLE ' . $tabname;
 149  	 	 foreach($flds as $v) {
 150  	 	 	 $f[] = "\n$this->dropCol ".$this->NameQuote($v);
 151  	 	 }
 152  	 	 $s .= implode(', ',$f);
 153  	 	 $sql[] = $s;
 154  	 	 return $sql;
 155  	 }
 156  
 157  	 // return string must begin with space
 158  	function _CreateSuffix($fname,&$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
 159  	 {
 160  	 	 $suffix = '';
 161  	 	 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
 162  	 	 if ($fautoinc) $suffix .= ' IDENTITY(1,1)';
 163  	 	 if ($fnotnull) $suffix .= ' NOT NULL';
 164  	 	 else if ($suffix == '') $suffix .= ' NULL';
 165  	 	 if ($fconstraint) $suffix .= ' '.$fconstraint;
 166  	 	 return $suffix;
 167  	 }
 168  
 169  	 /*
 170  CREATE TABLE
 171      [ database_name.[ owner ] . | owner. ] table_name
 172      ( { < column_definition >
 173          | column_name AS computed_column_expression
 174          | < table_constraint > ::= [ CONSTRAINT constraint_name ] }
 175  
 176              | [ { PRIMARY KEY | UNIQUE } [ ,...n ]
 177      )
 178  
 179  [ ON { filegroup | DEFAULT } ]
 180  [ TEXTIMAGE_ON { filegroup | DEFAULT } ]
 181  
 182  < column_definition > ::= { column_name data_type }
 183      [ COLLATE < collation_name > ]
 184      [ [ DEFAULT constant_expression ]
 185          | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
 186      ]
 187      [ ROWGUIDCOL]
 188      [ < column_constraint > ] [ ...n ]
 189  
 190  < column_constraint > ::= [ CONSTRAINT constraint_name ]
 191      { [ NULL | NOT NULL ]
 192          | [ { PRIMARY KEY | UNIQUE }
 193              [ CLUSTERED | NONCLUSTERED ]
 194              [ WITH FILLFACTOR = fillfactor ]
 195              [ON {filegroup | DEFAULT} ] ]
 196          ]
 197          | [ [ FOREIGN KEY ]
 198              REFERENCES ref_table [ ( ref_column ) ]
 199              [ ON DELETE { CASCADE | NO ACTION } ]
 200              [ ON UPDATE { CASCADE | NO ACTION } ]
 201              [ NOT FOR REPLICATION ]
 202          ]
 203          | CHECK [ NOT FOR REPLICATION ]
 204          ( logical_expression )
 205      }
 206  
 207  < table_constraint > ::= [ CONSTRAINT constraint_name ]
 208      { [ { PRIMARY KEY | UNIQUE }
 209          [ CLUSTERED | NONCLUSTERED ]
 210          { ( column [ ASC | DESC ] [ ,...n ] ) }
 211          [ WITH FILLFACTOR = fillfactor ]
 212          [ ON { filegroup | DEFAULT } ]
 213      ]
 214      | FOREIGN KEY
 215          [ ( column [ ,...n ] ) ]
 216          REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
 217          [ ON DELETE { CASCADE | NO ACTION } ]
 218          [ ON UPDATE { CASCADE | NO ACTION } ]
 219          [ NOT FOR REPLICATION ]
 220      | CHECK [ NOT FOR REPLICATION ]
 221          ( search_conditions )
 222      }
 223  
 224  
 225  	 */
 226  
 227  	 /*
 228  	 CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
 229      ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
 230  	 	 [ WITH < index_option > [ ,...n] ]
 231  	 	 [ ON filegroup ]
 232  	 	 < index_option > :: =
 233  	 	     { PAD_INDEX |
 234  	 	         FILLFACTOR = fillfactor |
 235  	 	         IGNORE_DUP_KEY |
 236  	 	         DROP_EXISTING |
 237  	 	     STATISTICS_NORECOMPUTE |
 238  	 	     SORT_IN_TEMPDB
 239  	 	 }
 240  */
 241  	function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
 242  	 {
 243  	 	 $sql = array();
 244  
 245  	 	 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
 246  	 	 	 $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
 247  	 	 	 if ( isset($idxoptions['DROP']) )
 248  	 	 	 	 return $sql;
 249  	 	 }
 250  
 251  	 	 if ( empty ($flds) ) {
 252  	 	 	 return $sql;
 253  	 	 }
 254  
 255  	 	 $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
 256  	 	 $clustered = isset($idxoptions['CLUSTERED']) ? ' CLUSTERED' : '';
 257  
 258  	 	 if ( is_array($flds) )
 259  	 	 	 $flds = implode(', ',$flds);
 260  	 	 $s = 'CREATE' . $unique . $clustered . ' INDEX ' . $idxname . ' ON ' . $tabname . ' (' . $flds . ')';
 261  
 262  	 	 if ( isset($idxoptions[$this->upperName]) )
 263  	 	 	 $s .= $idxoptions[$this->upperName];
 264  
 265  
 266  	 	 $sql[] = $s;
 267  
 268  	 	 return $sql;
 269  	 }
 270  
 271  
 272  	function _GetSize($ftype, $ty, $fsize, $fprec, $options=false)
 273  	 {
 274  	 	 switch ($ftype) {
 275  	 	 case 'INT':
 276  	 	 case 'SMALLINT':
 277  	 	 case 'TINYINT':
 278  	 	 case 'BIGINT':
 279  	 	 	 return $ftype;
 280  	 	 }
 281      	 if ($ty == 'T') return $ftype;
 282      	 return parent::_GetSize($ftype, $ty, $fsize, $fprec, $options);
 283  
 284  	 }
 285  }