[Summary view] [Print] [Text view]

   1  <?php
   2  
   3  /**
   4    @version   v5.20.3  01-Jan-2016
   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      /*
 150      function AlterColumnSQL($tabname, $flds, $tableflds='', $tableoptions='')
 151      {
 152          $tabname = $this->TableName ($tabname);
 153          $sql = array();
 154          list($lines,$pkey) = $this->_GenFields($flds);
 155          foreach($lines as $v) {
 156              $sql[] = "ALTER TABLE $tabname $this->alterCol $v";
 157          }
 158  
 159          return $sql;
 160      }
 161      */
 162  
 163      /**
 164       * Drop a column, syntax is ALTER TABLE table DROP COLUMN column,column
 165       *
 166       * @param string   $tabname      Table Name
 167       * @param string[] $flds         One, or an array of Fields To Drop
 168       * @param string   $tableflds    Throwaway value to make the function match the parent
 169       * @param string   $tableoptions Throway value to make the function match the parent
 170       *
 171       * @return string  The SQL necessary to drop the column
 172       */
 173  	function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 174      {
 175          $tabname = $this->TableName ($tabname);
 176          if (!is_array($flds))
 177              $flds = explode(',',$flds);
 178          $f = array();
 179          $s = 'ALTER TABLE ' . $tabname . ' DROP COLUMN ';
 180          foreach($flds as $v) {
 181              //$f[] = "\n$this->dropCol ".$this->NameQuote($v);
 182              $f[] = $this->NameQuote($v);
 183          }
 184          $s .= implode(', ',$f);
 185          $sql[] = $s;
 186          return $sql;
 187      }
 188  
 189      // return string must begin with space
 190  	function _CreateSuffix($fname,&$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
 191      {
 192          $suffix = '';
 193          if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
 194          if ($fautoinc) $suffix .= ' IDENTITY(1,1)';
 195          if ($fnotnull) $suffix .= ' NOT NULL';
 196          else if ($suffix == '') $suffix .= ' NULL';
 197          if ($fconstraint) $suffix .= ' '.$fconstraint;
 198          return $suffix;
 199      }
 200  
 201      /*
 202  CREATE TABLE
 203      [ database_name.[ owner ] . | owner. ] table_name
 204      ( { < column_definition >
 205          | column_name AS computed_column_expression
 206          | < table_constraint > ::= [ CONSTRAINT constraint_name ] }
 207  
 208              | [ { PRIMARY KEY | UNIQUE } [ ,...n ]
 209      )
 210  
 211  [ ON { filegroup | DEFAULT } ]
 212  [ TEXTIMAGE_ON { filegroup | DEFAULT } ]
 213  
 214  < column_definition > ::= { column_name data_type }
 215      [ COLLATE < collation_name > ]
 216      [ [ DEFAULT constant_expression ]
 217          | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
 218      ]
 219      [ ROWGUIDCOL]
 220      [ < column_constraint > ] [ ...n ]
 221  
 222  < column_constraint > ::= [ CONSTRAINT constraint_name ]
 223      { [ NULL | NOT NULL ]
 224          | [ { PRIMARY KEY | UNIQUE }
 225              [ CLUSTERED | NONCLUSTERED ]
 226              [ WITH FILLFACTOR = fillfactor ]
 227              [ON {filegroup | DEFAULT} ] ]
 228          ]
 229          | [ [ FOREIGN KEY ]
 230              REFERENCES ref_table [ ( ref_column ) ]
 231              [ ON DELETE { CASCADE | NO ACTION } ]
 232              [ ON UPDATE { CASCADE | NO ACTION } ]
 233              [ NOT FOR REPLICATION ]
 234          ]
 235          | CHECK [ NOT FOR REPLICATION ]
 236          ( logical_expression )
 237      }
 238  
 239  < table_constraint > ::= [ CONSTRAINT constraint_name ]
 240      { [ { PRIMARY KEY | UNIQUE }
 241          [ CLUSTERED | NONCLUSTERED ]
 242          { ( column [ ASC | DESC ] [ ,...n ] ) }
 243          [ WITH FILLFACTOR = fillfactor ]
 244          [ ON { filegroup | DEFAULT } ]
 245      ]
 246      | FOREIGN KEY
 247          [ ( column [ ,...n ] ) ]
 248          REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
 249          [ ON DELETE { CASCADE | NO ACTION } ]
 250          [ ON UPDATE { CASCADE | NO ACTION } ]
 251          [ NOT FOR REPLICATION ]
 252      | CHECK [ NOT FOR REPLICATION ]
 253          ( search_conditions )
 254      }
 255  
 256  
 257      */
 258  
 259      /*
 260      CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
 261      ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
 262          [ WITH < index_option > [ ,...n] ]
 263          [ ON filegroup ]
 264          < index_option > :: =
 265              { PAD_INDEX |
 266                  FILLFACTOR = fillfactor |
 267                  IGNORE_DUP_KEY |
 268                  DROP_EXISTING |
 269              STATISTICS_NORECOMPUTE |
 270              SORT_IN_TEMPDB
 271          }
 272  */
 273  	function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
 274      {
 275          $sql = array();
 276  
 277          if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
 278              $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
 279              if ( isset($idxoptions['DROP']) )
 280                  return $sql;
 281          }
 282  
 283          if ( empty ($flds) ) {
 284              return $sql;
 285          }
 286  
 287          $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
 288          $clustered = isset($idxoptions['CLUSTERED']) ? ' CLUSTERED' : '';
 289  
 290          if ( is_array($flds) )
 291              $flds = implode(', ',$flds);
 292          $s = 'CREATE' . $unique . $clustered . ' INDEX ' . $idxname . ' ON ' . $tabname . ' (' . $flds . ')';
 293  
 294          if ( isset($idxoptions[$this->upperName]) )
 295              $s .= $idxoptions[$this->upperName];
 296  
 297  
 298          $sql[] = $s;
 299  
 300          return $sql;
 301      }
 302  
 303  
 304  	function _GetSize($ftype, $ty, $fsize, $fprec)
 305      {
 306          switch ($ftype) {
 307          case 'INT':
 308          case 'SMALLINT':
 309          case 'TINYINT':
 310          case 'BIGINT':
 311              return $ftype;
 312          }
 313          if ($ty == 'T') return $ftype;
 314          return parent::_GetSize($ftype, $ty, $fsize, $fprec);
 315  
 316      }
 317  }