[Summary view] [Print] [Text view]

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