View Issue Details

IDProjectCategoryView StatusLast Update
0011524mantisbtdb mssqlpublic2016-11-09 07:54
Reportermaximaxi Assigned Todregad  
PriorityimmediateSeverityblockReproducibilityalways
Status closedResolutionfixed 
PlatformWindows 7OSWindows 7OS VersionWindows 7
Product Version1.2.0rc2 
Target Version1.3.0-beta.2Fixed in Version1.3.0-beta.2 
Summary0011524: 1.2.0rc2 with MS SQL not installable
Description

I can't get it to work.

With "Print SQL Queries instead of Writing to the Database" checked, I get some curious WARNINGS inside the sql statements:

SYSTEM WARNING: htmlentities() expects parameter 1 to be string, array given
;

One line above each of these warnings (they appear 14 times) are commands I've never seen before:

date_migrate;

After deleting these warnings from the sql script I tried to run it but it seems that MS SQL couldn't interprete these _migrate commands (date_migrate & category_migrate)?


After removing BOTH (_migrate commands & warning messages) the script stops at various ALTER TABLE commands the first one is this:

ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay INT;

The error message looks like this:

Meldung 5074, Ebene 16, Status 1, Zeile 425
Das Objekt-Objekt 'DF__mantis_us__redir__2739D489' ist vom Spalte-Objekt  'redirect_delay' abhängig.

Think it has something to do with bound private keys, constraints, ...

Can anyone help me?

Has anybody already installed 1.2.0rc2 with MS SQL Server 2005?

Steps To Reproduce

Simply try to run admin/install.php with MS SQL Server 2005.

