<?php
/* $Id$ */
namespace Mammut\DB\Postgre;

(defined('MAMMUT') && (basename(__FILE__) != basename($_SERVER['PHP_SELF']))) or die('ACCESS DENIED');

use Mammut\DB\DB;
use Mammut\DB\Charset;
use Mammut\DB\Model\TableInfo;
use Mammut\DB\Model\ColumnInfo;
use Mammut\DB\Sql\Dialect\iDialect;
use Mammut\Exception\ImplementationException;

/**
 * Postgre utility library
 *
 * @author Stefan Daurer <s.daurer@q-spot.org>
 * @package Mammut\DB\Adapter\PostgreSQL
 */
class PostGreUtil extends \Mammut\StrictObject {

	private function __construct() {
	} // only static access
	
// 	public static function getTableInfoSql(iDialect $d, $schema,$table) {
// 		return 'SELECT
//   				c.relname AS "name",
// 				n.nspname AS "schema",
//   				CASE c.relkind WHEN \'r\' THEN \'table\' WHEN \'v\' THEN \'view\' WHEN \'i\' THEN \'index\' WHEN \'S\' THEN \'sequence\' WHEN \'s\' THEN \'special\' END as "type",
//   				u.usename as "owner",
//   				c2.relname as "table"
// 				FROM pg_catalog.pg_class c
//      			JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
//      			JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
//      			LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
//      			LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
// 				WHERE c.relkind IN (\'i\',\'\')
//       			AND n.nspname NOT IN (\'pg_catalog\', \'pg_toast\')
//       			AND pg_catalog.pg_table_is_visible(c.oid)
//       			AND n.nspname = '.$d->quoteValue($schema).'
//       			AND c2.relname = '.$d->quoteValue($table);
// 	}

// 	protected static function genNumSequence($table, $column) {
// 		$seqName = strtolower($table) . '_' . strtolower($column) . '_seq';
// 		$preSQL = 'CREATE SEQUENCE "' . $seqName . '"';
// 		$postSQL = 'ALTER SEQUENCE "' . $seqName . '" OWNED BY "' . $table . '"."' . $column . '"';
		
// 		$result = array('name' => $seqName,'pre' => $preSQL,'post' => $postSQL);
		
// 		return $result;
// 	}

// 	protected static function genDataType($type, $size) {
// 		switch($type) {
			
// 			case ColumnInfo::TYPE_BOOLEAN:
// 				$sql = 'BOOLEAN';
// 				return $sql;
			
// 			case ColumnInfo::TYPE_TINY:
// 				$sql = 'SMALLINT'; // pgsql has no tinyint type
// 				return $sql;
			
// 			case ColumnInfo::TYPE_SHORT:
// 				$sql .= 'SMALLINT';
// 				return $sql;
			
// 			case ColumnInfo::TYPE_INT:
// 				$sql .= 'INTEGER';
// 				return $sql;
			
// 			case ColumnInfo::TYPE_LONG:
// 				$sql .= 'BIGINT';
// 				return $sql;
			
// 			case ColumnInfo::TYPE_FLOAT:
// 				$sql .= ' REAL';
// 				return $sql;
			
// 			case ColumnInfo::TYPE_DOUBLE:
// 				$sql .= ' DOUBLE PRECISION';
// 				$size = $column->getSize();
// 				if(!is_int($size))
// 					throw new \InvalidArgumentException("precicion has to be a integer for type double");
					
// 					// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
// 				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 			break;
			
// 			case ColumnInfo::TYPE_DECIMAL:
// 				$sql .= ' DECIMAL';
// 				$size = $column->getSize();
// 				if(!is_array($size))
// 					throw new \InvalidArgumentException("size has to be an array for type decimal");
// 				if(count($size) != 2)
// 					throw new \RangeException("size array has to containt 2 integers");
// 				reset($size);
// 				$m = (int) current($size);
// 				next($size);
// 				$d = (int) current($size);
// 				if($m < 0 || $d < 0)
// 					throw new \UnexpectedValueException("integers of the size array needs to be positive");
				
// 				$sql .= '(' . $m . ',' . $d . ')';
// 				// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
// 				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 			break;
			
// 			case ColumnInfo::TYPE_CHAR:
// 				$sql .= ' CHAR';
// 				$size = $column->getSize();
// 				if(!is_int($size))
// 					throw new \InvalidArgumentException("size has to be a integer for type char");
				
// 				$size = (int) $size;
// 				if($size > 0)
// 					$sql .= '(' . $size . ')';
					
// 					//	$sql .= ' '.self::createCollateSQL($column->getCharset(), $column->getCollate());
// 				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 			break;
			
// 			case ColumnInfo::TYPE_VCHAR:
// 				$sql .= ' VARCHAR';
// 				$size = $column->getSize();
// 				if(!is_int($size))
// 					throw new \InvalidArgumentException("size has to be a integer for type vchar");
				
// 				$size = (int) $size;
// 				if($size > 0)
// 					$sql .= '(' . $size . ')';
					
// 					//	$sql .= ' '.self::createCollateSQL($column->getCharset(), $column->getCollate());
// 				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 			break;
			
// 			case ColumnInfo::TYPE_TEXT:
// 				$sql .= ' TEXT';
				
// 				//	$sql .= ' '.self::createCollateSQL($column->getCharset(), $column->getCollate());
// 				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 			break;
			
// 			case ColumnInfo::TYPE_BINARY:
// 				$sql .= ' BYTEA';
// 				// $size = $column->getSize();
// 				//if (!is_int($size))
// 				//	throw new \InvalidArgumentException("size has to be a integer for type binary");
// 				// $size = (int) $size;
// 				//if ($size > 0)
// 				//	$sql .= '('.$size.')';
// 				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 			break;
			
// 			case ColumnInfo::TYPE_BLOB:
// 				$sql .= ' BYTEA';
// 				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 			break;
			
// 			case ColumnInfo::TYPE_DATE:
// 				$sql .= ' DATE';
// 				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 			break;
			
// 			case ColumnInfo::TYPE_DATETIME:
// 				$sql .= ' TIMESTAMP';
// 				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 			break;
			
// 			case ColumnInfo::TYPE_UTS:
// 				$sql .= ' INT';
// 				$sql .= $column->isUnsigned() ? ' UNSIGNED' : '';
// 				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 			break;
			
// 			case ColumnInfo::TYPE_ENUM:
// 				$values = array();
// 				foreach($column->getParam() as $e) {
// 					if(preg_match('#values\(([a-zA-Z0-9_,]*)\)#', $e, $match)) {
// 						$values = explode(',', $match[1]);
// 						break;
// 					}
// 				}
// 				$enuName = $info->getName() . '_' . strtolower($column->getName()) . '_enum';
// 				$preSQL[] = "DROP TYPE IF EXISTS \"{$enuName}\"";
// 				$preSQL[] = "CREATE TYPE \"{$enuName}\" AS ENUM ('" . implode("','", $values) . "')";
				
// 				$sql .= ' "' . $enuName . '"';
// 				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 			break;
// 		}
// 	}

// 	public static function createTableSQL(DB $db, TableInfo $info, $schema = null) {
// 		$preSQL = array();
// 		$postSQL = array();
// 		if(is_null($schema))
// 			$sql = 'CREATE TABLE ' . $db->escapeTableName($info->getName()) . " (\n";
// 		else
// 			$sql = 'CREATE TABLE ' . $db->escapeTableName($schema) . '.' . $db->escapeTableName($info->getName()) . " (\n";
// 		$next = false;
		
// 		$primary = $info->getPrimary();
		
// 		for($i = 0; $i < $info->getColumnCount(); $i++) {
// 			$column = $info->getColumnInfo($i);
			
// 			if($next)
// 				$sql .= ",\n";
// 			$sql .= "\t" . $db->escapeColumnName($column->getName());
// 			switch($column->getType()) {
				
// 				case ColumnInfo::TYPE_BOOLEAN:
// 					$sql .= ' BOOLEAN';
// 					$size = $column->getSize();
// 					if(!is_int($size))
// 						throw new \InvalidArgumentException("size has to be a integer for type boolean");
// 				break;
				
// 				case ColumnInfo::TYPE_TINY:
// 					$sql .= ' SMALLINT'; // pgsql has no tinyint type
// 					$size = $column->getSize();
// 					if(!is_int($size))
// 						throw new \InvalidArgumentException("size has to be a integer for type tiny");
// 					$size = (int) $size;
// 					if($size > 4)
// 						throw new \OverflowException("size bigger than maximum size");
// 					if($size > 0)
// 						$sql .= '(' . $size . ')';
						
// 						// add info that this is a tinyint column
// 					$postSQL[] = 'COMMENT ON COLUMN "' . $info->getName() . '"."' . $column->getName() . '" IS \'tiny\'';
					
// 					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
					
// 					if($column->isAutoNum()) {
// 						if(!in_array($column->getName(), $primary))
// 							throw new \InvalidArgumentException("serial fields needs to be in the primary key");
						
// 						$result = self::genNumSequence($info->getName(), $column->getName());
// 						$preSQL[] = $result['pre'];
// 						$sql .= ' DEFAULT nextval(\'"' . $result['name'] . '"\')';
// 						$postSQL[] = $result['post'];
// 					}
// 				break;
				
// 				case ColumnInfo::TYPE_SHORT:
// 					$sql .= ' SMALLINT';
// 					$size = $column->getSize();
// 					if(!is_int($size))
// 						throw new \InvalidArgumentException("size has to be a integer for type short");
// 					$size = (int) $size;
// 					if($size > 6)
// 						throw new \OverflowException("size bigger than maximum size");
// 					if($size > 0)
// 						$sql .= '(' . $size . ')';
						
// 						// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
					

// 					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 					if($column->isAutoNum()) {
// 						if(!in_array($column->getName(), $primary))
// 							throw new \InvalidArgumentException("serial fields needs to be in the primary key");
						
// 						$result = self::genNumSequence($info->getName(), $column->getName());
// 						$preSQL[] = $result['pre'];
// 						$sql .= ' DEFAULT nextval(\'"' . $result['name'] . '"\')';
// 						$postSQL[] = $result['post'];
// 					}
// 				break;
				
// 				case ColumnInfo::TYPE_INT:
// 					$sql .= ' INTEGER';
// 					$size = $column->getSize();
// 					if(!is_int($size))
// 						throw new \InvalidArgumentException("size has to be a integer for type short");
// 					$size = (int) $size;
// 					if($size > 11)
// 						throw new \OverflowException("size bigger than maximum size");
// 					if($size > 0)
// 						$sql .= '(' . $size . ')';
						
// 						// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
					

// 					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 					if($column->isAutoNum()) {
// 						if(!in_array($column->getName(), $primary))
// 							throw new \InvalidArgumentException("serial fields needs to be in the primary key");
						
// 						$result = self::genNumSequence($info->getName(), $column->getName());
// 						$preSQL[] = $result['pre'];
// 						$sql .= ' DEFAULT nextval(\'"' . $result['name'] . '"\')';
// 						$postSQL[] = $result['post'];
// 					}
// 				break;
				
// 				case ColumnInfo::TYPE_LONG:
// 					$sql .= ' BIGINT';
// 					$size = $column->getSize();
// 					if(!is_int($size))
// 						throw new \InvalidArgumentException("size has to be a integer for type short");
// 					$size = (int) $size;
// 					if($size > 0)
// 						$sql .= '(' . $size . ')';
						
// 						// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
					

// 					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 					if($column->isAutoNum()) {
// 						if(!in_array($column->getName(), $primary))
// 							throw new \InvalidArgumentException("serial fields needs to be in the primary key");
						
// 						$result = self::genNumSequence($info->getName(), $column->getName());
// 						$preSQL[] = $result['pre'];
// 						$sql .= ' DEFAULT nextval(\'"' . $result['name'] . '"\')';
// 						$postSQL[] = $result['post'];
// 					}
// 				break;
				
// 				case ColumnInfo::TYPE_FLOAT:
// 					$sql .= ' REAL';
// 					$size = $column->getSize();
// 					if(!is_int($size))
// 						throw new \InvalidArgumentException("precicion has to be a integer for type real");
						
// 						// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
// 					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 				break;
				
// 				case ColumnInfo::TYPE_DOUBLE:
// 					$sql .= ' DOUBLE PRECISION';
// 					$size = $column->getSize();
// 					if(!is_int($size))
// 						throw new \InvalidArgumentException("precicion has to be a integer for type double");
						
// 						// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
// 					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 				break;
				
// 				case ColumnInfo::TYPE_DECIMAL:
// 					$sql .= ' DECIMAL';
// 					$size = $column->getSize();
// 					if(!is_array($size))
// 						throw new \InvalidArgumentException("size has to be an array for type decimal");
// 					if(count($size) != 2)
// 						throw new \RangeException("size array has to containt 2 integers");
// 					reset($size);
// 					$m = (int) current($size);
// 					next($size);
// 					$d = (int) current($size);
// 					if($m < 0 || $d < 0)
// 						throw new \UnexpectedValueException("integers of the size array needs to be positive");
					
// 					$sql .= '(' . $m . ',' . $d . ')';
// 					// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
// 					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 				break;
				
// 				case ColumnInfo::TYPE_CHAR:
// 					$sql .= ' CHAR';
// 					$size = $column->getSize();
// 					if(!is_int($size))
// 						throw new \InvalidArgumentException("size has to be a integer for type char");
					
// 					$size = (int) $size;
// 					if($size > 0)
// 						$sql .= '(' . $size . ')';
						
// 						//	$sql .= ' '.self::createCollateSQL($column->getCharset(), $column->getCollate());
// 					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 				break;
				
// 				case ColumnInfo::TYPE_VCHAR:
// 					$sql .= ' VARCHAR';
// 					$size = $column->getSize();
// 					if(!is_int($size))
// 						throw new \InvalidArgumentException("size has to be a integer for type vchar");
					
// 					$size = (int) $size;
// 					if($size > 0)
// 						$sql .= '(' . $size . ')';
						
// 						//	$sql .= ' '.self::createCollateSQL($column->getCharset(), $column->getCollate());
// 					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 				break;
				
// 				case ColumnInfo::TYPE_TEXT:
// 					$sql .= ' TEXT';
					
// 					//	$sql .= ' '.self::createCollateSQL($column->getCharset(), $column->getCollate());
// 					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 				break;
				
// 				case ColumnInfo::TYPE_BINARY:
// 					$sql .= ' BYTEA';
// 					// $size = $column->getSize();
// 					//if (!is_int($size))
// 					//	throw new \InvalidArgumentException("size has to be a integer for type binary");
// 					// $size = (int) $size;
// 					//if ($size > 0)
// 					//	$sql .= '('.$size.')'; 
// 					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 				break;
				
// 				case ColumnInfo::TYPE_BLOB:
// 					$sql .= ' BYTEA';
// 					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 				break;
				
// 				case ColumnInfo::TYPE_DATE:
// 					$sql .= ' DATE';
// 					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 				break;
				
// 				case ColumnInfo::TYPE_DATETIME:
// 					$sql .= ' TIMESTAMP';
// 					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 				break;
				
// 				case ColumnInfo::TYPE_UTS:
// 					$sql .= ' INT';
// 					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 				break;
				
// 				case ColumnInfo::TYPE_ENUM:
// 					$values = array();
// 					foreach($column->getParam() as $e) {
// 						if(preg_match('#values\(([a-zA-Z0-9_,]*)\)#', $e, $match)) {
// 							$values = explode(',', $match[1]);
// 							break;
// 						}
// 					}
// 					$enuName = $info->getName() . '_' . strtolower($column->getName()) . '_enum';
// 					$preSQL[] = "DROP TYPE IF EXISTS \"{$enuName}\"";
// 					$preSQL[] = "CREATE TYPE \"{$enuName}\" AS ENUM ('" . implode("','", $values) . "')";
					
// 					$sql .= ' "' . $enuName . '"';
// 					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
// 				break;
// 			}
			
// 			if(!is_null($column->getDefault()))
// 				$sql .= ' DEFAULT ' . $db->escapeValue($column->getDefault());
				
// 				//if (in_array($column->getName(), $primary))
// 				//	$sql .= ' PRIMARY KEY';
			

// 			$next = true;
// 		}
// 		$prefix = '';
// 		$postfix = '';
// 		if(count($primary) != 0) {
// 			$sql .= ', PRIMARY KEY(' . implode(',', array_map(array($db,'escapeColumnName'), $primary)) . ')';
// 		}
// 		$sql .= "\n)";
// 		foreach($preSQL as $q)
// 			$prefix .= $q . ";\n";
// 		foreach($postSQL as $q)
// 			$postfix .= $q . ";\n";
// 		$sql = $prefix . $sql . ";\n" . $postfix;
// 		return $sql;
// 	}

