コード例 #1
0
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;
}
コード例 #2
0
ファイル: upgrade-mysql.php プロジェクト: rkaldung/LimeSurvey
function db_upgrade($oldversion)
{
    /// This function does anything necessary to upgrade
    /// older versions to match current functionality
    global $modifyoutput, $databasename, $databasetabletype, $connect, $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;
        flush();
        @ob_flush();
    }
    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` CHANGE `users_name` `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) {
        //Fixes the collation for the complete DB, tables and columns
        echo "<strong>Attention:</strong>The following upgrades will update your MySQL Database collations. This may take some time.<br />If for any reason you should get a timeout just re-run the upgrade procedure. The updating will continue where it left off.<br /><br />";
        flush();
        @ob_flush();
        fix_mysql_collation();
        modify_database("", "ALTER DATABASE `{$databasename}` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        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` CHANGE `email` `email` VARCHAR(320) NOT NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `adminemail` `adminemail` VARCHAR(320) NOT NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_users` CHANGE `email` `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) {
        //Adds new "public" field
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `printanswers` CHAR(1) default 'N' AFTER allowsave");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `listpublic` CHAR(1) default 'N' AFTER `datecreated`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        upgrade_survey_tables117();
        upgrade_survey_tables118();
        // 119
        modify_database("", "CREATE TABLE `prefix_quota` (\r\n \t\t\t\t            `id` int(11) NOT NULL auto_increment,\r\n  \t\t\t\t\t\t\t`sid` int(11) default NULL,\r\n  \t\t\t\t\t\t\t`qlimit` int(8) default NULL,\r\n  \t\t\t\t\t\t\t`name` varchar(255) collate utf8_unicode_ci default NULL,\r\n  \t\t\t\t\t\t\t`action` int(2) default NULL,\r\n  \t\t\t\t\t\t\t`active` int(1) NOT NULL default '1',\r\n  \t\t\t\t\t\t\tPRIMARY KEY  (`id`)\r\n\t\t\t\t\t\t\t)   CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "CREATE TABLE `prefix_quota_members` (\r\n   \t\t \t\t\t\t   `id` int(11) NOT NULL auto_increment,\r\n\t\t\t\t\t\t   `sid` int(11) default NULL,\r\n  \t\t\t\t\t\t   `qid` int(11) default NULL,\r\n  \t\t\t\t\t\t   `quota_id` int(11) default NULL,\r\n  \t\t\t\t\t\t   `code` varchar(5) collate utf8_unicode_ci default NULL,\r\n  \t\t\t\t\t\t   PRIMARY KEY  (`id`),\r\n  \t\t\t\t\t\t   UNIQUE KEY `sid` (`sid`,`qid`,`quota_id`,`code`)\r\n\t\t\t\t\t\t   )    CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        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 `tokenanswerspersistence` 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_users` ADD `htmleditormode` CHAR(7) default 'default'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        //122
        modify_database("", "CREATE TABLE `prefix_templates_rights` (\r\n\t\t\t\t\t\t   `uid` int(11) 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(1) NOT NULL,\r\n\t\t\t\t\t\t   PRIMARY KEY  (`uid`,`folder`)\r\n\t\t\t\t\t\t   )  CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        @ob_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(11) NOT NULL,\r\n\t\t\t\t\t\t   PRIMARY KEY  (`folder`)\r\n\t\t\t\t\t\t   )  CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        //123
        modify_database("", "ALTER TABLE `prefix_conditions` CHANGE `value` `value` VARCHAR(255) NOT NULL default ''");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_labels` CHANGE `title` `title` text");
        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("", "ALTER TABLE `prefix_users` ADD `superadmin` tinyint(1) NOT NULL default '0'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_users` SET `superadmin`=1 where (create_survey=1 AND create_user=1 AND move_user=1 AND delete_user=1 AND configurator=1)");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_users` DROP COLUMN `move_user`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        //126
        modify_database("", "ALTER TABLE `prefix_questions` ADD `lid1` integer 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 `assessments_idx2` on `prefix_assessments` (`sid`)");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "create index `assessments_idx3` on `prefix_assessments` (`gid`)");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "create index `conditions_idx2` on `prefix_conditions` (`qid`)");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "create index `groups_idx2` on `prefix_groups` (`sid`)");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "create index `questions_idx2` on `prefix_questions` (`sid`)");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "create index `questions_idx3` on `prefix_questions` (`gid`)");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "create index `question_attributes_idx2` on `prefix_question_attributes` (`qid`)");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "create index `quota_idx2` on `prefix_quota` (`sid`)");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "create index `saved_control_idx2` on `prefix_saved_control` (`sid`)");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "create index `user_in_groups_idx1` on `prefix_user_in_groups`  (`ugid`, `uid`)");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "create index `answers_idx2` on `prefix_answers` (`sortorder`)");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "create index `conditions_idx3` on `prefix_conditions` (`cqid`)");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "create index `questions_idx4` on `prefix_questions` (`type`)");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "update `prefix_settings_global` set `stg_value`='127' where stg_name='DBVersion'");
        echo $modifyoutput;
        flush();
        @ob_flush();
    }
    if ($oldversion < 128) {
        //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) {
        //129
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `startdate` DATETIME");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `usestartdate` varchar(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` integer NOT NULL default '1' AFTER `qid`");
        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` varchar(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` varchar(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` blob");
        echo $modifyoutput;
        flush();
        @ob_flush();
        // Add new assessment setting
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `assessments` varchar(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(11) NOT NULL default '0'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_labels` ADD `assessment_value` int(11) 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 SIGNED) where `code` REGEXP '^-?[0-9]+\$'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "update `prefix_labels` set `assessment_value`=CAST(`code` as SIGNED) where `code` REGEXP '^-?[0-9]+\$'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        // 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`=concat(replace(`message`,'/''',''''),'<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
        modify_database("", "ALTER TABLE `prefix_assessments` DROP PRIMARY KEY, ADD PRIMARY KEY  USING BTREE(`id`, `language`)");
        echo $modifyoutput;
        flush();
        @ob_flush();
        // 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
        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 tokens setting
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `usetokens` varchar(1) NOT NULL default 'N'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        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) {
        modify_database("", "ALTER TABLE `prefix_question_attributes` MODIFY `value` text");
        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(1) NOT NULL default '0'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "CREATE TABLE `prefix_quota_languagesettings` (\r\n\t\t\t\t\t\t\t\t         `quotals_id` int(11) NOT NULL auto_increment,\r\n\t\t\t\t\t\t\t\t\t\t `quotals_quota_id` int(11) NOT NULL default '0',\r\n\t\t\t\t\t\t\t\t\t\t `quotals_language` varchar(45) NOT NULL default 'en',\r\n\t\t\t\t\t\t\t\t\t\t `quotals_name` varchar(255) collate utf8_unicode_ci default NULL,\r\n\t\t\t\t\t\t\t\t\t\t `quotals_message` text NOT NULL,\r\n\t\t\t\t\t\t\t\t\t\t `quotals_url` varchar(255),\r\n\t\t\t\t\t\t\t\t\t\t `quotals_urldescrip` varchar(255),\r\n\t\t\t\t\t\t\t\t\t\t PRIMARY KEY (`quotals_id`)\r\n\t\t\t\t\t\t\t\t\t\t )   CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        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(1) NOT NULL default '1'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_users` ADD `dateformat` int(1) 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();
        modify_database("", "ALTER TABLE `prefix_surveys` DROP COLUMN `useexpiry`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` DROP COLUMN `usestartdate`");
        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` CHANGE `code` `code` VARCHAR(11) collate utf8_unicode_ci default 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 DEFAULT NULL");
        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(2) 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` CHANGE `expires` `expires` datetime");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `startdate` `startdate` datetime");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_question_attributes` SET `value`='0' WHERE `value`='false'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_question_attributes` SET `value`='1' WHERE `value`='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' COMMENT 'Saves if user set to use the same default value across languages in default options dialog'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_answers` DROP PRIMARY KEY, ADD PRIMARY KEY (`qid`,`code`,`language`,`scale_id`)");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "CREATE TABLE `prefix_defaultvalues` (\r\n                              `qid` int(11) NOT NULL default '0',\r\n                              `scale_id` int(11) NOT NULL default '0',\r\n                              `sqid` int(11) NOT NULL default '0',\r\n                              `language` varchar(20) NOT NULL,\r\n                              `specialtype` varchar(20) NOT NULL default '',\r\n                              `defaultvalue` text,\r\n                              PRIMARY KEY  (`qid` , `scale_id`, `language`, `specialtype`, `sqid` )\r\n                            )  CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        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();
        modify_database("", "ALTER TABLE `prefix_answers` DROP COLUMN `default_value`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_questions` DROP COLUMN `lid`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_questions` DROP COLUMN `lid1`");
        echo $modifyoutput;
        flush();
        @ob_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 LONGTEXT,\r\n                              PRIMARY KEY ( sesskey ) ,\r\n                              INDEX sess2_expiry( expiry ),\r\n                              INDEX sess2_expireref( expireref ))  CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        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 `savetimings` CHAR(1) NULL default 'N' AFTER `format`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        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(20) 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) default 'Y'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `allowjumps` char(1) default 'N'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `navigationdelay` tinyint(2) default '0'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `nokeyboard` char(1) default 'N'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `alloweditaftercompletion` char(1) default 'N'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "CREATE TABLE `prefix_survey_permissions` (\r\n                                `sid` int(10) unsigned NOT NULL,\r\n                                `uid` int(10) unsigned NOT NULL,\r\n                                `permission` varchar(20) NOT NULL,\r\n                                `create_p` tinyint(1) NOT NULL default '0',\r\n                                `read_p` tinyint(1) NOT NULL default '0',\r\n                                `update_p` tinyint(1) NOT NULL default '0',\r\n                                `delete_p` tinyint(1) NOT NULL default '0',\r\n                                `import_p` tinyint(1) NOT NULL default '0',\r\n                                `export_p` tinyint(1) NOT NULL default '0',\r\n                                PRIMARY KEY (sid, uid, permission)\r\n                            )  CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        upgrade_surveypermissions_table145();
        // drop the old survey rights table
        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\r\n                             (`email_admin_notification_subj`  VARCHAR(255) NULL,\r\n                              `email_admin_notification` TEXT NULL,\r\n                              `email_admin_responses_subj` VARCHAR(255) NULL,\r\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 AFTER `emailresponseto`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        upgrade_survey_table145();
        modify_database("", "ALTER TABLE `prefix_surveys` DROP COLUMN `notification`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_conditions` CHANGE `method` `method` CHAR( 5 ) NOT NULL default '';");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_surveys` set `private`='N' where `private` is NULL;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `private` `anonymized` char(1) collate utf8_unicode_ci NOT NULL default 'N';");
        echo $modifyoutput;
        flush();
        @ob_flush();
        //now we clean up things that were not properly set in previous DB upgrades
        modify_database("", "UPDATE `prefix_answers` SET `answer`='' where `answer` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_answers` CHANGE `answer` `answer` text collate utf8_unicode_ci NOT NULL;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_answers` CHANGE `assessment_value` `assessment_value` int(11) NOT NULL default '0' AFTER `answer`;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_assessments` SET `scope`='' where `scope` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_assessments` CHANGE `scope` `scope` varchar(5) collate utf8_unicode_ci NOT NULL default '';");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_assessments` SET `name`='' where `name` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_assessments` CHANGE `name` `name` text collate utf8_unicode_ci NOT NULL;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_assessments` SET `message`='' where `message` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_assessments` CHANGE `message` `message` text collate utf8_unicode_ci NOT NULL;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_assessments` SET `minimum`='' where `minimum` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_assessments` CHANGE `minimum` `minimum` varchar(50) collate utf8_unicode_ci NOT NULL default '';");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_assessments` SET `maximum`='' where `maximum` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_assessments` CHANGE `maximum` `maximum` varchar(50) collate utf8_unicode_ci NOT NULL default '';");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_assessments` CHANGE `id` `id` int(11) NOT NULL;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_assessments` DROP PRIMARY KEY;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_assessments` ADD PRIMARY KEY (`id`,`language`);");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_assessments` CHANGE `id` `id` int(11) NOT NULL auto_increment;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_conditions` CHANGE `cfieldname` `cfieldname` varchar(50) collate utf8_unicode_ci NOT NULL default '';");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_defaultvalues` CHANGE `specialtype` `specialtype` varchar(20) collate utf8_unicode_ci NOT NULL default '' AFTER `qid`;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_groups` SET `group_name`='' where `group_name` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_groups` CHANGE `group_name` `group_name` varchar(100) collate utf8_unicode_ci NOT NULL default '';");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_labels` SET `code`='' where `code` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_labels` CHANGE `code` `code` varchar(5) collate utf8_unicode_ci NOT NULL default '';");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_labels` CHANGE `language` `language` varchar(20) collate utf8_unicode_ci NOT NULL default 'en' AFTER `assessment_value`;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_labelsets` SET `label_name`='' WHERE `label_name` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_labelsets` CHANGE `label_name` `label_name` varchar(100) collate utf8_unicode_ci NOT NULL default '';");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_questions` CHANGE `parent_qid` `parent_qid` int(11) NOT NULL default '0' AFTER `qid`;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_questions` SET `type`='T' where `type` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_questions` CHANGE `type` `type` char(1) collate utf8_unicode_ci NOT NULL default 'T';");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_questions` SET `title`='' where `type` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_questions` CHANGE `title` `title` varchar(20) collate utf8_unicode_ci NOT NULL default '';");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_questions` SET `question`='' where `question` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_questions` CHANGE `question` `question` text collate utf8_unicode_ci NOT NULL;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_questions` SET `other`='N' where `other` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_questions` CHANGE `other` `other` char(1) collate utf8_unicode_ci NOT NULL default 'N';");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_questions` CHANGE `mandatory` `mandatory` char(1) collate utf8_unicode_ci default NULL;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_question_attributes` CHANGE `attribute` `attribute` varchar(50) collate utf8_unicode_ci default NULL;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_quota` CHANGE `qlimit` `qlimit` int(8) default NULL AFTER `name`;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_saved_control` SET `identifier`='' where `identifier` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_saved_control` CHANGE `identifier` `identifier` text collate utf8_unicode_ci NOT NULL;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_saved_control` SET `access_code`='' where `access_code` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_saved_control` CHANGE `access_code` `access_code` text collate utf8_unicode_ci NOT NULL;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_saved_control` CHANGE `email` `email` varchar(320) collate utf8_unicode_ci default NULL;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_saved_control` SET `ip`='' where `ip` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_saved_control` CHANGE `ip` `ip` text collate utf8_unicode_ci NOT NULL;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_saved_control` SET `saved_thisstep`='' where `access_code` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_saved_control` CHANGE `saved_thisstep` `saved_thisstep` text collate utf8_unicode_ci NOT NULL;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_saved_control` SET `status`='' where `access_code` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_saved_control` CHANGE `status` `status` char(1) collate utf8_unicode_ci NOT NULL default '';");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_saved_control` SET `saved_date`='0000-00-00 00:00:00' where `saved_date` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_saved_control` CHANGE `saved_date` `saved_date` datetime NOT NULL;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_settings_global` SET `stg_value`='' where `stg_value` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_settings_global` CHANGE `stg_value` `stg_value` varchar(255) collate utf8_unicode_ci NOT NULL default ''");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `admin` `admin` varchar(50) collate utf8_unicode_ci default NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_surveys` SET `active`='N' where `active` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `active` `active` char(1) collate utf8_unicode_ci NOT NULL default 'N';");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `startdate` `startdate` datetime default NULL AFTER `expires`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `adminemail` `adminemail` varchar(320) collate utf8_unicode_ci default NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `anonymized` `anonymized` char(1) collate utf8_unicode_ci NOT NULL default 'N'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `faxto` `faxto` varchar(20) collate utf8_unicode_ci default NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `format` `format` char(1) collate utf8_unicode_ci default NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `language` `language` varchar(50) collate utf8_unicode_ci default NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `additional_languages` `additional_languages` varchar(255) collate utf8_unicode_ci default NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `printanswers` `printanswers` char(1) collate utf8_unicode_ci default 'N' AFTER `allowprev`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `publicstatistics` `publicstatistics` char(1) collate utf8_unicode_ci default 'N' after `datecreated`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `publicgraphs` `publicgraphs` char(1) collate utf8_unicode_ci default 'N' AFTER `publicstatistics`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `assessments` `assessments` char(1) collate utf8_unicode_ci default 'N' AFTER `tokenanswerspersistence`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `usetokens` `usetokens` char(1) collate utf8_unicode_ci default 'N' AFTER `usecaptcha`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `bounce_email` `bounce_email` varchar(320) collate utf8_unicode_ci default NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `tokenlength` `tokenlength` tinyint(2) default '15'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_surveys_languagesettings` SET `surveyls_title`='' where `surveyls_title` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys_languagesettings` CHANGE `surveyls_title` `surveyls_title` varchar(200) collate utf8_unicode_ci NOT NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys_languagesettings` CHANGE `surveyls_endtext` `surveyls_endtext` text collate utf8_unicode_ci AFTER `surveyls_welcometext`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys_languagesettings` CHANGE `surveyls_url` `surveyls_url` varchar(255) collate utf8_unicode_ci default NULL   AFTER `surveyls_endtext`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys_languagesettings` CHANGE `surveyls_urldescription` `surveyls_urldescription` varchar(255) collate utf8_unicode_ci default NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys_languagesettings` CHANGE `surveyls_email_invite_subj` `surveyls_email_invite_subj` varchar(255) collate utf8_unicode_ci default NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys_languagesettings` CHANGE `surveyls_email_remind_subj` `surveyls_email_remind_subj` varchar(255) collate utf8_unicode_ci default NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys_languagesettings` CHANGE `surveyls_email_register_subj` `surveyls_email_register_subj` varchar(255) collate utf8_unicode_ci default NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys_languagesettings` CHANGE `surveyls_email_confirm_subj` `surveyls_email_confirm_subj` varchar(255) collate utf8_unicode_ci default NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys_languagesettings` CHANGE `surveyls_dateformat` `surveyls_dateformat` int(10) unsigned NOT NULL default '1'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_users` SET `users_name`='' where `users_name` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_users` CHANGE `users_name` `users_name` varchar(64) collate utf8_unicode_ci NOT NULL default ''");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_users` SET `full_name`='' where `full_name` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_users` CHANGE `full_name` `full_name` varchar(50) collate utf8_unicode_ci NOT NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_users` CHANGE `lang` `lang` varchar(20) collate utf8_unicode_ci default NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_users` CHANGE `email` `email` varchar(320) collate utf8_unicode_ci default NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_users` CHANGE `superadmin` `superadmin` tinyint(1) NOT NULL default '0' AFTER `delete_user`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_users` CHANGE `htmleditormode` `htmleditormode` varchar(7) collate utf8_unicode_ci default 'default'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_users` CHANGE `dateformat` `dateformat` int(10) unsigned NOT NULL default '1'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_users` DROP INDEX `email`;");
        modify_database("", "UPDATE `prefix_user_groups` SET `name`='' where `name` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_user_groups` CHANGE `name` `name` varchar(20) collate utf8_unicode_ci NOT NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_user_groups` SET `description`='' where `description` is null;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_user_groups` CHANGE `description` `description` text collate utf8_unicode_ci NOT NULL");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_user_in_groups` DROP INDEX `user_in_groups_idx1`");
        // Don't show output because this key might not exist
        modify_database("", "ALTER TABLE `prefix_user_in_groups` ADD PRIMARY KEY (`ugid`, `uid`)");
        // Don't show output because this might already be set
        modify_database("", "ALTER TABLE  `prefix_surveys_languagesettings` ADD  `surveyls_numberformat` int(11) NOT NULL DEFAULT 0 AFTER  `surveyls_dateformat`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "CREATE TABLE `prefix_failed_login_attempts` (\r\n                              `id` int(11) NOT NULL AUTO_INCREMENT,\r\n                              `ip` varchar(37) NOT NULL,\r\n                              `last_attempt` varchar(20) NOT NULL,\r\n                              `number_attempts` int(11) NOT NULL,\r\n                              PRIMARY KEY (`id`)\r\n                            )  CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        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();
        // Fix permissions for new feature quick-translation
        modify_database("", "INSERT into prefix_survey_permissions (sid,`uid`,permission,`read_p`,`update_p`) SELECT sid,owner_id,'translations','1','1' from prefix_surveys");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_settings_global` SET `stg_value`='146' WHERE stg_name='DBVersion'");
        echo $modifyoutput;
        flush();
        @ob_flush();
    }
    if ($oldversion < 147) {
        modify_database("", "ALTER TABLE `prefix_users` ADD `templateeditormode` VARCHAR( 7 )NOT NULL DEFAULT 'default' AFTER `htmleditormode`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_users` ADD `questionselectormode` VARCHAR( 7 )NOT NULL DEFAULT 'default' AFTER `templateeditormode`");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_settings_global` SET `stg_value`='147' WHERE stg_name='DBVersion'");
        echo $modifyoutput;
        flush();
        @ob_flush();
    }
    if ($oldversion < 148) {
        modify_database("", "ALTER TABLE `prefix_users` ADD `participant_panel` tinyint(1) NOT NULL default '0'");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "CREATE TABLE `prefix_participants` (`participant_id` varchar(50) NOT NULL,\r\n                                                                `firstname` varchar(40) default NULL,\r\n                                                                `lastname` varchar(40) default NULL,\r\n                                                                `email` varchar(80) collate utf8_unicode_ci default NULL,\r\n                                                                `language` varchar(40) default NULL,\r\n                                                                `blacklisted` varchar(1) NOT NULL,\r\n                                                                `owner_uid` int(20) NOT NULL ,\r\n                                                                PRIMARY KEY  (`participant_id`)\r\n                                                                )   CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "CREATE TABLE `prefix_participant_attribute` (\r\n        `participant_id` varchar(50) NOT NULL,\r\n        `attribute_id` int(11) NOT NULL,\r\n        `value` varchar(50) NOT NULL,\r\n        PRIMARY KEY  (`participant_id`,`attribute_id`)\r\n        )   CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "CREATE TABLE `prefix_participant_attribute_names` (\r\n        `attribute_id` int(11) NOT NULL AUTO_INCREMENT,\r\n        `attribute_type` varchar(4) NOT NULL,\r\n        `visible` char(5) NOT NULL,\r\n        PRIMARY KEY  (`attribute_id`,`attribute_type`)\r\n        )   CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "CREATE TABLE `prefix_participant_attribute_names_lang` (\r\n        `attribute_id` int(11) NOT NULL,\r\n        `attribute_name` varchar(30) NOT NULL,\r\n        `lang` varchar(20) NOT NULL,\r\n        PRIMARY KEY  (`attribute_id`,`lang`)\r\n        )   CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "CREATE TABLE `prefix_participant_attribute_values` (\r\n        `attribute_id` int(11) NOT NULL,\r\n        `value_id` int(11) NOT NULL AUTO_INCREMENT,\r\n        `value` varchar(20) NOT NULL,\r\n        PRIMARY KEY  (`value_id`)\r\n        )   CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "CREATE TABLE `prefix_participant_shares` (\r\n        `participant_id` varchar(50) NOT NULL,\r\n        `share_uid` int(11) NOT NULL,\r\n        `date_added` datetime NOT NULL,\r\n        `can_edit` varchar(5) NOT NULL,\r\n        PRIMARY KEY  (`participant_id`,`share_uid`)\r\n        )   CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "CREATE TABLE `prefix_survey_links` (\r\n        `participant_id` varchar(50) NOT NULL,\r\n        `token_id` int(11) NOT NULL,\r\n        `survey_id` int(11) NOT NULL,\r\n        `date_created` datetime NOT NULL,\r\n        PRIMARY KEY  (`participant_id`,`token_id`,`survey_id`)\r\n        )   CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        // add question_attributes field to assessment table
        modify_database("", "ALTER TABLE `prefix_question_attributes` ADD `language` varchar(20)");
        echo $modifyoutput;
        flush();
        @ob_flush();
        upgrade_question_attributes148();
        fixSubquestions();
        modify_database("", "UPDATE `prefix_settings_global` SET `stg_value`='148' WHERE stg_name='DBVersion'");
        echo $modifyoutput;
        flush();
        @ob_flush();
    }
    if ($oldversion < 149) {
        modify_database("", "CREATE TABLE `prefix_survey_url_parameters` (\r\n        `id` int(9) NOT NULL AUTO_INCREMENT,\r\n        `sid` int(10) NOT NULL,\r\n        `parameter` varchar(50) NOT NULL,\r\n        `targetqid` int(10) NULL,\r\n        `targetsqid` int(10) NULL,\r\n        PRIMARY KEY (`id`)\r\n        ) ENGINE=MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "update `prefix_settings_global` set `stg_value`='149' where stg_name='DBVersion'");
        echo $modifyoutput;
        flush();
        @ob_flush();
    }
    if ($oldversion < 150) {
        modify_database("", "ALTER TABLE `prefix_questions` ADD `relevance` TEXT;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "update `prefix_settings_global` set `stg_value`='150' where stg_name='DBVersion'");
        echo $modifyoutput;
        flush();
        @ob_flush();
    }
    if ($oldversion < 151) {
        modify_database("", "ALTER TABLE `prefix_groups` ADD `randomization_group` VARCHAR(20) NOT NULL DEFAULT '';");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "update `prefix_settings_global` set `stg_value`='151' where stg_name='DBVersion'");
        echo $modifyoutput;
        flush();
        @ob_flush();
    }
    if ($oldversion < 152) {
        modify_database("", "CREATE INDEX `question_attributes_idx3` ON `prefix_question_attributes` (`attribute`);");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "update `prefix_settings_global` set `stg_value`='152' where stg_name='DBVersion'");
        echo $modifyoutput;
        flush();
        @ob_flush();
    }
    if ($oldversion < 153) {
        modify_database("", "CREATE TABLE `prefix_expression_errors` (\r\n        `id` int(9) NOT NULL AUTO_INCREMENT,\r\n        `errortime` varchar(50) DEFAULT NULL,\r\n        `sid` int(11) DEFAULT NULL,\r\n        `gid` int(11) DEFAULT NULL,\r\n        `qid` int(11) DEFAULT NULL,\r\n        `gseq` int(11) DEFAULT NULL,\r\n        `qseq` int(11) DEFAULT NULL,\r\n        `type` varchar(50) ,\r\n        `eqn` text,\r\n        `prettyprint` text,\r\n        PRIMARY KEY (`id`)\r\n        ) ENGINE=MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "update `prefix_settings_global` set `stg_value`='153' where stg_name='DBVersion'");
        echo $modifyoutput;
        flush();
        @ob_flush();
    }
    if ($oldversion < 154) {
        modify_database("", "ALTER TABLE `prefix_groups` ADD `grelevance` text DEFAULT NULL;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        LimeExpressionManager::UpgradeConditionsToRelevance();
        modify_database("", "update `prefix_settings_global` set `stg_value`='154' where stg_name='DBVersion'");
        echo $modifyoutput;
        flush();
        @ob_flush();
    }
    if ($oldversion < 155) {
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `googleanalyticsstyle` char(1) DEFAULT NULL;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `googleanalyticsapikey` varchar(25) DEFAULT NULL;");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `showXquestions` `showxquestions` char(1) collate utf8_unicode_ci NOT NULL default 'Y';");
        echo $modifyoutput;
        flush();
        @ob_flush();
        modify_database("", "UPDATE `prefix_settings_global` SET stg_value='155' WHERE stg_name='DBVersion'");
        echo $modifyoutput;
        flush();
        ob_flush();
    }
    fixLanguageConsistencyAllSurveys();
    echo '<br /><br />' . sprintf($clang->gT('Database update finished (%s)'), date('Y-m-d H:i:s')) . '<br />';
    return true;
}
コード例 #3
0
function db_upgrade($oldversion)
{
    /// This function does anything necessary to upgrade
    /// older versions to match current functionality
    global $modifyoutput, $databasename, $databasetabletype;
    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;
        flush();
    }
    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` CHANGE `users_name` `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) {
        //Fixes the collation for the complete DB, tables and columns
        echo "<strong>Attention:</strong>The following upgrades will update your MySQL Database collations. This may take some time.<br />If for any reason you should get a timeout just re-run the upgrade procedure. The updating will continue where it left off.<br /><br />";
        flush();
        fix_mysql_collation();
        modify_database("", "ALTER DATABASE `{$databasename}` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        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` CHANGE `email` `email` VARCHAR(320) NOT NULL");
        echo $modifyoutput;
        flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `adminemail` `adminemail` VARCHAR(320) NOT NULL");
        echo $modifyoutput;
        flush();
        modify_database("", "ALTER TABLE `prefix_users` CHANGE `email` `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) {
        //Adds new "public" field
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `printanswers` CHAR(1) default 'N' AFTER allowsave");
        echo $modifyoutput;
        flush();
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `listpublic` CHAR(1) default 'N' AFTER `datecreated`");
        echo $modifyoutput;
        flush();
        upgrade_survey_tables117();
        upgrade_survey_tables118();
        // 119
        modify_database("", "CREATE TABLE `prefix_quota` (\r\n \t\t\t\t            `id` int(11) NOT NULL auto_increment,\r\n  \t\t\t\t\t\t\t`sid` int(11) default NULL,\r\n  \t\t\t\t\t\t\t`qlimit` int(8) default NULL,\r\n  \t\t\t\t\t\t\t`name` varchar(255) collate utf8_unicode_ci default NULL,\r\n  \t\t\t\t\t\t\t`action` int(2) default NULL,\r\n  \t\t\t\t\t\t\t`active` int(1) NOT NULL default '1',\r\n  \t\t\t\t\t\t\tPRIMARY KEY  (`id`)\r\n\t\t\t\t\t\t\t)  ENGINE={$databasetabletype} CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        modify_database("", "CREATE TABLE `prefix_quota_members` (\r\n   \t\t \t\t\t\t   `id` int(11) NOT NULL auto_increment,\r\n\t\t\t\t\t\t   `sid` int(11) default NULL,\r\n  \t\t\t\t\t\t   `qid` int(11) default NULL,\r\n  \t\t\t\t\t\t   `quota_id` int(11) default NULL,\r\n  \t\t\t\t\t\t   `code` varchar(5) collate utf8_unicode_ci default NULL,\r\n  \t\t\t\t\t\t   PRIMARY KEY  (`id`),\r\n  \t\t\t\t\t\t   UNIQUE KEY `sid` (`sid`,`qid`,`quota_id`,`code`)\r\n\t\t\t\t\t\t   )   ENGINE={$databasetabletype} CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        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 `tokenanswerspersistence` 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_users` ADD `htmleditormode` CHAR(7) default 'default'");
        echo $modifyoutput;
        flush();
        //122
        modify_database("", "CREATE TABLE `prefix_templates_rights` (\r\n\t\t\t\t\t\t   `uid` int(11) 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(1) NOT NULL,\r\n\t\t\t\t\t\t   PRIMARY KEY  (`uid`,`folder`)\r\n\t\t\t\t\t\t   ) ENGINE={$databasetabletype} CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        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(11) NOT NULL,\r\n\t\t\t\t\t\t   PRIMARY KEY  (`folder`)\r\n\t\t\t\t\t\t   ) ENGINE={$databasetabletype} CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        echo $modifyoutput;
        flush();
        //123
        modify_database("", "ALTER TABLE `prefix_conditions` CHANGE `value` `value` VARCHAR(255) NOT NULL default ''");
        echo $modifyoutput;
        flush();
        modify_database("", "ALTER TABLE `prefix_labels` CHANGE `title` `title` text");
        echo $modifyoutput;
        flush();
        //124
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `bounce_email` text");
        echo $modifyoutput;
        flush();
        //125
        upgrade_token_tables125();
        modify_database("", "ALTER TABLE `prefix_users` ADD `superadmin` tinyint(1) NOT NULL default '0'");
        echo $modifyoutput;
        flush();
        modify_database("", "UPDATE `prefix_users` SET `superadmin`=1 where (create_survey=1 AND create_user=1 AND move_user=1 AND delete_user=1 AND configurator=1)");
        echo $modifyoutput;
        flush();
        modify_database("", "ALTER TABLE `prefix_users` DROP COLUMN `move_user`");
        echo $modifyoutput;
        flush();
        //126
        modify_database("", "ALTER TABLE `prefix_questions` ADD `lid1` integer 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 `assessments_idx2` on `prefix_assessments` (`sid`)");
        echo $modifyoutput;
        flush();
        modify_database("", "create index `assessments_idx3` on `prefix_assessments` (`gid`)");
        echo $modifyoutput;
        flush();
        modify_database("", "create index `conditions_idx2` on `prefix_conditions` (`qid`)");
        echo $modifyoutput;
        flush();
        modify_database("", "create index `groups_idx2` on `prefix_groups` (`sid`)");
        echo $modifyoutput;
        flush();
        modify_database("", "create index `questions_idx2` on `prefix_questions` (`sid`)");
        echo $modifyoutput;
        flush();
        modify_database("", "create index `questions_idx3` on `prefix_questions` (`gid`)");
        echo $modifyoutput;
        flush();
        modify_database("", "create index `question_attributes_idx2` on `prefix_question_attributes` (`qid`)");
        echo $modifyoutput;
        flush();
        modify_database("", "create index `quota_idx2` on `prefix_quota` (`sid`)");
        echo $modifyoutput;
        flush();
        modify_database("", "create index `saved_control_idx2` on `prefix_saved_control` (`sid`)");
        echo $modifyoutput;
        flush();
        modify_database("", "create index `user_in_groups_idx1` on `prefix_user_in_groups`  (`ugid`, `uid`)");
        echo $modifyoutput;
        flush();
        modify_database("", "create index `answers_idx2` on `prefix_answers` (`sortorder`)");
        echo $modifyoutput;
        flush();
        modify_database("", "create index `conditions_idx3` on `prefix_conditions` (`cqid`)");
        echo $modifyoutput;
        flush();
        modify_database("", "create index `questions_idx4` on `prefix_questions` (`type`)");
        echo $modifyoutput;
        flush();
        modify_database("", "update `prefix_settings_global` set `stg_value`='127' where stg_name='DBVersion'");
        echo $modifyoutput;
        flush();
    }
    if ($oldversion < 128) {
        //128
        upgrade_token_tables128();
        modify_database("", "update `prefix_settings_global` set `stg_value`='128' where stg_name='DBVersion'");
        echo $modifyoutput;
        flush();
    }
    if ($oldversion < 129) {
        //129
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `startdate` DATETIME");
        echo $modifyoutput;
        flush();
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `usestartdate` varchar(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` integer NOT NULL default '1' AFTER `qid`");
        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` varchar(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` varchar(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` blob");
        echo $modifyoutput;
        flush();
        // Add new assessment setting
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `assessments` varchar(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(11) NOT NULL default '0'");
        echo $modifyoutput;
        flush();
        modify_database("", "ALTER TABLE `prefix_labels` ADD `assessment_value` int(11) 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 SIGNED) where `code` REGEXP '^-?[0-9]+\$'");
        echo $modifyoutput;
        flush();
        modify_database("", "update `prefix_labels` set `assessment_value`=CAST(`code` as SIGNED) where `code` REGEXP '^-?[0-9]+\$'");
        echo $modifyoutput;
        flush();
        // 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`=concat(replace(`message`,'/''',''''),'<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
        modify_database("", "ALTER TABLE `prefix_assessments` DROP PRIMARY KEY, ADD PRIMARY KEY  USING BTREE(`id`, `language`)");
        echo $modifyoutput;
        flush();
        //finally fix missing translations for assessments
        upgrade_survey_tables133();
        // 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` text");
        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
        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 tokens setting
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `usetokens` varchar(1) NOT NULL default 'N'");
        echo $modifyoutput;
        flush();
        modify_database("", "ALTER TABLE `prefix_surveys` ADD `attributedescriptions` TEXT;");
        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) {
        modify_database("", "ALTER TABLE `prefix_question_attributes` MODIFY `value` text");
        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(1) NOT NULL default '0'");
        echo $modifyoutput;
        flush();
        modify_database("", "CREATE TABLE `prefix_quota_languagesettings` (\r\n\t\t\t\t\t\t\t\t         `quotals_id` int(11) NOT NULL auto_increment,\r\n\t\t\t\t\t\t\t\t\t\t `quotals_quota_id` int(11) NOT NULL default '0',\r\n\t\t\t\t\t\t\t\t\t\t `quotals_language` varchar(45) NOT NULL default 'en',\r\n\t\t\t\t\t\t\t\t\t\t `quotals_name` varchar(255) collate utf8_unicode_ci default NULL,\r\n\t\t\t\t\t\t\t\t\t\t `quotals_message` text NOT NULL,\r\n\t\t\t\t\t\t\t\t\t\t `quotals_url` varchar(255),\r\n\t\t\t\t\t\t\t\t\t\t `quotals_urldescrip` varchar(255),\r\n\t\t\t\t\t\t\t\t\t\t PRIMARY KEY (`quotals_id`)\r\n\t\t\t\t\t\t\t\t\t\t )  ENGINE={$databasetabletype} CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        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(1) NOT NULL default '1'");
        echo $modifyoutput;
        flush();
        modify_database("", "ALTER TABLE `prefix_users` ADD `dateformat` int(1) 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();
        modify_database("", "ALTER TABLE `prefix_surveys` DROP COLUMN `useexpiry`");
        echo $modifyoutput;
        flush();
        modify_database("", "ALTER TABLE `prefix_surveys` DROP COLUMN `usestartdate`");
        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` CHANGE `code` `code` VARCHAR(11) collate utf8_unicode_ci default 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` text DEFAULT NULL");
        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(2) 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` CHANGE `expires` `expires` datetime");
        echo $modifyoutput;
        flush();
        modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `startdate` `startdate` datetime");
        echo $modifyoutput;
        flush();
        modify_database("", "UPDATE `prefix_question_attributes` SET `value`='0' WHERE `value`='false'");
        echo $modifyoutput;
        flush();
        modify_database("", "UPDATE `prefix_question_attributes` SET `value`='1' WHERE `value`='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' COMMENT 'Saves if user set to use the same default value across languages in default options dialog'");
        echo $modifyoutput;
        flush();
        modify_database("", "ALTER TABLE `prefix_answers` DROP PRIMARY KEY, ADD PRIMARY KEY (`qid`,`code`,`language`,`scale_id`)");
        echo $modifyoutput;
        flush();
        modify_database("", "CREATE TABLE `prefix_defaultvalues` (\r\n                              `qid` int(11) NOT NULL default '0',\r\n                              `scale_id` int(11) NOT NULL default '0',\r\n                              `sqid` int(11) NOT NULL default '0',\r\n                              `language` varchar(20) NOT NULL,\r\n                              `specialtype` varchar(20) NOT NULL default '',\r\n                              `defaultvalue` text,\r\n                              PRIMARY KEY  (`qid` , `scale_id`, `language`, `specialtype`, `sqid` )\r\n                            ) ENGINE={$databasetabletype} CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
        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();
        modify_database("", "ALTER TABLE `prefix_answers` DROP COLUMN `default_value`");
        echo $modifyoutput;
        flush();
        modify_database("", "ALTER TABLE `prefix_questions` DROP COLUMN `lid`");
        echo $modifyoutput;
        flush();
        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 LONGTEXT,\r\n                              PRIMARY KEY ( sesskey ) ,\r\n                              INDEX sess2_expiry( expiry ),\r\n                              INDEX sess2_expireref( expireref ))");
        echo $modifyoutput;
        flush();
        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;
}
コード例 #4
0
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;
}