lib/adodb/datadict/datadict-mssql.inc.php

   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 }