mssql: fix drop/alter column with existing default constraint

With MSSQL it is not possible to drop or alter a column with an
existing constraint. The constraint has to be removed before the
operation takes place.

In 'datadict-mssqlnative.inc.php' AlterColumnSQL is commented out and
DropColumnSQL doesn't care.

This tries to fix the problem with the smallest possible impact:

- Fix DropColumnSQL(), to allow the drop even with a constraint on that
  given column. We drop, so any default doesn't matter.
- Fix AlterColumnSql(), to allow changes if (and only if !) either a
  'new' default is given for an existing default, or there is no
  existing one at all. So something like
  'ALTER TABLE t ALTER COLUMN c INT NOT NULL'
  with an existing constraint on c will still fail since it can't be
  determined if keeping or removing the constraint is implied here.

Fixes #290 via #297

Changes to original commit:
- split long lines, whitespace
- Added commit message text from issue #290's description

Signed-off-by: Damien Regad <dregad@mantisbt.org>

1 files changed, 61 insertions, 9 deletions

diff --git a/datadict/datadict-mssqlnative.inc.php b/datadict/datadict-mssqlnative.inc.php
index 8c884047..6725e03d 100644
--- a/datadict/datadict-mssqlnative.inc.php
+++ b/datadict/datadict-mssqlnative.inc.php
@@ -146,19 +146,69 @@ class ADODB2_mssqlnative extends ADODB_DataDict {
 		return $sql;
 	}
 
-	/*
-	function AlterColumnSQL($tabname, $flds, $tableflds='', $tableoptions='')
+	function DefaultConstraintname($tabname, $colname)
+	{
+		$constraintname = false;
+		$rs = $this->connection->Execute(
+			"SELECT name FROM sys.default_constraints
+			WHERE object_name(parent_object_id) = '$tabname'
+			AND col_name(parent_object_id, parent_column_id) = '$colname'"
+		);
+		if ( is_object($rs) ) {
+			$row = $rs->FetchRow();
+			$constraintname = $row['name'];
+		}
+		return $constraintname;
+	}
+  
+	function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 	{
 		$tabname = $this->TableName ($tabname);
 		$sql = array();
-		list($lines,$pkey) = $this->_GenFields($flds);
+
+		list($lines,$pkey,$idxs) = $this->_GenFields($flds);
+		$alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
 		foreach($lines as $v) {
-			$sql[] = "ALTER TABLE $tabname $this->alterCol $v";
+			$not_null = false;
+			if ($not_null = preg_match('/NOT NULL/i',$v)) {
+				$v = preg_replace('/NOT NULL/i','',$v);
+			}
+			if (preg_match('/^([^ ]+) .*DEFAULT (\'[^\']+\'|\"[^\"]+\"|[^ ]+)/',$v,$matches)) {
+				list(,$colname,$default) = $matches;
+				$v = preg_replace('/^' . preg_quote($colname) . '\s/', '', $v);
+				$t = trim(str_replace('DEFAULT '.$default,'',$v));
+				if ( $constraintname = $this->DefaultConstraintname($tabname,$colname) ) {
+					$sql[] = 'ALTER TABLE '.$tabname.' DROP CONSTRAINT '. $constraintname;
+				}
+				if ($not_null) {
+					$sql[] = $alter . $colname . ' ' . $t  . ' NOT NULL';
+				} else {
+					$sql[] = $alter . $colname . ' ' . $t ;
+				}
+				$sql[] = 'ALTER TABLE ' . $tabname
+					. ' ADD CONSTRAINT DF__' . $tabname . '__' .  $colname .  '__' . dechex(rand())
+					. ' DEFAULT ' . $default . ' FOR ' . $colname;
+			} else {
+				$colname = strtok($v," ");
+				if ( $constraintname = $this->DefaultConstraintname($tabname,$colname) ) {
+					$sql[] = 'ALTER TABLE '.$tabname.' DROP CONSTRAINT '. $constraintname;
+				}
+				if ($not_null) {
+					$sql[] = $alter . $v  . ' NOT NULL';
+				} else {
+					$sql[] = $alter . $v;
+				}
+			}
+		}
+		if (is_array($idxs)) {
+			foreach($idxs as $idx => $idxdef) {
+				$sql_idxs = $this->CreateIndexSql($idx, $tabname, $idxdef['cols'], $idxdef['opts']);
+				$sql = array_merge($sql, $sql_idxs);
+			}
 		}
-
 		return $sql;
 	}
-	*/
+
 
 	/**
 	 * Drop a column, syntax is ALTER TABLE table DROP COLUMN column,column
@@ -176,10 +226,12 @@ class ADODB2_mssqlnative extends ADODB_DataDict {
 		if (!is_array($flds))
 			$flds = explode(',',$flds);
 		$f = array();
-		$s = 'ALTER TABLE ' . $tabname . ' DROP COLUMN ';
+		$s = 'ALTER TABLE ' . $tabname;
 		foreach($flds as $v) {
-			//$f[] = "\n$this->dropCol ".$this->NameQuote($v);
-			$f[] = $this->NameQuote($v);
+			if ( $constraintname = $this->DefaultConstraintname($tabname,$v) ) {
+				$sql[] = 'ALTER TABLE ' . $tabname . ' DROP CONSTRAINT ' . $constraintname;
+			}
+			$f[] = ' DROP COLUMN ' . $this->NameQuote($v);
 		}
 		$s .= implode(', ',$f);
 		$sql[] = $s;