[Sommaire] [Imprimer]

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