[Summary view]

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

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