TagsNo tags attached.
Attached Files
sql_errors.png (341,590 bytes)
alter_table_sql.txt (1,881 bytes)   
	function AlterColumnSQL($tabname, $flds)
	{
		$tabname = $this->TableName($tabname);
		$sql = array();
		list($lines,$pkey) = $this->_GenFields($flds);

		foreach($lines as $v) {

			// get field name and type
			$arTmp = array();
			$arTmp = split(" ", $v);
			$arCnt = count($arTmp);
			$fieldname = $arTmp[0];
			$fieldtype = $arTmp[1];

			$bHasDef = false;
			$bIsNull = true;
			
			for($i = 2; $i < $arCnt; $i++) {
				// get default value
				if( trim($arTmp[$i]) == "DEFAULT" ) 
				{
					$bHasDef = true;
					if( $i+1 <= $arCnt ) {
						$field_def = $arTmp[$i+1];
					}
				}
				
				// get nullability
				if( trim($arTmp[$i]) == "NOTNULL" )
					$bIsNull = false;
			}
			


			// delete default value if exists
			if( $this->connection->IsConnected() )
			{
				$oRs = $this->connection->Execute('

							SELECT name FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid
							WHERE object_name(so.parent_obj) = \'' . $tabname . '\'
							AND so.xtype = \'D\'
							AND sc.colid = (
								SELECT colid FROM syscolumns
								WHERE id = object_id(\'' . $tabname . '\') AND name = \'' . $fieldname . '\'
							)
				');
				if( $oRs )
					if(!$oRs->EOF) 
					{
						$defName = $oRs->fields[0];
						// Remove default value
						$sql[] = "ALTER TABLE $tabname DROP CONSTRAINT $defName";
					}
				
				$oRs->Close();
			};


			
			if($bHasDef) {
				// change type and nulls
				if($bIsNull)
					$sql[] = "ALTER TABLE $tabname $this->alterCol $fieldname $fieldtype NOT NULL";
				else
					$sql[] = "ALTER TABLE $tabname$this->alterCol $fieldname $fieldtype";
				// Add default value
				$sql[] = "ALTER TABLE $tabname ADD CONSTRAINT def_" . $tabname . "_" . $fieldname . " DEFAULT " . $field_def . " FOR $fieldname";
			}
			else {
				$sql[] = "ALTER TABLE $tabname $this->alterCol $v";
			}
		}		
		//print_r($sql);

		return $sql;
	}
alter_table_sql.txt (1,881 bytes)   
adodb.txt (3,021 bytes)   
// Add this as last function in datadict-mssqlnative.inc.php or datadict-mssql.inc.php

	function DropDefaultValue($tabname, $fieldname)
	{
		$strSql = "";
		
		// delete default value if exists
		if( $this->connection->IsConnected() )
		{
			$oRs = $this->connection->Execute('

						SELECT name FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid
						WHERE object_name(so.parent_obj) = \'' . $tabname . '\'
						AND so.xtype = \'D\'
						AND sc.colid = (
							SELECT colid FROM syscolumns
							WHERE id = object_id(\'' . $tabname . '\') AND name = \'' . $fieldname . '\'
						)
			');
			if( $oRs )
				if(!$oRs->EOF) 
				{
					$defName = $oRs->fields[0];
					// Remove default value
					$strSql = "ALTER TABLE $tabname DROP CONSTRAINT $defName";
				}
			
			$oRs->Close();
		};
		//echo "<br>DropDefaultValue: " . $strSql . "<br>";
		
		return $strSql;
	}


// Change those methods

	function AlterColumnSQL($tabname, $flds)
	{
		$tabname = $this->TableName($tabname);
		$sql = array();
		list($lines,$pkey) = $this->_GenFields($flds);

		foreach($lines as $v) {

			// get field name and type
			$arTmp = array();
			$arTmp = preg_split("/[\s]+/", $v);
			$arCnt = count($arTmp);
			$fieldname = $arTmp[0];
			$fieldtype = $arTmp[1];

			$bHasDef = false;
			$bIsNull = true;
			
			for($i = 2; $i < $arCnt; $i++) {
				// get default value
				if( trim($arTmp[$i]) == "DEFAULT" ) 
				{
					$bHasDef = true;
					if( $i+1 <= $arCnt ) {
						$field_def = $arTmp[$i+1];
					}
				}
				
				// get nullability
				if( trim($arTmp[$i]) == "NOTNULL" )
					$bIsNull = false;
			}
			

			// delete default value if exists
			$strDropDef = $this->DropDefaultValue($tabname, $fieldname);
			if( trim($strDropDef) !== "" )
				$sql[] = $strDropDef;


			if($bHasDef) {				
				// change type and nulls
				if($bIsNull)
					$sql[] = "ALTER TABLE $tabname $this->alterCol $fieldname $fieldtype NOT NULL";
				else
					$sql[] = "ALTER TABLE $tabname$this->alterCol $fieldname $fieldtype";
				// Add default value
				$sql[] = "ALTER TABLE $tabname ADD CONSTRAINT def_" . $tabname . "_" . $fieldname . " DEFAULT " . $field_def . " FOR $fieldname";
			}
			else {
				$sql[] = "ALTER TABLE $tabname $this->alterCol $v";
			}
		}		
/*		
		echo "<pre>";
		print_r($sql);
		echo "</pre><br>";
*/
		return $sql;
	}


	
	function DropColumnSQL($tabname, $flds)
	{
		$tabname = $this->TableName ($tabname);
		if (!is_array($flds))
			$flds = explode(',',$flds);
		$f = array();
		$s = 'ALTER TABLE ' . $tabname;
		foreach($flds as $v) {
			
			// delete default value if exists
			$strDropDef = $this->DropDefaultValue($tabname, $v);
			if( trim($strDropDef) !== "" )
				$sql[] = $strDropDef;

			
			$f[] = "\n$this->dropCol ".$this->NameQuote($v);
		}
		$s .= implode(', ',$f);
		$sql[] = $s;
/*	
		echo "<pre>";
		print_r($sql);
		echo "</pre><br>";
*/
		return $sql;
	}
adodb.txt (3,021 bytes)   
schema_NEW.php (28,727 bytes)   
<?php
# MantisBT - a php based bugtracking system

# MantisBT is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 2 of the License, or
# (at your option) any later version.
#
# MantisBT is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with MantisBT.  If not, see <http://www.gnu.org/licenses/>.

# Each entry below defines the schema. The upgrade array consists of
#  two elements
# The first is the function to generate SQL statements (see adodb schema doc for more details)
#  e.g., CreateTableSQL, DropTableSQL, ChangeTableSQL, RenameTableSQL, RenameColumnSQL,
#  DropTableSQL, ChangeTableSQL, RenameTableSQL, RenameColumnSQL, AlterColumnSQL, DropColumnSQL
#  A local function "InsertData" has been provided to add data to the db
# The second parameter is an array of the parameters to be passed to the function.

# An update identifier is inferred from the ordering of this table. ONLY ADD NEW CHANGES TO THE
#  END OF THE TABLE!!!

if ( !function_exists( 'db_null_date' ) ) {
	function db_null_date() {
		return 0;
	}
}


function installer_db_now() {
        global $g_db;
 
       return $g_db->BindTimeStamp( time() );
}

$upgrade[] = Array('CreateTableSQL',Array(db_get_table( 'mantis_config_table' ),"
			  config_id C(64) NOTNULL PRIMARY,
			  project_id I DEFAULT '0' PRIMARY,
			  user_id I DEFAULT '0' PRIMARY,
			  access_reqd I DEFAULT '0',
			  type I DEFAULT '90',
			  value XL NOTNULL",
Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateIndexSQL',Array('idx_config',db_get_table( 'mantis_config_table' ),'config_id'));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_bug_file_table'),"
  id			 I  UNSIGNED NOTNULL PRIMARY AUTOINCREMENT,
  bug_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  title 		C(250) NOTNULL DEFAULT \" '' \",
  description 		C(250) NOTNULL DEFAULT \" '' \",
  diskfile 		C(250) NOTNULL DEFAULT \" '' \",
  filename 		C(250) NOTNULL DEFAULT \" '' \",
  folder 		C(250) NOTNULL DEFAULT \" '' \",
  filesize 		 I NOTNULL DEFAULT '0',
  file_type 		C(250) NOTNULL DEFAULT \" '' \",
  date_added 		I  UNSIGNED     NOTNULL DEFAULT '1',
  content 		B NOTNULL
  ",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateIndexSQL',Array('idx_bug_file_bug_id',db_get_table('mantis_bug_file_table'),'bug_id'));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_bug_history_table'),"
  id 			 I  UNSIGNED NOTNULL PRIMARY AUTOINCREMENT,
  user_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  bug_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  date_modified 	I  UNSIGNED     NOTNULL DEFAULT '1',
  field_name 		C(32) NOTNULL DEFAULT \" '' \",
  old_value 		C(128) NOTNULL DEFAULT \" '' \",
  new_value 		C(128) NOTNULL DEFAULT \" '' \",
  type 			I2 NOTNULL DEFAULT '0'
  ",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateIndexSQL',Array('idx_bug_history_bug_id',db_get_table('mantis_bug_history_table'),'bug_id'));
$upgrade[] = Array('CreateIndexSQL',Array('idx_history_user_id',db_get_table('mantis_bug_history_table'),'user_id'));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_bug_monitor_table'),"
  user_id 		 I  UNSIGNED NOTNULL PRIMARY DEFAULT '0',
  bug_id 		 I  UNSIGNED NOTNULL PRIMARY DEFAULT '0'
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_bug_relationship_table'),"
  id 			 I  UNSIGNED NOTNULL AUTOINCREMENT PRIMARY,
  source_bug_id		 I  UNSIGNED NOTNULL DEFAULT '0',
  destination_bug_id 	 I  UNSIGNED NOTNULL DEFAULT '0',
  relationship_type 	I2 NOTNULL DEFAULT '0'
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateIndexSQL',Array('idx_relationship_source',db_get_table('mantis_bug_relationship_table'),'source_bug_id'));
/* 10 */
$upgrade[] = Array('CreateIndexSQL',Array('idx_relationship_destination',db_get_table('mantis_bug_relationship_table'),'destination_bug_id'));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_bug_table'),"
  id 			 I  UNSIGNED PRIMARY NOTNULL AUTOINCREMENT,
  project_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  reporter_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  handler_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  duplicate_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  priority 		I2 NOTNULL DEFAULT '30',
  severity 		I2 NOTNULL DEFAULT '50',
  reproducibility 	I2 NOTNULL DEFAULT '10',
  status 		I2 NOTNULL DEFAULT '10',
  resolution 		I2 NOTNULL DEFAULT '10',
  projection 		I2 NOTNULL DEFAULT '10',
  category 		C(64) NOTNULL DEFAULT \" '' \",
  date_submitted 	I  UNSIGNED     NOTNULL DEFAULT '1',
  last_updated 		I  UNSIGNED     NOTNULL DEFAULT '1',
  eta 			I2 NOTNULL DEFAULT '10',
  bug_text_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  os 			C(32) NOTNULL DEFAULT \" '' \",
  os_build 		C(32) NOTNULL DEFAULT \" '' \",
  platform 		C(32) NOTNULL DEFAULT \" '' \",
  version 		C(64) NOTNULL DEFAULT \" '' \",
  fixed_in_version 	C(64) NOTNULL DEFAULT \" '' \",
  build 		C(32) NOTNULL DEFAULT \" '' \",
  profile_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  view_state 		I2 NOTNULL DEFAULT '10',
  summary 		C(128) NOTNULL DEFAULT \" '' \",
  sponsorship_total 	 I  NOTNULL DEFAULT '0',
  sticky		L  NOTNULL DEFAULT  \"'0'\"
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateIndexSQL',Array('idx_bug_sponsorship_total',db_get_table('mantis_bug_table'),'sponsorship_total'));
$upgrade[] = Array('CreateIndexSQL',Array('idx_bug_fixed_in_version',db_get_table('mantis_bug_table'),'fixed_in_version'));
$upgrade[] = Array('CreateIndexSQL',Array('idx_bug_status',db_get_table('mantis_bug_table'),'status'));
$upgrade[] = Array('CreateIndexSQL',Array('idx_project',db_get_table('mantis_bug_table'),'project_id'));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_bug_text_table'),"
  id 			 I  PRIMARY UNSIGNED NOTNULL AUTOINCREMENT,
  description 		XL NOTNULL,
  steps_to_reproduce 	XL NOTNULL,
  additional_information XL NOTNULL
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_bugnote_table'),"
  id 			 I  UNSIGNED PRIMARY NOTNULL AUTOINCREMENT,
  bug_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  reporter_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  bugnote_text_id 	 I  UNSIGNED NOTNULL DEFAULT '0',
  view_state 		I2 NOTNULL DEFAULT '10',
  date_submitted 	I  UNSIGNED     NOTNULL DEFAULT '1',
  last_modified 	I  UNSIGNED     NOTNULL DEFAULT '1',
  note_type 		 I  DEFAULT '0',
  note_attr 		C(250) DEFAULT \" '' \"
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateIndexSQL',Array('idx_bug',db_get_table('mantis_bugnote_table'),'bug_id'));
$upgrade[] = Array('CreateIndexSQL',Array('idx_last_mod',db_get_table('mantis_bugnote_table'),'last_modified'));
/* 20 */
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_bugnote_text_table'),"
  id 			 I  UNSIGNED NOTNULL PRIMARY AUTOINCREMENT,
  note 			XL NOTNULL
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_custom_field_project_table'),"
  field_id 		 I  NOTNULL PRIMARY DEFAULT '0',
  project_id 		 I  UNSIGNED PRIMARY NOTNULL DEFAULT '0',
  sequence 		I2 NOTNULL DEFAULT '0'
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_custom_field_string_table'),"
  field_id 		 I  NOTNULL PRIMARY DEFAULT '0',
  bug_id 		 I  NOTNULL PRIMARY DEFAULT '0',
  value 		C(255) NOTNULL DEFAULT \" '' \"
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateIndexSQL',Array('idx_custom_field_bug',db_get_table('mantis_custom_field_string_table'),'bug_id'));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_custom_field_table'),"
  id 			 I  NOTNULL PRIMARY AUTOINCREMENT,
  name 			C(64) NOTNULL DEFAULT \" '' \",
  type 			I2 NOTNULL DEFAULT '0',
  possible_values 	C(255) NOTNULL DEFAULT \" '' \",
  default_value 	C(255) NOTNULL DEFAULT \" '' \",
  valid_regexp 		C(255) NOTNULL DEFAULT \" '' \",
  access_level_r 	I2 NOTNULL DEFAULT '0',
  access_level_rw 	I2 NOTNULL DEFAULT '0',
  length_min 		 I  NOTNULL DEFAULT '0',
  length_max 		 I  NOTNULL DEFAULT '0',
  require_report 	L NOTNULL DEFAULT \" '0' \",
  require_update 	L NOTNULL DEFAULT \" '0' \",
  display_report 	L NOTNULL DEFAULT \" '0' \",
  display_update 	L NOTNULL DEFAULT \" '1' \",
  require_resolved 	L NOTNULL DEFAULT \" '0' \",
  display_resolved 	L NOTNULL DEFAULT \" '0' \",
  display_closed 	L NOTNULL DEFAULT \" '0' \",
  require_closed 	L NOTNULL DEFAULT \" '0' \"
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateIndexSQL',Array('idx_custom_field_name',db_get_table('mantis_custom_field_table'),'name'));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_filters_table'),"
  id 			 I  UNSIGNED NOTNULL PRIMARY AUTOINCREMENT,
  user_id 		 I  NOTNULL DEFAULT '0',
  project_id 		 I  NOTNULL DEFAULT '0',
  is_public 		L DEFAULT NULL,
  name 			C(64) NOTNULL DEFAULT \" '' \",
  filter_string 	XL NOTNULL
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_news_table'),"
  id 			 I  UNSIGNED PRIMARY NOTNULL AUTOINCREMENT,
  project_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  poster_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  date_posted 		I  UNSIGNED     NOTNULL DEFAULT '1',
  last_modified 	I  UNSIGNED     NOTNULL DEFAULT '1',
  view_state 		I2 NOTNULL DEFAULT '10',
  announcement 		L NOTNULL DEFAULT \" '0' \",
  headline 		C(64) NOTNULL DEFAULT \" '' \",
  body 			XL NOTNULL
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_project_category_table'),"
  project_id 		 I  UNSIGNED NOTNULL PRIMARY DEFAULT '0',
  category 		C(64) NOTNULL PRIMARY DEFAULT \" '' \",
  user_id 		 I  UNSIGNED NOTNULL DEFAULT '0'
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_project_file_table'),"
  id 			 I  UNSIGNED NOTNULL PRIMARY AUTOINCREMENT,
  project_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  title 		C(250) NOTNULL DEFAULT \" '' \",
  description 		C(250) NOTNULL DEFAULT \" '' \",
  diskfile 		C(250) NOTNULL DEFAULT \" '' \",
  filename 		C(250) NOTNULL DEFAULT \" '' \",
  folder 		C(250) NOTNULL DEFAULT \" '' \",
  filesize 		 I NOTNULL DEFAULT '0',
  file_type 		C(250) NOTNULL DEFAULT \" '' \",
  date_added 		I  UNSIGNED     NOTNULL DEFAULT '1',
  content 		B NOTNULL
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
/* 30 */
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_project_hierarchy_table'),"
			  child_id I UNSIGNED NOTNULL,
			  parent_id I UNSIGNED NOTNULL",
Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_project_table'),"
  id 			 I  UNSIGNED PRIMARY NOTNULL AUTOINCREMENT,
  name 			C(128) NOTNULL DEFAULT \" '' \",
  status 		I2 NOTNULL DEFAULT '10',
  enabled 		L NOTNULL DEFAULT \" '1' \",
  view_state 		I2 NOTNULL DEFAULT '10',
  access_min 		I2 NOTNULL DEFAULT '10',
  file_path 		C(250) NOTNULL DEFAULT \" '' \",
  description 		XL NOTNULL
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateIndexSQL',Array('idx_project_id',db_get_table('mantis_project_table'),'id'));
$upgrade[] = Array('CreateIndexSQL',Array('idx_project_name',db_get_table('mantis_project_table'),'name',Array('UNIQUE')));
$upgrade[] = Array('CreateIndexSQL',Array('idx_project_view',db_get_table('mantis_project_table'),'view_state'));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_project_user_list_table'),"
  project_id 		 I  UNSIGNED PRIMARY NOTNULL DEFAULT '0',
  user_id 		 I  UNSIGNED PRIMARY NOTNULL DEFAULT '0',
  access_level 		I2 NOTNULL DEFAULT '10'
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array( 'CreateIndexSQL',Array('idx_project_user',db_get_table('mantis_project_user_list_table'),'user_id'));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_project_version_table'),"
  id 			 I  NOTNULL PRIMARY AUTOINCREMENT,
  project_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  version 		C(64) NOTNULL DEFAULT \" '' \",
  date_order 		I  UNSIGNED     NOTNULL DEFAULT '1',
  description 		XL NOTNULL,
  released 		L NOTNULL DEFAULT \" '1' \"
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateIndexSQL',Array('idx_project_version',db_get_table('mantis_project_version_table'),'project_id,version',Array('UNIQUE')));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_sponsorship_table'),"
  id 			 I  NOTNULL PRIMARY AUTOINCREMENT,
  bug_id 		 I  NOTNULL DEFAULT '0',
  user_id 		 I  NOTNULL DEFAULT '0',
  amount 		 I  NOTNULL DEFAULT '0',
  logo 			C(128) NOTNULL DEFAULT \" '' \",
  url 			C(128) NOTNULL DEFAULT \" '' \",
  paid 			L NOTNULL DEFAULT \" '0' \",
  date_submitted 	I  UNSIGNED     NOTNULL DEFAULT '1',
  last_updated 		I  UNSIGNED     NOTNULL DEFAULT '1'
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
/* 40 */
$upgrade[] = Array('CreateIndexSQL',Array('idx_sponsorship_bug_id',db_get_table('mantis_sponsorship_table'),'bug_id'));
$upgrade[] = Array('CreateIndexSQL',Array('idx_sponsorship_user_id',db_get_table('mantis_sponsorship_table'),'user_id'));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_tokens_table'),"
			  id I NOTNULL PRIMARY AUTOINCREMENT,
			  owner I NOTNULL,
			  type I NOTNULL,
			  timestamp I  UNSIGNED     NOTNULL DEFAULT '1',
			  expiry I  UNSIGNED     NOTNULL DEFAULT '1',
			  value XL NOTNULL",
Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_user_pref_table'),"
  id 			 I  UNSIGNED NOTNULL PRIMARY AUTOINCREMENT,
  user_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  project_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  default_profile 	 I  UNSIGNED NOTNULL DEFAULT '0',
  default_project 	 I  UNSIGNED NOTNULL DEFAULT '0',
  refresh_delay 	 I  NOTNULL DEFAULT '0',
  redirect_delay 	L NOTNULL DEFAULT \" '0' \",
  bugnote_order 	C(4) NOTNULL DEFAULT 'ASC',
  email_on_new 		L NOTNULL DEFAULT \" '0' \",
  email_on_assigned 	L NOTNULL DEFAULT \" '0' \",
  email_on_feedback 	L NOTNULL DEFAULT \" '0' \",
  email_on_resolved	L NOTNULL DEFAULT \" '0' \",
  email_on_closed 	L NOTNULL DEFAULT \" '0' \",
  email_on_reopened 	L NOTNULL DEFAULT \" '0' \",
  email_on_bugnote 	L NOTNULL DEFAULT \" '0' \",
  email_on_status 	L NOTNULL DEFAULT \" '0' \",
  email_on_priority 	L NOTNULL DEFAULT \" '0' \",
  email_on_priority_min_severity 	I2 NOTNULL DEFAULT '10',
  email_on_status_min_severity 	I2 NOTNULL DEFAULT '10',
  email_on_bugnote_min_severity 	I2 NOTNULL DEFAULT '10',
  email_on_reopened_min_severity 	I2 NOTNULL DEFAULT '10',
  email_on_closed_min_severity 	I2 NOTNULL DEFAULT '10',
  email_on_resolved_min_severity 	I2 NOTNULL DEFAULT '10',
  email_on_feedback_min_severity	I2 NOTNULL DEFAULT '10',
  email_on_assigned_min_severity 	I2 NOTNULL DEFAULT '10',
  email_on_new_min_severity 	I2 NOTNULL DEFAULT '10',
  email_bugnote_limit 	I2 NOTNULL DEFAULT '0',
  language 		C(32) NOTNULL DEFAULT 'english'
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_user_print_pref_table'),"
  user_id 		 I  UNSIGNED NOTNULL PRIMARY DEFAULT '0',
  print_pref 		C(27) NOTNULL DEFAULT \" '' \"
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_user_profile_table'),"
  id 			 I  UNSIGNED NOTNULL PRIMARY AUTOINCREMENT,
  user_id 		 I  UNSIGNED NOTNULL DEFAULT '0',
  platform 		C(32) NOTNULL DEFAULT \" '' \",
  os 			C(32) NOTNULL DEFAULT \" '' \",
  os_build 		C(32) NOTNULL DEFAULT \" '' \",
  description 		XL NOTNULL
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_user_table'),"
  id 			 I  UNSIGNED NOTNULL PRIMARY AUTOINCREMENT,
  username 		C(32) NOTNULL DEFAULT \" '' \",
  realname 		C(64) NOTNULL DEFAULT \" '' \",
  email 		C(64) NOTNULL DEFAULT \" '' \",
  password 		C(32) NOTNULL DEFAULT \" '' \",
  date_created 		I  UNSIGNED     NOTNULL DEFAULT '1',
  last_visit 		I  UNSIGNED     NOTNULL DEFAULT '1',
  enabled		L NOTNULL DEFAULT \" '1' \",
  protected 		L NOTNULL DEFAULT \" '0' \",
  access_level 		I2 NOTNULL DEFAULT '10',
  login_count 		 I  NOTNULL DEFAULT '0',
  lost_password_request_count 	I2 NOTNULL DEFAULT '0',
  failed_login_count 	I2 NOTNULL DEFAULT '0',
  cookie_string 	C(64) NOTNULL DEFAULT \" '' \"
",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateIndexSQL',Array('idx_user_cookie_string',db_get_table('mantis_user_table'),'cookie_string',Array('UNIQUE')));
$upgrade[] = Array('CreateIndexSQL',Array('idx_user_username',db_get_table('mantis_user_table'),'username',Array('UNIQUE')));
$upgrade[] = Array('CreateIndexSQL',Array('idx_enable',db_get_table('mantis_user_table'),'enabled'));
/* 50 */
$upgrade[] = Array('CreateIndexSQL',Array('idx_access',db_get_table('mantis_user_table'),'access_level'));
$upgrade[] = Array('InsertData', Array( db_get_table('mantis_user_table'),
    "(username, realname, email, password, date_created, last_visit, enabled, protected, access_level, login_count, lost_password_request_count, failed_login_count, cookie_string) VALUES
        ('administrator', '', 'root@localhost', '63a9f0ea7bb98050796b649e85481845', '1', '1', '1', '0', 90, 3, 0, 0, '" .
             md5( mt_rand( 0, mt_getrandmax() ) + mt_rand( 0, mt_getrandmax() ) ) . md5( time() ) . "')" ) );
$upgrade[] = Array('AlterColumnSQL', Array( db_get_table( 'mantis_bug_history_table' ), "old_value C(255) NOTNULL" ) );
$upgrade[] = Array('AlterColumnSQL', Array( db_get_table( 'mantis_bug_history_table' ), "new_value C(255) NOTNULL" ) );

$upgrade[] = Array('CreateTableSQL',Array(db_get_table('mantis_email_table'),"
  email_id 		I  UNSIGNED NOTNULL PRIMARY AUTOINCREMENT,
  email		 	C(64) NOTNULL DEFAULT \" '' \",
  subject		C(250) NOTNULL DEFAULT \" '' \",
  submitted 	I  UNSIGNED     NOTNULL DEFAULT '1',
  metadata 		XL NOTNULL,
  body 			XL NOTNULL
  ",Array('mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS')));
$upgrade[] = Array('CreateIndexSQL',Array('idx_email_id',db_get_table('mantis_email_table'),'email_id'));
$upgrade[] = Array('AddColumnSQL',Array(db_get_table('mantis_bug_table'), "target_version C(64) NOTNULL DEFAULT \" '' \""));
$upgrade[] = Array('AddColumnSQL',Array(db_get_table('mantis_bugnote_table'), "time_tracking I UNSIGNED NOTNULL DEFAULT \" 0 \""));
$upgrade[] = Array('CreateIndexSQL',Array('idx_diskfile',db_get_table('mantis_bug_file_table'),'diskfile'));
$upgrade[] = Array('AlterColumnSQL', Array( db_get_table( 'mantis_user_print_pref_table' ), "print_pref C(64) NOTNULL" ) );
/* 60 */
$upgrade[] = Array('AlterColumnSQL', Array( db_get_table( 'mantis_bug_history_table' ), "field_name C(64) NOTNULL" ) );

# Release marker: 1.1.0a4

$upgrade[] = Array('CreateTableSQL', Array( db_get_table( 'mantis_tag_table' ), "
	id				I		UNSIGNED NOTNULL PRIMARY AUTOINCREMENT,
	user_id			I		UNSIGNED NOTNULL DEFAULT '0',
	name			C(100)	NOTNULL PRIMARY DEFAULT \" '' \",
	description		XL		NOTNULL,
	date_created	I		UNSIGNED NOTNULL DEFAULT '1',
	date_updated	I		UNSIGNED NOTNULL DEFAULT '1'
	", Array( 'mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS' ) ) );
$upgrade[] = Array('CreateTableSQL', Array( db_get_table( 'mantis_bug_tag_table' ), "
	bug_id			I	UNSIGNED NOTNULL PRIMARY DEFAULT '0',
	tag_id			I	UNSIGNED NOTNULL PRIMARY DEFAULT '0',
	user_id			I	UNSIGNED NOTNULL DEFAULT '0',
	date_attached	I  UNSIGNED     NOTNULL DEFAULT '1'
	", Array( 'mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS' ) ) );

$upgrade[] = Array('CreateIndexSQL', Array( 'idx_typeowner', db_get_table( 'mantis_tokens_table' ), 'type, owner' ) );

# Release marker: 1.2.0-SVN

$upgrade[] = Array('CreateTableSQL', Array( db_get_table( 'mantis_plugin_table' ), "
	basename		C(40)	NOTNULL PRIMARY,
	enabled			L		NOTNULL DEFAULT \" '0' \"
	", Array( 'mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS' ) ) );

/******************************************************** 
 * I used Enterprise Manager to do this after the install  
 ********************************************************
$upgrade[] = Array('AlterColumnSQL', Array( db_get_table( 'mantis_user_pref_table' ), "redirect_delay 	I NOTNULL DEFAULT 0" ) );
*/

/* apparently mysql now has a STRICT mode, where setting a DEFAULT value on a blob/text is now an error, instead of being silently ignored */
if ( isset( $f_db_type ) && ( $f_db_type == 'mysql' || $f_db_type == 'mysqli' ) ) {
	$upgrade[] = Array('AlterColumnSQL', Array( db_get_table( 'mantis_custom_field_table' ), "possible_values X NOTNULL" ) );
/******************************************************** 
 * I used Enterprise Manager to do this after the install  
 ********************************************************
} else {
	$upgrade[] = Array('AlterColumnSQL', Array( db_get_table( 'mantis_custom_field_table' ), "possible_values X NOTNULL DEFAULT \" '' \"" ) );
*/
}

$upgrade[] = Array( 'CreateTableSQL', Array( db_get_table( 'mantis_category_table' ), "
	id				I		UNSIGNED NOTNULL PRIMARY AUTOINCREMENT,
	project_id		I		UNSIGNED NOTNULL DEFAULT '0',
	user_id			I		UNSIGNED NOTNULL DEFAULT '0',
	name			C(128)	NOTNULL DEFAULT \" '' \",
	status			I		UNSIGNED NOTNULL DEFAULT '0'
	", Array( 'mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS' ) ) );
$upgrade[] = Array( 'CreateIndexSQL', Array( 'idx_category_project_name', db_get_table( 'mantis_category_table' ), 'project_id, name', array( 'UNIQUE' ) ) );
$upgrade[] = Array( 'InsertData', Array( db_get_table( 'mantis_category_table' ), "
	( project_id, user_id, name, status ) VALUES
	( '0', '0', 'General', '0' ) " ) );
/* 70 */
$upgrade[] = Array( 'AddColumnSQL', Array( db_get_table( 'mantis_bug_table' ), "category_id I UNSIGNED NOTNULL DEFAULT '1'" ) );
$upgrade[] = Array( 'UpdateFunction', "category_migrate" );
/******************************************************** 
 * I used Enterprise Manager to do this after the install  
 ********************************************************
$upgrade[] = Array( 'DropColumnSQL', Array( db_get_table( 'mantis_bug_table' ), "category" ) );
*/
$upgrade[] = Array( 'DropTableSQL', Array( db_get_table( 'mantis_project_category_table' ) ) );
$upgrade[] = Array( 'AddColumnSQL', Array( db_get_table( 'mantis_project_table' ), "category_id I UNSIGNED NOTNULL DEFAULT '1'" ) );
$upgrade[] = Array('CreateIndexSQL',Array('idx_project_id',db_get_table('mantis_project_table'),'id', array('DROP')), Array( 'db_index_exists', Array( db_get_table('mantis_project_table'), 'idx_project_id')));
$upgrade[] = Array('CreateIndexSQL',Array('idx_config',db_get_table( 'mantis_config_table' ),'config_id', array('DROP')), Array( 'db_index_exists', Array( db_get_table('mantis_config_table'), 'idx_config')));

$upgrade[] = Array( 'InsertData', Array( db_get_table( 'mantis_plugin_table' ), "
	( basename, enabled ) VALUES
	( 'MantisCoreFormatting', '1' )" ) );

$upgrade[] = Array( 'AddColumnSQL', Array( db_get_table( 'mantis_project_table' ), "inherit_global I UNSIGNED NOTNULL DEFAULT '0'" ) );
$upgrade[] = Array( 'AddColumnSQL', Array( db_get_table( 'mantis_project_hierarchy_table' ), "inherit_parent I UNSIGNED NOTNULL DEFAULT '0'" ) );
/* 80 */
$upgrade[] = Array( 'AddColumnSQL', Array( db_get_table( 'mantis_plugin_table' ), "
	protected		L		NOTNULL DEFAULT \" '0' \",
	priority		I		UNSIGNED NOTNULL DEFAULT '3'
	" ) );
$upgrade[] = Array( 'AddColumnSQL', Array( db_get_table( 'mantis_project_version_table' ), "
	obsolete		L		NOTNULL DEFAULT \" '0' \"" ) );
$upgrade[] = Array( 'AddColumnSQL', Array( db_get_table( 'mantis_bug_table' ), "
    due_date        I  UNSIGNED     NOTNULL DEFAULT '1'" ) );

$upgrade[] = Array( 'AddColumnSQL', Array( db_get_table( 'mantis_custom_field_table' ), "
  filter_by 		L 		NOTNULL DEFAULT \" '1' \"" ) );
$upgrade[] = Array( 'CreateTableSQL', Array( db_get_table( 'mantis_bug_revision_table' ), "
	id			I		UNSIGNED NOTNULL PRIMARY AUTOINCREMENT,
	bug_id		I		UNSIGNED NOTNULL,
	bugnote_id	I		UNSIGNED NOTNULL DEFAULT '0',
	user_id		I		UNSIGNED NOTNULL,
	timestamp	I		UNSIGNED NOTNULL DEFAULT '1',
	type		I		UNSIGNED NOTNULL,
	value		XL		NOTNULL
	", Array( 'mysql' => 'ENGINE=MyISAM DEFAULT CHARSET=utf8', 'pgsql' => 'WITHOUT OIDS' ) ) );
$upgrade[] = Array( 'CreateIndexSQL', Array( 'idx_bug_rev_id_time', db_get_table( 'mantis_bug_revision_table' ), 'bug_id, timestamp' ) );
$upgrade[] = Array( 'CreateIndexSQL', Array( 'idx_bug_rev_type', db_get_table( 'mantis_bug_revision_table' ), 'type' ) );

#date conversion
/************************************************************************************
 * I removed all the conversion SQL and modified the data types during table creation
 ************************************************************************************/
$upgrade[] = Array('CreateIndexSQL',Array('idx_last_mod',db_get_table( 'mantis_bugnote_table' ),'last_modified', array('DROP')), Array( 'db_index_exists', Array( db_get_table('mantis_bugnote_table'), 'idx_last_mod')));
$upgrade[] = Array('CreateIndexSQL',Array('idx_last_mod',db_get_table('mantis_bugnote_table'),'last_modified'));
$upgrade[] = Array('CreateIndexSQL',Array('idx_bug_rev_id_time',db_get_table( 'mantis_bug_revision_table' ),'bug_id, timestamp', array('DROP')), Array( 'db_index_exists', Array( db_get_table('mantis_bug_revision_table'), 'idx_bug_rev_id_time')));
$upgrade[] = Array( 'CreateIndexSQL', Array( 'idx_bug_rev_id_time', db_get_table( 'mantis_bug_revision_table' ), 'bug_id, timestamp' ) );
$upgrade[] = Array( 'CreateIndexSQL', Array( 'idx_project_hierarchy_child_id', db_get_table( 'mantis_project_hierarchy_table' ), 'child_id' ) );
$upgrade[] = Array( 'CreateIndexSQL', Array( 'idx_project_hierarchy_parent_id', db_get_table( 'mantis_project_hierarchy_table' ), 'parent_id' ) );
$upgrade[] = Array( 'CreateIndexSQL', Array( 'idx_tag_name', db_get_table( 'mantis_tag_table' ), 'name' ) );
$upgrade[] = Array( 'CreateIndexSQL', Array( 'idx_bug_tag_tag_id', db_get_table( 'mantis_bug_tag_table' ), 'tag_id' ) );
$upgrade[] = Array( 'CreateIndexSQL', Array( 'idx_email_id', db_get_table( 'mantis_email_table' ), 'email_id', array( 'DROP' ) ), Array( 'db_index_exists', Array( db_get_table( 'mantis_email_table' ), 'idx_email_id') ) );

$upgrade[] = Array( 'AddColumnSQL', Array( db_get_table( 'mantis_user_pref_table' ), "
	 timezone C(32) NOTNULL DEFAULT '' " ) );

$upgrade[] = Array( 'AddColumnSQL', Array( db_get_table( 'mantis_project_file_table' ), "
	user_id			I		UNSIGNED NOTNULL DEFAULT '0' " ) );
$upgrade[] = Array( 'AddColumnSQL', Array( db_get_table( 'mantis_bug_file_table' ), "
	user_id		I  			UNSIGNED NOTNULL DEFAULT '0' " ) );
schema_NEW.php (28,727 bytes)   
datadict-mssqlnative.inc.patch (3,275 bytes)   
--- datadict-mssqlnative.inc.orig.php	2011-09-08 13:59:32.000000000 +0200
+++ datadict-mssqlnative.inc.php	2012-01-08 16:18:53.251672800 +0100
@@ -121,19 +121,66 @@
 		return $sql;
 	}
 	
-	/*
 	function AlterColumnSQL($tabname, $flds)
 	{
-		$tabname = $this->TableName ($tabname);
+		$tabname = $this->TableName($tabname);
 		$sql = array();
 		list($lines,$pkey) = $this->_GenFields($flds);
+
 		foreach($lines as $v) {
-			$sql[] = "ALTER TABLE $tabname $this->alterCol $v";
-		}
 
+			// get field name and type
+			$arTmp = array();
+			$arTmp = preg_split("/[\s]+/", $v);
+			$arCnt = count($arTmp);
+			$fieldname = $arTmp[0];
+			$fieldtype = $arTmp[1];
+
+			$bHasDef = false;
+			$bIsNull = true;
+			
+			for($i = 2; $i < $arCnt; $i++) {
+				// get default value
+				if( trim($arTmp[$i]) == "DEFAULT" ) 
+				{
+					$bHasDef = true;
+					if( $i+1 <= $arCnt ) {
+						$field_def = $arTmp[$i+1];
+					}
+				}
+				
+				// get nullability
+				if( trim($arTmp[$i]) == "NOTNULL" )
+					$bIsNull = false;
+			}
+			
+
+			// delete default value if exists
+			$strDropDef = $this->DropDefaultValue($tabname, $fieldname);
+			if( trim($strDropDef) !== "" )
+				$sql[] = $strDropDef;
+
+
+			if($bHasDef) {				
+				// change type and nulls
+				if($bIsNull)
+					$sql[] = "ALTER TABLE $tabname $this->alterCol $fieldname $fieldtype NOT NULL";
+				else
+					$sql[] = "ALTER TABLE $tabname$this->alterCol $fieldname $fieldtype";
+				// Add default value
+				$sql[] = "ALTER TABLE $tabname ADD CONSTRAINT def_" . $tabname . "_" . $fieldname . " DEFAULT " . $field_def . " FOR $fieldname";
+			}
+			else {
+				$sql[] = "ALTER TABLE $tabname $this->alterCol $v";
+			}
+		}		
+/*		
+		echo "<pre>";
+		print_r($sql);
+		echo "</pre><br>";
+*/
 		return $sql;
 	}
-	*/
 	
 	function DropColumnSQL($tabname, $flds)
 	{
@@ -143,12 +190,59 @@
 		$f = array();
 		$s = 'ALTER TABLE ' . $tabname;
 		foreach($flds as $v) {
+			
+			// delete default value if exists
+			$strDropDef = $this->DropDefaultValue($tabname, $v);
+			if( trim($strDropDef) !== "" )
+				$sql[] = $strDropDef;
+
+			
 			$f[] = "\n$this->dropCol ".$this->NameQuote($v);
 		}
 		$s .= implode(', ',$f);
 		$sql[] = $s;
+/*	
+		echo "<pre>";
+		print_r($sql);
+		echo "</pre><br>";
+*/
 		return $sql;
 	}
+
+	function DropDefaultValue($tabname, $fieldname)
+	{
+		$strSql = "";
+		
+		// delete default value if exists
+		if( $this->connection->IsConnected() )
+		{
+			$oRs = $this->connection->Execute('
+
+						SELECT name FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid
+						WHERE object_name(so.parent_obj) = \'' . $tabname . '\'
+						AND so.xtype = \'D\'
+						AND sc.colid = (
+							SELECT colid FROM syscolumns
+							WHERE id = object_id(\'' . $tabname . '\') AND name = \'' . $fieldname . '\'
+						)
+			');
+			if( $oRs )
+				if(!$oRs->EOF) 
+				{
+					$defName = $oRs->fields['name'];
+					// Remove default value
+					$strSql = "ALTER TABLE $tabname DROP CONSTRAINT $defName";
+				}
+			
+			$oRs->Close();
+		};
+/*
+		echo "<pre>";
+		print_r($strSql);
+		echo "</pre><br>";
+*/
+		return $strSql;
+	}
 	
 	// return string must begin with space
 	function _CreateSuffix($fname,&$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
datadict-mssqlnative.inc.patch (3,275 bytes)   
datadict-mssqlnative.inc-adodb5.15.patch (3,275 bytes)   
--- datadict-mssqlnative.inc.php	2012-01-22 10:05:29.312500000 +0100
+++ datadict-mssqlnative.inc-orig.php	2012-01-19 14:44:32.000000000 +0100
@@ -121,66 +121,19 @@
 		return $sql;
 	}
 	
+	/*
 	function AlterColumnSQL($tabname, $flds)
 	{
-		$tabname = $this->TableName($tabname);
+		$tabname = $this->TableName ($tabname);
 		$sql = array();
 		list($lines,$pkey) = $this->_GenFields($flds);
-
 		foreach($lines as $v) {
+			$sql[] = "ALTER TABLE $tabname $this->alterCol $v";
+		}
 
-			// get field name and type
-			$arTmp = array();
-			$arTmp = preg_split("/[\s]+/", $v);
-			$arCnt = count($arTmp);
-			$fieldname = $arTmp[0];
-			$fieldtype = $arTmp[1];
-
-			$bHasDef = false;
-			$bIsNull = true;
-			
-			for($i = 2; $i < $arCnt; $i++) {
-				// get default value
-				if( trim($arTmp[$i]) == "DEFAULT" ) 
-				{
-					$bHasDef = true;
-					if( $i+1 <= $arCnt ) {
-						$field_def = $arTmp[$i+1];
-					}
-				}
-				
-				// get nullability
-				if( trim($arTmp[$i]) == "NOTNULL" )
-					$bIsNull = false;
-			}
-			
-
-			// delete default value if exists
-			$strDropDef = $this->DropDefaultValue($tabname, $fieldname);
-			if( trim($strDropDef) !== "" )
-				$sql[] = $strDropDef;
-
-
-			if($bHasDef) {				
-				// change type and nulls
-				if($bIsNull)
-					$sql[] = "ALTER TABLE $tabname $this->alterCol $fieldname $fieldtype NOT NULL";
-				else
-					$sql[] = "ALTER TABLE $tabname$this->alterCol $fieldname $fieldtype";
-				// Add default value
-				$sql[] = "ALTER TABLE $tabname ADD CONSTRAINT def_" . $tabname . "_" . $fieldname . " DEFAULT " . $field_def . " FOR $fieldname";
-			}
-			else {
-				$sql[] = "ALTER TABLE $tabname $this->alterCol $v";
-			}
-		}		
-/*		
-		echo "<pre>";
-		print_r($sql);
-		echo "</pre><br>";
-*/
 		return $sql;
 	}
+	*/
 	
 	function DropColumnSQL($tabname, $flds)
 	{
@@ -190,59 +143,12 @@
 		$f = array();
 		$s = 'ALTER TABLE ' . $tabname;
 		foreach($flds as $v) {
-			
-			// delete default value if exists
-			$strDropDef = $this->DropDefaultValue($tabname, $v);
-			if( trim($strDropDef) !== "" )
-				$sql[] = $strDropDef;
-
-			
 			$f[] = "\n$this->dropCol ".$this->NameQuote($v);
 		}
 		$s .= implode(', ',$f);
 		$sql[] = $s;
-/*	
-		echo "<pre>";
-		print_r($sql);
-		echo "</pre><br>";
-*/
 		return $sql;
 	}
-
-	function DropDefaultValue($tabname, $fieldname)
-	{
-		$strSql = "";
-		
-		// delete default value if exists
-		if( $this->connection->IsConnected() )
-		{
-			$oRs = $this->connection->Execute('
-
-						SELECT name FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid
-						WHERE object_name(so.parent_obj) = \'' . $tabname . '\'
-						AND so.xtype = \'D\'
-						AND sc.colid = (
-							SELECT colid FROM syscolumns
-							WHERE id = object_id(\'' . $tabname . '\') AND name = \'' . $fieldname . '\'
-						)
-			');
-			if( $oRs )
-				if(!$oRs->EOF) 
-				{
-					$defName = $oRs->fields['name'];
-					// Remove default value
-					$strSql = "ALTER TABLE $tabname DROP CONSTRAINT $defName";
-				}
-			
-			$oRs->Close();
-		};
-/*
-		echo "<pre>";
-		print_r($strSql);
-		echo "</pre><br>";
-*/
-		return $strSql;
-	}
 	
 	// return string must begin with space
 	function _CreateSuffix($fname,&$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)

Relationships

related to 0010742 closeddregad Database query failed. Error received from database was 0000206: Operand type clash: int is incompatible with text for the query 
related to 0011364 closeddhx on installl.php 1.2.0rc2 SYSTEM WARNING: htmlentities() expects parameter 1 to be string, array given 
has duplicate 0014095 closeddregad Cannot initialize DB on SQL Server 2008 R2 
has duplicate 0015454 closeddregad installation errors with SQLServer 
has duplicate 0015582 closeddregad Unable to Install on MSSQL (SQL Server 2008) 
related to 0011776 closeddregad Support for Sql Server Native driver (sqlsrv) 
related to 0021883 closeddregad MSSQL installation fails with BAD ALTER TABLE error 

Activities

maximaxi

maximaxi

2010-02-22 06:43

reporter   ~0024458

I'm just using the "Print SQL Queries instead of Writing to the Database", because "automatic" installation fails even when creating the mantis_config_table.
I have more control when using "Print SQL Queries instead of Writing to the Database".

date_migrate is a function by mantis right? I can't find it...
But this also means, a manual UPGRADE from an earlier version via "Print SQL Queries instead of Writing to the Database" (copy & paste) is not possible right? Because MS SQL is not able to call this function.

maximaxi

maximaxi

2010-02-26 06:11

reporter   ~0024536

Same thing with 1.2.0 Final. :(

makafre

makafre

2010-03-30 09:54

reporter   ~0024931

Last edited: 2010-03-30 09:57

Hi guys, I am also not able to use Mantis with MS SQL; setup is PHP 5.2.10 on
FastCGI (IIS6) acessing a 8.00.2039 - SP4 - Standard Edition SQL Server.
Mantis is 1.2.0 Stable.

Message: "Database query failed. Error received from database was 0000206: Operand type clash: int is incompatible with text for the query: INSERT INTO mantis_config_table
( value, type, access_reqd, config_id, project_id, user_id )
VALUES
(?, ?, ?, ?, ?,? )."

See issue 0010742 for more info.

maximaxi

maximaxi

2010-03-31 02:55

reporter   ~0024934

I agree with you makafre!

If I don't use the "Print SQL Queries instead of Writing to the Database" option, I get the same error.

ChrisSchwarz

ChrisSchwarz

2010-04-23 05:10

reporter   ~0025226

Hi Guys,
is there any chance to get the ms sql installer version for mantis 1.2.x anytime soon? my company is eagerly waiting to upgrade to mantis 1.2, but cannot as the upgrade of the ms sql db doesnt work because of the above described issues.

tomkraw1

tomkraw1

2010-04-28 01:39

reporter   ~0025312

Last edited: 2010-04-28 01:46

Hi guys!

I'm trying to install Mantis BT 1.2 on SQL Server 2000. After I ticked "Print SQL Queries..." I started installing manually. Query after query. I think installer uses incorrect SQL Server syntax.

/
First problem
/
ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay INT SET DEFAULT 0 NOT NULL;

Syntax of this query is incorrect in SQL Server (2000 in my case). If you try this you will see an error similar to the error from attachment (mantis_1.2_install_mssql2000.png).

I did it in that way:
-- deleting default value
ALTER TABLE mantis_user_pref_table DROP
CONSTRAINT DF__mantis_usredir7D2E8C24;

-- changing field type
ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay INT NOT NULL;

-- adding new default value
ALTER TABLE mantis_user_pref_table ADD
CONSTRAINT DF__mantis_usredir7D2E8C24 DEFAULT (0) FOR redirect_delay;

Now the problem is random name of default value given by SQL Server.

<b>My suggestion</b>
Remove evry default value from CREATE TABLE instructions and add names to every default values with such syntax

ALTER TABLE mantis_user_pref_table ADD
CONSTRAINT def_redirect_delay DEFAULT (0) FOR redirect_delay;

def_redirect_delay will mean:

  • def - abberviation from "DEFAULT"
  • redirect_delay - field name

Then in the future removing default values will be easier because there won't be random names (DF__mantis_usredir7D2E8C24) given by SQL Server. It will be possible to remove default value in that way.

ALTER TABLE mantis_user_pref_table DROP
CONSTRAINT def_redirect_delay;

/
Second problem
/
ALTER TABLE mantis_bugnote_table DROP COLUMN last_modified;
and
ALTER TABLE mantis_bug_revision_table DROP COLUMN timestamp;

Here is another error. Column can't be deleted while is a part of index. So before we delete column we have to drop index.

While I was running query after query making corrections I sow about 20 first type errors and two second type.

maximaxi

maximaxi

2010-04-28 02:32

reporter   ~0025313

Last edited: 2010-04-28 02:32

Like ChrisSchwarz already told we are also a company and still waiting for the missing MS SQL support, we can't rely on such workarounds. It's a running productive system and highly frequented by its users.

jstevensnl

jstevensnl

2010-04-29 09:52

reporter   ~0025349

The migrate commands have not been correctly updated (schema.php):
category_migrate must be install_category_migrate
date_migrate must be install_date_migrate
correct_multiselect_custom_fields_db_format must be install_correct_multiselect_custom_fields_db_format

They all are missing the install_ prefix.

This solves a part of the problem.

maximaxi

maximaxi

2010-05-03 03:11

reporter   ~0025367

Last edited: 2010-05-03 03:11

It is that simple? So they have just missed that?

Great, now the other part: Alter table commands fail because of indices/constraints on the affected tables/columns.

How could this be fixed?

jstevensnl

jstevensnl

2010-05-03 04:18

reporter   ~0025369

Last edited: 2010-05-03 04:31

The indices problem can be solved by changing the schema.
All index creation must be moved to the bottom (after all other SQL commands).
This means that all changes can be performed.

The proposed change has no impact on any of the database engines.
It only makes maintenance of the schema a little bit more difficult.

This does not solve the problem of the insert command (needs to be investigated). A curious side-effect now is that a retry in the browser solves the problem of the insert, to give the same error on the next insert command.

Looks to me as a missing "commit" statement or something like it.

jchoover

jchoover

2010-05-04 02:07

reporter   ~0025385

Last edited: 2010-05-04 02:11

Another option for the default constrains would be to provide queries for MS SQL which would get the dynamic constraint names.

Ex:

DECLARE @defname VARCHAR(100), @cmd VARCHAR(1000)
SET @defname =
(SELECT name
FROM sysobjects so JOIN sysconstraints sc
ON so.id = sc.constid
WHERE object_name(so.parent_obj) = 'mantis_user_pref_table'
AND so.xtype = 'D'
AND sc.colid =
(SELECT colid FROM syscolumns
WHERE id = object_id('dbo.mantis_user_pref_table') AND
name = 'redirect_delay'))
SET @cmd = ‘ALTER TABLE mantis_user_pref_table DROP CONSTRAINT ‘

  • @defname
    EXEC(@cmd)

(from http://msdn.microsoft.com/en-us/library/aa175912%28SQL.80%29.aspx)

The hard part about making the statement of "don't do it they way you have been doing" means that upgrade scripts won't work. What we really need is a MS SQL Server guru who can write the MS specific queries and ensure they are version independent.

maximaxi

maximaxi

2010-05-04 02:42

reporter   ~0025387

Last edited: 2010-05-04 02:44

jstevensnl: At least an initial creation of the database would work with your procedure. A solution for an update of a database as jchoover mentioned is still missing.

tomkraw1

tomkraw1

2010-05-04 16:25

reporter   ~0025396

I listed all default values in my database and I have to modify my suggestion. Two tables may have columns with the same name. So default values should have names like this

def-mantis_user_pref_table-redirect_delay will mean:

  • def - abberviation from "DEFAULT"
  • mantis_user_pref_table - table name
  • redirect_delay - field name
ottog

ottog

2010-05-05 10:59

reporter   ~0025407

Hi,

The original issue reported here is a duplicate of issue 0011364 but I do not think I am authorized to mark it as such. I have attached a note to 0011364 explaining the causes and suggesting a patch to admin/install.php.

Regarding the "constraints" problem also discussed above, I have found that it is connected to a problem with the ADODB database-abstraction library. MantisBT calls ADODB in a correct way and rightly relies on the library to provide compatibiliy.

However, the ADODB library for some reason relies on having an active database connection and issuing queries intermixed with PHP logic for dropping constraints before columns are modified or dropped. Thus, dozens of SQL queries that are very cumbersome to recreate manually are missing from the printed SQL code when the "Print SQL Queries instead of Writing to the Database" option is checked.

This is arguably a defect of the ADODB library, which could in principle generate pure SQL code for dropping the constraints instead of relying on PHP logic, and I do not think the problem ought to be fixed or worked around by MantisBT. But there should perhaps be a warning if "MS SQL Server" and "Print SQL Queries" are selected together on the installation page.

Perhaps someone can volunteer to suggest such changes to the ADODB developers?

tomkraw1

tomkraw1

2010-05-05 18:06

reporter   ~0025413

I added sql script that may be used for upgrade mantis to newer version.

tomkraw1

tomkraw1

2010-05-17 06:08

reporter   ~0025514

I made AlterColumnSQL method that should be added in datadict-mssql.inc.php or datadict-mssqlnative.inc.php. It should help for constraints problem. Can anyone test it?

ottog

ottog

2010-05-17 08:18

reporter   ~0025515

I have not tested the code, but upon quick inspection, it looks reasonable. However, it does not look like it solves the case of a fresh installation being done by the "SQL printout" method. (When tables are both created and changed within the same script.) It may work in an iterative fashion by running the script as far as possible, then regenerating it and running the new script -- possibly repeating the process a couple of times, depending on the evolution of the schema. But I do not think it will work right away for a fresh installation.

Also, it may be that "iterative" SQL-printout installation would work with the datadict-mssql.inc.php distributed with 1.2.1, too, although I have not tried that either.

I will not be able to do any testing of MantisBT installation in the near future, but I encourage anyone who is able to test to do so with and without the suggested datadict-mssql.inc.php change.

ChrisSchwarz

ChrisSchwarz

2010-06-12 07:17

reporter   ~0025818

hey guys, is there any solution for a working update from 1.1.8 to 1.2.2 any time soon?
it would be great to finally upgrade and use all the new features!

tomkraw1

tomkraw1

2010-07-20 09:35

reporter   ~0026105

Last edited: 2010-08-16 10:33

I made my own update to ADOdb V5.11 after which I successively installed MantisBT v.1.2.1!

Without changing schema.php. I use mssqlnative driver but it should work with mssql driver because I adjusted adodb to SQL Server 2000 syntax.

I modified methods: AlterColumnSQL, DropColumnSQL and I added DropDefaultValue. Functions attached.

Can anyone confirm it is working?

vallen

vallen

2010-08-17 23:29

reporter   ~0026346

Last edited: 2010-08-17 23:35

I have successfully installed Mantis 1.2.2 on Windows Server 2003, IIS6, and MSSQL 2005. This is a new installation, so my solution will not work for anyone wishing to upgrade from 1.1.x.

First I applied the patches identified in Ottog's note (http://www.mantisbt.org/bugs/view.php?id=10742#c25405). I did have some issues with the patch for token_api.php and skipped that one.

Once I applied the patches, install.php was failing to alter tables, specifically dropping columns, saying that an object is still associated to the column. I tried moving all the index creation queries to the bottom of schema, but that didn't seem to have an effect.

Looking closer at the SQL it became clear that the alter table queries were in almost all cases changing v1.1.x date/time fields to int fields for 1.2.x (I think to account for MySQL not reading seconds from date/time fields or something). Since mine is a clean install, I simply modified the table creation queries in schema.php to use ints instead of date/time data types. Doing this also obviated the need to perform all those date_migrate update functions.

Install.php ran without a gripe and everything seems to be working fine!

I have attached my modified schema.php; I suggest doing a diff between the original and my version to see exactly what I did. BTW, there are a few queries I commented-out and then manually performed after the install using Enterprise Manager -- you can't miss my comments pointing them out!

All-in-all not too bad for a few days of pounding my head against my keyboard!

DiamondEagle

DiamondEagle

2010-10-06 08:52

reporter   ~0026980

tomkraw1, thanks for your changes. I'd like to test your changes, but I need some guidance. How do I test your changes? What do I do? (blush...)

tomkraw1

tomkraw1

2010-10-06 17:21

reporter   ~0026988

Last edited: 2011-01-05 06:26

All changes should be done in the <mantis_dir>/library/adodb/datadict/datadict-mssqlnative.inc.php or datadict-mssql.inc.php
file.

Add my DropDefaultValue method and comment out original AlterColumnSQL and DropColumnSQL methods. Then add mine vresions of AlterColumnSQL and DropColumnSQL methods. All methods are in the adodb.txt file that I posted.

The idea is simple. My code checks if a constraint exists. If yes then it is deleted before the column is dropped or altered. In case of altering a column after altering column constraint is recreated.

You can see sql queries produced by my code if you uncomment print_r() instructions from my adodb.txt and if you check "Print SQL Queries" in the install.php.

DiamondEagle

DiamondEagle

2010-10-28 05:56

reporter   ~0027183

Last edited: 2010-10-28 05:58

Thanks tomkraw1.

I deleted function AlterColumnSQL and function DropColumnSQL from \library\adodb\datadict\datadict-mssqlnative.inc.php

Then I added your DropDefaultValue, AlterColumnSQL and DropColumnSQL functions to \library\adodb\drivers\adodb-mssql.inc.php

Then I ran /admin/install.php and the setup crashes when it tries to CREATE TABLE mantis_plugin_table because: "There is already an object named 'mantis_plugin_table' in the database."

The full error is:

Setting Database Hostname GOOD
Setting Database Type GOOD
Checking PHP support for database type GOOD
Setting Database Username GOOD
Setting Database Password GOOD
Setting Database Name GOOD
Setting Admin Username GOOD
Setting Admin Password GOOD
Attempting to connect to database as admin GOOD
Attempting to connect to database as user GOOD
Checking Database Server Version
Running mssql version Microsoft SQL Server Yukon - 9.00.4035 GOOD

Installing Database
Create database if it does not exist GOOD
Checking Database Server Version
Running mssql version Microsoft SQL Server Yukon - 9.00.4035 GOOD
Attempting to connect to database as user GOOD
Schema CreateTableSQL ( mantis_plugin_table ) BAD
CREATE TABLE mantis_plugin_table ( basename VARCHAR(40) NOT NULL, enabled BIT DEFAULT '0' NOT NULL, PRIMARY KEY (basename) )
There is already an object named 'mantis_plugin_table' in the database.

Checks Failed...
Please correct failed checks

I renamed mantis_plugin_table to mantis_plugin_table_TEST and ran /admin/install.php again. This time I got the following message:

APPLICATION ERROR 0000401
Database query failed. Error received from database was 0000206: Operand type clash: int is incompatible with text for the query: UPDATE mantis_config_table
SET value=?, type=?, access_reqd=?
WHERE config_id = ? AND
project_id = ? AND
user_id = ?.

Please use the "Back" button in your web browser to return to the previous page. There you can correct whatever problems were identified in this error or select another action. You can also click an option from the menu bar to go directly to a new section.

So I restored the ORIGINAL mantis_plugin_table and tried running /admin/install.php again. I got the CREATE TABLE mantis_plugin_table error again:

BAD
CREATE TABLE mantis_plugin_table ( basename VARCHAR(40) NOT NULL, enabled BIT DEFAULT '0' NOT NULL, PRIMARY KEY (basename) )
There is already an object named 'mantis_plugin_table' in the database.

Can you determine what is causing this problem?
Thanks.

DiamondEagle

DiamondEagle

2010-10-28 06:16

reporter   ~0027184

Last edited: 2010-10-28 06:17

As per the instructions in adodb.txt I added the DropDefaultValue function to the end of datadict-mssqlnative.inc.php. It is the last function in the ADODB2_mssqlnative class. Is this correct? Or should DropDefaultValue be put right at the end of datadict-mssqlnative.inc.php so that it is OUTSIDE the the ADODB2_mssqlnative class?

I get the "There is already an object named 'mantis_plugin_table' in the database" error both when DropDefaultValue is INSIDE and OUTSIDE of ADODB2_mssqlnative.

DiamondEagle

DiamondEagle

2010-10-28 06:22

reporter   ~0027185

Last edited: 2010-10-28 07:51

When I enable the "Print SQL Queries instead of Writing to the Database" option I don't get any errors. The output is:

Setting Database Hostname GOOD
Setting Database Type GOOD
Checking PHP support for database type GOOD
Setting Database Username GOOD
Setting Database Password GOOD
Setting Database Name GOOD
Setting Admin Username GOOD
Setting Admin Password GOOD
Attempting to connect to database as admin GOOD
Attempting to connect to database as user GOOD
Checking Database Server Version
Running mssql version Microsoft SQL Server Yukon - 9.00.4035 GOOD
Installing Database
Database Creation Suppressed, SQL Queries follow
CREATE TABLE mantis_plugin_table (
basename VARCHAR(40) NOT NULL,
enabled BIT DEFAULT '0' NOT NULL,
PRIMARY KEY (basename)
);

ALTER TABLE mantis_user_pref_table DROP CONSTRAINT DF__mantis_usredir31B762FC;

ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay INT;

ALTER TABLE mantis_user_pref_table ADD CONSTRAINT DF__mantis_usredir31B762FC DEFAULT 0 FOR redirect_delay;

ALTER TABLE mantis_user_pref_table ALTER COLUMN redirect_delay INT NOT NULL;

ALTER TABLE mantis_custom_field_table DROP CONSTRAINT DF__mantis_cupossi5CD6CB2B;

ALTER TABLE mantis_custom_field_table ALTER COLUMN possible_values TEXT;

ALTER TABLE mantis_custom_field_table ADD CONSTRAINT DF__mantis_cupossi5CD6CB2B DEFAULT '' FOR possible_values;

ALTER TABLE mantis_custom_field_table ALTER COLUMN possible_values TEXT NOT NULL;

CREATE TABLE mantis_category_table (
id INT IDENTITY(1,1) NOT NULL,
project_id INT DEFAULT 0 NOT NULL,
user_id INT DEFAULT 0 NOT NULL,
name VARCHAR(128) DEFAULT '' NOT NULL,
status INT DEFAULT 0 NOT NULL,
PRIMARY KEY (id)
);

CREATE UNIQUE INDEX idx_category_project_name ON mantis_category_table (project_id, name);

INSERT INTO mantis_category_table
( project_id, user_id, name, status ) VALUES
( '0', '0', 'General', '0' ) ;

ALTER TABLE mantis_bug_table ADD
category_id INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_bug_table DROP CONSTRAINT DF__mantis_bucateg32E0915F;

ALTER TABLE mantis_bug_table
DROP COLUMN category;

DROP TABLE mantis_project_category_table;

ALTER TABLE mantis_project_table ADD
category_id INT DEFAULT 1 NOT NULL;

DROP INDEX mantis_project_table.idx_project_id;

DROP INDEX mantis_config_table.idx_config;

INSERT INTO mantis_plugin_table
( basename, enabled ) VALUES
( 'MantisCoreFormatting', '1' );

ALTER TABLE mantis_project_table ADD
inherit_global INT DEFAULT 0 NOT NULL;

ALTER TABLE mantis_project_hierarchy_table ADD
inherit_parent INT DEFAULT 0 NOT NULL;

ALTER TABLE mantis_plugin_table ADD
protected BIT DEFAULT '0' NOT NULL,
priority INT DEFAULT 3 NOT NULL;

ALTER TABLE mantis_project_version_table ADD
obsolete BIT DEFAULT '0' NOT NULL;

ALTER TABLE mantis_bug_table ADD
due_date DATETIME DEFAULT '1970-01-01 00:00:01' NOT NULL;

ALTER TABLE mantis_custom_field_table ADD
filter_by BIT DEFAULT '1' NOT NULL;

CREATE TABLE mantis_bug_revision_table (
id INT IDENTITY(1,1) NOT NULL,
bug_id INT NOT NULL,
bugnote_id INT DEFAULT 0 NOT NULL,
user_id INT NOT NULL,
timestamp DATETIME DEFAULT '1970-01-01 00:00:01' NOT NULL,
type INT NOT NULL,
value TEXT NOT NULL,
PRIMARY KEY (id)
);

CREATE INDEX idx_bug_rev_id_time ON mantis_bug_revision_table (bug_id, timestamp);

CREATE INDEX idx_bug_rev_type ON mantis_bug_revision_table (type);

ALTER TABLE mantis_bug_table ADD
date_submitted_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_bug_table ADD
due_date_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_bug_table ADD
last_updated_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_bug_table DROP CONSTRAINT DFmantis_bu_date33D4B598;

ALTER TABLE mantis_bug_table
DROP COLUMN date_submitted;

EXEC sp_rename 'mantis_bug_table.date_submitted_int','date_submitted';

ALTER TABLE mantis_bug_table
DROP COLUMN due_date;

EXEC sp_rename 'mantis_bug_table.due_date_int','due_date';

ALTER TABLE mantis_bug_table DROP CONSTRAINT DFmantis_bu_last34C8D9D1;

ALTER TABLE mantis_bug_table
DROP COLUMN last_updated;

EXEC sp_rename 'mantis_bug_table.last_updated_int','last_updated';

DROP INDEX mantis_bugnote_table.idx_last_mod;

ALTER TABLE mantis_bugnote_table ADD
last_modified_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_bugnote_table ADD
date_submitted_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_bugnote_table DROP CONSTRAINT DFmantis_bu_last4AB81AF0;

ALTER TABLE mantis_bugnote_table
DROP COLUMN last_modified;

EXEC sp_rename 'mantis_bugnote_table.last_modified_int','last_modified';

CREATE INDEX idx_last_mod ON mantis_bugnote_table (last_modified);

ALTER TABLE mantis_bugnote_table DROP CONSTRAINT DFmantis_bu_date49C3F6B7;

ALTER TABLE mantis_bugnote_table
DROP COLUMN date_submitted;

EXEC sp_rename 'mantis_bugnote_table.date_submitted_int','date_submitted';

ALTER TABLE mantis_bug_file_table ADD
date_added_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_bug_file_table DROP CONSTRAINT DFmantis_bu_date15502E78;

ALTER TABLE mantis_bug_file_table
DROP COLUMN date_added;

EXEC sp_rename 'mantis_bug_file_table.date_added_int','date_added';

ALTER TABLE mantis_project_file_table ADD
date_added_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_project_file_table DROP CONSTRAINT DFmantis_pr_date08B54D69;

ALTER TABLE mantis_project_file_table
DROP COLUMN date_added;

EXEC sp_rename 'mantis_project_file_table.date_added_int','date_added';

ALTER TABLE mantis_bug_history_table ADD
date_modified_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_bug_history_table DROP CONSTRAINT DFmantis_bu_date1A14E395;

ALTER TABLE mantis_bug_history_table
DROP COLUMN date_modified;

EXEC sp_rename 'mantis_bug_history_table.date_modified_int','date_modified';

ALTER TABLE mantis_user_table ADD
last_visit_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_user_table ADD
date_created_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_user_table DROP CONSTRAINT DFmantis_us_date55F4C372;

ALTER TABLE mantis_user_table
DROP COLUMN date_created;

EXEC sp_rename 'mantis_user_table.date_created_int','date_created';

ALTER TABLE mantis_user_table DROP CONSTRAINT DFmantis_us_last56E8E7AB;

ALTER TABLE mantis_user_table
DROP COLUMN last_visit;

EXEC sp_rename 'mantis_user_table.last_visit_int','last_visit';

ALTER TABLE mantis_email_table ADD
submitted_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_email_table DROP CONSTRAINT DF__mantis_emsubmi625A9A57;

ALTER TABLE mantis_email_table
DROP COLUMN submitted;

EXEC sp_rename 'mantis_email_table.submitted_int','submitted';

ALTER TABLE mantis_tag_table ADD
date_created_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_tag_table ADD
date_updated_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_tag_table DROP CONSTRAINT DFmantis_ta_date690797E6;

ALTER TABLE mantis_tag_table
DROP COLUMN date_created;

EXEC sp_rename 'mantis_tag_table.date_created_int','date_created';

ALTER TABLE mantis_tag_table DROP CONSTRAINT DFmantis_ta_date69FBBC1F;

ALTER TABLE mantis_tag_table
DROP COLUMN date_updated;

EXEC sp_rename 'mantis_tag_table.date_updated_int','date_updated';

ALTER TABLE mantis_bug_tag_table ADD
date_attached_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_bug_tag_table DROP CONSTRAINT DFmantis_bu_date6FB49575;

ALTER TABLE mantis_bug_tag_table
DROP COLUMN date_attached;

EXEC sp_rename 'mantis_bug_tag_table.date_attached_int','date_attached';

ALTER TABLE mantis_tokens_table ADD
timestamp_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_tokens_table ADD
expiry_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_tokens_table
DROP COLUMN timestamp;

EXEC sp_rename 'mantis_tokens_table.timestamp_int','timestamp';

ALTER TABLE mantis_tokens_table
DROP COLUMN expiry;

EXEC sp_rename 'mantis_tokens_table.expiry_int','expiry';

ALTER TABLE mantis_news_table ADD
last_modified_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_news_table ADD
date_posted_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_news_table DROP CONSTRAINT DFmantis_ne_last76969D2E;

ALTER TABLE mantis_news_table
DROP COLUMN last_modified;

EXEC sp_rename 'mantis_news_table.last_modified_int','last_modified';

ALTER TABLE mantis_news_table DROP CONSTRAINT DFmantis_ne_date75A278F5;

ALTER TABLE mantis_news_table
DROP COLUMN date_posted;

EXEC sp_rename 'mantis_news_table.date_posted_int','date_posted';

ALTER TABLE mantis_bug_revision_table ADD
timestamp_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_bug_revision_table
DROP COLUMN timestamp;

EXEC sp_rename 'mantis_bug_revision_table.timestamp_int','timestamp';

CREATE INDEX idx_bug_rev_id_time ON mantis_bug_revision_table (bug_id, timestamp);

ALTER TABLE mantis_user_pref_table ADD
timezone VARCHAR(32) DEFAULT '' NOT NULL;

ALTER TABLE mantis_project_version_table ADD
date_order_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_project_version_table DROP CONSTRAINT DFmantis_pr_date1AD3FDA4;

ALTER TABLE mantis_project_version_table
DROP COLUMN date_order;

EXEC sp_rename 'mantis_project_version_table.date_order_int','date_order';

ALTER TABLE mantis_sponsorship_table ADD
date_submitted_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_sponsorship_table ADD
last_updated_int INT DEFAULT 1 NOT NULL;

ALTER TABLE mantis_sponsorship_table DROP CONSTRAINT DFmantis_sp_last25518C17;

ALTER TABLE mantis_sponsorship_table
DROP COLUMN last_updated;

EXEC sp_rename 'mantis_sponsorship_table.last_updated_int','last_updated';

ALTER TABLE mantis_sponsorship_table DROP CONSTRAINT DFmantis_sp_date245D67DE;

ALTER TABLE mantis_sponsorship_table
DROP COLUMN date_submitted;

EXEC sp_rename 'mantis_sponsorship_table.date_submitted_int','date_submitted';

ALTER TABLE mantis_project_file_table ADD
user_id INT DEFAULT 0 NOT NULL;

ALTER TABLE mantis_bug_file_table ADD
user_id INT DEFAULT 0 NOT NULL;

ALTER TABLE mantis_custom_field_table DROP CONSTRAINT DF__mantis_cuadvan6383C8BA;

ALTER TABLE mantis_custom_field_table
DROP COLUMN advanced;

ALTER TABLE mantis_user_pref_table DROP CONSTRAINT DF__mantis_usadvan2DE6D218;

ALTER TABLE mantis_user_pref_table
DROP COLUMN advanced_report;

ALTER TABLE mantis_user_pref_table DROP CONSTRAINT DF__mantis_usadvan2EDAF651;

ALTER TABLE mantis_user_pref_table
DROP COLUMN advanced_view;

ALTER TABLE mantis_user_pref_table DROP CONSTRAINT DF__mantis_usadvan2FCF1A8A;

ALTER TABLE mantis_user_pref_table
DROP COLUMN advanced_update;

CREATE INDEX idx_project_hierarchy_child_id ON mantis_project_hierarchy_table (child_id);

CREATE INDEX idx_project_hierarchy_parent_id ON mantis_project_hierarchy_table (parent_id);

CREATE INDEX idx_tag_name ON mantis_tag_table (name);

CREATE INDEX idx_bug_tag_tag_id ON mantis_bug_tag_table (tag_id);

DROP INDEX mantis_email_table.idx_email_id;

INSERT INTO mantis_config_table ( value, type, access_reqd, config_id, project_id, user_id ) VALUES ('183', 1, 90, 'database_version', 0, 0 );

Your database has not been created yet. Please create the database, then install the tables and data using the information above before proceeding.

     Write Configuration File(s)  

Updating Configuration File (config_inc.php)
GOOD
Checking Installation...
Checking for register_globals are off for mantis GOOD
Attempting to connect to database as user GOOD
checking ability to SELECT records GOOD
checking ability to INSERT records GOOD
checking ability to UPDATE records GOOD
checking ability to DELETE records GOOD

Install was successful.

Continue to log into Mantis

If it will help, my server config is:

Windows Server 2003 Standard Edition (Build 3790: Service Pack 2)
Intel Xeon CPU 5120 @ 1.86GHz 512MB RAM
IIS 6.0.3790.4735
Microsoft FastCGI Handler x86 6.1.36.1
Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) Standard Edition
PHP 5.3.3 NTS VC9
Microsoft SQL Server Driver for PHP 1.1.428.1
FreeTDS 0.82 + post 0.82 patches
Microsoft Windows Cache Extension for PHP 1.1.630.0

maximaxi

maximaxi

2010-10-29 05:30

reporter   ~0027193

Is this the output AFTER your changes in the php files?

If this is not the case, it seems that they have fixed some bugs (this bug?)
because these constraint modifications

ALTER TABLE mantis_bug_table DROP CONSTRAINT DFmantis_bu_last34C8D9D1;

were not existent in 1.2.0rc2.

Maybe we should give it a try again?

DiamondEagle: Would you please answer my question concerning the php modifications.

Greetings

maxi

DiamondEagle

DiamondEagle

2010-10-30 18:08

reporter   ~0027203

The output that I posted is the output that I get AFTER I have applied tomkraw1's changes, i.e. the DropDefaultValue, AlterColumnSQL, DropColumnSQL functions.

Before I made those changes I downloaded the latest version of Mantis and extracted it to a test direstory. I applied the changes to the php files and then I pointed my web server to this test instance. I made a copy of my production database and pointed this instance of Mantis to the copy of the database. Then I tried doing the upgrade. The result is the errors that I posted previously.

abdzso

abdzso

2010-11-05 01:50

reporter   ~0027258

Could anyone install the latest Mantis to MSSQL Server? Will be ever supported? I don't want upgrade, just a new install, and after I could do it, make the migration myself.

ChrisSchwarz

ChrisSchwarz

2011-01-04 11:31

reporter   ~0027790

hi,
i guess that mssql users are not really important to the mantis people as there is no real support for an upgrade since almost a year now. is there any possibility that this upgrade from 1.1.8 to 1.2.4 or later will be provided soon?
thanks
chris

tomkraw1

tomkraw1

2011-01-05 08:16

reporter   ~0027810

I reviewed MBT 1.2.3, 1.2.4 and included ADODB. It looks my changes for constraints are no longer necesairy.

Without mssqlnative driver I can't install Mantis in my company (http://www.mantisbt.org/bugs/view.php?id=11776).

tomkraw1

tomkraw1

2012-01-08 10:37

reporter   ~0030860

I attached patch file to ADODB v5.14 that fixes the constraints problem.

dregad

dregad

2012-01-08 11:44

developer   ~0030862

Referencing @tomkraw1's Upstream bug report
http://phplens.com/lens/lensforum/msgs.php?id=18422

dregad

dregad

2012-01-19 10:05

developer   ~0030943

adodb 5.15 has just been released, it mentions "Misc fixes for mssqlnative" so maybe you want to re-test

http://phplens.com/lens/lensforum/msgs.php?id=19067

tomkraw1

tomkraw1

2012-01-22 04:36

reporter   ~0030989

adodb 5.15 doesn't have my patch included yet. Maybe it will be included in the next version.
I attached the patch for 5.15 version. It installs and works well on SQL Server 2005 Express and sql server native driver 0011776 .

dregad

dregad

2013-02-18 05:42

developer   ~0035201

Last edited: 2013-02-18 05:50

User jeckyll reported the same issue in 0015454 and followed up on adodb forums with a request for patch [1]. I updated both jeckyll's and tomkraw1's threads on adodb forums.

[1] http://phplens.com/lens/lensforum/msgs.php?id=19342

oort

oort

2014-02-15 16:48

reporter   ~0039418

I was completely unable to install the latest version (1.2.16) on SQL Server by using the installer. Therefore, I installed it on MySQL and then used Microsoft SQL Server Migration Assistant for MySQL to migrate the database to SQL Server.

In case anyone else has the same problem I have attached a database script to create all tables and an Administrator account. The script can be used to create only a new database and not to upgrade an existing one. An existing database can be upgraded by creating a new one and then by using some tool to synchronize the schema.

Attached file name: [Mantis 1.2.16 Database SQL Server.sql]

grangeway

grangeway

2014-02-17 13:21

reporter   ~0039428

Marking as Suspended

MSSQL support is currently known broken. We are going to be replacing the DB Layer in Mantis to fix this properly after the next release.

I'd strongly advise using MYSQL for now.

The new DB layer already contains the appropriate fixes for MS SQL Support.

grangeway

grangeway

2014-03-27 19:12

reporter   ~0039753

MSSQL support is currently known broken. We are going to be replacing the DB Layer in Mantis to fix this properly after the next release.

I'd strongly advise using MYSQL for now.

The new DB layer already contains the appropriate fixes for MS SQL Support.

dregad

dregad

2014-03-30 04:45

developer   ~0039787

It is possible that this issue is resolved with the new version of ADOdb bundled in master branch, but I cannot test as I don't have a MSSQL platform. Could anyone could confirm this with a nightly build ?

grangeway

grangeway

2014-03-30 06:09

reporter   ~0039789

Just as an update, work to support MSSQL via the new DB Layer is at https://github.com/grangeway/mantisbt/tree/dbapi

I'll generate a tarball for testing in a few days, once I've tested oracle,pgsql,mysql,mssql support.

grangeway

grangeway

2014-05-16 15:00

reporter   ~0040332

MantisBT currently supports Mysql and has support for other database engines.

The support for other databases is known to be problematic.

Having implemented the current database layer into Mantis 10 years ago, I'm currently working on replacing the current layer.

If you are interested in using Mantis with non-mysql databases - for example, Oracle, PGSQL or MSSQL, and would be willing to help out testing the new database layer, please drop me an email at paul@mantisforge.org

In the meantime, I'd advise running Mantis with Mysql Only to avoid issues.

Thanks
Paul

dregad

dregad

2015-03-14 14:02

developer   ~0049225

Despite the fact that I am not actually able to test whether the problem is truly resolved since I don't have access to a MSSQL server setup, I'm marking this issue as fixed in 1.3.x on the grounds that the recent updates in ADOdb (which contain many improvements in the MSSQL driver) are likely to address it.

Should the problem persist (using a nightly build or a version > 1.3.0-beta.1), feel free to reopen this issue or to create a new one.