	/**
	 * creates an array of sql statements to alter the table definition to match the new
	 * column info
	 *
	 * @param MFDB $db
	 *        	database object
	 * @param string $table
	 *        	table which contains the column
	 * @param ColumnInfo $colInfo
	 *        	the new column info
	 * @return an array with the keys 'pre', 'main' and 'post', which contain all the
	 *         neccesary sql statements
	 */
	public static function createAddColumnSQLs(DB $db, $table, ColumnInfo $colInfo) {
		$preSQL = array();
		$mainSQL = array();
		$postSQL = array();
		
		$tableInfo = $db->table($table)->getInfo();
		
		// echo "New info:\n";
		// var_dump($colInfo);
		$oldColName = false;
		
		$sqlbase = "ALTER TABLE " . $db->escapeTableName($table) . " ADD COLUMN " . $colInfo->getName();
		
		switch($colInfo->getType()) {
			case ColumnInfo::TYPE_BOOLEAN:
				$mainSQL[] = $sqlbase . ' BOOLEAN ' . ($colInfo->isNullAllowed() ? ' NULL' : ' NOT NULL');
			break;
			
			case ColumnInfo::TYPE_TINY:
				$mainSQL[] = $sqlbase . ' SMALLINT ' . ($colInfo->isNullAllowed() ? ' NULL' : ' NOT NULL'); // pgsql has no tinyint type
				

				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type tiny");
				$size = (int) $size;
				if($size > 4)
					throw new \OverflowException("size bigger than maximum size");
					
					// add info that this is a tinyint column
				$postSQL[] = 'COMMENT ON COLUMN "' . $table . '"."' . $colInfo->getName() . '" IS \'tiny\'';
				
				if($colInfo->isAutoNum()) {
					if(!in_array($colInfo->getName(), $primary))
						throw new \InvalidArgumentException("serial fields needs to be in the primary key");
					
					$result = self::genNumSequence($info->getName(), $column->getName());
					$preSQL[] = $result['pre'];
					$sql .= ' DEFAULT nextval(\'"' . $result['name'] . '"\')';
					$postSQL[] = $result['post'];
				}
			break;
			
			case ColumnInfo::TYPE_SHORT:
				$mainSQL[] = $sqlbase . ' SMALLINT ' . ($colInfo->isNullAllowed() ? ' NULL' : ' NOT NULL');
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type tiny");
				$size = (int) $size;
				if($size > 6)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
				$sql .= ';';
				
				// TODO: remove this comment:
				//$postSQL[] = 'COMMENT ON COLUMN "'.$info->getName().'"."'.$colInfo->getName().'" IS \'tiny\'';
				

				$sql .= $sqlbase . ($colInfo->isNullAllowed() ? ' DROP' : ' SET') . ' NOT NULL;';
				if($colInfo->isAutoNum()) {
					if(!in_array($colInfo->getName(), $primary))
						throw new \InvalidArgumentException("serial fields needs to be in the primary key");
					
					$result = self::genNumSequence($info->getName(), $column->getName());
					$preSQL[] = $result['pre'];
					$sql .= ' DEFAULT nextval(\'"' . $result['name'] . '"\')';
					$postSQL[] = $result['post'];
				}
			
			break;
			
			case ColumnInfo::TYPE_INT:
				$mainSQL[] = $sqlbase . ' INTEGER ' . ($colInfo->isNullAllowed() ? ' NULL' : ' NOT NULL');
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type tiny");
				$size = (int) $size;
				if($size > 11)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
					// $sql .= ';';
					

				// TODO: remove this comment:
					//$postSQL[] = 'COMMENT ON COLUMN "'.$info->getName().'"."'.$colInfo->getName().'" IS \'tiny\'';
				

				if($colInfo->isAutoNum()) {
					if(!in_array($colInfo->getName(), $primary))
						throw new \InvalidArgumentException("serial fields needs to be in the primary key");
					
					$result = self::genNumSequence($info->getName(), $column->getName());
					$preSQL[] = $result['pre'];
					$sql .= ' DEFAULT nextval(\'"' . $result['name'] . '"\')';
					$postSQL[] = $result['post'];
				}
			break;
			
			case ColumnInfo::TYPE_LONG:
				$mainSQL[] = $sqlbase . ' BIGINT ' . ($colInfo->isNullAllowed() ? ' NULL' : ' NOT NULL');
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type tiny");
				$size = (int) $size;
				if($size > 13)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
				$sql .= ';';
				
				// TODO: remove this comment:
				//$postSQL[] = 'COMMENT ON COLUMN "'.$info->getName().'"."'.$colInfo->getName().'" IS \'tiny\'';
				

				$sql .= $sqlbase . ($colInfo->isNullAllowed() ? ' DROP' : ' SET') . ' NOT NULL;';
				if($colInfo->isAutoNum()) {
					if(!in_array($colInfo->getName(), $primary))
						throw new \InvalidArgumentException("serial fields needs to be in the primary key");
					
					$result = self::genNumSequence($info->getName(), $column->getName());
					$preSQL[] = $result['pre'];
					$sql .= ' DEFAULT nextval(\'"' . $result['name'] . '"\')';
					$postSQL[] = $result['post'];
				}
			break;
			
			case ColumnInfo::TYPE_FLOAT:
				$mainSQL[] = $sqlbase . ' REAL ' . ($colInfo->isNullAllowed() ? ' NULL' : ' NOT NULL');
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("precicion has to be a integer for type real");
			break;
			
			case ColumnInfo::TYPE_DOUBLE:
				die('not implemented');
				$mainSQL[] = $sqlbase . ' TYPE DOUBLE PRECISION';
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("precicion has to be a integer for type double");
			break;
			
			case ColumnInfo::TYPE_DECIMAL:
				die('not implemented');
				$mainSQL[] = $sqlbase . ' DECIMAL ' . ($colInfo->isNullAllowed() ? ' NULL' : ' NOT NULL');
				$size = $colInfo->getSize();
				if(!is_array($size))
					throw new \InvalidArgumentException("size has to be an array for type decimal");
				if(count($size) != 2)
					throw new \RangeException("size array has to containt 2 integers");
				reset($size);
				$m = (int) current($size);
				next($size);
				$d = (int) current($size);
				if($m < 0 || $d < 0)
					throw new \UnexpectedValueException("integers of the size array needs to be positive");
				
				$sql .= '(' . $m . ',' . $d . ')';
			break;
			
			case ColumnInfo::TYPE_CHAR:
				$sql = $sqlbase . ' CHAR';
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type tiny");
				$size = (int) $size;
				if($size > 255)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
				$mainSQL[] = $sql . ($colInfo->isNullAllowed() ? ' NULL' : ' NOT NULL');
			break;
			
			case ColumnInfo::TYPE_VCHAR:
				$sql = $sqlbase . ' VARCHAR';
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type tiny");
				$size = (int) $size;
				if($size > 255)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
				$mainSQL[] = $sql . ($colInfo->isNullAllowed() ? ' NULL' : ' NOT NULL');
			break;
			
			case ColumnInfo::TYPE_TEXT:
				$mainSQL[] = $sqlbase . ' TEXT ' . ($colInfo->isNullAllowed() ? ' NULL' : ' NOT NULL');
			break;
			
			case ColumnInfo::TYPE_BINARY:
				$mainSQL[] = $sqlbase . ' BYTEA ' . ($colInfo->isNullAllowed() ? ' NULL' : ' NOT NULL');
			break;
			
			case ColumnInfo::TYPE_BLOB:
				$mainSQL[] = $sqlbase . ' BYTEA ' . ($colInfo->isNullAllowed() ? ' NULL' : ' NOT NULL');
			break;
			
			case ColumnInfo::TYPE_DATE:
				$mainSQL[] = $sqlbase . ' DATE ' . ($colInfo->isNullAllowed() ? ' NULL' : ' NOT NULL');
			break;
			
			case ColumnInfo::TYPE_DATETIME:
				$mainSQL[] = $sqlbase . ' TIMESTAMP ' . ($colInfo->isNullAllowed() ? ' NULL' : ' NOT NULL');
			break;
			
			case ColumnInfo::TYPE_UTS:
				$mainSQL[] = $sqlbase . ' INT ' . ($colInfo->isNullAllowed() ? ' NULL' : ' NOT NULL');
			break;
			
			case ColumnInfo::TYPE_ENUM:
				$values = array();
				foreach($colInfo->getParam() as $e) {
					if(preg_match('#values\(([a-zA-Z0-9_,]*)\)#', $e, $match)) {
						$values = explode(',', $match[1]);
						break;
					}
				}
				$enuName = $info->getName() . '_' . strtolower($colInfo->getName()) . '_enum';
				
				if($oldColName) {
					$preSQL[] = "DROP TYPE IF EXISTS \"{$enuName}\"";
				}
				
				$preSQL[] = "DROP TYPE IF EXISTS \"{$enuName}\"";
				$preSQL[] = "CREATE TYPE \"{$enuName}\" AS ENUM ('" . implode("','", $values) . "')";
				
				$mainSQL[] = $sqlbase . ' "' . $enuName . '" ' . ($colInfo->isNullAllowed() ? ' NULL' : ' NOT NULL');
				;
			break;
		}
		
		$result = array('pre' => $preSQL,'main' => $mainSQL,'post' => $postSQL);
		return $result;
	}

