function db_upgrade($oldversion)
{
global $modifyoutput, $databasename, $databasetabletype;
if ($oldversion < 127) {
modify_database("", "create index answers_idx2 on prefix_answers (sortorder)");
echo $modifyoutput;
flush();
modify_database("", "create index assessments_idx2 on prefix_assessments (sid)");
echo $modifyoutput;
flush();
modify_database("", "create index assessments_idx on prefix_assessments (gid)");
echo $modifyoutput;
flush();
modify_database("", "create index conditions_idx2 on prefix_conditions (qid)");
echo $modifyoutput;
flush();
modify_database("", "create index conditions_idx3 on prefix_conditions (cqid)");
echo $modifyoutput;
flush();
modify_database("", "create index groups_idx2 on prefix_groups (sid)");
echo $modifyoutput;
flush();
modify_database("", "create index question_attributes_idx2 on prefix_question_attributes (qid)");
echo $modifyoutput;
flush();
modify_database("", "create index questions_idx2 on prefix_questions (sid)");
echo $modifyoutput;
flush();
modify_database("", "create index questions_idx3 on prefix_questions (gid)");
echo $modifyoutput;
flush();
modify_database("", "create index questions_idx4 on prefix_questions (type)");
echo $modifyoutput;
flush();
modify_database("", "create index quota_idx2 on prefix_quota (sid)");
echo $modifyoutput;
flush();
modify_database("", "create index saved_control_idx2 on prefix_saved_control (sid)");
echo $modifyoutput;
flush();
modify_database("", "create index user_in_groups_idx1 on prefix_user_in_groups (ugid, uid)");
echo $modifyoutput;
flush();
modify_database("", "update prefix_settings_global set stg_value='127' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 128) {
//128
upgrade_token_tables128();
modify_database("", "update prefix_settings_global set stg_value='128' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 129) {
//129
modify_database("", "ALTER TABLE prefix_surveys ADD startdate date");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_surveys ADD usestartdate char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
modify_database("", "update prefix_settings_global set stg_value='129' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 130) {
modify_database("", "ALTER TABLE prefix_conditions ADD scenario integer NOT NULL default '1'");
echo $modifyoutput;
flush();
modify_database("", "UPDATE prefix_conditions SET scenario=1 where (scenario is null) or scenario=0");
echo $modifyoutput;
flush();
modify_database("", "update prefix_settings_global set stg_value='130' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 131) {
modify_database("", "ALTER TABLE prefix_surveys ADD publicstatistics char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
modify_database("", "update prefix_settings_global set stg_value='131' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 132) {
modify_database("", "ALTER TABLE prefix_surveys ADD publicgraphs char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
modify_database("", "update prefix_settings_global set stg_value='132' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 133) {
modify_database("", "ALTER TABLE prefix_users ADD one_time_pw bytea");
echo $modifyoutput;
flush();
// Add new assessment setting
modify_database("", "ALTER TABLE prefix_surveys ADD assessments char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
// add new assessment value fields to answers & labels
modify_database("", "ALTER TABLE prefix_answers ADD assessment_value integer NOT NULL default '0'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_labels ADD assessment_value integer NOT NULL default '0'");
echo $modifyoutput;
flush();
// copy any valid codes from code field to assessment field
modify_database("", "update [prefix_answers set assessment_value=CAST(code as 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();
// add language field to assessment table
modify_database("", "ALTER TABLE prefix_assessments ADD language character varying(20) NOT NULL default 'en'");
echo $modifyoutput;
flush();
// update language field with default language of that particular survey
modify_database("", "update prefix_assessments set language=(select language from prefix_surveys where sid=prefix_assessments.sid)");
echo $modifyoutput;
flush();
// copy assessment link to message since from now on we will have HTML assignment messages
modify_database("", "update prefix_assessments set message=cast(message as character) ||'<br /><a href=\"'||link||'\">'||link||'</a>'");
echo $modifyoutput;
flush();
// drop the old link field
modify_database("", "ALTER TABLE prefix_assessments DROP COLUMN link");
echo $modifyoutput;
flush();
// change the primary index to include language
modify_database("", "ALTER TABLE prefix_assessments DROP CONSTRAINT prefix_assessments_pkey");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_assessments ADD CONSTRAINT prefix_assessments_pkey PRIMARY KEY (id,language)");
echo $modifyoutput;
flush();
// and fix missing translations for assessments
upgrade_survey_tables133();
// Add new fields to survey language settings
modify_database("", "ALTER TABLE prefix_surveys_languagesettings ADD surveyls_url character varying(255)");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_surveys_languagesettings ADD surveyls_endtext text");
echo $modifyoutput;
flush();
// copy old URL fields ot language specific entries
modify_database("", "update prefix_surveys_languagesettings set surveyls_url=(select url from prefix_surveys where sid=prefix_surveys_languagesettings.surveyls_survey_id)");
echo $modifyoutput;
flush();
// drop old URL field
modify_database("", "ALTER TABLE prefix_surveys DROP COLUMN url");
echo $modifyoutput;
flush();
modify_database("", "update prefix_settings_global set stg_value='133' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 134) {
modify_database("", "ALTER TABLE prefix_surveys ADD usetokens char(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_surveys ADD attributedescriptions TEXT;");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_surveys DROP COLUMN attribute1");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_surveys DROP COLUMN attribute2");
echo $modifyoutput;
flush();
upgrade_token_tables134();
modify_database("", "update prefix_settings_global set stg_value='134' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 135) {
modify_database("", "ALTER TABLE prefix_question_attributes ALTER COLUMN value TYPE text");
echo $modifyoutput;
flush();
modify_database("", "update prefix_settings_global set stg_value='135' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 136) {
modify_database("", "ALTER TABLE prefix_quota ADD autoload_url integer NOT NULL DEFAULT 0");
echo $modifyoutput;
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();
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();
modify_database("", "ALTER TABLE ONLY prefix_users ADD CONSTRAINT prefix_users_pkey PRIMARY KEY (uid)");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE ONLY prefix_users ADD CONSTRAINT prefix_user_name_key UNIQUE (users_name)");
echo $modifyoutput;
flush();
modify_database("", "update prefix_settings_global set stg_value='136' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 137) {
modify_database("", "ALTER TABLE prefix_surveys_languagesettings ADD surveyls_dateformat integer NOT NULL default 1");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_users ADD \"dateformat\" integer NOT NULL default 1");
echo $modifyoutput;
flush();
modify_database("", "update prefix_surveys set startdate=null where usestartdate='N'");
echo $modifyoutput;
flush();
modify_database("", "update prefix_surveys set expires=null where useexpiry='N'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_surveys DROP COLUMN usestartdate");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_surveys DROP COLUMN useexpiry");
echo $modifyoutput;
flush();
modify_database("", "update prefix_settings_global set stg_value='137' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 138) {
modify_database("", "ALTER TABLE prefix_quota_members ALTER COLUMN code TYPE character varying(11)");
echo $modifyoutput;
flush();
modify_database("", "UPDATE prefix_settings_global SET stg_value='138' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 139) {
upgrade_survey_tables139();
modify_database("", "UPDATE prefix_settings_global SET stg_value='139' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 140) {
modify_database("", "ALTER TABLE prefix_surveys ADD \"emailresponseto\" TEXT");
echo $modifyoutput;
flush();
modify_database("", "UPDATE prefix_settings_global SET stg_value='140' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 141) {
modify_database("", "ALTER TABLE prefix_surveys ADD \"tokenlength\" smallint NOT NULL DEFAULT '15'");
echo $modifyoutput;
flush();
modify_database("", "UPDATE prefix_settings_global SET stg_value='141' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 142) {
upgrade_question_attributes142();
modify_database("", "ALTER TABLE prefix_surveys ALTER COLUMN \"startdate\" TYPE timestamp");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_surveys ALTER COLUMN \"expires\" TYPE timestamp");
echo $modifyoutput;
flush();
modify_database("", "UPDATE prefix_question_attributes SET value='0' WHERE value='false'");
echo $modifyoutput;
flush();
modify_database("", "UPDATE prefix_question_attributes SET value='1' WHERE value='true'");
echo $modifyoutput;
flush();
modify_database("", "UPDATE prefix_settings_global SET stg_value='142' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 143) {
modify_database("", "ALTER TABLE prefix_questions ADD parent_qid integer NOT NULL default '0'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_answers ADD scale_id smallint NOT NULL default '0'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_questions ADD scale_id smallint NOT NULL default '0'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_questions ADD same_default smallint NOT NULL default '0'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_answers DROP CONSTRAINT prefix_answers_pkey");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_answers ADD CONSTRAINT prefix_answers_pkey PRIMARY KEY (qid,code,language,scale_id)");
echo $modifyoutput;
flush();
modify_database("", "CREATE TABLE prefix_defaultvalues (\r\n qid integer NOT NULL default '0',\r\n scale_id 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();
modify_database("", "ALTER TABLE prefix_defaultvalues ADD CONSTRAINT prefix_defaultvalues_pkey PRIMARY KEY (qid , scale_id, language, specialtype, sqid)");
echo $modifyoutput;
flush();
// -Move all 'answers' that are subquestions to the questions table
// -Move all 'labels' that are answers to the answers table
// -Transscribe the default values where applicable
// -Move default values from answers to questions
upgrade_tables143();
modify_database("", "ALTER TABLE prefix_answers DROP COLUMN default_value");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_questions DROP COLUMN lid");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE prefix_questions DROP COLUMN lid1");
echo $modifyoutput;
flush();
modify_database("", "CREATE TABLE prefix_sessions(\r\n sesskey VARCHAR( 64 ) NOT NULL DEFAULT '',\r\n expiry 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();
modify_database("", "create INDEX sess_expiry on prefix_sessions( expiry );");
echo $modifyoutput;
flush();
modify_database("", "create INDEX sess_expireref on prefix_sessions ( expireref );");
echo $modifyoutput;
flush();
modify_database("", "UPDATE prefix_settings_global SET stg_value='143' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
echo '<br /><br />Database update finished (' . date('Y-m-d H:i:s') . ')<br />';
return true;
}
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();
// and fix missing translations for assessments
upgrade_survey_tables133();
// Add new fields to survey language settings
modify_database("", "ALTER TABLE prefix_surveys_languagesettings ADD surveyls_url 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("", "UPDATE prefix_settings_global SET stg_value='146' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
ob_flush();
}
echo '<br /><br />' . sprintf($clang->gT('Database update finished (%s)'), date('Y-m-d H:i:s')) . '<br />';
return true;
}
function db_upgrade($oldversion)
{
/// This function does anything necessary to upgrade
/// older versions to match current functionality
global $modifyoutput, $databasename, $databasetabletype;
if ($oldversion < 111) {
// Language upgrades from version 110 to 111 since the language names did change
$oldnewlanguages = array('german_informal' => 'german-informal', 'cns' => 'cn-Hans', 'cnt' => 'cn-Hant', 'pt_br' => 'pt-BR', 'gr' => 'el', 'jp' => 'ja', 'si' => 'sl', 'se' => 'sv', 'vn' => 'vi');
foreach ($oldnewlanguages as $oldlang => $newlang) {
modify_database("", "update `prefix_answers` set `language`='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_questions` set `language`='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_groups` set `language`='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_labels` set `language`='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_surveys` set `language`='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_surveys_languagesettings` set `surveyls_language`='{$newlang}' where surveyls_language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_users` set `lang`='{$newlang}' where lang='{$oldlang}'");
echo $modifyoutput;
flush();
}
$resultdata = db_execute_assoc("select * from " . db_table_name("labelsets"));
while ($datarow = $resultdata->FetchRow()) {
$toreplace = $datarow['languages'];
$toreplace = str_replace('german_informal', 'german-informal', $toreplace);
$toreplace = str_replace('cns', 'cn-Hans', $toreplace);
$toreplace = str_replace('cnt', 'cn-Hant', $toreplace);
$toreplace = str_replace('pt_br', 'pt-BR', $toreplace);
$toreplace = str_replace('gr', 'el', $toreplace);
$toreplace = str_replace('jp', 'ja', $toreplace);
$toreplace = str_replace('si', 'sl', $toreplace);
$toreplace = str_replace('se', 'sv', $toreplace);
$toreplace = str_replace('vn', 'vi', $toreplace);
modify_database("", "update `prefix_labelsets` set `languages`='{$toreplace}' where lid=" . $datarow['lid']);
echo $modifyoutput;
flush();
}
$resultdata = db_execute_assoc("select * from " . db_table_name("surveys"));
while ($datarow = $resultdata->FetchRow()) {
$toreplace = $datarow['additional_languages'];
$toreplace = str_replace('german_informal', 'german-informal', $toreplace);
$toreplace = str_replace('cns', 'cn-Hans', $toreplace);
$toreplace = str_replace('cnt', 'cn-Hant', $toreplace);
$toreplace = str_replace('pt_br', 'pt-BR', $toreplace);
$toreplace = str_replace('gr', 'el', $toreplace);
$toreplace = str_replace('jp', 'ja', $toreplace);
$toreplace = str_replace('si', 'sl', $toreplace);
$toreplace = str_replace('se', 'sv', $toreplace);
$toreplace = str_replace('vn', 'vi', $toreplace);
modify_database("", "update `prefix_surveys` set `additional_languages`='{$toreplace}' where sid=" . $datarow['sid']);
echo $modifyoutput;
flush();
}
modify_database("", "update `prefix_settings_global` set `stg_value`='111' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 112) {
//The size of the users_name field is now 64 char (20 char before version 112)
modify_database("", "ALTER TABLE `prefix_users` CHANGE `users_name` `users_name` VARCHAR( 64 ) NOT NULL");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_settings_global` set `stg_value`='112' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 113) {
//Fixes the collation for the complete DB, tables and columns
echo "<strong>Attention:</strong>The following upgrades will update your MySQL Database collations. This may take some time.<br />If for any reason you should get a timeout just re-run the upgrade procedure. The updating will continue where it left off.<br /><br />";
flush();
fix_mysql_collation();
modify_database("", "ALTER DATABASE `{$databasename}` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_settings_global` set `stg_value`='113' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 114) {
modify_database("", "ALTER TABLE `prefix_saved_control` CHANGE `email` `email` VARCHAR(320) NOT NULL");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `adminemail` `adminemail` VARCHAR(320) NOT NULL");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_users` CHANGE `email` `email` VARCHAR(320) NOT NULL");
echo $modifyoutput;
flush();
modify_database("", 'INSERT INTO `prefix_settings_global` VALUES (\'SessionName\', \'$sessionname\');');
echo $modifyoutput;
flush();
modify_database("", "update `prefix_settings_global` set `stg_value`='114' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 126) {
//Adds new "public" field
modify_database("", "ALTER TABLE `prefix_surveys` ADD `printanswers` CHAR(1) default 'N' AFTER allowsave");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_surveys` ADD `listpublic` CHAR(1) default 'N' AFTER `datecreated`");
echo $modifyoutput;
flush();
upgrade_survey_tables117();
upgrade_survey_tables118();
// 119
modify_database("", "CREATE TABLE `prefix_quota` (\r\n \t\t\t\t `id` int(11) NOT NULL auto_increment,\r\n \t\t\t\t\t\t\t`sid` int(11) default NULL,\r\n \t\t\t\t\t\t\t`qlimit` int(8) default NULL,\r\n \t\t\t\t\t\t\t`name` varchar(255) collate utf8_unicode_ci default NULL,\r\n \t\t\t\t\t\t\t`action` int(2) default NULL,\r\n \t\t\t\t\t\t\t`active` int(1) NOT NULL default '1',\r\n \t\t\t\t\t\t\tPRIMARY KEY (`id`)\r\n\t\t\t\t\t\t\t) ENGINE={$databasetabletype} CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
echo $modifyoutput;
flush();
modify_database("", "CREATE TABLE `prefix_quota_members` (\r\n \t\t \t\t\t\t `id` int(11) NOT NULL auto_increment,\r\n\t\t\t\t\t\t `sid` int(11) default NULL,\r\n \t\t\t\t\t\t `qid` int(11) default NULL,\r\n \t\t\t\t\t\t `quota_id` int(11) default NULL,\r\n \t\t\t\t\t\t `code` varchar(5) collate utf8_unicode_ci default NULL,\r\n \t\t\t\t\t\t PRIMARY KEY (`id`),\r\n \t\t\t\t\t\t UNIQUE KEY `sid` (`sid`,`qid`,`quota_id`,`code`)\r\n\t\t\t\t\t\t ) ENGINE={$databasetabletype} CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
echo $modifyoutput;
flush();
// Rename Norwegian language code from NO to NB
$oldnewlanguages = array('no' => 'nb');
foreach ($oldnewlanguages as $oldlang => $newlang) {
modify_database("", "update `prefix_answers` set `language`='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_questions` set `language`='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_groups` set `language`='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_labels` set `language`='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_surveys` set `language`='{$newlang}' where language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_surveys_languagesettings` set `surveyls_language`='{$newlang}' where surveyls_language='{$oldlang}'");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_users` set `lang`='{$newlang}' where lang='{$oldlang}'");
echo $modifyoutput;
flush();
}
$resultdata = db_execute_assoc("select * from " . db_table_name("labelsets"));
while ($datarow = $resultdata->FetchRow()) {
$toreplace = $datarow['languages'];
$toreplace2 = str_replace('no', 'nb', $toreplace);
if ($toreplace2 != $toreplace) {
modify_database("", "update `prefix_labelsets` set `languages`='{$toreplace}' where lid=" . $datarow['lid']);
echo $modifyoutput;
flush();
}
}
$resultdata = db_execute_assoc("select * from " . db_table_name("surveys"));
while ($datarow = $resultdata->FetchRow()) {
$toreplace = $datarow['additional_languages'];
$toreplace2 = str_replace('no', 'nb', $toreplace);
if ($toreplace2 != $toreplace) {
modify_database("", "update `prefix_surveys` set `additional_languages`='{$toreplace}' where sid=" . $datarow['sid']);
echo $modifyoutput;
flush();
}
}
modify_database("", "ALTER TABLE `prefix_surveys` ADD `htmlemail` CHAR(1) default 'N'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_surveys` ADD `tokenanswerspersistence` CHAR(1) default 'N'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_surveys` ADD `usecaptcha` CHAR(1) default 'N'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_users` ADD `htmleditormode` CHAR(7) default 'default'");
echo $modifyoutput;
flush();
//122
modify_database("", "CREATE TABLE `prefix_templates_rights` (\r\n\t\t\t\t\t\t `uid` int(11) NOT NULL,\r\n\t\t\t\t\t\t `folder` varchar(255) NOT NULL,\r\n\t\t\t\t\t\t `use` int(1) NOT NULL,\r\n\t\t\t\t\t\t PRIMARY KEY (`uid`,`folder`)\r\n\t\t\t\t\t\t ) ENGINE={$databasetabletype} CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
echo $modifyoutput;
flush();
modify_database("", "CREATE TABLE `prefix_templates` (\r\n\t\t\t\t\t\t `folder` varchar(255) NOT NULL,\r\n\t\t\t\t\t\t `creator` int(11) NOT NULL,\r\n\t\t\t\t\t\t PRIMARY KEY (`folder`)\r\n\t\t\t\t\t\t ) ENGINE={$databasetabletype} CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
echo $modifyoutput;
flush();
//123
modify_database("", "ALTER TABLE `prefix_conditions` CHANGE `value` `value` VARCHAR(255) NOT NULL default ''");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_labels` CHANGE `title` `title` text");
echo $modifyoutput;
flush();
//124
modify_database("", "ALTER TABLE `prefix_surveys` ADD `bounce_email` text");
echo $modifyoutput;
flush();
//125
upgrade_token_tables125();
modify_database("", "ALTER TABLE `prefix_users` ADD `superadmin` tinyint(1) NOT NULL default '0'");
echo $modifyoutput;
flush();
modify_database("", "UPDATE `prefix_users` SET `superadmin`=1 where (create_survey=1 AND create_user=1 AND move_user=1 AND delete_user=1 AND configurator=1)");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_users` DROP COLUMN `move_user`");
echo $modifyoutput;
flush();
//126
modify_database("", "ALTER TABLE `prefix_questions` ADD `lid1` integer NOT NULL default '0'");
echo $modifyoutput;
flush();
modify_database("", "UPDATE `prefix_conditions` SET `method`='==' where (`method` is null) or `method`='' or `method`='0'");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_settings_global` set `stg_value`='126' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 127) {
modify_database("", "create index `assessments_idx2` on `prefix_assessments` (`sid`)");
echo $modifyoutput;
flush();
modify_database("", "create index `assessments_idx3` on `prefix_assessments` (`gid`)");
echo $modifyoutput;
flush();
modify_database("", "create index `conditions_idx2` on `prefix_conditions` (`qid`)");
echo $modifyoutput;
flush();
modify_database("", "create index `groups_idx2` on `prefix_groups` (`sid`)");
echo $modifyoutput;
flush();
modify_database("", "create index `questions_idx2` on `prefix_questions` (`sid`)");
echo $modifyoutput;
flush();
modify_database("", "create index `questions_idx3` on `prefix_questions` (`gid`)");
echo $modifyoutput;
flush();
modify_database("", "create index `question_attributes_idx2` on `prefix_question_attributes` (`qid`)");
echo $modifyoutput;
flush();
modify_database("", "create index `quota_idx2` on `prefix_quota` (`sid`)");
echo $modifyoutput;
flush();
modify_database("", "create index `saved_control_idx2` on `prefix_saved_control` (`sid`)");
echo $modifyoutput;
flush();
modify_database("", "create index `user_in_groups_idx1` on `prefix_user_in_groups` (`ugid`, `uid`)");
echo $modifyoutput;
flush();
modify_database("", "create index `answers_idx2` on `prefix_answers` (`sortorder`)");
echo $modifyoutput;
flush();
modify_database("", "create index `conditions_idx3` on `prefix_conditions` (`cqid`)");
echo $modifyoutput;
flush();
modify_database("", "create index `questions_idx4` on `prefix_questions` (`type`)");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_settings_global` set `stg_value`='127' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 128) {
//128
upgrade_token_tables128();
modify_database("", "update `prefix_settings_global` set `stg_value`='128' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 129) {
//129
modify_database("", "ALTER TABLE `prefix_surveys` ADD `startdate` DATETIME");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_surveys` ADD `usestartdate` varchar(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_settings_global` set `stg_value`='129' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 130) {
modify_database("", "ALTER TABLE `prefix_conditions` ADD `scenario` integer NOT NULL default '1' AFTER `qid`");
echo $modifyoutput;
flush();
modify_database("", "UPDATE `prefix_conditions` SET `scenario`=1 where (`scenario` is null) or `scenario`='' or `scenario`=0");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_settings_global` set `stg_value`='130' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 131) {
modify_database("", "ALTER TABLE `prefix_surveys` ADD `publicstatistics` varchar(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_settings_global` set `stg_value`='131' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 132) {
modify_database("", "ALTER TABLE `prefix_surveys` ADD `publicgraphs` varchar(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_settings_global` set `stg_value`='132' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 133) {
modify_database("", "ALTER TABLE `prefix_users` ADD `one_time_pw` blob");
echo $modifyoutput;
flush();
// Add new assessment setting
modify_database("", "ALTER TABLE `prefix_surveys` ADD `assessments` varchar(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
// add new assessment value fields to answers & labels
modify_database("", "ALTER TABLE `prefix_answers` ADD `assessment_value` int(11) NOT NULL default '0'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_labels` ADD `assessment_value` int(11) NOT NULL default '0'");
echo $modifyoutput;
flush();
// copy any valid codes from code field to assessment field
modify_database("", "update `prefix_answers` set `assessment_value`=CAST(`code` as SIGNED) where `code` REGEXP '^-?[0-9]+\$'");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_labels` set `assessment_value`=CAST(`code` as SIGNED) where `code` REGEXP '^-?[0-9]+\$'");
echo $modifyoutput;
flush();
// activate assessment where assesment rules exist
modify_database("", "update `prefix_surveys` set `assessments`='Y' where `sid` in (SELECT `sid` FROM `prefix_assessments` group by `sid`)");
echo $modifyoutput;
flush();
// add language field to assessment table
modify_database("", "ALTER TABLE `prefix_assessments` ADD `language` varchar(20) NOT NULL default 'en'");
echo $modifyoutput;
flush();
// update language field with default language of that particular survey
modify_database("", "update `prefix_assessments` set `language`=(select `language` from `prefix_surveys` where `sid`=`prefix_assessments`.`sid`)");
echo $modifyoutput;
flush();
// copy assessment link to message since from now on we will have HTML assignment messages
modify_database("", "update `prefix_assessments` set `message`=concat(replace(`message`,'/''',''''),'<br /><a href=\"',`link`,'\">',`link`,'</a>')");
echo $modifyoutput;
flush();
// drop the old link field
modify_database("", "ALTER TABLE `prefix_assessments` DROP COLUMN `link`");
echo $modifyoutput;
flush();
// change the primary index to include language
modify_database("", "ALTER TABLE `prefix_assessments` DROP PRIMARY KEY, ADD PRIMARY KEY USING BTREE(`id`, `language`)");
echo $modifyoutput;
flush();
//finally fix missing translations for assessments
upgrade_survey_tables133();
// Add new fields to survey language settings
modify_database("", "ALTER TABLE `prefix_surveys_languagesettings` ADD `surveyls_url` varchar(255)");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_surveys_languagesettings` ADD `surveyls_endtext` text");
echo $modifyoutput;
flush();
// copy old URL fields ot language specific entries
modify_database("", "update `prefix_surveys_languagesettings` set `surveyls_url`=(select `url` from `prefix_surveys` where `sid`=`prefix_surveys_languagesettings`.`surveyls_survey_id`)");
echo $modifyoutput;
flush();
// drop old URL field
modify_database("", "ALTER TABLE `prefix_surveys` DROP COLUMN `url`");
echo $modifyoutput;
flush();
modify_database("", "update `prefix_settings_global` set `stg_value`='133' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 134) {
// Add new tokens setting
modify_database("", "ALTER TABLE `prefix_surveys` ADD `usetokens` varchar(1) NOT NULL default 'N'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_surveys` ADD `attributedescriptions` TEXT;");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_surveys` DROP COLUMN `attribute1`");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_surveys` DROP COLUMN `attribute2`");
echo $modifyoutput;
flush();
upgrade_token_tables134();
modify_database("", "update `prefix_settings_global` set `stg_value`='134' where stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 135) {
modify_database("", "ALTER TABLE `prefix_question_attributes` MODIFY `value` text");
echo $modifyoutput;
flush();
modify_database("", "UPDATE `prefix_settings_global` SET `stg_value`='135' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 136) {
modify_database("", "ALTER TABLE `prefix_quota` ADD `autoload_url` int(1) NOT NULL default '0'");
echo $modifyoutput;
flush();
modify_database("", "CREATE TABLE `prefix_quota_languagesettings` (\r\n\t\t\t\t\t\t\t\t `quotals_id` int(11) NOT NULL auto_increment,\r\n\t\t\t\t\t\t\t\t\t\t `quotals_quota_id` int(11) NOT NULL default '0',\r\n\t\t\t\t\t\t\t\t\t\t `quotals_language` varchar(45) NOT NULL default 'en',\r\n\t\t\t\t\t\t\t\t\t\t `quotals_name` varchar(255) collate utf8_unicode_ci default NULL,\r\n\t\t\t\t\t\t\t\t\t\t `quotals_message` text NOT NULL,\r\n\t\t\t\t\t\t\t\t\t\t `quotals_url` varchar(255),\r\n\t\t\t\t\t\t\t\t\t\t `quotals_urldescrip` varchar(255),\r\n\t\t\t\t\t\t\t\t\t\t PRIMARY KEY (`quotals_id`)\r\n\t\t\t\t\t\t\t\t\t\t ) ENGINE={$databasetabletype} CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
echo $modifyoutput;
flush();
modify_database("", "UPDATE `prefix_settings_global` SET `stg_value`='136' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 137) {
modify_database("", "ALTER TABLE `prefix_surveys_languagesettings` ADD `surveyls_dateformat` int(1) NOT NULL default '1'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_users` ADD `dateformat` int(1) NOT NULL default '1'");
echo $modifyoutput;
flush();
modify_database("", "UPDATE `prefix_surveys` set `startdate`=null where `usestartdate`='N'");
echo $modifyoutput;
flush();
modify_database("", "UPDATE `prefix_surveys` set `expires`=null where `useexpiry`='N'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_surveys` DROP COLUMN `useexpiry`");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_surveys` DROP COLUMN `usestartdate`");
echo $modifyoutput;
flush();
modify_database("", "UPDATE `prefix_settings_global` SET `stg_value`='137' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 138) {
modify_database("", "ALTER TABLE `prefix_quota_members` CHANGE `code` `code` VARCHAR(11) collate utf8_unicode_ci default NULL");
echo $modifyoutput;
flush();
modify_database("", "UPDATE `prefix_settings_global` SET `stg_value`='138' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 139) {
upgrade_survey_tables139();
modify_database("", "UPDATE `prefix_settings_global` SET `stg_value`='139' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 140) {
modify_database("", "ALTER TABLE `prefix_surveys` ADD `emailresponseto` text DEFAULT NULL");
echo $modifyoutput;
flush();
modify_database("", "UPDATE `prefix_settings_global` SET `stg_value`='140' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 141) {
modify_database("", "ALTER TABLE `prefix_surveys` ADD `tokenlength` tinyint(2) NOT NULL default '15'");
echo $modifyoutput;
flush();
modify_database("", "UPDATE `prefix_settings_global` SET `stg_value`='141' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 142) {
upgrade_question_attributes142();
modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `expires` `expires` datetime");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_surveys` CHANGE `startdate` `startdate` datetime");
echo $modifyoutput;
flush();
modify_database("", "UPDATE `prefix_question_attributes` SET `value`='0' WHERE `value`='false'");
echo $modifyoutput;
flush();
modify_database("", "UPDATE `prefix_question_attributes` SET `value`='1' WHERE `value`='true'");
echo $modifyoutput;
flush();
modify_database("", "UPDATE `prefix_settings_global` SET `stg_value`='142' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
if ($oldversion < 143) {
modify_database("", "ALTER TABLE `prefix_questions` ADD `parent_qid` integer NOT NULL default '0'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_answers` ADD `scale_id` tinyint NOT NULL default '0'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_questions` ADD `scale_id` tinyint NOT NULL default '0'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_questions` ADD `same_default` tinyint NOT NULL default '0' COMMENT 'Saves if user set to use the same default value across languages in default options dialog'");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_answers` DROP PRIMARY KEY, ADD PRIMARY KEY (`qid`,`code`,`language`,`scale_id`)");
echo $modifyoutput;
flush();
modify_database("", "CREATE TABLE `prefix_defaultvalues` (\r\n `qid` int(11) NOT NULL default '0',\r\n `scale_id` int(11) NOT NULL default '0',\r\n `sqid` int(11) NOT NULL default '0',\r\n `language` varchar(20) NOT NULL,\r\n `specialtype` varchar(20) NOT NULL default '',\r\n `defaultvalue` text,\r\n PRIMARY KEY (`qid` , `scale_id`, `language`, `specialtype`, `sqid` )\r\n ) ENGINE={$databasetabletype} CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
echo $modifyoutput;
flush();
// -Move all 'answers' that are subquestions to the questions table
// -Move all 'labels' that are answers to the answers table
// -Transscribe the default values where applicable
// -Move default values from answers to questions
upgrade_tables143();
modify_database("", "ALTER TABLE `prefix_answers` DROP COLUMN `default_value`");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_questions` DROP COLUMN `lid`");
echo $modifyoutput;
flush();
modify_database("", "ALTER TABLE `prefix_questions` DROP COLUMN `lid1`");
echo $modifyoutput;
flush();
modify_database("", "CREATE TABLE prefix_sessions(\r\n sesskey VARCHAR( 64 ) NOT NULL DEFAULT '',\r\n expiry DATETIME NOT NULL ,\r\n expireref VARCHAR( 250 ) DEFAULT '',\r\n created DATETIME NOT NULL ,\r\n modified DATETIME NOT NULL ,\r\n sessdata LONGTEXT,\r\n PRIMARY KEY ( sesskey ) ,\r\n INDEX sess2_expiry( expiry ),\r\n INDEX sess2_expireref( expireref ))");
echo $modifyoutput;
flush();
modify_database("", "UPDATE `prefix_settings_global` SET `stg_value`='143' WHERE stg_name='DBVersion'");
echo $modifyoutput;
flush();
}
echo '<br /><br />Database update finished (' . date('Y-m-d H:i:s') . ')<br />';
return true;
}