/**
* Function to upgrade the database tables.
*
* @param Integer $installedVersion The version that exists prior to the upgrade.
* @param Boolean $forceUpgrade If true, we force an upgrade.
* @param Boolean $showErrors If true, show any debug errors.
*/
function WPCW_database_upgradeTables($installedVersion, $forceUpgrade, $showErrors = false)
{
global $wpdb, $wpcwdb;
if ($showErrors) {
$wpdb->show_errors();
}
// Always upgrade tables. Conditionally execute any other table changes.
$upgradeNow = true;
// Only enable if debugging
//$wpdb->show_errors();
// #### Courses Table
$SQL = "CREATE TABLE {$wpcwdb->courses} (\n\t\t\t course_id int(11) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t course_title varchar(150) NOT NULL,\n\t\t\t course_desc text NULL,\n\t\t\t course_opt_completion_wall varchar(20) NOT NULL,\n\t\t\t course_opt_use_certificate varchar(20) NOT NULL DEFAULT 'no_certs',\n\t\t\t course_opt_user_access varchar(20) NOT NULL,\n\t\t\t course_unit_count int(11) unsigned NULL DEFAULT '0',\n\t\t\t course_from_name varchar(150) NOT NULL,\n\t\t\t course_from_email varchar(150) NOT NULL,\n\t\t\t course_to_email varchar(150) NOT NULL,\n\t\t\t course_message_unit_complete text NULL,\n\t\t\t course_message_course_complete text NULL,\n\t\t\t course_message_unit_not_logged_in text NULL,\n\t\t\t course_message_unit_pending text NULL,\n\t\t\t course_message_unit_no_access text NULL,\n\t\t\t course_message_unit_not_yet text NULL,\n\t\t\t course_message_quiz_open_grading_blocking text NULL,\n\t\t\t course_message_quiz_open_grading_non_blocking text NULL,\n\t\t\t email_complete_module_option_admin varchar(20) NOT NULL,\n\t\t\t email_complete_module_option varchar(20) NOT NULL,\n\t\t\t email_complete_module_subject varchar(300) NOT NULL,\n\t\t\t email_complete_module_body text NULL,\n\t\t\t email_complete_course_option_admin varchar(20) NOT NULL,\n\t\t\t email_complete_course_option varchar(20) NOT NULL,\n\t\t\t email_complete_course_subject varchar(300) NOT NULL,\n\t\t\t email_complete_course_body text NULL,\t\t\t \n\t\t\t email_quiz_grade_option varchar(20) NOT NULL,\n\t\t\t email_quiz_grade_subject varchar(300) NOT NULL,\n\t\t\t email_quiz_grade_body text NULL,\n\t\t\t email_complete_course_grade_summary_subject varchar(300) NOT NULL,\n\t\t\t email_complete_course_grade_summary_body text NULL,\n\t\t\t PRIMARY KEY (course_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8";
WPCW_database_installTable($wpcwdb->courses, $SQL, $upgradeNow);
// Added in 2.2 - New course completed message
if ($forceUpgrade || $installedVersion < 2.2) {
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} SET course_message_course_complete = %s \n\t\t\tWHERE course_message_course_complete IS NULL \n\t\t\t OR course_message_course_complete = ''", __("You have now completed the whole course. Congratulations!", 'wp_courseware')));
}
// Added in 2.4 - New quiz messages
if ($forceUpgrade || $installedVersion < 2.4) {
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET course_message_quiz_open_grading_blocking = %s \n\t\t\tWHERE course_message_quiz_open_grading_blocking IS NULL \n\t\t\t OR course_message_quiz_open_grading_blocking = ''", __('Your quiz has been submitted for grading by the course instructor. Once your grade has been entered, you will be able access the next unit.', 'wp_courseware')));
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET course_message_quiz_open_grading_non_blocking = %s \n\t\t\tWHERE course_message_quiz_open_grading_non_blocking IS NULL \n\t\t\t OR course_message_quiz_open_grading_non_blocking = ''", __('Your quiz has been submitted for grading by the course instructor. You have now completed this unit.', 'wp_courseware')));
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_quiz_grade_option = %s \n\t\t\tWHERE email_quiz_grade_option IS NULL \n\t\t\t OR email_quiz_grade_option = ''", 'send_email'));
// After grade completion email.
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_quiz_grade_subject = %s \n\t\t\tWHERE email_quiz_grade_subject IS NULL \n\t\t\t OR email_quiz_grade_subject = ''", EMAIL_TEMPLATE_QUIZ_GRADE_SUBJECT));
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_quiz_grade_body = %s \n\t\t\tWHERE email_quiz_grade_body IS NULL \n\t\t\t OR email_quiz_grade_body = ''", EMAIL_TEMPLATE_QUIZ_GRADE_BODY));
// Grade summary email
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_complete_course_grade_summary_subject = %s \n\t\t\tWHERE email_complete_course_grade_summary_subject IS NULL \n\t\t\t OR email_complete_course_grade_summary_subject = ''", EMAIL_TEMPLATE_COURSE_SUMMARY_WITH_GRADE_SUBJECT));
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_complete_course_grade_summary_body = %s \n\t\t\tWHERE email_complete_course_grade_summary_body IS NULL \n\t\t\t OR email_complete_course_grade_summary_body = ''", EMAIL_TEMPLATE_COURSE_SUMMARY_WITH_GRADE_BODY));
}
// #### Modules Table
$SQL = "CREATE TABLE {$wpcwdb->modules} (\n\t\t\t module_id int(11) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t parent_course_id int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t module_title varchar(150) NOT NULL,\n\t\t\t module_desc text NULL,\n\t\t\t module_order int(11) unsigned NOT NULL DEFAULT '10000',\n\t\t\t module_number int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t PRIMARY KEY (module_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->modules, $SQL, $upgradeNow);
// #### Units Meta Table
$SQL = "CREATE TABLE {$wpcwdb->units_meta} (\n\t\t\t unit_id int(11) unsigned NOT NULL,\n\t\t\t parent_module_id int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t parent_course_id int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t unit_order int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t unit_number int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t PRIMARY KEY (unit_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->units_meta, $SQL, $upgradeNow);
// #### User Courses Allocations Table
$SQL = "CREATE TABLE {$wpcwdb->user_courses} (\n\t\t\t user_id int(11) unsigned NOT NULL,\n\t\t\t course_id int(11) unsigned NOT NULL,\n\t\t\t course_progress int(11) NOT NULL DEFAULT '0',\n\t\t\t course_final_grade_sent VARCHAR(30) NOT NULL DEFAULT '',\n\t\t\t UNIQUE KEY user_id (user_id,course_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->user_courses, $SQL, $upgradeNow);
// #### User Progress Table
$SQL = "CREATE TABLE {$wpcwdb->user_progress} (\n\t\t\t user_id int(11) unsigned NOT NULL,\n\t\t\t unit_id int(11) unsigned NOT NULL,\n\t\t\t unit_completed_date datetime DEFAULT NULL,\n\t\t\t unit_completed_status varchar(20) NOT NULL,\n\t\t\t PRIMARY KEY (user_id,unit_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->user_progress, $SQL, $upgradeNow);
// #### Quizzes
// Test code - removing any existing primary keys
/*if ($forceUpgrade || $installedVersion < 2.4)
{
$SQL = "SHOW COLUMNS FROM $wpcwdb->quiz LIKE 'quiz_id'";
if (!$wpdb->get_row($SQL))
{
$SQL = "ALTER TABLE $wpcwdb->quiz ADD COLUMN quiz_id int(11) unsigned NOT NULL";
$wpdb->query($SQL);
}
}*/
$SQL = "CREATE TABLE {$wpcwdb->quiz} (\n\t\t\t quiz_id int(11) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t quiz_title varchar(150) NOT NULL,\n\t\t\t quiz_desc text NULL,\n\t\t\t parent_unit_id int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t parent_course_id int(11) NOT NULL DEFAULT '0',\t\t\t \n\t\t\t quiz_type varchar(15) NOT NULL,\n\t\t\t quiz_pass_mark int(11) NOT NULL DEFAULT '0',\n\t\t\t quiz_show_answers varchar(15) NOT NULL DEFAULT 'no_answers',\t\t\t\n\t\t\t quiz_show_survey_responses varchar(15) NOT NULL DEFAULT 'no_responses',\n\t\t\t quiz_attempts_allowed int(11) NOT NULL DEFAULT '-1',\n\t\t\t show_answers_settings VARCHAR(500) NOT NULL DEFAULT '',\n\t\t\t quiz_paginate_questions VARCHAR(15) NOT NULL DEFAULT 'no_paging',\n\t\t\t quiz_paginate_questions_settings VARCHAR(500) NOT NULL DEFAULT '',\n\t\t\t quiz_timer_mode varchar(25) NOT NULL DEFAULT 'no_timer',\n\t\t\t quiz_timer_mode_limit int(11) unsigned NOT NULL DEFAULT '15',\n\t\t\t quiz_results_by_tag varchar(10) NOT NULL DEFAULT 'on',\n\t\t\t quiz_results_by_timer varchar(10) NOT NULL DEFAULT 'on',\n\t\t\t quiz_recommended_score varchar(20) NOT NULL DEFAULT 'no_recommended',\n\t\t\t show_recommended_percentage int(10) unsigned NOT NULL DEFAULT 50,\n\t\t\t PRIMARY KEY (quiz_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->quiz, $SQL, $upgradeNow);
if ($forceUpgrade || $installedVersion < 2.61) {
// These are the defaults for the new quiz show answers settings that have been added in V2.61
$default_showAnswers = 'a:5:{s:19:"show_correct_answer";s:2:"on";s:16:"show_user_answer";s:2:"on";s:16:"show_explanation";s:2:"on";s:12:"mark_answers";s:2:"on";s:18:"show_results_later";s:2:"on";}';
$SQL = $wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->quiz} \n\t\t\tSET show_answers_settings = %s \n\t\t\tWHERE show_answers_settings = ''\n\t\t", $default_showAnswers);
$wpdb->query($SQL);
}
// Defaults for the new paging option that was added in V3.00
if ($forceUpgrade || $installedVersion < 3.0) {
// These are the defaults for the new quiz paging settings that have been added in V2.61
$default_pagingOption = 'a:3:{s:30:"allow_review_before_submission";s:2:"on";s:30:"allow_students_to_answer_later";s:2:"on";s:28:"allow_nav_previous_questions";s:2:"on";}';
$SQL = $wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->quiz} \n\t\t\tSET quiz_paginate_questions_settings = %s \n\t\t\tWHERE quiz_paginate_questions_settings = ''\n\t\t", $default_pagingOption);
$wpdb->query($SQL);
}
// #### Quiz - Questions
$SQL = "CREATE TABLE {$wpcwdb->quiz_qs} (\n\t\t\t question_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t question_type VARCHAR(20) NOT NULL DEFAULT 'multi',\n\t\t\t question_question text NULL,\n\t\t\t question_answers text NULL,\n\t\t\t question_data_answers text NULL,\n\t\t\t question_correct_answer VARCHAR(300) NOT NULL,\n\t\t\t question_answer_type VARCHAR(50) NOT NULL DEFAULT '',\n\t\t\t question_answer_hint text NULL,\n\t\t\t question_answer_explanation text NULL,\n\t\t\t question_image VARCHAR(300) NOT NULL DEFAULT '',\n\t\t\t question_answer_file_types VARCHAR(300) NOT NULL DEFAULT '',\n\t\t\t question_usage_count int(11) UNSIGNED DEFAULT 0,\n\t\t\t question_expanded_count int(11) UNSIGNED DEFAULT 1,\n\t\t\t question_multi_random_enable int(2) UNSIGNED DEFAULT 0,\n\t\t\t question_multi_random_count int(4) UNSIGNED DEFAULT 5,\n\t\t\t PRIMARY KEY (question_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->quiz_qs, $SQL, $upgradeNow);
// #### Added in 2.4 - Quiz - User Progress - remove old unique index by checking for it first.
if ($forceUpgrade || $installedVersion < 2.8) {
$SQL = "SHOW INDEX FROM {$wpcwdb->user_progress_quiz} WHERE KEY_NAME = 'user_id'";
if ($wpdb->get_row($SQL)) {
$SQL = "ALTER TABLE {$wpcwdb->user_progress_quiz} DROP INDEX user_id";
$wpdb->query($SQL);
}
}
// #### Added in 2.5 - Quiz - Upgrade questions to use the new serialised array format.
if ($forceUpgrade || $installedVersion < 2.5) {
WPCW_database_upgrade_quizQuestions();
}
// #### Quiz - User Progress
$SQL = "CREATE TABLE {$wpcwdb->user_progress_quiz} (\n\t\t\t user_id int(11) NOT NULL,\n\t\t\t unit_id int(11) NOT NULL,\n\t\t\t quiz_id bigint(20) NOT NULL,\n\t\t\t quiz_attempt_id int(11) NOT NULL DEFAULT '0',\t\t\t \n\t\t\t quiz_completed_date datetime NOT NULL,\n\t\t\t quiz_started_date datetime NOT NULL,\n\t\t\t quiz_correct_questions int(11) unsigned NOT NULL,\n\t\t\t quiz_grade FLOAT(8,2) NOT NULL DEFAULT '-1',\n\t\t\t quiz_question_total int(11) unsigned NOT NULL,\n\t\t\t quiz_data text NULL,\n\t\t\t quiz_is_latest VARCHAR(50) DEFAULT 'latest',\n\t\t\t quiz_needs_marking int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t quiz_needs_marking_list TEXT NULL,\n\t\t\t quiz_next_step_type VARCHAR(50) DEFAULT '',\t\t\t \n\t\t\t quiz_next_step_msg TEXT DEFAULT '',\n\t\t\t quiz_paging_status VARCHAR(20) NOT NULL DEFAULT 'complete',\t\t\n\t\t\t quiz_paging_next_q int(11) NOT NULL DEFAULT 0,\n\t\t\t quiz_paging_incomplete int(11) NOT NULL DEFAULT 0,\n\t\t\t quiz_completion_time_seconds BIGINT NOT NULL DEFAULT 0,\n\t\t\t UNIQUE KEY unique_progress_item (user_id,unit_id,quiz_id,quiz_attempt_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->user_progress_quiz, $SQL, $upgradeNow);
// #### Added in 2.4 - Quiz - User Progress - grade data added to the table.
if ($forceUpgrade || $installedVersion < 2.4) {
set_time_limit(0);
// All quizzes with a grade of -1 and all items are graded.
$SQL_NEED_UPDATE = "\n\t\t\tSELECT user_id, unit_id, quiz_id, quiz_attempt_id, quiz_data\n\t\t\tFROM {$wpcwdb->user_progress_quiz} \n\t\t\tWHERE quiz_grade = -1\n\t\t\t AND quiz_needs_marking = 0";
$quizProgressToUpdate = $wpdb->get_row($SQL_NEED_UPDATE);
while ($quizProgressToUpdate) {
$quizData = maybe_unserialize($quizProgressToUpdate->quiz_data);
$newGrade = WPCW_quizzes_calculateGradeForQuiz($quizData);
if ($newGrade > -1) {
$wpdb->query($wpdb->prepare("\n\t\t\t\t\tUPDATE {$wpcwdb->user_progress_quiz} \n\t\t\t\t\t SET quiz_grade = %s\n\t\t\t\t\tWHERE unit_id = %d\n\t\t\t\t\t AND user_id = %d\n\t\t\t\t\t AND quiz_id = %d\n\t\t\t\t\t AND quiz_attempt_id = %d\n\t\t\t\t", $newGrade, $quizProgressToUpdate->unit_id, $quizProgressToUpdate->user_id, $quizProgressToUpdate->quiz_id, $quizProgressToUpdate->quiz_attempt_id));
}
$quizProgressToUpdate = $wpdb->get_row($SQL_NEED_UPDATE);
flush();
}
}
// #### Mapping of membership levels
$SQL = "CREATE TABLE {$wpcwdb->map_member_levels} (\n\t\t\t \tcourse_id int(11) NOT NULL,\n \t\t\t\tmember_level_id varchar(100) NOT NULL,\n \t\t\t\tUNIQUE KEY course_id (course_id,member_level_id) \t\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->map_member_levels, $SQL, $upgradeNow);
// #### Mapping of certificates
$SQL = "CREATE TABLE {$wpcwdb->certificates} (\n\t\t\t cert_user_id int(11) NOT NULL,\n\t\t\t cert_course_id int(11) NOT NULL,\n\t\t\t cert_access_key varchar(50) NOT NULL,\n\t\t\t cert_generated datetime NOT NULL,\n\t\t\t UNIQUE KEY cert_user_id (cert_user_id,cert_course_id)\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->certificates, $SQL, $upgradeNow);
// #### Questions - Tags
$SQL = "CREATE TABLE {$wpcwdb->question_tags} (\n\t\t\t\tquestion_tag_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t\tquestion_tag_name varchar(150) NOT NULL,\n\t\t\t\tquestion_tag_usage int(11) unsigned NOT NULL,\n\t\t\t\tPRIMARY KEY (question_tag_id)\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->question_tags, $SQL, $upgradeNow);
// #### Questions - Tag Mappings
$SQL = "CREATE TABLE {$wpcwdb->question_tag_mapping} (\n\t\t\t\tquestion_id bigint(20) unsigned NOT NULL,\n\t\t\t\ttag_id bigint(20) unsigned NOT NULL,\n\t\t\t\tUNIQUE KEY question_tag_id (question_id,tag_id)\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->question_tag_mapping, $SQL, $upgradeNow);
// #### Questions/Quizzes - Mappings
$SQL = "CREATE TABLE {$wpcwdb->quiz_qs_mapping} (\n\t\t\t\tparent_quiz_id bigint(20) unsigned NULL,\n\t\t\t\tquestion_id bigint(20) unsigned NOT NULL,\n\t\t\t\tquestion_order int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t\tUNIQUE KEY question_assoc_id (parent_quiz_id,question_id)\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->quiz_qs_mapping, $SQL, $upgradeNow);
// #### Random Questions - Lock selections to a user so that they don't
// see different variations if refreshed.
$SQL = "CREATE TABLE {$wpcwdb->question_rand_lock} (\n\t\t\t\tquestion_user_id int(11) unsigned NOT NULL,\n\t\t\t\trand_question_id int(11) unsigned NOT NULL,\n\t\t\t\tparent_unit_id int(11) unsigned NOT NULL,\n\t\t\t\tquestion_selection_list text NOT NULL,\n\t\t\t\tUNIQUE KEY wpcw_question_rand_lock (question_user_id,rand_question_id)\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->question_rand_lock, $SQL, $upgradeNow);
// #### Added in 3.0 - Upgrade questions to have multiple quiz parents
// by creating a quiz-to-question mapping. We need to migrate all of
// the previous questions to refer to the new quiz parent.
if ($forceUpgrade || $installedVersion < 3.0) {
// See if we still have the old versions of the columns. Simply as parent_quiz_id and question_order
// are now deprecated.
$gotCol_parent_quiz_id = $wpdb->get_row("SHOW COLUMNS FROM {$wpcwdb->quiz_qs} LIKE 'parent_quiz_id'");
$gotCol_question_order = $wpdb->get_row("SHOW COLUMNS FROM {$wpcwdb->quiz_qs} LIKE 'question_order'");
// Yep, found both versions of the columns.
if ($gotCol_parent_quiz_id && $gotCol_question_order) {
// Need to get data from all questions
$SQL_NEED_UPDATE = "\n\t\t\t\tSELECT question_id, parent_quiz_id, question_order\n\t\t\t\tFROM {$wpcwdb->quiz_qs} \t\t\t\t\n\t\t\t";
$questionsToUpdate = $wpdb->get_results($SQL_NEED_UPDATE);
if ($questionsToUpdate) {
set_time_limit(0);
foreach ($questionsToUpdate as $singleQuestion) {
// Create the association in the new table, ignoring if it exists already.
$wpdb->query($wpdb->prepare("\n\t\t\t\t\t\tINSERT IGNORE INTO {$wpcwdb->quiz_qs_mapping} \n\t\t\t\t\t\t(parent_quiz_id, question_id, question_order)\n\t\t\t\t\t\tVALUES (%d, %d, %d)\n\t\t\t\t\t", $singleQuestion->parent_quiz_id, $singleQuestion->question_id, $singleQuestion->question_order));
// Update question to indicate it's usage
$countNumber = $wpdb->get_var($wpdb->prepare("\n\t\t\t\t\t\tSELECT COUNT(parent_quiz_id)\n\t\t\t\t\t\tFROM {$wpcwdb->quiz_qs_mapping} \n\t\t\t\t\t\tWHERE question_id = %d\n\t\t\t\t\t\t", $singleQuestion->question_id));
$wpdb->query($wpdb->prepare("\n\t\t\t\t\t\tUPDATE {$wpcwdb->quiz_qs}\n\t\t\t\t\t\tSET question_usage_count = %d \n\t\t\t\t\t\tWHERE question_id = %d\n\t\t\t\t\t\t", $countNumber, $singleQuestion->question_id));
flush();
}
}
// end if $questionsToUpdate
// Data migrated, now we just rename the old columns, rather than delete. Just in case something
// goes wrong. We can remove some day in the future.
$wpdb->query("ALTER TABLE {$wpcwdb->quiz_qs} CHANGE parent_quiz_id deprecated_parent_quiz_id bigint(20) AFTER question_answers");
$wpdb->query("ALTER TABLE {$wpcwdb->quiz_qs} CHANGE question_order deprecated_question_order int(11) AFTER question_answers");
}
// end if ($gotCol_parent_quiz_id && $gotCol_question_order)
}
// #### List of quiz custom feedback
$SQL = "CREATE TABLE {$wpcwdb->quiz_feedback} (\n\t\t\t \tqfeedback_id int(11) unsigned NOT NULL AUTO_INCREMENT,\n \t\t\t\tqfeedback_tag_id bigint(20) unsigned NOT NULL,\n \t\t\t\tqfeedback_quiz_id int(1) unsigned NOT NULL,\n \t\t\t\tqfeedback_summary varchar(300) NOT NULL,\n \t\t\t\tqfeedback_score_type varchar(20) NOT NULL DEFAULT 'below',\n \t\t\t\tqfeedback_score_grade int(11) unsigned NOT NULL DEFAULT '50',\n \t\t\t\tqfeedback_message text NOT NULL,\n \t\t\t\tPRIMARY KEY (qfeedback_id)\t \t\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->quiz_feedback, $SQL, $upgradeNow);
// Update settings once upgrade has happened
update_option(WPCW_DATABASE_KEY, WPCW_DATABASE_VERSION);
}
/**
* Function to upgrade the database tables.
*
* @param Integer $installedVersion The version that exists prior to the upgrade.
* @param Boolean $forceUpgrade If true, we force an upgrade.
* @param Boolean $showErrors If true, show any debug errors.
*/
function WPCW_database_upgradeTables($installedVersion, $forceUpgrade, $showErrors = false)
{
global $wpdb, $wpcwdb;
if ($showErrors) {
$wpdb->show_errors();
}
// Always upgrade tables. Conditionally execute any other table changes.
$upgradeNow = true;
// Only enable if debugging
//$wpdb->show_errors();
// #### Courses Table
$SQL = "CREATE TABLE {$wpcwdb->courses} (\n\t\t\t course_id int(10) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t course_title varchar(150) NOT NULL,\n\t\t\t course_desc text NOT NULL,\n\t\t\t course_opt_completion_wall varchar(20) NOT NULL,\n\t\t\t course_opt_use_certificate varchar(20) NOT NULL DEFAULT 'no_certs',\n\t\t\t course_opt_user_access varchar(20) NOT NULL,\n\t\t\t course_unit_count int(10) unsigned NULL DEFAULT '0',\n\t\t\t course_from_name varchar(150) NOT NULL,\n\t\t\t course_from_email varchar(150) NOT NULL,\n\t\t\t course_to_email varchar(150) NOT NULL,\n\t\t\t course_message_unit_complete text NOT NULL,\n\t\t\t course_message_course_complete text NOT NULL,\n\t\t\t course_message_unit_not_logged_in text NOT NULL,\n\t\t\t course_message_unit_pending text NOT NULL,\n\t\t\t course_message_unit_no_access text NOT NULL,\n\t\t\t course_message_unit_not_yet text NOT NULL,\n\t\t\t course_message_quiz_open_grading_blocking text NOT NULL,\n\t\t\t course_message_quiz_open_grading_non_blocking text NOT NULL,\n\t\t\t email_complete_module_option_admin varchar(20) NOT NULL,\n\t\t\t email_complete_module_option varchar(20) NOT NULL,\n\t\t\t email_complete_module_subject varchar(300) NOT NULL,\n\t\t\t email_complete_module_body text NOT NULL,\n\t\t\t email_complete_course_option_admin varchar(20) NOT NULL,\n\t\t\t email_complete_course_option varchar(20) NOT NULL,\n\t\t\t email_complete_course_subject varchar(300) NOT NULL,\n\t\t\t email_complete_course_body text NOT NULL,\t\t\t \n\t\t\t email_quiz_grade_option varchar(20) NOT NULL,\n\t\t\t email_quiz_grade_subject varchar(300) NOT NULL,\n\t\t\t email_quiz_grade_body text NOT NULL,\n\t\t\t email_complete_course_grade_summary_subject varchar(300) NOT NULL,\n\t\t\t email_complete_course_grade_summary_body text NOT NULL,\n\t\t\t PRIMARY KEY (course_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8";
WPCW_database_installTable($wpcwdb->courses, $SQL, $upgradeNow);
// Added in 1.22 - New course completed message
if ($forceUpgrade || $installedVersion < 1.22) {
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} SET course_message_course_complete = %s \n\t\t\tWHERE course_message_course_complete IS NULL \n\t\t\t OR course_message_course_complete = ''", __("You have now completed the whole course. Congratulations!", 'wp_courseware')));
}
// Added in 1.24 - New quiz messages
if ($forceUpgrade || $installedVersion < 1.24) {
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET course_message_quiz_open_grading_blocking = %s \n\t\t\tWHERE course_message_quiz_open_grading_blocking IS NULL \n\t\t\t OR course_message_quiz_open_grading_blocking = ''", __('Your quiz has been submitted for grading by the course instructor. Once your grade has been entered, you will be able access the next unit.', 'wp_courseware')));
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET course_message_quiz_open_grading_non_blocking = %s \n\t\t\tWHERE course_message_quiz_open_grading_non_blocking IS NULL \n\t\t\t OR course_message_quiz_open_grading_non_blocking = ''", __('Your quiz has been submitted for grading by the course instructor. You have now completed this unit.', 'wp_courseware')));
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_quiz_grade_option = %s \n\t\t\tWHERE email_quiz_grade_option IS NULL \n\t\t\t OR email_quiz_grade_option = ''", 'send_email'));
// After grade completion email.
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_quiz_grade_subject = %s \n\t\t\tWHERE email_quiz_grade_subject IS NULL \n\t\t\t OR email_quiz_grade_subject = ''", EMAIL_TEMPLATE_QUIZ_GRADE_SUBJECT));
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_quiz_grade_body = %s \n\t\t\tWHERE email_quiz_grade_body IS NULL \n\t\t\t OR email_quiz_grade_body = ''", EMAIL_TEMPLATE_QUIZ_GRADE_BODY));
// Grade summary email
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_complete_course_grade_summary_subject = %s \n\t\t\tWHERE email_complete_course_grade_summary_subject IS NULL \n\t\t\t OR email_complete_course_grade_summary_subject = ''", EMAIL_TEMPLATE_COURSE_SUMMARY_WITH_GRADE_SUBJECT));
$SQL = $wpdb->query($wpdb->prepare("\n\t\t\tUPDATE {$wpcwdb->courses} \n\t\t\t SET email_complete_course_grade_summary_body = %s \n\t\t\tWHERE email_complete_course_grade_summary_body IS NULL \n\t\t\t OR email_complete_course_grade_summary_body = ''", EMAIL_TEMPLATE_COURSE_SUMMARY_WITH_GRADE_BODY));
}
// #### Modules Table
$SQL = "CREATE TABLE {$wpcwdb->modules} (\n\t\t\t module_id int(10) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t parent_course_id int(10) unsigned NOT NULL DEFAULT '0',\n\t\t\t module_title varchar(150) NOT NULL,\n\t\t\t module_desc text NOT NULL,\n\t\t\t module_order int(10) unsigned NOT NULL DEFAULT '10000',\n\t\t\t module_number int(10) unsigned NOT NULL DEFAULT '0',\n\t\t\t PRIMARY KEY (module_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->modules, $SQL, $upgradeNow);
// #### Units Meta Table
$SQL = "CREATE TABLE {$wpcwdb->units_meta} (\n\t\t\t unit_id int(10) unsigned NOT NULL,\n\t\t\t parent_module_id int(10) unsigned NOT NULL DEFAULT '0',\n\t\t\t parent_course_id int(10) unsigned NOT NULL DEFAULT '0',\n\t\t\t unit_order int(10) unsigned NOT NULL DEFAULT '0',\n\t\t\t unit_number int(10) unsigned NOT NULL DEFAULT '0',\n\t\t\t PRIMARY KEY (unit_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->units_meta, $SQL, $upgradeNow);
// #### User Courses Allocations Table
$SQL = "CREATE TABLE {$wpcwdb->user_courses} (\n\t\t\t user_id int(10) unsigned NOT NULL,\n\t\t\t course_id int(10) unsigned NOT NULL,\n\t\t\t course_progress int(11) NOT NULL DEFAULT '0',\n\t\t\t course_final_grade_sent VARCHAR(30) NOT NULL DEFAULT '',\n\t\t\t UNIQUE KEY user_id (user_id,course_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->user_courses, $SQL, $upgradeNow);
// #### User Progress Table
$SQL = "CREATE TABLE {$wpcwdb->user_progress} (\n\t\t\t user_id int(10) unsigned NOT NULL,\n\t\t\t unit_id int(10) unsigned NOT NULL,\n\t\t\t unit_completed_date datetime DEFAULT NULL,\n\t\t\t unit_completed_status varchar(20) NOT NULL,\n\t\t\t PRIMARY KEY (user_id,unit_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->user_progress, $SQL, $upgradeNow);
// #### Quizzes
$SQL = "CREATE TABLE {$wpcwdb->quiz} (\n\t\t\t quiz_id int(10) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t quiz_title varchar(150) NOT NULL,\n\t\t\t quiz_desc text NOT NULL,\n\t\t\t parent_unit_id int(10) unsigned NOT NULL DEFAULT '0',\n\t\t\t parent_course_id int(11) NOT NULL DEFAULT '0',\t\t\t \n\t\t\t quiz_type varchar(15) NOT NULL,\n\t\t\t quiz_pass_mark int(11) NOT NULL DEFAULT '0',\n\t\t\t quiz_show_answers varchar(15) NOT NULL DEFAULT 'no_answers',\n\t\t\t PRIMARY KEY (quiz_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->quiz, $SQL, $upgradeNow);
// #### Quiz - Questions
$SQL = "CREATE TABLE {$wpcwdb->quiz_qs} (\n\t\t\t question_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t parent_quiz_id bigint(20) unsigned NOT NULL DEFAULT '0',\n\t\t\t question_type varchar(20) NOT NULL DEFAULT 'multi',\n\t\t\t question_question text NOT NULL,\n\t\t\t question_answers text NOT NULL,\n\t\t\t question_correct_answer varchar(300) NOT NULL,\n\t\t\t question_answer_type varchar(50) NOT NULL DEFAULT '',\n\t\t\t question_order int(11) unsigned NOT NULL DEFAULT '0',\n\t\t\t question_answer_hint text NOT NULL DEFAULT '',\n\t\t\t question_answer_file_types varchar(300) NOT NULL DEFAULT '',\n\t\t\t PRIMARY KEY (question_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->quiz_qs, $SQL, $upgradeNow);
// #### Added in 1.24 - Quiz - User Progress - remove old unique index by checking for it first.
if ($forceUpgrade || $installedVersion < 1.24) {
$SQL = "SHOW INDEX FROM {$wpcwdb->user_progress_quiz} WHERE KEY_NAME = 'user_id'";
if ($wpdb->get_row($SQL)) {
$SQL = "ALTER TABLE {$wpcwdb->user_progress_quiz} DROP INDEX user_id";
$wpdb->query($SQL);
}
}
// #### Quiz - User Progress
$SQL = "CREATE TABLE {$wpcwdb->user_progress_quiz} (\n\t\t\t user_id int(11) NOT NULL,\n\t\t\t unit_id int(11) NOT NULL,\n\t\t\t quiz_id bigint(20) NOT NULL,\n\t\t\t quiz_attempt_id int(11) NOT NULL DEFAULT '0',\t\t\t \n\t\t\t quiz_completed_date datetime NOT NULL,\n\t\t\t quiz_correct_questions int(10) unsigned NOT NULL,\n\t\t\t quiz_grade FLOAT(8,2) NOT NULL DEFAULT '-1',\n\t\t\t quiz_question_total int(10) unsigned NOT NULL,\n\t\t\t quiz_data text NOT NULL,\n\t\t\t quiz_is_latest VARCHAR(50) DEFAULT 'latest',\n\t\t\t quiz_needs_marking int(10) unsigned NOT NULL DEFAULT '0',\n\t\t\t quiz_needs_marking_list TEXT NOT NULL,\n\t\t\t quiz_next_step_type VARCHAR(50) DEFAULT '',\t\t\t \n\t\t\t quiz_next_step_msg TEXT DEFAULT '',\t\t\t \n\t\t\t UNIQUE KEY unique_progress_item (user_id,unit_id,quiz_id,quiz_attempt_id)\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->user_progress_quiz, $SQL, $upgradeNow);
// #### Added in 1.24 - Quiz - User Progress - grade data added to the table.
if ($forceUpgrade || $installedVersion < 1.24) {
set_time_limit(0);
// All quizzes with a grade of -1 and all items are graded.
$SQL_NEED_UPDATE = "\n\t\t\tSELECT user_id, unit_id, quiz_id, quiz_attempt_id, quiz_data\n\t\t\tFROM {$wpcwdb->user_progress_quiz} \n\t\t\tWHERE quiz_grade = -1\n\t\t\t AND quiz_needs_marking = 0";
$quizProgressToUpdate = $wpdb->get_row($SQL_NEED_UPDATE);
while ($quizProgressToUpdate) {
$quizData = maybe_unserialize($quizProgressToUpdate->quiz_data);
$newGrade = WPCW_quizzes_calculateGradeForQuiz($quizData);
if ($newGrade > -1) {
$wpdb->query($wpdb->prepare("\n\t\t\t\t\tUPDATE {$wpcwdb->user_progress_quiz} \n\t\t\t\t\t SET quiz_grade = %s\n\t\t\t\t\tWHERE unit_id = %d\n\t\t\t\t\t AND user_id = %d\n\t\t\t\t\t AND quiz_id = %d\n\t\t\t\t\t AND quiz_attempt_id = %d\n\t\t\t\t", $newGrade, $quizProgressToUpdate->unit_id, $quizProgressToUpdate->user_id, $quizProgressToUpdate->quiz_id, $quizProgressToUpdate->quiz_attempt_id));
}
$quizProgressToUpdate = $wpdb->get_row($SQL_NEED_UPDATE);
flush();
}
}
// #### Mapping of membership levels
$SQL = "CREATE TABLE {$wpcwdb->map_member_levels} (\n\t\t\t \tcourse_id int(11) NOT NULL,\n \t\t\t\tmember_level_id varchar(100) NOT NULL,\n \t\t\t\tUNIQUE KEY course_id (course_id,member_level_id) \t\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->map_member_levels, $SQL, $upgradeNow);
// #### Mapping of certificates
$SQL = "CREATE TABLE {$wpcwdb->certificates} (\n\t\t\t cert_user_id int(11) NOT NULL,\n\t\t\t cert_course_id int(11) NOT NULL,\n\t\t\t cert_access_key varchar(50) NOT NULL,\n\t\t\t cert_generated datetime NOT NULL,\n\t\t\t UNIQUE KEY cert_user_id (cert_user_id,cert_course_id)\t\t\t\n\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
WPCW_database_installTable($wpcwdb->certificates, $SQL, $upgradeNow);
}
