vendor/adodb/adodb-php/datadict/datadict-mssqlnative.inc.php

   1 <?php
   2
   3 /**
   4   @version   v5.20.9  21-Dec-2016
   5   @copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
   6   @copyright (c) 2014      Damien Regad, Mark Newnham and the ADOdb community
   7   Released under both BSD license and Lesser GPL library license.
   8   Whenever there is any discrepancy between the two licenses,
   9   the BSD license will take precedence.
  10
  11   Set tabs to 4 for best viewing.
  12
  13 */
  14
  15 /*
  16 In ADOdb, named quotes for MS SQL Server use ". From the MSSQL Docs:
  17
  18         Note Delimiters are for identifiers only. Delimiters cannot be used for keywords,
  19         whether or not they are marked as reserved in SQL Server.
  20
  21         Quoted identifiers are delimited by double quotation marks ("):
  22         SELECT * FROM "Blanks in Table Name"
  23
  24         Bracketed identifiers are delimited by brackets ([ ]):
  25         SELECT * FROM [Blanks In Table Name]
  26
  27         Quoted identifiers are valid only when the QUOTED_IDENTIFIER option is set to ON. By default,
  28         the Microsoft OLE DB Provider for SQL Server and SQL Server ODBC driver set QUOTED_IDENTIFIER ON
  29         when they connect.
  30
  31         In Transact-SQL, the option can be set at various levels using SET QUOTED_IDENTIFIER,
  32         the quoted identifier option of sp_dboption, or the user options option of sp_configure.
  33
  34         When SET ANSI_DEFAULTS is ON, SET QUOTED_IDENTIFIER is enabled.
  35
  36         Syntax
  37
  38                 SET QUOTED_IDENTIFIER { ON | OFF }
  39
  40
  41 */
  42
  43 // security - hide paths
  44 if (!defined('ADODB_DIR')) die();
  45
  46 class ADODB2_mssqlnative extends ADODB_DataDict {
  47         var $databaseType = 'mssqlnative';
  48         var $dropIndex = 'DROP INDEX %1$s ON %2$s';
  49         var $renameTable = "EXEC sp_rename '%s','%s'";
  50         var $renameColumn = "EXEC sp_rename '%s.%s','%s'";
  51         var $typeX = 'TEXT';  ## Alternatively, set it to VARCHAR(4000)
  52         var $typeXL = 'TEXT';
  53
  54         //var $alterCol = ' ALTER COLUMN ';
  55
  56         function MetaType($t,$len=-1,$fieldobj=false)
  57         {
  58                 if (is_object($t)) {
  59                         $fieldobj = $t;
  60                         $t = $fieldobj->type;
  61                         $len = $fieldobj->max_length;
  62                 }
  63
  64                 $_typeConversion = array(
  65                         -155 => 'D',
  66                           93 => 'D',
  67                         -154 => 'D',
  68                           -2 => 'D',
  69                           91 => 'D',
  70
  71                           12 => 'C',
  72                            1 => 'C',
  73                           -9 => 'C',
  74                           -8 => 'C',
  75
  76                           -7 => 'L',
  77                           -6 => 'I2',
  78                           -5 => 'I8',
  79                          -11 => 'I',
  80                            4 => 'I',
  81                            5 => 'I4',
  82
  83                           -1 => 'X',
  84                          -10 => 'X',
  85
  86                            2 => 'N',
  87                            3 => 'N',
  88                            6 => 'N',
  89                            7 => 'N',
  90
  91                         -152 => 'X',
  92                         -151 => 'X',
  93                           -4 => 'X',
  94                           -3 => 'X'
  95                         );
  96
  97                 return $_typeConversion($t);
  98
  99         }
 100
 101         function ActualType($meta)
 102         {
 103                 $DATE_TYPE = 'DATETIME';
 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 $DATE_TYPE;
 116                 case 'T': return 'TIME';
 117                 case 'L': return 'BIT';
 118
 119                 case 'R':
 120                 case 'I': return 'INT';
 121                 case 'I1': return 'TINYINT';
 122                 case 'I2': return 'SMALLINT';
 123                 case 'I4': return 'INT';
 124                 case 'I8': return 'BIGINT';
 125
 126                 case 'F': return 'REAL';
 127                 case 'N': return 'NUMERIC';
 128                 default:
 129                         print "RETURN $meta";
 130                         return $meta;
 131                 }
 132         }
 133
 134
 135         function AddColumnSQL($tabname, $flds)
 136         {
 137                 $tabname = $this->TableName ($tabname);
 138                 $f = array();
 139                 list($lines,$pkey) = $this->_GenFields($flds);
 140                 $s = "ALTER TABLE $tabname $this->addCol";
 141                 foreach($lines as $v) {
 142                         $f[] = "\n $v";
 143                 }
 144                 $s .= implode(', ',$f);
 145                 $sql[] = $s;
 146                 return $sql;
 147         }
 148
 149         function DefaultConstraintname($tabname, $colname)
 150         {
 151                 $constraintname = false;
 152                 $rs = $this->connection->Execute(
 153                         "SELECT name FROM sys.default_constraints
 154                         WHERE object_name(parent_object_id) = '$tabname'
 155                         AND col_name(parent_object_id, parent_column_id) = '$colname'"
 156                 );
 157                 if ( is_object($rs) ) {
 158                         $row = $rs->FetchRow();
 159                         $constraintname = $row['name'];
 160                 }
 161                 return $constraintname;
 162         }
 163
 164         function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 165         {
 166                 $tabname = $this->TableName ($tabname);
 167                 $sql = array();
 168
 169                 list($lines,$pkey,$idxs) = $this->_GenFields($flds);
 170                 $alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
 171                 foreach($lines as $v) {
 172                         $not_null = false;
 173                         if ($not_null = preg_match('/NOT NULL/i',$v)) {
 174                                 $v = preg_replace('/NOT NULL/i','',$v);
 175                         }
 176                         if (preg_match('/^([^ ]+) .*DEFAULT (\'[^\']+\'|\"[^\"]+\"|[^ ]+)/',$v,$matches)) {
 177                                 list(,$colname,$default) = $matches;
 178                                 $v = preg_replace('/^' . preg_quote($colname) . '\s/', '', $v);
 179                                 $t = trim(str_replace('DEFAULT '.$default,'',$v));
 180                                 if ( $constraintname = $this->DefaultConstraintname($tabname,$colname) ) {
 181                                         $sql[] = 'ALTER TABLE '.$tabname.' DROP CONSTRAINT '. $constraintname;
 182                                 }
 183                                 if ($not_null) {
 184                                         $sql[] = $alter . $colname . ' ' . $t  . ' NOT NULL';
 185                                 } else {
 186                                         $sql[] = $alter . $colname . ' ' . $t ;
 187                                 }
 188                                 $sql[] = 'ALTER TABLE ' . $tabname
 189                                         . ' ADD CONSTRAINT DF__' . $tabname . '__' .  $colname .  '__' . dechex(rand())
 190                                         . ' DEFAULT ' . $default . ' FOR ' . $colname;
 191                         } else {
 192                                 $colname = strtok($v," ");
 193                                 if ( $constraintname = $this->DefaultConstraintname($tabname,$colname) ) {
 194                                         $sql[] = 'ALTER TABLE '.$tabname.' DROP CONSTRAINT '. $constraintname;
 195                                 }
 196                                 if ($not_null) {
 197                                         $sql[] = $alter . $v  . ' NOT NULL';
 198                                 } else {
 199                                         $sql[] = $alter . $v;
 200                                 }
 201                         }
 202                 }
 203                 if (is_array($idxs)) {
 204                         foreach($idxs as $idx => $idxdef) {
 205                                 $sql_idxs = $this->CreateIndexSql($idx, $tabname, $idxdef['cols'], $idxdef['opts']);
 206                                 $sql = array_merge($sql, $sql_idxs);
 207                         }
 208                 }
 209                 return $sql;
 210         }
 211
 212
 213         /**
 214          * Drop a column, syntax is ALTER TABLE table DROP COLUMN column,column
 215          *
 216          * @param string   $tabname      Table Name
 217          * @param string[] $flds         One, or an array of Fields To Drop
 218          * @param string   $tableflds    Throwaway value to make the function match the parent
 219          * @param string   $tableoptions Throway value to make the function match the parent
 220          *
 221          * @return string  The SQL necessary to drop the column
 222          */
 223         function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 224         {
 225                 $tabname = $this->TableName ($tabname);
 226                 if (!is_array($flds))
 227                         $flds = explode(',',$flds);
 228                 $f = array();
 229                 $s = 'ALTER TABLE ' . $tabname;
 230                 foreach($flds as $v) {
 231                         if ( $constraintname = $this->DefaultConstraintname($tabname,$v) ) {
 232                                 $sql[] = 'ALTER TABLE ' . $tabname . ' DROP CONSTRAINT ' . $constraintname;
 233                         }
 234                         $f[] = ' DROP COLUMN ' . $this->NameQuote($v);
 235                 }
 236                 $s .= implode(', ',$f);
 237                 $sql[] = $s;
 238                 return $sql;
 239         }
 240
 241         // return string must begin with space
 242         function _CreateSuffix($fname,&$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
 243         {
 244                 $suffix = '';
 245                 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
 246                 if ($fautoinc) $suffix .= ' IDENTITY(1,1)';
 247                 if ($fnotnull) $suffix .= ' NOT NULL';
 248                 else if ($suffix == '') $suffix .= ' NULL';
 249                 if ($fconstraint) $suffix .= ' '.$fconstraint;
 250                 return $suffix;
 251         }
 252
 253         /*
 254 CREATE TABLE
 255     [ database_name.[ owner ] . | owner. ] table_name
 256     ( { < column_definition >
 257         | column_name AS computed_column_expression
 258         | < table_constraint > ::= [ CONSTRAINT constraint_name ] }
 259
 260             | [ { PRIMARY KEY | UNIQUE } [ ,...n ]
 261     )
 262
 263 [ ON { filegroup | DEFAULT } ]
 264 [ TEXTIMAGE_ON { filegroup | DEFAULT } ]
 265
 266 < column_definition > ::= { column_name data_type }
 267     [ COLLATE < collation_name > ]
 268     [ [ DEFAULT constant_expression ]
 269         | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
 270     ]
 271     [ ROWGUIDCOL]
 272     [ < column_constraint > ] [ ...n ]
 273
 274 < column_constraint > ::= [ CONSTRAINT constraint_name ]
 275     { [ NULL | NOT NULL ]
 276         | [ { PRIMARY KEY | UNIQUE }
 277             [ CLUSTERED | NONCLUSTERED ]
 278             [ WITH FILLFACTOR = fillfactor ]
 279             [ON {filegroup | DEFAULT} ] ]
 280         ]
 281         | [ [ FOREIGN KEY ]
 282             REFERENCES ref_table [ ( ref_column ) ]
 283             [ ON DELETE { CASCADE | NO ACTION } ]
 284             [ ON UPDATE { CASCADE | NO ACTION } ]
 285             [ NOT FOR REPLICATION ]
 286         ]
 287         | CHECK [ NOT FOR REPLICATION ]
 288         ( logical_expression )
 289     }
 290
 291 < table_constraint > ::= [ CONSTRAINT constraint_name ]
 292     { [ { PRIMARY KEY | UNIQUE }
 293         [ CLUSTERED | NONCLUSTERED ]
 294         { ( column [ ASC | DESC ] [ ,...n ] ) }
 295         [ WITH FILLFACTOR = fillfactor ]
 296         [ ON { filegroup | DEFAULT } ]
 297     ]
 298     | FOREIGN KEY
 299         [ ( column [ ,...n ] ) ]
 300         REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
 301         [ ON DELETE { CASCADE | NO ACTION } ]
 302         [ ON UPDATE { CASCADE | NO ACTION } ]
 303         [ NOT FOR REPLICATION ]
 304     | CHECK [ NOT FOR REPLICATION ]
 305         ( search_conditions )
 306     }
 307
 308
 309         */
 310
 311         /*
 312         CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
 313     ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
 314                 [ WITH < index_option > [ ,...n] ]
 315                 [ ON filegroup ]
 316                 < index_option > :: =
 317                     { PAD_INDEX |
 318                         FILLFACTOR = fillfactor |
 319                         IGNORE_DUP_KEY |
 320                         DROP_EXISTING |
 321                     STATISTICS_NORECOMPUTE |
 322                     SORT_IN_TEMPDB
 323                 }
 324 */
 325         function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
 326         {
 327                 $sql = array();
 328
 329                 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
 330                         $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
 331                         if ( isset($idxoptions['DROP']) )
 332                                 return $sql;
 333                 }
 334
 335                 if ( empty ($flds) ) {
 336                         return $sql;
 337                 }
 338
 339                 $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
 340                 $clustered = isset($idxoptions['CLUSTERED']) ? ' CLUSTERED' : '';
 341
 342                 if ( is_array($flds) )
 343                         $flds = implode(', ',$flds);
 344                 $s = 'CREATE' . $unique . $clustered . ' INDEX ' . $idxname . ' ON ' . $tabname . ' (' . $flds . ')';
 345
 346                 if ( isset($idxoptions[$this->upperName]) )
 347                         $s .= $idxoptions[$this->upperName];
 348
 349
 350                 $sql[] = $s;
 351
 352                 return $sql;
 353         }
 354
 355
 356         function _GetSize($ftype, $ty, $fsize, $fprec)
 357         {
 358                 switch ($ftype) {
 359                 case 'INT':
 360                 case 'SMALLINT':
 361                 case 'TINYINT':
 362                 case 'BIGINT':
 363                         return $ftype;
 364                 }
 365         if ($ty == 'T') return $ftype;
 366         return parent::_GetSize($ftype, $ty, $fsize, $fprec);
 367
 368         }
 369 }