View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0011524 | mantisbt | db mssql | public | 2010-02-19 09:11 | 2016-11-09 07:54 |
Reporter | maximaxi | Assigned To | dregad | ||
Priority | immediate | Severity | block | Reproducibility | always |
Status | closed | Resolution | fixed | ||
Platform | Windows 7 | OS | Windows 7 | OS Version | Windows 7 |
Product Version | 1.2.0rc2 | ||||
Target Version | 1.3.0-beta.2 | Fixed in Version | 1.3.0-beta.2 | ||
Summary | 0011524: 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:
One line above each of these warnings (they appear 14 times) are commands I've never seen before:
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:
The error message looks like this:
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. | ||||
Tags | No tags attached. | ||||
Attached Files | 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; } 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; } 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' " ) ); 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-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) | ||||
related to | 0010742 | closed | dregad | Database query failed. Error received from database was 0000206: Operand type clash: int is incompatible with text for the query |
related to | 0011364 | closed | dhx | on installl.php 1.2.0rc2 SYSTEM WARNING: htmlentities() expects parameter 1 to be string, array given |
has duplicate | 0014095 | closed | dregad | Cannot initialize DB on SQL Server 2008 R2 |
has duplicate | 0015454 | closed | dregad | installation errors with SQLServer |
has duplicate | 0015582 | closed | dregad | Unable to Install on MSSQL (SQL Server 2008) |
related to | 0011776 | closed | dregad | Support for Sql Server Native driver (sqlsrv) |
related to | 0021883 | closed | dregad | MSSQL installation fails with BAD ALTER TABLE error |
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. date_migrate is a function by mantis right? I can't find it... |
|
Same thing with 1.2.0 Final. :( |
|
Hi guys, I am also not able to use Mantis with MS SQL; setup is PHP 5.2.10 on 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 See issue 0010742 for more info. |
|
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. |
|
Hi Guys, |
|
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. / 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: -- changing field type -- adding new default value Now the problem is random name of default value given by SQL Server. <b>My suggestion</b> ALTER TABLE mantis_user_pref_table ADD def_redirect_delay will mean:
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 / 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. |
|
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. |
|
The migrate commands have not been correctly updated (schema.php): They all are missing the install_ prefix. This solves a part of the problem. |
|
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? |
|
The indices problem can be solved by changing the schema. The proposed change has no impact on any of the database engines. 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. |
|
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)
(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. |
|
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. |
|
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:
|
|
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? |
|
I added sql script that may be used for upgrade mantis to newer version. |
|
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? |
|
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. |
|
hey guys, is there any solution for a working update from 1.1.8 to 1.2.2 any time soon? |
|
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? |
|
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! |
|
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...) |
|
All changes should be done in the <mantis_dir>/library/adodb/datadict/datadict-mssqlnative.inc.php or datadict-mssql.inc.php 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. |
|
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 Installing Database Checks Failed... 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 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 Can you determine what is causing this problem? |
|
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. |
|
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 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 ( CREATE UNIQUE INDEX idx_category_project_name ON mantis_category_table (project_id, name); INSERT INTO mantis_category_table ALTER TABLE mantis_bug_table ADD ALTER TABLE mantis_bug_table DROP CONSTRAINT DF__mantis_bucateg32E0915F; ALTER TABLE mantis_bug_table DROP TABLE mantis_project_category_table; ALTER TABLE mantis_project_table ADD DROP INDEX mantis_project_table.idx_project_id; DROP INDEX mantis_config_table.idx_config; INSERT INTO mantis_plugin_table ALTER TABLE mantis_project_table ADD ALTER TABLE mantis_project_hierarchy_table ADD ALTER TABLE mantis_plugin_table ADD ALTER TABLE mantis_project_version_table ADD ALTER TABLE mantis_bug_table ADD ALTER TABLE mantis_custom_field_table ADD CREATE TABLE mantis_bug_revision_table ( 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 ALTER TABLE mantis_bug_table ADD ALTER TABLE mantis_bug_table ADD ALTER TABLE mantis_bug_table DROP CONSTRAINT DFmantis_bu_date33D4B598; ALTER TABLE mantis_bug_table EXEC sp_rename 'mantis_bug_table.date_submitted_int','date_submitted'; ALTER TABLE mantis_bug_table 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 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 ALTER TABLE mantis_bugnote_table ADD ALTER TABLE mantis_bugnote_table DROP CONSTRAINT DFmantis_bu_last4AB81AF0; ALTER TABLE mantis_bugnote_table 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 EXEC sp_rename 'mantis_bugnote_table.date_submitted_int','date_submitted'; ALTER TABLE mantis_bug_file_table ADD ALTER TABLE mantis_bug_file_table DROP CONSTRAINT DFmantis_bu_date15502E78; ALTER TABLE mantis_bug_file_table EXEC sp_rename 'mantis_bug_file_table.date_added_int','date_added'; ALTER TABLE mantis_project_file_table ADD ALTER TABLE mantis_project_file_table DROP CONSTRAINT DFmantis_pr_date08B54D69; ALTER TABLE mantis_project_file_table EXEC sp_rename 'mantis_project_file_table.date_added_int','date_added'; ALTER TABLE mantis_bug_history_table ADD ALTER TABLE mantis_bug_history_table DROP CONSTRAINT DFmantis_bu_date1A14E395; ALTER TABLE mantis_bug_history_table EXEC sp_rename 'mantis_bug_history_table.date_modified_int','date_modified'; ALTER TABLE mantis_user_table ADD ALTER TABLE mantis_user_table ADD ALTER TABLE mantis_user_table DROP CONSTRAINT DFmantis_us_date55F4C372; ALTER TABLE mantis_user_table 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 EXEC sp_rename 'mantis_user_table.last_visit_int','last_visit'; ALTER TABLE mantis_email_table ADD ALTER TABLE mantis_email_table DROP CONSTRAINT DF__mantis_emsubmi625A9A57; ALTER TABLE mantis_email_table EXEC sp_rename 'mantis_email_table.submitted_int','submitted'; ALTER TABLE mantis_tag_table ADD ALTER TABLE mantis_tag_table ADD ALTER TABLE mantis_tag_table DROP CONSTRAINT DFmantis_ta_date690797E6; ALTER TABLE mantis_tag_table 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 EXEC sp_rename 'mantis_tag_table.date_updated_int','date_updated'; ALTER TABLE mantis_bug_tag_table ADD ALTER TABLE mantis_bug_tag_table DROP CONSTRAINT DFmantis_bu_date6FB49575; ALTER TABLE mantis_bug_tag_table EXEC sp_rename 'mantis_bug_tag_table.date_attached_int','date_attached'; ALTER TABLE mantis_tokens_table ADD ALTER TABLE mantis_tokens_table ADD ALTER TABLE mantis_tokens_table EXEC sp_rename 'mantis_tokens_table.timestamp_int','timestamp'; ALTER TABLE mantis_tokens_table EXEC sp_rename 'mantis_tokens_table.expiry_int','expiry'; ALTER TABLE mantis_news_table ADD ALTER TABLE mantis_news_table ADD ALTER TABLE mantis_news_table DROP CONSTRAINT DFmantis_ne_last76969D2E; ALTER TABLE mantis_news_table 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 EXEC sp_rename 'mantis_news_table.date_posted_int','date_posted'; ALTER TABLE mantis_bug_revision_table ADD ALTER TABLE mantis_bug_revision_table 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 ALTER TABLE mantis_project_version_table ADD ALTER TABLE mantis_project_version_table DROP CONSTRAINT DFmantis_pr_date1AD3FDA4; ALTER TABLE mantis_project_version_table EXEC sp_rename 'mantis_project_version_table.date_order_int','date_order'; ALTER TABLE mantis_sponsorship_table ADD ALTER TABLE mantis_sponsorship_table ADD ALTER TABLE mantis_sponsorship_table DROP CONSTRAINT DFmantis_sp_last25518C17; ALTER TABLE mantis_sponsorship_table 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 EXEC sp_rename 'mantis_sponsorship_table.date_submitted_int','date_submitted'; ALTER TABLE mantis_project_file_table ADD ALTER TABLE mantis_bug_file_table ADD ALTER TABLE mantis_custom_field_table DROP CONSTRAINT DF__mantis_cuadvan6383C8BA; ALTER TABLE mantis_custom_field_table ALTER TABLE mantis_user_pref_table DROP CONSTRAINT DF__mantis_usadvan2DE6D218; ALTER TABLE mantis_user_pref_table ALTER TABLE mantis_user_pref_table DROP CONSTRAINT DF__mantis_usadvan2EDAF651; ALTER TABLE mantis_user_pref_table ALTER TABLE mantis_user_pref_table DROP CONSTRAINT DF__mantis_usadvan2FCF1A8A; ALTER TABLE mantis_user_pref_table 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.
Updating Configuration File (config_inc.php) 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) |
|
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?) 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 |
|
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. |
|
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. |
|
hi, |
|
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). |
|
I attached patch file to ADODB v5.14 that fixes the constraints problem. |
|
Referencing @tomkraw1's Upstream bug report |
|
adodb 5.15 has just been released, it mentions "Misc fixes for mssqlnative" so maybe you want to re-test |
|
adodb 5.15 doesn't have my patch included yet. Maybe it will be included in the next version. |
|
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. |
|
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] |
|
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. |
|
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. |
|
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 ? |
|
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. |
|
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 |
|
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. |
|