TYPO3 CMS  TYPO3_6-2
•All Classes Namespaces Files Functions Variables Pages
datadict-mssql.inc.php
Go to the documentation of this file.
1 <?php
2 
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  case 'SMALLDATETIME': return 'T';
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 
93  case 'TS':
94  case 'T': return 'DATETIME';
95  case 'L': return 'BIT';
96 
97  case 'R':
98  case 'I': return 'INT';
99  case 'I1': return 'TINYINT';
100  case 'I2': return 'SMALLINT';
101  case 'I4': return 'INT';
102  case 'I8': return 'BIGINT';
103 
104  case 'F': return 'REAL';
105  case 'N': return 'NUMERIC';
106  default:
107  return $meta;
108  }
109  }
110 
111 
112  function AddColumnSQL($tabname, $flds)
113  {
114  $tabname = $this->TableName ($tabname);
115  $f = array();
116  list($lines,$pkey) = $this->_GenFields($flds);
117  $s = "ALTER TABLE $tabname $this->addCol";
118  foreach($lines as $v) {
119  $f[] = "\n $v";
120  }
121  $s .= implode(', ',$f);
122  $sql[] = $s;
123  return $sql;
124  }
125 
126  /*
127  function AlterColumnSQL($tabname, $flds)
128  {
129  $tabname = $this->TableName ($tabname);
130  $sql = array();
131  list($lines,$pkey) = $this->_GenFields($flds);
132  foreach($lines as $v) {
133  $sql[] = "ALTER TABLE $tabname $this->alterCol $v";
134  }
135 
136  return $sql;
137  }
138  */
139 
140  function DropColumnSQL($tabname, $flds, $tableflds='', $tableoptions='')
141  {
142  $tabname = $this->TableName ($tabname);
143  if (!is_array($flds))
144  $flds = explode(',',$flds);
145  $f = array();
146  $s = 'ALTER TABLE ' . $tabname;
147  foreach($flds as $v) {
148  $f[] = "\n$this->dropCol ".$this->NameQuote($v);
149  }
150  $s .= implode(', ',$f);
151  $sql[] = $s;
152  return $sql;
153  }
154 
155  // return string must begin with space
156  function _CreateSuffix($fname,&$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
157  {
158  $suffix = '';
159  if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
160  if ($fautoinc) $suffix .= ' IDENTITY(1,1)';
161  if ($fnotnull) $suffix .= ' NOT NULL';
162  else if ($suffix == '') $suffix .= ' NULL';
163  if ($fconstraint) $suffix .= ' '.$fconstraint;
164  return $suffix;
165  }
166 
167  /*
168 CREATE TABLE
169  [ database_name.[ owner ] . | owner. ] table_name
170  ( { < column_definition >
171  | column_name AS computed_column_expression
172  | < table_constraint > ::= [ CONSTRAINT constraint_name ] }
173 
174  | [ { PRIMARY KEY | UNIQUE } [ ,...n ]
175  )
176 
177 [ ON { filegroup | DEFAULT } ]
178 [ TEXTIMAGE_ON { filegroup | DEFAULT } ]
179 
180 < column_definition > ::= { column_name data_type }
181  [ COLLATE < collation_name > ]
182  [ [ DEFAULT constant_expression ]
183  | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
184  ]
185  [ ROWGUIDCOL]
186  [ < column_constraint > ] [ ...n ]
187 
188 < column_constraint > ::= [ CONSTRAINT constraint_name ]
189  { [ NULL | NOT NULL ]
190  | [ { PRIMARY KEY | UNIQUE }
191  [ CLUSTERED | NONCLUSTERED ]
192  [ WITH FILLFACTOR = fillfactor ]
193  [ON {filegroup | DEFAULT} ] ]
194  ]
195  | [ [ FOREIGN KEY ]
196  REFERENCES ref_table [ ( ref_column ) ]
197  [ ON DELETE { CASCADE | NO ACTION } ]
198  [ ON UPDATE { CASCADE | NO ACTION } ]
199  [ NOT FOR REPLICATION ]
200  ]
201  | CHECK [ NOT FOR REPLICATION ]
202  ( logical_expression )
203  }
204 
205 < table_constraint > ::= [ CONSTRAINT constraint_name ]
206  { [ { PRIMARY KEY | UNIQUE }
207  [ CLUSTERED | NONCLUSTERED ]
208  { ( column [ ASC | DESC ] [ ,...n ] ) }
209  [ WITH FILLFACTOR = fillfactor ]
210  [ ON { filegroup | DEFAULT } ]
211  ]
212  | FOREIGN KEY
213  [ ( column [ ,...n ] ) ]
214  REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
215  [ ON DELETE { CASCADE | NO ACTION } ]
216  [ ON UPDATE { CASCADE | NO ACTION } ]
217  [ NOT FOR REPLICATION ]
218  | CHECK [ NOT FOR REPLICATION ]
219  ( search_conditions )
220  }
221 
222 
223  */
224 
225  /*
226  CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
227  ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
228  [ WITH < index_option > [ ,...n] ]
229  [ ON filegroup ]
230  < index_option > :: =
231  { PAD_INDEX |
232  FILLFACTOR = fillfactor |
233  IGNORE_DUP_KEY |
234  DROP_EXISTING |
235  STATISTICS_NORECOMPUTE |
236  SORT_IN_TEMPDB
237  }
238 */
239  function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
240  {
241  $sql = array();
242 
243  if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
244  $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
245  if ( isset($idxoptions['DROP']) )
246  return $sql;
247  }
248 
249  if ( empty ($flds) ) {
250  return $sql;
251  }
252 
253  $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
254  $clustered = isset($idxoptions['CLUSTERED']) ? ' CLUSTERED' : '';
255 
256  if ( is_array($flds) )
257  $flds = implode(', ',$flds);
258  $s = 'CREATE' . $unique . $clustered . ' INDEX ' . $idxname . ' ON ' . $tabname . ' (' . $flds . ')';
259 
260  if ( isset($idxoptions[$this->upperName]) )
261  $s .= $idxoptions[$this->upperName];
262 
263 
264  $sql[] = $s;
265 
266  return $sql;
267  }
268 
269 
270  function _GetSize($ftype, $ty, $fsize, $fprec)
271  {
272  switch ($ftype) {
273  case 'INT':
274  case 'SMALLINT':
275  case 'TINYINT':
276  case 'BIGINT':
277  return $ftype;
278  }
279  if ($ty == 'T') return $ftype;
280  return parent::_GetSize($ftype, $ty, $fsize, $fprec);
281 
282  }
283 }
ADODB2_mssql
Definition: datadict-mssql.inc.php:44
ADODB_DataDict\_GenFields
_GenFields($flds, $widespacing=false)
Definition: adodb-datadict.inc.php:595
ADODB2_mssql\_CreateSuffix
_CreateSuffix($fname, &$ftype, $fnotnull, $fdefault, $fautoinc, $fconstraint, $funsigned)
Definition: datadict-mssql.inc.php:156
ADODB2_mssql\_GetSize
_GetSize($ftype, $ty, $fsize, $fprec)
Definition: datadict-mssql.inc.php:270
$sql
$sql
Definition: server.php:82
ADODB2_mssql\_IndexSQL
_IndexSQL($idxname, $tabname, $flds, $idxoptions)
Definition: datadict-mssql.inc.php:239
ADODB2_mssql\AddColumnSQL
AddColumnSQL($tabname, $flds)
Definition: datadict-mssql.inc.php:112
ADODB2_mssql\ActualType
ActualType($meta)
Definition: datadict-mssql.inc.php:79
ADODB2_mssql\DropColumnSQL
DropColumnSQL($tabname, $flds, $tableflds='', $tableoptions='')
Definition: datadict-mssql.inc.php:140
ADODB_DataDict\TableName
TableName($name)
Definition: adodb-datadict.inc.php:361
die
die
Definition: index.php:6
ADODB2_mssql\$databaseType
$databaseType
Definition: datadict-mssql.inc.php:45
ADODB2_mssql\$dropIndex
$dropIndex
Definition: datadict-mssql.inc.php:46
ADODB2_mssql\$renameTable
$renameTable
Definition: datadict-mssql.inc.php:47
ADODB_DataDict
Definition: adodb-datadict.inc.php:164
ADODB2_mssql\$renameColumn
$renameColumn
Definition: datadict-mssql.inc.php:48
ADODB2_mssql\$typeX
$typeX
Definition: datadict-mssql.inc.php:50
ADODB2_mssql\$typeXL
$typeXL
Definition: datadict-mssql.inc.php:51
ADODB2_mssql\MetaType
MetaType($t, $len=-1, $fieldobj=false)
Definition: datadict-mssql.inc.php:55