Context Navigation

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

Revision 2, 14.5 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
17	{
18	var $m_oTranslator;
19	var $m_oDeltaProc;
20	var $m_odb;
21	var $m_aTables;
22	var $m_bDeltaOnly;
23
24	function schema_proc($dbms)
25	{
26	$this->sType = $dbms;
27	$this->m_oTranslator = CreateObject('phpgwapi.schema_proc_' . $dbms);
28	$this->m_oDeltaProc = CreateObject('phpgwapi.schema_proc_array');
29	$this->m_aTables = array();
30	$this->m_bDeltaOnly = False; // Default to false here in case it's just a CreateTable script
31	}
32
33	function GenerateScripts($aTables, $bOutputHTML=False)
34	{
35	if (!is_array($aTables))
36	{
37	return False;
38	}
39	$this->m_aTables = $aTables;
40
41	$sAllTableSQL = '';
42	foreach ($this->m_aTables as $sTableName => $aTableDef)
43	{
44	$sSequenceSQL = '';
45	$append_ix = False;
46	if($this->_GetTableSQL($sTableName, $aTableDef, $sTableSQL, $sSequenceSQL,$append_ix))
47	{
48	if($append_ix)
49	{
50	$sTableSQL = "CREATE TABLE $sTableName (\n$sTableSQL\n"
51	. $this->m_oTranslator->m_sStatementTerminator;
52	}
53	else
54	{
55	$sTableSQL = "CREATE TABLE $sTableName (\n$sTableSQL\n)"
56	. $this->m_oTranslator->m_sStatementTerminator;
57	}
58	if($sSequenceSQL != '')
59	{
60	$sAllTableSQL .= $sSequenceSQL . "\n";
61	}
62	$sAllTableSQL .= $sTableSQL . "\n\n";
63	}
64	else
65	{
66	if($bOutputHTML)
67	{
68	print('<br>Failed generating script for <b>' . $sTableName . '</b><br>');
69	echo '<pre style="text-align: left;">'.$sTableName.' = '; print_r($aTableDef); echo "</pre>\n";
70	}
71
72	return false;
73	}
74	}
75
76	if($bOutputHTML)
77	{
78	print('<pre>' . $sAllTableSQL . '</pre><br><br>');
79	}
80
81	return True;
82	}
83
84	function ExecuteScripts($aTables, $bOutputHTML=False)
85	{
86	if(!is_array($aTables) \|\| !IsSet($this->m_odb))
87	{
88	return False;
89	}
90
91	reset($aTables);
92	$this->m_aTables = $aTables;
93
94	while(list($sTableName, $aTableDef) = each($aTables))
95	{
96	if($this->CreateTable($sTableName, $aTableDef))
97	{
98	if($bOutputHTML)
99	{
100	echo '<br>Create Table <b>' . $sTableName . '</b>';
101	}
102	}
103	else
104	{
105	if($bOutputHTML)
106	{
107	echo '<br>Create Table Failed For <b>' . $sTableName . '</b>';
108	}
109
110	return False;
111	}
112	}
113
114	return True;
115	}
116
117	function DropAllTables($aTables, $bOutputHTML=False)
118	{
119	if(!is_array($aTables) \|\| !isset($this->m_odb))
120	{
121	return False;
122	}
123
124	$this->m_aTables = $aTables;
125
126	reset($this->m_aTables);
127	while(list($sTableName, $aTableDef) = each($this->m_aTables))
128	{
129	if($this->DropTable($sTableName))
130	{
131	if($bOutputHTML)
132	{
133	echo '<br>Drop Table <b>' . $sTableSQL . '</b>';
134	}
135	}
136	else
137	{
138	return False;
139	}
140	}
141
142	return True;
143	}
144
145	function DropTable($sTableName)
146	{
147	$retVal = $this->m_oDeltaProc->DropTable($this, $this->m_aTables, $sTableName);
148	if($this->m_bDeltaOnly)
149	{
150	return $retVal;
151	}
152
153	return $retVal && $this->m_oTranslator->DropTable($this, $this->m_aTables, $sTableName);
154	}
155
156	function DropColumn($sTableName, $aTableDef, $sColumnName, $bCopyData = true)
157	{
158	$retVal = $this->m_oDeltaProc->DropColumn($this, $this->m_aTables, $sTableName, $aTableDef, $sColumnName, $bCopyData);
159	if($this->m_bDeltaOnly)
160	{
161	return $retVal;
162	}
163
164	return $retVal && $this->m_oTranslator->DropColumn($this, $this->m_aTables, $sTableName, $aTableDef, $sColumnName, $bCopyData);
165	}
166
167	function RenameTable($sOldTableName, $sNewTableName)
168	{
169	$retVal = $this->m_oDeltaProc->RenameTable($this, $this->m_aTables, $sOldTableName, $sNewTableName);
170	if($this->m_bDeltaOnly)
171	{
172	return $retVal;
173	}
174
175	return $retVal && $this->m_oTranslator->RenameTable($this, $this->m_aTables, $sOldTableName, $sNewTableName);
176	}
177
178	function RenameColumn($sTableName, $sOldColumnName, $sNewColumnName, $bCopyData=True)
179	{
180	$retVal = $this->m_oDeltaProc->RenameColumn($this, $this->m_aTables, $sTableName, $sOldColumnName, $sNewColumnName, $bCopyData);
181	if($this->m_bDeltaOnly)
182	{
183	return $retVal;
184	}
185
186	return $retVal && $this->m_oTranslator->RenameColumn($this, $this->m_aTables, $sTableName, $sOldColumnName, $sNewColumnName, $bCopyData);
187	}
188
189	function AlterColumn($sTableName, $sColumnName, $aColumnDef, $bCopyData=True)
190	{
191	$retVal = $this->m_oDeltaProc->AlterColumn($this, $this->m_aTables, $sTableName, $sColumnName, $aColumnDef, $bCopyData);
192	if($this->m_bDeltaOnly)
193	{
194	return $retVal;
195	}
196
197	return $retVal && $this->m_oTranslator->AlterColumn($this, $this->m_aTables, $sTableName, $sColumnName, $aColumnDef, $bCopyData);
198	}
199
200	function AddColumn($sTableName, $sColumnName, $aColumnDef)
201	{
202	$retVal = $this->m_oDeltaProc->AddColumn($this, $this->m_aTables, $sTableName, $sColumnName, $aColumnDef);
203	if($this->m_bDeltaOnly)
204	{
205	return $retVal;
206	}
207
208	return $retVal && $this->m_oTranslator->AddColumn($this, $this->m_aTables, $sTableName, $sColumnName, $aColumnDef);
209	}
210
211	function CreateTable($sTableName, $aTableDef)
212	{
213	$retVal = $this->m_oDeltaProc->CreateTable($this, $this->m_aTables, $sTableName, $aTableDef);
214	if($this->m_bDeltaOnly)
215	{
216	return $retVal;
217	}
218
219	return $retVal && $this->m_oTranslator->CreateTable($this, $this->m_aTables, $sTableName, $aTableDef);
220	}
221
222	function UpdateSequence($sTableName,$sColumnName)
223	{
224	if (method_exists($this->m_oTranslator,'UpdateSequence'))
225	{
226	return $this->m_oTranslator->UpdateSequence($this->m_odb,$sTableName,$sColumnName);
227	}
228	return True;
229	}
230
231
232	// This function manually re-created the table incl. primary key and all other indices
233	// It is meant to use if the primary key, existing indices or column-order changes or
234	// columns are not longer used or new columns need to be created (with there default value or NULL)
235	// Beside the default-value in the schema, one can give extra defaults via $aDefaults to eg. use an
236	// other colum or function to set the value of a new or changed column
237	function RefreshTable($sTableName, $aTableDef, $aDefaults=False)
238	{
239	if($GLOBALS['DEBUG']) { echo "<p>schema_proc::RefreshTable('$sTableName',"._debug_array($aTableDef,False).")<p>m_aTables[$sTableName]="._debug_array($this->m_aTables[$sTableName],False)."\n"; }
240	$old_fd = $this->m_aTables[$sTableName]['fd'];
241
242	$Ok = $this->m_oDeltaProc->RefreshTable($this, $this->m_aTables, $sTableName, $aTableDef);
243	if(!$Ok \|\| $this->m_bDeltaOnly)
244	{
245	return $Ok; // nothing else to do
246	}
247	$tmp_name = 'tmp_'.$sTableName;
248	$this->m_odb->transaction_begin();
249
250	$select = array();
251	foreach($aTableDef['fd'] as $name => $data)
252	{
253	if ($aDefaults && isset($aDefaults[$name])) // use given default
254	{
255	$value = $aDefaults[$name];
256	}
257	elseif (isset($old_fd[$name])) // existing column, use its value => column-name in query
258	{
259	$value = $name;
260	}
261	else // new column => use default value or NULL
262	{
263	if (!isset($data['default']) && (!isset($data['nullable']) \|\| $data['nullable']))
264	{
265	$value = 'NULL';
266	}
267	else
268	{
269	$value = $this->m_odb->quote(isset($data['default']) ? $data['default'] : '',$data['type']);
270	// fix for postgres error "no '<' operator for type 'unknown'"
271	if ($this->sType == 'pgsql')
272	{
273	$type_translated = $this->m_oTranslator->TranslateType($data['type']);
274	$value = "CAST($value AS $type_translated)";
275	}
276	}
277	}
278	$select[] = $value;
279	}
280	$select = implode(',',$select);
281
282	$Ok = $this->RenameTable($sTableName,$tmp_name) &&
283	$this->CreateTable($sTableName,$aTableDef) &&
284	$this->m_odb->query("INSERT INTO $sTableName SELECT DISTINCT $select FROM $tmp_name",__LINE__,__FILE__);
285
286	if (!$Ok)
287	{
288	$this->m_odb->transaction_abort();
289	return False;
290	}
291	// do we need to update the new sequences value ?
292	if (count($aTableDef['pk']) == 1 && $aTableDef['fd'][$aTableDef['pk'][0]]['type'] == 'auto')
293	{
294	$this->UpdateSequence($sTableName,$aTableDef['pk'][0]);
295	}
296	$this->DropTable($tmp_name);
297	$this->m_odb->transaction_commit();
298
299	return True;
300	}
301
302	function f($value)
303	{
304	if($this->m_bDeltaOnly)
305	{
306	// Don't care, since we are processing deltas only
307	return False;
308	}
309
310	return $this->m_odb->f($value);
311	}
312
313	function num_rows()
314	{
315	if($this->m_bDeltaOnly)
316	{
317	// If not False, we will cause while loops calling us to hang
318	return False;
319	}
320
321	return $this->m_odb->num_rows();
322	}
323
324	function next_record()
325	{
326	if($this->m_bDeltaOnly)
327	{
328	// If not False, we will cause while loops calling us to hang
329	return False;
330	}
331
332	return $this->m_odb->next_record();
333	}
334
335	function query($sQuery, $line='', $file='')
336	{
337	if($this->m_bDeltaOnly)
338	{
339	// Don't run this query, since we are processing deltas only
340	return True;
341	}
342
343	return $this->m_odb->query($sQuery, $line, $file);
344	}
345
346	function _GetTableSQL($sTableName, $aTableDef, &$sTableSQL, &$sSequenceSQL,&$append_ix)
347	{
348	if(!is_array($aTableDef))
349	{
350	return False;
351	}
352
353	$sTableSQL = '';
354	reset($aTableDef['fd']);
355	while(list($sFieldName, $aFieldAttr) = each($aTableDef['fd']))
356	{
357	$sFieldSQL = '';
358	if($this->_GetFieldSQL($aFieldAttr, $sFieldSQL))
359	{
360	if($sTableSQL != '')
361	{
362	$sTableSQL .= ",\n";
363	}
364
365	$sTableSQL .= "$sFieldName $sFieldSQL";
366
367	if($aFieldAttr['type'] == 'auto')
368	{
369	$this->m_oTranslator->GetSequenceSQL($sTableName, $sSequenceSQL);
370	if($sSequenceSQL != '')
371	{
372	$sTableSQL .= sprintf(" DEFAULT nextval('seq_%s')", $sTableName);
373	}
374	}
375	}
376	else
377	{
378	if($GLOBALS['DEBUG']) { echo 'GetFieldSQL failed for ' . $sFieldName; }
379	return False;
380	}
381	}
382
383	$sUCSQL = '';
384	$sPKSQL = '';
385	$sIXSQL = '';
386
387	if(count($aTableDef['pk']) > 0)
388	{
389	if(!$this->_GetPK($aTableDef['pk'], $sPKSQL))
390	{
391	if($bOutputHTML)
392	{
393	print('<br>Failed getting primary key<br>');
394	}
395
396	return False;
397	}
398	}
399
400	if(count($aTableDef['uc']) > 0)
401	{
402	if(!$this->_GetUC($aTableDef['uc'], $sUCSQL))
403	{
404	if($bOutputHTML)
405	{
406	print('<br>Failed getting unique constraint<br>');
407	}
408
409	return False;
410	}
411	}
412
413	if(count($aTableDef['ix']) > 0)
414	{
415	$append_ix = False;
416	if(!$this->_GetIX($aTableDef['ix'], $sIXSQL,$append_ix,$sTableName))
417	{
418	if($bOutputHTML)
419	{
420	print('<br>Failed getting index<br>');
421	}
422
423	return False;
424	}
425	// print('<br>HELLO!: ' . $sIXSQL);
426	}
427
428	if($sPKSQL != '')
429	{
430	$sTableSQL .= ",\n" . $sPKSQL;
431	}
432
433	if($sUCSQL != '')
434	{
435	$sTableSQL .= ",\n" . $sUCSQL;
436	}
437
438	if($sIXSQL != '')
439	{
440	if($append_ix)
441	{
442	$sTableSQL .= ");\n" . $sIXSQL;
443	//pg: CREATE INDEX test1_id_index ON test1 (id);
444	}
445	else
446	{
447	$sTableSQL .= ",\n" . $sIXSQL;
448	}
449	}
450
451	return True;
452	}
453
454	// Get field DDL
455	function _GetFieldSQL($aField, &$sFieldSQL)
456	{
457	if($GLOBALS['DEBUG']) { echo'<br>_GetFieldSQL(): Incoming ARRAY: '; var_dump($aField); }
458	if(!is_array($aField))
459	{
460	return false;
461	}
462
463	$sType = '';
464	$iPrecision = 0;
465	$iScale = 0;
466	$bNullable = true;
467
468	reset($aField);
469	while(list($sAttr, $vAttrVal) = each($aField))
470	{
471	switch ($sAttr)
472	{
473	case 'type':
474	$sType = $vAttrVal;
475	break;
476	case 'precision':
477	$iPrecision = (int)$vAttrVal;
478	break;
479	case 'scale':
480	$iScale = (int)$vAttrVal;
481	break;
482	case 'nullable':
483	$bNullable = $vAttrVal;
484	break;
485	default:
486	break;
487	}
488	}
489
490	// Translate the type for the DBMS
491	if($sFieldSQL = $this->m_oTranslator->TranslateType($sType, $iPrecision, $iScale))
492	{
493	if(strpos(strtolower($sFieldSQL),'null')===false)
494	{
495	if(!$bNullable)
496	{
497	$sFieldSQL .= ' NOT NULL';
498	}
499	elseif ($this->m_oTranslator->b_needExplicitNULL)
500	{
501	$sFieldSQL .= ' NULL';
502	}
503	}
504	if(isset($aField['default']))
505	{
506	if($GLOBALS['DEBUG']) { echo'<br>_GetFieldSQL(): Calling TranslateDefault for "' . $aField['default'] . '"'; }
507	// Get default DDL - useful for differences in date defaults (eg, now() vs. getdate())
508
509	$sFieldSQL .= ' DEFAULT ' . (is_numeric($aField['default']) ? $aField['default'] :
510	$this->m_oTranslator->TranslateDefault($aField['default']));
511	}
512	if($GLOBALS['DEBUG']) { echo'<br>_GetFieldSQL(): Outgoing SQL: ' . $sFieldSQL; }
513	return true;
514	}
515
516	if($GLOBALS['DEBUG']) { echo '<br>Failed to translate field: type[' . $sType . '] precision[' . $iPrecision . '] scale[' . $iScale . ']<br>'; }
517
518	return False;
519	}
520
521	function _GetPK($aFields, &$sPKSQL)
522	{
523	$sPKSQL = '';
524	if(count($aFields) < 1)
525	{
526	return True;
527	}
528
529	$sPKSQL = $this->m_oTranslator->GetPKSQL(implode(',',$aFields));
530
531	return True;
532	}
533
534	function _GetUC($aFields, &$sUCSQL)
535	{
536	$sUCSQL = '';
537	if(count($aFields) < 1)
538	{
539	return True;
540	}
541	foreach($aFields as $mFields)
542	{
543	$aUCSQL[] = $this->m_oTranslator->GetUCSQL(
544	is_array($mFields) ? implode(',',$mFields) : $mFields);
545	}
546	$sUCSQL = implode(",\n",$aUCSQL);
547
548	return True;
549	}
550
551	function _GetIX($aFields, &$sIXSQL, &$append, $sTableName)
552	{
553	$sUCSQL = '';
554	if(count($aFields) < 1)
555	{
556	return True;
557	}
558	$aIXSQL = array();
559	foreach($aFields as $mFields)
560	{
561	$options = '';
562	if (is_array($mFields))
563	{
564	if (isset($mFields['options'])) // array sets additional options
565	{
566	$options = @$mFields['options'][$this->sType]; // db-specific options, eg. index-type
567	unset($mFields['options']);
568	}
569	if ($options === false)
570	{
571	continue; // dont create index for that db, eg. cant index text
572	}
573	$mFields = implode(',',$mFields);
574	}
575	$aIXSQL[] = $this->m_oTranslator->GetIXSQL($mFields,$append,$options,$sTableName);
576	}
577	if($append)
578	{
579	$sIXSQL = implode("\n",$aIXSQL);
580	}
581	else
582	{
583	$sIXSQL = implode(",\n",$aIXSQL);
584	}
585
586	return True;
587	}
588	}
589	?>

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

Download in other formats: