Index: lams_common/db/sql/create_lams_11_tables.sql =================================================================== diff -u -rf9d8fc434a8b7629a1353f532eb15e5c4f5f3cac -r2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe --- lams_common/db/sql/create_lams_11_tables.sql (.../create_lams_11_tables.sql) (revision f9d8fc434a8b7629a1353f532eb15e5c4f5f3cac) +++ lams_common/db/sql/create_lams_11_tables.sql (.../create_lams_11_tables.sql) (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -271,13 +271,15 @@ CREATE TABLE `lams_cr_node` ( `node_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, + `portrait_uuid` BINARY(16), `workspace_id` bigint(20) unsigned NOT NULL, `path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_date_time` datetime NOT NULL, `next_version_id` bigint(20) unsigned NOT NULL DEFAULT '1', `parent_nv_id` bigint(20) unsigned DEFAULT NULL, PRIMARY KEY (`node_id`), + UNIQUE INDEX IDX_portrait_uuid (portrait_uuid), KEY `workspace_id` (`workspace_id`), CONSTRAINT `FK_lams_cr_node_1` FOREIGN KEY (`workspace_id`) REFERENCES `lams_cr_workspace` (`workspace_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; @@ -459,6 +461,7 @@ `prefix` varchar(11) COLLATE utf8mb4_unicode_ci NOT NULL, `userinfo_url` text COLLATE utf8mb4_unicode_ci NOT NULL, `lesson_finish_url` text COLLATE utf8mb4_unicode_ci, + `logout_url` TEXT, `disabled` bit(1) NOT NULL, `time_to_live_login_request` int(11) DEFAULT '80', `time_to_live_login_request_enabled` tinyint(1) NOT NULL DEFAULT '0', @@ -472,6 +475,8 @@ `force_restart` tinyint(1) DEFAULT '0', `allow_restart` tinyint(1) DEFAULT '0', `gradebook_on_complete` tinyint(1) DEFAULT '1', + `use_alternative_user_id_parameter_name` tinyint(1) DEFAULT '0', + `membership_url` text COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`sid`), UNIQUE KEY `serverid` (`serverid`), UNIQUE KEY `prefix` (`prefix`), @@ -636,7 +641,7 @@ `mark` double DEFAULT NULL, `feedback` text COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`uid`), - KEY `lesson_id` (`lesson_id`,`user_id`), + UNIQUE KEY `lesson_id` (`lesson_id`,`user_id`), KEY `FK_lams_gradebook_user_lesson_2` (`user_id`), CONSTRAINT `FK_lams_gradebook_user_lesson_1` FOREIGN KEY (`lesson_id`) REFERENCES `lams_lesson` (`lesson_id`), CONSTRAINT `FK_lams_gradebook_user_lesson_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE @@ -993,6 +998,7 @@ `stop_after_activity` tinyint(1) NOT NULL DEFAULT '0', `transition_to_id` bigint(20) DEFAULT NULL, `transition_from_id` bigint(20) DEFAULT NULL, + `branching_ordered_asc` TINYINT(1), PRIMARY KEY (`activity_id`), KEY `lams_learning_activity_tool_content_id` (`tool_content_id`), KEY `learning_library_id` (`learning_library_id`), @@ -1244,17 +1250,17 @@ `end_date_time` datetime DEFAULT NULL, `schedule_end_date_time` datetime DEFAULT NULL, `previous_state_id` int(3) DEFAULT NULL, - `learner_presence_avail` tinyint(1) DEFAULT '0', - `learner_im_avail` tinyint(1) DEFAULT '0', - `live_edit_enabled` tinyint(1) DEFAULT '0', - `enable_lesson_notifications` tinyint(1) DEFAULT '0', - `locked_for_edit` tinyint(1) DEFAULT '0', - `marks_released` tinyint(1) DEFAULT '0', - `enable_lesson_INTro` tinyint(1) DEFAULT '0', - `display_design_image` tinyint(1) DEFAULT '0', - `force_restart` tinyint(1) DEFAULT '0', - `allow_restart` tinyint(1) DEFAULT '0', - `gradebook_on_complete` tinyint(1) DEFAULT '0', + `learner_presence_avail` TINYINT(1) NOT NULL DEFAULT 0, + `learner_im_avail` TINYINT(1) NOT NULL DEFAULT 0, + `live_edit_enabled` TINYINT(1) NOT NULL DEFAULT 0, + `enable_lesson_notifications` TINYINT(1) NOT NULL DEFAULT 0, + `locked_for_edit` TINYINT(1) NOT NULL DEFAULT 0, + `marks_released` TINYINT(1) NOT NULL DEFAULT 0, + `enable_lesson_INTro` TINYINT(1) NOT NULL DEFAULT 0, + `display_design_image` TINYINT(1) NOT NULL DEFAULT 0, + `force_restart` TINYINT(1) NOT NULL DEFAULT 0, + `allow_restart` TINYINT(1) NOT NULL DEFAULT 0, + `gradebook_on_complete` TINYINT(1) NOT NULL DEFAULT 0, PRIMARY KEY (`lesson_id`), KEY `learning_design_id` (`learning_design_id`), KEY `user_id` (`user_id`), @@ -1334,6 +1340,7 @@ PRIMARY KEY (`id`), KEY `event_log_occurred_date_time` (`occurred_date_time`), KEY `FK_event_log_event_type_idx` (`log_event_type_id`), + KEY `event_log_date_and_type` (`occurred_date_time`, `log_event_type_id`), CONSTRAINT `FK_event_log_event_type` FOREIGN KEY (`log_event_type_id`) REFERENCES `lams_log_event_type` (`log_event_type_id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; @@ -1519,21 +1526,16 @@ CREATE TABLE `lams_outcome` ( `outcome_id` mediumint(9) NOT NULL AUTO_INCREMENT, - `organisation_id` bigint(20) DEFAULT NULL, `scale_id` mediumint(9) NOT NULL, `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `description` text COLLATE utf8mb4_unicode_ci, - `content_folder_id` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `create_by` bigint(20) DEFAULT NULL, `create_date_time` datetime NOT NULL, PRIMARY KEY (`outcome_id`), - UNIQUE KEY `code_2` (`code`,`organisation_id`), KEY `name` (`name`), KEY `code` (`code`), - KEY `FK_lams_outcome_1` (`organisation_id`), KEY `FK_lams_outcome_2` (`scale_id`), - CONSTRAINT `FK_lams_outcome_1` FOREIGN KEY (`organisation_id`) REFERENCES `lams_organisation` (`organisation_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_lams_outcome_2` FOREIGN KEY (`scale_id`) REFERENCES `lams_outcome_scale` (`scale_id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; @@ -1550,6 +1552,7 @@ `lesson_id` bigint(20) DEFAULT NULL, `tool_content_id` bigint(20) DEFAULT NULL, `item_id` bigint(20) DEFAULT NULL, + qb_question_id INT, PRIMARY KEY (`mapping_id`), KEY `FK_lams_outcome_mapping_1` (`outcome_id`), KEY `FK_lams_outcome_mapping_2` (`lesson_id`), @@ -1588,22 +1591,17 @@ -- DROP TABLE IF EXISTS `lams_outcome_scale`; - + CREATE TABLE `lams_outcome_scale` ( `scale_id` mediumint(9) NOT NULL AUTO_INCREMENT, - `organisation_id` bigint(20) DEFAULT NULL, `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `description` text COLLATE utf8mb4_unicode_ci, - `content_folder_id` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `create_by` bigint(20) DEFAULT NULL, `create_date_time` datetime NOT NULL, PRIMARY KEY (`scale_id`), - UNIQUE KEY `code_2` (`code`,`organisation_id`), KEY `name` (`name`), - KEY `code` (`code`), - KEY `FK_lams_outcome_scale_1` (`organisation_id`), - CONSTRAINT `FK_lams_outcome_scale_1` FOREIGN KEY (`organisation_id`) REFERENCES `lams_organisation` (`organisation_id`) ON DELETE CASCADE ON UPDATE CASCADE + KEY `code` (`code`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; @@ -2272,7 +2270,7 @@ `workspace_folder_id` bigint(20) DEFAULT NULL, `theme_id` bigint(20) DEFAULT NULL, `locale_id` int(11) DEFAULT NULL, - `portrait_uuid` bigint(20) DEFAULT NULL, + `portrait_uuid` BINARY(16), `change_password` tinyint(1) DEFAULT '0', `timezone` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `first_login` tinyint(1) DEFAULT '1', @@ -2442,4 +2440,201 @@ PRIMARY KEY (`system_name`,`patch_level`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table `lams_qb_question` +-- + +DROP TABLE IF EXISTS `lams_qb_question`; + +CREATE TABLE `lams_qb_question` ( + `uid` BIGINT AUTO_INCREMENT, + `uuid` BINARY(16) NOT NULL, + `type` TINYINT NOT NULL, + `question_id` INT NOT NULL, + `version` SMALLINT NOT NULL DEFAULT 1, + `create_date` DATETIME NOT NULL DEFAULT NOW(), + `content_folder_id` char(36) NOT NULL, + `name` TEXT, + `description` MEDIUMTEXT, + `max_mark` INT, + `feedback` TEXT, + `penalty_factor` float DEFAULT 0, + `answer_required` TINYINT(1) DEFAULT 0, + `multiple_answers_allowed` TINYINT(1) DEFAULT 0, + `incorrect_answer_nullifies_mark` TINYINT(1) DEFAULT 0, + `feedback_on_correct` TEXT, + `feedback_on_partially_correct` TEXT, + `feedback_on_incorrect` TEXT, + `shuffle` TINYINT(1) DEFAULT 0, + `prefix_answers_with_letters` TINYINT(1) DEFAULT 0, + `case_sensitive` TINYINT(1) DEFAULT 0, + `correct_answer` TINYINT(1) DEFAULT 0, + `allow_rich_editor` TINYINT(1) DEFAULT 0, + `max_words_limit` int(11) DEFAULT 0, + `min_words_limit` int(11) DEFAULT 0, + `hedging_justification_enabled` TINYINT(1) DEFAULT 0, + `autocomplete_enabled` TINYINT(1) DEFAULT 0, + PRIMARY KEY (uid), + CONSTRAINT UQ_question_version UNIQUE INDEX (question_id, version) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +DROP TRIGGER IF EXISTS before_insert_qb_question; + +CREATE TRIGGER before_insert_qb_question + BEFORE INSERT ON lams_qb_question + FOR EACH ROW + SET new.uuid = IF(new.uuid IS NULL, UUID_TO_BIN(UUID()), new.uuid); + + +-- +-- Table structure for table `lams_qb_tool_question` +-- + +DROP TABLE IF EXISTS `lams_qb_tool_question`; + +CREATE TABLE lams_qb_tool_question ( + `tool_question_uid` BIGINT AUTO_INCREMENT, + `qb_question_uid` BIGINT NOT NULL, + `tool_content_id` BIGINT NOT NULL, + `display_order` TINYINT NOT NULL DEFAULT 1, + PRIMARY KEY (tool_question_uid), + INDEX (tool_content_id), + CONSTRAINT FK_lams_qb_tool_question_1 FOREIGN KEY (qb_question_uid) REFERENCES lams_qb_question (uid) ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- Table structure for table `lams_qb_option` +-- + +DROP TABLE IF EXISTS `lams_qb_option`; + +CREATE TABLE lams_qb_option ( + `uid` BIGINT AUTO_INCREMENT, + `qb_question_uid` BIGINT NOT NULL, + `display_order` TINYINT NOT NULL DEFAULT 1, + `name` TEXT, + `matching_pair` TEXT, + `numerical_option` float DEFAULT 0, + `max_mark` float DEFAULT 0, + `accepted_error` float DEFAULT 0, + `feedback` TEXT, + PRIMARY KEY (uid), + INDEX (display_order), + CONSTRAINT FK_lams_qb_option_1 FOREIGN KEY (qb_question_uid) REFERENCES lams_qb_question (uid) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- Table structure for table `lams_qb_question_unit` +-- + +DROP TABLE IF EXISTS `lams_qb_question_unit`; + +CREATE TABLE lams_qb_question_unit ( + `uid` BIGINT AUTO_INCREMENT, + `qb_question_uid` BIGINT NOT NULL, + `display_order` TINYINT NOT NULL DEFAULT 1, + `multiplier` float DEFAULT 0, + `name` varchar(255), + PRIMARY KEY (uid), + CONSTRAINT FK_lams_qb_question_unit_1 FOREIGN KEY (qb_question_uid) REFERENCES lams_qb_question (uid) ON DELETE CASCADE ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- Table structure for table `lams_qb_tool_answer` +-- + +DROP TABLE IF EXISTS `lams_qb_tool_answer`; + +CREATE TABLE lams_qb_tool_answer ( + `answer_uid` BIGINT AUTO_INCREMENT, + `tool_question_uid` BIGINT NOT NULL, + `qb_option_uid` BIGINT DEFAULT NULL, + `answer` MEDIUMTEXT, + PRIMARY KEY (answer_uid), + CONSTRAINT FK_lams_qb_tool_answer_1 FOREIGN KEY (tool_question_uid) REFERENCES lams_qb_tool_question (tool_question_uid) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_qb_tool_answer_2 FOREIGN KEY (qb_option_uid) REFERENCES lams_qb_option (uid) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- Table structure for table `lams_sequence_generator` +-- + +DROP TABLE IF EXISTS `lams_sequence_generator`; + +CREATE TABLE lams_sequence_generator ( + lams_qb_question_question_id INT +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +CREATE UNIQUE INDEX IDX_lams_qb_question_question_id ON lams_sequence_generator(lams_qb_question_question_id); + + +-- +-- Table structure for table `lams_qb_collection` +-- + +DROP TABLE IF EXISTS `lams_qb_collection`; + +CREATE TABLE lams_qb_collection ( + `uid` BIGINT AUTO_INCREMENT, + `name` VARCHAR(255), + `user_id` BIGINT, + `personal` TINYINT(1) NOT NULL DEFAULT 0, + PRIMARY KEY (uid), + INDEX (personal), + CONSTRAINT FK_lams_qb_collection_1 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- Table structure for table `lams_qb_collection_question` +-- + +DROP TABLE IF EXISTS `lams_qb_collection_question`; + +CREATE TABLE lams_qb_collection_question ( + `collection_uid` BIGINT NOT NULL, + `qb_question_id` INT NOT NULL, + CONSTRAINT FK_lams_qb_collection_question_1 FOREIGN KEY (collection_uid) REFERENCES lams_qb_collection (uid) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_qb_collection_question_2 FOREIGN KEY (qb_question_id) REFERENCES lams_qb_question (question_id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- Table structure for table `lams_qb_collection_organisation` +-- + +DROP TABLE IF EXISTS `lams_qb_collection_organisation`; + +CREATE TABLE lams_qb_collection_organisation ( + `collection_uid` BIGINT NOT NULL, + `organisation_id` BIGINT NOT NULL, + CONSTRAINT FK_lams_qb_collection_share_1 FOREIGN KEY (collection_uid) REFERENCES lams_qb_collection (uid) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_qb_collection_share_2 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- Table structure for table `lams_user_organisation_collapsed` +-- + +DROP TABLE IF EXISTS `lams_user_organisation_collapsed`; + +CREATE TABLE `lams_user_organisation_collapsed` ( + `uid` bigint(20) NOT NULL AUTO_INCREMENT, + `organisation_id` bigint(20) NOT NULL, + `user_id` bigint(20) NOT NULL, + `collapsed` TINYINT(1) NOT NULL DEFAULT 0, + PRIMARY KEY (`uid`), + KEY `organisation_id` (`organisation_id`), + KEY `user_id` (`user_id`), + CONSTRAINT `FK_lams_user_organisation_collapsed_1` FOREIGN KEY (`organisation_id`) REFERENCES `lams_organisation` (`organisation_id`), + CONSTRAINT `FK_lams_user_organisation_collapsed_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Index: lams_common/db/sql/insert_lams_config_data.sql =================================================================== diff -u -rf9d8fc434a8b7629a1353f532eb15e5c4f5f3cac -r2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe --- lams_common/db/sql/insert_lams_config_data.sql (.../insert_lams_config_data.sql) (revision f9d8fc434a8b7629a1353f532eb15e5c4f5f3cac) +++ lams_common/db/sql/insert_lams_config_data.sql (.../insert_lams_config_data.sql) (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -6,7 +6,21 @@ -- LOCK TABLES `lams_configuration` WRITE; -INSERT INTO `lams_configuration` VALUES ('AdminScreenSize','1280x720','config.admin.screen.size','config.header.look.feel','STRING',1),('AllowDirectAccessIntgrtnLrnr','false','config.allow.direct.access.for.integration.learners','config.header.features','BOOLEAN',1),('AllowDirectLessonLaunch','false','config.allow.direct.lesson.launch','config.header.features','BOOLEAN',1),('AllowKumalive','true','config.allow.kumalive','config.header.features','BOOLEAN',1),('AllowLiveEdit','true','config.allow.live.edit','config.header.features','BOOLEAN',1),('AuthoringScreenSize','1280x720','config.authoring.screen.size','config.header.look.feel','STRING',1),('CleanupPreviewOlderThanDays','7','config.cleanup.preview.older.than.days','config.header.system','LONG',1),('ConfigCacheRefreshInterval','0','config.cache.refresh','config.header.system','LONG',0),('ContentRepositoryPath','@contentrepository.directory@','config.content.repository.path','config.header.uploads','STRING',1),('CustomTabLink','','config.custom.tab.link','config.header.look.feel','STRING',0),('CustomTabTitle','','config.custom.tab.title','config.header.look.feel','STRING',0),('DefaultTheme','defaultHTML','config.default.html.theme','config.header.look.feel','STRING',1),('DictionaryDateCreated','2018-12-28','config.dictionary.date.created','config.header.versions','STRING',1),('DisplayPortrait','true','config.display.portrait','config.header.look.feel','BOOLEAN',0),('DisplayPrintButton','false','config.display.print.button','config.header.features','BOOLEAN',1),('EARDir','@ear.directory@','config.ear.dir','config.header.system','STRING',1),('EnableForgotYourPasswordLink','true','config.enable.forgot.your.password.link','config.header.features','BOOLEAN',0),('EnablePortraitEditing','true','config.enable.portrait.editing','config.header.features','BOOLEAN',0),('EnableServerRegistration','false','config.server2server.registration.enable','config.header.system','BOOLEAN',1),('ErrorStackTrace','true','config.stacktrace.error','config.header.system','BOOLEAN',0),('ExecutableExtensions','.bat,.bin,.com,.cmd,.exe,.msi,.msp,.ocx,.pif,.scr,.sct,.sh,.shs,.vbs,.php,.jsp,.asp,.aspx,.pl,.do,.py,.tcl,.cgi,.shtml,.stm,.cfm,.adp','config.executable.extensions','config.header.uploads','STRING',1),('FailedAttempts','3','config.failed.attempts','config.header.password.policy','LONG',1),('HelpURL','http://wiki.lamsfoundation.org/display/lamsdocs/','config.help.url','config.header.system','STRING',1),('LamsSupportEmail','','config.lams.support.email','config.header.email','STRING',0),('LDAPAddr1Attr','postalAddress','admin.user.address_line_1','config.header.ldap.attributes','STRING',0),('LDAPAddr2Attr','','admin.user.address_line_2','config.header.ldap.attributes','STRING',0),('LDAPAddr3Attr','','admin.user.address_line_3','config.header.ldap.attributes','STRING',0),('LDAPAuthorMap','Teacher;SeniorStaff;Principal','config.ldap.author.map','config.header.ldap.attributes','STRING',0),('LDAPBaseDN','ou=Users,dc=melcoe,dc=mq,dc=edu,dc=au','config.ldap.base.dn','config.header.ldap','STRING',0),('LDAPBindUserDN','','config.ldap.bind.user.dn','config.header.ldap','STRING',0),('LDAPBindUserPassword','','config.ldap.bind.user.password','config.header.ldap','STRING',0),('LDAPCityAttr','l','admin.user.city','config.header.ldap.attributes','STRING',0),('LDAPCountryAttr','','admin.user.country','config.header.ldap.attributes','STRING',0),('LDAPDayPhoneAttr','telephoneNumber','admin.user.day_phone','config.header.ldap.attributes','STRING',0),('LDAPDisabledAttr','!accountStatus','sysadmin.disabled','config.header.ldap.attributes','STRING',0),('LDAPEmailAttr','mail','admin.user.email','config.header.ldap.attributes','STRING',0),('LDAPEveningPhoneAttr','homePhone','admin.user.evening_phone','config.header.ldap.attributes','STRING',0),('LDAPFaxAttr','facsimileTelephoneNumber','admin.user.fax','config.header.ldap.attributes','STRING',0),('LDAPFNameAttr','givenName','admin.user.first_name','config.header.ldap.attributes','STRING',0),('LDAPGroupManagerMap','Principal;Teacher;SeniorStaff','config.ldap.group.manager.map','config.header.ldap.attributes','STRING',0),('LDAPLearnerMap','Student;SchoolSupportStaff;Teacher;SeniorStaff;Principal','config.ldap.learner.map','config.header.ldap.attributes','STRING',0),('LDAPLNameAttr','sn','admin.user.last_name','config.header.ldap.attributes','STRING',0),('LDAPLocaleAttr','preferredLanguage','admin.organisation.locale','config.header.ldap.attributes','STRING',0),('LDAPLoginAttr','uid','admin.user.login','config.header.ldap.attributes','STRING',0),('LDAPMobileAttr','mobile','admin.user.mobile_phone','config.header.ldap.attributes','STRING',0),('LDAPMonitorMap','SchoolSupportStaff;Teacher;SeniorStaff;Principal','config.ldap.monitor.map','config.header.ldap.attributes','STRING',0),('LDAPOnlyOneOrg','true','config.ldap.only.one.org','config.header.ldap','BOOLEAN',1),('LDAPOrgAttr','schoolCode','admin.course','config.header.ldap.attributes','STRING',0),('LDAPOrgField','code','config.ldap.org.field','config.header.ldap.attributes','STRING',0),('LDAPPostcodeAttr','postalCode','admin.user.postcode','config.header.ldap.attributes','STRING',0),('LDAPProviderURL','ldap://192.168.111.15','config.ldap.provider.url','config.header.ldap','STRING',0),('LDAPProvisioningEnabled','false','config.ldap.provisioning.enabled','config.header.ldap','BOOLEAN',1),('LDAPRolesAttr','memberOf','admin.user.roles','config.header.ldap.attributes','STRING',0),('LDAPSearchFilter','(cn={0})','config.ldap.search.filter','config.header.ldap','STRING',0),('LDAPSearchResultsPageSize','100','config.ldap.search.results.page.size','config.header.ldap','LONG',0),('LDAPSecurityAuthentication','simple','config.ldap.security.authentication','config.header.ldap','STRING',0),('LDAPSecurityProtocol','','config.ldap.security.protocol','config.header.ldap','STRING',0),('LDAPStateAttr','st','admin.user.state','config.header.ldap.attributes','STRING',0),('LDAPUpdateOnLogin','true','config.ldap.update.on.login','config.header.ldap','BOOLEAN',1),('LearnerScreenSize','1280x720','config.learner.screen.size','config.header.look.feel','STRING',1),('LockOutTime','5','config.lock.out.time','config.header.password.policy','LONG',1),('MonitorScreenSize','1280x720','config.monitor.screen.size','config.header.look.feel','STRING',1),('PasswordPolicyLowercase','true','config.password.lowercase','config.header.password.policy','BOOLEAN',0),('PasswordPolicyMinChars','8','config.password.minimum.characters','config.header.password.policy','LONG',1),('PasswordPolicyNumerics','true','config.password.numerics','config.header.password.policy','BOOLEAN',0),('PasswordPolicySymbols','false','config.password.symbols','config.header.password.policy','BOOLEAN',0),('PasswordPolicyUppercase','true','config.password.uppercase','config.header.password.policy','BOOLEAN',0),('ProfileEditEnable','true','config.profile.edit.enable','config.header.features','BOOLEAN',1),('ProfilePartialEditEnable','true','config.profile.partial.edit.enable','config.header.features','BOOLEAN',1),('ServerCountry','AU','config.server.country','config.header.look.feel','STRING',1),('ServerLanguage','en_AU','config.server.language','config.header.look.feel','STRING',1),('ServerPageDirection','LTR','config.server.page.direction','config.header.look.feel','STRING',1),('ServerURL','http://localhost:8080/lams/','config.server.url','config.header.system','STRING',1),('ServerURLContextPath','lams/','config.server.url.context.path','config.header.system','STRING',1),('ServerVersionNumber','3.1','config.server.version.number','config.header.versions','STRING',1),('ShowAllMyLessonLink','true','config.show.all.my.lesson.link','config.header.features','BOOLEAN',1),('ShowTimezoneWarning','true','config.show.timezone.warning','config.header.features','BOOLEAN',1),('SiteName','LAMS','config.site.name','config.header.system','STRING',1),('SMTPAuthSecurity','none','config.smtp.auth.security','config.header.email','STRING',1),('SMTPPassword','','config.smtp.password','config.header.email','STRING',0),('SMTPPort','25','config.smtp.port','config.header.email','LONG',0),('SMTPServer','','config.smtp.server','config.header.email','STRING',0),('SMTPUser','','config.smtp.user','config.header.email','STRING',0),('SuffixImportedLD','false','config.authoring.suffix','config.header.features','BOOLEAN',0),('TempDir','@temp.directory@','config.temp.dir','config.header.system','STRING',1),('UploadFileMaxMemorySize','4096','config.upload.file.max.memory.size','config.header.uploads','LONG',1),('UploadFileMaxSize','10485760','config.upload.file.max.size','config.header.uploads','LONG',1),('UploadLargeFileMaxSize','104857600','config.upload.large.file.max.size','config.header.uploads','LONG',1),('UserInactiveTimeout','10800','config.user.inactive.timeout','config.header.system','LONG',1),('UserValidationEmail','true','config.user.validation.emails','config.header.user.validation','BOOLEAN',0),('UserValidationFirstLastName','true','config.user.validation.first.last.name','config.header.user.validation','BOOLEAN',0),('UserValidationUsername','true','config.user.validation.username','config.header.user.validation','BOOLEAN',0),('Version','3.1','config.version','config.header.system','STRING',1); +INSERT INTO `lams_configuration` VALUES ('AdminScreenSize','1280x720','config.admin.screen.size','config.header.look.feel','STRING',1),('AllowDirectAccessIntgrtnLrnr','false','config.allow.direct.access.for.integration.learners','config.header.features','BOOLEAN',1),('AllowDirectLessonLaunch','false','config.allow.direct.lesson.launch','config.header.features','BOOLEAN',1),('AllowKumalive','true','config.allow.kumalive','config.header.features','BOOLEAN',1),('AllowLiveEdit','true','config.allow.live.edit','config.header.features','BOOLEAN',1),('AuthoringScreenSize','1280x720','config.authoring.screen.size','config.header.look.feel','STRING',1),('CleanupPreviewOlderThanDays','7','config.cleanup.preview.older.than.days','config.header.system','LONG',1),('ConfigCacheRefreshInterval','0','config.cache.refresh','config.header.system','LONG',0),('ContentRepositoryPath','@contentrepository.directory@','config.content.repository.path','config.header.uploads','STRING',1),('CustomTabLink','','config.custom.tab.link','config.header.look.feel','STRING',0),('CustomTabTitle','','config.custom.tab.title','config.header.look.feel','STRING',0),('DefaultTheme','defaultHTML','config.default.html.theme','config.header.look.feel','STRING',1),('DictionaryDateCreated','2018-12-28','config.dictionary.date.created','config.header.versions','STRING',1),('DisplayPortrait','true','config.display.portrait','config.header.look.feel','BOOLEAN',0),('DisplayPrintButton','false','config.display.print.button','config.header.features','BOOLEAN',1),('EARDir','@ear.directory@','config.ear.dir','config.header.system','STRING',1),('EnableForgotYourPasswordLink','true','config.enable.forgot.your.password.link','config.header.features','BOOLEAN',0),('EnablePortraitEditing','true','config.enable.portrait.editing','config.header.features','BOOLEAN',0),('EnableServerRegistration','false','config.server2server.registration.enable','config.header.system','BOOLEAN',1),('ErrorStackTrace','true','config.stacktrace.error','config.header.system','BOOLEAN',0),('ExecutableExtensions','.bat,.bin,.com,.cmd,.exe,.msi,.msp,.ocx,.pif,.scr,.sct,.sh,.shs,.vbs,.php,.jsp,.asp,.aspx,.pl,.do,.py,.tcl,.cgi,.shtml,.stm,.cfm,.adp','config.executable.extensions','config.header.uploads','STRING',1),('FailedAttempts','3','config.failed.attempts','config.header.password.policy','LONG',1),('HelpURL','http://wiki.lamsfoundation.org/display/lamsdocs/','config.help.url','config.header.system','STRING',1),('LamsSupportEmail','','config.lams.support.email','config.header.email','STRING',0),('LDAPAddr1Attr','postalAddress','admin.user.address_line_1','config.header.ldap.attributes','STRING',0),('LDAPAddr2Attr','','admin.user.address_line_2','config.header.ldap.attributes','STRING',0),('LDAPAddr3Attr','','admin.user.address_line_3','config.header.ldap.attributes','STRING',0),('LDAPAuthorMap','Teacher;SeniorStaff;Principal','config.ldap.author.map','config.header.ldap.attributes','STRING',0),('LDAPBaseDN','ou=Users,dc=melcoe,dc=mq,dc=edu,dc=au','config.ldap.base.dn','config.header.ldap','STRING',0),('LDAPBindUserDN','','config.ldap.bind.user.dn','config.header.ldap','STRING',0),('LDAPBindUserPassword','','config.ldap.bind.user.password','config.header.ldap','STRING',0),('LDAPCityAttr','l','admin.user.city','config.header.ldap.attributes','STRING',0),('LDAPCountryAttr','','admin.user.country','config.header.ldap.attributes','STRING',0),('LDAPDayPhoneAttr','telephoneNumber','admin.user.day_phone','config.header.ldap.attributes','STRING',0),('LDAPDisabledAttr','!accountStatus','sysadmin.disabled','config.header.ldap.attributes','STRING',0),('LDAPEmailAttr','mail','admin.user.email','config.header.ldap.attributes','STRING',0),('LDAPEveningPhoneAttr','homePhone','admin.user.evening_phone','config.header.ldap.attributes','STRING',0),('LDAPFaxAttr','facsimileTelephoneNumber','admin.user.fax','config.header.ldap.attributes','STRING',0),('LDAPFNameAttr','givenName','admin.user.first_name','config.header.ldap.attributes','STRING',0),('LDAPGroupManagerMap','Principal;Teacher;SeniorStaff','config.ldap.group.manager.map','config.header.ldap.attributes','STRING',0),('LDAPLearnerMap','Student;SchoolSupportStaff;Teacher;SeniorStaff;Principal','config.ldap.learner.map','config.header.ldap.attributes','STRING',0),('LDAPLNameAttr','sn','admin.user.last_name','config.header.ldap.attributes','STRING',0),('LDAPLocaleAttr','preferredLanguage','admin.organisation.locale','config.header.ldap.attributes','STRING',0),('LDAPLoginAttr','uid','admin.user.login','config.header.ldap.attributes','STRING',0),('LDAPMobileAttr','mobile','admin.user.mobile_phone','config.header.ldap.attributes','STRING',0),('LDAPMonitorMap','SchoolSupportStaff;Teacher;SeniorStaff;Principal','config.ldap.monitor.map','config.header.ldap.attributes','STRING',0),('LDAPOnlyOneOrg','true','config.ldap.only.one.org','config.header.ldap','BOOLEAN',1),('LDAPOrgAttr','schoolCode','admin.course','config.header.ldap.attributes','STRING',0),('LDAPOrgField','code','config.ldap.org.field','config.header.ldap.attributes','STRING',0),('LDAPPostcodeAttr','postalCode','admin.user.postcode','config.header.ldap.attributes','STRING',0),('LDAPProviderURL','ldap://192.168.111.15','config.ldap.provider.url','config.header.ldap','STRING',0),('LDAPProvisioningEnabled','false','config.ldap.provisioning.enabled','config.header.ldap','BOOLEAN',1),('LDAPRolesAttr','memberOf','admin.user.roles','config.header.ldap.attributes','STRING',0),('LDAPSearchFilter','(cn={0})','config.ldap.search.filter','config.header.ldap','STRING',0),('LDAPSearchResultsPageSize','100','config.ldap.search.results.page.size','config.header.ldap','LONG',0),('LDAPSecurityAuthentication','simple','config.ldap.security.authentication','config.header.ldap','STRING',0),('LDAPSecurityProtocol','','config.ldap.security.protocol','config.header.ldap','STRING',0),('LDAPStateAttr','st','admin.user.state','config.header.ldap.attributes','STRING',0),('LDAPUpdateOnLogin','true','config.ldap.update.on.login','config.header.ldap','BOOLEAN',1),('LearnerScreenSize','1280x720','config.learner.screen.size','config.header.look.feel','STRING',1),('LockOutTime','5','config.lock.out.time','config.header.password.policy','LONG',1),('MonitorScreenSize','1280x720','config.monitor.screen.size','config.header.look.feel','STRING',1),('PasswordPolicyLowercase','true','config.password.lowercase','config.header.password.policy','BOOLEAN',0),('PasswordPolicyMinChars','8','config.password.minimum.characters','config.header.password.policy','LONG',1),('PasswordPolicyNumerics','true','config.password.numerics','config.header.password.policy','BOOLEAN',0),('PasswordPolicySymbols','false','config.password.symbols','config.header.password.policy','BOOLEAN',0),('PasswordPolicyUppercase','true','config.password.uppercase','config.header.password.policy','BOOLEAN',0),('ProfileEditEnable','true','config.profile.edit.enable','config.header.features','BOOLEAN',1),('ProfilePartialEditEnable','true','config.profile.partial.edit.enable','config.header.features','BOOLEAN',1),('ServerCountry','AU','config.server.country','config.header.look.feel','STRING',1),('ServerLanguage','en_AU','config.server.language','config.header.look.feel','STRING',1),('ServerPageDirection','LTR','config.server.page.direction','config.header.look.feel','STRING',1),('ServerURL','http://localhost:8080/lams/','config.server.url','config.header.system','STRING',1),('ServerURLContextPath','lams/','config.server.url.context.path','config.header.system','STRING',1),('ServerVersionNumber','3.1','config.server.version.number','config.header.versions','STRING',1),('ShowAllMyLessonLink','true','config.show.all.my.lesson.link','config.header.features','BOOLEAN',1),('ShowTimezoneWarning','true','config.show.timezone.warning','config.header.features','BOOLEAN',1),('SiteName','LAMS','config.site.name','config.header.system','STRING',1),('SMTPAuthSecurity','none','config.smtp.auth.security','config.header.email','STRING',1),('SMTPPassword','','config.smtp.password','config.header.email','STRING',0),('SMTPPort','25','config.smtp.port','config.header.email','LONG',0),('SMTPServer','','config.smtp.server','config.header.email','STRING',0),('SMTPUser','','config.smtp.user','config.header.email','STRING',0),('SuffixImportedLD','false','config.authoring.suffix','config.header.features','BOOLEAN',0),('TempDir','@temp.directory@','config.temp.dir','config.header.system','STRING',1),('UploadFileMaxMemorySize','4096','config.upload.file.max.memory.size','config.header.uploads','LONG',1),('UploadFileMaxSize','10485760','config.upload.file.max.size','config.header.uploads','LONG',1),('UploadLargeFileMaxSize','104857600','config.upload.large.file.max.size','config.header.uploads','LONG',1),('UserInactiveTimeout','10800','config.user.inactive.timeout','config.header.system','LONG',1),('UserValidationEmail','true','config.user.validation.emails','config.header.user.validation','BOOLEAN',0),('UserValidationFirstLastName','true','config.user.validation.first.last.name','config.header.user.validation','BOOLEAN',0),('UserValidationUsername','true','config.user.validation.username','config.header.user.validation','BOOLEAN',0),('Version','3.1','config.version','config.header.system','STRING',1), +('AntivirusEnable', 'false', 'config.av.enable', 'config.header.antivirus', 'BOOLEAN', 1), +('AntivirusHost', 'localhost', 'config.av.host', 'config.header.antivirus', 'STRING', 0), +('AntivirusPort', '3310', 'config.av.port', 'config.header.antivirus', 'LONG', 0), +('QbQtiEnable', 'true', 'config.qb.qti.enable', 'config.header.qb', 'BOOLEAN', 1), +('QbWordEnable', 'true', 'config.qb.word.enable', 'config.header.qb', 'BOOLEAN', 1), +('QbCollectionsCreateEnable', 'true', 'config.qb.collections.create.enable', 'config.header.qb', 'BOOLEAN', 1), +('QbMonitorsReadOnly', 'false', 'config.qb.monitors.read.only', 'config.header.qb', 'BOOLEAN', 1), +('QbStatsMinParticipants', '2', 'config.qb.stats.min.participants', 'config.header.qb', 'LONG', 1), +('QbStatsGroupSize', '27', 'config.qb.stats.group.size', 'config.header.qb', 'LONG', 1), +('QbMergeEnable', 'true', 'config.qb.merge.enable', 'config.header.qb', 'BOOLEAN', 1), +('QbCollectionsTransferEnable', 'true', 'config.qb.collections.transfer.enable', 'config.header.qb', 'BOOLEAN', 1), +('RestrictedGroupUserNames','true', 'config.restricted.displaying.user.names.in.groupings', 'config.header.privacy.settings', 'BOOLEAN', 0), +('EnableCollapsingSubcourses','false', 'config.enable.collapsing.subcourses', 'config.header.features', 'BOOLEAN', 0), +('LearningOutcomeQuickAddEnable', 'true', 'config.learning.outcome.add.enable', 'config.header.features', 'BOOLEAN', 1); UNLOCK TABLES; SET FOREIGN_KEY_CHECKS=1; Index: lams_common/db/sql/insert_types_data.sql =================================================================== diff -u -rf9d8fc434a8b7629a1353f532eb15e5c4f5f3cac -r2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe --- lams_common/db/sql/insert_types_data.sql (.../insert_types_data.sql) (revision f9d8fc434a8b7629a1353f532eb15e5c4f5f3cac) +++ lams_common/db/sql/insert_types_data.sql (.../insert_types_data.sql) (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -38,7 +38,7 @@ -- LOCK TABLES `lams_ext_server_org_map` WRITE; -INSERT INTO `lams_ext_server_org_map` VALUES (1,'moodle','moodle','moodle','moodle','mdl','http://localhost/moodle/mod/lamstwo/userinfo.php?ts=%timestamp%&un=%username%&hs=%hash%','',0x01,80,0,NULL,1,NULL,0,0,1,1,0,0,1); +INSERT INTO `lams_ext_server_org_map` VALUES (1,'moodle','moodle','moodle','moodle','mdl','http://localhost/moodle/mod/lamstwo/userinfo.php?ts=%timestamp%&un=%username%&hs=%hash%','',NULL,0x01,80,0,NULL,1,NULL,0,0,1,1,0,0,1,0,NULL); UNLOCK TABLES; -- @@ -102,7 +102,11 @@ -- LOCK TABLES `lams_log_event_type` WRITE; -INSERT INTO `lams_log_event_type` VALUES (1,'TYPE_TEACHER_LEARNING_DESIGN_CREATE','LESSON'),(2,'TYPE_TEACHER_LESSON_CREATE','LESSON'),(3,'TYPE_TEACHER_LESSON_START','LESSON'),(4,'TYPE_TEACHER_LESSON_CHANGE_STATE','LESSON'),(5,'TYPE_LEARNER_ACTIVITY_START','LESSON'),(6,'TYPE_LEARNER_ACTIVITY_FINISH','LESSON'),(7,'TYPE_LEARNER_LESSON_COMPLETE','LESSON'),(8,'TYPE_LEARNER_LESSON_MARK_SUBMIT','LESSON'),(9,'TYPE_ACTIVITY_EDIT','LESSON'),(10,'TYPE_FORCE_COMPLETE','LESSON'),(11,'TYPE_USER_ORG_ADMIN','SECURITY'),(12,'TYPE_LOGIN_AS','SECURITY'),(13,'TYPE_PASSWORD_CHANGE','SECURITY'),(14,'TYPE_ROLE_FAILURE','SECURITY'),(15,'TYPE_ACCOUNT_LOCKED','SECURITY'),(16,'TYPE_NOTIFICATION','NOTIFICATION'),(17,'TYPE_MARK_UPDATED','MARKS'),(18,'TYPE_MARK_RELEASED','MARKS'),(19,'TYPE_LEARNER_CONTENT_UPDATED','LEARNER_CONTENT'),(20,'TYPE_LEARNER_CONTENT_SHOW_HIDE','LEARNER_CONTENT'),(21,'TYPE_UNKNOWN','UNKNOWN'),(22,'TYPE_LIVE_EDIT','LESSON'),(23,'TYPE_TOOL_MARK_RELEASED','MARKS'); +INSERT INTO `lams_log_event_type` VALUES (1,'TYPE_TEACHER_LEARNING_DESIGN_CREATE','LESSON'),(2,'TYPE_TEACHER_LESSON_CREATE','LESSON'),(3,'TYPE_TEACHER_LESSON_START','LESSON'),(4,'TYPE_TEACHER_LESSON_CHANGE_STATE','LESSON'),(5,'TYPE_LEARNER_ACTIVITY_START','LESSON'),(6,'TYPE_LEARNER_ACTIVITY_FINISH','LESSON'),(7,'TYPE_LEARNER_LESSON_COMPLETE','LESSON'),(8,'TYPE_LEARNER_LESSON_MARK_SUBMIT','LESSON'),(9,'TYPE_ACTIVITY_EDIT','LESSON'),(10,'TYPE_FORCE_COMPLETE','LESSON'),(11,'TYPE_USER_ORG_ADMIN','SECURITY'),(12,'TYPE_LOGIN_AS','SECURITY'),(13,'TYPE_PASSWORD_CHANGE','SECURITY'),(14,'TYPE_ROLE_FAILURE','SECURITY'),(15,'TYPE_ACCOUNT_LOCKED','SECURITY'),(16,'TYPE_NOTIFICATION','NOTIFICATION'),(17,'TYPE_MARK_UPDATED','MARKS'),(18,'TYPE_MARK_RELEASED','MARKS'),(19,'TYPE_LEARNER_CONTENT_UPDATED','LEARNER_CONTENT'),(20,'TYPE_LEARNER_CONTENT_SHOW_HIDE','LEARNER_CONTENT'),(21,'TYPE_UNKNOWN','UNKNOWN'),(22,'TYPE_LIVE_EDIT','LESSON'),(23,'TYPE_TOOL_MARK_RELEASED','MARKS'), +(24, 'TYPE_LOGIN', 'SECURITY'), +(25, 'TYPE_LOGOUT', 'SECURITY'), +(26, 'TYPE_CONFIG_CHANGE', 'SECURITY'), +(27, 'TYPE_QUESTIONS_MERGED', 'QUESTION_BANK'); UNLOCK TABLES; @@ -128,7 +132,7 @@ -- LOCK TABLES `lams_outcome_scale` WRITE; -INSERT INTO `lams_outcome_scale` VALUES (1,NULL,'Default attainment scale','default','Default global scale',NULL,1,NOW()); +INSERT INTO `lams_outcome_scale` VALUES (1,'Default attainment scale','default','Default global scale',1,NOW()); UNLOCK TABLES; -- @@ -229,7 +233,15 @@ -- LOCK TABLES `patches` WRITE; -INSERT INTO patches VALUES ('lams', 20190103, NOW(), 'F'); +INSERT INTO patches VALUES ('lams', 20200219, NOW(), 'F'); UNLOCK TABLES; +-- +-- Dumping data for table `lams_qb_collection` +-- + +LOCK TABLES `lams_qb_collection` WRITE; +INSERT INTO `lams_qb_collection` VALUES (1, 'Public questions', NULL, false); +UNLOCK TABLES; + SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190119.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190126.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190201.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190226.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190313.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190415.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190429.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190520.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190721.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190722.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190723.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190724.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190725.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190806.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190808.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190816.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190913.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20191009.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20191010.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20191023.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20191122.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20191202.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20200116.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20200129.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20200204.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20200219_updateTo40.sql =================================================================== diff -u --- lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20200219_updateTo40.sql (revision 0) +++ lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20200219_updateTo40.sql (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -0,0 +1,1037 @@ +SET AUTOCOMMIT = 0; +SET FOREIGN_KEY_CHECKS = 0; + +-- This patch contains files patch20190119.sql to patch20200204.sql +-- It should upgrade this tool to version 4.0 + +-- LDEV-4755 Add configuratio settings for ClamAV antivirus +INSERT INTO lams_configuration VALUES +('AntivirusEnable', 'false', 'config.av.enable', 'config.header.antivirus', 'BOOLEAN', 1), +('AntivirusHost', 'localhost', 'config.av.host', 'config.header.antivirus', 'STRING', 0), +('AntivirusPort', '3310', 'config.av.port', 'config.header.antivirus', 'LONG', 0); + +-- LDEV-4587 Direction (asc/desc) of ordered branching. NULL means it is non-ordered tool-based branching +ALTER TABLE lams_learning_activity +ADD COLUMN branching_ordered_asc TINYINT(1); + +-- LDEV-4767 Add new event types for log in and log out + +INSERT INTO lams_log_event_type VALUES (24, 'TYPE_LOGIN', 'SECURITY'), + (25, 'TYPE_LOGOUT', 'SECURITY'); + +-- LDEV-4778 Add new event type for sysadmin configuration change + +INSERT INTO lams_log_event_type VALUES (26, 'TYPE_CONFIG_CHANGE', 'SECURITY'); + +-- LDEV-4788 Remove reference to organisation in learning outcomes. They are all global now. + +ALTER TABLE lams_outcome_scale DROP FOREIGN KEY FK_lams_outcome_scale_1, + DROP KEY code_2, + DROP COLUMN organisation_id, + DROP COLUMN content_folder_id; + +ALTER TABLE lams_outcome DROP FOREIGN KEY FK_lams_outcome_1, + DROP KEY code_2, + DROP COLUMN organisation_id, + DROP COLUMN content_folder_id; + +-- SP-2 Add logout URL to integrated servers + +ALTER TABLE lams_ext_server_org_map +ADD COLUMN logout_url TEXT AFTER lesson_finish_url; + +-- LDEV-4813 Add an index to process event log faster + +ALTER TABLE lams_log_event ADD KEY event_log_date_and_type (occurred_date_time, log_event_type_id); + +-- LDEV-4819 Add configuration setting for disabling Learning Outcome quick add by authors +INSERT INTO lams_configuration VALUES +('LearningOutcomeQuickAddEnable', 'true', 'config.learning.outcome.add.enable', 'config.header.features', 'BOOLEAN', 1); + + + + + +-- LDEV-4746 Create Question Bank table structure and migrate existing data + +-- AUTO COMMIT stays ON because there is so many ALTER TABLE statements which are committed immediately anyway, that it does not make a difference + +-- Create QB question table + +CREATE TABLE lams_qb_question (`uid` BIGINT AUTO_INCREMENT, + `uuid` BINARY(16) NOT NULL, + `type` TINYINT NOT NULL, + `question_id` INT NOT NULL, + `version` SMALLINT NOT NULL DEFAULT 1, + `create_date` DATETIME NOT NULL DEFAULT NOW(), + `content_folder_id` char(36), + `name` TEXT, + `description` MEDIUMTEXT, + `max_mark` INT, + `feedback` TEXT, + `penalty_factor` float DEFAULT 0, + `answer_required` TINYINT(1) DEFAULT 0, + `multiple_answers_allowed` TINYINT(1) DEFAULT 0, + `incorrect_answer_nullifies_mark` TINYINT(1) DEFAULT 0, + `feedback_on_correct` TEXT, + `feedback_on_partially_correct` TEXT, + `feedback_on_incorrect` TEXT, + `shuffle` TINYINT(1) DEFAULT 0, + `prefix_answers_with_letters` TINYINT(1) DEFAULT 0, + `case_sensitive` TINYINT(1) DEFAULT 0, + `correct_answer` TINYINT(1) DEFAULT 0, + `allow_rich_editor` TINYINT(1) DEFAULT 0, + `max_words_limit` int(11) DEFAULT 0, + `min_words_limit` int(11) DEFAULT 0, + `hedging_justification_enabled` TINYINT(1) DEFAULT 0, + `tmp_question_id` BIGINT, + PRIMARY KEY (uid), + INDEX (tmp_question_id), + CONSTRAINT UQ_question_version UNIQUE INDEX (question_id, version)); + +-- Create a trigger to run before insert to generate the UUID for the uuid column +CREATE TRIGGER before_insert_qb_question + BEFORE INSERT ON lams_qb_question + FOR EACH ROW + SET new.uuid = UUID_TO_BIN(UUID()); + +-- Create a question table from which tools' questions will inherit +CREATE TABLE lams_qb_tool_question (`tool_question_uid` BIGINT AUTO_INCREMENT, + `qb_question_uid` BIGINT NOT NULL, + `tool_content_id` BIGINT NOT NULL, + `display_order` TINYINT NOT NULL DEFAULT 1, + PRIMARY KEY (tool_question_uid), + INDEX (tool_content_id), + CONSTRAINT FK_lams_qb_tool_question_1 FOREIGN KEY (qb_question_uid) REFERENCES lams_qb_question (uid) ON UPDATE CASCADE); +-- create Question Bank option +CREATE TABLE lams_qb_option (`uid` BIGINT AUTO_INCREMENT, + `qb_question_uid` BIGINT NOT NULL, + `display_order` TINYINT NOT NULL DEFAULT 1, + `name` TEXT, + `matching_pair` TEXT, + `numerical_option` float DEFAULT 0, + `max_mark` float DEFAULT 0, + `accepted_error` float DEFAULT 0, + `feedback` TEXT, + PRIMARY KEY (uid), + INDEX (display_order), + CONSTRAINT FK_lams_qb_option_1 FOREIGN KEY (qb_question_uid) REFERENCES lams_qb_question (uid) ON DELETE CASCADE ON UPDATE CASCADE); + +-- create Question Bank question unit (used by numerical type of questions only) +CREATE TABLE lams_qb_question_unit (`uid` BIGINT AUTO_INCREMENT, + `qb_question_uid` BIGINT NOT NULL, + `display_order` TINYINT NOT NULL DEFAULT 1, + `multiplier` float DEFAULT 0, + `name` varchar(255), + PRIMARY KEY (uid), + CONSTRAINT FK_lams_qb_question_unit_1 FOREIGN KEY (qb_question_uid) REFERENCES lams_qb_question (uid) ON DELETE CASCADE ON UPDATE CASCADE); + +-- create an answer table from which tools' answers will inherit +CREATE TABLE lams_qb_tool_answer (`answer_uid` BIGINT AUTO_INCREMENT, + `tool_question_uid` BIGINT NOT NULL, + `qb_option_uid` BIGINT DEFAULT NULL, + `answer` MEDIUMTEXT, + PRIMARY KEY (answer_uid), + CONSTRAINT FK_lams_qb_tool_answer_1 FOREIGN KEY (tool_question_uid) + REFERENCES lams_qb_tool_question (tool_question_uid) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_qb_tool_answer_2 FOREIGN KEY (qb_option_uid) + REFERENCES lams_qb_option (uid) ON DELETE CASCADE ON UPDATE CASCADE); + +-- Migrate MCQ question into Question Bank question +-- This part of patch should be in MCQ, but it must be run after lams_qb_question is created and it must not run in parallel with other tools' patches, +-- so the safest solution is to place it here + +-- default value for a concat result is 1024 characters, which can be too little for multiple concatenated answers +-- we choose a value big enough to accept anything +SET group_concat_max_len = 10000000; + + +-- MULTIPLE CHOICE (MCQ) + +-- remove characters that prevent detecting identical questions +UPDATE tl_lamc11_que_content SET `question` = TRIM(REPLACE(REPLACE(REPLACE(question, '> ', '>' ), '\r', '' ), '\n', '')); +UPDATE tl_lamc11_options_content SET `mc_que_option_text` = TRIM(REPLACE(REPLACE(REPLACE(mc_que_option_text, '> ', '>' ), '\r', '' ), '\n', '')); + +-- create a mapping of MCQ question UID -> its question text + all answers in a single column +-- if this column is not *exactly* as in an other row, it means it should be a separate question in QB +-- Also remove all whitespace just for less demanding matching +CREATE TABLE tmp_question (question_uid BIGINT PRIMARY KEY, + content MEDIUMTEXT) + AS SELECT q.uid AS question_uid, + REPLACE(REPLACE(REPLACE(strip_tags(GROUP_CONCAT(question, mc_que_option_text ORDER BY displayOrder), true) + COLLATE utf8mb4_0900_ai_ci, + ' ', ''), + '\t', ''), + ' ', '') AS content + FROM tl_lamc11_que_content AS q + JOIN tl_lamc11_options_content AS o ON q.uid = o.mc_que_content_id + GROUP BY q.uid; + +-- create a mapping of MCQ question UID -> UID of one of MCQ questions which holds the same content +CREATE TABLE tmp_question_match (question_uid BIGINT PRIMARY KEY, + target_uid BIGINT) + SELECT q.question_uid, merged.question_uid AS target_uid + FROM (SELECT * FROM tmp_question GROUP BY content) AS merged + JOIN tmp_question AS q USING (content) + GROUP BY q.question_uid; + +ALTER TABLE tmp_question_match ADD INDEX (target_uid); + + +-- fill Question Bank question table with unique questions, with manually incremented question ID +SET @question_id = (SELECT IF(MAX(question_id) IS NULL, 0, MAX(question_id)) FROM lams_qb_question); + +INSERT INTO lams_qb_question (uid, `type`, question_id, version, create_date, name, description, max_mark, feedback, tmp_question_id) + SELECT NULL, 1, @question_id:=@question_id + 1, 1, IFNULL(c.creation_date, NOW()), + SUBSTRING(TRIM(REPLACE(REPLACE(strip_tags(mcq.question, false) COLLATE utf8mb4_0900_ai_ci, ' ', ' '), '\t', '')), 1, 200), + mcq.question, IFNULL(mcq.max_mark, 1), mcq.feedback, q.target_uid + FROM (SELECT uid, + question AS question, + mark AS max_mark, + IF(TRIM(feedback) = '', NULL, TRIM(feedback)) AS feedback, + mc_content_id + FROM tl_lamc11_que_content) AS mcq + JOIN (SELECT DISTINCT target_uid FROM tmp_question_match) AS q + ON mcq.uid = q.target_uid + JOIN tl_lamc11_content AS c + ON mcq.mc_content_id = c.uid; + +-- set up references to QB question UIDs created above +INSERT INTO lams_qb_tool_question + SELECT q.question_uid, qb.uid, c.content_id, mcq.display_order + FROM lams_qb_question AS qb + JOIN tmp_question_match AS q + ON qb.tmp_question_id = q.target_uid + JOIN tl_lamc11_que_content AS mcq + ON q.question_uid = mcq.uid + JOIN tl_lamc11_content AS c + ON mcq.mc_content_id = c.uid; + +-- remove columns from MCQ which are duplicated in Question Bank +ALTER TABLE tl_lamc11_que_content DROP COLUMN question, + DROP COLUMN mark, + DROP COLUMN display_order, + DROP COLUMN feedback; + +-- fill table with options matching unique QB questions inserted above +INSERT INTO lams_qb_option (qb_question_uid, display_order, name, max_mark) + SELECT q.uid, o.displayOrder, o.mc_que_option_text, o.correct_option + FROM tl_lamc11_options_content AS o + JOIN lams_qb_question AS q + ON o.mc_que_content_id = q.tmp_question_id + ORDER BY o.mc_que_content_id, o.displayOrder; + +ALTER TABLE tl_lamc11_usr_attempt DROP FOREIGN KEY FK_tl_lamc11_usr_attempt_2, + DROP FOREIGN KEY FK_tl_lamc11_usr_attempt_3, + DROP INDEX attempt_unique_index; + +-- rewrite references from MCQ options to QB options +UPDATE tl_lamc11_usr_attempt AS ua, tl_lamc11_options_content AS mco, lams_qb_tool_question AS tq, lams_qb_option AS qo + SET ua.mc_que_option_id = qo.uid + WHERE mco.displayOrder = qo.display_order + AND ua.mc_que_option_id = mco.uid + AND qo.qb_question_uid = tq.qb_question_uid + AND mco.mc_que_content_id = tq.tool_question_uid; + +-- prepare for answer inheritance +INSERT INTO lams_qb_tool_answer + SELECT uid, mc_que_content_id, mc_que_option_id, NULL FROM tl_lamc11_usr_attempt; + +-- clean up +ALTER TABLE tl_lamc11_usr_attempt DROP COLUMN mc_que_content_id, + DROP COLUMN mc_que_option_id; + +DROP TABLE tl_lamc11_options_content; + +-- prepare for next tool migration, recreate tables +DROP TABLE tmp_question; +DROP TABLE tmp_question_match; + +CREATE TABLE tmp_question (question_uid BIGINT PRIMARY KEY, + content MEDIUMTEXT); + +CREATE TABLE tmp_question_match (question_uid BIGINT PRIMARY KEY, + target_uid BIGINT); +ALTER TABLE tmp_question_match ADD INDEX (target_uid); + + +-- SCRATCHIE + +-- delete corrupted data +DELETE FROM tl_lascrt11_scratchie_answer WHERE scratchie_item_uid IS NULL; + +-- shift Scratchie question UIDs by offset equal to existing UIDs of MCQ in lams_qb_tool_question +SET @max_tool_question_id = (SELECT MAX(tool_question_uid) FROM lams_qb_tool_question); +-- remove characters that prevent detecting identical questions +UPDATE tl_lascrt11_scratchie_item SET `title` = TRIM(REPLACE(REPLACE(REPLACE(title, '> ', '>' ), '\r', '' ), '\n', '')), + `description` = TRIM(REPLACE(REPLACE(REPLACE(description, '> ', '>' ), '\r', '' ), '\n', '')), + uid = uid + @max_tool_question_id ORDER BY uid DESC; +UPDATE tl_lascrt11_scratchie_answer SET `description` = TRIM(REPLACE(REPLACE(REPLACE(description, '> ', '>' ), '\r', '' ), '\n', '')); +-- UPDATE tl_lascrt11_scratchie_answer SET scratchie_item_uid = scratchie_item_uid + @max_tool_question_id ORDER BY scratchie_item_uid DESC; + +-- create a mapping of Scratchie question UID -> its question description + all answers in a single column +-- if this column is not *exactly* as in an other row, it means it should be a separate question in QB +INSERT INTO tmp_question + SELECT q.uid, + REPLACE(REPLACE(REPLACE(strip_tags(GROUP_CONCAT(q.description, o.description ORDER BY o.order_id), true) + COLLATE utf8mb4_0900_ai_ci, + ' ', ''), + '\t', ''), + ' ', '') + FROM tl_lascrt11_scratchie_item AS q + JOIN tl_lascrt11_scratchie_answer AS o + ON q.uid = o.scratchie_item_uid + GROUP BY q.uid; + +-- create a similar mapping for existing questions in QB +CREATE TABLE tmp_qb_question (question_uid BIGINT PRIMARY KEY, + content MEDIUMTEXT) + AS SELECT q.uid AS question_uid, + REPLACE(REPLACE(REPLACE(strip_tags(GROUP_CONCAT(q.description, o.name ORDER BY o.display_order), true) + COLLATE utf8mb4_0900_ai_ci, + ' ', ''), + '\t', ''), + ' ', '') AS content + FROM lams_qb_question AS q + JOIN lams_qb_option AS o + ON q.uid = o.qb_question_uid + GROUP BY q.uid; + +-- create a table which holds IDs of questions which are already in the question bank +CREATE TABLE tmp_qb_question_match (question_uid BIGINT PRIMARY KEY, qb_question_uid BIGINT) + AS SELECT q.question_uid, qb.question_uid AS qb_question_uid + FROM tmp_question AS q + JOIN tmp_qb_question AS qb + USING (content) + GROUP BY q.question_uid; + +ALTER TABLE tmp_qb_question_match ADD INDEX (qb_question_uid); + +-- create a mapping of Scratchie question UID -> UID of one of Scratchie questions which holds the same content +INSERT INTO tmp_question_match + SELECT q.question_uid, merged.question_uid + FROM (SELECT * FROM tmp_question GROUP BY content) AS merged + JOIN tmp_question AS q + USING (content) + LEFT JOIN tmp_qb_question_match AS qb + ON q.question_uid = qb.question_uid + WHERE qb.question_uid IS NULL + GROUP BY q.question_uid; + +-- reset column for matching QB questions with Scratchie questions +UPDATE lams_qb_question SET tmp_question_id = -1; + +-- fill Question Bank question table with unique questions, with manually incremented question ID +INSERT INTO lams_qb_question (uid, `type`, question_id, version, create_date, name, description, max_mark, feedback, tmp_question_id) + SELECT NULL, 1, @question_id:=@question_id + 1, 1, sq.create_date, + TRIM(REPLACE(REPLACE(strip_tags(sq.question, false) COLLATE utf8mb4_0900_ai_ci, ' ', ' '), '\t', '')), + TRIM(sq.description), NULL, NULL, q.target_uid + FROM (SELECT uid, + title AS question, + description, + create_date + FROM tl_lascrt11_scratchie_item) AS sq + JOIN (SELECT DISTINCT target_uid FROM tmp_question_match) AS q + ON sq.uid = q.target_uid; + +-- set up references to QB question UIDs created above +INSERT INTO lams_qb_tool_question + SELECT q.question_uid, qb.uid, s.content_id, sq.order_id + FROM lams_qb_question AS qb + JOIN tmp_question_match AS q + ON qb.tmp_question_id = q.target_uid + JOIN tl_lascrt11_scratchie_item AS sq + ON q.question_uid = sq.uid + JOIN tl_lascrt11_scratchie AS s + ON sq.scratchie_uid = s.uid; + +-- set up references to QB question UIDs for existing questions +INSERT INTO lams_qb_tool_question + SELECT qb.question_uid, qb.qb_question_uid, s.content_id, sq.order_id + FROM tmp_qb_question_match AS qb + JOIN tl_lascrt11_scratchie_item AS sq + ON qb.question_uid = sq.uid + JOIN tl_lascrt11_scratchie AS s + ON sq.scratchie_uid = s.uid; + +-- update question names generated for MCQ with real names from Scratchie +UPDATE lams_qb_question AS q + JOIN (SELECT * FROM tmp_qb_question_match GROUP BY qb_question_uid) AS m + ON q.uid = m.qb_question_uid + JOIN tl_lascrt11_scratchie_item AS si + ON si.uid = m.question_uid + SET q.name = TRIM(si.title); + +-- delete obsolete columns +ALTER TABLE tl_lascrt11_scratchie_item DROP FOREIGN KEY FK_NEW_610529188_F52D1F93EC0D3147, + DROP COLUMN title, + DROP COLUMN description, + DROP COLUMN create_date, + DROP COLUMN create_by_author, + DROP COLUMN session_uid, + DROP COLUMN order_id; + +-- some Scratchie options can be ordered from 0, some from 1 +-- shift ones ordered from 0 by +1 to match the other group +CREATE TABLE tmp_scratchie_answer +SELECT scratchie_item_uid FROM tl_lascrt11_scratchie_answer WHERE order_id = 0 AND scratchie_item_uid IS NOT NULL; + +ALTER TABLE tmp_scratchie_answer ADD PRIMARY KEY (scratchie_item_uid); + +UPDATE tl_lascrt11_scratchie_answer AS sa +SET order_id = order_id + 1 +WHERE EXISTS + (SELECT 1 FROM tmp_scratchie_answer WHERE scratchie_item_uid = sa.scratchie_item_uid); + +DROP TABLE tmp_scratchie_answer; + + +-- fill table with options matching unique QB questions inserted above +INSERT INTO lams_qb_option (qb_question_uid, display_order, name, max_mark) + SELECT q.uid, o.order_id, o.description, o.correct + FROM tl_lascrt11_scratchie_answer AS o + JOIN lams_qb_question AS q + ON o.scratchie_item_uid = q.tmp_question_id + ORDER BY o.scratchie_item_uid, o.order_id; + + +ALTER TABLE tl_lascrt11_answer_log ADD COLUMN scratchie_item_uid BIGINT; + +-- shift Scratchie answer UIDs by offset equal to existing UIDs of MCQ answers in lams_qb_tool_answer +SET @max_answer_uid = (SELECT MAX(answer_uid) FROM lams_qb_tool_answer); +UPDATE tl_lascrt11_answer_log SET uid = uid + @max_answer_uid ORDER BY uid DESC; + +ALTER TABLE tl_lascrt11_answer_log DROP FOREIGN KEY FK_NEW_610529188_693580A438BF8DFE, + DROP KEY FK_NEW_lascrt11_30113BFC309ED321; + +-- rewrite references from Scratchie options to QB options +UPDATE tl_lascrt11_answer_log AS sl, tl_lascrt11_scratchie_answer AS sa, lams_qb_tool_question AS tq, lams_qb_option AS qo + SET sl.scratchie_answer_uid = qo.uid, + sl.scratchie_item_uid = tq.tool_question_uid + WHERE sa.order_id = qo.display_order + AND sl.scratchie_answer_uid = sa.uid + AND qo.qb_question_uid = tq.qb_question_uid + AND sa.scratchie_item_uid = tq.tool_question_uid; + +-- prepare for answer inheritance +INSERT INTO lams_qb_tool_answer + SELECT uid, scratchie_item_uid, scratchie_answer_uid, NULL FROM tl_lascrt11_answer_log; + + +-- cleanup +ALTER TABLE tl_lascrt11_answer_log DROP COLUMN scratchie_item_uid, + DROP COLUMN scratchie_answer_uid; + +DROP TABLE tl_lascrt11_scratchie_answer; + +-- prepare for next tool migration, recreate tables +DROP TABLE tmp_question; +DROP TABLE tmp_question_match; +DROP TABLE tmp_qb_question; +DROP TABLE tmp_qb_question_match; + +CREATE TABLE tmp_question (question_uid BIGINT PRIMARY KEY, + content MEDIUMTEXT); + +CREATE TABLE tmp_question_match (question_uid BIGINT PRIMARY KEY, + target_uid BIGINT); +ALTER TABLE tmp_question_match ADD INDEX (target_uid); + +CREATE TABLE tmp_qb_question (question_uid BIGINT PRIMARY KEY, + content MEDIUMTEXT); + +CREATE TABLE tmp_qb_question_match (question_uid BIGINT PRIMARY KEY, + qb_question_uid BIGINT); +ALTER TABLE tmp_qb_question_match ADD INDEX (qb_question_uid); + +-- ASSESSMENT + +-- shift Assessment question UIDs by offset equal to existing UIDs of MCQ adn Scratchie in lams_qb_tool_question +SET @max_tool_question_id = (SELECT MAX(tool_question_uid) FROM lams_qb_tool_question); +-- remove characters that prevent detecting identical questions +UPDATE tl_laasse10_assessment_question SET `title` = TRIM(REPLACE(REPLACE(REPLACE(title, '> ', '>' ), '\r', '' ), '\n', '')), + `question` = TRIM(REPLACE(REPLACE(REPLACE(question, '> ', '>' ), '\r', '' ), '\n', '')), + uid = uid + @max_tool_question_id ORDER BY uid DESC; +UPDATE tl_laasse10_question_option SET `option_string` = TRIM(REPLACE(REPLACE(REPLACE(option_string, '> ', '>' ), '\r', '' ), '\n', '')), + `question` = TRIM(REPLACE(REPLACE(REPLACE(question, '> ', '>' ), '\r', '' ), '\n', '')); +UPDATE tl_laasse10_question_result SET `answer_string` = TRIM(REPLACE(REPLACE(REPLACE(answer_string, '> ', '>' ), '\r', '' ), '\n', '')); + +-- first, process questions with question_type=1 (as MCQ and Scratchie have only this type of questions) + +-- create a mapping of Assessment question UID -> its question description + all answers in a single column +-- if this column is not *exactly* as in an other row, it means it should be a separate question in QB +ALTER TABLE tl_laasse10_question_option ADD INDEX (sequence_id), + ADD INDEX tmp_index (sequence_id, question_uid); + +-- check if patch20190423.sql in Assessment has already run +-- if not, create index so we can use it in queries, then drop it +SET @exist := (SELECT COUNT(*) from information_schema.statistics WHERE + table_name = 'tl_laasse10_option_answer' AND + index_name = 'FK_tl_laasse10_option_answer_2' AND + table_schema = database()); +SET @sqlstmt := IF(@exist > 0, + 'SELECT ''INFO: Index FK_tl_laasse10_option_answer_2 already exists.''', + 'ALTER TABLE tl_laasse10_option_answer ADD CONSTRAINT FK_tl_laasse10_option_answer_2 FOREIGN KEY (question_option_uid) REFERENCES tl_laasse10_question_option (uid)'); +PREPARE stmt FROM @sqlstmt; +EXECUTE stmt; + +INSERT INTO tmp_question + SELECT q.uid, + REPLACE(REPLACE(REPLACE(strip_tags(GROUP_CONCAT(q.question, o.option_string ORDER BY o.sequence_id), true) + COLLATE utf8mb4_0900_ai_ci, + ' ', ''), + '\t', ''), + ' ', '') + FROM tl_laasse10_assessment_question AS q + JOIN tl_laasse10_question_option AS o + ON q.uid = o.question_uid + WHERE q.question_type = 1 + GROUP BY q.uid; + +-- create a similar mapping for existing questions in QB +INSERT INTO tmp_qb_question + SELECT q.uid AS question_uid, + REPLACE(REPLACE(REPLACE(strip_tags(GROUP_CONCAT(q.description, o.name ORDER BY o.display_order), true) + COLLATE utf8mb4_0900_ai_ci, + ' ', ''), + '\t', ''), + ' ', '') AS content + FROM lams_qb_question AS q + JOIN lams_qb_option AS o + ON q.uid = o.qb_question_uid + GROUP BY q.uid; + +-- create a mapping which holds IDs of questions which are already in the question bank +INSERT INTO tmp_qb_question_match + SELECT q.question_uid, qb.question_uid AS qb_question_uid + FROM tmp_question AS q + JOIN tmp_qb_question AS qb + USING (content); + +-- set up references to QB question UIDs for existing questions +INSERT INTO lams_qb_tool_question + SELECT qb.question_uid, qb.qb_question_uid, assess.content_id, aq.sequence_id + FROM tmp_qb_question_match qb + JOIN tl_laasse10_assessment_question AS aq + ON qb.question_uid = aq.uid + JOIN tl_laasse10_assessment AS assess + ON aq.assessment_uid = assess.uid; + + +-- update question names generated for MCQ with real names from Assessment +UPDATE lams_qb_question AS q + JOIN (SELECT * FROM tmp_qb_question_match GROUP BY qb_question_uid) AS m + ON q.uid = m.qb_question_uid + JOIN tl_laasse10_assessment_question AS aq + ON aq.uid = m.question_uid + SET q.name = TRIM(aq.title); + +-- create a mapping of Assessment question UID -> UID of one of Assessment questions which holds the same content +INSERT INTO tmp_question_match + SELECT q.question_uid, merged.question_uid + FROM (SELECT * FROM tmp_question GROUP BY content) AS merged + JOIN tmp_question AS q + USING (content) + LEFT JOIN lams_qb_tool_question AS qb + ON qb.tool_question_uid = q.question_uid + WHERE qb.tool_question_uid IS NULL + GROUP BY q.question_uid; + + +-- second run, process questions with all other question_type +DROP TABLE tmp_question; + +CREATE TABLE tmp_question (question_uid BIGINT PRIMARY KEY, + content MEDIUMTEXT); + +INSERT INTO tmp_question + SELECT q.uid, + REPLACE(REPLACE(REPLACE(strip_tags(GROUP_CONCAT(q.question_type, + IFNULL(q.question, ''), + q.correct_answer, + IFNULL( + CONCAT(IFNULL(o.question, ''), + IFNULL(o.option_string, ''), + o.option_float, + o.correct), + '') + ORDER BY o.sequence_id), true) + COLLATE utf8mb4_0900_ai_ci, + ' ', ''), + '\t', ''), + ' ', '') + FROM tl_laasse10_assessment_question AS q + LEFT JOIN tl_laasse10_question_option AS o + ON q.uid = o.question_uid + WHERE q.question_type != 1 + GROUP BY q.uid; + +INSERT INTO tmp_question_match + SELECT q.question_uid, merged.question_uid + FROM (SELECT * FROM tmp_question GROUP BY content) AS merged + JOIN tmp_question AS q + USING (content) + GROUP BY q.question_uid; + +-- reset column for matching QB questions with Assessment questions +UPDATE lams_qb_question SET tmp_question_id = -1; + + +-- fill Question Bank question table with unique questions, with manually incremented question ID +INSERT INTO lams_qb_question SELECT NULL, NULL, aq.question_type, @question_id:=@question_id + 1, 1, IFNULL(assessment.create_date, NOW()), NULL, + TRIM(REPLACE(REPLACE(strip_tags(aq.question, false) COLLATE utf8mb4_0900_ai_ci, ' ', ' '), '\t', ' ')), + TRIM(aq.description), IFNULL(aq.max_mark, 1), aq.feedback, aq.penalty_factor, aq.answer_required, + aq.multiple_answers_allowed, aq.incorrect_answer_nullifies_mark, aq.feedback_on_correct, aq.feedback_on_partially_correct, + aq.feedback_on_incorrect, aq.shuffle, aq.prefix_answers_with_letters, aq.case_sensitive, aq.correct_answer, + aq.allow_rich_editor, aq.max_words_limit, aq.min_words_limit, aq.hedging_justification_enabled, q.target_uid + FROM (SELECT uid, + title AS question, + question AS description, + default_grade AS max_mark, + question_type, + IF(TRIM(TRIM(BOTH '\r' FROM TRIM(BOTH '\n' FROM general_feedback)))= '', + NULL, TRIM(TRIM(BOTH '\r' FROM TRIM(BOTH '\n' FROM general_feedback)))) AS feedback, + penalty_factor, + answer_required, + multiple_answers_allowed, + incorrect_answer_nullifies_mark, + TRIM(TRIM(BOTH '\r' FROM TRIM(BOTH '\n' FROM feedback_on_correct))) AS feedback_on_correct, + TRIM(TRIM(BOTH '\r' FROM TRIM(BOTH '\n' FROM feedback_on_partially_correct))) AS feedback_on_partially_correct, + TRIM(TRIM(BOTH '\r' FROM TRIM(BOTH '\n' FROM feedback_on_incorrect))) AS feedback_on_incorrect, + shuffle, + prefix_answers_with_letters, + case_sensitive, + correct_answer, + allow_rich_editor, + max_words_limit, + min_words_limit, + hedging_justification_enabled, + assessment_uid + FROM tl_laasse10_assessment_question) AS aq + JOIN (SELECT DISTINCT target_uid FROM tmp_question_match) AS q + ON aq.uid = q.target_uid + JOIN tl_laasse10_assessment AS assessment + ON aq.assessment_uid = assessment.uid; + +-- set up references to QB question UIDs created above +INSERT INTO lams_qb_tool_question + SELECT q.question_uid, qb.uid, assess.content_id, aq.sequence_id + FROM lams_qb_question AS qb + JOIN tmp_question_match AS q + ON qb.tmp_question_id = q.target_uid + JOIN tl_laasse10_assessment_question AS aq + ON q.question_uid = aq.uid + JOIN tl_laasse10_assessment AS assess + ON aq.assessment_uid = assess.uid; + +-- delete obsolete columns +ALTER TABLE tl_laasse10_assessment_question DROP FOREIGN KEY FK_NEW_1720029621_F52D1F93EC0D3147, + DROP COLUMN title, + DROP COLUMN question, + DROP COLUMN question_type, + DROP COLUMN default_grade, + DROP COLUMN feedback, + DROP COLUMN general_feedback, + DROP COLUMN penalty_factor, + DROP COLUMN answer_required, + DROP COLUMN multiple_answers_allowed, + DROP COLUMN incorrect_answer_nullifies_mark, + DROP COLUMN feedback_on_correct, + DROP COLUMN feedback_on_partially_correct, + DROP COLUMN feedback_on_incorrect, + DROP COLUMN shuffle, + DROP COLUMN prefix_answers_with_letters, + DROP COLUMN case_sensitive, + DROP COLUMN correct_answer, + DROP COLUMN allow_rich_editor, + DROP COLUMN max_words_limit, + DROP COLUMN min_words_limit, + DROP COLUMN hedging_justification_enabled, + DROP COLUMN session_uid, + DROP COLUMN sequence_id; + + +-- delete corrupted data +DELETE a FROM tl_laasse10_option_answer AS a JOIN tl_laasse10_question_option AS o ON a.question_option_uid = o.uid WHERE o.question_uid IS NULL; +DELETE FROM tl_laasse10_question_option WHERE question_uid IS NULL; + +-- some Assessment options can be ordered from 0, some from 1 +-- shift ones ordered from 0 by +1 to match the other group +CREATE TABLE tmp_assessment_option +SELECT DISTINCT question_uid FROM tl_laasse10_question_option WHERE sequence_id = 0; + +ALTER TABLE tmp_assessment_option ADD PRIMARY KEY (question_uid); + +UPDATE tl_laasse10_question_option AS o +SET sequence_id = sequence_id + 1 +WHERE EXISTS + (SELECT 1 FROM tmp_assessment_option WHERE question_uid = o.question_uid); + +DROP TABLE tmp_assessment_option; + +-- fill table with options matching unique QB questions inserted above +INSERT INTO lams_qb_option (qb_question_uid, display_order, name, matching_pair, numerical_option, max_mark, accepted_error, feedback) + SELECT q.uid, o.sequence_id, IFNULL(o.option_string, ''), o.question, o.option_float, + IF(o.correct = 1, 1, o.grade), o.accepted_error, o.feedback + FROM tl_laasse10_question_option AS o + JOIN lams_qb_question AS q + ON o.question_uid = q.tmp_question_id + ORDER BY o.question_uid, o.sequence_id; + +-- fill table with units matching unique QB questions inserted above +INSERT INTO lams_qb_question_unit (qb_question_uid, display_order, multiplier, name) + SELECT q.uid, u.sequence_id, u.multiplier, u.unit + FROM tl_laasse10_assessment_unit AS u + JOIN lams_qb_question AS q + ON u.question_uid = q.tmp_question_id + ORDER BY u.question_uid, u.sequence_id; + + + +ALTER TABLE tl_laasse10_question_result DROP FOREIGN KEY FK_NEW_1720029621_693580A438BF8DFE; + +-- clean up corrupted Assessment results +DELETE r FROM tl_laasse10_question_result AS r LEFT JOIN tl_laasse10_assessment_result AS a ON r.result_uid = a.uid WHERE a.uid IS NULL; + +-- shift Assessment answer UIDs by offset equal to existing UIDs of MCQ and Assessment answers in lams_qb_tool_answer +SET @max_answer_uid = (SELECT MAX(answer_uid) FROM lams_qb_tool_answer); +UPDATE tl_laasse10_question_result SET uid = uid + @max_answer_uid ORDER BY uid DESC; + +-- rewrite references from Assessment options to QB options +UPDATE tl_laasse10_question_result AS sl, tl_laasse10_question_option AS o, lams_qb_tool_question AS tq, lams_qb_option AS qo + SET sl.submitted_option_uid = qo.uid + WHERE o.sequence_id = qo.display_order + AND sl.submitted_option_uid = o.uid + AND qo.qb_question_uid = tq.qb_question_uid + AND o.question_uid = tq.tool_question_uid; + + +-- prepare for updating option IDs in tl_laasse10_option_answer +CREATE TABLE tmp_option_answer + SELECT DISTINCT sa.question_option_uid, qo.uid + FROM tl_laasse10_option_answer AS sa + JOIN tl_laasse10_question_option AS o FORCE INDEX (tmp_index) ON sa.question_option_uid = o.uid + JOIN lams_qb_option AS qo ON o.sequence_id = qo.display_order + JOIN lams_qb_tool_question AS tq ON qo.qb_question_uid = tq.qb_question_uid + AND o.question_uid = tq.tool_question_uid; + +ALTER TABLE tmp_option_answer ADD PRIMARY KEY (question_option_uid); + +-- there can be few malformed answers; delete them +DELETE o + FROM tl_laasse10_option_answer AS o + LEFT JOIN tmp_option_answer AS t USING (question_option_uid) + WHERE t.question_option_uid IS NULL; + +ALTER TABLE tl_laasse10_option_answer DROP FOREIGN KEY FK_tl_laasse10_option_answer_2; + +-- proper update +UPDATE tl_laasse10_option_answer AS sa, tmp_option_answer AS t + SET sa.question_option_uid = t.uid + WHERE sa.question_option_uid = t.question_option_uid; + +ALTER TABLE tl_laasse10_option_answer ADD CONSTRAINT FK_tl_laasse10_option_answer_2 + FOREIGN KEY (question_option_uid) REFERENCES lams_qb_option (uid) ON DELETE CASCADE ON UPDATE CASCADE; + +-- prepare for answer inheritance +INSERT INTO lams_qb_tool_answer + SELECT uid, assessment_question_uid, submitted_option_uid, answer_string FROM tl_laasse10_question_result; + +-- cleanup +ALTER TABLE tl_laasse10_question_result DROP COLUMN assessment_question_uid, + DROP COLUMN submitted_option_uid, + DROP COLUMN answer_string; + +DROP TABLE tl_laasse10_question_option, + tl_laasse10_assessment_unit; + +-- prepare for next tool migration, recreate tables +DROP TABLE tmp_question; +DROP TABLE tmp_question_match; + +CREATE TABLE tmp_question (question_uid BIGINT PRIMARY KEY, + content MEDIUMTEXT); + +CREATE TABLE tmp_question_match (question_uid BIGINT PRIMARY KEY, + target_uid BIGINT); +ALTER TABLE tmp_question_match ADD INDEX (target_uid); + + + +-- QUESTION & ANSWERS (Q&A) + +SET @max_tool_question_id = (SELECT MAX(tool_question_uid) FROM lams_qb_tool_question); +-- remove characters that prevent detecting identical questions +UPDATE tl_laqa11_que_content SET `question` = TRIM(REPLACE(REPLACE(REPLACE(question, '> ', '>' ), '\r', '' ), '\n', '')), + `feedback` = TRIM(REPLACE(REPLACE(REPLACE(feedback, '> ', '>' ), '\r', '' ), '\n', '')), + uid = uid + @max_tool_question_id ORDER BY uid DESC; +UPDATE tl_laqa11_usr_resp SET `answer` = TRIM(REPLACE(REPLACE(REPLACE(answer, '> ', '>' ), '\r', '' ), '\n', '')); + +-- create a mapping of Q&A question UID -> its question text in a single column +-- if this column is not *exactly* as in an other row, it means it should be a separate question in QB +-- Also remove all whitespace just for less demanding matching + +INSERT INTO tmp_question + SELECT q.uid AS question_uid, + REPLACE(REPLACE(REPLACE(strip_tags(question, true) COLLATE utf8mb4_0900_ai_ci, + ' ', ''), + '\t', ''), + ' ', '') AS content + FROM tl_laqa11_que_content AS q + GROUP BY q.uid; + +-- create a mapping of Q&A question UID -> UID of one of Q&A questions which holds the same content +INSERT INTO tmp_question_match + SELECT q.question_uid, merged.question_uid AS target_uid + FROM (SELECT * FROM tmp_question GROUP BY content) AS merged + JOIN tmp_question AS q USING (content) + GROUP BY q.question_uid; + +-- reset column for matching QB questions with Scratchie questions +UPDATE lams_qb_question SET tmp_question_id = -1; + +INSERT INTO lams_qb_question (uid, `type`, question_id, version, create_date, + name, + description, max_mark, feedback, answer_required, min_words_limit, tmp_question_id) + SELECT NULL, 6, @question_id:=@question_id + 1, 1, IFNULL(c.creation_date, NOW()), + SUBSTRING(TRIM(REPLACE(REPLACE(strip_tags(qa.question, false) COLLATE utf8mb4_0900_ai_ci, ' ', ' '), '\t', '')), 1, 200), + qa.question, 1, qa.feedback, qa.answer_required, qa.min_words_limit, q.target_uid + FROM (SELECT uid, + question AS question, + IF(TRIM(feedback) = '', NULL, TRIM(feedback)) AS feedback, + answer_required, + min_words_limit, + qa_content_id + FROM tl_laqa11_que_content) AS qa + JOIN (SELECT DISTINCT target_uid FROM tmp_question_match) AS q + ON qa.uid = q.target_uid + JOIN tl_laqa11_content AS c + ON qa.qa_content_id = c.uid; + +-- set up references to QB question UIDs created above +INSERT INTO lams_qb_tool_question + SELECT q.question_uid, qb.uid, c.qa_content_id, qa.display_order + FROM lams_qb_question AS qb + JOIN tmp_question_match AS q + ON qb.tmp_question_id = q.target_uid + JOIN tl_laqa11_que_content AS qa + ON q.question_uid = qa.uid + JOIN tl_laqa11_content AS c + ON qa.qa_content_id = c.uid; + +-- remove columns from Q&A which are duplicated in Question Bank +ALTER TABLE tl_laqa11_que_content DROP COLUMN question, + DROP COLUMN display_order, + DROP COLUMN feedback, + DROP COLUMN answer_required, + DROP COLUMN min_words_limit; + +ALTER TABLE tl_laqa11_usr_resp DROP FOREIGN KEY FK_tl_laqa11_usr_resp_2, + -- to keep consistency with other tools + RENAME COLUMN response_id TO uid; + +-- shift Q&A answer UIDs by offset equal to existing UIDs of Q&A and Assessment answers in lams_qb_tool_answer +SET @max_answer_uid = (SELECT MAX(answer_uid) FROM lams_qb_tool_answer); +UPDATE tl_laqa11_usr_resp SET uid = uid + @max_answer_uid ORDER BY uid DESC; + + +-- prepare for answer inheritance +INSERT INTO lams_qb_tool_answer + SELECT uid, qa_que_content_id, NULL, answer FROM tl_laqa11_usr_resp; + +-- fill content_folder_id with real values from learning designs +UPDATE lams_qb_question AS qbque, lams_qb_tool_question AS toolque, lams_learning_activity AS activity, lams_learning_design AS design + SET qbque.content_folder_id = design.content_folder_id + WHERE qbque.uid = toolque.qb_question_uid + AND toolque.tool_content_id = activity.tool_content_id + AND activity.learning_design_id = design.learning_design_id; + +-- add a random, default content folder ID for activities which were not a part of a LD, like template activities +UPDATE lams_qb_question SET content_folder_id = '93b97f99-a9ad-471b-a71a-5cc58ab2196e' WHERE content_folder_id IS NULL; + +-- clean up +ALTER TABLE tl_laqa11_usr_resp DROP COLUMN qa_que_content_id, + DROP COLUMN answer; + + +ALTER TABLE lams_qb_question DROP COLUMN tmp_question_id; +DROP TABLE tmp_question, + tmp_question_match, + tmp_qb_question, + tmp_qb_question_match, + tmp_option_answer; + +-- add a table for generating questionId in QbQuestion and possible other sequences in the future +CREATE TABLE lams_sequence_generator (lams_qb_question_question_id INT); +CREATE UNIQUE INDEX IDX_lams_qb_question_question_id ON lams_sequence_generator(lams_qb_question_question_id); +INSERT INTO lams_sequence_generator(lams_qb_question_question_id) VALUES ((SELECT MAX(question_id) FROM lams_qb_question)); + + + + +-- LDEV-4827 Add configuration settings for Question Bank +INSERT INTO lams_configuration VALUES +('QbQtiEnable', 'true', 'config.qb.qti.enable', 'config.header.qb', 'BOOLEAN', 1), +('QbWordEnable', 'true', 'config.qb.word.enable', 'config.header.qb', 'BOOLEAN', 1), +('QbCollectionsCreateEnable', 'true', 'config.qb.collections.create.enable', 'config.header.qb', 'BOOLEAN', 1), +('QbMonitorsReadOnly', 'false', 'config.qb.monitors.read.only', 'config.header.qb', 'BOOLEAN', 1), +('QbStatsMinParticipants', '2', 'config.qb.stats.min.participants', 'config.header.qb', 'LONG', 1), +('QbStatsGroupSize', '27', 'config.qb.stats.group.size', 'config.header.qb', 'LONG', 1); + +-- LDEV-4828 Add tables for QB collections +CREATE TABLE lams_qb_collection (`uid` BIGINT AUTO_INCREMENT, + `name` VARCHAR(255), + `user_id` BIGINT, + `personal` TINYINT(1) NOT NULL DEFAULT 0, + PRIMARY KEY (uid), + INDEX (personal), + CONSTRAINT FK_lams_qb_collection_1 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE + ); + +CREATE TABLE lams_qb_collection_question (`collection_uid` BIGINT NOT NULL, + `qb_question_id` INT NOT NULL, + CONSTRAINT FK_lams_qb_collection_question_1 FOREIGN KEY (collection_uid) REFERENCES lams_qb_collection (uid) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_qb_collection_question_2 FOREIGN KEY (qb_question_id) REFERENCES lams_qb_question (question_id) + ON DELETE CASCADE ON UPDATE CASCADE + ); + + +CREATE TABLE lams_qb_collection_organisation (`collection_uid` BIGINT NOT NULL, + `organisation_id` BIGINT NOT NULL, + CONSTRAINT FK_lams_qb_collection_share_1 FOREIGN KEY (collection_uid) REFERENCES lams_qb_collection (uid) + ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_qb_collection_share_2 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id) + ON DELETE CASCADE ON UPDATE CASCADE + ); + +INSERT INTO lams_qb_collection VALUES (1, 'Public questions', NULL, false); + +INSERT INTO lams_qb_collection_question + SELECT 1, question_id FROM lams_qb_question; + +INSERT INTO lams_configuration VALUES +('QbCollectionsTransferEnable', 'true', 'config.qb.collections.transfer.enable', 'config.header.qb', 'BOOLEAN', 1); + +-- LDEV-4834 Add Learning Outcomes to QB questions +ALTER TABLE lams_outcome_mapping ADD COLUMN qb_question_id INT; + +-- LDEV-4589 Add column for LTI Membership service +ALTER TABLE lams_ext_server_org_map ADD COLUMN `membership_url` text COLLATE utf8mb4_unicode_ci; + +-- LDEV-4844 Add a Question Bank event for merging questions +INSERT INTO lams_log_event_type VALUES (27, 'TYPE_QUESTIONS_MERGED', 'QUESTION_BANK'); + +-- LDEV-4827 Add configuration settings for Question Bank +INSERT INTO lams_configuration VALUES +('QbMergeEnable', 'true', 'config.qb.merge.enable', 'config.header.qb', 'BOOLEAN', 1); + +-- LDEV-4875 Add VSA question type to Assessment and Scratchie +ALTER TABLE lams_qb_question ADD COLUMN autocomplete_enabled TINYINT(1) DEFAULT 0; + +-- LDEV-4876 Ability for LTI servers to get userId from another request parameter name +ALTER TABLE lams_ext_server_org_map ADD COLUMN `use_alternative_user_id_parameter_name` tinyint(1) DEFAULT '0'; + +-- LDEV-4874 Restrict displaying names for students that are not within student's group +insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) +values ('RestrictedGroupUserNames','true', 'config.restricted.displaying.user.names.in.groupings', 'config.header.privacy.settings', 'BOOLEAN', 0); + +-- LDEV-4884 Allow pre-filling QB question UUID, for example when it gets imported +DROP TRIGGER IF EXISTS before_insert_qb_question; + +CREATE TRIGGER before_insert_qb_question + BEFORE INSERT ON lams_qb_question + FOR EACH ROW + SET new.uuid = IF(new.uuid IS NULL, UUID_TO_BIN(UUID()), new.uuid); + +-- LDEV-4914 Do not allow NULLs in Lesson settings + +-- set any null values to default value of 0 +UPDATE lams_lesson SET + learner_presence_avail = IFNULL(learner_presence_avail, 0), + learner_im_avail = IFNULL(learner_im_avail, 0), + live_edit_enabled = IFNULL(live_edit_enabled, 0), + enable_lesson_notifications = IFNULL(enable_lesson_notifications, 0), + locked_for_edit = IFNULL(locked_for_edit, 0), + marks_released = IFNULL(marks_released, 0), + enable_lesson_intro = IFNULL(enable_lesson_intro, 0), + display_design_image = IFNULL(display_design_image, 0), + force_restart = IFNULL(force_restart, 0), + allow_restart = IFNULL(allow_restart, 0), + gradebook_on_complete = IFNULL(gradebook_on_complete, 0); + + +ALTER TABLE lams_lesson + MODIFY COLUMN learner_presence_avail TINYINT(1) NOT NULL DEFAULT 0, + MODIFY COLUMN learner_im_avail TINYINT(1) NOT NULL DEFAULT 0, + MODIFY COLUMN live_edit_enabled TINYINT(1) NOT NULL DEFAULT 0, + MODIFY COLUMN enable_lesson_notifications TINYINT(1) NOT NULL DEFAULT 0, + MODIFY COLUMN locked_for_edit TINYINT(1) NOT NULL DEFAULT 0, + MODIFY COLUMN marks_released TINYINT(1) NOT NULL DEFAULT 0, + MODIFY COLUMN enable_lesson_intro TINYINT(1) NOT NULL DEFAULT 0, + MODIFY COLUMN display_design_image TINYINT(1) NOT NULL DEFAULT 0, + MODIFY COLUMN force_restart TINYINT(1) NOT NULL DEFAULT 0, + MODIFY COLUMN allow_restart TINYINT(1) NOT NULL DEFAULT 0, + MODIFY COLUMN gradebook_on_complete TINYINT(1) NOT NULL DEFAULT 0; + +-- LDEV-4918 Collapsible subcourses + +CREATE TABLE `lams_user_organisation_collapsed` ( + `uid` bigint(20) NOT NULL AUTO_INCREMENT, + `organisation_id` bigint(20) NOT NULL, + `user_id` bigint(20) NOT NULL, + `collapsed` TINYINT(1) NOT NULL DEFAULT 0, + PRIMARY KEY (`uid`), + KEY `organisation_id` (`organisation_id`), + KEY `user_id` (`user_id`), + CONSTRAINT `FK_lams_user_organisation_collapsed_1` FOREIGN KEY (`organisation_id`) REFERENCES `lams_organisation` (`organisation_id`), + CONSTRAINT `FK_lams_user_organisation_collapsed_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- Add "Enable collapsing subcourses" to system settings and organisation +INSERT INTO lams_configuration (config_key, config_value, description_key, header_name, format, required) +VALUES ('EnableCollapsingSubcourses','false', 'config.enable.collapsing.subcourses', 'config.header.features', 'BOOLEAN', 0); + +-- LDEV-4846 Fix missin QB question content folder IDs + +UPDATE lams_qb_question SET content_folder_id = '01234567-89ab-cdef-0123-4567890abcde' WHERE content_folder_id IS NULL; + +ALTER TABLE lams_qb_question MODIFY COLUMN content_folder_id char(36) NOT NULL; + +-- LDEV-4959 Prevent leader and non-leader from creating two GradebookUserLessons at the same time + +--Take care about potential duplicates. For this move all entries to tmp table first. +CREATE TABLE tmp_table SELECT * FROM lams_gradebook_user_lesson; +TRUNCATE TABLE lams_gradebook_user_lesson; +--Change key to unique +ALTER TABLE `lams_gradebook_user_lesson` + DROP FOREIGN KEY `FK_lams_gradebook_user_lesson_1`, + DROP INDEX `lesson_id`; +ALTER TABLE `lams_gradebook_user_lesson` + ADD UNIQUE INDEX `lesson_id` (`lesson_id`,`user_id`), + ADD CONSTRAINT `FK_lams_gradebook_user_lesson_1` FOREIGN KEY (`lesson_id`) REFERENCES `lams_lesson` (`lesson_id`); +--Move entries back to lams_gradebook_user_lesson +INSERT IGNORE INTO lams_gradebook_user_lesson SELECT * from tmp_table; +DROP TABLE tmp_table; + +-- LDEV-4962 Use complex UUIDs for user portraits + +ALTER TABLE lams_cr_node ADD COLUMN portrait_uuid BINARY(16) AFTER node_id, + ADD UNIQUE INDEX IDX_portrait_uuid (portrait_uuid); + +UPDATE lams_cr_node AS n JOIN lams_user AS u ON n.node_id = u.portrait_uuid +SET n.portrait_uuid = UUID_TO_BIN(UUID()); + +ALTER TABLE lams_user ADD COLUMN temp BINARY(16) AFTER portrait_uuid; + +UPDATE lams_cr_node AS n JOIN lams_user AS u ON n.node_id = u.portrait_uuid +SET u.temp = n.portrait_uuid; + +ALTER TABLE lams_user DROP COLUMN portrait_uuid; +ALTER TABLE lams_user RENAME COLUMN temp TO portrait_uuid; + +-- If there were no errors, commit and restore autocommit to on +COMMIT; +SET AUTOCOMMIT = 1; +SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Index: lams_tool_assessment/build.properties =================================================================== diff -u -r4ba184e420dd753ae8e2dcc9434581a794fa244d -r2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe --- lams_tool_assessment/build.properties (.../build.properties) (revision 4ba184e420dd753ae8e2dcc9434581a794fa244d) +++ lams_tool_assessment/build.properties (.../build.properties) (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -2,7 +2,7 @@ min.server.version.number=3.0 signature=laasse10 -tool.version=20190103 +tool.version=20200219 tool.hide=false tool.short.name=assessment Index: lams_tool_assessment/db/sql/create_lams_tool_assessment.sql =================================================================== diff -u -r4ba184e420dd753ae8e2dcc9434581a794fa244d -r2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe --- lams_tool_assessment/db/sql/create_lams_tool_assessment.sql (.../create_lams_tool_assessment.sql) (revision 4ba184e420dd753ae8e2dcc9434581a794fa244d) +++ lams_tool_assessment/db/sql/create_lams_tool_assessment.sql (.../create_lams_tool_assessment.sql) (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -29,6 +29,7 @@ numbered tinyint(1) DEFAULT 1, use_select_leader_tool_ouput tinyint(1) NOT NULL DEFAULT 0, enable_confidence_levels TINYINT(1) NOT NULL DEFAULT 0, + confidence_levels_type TINYINT DEFAULT 1, PRIMARY KEY (uid), UNIQUE KEY content_id (content_id) ); @@ -67,45 +68,20 @@ create table tl_laasse10_assessment_question ( uid bigint not null auto_increment, - question_type smallint, - title varchar(255), - question MEDIUMTEXT, sequence_id integer, - default_grade integer DEFAULT 1, - penalty_factor float DEFAULT 0, - general_feedback MEDIUMTEXT, - feedback MEDIUMTEXT, - multiple_answers_allowed tinyint DEFAULT 0, - feedback_on_correct MEDIUMTEXT, - feedback_on_partially_correct MEDIUMTEXT, - feedback_on_incorrect MEDIUMTEXT, - shuffle tinyint(1), - case_sensitive tinyint(1), - correct_answer tinyint(1) DEFAULT 0, - allow_rich_editor tinyint(1) DEFAULT 0, + random_question TINYINT(1) NOT NULL DEFAULT 0, assessment_uid bigint, session_uid bigint, - answer_required tinyint(1) NOT NULL DEFAULT 0, - max_words_limit int(11) DEFAULT 0, - min_words_limit int(11) DEFAULT 0, - incorrect_answer_nullifies_mark tinyint(1) NOT NULL DEFAULT 0, - hedging_justification_enabled tinyint(1) NOT NULL DEFAULT 0, - question_hash CHAR(40), correct_answers_disclosed TINYINT(1) DEFAULT 0, groups_answers_disclosed TINYINT(1) DEFAULT 0, - prefix_answers_with_letters TINYINT(1) NOT NULL DEFAULT 0, PRIMARY KEY (uid), CONSTRAINT FK_NEW_1720029621_F52D1F9330E79035 FOREIGN KEY (assessment_uid) - REFERENCES tl_laasse10_assessment (uid) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT FK_NEW_1720029621_F52D1F93EC0D3147 FOREIGN KEY (session_uid) - REFERENCES tl_laasse10_session (uid) ON DELETE CASCADE ON UPDATE CASCADE + REFERENCES tl_laasse10_assessment (uid) ON DELETE CASCADE ON UPDATE CASCADE ); create table tl_laasse10_question_reference ( uid bigint not null auto_increment, question_uid bigint, - question_type smallint, - title varchar(255), sequence_id integer, default_grade integer DEFAULT 1, random_question tinyint(1) DEFAULT 0, @@ -117,22 +93,6 @@ REFERENCES tl_laasse10_assessment (uid) ON DELETE CASCADE ON UPDATE CASCADE ); -create table tl_laasse10_question_option ( - uid bigint not null unique auto_increment, - question_uid bigint, - sequence_id integer, - question MEDIUMTEXT, - option_string MEDIUMTEXT, - option_float float, - accepted_error float, - grade float, - feedback MEDIUMTEXT, - correct tinyint(1) NOT NULL DEFAULT 0, - primary key (uid), - CONSTRAINT FK_tl_laasse10_question_option_1 FOREIGN KEY (question_uid) - REFERENCES tl_laasse10_assessment_question (uid) ON DELETE CASCADE ON UPDATE CASCADE -); - create table tl_laasse10_assessment_overall_feedback ( uid bigint not null unique auto_increment, assessment_uid bigint, @@ -143,16 +103,6 @@ CONSTRAINT FK_tl_laasse10_assessment_overall_feedback_1 FOREIGN KEY (assessment_uid) REFERENCES tl_laasse10_assessment (uid) ON DELETE CASCADE ON UPDATE CASCADE ); -create table tl_laasse10_assessment_unit ( - uid bigint not null unique auto_increment, - question_uid bigint, - sequence_id integer, - multiplier float, - unit varchar(255), - primary key (uid), - CONSTRAINT FK_tl_laasse10_assessment_unit_1 FOREIGN KEY (question_uid) - REFERENCES tl_laasse10_assessment_question (uid) ON DELETE CASCADE ON UPDATE CASCADE -); create table tl_laasse10_assessment_result ( uid bigint not null auto_increment, @@ -167,6 +117,7 @@ time_limit_launched_date datetime, primary key (uid), UNIQUE KEY `UQ_tl_laasse10_assessment_result_5` (`assessment_uid`,`user_uid`,`latest`), + KEY `latest` (`latest`), CONSTRAINT FK_tl_laasse10_assessment_result_2 FOREIGN KEY (user_uid) REFERENCES tl_laasse10_user (uid) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FK_tl_laasse10_assessment_result_3 FOREIGN KEY (assessment_uid) @@ -175,20 +126,15 @@ create table tl_laasse10_question_result ( uid bigint not null auto_increment, - assessment_question_uid bigint, result_uid bigint, - answer_string MEDIUMTEXT, answer_float float, answer_boolean tinyint(1), - submitted_option_uid bigint, mark float, penalty float, finish_date datetime, max_mark float, confidence_level INT(11) NOT NULL DEFAULT 0, primary key (uid), - CONSTRAINT FK_NEW_1720029621_693580A438BF8DFE FOREIGN KEY (assessment_question_uid) - REFERENCES tl_laasse10_assessment_question (uid) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FK_tl_laasse10_question_result_1 FOREIGN KEY (result_uid) REFERENCES tl_laasse10_assessment_result (uid) ON DELETE CASCADE ON UPDATE CASCADE ); @@ -200,20 +146,30 @@ answer_boolean tinyint(1), answer_int integer, primary key (uid), + KEY `answer_boolean` (`answer_boolean`), CONSTRAINT FK_tl_laasse10_option_answer_1 FOREIGN KEY (question_result_uid) - REFERENCES tl_laasse10_question_result (uid) ON DELETE CASCADE ON UPDATE CASCADE + REFERENCES tl_laasse10_question_result (uid) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_tl_laasse10_option_answer_2 FOREIGN KEY (question_option_uid) + REFERENCES lams_qb_option (uid) ON DELETE CASCADE ON UPDATE CASCADE ); ALTER TABLE tl_laasse10_assessment ADD CONSTRAINT FK_NEW_1720029621_89093BF758092FB FOREIGN KEY (create_by) REFERENCES tl_laasse10_user (uid) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE tl_laasse10_session ADD CONSTRAINT tl_laasse10_session FOREIGN KEY (group_leader_uid) REFERENCES tl_laasse10_user (uid) ON DELETE CASCADE ON UPDATE CASCADE; - + +CREATE TABLE `tl_laasse10_configuration` ( + `config_key` varchar(30), + `config_value` varchar(255), + PRIMARY KEY (`config_key`) +); INSERT INTO tl_laasse10_assessment (uid, title, instructions, define_later, content_id, allow_question_feedback, allow_overall_feedback, allow_right_answers, allow_wrong_answers, allow_grades_after_attempt, allow_history_responses, display_summary, shuffled) VALUES (1,'Assessment','Instructions',0,${default_content_id},0,0,0,0,0,0,0,0); + +INSERT INTO `tl_laasse10_configuration` (`config_key`, `config_value`) VALUES ('hideTitles', 'false'); SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Index: lams_tool_assessment/db/sql/tool_insert.sql =================================================================== diff -u -r7475d08afc280b5e2e5ddf04e8bf35e3166aaf80 -r2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe --- lams_tool_assessment/db/sql/tool_insert.sql (.../tool_insert.sql) (revision 7475d08afc280b5e2e5ddf04e8bf35e3166aaf80) +++ lams_tool_assessment/db/sql/tool_insert.sql (.../tool_insert.sql) (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -17,6 +17,7 @@ language_file, create_date_time, modified_date_time, +admin_url, supports_outputs ) VALUES @@ -38,5 +39,6 @@ 'org.lamsfoundation.lams.tool.assessment.ApplicationResources', NOW(), NOW(), +'tool/laasse10/admin/start.do', 1 ) \ No newline at end of file Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20190423.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20190517.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20190704.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20190809.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20190913.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20191016.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20191120.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20191205.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20200219_updateTo40.sql =================================================================== diff -u --- lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20200219_updateTo40.sql (revision 0) +++ lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20200219_updateTo40.sql (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -0,0 +1,66 @@ +SET AUTOCOMMIT = 0; +SET FOREIGN_KEY_CHECKS = 0; + +-- This patch contains files patch20190423.sql to patch20191205.sql +-- It should upgrade this tool to version 4.0 + +-- LDEV-4813 Add a missing foreign key and index to speed up queries +-- We need to check if patch20190722.sql from lams_common has not run already +SET @exist := (SELECT COUNT(*) from information_schema.statistics WHERE + table_name = 'lams_qb_question' AND + table_schema = database()); +SET @sqlstmt := IF(@exist > 0, + 'SELECT ''INFO: Question Bank has already created necessary foreign keys''', + 'ALTER TABLE tl_laasse10_option_answer ADD CONSTRAINT FK_tl_laasse10_option_answer_2 FOREIGN KEY (question_option_uid) REFERENCES tl_laasse10_question_option (uid) ON DELETE CASCADE ON UPDATE CASCADE'); +PREPARE stmt FROM @sqlstmt; +EXECUTE stmt; + +--LDEV-4821 remove obsolete hash field +ALTER TABLE tl_laasse10_assessment_question DROP COLUMN question_hash; + +UPDATE lams_tool SET tool_version='20190517' WHERE tool_signature='laasse10'; + +-- LDEV-4836 remove obsolete columns +ALTER TABLE tl_laasse10_question_reference DROP COLUMN title; +ALTER TABLE tl_laasse10_question_reference DROP COLUMN question_type; + +-- fill assessment_question.random_question with real values for old questions +ALTER TABLE tl_laasse10_assessment_question ADD COLUMN random_question TINYINT(1) NOT NULL DEFAULT 0; +UPDATE tl_laasse10_assessment_question AS asque +SET asque.random_question = 1 +WHERE NOT EXISTS + (SELECT 1 FROM tl_laasse10_question_reference WHERE question_uid = asque.uid); + +UPDATE lams_tool SET tool_version='20190704' WHERE tool_signature='laasse10'; + +--LDEV-4845 Bump version so content version filter kicks in +UPDATE lams_tool SET tool_version='20190809' WHERE tool_signature='laasse10'; + +-- LDEV-4875 Add VSA question type to Assessment and Scratchie +ALTER TABLE tl_lascrt11_scratchie ADD COLUMN activity_uuid_providing_vsa_answers INT(11); + +-- LDEV-4813 Add missing indexes to speed up queries +ALTER TABLE tl_laasse10_assessment_result ADD INDEX (latest); +ALTER TABLE tl_laasse10_option_answer ADD INDEX (answer_boolean); + +-- LDEV-4880 Confidence level variations +ALTER TABLE tl_laasse10_assessment ADD COLUMN confidence_levels_type TINYINT DEFAULT 1; +UPDATE lams_tool SET tool_version='20191120' WHERE tool_signature='laasse10'; + +--LDEV-4921 Allow question title hiding in learner +CREATE TABLE `tl_laasse10_configuration` ( + `config_key` varchar(30), + `config_value` varchar(255), + PRIMARY KEY (`config_key`) +); +INSERT INTO `tl_laasse10_configuration` (`config_key`, `config_value`) VALUES ('hideTitles', 'false'); + +UPDATE lams_tool SET admin_url = 'tool/laasse10/admin/start.do' WHERE tool_signature = 'laasse10'; + +-- LDEV-4951 Update tools version for LAMS 4.0 release +UPDATE lams_tool SET tool_version='20190219' WHERE tool_signature='laasse10'; + +-- If there were no errors, commit and restore autocommit to on +COMMIT; +SET AUTOCOMMIT = 1; +SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Index: lams_tool_lamc/build.properties =================================================================== diff -u -r4ba184e420dd753ae8e2dcc9434581a794fa244d -r2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe --- lams_tool_lamc/build.properties (.../build.properties) (revision 4ba184e420dd753ae8e2dcc9434581a794fa244d) +++ lams_tool_lamc/build.properties (.../build.properties) (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -2,6 +2,6 @@ min.server.version.number=3.0 signature=lamc11 -tool.version=20190103 +tool.version=20200219 tool.hide=false tool.short.name=mc \ No newline at end of file Index: lams_tool_lamc/db/sql/create_lams_tool_mc.sql =================================================================== diff -u -r4ba184e420dd753ae8e2dcc9434581a794fa244d -r2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe --- lams_tool_lamc/db/sql/create_lams_tool_mc.sql (.../create_lams_tool_mc.sql) (revision 4ba184e420dd753ae8e2dcc9434581a794fa244d) +++ lams_tool_lamc/db/sql/create_lams_tool_mc.sql (.../create_lams_tool_mc.sql) (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -62,63 +62,54 @@ CREATE TABLE tl_lamc11_que_content ( uid BIGINT(20) NOT NULL AUTO_INCREMENT - , question MEDIUMTEXT - , mark INT(5) NOT NULL DEFAULT 1 - , display_order INT(5) , mc_content_id BIGINT(20) NOT NULL - , feedback MEDIUMTEXT - , question_hash CHAR(40) , PRIMARY KEY (uid) , INDEX (mc_content_id) , CONSTRAINT FK_tl_lamc11_que_content_1 FOREIGN KEY (mc_content_id) REFERENCES tl_lamc11_content (uid) ON DELETE CASCADE ON UPDATE CASCADE ); -CREATE TABLE tl_lamc11_options_content ( - uid BIGINT(20) NOT NULL AUTO_INCREMENT - , correct_option TINYINT(1) NOT NULL DEFAULT 0 - , mc_que_content_id BIGINT(20) - , mc_que_option_text MEDIUMTEXT - , displayOrder INT(5) - , PRIMARY KEY (uid) - , INDEX (mc_que_content_id) - , CONSTRAINT FK_tl_lamc11_options_content_1 FOREIGN KEY (mc_que_content_id) - REFERENCES tl_lamc11_que_content (uid) ON DELETE CASCADE ON UPDATE CASCADE -); - CREATE TABLE tl_lamc11_usr_attempt ( uid BIGINT(20) NOT NULL AUTO_INCREMENT , que_usr_id BIGINT(20) NOT NULL - , mc_que_content_id BIGINT(20) NOT NULL - , mc_que_option_id BIGINT(20) NOT NULL , attempt_time DATETIME , isAttemptCorrect TINYINT(1) NOT NULL DEFAULT 0 , mark VARCHAR(255) , passed TINYINT(1) NOT NULL DEFAULT 0 , confidence_level INT(11) NOT NULL DEFAULT 0 , PRIMARY KEY (uid) - , UNIQUE KEY attempt_unique_index (que_usr_id,mc_que_content_id) - , INDEX (mc_que_content_id) - , CONSTRAINT FK_tl_lamc11_usr_attempt_2 FOREIGN KEY (mc_que_content_id) - REFERENCES tl_lamc11_que_content (uid) ON DELETE CASCADE ON UPDATE CASCADE - , INDEX (mc_que_option_id) - , CONSTRAINT FK_tl_lamc11_usr_attempt_3 FOREIGN KEY (mc_que_option_id) - REFERENCES tl_lamc11_options_content (uid) ON DELETE CASCADE ON UPDATE CASCADE , INDEX (que_usr_id) , CONSTRAINT FK_tl_lamc11_usr_attempt_4 FOREIGN KEY (que_usr_id) REFERENCES tl_lamc11_que_usr (uid) ON DELETE CASCADE ON UPDATE CASCADE ); +CREATE TABLE `tl_lamc11_configuration` ( + `config_key` varchar(30), + `config_value` varchar(255), + PRIMARY KEY (`config_key`) +); ALTER TABLE tl_lamc11_session ADD CONSTRAINT FK_lamc11_session1 FOREIGN KEY (mc_group_leader_uid) REFERENCES tl_lamc11_que_usr (uid) ON DELETE SET NULL ON UPDATE CASCADE; - +-- data for content table INSERT INTO tl_lamc11_content(uid, content_id , title, instructions, creation_date , reflect, questions_sequenced, created_by , define_later, retries, show_report, pass_mark) VALUES (1, ${default_content_id}, 'MCQ', 'Instructions', NOW(), 0, 0, 1, 0, 0, 0, 0); -INSERT INTO tl_lamc11_que_content (uid,question, mark, display_order, mc_content_id) VALUES (1, 'A Sample question?', 1,1,1); - -INSERT INTO tl_lamc11_options_content (uid, correct_option, displayOrder, mc_que_content_id, mc_que_option_text) VALUES (1, 0, 1, 1,'Answer 1'); -INSERT INTO tl_lamc11_options_content (uid, correct_option, displayOrder, mc_que_content_id, mc_que_option_text) VALUES (2, 1, 2, 1,'Answer 2'); +-- data for QB question table +SET @max_question_id = COALESCE((SELECT MAX(question_id) FROM lams_qb_question),0) + 1; +INSERT INTO `lams_qb_question` (`type`,`question_id`,`version`,`create_date`,`name`,`description`,`max_mark`,`content_folder_id`) + VALUES (1,@max_question_id,1,NOW(),'A Sample question?','Question description',1,'01234567-89ab-cdef-0123-4567890abcde'); +INSERT INTO lams_sequence_generator(lams_qb_question_question_id) VALUES (@max_question_id); +SET @qb_question_uid = (SELECT MAX(uid) FROM lams_qb_question); +INSERT INTO `lams_qb_option` (`qb_question_uid`,`display_order`,`name`,`max_mark`) VALUES (@qb_question_uid,1,'Answer 1',0); +INSERT INTO `lams_qb_option` (`qb_question_uid`,`display_order`,`name`,`max_mark`) VALUES (@qb_question_uid,2,'Answer 2',1); +INSERT INTO lams_qb_collection_question SELECT 1, @max_question_id; +-- data for tool question tables +INSERT INTO lams_qb_tool_question (qb_question_uid,tool_content_id,display_order) VALUES (@qb_question_uid,${default_content_id},1); +INSERT INTO tl_lamc11_que_content (uid,mc_content_id) VALUES ((SELECT MAX(tool_question_uid) FROM lams_qb_tool_question),1); + + +INSERT INTO `tl_lamc11_configuration` (`config_key`, `config_value`) VALUES ('hideTitles', 'false'); + SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Index: lams_tool_lamc/db/sql/tool_insert.sql =================================================================== diff -u -r4ba184e420dd753ae8e2dcc9434581a794fa244d -r2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe --- lams_tool_lamc/db/sql/tool_insert.sql (.../tool_insert.sql) (revision 4ba184e420dd753ae8e2dcc9434581a794fa244d) +++ lams_tool_lamc/db/sql/tool_insert.sql (.../tool_insert.sql) (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -18,6 +18,7 @@ language_file, create_date_time, modified_date_time, +admin_url, supports_outputs ) VALUES @@ -40,4 +41,5 @@ 'org.lamsfoundation.lams.tool.mc.ApplicationResources', NOW(), NOW(), +'tool/lamc11/admin/start.do', 1) \ No newline at end of file Index: lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20070101_create_lams_tool_mc.sql =================================================================== diff -u --- lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20070101_create_lams_tool_mc.sql (revision 0) +++ lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20070101_create_lams_tool_mc.sql (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -0,0 +1,124 @@ +SET FOREIGN_KEY_CHECKS=0; + +CREATE TABLE tl_lamc11_content ( + uid BIGINT(20) NOT NULL AUTO_INCREMENT + , content_id BIGINT(20) NOT NULL + , title TEXT + , instructions MEDIUMTEXT + , creation_date DATETIME + , update_date DATETIME + , reflect TINYINT(1) NOT NULL DEFAULT 0 + , questions_sequenced TINYINT(1) NOT NULL DEFAULT 0 + , created_by BIGINT(20) NOT NULL DEFAULT 0 + , define_later TINYINT(1) NOT NULL DEFAULT 0 + , retries TINYINT(1) NOT NULL DEFAULT 0 + , pass_mark INTEGER + , show_report TINYINT(1) NOT NULL DEFAULT 0 + , reflectionSubject MEDIUMTEXT + , showMarks TINYINT(1) NOT NULL DEFAULT 0 + , randomize TINYINT(1) NOT NULL DEFAULT 0 + , displayAnswers TINYINT(1) NOT NULL DEFAULT 1 + , submission_deadline datetime + , use_select_leader_tool_ouput tinyint(1) NOT NULL DEFAULT 0 + , prefix_answers_with_letters tinyint(1) NOT NULL DEFAULT 1 + , enable_confidence_levels TINYINT(1) NOT NULL DEFAULT 0 + , display_feedback_only TINYINT(1) NOT NULL DEFAULT 0 + , UNIQUE UQ_tl_lamc11_content_1 (content_id) + , PRIMARY KEY (uid) +); + +CREATE TABLE tl_lamc11_session ( + uid BIGINT(20) NOT NULL AUTO_INCREMENT + , mc_session_id BIGINT(20) NOT NULL + , session_start_date DATETIME + , session_end_date DATETIME + , session_name VARCHAR(100) + , session_status VARCHAR(100) + , mc_content_id BIGINT(20) NOT NULL + , mc_group_leader_uid BIGINT(20) + , PRIMARY KEY (uid) + , UNIQUE UQ_tl_lamc11_session_1 (mc_session_id) + , INDEX (mc_content_id) + , CONSTRAINT FK_tl_lamc_session_1 FOREIGN KEY (mc_content_id) + REFERENCES tl_lamc11_content (uid) ON DELETE CASCADE ON UPDATE CASCADE +); + +CREATE TABLE tl_lamc11_que_usr ( + uid BIGINT(20) NOT NULL AUTO_INCREMENT + , que_usr_id BIGINT(20) NOT NULL + , mc_session_id BIGINT(20) NOT NULL + , username VARCHAR(255) + , fullname VARCHAR(255) + , responseFinalised TINYINT(1) NOT NULL DEFAULT 0 + , viewSummaryRequested TINYINT(1) NOT NULL DEFAULT 0 + , last_attempt_total_mark INTEGER + , number_attempts int(11) DEFAULT 0 + , PRIMARY KEY (uid) + , UNIQUE KEY que_usr_id (que_usr_id,mc_session_id) + , INDEX (mc_session_id) + , CONSTRAINT FK_tl_lamc11_que_usr_1 FOREIGN KEY (mc_session_id) + REFERENCES tl_lamc11_session (uid) ON DELETE CASCADE ON UPDATE CASCADE +); + +CREATE TABLE tl_lamc11_que_content ( + uid BIGINT(20) NOT NULL AUTO_INCREMENT + , question MEDIUMTEXT + , mark INT(5) NOT NULL DEFAULT 1 + , display_order INT(5) + , mc_content_id BIGINT(20) NOT NULL + , feedback MEDIUMTEXT + , question_hash CHAR(40) + , PRIMARY KEY (uid) + , INDEX (mc_content_id) + , CONSTRAINT FK_tl_lamc11_que_content_1 FOREIGN KEY (mc_content_id) + REFERENCES tl_lamc11_content (uid) ON DELETE CASCADE ON UPDATE CASCADE +); + +CREATE TABLE tl_lamc11_options_content ( + uid BIGINT(20) NOT NULL AUTO_INCREMENT + , correct_option TINYINT(1) NOT NULL DEFAULT 0 + , mc_que_content_id BIGINT(20) + , mc_que_option_text MEDIUMTEXT + , displayOrder INT(5) + , PRIMARY KEY (uid) + , INDEX (mc_que_content_id) + , CONSTRAINT FK_tl_lamc11_options_content_1 FOREIGN KEY (mc_que_content_id) + REFERENCES tl_lamc11_que_content (uid) ON DELETE CASCADE ON UPDATE CASCADE +); + +CREATE TABLE tl_lamc11_usr_attempt ( + uid BIGINT(20) NOT NULL AUTO_INCREMENT + , que_usr_id BIGINT(20) NOT NULL + , mc_que_content_id BIGINT(20) NOT NULL + , mc_que_option_id BIGINT(20) NOT NULL + , attempt_time DATETIME + , isAttemptCorrect TINYINT(1) NOT NULL DEFAULT 0 + , mark VARCHAR(255) + , passed TINYINT(1) NOT NULL DEFAULT 0 + , confidence_level INT(11) NOT NULL DEFAULT 0 + , PRIMARY KEY (uid) + , UNIQUE KEY attempt_unique_index (que_usr_id,mc_que_content_id) + , INDEX (mc_que_content_id) + , CONSTRAINT FK_tl_lamc11_usr_attempt_2 FOREIGN KEY (mc_que_content_id) + REFERENCES tl_lamc11_que_content (uid) ON DELETE CASCADE ON UPDATE CASCADE + , INDEX (mc_que_option_id) + , CONSTRAINT FK_tl_lamc11_usr_attempt_3 FOREIGN KEY (mc_que_option_id) + REFERENCES tl_lamc11_options_content (uid) ON DELETE CASCADE ON UPDATE CASCADE + , INDEX (que_usr_id) + , CONSTRAINT FK_tl_lamc11_usr_attempt_4 FOREIGN KEY (que_usr_id) + REFERENCES tl_lamc11_que_usr (uid) ON DELETE CASCADE ON UPDATE CASCADE +); + + +ALTER TABLE tl_lamc11_session ADD CONSTRAINT FK_lamc11_session1 FOREIGN KEY (mc_group_leader_uid) + REFERENCES tl_lamc11_que_usr (uid) ON DELETE SET NULL ON UPDATE CASCADE; + + +INSERT INTO tl_lamc11_content(uid, content_id , title, instructions, creation_date , reflect, questions_sequenced, created_by , define_later, retries, show_report, pass_mark) VALUES (1, ${default_content_id}, 'MCQ', 'Instructions', NOW(), 0, 0, 1, 0, 0, 0, 0); + +INSERT INTO tl_lamc11_que_content (uid,question, mark, display_order, mc_content_id) VALUES (1, 'A Sample question?', 1,1,1); + +INSERT INTO tl_lamc11_options_content (uid, correct_option, displayOrder, mc_que_content_id, mc_que_option_text) VALUES (1, 0, 1, 1,'Answer 1'); +INSERT INTO tl_lamc11_options_content (uid, correct_option, displayOrder, mc_que_content_id, mc_que_option_text) VALUES (2, 1, 2, 1,'Answer 2'); + +SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20190517.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20190809.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20191205.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20200219_updateTo40.sql =================================================================== diff -u --- lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20200219_updateTo40.sql (revision 0) +++ lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/dbupdates/patch20200219_updateTo40.sql (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -0,0 +1,35 @@ +SET AUTOCOMMIT = 0; +SET FOREIGN_KEY_CHECKS = 0; + +-- This patch contains files patch20190517.sql to patch20191205.sql +-- It should upgrade this tool to version 4.0 + +--LDEV-4821 remove obsolete hash field +ALTER TABLE tl_lamc11_que_content DROP COLUMN question_hash; + +UPDATE lams_tool SET tool_version='20190517' WHERE tool_signature='lamc11'; + +--LDEV-4845 Bump version so content version filter kicks in + +UPDATE lams_tool SET tool_version='20190809' WHERE tool_signature='lamc11'; + +--LDEV-4921 Allow question title hiding in learner + + CREATE TABLE `tl_lamc11_configuration` ( + `config_key` varchar(30), + `config_value` varchar(255), + PRIMARY KEY (`config_key`) + ); + +INSERT INTO `tl_lamc11_configuration` (`config_key`, `config_value`) VALUES ('hideTitles', 'false'); + +UPDATE lams_tool SET admin_url = 'tool/lamc11/admin/start.do' WHERE tool_signature = 'lamc11'; + +-- LDEV-4951 Update tools version for LAMS 4.0 release +UPDATE lams_tool SET tool_version='20190219' WHERE tool_signature='lamc11'; + + +-- If there were no errors, commit and restore autocommit to on +COMMIT; +SET AUTOCOMMIT = 1; +SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Index: lams_tool_laqa/build.properties =================================================================== diff -u -r8cb3b47e0bc1e7d89582929aa33ee29f0ef37189 -r2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe --- lams_tool_laqa/build.properties (.../build.properties) (revision 8cb3b47e0bc1e7d89582929aa33ee29f0ef37189) +++ lams_tool_laqa/build.properties (.../build.properties) (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -2,7 +2,7 @@ min.server.version.number=3.0 signature=laqa11 -tool.version=20190103 +tool.version=20200219 tool.hide=false tool.short.name=qa Index: lams_tool_laqa/db/sql/create_lams_tool_qa.sql =================================================================== diff -u -r8cb3b47e0bc1e7d89582929aa33ee29f0ef37189 -r2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe --- lams_tool_laqa/db/sql/create_lams_tool_qa.sql (.../create_lams_tool_qa.sql) (revision 8cb3b47e0bc1e7d89582929aa33ee29f0ef37189) +++ lams_tool_laqa/db/sql/create_lams_tool_qa.sql (.../create_lams_tool_qa.sql) (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -59,34 +59,24 @@ CREATE TABLE tl_laqa11_que_content ( uid BIGINT(20) NOT NULL AUTO_INCREMENT - , question MEDIUMTEXT - , feedback MEDIUMTEXT - , display_order INT(5) DEFAULT 1 , qa_content_id BIGINT(20) - , answer_required TINYINT(1) NOT NULL DEFAULT 0 - , min_words_limit int(11) DEFAULT 0 , PRIMARY KEY (uid) , INDEX (qa_content_id) , CONSTRAINT FK_tl_laqa11_que_content_1 FOREIGN KEY (qa_content_id) REFERENCES tl_laqa11_content (uid) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE tl_laqa11_usr_resp ( - response_id BIGINT(20) NOT NULL AUTO_INCREMENT - , answer MEDIUMTEXT + uid BIGINT(20) NOT NULL AUTO_INCREMENT , time_zone VARCHAR(255) , attempt_time DATETIME , que_usr_id BIGINT(20) NOT NULL - , qa_que_content_id BIGINT(20) , visible TINYINT(1) NOT NULL DEFAULT 1 , answer_autosaved MEDIUMTEXT - , PRIMARY KEY (response_id) + , PRIMARY KEY (uid) , INDEX (que_usr_id) , CONSTRAINT FK_tl_laqa11_usr_resp_3 FOREIGN KEY (que_usr_id) REFERENCES tl_laqa11_que_usr (uid) ON DELETE CASCADE ON UPDATE CASCADE - , INDEX (qa_que_content_id) - , CONSTRAINT FK_tl_laqa11_usr_resp_2 FOREIGN KEY (qa_que_content_id) - REFERENCES tl_laqa11_que_content (uid) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE tl_laqa11_conditions ( @@ -117,7 +107,16 @@ -- data for content table INSERT INTO tl_laqa11_content (qa_content_id, title, instructions, creation_date, lockWhenFinished) VALUES (${default_content_id}, 'Q&A', 'Instructions', NOW() , 0); --- data for content questions table -INSERT INTO tl_laqa11_que_content (question, display_order, qa_content_id) VALUES ('Sample Question 1?',1,1); +-- data for QB question table +SET @max_question_id = COALESCE((SELECT MAX(question_id) FROM lams_qb_question),0) + 1; +INSERT INTO `lams_qb_question` (`type`,`question_id`,`version`,`create_date`,`name`,`description`,`max_mark`,`content_folder_id`) + VALUES (6,@max_question_id,1,NOW(),'Sample Question 1?','Question description',1,'01234567-89ab-cdef-0123-4567890abcde'); +INSERT INTO lams_sequence_generator(lams_qb_question_question_id) VALUES (@max_question_id); +INSERT INTO lams_qb_collection_question SELECT 1, @max_question_id; +-- data for tool question tables +INSERT INTO lams_qb_tool_question (qb_question_uid,tool_content_id,display_order) VALUES ((SELECT MAX(uid) FROM lams_qb_question),${default_content_id},1); +INSERT INTO tl_laqa11_que_content (uid,qa_content_id) VALUES ((SELECT MAX(tool_question_uid) FROM lams_qb_tool_question),1); + + SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/dbupdates/patch20190809.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/dbupdates/patch20200219_updateTo40.sql =================================================================== diff -u --- lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/dbupdates/patch20200219_updateTo40.sql (revision 0) +++ lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/dbupdates/patch20200219_updateTo40.sql (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -0,0 +1,16 @@ +SET AUTOCOMMIT = 0; +SET FOREIGN_KEY_CHECKS = 0; + +-- This patch contains file patch20190809.sql +-- It should upgrade this tool to version 4.0 + +--LDEV-4845 Bump version so content version filter kicks in +UPDATE lams_tool SET tool_version='20190809' WHERE tool_signature='laqa11'; + +-- LDEV-4951 Update tools version for LAMS 4.0 release +UPDATE lams_tool SET tool_version='20190219' WHERE tool_signature='laqa11'; + +-- If there were no errors, commit and restore autocommit to on +COMMIT; +SET AUTOCOMMIT = 1; +SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Index: lams_tool_scratchie/build.properties =================================================================== diff -u -r8cb3b47e0bc1e7d89582929aa33ee29f0ef37189 -r2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe --- lams_tool_scratchie/build.properties (.../build.properties) (revision 8cb3b47e0bc1e7d89582929aa33ee29f0ef37189) +++ lams_tool_scratchie/build.properties (.../build.properties) (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -2,7 +2,7 @@ min.server.version.number=3.0 signature=lascrt11 -tool.version=20190103 +tool.version=20200219 tool.hide=false tool.short.name=scratchie Index: lams_tool_scratchie/db/sql/create_lams_tool_scratchie.sql =================================================================== diff -u -r764e7e5bc34045468731408215fe24cf980c538c -r2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe --- lams_tool_scratchie/db/sql/create_lams_tool_scratchie.sql (.../create_lams_tool_scratchie.sql) (revision 764e7e5bc34045468731408215fe24cf980c538c) +++ lams_tool_scratchie/db/sql/create_lams_tool_scratchie.sql (.../create_lams_tool_scratchie.sql) (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -19,6 +19,7 @@ confidence_levels_activity_uiid INT(11), preset_marks varchar(255), show_scratchies_in_results TINYINT(1) DEFAULT 1, + activity_uuid_providing_vsa_answers INT(11), PRIMARY KEY (uid), UNIQUE KEY content_id (content_id) ); @@ -56,43 +57,20 @@ CONSTRAINT FK_NEW_610529188_30113BFCEC0D3147 FOREIGN KEY (session_uid) REFERENCES tl_lascrt11_session (uid) ON DELETE CASCADE ON UPDATE CASCADE ); - + create table tl_lascrt11_scratchie_item ( uid bigint not null auto_increment, - title varchar(255), - description MEDIUMTEXT, - create_date datetime, - create_by_author TINYINT(1), scratchie_uid bigint, - session_uid bigint, - order_id integer, primary key (uid), CONSTRAINT FK_NEW_610529188_F52D1F9330E79035 FOREIGN KEY (scratchie_uid) - REFERENCES tl_lascrt11_scratchie (uid) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT FK_NEW_610529188_F52D1F93EC0D3147 FOREIGN KEY (session_uid) - REFERENCES tl_lascrt11_session (uid) ON DELETE CASCADE ON UPDATE CASCADE + REFERENCES tl_lascrt11_scratchie (uid) ON DELETE CASCADE ON UPDATE CASCADE ); -create table tl_lascrt11_scratchie_answer ( - uid bigint not null auto_increment, - description MEDIUMTEXT, - correct TINYINT(1), - scratchie_item_uid bigint, - order_id integer, - primary key (uid), - CONSTRAINT FK_scratchie_answer_1 FOREIGN KEY (scratchie_item_uid) - REFERENCES tl_lascrt11_scratchie_item (uid) ON DELETE CASCADE ON UPDATE CASCADE -); - create table tl_lascrt11_answer_log ( uid bigint not null auto_increment, access_date datetime, - scratchie_answer_uid bigint, session_id bigint, primary key (uid), - UNIQUE KEY FK_NEW_lascrt11_30113BFC309ED321 (scratchie_answer_uid,session_id), - CONSTRAINT FK_NEW_610529188_693580A438BF8DFE FOREIGN KEY (scratchie_answer_uid) - REFERENCES tl_lascrt11_scratchie_answer (uid) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT sessionIdIndex FOREIGN KEY (session_id) REFERENCES tl_lascrt11_session (session_id) ON DELETE CASCADE ON UPDATE CASCADE ); @@ -133,27 +111,33 @@ ALTER TABLE tl_lascrt11_session ADD CONSTRAINT FK_lalead11_session1 FOREIGN KEY (group_leader_uid) REFERENCES tl_lascrt11_user (uid) ON DELETE SET NULL ON UPDATE CASCADE; - -INSERT INTO tl_lascrt11_scratchie (uid,title, - instructions, define_later, content_id, reflect_on_activity, extra_point) VALUES +-- data for content table +INSERT INTO tl_lascrt11_scratchie (uid,title, instructions, define_later, content_id, reflect_on_activity, extra_point) VALUES (1,'Scratchie','Scenario explanation ',0,${default_content_id},0, 0); + +-- data for QB question table +SET @max_question_id = COALESCE((SELECT MAX(question_id) FROM lams_qb_question),0) + 1; +INSERT INTO `lams_qb_question` (`type`,`question_id`,`version`,`create_date`,`name`,`description`,`max_mark`,`content_folder_id`) + VALUES (1,@max_question_id,1,NOW(),'Question Title','Question Description',1,'01234567-89ab-cdef-0123-4567890abcde'); +INSERT INTO lams_sequence_generator(lams_qb_question_question_id) VALUES (@max_question_id); +SET @qb_question_uid = (SELECT MAX(uid) FROM lams_qb_question); +INSERT INTO `lams_qb_option` (`qb_question_uid`,`display_order`,`name`,`max_mark`) VALUES (@qb_question_uid,1,'Question Answer A',1); +INSERT INTO `lams_qb_option` (`qb_question_uid`,`display_order`,`name`,`max_mark`) VALUES (@qb_question_uid,2,'Question Answer B',0); +INSERT INTO `lams_qb_option` (`qb_question_uid`,`display_order`,`name`,`max_mark`) VALUES (@qb_question_uid,3,'Question Answer C',0); +INSERT INTO `lams_qb_option` (`qb_question_uid`,`display_order`,`name`,`max_mark`) VALUES (@qb_question_uid,4,'Question Answer D',0); +INSERT INTO lams_qb_collection_question SELECT 1, @max_question_id; -INSERT INTO tl_lascrt11_scratchie_item (uid, title, description, create_date, create_by_author, scratchie_uid, order_id) VALUES - (1, 'Question Title','Question Description',NOW(),1,1,1); +-- data for tool question tables +INSERT INTO lams_qb_tool_question (qb_question_uid,tool_content_id,display_order) VALUES (@qb_question_uid,${default_content_id},1); +INSERT INTO tl_lascrt11_scratchie_item (uid,scratchie_uid) VALUES ((SELECT MAX(tool_question_uid) FROM lams_qb_tool_question),1); + -INSERT INTO tl_lascrt11_scratchie_answer (uid, description, correct, scratchie_item_uid, order_id) VALUES - (1, 'Question Answer A',1,1,0); -INSERT INTO tl_lascrt11_scratchie_answer (uid, description, correct, scratchie_item_uid, order_id) VALUES - (2, 'Question Answer B',0,1,1); -INSERT INTO tl_lascrt11_scratchie_answer (uid, description, correct, scratchie_item_uid, order_id) VALUES - (3, 'Question Answer C',0,1,2); -INSERT INTO tl_lascrt11_scratchie_answer (uid, description, correct, scratchie_item_uid, order_id) VALUES - (4, 'Question Answer D',0,1,3); - - + INSERT INTO tl_lascrt11_configuration (config_key, config_value) VALUES ('isEnabledExtraPointOption', 'true'); INSERT INTO tl_lascrt11_configuration (config_key, config_value) VALUES ('presetMarks', '4,2,1,0'); +INSERT INTO `tl_lascrt11_configuration` (`config_key`, `config_value`) VALUES + ('hideTitles', 'false'); SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dbupdates/patch20190510.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dbupdates/patch20190809.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe refers to a dead (removed) revision in file `lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dbupdates/patch20191205.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dbupdates/patch20200219_updateTo40.sql =================================================================== diff -u --- lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dbupdates/patch20200219_updateTo40.sql (revision 0) +++ lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dbupdates/patch20200219_updateTo40.sql (revision 2b7ffc7c6e661a7f78d74a80a367b87d1f71e2fe) @@ -0,0 +1,23 @@ +SET AUTOCOMMIT = 0; +SET FOREIGN_KEY_CHECKS = 0; + +-- This patch contains files patch20190510.sql to patch20191205.sql +-- It should upgrade this tool to version 4.0 + +-- LDEV-4817 Delete existing blank burning questions +DELETE FROM tl_lascrt11_burning_question WHERE question IS NULL OR TRIM(question) = ''; + +--LDEV-4845 Bump version so content version filter kicks in +UPDATE lams_tool SET tool_version='20190809' WHERE tool_signature='lascrt11'; + +--LDEV-4921 Allow question title hiding in learner +INSERT INTO `tl_lascrt11_configuration` (`config_key`, `config_value`) VALUES ('hideTitles', 'false'); + +-- LDEV-4951 Update tools version for LAMS 4.0 release +UPDATE lams_tool SET tool_version='20190219' WHERE tool_signature='lascrt11'; + + +-- If there were no errors, commit and restore autocommit to on +COMMIT; +SET AUTOCOMMIT = 1; +SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file