Context Navigation

source: trunk/phpgwapi/inc/class.schema_proc_mssql.inc.php @ 2

Revision 2, 9.4 KB checked in by niltonneto, 17 years ago (diff)
Removida todas as tags usadas pelo CVS ($Id, $Source). Primeira versão no CVS externo.
Property svn:eol-style set to `native` Property svn:executable set to ``*

Line
1	<?php
2	/**************************************************************************\
3	* eGroupWare - Setup *
4	* http://www.egroupware.org *
5	* -------------------------------------------- *
6	* This file written by Michael Dean<mdean@users.sourceforge.net> *
7	* and Miles Lott<milosch@groupwhere.org> *
8	* -------------------------------------------- *
9	* This program is free software; you can redistribute it and/or modify it *
10	* under the terms of the GNU General Public License as published by the *
11	* Free Software Foundation; either version 2 of the License, or (at your *
12	* option) any later version. *
13	\**************************************************************************/
14
15
16	class schema_proc_mssql
17	{
18	var $m_sStatementTerminator;
19	/* Following added to convert sql to array */
20	var $sCol = array();
21	var $pk = array();
22	var $fk = array();
23	var $ix = array();
24	var $uc = array();
25	var $b_needExplicitNULL = true; // no definition means NOT NULL for mssql
26
27	function schema_proc_mssql()
28	{
29	$this->m_sStatementTerminator = ';';
30	}
31
32	/* Return a type suitable for DDL */
33	function TranslateType($sType, $iPrecision = 0, $iScale = 0)
34	{
35	$sTranslated = '';
36	switch($sType)
37	{
38	case 'auto':
39	$sTranslated = 'int identity(1,1) NOT NULL';
40	break;
41	case 'blob':
42	$sTranslated = 'image'; /* wonder how well PHP will support this??? */
43	break;
44	case 'char':
45	if ($iPrecision > 0 && $iPrecision < 256)
46	{
47	$sTranslated = sprintf("char(%d)", $iPrecision);
48	}
49	if ($iPrecision > 255)
50	{
51	$sTranslated = 'text';
52	}
53	break;
54	case 'date':
55	$sTranslated = 'smalldatetime';
56	break;
57	case 'decimal':
58	$sTranslated = sprintf("decimal(%d,%d)", $iPrecision, $iScale);
59	break;
60	case 'float':
61	switch ($iPrecision)
62	{
63	case 4:
64	$sTranslated = 'float';
65	break;
66	case 8:
67	$sTranslated = 'real';
68	break;
69	}
70	break;
71	case 'int':
72	switch ($iPrecision)
73	{
74	case 2:
75	$sTranslated = 'smallint';
76	break;
77	case 4:
78	case 8:
79	$sTranslated = 'int';
80	break;
81	}
82	break;
83	case 'longtext':
84	case 'text':
85	$sTranslated = 'text';
86	break;
87	case 'timestamp':
88	$sTranslated = 'datetime';
89	break;
90	case 'bool':
91	$sTranslated = 'bit';
92	break;
93	case 'varchar':
94	if ($iPrecision > 0 && $iPrecision <= 256)
95	{
96	$sTranslated = sprintf("varchar(%d)", $iPrecision);
97	}
98	if ($iPrecision > 256)
99	{
100	$sTranslated = 'text';
101	}
102	break;
103	}
104	return $sTranslated;
105	}
106
107	function TranslateDefault($sDefault)
108	{
109	switch ($sDefault)
110	{
111	case 'current_date':
112	case 'current_timestamp':
113	return 'GetDate()';
114	}
115
116	return "'$sDefault'";
117	}
118
119	// Inverse of above, convert sql column types to array info
120	function rTranslateType($sType, $iPrecision = 0, $iScale = 0)
121	{
122	$sTranslated = '';
123	if ($sType == 'int' \|\| $sType == 'tinyint' \|\| $sType == 'smallint')
124	{
125	if ($iPrecision > 8)
126	{
127	$iPrecision = 8;
128	}
129	elseif($iPrecision > 4)
130	{
131	$iPrecision = 4;
132	}
133	else
134	{
135	$iPrecision = 2;
136	}
137	}
138	switch($sType)
139	{
140	case 'tinyint':
141	case 'smallint':
142	$sTranslated = "'type' => 'int', 'precision' => 2";
143	break;
144	case 'int':
145	$sTranslated = "'type' => 'int', 'precision' => 4";
146	break;
147	case 'char':
148	if ($iPrecision > 0 && $iPrecision < 256)
149	{
150	$sTranslated = "'type' => 'char', 'precision' => $iPrecision";
151	}
152	if ($iPrecision > 255)
153	{
154	$sTranslated = "'type' => 'text'";
155	}
156	break;
157	case 'decimal':
158	$sTranslated = "'type' => 'decimal', 'precision' => $iPrecision, 'scale' => $iScale";
159	break;
160	case 'float':
161	case 'double':
162	$sTranslated = "'type' => 'float', 'precision' => $iPrecision";
163	break;
164	case 'smalldatetime':
165	$sTranslated = "'type' => 'date'";
166	break;
167	case 'datetime':
168	$sTranslated = "'type' => 'timestamp'";
169	break;
170	case 'varchar':
171	if ($iPrecision > 0 && $iPrecision < 256)
172	{
173	$sTranslated = "'type' => 'varchar', 'precision' => $iPrecision";
174	}
175	if ($iPrecision > 255)
176	{
177	$sTranslated = "'type' => 'text'";
178	}
179	break;
180	case 'image':
181	$sTranslated = "'type' => 'blob'";
182	break;
183	case 'text':
184	$sTranslated = "'type' => '$sType'";
185	break;
186	case 'bit':
187	$sTranslated = "'type' => 'bool'";
188	break;
189	}
190	return $sTranslated;
191	}
192
193	function GetPKSQL($sFields)
194	{
195	return "PRIMARY KEY NONCLUSTERED ($sFields)";
196	}
197
198	function GetUCSQL($sFields)
199	{
200	return "UNIQUE($sFields)";
201	}
202
203	function GetIXSQL($sFields,&$append,$options,$sTableName)
204	{
205	$append = True;
206	$ixFields = str_replace(',','_',$sFields);
207	$index = $sTableName . '_' . $ixFields . '_idx';
208	return "CREATE INDEX $index ON $sTableName ($sFields);\n";
209	}
210
211	function _GetColumns($oProc, $sTableName, &$sColumns, $sDropColumn = '')
212	{
213	$sColumns = '';
214	$this->pk = array();
215	$this->fk = array();
216	$this->ix = array();
217	$this->uc = array();
218
219	// Field, Type, Null, Key, Default, Extra
220	$oProc->m_odb->query("exec sp_columns '$sTableName'");
221	while ($oProc->m_odb->next_record())
222	{
223	$type = $default = $null = $nullcomma = $prec = $scale = $ret = $colinfo = $scales = '';
224	if ($sColumns != '')
225	{
226	$sColumns .= ',';
227	}
228	$sColumns .= $oProc->m_odb->f(0);
229
230	// The rest of this is used only for SQL->array
231	$colinfo = explode('(',$oProc->m_odb->f(1));
232	$prec = str_replace(')','',$colinfo[1]);
233	$scales = explode(',',$prec);
234	if ($scales[1])
235	{
236	$prec = $scales[0];
237	$scale = $scales[1];
238	}
239	$type = $this->rTranslateType($colinfo[0], $prec, $scale);
240
241	if ($oProc->m_odb->f(2) == 'YES')
242	{
243	$null = "'nullable' => True";
244	}
245	else
246	{
247	$null = "'nullable' => False";
248	}
249	if ($oProc->m_odb->f(4))
250	{
251	$default = "'default' => '".$oProc->m_odb->f(4)."'";
252	$nullcomma = ',';
253	}
254	else
255	{
256	$default = '';
257	$nullcomma = '';
258	}
259	if ($oProc->m_odb->f(5))
260	{
261	$type = "'type' => 'auto'";
262	}
263	$this->sCol[] = "\t\t\t\t'" . $oProc->m_odb->f(0)."' => array(" . $type . ',' . $null . $nullcomma . $default . '),' . "\n";
264	if ($oProc->m_odb->f(3) == 'PRI')
265	{
266	$this->pk[] = $oProc->m_odb->f(0);
267	}
268	if ($oProc->m_odb->f(3) == 'UNI')
269	{
270	$this->uc[] = $oProc->m_odb->f(0);
271	}
272	/* Hmmm, MUL could also mean unique, or not... */
273	if ($oProc->m_odb->f(3) == 'MUL')
274	{
275	$this->ix[] = $oProc->m_odb->f(0);
276	}
277	}
278	/* ugly as heck, but is here to chop the trailing comma on the last element (for php3) */
279	$this->sCol[count($this->sCol) - 1] = substr($this->sCol[count($this->sCol) - 1],0,-2) . "\n";
280
281	return false;
282	}
283
284	function DropTable($oProc, &$aTables, $sTableName)
285	{
286	return !!($oProc->m_odb->query("DROP TABLE " . $sTableName));
287	}
288
289	function DropColumn($oProc, &$aTables, $sTableName, $aNewTableDef, $sColumnName, $bCopyData = true)
290	{
291	return !!($oProc->m_odb->query("ALTER TABLE $sTableName DROP COLUMN $sColumnName"));
292	}
293
294	function RenameTable($oProc, &$aTables, $sOldTableName, $sNewTableName)
295	{
296	return !!($oProc->m_odb->query("EXEC sp_rename '$sOldTableName', '$sNewTableName'"));
297	}
298
299	function RenameColumn($oProc, &$aTables, $sTableName, $sOldColumnName, $sNewColumnName, $bCopyData = true)
300	{
301	// This really needs testing - it can affect primary keys, and other table-related objects
302	// like sequences and such
303	global $DEBUG;
304	if ($DEBUG) { echo '<br>RenameColumn: calling _GetFieldSQL for ' . $sNewColumnName; }
305	if ($oProc->_GetFieldSQL($aTables[$sTableName]["fd"][$sNewColumnName], $sNewColumnSQL))
306	{
307	return !!($oProc->m_odb->query("EXEC sp_rename '$sTableName.$sOldColumnName', '$sNewColumnName'"));
308	}
309	return false;
310	}
311
312	function AlterColumn($oProc, &$aTables, $sTableName, $sColumnName, &$aColumnDef, $bCopyData = true)
313	{
314	global $DEBUG;
315	if ($DEBUG) { echo '<br>AlterColumn: calling _GetFieldSQL for ' . $sNewColumnName; }
316	if ($oProc->_GetFieldSQL($aTables[$sTableName]["fd"][$sColumnName], $sNewColumnSQL))
317	{
318	return !!($oProc->m_odb->query("ALTER TABLE $sTableName ALTER COLUMN $sColumnName " . $sNewColumnSQL));
319	}
320
321	return false;
322	}
323
324	function AddColumn($oProc, &$aTables, $sTableName, $sColumnName, &$aColumnDef)
325	{
326	$oProc->_GetFieldSQL($aColumnDef, $sFieldSQL);
327	$query = "ALTER TABLE $sTableName ADD $sColumnName $sFieldSQL";
328
329	return !!($oProc->m_odb->query($query));
330	}
331
332	function GetSequenceSQL($sTableName, &$sSequenceSQL)
333	{
334	$sSequenceSQL = '';
335	return true;
336	}
337
338	function CreateTable($oProc, &$aTables, $sTableName, $aTableDef)
339	{
340	if ($oProc->_GetTableSQL($sTableName, $aTableDef, $sTableSQL, $sSequenceSQL,$append_ix))
341	{
342	// create sequence first since it will be needed for default
343	if ($sSequenceSQL != '')
344	{
345	$oProc->m_odb->query($sSequenceSQL);
346	}
347
348	if($append_ix)
349	{
350	$query = "CREATE TABLE $sTableName ($sTableSQL";
351	}
352	else
353	{
354	$query = "CREATE TABLE $sTableName ($sTableSQL)";
355	}
356
357	return !!($oProc->m_odb->query($query));
358	}
359
360	return false;
361	}
362	}
363	?>

Note: See TracBrowser for help on using the repository browser.

Download in other formats: