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

   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 ?>