1<?php
2	/**
3	* Database schema abstraction class for PostgreSQL
4	* @author Tobias Ratschiller <tobias@dnet.it>
5	* @author Dan Wilson <phpPgAdmin@acucore.com>
6	* @author Michael Dean <mdean@users.sourceforge.net>
7	* @author Miles Lott <milosch@phpgroupware.org>
8	* @copyright Copyright (C) 1998-1999 Tobias Ratschiller
9	* @copyright Copyright (C) 1999-2000 Dan Wilson
10	* @copyright Copyright (C) ? Michael Dean, Miles Lott
11	* @copyright Portions Copyright (C) 2003,2004 Free Software Foundation, Inc. http://www.fsf.org/
12	* @license http://www.fsf.org/licenses/gpl.html GNU General Public License
13	* @package phpgwapi
14	* @subpackage database
15	* @version $Id: class.schema_proc_pgsql.inc.php 16676 2006-04-20 06:47:04Z sigurdne $
16	* @link http://www.greatbridge.org/project/phppgadmin
17	* @internal SQL for table properties taken from phpPgAdmin Version 2.2.1
18	*/
19
20	/**
21	* Database schema abstraction class for PostgreSQL
22	*
23	* @package phpgwapi
24	* @subpackage database
25	*/
26	class schema_proc_pgsql
27	{
28		var $m_sStatementTerminator;
29		/* Following added to convert sql to array */
30		var $sCol = array();
31		var $pk = array();
32		var $fk = array();
33		var $ix = array();
34		var $uc = array();
35
36		function schema_proc_pgsql()
37		{
38			$this->m_sStatementTerminator = ';';
39		}
40
41		/* Return a type suitable for DDL */
42		function TranslateType($sType, $iPrecision = 0, $iScale = 0)
43		{
44			switch($sType)
45			{
46				case 'auto':
47					$sTranslated = 'int4';
48					break;
49				case 'blob':
50					$sTranslated = 'text';
51					break;
52				case 'char':
53					if ($iPrecision > 0 && $iPrecision < 256)
54					{
55						$sTranslated =  sprintf("char(%d)", $iPrecision);
56					}
57					if ($iPrecision > 255)
58					{
59						$sTranslated =  'text';
60					}
61					break;
62				case 'date':
63					$sTranslated =  'date';
64					break;
65				case 'decimal':
66					$sTranslated =  sprintf("decimal(%d,%d)", $iPrecision, $iScale);
67					break;
68				case 'float':
69					if ($iPrecision == 4 || $iPrecision == 8)
70					{
71						$sTranslated =  sprintf("float%d", $iPrecision);
72					}
73					break;
74				case 'int':
75					if ($iPrecision == 2 || $iPrecision == 4 || $iPrecision == 8)
76					{
77						$sTranslated =  sprintf("int%d", $iPrecision);
78					}
79					break;
80				case 'longtext':
81					$sTranslated = 'text';
82					break;
83				case 'text':
84					$sTranslated = 'text';
85					break;
86				case 'timestamp':
87					$sTranslated = 'timestamp';
88					break;
89				case 'varchar':
90					if ($iPrecision > 0 && $iPrecision < 256)
91					{
92						$sTranslated =  sprintf("varchar(%d)", $iPrecision);
93					}
94					if ($iPrecision > 255)
95					{
96						$sTranslated =  'text';
97					}
98					break;
99			}
100			return $sTranslated;
101		}
102
103		function TranslateDefault($sDefault)
104		{
105			switch ($sDefault)
106			{
107				case 'current_date':
108				case 'current_timestamp':
109					return "'now'";
110			}
111			return "'" . $sDefault . "'";
112		}
113
114		/* Inverse of above, convert sql column types to array info */
115		function rTranslateType($sType, $iPrecision = 0, $iScale = 0)
116		{
117			$sTranslated = '';
118			switch($sType)
119			{
120				case 'serial':
121					$sTranslated = "'type' => 'auto'";
122					break;
123				case 'int2':
124					$sTranslated = "'type' => 'int', 'precision' => 2";
125					break;
126				case 'int4':
127					$sTranslated = "'type' => 'int', 'precision' => 4";
128					break;
129				case 'int8':
130					$sTranslated = "'type' => 'int', 'precision' => 8";
131					break;
132				case 'bpchar':
133				case 'char':
134					if ($iPrecision > 0 && $iPrecision < 256)
135					{
136						$sTranslated = "'type' => 'char', 'precision' => $iPrecision";
137					}
138					if ($iPrecision > 255)
139					{
140						$sTranslated =  "'type' => 'text'";
141					}
142					break;
143				case 'numeric':
144					/* Borrowed from phpPgAdmin */
145					$iPrecision = ($iScale >> 16) & 0xffff;
146					$iScale     = ($iScale - 4) & 0xffff;
147					$sTranslated = "'type' => 'decimal', 'precision' => $iPrecision, 'scale' => $iScale";
148					break;
149				case 'float':
150				case 'float4':
151				case 'float8':
152				case 'double':
153					$sTranslated = "'type' => 'float', 'precision' => $iPrecision";
154					break;
155				case 'datetime':
156				case 'timestamp':
157					$sTranslated = "'type' => 'timestamp'";
158					break;
159				case 'varchar':
160					if ($iPrecision > 0 && $iPrecision < 256)
161					{
162						$sTranslated =  "'type' => 'varchar', 'precision' => $iPrecision";
163					}
164					if ($iPrecision > 255)
165					{
166						$sTranslated =  "'type' => 'text'";
167					}
168					break;
169				case 'text':
170				case 'blob':
171				case 'date':
172					$sTranslated = "'type' => '$sType'";
173					break;
174			}
175			return $sTranslated;
176		}
177
178		function GetPKSQL($sFields)
179		{
180			return "PRIMARY KEY($sFields)";
181		}
182
183		function GetUCSQL($sFields)
184		{
185			return "UNIQUE($sFields)";
186		}
187
188		function GetIXSQL($sFields)
189		{
190			$this->indexes_sql[$sFields] = "CREATE INDEX __index_name__ ON __table_name__ USING btree ($sFields)";
191			return '';
192		}
193
194
195               function GetFKSQL($sFields)
196               {
197                 if (ereg("\((.*)\)", $sFields, $regs))
198                 {
199                   $ret = "FOREIGN KEY (".$regs[1].")\n" .
200                     "  REFERENCES ".$sFields;
201                   return $ret;
202                 } else
203                   return ""; // incorrect FK declaration found
204               }
205
206		function _GetColumns($oProc, $sTableName, &$sColumns, $sDropColumn = '', $sAlteredColumn = '', $sAlteredColumnType = '')
207		{
208			$sdb = $oProc->m_odb;
209			$sdc = $oProc->m_odb;
210
211			$sColumns = '';
212			$this->pk = array();
213			$this->fk = array();
214			$this->ix = array();
215			$this->uc = array();
216
217			$query = "SELECT a.attname,a.attnum FROM pg_attribute a,pg_class b WHERE ";
218			$query .= "b.oid=a.attrelid AND a.attnum>0 and b.relname='$sTableName'";
219			if ($sDropColumn != '')
220			{
221				$query .= " AND a.attname != '$sDropColumn'";
222			}
223			$query .= ' ORDER BY a.attnum';
224
225			$oProc->m_odb->query($query);
226			while ($oProc->m_odb->next_record())
227			{
228				if ($sColumns != '')
229				{
230					$sColumns .= ',';
231				}
232
233				$sFieldName = $oProc->m_odb->f(0);
234				$sColumns .= $sFieldName;
235				if ($sAlteredColumn == $sFieldName && $sAlteredColumnType != '')
236				{
237					$sColumns .= '::' . $sAlteredColumnType;
238				}
239			}
240			//$qdefault = "SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c "
241			//	. "WHERE c.relname = $sTableName AND c.oid = d.adrelid AND d.adnum =" . $oProc->m_odb->f(1);
242			$sql_get_fields = "
243				SELECT
244					a.attnum,
245					a.attname AS field,
246					t.typname AS type,
247					a.attlen AS length,
248					a.atttypmod AS lengthvar,
249					a.attnotnull AS notnull
250				FROM
251					pg_class c,
252					pg_attribute a,
253					pg_type t
254				WHERE
255					c.relname = '$sTableName'
256					and a.attnum > 0
257					and a.attrelid = c.oid
258					and a.atttypid = t.oid
259					ORDER BY a.attnum";
260			/* attnum field type length lengthvar notnull(Yes/No) */
261			$sdb->query($sql_get_fields);
262			while ($sdb->next_record())
263			{
264				$colnum  = $sdb->f(0);
265				$colname = $sdb->f(1);
266
267				if ($sdb->f(5) == 'Yes')
268				{
269					$null = "'nullable' => True";
270				}
271				else
272				{
273					$null = "'nullable' => False";
274				}
275
276				if ($sdb->f(2) == 'numeric')
277				{
278					$prec  = $sdb->f(3);
279					$scale = $sdb->f(4);
280				}
281				elseif ($sdb->f(3) > 0)
282				{
283					$prec  = $sdb->f(3);
284					$scale = 0;
285				}
286				elseif ($sdb->f(4) > 0)
287				{
288					$prec = $sdb->f(4) - 4;
289					$scale = 0;
290				}
291				else
292				{
293					$prec = 0;
294					$scale = 0;
295				}
296
297				$type = $this->rTranslateType($sdb->f(2), $prec, $scale);
298
299				$sql_get_default = "
300					SELECT d.adsrc AS rowdefault
301						FROM pg_attrdef d, pg_class c
302						WHERE
303							c.relname = '$sTableName' AND
304							c.oid = d.adrelid AND
305							d.adnum = $colnum
306					";
307				$sdc->query($sql_get_default);
308				$sdc->next_record();
309				if ($sdc->f(0))
310				{
311					if (ereg('nextval',$sdc->f(0)))
312					{
313						$default = '';
314						$nullcomma = '';
315					}
316					else
317					{
318						$default = "'default' => '".$sdc->f(0)."'";
319						$nullcomma = ',';
320					}
321				}
322				else
323				{
324					$default = '';
325					$nullcomma = '';
326				}
327				$default = ereg_replace("''","'",$default);
328
329				$this->sCol[] = "\t\t\t\t'" . $colname . "' => array(" . $type . ',' . $null . $nullcomma . $default . '),' . "\n";
330			}
331			$sql_pri_keys = "
332				SELECT
333					ic.relname AS index_name,
334					bc.relname AS tab_name,
335					ta.attname AS column_name,
336					i.indisunique AS unique_key,
337					i.indisprimary AS primary_key
338				FROM
339					pg_class bc,
340					pg_class ic,
341					pg_index i,
342					pg_attribute ta,
343					pg_attribute ia
344				WHERE
345					bc.oid = i.indrelid
346					AND ic.oid = i.indexrelid
347					AND ia.attrelid = i.indexrelid
348					AND ta.attrelid = bc.oid
349					AND bc.relname = '$sTableName'
350					AND ta.attrelid = i.indrelid
351					AND ta.attnum = i.indkey[ia.attnum-1]
352				ORDER BY
353					index_name, tab_name, column_name";
354			$sdc->query($sql_pri_keys);
355			while ($sdc->next_record())
356			{
357				//echo '<br /> checking: ' . $sdc->f(4);
358				if ($sdc->f(4) == 't')
359				{
360					$this->pk[] = $sdc->f(2);
361				}
362				if ($sdc->f(3) == 't')
363				{
364					$this->uc[] = $sdc->f(2);
365				}
366			}
367			/* ugly as heck, but is here to chop the trailing comma on the last element (for php3) */
368			$this->sCol[count($this->sCol) - 1] = substr($this->sCol[count($this->sCol) - 1],0,-2) . "\n";
369
370			return false;
371		}
372
373		function GetSequenceForTable($oProc,$table,&$sSequenceName)
374		{
375			global $DEBUG;
376			if($DEBUG) { echo '<br />GetSequenceForTable: ' . $table; }
377
378			$oProc->m_odb->query("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE 'seq_$table' AND relkind='S' ORDER BY relname",__LINE__,__FILE__);
379			$oProc->m_odb->next_record();
380			if ($oProc->m_odb->f('relname'))
381			{
382				$sSequenceName = $oProc->m_odb->f('relname');
383			}
384			return True;
385		}
386
387		function GetSequenceFieldForTable($oProc,$table,&$sField)
388		{
389			global $DEBUG;
390			if($DEBUG) { echo '<br />GetSequenceFieldForTable: You rang?'; }
391			$oProc->m_odb->query("SELECT a.attname FROM pg_attribute a, pg_class c, pg_attrdef d WHERE c.relname='$table' AND c.oid=d.adrelid AND d.adsrc LIKE '%seq_$table%' AND a.attrelid=c.oid AND d.adnum=a.attnum");
392			$oProc->m_odb->next_record();
393			if ($oProc->m_odb->f('attname'))
394			{
395				$sField = $oProc->m_odb->f('attname');
396			}
397			return True;
398		}
399
400		function DropSequenceForTable($oProc,$table)
401		{
402			global $DEBUG;
403			if($DEBUG) { echo '<br />DropSequenceForTable: ' . $table; }
404
405			$this->GetSequenceForTable($oProc,$table,$sSequenceName);
406			if ($sSequenceName)
407			{
408				$oProc->m_odb->query("DROP SEQUENCE " . $sSequenceName . " CASCADE",__LINE__,__FILE__);
409			}
410			return True;
411		}
412
413		function DropTable($oProc, &$aTables, $sTableName)
414		{
415			$this->DropSequenceForTable($oProc,$sTableName);
416
417			return $oProc->m_odb->query("DROP TABLE " . $sTableName . " CASCADE") &&
418			       $this->DropSequenceForTable($oProc, $sTableName);
419		}
420
421		function DropColumn($oProc, &$aTables, $sTableName, $aNewTableDef, $sColumnName, $bCopyData = true)
422		{
423			$query = "ALTER TABLE $sTableName DROP COLUMN $sColumnName CASCADE";
424			$bRet = !!($oProc->m_odb->query($query));
425			return $bRet;
426
427		}
428
429		function RenameTable($oProc, &$aTables, $sOldTableName, $sNewTableName)
430		{
431			global $DEBUG;
432			if ($DEBUG) { echo '<br>RenameTable():' . $sOldTableName . 'to: '. $sNewTableName; }
433			if ($DEBUG) { echo '<br>RenameTable(): Fetching old sequence for: ' . $sOldTableName; }
434			$this->GetSequenceForTable($oProc,$sOldTableName,$sSequenceName);
435			if ($DEBUG) { echo ' - ' . $sSequenceName; }
436			if ($DEBUG) { echo '<br>RenameTable(): Fetching sequence field for: ' . $sOldTableName; }
437			$this->GetSequenceFieldForTable($oProc,$sOldTableName,$sField);
438			if ($DEBUG) { echo ' - ' . $sField; }
439
440			if ($sSequenceName)
441			{
442				$oProc->m_odb->query("SELECT last_value FROM seq_$sOldTableName",__LINE__,__FILE__);
443				$oProc->m_odb->next_record();
444				$lastval = $oProc->m_odb->f(0);
445
446				if ($lastval)
447				{
448					$lastval = ' start ' . $lastval;
449				}
450				$this->GetSequenceSQL($sNewTableName,$sSequenceSQL);
451				if ($DEBUG) { echo '<br>RenameTable(): Making new sequence using: ' . $sSequenceSQL . $lastval; }
452				$oProc->m_odb->query($sSequenceSQL . $lastval,__LINE__,__FILE__);
453				if ($DEBUG) { echo '<br>RenameTable(): Altering column default for: ' . $sField; }
454			}
455
456			$oProc->m_odb->query("ALTER TABLE $sOldTableName RENAME TO $sNewTableName");
457			if ($sSequenceName)
458			{
459				$Ok = !!($oProc->m_odb->query("ALTER TABLE $sNewTableName ALTER $sField SET DEFAULT nextval('seq_" . $sNewTableName . "')"));
460				$this->DropSequenceForTable($oProc,$sOldTableName);
461			}
462
463			return $Ok;
464
465		/* todo - fix index-renaming.
466			$indexnames = $oProc->m_odb->index_names();
467			while(list($key,$val) = @each($indexnames))
468			{
469				$indexes[] = $val['index_name'];
470			}
471			if(!in_array($sOldTableName . '_pkey',$indexes))	// no idea how this can happen
472			{
473				$oProc->m_odb->query("DROP INDEX " . $sOldTableName . "_pkey",__LINE__,__FILE__);
474			}
475			else	// rename the index
476			{
477				$oProc->m_odb->query('ALTER TABLE '.$sOldTableName.'_pkey RENAME TO '.$sNewTableName.'_pkey');
478			}
479
480
481			return !!($oProc->m_odb->query("ALTER TABLE $sOldTableName RENAME TO $sNewTableName"));
482		*/
483		}
484
485		function RenameColumn($oProc, &$aTables, $sTableName, $sOldColumnName, $sNewColumnName, $bCopyData = true)
486		{
487			$query = "ALTER TABLE $sTableName RENAME COLUMN $sOldColumnName TO $sNewColumnName";
488			return !!($oProc->m_odb->query($query));
489		}
490
491		function AlterColumn($oProc, &$aTables, $sTableName, $sColumnName, &$aColumnDef, $bCopyData = true)
492		{
493			$sType = '';
494			$iPrecision = 0;
495			$iScale = 0;
496			$sDefault = '';
497			$bNullable = true;
498
499			reset($aColumnDef);
500			while(list($sAttr, $vAttrVal) = each($aColumnDef))
501			{
502				switch ($sAttr)
503				{
504					case 'type':
505						$sType = $vAttrVal;
506						break;
507					case 'precision':
508						$iPrecision = (int)$vAttrVal;
509						break;
510					case 'scale':
511						$iScale = (int)$vAttrVal;
512						break;
513					case 'default':
514						$sDefault = $vAttrVal;
515						break;
516					case 'nullable':
517						$bNullable = $vAttrVal;
518						break;
519				}
520			}
521
522			$sFieldSQL = $this->TranslateType($sType, $iPrecision, $iScale);
523			$query = "ALTER TABLE $sTableName ALTER COLUMN $sColumnName TYPE $sFieldSQL";
524			$Ok = !!($oProc->m_odb->query($query));
525
526			if($bNullable == False || $bNullable == 'False')
527			{
528				$sFieldSQL = ' NOT NULL';
529				$query = "ALTER TABLE $sTableName ALTER COLUMN $sColumnName SET $sFieldSQL";
530				$Ok = !!($oProc->m_odb->query($query));
531			}
532
533			if($sDefault == '0')
534			{
535				$defaultSQL = " DEFAULT 0";
536			}
537			elseif(!is_numeric($sDefault) && $sDefault != '')
538			{
539				$sTranslatedDefault = $this->TranslateDefault($sDefault);
540				$defaultSQL = " DEFAULT $sTranslatedDefault";
541			}
542			elseif($sDefault)
543			{
544				$defaultSQL = " DEFAULT $sDefault";
545			}
546
547			if($defaultSQL)
548			{
549				$query = "ALTER TABLE $sTableName ALTER COLUMN $sColumnName SET $defaultSQL";
550				$Ok = !!($oProc->m_odb->query($query));
551			}
552
553			return $Ok;
554		}
555
556		function AddColumn($oProc, &$aTables, $sTableName, $sColumnName, &$aColumnDef)
557		{
558			if (isset($aColumnDef['default']))	// pgsql cant add a colum with a default
559			{
560				$default = $aColumnDef['default'];
561				unset($aColumnDef['default']);
562			}
563			if (isset($aColumnDef['nullable']) && !$aColumnDef['nullable'])	// pgsql cant add a column not nullable
564			{
565				$notnull = !$aColumnDef['nullable'];
566				unset($aColumnDef['nullable']);
567			}
568			$oProc->_GetFieldSQL($aColumnDef, $sFieldSQL);
569			$query = "ALTER TABLE $sTableName ADD COLUMN $sColumnName $sFieldSQL";
570
571			if (($Ok = !!($oProc->m_odb->query($query))) && isset($default))
572			{
573				if($default == '0')
574				{
575					$defaultSQL = " DEFAULT 0";
576				}
577				elseif(!is_numeric($default) && $default != '')
578				{
579					$sTranslatedDefault = $this->TranslateDefault($default);
580					$defaultSQL = " DEFAULT $sTranslatedDefault";
581				}
582				elseif($default)
583				{
584					$defaultSQL = " DEFAULT $default";
585				}
586
587				$query = "ALTER TABLE $sTableName ALTER COLUMN $sColumnName SET $defaultSQL;\n";
588
589				$query .= "UPDATE $sTableName SET $sColumnName='$default';\n";
590
591				$Ok = !!($oProc->m_odb->query($query));
592
593				if ($OK && $notnull)
594				{
595					// unfortunally this is pgSQL >= 7.3
596					//$query .= "ALTER TABLE $sTableName ALTER COLUMN $sColumnName SET NOT NULL;\n";
597					//$Ok = !!($oProc->m_odb->query($query));
598					// so we do it the slow way
599					AlterColumn($oProc, $aTables, $sTableName, $sColumnName, $aColumnDef);
600				}
601			}
602			return $Ok;
603		}
604
605		function GetSequenceSQL($sTableName, &$sSequenceSQL)
606		{
607			$sSequenceSQL = sprintf("CREATE SEQUENCE seq_%s", $sTableName);
608			return true;
609		}
610
611		function CreateTable($oProc, $aTables, $sTableName, $aTableDef, $bCreateSequence = true)
612		{
613			global $DEBUG;
614			unset($this->indexes_sql);
615			if ($oProc->_GetTableSQL($sTableName, $aTableDef, $sTableSQL, $sSequenceSQL))
616			{
617				/* create sequence first since it will be needed for default */
618				if ($bCreateSequence && $sSequenceSQL != '')
619				{
620					if ($DEBUG) { echo '<br />Making sequence using: ' . $sSequenceSQL; }
621					$oProc->m_odb->query($sSequenceSQL);
622				}
623
624				$query = "CREATE TABLE $sTableName ($sTableSQL)";
625				//echo 'sql' .$query . "\n";
626
627				$result = !!($oProc->m_odb->query($query));
628				if($result==True)
629				{
630					if ($DEBUG)
631					{
632						echo  '<pre>';
633						print_r($this->indexes_sql);
634						echo '</pre>';
635					}
636
637					if(is_array($this->indexes_sql) && count($this->indexes_sql)>0)
638					{
639						foreach($this->indexes_sql as $key => $sIndexSQL)
640						{
641							$ix_name = str_replace(',','_',$key).'_'.$sTableName.'_idx';
642							$IndexSQL = str_replace(array('__index_name__','__table_name__'), array($ix_name,$sTableName), $sIndexSQL);
643							$oProc->m_odb->query($IndexSQL);
644						}
645					}
646				}
647				return $result;
648				//return !!($oProc->m_odb->query($query));
649			}
650
651			return false;
652		}
653	}
654?>
655