	/**
	 * creates an array of sql statements to alter the table definition to match the new
	 * column info
	 *
	 * @param MFDB $db
	 *        	database object
	 * @param string $table
	 *        	table which contains the column
	 * @param string $colName
	 *        	old column name
	 * @param ColumnInfo $colInfo
	 *        	the new column info
	 * @return an array with the keys 'pre', 'main' and 'post', which contain all the
	 *         neccesary sql statements
	 */
	public static function createAlterColumnSQLs(DB $db, $table, $colName, ColumnInfo $colInfo) {
		$dia = $db->getDialect();
		
		$preSQL = array();
		$mainSQL = array();
		$postSQL = array();
		
		//echo "Old name: $colName\n";
		
		$tableInfo = $db->table($table)->getInfo();
		$oldInfo = $tableInfo->getColumnInfo($colName);
		$primary = $db->table($table)->getPKeyFields();
		
		// echo "Old info:\n";
		// var_dump($oldInfo);
		// echo "New info:\n";
		// var_dump($colInfo);
		$oldColName = false;
		
		if($colInfo->getName() != $colName) {
			$preSQL[] = "ALTER TABLE " . $db->escapeTableName($table) . " RENAME COLUMN " . $colName . ' TO ' . $colInfo->getName();
			$oldColName = $colName;
			$colName = $colInfo->getName();
		}
		
		$sqlbase = "ALTER TABLE " . $db->escapeTableName($table) . " ALTER COLUMN " . $colName;
		
		if($oldInfo->isNullAllowed() != $colInfo->isNullAllowed()) {
			$mainSQL[] = $sqlBase . ($colInfo->isNullAllowed() ? ' DROP' : ' SET') . ' NOT NULL';
		}
		
		switch($colInfo->getType()) {
			case ColumnInfo::TYPE_BOOLEAN:
				$mainSQL[] = $sqlbase . ' TYPE BOOLEAN';
			break;
			
			case ColumnInfo::TYPE_TINY:
				$sql = $sqlbase . ' TYPE SMALLINT'; // pgsql has no tinyint type
				
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type tiny");
				$size = (int) $size;
				if($size > 4)
					throw new \OverflowException("size bigger than maximum size");
					
				// add info that this is a tinyint column
				$postSQL[] = 'COMMENT ON COLUMN "' . $tableInfo->getName() . '"."' . $colInfo->getName() . '" IS \'tiny\'';
				
				$seqName = $tableInfo->getName() . '_' . strtolower($colInfo->getName()) . '_seq';
				if($colInfo->isAutoNum() && !$oldInfo->isAutoNum()) {
					if(!in_array($colInfo->getName(), $primary))
						throw new \InvalidArgumentException("serial fields needs to be in the primary key");
					$seqEx = $db->getObject("SELECT COUNT(*) AS c FROM pg_class c WHERE c.relkind = 'S' AND c.relname=".$dia->quoteValue($seqName));
					if ($seqEx->c == 0)
						$preSQL[] = 'CREATE SEQUENCE "' . $seqName . '"';
					$sql .= $sqlbase .' SET DEFAULT nextval(\'"' . $seqName . '"\')';
					$postSQL[] = 'ALTER SEQUENCE "' . strtolower($seqName) . '" OWNED BY "' . $tableInfo->getName() . '"."' . $colInfo->getName() . '"';
				}
				elseif(!$colInfo->isAutoNum() && $oldInfo->isAutoNum()) {
					$sql .= $sqlbase . ' DROP DEFAULT;';
					$sql .= 'DROP SEQUENCE "' . $seqName . '";';
				}
				$mainSQL[] = $sql;
			break;
			
			case ColumnInfo::TYPE_SHORT:
				$sql = $sqlbase . ' TYPE SMALLINT';
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type tiny");
				$size = (int) $size;
				if($size > 6)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
				$sql .= ';';
				
				// TODO: remove this comment:
				//$postSQL[] = 'COMMENT ON COLUMN "'.$tableInfo->getName().'"."'.$colInfo->getName().'" IS \'tiny\'';
				

				$sql .= $sqlbase . ($colInfo->isNullAllowed() ? ' DROP' : ' SET') . ' NOT NULL;';
				$seqName = $tableInfo->getName() . '_' . strtolower($colInfo->getName()) . '_seq';
				if($colInfo->isAutoNum() && !$oldInfo->isAutoNum()) {
					if(!in_array($colInfo->getName(), $primary))
						throw new \InvalidArgumentException("serial fields needs to be in the primary key");
					
					$seqEx = $db->getObject("SELECT COUNT(*) AS c FROM pg_class c WHERE c.relkind = 'S' AND c.relname=".$dia->quoteValue($seqName));
					if ($seqEx->c == 0)
						$preSQL[] = 'CREATE SEQUENCE "' . $seqName . '"';
					$sql .= $sqlbase .' SET DEFAULT nextval(\'"' . $seqName . '"\')';
					$postSQL[] = 'ALTER SEQUENCE "' . strtolower($seqName) . '" OWNED BY "' . $tableInfo->getName() . '"."' . $colInfo->getName() . '"';
				}
				elseif(!$colInfo->isAutoNum() && $oldInfo->isAutoNum()) {
					$sql .= $sqlbase . ' DROP DEFAULT;';
					$sql .= 'DROP SEQUENCE "' . $seqName . '";';
				}
				$mainSQL[] = $sql;
			break;
			
			case ColumnInfo::TYPE_INT:
				$sql = $sqlbase . ' TYPE INTEGER';
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type tiny");
				$size = (int) $size;
				if($size > 11)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
				$sql .= ';';
				
				// TODO: remove this comment:
				//$postSQL[] = 'COMMENT ON COLUMN "'.$tableInfo->getName().'"."'.$colInfo->getName().'" IS \'tiny\'';
				

				$sql .= $sqlbase . ($colInfo->isNullAllowed() ? ' DROP' : ' SET') . ' NOT NULL;';
				$seqName = $tableInfo->getName() . '_' . strtolower($colInfo->getName()) . '_seq';
				if($colInfo->isAutoNum() && !$oldInfo->isAutoNum()) {
					if(!in_array($colInfo->getName(), $primary))
						throw new \InvalidArgumentException("serial fields needs to be in the primary key");
					
					$seqEx = $db->getObject("SELECT COUNT(*) AS c FROM pg_class c WHERE c.relkind = 'S' AND c.relname=".$dia->quoteValue($seqName));
					if ($seqEx->c == 0)
						$preSQL[] = 'CREATE SEQUENCE "' . $seqName . '"';
					$sql .=$sqlbase .' SET DEFAULT nextval(\'"' . $seqName . '"\')';
					$postSQL[] = 'ALTER SEQUENCE "' . strtolower($seqName) . '" OWNED BY "' . $tableInfo->getName() . '"."' . $colInfo->getName() . '"';
				}
				elseif(!$colInfo->isAutoNum() && $oldInfo->isAutoNum()) {
					$sql .= $sqlbase . ' DROP DEFAULT;';
					$sql .= 'DROP SEQUENCE "' . $seqName . '";';
				}
				$mainSQL[] = $sql;
			break;
			
			case ColumnInfo::TYPE_LONG:
				$sql = $sqlbase . ' TYPE BIGINT';
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type tiny");
				$size = (int) $size;
				if($size > 13)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
				$sql .= ';';
				
				// TODO: remove this comment:
				//$postSQL[] = 'COMMENT ON COLUMN "'.$tableInfo->getName().'"."'.$colInfo->getName().'" IS \'tiny\'';
				

				$sql .= $sqlbase . ($colInfo->isNullAllowed() ? ' DROP' : ' SET') . ' NOT NULL;';
				$seqName = $table . '_' . strtolower($colInfo->getName()) . '_seq';
				if($colInfo->isAutoNum() && !$oldInfo->isAutoNum()) {
					if(!in_array($colInfo->getName(), $primary))
						throw new \InvalidArgumentException("serial fields needs to be in the primary key");
					
					$seqEx = $db->getObject("SELECT COUNT(*) AS c FROM pg_class c WHERE c.relkind = 'S' AND c.relname=".$dia->quoteValue($seqName));
					if ($seqEx->c == 0)
						$preSQL[] = 'CREATE SEQUENCE "' . $seqName . '"';
					$sql .= $sqlbase .' SET DEFAULT nextval(\'"' . $seqName . '"\')';
					$postSQL[] = 'ALTER SEQUENCE "' . strtolower($seqName) . '" OWNED BY "' . $table . '"."' . $colInfo->getName() . '"';
				}
				elseif(!$colInfo->isAutoNum() && $oldInfo->isAutoNum()) {
					$sql .= $sqlbase . ' DROP DEFAULT;';
					$sql .= 'DROP SEQUENCE "' . $seqName . '";';
				}
				$mainSQL[] = $sql;
			break;
			
			case ColumnInfo::TYPE_FLOAT:
				$mainSQL[] = $sqlbase . ' TYPE REAL';
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("precicion has to be a integer for type real");
			break;
			
			case ColumnInfo::TYPE_DOUBLE:
				die('not implemented');
				$mainSQL[] = $sqlbase . ' TYPE DOUBLE PRECISION';
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("precicion has to be a integer for type double");
			break;
			
			case ColumnInfo::TYPE_DECIMAL:
				die('not implemented');
				$mainSQL[] = $sqlbase . ' TYPE DECIMAL';
				$size = $colInfo->getSize();
				if(!is_array($size))
					throw new \InvalidArgumentException("size has to be an array for type decimal");
				if(count($size) != 2)
					throw new \RangeException("size array has to containt 2 integers");
				reset($size);
				$m = (int) current($size);
				next($size);
				$d = (int) current($size);
				if($m < 0 || $d < 0)
					throw new \UnexpectedValueException("integers of the size array needs to be positive");
				
				$sql .= '(' . $m . ',' . $d . ')';
			break;
			
			case ColumnInfo::TYPE_CHAR:
				$sql = $sqlbase . ' TYPE CHAR';
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type tiny");
				$size = (int) $size;
				if($size > 255)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
				$mainSQL[] = $sql;
			break;
			
			case ColumnInfo::TYPE_VCHAR:
				$sql = $sqlbase . ' TYPE VARCHAR';
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type tiny");
				$size = (int) $size;
				if($size > 255)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
				$mainSQL[] = $sql;
			break;
			
			case ColumnInfo::TYPE_TEXT:
				$mainSQL[] = $sqlbase . ' TYPE TEXT';
			break;
			
			case ColumnInfo::TYPE_BINARY:
				$mainSQL[] = $sqlbase . ' TYPE BYTEA';
			break;
			
			case ColumnInfo::TYPE_BLOB:
				$mainSQL[] = $sqlbase . ' TYPE BYTEA';
			break;
			
			case ColumnInfo::TYPE_DATE:
				$mainSQL[] = $sqlbase . ' TYPE DATE';
			break;
			
			case ColumnInfo::TYPE_DATETIME:
				$mainSQL[] = $sqlbase . ' TYPE TIMESTAMP';
			break;
			
			case ColumnInfo::TYPE_UTS:
				$mainSQL[] = $sqlbase . ' TYPE INT';
			break;
			
			case ColumnInfo::TYPE_ENUM:
				die('not implemented');
				$values = array();
				foreach($colInfo->getParam() as $e) {
					if(preg_match('#values\(([a-zA-Z0-9_,]*)\)#', $e, $match)) {
						$values = explode(',', $match[1]);
						break;
					}
				}
				$enuName = $tableInfo->getName() . '_' . strtolower($colInfo->getName()) . '_enum';
				
				if($oldColName)
					$preSQL[] = "DROP TYPE IF EXISTS \"{$enuName}\"";
				
				$preSQL[] = "DROP TYPE IF EXISTS \"{$enuName}\"";
				$preSQL[] = "CREATE TYPE \"{$enuName}\" AS ENUM ('" . implode("','", $values) . "')";
				
				$sql .= ' "' . $enuName . '"';
			break;
		}

//			if (!is_null($colInfo->getDefault()))
//				$sql .= ' DEFAULT '.$db->escapeValue($colInfo->getDefault());
		

		//if (in_array($colInfo->getName(), $primary))
		//	$sql .= ' PRIMARY KEY';
		$result = array('pre' => $preSQL,'main' => $mainSQL,'post' => $postSQL);
		return $result;
	}
}