See Release Notes
Long Term Support Release
Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401] [Versions 400 and 401]
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 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body