TYPO3 CMS  TYPO3_6-2
•All Classes Namespaces Files Functions Variables Pages
datadict-mssqlnative.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_mssqlnative extends ADODB_DataDict {
45  var $databaseType = 'mssqlnative';
46  var $dropIndex = 'DROP INDEX %1$s ON %2$s';
47  var $renameTable = "EXEC sp_rename '%s','%s'";
48  var $renameColumn = "EXEC sp_rename '%s.%s','%s'";
49  var $typeX = 'TEXT'; ## Alternatively, set it to VARCHAR(4000)
50  var $typeXL = 'TEXT';
51 
52  //var $alterCol = ' ALTER COLUMN ';
53 
54  function MetaType($t,$len=-1,$fieldobj=false)
55  {
56  if (is_object($t)) {
57  $fieldobj = $t;
58  $t = $fieldobj->type;
59  $len = $fieldobj->max_length;
60  }
61 
62  $_typeConversion = array(
63  -155 => 'D',
64  93 => 'D',
65  -154 => 'D',
66  -2 => 'D',
67  91 => 'D',
68 
69  12 => 'C',
70  1 => 'C',
71  -9 => 'C',
72  -8 => 'C',
73 
74  -7 => 'L',
75  -6 => 'I2',
76  -5 => 'I8',
77  -11 => 'I',
78  4 => 'I',
79  5 => 'I4',
80 
81  -1 => 'X',
82  -10 => 'X',
83 
84  2 => 'N',
85  3 => 'N',
86  6 => 'N',
87  7 => 'N',
88 
89  -152 => 'X',
90  -151 => 'X',
91  -4 => 'X',
92  -3 => 'X'
93  );
94 
95  return $_typeConversion($t);
96 
97  }
98 
99  function ActualType($meta)
100  {
101  $DATE_TYPE = 'DATETIME';
102 
103  switch(strtoupper($meta)) {
104 
105  case 'C': return 'VARCHAR';
106  case 'XL': return (isset($this)) ? $this->typeXL : 'TEXT';
107  case 'X': return (isset($this)) ? $this->typeX : 'TEXT'; ## could be varchar(8000), but we want compat with oracle
108  case 'C2': return 'NVARCHAR';
109  case 'X2': return 'NTEXT';
110 
111  case 'B': return 'IMAGE';
112 
113  case 'D': return $DATE_TYPE;
114  case 'T': return 'TIME';
115  case 'L': return 'BIT';
116 
117  case 'R':
118  case 'I': return 'INT';
119  case 'I1': return 'TINYINT';
120  case 'I2': return 'SMALLINT';
121  case 'I4': return 'INT';
122  case 'I8': return 'BIGINT';
123 
124  case 'F': return 'REAL';
125  case 'N': return 'NUMERIC';
126  default:
127  print "RETURN $meta";
128  return $meta;
129  }
130  }
131 
132 
133  function AddColumnSQL($tabname, $flds)
134  {
135  $tabname = $this->TableName ($tabname);
136  $f = array();
137  list($lines,$pkey) = $this->_GenFields($flds);
138  $s = "ALTER TABLE $tabname $this->addCol";
139  foreach($lines as $v) {
140  $f[] = "\n $v";
141  }
142  $s .= implode(', ',$f);
143  $sql[] = $s;
144  return $sql;
145  }
146 
147  /*
148  function AlterColumnSQL($tabname, $flds)
149  {
150  $tabname = $this->TableName ($tabname);
151  $sql = array();
152  list($lines,$pkey) = $this->_GenFields($flds);
153  foreach($lines as $v) {
154  $sql[] = "ALTER TABLE $tabname $this->alterCol $v";
155  }
156 
157  return $sql;
158  }
159  */
160 
171  function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
172  {
173  $tabname = $this->TableName ($tabname);
174  if (!is_array($flds))
175  $flds = explode(',',$flds);
176  $f = array();
177  $s = 'ALTER TABLE ' . $tabname . ' DROP COLUMN ';
178  foreach($flds as $v) {
179  //$f[] = "\n$this->dropCol ".$this->NameQuote($v);
180  $f[] = $this->NameQuote($v);
181  }
182  $s .= implode(', ',$f);
183  $sql[] = $s;
184  return $sql;
185  }
186 
187  // return string must begin with space
188  function _CreateSuffix($fname,&$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
189  {
190  $suffix = '';
191  if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
192  if ($fautoinc) $suffix .= ' IDENTITY(1,1)';
193  if ($fnotnull) $suffix .= ' NOT NULL';
194  else if ($suffix == '') $suffix .= ' NULL';
195  if ($fconstraint) $suffix .= ' '.$fconstraint;
196  return $suffix;
197  }
198 
199  /*
200 CREATE TABLE
201  [ database_name.[ owner ] . | owner. ] table_name
202  ( { < column_definition >
203  | column_name AS computed_column_expression
204  | < table_constraint > ::= [ CONSTRAINT constraint_name ] }
205 
206  | [ { PRIMARY KEY | UNIQUE } [ ,...n ]
207  )
208 
209 [ ON { filegroup | DEFAULT } ]
210 [ TEXTIMAGE_ON { filegroup | DEFAULT } ]
211 
212 < column_definition > ::= { column_name data_type }
213  [ COLLATE < collation_name > ]
214  [ [ DEFAULT constant_expression ]
215  | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
216  ]
217  [ ROWGUIDCOL]
218  [ < column_constraint > ] [ ...n ]
219 
220 < column_constraint > ::= [ CONSTRAINT constraint_name ]
221  { [ NULL | NOT NULL ]
222  | [ { PRIMARY KEY | UNIQUE }
223  [ CLUSTERED | NONCLUSTERED ]
224  [ WITH FILLFACTOR = fillfactor ]
225  [ON {filegroup | DEFAULT} ] ]
226  ]
227  | [ [ FOREIGN KEY ]
228  REFERENCES ref_table [ ( ref_column ) ]
229  [ ON DELETE { CASCADE | NO ACTION } ]
230  [ ON UPDATE { CASCADE | NO ACTION } ]
231  [ NOT FOR REPLICATION ]
232  ]
233  | CHECK [ NOT FOR REPLICATION ]
234  ( logical_expression )
235  }
236 
237 < table_constraint > ::= [ CONSTRAINT constraint_name ]
238  { [ { PRIMARY KEY | UNIQUE }
239  [ CLUSTERED | NONCLUSTERED ]
240  { ( column [ ASC | DESC ] [ ,...n ] ) }
241  [ WITH FILLFACTOR = fillfactor ]
242  [ ON { filegroup | DEFAULT } ]
243  ]
244  | FOREIGN KEY
245  [ ( column [ ,...n ] ) ]
246  REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
247  [ ON DELETE { CASCADE | NO ACTION } ]
248  [ ON UPDATE { CASCADE | NO ACTION } ]
249  [ NOT FOR REPLICATION ]
250  | CHECK [ NOT FOR REPLICATION ]
251  ( search_conditions )
252  }
253 
254 
255  */
256 
257  /*
258  CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
259  ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
260  [ WITH < index_option > [ ,...n] ]
261  [ ON filegroup ]
262  < index_option > :: =
263  { PAD_INDEX |
264  FILLFACTOR = fillfactor |
265  IGNORE_DUP_KEY |
266  DROP_EXISTING |
267  STATISTICS_NORECOMPUTE |
268  SORT_IN_TEMPDB
269  }
270 */
271  function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
272  {
273  $sql = array();
274 
275  if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
276  $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
277  if ( isset($idxoptions['DROP']) )
278  return $sql;
279  }
280 
281  if ( empty ($flds) ) {
282  return $sql;
283  }
284 
285  $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
286  $clustered = isset($idxoptions['CLUSTERED']) ? ' CLUSTERED' : '';
287 
288  if ( is_array($flds) )
289  $flds = implode(', ',$flds);
290  $s = 'CREATE' . $unique . $clustered . ' INDEX ' . $idxname . ' ON ' . $tabname . ' (' . $flds . ')';
291 
292  if ( isset($idxoptions[$this->upperName]) )
293  $s .= $idxoptions[$this->upperName];
294 
295 
296  $sql[] = $s;
297 
298  return $sql;
299  }
300 
301 
302  function _GetSize($ftype, $ty, $fsize, $fprec)
303  {
304  switch ($ftype) {
305  case 'INT':
306  case 'SMALLINT':
307  case 'TINYINT':
308  case 'BIGINT':
309  return $ftype;
310  }
311  if ($ty == 'T') return $ftype;
312  return parent::_GetSize($ftype, $ty, $fsize, $fprec);
313 
314  }
315 }
ADODB2_mssqlnative\MetaType
MetaType($t, $len=-1, $fieldobj=false)
Definition: datadict-mssqlnative.inc.php:54
ADODB_DataDict\_GenFields
_GenFields($flds, $widespacing=false)
Definition: adodb-datadict.inc.php:595
$sql
$sql
Definition: server.php:82
ADODB2_mssqlnative\$dropIndex
$dropIndex
Definition: datadict-mssqlnative.inc.php:46
ADODB2_mssqlnative\_GetSize
_GetSize($ftype, $ty, $fsize, $fprec)
Definition: datadict-mssqlnative.inc.php:302
ADODB2_mssqlnative\$typeXL
$typeXL
Definition: datadict-mssqlnative.inc.php:50
ADODB2_mssqlnative\AddColumnSQL
AddColumnSQL($tabname, $flds)
Definition: datadict-mssqlnative.inc.php:133
ADODB_DataDict\TableName
TableName($name)
Definition: adodb-datadict.inc.php:361
die
die
Definition: index.php:6
ADODB2_mssqlnative\_IndexSQL
_IndexSQL($idxname, $tabname, $flds, $idxoptions)
Definition: datadict-mssqlnative.inc.php:271
ADODB2_mssqlnative\$databaseType
$databaseType
Definition: datadict-mssqlnative.inc.php:45
ADODB_DataDict
Definition: adodb-datadict.inc.php:164
ADODB2_mssqlnative\DropColumnSQL
DropColumnSQL($tabname, $flds, $tableflds='', $tableoptions='')
Definition: datadict-mssqlnative.inc.php:171
ADODB2_mssqlnative\$renameColumn
$renameColumn
Definition: datadict-mssqlnative.inc.php:48
ADODB2_mssqlnative\ActualType
ActualType($meta)
Definition: datadict-mssqlnative.inc.php:99
ADODB2_mssqlnative
Definition: datadict-mssqlnative.inc.php:44
ADODB2_mssqlnative\$renameTable
$renameTable
Definition: datadict-mssqlnative.inc.php:47
ADODB2_mssqlnative\_CreateSuffix
_CreateSuffix($fname, &$ftype, $fnotnull, $fdefault, $fautoinc, $fconstraint, $funsigned)
Definition: datadict-mssqlnative.inc.php:188
ADODB2_mssqlnative\$typeX
$typeX
Definition: datadict-mssqlnative.inc.php:49
ADODB_DataDict\NameQuote
NameQuote($name=NULL, $allowBrackets=false)
Definition: adodb-datadict.inc.php:332