function db_upgrade($oldversion)
{
/// This function does anything necessary to upgrade
/// older versions to match current functionality
global $modifyoutput, $dbprefix, $clang;
if ($oldversion < 111) {
// Language upgrades from version 110 to 111 since the language names did change
$oldnewlanguages = array('german_informal' => 'german-informal', 'cns' => 'cn-Hans', 'cnt' => 'cn-Hant', 'pt_br' => 'pt-BR', 'gr' => 'el', 'jp' => 'ja', 'si' => 'sl', 'se' => 'sv', 'vn' => 'vi');
foreach ($oldnewlanguages as $oldlang => $newlang) {
modify_database("", "update [prefix_answers] set [language]='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_questions] set [language]='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_groups] set [language]='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_labels] set [language]='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_surveys] set [language]='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_surveys_languagesettings] set [surveyls_language]='{$newlang}' where surveyls_language='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_users] set [lang]='{$newlang} where lang='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
}
$resultdata = db_execute_assoc("select * from " . db_table_name("labelsets"));
while ($datarow = $resultdata->FetchRow()) {
$toreplace = $datarow['languages'];
$toreplace = str_replace('german_informal', 'german-informal', $toreplace);
$toreplace = str_replace('cns', 'cn-Hans', $toreplace);
$toreplace = str_replace('cnt', 'cn-Hant', $toreplace);
$toreplace = str_replace('pt_br', 'pt-BR', $toreplace);
$toreplace = str_replace('gr', 'el', $toreplace);
$toreplace = str_replace('jp', 'ja', $toreplace);
$toreplace = str_replace('si', 'sl', $toreplace);
$toreplace = str_replace('se', 'sv', $toreplace);
$toreplace = str_replace('vn', 'vi', $toreplace);
modify_database("", "update [prefix_labelsets] set [languages`='{$toreplace}' where lid=" . $datarow['lid']);
echo $modifyoutput;
flush();
ob_flush();
}
$resultdata = db_execute_assoc("select * from " . db_table_name("surveys"));
while ($datarow = $resultdata->FetchRow()) {
$toreplace = $datarow['additional_languages'];
$toreplace = str_replace('german_informal', 'german-informal', $toreplace);
$toreplace = str_replace('cns', 'cn-Hans', $toreplace);
$toreplace = str_replace('cnt', 'cn-Hant', $toreplace);
$toreplace = str_replace('pt_br', 'pt-BR', $toreplace);
$toreplace = str_replace('gr', 'el', $toreplace);
$toreplace = str_replace('jp', 'ja', $toreplace);
$toreplace = str_replace('si', 'sl', $toreplace);
$toreplace = str_replace('se', 'sv', $toreplace);
$toreplace = str_replace('vn', 'vi', $toreplace);
modify_database("", "update [prefix_surveys] set [additional_languages`='{$toreplace}' where sid=" . $datarow['sid']);
echo $modifyoutput;
flush();
ob_flush();
}
modify_database("", "update [prefix_settings_global] set [stg_value]='111' where stg_name='DBVersion'");
echo $modifyoutput;
}
if ($oldversion < 112) {
//The size of the users_name field is now 64 char (20 char before version 112)
modify_database("", "ALTER TABLE [prefix_users] ALTER COLUMN [users_name] VARCHAR( 64 ) NOT NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='112' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 113) {
//No action needed
modify_database("", "update [prefix_settings_global] set [stg_value]='113' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 114) {
modify_database("", "ALTER TABLE [prefix_saved_control] ALTER COLUMN [email] VARCHAR(320) NOT NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ALTER COLUMN [adminemail] VARCHAR(320) NOT NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_users] ALTER COLUMN [email] VARCHAR(320) NOT NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", 'INSERT INTO [prefix_settings_global] VALUES (\'SessionName\', \'$sessionname\');');
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='114' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 126) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD [printanswers] CHAR(1) DEFAULT 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD [listpublic] CHAR(1) DEFAULT 'N'");
echo $modifyoutput;
flush();
ob_flush();
upgrade_survey_tables117();
upgrade_survey_tables118();
//119
modify_database("", "CREATE TABLE [prefix_quota] (\n\t\t\t\t\t\t [id] int NOT NULL IDENTITY (1,1),\n\t\t\t\t\t\t [sid] int,\n\t\t\t\t\t\t [name] varchar(255) ,\n\t\t\t\t\t\t [qlimit] int ,\n\t\t\t\t\t\t [action] int ,\n\t\t\t\t\t\t [active] int NOT NULL default '1',\n\t\t\t\t\t\t PRIMARY KEY ([id])\n\t\t\t\t\t\t);");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_quota_members] (\n\t\t\t\t\t\t [id] int NOT NULL IDENTITY (1,1),\n\t\t\t\t\t\t [sid] int ,\n\t\t\t\t\t\t [qid] int ,\n\t\t\t\t\t\t [quota_id] int ,\n\t\t\t\t\t\t [code] varchar(5) ,\n\t\t\t\t\t\t PRIMARY KEY ([id])\n\t\t\t\t\t\t);");
echo $modifyoutput;
flush();
ob_flush();
// Rename Norwegian language code from NO to NB
$oldnewlanguages = array('no' => 'nb');
foreach ($oldnewlanguages as $oldlang => $newlang) {
modify_database("", "update [prefix_answers] set [language]='{$newlang}' where [language]='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_questions] set [language]='{$newlang}' where [language]='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_groups] set [language]='{$newlang}' where [language]='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_labels] set [language]='{$newlang}' where [language]='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_surveys] set [language]='{$newlang}' where [language]='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_surveys_languagesettings] set [surveyls_language]='{$newlang}' where surveyls_language='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_users] set [lang]='{$newlang}' where lang='{$oldlang}'");
echo $modifyoutput;
flush();
ob_flush();
}
$resultdata = db_execute_assoc("select * from " . db_table_name("labelsets"));
while ($datarow = $resultdata->FetchRow()) {
$toreplace = $datarow['languages'];
$toreplace2 = str_replace('no', 'nb', $toreplace);
if ($toreplace2 != $toreplace) {
modify_database("", "update [prefix_labelsets] set [languages]='{$toreplace}' where lid=" . $datarow['lid']);
echo $modifyoutput;
flush();
ob_flush();
}
}
$resultdata = db_execute_assoc("select * from " . db_table_name("surveys"));
while ($datarow = $resultdata->FetchRow()) {
$toreplace = $datarow['additional_languages'];
$toreplace2 = str_replace('no', 'nb', $toreplace);
if ($toreplace2 != $toreplace) {
modify_database("", "update [prefix_surveys] set [additional_languages]='{$toreplace}' where sid=" . $datarow['sid']);
echo $modifyoutput;
flush();
ob_flush();
}
}
modify_database("", "ALTER TABLE [prefix_surveys] ADD [htmlemail] CHAR(1) DEFAULT 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD [usecaptcha] CHAR(1) DEFAULT 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD [tokenanswerspersistence] CHAR(1) DEFAULT 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_users] ADD [htmleditormode] CHAR(7) DEFAULT 'default'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_templates_rights] (\n\t\t\t\t\t\t [uid] int NOT NULL,\n\t\t\t\t\t\t [folder] varchar(255) NOT NULL,\n\t\t\t\t\t\t [use] int NOT NULL,\n\t\t\t\t\t\t PRIMARY KEY ([uid],[folder])\n\t\t\t\t\t\t );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_templates] (\n\t\t\t\t\t\t [folder] varchar(255) NOT NULL,\n\t\t\t\t\t\t [creator] int NOT NULL,\n\t\t\t\t\t\t PRIMARY KEY ([folder])\n\t\t\t\t\t\t );");
echo $modifyoutput;
flush();
ob_flush();
//123
modify_database("", "ALTER TABLE [prefix_conditions] ALTER COLUMN [value] VARCHAR(255)");
echo $modifyoutput;
flush();
ob_flush();
mssql_drop_constraint('title', 'labels');
modify_database("", "ALTER TABLE [prefix_labels] ALTER COLUMN [title] varchar(4000)");
echo $modifyoutput;
flush();
ob_flush();
//124
modify_database("", "ALTER TABLE [prefix_surveys] ADD [bounce_email] text");
echo $modifyoutput;
flush();
ob_flush();
//125
upgrade_token_tables125();
modify_database("", "EXEC sp_rename 'prefix_users.move_user','superadmin'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_users] SET [superadmin]=1 where ([create_survey]=1 AND [create_user]=1 AND [delete_user]=1 AND [configurator]=1)");
echo $modifyoutput;
flush();
ob_flush();
//126
modify_database("", "ALTER TABLE [prefix_questions] ADD [lid1] int NOT NULL DEFAULT '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_conditions] SET [method]='==' where ( [method] is null) or [method]='' or [method]='0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='126' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 127) {
modify_database("", "create index [answers_idx2] on [prefix_answers] ([sortorder])");
echo $modifyoutput;
modify_database("", "create index [assessments_idx2] on [prefix_assessments] ([sid])");
echo $modifyoutput;
modify_database("", "create index [assessments_idx3] on [prefix_assessments] ([gid])");
echo $modifyoutput;
modify_database("", "create index [conditions_idx2] on [prefix_conditions] ([qid])");
echo $modifyoutput;
modify_database("", "create index [conditions_idx3] on [prefix_conditions] ([cqid])");
echo $modifyoutput;
modify_database("", "create index [groups_idx2] on [prefix_groups] ([sid])");
echo $modifyoutput;
modify_database("", "create index [question_attributes_idx2] on [prefix_question_attributes] ([qid])");
echo $modifyoutput;
modify_database("", "create index [questions_idx2] on [prefix_questions] ([sid])");
echo $modifyoutput;
modify_database("", "create index [questions_idx3] on [prefix_questions] ([gid])");
echo $modifyoutput;
modify_database("", "create index [questions_idx4] on [prefix_questions] ([type])");
echo $modifyoutput;
modify_database("", "create index [quota_idx2] on [prefix_quota] ([sid])");
echo $modifyoutput;
modify_database("", "create index [saved_control_idx2] on [prefix_saved_control] ([sid])");
echo $modifyoutput;
modify_database("", "create index [user_in_groups_idx1] on [prefix_user_in_groups] ([ugid], [uid])");
echo $modifyoutput;
modify_database("", "update [prefix_settings_global] set [stg_value]='127' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 128) {
upgrade_token_tables128();
modify_database("", "update [prefix_settings_global] set [stg_value]='128' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 129) {
//128
modify_database("", "ALTER TABLE [prefix_surveys] ADD [startdate] DATETIME");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD [usestartdate] char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='129' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 130) {
modify_database("", "ALTER TABLE [prefix_conditions] ADD [scenario] int NOT NULL DEFAULT '1'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_conditions] SET [scenario]=1 where ( [scenario] is null) or [scenario]='' or [scenario]=0");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='130' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 131) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD [publicstatistics] char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='131' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 132) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD [publicgraphs] char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='132' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 133) {
modify_database("", "ALTER TABLE [prefix_users] ADD [one_time_pw] text");
echo $modifyoutput;
flush();
ob_flush();
// Add new assessment setting
modify_database("", "ALTER TABLE [prefix_surveys] ADD [assessments] char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
// add new assessment value fields to answers & labels
modify_database("", "ALTER TABLE [prefix_answers] ADD [assessment_value] int NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_labels] ADD [assessment_value] int NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
// copy any valid codes from code field to assessment field
modify_database("", "update [prefix_answers] set [assessment_value]=CAST([code] as int)");
// no output here is intended
modify_database("", "update [prefix_labels] set [assessment_value]=CAST([code] as int)");
// no output here is intended
// activate assessment where assesment rules exist
modify_database("", "update [prefix_surveys] set [assessments]='Y' where [sid] in (SELECT [sid] FROM [prefix_assessments] group by [sid])");
echo $modifyoutput;
flush();
ob_flush();
// add language field to assessment table
modify_database("", "ALTER TABLE [prefix_assessments] ADD [language] varchar(20) NOT NULL default 'en'");
echo $modifyoutput;
flush();
ob_flush();
// update language field with default language of that particular survey
modify_database("", "update [prefix_assessments] set [language]=(select [language] from [prefix_surveys] where [sid]=[prefix_assessments].[sid])");
echo $modifyoutput;
flush();
ob_flush();
// copy assessment link to message since from now on we will have HTML assignment messages
modify_database("", "update [prefix_assessments] set [message]=cast([message] as varchar) +'<br /><a href=\"'+[link]+'\">'+[link]+'</a>'");
echo $modifyoutput;
flush();
ob_flush();
// drop the old link field
modify_database("", "ALTER TABLE [prefix_assessments] DROP COLUMN [link]");
echo $modifyoutput;
flush();
ob_flush();
// change the primary index to include language
// and fix missing translations for assessments
upgrade_survey_tables133a();
// Add new fields to survey language settings
modify_database("", "ALTER TABLE [prefix_surveys_languagesettings] ADD [surveyls_url] varchar(255)");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys_languagesettings] ADD [surveyls_endtext] text");
echo $modifyoutput;
flush();
ob_flush();
// copy old URL fields ot language specific entries
modify_database("", "update [prefix_surveys_languagesettings] set [surveyls_url]=(select [url] from [prefix_surveys] where [sid]=[prefix_surveys_languagesettings].[surveyls_survey_id])");
echo $modifyoutput;
flush();
ob_flush();
// drop old URL field
mssql_drop_constraint('url', 'surveys');
modify_database("", "ALTER TABLE [prefix_surveys] DROP COLUMN [url]");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='133' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 134) {
// Add new assessment setting
modify_database("", "ALTER TABLE [prefix_surveys] ADD [usetokens] char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
mssql_drop_constraint('attribute1', 'surveys');
mssql_drop_constraint('attribute2', 'surveys');
modify_database("", "ALTER TABLE [prefix_surveys] ADD [attributedescriptions] TEXT;");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] DROP COLUMN [attribute1]");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] DROP COLUMN [attribute2]");
echo $modifyoutput;
flush();
ob_flush();
upgrade_token_tables134();
modify_database("", "update [prefix_settings_global] set [stg_value]='134' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 135) {
mssql_drop_constraint('value', 'question_attributes');
modify_database("", "ALTER TABLE [prefix_question_attributes] ALTER COLUMN [value] text");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_answers] ALTER COLUMN [answer] varchar(8000)");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='135' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 136) {
modify_database("", "ALTER TABLE[prefix_quota] ADD [autoload_url] int NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_quota_languagesettings] (\n \t\t\t\t\t\t\t[quotals_id] int NOT NULL IDENTITY (1,1),\n\t\t\t\t\t\t\t[quotals_quota_id] int,\n \t\t\t\t\t\t\t[quotals_language] varchar(45) NOT NULL default 'en',\n \t\t\t\t\t\t\t[quotals_name] varchar(255),\n \t\t\t\t\t\t\t[quotals_message] text,\n \t\t\t\t\t\t\t[quotals_url] varchar(255),\n \t\t\t\t\t\t\t[quotals_urldescrip] varchar(255),\n \t\t\t\t\t\t\tPRIMARY KEY ([quotals_id])\n\t\t\t\t\t\t\t);");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='136' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 137) {
modify_database("", "ALTER TABLE [prefix_surveys_languagesettings] ADD [surveyls_dateformat] int NOT NULL default '1'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_users] ADD [dateformat] int NOT NULL default '1'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_surveys] set startdate=null where usestartdate='N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_surveys] set expires=null where useexpiry='N'");
echo $modifyoutput;
flush();
ob_flush();
mssql_drop_constraint('usestartdate', 'surveys');
mssql_drop_constraint('useexpiry', 'surveys');
modify_database("", "ALTER TABLE [prefix_surveys] DROP COLUMN usestartdate");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] DROP COLUMN useexpiry");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='137' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 138) {
modify_database("", "ALTER TABLE [prefix_quota_members] ALTER COLUMN [code] VARCHAR(11) NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_settings_global] SET [stg_value]='138' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 139) {
upgrade_survey_tables139();
modify_database("", "UPDATE [prefix_settings_global] SET [stg_value]='139' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 140) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD [emailresponseto] text");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_settings_global] SET [stg_value]='140' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 141) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD [tokenlength] tinyint NOT NULL default '15'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_settings_global] SET [stg_value]='141' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 142) {
upgrade_question_attributes142();
modify_database("", "ALTER TABLE [prefix_surveys] ALTER COLUMN [startdate] datetime NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ALTER COLUMN [expires] datetime NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_question_attributes] SET [value]='0' WHERE cast([value] as varchar)='false'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_question_attributes] SET [value]='1' WHERE cast([value] as varchar)='true'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE [prefix_settings_global] SET [stg_value]='142' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 143) {
modify_database("", "ALTER TABLE [prefix_questions] ADD parent_qid integer NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_answers] ADD scale_id tinyint NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_questions] ADD scale_id tinyint NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_questions] ADD same_default tinyint NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
mssql_drop_primary_index('answers');
modify_database("", "ALTER TABLE [prefix_answers] ADD CONSTRAINT pk_answers_qcls PRIMARY KEY ([qid],[code],[language],[scale_id])");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_defaultvalues] (\n [qid] integer NOT NULL default '0',\n [scale_id] tinyint NOT NULL default '0',\n [sqid] integer NOT NULL default '0',\n [language] varchar(20) NOT NULL,\n [specialtype] varchar(20) NOT NULL default '',\n [defaultvalue] text,\n CONSTRAINT pk_defaultvalues_qlss PRIMARY KEY ([qid] , [scale_id], [language], [specialtype], [sqid]))");
echo $modifyoutput;
flush();
ob_flush();
// -Move all 'answers' that are subquestions to the questions table
// -Move all 'labels' that are answers to the answers table
// -Transscribe the default values where applicable
// -Move default values from answers to questions
upgrade_tables143();
mssql_drop_constraint('default_value', 'answers');
modify_database("", "ALTER TABLE [prefix_answers] DROP COLUMN [default_value]");
echo $modifyoutput;
flush();
ob_flush();
mssql_drop_constraint('lid', 'questions');
modify_database("", "ALTER TABLE [prefix_questions] DROP COLUMN lid");
echo $modifyoutput;
flush();
ob_flush();
mssql_drop_constraint('lid1', 'questions');
modify_database("", "ALTER TABLE [prefix_questions] DROP COLUMN lid1");
echo $modifyoutput;
flush();
ob_flush();
// add field for timings and table for extended conditions
modify_database("", "ALTER TABLE [prefix_surveys] ADD savetimings char(1) default 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE prefix_sessions(\n sesskey VARCHAR( 64 ) NOT NULL DEFAULT '',\n expiry DATETIME NOT NULL ,\n expireref VARCHAR( 250 ) DEFAULT '',\n created DATETIME NOT NULL ,\n modified DATETIME NOT NULL ,\n sessdata text,\n CONSTRAINT pk_sessions_sesskey PRIMARY KEY ( [sesskey] ))");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "create index [idx_expiry] on [prefix_sessions] ([expiry])");
echo $modifyoutput;
modify_database("", "create index [idx_expireref] on [prefix_sessions] ([expireref])");
echo $modifyoutput;
modify_database("", "UPDATE [prefix_settings_global] SET stg_value='143' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 145) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD showXquestions CHAR(1) NULL default 'Y'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD showgroupinfo CHAR(1) NULL default 'B'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD shownoanswer CHAR(1) NULL default 'Y'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD showqnumcode CHAR(1) NULL default 'X'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD bouncetime BIGINT NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD bounceprocessing VARCHAR(1) NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD bounceaccounttype VARCHAR(4) NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD bounceaccounthost VARCHAR(200) NULL ");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD bounceaccountpass VARCHAR(100) NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD bounceaccountencryption VARCHAR(3) NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD bounceaccountuser VARCHAR(200) NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD showwelcome CHAR(1) NULL default 'Y'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD showprogress CHAR(1) NULL default 'Y'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD allowjumps CHAR(1) NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD navigationdelay tinyint NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD nokeyboard CHAR(1) NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD alloweditaftercompletion CHAR(1) NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_survey_permissions] (\n [sid] INT NOT NULL,\n [uid] INT NOT NULL,\n [permission] VARCHAR(20) NOT NULL,\n [create_p] TINYINT NOT NULL default '0',\n [read_p] TINYINT NOT NULL default '0',\n [update_p] TINYINT NOT NULL default '0',\n [delete_p] TINYINT NOT NULL default '0',\n [import_p] TINYINT NOT NULL default '0',\n [export_p] TINYINT NOT NULL default '0',\n PRIMARY KEY ([sid], [uid],[permission])\n );");
echo $modifyoutput;
flush();
ob_flush();
upgrade_surveypermissions_table145();
modify_database("", "DROP TABLE [prefix_surveys_rights]");
echo $modifyoutput;
flush();
ob_flush();
// Add new fields for email templates
modify_database("", "ALTER TABLE prefix_surveys_languagesettings ADD\n email_admin_notification_subj VARCHAR(255) NULL,\n email_admin_notification TEXT NULL,\n email_admin_responses_subj VARCHAR(255) NULL,\n email_admin_responses TEXT NULL");
//Add index to questions table to speed up subquestions
modify_database("", "create index [parent_qid_idx] on [prefix_questions] ([parent_qid])");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_surveys ADD emailnotificationto text DEFAULT NULL");
echo $modifyoutput;
flush();
ob_flush();
upgrade_survey_table145();
mssql_drop_constraint('notification', 'surveys');
modify_database("", "ALTER TABLE [prefix_surveys] DROP COLUMN [notification]");
echo $modifyoutput;
flush();
ob_flush();
// modify length of method in conditions
modify_database("", "ALTER TABLE [prefix_conditions] ALTER COLUMN [method] CHAR( 5 ) NOT NULL");
echo $modifyoutput;
flush();
ob_flush();
//Add index to questions table to speed up subquestions
modify_database("", "create index [parent_qid] on [prefix_questions] ([parent_qid])");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE prefix_surveys set [private]='N' where [private] is NULL;");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "EXEC sp_rename 'prefix_surveys.private','anonymized'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys] ALTER COLUMN [anonymized] char(1) NOT NULL;");
echo $modifyoutput;
flush();
ob_flush();
mssql_drop_constraint('anonymized', 'surveys');
modify_database("", "ALTER TABLE [prefix_surveys] ADD CONSTRAINT DF_surveys_anonymized DEFAULT 'N' FOR [anonymized];");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE [prefix_failed_login_attempts] (\n [id] INT NOT NULL IDENTITY (1,1) PRIMARY KEY,\n [ip] varchar(37) NOT NULL,\n [last_attempt] varchar(20) NOT NULL,\n [number_attempts] int NOT NULL );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_surveys_languagesettings] ADD [surveyls_numberformat] INT default 0 NOT NULL");
echo $modifyoutput;
flush();
ob_flush();
upgrade_token_tables145();
modify_database("", "UPDATE [prefix_settings_global] SET stg_value='145' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
if ($oldversion < 146) {
upgrade_timing_tables146();
modify_database("", "UPDATE [prefix_settings_global] SET stg_value='146' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
echo '<br /><br />' . sprintf($clang->gT('Database update finished (%s)'), date('Y-m-d H:i:s')) . '<br />';
return true;
}
function db_upgrade($oldversion)
{
/// This function does anything necessary to upgrade
/// older versions to match current functionality
global $modifyoutput, $dbprefix;
if ($oldversion < 111) {
// Language upgrades from version 110 to 111 since the language names did change
$oldnewlanguages = array('german_informal' => 'german-informal', 'cns' => 'cn-Hans', 'cnt' => 'cn-Hant', 'pt_br' => 'pt-BR', 'gr' => 'el', 'jp' => 'ja', 'si' => 'sl', 'se' => 'sv', 'vn' => 'vi');
foreach ($oldnewlanguages as $oldlang => $newlang) {
modify_database("", "update [prefix_answers] set [language]='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_questions] set [language]='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_groups] set [language]='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_labels] set [language]='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_surveys] set [language]='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_surveys_languagesettings] set [surveyls_language]='{$newlang}' where surveyls_language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_users] set [lang]='{$newlang} where lang='{$oldlang}'");
echo $modifyoutput;
flush();
}
$resultdata = db_execute_assoc("select * from " . db_table_name("labelsets"));
while ($datarow = $resultdata->FetchRow()) {
$toreplace = $datarow['languages'];
$toreplace = str_replace('german_informal', 'german-informal', $toreplace);
$toreplace = str_replace('cns', 'cn-Hans', $toreplace);
$toreplace = str_replace('cnt', 'cn-Hant', $toreplace);
$toreplace = str_replace('pt_br', 'pt-BR', $toreplace);
$toreplace = str_replace('gr', 'el', $toreplace);
$toreplace = str_replace('jp', 'ja', $toreplace);
$toreplace = str_replace('si', 'sl', $toreplace);
$toreplace = str_replace('se', 'sv', $toreplace);
$toreplace = str_replace('vn', 'vi', $toreplace);
modify_database("", "update [prefix_labelsets] set [languages`='{$toreplace}' where lid=" . $datarow['lid']);
echo $modifyoutput;
flush();
}
$resultdata = db_execute_assoc("select * from " . db_table_name("surveys"));
while ($datarow = $resultdata->FetchRow()) {
$toreplace = $datarow['additional_languages'];
$toreplace = str_replace('german_informal', 'german-informal', $toreplace);
$toreplace = str_replace('cns', 'cn-Hans', $toreplace);
$toreplace = str_replace('cnt', 'cn-Hant', $toreplace);
$toreplace = str_replace('pt_br', 'pt-BR', $toreplace);
$toreplace = str_replace('gr', 'el', $toreplace);
$toreplace = str_replace('jp', 'ja', $toreplace);
$toreplace = str_replace('si', 'sl', $toreplace);
$toreplace = str_replace('se', 'sv', $toreplace);
$toreplace = str_replace('vn', 'vi', $toreplace);
modify_database("", "update [prefix_surveys] set [additional_languages`='{$toreplace}' where sid=" . $datarow['sid']);
echo $modifyoutput;
flush();
}
modify_database("", "update [prefix_settings_global] set [stg_value]='111' where stg_name='DBVersion'");
echo $modifyoutput;
}
if ($oldversion < 112) {
//The size of the users_name field is now 64 char (20 char before version 112)
modify_database("", "ALTER TABLE [prefix_users] ALTER COLUMN [users_name] VARCHAR( 64 ) NOT NULL");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='112' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 113) {
//No action needed
modify_database("", "update [prefix_settings_global] set [stg_value]='113' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 114) {
modify_database("", "ALTER TABLE [prefix_saved_control] ALTER COLUMN [email] VARCHAR(320) NOT NULL");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_surveys] ALTER COLUMN [adminemail] VARCHAR(320) NOT NULL");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_users] ALTER COLUMN [email] VARCHAR(320) NOT NULL");
echo $modifyoutput;
flush();
modify_database("", 'INSERT INTO [prefix_settings_global] VALUES (\'SessionName\', \'$sessionname\');');
echo $modifyoutput;
flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='114' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 126) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD [printanswers] CHAR(1) DEFAULT 'N'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD [listpublic] CHAR(1) DEFAULT 'N'");
echo $modifyoutput;
flush();
upgrade_survey_tables117();
upgrade_survey_tables118();
//119
modify_database("", "CREATE TABLE [prefix_quota] (\r\n\t\t\t\t\t\t [id] int NOT NULL IDENTITY (1,1),\r\n\t\t\t\t\t\t [sid] int,\r\n\t\t\t\t\t\t [name] varchar(255) ,\r\n\t\t\t\t\t\t [qlimit] int ,\r\n\t\t\t\t\t\t [action] int ,\r\n\t\t\t\t\t\t [active] int NOT NULL default '1',\r\n\t\t\t\t\t\t PRIMARY KEY ([id])\r\n\t\t\t\t\t\t);");
echo $modifyoutput;
flush();
modify_database("", "CREATE TABLE [prefix_quota_members] (\r\n\t\t\t\t\t\t [id] int NOT NULL IDENTITY (1,1),\r\n\t\t\t\t\t\t [sid] int ,\r\n\t\t\t\t\t\t [qid] int ,\r\n\t\t\t\t\t\t [quota_id] int ,\r\n\t\t\t\t\t\t [code] varchar(5) ,\r\n\t\t\t\t\t\t PRIMARY KEY ([id])\r\n\t\t\t\t\t\t);");
echo $modifyoutput;
flush();
// Rename Norwegian language code from NO to NB
$oldnewlanguages = array('no' => 'nb');
foreach ($oldnewlanguages as $oldlang => $newlang) {
modify_database("", "update [prefix_answers] set [language]='{$newlang}' where [language]='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_questions] set [language]='{$newlang}' where [language]='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_groups] set [language]='{$newlang}' where [language]='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_labels] set [language]='{$newlang}' where [language]='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_surveys] set [language]='{$newlang}' where [language]='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_surveys_languagesettings] set [surveyls_language]='{$newlang}' where surveyls_language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_users] set [lang]='{$newlang}' where lang='{$oldlang}'");
echo $modifyoutput;
flush();
}
$resultdata = db_execute_assoc("select * from " . db_table_name("labelsets"));
while ($datarow = $resultdata->FetchRow()) {
$toreplace = $datarow['languages'];
$toreplace2 = str_replace('no', 'nb', $toreplace);
if ($toreplace2 != $toreplace) {
modify_database("", "update [prefix_labelsets] set [languages]='{$toreplace}' where lid=" . $datarow['lid']);
echo $modifyoutput;
flush();
}
}
$resultdata = db_execute_assoc("select * from " . db_table_name("surveys"));
while ($datarow = $resultdata->FetchRow()) {
$toreplace = $datarow['additional_languages'];
$toreplace2 = str_replace('no', 'nb', $toreplace);
if ($toreplace2 != $toreplace) {
modify_database("", "update [prefix_surveys] set [additional_languages]='{$toreplace}' where sid=" . $datarow['sid']);
echo $modifyoutput;
flush();
}
}
modify_database("", "ALTER TABLE [prefix_surveys] ADD [htmlemail] CHAR(1) DEFAULT 'N'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD [usecaptcha] CHAR(1) DEFAULT 'N'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD [tokenanswerspersistence] CHAR(1) DEFAULT 'N'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_users] ADD [htmleditormode] CHAR(7) DEFAULT 'default'");
echo $modifyoutput;
flush();
modify_database("", "CREATE TABLE [prefix_templates_rights] (\r\n\t\t\t\t\t\t [uid] int NOT NULL,\r\n\t\t\t\t\t\t [folder] varchar(255) NOT NULL,\r\n\t\t\t\t\t\t [use] int NOT NULL,\r\n\t\t\t\t\t\t PRIMARY KEY ([uid],[folder])\r\n\t\t\t\t\t\t );");
echo $modifyoutput;
flush();
modify_database("", "CREATE TABLE [prefix_templates] (\r\n\t\t\t\t\t\t [folder] varchar(255) NOT NULL,\r\n\t\t\t\t\t\t [creator] int NOT NULL,\r\n\t\t\t\t\t\t PRIMARY KEY ([folder])\r\n\t\t\t\t\t\t );");
echo $modifyoutput;
flush();
//123
modify_database("", "ALTER TABLE [prefix_conditions] ALTER COLUMN [value] VARCHAR(255)");
echo $modifyoutput;
flush();
// There is no other way to remove the previous default value
/*modify_database("","DECLARE @STR VARCHAR(100)
SET @STR = (
SELECT NAME
FROM SYSOBJECTS SO
JOIN SYSCONSTRAINTS SC ON SO.ID = SC.CONSTID
WHERE OBJECT_NAME(SO.PARENT_OBJ) = 'lime_labels'
AND SO.XTYPE = 'D' AND SC.COLID =
(SELECT COLID FROM SYSCOLUMNS WHERE ID = OBJECT_ID('lime_labels') AND NAME = 'title'))
SET @STR = 'ALTER TABLE lime_labels DROP CONSTRAINT ' + @STR
exec (@STR);"); echo $modifyoutput; flush(); */
modify_database("", "ALTER TABLE [prefix_labels] ALTER COLUMN [title] varchar(4000)");
echo $modifyoutput;
flush();
//124
modify_database("", "ALTER TABLE [prefix_surveys] ADD [bounce_email] varchar(max)");
echo $modifyoutput;
flush();
//125
upgrade_token_tables125();
modify_database("", "EXEC sp_rename 'prefix_users.move_user','superadmin'");
echo $modifyoutput;
flush();
modify_database("", "UPDATE [prefix_users] SET [superadmin]=1 where ([create_survey]=1 AND [create_user]=1 AND [delete_user]=1 AND [configurator]=1)");
echo $modifyoutput;
flush();
//126
modify_database("", "ALTER TABLE [prefix_questions] ADD [lid1] int NOT NULL DEFAULT '0'");
echo $modifyoutput;
flush();
modify_database("", "UPDATE [prefix_conditions] SET [method]='==' where ( [method] is null) or [method]='' or [method]='0'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='126' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 127) {
modify_database("", "create index [answers_idx2] on [prefix_answers] ([sortorder])");
echo $modifyoutput;
modify_database("", "create index [assessments_idx2] on [prefix_assessments] ([sid])");
echo $modifyoutput;
modify_database("", "create index [assessments_idx3] on [prefix_assessments] ([gid])");
echo $modifyoutput;
modify_database("", "create index [conditions_idx2] on [prefix_conditions] ([qid])");
echo $modifyoutput;
modify_database("", "create index [conditions_idx3] on [prefix_conditions] ([cqid])");
echo $modifyoutput;
modify_database("", "create index [groups_idx2] on [prefix_groups] ([sid])");
echo $modifyoutput;
modify_database("", "create index [question_attributes_idx2] on [prefix_question_attributes] ([qid])");
echo $modifyoutput;
modify_database("", "create index [questions_idx2] on [prefix_questions] ([sid])");
echo $modifyoutput;
modify_database("", "create index [questions_idx3] on [prefix_questions] ([gid])");
echo $modifyoutput;
modify_database("", "create index [questions_idx4] on [prefix_questions] ([type])");
echo $modifyoutput;
modify_database("", "create index [quota_idx2] on [prefix_quota] ([sid])");
echo $modifyoutput;
modify_database("", "create index [saved_control_idx2] on [prefix_saved_control] ([sid])");
echo $modifyoutput;
modify_database("", "create index [user_in_groups_idx1] on [prefix_user_in_groups] ([ugid], [uid])");
echo $modifyoutput;
modify_database("", "update [prefix_settings_global] set [stg_value]='127' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 128) {
upgrade_token_tables128();
modify_database("", "update [prefix_settings_global] set [stg_value]='128' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 129) {
//128
modify_database("", "ALTER TABLE [prefix_surveys] ADD [startdate] DATETIME");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_surveys] ADD [usestartdate] char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='129' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 130) {
modify_database("", "ALTER TABLE [prefix_conditions] ADD [scenario] int NOT NULL DEFAULT '1'");
echo $modifyoutput;
flush();
modify_database("", "UPDATE [prefix_conditions] SET [scenario]=1 where ( [scenario] is null) or [scenario]='' or [scenario]=0");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='130' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 131) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD [publicstatistics] char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='131' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 132) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD [publicgraphs] char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='132' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 133) {
modify_database("", "ALTER TABLE [prefix_users] ADD [one_time_pw] varchar(max)");
echo $modifyoutput;
flush();
// Add new assessment setting
modify_database("", "ALTER TABLE [prefix_surveys] ADD [assessments] char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
// add new assessment value fields to answers & labels
modify_database("", "ALTER TABLE [prefix_answers] ADD [assessment_value] int NOT NULL default '0'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_labels] ADD [assessment_value] int NOT NULL default '0'");
echo $modifyoutput;
flush();
// copy any valid codes from code field to assessment field
modify_database("", "update [prefix_answers] set [assessment_value]=CAST([code] as int)");
// no output here is intended
modify_database("", "update [prefix_labels] set [assessment_value]=CAST([code] as int)");
// no output here is intended
// activate assessment where assesment rules exist
modify_database("", "update [prefix_surveys] set [assessments]='Y' where [sid] in (SELECT [sid] FROM [prefix_assessments] group by [sid])");
echo $modifyoutput;
flush();
// add language field to assessment table
modify_database("", "ALTER TABLE [prefix_assessments] ADD [language] varchar(20) NOT NULL default 'en'");
echo $modifyoutput;
flush();
// update language field with default language of that particular survey
modify_database("", "update [prefix_assessments] set [language]=(select [language] from [prefix_surveys] where [sid]=[prefix_assessments].[sid])");
echo $modifyoutput;
flush();
// copy assessment link to message since from now on we will have HTML assignment messages
modify_database("", "update [prefix_assessments] set [message]=cast([message] as varchar) +'<br /><a href=\"'+[link]+'\">'+[link]+'</a>'");
echo $modifyoutput;
flush();
// drop the old link field
modify_database("", "ALTER TABLE [prefix_assessments] DROP COLUMN [link]");
echo $modifyoutput;
flush();
// change the primary index to include language
// and fix missing translations for assessments
upgrade_survey_tables133a();
// Add new fields to survey language settings
modify_database("", "ALTER TABLE [prefix_surveys_languagesettings] ADD [surveyls_url] varchar(255)");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_surveys_languagesettings] ADD [surveyls_endtext] varchar(max)");
echo $modifyoutput;
flush();
// copy old URL fields ot language specific entries
modify_database("", "update [prefix_surveys_languagesettings] set [surveyls_url]=(select [url] from [prefix_surveys] where [sid]=[prefix_surveys_languagesettings].[surveyls_survey_id])");
echo $modifyoutput;
flush();
// drop old URL field
mssql_drop_constraint('url', 'surveys');
modify_database("", "ALTER TABLE [prefix_surveys] DROP COLUMN [url]");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='133' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 134) {
// Add new assessment setting
modify_database("", "ALTER TABLE [prefix_surveys] ADD [usetokens] char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
mssql_drop_constraint('attribute1', 'surveys');
mssql_drop_constraint('attribute2', 'surveys');
modify_database("", "ALTER TABLE [prefix_surveys] ADD [attributedescriptions] varchar(max);");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_surveys] DROP COLUMN [attribute1]");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_surveys] DROP COLUMN [attribute2]");
echo $modifyoutput;
flush();
upgrade_token_tables134();
modify_database("", "update [prefix_settings_global] set [stg_value]='134' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 135) {
mssql_drop_constraint('value', 'question_attributes');
modify_database("", "ALTER TABLE [prefix_question_attributes] ALTER COLUMN [value] varchar(max)");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_answers] ALTER COLUMN [answer] varchar(8000)");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='135' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 136) {
modify_database("", "ALTER TABLE[prefix_quota] ADD [autoload_url] int NOT NULL default '0'");
echo $modifyoutput;
flush();
modify_database("", "CREATE TABLE [prefix_quota_languagesettings] (\r\n \t\t\t\t\t\t\t[quotals_id] int NOT NULL IDENTITY (1,1),\r\n\t\t\t\t\t\t\t[quotals_quota_id] int,\r\n \t\t\t\t\t\t\t[quotals_language] varchar(45) NOT NULL default 'en',\r\n \t\t\t\t\t\t\t[quotals_name] varchar(255),\r\n \t\t\t\t\t\t\t[quotals_message] varchar(max),\r\n \t\t\t\t\t\t\t[quotals_url] varchar(255),\r\n \t\t\t\t\t\t\t[quotals_urldescrip] varchar(255),\r\n \t\t\t\t\t\t\tPRIMARY KEY ([quotals_id])\r\n\t\t\t\t\t\t\t);");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='136' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 137) {
modify_database("", "ALTER TABLE [prefix_surveys_languagesettings] ADD [surveyls_dateformat] int NOT NULL default '1'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_users] ADD [dateformat] int NOT NULL default '1'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_surveys] set startdate=null where usestartdate='N'");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_surveys] set expires=null where useexpiry='N'");
echo $modifyoutput;
flush();
mssql_drop_constraint('usestartdate', 'surveys');
mssql_drop_constraint('useexpiry', 'surveys');
modify_database("", "ALTER TABLE [prefix_surveys] DROP COLUMN usestartdate");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_surveys] DROP COLUMN useexpiry");
echo $modifyoutput;
flush();
modify_database("", "update [prefix_settings_global] set [stg_value]='137' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 138) {
modify_database("", "ALTER TABLE [prefix_quota_members] ALTER COLUMN [code] VARCHAR(11) NULL");
echo $modifyoutput;
flush();
modify_database("", "UPDATE [prefix_settings_global] SET [stg_value]='138' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 139) {
upgrade_survey_tables139();
modify_database("", "UPDATE [prefix_settings_global] SET [stg_value]='139' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 140) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD [emailresponseto] varchar(max)");
echo $modifyoutput;
flush();
modify_database("", "UPDATE [prefix_settings_global] SET [stg_value]='140' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 141) {
modify_database("", "ALTER TABLE [prefix_surveys] ADD [tokenlength] tinyint NOT NULL default '15'");
echo $modifyoutput;
flush();
modify_database("", "UPDATE [prefix_settings_global] SET [stg_value]='141' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 142) {
upgrade_question_attributes142();
modify_database("", "ALTER TABLE [prefix_surveys] ALTER COLUMN [startdate] datetime NULL");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_surveys] ALTER COLUMN [expires] datetime NULL");
echo $modifyoutput;
flush();
modify_database("", "UPDATE [prefix_question_attributes] SET [value]='0' WHERE cast([value] as varchar)='false'");
echo $modifyoutput;
flush();
modify_database("", "UPDATE [prefix_question_attributes] SET [value]='1' WHERE cast([value] as varchar)='true'");
echo $modifyoutput;
flush();
modify_database("", "UPDATE [prefix_settings_global] SET [stg_value]='142' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 143) {
modify_database("", "ALTER TABLE [prefix_questions] ADD parent_qid integer NOT NULL default '0'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_answers] ADD scale_id tinyint NOT NULL default '0'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_questions] ADD scale_id tinyint NOT NULL default '0'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE [prefix_questions] ADD same_default tinyint NOT NULL default '0'");
echo $modifyoutput;
flush();
mssql_drop_primary_index('answers');
modify_database("", "ALTER TABLE [prefix_answers] ADD CONSTRAINT pk_answers_qcls PRIMARY KEY ([qid],[code],[language],[scale_id])");
echo $modifyoutput;
flush();
modify_database("", "CREATE TABLE [prefix_defaultvalues] (\r\n [qid] integer NOT NULL default '0',\r\n [scale_id] tinyint NOT NULL default '0',\r\n [sqid] integer NOT NULL default '0',\r\n [language] varchar(20) NOT NULL,\r\n [specialtype] varchar(20) NOT NULL default '',\r\n [defaultvalue] varchar(max),\r\n CONSTRAINT pk_defaultvalues_qlss PRIMARY KEY ([qid] , [scale_id], [language], [specialtype], [sqid]))");
echo $modifyoutput;
flush();
// -Move all 'answers' that are subquestions to the questions table
// -Move all 'labels' that are answers to the answers table
// -Transscribe the default values where applicable
// -Move default values from answers to questions
upgrade_tables143();
mssql_drop_constraint('default_value', 'answers');
modify_database("", "ALTER TABLE [prefix_answers] DROP COLUMN [default_value]");
echo $modifyoutput;
flush();
mssql_drop_constraint('lid', 'questions');
modify_database("", "ALTER TABLE [prefix_questions] DROP COLUMN lid");
echo $modifyoutput;
flush();
mssql_drop_constraint('lid1', 'questions');
modify_database("", "ALTER TABLE [prefix_questions] DROP COLUMN lid1");
echo $modifyoutput;
flush();
modify_database("", "CREATE TABLE prefix_sessions(\r\n sesskey VARCHAR( 64 ) NOT NULL DEFAULT '',\r\n expiry DATETIME NOT NULL ,\r\n expireref VARCHAR( 250 ) DEFAULT '',\r\n created DATETIME NOT NULL ,\r\n modified DATETIME NOT NULL ,\r\n sessdata varchar(max),\r\n CONSTRAINT pk_sessions_sesskey PRIMARY KEY ( [sesskey] ))");
echo $modifyoutput;
flush();
modify_database("", "create index [idx_expiry] on [prefix_sessions] ([expiry])");
echo $modifyoutput;
modify_database("", "create index [idx_expireref] on [prefix_sessions] ([expireref])");
echo $modifyoutput;
modify_database("", "UPDATE [prefix_settings_global] SET stg_value='143' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
echo '<br /><br />Database update finished (' . date('Y-m-d H:i:s') . ')<br />';
return true;
}
