/**
* @param integer $iOldDBVersion
*/
function db_upgrade_all($iOldDBVersion)
{
/// This function does anything necessary to upgrade
/// older versions to match current functionality
global $modifyoutput;
Yii::app()->loadHelper('database');
$sUserTemplateRootDir = Yii::app()->getConfig('usertemplaterootdir');
$sStandardTemplateRootDir = Yii::app()->getConfig('standardtemplaterootdir');
$oDB = Yii::app()->getDb();
Yii::app()->setConfig('Updating', true);
$oDB->schemaCachingDuration = 0;
// Deactivate schema caching
$oTransaction = $oDB->beginTransaction();
try {
if ($iOldDBVersion < 111) {
// Language upgrades from version 110 to 111 because the language names did change
$aOldNewLanguages = 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 ($aOldNewLanguages as $sOldLanguageCode => $sNewLanguageCode) {
alterLanguageCode($sOldLanguageCode, $sNewLanguageCode);
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 111), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 112) {
// New size of the username field (it was previously 20 chars wide)
$oDB->createCommand()->alterColumn('{{users}}', 'users_name', "string(64) NOT NULL");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 112), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 113) {
//Fixes the collation for the complete DB, tables and columns
if (Yii::app()->db->driverName == 'mysql') {
$sDatabaseName = getDBConnectionStringProperty('dbname');
fixMySQLCollations();
modifyDatabase("", "ALTER DATABASE `{$sDatabaseName}` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
echo $modifyoutput;
flush();
@ob_flush();
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 113), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 114) {
$oDB->createCommand()->alterColumn('{{saved_control}}', 'email', "string(320) NOT NULL");
$oDB->createCommand()->alterColumn('{{surveys}}', 'adminemail', "string(320) NOT NULL");
$oDB->createCommand()->alterColumn('{{users}}', 'email', "string(320) NOT NULL");
$oDB->createCommand()->insert('{{settings_global}}', array('stg_name' => 'SessionName', 'stg_value' => randomChars(64, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ!"$%&/()=?`+*~#",;.:abcdefghijklmnopqrstuvwxyz123456789')));
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 114), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 126) {
addColumn('{{surveys}}', 'printanswers', "string(1) default 'N'");
addColumn('{{surveys}}', 'listpublic', "string(1) default 'N'");
upgradeSurveyTables126();
upgradeTokenTables126();
// Create quota table
$oDB->createCommand()->createTable('{{quota}}', array('id' => 'pk', 'sid' => 'integer', 'qlimit' => 'integer', 'name' => 'string', 'action' => 'integer', 'active' => 'integer NOT NULL DEFAULT 1'));
// Create quota_members table
$oDB->createCommand()->createTable('{{quota_members}}', array('id' => 'pk', 'sid' => 'integer', 'qid' => 'integer', 'quota_id' => 'integer', 'code' => 'string(5)'));
$oDB->createCommand()->createIndex('sid', '{{quota_members}}', 'sid,qid,quota_id,code', true);
// Create templates_rights table
$oDB->createCommand()->createTable('{{templates_rights}}', array('uid' => 'integer NOT NULL', 'folder' => 'string NOT NULL', 'use' => 'integer', 'PRIMARY KEY (uid, folder)'));
// Create templates table
$oDB->createCommand()->createTable('{{templates}}', array('folder' => 'string NOT NULL', 'creator' => 'integer NOT NULL', 'PRIMARY KEY (folder)'));
// Rename Norwegian language codes
alterLanguageCode('no', 'nb');
addColumn('{{surveys}}', 'htmlemail', "string(1) default 'N'");
addColumn('{{surveys}}', 'tokenanswerspersistence', "string(1) default 'N'");
addColumn('{{surveys}}', 'usecaptcha', "string(1) default 'N'");
addColumn('{{surveys}}', 'bounce_email', 'text');
addColumn('{{users}}', 'htmleditormode', "string(7) default 'default'");
addColumn('{{users}}', 'superadmin', "integer NOT NULL default '0'");
addColumn('{{questions}}', 'lid1', "integer NOT NULL default '0'");
alterColumn('{{conditions}}', 'value', "string", false, '');
alterColumn('{{labels}}', 'title', "text");
$oDB->createCommand()->update('{{users}}', array('superadmin' => 1), "create_survey=1 AND create_user=1 AND move_user=1 AND delete_user=1 AND configurator=1");
$oDB->createCommand()->update('{{conditions}}', array('method' => '=='), "(method is null) or method='' or method='0'");
dropColumn('{{users}}', 'move_user');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 126), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 127) {
modifyDatabase("", "create index answers_idx2 on {{answers}} (sortorder)");
echo $modifyoutput;
modifyDatabase("", "create index assessments_idx2 on {{assessments}} (sid)");
echo $modifyoutput;
modifyDatabase("", "create index assessments_idx3 on {{assessments}} (gid)");
echo $modifyoutput;
modifyDatabase("", "create index conditions_idx2 on {{conditions}} (qid)");
echo $modifyoutput;
modifyDatabase("", "create index conditions_idx3 on {{conditions}} (cqid)");
echo $modifyoutput;
modifyDatabase("", "create index groups_idx2 on {{groups}} (sid)");
echo $modifyoutput;
modifyDatabase("", "create index question_attributes_idx2 on {{question_attributes}} (qid)");
echo $modifyoutput;
modifyDatabase("", "create index questions_idx2 on {{questions}} (sid)");
echo $modifyoutput;
modifyDatabase("", "create index questions_idx3 on {{questions}} (gid)");
echo $modifyoutput;
modifyDatabase("", "create index questions_idx4 on {{questions}} (type)");
echo $modifyoutput;
modifyDatabase("", "create index quota_idx2 on {{quota}} (sid)");
echo $modifyoutput;
modifyDatabase("", "create index saved_control_idx2 on {{saved_control}} (sid)");
echo $modifyoutput;
modifyDatabase("", "create index user_in_groups_idx1 on {{user_in_groups}} (ugid, uid)");
echo $modifyoutput;
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 127), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 128) {
upgradeTokens128();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 128), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 129) {
addColumn('{{surveys}}', 'startdate', "datetime");
addColumn('{{surveys}}', 'usestartdate', "string(1) NOT NULL default 'N'");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 129), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 130) {
addColumn('{{conditions}}', 'scenario', "integer NOT NULL default '1'");
$oDB->createCommand()->update('{{conditions}}', array('scenario' => '1'), "(scenario is null) or scenario=0");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 130), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 131) {
addColumn('{{surveys}}', 'publicstatistics', "string(1) NOT NULL default 'N'");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 131), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 132) {
addColumn('{{surveys}}', 'publicgraphs', "string(1) NOT NULL default 'N'");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 132), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 133) {
addColumn('{{users}}', 'one_time_pw', 'binary');
// Add new assessment setting
addColumn('{{surveys}}', 'assessments', "string(1) NOT NULL default 'N'");
// add new assessment value fields to answers & labels
addColumn('{{answers}}', 'assessment_value', "integer NOT NULL default '0'");
addColumn('{{labels}}', 'assessment_value', "integer NOT NULL default '0'");
// copy any valid codes from code field to assessment field
switch (Yii::app()->db->driverName) {
case 'mysql':
case 'mysqli':
$oDB->createCommand("UPDATE {{answers}} SET assessment_value=CAST(`code` as SIGNED) where `code` REGEXP '^-?[0-9]+\$'")->execute();
$oDB->createCommand("UPDATE {{labels}} SET assessment_value=CAST(`code` as SIGNED) where `code` REGEXP '^-?[0-9]+\$'")->execute();
// copy assessment link to message since from now on we will have HTML assignment messages
$oDB->createCommand("UPDATE {{assessments}} set message=concat(replace(message,'/''',''''),'<br /><a href=\"',link,'\">',link,'</a>')")->execute();
break;
case 'sqlsrv':
case 'dblib':
case 'mssql':
try {
$oDB->createCommand("UPDATE {{answers}} SET assessment_value=CAST([code] as int) WHERE ISNUMERIC([code])=1")->execute();
$oDB->createCommand("UPDATE {{labels}} SET assessment_value=CAST([code] as int) WHERE ISNUMERIC([code])=1")->execute();
} catch (Exception $e) {
}
// copy assessment link to message since from now on we will have HTML assignment messages
alterColumn('{{assessments}}', 'link', "text", false);
alterColumn('{{assessments}}', 'message', "text", false);
$oDB->createCommand("UPDATE {{assessments}} set message=replace(message,'/''','''')+'<br /><a href=\"'+link+'\">'+link+'</a>'")->execute();
break;
case 'pgsql':
$oDB->createCommand("UPDATE {{answers}} SET assessment_value=CAST(code as integer) where code ~ '^[0-9]+'")->execute();
$oDB->createCommand("UPDATE {{labels}} SET assessment_value=CAST(code as integer) where code ~ '^[0-9]+'")->execute();
// copy assessment link to message since from now on we will have HTML assignment messages
$oDB->createCommand("UPDATE {{assessments}} set message=replace(message,'/''','''')||'<br /><a href=\"'||link||'\">'||link||'</a>'")->execute();
break;
default:
die('Unknown database type');
}
// activate assessment where assessment rules exist
$oDB->createCommand("UPDATE {{surveys}} SET assessments='Y' where sid in (SELECT sid FROM {{assessments}} group by sid)")->execute();
// add language field to assessment table
addColumn('{{assessments}}', 'language', "string(20) NOT NULL default 'en'");
// update language field with default language of that particular survey
$oDB->createCommand("UPDATE {{assessments}} SET language=(select language from {{surveys}} where sid={{assessments}}.sid)")->execute();
// drop the old link field
dropColumn('{{assessments}}', 'link');
// Add new fields to survey language settings
addColumn('{{surveys_languagesettings}}', 'surveyls_url', "string");
addColumn('{{surveys_languagesettings}}', 'surveyls_endtext', 'text');
// copy old URL fields ot language specific entries
$oDB->createCommand("UPDATE {{surveys_languagesettings}} set surveyls_url=(select url from {{surveys}} where sid={{surveys_languagesettings}}.surveyls_survey_id)")->execute();
// drop old URL field
dropColumn('{{surveys}}', 'url');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 133), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 134) {
// Add new tokens setting
addColumn('{{surveys}}', 'usetokens', "string(1) NOT NULL default 'N'");
addColumn('{{surveys}}', 'attributedescriptions', 'text');
dropColumn('{{surveys}}', 'attribute1');
dropColumn('{{surveys}}', 'attribute2');
upgradeTokenTables134();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 134), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 135) {
alterColumn('{{question_attributes}}', 'value', 'text');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 135), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 136) {
addColumn('{{quota}}', 'autoload_url', "integer NOT NULL default 0");
// Create quota table
$aFields = array('quotals_id' => 'pk', 'quotals_quota_id' => 'integer NOT NULL DEFAULT 0', 'quotals_language' => "string(45) NOT NULL default 'en'", 'quotals_name' => 'string', 'quotals_message' => 'text NOT NULL', 'quotals_url' => 'string', 'quotals_urldescrip' => 'string');
$oDB->createCommand()->createTable('{{quota_languagesettings}}', $aFields);
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 136), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 137) {
addColumn('{{surveys_languagesettings}}', 'surveyls_dateformat', "integer NOT NULL default 1");
addColumn('{{users}}', 'dateformat', "integer NOT NULL default 1");
$oDB->createCommand()->update('{{surveys}}', array('startdate' => NULL), "usestartdate='N'");
$oDB->createCommand()->update('{{surveys}}', array('expires' => NULL), "useexpiry='N'");
dropColumn('{{surveys}}', 'useexpiry');
dropColumn('{{surveys}}', 'usestartdate');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 137), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 138) {
alterColumn('{{quota_members}}', 'code', "string(11)");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 138), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 139) {
upgradeSurveyTables139();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 139), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 140) {
addColumn('{{surveys}}', 'emailresponseto', 'text');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 140), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 141) {
addColumn('{{surveys}}', 'tokenlength', 'integer NOT NULL default 15');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 141), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 142) {
upgradeQuestionAttributes142();
$oDB->createCommand()->alterColumn('{{surveys}}', 'expires', "datetime");
$oDB->createCommand()->alterColumn('{{surveys}}', 'startdate', "datetime");
$oDB->createCommand()->update('{{question_attributes}}', array('value' => 0), "value='false'");
$oDB->createCommand()->update('{{question_attributes}}', array('value' => 1), "value='true'");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 142), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 143) {
addColumn('{{questions}}', 'parent_qid', 'integer NOT NULL default 0');
addColumn('{{answers}}', 'scale_id', 'integer NOT NULL default 0');
addColumn('{{questions}}', 'scale_id', 'integer NOT NULL default 0');
addColumn('{{questions}}', 'same_default', 'integer NOT NULL default 0');
dropPrimaryKey('answers');
addPrimaryKey('answers', array('qid', 'code', 'language', 'scale_id'));
$aFields = array('qid' => "integer NOT NULL default 0", 'scale_id' => 'integer NOT NULL default 0', 'sqid' => 'integer NOT NULL default 0', 'language' => 'string(20) NOT NULL', 'specialtype' => "string(20) NOT NULL default ''", 'defaultvalue' => 'text');
$oDB->createCommand()->createTable('{{defaultvalues}}', $aFields);
addPrimaryKey('defaultvalues', array('qid', 'specialtype', 'language', 'scale_id', 'sqid'));
// -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
upgradeTables143();
dropColumn('{{answers}}', 'default_value');
dropColumn('{{questions}}', 'lid');
dropColumn('{{questions}}', 'lid1');
$aFields = array('sesskey' => "string(64) NOT NULL DEFAULT ''", 'expiry' => "datetime NOT NULL", 'expireref' => "string(250) DEFAULT ''", 'created' => "datetime NOT NULL", 'modified' => "datetime NOT NULL", 'sessdata' => 'text');
$oDB->createCommand()->createTable('{{sessions}}', $aFields);
addPrimaryKey('sessions', array('sesskey'));
$oDB->createCommand()->createIndex('sess2_expiry', '{{sessions}}', 'expiry');
$oDB->createCommand()->createIndex('sess2_expireref', '{{sessions}}', 'expireref');
// Move all user templates to the new user template directory
echo "<br>" . sprintf(gT("Moving user templates to new location at %s..."), $sUserTemplateRootDir) . "<br />";
$hTemplateDirectory = opendir($sStandardTemplateRootDir);
$aFailedTemplates = array();
// get each entry
while ($entryName = readdir($hTemplateDirectory)) {
if (!in_array($entryName, array('.', '..', '.svn')) && is_dir($sStandardTemplateRootDir . DIRECTORY_SEPARATOR . $entryName) && !isStandardTemplate($entryName)) {
if (!rename($sStandardTemplateRootDir . DIRECTORY_SEPARATOR . $entryName, $sUserTemplateRootDir . DIRECTORY_SEPARATOR . $entryName)) {
$aFailedTemplates[] = $entryName;
}
}
}
if (count($aFailedTemplates) > 0) {
echo "The following templates at {$sStandardTemplateRootDir} could not be moved to the new location at {$sUserTemplateRootDir}:<br /><ul>";
foreach ($aFailedTemplates as $sFailedTemplate) {
echo "<li>{$sFailedTemplate}</li>";
}
echo "</ul>Please move these templates manually after the upgrade has finished.<br />";
}
// close directory
closedir($hTemplateDirectory);
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 143), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 145) {
addColumn('{{surveys}}', 'savetimings', "string(1) NULL default 'N'");
addColumn('{{surveys}}', 'showXquestions', "string(1) NULL default 'Y'");
addColumn('{{surveys}}', 'showgroupinfo', "string(1) NULL default 'B'");
addColumn('{{surveys}}', 'shownoanswer', "string(1) NULL default 'Y'");
addColumn('{{surveys}}', 'showqnumcode', "string(1) NULL default 'X'");
addColumn('{{surveys}}', 'bouncetime', 'integer');
addColumn('{{surveys}}', 'bounceprocessing', "string(1) NULL default 'N'");
addColumn('{{surveys}}', 'bounceaccounttype', "string(4)");
addColumn('{{surveys}}', 'bounceaccounthost', "string(200)");
addColumn('{{surveys}}', 'bounceaccountpass', "string(100)");
addColumn('{{surveys}}', 'bounceaccountencryption', "string(3)");
addColumn('{{surveys}}', 'bounceaccountuser', "string(200)");
addColumn('{{surveys}}', 'showwelcome', "string(1) default 'Y'");
addColumn('{{surveys}}', 'showprogress', "string(1) default 'Y'");
addColumn('{{surveys}}', 'allowjumps', "string(1) default 'N'");
addColumn('{{surveys}}', 'navigationdelay', "integer default 0");
addColumn('{{surveys}}', 'nokeyboard', "string(1) default 'N'");
addColumn('{{surveys}}', 'alloweditaftercompletion', "string(1) default 'N'");
$aFields = array('sid' => "integer NOT NULL", 'uid' => "integer NOT NULL", 'permission' => 'string(20) NOT NULL', 'create_p' => "integer NOT NULL default 0", 'read_p' => "integer NOT NULL default 0", 'update_p' => "integer NOT NULL default 0", 'delete_p' => "integer NOT NULL default 0", 'import_p' => "integer NOT NULL default 0", 'export_p' => "integer NOT NULL default 0");
$oDB->createCommand()->createTable('{{survey_permissions}}', $aFields);
addPrimaryKey('survey_permissions', array('sid', 'uid', 'permission'));
upgradeSurveyPermissions145();
// drop the old survey rights table
$oDB->createCommand()->dropTable('{{surveys_rights}}');
// Add new fields for email templates
addColumn('{{surveys_languagesettings}}', 'email_admin_notification_subj', "string");
addColumn('{{surveys_languagesettings}}', 'email_admin_responses_subj', "string");
addColumn('{{surveys_languagesettings}}', 'email_admin_notification', "text");
addColumn('{{surveys_languagesettings}}', 'email_admin_responses', "text");
//Add index to questions table to speed up subquestions
$oDB->createCommand()->createIndex('parent_qid_idx', '{{questions}}', 'parent_qid');
addColumn('{{surveys}}', 'emailnotificationto', "text");
upgradeSurveys145();
dropColumn('{{surveys}}', 'notification');
alterColumn('{{conditions}}', 'method', "string(5)", false, '');
$oDB->createCommand()->renameColumn('{{surveys}}', 'private', 'anonymized');
$oDB->createCommand()->update('{{surveys}}', array('anonymized' => 'N'), "anonymized is NULL");
alterColumn('{{surveys}}', 'anonymized', "string(1)", false, 'N');
//now we clean up things that were not properly set in previous DB upgrades
$oDB->createCommand()->update('{{answers}}', array('answer' => ''), "answer is NULL");
$oDB->createCommand()->update('{{assessments}}', array('scope' => ''), "scope is NULL");
$oDB->createCommand()->update('{{assessments}}', array('name' => ''), "name is NULL");
$oDB->createCommand()->update('{{assessments}}', array('message' => ''), "message is NULL");
$oDB->createCommand()->update('{{assessments}}', array('minimum' => ''), "minimum is NULL");
$oDB->createCommand()->update('{{assessments}}', array('maximum' => ''), "maximum is NULL");
$oDB->createCommand()->update('{{groups}}', array('group_name' => ''), "group_name is NULL");
$oDB->createCommand()->update('{{labels}}', array('code' => ''), "code is NULL");
$oDB->createCommand()->update('{{labelsets}}', array('label_name' => ''), "label_name is NULL");
$oDB->createCommand()->update('{{questions}}', array('type' => 'T'), "type is NULL");
$oDB->createCommand()->update('{{questions}}', array('title' => ''), "title is NULL");
$oDB->createCommand()->update('{{questions}}', array('question' => ''), "question is NULL");
$oDB->createCommand()->update('{{questions}}', array('other' => 'N'), "other is NULL");
alterColumn('{{answers}}', 'answer', "text", false);
alterColumn('{{answers}}', 'assessment_value', 'integer', false, '0');
alterColumn('{{assessments}}', 'scope', "string(5)", false, '');
alterColumn('{{assessments}}', 'name', "text", false);
alterColumn('{{assessments}}', 'message', "text", false);
alterColumn('{{assessments}}', 'minimum', "string(50)", false, '');
alterColumn('{{assessments}}', 'maximum', "string(50)", false, '');
// change the primary index to include language
if (Yii::app()->db->driverName == 'mysql') {
modifyPrimaryKey('assessments', array('id', 'language'));
} else {
dropPrimaryKey('assessments');
addPrimaryKey('assessments', array('id', 'language'));
}
alterColumn('{{conditions}}', 'cfieldname', "string(50)", false, '');
dropPrimaryKey('defaultvalues');
alterColumn('{{defaultvalues}}', 'specialtype', "string(20)", false, '');
addPrimaryKey('defaultvalues', array('qid', 'specialtype', 'language', 'scale_id', 'sqid'));
alterColumn('{{groups}}', 'group_name', "string(100)", false, '');
alterColumn('{{labels}}', 'code', "string(5)", false, '');
dropPrimaryKey('labels');
alterColumn('{{labels}}', 'language', "string(20)", false, 'en');
addPrimaryKey('labels', array('lid', 'sortorder', 'language'));
alterColumn('{{labelsets}}', 'label_name', "string(100)", false, '');
alterColumn('{{questions}}', 'parent_qid', 'integer', false, '0');
alterColumn('{{questions}}', 'title', "string(20)", false, '');
alterColumn('{{questions}}', 'question', "text", false);
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('questions_idx4', '{{questions}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{questions}}', 'type', "string(1)", false, 'T');
try {
$oDB->createCommand()->createIndex('questions_idx4', '{{questions}}', 'type');
} catch (Exception $e) {
}
alterColumn('{{questions}}', 'other', "string(1)", false, 'N');
alterColumn('{{questions}}', 'mandatory', "string(1)");
alterColumn('{{question_attributes}}', 'attribute', "string(50)");
alterColumn('{{quota}}', 'qlimit', 'integer');
$oDB->createCommand()->update('{{saved_control}}', array('identifier' => ''), "identifier is NULL");
alterColumn('{{saved_control}}', 'identifier', "text", false);
$oDB->createCommand()->update('{{saved_control}}', array('access_code' => ''), "access_code is NULL");
alterColumn('{{saved_control}}', 'access_code', "text", false);
alterColumn('{{saved_control}}', 'email', "string(320)");
$oDB->createCommand()->update('{{saved_control}}', array('ip' => ''), "ip is NULL");
alterColumn('{{saved_control}}', 'ip', "text", false);
$oDB->createCommand()->update('{{saved_control}}', array('saved_thisstep' => ''), "saved_thisstep is NULL");
alterColumn('{{saved_control}}', 'saved_thisstep', "text", false);
$oDB->createCommand()->update('{{saved_control}}', array('status' => ''), "status is NULL");
alterColumn('{{saved_control}}', 'status', "string(1)", false, '');
$oDB->createCommand()->update('{{saved_control}}', array('saved_date' => '1980-01-01 00:00:00'), "saved_date is NULL");
alterColumn('{{saved_control}}', 'saved_date', "datetime", false);
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => ''), "stg_value is NULL");
alterColumn('{{settings_global}}', 'stg_value', "string", false, '');
alterColumn('{{surveys}}', 'admin', "string(50)");
$oDB->createCommand()->update('{{surveys}}', array('active' => 'N'), "active is NULL");
alterColumn('{{surveys}}', 'active', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'startdate', "datetime");
alterColumn('{{surveys}}', 'adminemail', "string(320)");
alterColumn('{{surveys}}', 'anonymized', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'faxto', "string(20)");
alterColumn('{{surveys}}', 'format', "string(1)");
alterColumn('{{surveys}}', 'language', "string(50)");
alterColumn('{{surveys}}', 'additional_languages', "string");
alterColumn('{{surveys}}', 'printanswers', "string(1)", true, 'N');
alterColumn('{{surveys}}', 'publicstatistics', "string(1)", true, 'N');
alterColumn('{{surveys}}', 'publicgraphs', "string(1)", true, 'N');
alterColumn('{{surveys}}', 'assessments', "string(1)", true, 'N');
alterColumn('{{surveys}}', 'usetokens', "string(1)", true, 'N');
alterColumn('{{surveys}}', 'bounce_email', "string(320)");
alterColumn('{{surveys}}', 'tokenlength', 'integer', true, 15);
$oDB->createCommand()->update('{{surveys_languagesettings}}', array('surveyls_title' => ''), "surveyls_title is NULL");
alterColumn('{{surveys_languagesettings}}', 'surveyls_title', "string(200)", false);
alterColumn('{{surveys_languagesettings}}', 'surveyls_endtext', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_url', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_urldescription', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_invite_subj', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_remind_subj', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_register_subj', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_confirm_subj', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_dateformat', 'integer', false, 1);
$oDB->createCommand()->update('{{users}}', array('users_name' => ''), "users_name is NULL");
$oDB->createCommand()->update('{{users}}', array('full_name' => ''), "full_name is NULL");
alterColumn('{{users}}', 'users_name', "string(64)", false, '');
alterColumn('{{users}}', 'full_name', "string(50)", false);
alterColumn('{{users}}', 'lang', "string(20)");
alterColumn('{{users}}', 'email', "string(320)");
alterColumn('{{users}}', 'superadmin', 'integer', false, 0);
alterColumn('{{users}}', 'htmleditormode', "string(7)", true, 'default');
alterColumn('{{users}}', 'dateformat', 'integer', false, 1);
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('email', '{{users}}');
} catch (Exception $e) {
// do nothing
rollBackToTransactionBookmark();
}
$oDB->createCommand()->update('{{user_groups}}', array('name' => ''), "name is NULL");
$oDB->createCommand()->update('{{user_groups}}', array('description' => ''), "description is NULL");
alterColumn('{{user_groups}}', 'name', "string(20)", false);
alterColumn('{{user_groups}}', 'description', "text", false);
try {
$oDB->createCommand()->dropIndex('user_in_groups_idx1', '{{user_in_groups}}');
} catch (Exception $e) {
}
try {
addPrimaryKey('user_in_groups', array('ugid', 'uid'));
} catch (Exception $e) {
}
addColumn('{{surveys_languagesettings}}', 'surveyls_numberformat', "integer NOT NULL DEFAULT 0");
$oDB->createCommand()->createTable('{{failed_login_attempts}}', array('id' => "pk", 'ip' => 'string(37) NOT NULL', 'last_attempt' => 'string(20) NOT NULL', 'number_attempts' => "integer NOT NULL"));
upgradeTokens145();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 145), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 146) {
upgradeSurveyTimings146();
// Fix permissions for new feature quick-translation
try {
setTransactionBookmark();
$oDB->createCommand("INSERT into {{survey_permissions}} (sid,uid,permission,read_p,update_p) SELECT sid,owner_id,'translations','1','1' from {{surveys}}")->execute();
echo $modifyoutput;
flush();
@ob_flush();
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 146), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 147) {
addColumn('{{users}}', 'templateeditormode', "string(7) NOT NULL default 'default'");
addColumn('{{users}}', 'questionselectormode', "string(7) NOT NULL default 'default'");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 147), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 148) {
addColumn('{{users}}', 'participant_panel', "integer NOT NULL default 0");
$oDB->createCommand()->createTable('{{participants}}', array('participant_id' => 'string(50) NOT NULL', 'firstname' => 'string(40) default NULL', 'lastname' => 'string(40) default NULL', 'email' => 'string(80) default NULL', 'language' => 'string(40) default NULL', 'blacklisted' => 'string(1) NOT NULL', 'owner_uid' => "integer NOT NULL"));
addPrimaryKey('participants', array('participant_id'));
$oDB->createCommand()->createTable('{{participant_attribute}}', array('participant_id' => 'string(50) NOT NULL', 'attribute_id' => "integer NOT NULL", 'value' => 'string(50) NOT NULL'));
addPrimaryKey('participant_attribute', array('participant_id', 'attribute_id'));
$oDB->createCommand()->createTable('{{participant_attribute_names}}', array('attribute_id' => 'autoincrement', 'attribute_type' => 'string(4) NOT NULL', 'visible' => 'string(5) NOT NULL', 'PRIMARY KEY (attribute_id,attribute_type)'));
$oDB->createCommand()->createTable('{{participant_attribute_names_lang}}', array('attribute_id' => 'integer NOT NULL', 'attribute_name' => 'string(30) NOT NULL', 'lang' => 'string(20) NOT NULL'));
addPrimaryKey('participant_attribute_names_lang', array('attribute_id', 'lang'));
$oDB->createCommand()->createTable('{{participant_attribute_values}}', array('attribute_id' => 'integer NOT NULL', 'value_id' => 'pk', 'value' => 'string(20) NOT NULL'));
$oDB->createCommand()->createTable('{{participant_shares}}', array('participant_id' => 'string(50) NOT NULL', 'share_uid' => 'integer NOT NULL', 'date_added' => 'datetime NOT NULL', 'can_edit' => 'string(5) NOT NULL'));
addPrimaryKey('participant_shares', array('participant_id', 'share_uid'));
$oDB->createCommand()->createTable('{{survey_links}}', array('participant_id' => 'string(50) NOT NULL', 'token_id' => 'integer NOT NULL', 'survey_id' => 'integer NOT NULL', 'date_created' => 'datetime NOT NULL'));
addPrimaryKey('survey_links', array('participant_id', 'token_id', 'survey_id'));
// Add language field to question_attributes table
addColumn('{{question_attributes}}', 'language', "string(20)");
upgradeQuestionAttributes148();
fixSubquestions();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 148), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 149) {
$aFields = array('id' => 'integer', 'sid' => 'integer', 'parameter' => 'string(50)', 'targetqid' => 'integer', 'targetsqid' => 'integer');
$oDB->createCommand()->createTable('{{survey_url_parameters}}', $aFields);
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 149), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 150) {
addColumn('{{questions}}', 'relevance', 'TEXT');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 150), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 151) {
addColumn('{{groups}}', 'randomization_group', "string(20) NOT NULL default ''");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 151), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 152) {
$oDB->createCommand()->createIndex('question_attributes_idx3', '{{question_attributes}}', 'attribute');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 152), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 153) {
$oDB->createCommand()->createTable('{{expression_errors}}', array('id' => 'pk', 'errortime' => 'string(50)', 'sid' => 'integer', 'gid' => 'integer', 'qid' => 'integer', 'gseq' => 'integer', 'qseq' => 'integer', 'type' => 'string(50)', 'eqn' => 'text', 'prettyprint' => 'text'));
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 153), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 154) {
$oDB->createCommand()->addColumn('{{groups}}', 'grelevance', "text");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 154), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 155) {
addColumn('{{surveys}}', 'googleanalyticsstyle', "string(1)");
addColumn('{{surveys}}', 'googleanalyticsapikey', "string(25)");
try {
setTransactionBookmark();
$oDB->createCommand()->renameColumn('{{surveys}}', 'showXquestions', 'showxquestions');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 155), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 156) {
try {
$oDB->createCommand()->dropTable('{{survey_url_parameters}}');
} catch (Exception $e) {
// do nothing
}
$oDB->createCommand()->createTable('{{survey_url_parameters}}', array('id' => 'pk', 'sid' => 'integer NOT NULL', 'parameter' => 'string(50) NOT NULL', 'targetqid' => 'integer', 'targetsqid' => 'integer'));
$oDB->createCommand()->dropTable('{{sessions}}');
if (Yii::app()->db->driverName == 'mysql') {
$oDB->createCommand()->createTable('{{sessions}}', array('id' => 'string(32) NOT NULL', 'expire' => 'integer', 'data' => 'longtext'));
} else {
$oDB->createCommand()->createTable('{{sessions}}', array('id' => 'string(32) NOT NULL', 'expire' => 'integer', 'data' => 'text'));
}
addPrimaryKey('sessions', array('id'));
addColumn('{{surveys_languagesettings}}', 'surveyls_attributecaptions', "TEXT");
addColumn('{{surveys}}', 'sendconfirmation', "string(1) default 'Y'");
upgradeSurveys156();
// If a survey has an deleted owner, re-own the survey to the superadmin
$oDB->schema->refresh();
Survey::model()->refreshMetaData();
$surveys = Survey::model();
$surveys = $surveys->with(array('owner'))->findAll();
foreach ($surveys as $row) {
if (!isset($row->owner->attributes)) {
Survey::model()->updateByPk($row->sid, array('owner_id' => 1));
}
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 156), "stg_name='DBVersion'");
$oTransaction->commit();
$oTransaction = $oDB->beginTransaction();
}
if ($iOldDBVersion < 157) {
// MySQL DB corrections
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('questions_idx4', '{{questions}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{answers}}', 'assessment_value', 'integer', false, '0');
dropPrimaryKey('answers');
alterColumn('{{answers}}', 'scale_id', 'integer', false, '0');
addPrimaryKey('answers', array('qid', 'code', 'language', 'scale_id'));
alterColumn('{{conditions}}', 'method', "string(5)", false, '');
alterColumn('{{participants}}', 'owner_uid', 'integer', false);
alterColumn('{{participant_attribute_names}}', 'visible', 'string(5)', false);
alterColumn('{{questions}}', 'type', "string(1)", false, 'T');
alterColumn('{{questions}}', 'other', "string(1)", false, 'N');
alterColumn('{{questions}}', 'mandatory', "string(1)");
alterColumn('{{questions}}', 'scale_id', 'integer', false, '0');
alterColumn('{{questions}}', 'parent_qid', 'integer', false, '0');
alterColumn('{{questions}}', 'same_default', 'integer', false, '0');
alterColumn('{{quota}}', 'qlimit', 'integer');
alterColumn('{{quota}}', 'action', 'integer');
alterColumn('{{quota}}', 'active', 'integer', false, '1');
alterColumn('{{quota}}', 'autoload_url', 'integer', false, '0');
alterColumn('{{saved_control}}', 'status', "string(1)", false, '');
try {
setTransactionBookmark();
alterColumn('{{sessions}}', 'id', "string(32)", false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{surveys}}', 'active', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'anonymized', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'format', "string(1)");
alterColumn('{{surveys}}', 'savetimings', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'datestamp', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'usecookie', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'allowregister', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'allowsave', "string(1)", false, 'Y');
alterColumn('{{surveys}}', 'autonumber_start', 'integer', false, '0');
alterColumn('{{surveys}}', 'autoredirect', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'allowprev', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'printanswers', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'ipaddr', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'refurl', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'publicstatistics', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'publicgraphs', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'listpublic', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'htmlemail', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'sendconfirmation', "string(1)", false, 'Y');
alterColumn('{{surveys}}', 'tokenanswerspersistence', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'assessments', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'usecaptcha', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'usetokens', "string(1)", false, 'N');
alterColumn('{{surveys}}', 'tokenlength', 'integer', false, '15');
alterColumn('{{surveys}}', 'showxquestions', "string(1)", true, 'Y');
alterColumn('{{surveys}}', 'showgroupinfo', "string(1) ", true, 'B');
alterColumn('{{surveys}}', 'shownoanswer', "string(1) ", true, 'Y');
alterColumn('{{surveys}}', 'showqnumcode', "string(1) ", true, 'X');
alterColumn('{{surveys}}', 'bouncetime', 'integer');
alterColumn('{{surveys}}', 'showwelcome', "string(1)", true, 'Y');
alterColumn('{{surveys}}', 'showprogress', "string(1)", true, 'Y');
alterColumn('{{surveys}}', 'allowjumps', "string(1)", true, 'N');
alterColumn('{{surveys}}', 'navigationdelay', 'integer', false, '0');
alterColumn('{{surveys}}', 'nokeyboard', "string(1)", true, 'N');
alterColumn('{{surveys}}', 'alloweditaftercompletion', "string(1)", true, 'N');
alterColumn('{{surveys}}', 'googleanalyticsstyle', "string(1)");
alterColumn('{{surveys_languagesettings}}', 'surveyls_dateformat', 'integer', false, 1);
try {
setTransactionBookmark();
alterColumn('{{survey_permissions}}', 'sid', "integer", false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
alterColumn('{{survey_permissions}}', 'uid', "integer", false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{survey_permissions}}', 'create_p', 'integer', false, '0');
alterColumn('{{survey_permissions}}', 'read_p', 'integer', false, '0');
alterColumn('{{survey_permissions}}', 'update_p', 'integer', false, '0');
alterColumn('{{survey_permissions}}', 'delete_p', 'integer', false, '0');
alterColumn('{{survey_permissions}}', 'import_p', 'integer', false, '0');
alterColumn('{{survey_permissions}}', 'export_p', 'integer', false, '0');
alterColumn('{{survey_url_parameters}}', 'targetqid', 'integer');
alterColumn('{{survey_url_parameters}}', 'targetsqid', 'integer');
alterColumn('{{templates_rights}}', 'use', 'integer', false);
alterColumn('{{users}}', 'create_survey', 'integer', false, '0');
alterColumn('{{users}}', 'create_user', 'integer', false, '0');
alterColumn('{{users}}', 'participant_panel', 'integer', false, '0');
alterColumn('{{users}}', 'delete_user', 'integer', false, '0');
alterColumn('{{users}}', 'superadmin', 'integer', false, '0');
alterColumn('{{users}}', 'configurator', 'integer', false, '0');
alterColumn('{{users}}', 'manage_template', 'integer', false, '0');
alterColumn('{{users}}', 'manage_label', 'integer', false, '0');
alterColumn('{{users}}', 'dateformat', 'integer', false, 1);
alterColumn('{{users}}', 'participant_panel', 'integer', false, '0');
alterColumn('{{users}}', 'parent_id', 'integer', false);
try {
setTransactionBookmark();
alterColumn('{{surveys_languagesettings}}', 'surveyls_survey_id', "integer", false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{user_groups}}', 'owner_id', "integer", false);
dropPrimaryKey('user_in_groups');
alterColumn('{{user_in_groups}}', 'ugid', "integer", false);
alterColumn('{{user_in_groups}}', 'uid', "integer", false);
// Additional corrections for Postgres
try {
setTransactionBookmark();
$oDB->createCommand()->createIndex('questions_idx3', '{{questions}}', 'gid');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->createIndex('conditions_idx3', '{{conditions}}', 'cqid');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->createIndex('questions_idx4', '{{questions}}', 'type');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('user_in_groups_idx1', '{{user_in_groups}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('{{user_name_key}}', '{{users}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->createIndex('users_name', '{{users}}', 'users_name', true);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
addPrimaryKey('user_in_groups', array('ugid', 'uid'));
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{participant_attribute}}', 'value', "string(50)", false);
try {
setTransactionBookmark();
alterColumn('{{participant_attribute_names}}', 'attribute_type', "string(4)", false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
dropColumn('{{participant_attribute_names_lang}}', 'id');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
addPrimaryKey('participant_attribute_names_lang', array('attribute_id', 'lang'));
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->renameColumn('{{participant_shares}}', 'shared_uid', 'share_uid');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{participant_shares}}', 'date_added', "datetime", false);
alterColumn('{{participants}}', 'firstname', "string(40)");
alterColumn('{{participants}}', 'lastname', "string(40)");
alterColumn('{{participants}}', 'email', "string(80)");
alterColumn('{{participants}}', 'language', "string(40)");
alterColumn('{{quota_languagesettings}}', 'quotals_name', "string");
try {
setTransactionBookmark();
alterColumn('{{survey_permissions}}', 'sid', 'integer', false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
alterColumn('{{survey_permissions}}', 'uid', 'integer', false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{users}}', 'htmleditormode', "string(7)", true, 'default');
// Sometimes the survey_links table was deleted before this step, if so
// we recreate it (copied from line 663)
if (!tableExists('{survey_links}')) {
$oDB->createCommand()->createTable('{{survey_links}}', array('participant_id' => 'string(50) NOT NULL', 'token_id' => 'integer NOT NULL', 'survey_id' => 'integer NOT NULL', 'date_created' => 'datetime NOT NULL'));
addPrimaryKey('survey_links', array('participant_id', 'token_id', 'survey_id'));
}
alterColumn('{{survey_links}}', 'date_created', "datetime", true);
alterColumn('{{saved_control}}', 'identifier', "text", false);
alterColumn('{{saved_control}}', 'email', "string(320)");
alterColumn('{{surveys}}', 'adminemail', "string(320)");
alterColumn('{{surveys}}', 'bounce_email', "string(320)");
alterColumn('{{users}}', 'email', "string(320)");
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('assessments_idx', '{{assessments}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->createIndex('assessments_idx3', '{{assessments}}', 'gid');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('ixcode', '{{labels}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('{{labels_ixcode_idx}}', '{{labels}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->createIndex('labels_code_idx', '{{labels}}', 'code');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
if (Yii::app()->db->driverName == 'pgsql') {
try {
setTransactionBookmark();
$oDB->createCommand("ALTER TABLE ONLY {{user_groups}} ADD PRIMARY KEY (ugid); ")->execute;
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand("ALTER TABLE ONLY {{users}} ADD PRIMARY KEY (uid); ")->execute;
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
}
// Additional corrections for MSSQL
alterColumn('{{answers}}', 'answer', "text", false);
alterColumn('{{assessments}}', 'name', "text", false);
alterColumn('{{assessments}}', 'message', "text", false);
alterColumn('{{defaultvalues}}', 'defaultvalue', "text");
alterColumn('{{expression_errors}}', 'eqn', "text");
alterColumn('{{expression_errors}}', 'prettyprint', "text");
alterColumn('{{groups}}', 'description', "text");
alterColumn('{{groups}}', 'grelevance', "text");
alterColumn('{{labels}}', 'title', "text");
alterColumn('{{question_attributes}}', 'value', "text");
alterColumn('{{questions}}', 'preg', "text");
alterColumn('{{questions}}', 'help', "text");
alterColumn('{{questions}}', 'relevance', "text");
alterColumn('{{questions}}', 'question', "text", false);
alterColumn('{{quota_languagesettings}}', 'quotals_quota_id', "integer", false);
alterColumn('{{quota_languagesettings}}', 'quotals_message', "text", false);
alterColumn('{{saved_control}}', 'refurl', "text");
alterColumn('{{saved_control}}', 'access_code', "text", false);
alterColumn('{{saved_control}}', 'ip', "text", false);
alterColumn('{{saved_control}}', 'saved_thisstep', "text", false);
alterColumn('{{saved_control}}', 'saved_date', "datetime", false);
alterColumn('{{surveys}}', 'attributedescriptions', "text");
alterColumn('{{surveys}}', 'emailresponseto', "text");
alterColumn('{{surveys}}', 'emailnotificationto', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_description', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_welcometext', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_invite', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_remind', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_register', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_confirm', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_attributecaptions', "text");
alterColumn('{{surveys_languagesettings}}', 'email_admin_notification', "text");
alterColumn('{{surveys_languagesettings}}', 'email_admin_responses', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_endtext', "text");
alterColumn('{{user_groups}}', 'description', "text", false);
alterColumn('{{conditions}}', 'value', 'string', false, '');
alterColumn('{{participant_shares}}', 'can_edit', "string(5)", false);
alterColumn('{{users}}', 'password', "binary", false);
dropColumn('{{users}}', 'one_time_pw');
addColumn('{{users}}', 'one_time_pw', 'binary');
$oDB->createCommand()->update('{{question_attributes}}', array('value' => '1'), "attribute = 'random_order' and value = '2'");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 157), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 158) {
LimeExpressionManager::UpgradeConditionsToRelevance();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 158), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 159) {
alterColumn('{{failed_login_attempts}}', 'ip', "string(40)", false);
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 159), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 160) {
alterLanguageCode('it', 'it-informal');
alterLanguageCode('it-formal', 'it');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 160), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 161) {
addColumn('{{survey_links}}', 'date_invited', 'datetime NULL default NULL');
addColumn('{{survey_links}}', 'date_completed', 'datetime NULL default NULL');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 161), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 162) {
/* Fix participant db types */
alterColumn('{{participant_attribute}}', 'value', "text", false);
alterColumn('{{participant_attribute_names_lang}}', 'attribute_name', "string(255)", false);
alterColumn('{{participant_attribute_values}}', 'value', "text", false);
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 162), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 163) {
//Replace by <script type="text/javascript" src="{TEMPLATEURL}template.js"></script> by {TEMPLATEJS}
$replacedTemplate = replaceTemplateJS();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 163), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 164) {
upgradeTokens148();
// this should have bee done in 148 - that's why it is named this way
// fix survey tables for missing or incorrect token field
upgradeSurveyTables164();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 164), "stg_name='DBVersion'");
// Not updating settings table as upgrade process takes care of that step now
}
if ($iOldDBVersion < 165) {
$oDB->createCommand()->createTable('{{plugins}}', array('id' => 'pk', 'name' => 'string NOT NULL', 'active' => 'boolean'));
$oDB->createCommand()->createTable('{{plugin_settings}}', array('id' => 'pk', 'plugin_id' => 'integer NOT NULL', 'model' => 'string', 'model_id' => 'integer', 'key' => 'string', 'value' => 'text'));
alterColumn('{{surveys_languagesettings}}', 'surveyls_url', "text");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 165), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 166) {
$oDB->createCommand()->renameTable('{{survey_permissions}}', '{{permissions}}');
dropPrimaryKey('permissions');
alterColumn('{{permissions}}', 'permission', "string(100)", false);
$oDB->createCommand()->renameColumn('{{permissions}}', 'sid', 'entity_id');
alterColumn('{{permissions}}', 'entity_id', "string(100)", false);
addColumn('{{permissions}}', 'entity', "string(50)");
$oDB->createCommand("update {{permissions}} set entity='survey'")->query();
addColumn('{{permissions}}', 'id', 'pk');
$oDB->createCommand()->createIndex('idxPermissions', '{{permissions}}', 'entity_id,entity,permission,uid', true);
upgradePermissions166();
dropColumn('{{users}}', 'create_survey');
dropColumn('{{users}}', 'create_user');
dropColumn('{{users}}', 'delete_user');
dropColumn('{{users}}', 'superadmin');
dropColumn('{{users}}', 'configurator');
dropColumn('{{users}}', 'manage_template');
dropColumn('{{users}}', 'manage_label');
dropColumn('{{users}}', 'participant_panel');
$oDB->createCommand()->dropTable('{{templates_rights}}');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 166), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 167) {
addColumn('{{surveys_languagesettings}}', 'attachments', 'text');
addColumn('{{users}}', 'created', 'datetime');
addColumn('{{users}}', 'modified', 'datetime');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 167), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 168) {
addColumn('{{participants}}', 'created', 'datetime');
addColumn('{{participants}}', 'modified', 'datetime');
addColumn('{{participants}}', 'created_by', 'integer');
$oDB->createCommand('update {{participants}} set created_by=owner_uid')->query();
alterColumn('{{participants}}', 'created_by', "integer", false);
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 168), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 169) {
// Add new column for question index options.
addColumn('{{surveys}}', 'questionindex', 'integer not null default 0');
// Set values for existing surveys.
$oDB->createCommand("update {{surveys}} set questionindex = 0 where allowjumps <> 'Y'")->query();
$oDB->createCommand("update {{surveys}} set questionindex = 1 where allowjumps = 'Y'")->query();
// Remove old column.
dropColumn('{{surveys}}', 'allowjumps');
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 169), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 170) {
// renamed advanced attributes fields dropdown_dates_year_min/max
$oDB->createCommand()->update('{{question_attributes}}', array('attribute' => 'date_min'), "attribute='dropdown_dates_year_min'");
$oDB->createCommand()->update('{{question_attributes}}', array('attribute' => 'date_max'), "attribute='dropdown_dates_year_max'");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 170), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 171) {
try {
dropColumn('{{sessions}}', 'data');
} catch (Exception $e) {
}
switch (Yii::app()->db->driverName) {
case 'mysql':
case 'mysqli':
addColumn('{{sessions}}', 'data', 'longbinary');
break;
case 'sqlsrv':
case 'dblib':
case 'mssql':
addColumn('{{sessions}}', 'data', 'VARBINARY(MAX)');
break;
case 'pgsql':
addColumn('{{sessions}}', 'data', 'BYTEA');
break;
default:
die('Unknown database type');
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 171), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 172) {
switch (Yii::app()->db->driverName) {
case 'pgsql':
// Special treatment for Postgres as it is too dumb to convert a string to a number without explicit being told to do so ... seriously?
alterColumn('{{permissions}}', 'entity_id', "INTEGER USING (entity_id::integer)", false);
break;
case 'sqlsrv':
case 'dblib':
case 'mssql':
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('permissions_idx2', '{{permissions}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
$oDB->createCommand()->dropIndex('idxPermissions', '{{permissions}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{permissions}}', 'entity_id', "INTEGER", false);
$oDB->createCommand()->createIndex('permissions_idx2', '{{permissions}}', 'entity_id,entity,permission,uid', true);
break;
default:
alterColumn('{{permissions}}', 'entity_id', "INTEGER", false);
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 172), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 173) {
addColumn('{{participant_attribute_names}}', 'defaultname', "string(50) NOT NULL default ''");
upgradeCPDBAttributeDefaultNames173();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 173), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 174) {
alterColumn('{{participants}}', 'email', "string(254)");
alterColumn('{{saved_control}}', 'email', "string(254)");
alterColumn('{{surveys}}', 'adminemail', "string(254)");
alterColumn('{{surveys}}', 'bounce_email', "string(254)");
switch (Yii::app()->db->driverName) {
case 'sqlsrv':
case 'dblib':
case 'mssql':
dropUniqueKeyMSSQL('email', '{{users}}');
}
alterColumn('{{users}}', 'email', "string(254)");
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 174), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 175) {
switch (Yii::app()->db->driverName) {
case 'pgsql':
// Special treatment for Postgres as it is too dumb to convert a boolean to a number without explicit being told to do so
alterColumn('{{plugins}}', 'active', "INTEGER USING (active::integer)", false);
break;
default:
alterColumn('{{plugins}}', 'active', "integer", false, '0');
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 175), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 176) {
upgradeTokens176();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 176), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 177) {
if (Yii::app()->getConfig('auth_webserver') === true) {
// using auth webserver, now activate the plugin with default settings.
if (!class_exists('Authwebserver', false)) {
$plugin = Plugin::model()->findByAttributes(array('name' => 'Authwebserver'));
if (!$plugin) {
$plugin = new Plugin();
$plugin->name = 'Authwebserver';
$plugin->active = 1;
$plugin->save();
$plugin = App()->getPluginManager()->loadPlugin('Authwebserver', $plugin->id);
$aPluginSettings = $plugin->getPluginSettings(true);
$aDefaultSettings = array();
foreach ($aPluginSettings as $key => $settings) {
if (is_array($settings) && array_key_exists('current', $settings)) {
$aDefaultSettings[$key] = $settings['current'];
}
}
$plugin->saveSettings($aDefaultSettings);
} else {
$plugin->active = 1;
$plugin->save();
}
}
}
upgradeSurveys177();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 177), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 178) {
if (Yii::app()->db->driverName == 'mysql' || Yii::app()->db->driverName == 'mysqli') {
modifyPrimaryKey('questions', array('qid', 'language'));
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 178), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 179) {
upgradeSurveys177();
// Needs to be run again to make sure
upgradeTokenTables179();
alterColumn('{{participants}}', 'email', "string(254)", false);
alterColumn('{{participants}}', 'firstname', "string(150)", false);
alterColumn('{{participants}}', 'lastname', "string(150)", false);
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 179), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 180) {
$aUsers = User::model()->findAll();
$aPerm = array('entity_id' => 0, 'entity' => 'global', 'uid' => 0, 'permission' => 'auth_db', 'create_p' => 0, 'read_p' => 1, 'update_p' => 0, 'delete_p' => 0, 'import_p' => 0, 'export_p' => 0);
foreach ($aUsers as $oUser) {
if (!Permission::model()->hasGlobalPermission('auth_db', 'read', $oUser->uid)) {
$oPermission = new Permission();
foreach ($aPerm as $k => $v) {
$oPermission->{$k} = $v;
}
$oPermission->uid = $oUser->uid;
$oPermission->save();
}
}
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 180), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 181) {
upgradeTokenTables181();
upgradeSurveyTables181();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 181), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 183) {
upgradeSurveyTables183();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 183), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 184) {
fixKCFinder184();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 184), "stg_name='DBVersion'");
}
// LS 2.5 table start at 250
if ($iOldDBVersion < 250) {
createBoxes250();
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 250), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 251) {
upgradeSurveyTables251();
// Update DBVersion
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 251), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 252) {
Yii::app()->db->createCommand()->addColumn('{{questions}}', 'modulename', 'string');
// Update DBVersion
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 252), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 253) {
upgradeSurveyTables253();
// Update DBVersion
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 253), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 254) {
upgradeSurveyTables254();
// Update DBVersion
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 254), "stg_name='DBVersion'");
}
if ($iOldDBVersion < 255) {
upgradeSurveyTables255();
// Update DBVersion
$oDB->createCommand()->update('{{settings_global}}', array('stg_value' => 255), "stg_name='DBVersion'");
}
$oTransaction->commit();
// Activate schema caching
$oDB->schemaCachingDuration = 3600;
// Load all tables of the application in the schema
$oDB->schema->getTables();
// clear the cache of all loaded tables
$oDB->schema->refresh();
} catch (Exception $e) {
Yii::app()->setConfig('Updating', false);
$oTransaction->rollback();
// Activate schema caching
$oDB->schemaCachingDuration = 3600;
// Load all tables of the application in the schema
$oDB->schema->getTables();
// clear the cache of all loaded tables
$oDB->schema->refresh();
echo '<br /><br />' . gT('An non-recoverable error happened during the update. Error details:') . "<p>" . htmlspecialchars($e->getMessage()) . '</p><br />';
return false;
}
fixLanguageConsistencyAllSurveys();
Yii::app()->setConfig('Updating', false);
return true;
}
*
* $Id: integritycheck.php 11664 2011-12-16 05:19:42Z tmswhite $
*/
include_once "login_check.php";
if ($_SESSION['USER_RIGHT_CONFIGURATOR'] == 1) {
// THIS FILE CHECKS THE CONSISTENCY OF THE DATABASE, IT "LOOKS FOR
// STRAY QUESTIONS, ANSWERS, CONDITIONS OR GROUPS AND DELETES THEM
$ok = returnglobal('ok');
$integritycheck = '';
if (!isset($ok) || $ok != "Y" && $ok != "R") {
$integritycheck .= "<div class='messagebox ui-corner-all'>" . "<div class='header ui-widget-header'>" . $clang->gT("Data Consistency Check") . "<br />\n" . "<span style='font-size:7pt;'>" . $clang->gT("If errors are showing up you might have to execute this script repeatedly.") . "</span></div>\n" . "<ul>\n";
/****** Plainly delete survey permissions if the survey or user does not exist ***/
$connect->query("delete FROM {$dbprefix}survey_permissions where sid not in (select sid from {$dbprefix}surveys)");
$connect->query("delete FROM {$dbprefix}survey_permissions where uid not in (select uid from {$dbprefix}users)");
// Silently fix subquestions
fixSubquestions();
/***** Check for activate survey tables with missing survey entry **/
$sQuery = db_select_tables_like("{$dbprefix}survey\\_%");
$aResult = db_execute_num($sQuery) or safe_die("Couldn't get list of conditions from database<br />{$query}<br />" . $connect->ErrorMsg());
while ($aRow = $aResult->FetchRow()) {
$tablename = substr($aRow[0], strlen($dbprefix));
if ($tablename == 'survey_permissions' || $tablename == 'survey_links') {
continue;
}
$iSurveyID = substr($tablename, strpos($tablename, '_') + 1);
$qquery = "SELECT sid FROM {$dbprefix}surveys WHERE sid='{$iSurveyID}'";
$qresult = $connect->Execute($qquery) or safe_die("Couldn't check questions table for qids<br />{$qquery}<br />" . $connect->ErrorMsg());
$qcount = $qresult->RecordCount();
if ($qcount == 0) {
$date = date('YmdHis') . rand(1, 1000);
$sOldTable = "survey_{$iSurveyID}";
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] (\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();
ob_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();
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] (\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();
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 NOT NULL,\r\n\t\t\t\t\t\t PRIMARY KEY ([folder])\r\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
mssql_drop_primary_index('assessments');
// add the new primary key
modify_database("", "ALTER TABLE [prefix_assessments] ADD CONSTRAINT pk_assessments_id_lang PRIMARY KEY ([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
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] (\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] text,\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();
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] (\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] text,\r\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(\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 text,\r\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] (\r\n [sid] INT NOT NULL,\r\n [uid] INT NOT NULL,\r\n [permission] VARCHAR(20) NOT NULL,\r\n [create_p] TINYINT NOT NULL default '0',\r\n [read_p] TINYINT NOT NULL default '0',\r\n [update_p] TINYINT NOT NULL default '0',\r\n [delete_p] TINYINT NOT NULL default '0',\r\n [import_p] TINYINT NOT NULL default '0',\r\n [export_p] TINYINT NOT NULL default '0',\r\n PRIMARY KEY ([sid], [uid],[permission])\r\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\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");
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] (\r\n [id] INT NOT NULL IDENTITY (1,1) PRIMARY KEY,\r\n [ip] varchar(37) NOT NULL,\r\n [last_attempt] varchar(20) NOT NULL,\r\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("", "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'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_users] ADD questionselectormode VARCHAR(7) NOT NULL default 'default'");
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("", "CREATE TABLE [prefix_participants] (\r\n [participant_id] varchar(50) NOT NULL,\r\n [firstname] varchar(40) NOT NULL,\r\n [lastname] varchar(40) NOT NULL,\r\n [email] varchar(80) NOT NULL,\r\n [language] varchar(2) NOT NULL,\r\n [blacklisted] varchar(1) NOT NULL,\r\n [owner_uid] int(20) NOT NULL,\r\n PRIMARY KEY ([participant_id])\r\n );");
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 );");
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 );");
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 );");
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 );");
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,\r\n [can_edit] text NOT NULL,\r\n PRIMARY KEY ([participant_id],[share_uid])\r\n );");
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,\r\n PRIMARY KEY ([participant_id],[token_id],[survey_id])\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE [prefix_users] ADD [participant_panel] int NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
// Add language field to question_attributes 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 );");
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] integer NOT NULL AUTO_INCREMENT,\r\n [errortime] varchar(50) DEFAULT NULL,\r\n [sid] integer DEFAULT NULL,\r\n [gid] integer DEFAULT NULL,\r\n [qid] integer DEFAULT NULL,\r\n [gseq] integer DEFAULT NULL,\r\n [qseq] integer DEFAULT NULL,\r\n [type] varchar(50) ,\r\n [eqn] text,\r\n [prettyprint] text,\r\n PRIMARY KEY ([id])\r\n );");
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("", "EXEC sp_rename 'prefix_surveys.showXquestions','showxquestions'");
echo $modifyoutput;
flush();
@ob_flush();
modify_database("", "UPDATE [prefix_settings_global] SET stg_value='155' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
echo '<br /><br />' . sprintf($clang->gT('Database update finished (%s)'), date('Y-m-d H:i:s')) . '<br />';
return true;
}
function db_upgrade_all($oldversion)
{
/// This function does anything necessary to upgrade
/// older versions to match current functionality
global $modifyoutput, $usertemplaterootdir, $standardtemplaterootdir;
Yii::app()->loadHelper('database');
$usertemplaterootdir = Yii::app()->getConfig('usertemplaterootdir');
$standardtemplaterootdir = Yii::app()->getConfig('standardtemplaterootdir');
$clang = Yii::app()->lang;
echo str_pad($clang->gT('The LimeSurvey database is being upgraded') . ' (' . date('Y-m-d H:i:s') . ')', 14096) . ".<br /><br />" . $clang->gT('Please be patient...') . "<br /><br />\n";
$sDBDriverName = setsDBDriverName();
setVarchar($sDBDriverName);
$sVarchar = Yii::app()->getConfig('varchar');
$sAutoIncrement = Yii::app()->getConfig('autoincrement');
$oTransaction = Yii::app()->db->beginTransaction();
try {
if ($oldversion < 111) {
// Language upgrades from version 110 to 111 because the language names did change
$aOldNewLanguages = 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 ($aOldNewLanguages as $sOldLanguageCode => $sNewLanguageCode) {
alterLanguageCode($sOldLanguageCode, $sNewLanguageCode);
}
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 111), "stg_name='DBVersion'");
}
if ($oldversion < 112) {
// New size of the username field (it was previously 20 chars wide)
Yii::app()->db->createCommand()->alterColumn('{{users}}', 'users_name', "{$sVarchar}(64) NOT NULL");
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 112), "stg_name='DBVersion'");
}
if ($oldversion < 113) {
//Fixes the collation for the complete DB, tables and columns
if ($sDBDriverName == 'mysql') {
$databasename = getDBConnectionStringProperty('dbname');
fixMySQLCollations();
modifyDatabase("", "ALTER DATABASE `{$databasename}` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
echo $modifyoutput;
flush();
@ob_flush();
}
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 113), "stg_name='DBVersion'");
}
if ($oldversion < 114) {
Yii::app()->db->createCommand()->alterColumn('{{saved_control}}', 'email', "{$sVarchar}(320) NOT NULL");
Yii::app()->db->createCommand()->alterColumn('{{surveys}}', 'adminemail', "{$sVarchar}(320) NOT NULL");
Yii::app()->db->createCommand()->alterColumn('{{users}}', 'email', "{$sVarchar}(320) NOT NULL");
Yii::app()->db->createCommand()->insert('{{settings_global}}', array('stg_name' => 'SessionName', 'stg_value' => randomChars(64, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ!"$%&/()=?`+*~#",;.:abcdefghijklmnopqrstuvwxyz123456789')));
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 114), "stg_name='DBVersion'");
}
if ($oldversion < 126) {
addColumn('{{surveys}}', 'printanswers', "{$sVarchar}(1) default 'N'");
addColumn('{{surveys}}', 'listpublic', "{$sVarchar}(1) default 'N'");
upgradeSurveyTables126();
upgradeTokenTables126();
// Create quota table
createTable('{{quota}}', array('id' => 'pk', 'sid' => 'integer', 'qlimit' => 'integer', 'name' => 'string', 'action' => 'integer', 'active' => 'integer NOT NULL DEFAULT 1'));
// Create quota_members table
createTable('{{quota_members}}', array('id' => 'pk', 'sid' => 'integer', 'qid' => 'integer', 'quota_id' => 'integer', 'code' => $sVarchar . '(5)'));
Yii::app()->db->createCommand()->createIndex('sid', '{{quota_members}}', 'sid,qid,quota_id,code', true);
// Create templates_rights table
createTable('{{templates_rights}}', array('uid' => 'integer NOT NULL', 'folder' => 'string NOT NULL', 'use' => 'integer', 'PRIMARY KEY (uid, folder)'));
// Create templates table
createTable('{{templates}}', array('folder' => 'string NOT NULL', 'creator' => 'integer NOT NULL', 'PRIMARY KEY (folder)'));
// Rename Norwegian language codes
alterLanguageCode('no', 'nb');
addColumn('{{surveys}}', 'htmlemail', "{$sVarchar}(1) default 'N'");
addColumn('{{surveys}}', 'tokenanswerspersistence', "{$sVarchar}(1) default 'N'");
addColumn('{{surveys}}', 'usecaptcha', "{$sVarchar}(1) default 'N'");
addColumn('{{surveys}}', 'bounce_email', 'text');
addColumn('{{users}}', 'htmleditormode', "{$sVarchar}(7) default 'default'");
addColumn('{{users}}', 'superadmin', "integer NOT NULL default '0'");
addColumn('{{questions}}', 'lid1', "integer NOT NULL default '0'");
alterColumn('{{conditions}}', 'value', "string", false, '');
alterColumn('{{labels}}', 'title', "text");
Yii::app()->db->createCommand()->update('{{users}}', array('superadmin' => 1), "create_survey=1 AND create_user=1 AND move_user=1 AND delete_user=1 AND configurator=1");
Yii::app()->db->createCommand()->update('{{conditions}}', array('method' => '=='), "(method is null) or method='' or method='0'");
dropColumn('{{users}}', 'move_user');
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 126), "stg_name='DBVersion'");
}
if ($oldversion < 127) {
modifyDatabase("", "create index answers_idx2 on {{answers}} (sortorder)");
echo $modifyoutput;
modifyDatabase("", "create index assessments_idx2 on {{assessments}} (sid)");
echo $modifyoutput;
modifyDatabase("", "create index assessments_idx3 on {{assessments}} (gid)");
echo $modifyoutput;
modifyDatabase("", "create index conditions_idx2 on {{conditions}} (qid)");
echo $modifyoutput;
modifyDatabase("", "create index conditions_idx3 on {{conditions}} (cqid)");
echo $modifyoutput;
modifyDatabase("", "create index groups_idx2 on {{groups}} (sid)");
echo $modifyoutput;
modifyDatabase("", "create index question_attributes_idx2 on {{question_attributes}} (qid)");
echo $modifyoutput;
modifyDatabase("", "create index questions_idx2 on {{questions}} (sid)");
echo $modifyoutput;
modifyDatabase("", "create index questions_idx3 on {{questions}} (gid)");
echo $modifyoutput;
modifyDatabase("", "create index questions_idx4 on {{questions}} (type)");
echo $modifyoutput;
modifyDatabase("", "create index quota_idx2 on {{quota}} (sid)");
echo $modifyoutput;
modifyDatabase("", "create index saved_control_idx2 on {{saved_control}} (sid)");
echo $modifyoutput;
modifyDatabase("", "create index user_in_groups_idx1 on {{user_in_groups}} (ugid, uid)");
echo $modifyoutput;
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 127), "stg_name='DBVersion'");
}
if ($oldversion < 128) {
upgradeTokens128();
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 128), "stg_name='DBVersion'");
}
if ($oldversion < 129) {
addColumn('{{surveys}}', 'startdate', "datetime");
addColumn('{{surveys}}', 'usestartdate', "{$sVarchar}(1) NOT NULL default 'N'");
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 129), "stg_name='DBVersion'");
}
if ($oldversion < 130) {
addColumn('{{conditions}}', 'scenario', "integer NOT NULL default '1'");
Yii::app()->db->createCommand()->update('{{conditions}}', array('scenario' => '1'), "(scenario is null) or scenario=0");
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 130), "stg_name='DBVersion'");
}
if ($oldversion < 131) {
addColumn('{{surveys}}', 'publicstatistics', "{$sVarchar}(1) NOT NULL default 'N'");
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 131), "stg_name='DBVersion'");
}
if ($oldversion < 132) {
addColumn('{{surveys}}', 'publicgraphs', "{$sVarchar}(1) NOT NULL default 'N'");
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 132), "stg_name='DBVersion'");
}
if ($oldversion < 133) {
addColumn('{{users}}', 'one_time_pw', 'binary');
// Add new assessment setting
addColumn('{{surveys}}', 'assessments', "{$sVarchar}(1) NOT NULL default 'N'");
// add new assessment value fields to answers & labels
addColumn('{{answers}}', 'assessment_value', "integer NOT NULL default '0'");
addColumn('{{labels}}', 'assessment_value', "integer NOT NULL default '0'");
// copy any valid codes from code field to assessment field
switch ($sDBDriverName) {
case 'mysql':
Yii::app()->db->createCommand("UPDATE {{answers}} SET assessment_value=CAST(`code` as SIGNED) where `code` REGEXP '^-?[0-9]+\$'")->execute();
Yii::app()->db->createCommand("UPDATE {{labels}} SET assessment_value=CAST(`code` as SIGNED) where `code` REGEXP '^-?[0-9]+\$'")->execute();
// copy assessment link to message since from now on we will have HTML assignment messages
Yii::app()->db->createCommand("UPDATE {{assessments}} set message=concat(replace(message,'/''',''''),'<br /><a href=\"',link,'\">',link,'</a>')")->execute();
break;
case 'mssql':
try {
Yii::app()->db->createCommand("UPDATE {{answers}} SET assessment_value=CAST([code] as int) WHERE ISNUMERIC([code])=1")->execute();
Yii::app()->db->createCommand("UPDATE {{labels}} SET assessment_value=CAST([code] as int) WHERE ISNUMERIC([code])=1")->execute();
} catch (Exception $e) {
}
// copy assessment link to message since from now on we will have HTML assignment messages
alterColumn('{{assessments}}', 'link', "varchar(max)", false);
alterColumn('{{assessments}}', 'message', "varchar(max)", false);
Yii::app()->db->createCommand("UPDATE {{assessments}} set message=replace(message,'/''','''')+'<br /><a href=\"'+link+'\">'+link+'</a>'")->execute();
break;
case 'pgsql':
Yii::app()->db->createCommand("UPDATE {{answers}} SET assessment_value=CAST(code as integer) where code ~ '^[0-9]+'")->execute();
Yii::app()->db->createCommand("UPDATE {{labels}} SET assessment_value=CAST(code as integer) where code ~ '^[0-9]+'")->execute();
// copy assessment link to message since from now on we will have HTML assignment messages
Yii::app()->db->createCommand("UPDATE {{assessments}} set message=replace(message,'/''','''')||'<br /><a href=\"'||link||'\">'||link||'</a>'")->execute();
break;
default:
die('Unkown database type');
}
// activate assessment where assessment rules exist
Yii::app()->db->createCommand("UPDATE {{surveys}} SET assessments='Y' where sid in (SELECT sid FROM {{assessments}} group by sid)")->execute();
// add language field to assessment table
addColumn('{{assessments}}', 'language', "{$sVarchar}(20) NOT NULL default 'en'");
// update language field with default language of that particular survey
Yii::app()->db->createCommand("UPDATE {{assessments}} SET language=(select language from {{surveys}} where sid={{assessments}}.sid)")->execute();
// drop the old link field
dropColumn('{{assessments}}', 'link');
// Add new fields to survey language settings
addColumn('{{surveys_languagesettings}}', 'surveyls_url', "string");
addColumn('{{surveys_languagesettings}}', 'surveyls_endtext', 'text');
// copy old URL fields ot language specific entries
Yii::app()->db->createCommand("UPDATE {{surveys_languagesettings}} set surveyls_url=(select url from {{surveys}} where sid={{surveys_languagesettings}}.surveyls_survey_id)")->execute();
// drop old URL field
dropColumn('{{surveys}}', 'url');
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 133), "stg_name='DBVersion'");
}
if ($oldversion < 134) {
// Add new tokens setting
addColumn('{{surveys}}', 'usetokens', "{$sVarchar}(1) NOT NULL default 'N'");
addColumn('{{surveys}}', 'attributedescriptions', 'text');
dropColumn('{{surveys}}', 'attribute1');
dropColumn('{{surveys}}', 'attribute2');
upgradeTokenTables134();
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 134), "stg_name='DBVersion'");
}
if ($oldversion < 135) {
alterColumn('{{question_attributes}}', 'value', 'text');
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 135), "stg_name='DBVersion'");
}
if ($oldversion < 136) {
addColumn('{{quota}}', 'autoload_url', "integer NOT NULL default 0");
// Create quota table
$fields = array('quotals_id' => 'pk', 'quotals_quota_id' => 'integer NOT NULL DEFAULT 0', 'quotals_language' => "{$sVarchar}(45) NOT NULL default 'en'", 'quotals_name' => 'string', 'quotals_message' => 'text NOT NULL', 'quotals_url' => 'string', 'quotals_urldescrip' => 'string');
createTable('{{quota_languagesettings}}', $fields);
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 136), "stg_name='DBVersion'");
}
if ($oldversion < 137) {
addColumn('{{surveys_languagesettings}}', 'surveyls_dateformat', "integer NOT NULL default 1");
addColumn('{{users}}', 'dateformat', "integer NOT NULL default 1");
Yii::app()->db->createCommand()->update('{{surveys}}', array('startdate' => NULL), "usestartdate='N'");
Yii::app()->db->createCommand()->update('{{surveys}}', array('expires' => NULL), "useexpiry='N'");
dropColumn('{{surveys}}', 'useexpiry');
dropColumn('{{surveys}}', 'usestartdate');
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 137), "stg_name='DBVersion'");
}
if ($oldversion < 138) {
alterColumn('{{quota_members}}', 'code', "{$sVarchar}(11)");
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 138), "stg_name='DBVersion'");
}
if ($oldversion < 139) {
upgradeSurveyTables139();
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 139), "stg_name='DBVersion'");
}
if ($oldversion < 140) {
addColumn('{{surveys}}', 'emailresponseto', 'text');
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 140), "stg_name='DBVersion'");
}
if ($oldversion < 141) {
addColumn('{{surveys}}', 'tokenlength', 'integer NOT NULL default 15');
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 141), "stg_name='DBVersion'");
}
if ($oldversion < 142) {
upgradeQuestionAttributes142();
Yii::app()->db->createCommand()->alterColumn('{{surveys}}', 'expires', "datetime");
Yii::app()->db->createCommand()->alterColumn('{{surveys}}', 'startdate', "datetime");
Yii::app()->db->createCommand()->update('{{question_attributes}}', array('value' => 0), "value='false'");
Yii::app()->db->createCommand()->update('{{question_attributes}}', array('value' => 1), "value='true'");
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 142), "stg_name='DBVersion'");
}
if ($oldversion < 143) {
addColumn('{{questions}}', 'parent_qid', 'integer NOT NULL default 0');
addColumn('{{answers}}', 'scale_id', 'integer NOT NULL default 0');
addColumn('{{questions}}', 'scale_id', 'integer NOT NULL default 0');
addColumn('{{questions}}', 'same_default', 'integer NOT NULL default 0');
dropPrimaryKey('answers');
addPrimaryKey('answers', array('qid', 'code', 'language', 'scale_id'));
$fields = array('qid' => "integer NOT NULL default 0", 'scale_id' => 'integer NOT NULL default 0', 'sqid' => 'integer NOT NULL default 0', 'language' => $sVarchar . '(20) NOT NULL', 'specialtype' => $sVarchar . "(20) NOT NULL default ''", 'defaultvalue' => 'text');
createTable('{{defaultvalues}}', $fields);
addPrimaryKey('defaultvalues', array('qid', 'specialtype', 'language', 'scale_id', 'sqid'));
// -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
upgradeTables143();
dropColumn('{{answers}}', 'default_value');
dropColumn('{{questions}}', 'lid');
dropColumn('{{questions}}', 'lid1');
$fields = array('sesskey' => "{$sVarchar}(64) NOT NULL DEFAULT ''", 'expiry' => "datetime NOT NULL", 'expireref' => "{$sVarchar}(250) DEFAULT ''", 'created' => "datetime NOT NULL", 'modified' => "datetime NOT NULL", 'sessdata' => 'text');
createTable('{{sessions}}', $fields);
addPrimaryKey('sessions', array('sesskey'));
Yii::app()->db->createCommand()->createIndex('sess2_expiry', '{{sessions}}', 'expiry');
Yii::app()->db->createCommand()->createIndex('sess2_expireref', '{{sessions}}', 'expireref');
// Move all user templates to the new user template directory
echo sprintf($clang->gT("Moving user templates to new location at %s..."), $usertemplaterootdir) . "<br />";
$myDirectory = opendir($standardtemplaterootdir);
$aFailedTemplates = array();
// get each entry
while ($entryName = readdir($myDirectory)) {
if (!in_array($entryName, array('.', '..', '.svn')) && is_dir($standardtemplaterootdir . DIRECTORY_SEPARATOR . $entryName) && !isStandardTemplate($entryName)) {
if (!rename($standardtemplaterootdir . DIRECTORY_SEPARATOR . $entryName, $usertemplaterootdir . DIRECTORY_SEPARATOR . $entryName)) {
$aFailedTemplates[] = $entryName;
}
}
}
if (count($aFailedTemplates) > 0) {
echo "The following templates at {$standardtemplaterootdir} could not be moved to the new location at {$usertemplaterootdir}:<br /><ul>";
foreach ($aFailedTemplates as $sFailedTemplate) {
echo "<li>{$sFailedTemplate}</li>";
}
echo "</ul>Please move these templates manually after the upgrade has finished.<br />";
}
// close directory
closedir($myDirectory);
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 143), "stg_name='DBVersion'");
}
if ($oldversion < 145) {
addColumn('{{surveys}}', 'savetimings', "{$sVarchar}(1) NULL default 'N'");
addColumn('{{surveys}}', 'showXquestions', "{$sVarchar}(1) NULL default 'Y'");
addColumn('{{surveys}}', 'showgroupinfo', "{$sVarchar}(1) NULL default 'B'");
addColumn('{{surveys}}', 'shownoanswer', "{$sVarchar}(1) NULL default 'Y'");
addColumn('{{surveys}}', 'showqnumcode', "{$sVarchar}(1) NULL default 'X'");
addColumn('{{surveys}}', 'bouncetime', 'integer');
addColumn('{{surveys}}', 'bounceprocessing', "{$sVarchar}(1) NULL default 'N'");
addColumn('{{surveys}}', 'bounceaccounttype', "{$sVarchar}(4)");
addColumn('{{surveys}}', 'bounceaccounthost', "{$sVarchar}(200)");
addColumn('{{surveys}}', 'bounceaccountpass', "{$sVarchar}(100)");
addColumn('{{surveys}}', 'bounceaccountencryption', "{$sVarchar}(3)");
addColumn('{{surveys}}', 'bounceaccountuser', "{$sVarchar}(200)");
addColumn('{{surveys}}', 'showwelcome', "{$sVarchar}(1) default 'Y'");
addColumn('{{surveys}}', 'showprogress', "{$sVarchar}(1) default 'Y'");
addColumn('{{surveys}}', 'allowjumps', "{$sVarchar}(1) default 'N'");
addColumn('{{surveys}}', 'navigationdelay', "integer default 0");
addColumn('{{surveys}}', 'nokeyboard', "{$sVarchar}(1) default 'N'");
addColumn('{{surveys}}', 'alloweditaftercompletion', "{$sVarchar}(1) default 'N'");
$fields = array('sid' => "integer NOT NULL", 'uid' => "integer NOT NULL", 'permission' => $sVarchar . '(20) NOT NULL', 'create_p' => "integer NOT NULL default 0", 'read_p' => "integer NOT NULL default 0", 'update_p' => "integer NOT NULL default 0", 'delete_p' => "integer NOT NULL default 0", 'import_p' => "integer NOT NULL default 0", 'export_p' => "integer NOT NULL default 0");
createTable('{{survey_permissions}}', $fields);
addPrimaryKey('survey_permissions', array('sid', 'uid', 'permission'));
upgradeSurveyPermissions145();
// drop the old survey rights table
Yii::app()->db->createCommand()->dropTable('{{surveys_rights}}');
// Add new fields for email templates
addColumn('{{surveys_languagesettings}}', 'email_admin_notification_subj', "string");
addColumn('{{surveys_languagesettings}}', 'email_admin_responses_subj', "string");
addColumn('{{surveys_languagesettings}}', 'email_admin_notification', "text");
addColumn('{{surveys_languagesettings}}', 'email_admin_responses', "text");
//Add index to questions table to speed up subquestions
Yii::app()->db->createCommand()->createIndex('parent_qid_idx', '{{questions}}', 'parent_qid');
addColumn('{{surveys}}', 'emailnotificationto', "text");
upgradeSurveys145();
dropColumn('{{surveys}}', 'notification');
alterColumn('{{conditions}}', 'method', "{$sVarchar}(5)", false, '');
Yii::app()->db->createCommand()->renameColumn('{{surveys}}', 'private', 'anonymized');
Yii::app()->db->createCommand()->update('{{surveys}}', array('anonymized' => 'N'), "anonymized is NULL");
alterColumn('{{surveys}}', 'anonymized', "{$sVarchar}(1)", false, 'N');
//now we clean up things that were not properly set in previous DB upgrades
Yii::app()->db->createCommand()->update('{{answers}}', array('answer' => ''), "answer is NULL");
Yii::app()->db->createCommand()->update('{{assessments}}', array('scope' => ''), "scope is NULL");
Yii::app()->db->createCommand()->update('{{assessments}}', array('name' => ''), "name is NULL");
Yii::app()->db->createCommand()->update('{{assessments}}', array('message' => ''), "message is NULL");
Yii::app()->db->createCommand()->update('{{assessments}}', array('minimum' => ''), "minimum is NULL");
Yii::app()->db->createCommand()->update('{{assessments}}', array('maximum' => ''), "maximum is NULL");
Yii::app()->db->createCommand()->update('{{groups}}', array('group_name' => ''), "group_name is NULL");
Yii::app()->db->createCommand()->update('{{labels}}', array('code' => ''), "code is NULL");
Yii::app()->db->createCommand()->update('{{labelsets}}', array('label_name' => ''), "label_name is NULL");
Yii::app()->db->createCommand()->update('{{questions}}', array('type' => 'T'), "type is NULL");
Yii::app()->db->createCommand()->update('{{questions}}', array('title' => ''), "title is NULL");
Yii::app()->db->createCommand()->update('{{questions}}', array('question' => ''), "question is NULL");
Yii::app()->db->createCommand()->update('{{questions}}', array('other' => 'N'), "other is NULL");
alterColumn('{{answers}}', 'answer', "text", false);
alterColumn('{{answers}}', 'assessment_value', 'integer', false, '0');
alterColumn('{{assessments}}', 'scope', "{$sVarchar}(5)", false, '');
alterColumn('{{assessments}}', 'name', "text", false);
alterColumn('{{assessments}}', 'message', "text", false);
alterColumn('{{assessments}}', 'minimum', "{$sVarchar}(50)", false, '');
alterColumn('{{assessments}}', 'maximum', "{$sVarchar}(50)", false, '');
// change the primary index to include language
if ($sDBDriverName == 'mysql') {
Yii::app()->db->createCommand("ALTER TABLE {{assessments}} DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `language`)")->execute();
} else {
dropPrimaryKey('assessments');
addPrimaryKey('assessments', array('id', 'language'));
}
alterColumn('{{conditions}}', 'cfieldname', "{$sVarchar}(50)", false, '');
dropPrimaryKey('defaultvalues');
alterColumn('{{defaultvalues}}', 'specialtype', "{$sVarchar}(20)", false, '');
addPrimaryKey('defaultvalues', array('qid', 'specialtype', 'language', 'scale_id', 'sqid'));
alterColumn('{{groups}}', 'group_name', "{$sVarchar}(100)", false, '');
alterColumn('{{labels}}', 'code', "{$sVarchar}(5)", false, '');
alterColumn('{{labels}}', 'language', "{$sVarchar}(20)", false, 'en');
alterColumn('{{labelsets}}', 'label_name', "{$sVarchar}(100)", false, '');
alterColumn('{{questions}}', 'parent_qid', 'integer', false, '0');
alterColumn('{{questions}}', 'title', "{$sVarchar}(20)", false, '');
alterColumn('{{questions}}', 'question', "text", false);
alterColumn('{{questions}}', 'type', "{$sVarchar}(1)", false, 'T');
try {
Yii::app()->db->createCommand()->createIndex('questions_idx4', '{{questions}}', 'type');
} catch (Exception $e) {
}
alterColumn('{{questions}}', 'other', "{$sVarchar}(1)", false, 'N');
alterColumn('{{questions}}', 'mandatory', "{$sVarchar}(1)");
alterColumn('{{question_attributes}}', 'attribute', "{$sVarchar}(50)");
alterColumn('{{quota}}', 'qlimit', 'integer');
Yii::app()->db->createCommand()->update('{{saved_control}}', array('identifier' => ''), "identifier is NULL");
alterColumn('{{saved_control}}', 'identifier', "text", false);
Yii::app()->db->createCommand()->update('{{saved_control}}', array('access_code' => ''), "access_code is NULL");
alterColumn('{{saved_control}}', 'access_code', "text", false);
alterColumn('{{saved_control}}', 'email', "{$sVarchar}(320)");
Yii::app()->db->createCommand()->update('{{saved_control}}', array('ip' => ''), "ip is NULL");
alterColumn('{{saved_control}}', 'ip', "text", false);
Yii::app()->db->createCommand()->update('{{saved_control}}', array('saved_thisstep' => ''), "saved_thisstep is NULL");
alterColumn('{{saved_control}}', 'saved_thisstep', "text", false);
Yii::app()->db->createCommand()->update('{{saved_control}}', array('status' => ''), "status is NULL");
alterColumn('{{saved_control}}', 'status', "{$sVarchar}(1)", false, '');
Yii::app()->db->createCommand()->update('{{saved_control}}', array('saved_date' => '1980-01-01 00:00:00'), "saved_date is NULL");
alterColumn('{{saved_control}}', 'saved_date', "datetime", false);
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => ''), "stg_value is NULL");
alterColumn('{{settings_global}}', 'stg_value', "string", false, '');
alterColumn('{{surveys}}', 'admin', "{$sVarchar}(50)");
Yii::app()->db->createCommand()->update('{{surveys}}', array('active' => 'N'), "active is NULL");
alterColumn('{{surveys}}', 'active', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'startdate', "datetime");
alterColumn('{{surveys}}', 'adminemail', "{$sVarchar}(320)");
alterColumn('{{surveys}}', 'anonymized', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'faxto', "{$sVarchar}(20)");
alterColumn('{{surveys}}', 'format', "{$sVarchar}(1)");
alterColumn('{{surveys}}', 'language', "{$sVarchar}(50)");
alterColumn('{{surveys}}', 'additional_languages', "string");
alterColumn('{{surveys}}', 'printanswers', "{$sVarchar}(1)", true, 'N');
alterColumn('{{surveys}}', 'publicstatistics', "{$sVarchar}(1)", true, 'N');
alterColumn('{{surveys}}', 'publicgraphs', "{$sVarchar}(1)", true, 'N');
alterColumn('{{surveys}}', 'assessments', "{$sVarchar}(1)", true, 'N');
alterColumn('{{surveys}}', 'usetokens', "{$sVarchar}(1)", true, 'N');
alterColumn('{{surveys}}', 'bounce_email', "{$sVarchar}(320)");
alterColumn('{{surveys}}', 'tokenlength', 'integer', true, 15);
Yii::app()->db->createCommand()->update('{{surveys_languagesettings}}', array('surveyls_title' => ''), "surveyls_title is NULL");
alterColumn('{{surveys_languagesettings}}', 'surveyls_title', "{$sVarchar}(200)", false);
alterColumn('{{surveys_languagesettings}}', 'surveyls_endtext', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_url', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_urldescription', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_invite_subj', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_remind_subj', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_register_subj', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_confirm_subj', "string");
alterColumn('{{surveys_languagesettings}}', 'surveyls_dateformat', 'integer', false, 1);
Yii::app()->db->createCommand()->update('{{users}}', array('users_name' => ''), "users_name is NULL");
Yii::app()->db->createCommand()->update('{{users}}', array('full_name' => ''), "full_name is NULL");
alterColumn('{{users}}', 'users_name', "{$sVarchar}(64)", false, '');
alterColumn('{{users}}', 'full_name', "{$sVarchar}(50)", false);
alterColumn('{{users}}', 'lang', "{$sVarchar}(20)");
alterColumn('{{users}}', 'email', "{$sVarchar}(320)");
alterColumn('{{users}}', 'superadmin', 'integer', false, 0);
alterColumn('{{users}}', 'htmleditormode', "{$sVarchar}(7)", true, 'default');
alterColumn('{{users}}', 'dateformat', 'integer', false, 1);
try {
Yii::app()->db->createCommand()->dropIndex('email', '{{users}}');
} catch (Exception $e) {
// do nothing
}
Yii::app()->db->createCommand()->update('{{user_groups}}', array('name' => ''), "name is NULL");
Yii::app()->db->createCommand()->update('{{user_groups}}', array('description' => ''), "description is NULL");
alterColumn('{{user_groups}}', 'name', "{$sVarchar}(20)", false);
alterColumn('{{user_groups}}', 'description', "text", false);
try {
Yii::app()->db->createCommand()->dropIndex('user_in_groups_idx1', '{{user_in_groups}}');
} catch (Exception $e) {
}
try {
addPrimaryKey('user_in_groups', array('ugid', 'uid'));
} catch (Exception $e) {
}
addColumn('{{surveys_languagesettings}}', 'surveyls_numberformat', "integer NOT NULL DEFAULT 0");
createTable('{{failed_login_attempts}}', array('id' => "pk", 'ip' => $sVarchar . '(37) NOT NULL', 'last_attempt' => $sVarchar . '(20) NOT NULL', 'number_attempts' => "integer NOT NULL"));
upgradeTokens145();
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 145), "stg_name='DBVersion'");
}
if ($oldversion < 146) {
upgradeSurveyTimings146();
// Fix permissions for new feature quick-translation
try {
setTransactionBookmark();
Yii::app()->db->createCommand("INSERT into {{survey_permissions}} (sid,uid,permission,read_p,update_p) SELECT sid,owner_id,'translations','1','1' from {{surveys}}")->execute();
echo $modifyoutput;
flush();
@ob_flush();
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 146), "stg_name='DBVersion'");
}
if ($oldversion < 147) {
addColumn('{{users}}', 'templateeditormode', "{$sVarchar}(7) NOT NULL default 'default'");
addColumn('{{users}}', 'questionselectormode', "{$sVarchar}(7) NOT NULL default 'default'");
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 147), "stg_name='DBVersion'");
}
if ($oldversion < 148) {
addColumn('{{users}}', 'participant_panel', "integer NOT NULL default 0");
createTable('{{participants}}', array('participant_id' => $sVarchar . '(50) NOT NULL', 'firstname' => $sVarchar . '(40) default NULL', 'lastname' => $sVarchar . '(40) default NULL', 'email' => $sVarchar . '(80) default NULL', 'language' => $sVarchar . '(40) default NULL', 'blacklisted' => $sVarchar . '(1) NOT NULL', 'owner_uid' => "integer NOT NULL"));
addPrimaryKey('participants', array('participant_id'));
createTable('{{participant_attribute}}', array('participant_id' => $sVarchar . '(50) NOT NULL', 'attribute_id' => "integer NOT NULL", 'value' => $sVarchar . '(50) NOT NULL'));
addPrimaryKey('participant_attribute', array('participant_id', 'attribute_id'));
createTable('{{participant_attribute_names}}', array('attribute_id' => $sAutoIncrement, 'attribute_type' => $sVarchar . '(4) NOT NULL', 'visible' => $sVarchar . '(5) NOT NULL', 'PRIMARY KEY (attribute_id,attribute_type)'));
createTable('{{participant_attribute_names_lang}}', array('attribute_id' => 'integer NOT NULL', 'attribute_name' => $sVarchar . '(30) NOT NULL', 'lang' => $sVarchar . '(20) NOT NULL'));
addPrimaryKey('participant_attribute_names_lang', array('attribute_id', 'lang'));
createTable('{{participant_attribute_values}}', array('attribute_id' => 'integer NOT NULL', 'value_id' => 'pk', 'value' => $sVarchar . '(20) NOT NULL'));
createTable('{{participant_shares}}', array('participant_id' => $sVarchar . '(50) NOT NULL', 'share_uid' => 'integer NOT NULL', 'date_added' => 'datetime NOT NULL', 'can_edit' => $sVarchar . '(5) NOT NULL'));
addPrimaryKey('participant_shares', array('participant_id', 'share_uid'));
createTable('{{survey_links}}', array('participant_id' => $sVarchar . '(50) NOT NULL', 'token_id' => 'integer NOT NULL', 'survey_id' => 'integer NOT NULL', 'date_created' => 'datetime NOT NULL'));
addPrimaryKey('survey_links', array('participant_id', 'token_id', 'survey_id'));
// Add language field to question_attributes table
addColumn('{{question_attributes}}', 'language', "{$sVarchar}(20)");
upgradeQuestionAttributes148();
upgradeTokens148();
fixSubquestions();
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 148), "stg_name='DBVersion'");
}
if ($oldversion < 149) {
$fields = array('id' => 'integer', 'sid' => 'integer', 'parameter' => $sVarchar . '(50)', 'targetqid' => 'integer', 'targetsqid' => 'integer');
createTable('{{survey_url_parameters}}', $fields);
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 149), "stg_name='DBVersion'");
}
if ($oldversion < 150) {
addColumn('{{questions}}', 'relevance', 'TEXT');
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 150), "stg_name='DBVersion'");
}
if ($oldversion < 151) {
addColumn('{{groups}}', 'randomization_group', "{$sVarchar}(20) NOT NULL default ''");
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 151), "stg_name='DBVersion'");
}
if ($oldversion < 152) {
Yii::app()->db->createCommand()->createIndex('question_attributes_idx3', '{{question_attributes}}', 'attribute');
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 152), "stg_name='DBVersion'");
}
if ($oldversion < 153) {
createTable('{{expression_errors}}', array('id' => 'pk', 'errortime' => $sVarchar . '(50)', 'sid' => 'integer', 'gid' => 'integer', 'qid' => 'integer', 'gseq' => 'integer', 'qseq' => 'integer', 'type' => $sVarchar . '(50)', 'eqn' => 'text', 'prettyprint' => 'text'));
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 153), "stg_name='DBVersion'");
}
if ($oldversion < 154) {
Yii::app()->db->createCommand()->addColumn('{{groups}}', 'grelevance', "text");
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 154), "stg_name='DBVersion'");
}
if ($oldversion < 155) {
addColumn('{{surveys}}', 'googleanalyticsstyle', "{$sVarchar}(1)");
addColumn('{{surveys}}', 'googleanalyticsapikey', "{$sVarchar}(25)");
try {
setTransactionBookmark();
Yii::app()->db->createCommand()->renameColumn('{{surveys}}', 'showXquestions', 'showxquestions');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 155), "stg_name='DBVersion'");
}
if ($oldversion < 156) {
try {
Yii::app()->db->createCommand()->dropTable('{{survey_url_parameters}}');
} catch (Exception $e) {
// do nothing
}
createTable('{{survey_url_parameters}}', array('id' => 'pk', 'sid' => 'integer NOT NULL', 'parameter' => $sVarchar . '(50) NOT NULL', 'targetqid' => 'integer', 'targetsqid' => 'integer'));
Yii::app()->db->createCommand()->dropTable('{{sessions}}');
if ($sDBDriverName == 'mysql') {
createTable('{{sessions}}', array('id' => $sVarchar . '(32) NOT NULL', 'expire' => 'integer', 'data' => 'longtext'));
} else {
createTable('{{sessions}}', array('id' => $sVarchar . '(32) NOT NULL', 'expire' => 'integer', 'data' => 'text'));
}
addPrimaryKey('sessions', array('id'));
addColumn('{{surveys_languagesettings}}', 'surveyls_attributecaptions', "TEXT");
addColumn('{{surveys}}', 'sendconfirmation', "{$sVarchar}(1) default 'Y'");
upgradeSurveys156();
// If a survey has an deleted owner, re-own the survey to the superadmin
Yii::app()->db->schema->refresh();
Survey::model()->refreshMetaData();
$surveys = Survey::model();
$surveys = $surveys->with(array('owner'))->findAll();
foreach ($surveys as $row) {
if (!isset($row->owner->attributes)) {
Survey::model()->updateByPk($row->sid, array('owner_id' => 1));
}
}
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 156), "stg_name='DBVersion'");
$oTransaction->commit();
$oTransaction = Yii::app()->db->beginTransaction();
}
if ($oldversion < 157) {
// MySQL DB corrections
try {
setTransactionBookmark();
Yii::app()->db->createCommand()->dropIndex('questions_idx4', '{{questions}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{answers}}', 'assessment_value', 'integer', false, '0');
dropPrimaryKey('answers');
alterColumn('{{answers}}', 'scale_id', 'integer', false, '0');
addPrimaryKey('answers', array('qid', 'code', 'language', 'scale_id'));
alterColumn('{{conditions}}', 'method', "{$sVarchar}(5)", false, '');
alterColumn('{{participants}}', 'owner_uid', 'integer', false);
alterColumn('{{participant_attribute_names}}', 'visible', $sVarchar . '(5)', false);
alterColumn('{{questions}}', 'type', "{$sVarchar}(1)", false, 'T');
alterColumn('{{questions}}', 'other', "{$sVarchar}(1)", false, 'N');
alterColumn('{{questions}}', 'mandatory', "{$sVarchar}(1)");
alterColumn('{{questions}}', 'scale_id', 'integer', false, '0');
alterColumn('{{questions}}', 'parent_qid', 'integer', false, '0');
alterColumn('{{questions}}', 'same_default', 'integer', false, '0');
alterColumn('{{quota}}', 'qlimit', 'integer');
alterColumn('{{quota}}', 'action', 'integer');
alterColumn('{{quota}}', 'active', 'integer', false, '1');
alterColumn('{{quota}}', 'autoload_url', 'integer', false, '0');
alterColumn('{{saved_control}}', 'status', "{$sVarchar}(1)", false, '');
try {
setTransactionBookmark();
alterColumn('{{sessions}}', 'id', "{$sVarchar}(32)", false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{surveys}}', 'active', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'anonymized', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'format', "{$sVarchar}(1)");
alterColumn('{{surveys}}', 'savetimings', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'datestamp', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'usecookie', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'allowregister', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'allowsave', "{$sVarchar}(1)", false, 'Y');
alterColumn('{{surveys}}', 'autonumber_start', 'integer', false, '0');
alterColumn('{{surveys}}', 'autoredirect', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'allowprev', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'printanswers', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'ipaddr', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'refurl', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'publicstatistics', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'publicgraphs', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'listpublic', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'htmlemail', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'sendconfirmation', "{$sVarchar}(1)", false, 'Y');
alterColumn('{{surveys}}', 'tokenanswerspersistence', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'assessments', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'usecaptcha', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'usetokens', "{$sVarchar}(1)", false, 'N');
alterColumn('{{surveys}}', 'tokenlength', 'integer', false, '15');
alterColumn('{{surveys}}', 'showxquestions', "{$sVarchar}(1)", true, 'Y');
alterColumn('{{surveys}}', 'showgroupinfo', "{$sVarchar}(1) ", true, 'B');
alterColumn('{{surveys}}', 'shownoanswer', "{$sVarchar}(1) ", true, 'Y');
alterColumn('{{surveys}}', 'showqnumcode', "{$sVarchar}(1) ", true, 'X');
alterColumn('{{surveys}}', 'bouncetime', 'integer');
alterColumn('{{surveys}}', 'showwelcome', "{$sVarchar}(1)", true, 'Y');
alterColumn('{{surveys}}', 'showprogress', "{$sVarchar}(1)", true, 'Y');
alterColumn('{{surveys}}', 'allowjumps', "{$sVarchar}(1)", true, 'N');
alterColumn('{{surveys}}', 'navigationdelay', 'integer', false, '0');
alterColumn('{{surveys}}', 'nokeyboard', "{$sVarchar}(1)", true, 'N');
alterColumn('{{surveys}}', 'alloweditaftercompletion', "{$sVarchar}(1)", true, 'N');
alterColumn('{{surveys}}', 'googleanalyticsstyle', "{$sVarchar}(1)");
alterColumn('{{surveys_languagesettings}}', 'surveyls_dateformat', 'integer', false, 1);
try {
setTransactionBookmark();
alterColumn('{{survey_permissions}}', 'sid', "integer", false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
alterColumn('{{survey_permissions}}', 'uid', "integer", false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{survey_permissions}}', 'create_p', 'integer', false, '0');
alterColumn('{{survey_permissions}}', 'read_p', 'integer', false, '0');
alterColumn('{{survey_permissions}}', 'update_p', 'integer', false, '0');
alterColumn('{{survey_permissions}}', 'delete_p', 'integer', false, '0');
alterColumn('{{survey_permissions}}', 'import_p', 'integer', false, '0');
alterColumn('{{survey_permissions}}', 'export_p', 'integer', false, '0');
alterColumn('{{survey_url_parameters}}', 'targetqid', 'integer');
alterColumn('{{survey_url_parameters}}', 'targetsqid', 'integer');
alterColumn('{{templates_rights}}', 'use', 'integer', false);
alterColumn('{{users}}', 'create_survey', 'integer', false, '0');
alterColumn('{{users}}', 'create_user', 'integer', false, '0');
alterColumn('{{users}}', 'participant_panel', 'integer', false, '0');
alterColumn('{{users}}', 'delete_user', 'integer', false, '0');
alterColumn('{{users}}', 'superadmin', 'integer', false, '0');
alterColumn('{{users}}', 'configurator', 'integer', false, '0');
alterColumn('{{users}}', 'manage_template', 'integer', false, '0');
alterColumn('{{users}}', 'manage_label', 'integer', false, '0');
alterColumn('{{users}}', 'dateformat', 'integer', false, 1);
alterColumn('{{users}}', 'participant_panel', 'integer', false, '0');
alterColumn('{{users}}', 'parent_id', 'integer', false);
try {
setTransactionBookmark();
alterColumn('{{surveys_languagesettings}}', 'surveyls_survey_id', "integer", false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{user_groups}}', 'owner_id', "integer", false);
alterColumn('{{user_in_groups}}', 'ugid', "integer", false);
alterColumn('{{user_in_groups}}', 'uid', "integer", false);
// Additional corrections for Postgres
try {
setTransactionBookmark();
Yii::app()->db->createCommand()->createIndex('questions_idx3', '{{questions}}', 'gid');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
Yii::app()->db->createCommand()->createIndex('conditions_idx3', '{{conditions}}', 'cqid');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
Yii::app()->db->createCommand()->createIndex('questions_idx4', '{{questions}}', 'type');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
Yii::app()->db->createCommand()->dropIndex('user_in_groups_idx1', '{{user_in_groups}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
Yii::app()->db->createCommand()->dropIndex('{{user_name_key}}', '{{users}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
Yii::app()->db->createCommand()->createIndex('users_name', '{{users}}', 'users_name', true);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
addPrimaryKey('user_in_groups', array('ugid', 'uid'));
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{participant_attribute}}', 'value', "{$sVarchar}(50)", false);
try {
setTransactionBookmark();
alterColumn('{{participant_attribute_names}}', 'attribute_type', "{$sVarchar}(4)", false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
dropColumn('{{participant_attribute_names_lang}}', 'id');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
addPrimaryKey('participant_attribute_names_lang', array('attribute_id', 'lang'));
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
Yii::app()->db->createCommand()->renameColumn('{{participant_shares}}', 'shared_uid', 'share_uid');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{participant_shares}}', 'date_added', "datetime", false);
alterColumn('{{participants}}', 'firstname', "{$sVarchar}(40)");
alterColumn('{{participants}}', 'lastname', "{$sVarchar}(40)");
alterColumn('{{participants}}', 'email', "{$sVarchar}(80)");
alterColumn('{{participants}}', 'language', "{$sVarchar}(40)");
alterColumn('{{quota_languagesettings}}', 'quotals_name', "string");
try {
setTransactionBookmark();
alterColumn('{{survey_permissions}}', 'sid', 'integer', false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
alterColumn('{{survey_permissions}}', 'uid', 'integer', false);
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
alterColumn('{{users}}', 'htmleditormode', "{$sVarchar}(7)", true, 'default');
// Sometimes the survey_links table was deleted before this step, if so
// we recreate it (copied from line 663)
if (!tableExists('{survey_links}')) {
createTable('{{survey_links}}', array('participant_id' => $sVarchar . '(50) NOT NULL', 'token_id' => 'integer NOT NULL', 'survey_id' => 'integer NOT NULL', 'date_created' => 'datetime NOT NULL'));
addPrimaryKey('survey_links', array('participant_id', 'token_id', 'survey_id'));
}
alterColumn('{{survey_links}}', 'date_created', "datetime", true);
alterColumn('{{saved_control}}', 'identifier', "text", false);
alterColumn('{{saved_control}}', 'email', "{$sVarchar}(320)");
alterColumn('{{surveys}}', 'adminemail', "{$sVarchar}(320)");
alterColumn('{{surveys}}', 'bounce_email', "{$sVarchar}(320)");
alterColumn('{{users}}', 'email', "{$sVarchar}(320)");
try {
setTransactionBookmark();
Yii::app()->db->createCommand()->dropIndex('assessments_idx', '{{assessments}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
Yii::app()->db->createCommand()->createIndex('assessments_idx3', '{{assessments}}', 'gid');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
Yii::app()->db->createCommand()->dropIndex('ixcode', '{{labels}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
Yii::app()->db->createCommand()->dropIndex('{{labels_ixcode_idx}}', '{{labels}}');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
Yii::app()->db->createCommand()->createIndex('labels_code_idx', '{{labels}}', 'code');
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
if ($sDBDriverName == 'pgsql') {
try {
setTransactionBookmark();
Yii::app()->db->createCommand("ALTER TABLE ONLY {{user_groups}} ADD PRIMARY KEY (ugid); ")->execute;
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
try {
setTransactionBookmark();
Yii::app()->db->createCommand("ALTER TABLE ONLY {{users}} ADD PRIMARY KEY (uid); ")->execute;
} catch (Exception $e) {
rollBackToTransactionBookmark();
}
}
// Additional corrections for MSSQL
alterColumn('{{answers}}', 'answer', "text", false);
alterColumn('{{assessments}}', 'name', "text", false);
alterColumn('{{assessments}}', 'message', "text", false);
alterColumn('{{defaultvalues}}', 'defaultvalue', "text");
alterColumn('{{expression_errors}}', 'eqn', "text");
alterColumn('{{expression_errors}}', 'prettyprint', "text");
alterColumn('{{groups}}', 'description', "text");
alterColumn('{{groups}}', 'grelevance', "text");
alterColumn('{{labels}}', 'title', "text");
alterColumn('{{question_attributes}}', 'value', "text");
alterColumn('{{questions}}', 'preg', "text");
alterColumn('{{questions}}', 'help', "text");
alterColumn('{{questions}}', 'relevance', "text");
alterColumn('{{questions}}', 'question', "text", false);
alterColumn('{{quota_languagesettings}}', 'quotals_quota_id', "integer", false);
alterColumn('{{quota_languagesettings}}', 'quotals_message', "text", false);
alterColumn('{{saved_control}}', 'refurl', "text");
alterColumn('{{saved_control}}', 'access_code', "text", false);
alterColumn('{{saved_control}}', 'ip', "text", false);
alterColumn('{{saved_control}}', 'saved_thisstep', "text", false);
alterColumn('{{saved_control}}', 'saved_date', "datetime", false);
alterColumn('{{surveys}}', 'attributedescriptions', "text");
alterColumn('{{surveys}}', 'emailresponseto', "text");
alterColumn('{{surveys}}', 'emailnotificationto', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_description', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_welcometext', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_invite', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_remind', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_register', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_email_confirm', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_attributecaptions', "text");
alterColumn('{{surveys_languagesettings}}', 'email_admin_notification', "text");
alterColumn('{{surveys_languagesettings}}', 'email_admin_responses', "text");
alterColumn('{{surveys_languagesettings}}', 'surveyls_endtext', "text");
alterColumn('{{user_groups}}', 'description', "text", false);
alterColumn('{{conditions}}', 'value', 'string', false, '');
alterColumn('{{participant_shares}}', 'can_edit', "{$sVarchar}(5)", false);
alterColumn('{{users}}', 'password', "binary", false);
dropColumn('{{users}}', 'one_time_pw');
addColumn('{{users}}', 'one_time_pw', 'binary');
Yii::app()->db->createCommand()->update('{{question_attributes}}', array('value' => '1'), "attribute = 'random_order' and value = '2'");
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 157), "stg_name='DBVersion'");
}
if ($oldversion < 158) {
LimeExpressionManager::UpgradeConditionsToRelevance();
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 158), "stg_name='DBVersion'");
}
if ($oldversion < 159) {
alterColumn('{{failed_login_attempts}}', 'ip', "{$sVarchar}(40)", false);
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 159), "stg_name='DBVersion'");
}
if ($oldversion < 160) {
alterLanguageCode('it', 'it-informal');
alterLanguageCode('it-formal', 'it');
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 160), "stg_name='DBVersion'");
}
if ($oldversion < 161) {
addColumn('{{survey_links}}', 'date_invited', 'datetime NULL default NULL');
addColumn('{{survey_links}}', 'date_completed', 'datetime NULL default NULL');
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 161), "stg_name='DBVersion'");
}
if ($oldversion < 162) {
/* Fix participant db types */
alterColumn('{{participant_attribute}}', 'value', "text", false);
alterColumn('{{participant_attribute_names_lang}}', 'attribute_name', "{$sVarchar}(255)", false);
alterColumn('{{participant_attribute_values}}', 'value', "text", false);
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 162), "stg_name='DBVersion'");
}
if ($oldversion < 163) {
//Replace by <script type="text/javascript" src="{TEMPLATEURL}template.js"></script> by {TEMPLATEJS}
$replacedTemplate = replaceTemplateJS();
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 163), "stg_name='DBVersion'");
}
if ($oldversion < 164) {
// fix survey tables for missing or incorrect token field
upgradeSurveyTables164();
Yii::app()->db->createCommand()->update('{{settings_global}}', array('stg_value' => 164), "stg_name='DBVersion'");
// Not updating settings table as upgrade process takes care of that step now
}
$oTransaction->commit();
} catch (Exception $e) {
$oTransaction->rollback();
echo '<br /><br />' . $clang->gT('An non-recoverable error happened during the update. Error details:') . "<p>" . htmlspecialchars($e->getMessage()) . '</p><br />';
return false;
}
fixLanguageConsistencyAllSurveys();
echo '<br /><br />' . sprintf($clang->gT('Database update finished (%s)'), date('Y-m-d H:i:s')) . '<br /><br />';
return true;
}
/**
* This function checks the LimeSurvey database for logical consistency and returns an according array
* containing all issues in the particular tables.
* @returns Array with all found issues.
*/
protected function _checkintegrity()
{
$clang = Yii::app()->lang;
/*** Plainly delete survey permissions if the survey or user does not exist ***/
$users = User::model()->findAll();
$uids = array();
foreach ($users as $user) {
$uids[] = $user['uid'];
}
$oCriteria = new CDbCriteria();
$oCriteria->addNotInCondition('uid', $uids, 'OR');
$surveys = Survey::model()->findAll();
$sids = array();
foreach ($surveys as $survey) {
$sids[] = $survey['sid'];
}
$oCriteria->addNotInCondition('entity_id', $sids, 'OR');
$oCriteria->addCondition("entity='survey'");
Permission::model()->deleteAll($oCriteria);
// Deactivate surveys that have a missing response table
foreach ($surveys as $survey) {
if ($survey['active'] == 'Y' && !tableExists("{{survey_{$survey['sid']}}}")) {
Survey::model()->updateByPk($survey['sid'], array('active' => 'N'));
}
}
unset($surveys);
// Fix subquestions
fixSubquestions();
/*** Check for active survey tables with missing survey entry and rename them ***/
$sDBPrefix = Yii::app()->db->tablePrefix;
$sQuery = dbSelectTablesLike('{{survey}}\\_%');
$aResult = dbQueryOrFalse($sQuery);
foreach ($aResult->readAll() as $aRow) {
$sTableName = substr(reset($aRow), strlen($sDBPrefix));
if ($sTableName == 'survey_links' || $sTableName == 'survey_url_parameters') {
continue;
}
$aTableName = explode('_', $sTableName);
if (isset($aTableName[1]) && ctype_digit($aTableName[1])) {
$iSurveyID = $aTableName[1];
if (!in_array($iSurveyID, $sids)) {
$sDate = date('YmdHis') . rand(1, 1000);
$sOldTable = "survey_{$iSurveyID}";
$sNewTable = "old_survey_{$iSurveyID}_{$sDate}";
try {
$deactivateresult = Yii::app()->db->createCommand()->renameTable("{{{$sOldTable}}}", "{{{$sNewTable}}}");
} catch (CDbException $e) {
die('Couldn\'t make backup of the survey table. Please try again. The database reported the following error:<br />' . htmlspecialchars($e) . '<br />');
}
}
}
}
/*** Check for active token tables with missing survey entry ***/
$aResult = dbQueryOrFalse(dbSelectTablesLike('{{tokens}}\\_%'));
foreach ($aResult->readAll() as $aRow) {
$sTableName = substr(reset($aRow), strlen($sDBPrefix));
$iSurveyID = substr($sTableName, strpos($sTableName, '_') + 1);
if (!in_array($iSurveyID, $sids)) {
$sDate = date('YmdHis') . rand(1, 1000);
$sOldTable = "tokens_{$iSurveyID}";
$sNewTable = "old_tokens_{$iSurveyID}_{$sDate}";
try {
$deactivateresult = Yii::app()->db->createCommand()->renameTable("{{{$sOldTable}}}", "{{{$sNewTable}}}");
} catch (CDbException $e) {
die('Couldn\'t make backup of the survey table. Please try again. The database reported the following error:<br />' . htmlspecialchars($e) . '<br />');
}
}
}
/**********************************************************************/
/* Check conditions */
/**********************************************************************/
$okQuestion = array();
$sQuery = 'SELECT cqid,cid,cfieldname FROM {{conditions}}';
$aConditions = Yii::app()->db->createCommand($sQuery)->queryAll();
foreach ($aConditions as $condition) {
if ($condition['cqid'] != 0) {
// skip case with cqid=0 for codnitions on {TOKEN:EMAIL} for instance
if (!array_key_exists($condition['cqid'], $okQuestion)) {
$iRowCount = Question::model()->countByAttributes(array('qid' => $condition['cqid']));
if (Question::model()->hasErrors()) {
safeDie(Question::model()->getError());
}
if (!$iRowCount) {
$aDelete['conditions'][] = array('cid' => $condition['cid'], 'reason' => $clang->gT('No matching CQID'));
} else {
$okQuestion[$condition['cqid']] = $condition['cqid'];
}
}
}
if ($condition['cfieldname']) {
if (preg_match('/^\\+{0,1}[0-9]+X[0-9]+X*$/', $condition['cfieldname'])) {
// only if cfieldname isn't Tag such as {TOKEN:EMAIL} or any other token
list($surveyid, $gid, $rest) = explode('X', $condition['cfieldname']);
$iRowCount = count(QuestionGroup::model()->findAllByAttributes(array('gid' => $gid)));
if (QuestionGroup::model()->hasErrors()) {
safeDie(QuestionGroup::model()->getError());
}
if (!$iRowCount) {
$aDelete['conditions'][] = array('cid' => $condition['cid'], 'reason' => $clang->gT('No matching CFIELDNAME group!') . " ({$gid}) ({$condition['cfieldname']})");
}
}
} elseif (!$condition['cfieldname']) {
$aDelete['conditions'][] = array('cid' => $condition['cid'], 'reason' => $clang->gT('No CFIELDNAME field set!') . " ({$condition['cfieldname']})");
}
}
unset($okQuestion);
unset($aConditions);
/**********************************************************************/
/* Check question attributes */
/**********************************************************************/
$question_attributes = QuestionAttribute::model()->findAllBySql('select qid from {{question_attributes}} where qid not in (select qid from {{questions}})');
if (QuestionAttribute::model()->hasErrors()) {
safeDie(QuestionAttribute::model()->getError());
}
foreach ($question_attributes as $question_attribute) {
$aDelete['questionattributes'][] = array('qid' => $question_attribute['qid']);
}
// foreach
/**********************************************************************/
/* Check default values */
/**********************************************************************/
$oCriteria = new CDbCriteria();
$oCriteria->join = 'LEFT JOIN {{questions}} q ON t.qid=q.qid';
$oCriteria->condition = 'q.qid IS NULL';
$aRecords = DefaultValue::model()->findAll($oCriteria);
$aDelete['defaultvalues'] = count($aRecords);
if (DefaultValue::model()->hasErrors()) {
safeDie(DefaultValue::model()->getError());
}
/**********************************************************************/
/* Check quotas */
/**********************************************************************/
$surveys = Survey::model()->findAll();
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
$sids = array();
foreach ($surveys as $survey) {
$sids[] = $survey['sid'];
}
$oCriteria = new CDbCriteria();
$oCriteria->addNotInCondition('sid', $sids);
$aDelete['quotas'] = count(Quota::model()->findAll($oCriteria));
if (Quota::model()->hasErrors()) {
safeDie(Quota::model()->getError());
}
/**********************************************************************/
/* Check quota languagesettings */
/**********************************************************************/
$quotas = Quota::model()->findAll();
if (Quota::model()->hasErrors()) {
safeDie(Quota::model()->getError());
}
$ids = array();
foreach ($quotas as $quota) {
$ids[] = $quota['id'];
}
$oCriteria = new CDbCriteria();
$oCriteria->addNotInCondition('quotals_quota_id', $ids);
$aDelete['quotals'] = count(QuotaLanguageSetting::model()->findAll($oCriteria));
if (QuotaLanguageSetting::model()->hasErrors()) {
safeDie(QuotaLanguageSetting::model()->getError());
}
/**********************************************************************/
/* Check quota members */
/**********************************************************************/
$oCriteria = new CDbCriteria();
$oCriteria->join = 'LEFT JOIN {{questions}} q ON t.qid=q.qid LEFT JOIN {{surveys}} s ON t.sid=s.sid';
$oCriteria->condition = '(q.qid IS NULL) OR (s.sid IS NULL)';
$aDelete['quotamembers'] = count(QuotaMember::model()->findAll($oCriteria));
if (QuotaMember::model()->hasErrors()) {
safeDie(QuotaMember::model()->getError());
}
/**********************************************************************/
/* Check assessments */
/**********************************************************************/
$oCriteria = new CDbCriteria();
$oCriteria->compare('scope', 'T');
$assessments = Assessment::model()->findAll($oCriteria);
if (Assessment::model()->hasErrors()) {
safeDie(Assessment::model()->getError());
}
foreach ($assessments as $assessment) {
$iAssessmentCount = count(Survey::model()->findAllByPk($assessment['sid']));
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
if (!$iAssessmentCount) {
$aDelete['assessments'][] = array('id' => $assessment['id'], 'assessment' => $assessment['name'], 'reason' => $clang->gT('No matching survey'));
}
}
$oCriteria = new CDbCriteria();
$oCriteria->compare('scope', 'G');
$assessments = Assessment::model()->findAll($oCriteria);
if (Assessment::model()->hasErrors()) {
safeDie(Assessment::model()->getError());
}
foreach ($assessments as $assessment) {
$iAssessmentCount = count(QuestionGroup::model()->findAllByPk(array('gid' => $assessment['gid'], 'language' => $assessment['language'])));
if (QuestionGroup::model()->hasErrors()) {
safeDie(QuestionGroup::model()->getError());
}
if (!$iAssessmentCount) {
$aDelete['assessments'][] = array('id' => $assessment['id'], 'assessment' => $assessment['name'], 'reason' => $clang->gT('No matching group'));
}
}
unset($assessments);
/**********************************************************************/
/* Check answers */
/**********************************************************************/
$oCriteria = new CDbCriteria();
$oCriteria->join = 'LEFT JOIN {{questions}} q ON t.qid=q.qid';
$oCriteria->condition = '(q.qid IS NULL)';
$answers = Answer::model()->findAll($oCriteria);
foreach ($answers as $answer) {
$aDelete['answers'][] = array('qid' => $answer['qid'], 'code' => $answer['code'], 'reason' => $clang->gT('No matching question'));
}
/***************************************************************************/
/* Check survey languagesettings and restore them if they don't exist */
/***************************************************************************/
$surveys = Survey::model()->findAll();
foreach ($surveys as $survey) {
$aLanguages = $survey->additionalLanguages;
$aLanguages[] = $survey->language;
foreach ($aLanguages as $langname) {
if ($langname) {
$oLanguageSettings = SurveyLanguageSetting::model()->find('surveyls_survey_id=:surveyid AND surveyls_language=:langname', array(':surveyid' => $survey->sid, ':langname' => $langname));
if (!$oLanguageSettings) {
$oLanguageSettings = new SurveyLanguageSetting();
$languagedetails = getLanguageDetails($langname);
$insertdata = array('surveyls_survey_id' => $survey->sid, 'surveyls_language' => $langname, 'surveyls_title' => '', 'surveyls_dateformat' => $languagedetails['dateformat']);
foreach ($insertdata as $k => $v) {
$oLanguageSettings->{$k} = $v;
}
$usresult = $oLanguageSettings->save();
}
}
}
}
/**********************************************************************/
/* Check survey language settings */
/**********************************************************************/
$surveys = Survey::model()->findAll();
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
$sids = array();
foreach ($surveys as $survey) {
$sids[] = $survey['sid'];
}
$oCriteria = new CDbCriteria();
$oCriteria->addNotInCondition('surveyls_survey_id', $sids);
$surveys_languagesettings = SurveyLanguageSetting::model()->findAll($oCriteria);
if (SurveyLanguageSetting::model()->hasErrors()) {
safeDie(SurveyLanguageSetting::model()->getError());
}
foreach ($surveys_languagesettings as $surveys_languagesetting) {
$aDelete['surveylanguagesettings'][] = array('slid' => $surveys_languagesetting['surveyls_survey_id'], 'reason' => $clang->gT('The related survey is missing.'));
}
/**********************************************************************/
/* Check questions */
/**********************************************************************/
$oCriteria = new CDbCriteria();
$oCriteria->join = 'LEFT JOIN {{surveys}} s ON t.sid=s.sid LEFT JOIN {{groups}} g ON t.gid=g.gid';
$oCriteria->condition = '(g.gid IS NULL) OR (s.sid IS NULL)';
$questions = Question::model()->findAll($oCriteria);
if (Question::model()->hasErrors()) {
safeDie(Question::model()->getError());
}
foreach ($questions as $question) {
$aDelete['questions'][] = array('qid' => $question['qid'], 'reason' => $clang->gT('No matching group') . " ({$question['gid']})");
}
/**********************************************************************/
/* Check groups */
/**********************************************************************/
$surveys = Survey::model()->findAll();
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
$sids = array();
foreach ($surveys as $survey) {
$sids[] = $survey['sid'];
}
$oCriteria = new CDbCriteria();
$oCriteria->addNotInCondition('sid', $sids);
$groups = QuestionGroup::model()->findAll($oCriteria);
foreach ($groups as $group) {
$aDelete['groups'][] = array('gid' => $group['gid'], 'reason' => $clang->gT('There is no matching survey.') . ' SID:' . $group['sid']);
}
/**********************************************************************/
/* Check old survey tables */
/**********************************************************************/
//1: Get list of 'old_survey' tables and extract the survey id
//2: Check if that survey id still exists
//3: If it doesn't offer it for deletion
$sQuery = dbSelectTablesLike('{{old_survey}}%');
$aTables = Yii::app()->db->createCommand($sQuery)->queryColumn();
$aOldSIDs = array();
$aSIDs = array();
foreach ($aTables as $sTable) {
list($sOldText, $SurveyText, $iSurveyID, $sDate) = explode('_', substr($sTable, strlen($sDBPrefix)));
$aOldSIDs[] = $iSurveyID;
$aFullOldSIDs[$iSurveyID][] = $sTable;
}
$aOldSIDs = array_unique($aOldSIDs);
//$sQuery = 'SELECT sid FROM {{surveys}} ORDER BY sid';
//$oResult = dbExecuteAssoc($sQuery) or safeDie('Couldn\'t get unique survey ids');
$surveys = Survey::model()->findAll();
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
$aSIDs = array();
foreach ($surveys as $survey) {
$aSIDs[] = $survey['sid'];
}
foreach ($aOldSIDs as $iOldSID) {
if (!in_array($iOldSID, $aSIDs)) {
foreach ($aFullOldSIDs[$iOldSID] as $sTableName) {
$aDelete['orphansurveytables'][] = $sTableName;
}
} else {
foreach ($aFullOldSIDs[$iOldSID] as $sTableName) {
$aTableParts = explode('_', substr($sTableName, strlen($sDBPrefix)));
if (count($aTableParts) == 4) {
$sOldText = $aTableParts[0];
$SurveyText = $aTableParts[1];
$iSurveyID = $aTableParts[2];
$sDateTime = $aTableParts[3];
$sType = $clang->gT('responses');
} elseif (count($aTableParts) == 5) {
//This is a timings table (
$sOldText = $aTableParts[0];
$SurveyText = $aTableParts[1];
$iSurveyID = $aTableParts[2];
$sDateTime = $aTableParts[4];
$sType = $clang->gT('timings');
}
$iYear = substr($sDateTime, 0, 4);
$iMonth = substr($sDateTime, 4, 2);
$iDay = substr($sDateTime, 6, 2);
$iHour = substr($sDateTime, 8, 2);
$iMinute = substr($sDateTime, 10, 2);
$sDate = date('d M Y H:i', mktime($iHour, $iMinute, 0, $iMonth, $iDay, $iYear));
$sQuery = 'SELECT count(*) as recordcount FROM ' . $sTableName;
$aFirstRow = Yii::app()->db->createCommand($sQuery)->queryRow();
if ($aFirstRow['recordcount'] == 0) {
// empty table - so add it to immediate deletion
$aDelete['orphansurveytables'][] = $sTableName;
} else {
$aOldSurveyTableAsk[] = array('table' => $sTableName, 'details' => sprintf($clang->gT('Survey ID %d saved at %s containing %d record(s) (%s)'), $iSurveyID, $sDate, $aFirstRow['recordcount'], $sType));
}
}
}
}
/**********************************************************************/
/* CHECK OLD TOKEN TABLES */
/**********************************************************************/
//1: Get list of 'old_token' tables and extract the survey id
//2: Check if that survey id still exists
//3: If it doesn't offer it for deletion
$sQuery = dbSelectTablesLike('{{old_token}}%');
$aTables = Yii::app()->db->createCommand($sQuery)->queryColumn();
$aOldTokenSIDs = array();
$aTokenSIDs = array();
$aFullOldTokenSIDs = array();
foreach ($aTables as $sTable) {
list($sOldText, $SurveyText, $iSurveyID, $sDateTime) = explode('_', substr($sTable, strlen($sDBPrefix)));
$aTokenSIDs[] = $iSurveyID;
$aFullOldTokenSIDs[$iSurveyID][] = $sTable;
}
$aOldTokenSIDs = array_unique($aTokenSIDs);
$surveys = Survey::model()->findAll();
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
$aSIDs = array();
foreach ($surveys as $survey) {
$aSIDs[] = $survey['sid'];
}
foreach ($aOldTokenSIDs as $iOldTokenSID) {
if (!in_array($iOldTokenSID, $aOldTokenSIDs)) {
foreach ($aFullOldTokenSIDs[$iOldTokenSID] as $sTableName) {
$aDelete['orphantokentables'][] = $sTableName;
}
} else {
foreach ($aFullOldTokenSIDs[$iOldTokenSID] as $sTableName) {
list($sOldText, $sTokensText, $iSurveyID, $sDateTime) = explode('_', substr($sTableName, strlen($sDBPrefix)));
$iYear = substr($sDateTime, 0, 4);
$iMonth = substr($sDateTime, 4, 2);
$iDay = substr($sDateTime, 6, 2);
$iHour = substr($sDateTime, 8, 2);
$iMinute = substr($sDateTime, 10, 2);
$sDate = date('D, d M Y h:i a', mktime($iHour, $iMinute, 0, $iMonth, $iDay, $iYear));
$sQuery = 'SELECT count(*) as recordcount FROM ' . $sTableName;
$aFirstRow = Yii::app()->db->createCommand($sQuery)->queryRow();
if ($aFirstRow['recordcount'] == 0) {
// empty table - so add it to immediate deletion
$aDelete['orphantokentables'][] = $sTableName;
} else {
$aOldTokenTableAsk[] = array('table' => $sTableName, 'details' => sprintf($clang->gT('Survey ID %d saved at %s containing %d record(s)'), $iSurveyID, $sDate, $aFirstRow['recordcount']));
}
}
}
}
if ($aDelete['defaultvalues'] == 0 && $aDelete['quotamembers'] == 0 && $aDelete['quotas'] == 0 && $aDelete['quotals'] == 0 && count($aDelete) == 4) {
$aDelete['integrityok'] = true;
} else {
$aDelete['integrityok'] = false;
}
if (!isset($aOldTokenTableAsk) && !isset($aOldSurveyTableAsk)) {
$aDelete['redundancyok'] = true;
} else {
$aDelete['redundancyok'] = false;
$aDelete['redundanttokentables'] = array();
$aDelete['redundantsurveytables'] = array();
if (isset($aOldTokenTableAsk)) {
$aDelete['redundanttokentables'] = $aOldTokenTableAsk;
}
if (isset($aOldSurveyTableAsk)) {
$aDelete['redundantsurveytables'] = $aOldSurveyTableAsk;
}
}
/**********************************************************************/
/* CHECK CPDB SURVEY_LINKS TABLE FOR REDUNDENT TOKEN TABLES */
/**********************************************************************/
//1: Get distinct list of survey_link survey ids, check if tokens
// table still exists for each one, and remove if not
/* TODO */
/**********************************************************************/
/* CHECK CPDB SURVEY_LINKS TABLE FOR REDUNDENT TOKEN ENTRIES */
/**********************************************************************/
//1: For each survey_link, see if the matching entry still exists in
// the token table and remove if it doesn't.
/* TODO */
return $aDelete;
}
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;
}
function db_upgrade($oldversion)
{
global $modifyoutput, $databasename, $databasetabletype, $clang;
if ($oldversion < 127) {
modify_database("", "create index answers_idx2 on prefix_answers (sortorder)");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "create index assessments_idx2 on prefix_assessments (sid)");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "create index assessments_idx 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 conditions_idx3 on prefix_conditions (cqid)");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "create index groups_idx2 on prefix_groups (sid)");
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 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 questions_idx4 on prefix_questions (type)");
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("", "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 date");
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 integer NOT NULL default '1'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "UPDATE prefix_conditions SET scenario=1 where (scenario is null) 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 bytea");
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 integer NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_labels ADD assessment_value integer 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 integer)");
// no output here is intended
modify_database("", "update prefix_labels set assessment_value=CAST(code as integer)");
// 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 character varying(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 character) ||'<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 CONSTRAINT prefix_assessments_pkey");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_assessments ADD CONSTRAINT prefix_assessments_pkey PRIMARY KEY (id,language)");
echo $modifyoutput;
flush();
ob_flush();
// Add new fields to survey language settings
modify_database("", "ALTER TABLE prefix_surveys_languagesettings ADD surveyls_url character varying(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) {
modify_database("", "ALTER TABLE prefix_surveys ADD usetokens char(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 ALTER COLUMN value TYPE 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 integer NOT NULL DEFAULT 0");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE prefix_quota_languagesettings (\r\n quotals_id serial NOT NULL,\r\n quotals_quota_id integer NOT NULL DEFAULT 0,\r\n quotals_language character varying(45) NOT NULL DEFAULT 'en'::character varying,\r\n quotals_name character varying(200),\r\n quotals_message text NOT NULL,\r\n quotals_url character varying(255),\r\n quotals_urldescrip character varying(255));");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE ONLY prefix_quota_languagesettings\r\n \t \t\t\t\t\t ADD CONSTRAINT prefix_quota_languagesettings_pkey PRIMARY KEY (quotals_id);");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE ONLY prefix_users ADD CONSTRAINT prefix_users_pkey PRIMARY KEY (uid)");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE ONLY prefix_users ADD CONSTRAINT prefix_user_name_key UNIQUE (users_name)");
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 integer NOT NULL default 1");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_users ADD \"dateformat\" integer 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 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 TYPE character varying(11)");
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\" smallint 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\" TYPE timestamp");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_surveys ALTER COLUMN \"expires\" TYPE timestamp");
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 smallint NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_questions ADD scale_id smallint NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_questions ADD same_default smallint NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_answers DROP CONSTRAINT prefix_answers_pkey");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_answers ADD CONSTRAINT prefix_answers_pkey PRIMARY KEY (qid,code,language,scale_id)");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE prefix_defaultvalues (\r\n qid integer NOT NULL default '0',\r\n scale_id integer NOT NULL default '0',\r\n sqid integer NOT NULL default '0',\r\n language character varying(20) NOT NULL,\r\n specialtype character varying(20) NOT NULL default '',\r\n defaultvalue text)");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_defaultvalues ADD CONSTRAINT prefix_defaultvalues_pkey 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();
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();
// 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(\r\n sesskey VARCHAR( 64 ) NOT NULL DEFAULT '',\r\n expiry TIMESTAMP NOT NULL ,\r\n expireref VARCHAR( 250 ) DEFAULT '',\r\n created TIMESTAMP NOT NULL ,\r\n modified TIMESTAMP NOT NULL ,\r\n sessdata TEXT DEFAULT '',\r\n PRIMARY KEY ( sesskey )\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "create INDEX sess2_expiry on prefix_sessions( expiry );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "create INDEX sess2_expireref on prefix_sessions ( expireref );");
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'");
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 NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_surveys ADD bounceprocessing character varying(1) NULL default 'N'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_surveys ADD bounceaccounttype character varying(4) NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_surveys ADD bounceaccounthost character varying(200) NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_surveys ADD bounceaccountpass character varying(100) NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_surveys ADD bounceaccountencryption character varying(3) NULL");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_surveys ADD bounceaccountuser character varying(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 smallint NOT NULL 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 integer DEFAULT 0 NOT NULL,\r\n uid integer DEFAULT 0 NOT NULL,\r\n permission character varying(20) NOT NULL,\r\n create_p integer DEFAULT 0 NOT NULL,\r\n read_p integer DEFAULT 0 NOT NULL,\r\n update_p integer DEFAULT 0 NOT NULL,\r\n delete_p integer DEFAULT 0 NOT NULL,\r\n import_p integer DEFAULT 0 NOT NULL,\r\n export_p integer DEFAULT 0 NOT NULL\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE ONLY prefix_survey_permissions ADD CONSTRAINT prefix_survey_permissions_pkey PRIMARY KEY (sid,uid,permission);");
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 email_admin_notification_subj character varying(255)");
modify_database("", "ALTER TABLE prefix_surveys_languagesettings ADD email_admin_responses_subj character varying(255)");
modify_database("", "ALTER TABLE prefix_surveys_languagesettings ADD email_admin_notification text");
modify_database("", "ALTER TABLE prefix_surveys_languagesettings ADD email_admin_responses text");
//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();
modify_database("", "ALTER TABLE prefix_surveys DROP COLUMN notification");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_conditions ALTER COLUMN method TYPE CHAR(5)");
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 RENAME COLUMN private TO anonymized;");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_surveys ALTER COLUMN anonymized TYPE char(1);");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_surveys ALTER COLUMN anonymized SET DEFAULT 'N';");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_surveys ALTER COLUMN anonymized SET NOT NULL ;");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE prefix_failed_login_attempts (\r\n id serial PRIMARY KEY NOT NULL,\r\n ip character varying(37) NOT NULL,\r\n last_attempt character varying(20) NOT NULL,\r\n number_attempts integer NOT NULL\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_surveys_languagesettings ADD surveyls_numberformat integer 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("", "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 character varying(7) NOT NULL DEFAULT 'default'");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_users ADD questionselectormode character varying(7) NOT NULL DEFAULT 'default'");
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("", "CREATE TABLE prefix_participants (\r\n participant_id VARCHAR( 50 ) NOT NULL,\r\n firstname VARCHAR( 40 ) NOT NULL,\r\n lastname VARCHAR( 40 ) NOT NULL,\r\n email VARCHAR( 80 ) NOT NULL,\r\n language VARCHAR( 2 ) NOT NULL,\r\n blacklisted VARCHAR( 1 ) NOT NULL,\r\n owner_uid integer NOT NULL,\r\n PRIMARY KEY (participant_id)\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE prefix_participant_attribute (\r\n participant_id VARCHAR( 50 ) NOT NULL,\r\n attribute_id integer NOT NULL,\r\n value integer NOT NULL,\r\n PRIMARY KEY (participant_id,attribute_id)\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE prefix_participant_attribute_names (\r\n attribute_id integer NOT NULL AUTO_INCREMENT,\r\n attribute_type VARCHAR( 30 ) NOT NULL,\r\n visible CHAR( 5 ) NOT NULL,\r\n PRIMARY KEY (attribute_type,attribute_id)\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE prefix_participant_attribute_names_lang (\r\n id integer NOT NULL AUTO_INCREMENT,\r\n attribute_id integer NOT NULL,\r\n attribute_name VARCHAR( 30 ) NOT NULL,\r\n lang CHAR( 20 ) NOT NULL,\r\n PRIMARY KEY (lang,attribute_id)\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE prefix_participant_attribute_values (\r\n attribute_id integer NOT NULL,\r\n value_id integer NOT NULL AUTO_INCREMENT,\r\n value VARCHAR( 20 ) NOT NULL,\r\n PRIMARY KEY (value_id)\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE prefix_participant_shares (\r\n participant_id VARCHAR( 50 ) NOT NULL,\r\n shared_uid integer NOT NULL,\r\n date_added date NOT NULL,\r\n can_edit VARCHAR( 5 ) NOT NULL,\r\n PRIMARY KEY (lang,attribute_id)\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "CREATE TABLE prefix_survey_links (\r\n participant_id VARCHAR( 50 ) NOT NULL,\r\n token_id integer NOT NULL,\r\n survey_id integer NOT NULL,\r\n date_created date NOT NULL,\r\n PRIMARY KEY (participant_id,token_id,survey_id)\r\n );");
echo $modifyoutput;
flush();
ob_flush();
modify_database("", "ALTER TABLE prefix_user ADD participant_panel integer NOT NULL default '0'");
echo $modifyoutput;
flush();
ob_flush();
// add language field to question_attributes table
modify_database("", "ALTER TABLE prefix_question_attributes ADD language character varying(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 serial PRIMARY KEY NOT NULL,\r\n sid integer NOT NULL,\r\n parameter character varying(50) NOT NULL,\r\n targetqid integer NULL,\r\n targetsqid integer NULL\r\n );");
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 varying(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 integer NOT NULL AUTO_INCREMENT,\r\n errortime varchar(50) DEFAULT NULL,\r\n sid integer DEFAULT NULL,\r\n gid integer DEFAULT NULL,\r\n qid integer DEFAULT NULL,\r\n gseq integer DEFAULT NULL,\r\n qseq integer DEFAULT NULL,\r\n \"type\" character varying(50) ,\r\n eqn text,\r\n prettyprint text,\r\n CONSTRAINT prefix_expression_errors_pkey PRIMARY KEY (id)\r\n );");
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 character varying(25) DEFAULT NULL;");
echo $modifyoutput;
flush();
@ob_flush();
modify_database("", "ALTER TABLE prefix_surveys RENAME COLUMN showXquestions TO showxquestions;");
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;
}
/**
* This function checks the LimeSurvey database for logical consistency and returns an according array
* containing all issues in the particular tables.
* @returns Array with all found issues.
*/
protected function _checkintegrity()
{
$clang = Yii::app()->lang;
/*** Plainly delete survey permissions if the survey or user does not exist ***/
$users = User::model()->findAll();
$uids = array();
foreach ($users as $user) {
$uids[] = $user['uid'];
}
$criteria = new CDbCriteria();
$criteria->addNotInCondition('uid', $uids, 'OR');
$surveys = Survey::model()->findAll();
$sids = array();
foreach ($surveys as $survey) {
$sids[] = $survey['sid'];
}
$criteria->addNotInCondition('sid', $sids, 'OR');
Survey_permissions::model()->deleteAll($criteria);
// Fix subquestions
fixSubquestions();
/*** Check for active survey tables with missing survey entry and rename them ***/
$sDBPrefix = Yii::app()->db->tablePrefix;
$sQuery = dbSelectTablesLike('{{survey}}\\_%');
$aResult = dbQueryOrFalse($sQuery) or safeDie("Couldn't get list of conditions from database<br />{$sQuery}<br />");
foreach ($aResult->readAll() as $aRow) {
$sTableName = substr(reset($aRow), strlen($sDBPrefix));
if ($sTableName == 'survey_permissions' || $sTableName == 'survey_links' || $sTableName == 'survey_url_parameters') {
continue;
}
$iSurveyID = substr($sTableName, strpos($sTableName, '_') + 1);
$count = $surveys = Survey::model()->findAllByPk($iSurveyID);
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
if ($count == 0) {
$sDate = date('YmdHis') . rand(1, 1000);
$sOldTable = "survey_{$iSurveyID}";
$sNewTable = "old_survey_{$iSurveyID}_{$sDate}";
try {
$deactivateresult = Yii::app()->db->createCommand()->renameTable("{{{$sOldTable}}}", "{{{$sNewTable}}}");
} catch (CDbException $e) {
die('Couldn\'t make backup of the survey table. Please try again. The database reported the following error:<br />' . htmlspecialchars($e) . '<br />');
}
}
}
/*** Check for active token tables with missing survey entry ***/
$aResult = dbQueryOrFalse(dbSelectTablesLike('{{tokens}}\\_%')) or safeDie("Couldn't get list of conditions from database<br />{$sQuery}<br />");
foreach ($aResult->readAll() as $aRow) {
$sTableName = substr(reset($aRow), strlen($sDBPrefix));
$iSurveyID = substr($sTableName, strpos($sTableName, '_') + 1);
$count = count(Survey::model()->findAllByPk($iSurveyID));
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
if ($count == 0) {
$sDate = date('YmdHis') . rand(1, 1000);
$sOldTable = "tokens_{$iSurveyID}";
$sNewTable = "old_tokens_{$iSurveyID}_{$sDate}";
try {
$deactivateresult = Yii::app()->db->createCommand()->renameTable("{{{$sOldTable}}}", "{{{$sNewTable}}}");
} catch (CDbException $e) {
die('Couldn\'t make backup of the survey table. Please try again. The database reported the following error:<br />' . htmlspecialchars($e) . '<br />');
}
}
}
/**********************************************************************/
/* Check conditions */
/**********************************************************************/
// TMSW Conditions->Relevance: Replace this with analysis of relevance
$conditions = Conditions::model()->findAll();
if (Conditions::model()->hasErrors()) {
safeDie(Conditions::model()->getError());
}
foreach ($conditions as $condition) {
$iRowCount = count(Questions::model()->findAll());
if (Questions::model()->hasErrors()) {
safeDie(Questions::model()->getError());
}
if (!$iRowCount) {
$aDelete['conditions'][] = array('cid' => $condition['cid'], 'reason' => 'No matching QID');
}
if ($condition['cqid'] != 0) {
// skip case with cqid=0 for codnitions on {TOKEN:EMAIL} for instance
$iRowCount = Questions::model()->countByAttributes(array('qid' => $condition['cqid']));
if (Questions::model()->hasErrors()) {
safeDie(Questions::model()->getError());
}
if (!$iRowCount) {
$aDelete['conditions'][] = array('cid' => $condition['cid'], 'reason' => $clang->gT('No matching CQID'));
}
}
if ($condition['cfieldname']) {
if (preg_match('/^\\+{0,1}[0-9]+X[0-9]+X*$/', $condition['cfieldname'])) {
// only if cfieldname isn't Tag such as {TOKEN:EMAIL} or any other token
list($surveyid, $gid, $rest) = explode('X', $condition['cfieldname']);
$iRowCount = count(Groups::model()->findAllByPk($gid));
if (Groups::model()->hasErrors()) {
safeDie(Groups::model()->getError());
}
if (!$iRowCount) {
$aDelete['conditions'][] = array('cid' => $condition['cid'], 'reason' => $clang->gT('No matching CFIELDNAME group!') . " ({$gid}) ({$condition['cfieldname']})");
}
}
} elseif (!$condition['cfieldname']) {
$aDelete['conditions'][] = array('cid' => $condition['cid'], 'reason' => $clang->gT('No CFIELDNAME field set!') . " ({$condition['cfieldname']})");
}
}
/**********************************************************************/
/* Check question attributes */
/**********************************************************************/
$question_attributes = Question_attributes::model()->findAll();
if (Question_attributes::model()->hasErrors()) {
safeDie(Question_attributes::model()->getError());
}
foreach ($question_attributes as $question_attribute) {
$iRowCount = Questions::model()->countByAttributes(array('qid' => $question_attribute['qid']));
if (Questions::model()->hasErrors()) {
safeDie(Questions::model()->getError());
}
if (!$iRowCount) {
$aDelete['questionattributes'][] = array('qid' => $question_attribute['qid']);
}
}
// foreach
/**********************************************************************/
/* Check default values */
/**********************************************************************/
$questions = Questions::model()->findAll();
if (Questions::model()->hasErrors()) {
safeDie(Questions::model()->getError());
}
$qids = array();
foreach ($questions as $question) {
$qids[] = $question['qid'];
}
$criteria = new CDbCriteria();
$criteria->addNotInCondition('qid', $qids);
$aDelete['defaultvalues'] = count(Defaultvalues::model()->findAll($criteria));
if (Defaultvalues::model()->hasErrors()) {
safeDie(Defaultvalues::model()->getError());
}
/**********************************************************************/
/* Check quotas */
/**********************************************************************/
$surveys = Survey::model()->findAll();
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
$sids = array();
foreach ($surveys as $survey) {
$sids[] = $survey['sid'];
}
$criteria = new CDbCriteria();
$criteria->addNotInCondition('sid', $sids);
$aDelete['quotas'] = count(Quota::model()->findAll($criteria));
if (Quota::model()->hasErrors()) {
safeDie(Quota::model()->getError());
}
/**********************************************************************/
/* Check quota languagesettings */
/**********************************************************************/
$quotas = Quota::model()->findAll();
if (Quota::model()->hasErrors()) {
safeDie(Quota::model()->getError());
}
$ids = array();
foreach ($quotas as $quota) {
$ids[] = $quota['id'];
}
$criteria = new CDbCriteria();
$criteria->addNotInCondition('quotals_quota_id', $ids);
$aDelete['quotals'] = count(Quota_languagesettings::model()->findAll($criteria));
if (Quota_languagesettings::model()->hasErrors()) {
safeDie(Quota_languagesettings::model()->getError());
}
/**********************************************************************/
/* Check quota members */
/**********************************************************************/
$quotas = Quota::model()->findAll();
$quota_ids = array();
foreach ($quotas as $quota) {
$quota_ids[] = $quota['id'];
}
$criteria = new CDbCriteria();
$criteria->addNotInCondition('quota_id', $quota_ids);
$questions = Questions::model()->findAll();
$qids = array();
foreach ($questions as $question) {
$qids[] = $question['qid'];
}
$criteria->addNotInCondition('qid', $qids, 'OR');
$surveys = Survey::model()->findAll();
$sids = array();
foreach ($surveys as $survey) {
$sids[] = $survey['sid'];
}
$criteria->addNotInCondition('sid', $sids, 'OR');
$aDelete['quotamembers'] = count(Quota_members::model()->findAll($criteria));
if (Quota_members::model()->hasErrors()) {
safeDie(Quota_members::model()->getError());
}
/**********************************************************************/
/* Check assessments */
/**********************************************************************/
$criteria = new CDbCriteria();
$criteria->compare('scope', 'T');
$assessments = Assessment::model()->findAll($criteria);
if (Assessment::model()->hasErrors()) {
safeDie(Assessment::model()->getError());
}
foreach ($assessments as $assessment) {
$iAssessmentCount = count(Survey::model()->findAllByPk($assessment['sid']));
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
if (!$iAssessmentCount) {
$aDelete['assessments'][] = array('id' => $assessment['id'], 'assessment' => $assessment['name'], 'reason' => $clang->gT('No matching survey'));
}
}
$criteria = new CDbCriteria();
$criteria->compare('scope', 'G');
$assessments = Assessment::model()->findAll($criteria);
if (Assessment::model()->hasErrors()) {
safeDie(Assessment::model()->getError());
}
foreach ($assessments as $assessment) {
$iAssessmentCount = count(Groups::model()->findAllByPk($assessment['gid']));
if (Groups::model()->hasErrors()) {
safeDie(Groups::model()->getError());
}
if (!$iAssessmentCount) {
$aDelete['assessments'][] = array('id' => $assessment['id'], 'assessment' => $assessment['name'], 'reason' => $clang->gT('No matching group'));
}
}
/**********************************************************************/
/* Check answers */
/**********************************************************************/
$answers = Answers::model()->findAll();
if (Answers::model()->hasErrors()) {
safeDie(Answers::model()->getError());
}
foreach ($answers as $answer) {
$iAnswerCount = Questions::model()->countByAttributes(array('qid' => $answer['qid']));
if (Questions::model()->hasErrors()) {
safeDie(Questions::model()->getError());
}
if (!$iAnswerCount) {
$aDelete['answers'][] = array('qid' => $answer['qid'], 'code' => $answer['code'], 'reason' => $clang->gT('No matching question'));
}
}
/**********************************************************************/
/* Check surveys */
/**********************************************************************/
$surveys = Survey::model()->findAll();
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
foreach ($surveys as $survey) {
$criteria = new CDbCriteria();
$criteria->compare('surveyls_survey_id', $survey['sid']);
$iSurveyLangSettingsCount = count(Surveys_languagesettings::model()->findAll($criteria));
if (Surveys_languagesettings::model()->hasErrors()) {
safeDie(Surveys_languagesettings::model()->getError());
}
if (!$iSurveyLangSettingsCount) {
$aDelete['surveys'][] = array('sid' => $survey['sid'], 'reason' => $clang->gT('Language specific settings missing'));
}
}
/**********************************************************************/
/* Check survey language settings */
/**********************************************************************/
$surveys = Survey::model()->findAll();
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
$sids = array();
foreach ($surveys as $survey) {
$sids[] = $survey['sid'];
}
$criteria = new CDbCriteria();
$criteria->addNotInCondition('surveyls_survey_id', $sids);
$surveys_languagesettings = Surveys_languagesettings::model()->findAll($criteria);
if (Surveys_languagesettings::model()->hasErrors()) {
safeDie(Surveys_languagesettings::model()->getError());
}
foreach ($surveys_languagesettings as $surveys_languagesetting) {
$aDelete['surveylanguagesettings'][] = array('slid' => $surveys_languagesetting['surveyls_survey_id'], 'reason' => $clang->gT('The related survey is missing.'));
}
/**********************************************************************/
/* Check questions */
/**********************************************************************/
$questions = Questions::model()->findAll();
if (Questions::model()->hasErrors()) {
safeDie(Questions::model()->getError());
}
foreach ($questions as $question) {
//Make sure the group exists
$criteria = new CDbCriteria();
$criteria->compare('gid', $question['gid']);
$iQuestionCount = count(Groups::model()->findAll($criteria));
if (Groups::model()->hasErrors()) {
safeDie(Groups::model()->getError());
}
if (!$iQuestionCount) {
$aDelete['questions'][] = array('qid' => $question['qid'], 'reason' => $clang->gT('No matching group') . " ({$question['gid']})");
}
//Make sure survey exists
$iQuestionCount = count(Survey::model()->findAllByPk($question['sid']));
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
if (!$iQuestionCount) {
$aDelete['questions'][] = array('qid' => $question['qid'], 'reason' => $clang->gT('There is no matching survey.') . " ({$question['sid']})");
}
}
/**********************************************************************/
/* Check groups */
/**********************************************************************/
$surveys = Survey::model()->findAll();
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
$sids = array();
foreach ($surveys as $survey) {
$sids[] = $survey['sid'];
}
$criteria = new CDbCriteria();
$criteria->addNotInCondition('sid', $sids);
$groups = Groups::model()->findAll($criteria);
foreach ($groups as $group) {
$aDelete['groups'][] = array('gid' => $group['gid'], 'reason' => $clang->gT('There is no matching survey.') . ' SID:' . $group['sid']);
}
/**********************************************************************/
/* Check old survey tables */
/**********************************************************************/
//1: Get list of 'old_survey' tables and extract the survey id
//2: Check if that survey id still exists
//3: If it doesn't offer it for deletion
$sQuery = dbSelectTablesLike('{{old_survey}}%');
$aResult = dbQueryOrFalse($sQuery) or safeDie("Couldn't get list of conditions from database<br />{$sQuery}<br />");
$aTables = $aResult->readAll();
$aOldSIDs = array();
$aSIDs = array();
foreach ($aTables as $sTable) {
$sTable = reset($sTable);
list($sOldText, $SurveyText, $iSurveyID, $sDate) = explode('_', substr($sTable, strlen($sDBPrefix)));
$aOldSIDs[] = $iSurveyID;
$aFullOldSIDs[$iSurveyID][] = $sTable;
}
$aOldSIDs = array_unique($aOldSIDs);
$sQuery = 'SELECT sid FROM {{surveys}} ORDER BY sid';
$oResult = dbExecuteAssoc($sQuery) or safeDie('Couldn\'t get unique survey ids');
$surveys = Survey::model()->findAll();
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
$aSIDs = array();
foreach ($surveys as $survey) {
$aSIDs[] = $survey['sid'];
}
foreach ($aOldSIDs as $iOldSID) {
if (!in_array($iOldSID, $aSIDs)) {
foreach ($aFullOldSIDs[$iOldSID] as $sTableName) {
$aDelete['orphansurveytables'][] = $sTableName;
}
} else {
foreach ($aFullOldSIDs[$iOldSID] as $sTableName) {
$aTableParts = explode('_', substr($sTableName, strlen($sDBPrefix)));
if (count($aTableParts) == 4) {
$sOldText = $aTableParts[0];
$SurveyText = $aTableParts[1];
$iSurveyID = $aTableParts[2];
$sDateTime = $aTableParts[3];
$sType = $clang->gT('responses');
} elseif (count($aTableParts) == 5) {
//This is a timings table (
$sOldText = $aTableParts[0];
$SurveyText = $aTableParts[1];
$iSurveyID = $aTableParts[2];
$sDateTime = $aTableParts[4];
$sType = $clang->gT('timings');
}
$iYear = substr($sDateTime, 0, 4);
$iMonth = substr($sDateTime, 4, 2);
$iDay = substr($sDateTime, 6, 2);
$iHour = substr($sDateTime, 8, 2);
$iMinute = substr($sDateTime, 10, 2);
$sDate = date('d M Y H:i', mktime($iHour, $iMinute, 0, $iMonth, $iDay, $iYear));
$sQuery = 'SELECT count(*) as recordcount FROM ' . $sTableName;
$aFirstRow = Yii::app()->db->createCommand($sQuery)->queryRow();
if ($aFirstRow['recordcount'] == 0) {
// empty table - so add it to immediate deletion
$aDelete['orphansurveytables'][] = $sTableName;
} else {
$aOldSurveyTableAsk[] = array('table' => $sTableName, 'details' => sprintf($clang->gT('Survey ID %d saved at %s containing %d record(s) (%s)'), $iSurveyID, $sDate, $aFirstRow['recordcount'], $sType));
}
}
}
}
/**********************************************************************/
/* CHECK OLD TOKEN TABLES */
/**********************************************************************/
//1: Get list of 'old_token' tables and extract the survey id
//2: Check if that survey id still exists
//3: If it doesn't offer it for deletion
$aResult = dbQueryOrFalse(dbSelectTablesLike('{{old_token}}%')) or safeDie("Couldn't get list of conditions from database<br />{$sQuery}<br />");
$aTables = $aResult->readAll();
$aOldTokenSIDs = array();
$aTokenSIDs = array();
$aFullOldTokenSIDs = array();
foreach ($aTables as $sTable) {
$sTable = reset($sTable);
list($sOldText, $SurveyText, $iSurveyID, $sDateTime) = explode('_', substr($sTable, strlen($sDBPrefix)));
$aTokenSIDs[] = $iSurveyID;
$aFullOldTokenSIDs[$iSurveyID][] = $sTable;
}
$aOldTokenSIDs = array_unique($aOldTokenSIDs);
$surveys = Survey::model()->findAll();
if (Survey::model()->hasErrors()) {
safeDie(Survey::model()->getError());
}
$aSIDs = array();
foreach ($surveys as $survey) {
$aSIDs[] = $survey['sid'];
}
foreach ($oResult->readAll() as $aRow) {
$aTokenSIDs[] = $aRow['sid'];
}
foreach ($aOldTokenSIDs as $iOldTokenSID) {
if (!in_array($iOldTokenSID, $aTokenSIDs)) {
foreach ($aFullOldTokenSIDs[$iOldTokenSID] as $sTableName) {
$aDelete['orphantokentables'][] = $sTableName;
}
} else {
foreach ($aFullOldTokenSIDs[$iOldTokenSID] as $sTableName) {
list($sOldText, $sTokensText, $iSurveyID, $sDateTime) = explode('_', substr($sTableName, strlen($sDBPrefix)));
$iYear = substr($sDateTime, 0, 4);
$iMonth = substr($sDateTime, 4, 2);
$iDay = substr($sDateTime, 6, 2);
$iHour = substr($sDateTime, 8, 2);
$iMinute = substr($sDateTime, 10, 2);
$sDate = date('D, d M Y h:i a', mktime($iHour, $iMinute, 0, $iMonth, $iDay, $iYear));
$sQuery = 'SELECT count(*) as recordcount FROM ' . $sTableName;
$aFirstRow = Yii::app()->db->createCommand($sQuery)->queryRow();
if ($aFirstRow['recordcount'] == 0) {
// empty table - so add it to immediate deletion
$aDelete['orphantokentables'][] = $sTableName;
} else {
$aOldTokenTableAsk[] = array('table' => $sTableName, 'details' => sprintf($clang->gT('Survey ID %d saved at %s containing %d record(s)'), $iSurveyID, $sDate, $aFirstRow['recordcount']));
}
}
}
}
if ($aDelete['defaultvalues'] == 0 && $aDelete['quotamembers'] == 0 && $aDelete['quotas'] == 0 && $aDelete['quotals'] == 0 && count($aDelete) == 4) {
$aDelete['integrityok'] = true;
} else {
$aDelete['integrityok'] = false;
}
if (!isset($aOldTokenTableAsk) && !isset($aOldSurveyTableAsk)) {
$aDelete['redundancyok'] = true;
} else {
$aDelete['redundancyok'] = false;
$aDelete['redundanttokentables'] = array();
$aDelete['redundantsurveytables'] = array();
if (isset($aOldTokenTableAsk)) {
$aDelete['redundanttokentables'] = $aOldTokenTableAsk;
}
if (isset($aOldSurveyTableAsk)) {
$aDelete['redundantsurveytables'] = $aOldSurveyTableAsk;
}
}
/**********************************************************************/
/* CHECK CPDB SURVEY_LINKS TABLE FOR REDUNDENT TOKEN TABLES */
/**********************************************************************/
//1: Get distinct list of survey_link survey ids, check if tokens
// table still exists for each one, and remove if not
/* TODO */
/**********************************************************************/
/* CHECK CPDB SURVEY_LINKS TABLE FOR REDUNDENT TOKEN ENTRIES */
/**********************************************************************/
//1: For each survey_link, see if the matching entry still exists in
// the token table and remove if it doesn't.
/* TODO */
return $aDelete;
}
