[Summary view]

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

   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  	 	 $t = strtoupper($t);
  73  	 	 
  74  	 	 if (array_key_exists($t,$this->connection->customActualTypes))
  75  	 	 	 return  $this->connection->customActualTypes[$t];
  76  
  77  	 	 $len = -1; // mysql max_length is not accurate
  78  	 	 switch ($t) {
  79  	 	 case 'R':
  80  	 	 case 'INT':
  81  	 	 case 'INTEGER': return  'I';
  82  	 	 case 'BIT':
  83  	 	 case 'TINYINT': return  'I1';
  84  	 	 case 'SMALLINT': return 'I2';
  85  	 	 case 'BIGINT':  return  'I8';
  86  	 	 case 'SMALLDATETIME': return 'T';
  87  	 	 case 'REAL':
  88  	 	 case 'FLOAT': return 'F';
  89  	 	 default: return parent::MetaType($t,$len,$fieldobj);
  90  	 	 }
  91  	 }
  92  
  93  	function ActualType($meta)
  94  	 {
  95  	 	 
  96  	 	 $meta = strtoupper($meta);
  97  	 	 
  98  	 	 /*
  99  	 	 * Add support for custom meta types. We do this
 100  	 	 * first, that allows us to override existing types
 101  	 	 */
 102  	 	 if (isset($this->connection->customMetaTypes[$meta]))
 103  	 	 	 return $this->connection->customMetaTypes[$meta]['actual'];
 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 'DATETIME';
 116  
 117  	 	 case 'TS':
 118  	 	 case 'T': return 'DATETIME';
 119  	 	 case 'L': return 'BIT';
 120  
 121  	 	 case 'R':
 122  	 	 case 'I': return 'INT';
 123  	 	 case 'I1': return 'TINYINT';
 124  	 	 case 'I2': return 'SMALLINT';
 125  	 	 case 'I4': return 'INT';
 126  	 	 case 'I8': return 'BIGINT';
 127  
 128  	 	 case 'F': return 'REAL';
 129  	 	 case 'N': return 'NUMERIC';
 130  	 	 default:
 131  	 	 	 return $meta;
 132  	 	 }
 133  	 }
 134  
 135  
 136  	function AddColumnSQL($tabname, $flds)
 137  	 {
 138  	 	 $tabname = $this->TableName ($tabname);
 139  	 	 $f = array();
 140  	 	 list($lines,$pkey) = $this->_GenFields($flds);
 141  	 	 $s = "ALTER TABLE $tabname $this->addCol";
 142  	 	 foreach($lines as $v) {
 143  	 	 	 $f[] = "\n $v";
 144  	 	 }
 145  	 	 $s .= implode(', ',$f);
 146  	 	 $sql[] = $s;
 147  	 	 return $sql;
 148  	 }
 149  
 150  	 /*
 151  	 function AlterColumnSQL($tabname, $flds, $tableflds='', $tableoptions='')
 152  	 {
 153  	 	 $tabname = $this->TableName ($tabname);
 154  	 	 $sql = array();
 155  	 	 list($lines,$pkey) = $this->_GenFields($flds);
 156  	 	 foreach($lines as $v) {
 157  	 	 	 $sql[] = "ALTER TABLE $tabname $this->alterCol $v";
 158  	 	 }
 159  
 160  	 	 return $sql;
 161  	 }
 162  	 */
 163  
 164  	function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 165  	 {
 166  	 	 $tabname = $this->TableName ($tabname);
 167  	 	 if (!is_array($flds))
 168  	 	 	 $flds = explode(',',$flds);
 169  	 	 $f = array();
 170  	 	 $s = 'ALTER TABLE ' . $tabname;
 171  	 	 foreach($flds as $v) {
 172  	 	 	 $f[] = "\n$this->dropCol ".$this->NameQuote($v);
 173  	 	 }
 174  	 	 $s .= implode(', ',$f);
 175  	 	 $sql[] = $s;
 176  	 	 return $sql;
 177  	 }
 178  
 179  	 // return string must begin with space
 180  	function _CreateSuffix($fname,&$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
 181  	 {
 182  	 	 $suffix = '';
 183  	 	 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
 184  	 	 if ($fautoinc) $suffix .= ' IDENTITY(1,1)';
 185  	 	 if ($fnotnull) $suffix .= ' NOT NULL';
 186  	 	 else if ($suffix == '') $suffix .= ' NULL';
 187  	 	 if ($fconstraint) $suffix .= ' '.$fconstraint;
 188  	 	 return $suffix;
 189  	 }
 190  
 191  	 /*
 192  CREATE TABLE
 193      [ database_name.[ owner ] . | owner. ] table_name
 194      ( { < column_definition >
 195          | column_name AS computed_column_expression
 196          | < table_constraint > ::= [ CONSTRAINT constraint_name ] }
 197  
 198              | [ { PRIMARY KEY | UNIQUE } [ ,...n ]
 199      )
 200  
 201  [ ON { filegroup | DEFAULT } ]
 202  [ TEXTIMAGE_ON { filegroup | DEFAULT } ]
 203  
 204  < column_definition > ::= { column_name data_type }
 205      [ COLLATE < collation_name > ]
 206      [ [ DEFAULT constant_expression ]
 207          | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
 208      ]
 209      [ ROWGUIDCOL]
 210      [ < column_constraint > ] [ ...n ]
 211  
 212  < column_constraint > ::= [ CONSTRAINT constraint_name ]
 213      { [ NULL | NOT NULL ]
 214          | [ { PRIMARY KEY | UNIQUE }
 215              [ CLUSTERED | NONCLUSTERED ]
 216              [ WITH FILLFACTOR = fillfactor ]
 217              [ON {filegroup | DEFAULT} ] ]
 218          ]
 219          | [ [ FOREIGN KEY ]
 220              REFERENCES ref_table [ ( ref_column ) ]
 221              [ ON DELETE { CASCADE | NO ACTION } ]
 222              [ ON UPDATE { CASCADE | NO ACTION } ]
 223              [ NOT FOR REPLICATION ]
 224          ]
 225          | CHECK [ NOT FOR REPLICATION ]
 226          ( logical_expression )
 227      }
 228  
 229  < table_constraint > ::= [ CONSTRAINT constraint_name ]
 230      { [ { PRIMARY KEY | UNIQUE }
 231          [ CLUSTERED | NONCLUSTERED ]
 232          { ( column [ ASC | DESC ] [ ,...n ] ) }
 233          [ WITH FILLFACTOR = fillfactor ]
 234          [ ON { filegroup | DEFAULT } ]
 235      ]
 236      | FOREIGN KEY
 237          [ ( column [ ,...n ] ) ]
 238          REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
 239          [ ON DELETE { CASCADE | NO ACTION } ]
 240          [ ON UPDATE { CASCADE | NO ACTION } ]
 241          [ NOT FOR REPLICATION ]
 242      | CHECK [ NOT FOR REPLICATION ]
 243          ( search_conditions )
 244      }
 245  
 246  
 247  	 */
 248  
 249  	 /*
 250  	 CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
 251      ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
 252  	 	 [ WITH < index_option > [ ,...n] ]
 253  	 	 [ ON filegroup ]
 254  	 	 < index_option > :: =
 255  	 	     { PAD_INDEX |
 256  	 	         FILLFACTOR = fillfactor |
 257  	 	         IGNORE_DUP_KEY |
 258  	 	         DROP_EXISTING |
 259  	 	     STATISTICS_NORECOMPUTE |
 260  	 	     SORT_IN_TEMPDB
 261  	 	 }
 262  */
 263  	function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
 264  	 {
 265  	 	 $sql = array();
 266  
 267  	 	 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
 268  	 	 	 $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
 269  	 	 	 if ( isset($idxoptions['DROP']) )
 270  	 	 	 	 return $sql;
 271  	 	 }
 272  
 273  	 	 if ( empty ($flds) ) {
 274  	 	 	 return $sql;
 275  	 	 }
 276  
 277  	 	 $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
 278  	 	 $clustered = isset($idxoptions['CLUSTERED']) ? ' CLUSTERED' : '';
 279  
 280  	 	 if ( is_array($flds) )
 281  	 	 	 $flds = implode(', ',$flds);
 282  	 	 $s = 'CREATE' . $unique . $clustered . ' INDEX ' . $idxname . ' ON ' . $tabname . ' (' . $flds . ')';
 283  
 284  	 	 if ( isset($idxoptions[$this->upperName]) )
 285  	 	 	 $s .= $idxoptions[$this->upperName];
 286  
 287  
 288  	 	 $sql[] = $s;
 289  
 290  	 	 return $sql;
 291  	 }
 292  
 293  
 294  	function _GetSize($ftype, $ty, $fsize, $fprec, $options=false)
 295  	 {
 296  	 	 switch ($ftype) {
 297  	 	 case 'INT':
 298  	 	 case 'SMALLINT':
 299  	 	 case 'TINYINT':
 300  	 	 case 'BIGINT':
 301  	 	 	 return $ftype;
 302  	 	 }
 303      	 if ($ty == 'T') return $ftype;
 304      	 return parent::_GetSize($ftype, $ty, $fsize, $fprec, $options);
 305  
 306  	 }
 307  }