Index: lams_common/db/sql/create_lams_11_tables.sql =================================================================== diff -u -rf9d8fc434a8b7629a1353f532eb15e5c4f5f3cac -r843bfb2a245193732040b8852349a5f5c4657893 --- 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 843bfb2a245193732040b8852349a5f5c4657893) @@ -1,2445 +1,1995 @@ SET FOREIGN_KEY_CHECKS=0; -SET NAMES utf8mb4 ; --- --- Table structure for table `lams_activity_category` --- +DROP TABLE IF EXISTS lams_activity_category; -DROP TABLE IF EXISTS `lams_activity_category`; -CREATE TABLE `lams_activity_category` ( - `activity_category_id` int(3) NOT NULL, - `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`activity_category_id`) +CREATE TABLE lams_activity_category ( + activity_category_id int NOT NULL, + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (activity_category_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; --- --- Table structure for table `lams_activity_evaluation` --- +DROP TABLE IF EXISTS lams_activity_evaluation; -DROP TABLE IF EXISTS `lams_activity_evaluation`; -CREATE TABLE `lams_activity_evaluation` ( - `activity_id` bigint(20) NOT NULL, - `tool_output_definition` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `weight` tinyint(4) DEFAULT NULL, - PRIMARY KEY (`activity_id`), - KEY `activity_id` (`activity_id`), - CONSTRAINT `FK_lams_activity_evaluation_1` FOREIGN KEY (`activity_id`) REFERENCES `lams_learning_activity` (`activity_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_activity_evaluation ( + activity_id bigint NOT NULL, + tool_output_definition varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + weight tinyint DEFAULT NULL, + PRIMARY KEY (activity_id), + KEY activity_id (activity_id), + CONSTRAINT FK_lams_activity_evaluation_1 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; --- --- Table structure for table `lams_auth_method_type` --- +DROP TABLE IF EXISTS lams_auth_method_type; -DROP TABLE IF EXISTS `lams_auth_method_type`; -CREATE TABLE `lams_auth_method_type` ( - `authentication_method_type_id` int(3) NOT NULL, - `description` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`authentication_method_type_id`) +CREATE TABLE lams_auth_method_type ( + authentication_method_type_id int NOT NULL, + `description` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (authentication_method_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_authentication_method; --- --- Table structure for table `lams_authentication_method` --- -DROP TABLE IF EXISTS `lams_authentication_method`; - -CREATE TABLE `lams_authentication_method` ( - `authentication_method_id` bigint(20) NOT NULL, - `authentication_method_type_id` int(3) NOT NULL DEFAULT '0', - `authentication_method_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`authentication_method_id`), - UNIQUE KEY `UQ_lams_authentication_method_1` (`authentication_method_name`), - KEY `authentication_method_type_id` (`authentication_method_type_id`), - CONSTRAINT `FK_lams_authorization_method_1` FOREIGN KEY (`authentication_method_type_id`) REFERENCES `lams_auth_method_type` (`authentication_method_type_id`) +CREATE TABLE lams_authentication_method ( + authentication_method_id bigint NOT NULL, + authentication_method_type_id int NOT NULL DEFAULT '0', + authentication_method_name varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (authentication_method_id), + UNIQUE KEY UQ_lams_authentication_method_1 (authentication_method_name), + KEY authentication_method_type_id (authentication_method_type_id), + CONSTRAINT FK_lams_authorization_method_1 FOREIGN KEY (authentication_method_type_id) REFERENCES lams_auth_method_type (authentication_method_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; --- --- Table structure for table `lams_branch_activity_entry` --- +DROP TABLE IF EXISTS lams_branch_activity_entry; -DROP TABLE IF EXISTS `lams_branch_activity_entry`; -CREATE TABLE `lams_branch_activity_entry` ( - `entry_id` bigint(20) NOT NULL AUTO_INCREMENT, - `entry_ui_id` int(11) DEFAULT NULL, - `group_id` bigint(20) DEFAULT NULL, - `sequence_activity_id` bigint(20) DEFAULT NULL, - `branch_activity_id` bigint(20) NOT NULL, - `condition_id` bigint(20) DEFAULT NULL, - `open_gate` tinyint(1) DEFAULT NULL, - PRIMARY KEY (`entry_id`), - KEY `group_id` (`group_id`), - KEY `sequence_activity_id` (`sequence_activity_id`), - KEY `branch_activity_id` (`branch_activity_id`), - KEY `condition_id` (`condition_id`), - CONSTRAINT `FK_lams_branch_activity_entry_4` FOREIGN KEY (`condition_id`) REFERENCES `lams_branch_condition` (`condition_id`), - CONSTRAINT `FK_lams_branch_map_branch` FOREIGN KEY (`branch_activity_id`) REFERENCES `lams_learning_activity` (`activity_id`), - CONSTRAINT `FK_lams_branch_map_sequence` FOREIGN KEY (`sequence_activity_id`) REFERENCES `lams_learning_activity` (`activity_id`), - CONSTRAINT `FK_lams_group_activity_1` FOREIGN KEY (`group_id`) REFERENCES `lams_group` (`group_id`) +CREATE TABLE lams_branch_activity_entry ( + entry_id bigint NOT NULL AUTO_INCREMENT, + entry_ui_id int DEFAULT NULL, + group_id bigint DEFAULT NULL, + sequence_activity_id bigint DEFAULT NULL, + branch_activity_id bigint NOT NULL, + condition_id bigint DEFAULT NULL, + open_gate tinyint(1) DEFAULT NULL, + PRIMARY KEY (entry_id), + KEY group_id (group_id), + KEY sequence_activity_id (sequence_activity_id), + KEY branch_activity_id (branch_activity_id), + KEY condition_id (condition_id), + CONSTRAINT FK_lams_branch_activity_entry_4 FOREIGN KEY (condition_id) REFERENCES lams_branch_condition (condition_id), + CONSTRAINT FK_lams_branch_map_branch FOREIGN KEY (branch_activity_id) REFERENCES lams_learning_activity (activity_id), + CONSTRAINT FK_lams_branch_map_sequence FOREIGN KEY (sequence_activity_id) REFERENCES lams_learning_activity (activity_id), + CONSTRAINT FK_lams_group_activity_1 FOREIGN KEY (group_id) REFERENCES lams_group (group_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_branch_condition; --- --- Table structure for table `lams_branch_condition` --- -DROP TABLE IF EXISTS `lams_branch_condition`; - -CREATE TABLE `lams_branch_condition` ( - `condition_id` bigint(20) NOT NULL AUTO_INCREMENT, - `condition_ui_id` int(11) DEFAULT NULL, - `order_id` int(11) DEFAULT NULL, - `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `display_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `type` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL, - `start_value` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `end_value` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `exact_match_value` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - PRIMARY KEY (`condition_id`) +CREATE TABLE lams_branch_condition ( + condition_id bigint NOT NULL AUTO_INCREMENT, + condition_ui_id int DEFAULT NULL, + order_id int DEFAULT NULL, + `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + display_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `type` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + start_value varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + end_value varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + exact_match_value varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (condition_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_comment; --- --- Table structure for table `lams_comment` --- -DROP TABLE IF EXISTS `lams_comment`; - -CREATE TABLE `lams_comment` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `session_id` bigint(20) NOT NULL, - `body` mediumtext COLLATE utf8mb4_unicode_ci, - `create_by` bigint(20) DEFAULT NULL, - `create_date` datetime DEFAULT NULL, - `update_date` datetime DEFAULT NULL, - `update_by` bigint(20) DEFAULT NULL, - `last_modified` datetime DEFAULT NULL, - `last_reply_date` datetime DEFAULT NULL, - `reply_number` int(11) DEFAULT NULL, - `hide_flag` smallint(6) DEFAULT NULL, - `parent_uid` bigint(20) DEFAULT NULL, - `root_comment_uid` bigint(20) DEFAULT NULL, - `comment_level` smallint(6) DEFAULT NULL, - `thread_comment_uid` bigint(20) DEFAULT NULL, - `sticky` smallint(6) DEFAULT '0', - `monitor` smallint(6) DEFAULT '0', - `anonymous` smallint(6) DEFAULT '0', - PRIMARY KEY (`uid`), - KEY `FK_comment_session` (`session_id`), - KEY `FK_comment_create` (`create_by`), - KEY `FK_comment_modify` (`update_by`), - KEY `FK_comment_parent` (`parent_uid`), - KEY `FK_comment_root` (`root_comment_uid`), - KEY `FK_comment_thread` (`thread_comment_uid`), - KEY `IX_comment_level_sticky` (`comment_level`,`sticky`), - CONSTRAINT `FK_comment_create` FOREIGN KEY (`create_by`) REFERENCES `lams_user` (`user_id`), - CONSTRAINT `FK_comment_modify` FOREIGN KEY (`update_by`) REFERENCES `lams_user` (`user_id`), - CONSTRAINT `FK_comment_parent` FOREIGN KEY (`parent_uid`) REFERENCES `lams_comment` (`uid`), - CONSTRAINT `FK_comment_root` FOREIGN KEY (`root_comment_uid`) REFERENCES `lams_comment` (`uid`), - CONSTRAINT `FK_comment_session` FOREIGN KEY (`session_id`) REFERENCES `lams_comment_session` (`uid`), - CONSTRAINT `FK_comment_thread` FOREIGN KEY (`thread_comment_uid`) REFERENCES `lams_comment` (`uid`) +CREATE TABLE lams_comment ( + uid bigint NOT NULL AUTO_INCREMENT, + session_id bigint NOT NULL, + body mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + create_by bigint DEFAULT NULL, + create_date datetime DEFAULT NULL, + update_date datetime DEFAULT NULL, + update_by bigint DEFAULT NULL, + last_modified datetime DEFAULT NULL, + last_reply_date datetime DEFAULT NULL, + reply_number int DEFAULT NULL, + hide_flag smallint DEFAULT NULL, + parent_uid bigint DEFAULT NULL, + root_comment_uid bigint DEFAULT NULL, + comment_level smallint DEFAULT NULL, + thread_comment_uid bigint DEFAULT NULL, + sticky smallint DEFAULT '0', + monitor smallint DEFAULT '0', + anonymous smallint DEFAULT '0', + PRIMARY KEY (uid), + KEY FK_comment_session (session_id), + KEY FK_comment_create (create_by), + KEY FK_comment_modify (update_by), + KEY FK_comment_parent (parent_uid), + KEY FK_comment_root (root_comment_uid), + KEY FK_comment_thread (thread_comment_uid), + KEY IX_comment_level_sticky (comment_level,sticky), + CONSTRAINT FK_comment_create FOREIGN KEY (create_by) REFERENCES lams_user (user_id), + CONSTRAINT FK_comment_modify FOREIGN KEY (update_by) REFERENCES lams_user (user_id), + CONSTRAINT FK_comment_parent FOREIGN KEY (parent_uid) REFERENCES lams_comment (uid), + CONSTRAINT FK_comment_root FOREIGN KEY (root_comment_uid) REFERENCES lams_comment (uid), + CONSTRAINT FK_comment_session FOREIGN KEY (session_id) REFERENCES lams_comment_session (uid), + CONSTRAINT FK_comment_thread FOREIGN KEY (thread_comment_uid) REFERENCES lams_comment (uid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_comment_likes; --- --- Table structure for table `lams_comment_likes` --- -DROP TABLE IF EXISTS `lams_comment_likes`; - -CREATE TABLE `lams_comment_likes` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `comment_uid` bigint(20) NOT NULL, - `user_id` bigint(20) NOT NULL, - `vote` tinyint(1) DEFAULT NULL, - PRIMARY KEY (`uid`), - UNIQUE KEY `comment_like_unique` (`comment_uid`,`user_id`), - KEY `FK_commentlike_comment` (`comment_uid`), - KEY `FK_commentlike_user` (`user_id`), - CONSTRAINT `FK_commentlike_comment` FOREIGN KEY (`comment_uid`) REFERENCES `lams_comment` (`uid`), - CONSTRAINT `FK_commentlike_user` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) +CREATE TABLE lams_comment_likes ( + uid bigint NOT NULL AUTO_INCREMENT, + comment_uid bigint NOT NULL, + user_id bigint NOT NULL, + vote tinyint(1) DEFAULT NULL, + PRIMARY KEY (uid), + UNIQUE KEY comment_like_unique (comment_uid,user_id), + KEY FK_commentlike_comment (comment_uid), + KEY FK_commentlike_user (user_id), + CONSTRAINT FK_commentlike_comment FOREIGN KEY (comment_uid) REFERENCES lams_comment (uid), + CONSTRAINT FK_commentlike_user FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_comment_session; --- --- Table structure for table `lams_comment_session` --- -DROP TABLE IF EXISTS `lams_comment_session`; - -CREATE TABLE `lams_comment_session` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `external_id` bigint(20) DEFAULT NULL, - `external_id_type` int(1) DEFAULT NULL, - `external_signature` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `external_secondary_id` bigint(20) DEFAULT NULL, - PRIMARY KEY (`uid`), - KEY `comment_ext_sig` (`external_id`,`external_signature`) +CREATE TABLE lams_comment_session ( + uid bigint NOT NULL AUTO_INCREMENT, + external_id bigint DEFAULT NULL, + external_id_type int DEFAULT NULL, + external_signature varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + external_secondary_id bigint DEFAULT NULL, + PRIMARY KEY (uid), + KEY comment_ext_sig (external_id,external_signature) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_competence; --- --- Table structure for table `lams_competence` --- -DROP TABLE IF EXISTS `lams_competence`; - -CREATE TABLE `lams_competence` ( - `competence_id` bigint(20) NOT NULL AUTO_INCREMENT, - `learning_design_id` bigint(20) DEFAULT NULL, - `description` mediumtext COLLATE utf8mb4_unicode_ci, - `title` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - PRIMARY KEY (`competence_id`), - UNIQUE KEY `competence_id` (`competence_id`), - UNIQUE KEY `learning_design_id` (`learning_design_id`,`title`), - CONSTRAINT `LearningDesignCompetenceMap` FOREIGN KEY (`learning_design_id`) REFERENCES `lams_learning_design` (`learning_design_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_competence ( + competence_id bigint NOT NULL AUTO_INCREMENT, + learning_design_id bigint DEFAULT NULL, + `description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + title varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (competence_id), + UNIQUE KEY competence_id (competence_id), + UNIQUE KEY learning_design_id (learning_design_id,title), + CONSTRAINT LearningDesignCompetenceMap FOREIGN KEY (learning_design_id) REFERENCES lams_learning_design (learning_design_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_competence_mapping; --- --- Table structure for table `lams_competence_mapping` --- -DROP TABLE IF EXISTS `lams_competence_mapping`; - -CREATE TABLE `lams_competence_mapping` ( - `competence_mapping_id` bigint(20) NOT NULL AUTO_INCREMENT, - `competence_id` bigint(20) DEFAULT NULL, - `activity_id` bigint(20) DEFAULT NULL, - PRIMARY KEY (`competence_mapping_id`), - UNIQUE KEY `competence_mapping_id` (`competence_mapping_id`), - UNIQUE KEY `competence_id` (`competence_id`,`activity_id`), - KEY `activity_id` (`activity_id`), - CONSTRAINT `FK_lams_competence_mapping_1` FOREIGN KEY (`activity_id`) REFERENCES `lams_learning_activity` (`activity_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_competence_mapping_2` FOREIGN KEY (`competence_id`) REFERENCES `lams_competence` (`competence_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_competence_mapping ( + competence_mapping_id bigint NOT NULL AUTO_INCREMENT, + competence_id bigint DEFAULT NULL, + activity_id bigint DEFAULT NULL, + PRIMARY KEY (competence_mapping_id), + UNIQUE KEY competence_mapping_id (competence_mapping_id), + UNIQUE KEY competence_id (competence_id,activity_id), + KEY activity_id (activity_id), + CONSTRAINT FK_lams_competence_mapping_1 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_competence_mapping_2 FOREIGN KEY (competence_id) REFERENCES lams_competence (competence_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_configuration; --- --- Table structure for table `lams_configuration` --- -DROP TABLE IF EXISTS `lams_configuration`; - -CREATE TABLE `lams_configuration` ( - `config_key` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL, - `config_value` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `description_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `header_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `format` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `required` tinyint(1) NOT NULL DEFAULT '0', - PRIMARY KEY (`config_key`) +CREATE TABLE lams_configuration ( + config_key varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + config_value varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + description_key varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + header_name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `format` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + required tinyint(1) NOT NULL DEFAULT '0', + PRIMARY KEY (config_key) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_copy_type; --- --- Table structure for table `lams_copy_type` --- -DROP TABLE IF EXISTS `lams_copy_type`; - -CREATE TABLE `lams_copy_type` ( - `copy_type_id` tinyint(4) NOT NULL, - `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`copy_type_id`) +CREATE TABLE lams_copy_type ( + copy_type_id tinyint NOT NULL, + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (copy_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_cr_credential; --- --- Table structure for table `lams_cr_credential` --- -DROP TABLE IF EXISTS `lams_cr_credential`; - -CREATE TABLE `lams_cr_credential` ( - `credential_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, - `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`credential_id`) +CREATE TABLE lams_cr_credential ( + credential_id bigint unsigned NOT NULL AUTO_INCREMENT, + `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (credential_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_cr_node; --- --- Table structure for table `lams_cr_node` --- -DROP TABLE IF EXISTS `lams_cr_node`; - -CREATE TABLE `lams_cr_node` ( - `node_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, - `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`), - KEY `workspace_id` (`workspace_id`), - CONSTRAINT `FK_lams_cr_node_1` FOREIGN KEY (`workspace_id`) REFERENCES `lams_cr_workspace` (`workspace_id`) +CREATE TABLE lams_cr_node ( + node_id bigint unsigned NOT NULL AUTO_INCREMENT, + workspace_id bigint unsigned NOT NULL, + `path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + created_date_time datetime NOT NULL, + next_version_id bigint unsigned NOT NULL DEFAULT '1', + parent_nv_id bigint unsigned DEFAULT NULL, + PRIMARY KEY (node_id), + 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; +DROP TABLE IF EXISTS lams_cr_node_version; --- --- Table structure for table `lams_cr_node_version` --- -DROP TABLE IF EXISTS `lams_cr_node_version`; - -CREATE TABLE `lams_cr_node_version` ( - `nv_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, - `node_id` bigint(20) unsigned NOT NULL, - `version_id` bigint(20) unsigned NOT NULL, - `created_date_time` datetime NOT NULL, - `user_id` bigint(20) NOT NULL, - PRIMARY KEY (`nv_id`), - KEY `node_id` (`node_id`), - CONSTRAINT `FK_lams_cr_node_version_2` FOREIGN KEY (`node_id`) REFERENCES `lams_cr_node` (`node_id`) +CREATE TABLE lams_cr_node_version ( + nv_id bigint unsigned NOT NULL AUTO_INCREMENT, + node_id bigint unsigned NOT NULL, + version_id bigint unsigned NOT NULL, + created_date_time datetime NOT NULL, + user_id bigint NOT NULL, + PRIMARY KEY (nv_id), + KEY node_id (node_id), + CONSTRAINT FK_lams_cr_node_version_2 FOREIGN KEY (node_id) REFERENCES lams_cr_node (node_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_cr_node_version_property; --- --- Table structure for table `lams_cr_node_version_property` --- -DROP TABLE IF EXISTS `lams_cr_node_version_property`; - -CREATE TABLE `lams_cr_node_version_property` ( - `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, - `nv_id` bigint(20) unsigned NOT NULL, - `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `value` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `type` tinyint(4) NOT NULL, - PRIMARY KEY (`id`), - KEY `nv_id` (`nv_id`), - CONSTRAINT `FK_lams_cr_node_version_property_1` FOREIGN KEY (`nv_id`) REFERENCES `lams_cr_node_version` (`nv_id`) +CREATE TABLE lams_cr_node_version_property ( + id bigint unsigned NOT NULL AUTO_INCREMENT, + nv_id bigint unsigned NOT NULL, + `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `type` tinyint NOT NULL, + PRIMARY KEY (id), + KEY nv_id (nv_id), + CONSTRAINT FK_lams_cr_node_version_property_1 FOREIGN KEY (nv_id) REFERENCES lams_cr_node_version (nv_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_cr_workspace; --- --- Table structure for table `lams_cr_workspace` --- -DROP TABLE IF EXISTS `lams_cr_workspace`; - -CREATE TABLE `lams_cr_workspace` ( - `workspace_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, - `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`workspace_id`) +CREATE TABLE lams_cr_workspace ( + workspace_id bigint unsigned NOT NULL AUTO_INCREMENT, + `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (workspace_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_cr_workspace_credential; --- --- Table structure for table `lams_cr_workspace_credential` --- -DROP TABLE IF EXISTS `lams_cr_workspace_credential`; - -CREATE TABLE `lams_cr_workspace_credential` ( - `wc_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, - `workspace_id` bigint(20) unsigned NOT NULL, - `credential_id` bigint(20) unsigned NOT NULL, - PRIMARY KEY (`wc_id`), - KEY `workspace_id` (`workspace_id`), - KEY `credential_id` (`credential_id`), - CONSTRAINT `FK_lams_cr_workspace_credential_1` FOREIGN KEY (`workspace_id`) REFERENCES `lams_cr_workspace` (`workspace_id`), - CONSTRAINT `FK_lams_cr_workspace_credential_2` FOREIGN KEY (`credential_id`) REFERENCES `lams_cr_credential` (`credential_id`) +CREATE TABLE lams_cr_workspace_credential ( + wc_id bigint unsigned NOT NULL AUTO_INCREMENT, + workspace_id bigint unsigned NOT NULL, + credential_id bigint unsigned NOT NULL, + PRIMARY KEY (wc_id), + KEY workspace_id (workspace_id), + KEY credential_id (credential_id), + CONSTRAINT FK_lams_cr_workspace_credential_1 FOREIGN KEY (workspace_id) REFERENCES lams_cr_workspace (workspace_id), + CONSTRAINT FK_lams_cr_workspace_credential_2 FOREIGN KEY (credential_id) REFERENCES lams_cr_credential (credential_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_data_flow; --- --- Table structure for table `lams_data_flow` --- -DROP TABLE IF EXISTS `lams_data_flow`; - -CREATE TABLE `lams_data_flow` ( - `data_flow_object_id` bigint(20) NOT NULL AUTO_INCREMENT, - `transition_id` bigint(20) NOT NULL, - `order_id` int(11) DEFAULT NULL, - `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `display_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `tool_assigment_id` int(11) DEFAULT NULL, - PRIMARY KEY (`data_flow_object_id`), - KEY `FK_lams_learning_transition_1` (`transition_id`), - CONSTRAINT `FK_lams_learning_transition_1` FOREIGN KEY (`transition_id`) REFERENCES `lams_learning_transition` (`transition_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_data_flow ( + data_flow_object_id bigint NOT NULL AUTO_INCREMENT, + transition_id bigint NOT NULL, + order_id int DEFAULT NULL, + `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + display_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + tool_assigment_id int DEFAULT NULL, + PRIMARY KEY (data_flow_object_id), + KEY FK_lams_learning_transition_1 (transition_id), + CONSTRAINT FK_lams_learning_transition_1 FOREIGN KEY (transition_id) REFERENCES lams_learning_transition (transition_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_email_notification_archive; --- --- Table structure for table `lams_email_notification_archive` --- -DROP TABLE IF EXISTS `lams_email_notification_archive`; - -CREATE TABLE `lams_email_notification_archive` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `organisation_id` bigint(20) DEFAULT NULL, - `lesson_id` bigint(20) DEFAULT NULL, - `search_type` tinyint(4) NOT NULL, - `sent_on` datetime NOT NULL, - `body` mediumtext COLLATE utf8mb4_unicode_ci, - PRIMARY KEY (`uid`), - KEY `FK_lams_email_notification_archive_1` (`organisation_id`), - KEY `FK_lams_email_notification_archive_2` (`lesson_id`), - CONSTRAINT `FK_lams_email_notification_archive_1` FOREIGN KEY (`organisation_id`) REFERENCES `lams_organisation` (`organisation_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_email_notification_archive_2` FOREIGN KEY (`lesson_id`) REFERENCES `lams_lesson` (`lesson_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_email_notification_archive ( + uid bigint NOT NULL AUTO_INCREMENT, + organisation_id bigint DEFAULT NULL, + lesson_id bigint DEFAULT NULL, + search_type tinyint NOT NULL, + sent_on datetime NOT NULL, + body mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + PRIMARY KEY (uid), + KEY FK_lams_email_notification_archive_1 (organisation_id), + KEY FK_lams_email_notification_archive_2 (lesson_id), + CONSTRAINT FK_lams_email_notification_archive_1 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_email_notification_archive_2 FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_email_notification_recipient_archive; --- --- Table structure for table `lams_email_notification_recipient_archive` --- -DROP TABLE IF EXISTS `lams_email_notification_recipient_archive`; - -CREATE TABLE `lams_email_notification_recipient_archive` ( - `email_notification_uid` bigint(20) NOT NULL, - `user_id` bigint(20) NOT NULL, - KEY `FK_lams_email_notification_recipient_archive_1` (`email_notification_uid`), - KEY `FK_lams_email_notification_recipient_archive_2` (`user_id`), - CONSTRAINT `FK_lams_email_notification_recipient_archive_1` FOREIGN KEY (`email_notification_uid`) REFERENCES `lams_email_notification_archive` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_email_notification_recipient_archive_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_email_notification_recipient_archive ( + email_notification_uid bigint NOT NULL, + user_id bigint NOT NULL, + KEY FK_lams_email_notification_recipient_archive_1 (email_notification_uid), + KEY FK_lams_email_notification_recipient_archive_2 (user_id), + CONSTRAINT FK_lams_email_notification_recipient_archive_1 FOREIGN KEY (email_notification_uid) REFERENCES lams_email_notification_archive (uid) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_email_notification_recipient_archive_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_ext_course_class_map; --- --- Table structure for table `lams_ext_course_class_map` --- -DROP TABLE IF EXISTS `lams_ext_course_class_map`; - -CREATE TABLE `lams_ext_course_class_map` ( - `sid` int(11) NOT NULL AUTO_INCREMENT, - `courseid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `classid` bigint(20) NOT NULL, - `ext_server_org_map_id` int(11) NOT NULL, - PRIMARY KEY (`sid`), - KEY `classid` (`classid`), - KEY `ext_server_org_map_id` (`ext_server_org_map_id`), - CONSTRAINT `lams_ext_course_class_map_fk` FOREIGN KEY (`classid`) REFERENCES `lams_organisation` (`organisation_id`), - CONSTRAINT `lams_ext_course_class_map_fk1` FOREIGN KEY (`ext_server_org_map_id`) REFERENCES `lams_ext_server_org_map` (`sid`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_ext_course_class_map ( + sid int NOT NULL AUTO_INCREMENT, + courseid varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + classid bigint NOT NULL, + ext_server_org_map_id int NOT NULL, + PRIMARY KEY (sid), + KEY classid (classid), + KEY ext_server_org_map_id (ext_server_org_map_id), + CONSTRAINT lams_ext_course_class_map_fk FOREIGN KEY (classid) REFERENCES lams_organisation (organisation_id), + CONSTRAINT lams_ext_course_class_map_fk1 FOREIGN KEY (ext_server_org_map_id) REFERENCES lams_ext_server_org_map (sid) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_ext_server_lesson_map; --- --- Table structure for table `lams_ext_server_lesson_map` --- -DROP TABLE IF EXISTS `lams_ext_server_lesson_map`; - -CREATE TABLE `lams_ext_server_lesson_map` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `lesson_id` bigint(20) NOT NULL, - `ext_server_org_map_id` int(11) NOT NULL, - `resource_link_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - PRIMARY KEY (`uid`), - UNIQUE KEY `lesson_id` (`lesson_id`), - KEY `lams_ext_server_lesson_map_fk1` (`ext_server_org_map_id`), - CONSTRAINT `lams_ext_server_lesson_map_fk1` FOREIGN KEY (`ext_server_org_map_id`) REFERENCES `lams_ext_server_org_map` (`sid`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `lams_ext_server_lesson_map_fk2` FOREIGN KEY (`lesson_id`) REFERENCES `lams_lesson` (`lesson_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_ext_server_lesson_map ( + uid bigint NOT NULL AUTO_INCREMENT, + lesson_id bigint NOT NULL, + ext_server_org_map_id int NOT NULL, + resource_link_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (uid), + UNIQUE KEY lesson_id (lesson_id), + KEY lams_ext_server_lesson_map_fk1 (ext_server_org_map_id), + CONSTRAINT lams_ext_server_lesson_map_fk1 FOREIGN KEY (ext_server_org_map_id) REFERENCES lams_ext_server_org_map (sid) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT lams_ext_server_lesson_map_fk2 FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_ext_server_org_map; --- --- Table structure for table `lams_ext_server_org_map` --- -DROP TABLE IF EXISTS `lams_ext_server_org_map`; - -CREATE TABLE `lams_ext_server_org_map` ( - `sid` int(11) NOT NULL AUTO_INCREMENT, - `serverid` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, - `serverkey` text COLLATE utf8mb4_unicode_ci NOT NULL, - `servername` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `serverdesc` mediumtext COLLATE utf8mb4_unicode_ci, - `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, - `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', - `ext_groups_url` text COLLATE utf8mb4_unicode_ci, - `server_type_id` int(11) NOT NULL DEFAULT '1', - `lti_consumer_monitor_roles` text COLLATE utf8mb4_unicode_ci, - `learner_presence_avail` tinyint(1) DEFAULT '0', - `learner_im_avail` tinyint(1) DEFAULT '0', - `live_edit_enabled` tinyint(1) DEFAULT '1', - `enable_lesson_notifications` tinyint(1) DEFAULT '1', - `force_restart` tinyint(1) DEFAULT '0', - `allow_restart` tinyint(1) DEFAULT '0', - `gradebook_on_complete` tinyint(1) DEFAULT '1', - PRIMARY KEY (`sid`), - UNIQUE KEY `serverid` (`serverid`), - UNIQUE KEY `prefix` (`prefix`), - KEY `FK_lams_ext_server_type` (`server_type_id`), - CONSTRAINT `FK_lams_ext_server_type` FOREIGN KEY (`server_type_id`) REFERENCES `lams_ext_server_type` (`server_type_id`) +CREATE TABLE lams_ext_server_org_map ( + sid int NOT NULL AUTO_INCREMENT, + serverid varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + serverkey text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + servername varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + serverdesc mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + prefix varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + userinfo_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + lesson_finish_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + logout_url text COLLATE utf8mb4_unicode_ci, + disabled bit(1) NOT NULL, + time_to_live_login_request int DEFAULT '80', + time_to_live_login_request_enabled tinyint(1) NOT NULL DEFAULT '0', + ext_groups_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + server_type_id int NOT NULL DEFAULT '1', + lti_consumer_monitor_roles text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + learner_presence_avail tinyint(1) DEFAULT '0', + learner_im_avail tinyint(1) DEFAULT '0', + live_edit_enabled tinyint(1) DEFAULT '1', + enable_lesson_notifications tinyint(1) DEFAULT '1', + force_restart tinyint(1) DEFAULT '0', + allow_restart tinyint(1) DEFAULT '0', + gradebook_on_complete tinyint(1) DEFAULT '1', + membership_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + user_id_parameter_name varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (sid), + UNIQUE KEY serverid (serverid), + UNIQUE KEY prefix (prefix), + KEY FK_lams_ext_server_type (server_type_id), + CONSTRAINT FK_lams_ext_server_type FOREIGN KEY (server_type_id) REFERENCES lams_ext_server_type (server_type_id) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_ext_server_tool_map; --- --- Table structure for table `lams_ext_server_tool_map` --- -DROP TABLE IF EXISTS `lams_ext_server_tool_map`; - -CREATE TABLE `lams_ext_server_tool_map` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `tool_id` bigint(20) NOT NULL, - `ext_server_org_map_id` int(11) NOT NULL, - PRIMARY KEY (`uid`), - UNIQUE KEY `unique_adapter_map` (`ext_server_org_map_id`,`tool_id`), - KEY `lams_ext_server_tool_map_fk2` (`tool_id`), - CONSTRAINT `lams_ext_server_tool_map_fk1` FOREIGN KEY (`ext_server_org_map_id`) REFERENCES `lams_ext_server_org_map` (`sid`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `lams_ext_server_tool_map_fk2` FOREIGN KEY (`tool_id`) REFERENCES `lams_tool` (`tool_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_ext_server_tool_map ( + uid bigint NOT NULL AUTO_INCREMENT, + tool_id bigint NOT NULL, + ext_server_org_map_id int NOT NULL, + PRIMARY KEY (uid), + UNIQUE KEY unique_adapter_map (ext_server_org_map_id,tool_id), + KEY lams_ext_server_tool_map_fk2 (tool_id), + CONSTRAINT lams_ext_server_tool_map_fk1 FOREIGN KEY (ext_server_org_map_id) REFERENCES lams_ext_server_org_map (sid) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT lams_ext_server_tool_map_fk2 FOREIGN KEY (tool_id) REFERENCES lams_tool (tool_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_ext_server_type; --- --- Table structure for table `lams_ext_server_type` --- -DROP TABLE IF EXISTS `lams_ext_server_type`; - -CREATE TABLE `lams_ext_server_type` ( - `server_type_id` int(11) NOT NULL, - `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`server_type_id`) +CREATE TABLE lams_ext_server_type ( + server_type_id int NOT NULL, + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (server_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_ext_user_userid_map; --- --- Table structure for table `lams_ext_user_userid_map` --- -DROP TABLE IF EXISTS `lams_ext_user_userid_map`; - -CREATE TABLE `lams_ext_user_userid_map` ( - `sid` int(11) NOT NULL AUTO_INCREMENT, - `external_username` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL, - `user_id` bigint(20) NOT NULL, - `ext_server_org_map_id` int(11) NOT NULL, - `tc_gradebook_id` text COLLATE utf8mb4_unicode_ci, - PRIMARY KEY (`sid`), - KEY `user_id` (`user_id`), - KEY `ext_server_org_map_id` (`ext_server_org_map_id`), - CONSTRAINT `lams_ext_user_userid_map_fk` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `lams_ext_user_userid_map_fk1` FOREIGN KEY (`ext_server_org_map_id`) REFERENCES `lams_ext_server_org_map` (`sid`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_ext_user_userid_map ( + sid int NOT NULL AUTO_INCREMENT, + external_username varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + user_id bigint NOT NULL, + ext_server_org_map_id int NOT NULL, + tc_gradebook_id text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + PRIMARY KEY (sid), + KEY user_id (user_id), + KEY ext_server_org_map_id (ext_server_org_map_id), + CONSTRAINT lams_ext_user_userid_map_fk FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT lams_ext_user_userid_map_fk1 FOREIGN KEY (ext_server_org_map_id) REFERENCES lams_ext_server_org_map (sid) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_favorite_organisation; --- --- Table structure for table `lams_favorite_organisation` --- -DROP TABLE IF EXISTS `lams_favorite_organisation`; - -CREATE TABLE `lams_favorite_organisation` ( - `favorite_organisation_id` bigint(20) NOT NULL AUTO_INCREMENT, - `organisation_id` bigint(20) NOT NULL, - `user_id` bigint(20) NOT NULL, - PRIMARY KEY (`favorite_organisation_id`), - KEY `organisation_id` (`organisation_id`), - KEY `user_id` (`user_id`), - CONSTRAINT `FK_lams_favorite_organisation_1` FOREIGN KEY (`organisation_id`) REFERENCES `lams_organisation` (`organisation_id`), - CONSTRAINT `FK_lams_favorite_organisation_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) +CREATE TABLE lams_favorite_organisation ( + favorite_organisation_id bigint NOT NULL AUTO_INCREMENT, + organisation_id bigint NOT NULL, + user_id bigint NOT NULL, + PRIMARY KEY (favorite_organisation_id), + KEY organisation_id (organisation_id), + KEY user_id (user_id), + CONSTRAINT FK_lams_favorite_organisation_1 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id), + CONSTRAINT FK_lams_favorite_organisation_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_gate_activity_level; --- --- Table structure for table `lams_gate_activity_level` --- -DROP TABLE IF EXISTS `lams_gate_activity_level`; - -CREATE TABLE `lams_gate_activity_level` ( - `gate_activity_level_id` int(11) NOT NULL, - `description` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`gate_activity_level_id`) +CREATE TABLE lams_gate_activity_level ( + gate_activity_level_id int NOT NULL, + `description` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (gate_activity_level_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_gate_allowed_learners; --- --- Table structure for table `lams_gate_allowed_learners` --- -DROP TABLE IF EXISTS `lams_gate_allowed_learners`; - -CREATE TABLE `lams_gate_allowed_learners` ( - `user_id` bigint(20) NOT NULL, - `activity_id` bigint(20) NOT NULL, - KEY `user_id` (`user_id`), - KEY `activity_id` (`activity_id`), - CONSTRAINT `FK_TABLE_32_1` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`), - CONSTRAINT `FK_TABLE_32_2` FOREIGN KEY (`activity_id`) REFERENCES `lams_learning_activity` (`activity_id`) +CREATE TABLE lams_gate_allowed_learners ( + user_id bigint NOT NULL, + activity_id bigint NOT NULL, + KEY user_id (user_id), + KEY activity_id (activity_id), + CONSTRAINT FK_TABLE_32_1 FOREIGN KEY (user_id) REFERENCES lams_user (user_id), + CONSTRAINT FK_TABLE_32_2 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_gradebook_user_activity; --- --- Table structure for table `lams_gradebook_user_activity` --- -DROP TABLE IF EXISTS `lams_gradebook_user_activity`; - -CREATE TABLE `lams_gradebook_user_activity` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `activity_id` bigint(20) NOT NULL, - `user_id` bigint(20) NOT NULL, - `mark` double DEFAULT NULL, - `feedback` mediumtext COLLATE utf8mb4_unicode_ci, - `marked_in_gradebook` tinyint(1) NOT NULL DEFAULT '0', - `update_date` datetime DEFAULT NULL, - PRIMARY KEY (`uid`), - KEY `activity_id` (`activity_id`,`user_id`), - KEY `FK_lams_gradebook_user_activity_2` (`user_id`), - CONSTRAINT `FK_lams_gradebook_user_activity_1` FOREIGN KEY (`activity_id`) REFERENCES `lams_learning_activity` (`activity_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_gradebook_user_activity_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_gradebook_user_activity ( + uid bigint NOT NULL AUTO_INCREMENT, + activity_id bigint NOT NULL, + user_id bigint NOT NULL, + mark double DEFAULT NULL, + feedback mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + marked_in_gradebook tinyint(1) NOT NULL DEFAULT '0', + update_date datetime DEFAULT NULL, + PRIMARY KEY (uid), + KEY activity_id (activity_id,user_id), + KEY FK_lams_gradebook_user_activity_2 (user_id), + CONSTRAINT FK_lams_gradebook_user_activity_1 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_gradebook_user_activity_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_gradebook_user_activity_archive; --- --- Table structure for table `lams_gradebook_user_activity_archive` --- -DROP TABLE IF EXISTS `lams_gradebook_user_activity_archive`; - -CREATE TABLE `lams_gradebook_user_activity_archive` ( - `uid` bigint(20) NOT NULL, - `activity_id` bigint(20) NOT NULL, - `user_id` bigint(20) NOT NULL, - `mark` double DEFAULT NULL, - `feedback` mediumtext COLLATE utf8mb4_unicode_ci, - `marked_in_gradebook` tinyint(1) NOT NULL DEFAULT '0', - `update_date` datetime DEFAULT NULL, - `archive_date` datetime DEFAULT NULL, - PRIMARY KEY (`uid`), - KEY `activity_id` (`activity_id`,`user_id`), - KEY `FK_lams_gradebook_user_activity_archive_2` (`user_id`), - CONSTRAINT `FK_lams_gradebook_user_activity_archive_1` FOREIGN KEY (`activity_id`) REFERENCES `lams_learning_activity` (`activity_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_gradebook_user_activity_archive_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_gradebook_user_activity_archive ( + uid bigint NOT NULL, + activity_id bigint NOT NULL, + user_id bigint NOT NULL, + mark double DEFAULT NULL, + feedback mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + marked_in_gradebook tinyint(1) NOT NULL DEFAULT '0', + update_date datetime DEFAULT NULL, + archive_date datetime DEFAULT NULL, + PRIMARY KEY (uid), + KEY activity_id (activity_id,user_id), + KEY FK_lams_gradebook_user_activity_archive_2 (user_id), + CONSTRAINT FK_lams_gradebook_user_activity_archive_1 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_gradebook_user_activity_archive_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_gradebook_user_lesson; --- --- Table structure for table `lams_gradebook_user_lesson` --- -DROP TABLE IF EXISTS `lams_gradebook_user_lesson`; - -CREATE TABLE `lams_gradebook_user_lesson` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `lesson_id` bigint(20) NOT NULL, - `user_id` bigint(20) NOT NULL, - `mark` double DEFAULT NULL, - `feedback` text COLLATE utf8mb4_unicode_ci, - PRIMARY KEY (`uid`), - 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 +CREATE TABLE lams_gradebook_user_lesson ( + uid bigint NOT NULL AUTO_INCREMENT, + lesson_id bigint NOT NULL, + user_id bigint NOT NULL, + mark double DEFAULT NULL, + feedback text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + PRIMARY KEY (uid), + 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 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_gradebook_user_lesson_archive; --- --- Table structure for table `lams_gradebook_user_lesson_archive` --- -DROP TABLE IF EXISTS `lams_gradebook_user_lesson_archive`; - -CREATE TABLE `lams_gradebook_user_lesson_archive` ( - `uid` bigint(20) NOT NULL, - `lesson_id` bigint(20) NOT NULL, - `user_id` bigint(20) NOT NULL, - `mark` double DEFAULT NULL, - `feedback` mediumtext COLLATE utf8mb4_unicode_ci, - `archive_date` datetime DEFAULT NULL, - PRIMARY KEY (`uid`), - KEY `lesson_id` (`lesson_id`,`user_id`), - KEY `FK_lams_gradebook_user_lesson_archive_2` (`user_id`), - CONSTRAINT `FK_lams_gradebook_user_lesson_archive_1` FOREIGN KEY (`lesson_id`) REFERENCES `lams_lesson` (`lesson_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_gradebook_user_lesson_archive_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_gradebook_user_lesson_archive ( + uid bigint NOT NULL, + lesson_id bigint NOT NULL, + user_id bigint NOT NULL, + mark double DEFAULT NULL, + feedback mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + archive_date datetime DEFAULT NULL, + PRIMARY KEY (uid), + KEY lesson_id (lesson_id,user_id), + KEY FK_lams_gradebook_user_lesson_archive_2 (user_id), + CONSTRAINT FK_lams_gradebook_user_lesson_archive_1 FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_gradebook_user_lesson_archive_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_group; --- --- Table structure for table `lams_group` --- -DROP TABLE IF EXISTS `lams_group`; - -CREATE TABLE `lams_group` ( - `group_id` bigint(20) NOT NULL AUTO_INCREMENT, - `group_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `grouping_id` bigint(20) NOT NULL, - `order_id` int(6) NOT NULL DEFAULT '1', - `group_ui_id` int(11) DEFAULT NULL, - PRIMARY KEY (`group_id`), - UNIQUE KEY `UQ_lams_group_1` (`grouping_id`,`order_id`), - KEY `grouping_id` (`grouping_id`), - CONSTRAINT `FK_lams_learning_group_1` FOREIGN KEY (`grouping_id`) REFERENCES `lams_grouping` (`grouping_id`) +CREATE TABLE lams_group ( + group_id bigint NOT NULL AUTO_INCREMENT, + group_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + grouping_id bigint NOT NULL, + order_id int NOT NULL DEFAULT '1', + group_ui_id int DEFAULT NULL, + PRIMARY KEY (group_id), + UNIQUE KEY UQ_lams_group_1 (grouping_id,order_id), + KEY grouping_id (grouping_id), + CONSTRAINT FK_lams_learning_group_1 FOREIGN KEY (grouping_id) REFERENCES lams_grouping (grouping_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_grouping; --- --- Table structure for table `lams_grouping` --- -DROP TABLE IF EXISTS `lams_grouping`; - -CREATE TABLE `lams_grouping` ( - `grouping_id` bigint(20) NOT NULL AUTO_INCREMENT, - `grouping_ui_id` int(11) DEFAULT NULL, - `grouping_type_id` int(11) NOT NULL, - `number_of_groups` int(11) DEFAULT NULL, - `learners_per_group` int(11) DEFAULT NULL, - `staff_group_id` bigint(20) DEFAULT '0', - `max_number_of_groups` int(3) DEFAULT NULL, - `equal_number_of_learners_per_group` tinyint(1) DEFAULT '0', - `view_students_before_selection` tinyint(1) DEFAULT '0', - PRIMARY KEY (`grouping_id`), - KEY `grouping_type_id` (`grouping_type_id`), - CONSTRAINT `FK_lams_learning_grouping_1` FOREIGN KEY (`grouping_type_id`) REFERENCES `lams_grouping_type` (`grouping_type_id`) +CREATE TABLE lams_grouping ( + grouping_id bigint NOT NULL AUTO_INCREMENT, + grouping_ui_id int DEFAULT NULL, + grouping_type_id int NOT NULL, + number_of_groups int DEFAULT NULL, + learners_per_group int DEFAULT NULL, + staff_group_id bigint DEFAULT '0', + max_number_of_groups int DEFAULT NULL, + equal_number_of_learners_per_group tinyint(1) DEFAULT '0', + view_students_before_selection tinyint(1) DEFAULT '0', + PRIMARY KEY (grouping_id), + KEY grouping_type_id (grouping_type_id), + CONSTRAINT FK_lams_learning_grouping_1 FOREIGN KEY (grouping_type_id) REFERENCES lams_grouping_type (grouping_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_grouping_support_type; --- --- Table structure for table `lams_grouping_support_type` --- -DROP TABLE IF EXISTS `lams_grouping_support_type`; - -CREATE TABLE `lams_grouping_support_type` ( - `grouping_support_type_id` int(3) NOT NULL, - `description` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`grouping_support_type_id`) +CREATE TABLE lams_grouping_support_type ( + grouping_support_type_id int NOT NULL, + `description` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (grouping_support_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_grouping_type; --- --- Table structure for table `lams_grouping_type` --- -DROP TABLE IF EXISTS `lams_grouping_type`; - -CREATE TABLE `lams_grouping_type` ( - `grouping_type_id` int(11) NOT NULL, - `description` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`grouping_type_id`) +CREATE TABLE lams_grouping_type ( + grouping_type_id int NOT NULL, + `description` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (grouping_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_input_activity; --- --- Table structure for table `lams_input_activity` --- -DROP TABLE IF EXISTS `lams_input_activity`; - -CREATE TABLE `lams_input_activity` ( - `activity_id` bigint(20) NOT NULL, - `input_activity_id` bigint(20) NOT NULL, - UNIQUE KEY `UQ_lams_input_activity_1` (`activity_id`,`input_activity_id`), - KEY `activity_id` (`activity_id`), - CONSTRAINT `FK_lams_input_activity_1` FOREIGN KEY (`activity_id`) REFERENCES `lams_learning_activity` (`activity_id`), - CONSTRAINT `FK_lams_input_activity_2` FOREIGN KEY (`activity_id`) REFERENCES `lams_learning_activity` (`activity_id`) +CREATE TABLE lams_input_activity ( + activity_id bigint NOT NULL, + input_activity_id bigint NOT NULL, + UNIQUE KEY UQ_lams_input_activity_1 (activity_id,input_activity_id), + KEY activity_id (activity_id), + CONSTRAINT FK_lams_input_activity_1 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id), + CONSTRAINT FK_lams_input_activity_2 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_kumalive; --- --- Table structure for table `lams_kumalive` --- -DROP TABLE IF EXISTS `lams_kumalive`; - -CREATE TABLE `lams_kumalive` ( - `kumalive_id` bigint(20) NOT NULL AUTO_INCREMENT, - `organisation_id` bigint(20) NOT NULL, - `created_by` bigint(20) DEFAULT NULL, - `finished` tinyint(1) NOT NULL DEFAULT '0', - `name` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - PRIMARY KEY (`kumalive_id`), - KEY `FK_lams_kumalive_1` (`organisation_id`), - KEY `FK_lams_kumalive_2` (`created_by`), - CONSTRAINT `FK_lams_kumalive_1` FOREIGN KEY (`organisation_id`) REFERENCES `lams_organisation` (`organisation_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_kumalive_2` FOREIGN KEY (`created_by`) REFERENCES `lams_user` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE +CREATE TABLE lams_kumalive ( + kumalive_id bigint NOT NULL AUTO_INCREMENT, + organisation_id bigint NOT NULL, + created_by bigint DEFAULT NULL, + finished tinyint(1) NOT NULL DEFAULT '0', + `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (kumalive_id), + KEY FK_lams_kumalive_1 (organisation_id), + KEY FK_lams_kumalive_2 (created_by), + CONSTRAINT FK_lams_kumalive_1 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_kumalive_2 FOREIGN KEY (created_by) REFERENCES lams_user (user_id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_kumalive_log; --- --- Table structure for table `lams_kumalive_log` --- -DROP TABLE IF EXISTS `lams_kumalive_log`; - -CREATE TABLE `lams_kumalive_log` ( - `log_id` bigint(20) NOT NULL AUTO_INCREMENT, - `kumalive_id` bigint(20) NOT NULL, - `user_id` bigint(20) DEFAULT NULL, - `log_date` datetime NOT NULL, - `log_type` tinyint(4) DEFAULT NULL, - PRIMARY KEY (`log_id`), - KEY `FK_lams_kumalive_log_1` (`kumalive_id`), - KEY `FK_lams_kumalive_log_2` (`user_id`), - CONSTRAINT `FK_lams_kumalive_log_1` FOREIGN KEY (`kumalive_id`) REFERENCES `lams_kumalive` (`kumalive_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_kumalive_log_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE +CREATE TABLE lams_kumalive_log ( + log_id bigint NOT NULL AUTO_INCREMENT, + kumalive_id bigint NOT NULL, + user_id bigint DEFAULT NULL, + log_date datetime NOT NULL, + log_type tinyint DEFAULT NULL, + PRIMARY KEY (log_id), + KEY FK_lams_kumalive_log_1 (kumalive_id), + KEY FK_lams_kumalive_log_2 (user_id), + CONSTRAINT FK_lams_kumalive_log_1 FOREIGN KEY (kumalive_id) REFERENCES lams_kumalive (kumalive_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_kumalive_log_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_kumalive_poll; --- --- Table structure for table `lams_kumalive_poll` --- -DROP TABLE IF EXISTS `lams_kumalive_poll`; - -CREATE TABLE `lams_kumalive_poll` ( - `poll_id` bigint(20) NOT NULL AUTO_INCREMENT, - `kumalive_id` bigint(20) NOT NULL, - `name` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `votes_released` tinyint(1) DEFAULT '0', - `voters_released` tinyint(1) DEFAULT '0', - `start_date` datetime NOT NULL, - `finish_date` datetime DEFAULT NULL, - PRIMARY KEY (`poll_id`), - KEY `FK_lams_kumalive_poll_1` (`kumalive_id`), - CONSTRAINT `FK_lams_kumalive_poll_1` FOREIGN KEY (`kumalive_id`) REFERENCES `lams_kumalive` (`kumalive_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_kumalive_poll ( + poll_id bigint NOT NULL AUTO_INCREMENT, + kumalive_id bigint NOT NULL, + `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + votes_released tinyint(1) DEFAULT '0', + voters_released tinyint(1) DEFAULT '0', + start_date datetime NOT NULL, + finish_date datetime DEFAULT NULL, + PRIMARY KEY (poll_id), + KEY FK_lams_kumalive_poll_1 (kumalive_id), + CONSTRAINT FK_lams_kumalive_poll_1 FOREIGN KEY (kumalive_id) REFERENCES lams_kumalive (kumalive_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_kumalive_poll_answer; --- --- Table structure for table `lams_kumalive_poll_answer` --- -DROP TABLE IF EXISTS `lams_kumalive_poll_answer`; - -CREATE TABLE `lams_kumalive_poll_answer` ( - `answer_id` bigint(20) NOT NULL AUTO_INCREMENT, - `poll_id` bigint(20) NOT NULL, - `order_id` tinyint(4) NOT NULL, - `name` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - PRIMARY KEY (`answer_id`), - KEY `FK_lams_kumalive_poll_answer_1` (`poll_id`), - CONSTRAINT `FK_lams_kumalive_poll_answer_1` FOREIGN KEY (`poll_id`) REFERENCES `lams_kumalive_poll` (`poll_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_kumalive_poll_answer ( + answer_id bigint NOT NULL AUTO_INCREMENT, + poll_id bigint NOT NULL, + order_id tinyint NOT NULL, + `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (answer_id), + KEY FK_lams_kumalive_poll_answer_1 (poll_id), + CONSTRAINT FK_lams_kumalive_poll_answer_1 FOREIGN KEY (poll_id) REFERENCES lams_kumalive_poll (poll_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_kumalive_poll_vote; --- --- Table structure for table `lams_kumalive_poll_vote` --- -DROP TABLE IF EXISTS `lams_kumalive_poll_vote`; - -CREATE TABLE `lams_kumalive_poll_vote` ( - `answer_id` bigint(20) NOT NULL, - `user_id` bigint(20) NOT NULL, - `vote_date` datetime DEFAULT NULL, - PRIMARY KEY (`answer_id`,`user_id`), - KEY `FK_lams_kumalive_poll_vote_2` (`user_id`), - CONSTRAINT `FK_lams_kumalive_poll_vote_1` FOREIGN KEY (`answer_id`) REFERENCES `lams_kumalive_poll_answer` (`answer_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_kumalive_poll_vote_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_kumalive_poll_vote ( + answer_id bigint NOT NULL, + user_id bigint NOT NULL, + vote_date datetime DEFAULT NULL, + PRIMARY KEY (answer_id,user_id), + KEY FK_lams_kumalive_poll_vote_2 (user_id), + CONSTRAINT FK_lams_kumalive_poll_vote_1 FOREIGN KEY (answer_id) REFERENCES lams_kumalive_poll_answer (answer_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_kumalive_poll_vote_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_kumalive_rubric; --- --- Table structure for table `lams_kumalive_rubric` --- -DROP TABLE IF EXISTS `lams_kumalive_rubric`; - -CREATE TABLE `lams_kumalive_rubric` ( - `rubric_id` bigint(20) NOT NULL AUTO_INCREMENT, - `organisation_id` bigint(20) NOT NULL, - `kumalive_id` bigint(20) DEFAULT NULL, - `order_id` tinyint(4) NOT NULL, - `name` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - PRIMARY KEY (`rubric_id`), - KEY `FK_lams_kumalive_rubric_1` (`organisation_id`), - KEY `FK_lams_kumalive_rubric_2` (`kumalive_id`), - CONSTRAINT `FK_lams_kumalive_rubric_1` FOREIGN KEY (`organisation_id`) REFERENCES `lams_organisation` (`organisation_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_kumalive_rubric_2` FOREIGN KEY (`kumalive_id`) REFERENCES `lams_kumalive` (`kumalive_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_kumalive_rubric ( + rubric_id bigint NOT NULL AUTO_INCREMENT, + organisation_id bigint NOT NULL, + kumalive_id bigint DEFAULT NULL, + order_id tinyint NOT NULL, + `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (rubric_id), + KEY FK_lams_kumalive_rubric_1 (organisation_id), + KEY FK_lams_kumalive_rubric_2 (kumalive_id), + CONSTRAINT FK_lams_kumalive_rubric_1 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_kumalive_rubric_2 FOREIGN KEY (kumalive_id) REFERENCES lams_kumalive (kumalive_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_kumalive_score; --- --- Table structure for table `lams_kumalive_score` --- -DROP TABLE IF EXISTS `lams_kumalive_score`; - -CREATE TABLE `lams_kumalive_score` ( - `score_id` bigint(20) NOT NULL AUTO_INCREMENT, - `rubric_id` bigint(20) NOT NULL, - `user_id` bigint(20) DEFAULT NULL, - `batch` bigint(10) DEFAULT NULL, - `score` tinyint(4) DEFAULT NULL, - PRIMARY KEY (`score_id`), - KEY `FK_lams_kumalive_score_1` (`rubric_id`), - KEY `FK_lams_kumalive_score_2` (`user_id`), - CONSTRAINT `FK_lams_kumalive_score_1` FOREIGN KEY (`rubric_id`) REFERENCES `lams_kumalive_rubric` (`rubric_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_kumalive_score_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_kumalive_score ( + score_id bigint NOT NULL AUTO_INCREMENT, + rubric_id bigint NOT NULL, + user_id bigint DEFAULT NULL, + batch bigint DEFAULT NULL, + score tinyint DEFAULT NULL, + PRIMARY KEY (score_id), + KEY FK_lams_kumalive_score_1 (rubric_id), + KEY FK_lams_kumalive_score_2 (user_id), + CONSTRAINT FK_lams_kumalive_score_1 FOREIGN KEY (rubric_id) REFERENCES lams_kumalive_rubric (rubric_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_kumalive_score_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_learner_progress; --- --- Table structure for table `lams_learner_progress` --- -DROP TABLE IF EXISTS `lams_learner_progress`; - -CREATE TABLE `lams_learner_progress` ( - `learner_progress_id` bigint(20) NOT NULL AUTO_INCREMENT, - `user_id` bigint(20) NOT NULL, - `lesson_id` bigint(20) NOT NULL, - `lesson_completed_flag` tinyint(1) NOT NULL DEFAULT '0', - `waiting_flag` tinyint(4) NOT NULL, - `start_date_time` datetime NOT NULL, - `finish_date_time` datetime DEFAULT NULL, - `current_activity_id` bigint(20) DEFAULT NULL, - `next_activity_id` bigint(20) DEFAULT NULL, - `previous_activity_id` bigint(20) DEFAULT NULL, - `requires_restart_flag` tinyint(1) NOT NULL, - PRIMARY KEY (`learner_progress_id`), - UNIQUE KEY `IX_lams_learner_progress_1` (`user_id`,`lesson_id`), - KEY `user_id` (`user_id`), - KEY `lesson_id` (`lesson_id`), - KEY `current_activity_id` (`current_activity_id`), - KEY `next_activity_id` (`next_activity_id`), - KEY `previous_activity_id` (`previous_activity_id`), - CONSTRAINT `FK_lams_learner_progress_1` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`), - CONSTRAINT `FK_lams_learner_progress_2` FOREIGN KEY (`lesson_id`) REFERENCES `lams_lesson` (`lesson_id`), - CONSTRAINT `FK_lams_learner_progress_3` FOREIGN KEY (`current_activity_id`) REFERENCES `lams_learning_activity` (`activity_id`), - CONSTRAINT `FK_lams_learner_progress_4` FOREIGN KEY (`next_activity_id`) REFERENCES `lams_learning_activity` (`activity_id`), - CONSTRAINT `FK_lams_learner_progress_5` FOREIGN KEY (`previous_activity_id`) REFERENCES `lams_learning_activity` (`activity_id`) +CREATE TABLE lams_learner_progress ( + learner_progress_id bigint NOT NULL AUTO_INCREMENT, + user_id bigint NOT NULL, + lesson_id bigint NOT NULL, + lesson_completed_flag tinyint(1) NOT NULL DEFAULT '0', + waiting_flag tinyint NOT NULL, + start_date_time datetime NOT NULL, + finish_date_time datetime DEFAULT NULL, + current_activity_id bigint DEFAULT NULL, + next_activity_id bigint DEFAULT NULL, + previous_activity_id bigint DEFAULT NULL, + requires_restart_flag tinyint(1) NOT NULL, + PRIMARY KEY (learner_progress_id), + UNIQUE KEY IX_lams_learner_progress_1 (user_id,lesson_id), + KEY user_id (user_id), + KEY lesson_id (lesson_id), + KEY current_activity_id (current_activity_id), + KEY next_activity_id (next_activity_id), + KEY previous_activity_id (previous_activity_id), + CONSTRAINT FK_lams_learner_progress_1 FOREIGN KEY (user_id) REFERENCES lams_user (user_id), + CONSTRAINT FK_lams_learner_progress_2 FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id), + CONSTRAINT FK_lams_learner_progress_3 FOREIGN KEY (current_activity_id) REFERENCES lams_learning_activity (activity_id), + CONSTRAINT FK_lams_learner_progress_4 FOREIGN KEY (next_activity_id) REFERENCES lams_learning_activity (activity_id), + CONSTRAINT FK_lams_learner_progress_5 FOREIGN KEY (previous_activity_id) REFERENCES lams_learning_activity (activity_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_learner_progress_archive; --- --- Table structure for table `lams_learner_progress_archive` --- -DROP TABLE IF EXISTS `lams_learner_progress_archive`; - -CREATE TABLE `lams_learner_progress_archive` ( - `learner_progress_id` bigint(20) NOT NULL AUTO_INCREMENT, - `user_id` bigint(20) NOT NULL, - `lesson_id` bigint(20) NOT NULL, - `attempt_id` tinyint(4) NOT NULL DEFAULT '1', - `lesson_completed_flag` tinyint(1) NOT NULL DEFAULT '0', - `start_date_time` datetime NOT NULL, - `finish_date_time` datetime DEFAULT NULL, - `current_activity_id` bigint(20) DEFAULT NULL, - `archive_date` datetime DEFAULT NULL, - PRIMARY KEY (`learner_progress_id`), - UNIQUE KEY `IX_lams_learner_progress_archive_1` (`user_id`,`lesson_id`,`attempt_id`), - KEY `FK_lams_learner_progress_archive_2` (`lesson_id`), - KEY `FK_lams_learner_progress_archive_3` (`current_activity_id`), - CONSTRAINT `FK_lams_learner_progress_archive_1` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_learner_progress_archive_2` FOREIGN KEY (`lesson_id`) REFERENCES `lams_lesson` (`lesson_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_learner_progress_archive_3` FOREIGN KEY (`current_activity_id`) REFERENCES `lams_learning_activity` (`activity_id`) ON DELETE SET NULL ON UPDATE CASCADE +CREATE TABLE lams_learner_progress_archive ( + learner_progress_id bigint NOT NULL AUTO_INCREMENT, + user_id bigint NOT NULL, + lesson_id bigint NOT NULL, + attempt_id tinyint NOT NULL DEFAULT '1', + lesson_completed_flag tinyint(1) NOT NULL DEFAULT '0', + start_date_time datetime NOT NULL, + finish_date_time datetime DEFAULT NULL, + current_activity_id bigint DEFAULT NULL, + archive_date datetime DEFAULT NULL, + PRIMARY KEY (learner_progress_id), + UNIQUE KEY IX_lams_learner_progress_archive_1 (user_id,lesson_id,attempt_id), + KEY FK_lams_learner_progress_archive_2 (lesson_id), + KEY FK_lams_learner_progress_archive_3 (current_activity_id), + CONSTRAINT FK_lams_learner_progress_archive_1 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_learner_progress_archive_2 FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_learner_progress_archive_3 FOREIGN KEY (current_activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_learning_activity; --- --- Table structure for table `lams_learning_activity` --- -DROP TABLE IF EXISTS `lams_learning_activity`; - -CREATE TABLE `lams_learning_activity` ( - `activity_id` bigint(20) NOT NULL AUTO_INCREMENT, - `activity_ui_id` int(11) DEFAULT NULL, - `description` mediumtext COLLATE utf8mb4_unicode_ci, - `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `xcoord` int(11) DEFAULT NULL, - `ycoord` int(11) DEFAULT NULL, - `parent_activity_id` bigint(20) DEFAULT NULL, - `parent_ui_id` int(11) DEFAULT NULL, - `learning_activity_type_id` int(11) NOT NULL DEFAULT '0', - `grouping_support_type_id` int(3) NOT NULL, - `apply_grouping_flag` tinyint(1) NOT NULL, - `grouping_id` bigint(20) DEFAULT NULL, - `grouping_ui_id` int(11) DEFAULT NULL, - `order_id` int(11) DEFAULT NULL, - `learning_design_id` bigint(20) DEFAULT NULL, - `learning_library_id` bigint(20) DEFAULT NULL, - `create_date_time` datetime NOT NULL, - `max_number_of_options` int(5) DEFAULT NULL, - `min_number_of_options` int(5) DEFAULT NULL, - `options_instructions` mediumtext COLLATE utf8mb4_unicode_ci, - `tool_id` bigint(20) DEFAULT NULL, - `tool_content_id` bigint(20) DEFAULT NULL, - `activity_category_id` int(3) NOT NULL, - `gate_activity_level_id` int(11) DEFAULT NULL, - `gate_open_flag` tinyint(1) DEFAULT NULL, - `gate_open_user` bigint(20) DEFAULT NULL, - `gate_open_time` datetime DEFAULT NULL, - `gate_start_time_offset` bigint(38) DEFAULT NULL, - `gate_end_time_offset` bigint(38) DEFAULT NULL, - `gate_activity_completion_based` tinyint(1) DEFAULT NULL, - `library_activity_ui_image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `create_grouping_id` bigint(20) DEFAULT NULL, - `create_grouping_ui_id` int(11) DEFAULT NULL, - `library_activity_id` bigint(20) DEFAULT NULL, - `language_file` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `system_tool_id` bigint(20) DEFAULT NULL, +CREATE TABLE lams_learning_activity ( + activity_id bigint NOT NULL AUTO_INCREMENT, + activity_ui_id int DEFAULT NULL, + `description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + title varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + xcoord int DEFAULT NULL, + ycoord int DEFAULT NULL, + parent_activity_id bigint DEFAULT NULL, + parent_ui_id int DEFAULT NULL, + learning_activity_type_id int NOT NULL DEFAULT '0', + grouping_support_type_id int NOT NULL, + apply_grouping_flag tinyint(1) NOT NULL, + grouping_id bigint DEFAULT NULL, + grouping_ui_id int DEFAULT NULL, + order_id int DEFAULT NULL, + learning_design_id bigint DEFAULT NULL, + learning_library_id bigint DEFAULT NULL, + create_date_time datetime NOT NULL, + max_number_of_options int DEFAULT NULL, + min_number_of_options int DEFAULT NULL, + options_instructions mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + tool_id bigint DEFAULT NULL, + tool_content_id bigint DEFAULT NULL, + activity_category_id int, + gate_activity_level_id int DEFAULT NULL, + gate_open_flag tinyint(1) DEFAULT NULL, + gate_open_user bigint DEFAULT NULL, + gate_open_time datetime DEFAULT NULL, + gate_start_time_offset bigint DEFAULT NULL, + gate_end_time_offset bigint DEFAULT NULL, + gate_activity_completion_based tinyint(1) DEFAULT NULL, + gate_password varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + library_activity_ui_image varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + create_grouping_id bigint DEFAULT NULL, + create_grouping_ui_id int DEFAULT NULL, + library_activity_id bigint DEFAULT NULL, + language_file varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + system_tool_id bigint DEFAULT NULL, `read_only` tinyint(1) DEFAULT '0', - `initialised` tinyint(1) DEFAULT '0', - `default_activity_id` bigint(20) DEFAULT NULL, - `start_xcoord` int(11) DEFAULT NULL, - `start_ycoord` int(11) DEFAULT NULL, - `end_xcoord` int(11) DEFAULT NULL, - `end_ycoord` int(11) DEFAULT NULL, - `stop_after_activity` tinyint(1) NOT NULL DEFAULT '0', - `transition_to_id` bigint(20) DEFAULT NULL, - `transition_from_id` bigint(20) DEFAULT NULL, - PRIMARY KEY (`activity_id`), - KEY `lams_learning_activity_tool_content_id` (`tool_content_id`), - KEY `learning_library_id` (`learning_library_id`), - KEY `learning_design_id` (`learning_design_id`), - KEY `parent_activity_id` (`parent_activity_id`), - KEY `learning_activity_type_id` (`learning_activity_type_id`), - KEY `grouping_id` (`grouping_id`), - KEY `tool_id` (`tool_id`), - KEY `gate_activity_level_id` (`gate_activity_level_id`), - KEY `create_grouping_id` (`create_grouping_id`), - KEY `library_activity_id` (`library_activity_id`), - KEY `activity_category_id` (`activity_category_id`), - KEY `grouping_support_type_id` (`grouping_support_type_id`), - KEY `system_tool_id` (`system_tool_id`), - KEY `FK_lams_learning_activity_15` (`transition_to_id`), - KEY `FK_lams_learning_activity_16` (`transition_from_id`), - KEY `FK_lams_learning_activity_17` (`gate_open_user`), - CONSTRAINT `FK_lams_learning_activity_10` FOREIGN KEY (`gate_activity_level_id`) REFERENCES `lams_gate_activity_level` (`gate_activity_level_id`), - CONSTRAINT `FK_lams_learning_activity_11` FOREIGN KEY (`library_activity_id`) REFERENCES `lams_learning_activity` (`activity_id`), - CONSTRAINT `FK_lams_learning_activity_12` FOREIGN KEY (`activity_category_id`) REFERENCES `lams_activity_category` (`activity_category_id`), - CONSTRAINT `FK_lams_learning_activity_13` FOREIGN KEY (`grouping_support_type_id`) REFERENCES `lams_grouping_support_type` (`grouping_support_type_id`), - CONSTRAINT `FK_lams_learning_activity_14` FOREIGN KEY (`system_tool_id`) REFERENCES `lams_system_tool` (`system_tool_id`), - CONSTRAINT `FK_lams_learning_activity_15` FOREIGN KEY (`transition_to_id`) REFERENCES `lams_learning_transition` (`transition_id`) ON DELETE SET NULL ON UPDATE CASCADE, - CONSTRAINT `FK_lams_learning_activity_16` FOREIGN KEY (`transition_from_id`) REFERENCES `lams_learning_transition` (`transition_id`) ON DELETE SET NULL ON UPDATE CASCADE, - CONSTRAINT `FK_lams_learning_activity_17` FOREIGN KEY (`gate_open_user`) REFERENCES `lams_user` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE, - CONSTRAINT `FK_lams_learning_activity_6` FOREIGN KEY (`learning_design_id`) REFERENCES `lams_learning_design` (`learning_design_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_learning_activity_7` FOREIGN KEY (`learning_library_id`) REFERENCES `lams_learning_library` (`learning_library_id`), - CONSTRAINT `FK_lams_learning_activity_8` FOREIGN KEY (`tool_id`) REFERENCES `lams_tool` (`tool_id`), - CONSTRAINT `FK_lams_learning_activity_9` FOREIGN KEY (`create_grouping_id`) REFERENCES `lams_grouping` (`grouping_id`), - CONSTRAINT `FK_learning_activity_2` FOREIGN KEY (`parent_activity_id`) REFERENCES `lams_learning_activity` (`activity_id`), - CONSTRAINT `FK_learning_activity_3` FOREIGN KEY (`learning_activity_type_id`) REFERENCES `lams_learning_activity_type` (`learning_activity_type_id`), - CONSTRAINT `FK_learning_activity_6` FOREIGN KEY (`grouping_id`) REFERENCES `lams_grouping` (`grouping_id`) -) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + initialised tinyint(1) DEFAULT '0', + default_activity_id bigint DEFAULT NULL, + start_xcoord int DEFAULT NULL, + start_ycoord int DEFAULT NULL, + end_xcoord int DEFAULT NULL, + end_ycoord int DEFAULT NULL, + stop_after_activity tinyint(1) NOT NULL DEFAULT '0', + transition_to_id bigint DEFAULT NULL, + transition_from_id bigint DEFAULT NULL, + PRIMARY KEY (activity_id), + KEY lams_learning_activity_tool_content_id (tool_content_id), + KEY learning_library_id (learning_library_id), + KEY learning_design_id (learning_design_id), + KEY parent_activity_id (parent_activity_id), + KEY learning_activity_type_id (learning_activity_type_id), + KEY grouping_id (grouping_id), + KEY tool_id (tool_id), + KEY gate_activity_level_id (gate_activity_level_id), + KEY create_grouping_id (create_grouping_id), + KEY library_activity_id (library_activity_id), + KEY activity_category_id (activity_category_id), + KEY grouping_support_type_id (grouping_support_type_id), + KEY system_tool_id (system_tool_id), + KEY FK_lams_learning_activity_15 (transition_to_id), + KEY FK_lams_learning_activity_16 (transition_from_id), + KEY FK_lams_learning_activity_17 (gate_open_user), + CONSTRAINT FK_lams_learning_activity_10 FOREIGN KEY (gate_activity_level_id) REFERENCES lams_gate_activity_level (gate_activity_level_id), + CONSTRAINT FK_lams_learning_activity_11 FOREIGN KEY (library_activity_id) REFERENCES lams_learning_activity (activity_id), + CONSTRAINT FK_lams_learning_activity_12 FOREIGN KEY (activity_category_id) REFERENCES lams_activity_category (activity_category_id), + CONSTRAINT FK_lams_learning_activity_13 FOREIGN KEY (grouping_support_type_id) REFERENCES lams_grouping_support_type (grouping_support_type_id), + CONSTRAINT FK_lams_learning_activity_14 FOREIGN KEY (system_tool_id) REFERENCES lams_system_tool (system_tool_id), + CONSTRAINT FK_lams_learning_activity_15 FOREIGN KEY (transition_to_id) REFERENCES lams_learning_transition (transition_id) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT FK_lams_learning_activity_16 FOREIGN KEY (transition_from_id) REFERENCES lams_learning_transition (transition_id) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT FK_lams_learning_activity_17 FOREIGN KEY (gate_open_user) REFERENCES lams_user (user_id) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT FK_lams_learning_activity_6 FOREIGN KEY (learning_design_id) REFERENCES lams_learning_design (learning_design_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_learning_activity_7 FOREIGN KEY (learning_library_id) REFERENCES lams_learning_library (learning_library_id), + CONSTRAINT FK_lams_learning_activity_8 FOREIGN KEY (tool_id) REFERENCES lams_tool (tool_id), + CONSTRAINT FK_lams_learning_activity_9 FOREIGN KEY (create_grouping_id) REFERENCES lams_grouping (grouping_id), + CONSTRAINT FK_learning_activity_2 FOREIGN KEY (parent_activity_id) REFERENCES lams_learning_activity (activity_id), + CONSTRAINT FK_learning_activity_3 FOREIGN KEY (learning_activity_type_id) REFERENCES lams_learning_activity_type (learning_activity_type_id), + CONSTRAINT FK_learning_activity_6 FOREIGN KEY (grouping_id) REFERENCES lams_grouping (grouping_id) +) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_learning_activity_type; --- --- Table structure for table `lams_learning_activity_type` --- -DROP TABLE IF EXISTS `lams_learning_activity_type`; - -CREATE TABLE `lams_learning_activity_type` ( - `learning_activity_type_id` int(11) NOT NULL, - `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`learning_activity_type_id`) +CREATE TABLE lams_learning_activity_type ( + learning_activity_type_id int NOT NULL, + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (learning_activity_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_learning_command; --- --- Table structure for table `lams_learning_command` --- -DROP TABLE IF EXISTS `lams_learning_command`; - -CREATE TABLE `lams_learning_command` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `lesson_id` bigint(20) DEFAULT NULL, - `user_name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `create_date` datetime NOT NULL, - `command_TEXT` text COLLATE utf8mb4_unicode_ci, - PRIMARY KEY (`uid`), - KEY `idx_lesson_id` (`lesson_id`), - KEY `idx_user_name` (`user_name`), - KEY `idx_create_date` (`create_date`) +CREATE TABLE lams_learning_command ( + uid bigint NOT NULL AUTO_INCREMENT, + lesson_id bigint DEFAULT NULL, + user_name varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + create_date datetime NOT NULL, + command_TEXT text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + PRIMARY KEY (uid), + KEY idx_lesson_id (lesson_id), + KEY idx_user_name (user_name), + KEY idx_create_date (create_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_learning_design; --- --- Table structure for table `lams_learning_design` --- -DROP TABLE IF EXISTS `lams_learning_design`; - -CREATE TABLE `lams_learning_design` ( - `learning_design_id` bigint(20) NOT NULL AUTO_INCREMENT, - `learning_design_ui_id` int(11) DEFAULT NULL, - `description` mediumtext COLLATE utf8mb4_unicode_ci, - `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `first_activity_id` bigint(20) DEFAULT NULL, - `floating_activity_id` bigint(20) DEFAULT NULL, - `max_id` int(11) DEFAULT NULL, - `valid_design_flag` tinyint(1) NOT NULL, - `read_only_flag` tinyint(1) NOT NULL, - `date_read_only` datetime DEFAULT NULL, - `user_id` bigint(20) NOT NULL, - `original_user_id` bigint(20) NOT NULL, - `help_text` mediumtext COLLATE utf8mb4_unicode_ci, - `copy_type_id` tinyint(4) NOT NULL, - `create_date_time` datetime NOT NULL, - `version` varchar(56) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `original_learning_design_id` bigint(20) DEFAULT NULL, - `workspace_folder_id` bigint(20) DEFAULT NULL, - `duration` bigint(38) DEFAULT NULL, - `license_id` bigint(20) DEFAULT NULL, - `license_TEXT` mediumtext COLLATE utf8mb4_unicode_ci, - `last_modified_date_time` datetime DEFAULT NULL, - `content_folder_id` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `edit_override_lock` tinyint(1) DEFAULT '0', - `edit_override_user_id` bigint(20) DEFAULT NULL, - `design_version` int(11) DEFAULT '1', - `removed` tinyint(1) NOT NULL DEFAULT '0', - `design_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - PRIMARY KEY (`learning_design_id`), - KEY `user_id` (`user_id`), - KEY `workspace_folder_id` (`workspace_folder_id`), - KEY `license_id` (`license_id`), - KEY `copy_type_id` (`copy_type_id`), - KEY `edit_override_user_id` (`edit_override_user_id`), - KEY `idx_design_parent_id` (`original_learning_design_id`), - KEY `idx_design_first_act` (`first_activity_id`), - KEY `idx_design_floating_act` (`floating_activity_id`), - CONSTRAINT `FK_lams_learning_design_3` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`), - CONSTRAINT `FK_lams_learning_design_4` FOREIGN KEY (`workspace_folder_id`) REFERENCES `lams_workspace_folder` (`workspace_folder_id`), - CONSTRAINT `FK_lams_learning_design_5` FOREIGN KEY (`license_id`) REFERENCES `lams_license` (`license_id`), - CONSTRAINT `FK_lams_learning_design_6` FOREIGN KEY (`copy_type_id`) REFERENCES `lams_copy_type` (`copy_type_id`), - CONSTRAINT `FK_lams_learning_design_7` FOREIGN KEY (`edit_override_user_id`) REFERENCES `lams_user` (`user_id`) +CREATE TABLE lams_learning_design ( + learning_design_id bigint NOT NULL AUTO_INCREMENT, + learning_design_ui_id int DEFAULT NULL, + `description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + title varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + first_activity_id bigint DEFAULT NULL, + floating_activity_id bigint DEFAULT NULL, + max_id int DEFAULT NULL, + valid_design_flag tinyint(1) NOT NULL, + read_only_flag tinyint(1) NOT NULL, + date_read_only datetime DEFAULT NULL, + user_id bigint NOT NULL, + original_user_id bigint NOT NULL, + help_text mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + copy_type_id tinyint NOT NULL, + create_date_time datetime NOT NULL, + version varchar(56) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + original_learning_design_id bigint DEFAULT NULL, + workspace_folder_id bigint DEFAULT NULL, + duration bigint DEFAULT NULL, + license_id bigint DEFAULT NULL, + license_TEXT mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + last_modified_date_time datetime DEFAULT NULL, + content_folder_id char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + edit_override_lock tinyint(1) DEFAULT '0', + edit_override_user_id bigint DEFAULT NULL, + design_version int DEFAULT '1', + removed tinyint(1) NOT NULL DEFAULT '0', + design_type varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (learning_design_id), + KEY user_id (user_id), + KEY workspace_folder_id (workspace_folder_id), + KEY license_id (license_id), + KEY copy_type_id (copy_type_id), + KEY edit_override_user_id (edit_override_user_id), + KEY idx_design_parent_id (original_learning_design_id), + KEY idx_design_first_act (first_activity_id), + KEY idx_design_floating_act (floating_activity_id), + CONSTRAINT FK_lams_learning_design_3 FOREIGN KEY (user_id) REFERENCES lams_user (user_id), + CONSTRAINT FK_lams_learning_design_4 FOREIGN KEY (workspace_folder_id) REFERENCES lams_workspace_folder (workspace_folder_id), + CONSTRAINT FK_lams_learning_design_5 FOREIGN KEY (license_id) REFERENCES lams_license (license_id), + CONSTRAINT FK_lams_learning_design_6 FOREIGN KEY (copy_type_id) REFERENCES lams_copy_type (copy_type_id), + CONSTRAINT FK_lams_learning_design_7 FOREIGN KEY (edit_override_user_id) REFERENCES lams_user (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_learning_design_access; --- --- Table structure for table `lams_learning_design_access` --- -DROP TABLE IF EXISTS `lams_learning_design_access`; - -CREATE TABLE `lams_learning_design_access` ( - `learning_design_id` bigint(20) NOT NULL, - `user_id` bigint(20) NOT NULL, - `access_date` datetime DEFAULT NULL, - PRIMARY KEY (`learning_design_id`,`user_id`), - KEY `FK_lams_learning_design_access_2` (`user_id`), - CONSTRAINT `FK_lams_learning_design_access_1` FOREIGN KEY (`learning_design_id`) REFERENCES `lams_learning_design` (`learning_design_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_learning_design_access_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_learning_design_access ( + learning_design_id bigint NOT NULL, + user_id bigint NOT NULL, + access_date datetime DEFAULT NULL, + PRIMARY KEY (learning_design_id,user_id), + KEY FK_lams_learning_design_access_2 (user_id), + CONSTRAINT FK_lams_learning_design_access_1 FOREIGN KEY (learning_design_id) REFERENCES lams_learning_design (learning_design_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_learning_design_access_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_learning_design_annotation; --- --- Table structure for table `lams_learning_design_annotation` --- -DROP TABLE IF EXISTS `lams_learning_design_annotation`; - -CREATE TABLE `lams_learning_design_annotation` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `learning_design_id` bigint(20) NOT NULL, - `ui_id` int(11) DEFAULT NULL, - `title` varchar(1024) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `xcoord` int(11) DEFAULT NULL, - `ycoord` int(11) DEFAULT NULL, - `end_xcoord` int(11) DEFAULT NULL, - `end_ycoord` int(11) DEFAULT NULL, - `color` char(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `size` tinyint(4) DEFAULT NULL, - PRIMARY KEY (`uid`), - KEY `FK_lams_learning_design_annotation_1` (`learning_design_id`), - CONSTRAINT `FK_lams_learning_design_annotation_1` FOREIGN KEY (`learning_design_id`) REFERENCES `lams_learning_design` (`learning_design_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_learning_design_annotation ( + uid bigint NOT NULL AUTO_INCREMENT, + learning_design_id bigint NOT NULL, + ui_id int DEFAULT NULL, + title varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + xcoord int DEFAULT NULL, + ycoord int DEFAULT NULL, + end_xcoord int DEFAULT NULL, + end_ycoord int DEFAULT NULL, + color char(7) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + size tinyint DEFAULT NULL, + PRIMARY KEY (uid), + KEY FK_lams_learning_design_annotation_1 (learning_design_id), + CONSTRAINT FK_lams_learning_design_annotation_1 FOREIGN KEY (learning_design_id) REFERENCES lams_learning_design (learning_design_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_learning_library; --- --- Table structure for table `lams_learning_library` --- -DROP TABLE IF EXISTS `lams_learning_library`; +CREATE TABLE lams_learning_library ( + learning_library_id bigint NOT NULL AUTO_INCREMENT, + `description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + title varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + valid_flag tinyint(1) NOT NULL DEFAULT '1', + create_date_time datetime NOT NULL, + PRIMARY KEY (learning_library_id) +) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE `lams_learning_library` ( - `learning_library_id` bigint(20) NOT NULL AUTO_INCREMENT, - `description` mediumtext COLLATE utf8mb4_unicode_ci, - `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `valid_flag` tinyint(1) NOT NULL DEFAULT '1', - `create_date_time` datetime NOT NULL, - PRIMARY KEY (`learning_library_id`) -) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_learning_library_group; --- --- Table structure for table `lams_learning_library_group` --- - -DROP TABLE IF EXISTS `lams_learning_library_group`; - -CREATE TABLE `lams_learning_library_group` ( - `group_id` int(11) NOT NULL AUTO_INCREMENT, - `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`group_id`) +CREATE TABLE lams_learning_library_group ( + group_id int NOT NULL AUTO_INCREMENT, + `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (group_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_learning_library_to_group; --- --- Table structure for table `lams_learning_library_to_group` --- -DROP TABLE IF EXISTS `lams_learning_library_to_group`; - -CREATE TABLE `lams_learning_library_to_group` ( - `group_id` int(11) NOT NULL, - `learning_library_id` bigint(20) NOT NULL, - PRIMARY KEY (`group_id`,`learning_library_id`) +CREATE TABLE lams_learning_library_to_group ( + group_id int NOT NULL, + learning_library_id bigint NOT NULL, + PRIMARY KEY (group_id,learning_library_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_learning_transition; --- --- Table structure for table `lams_learning_transition` --- -DROP TABLE IF EXISTS `lams_learning_transition`; - -CREATE TABLE `lams_learning_transition` ( - `transition_id` bigint(20) NOT NULL AUTO_INCREMENT, - `transition_ui_id` int(11) DEFAULT NULL, - `description` mediumtext COLLATE utf8mb4_unicode_ci, - `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `to_activity_id` bigint(20) NOT NULL, - `from_activity_id` bigint(20) NOT NULL, - `learning_design_id` bigint(20) DEFAULT NULL, - `create_date_time` datetime NOT NULL, - `to_ui_id` int(11) DEFAULT NULL, - `from_ui_id` int(11) DEFAULT NULL, - `transition_type` tinyint(4) NOT NULL DEFAULT '0', - PRIMARY KEY (`transition_id`), - KEY `from_activity_id` (`from_activity_id`), - KEY `to_activity_id` (`to_activity_id`), - KEY `learning_design_id` (`learning_design_id`), - CONSTRAINT `FK_learning_transition_2` FOREIGN KEY (`to_activity_id`) REFERENCES `lams_learning_activity` (`activity_id`), - CONSTRAINT `FK_learning_transition_3` FOREIGN KEY (`from_activity_id`) REFERENCES `lams_learning_activity` (`activity_id`), - CONSTRAINT `lddefn_transition_ibfk_1` FOREIGN KEY (`learning_design_id`) REFERENCES `lams_learning_design` (`learning_design_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_learning_transition ( + transition_id bigint NOT NULL AUTO_INCREMENT, + transition_ui_id int DEFAULT NULL, + `description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + title varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + to_activity_id bigint NOT NULL, + from_activity_id bigint NOT NULL, + learning_design_id bigint DEFAULT NULL, + create_date_time datetime NOT NULL, + to_ui_id int DEFAULT NULL, + from_ui_id int DEFAULT NULL, + transition_type tinyint NOT NULL DEFAULT '0', + PRIMARY KEY (transition_id), + KEY from_activity_id (from_activity_id), + KEY to_activity_id (to_activity_id), + KEY learning_design_id (learning_design_id), + CONSTRAINT FK_learning_transition_2 FOREIGN KEY (to_activity_id) REFERENCES lams_learning_activity (activity_id), + CONSTRAINT FK_learning_transition_3 FOREIGN KEY (from_activity_id) REFERENCES lams_learning_activity (activity_id), + CONSTRAINT lddefn_transition_ibfk_1 FOREIGN KEY (learning_design_id) REFERENCES lams_learning_design (learning_design_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_lesson; --- --- Table structure for table `lams_lesson` --- -DROP TABLE IF EXISTS `lams_lesson`; - -CREATE TABLE `lams_lesson` ( - `lesson_id` bigint(20) NOT NULL AUTO_INCREMENT, - `learning_design_id` bigint(20) NOT NULL, - `user_id` bigint(20) NOT NULL, - `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `description` mediumtext COLLATE utf8mb4_unicode_ci, - `create_date_time` datetime NOT NULL, - `organisation_id` bigint(20) DEFAULT NULL, - `class_grouping_id` bigint(20) DEFAULT NULL, - `lesson_state_id` int(3) NOT NULL, - `start_date_time` datetime DEFAULT NULL, - `scheduled_number_days_to_lesson_finish` int(3) DEFAULT NULL, - `schedule_start_date_time` datetime DEFAULT NULL, - `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', - PRIMARY KEY (`lesson_id`), - KEY `learning_design_id` (`learning_design_id`), - KEY `user_id` (`user_id`), - KEY `organisation_id` (`organisation_id`), - KEY `lesson_state_id` (`lesson_state_id`), - KEY `class_grouping_id` (`class_grouping_id`), - CONSTRAINT `FK_lams_lesson_1_1` FOREIGN KEY (`learning_design_id`) REFERENCES `lams_learning_design` (`learning_design_id`), - CONSTRAINT `FK_lams_lesson_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`), - CONSTRAINT `FK_lams_lesson_3` FOREIGN KEY (`organisation_id`) REFERENCES `lams_organisation` (`organisation_id`), - CONSTRAINT `FK_lams_lesson_4` FOREIGN KEY (`lesson_state_id`) REFERENCES `lams_lesson_state` (`lesson_state_id`), - CONSTRAINT `FK_lams_lesson_5` FOREIGN KEY (`class_grouping_id`) REFERENCES `lams_grouping` (`grouping_id`) +CREATE TABLE lams_lesson ( + lesson_id bigint NOT NULL AUTO_INCREMENT, + learning_design_id bigint NOT NULL, + user_id bigint NOT NULL, + `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + create_date_time datetime NOT NULL, + organisation_id bigint DEFAULT NULL, + class_grouping_id bigint DEFAULT NULL, + lesson_state_id int NOT NULL, + start_date_time datetime DEFAULT NULL, + scheduled_number_days_to_lesson_finish int DEFAULT NULL, + schedule_start_date_time datetime DEFAULT NULL, + end_date_time datetime DEFAULT NULL, + schedule_end_date_time datetime DEFAULT NULL, + previous_state_id int DEFAULT NULL, + 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), + KEY organisation_id (organisation_id), + KEY lesson_state_id (lesson_state_id), + KEY class_grouping_id (class_grouping_id), + CONSTRAINT FK_lams_lesson_1_1 FOREIGN KEY (learning_design_id) REFERENCES lams_learning_design (learning_design_id), + CONSTRAINT FK_lams_lesson_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id), + CONSTRAINT FK_lams_lesson_3 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id), + CONSTRAINT FK_lams_lesson_4 FOREIGN KEY (lesson_state_id) REFERENCES lams_lesson_state (lesson_state_id), + CONSTRAINT FK_lams_lesson_5 FOREIGN KEY (class_grouping_id) REFERENCES lams_grouping (grouping_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_lesson_dependency; --- --- Table structure for table `lams_lesson_dependency` --- -DROP TABLE IF EXISTS `lams_lesson_dependency`; - -CREATE TABLE `lams_lesson_dependency` ( - `lesson_id` bigint(20) NOT NULL, - `preceding_lesson_id` bigint(20) NOT NULL, - PRIMARY KEY (`lesson_id`,`preceding_lesson_id`), - KEY `FK_lams_lesson_dependency_2` (`preceding_lesson_id`), - CONSTRAINT `FK_lams_lesson_dependency_1` FOREIGN KEY (`lesson_id`) REFERENCES `lams_lesson` (`lesson_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_lesson_dependency_2` FOREIGN KEY (`preceding_lesson_id`) REFERENCES `lams_lesson` (`lesson_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_lesson_dependency ( + lesson_id bigint NOT NULL, + preceding_lesson_id bigint NOT NULL, + PRIMARY KEY (lesson_id,preceding_lesson_id), + KEY FK_lams_lesson_dependency_2 (preceding_lesson_id), + CONSTRAINT FK_lams_lesson_dependency_1 FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_lesson_dependency_2 FOREIGN KEY (preceding_lesson_id) REFERENCES lams_lesson (lesson_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_lesson_state; --- --- Table structure for table `lams_lesson_state` --- -DROP TABLE IF EXISTS `lams_lesson_state`; - -CREATE TABLE `lams_lesson_state` ( - `lesson_state_id` int(3) NOT NULL, - `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`lesson_state_id`) +CREATE TABLE lams_lesson_state ( + lesson_state_id int NOT NULL, + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (lesson_state_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_license; --- --- Table structure for table `lams_license` --- -DROP TABLE IF EXISTS `lams_license`; - -CREATE TABLE `lams_license` ( - `license_id` bigint(20) NOT NULL, - `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, - `code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL, - `url` varchar(256) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `default_flag` tinyint(1) NOT NULL DEFAULT '0', - `picture_url` varchar(256) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `order_id` tinyint(4) DEFAULT '0', - PRIMARY KEY (`license_id`) +CREATE TABLE lams_license ( + license_id bigint NOT NULL, + `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + url varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + default_flag tinyint(1) NOT NULL DEFAULT '0', + picture_url varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + order_id tinyint DEFAULT '0', + PRIMARY KEY (license_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_log_event; --- --- Table structure for table `lams_log_event` --- -DROP TABLE IF EXISTS `lams_log_event`; - -CREATE TABLE `lams_log_event` ( - `id` bigint(20) NOT NULL AUTO_INCREMENT, - `log_event_type_id` int(5) NOT NULL, - `user_id` bigint(20) DEFAULT NULL, - `occurred_date_time` datetime NOT NULL, - `lesson_id` bigint(20) DEFAULT NULL, - `activity_id` bigint(20) DEFAULT NULL, - `target_user_id` bigint(20) DEFAULT NULL, - `description` text COLLATE utf8mb4_unicode_ci, - PRIMARY KEY (`id`), - KEY `event_log_occurred_date_time` (`occurred_date_time`), - KEY `FK_event_log_event_type_idx` (`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 +CREATE TABLE lams_log_event ( + id bigint NOT NULL AUTO_INCREMENT, + log_event_type_id int NOT NULL, + user_id bigint DEFAULT NULL, + occurred_date_time datetime NOT NULL, + lesson_id bigint DEFAULT NULL, + activity_id bigint DEFAULT NULL, + target_user_id bigint DEFAULT NULL, + `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + 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; +DROP TABLE IF EXISTS lams_log_event_type; --- --- Table structure for table `lams_log_event_type` --- -DROP TABLE IF EXISTS `lams_log_event_type`; - -CREATE TABLE `lams_log_event_type` ( - `log_event_type_id` int(5) NOT NULL, - `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `area` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - PRIMARY KEY (`log_event_type_id`) +CREATE TABLE lams_log_event_type ( + log_event_type_id int NOT NULL, + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + area varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (log_event_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_notebook_entry; --- --- Table structure for table `lams_notebook_entry` --- -DROP TABLE IF EXISTS `lams_notebook_entry`; - -CREATE TABLE `lams_notebook_entry` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `external_id` bigint(20) DEFAULT NULL, - `external_id_type` int(11) DEFAULT NULL, - `external_signature` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `user_id` int(11) DEFAULT NULL, - `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `entry` mediumtext COLLATE utf8mb4_unicode_ci, - `create_date` datetime DEFAULT NULL, - `last_modified` datetime DEFAULT NULL, - PRIMARY KEY (`uid`), - KEY `ext_sig_user` (`external_id`,`external_id_type`,`external_signature`,`user_id`), - KEY `idx_create_date` (`create_date`) +CREATE TABLE lams_notebook_entry ( + uid bigint NOT NULL AUTO_INCREMENT, + external_id bigint DEFAULT NULL, + external_id_type int DEFAULT NULL, + external_signature varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + user_id int DEFAULT NULL, + title varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + entry mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + create_date datetime DEFAULT NULL, + last_modified datetime DEFAULT NULL, + PRIMARY KEY (uid), + KEY ext_sig_user (external_id,external_id_type,external_signature,user_id), + KEY idx_create_date (create_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_notification_event; --- --- Table structure for table `lams_notification_event` --- -DROP TABLE IF EXISTS `lams_notification_event`; - -CREATE TABLE `lams_notification_event` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `scope` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL, - `name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL, - `event_session_id` bigint(20) DEFAULT NULL, - `subject` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `message` mediumtext COLLATE utf8mb4_unicode_ci, - `fail_time` datetime DEFAULT NULL, - `html_format` tinyint(1) DEFAULT '0', - PRIMARY KEY (`uid`), - KEY `scope` (`scope`,`name`,`event_session_id`) +CREATE TABLE lams_notification_event ( + uid bigint NOT NULL AUTO_INCREMENT, + scope varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + event_session_id bigint DEFAULT NULL, + `subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + message mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + fail_time datetime DEFAULT NULL, + html_format tinyint(1) DEFAULT '0', + PRIMARY KEY (uid), + KEY scope (scope,`name`,event_session_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_notification_subscription; --- --- Table structure for table `lams_notification_subscription` --- -DROP TABLE IF EXISTS `lams_notification_subscription`; - -CREATE TABLE `lams_notification_subscription` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `user_id` bigint(20) DEFAULT NULL, - `event_uid` bigint(20) DEFAULT NULL, - `delivery_method_id` tinyint(3) unsigned DEFAULT NULL, - `last_operation_message` mediumtext COLLATE utf8mb4_unicode_ci, - PRIMARY KEY (`uid`), - KEY `EventSubscriptionsToUsers` (`user_id`), - KEY `event_uid` (`event_uid`), - CONSTRAINT `EventSubscriptionsToEvent` FOREIGN KEY (`event_uid`) REFERENCES `lams_notification_event` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `EventSubscriptionsToUsers` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_notification_subscription ( + uid bigint NOT NULL AUTO_INCREMENT, + user_id bigint DEFAULT NULL, + event_uid bigint DEFAULT NULL, + delivery_method_id tinyint unsigned DEFAULT NULL, + last_operation_message mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + PRIMARY KEY (uid), + KEY EventSubscriptionsToUsers (user_id), + KEY event_uid (event_uid), + CONSTRAINT EventSubscriptionsToEvent FOREIGN KEY (event_uid) REFERENCES lams_notification_event (uid) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT EventSubscriptionsToUsers FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_organisation; --- --- Table structure for table `lams_organisation` --- -DROP TABLE IF EXISTS `lams_organisation`; - -CREATE TABLE `lams_organisation` ( - `organisation_id` bigint(20) NOT NULL AUTO_INCREMENT, - `name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL, - `code` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `description` mediumtext COLLATE utf8mb4_unicode_ci, - `parent_organisation_id` bigint(20) DEFAULT NULL, - `organisation_type_id` int(3) NOT NULL DEFAULT '0', - `create_date` datetime NOT NULL, - `created_by` bigint(20) NOT NULL, - `organisation_state_id` int(3) NOT NULL, - `admin_add_new_users` tinyint(1) NOT NULL DEFAULT '0', - `admin_browse_all_users` tinyint(1) NOT NULL DEFAULT '0', - `admin_change_status` tinyint(1) NOT NULL DEFAULT '0', - `admin_create_guest` tinyint(1) NOT NULL DEFAULT '0', - `enable_course_notifications` tinyint(1) NOT NULL DEFAULT '0', - `enable_learner_gradebook` tinyint(1) NOT NULL DEFAULT '0', - `enable_single_activity_lessons` tinyint(1) NOT NULL DEFAULT '1', - `enable_live_edit` tinyint(1) NOT NULL DEFAULT '1', - `enable_kumalive` tinyint(1) NOT NULL DEFAULT '0', - `archived_date` datetime DEFAULT NULL, - `ordered_lesson_ids` text COLLATE utf8mb4_unicode_ci, - PRIMARY KEY (`organisation_id`), - KEY `organisation_type_id` (`organisation_type_id`), - KEY `parent_organisation_id` (`parent_organisation_id`), - KEY `organisation_state_id` (`organisation_state_id`), - CONSTRAINT `FK_lams_organisation_1` FOREIGN KEY (`organisation_type_id`) REFERENCES `lams_organisation_type` (`organisation_type_id`), - CONSTRAINT `FK_lams_organisation_3` FOREIGN KEY (`parent_organisation_id`) REFERENCES `lams_organisation` (`organisation_id`), - CONSTRAINT `FK_lams_organisation_4` FOREIGN KEY (`organisation_state_id`) REFERENCES `lams_organisation_state` (`organisation_state_id`) +CREATE TABLE lams_organisation ( + organisation_id bigint NOT NULL AUTO_INCREMENT, + `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `description` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + parent_organisation_id bigint DEFAULT NULL, + organisation_type_id int NOT NULL DEFAULT '0', + create_date datetime NOT NULL, + created_by bigint NOT NULL, + organisation_state_id int NOT NULL, + admin_add_new_users tinyint(1) NOT NULL DEFAULT '0', + admin_browse_all_users tinyint(1) NOT NULL DEFAULT '0', + admin_change_status tinyint(1) NOT NULL DEFAULT '0', + admin_create_guest tinyint(1) NOT NULL DEFAULT '0', + enable_course_notifications tinyint(1) NOT NULL DEFAULT '0', + enable_learner_gradebook tinyint(1) NOT NULL DEFAULT '0', + enable_single_activity_lessons tinyint(1) NOT NULL DEFAULT '1', + enable_live_edit tinyint(1) NOT NULL DEFAULT '1', + enable_kumalive tinyint(1) NOT NULL DEFAULT '0', + archived_date datetime DEFAULT NULL, + ordered_lesson_ids text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + PRIMARY KEY (organisation_id), + KEY organisation_type_id (organisation_type_id), + KEY parent_organisation_id (parent_organisation_id), + KEY organisation_state_id (organisation_state_id), + CONSTRAINT FK_lams_organisation_1 FOREIGN KEY (organisation_type_id) REFERENCES lams_organisation_type (organisation_type_id), + CONSTRAINT FK_lams_organisation_3 FOREIGN KEY (parent_organisation_id) REFERENCES lams_organisation (organisation_id), + CONSTRAINT FK_lams_organisation_4 FOREIGN KEY (organisation_state_id) REFERENCES lams_organisation_state (organisation_state_id) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_organisation_group; --- --- Table structure for table `lams_organisation_group` --- -DROP TABLE IF EXISTS `lams_organisation_group`; - -CREATE TABLE `lams_organisation_group` ( - `group_id` bigint(20) NOT NULL AUTO_INCREMENT, - `grouping_id` bigint(20) NOT NULL, - `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - PRIMARY KEY (`group_id`), - KEY `FK_lams_organisation_group_1` (`grouping_id`), - CONSTRAINT `FK_lams_organisation_group_1` FOREIGN KEY (`grouping_id`) REFERENCES `lams_organisation_grouping` (`grouping_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_organisation_group ( + group_id bigint NOT NULL AUTO_INCREMENT, + grouping_id bigint NOT NULL, + `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (group_id), + KEY FK_lams_organisation_group_1 (grouping_id), + CONSTRAINT FK_lams_organisation_group_1 FOREIGN KEY (grouping_id) REFERENCES lams_organisation_grouping (grouping_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_organisation_grouping; --- --- Table structure for table `lams_organisation_grouping` --- -DROP TABLE IF EXISTS `lams_organisation_grouping`; - -CREATE TABLE `lams_organisation_grouping` ( - `grouping_id` bigint(20) NOT NULL AUTO_INCREMENT, - `organisation_id` bigint(20) NOT NULL, - `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - PRIMARY KEY (`grouping_id`), - KEY `FK_lams_organisation_grouping_1` (`organisation_id`), - CONSTRAINT `FK_lams_organisation_grouping_1` FOREIGN KEY (`organisation_id`) REFERENCES `lams_organisation` (`organisation_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_organisation_grouping ( + grouping_id bigint NOT NULL AUTO_INCREMENT, + organisation_id bigint NOT NULL, + `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (grouping_id), + KEY FK_lams_organisation_grouping_1 (organisation_id), + CONSTRAINT FK_lams_organisation_grouping_1 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_organisation_state; --- --- Table structure for table `lams_organisation_state` --- -DROP TABLE IF EXISTS `lams_organisation_state`; - -CREATE TABLE `lams_organisation_state` ( - `organisation_state_id` int(3) NOT NULL, - `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - PRIMARY KEY (`organisation_state_id`) +CREATE TABLE lams_organisation_state ( + organisation_state_id int NOT NULL, + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (organisation_state_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_organisation_type; --- --- Table structure for table `lams_organisation_type` --- -DROP TABLE IF EXISTS `lams_organisation_type`; - -CREATE TABLE `lams_organisation_type` ( - `organisation_type_id` int(3) NOT NULL, - `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, - `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`organisation_type_id`), - UNIQUE KEY `UQ_lams_organisation_type_name` (`name`) +CREATE TABLE lams_organisation_type ( + organisation_type_id int NOT NULL, + `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (organisation_type_id), + UNIQUE KEY UQ_lams_organisation_type_name (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_outcome; --- --- Table structure for table `lams_outcome` --- -DROP TABLE IF EXISTS `lams_outcome`; - -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`), +CREATE TABLE lams_outcome ( + outcome_id mediumint NOT NULL AUTO_INCREMENT, + organisation_id bigint DEFAULT NULL, + scale_id mediumint NOT NULL, + `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + content_folder_id char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + create_by bigint 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 + 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; +DROP TABLE IF EXISTS lams_outcome_mapping; --- --- Table structure for table `lams_outcome_mapping` --- -DROP TABLE IF EXISTS `lams_outcome_mapping`; - -CREATE TABLE `lams_outcome_mapping` ( - `mapping_id` bigint(20) NOT NULL AUTO_INCREMENT, - `outcome_id` mediumint(9) NOT NULL, - `lesson_id` bigint(20) DEFAULT NULL, - `tool_content_id` bigint(20) DEFAULT NULL, - `item_id` bigint(20) DEFAULT NULL, - PRIMARY KEY (`mapping_id`), - KEY `FK_lams_outcome_mapping_1` (`outcome_id`), - KEY `FK_lams_outcome_mapping_2` (`lesson_id`), - KEY `FK_lams_outcome_mapping_3` (`tool_content_id`), - CONSTRAINT `FK_lams_outcome_mapping_1` FOREIGN KEY (`outcome_id`) REFERENCES `lams_outcome` (`outcome_id`) ON DELETE RESTRICT ON UPDATE CASCADE, - CONSTRAINT `FK_lams_outcome_mapping_2` FOREIGN KEY (`lesson_id`) REFERENCES `lams_lesson` (`lesson_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_outcome_mapping_3` FOREIGN KEY (`tool_content_id`) REFERENCES `lams_tool_content` (`tool_content_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_outcome_mapping ( + mapping_id bigint NOT NULL AUTO_INCREMENT, + outcome_id mediumint NOT NULL, + lesson_id bigint DEFAULT NULL, + tool_content_id bigint DEFAULT NULL, + item_id bigint DEFAULT NULL, + PRIMARY KEY (mapping_id), + KEY FK_lams_outcome_mapping_1 (outcome_id), + KEY FK_lams_outcome_mapping_2 (lesson_id), + KEY FK_lams_outcome_mapping_3 (tool_content_id), + CONSTRAINT FK_lams_outcome_mapping_1 FOREIGN KEY (outcome_id) REFERENCES lams_outcome (outcome_id) ON DELETE RESTRICT ON UPDATE CASCADE, + CONSTRAINT FK_lams_outcome_mapping_2 FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_outcome_mapping_3 FOREIGN KEY (tool_content_id) REFERENCES lams_tool_content (tool_content_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_outcome_result; --- --- Table structure for table `lams_outcome_result` --- -DROP TABLE IF EXISTS `lams_outcome_result`; - -CREATE TABLE `lams_outcome_result` ( - `result_id` bigint(20) NOT NULL AUTO_INCREMENT, - `mapping_id` bigint(20) NOT NULL, - `user_id` bigint(20) DEFAULT NULL, - `value` tinyint(4) DEFAULT NULL, - `create_by` bigint(20) DEFAULT NULL, - `create_date_time` datetime NOT NULL, - PRIMARY KEY (`result_id`), - KEY `FK_lams_outcome_result_1` (`mapping_id`), - KEY `FK_lams_outcome_result_2` (`user_id`), - KEY `FK_lams_outcome_result_3` (`create_by`), - CONSTRAINT `FK_lams_outcome_result_1` FOREIGN KEY (`mapping_id`) REFERENCES `lams_outcome_mapping` (`mapping_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_outcome_result_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_outcome_result_3` FOREIGN KEY (`create_by`) REFERENCES `lams_user` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE +CREATE TABLE lams_outcome_result ( + result_id bigint NOT NULL AUTO_INCREMENT, + mapping_id bigint NOT NULL, + user_id bigint DEFAULT NULL, + `value` tinyint DEFAULT NULL, + create_by bigint DEFAULT NULL, + create_date_time datetime NOT NULL, + PRIMARY KEY (result_id), + KEY FK_lams_outcome_result_1 (mapping_id), + KEY FK_lams_outcome_result_2 (user_id), + KEY FK_lams_outcome_result_3 (create_by), + CONSTRAINT FK_lams_outcome_result_1 FOREIGN KEY (mapping_id) REFERENCES lams_outcome_mapping (mapping_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_outcome_result_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_outcome_result_3 FOREIGN KEY (create_by) REFERENCES lams_user (user_id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_outcome_scale; --- --- Table structure for table `lams_outcome_scale` --- -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`), +CREATE TABLE lams_outcome_scale ( + scale_id mediumint NOT NULL AUTO_INCREMENT, + organisation_id bigint DEFAULT NULL, + `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + content_folder_id char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + create_by bigint 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 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 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_outcome_scale_item; --- --- Table structure for table `lams_outcome_scale_item` --- -DROP TABLE IF EXISTS `lams_outcome_scale_item`; - -CREATE TABLE `lams_outcome_scale_item` ( - `item_id` int(11) NOT NULL AUTO_INCREMENT, - `scale_id` mediumint(9) DEFAULT NULL, - `value` tinyint(4) DEFAULT NULL, - `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - PRIMARY KEY (`item_id`), - KEY `FK_lams_outcome_scale_item_1` (`scale_id`), - CONSTRAINT `FK_lams_outcome_scale_item_1` FOREIGN KEY (`scale_id`) REFERENCES `lams_outcome_scale` (`scale_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_outcome_scale_item ( + item_id int NOT NULL AUTO_INCREMENT, + scale_id mediumint DEFAULT NULL, + `value` tinyint DEFAULT NULL, + `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (item_id), + KEY FK_lams_outcome_scale_item_1 (scale_id), + CONSTRAINT FK_lams_outcome_scale_item_1 FOREIGN KEY (scale_id) REFERENCES lams_outcome_scale (scale_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_password_request; --- --- Table structure for table `lams_password_request` --- -DROP TABLE IF EXISTS `lams_password_request`; - -CREATE TABLE `lams_password_request` ( - `request_id` bigint(20) NOT NULL AUTO_INCREMENT, - `user_id` bigint(20) NOT NULL, - `request_key` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `request_date` datetime NOT NULL, - PRIMARY KEY (`request_id`), - UNIQUE KEY `IX_lams_psswd_rqst_key` (`request_key`) +CREATE TABLE lams_password_request ( + request_id bigint NOT NULL AUTO_INCREMENT, + user_id bigint NOT NULL, + request_key char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + request_date datetime NOT NULL, + PRIMARY KEY (request_id), + UNIQUE KEY IX_lams_psswd_rqst_key (request_key) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_planner_activity_metadata; --- --- Table structure for table `lams_planner_activity_metadata` --- -DROP TABLE IF EXISTS `lams_planner_activity_metadata`; - -CREATE TABLE `lams_planner_activity_metadata` ( - `activity_id` bigint(20) NOT NULL, - `collapsed` tinyint(1) DEFAULT '0', - `expanded` tinyint(1) DEFAULT '0', - `hidden` tinyint(1) DEFAULT '0', - `editing_advice` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - KEY `FK_lams_planner_metadata_primary` (`activity_id`), - CONSTRAINT `FK_lams_planner_metadata_primary` FOREIGN KEY (`activity_id`) REFERENCES `lams_learning_activity` (`activity_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_planner_activity_metadata ( + activity_id bigint NOT NULL, + collapsed tinyint(1) DEFAULT '0', + expanded tinyint(1) DEFAULT '0', + hidden tinyint(1) DEFAULT '0', + editing_advice varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + KEY FK_lams_planner_metadata_primary (activity_id), + CONSTRAINT FK_lams_planner_metadata_primary FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_planner_node_role; --- --- Table structure for table `lams_planner_node_role` --- -DROP TABLE IF EXISTS `lams_planner_node_role`; - -CREATE TABLE `lams_planner_node_role` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `node_uid` bigint(20) NOT NULL, - `user_id` bigint(20) NOT NULL, - `role_id` int(6) NOT NULL, - PRIMARY KEY (`uid`), - KEY `FK_planner_node_role_user` (`user_id`), - KEY `FK_planner_node_role_node` (`node_uid`), - KEY `FK_planner_node_role_role` (`role_id`), - CONSTRAINT `FK_planner_node_role_node` FOREIGN KEY (`node_uid`) REFERENCES `lams_planner_nodes` (`uid`) ON DELETE CASCADE, - CONSTRAINT `FK_planner_node_role_role` FOREIGN KEY (`role_id`) REFERENCES `lams_role` (`role_id`) ON DELETE CASCADE, - CONSTRAINT `FK_planner_node_role_user` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE +CREATE TABLE lams_planner_node_role ( + uid bigint NOT NULL AUTO_INCREMENT, + node_uid bigint NOT NULL, + user_id bigint NOT NULL, + role_id int NOT NULL, + PRIMARY KEY (uid), + KEY FK_planner_node_role_user (user_id), + KEY FK_planner_node_role_node (node_uid), + KEY FK_planner_node_role_role (role_id), + CONSTRAINT FK_planner_node_role_node FOREIGN KEY (node_uid) REFERENCES lams_planner_nodes (uid) ON DELETE CASCADE, + CONSTRAINT FK_planner_node_role_role FOREIGN KEY (role_id) REFERENCES lams_role (role_id) ON DELETE CASCADE, + CONSTRAINT FK_planner_node_role_user FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_planner_nodes; --- --- Table structure for table `lams_planner_nodes` --- -DROP TABLE IF EXISTS `lams_planner_nodes`; - -CREATE TABLE `lams_planner_nodes` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `parent_uid` bigint(20) DEFAULT NULL, - `order_id` tinyint(3) unsigned NOT NULL, +CREATE TABLE lams_planner_nodes ( + uid bigint NOT NULL AUTO_INCREMENT, + parent_uid bigint DEFAULT NULL, + order_id tinyint unsigned NOT NULL, `locked` tinyint(1) NOT NULL DEFAULT '0', - `content_folder_id` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `brief_desc` mediumtext COLLATE utf8mb4_unicode_ci, - `full_desc` mediumtext COLLATE utf8mb4_unicode_ci, - `ld_id` bigint(20) DEFAULT NULL, - `user_id` bigint(20) DEFAULT NULL, - `permissions` int(11) DEFAULT NULL, - PRIMARY KEY (`uid`), - UNIQUE KEY `parent_uid` (`parent_uid`,`order_id`), - KEY `FK_lams_planner_node_user` (`user_id`), - CONSTRAINT `FK_lams_planner_node_parent` FOREIGN KEY (`parent_uid`) REFERENCES `lams_planner_nodes` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_planner_node_user` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE SET NULL ON UPDATE SET NULL + content_folder_id char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + title varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + brief_desc mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + full_desc mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + ld_id bigint DEFAULT NULL, + user_id bigint DEFAULT NULL, + permissions int DEFAULT NULL, + PRIMARY KEY (uid), + UNIQUE KEY parent_uid (parent_uid,order_id), + KEY FK_lams_planner_node_user (user_id), + CONSTRAINT FK_lams_planner_node_parent FOREIGN KEY (parent_uid) REFERENCES lams_planner_nodes (uid) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_planner_node_user FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE SET NULL ON UPDATE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_planner_recent_learning_designs; --- --- Table structure for table `lams_planner_recent_learning_designs` --- -DROP TABLE IF EXISTS `lams_planner_recent_learning_designs`; - -CREATE TABLE `lams_planner_recent_learning_designs` ( - `user_id` bigint(20) NOT NULL, - `learning_design_id` bigint(20) NOT NULL, - `last_modified_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - PRIMARY KEY (`user_id`,`learning_design_id`), - KEY `FK_lams_planner_recent_learning_designs_2` (`learning_design_id`), - CONSTRAINT `FK_lams_planner_recent_learning_designs_1` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_planner_recent_learning_designs_2` FOREIGN KEY (`learning_design_id`) REFERENCES `lams_learning_design` (`learning_design_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_planner_recent_learning_designs ( + user_id bigint NOT NULL, + learning_design_id bigint NOT NULL, + last_modified_date timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (user_id,learning_design_id), + KEY FK_lams_planner_recent_learning_designs_2 (learning_design_id), + CONSTRAINT FK_lams_planner_recent_learning_designs_1 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_planner_recent_learning_designs_2 FOREIGN KEY (learning_design_id) REFERENCES lams_learning_design (learning_design_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_policy; --- --- Table structure for table `lams_policy` --- -DROP TABLE IF EXISTS `lams_policy`; - -CREATE TABLE `lams_policy` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `policy_id` bigint(20) DEFAULT NULL, - `created_by` bigint(20) NOT NULL, - `policy_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `version` mediumtext COLLATE utf8mb4_unicode_ci, - `summary` text COLLATE utf8mb4_unicode_ci, - `full_policy` text COLLATE utf8mb4_unicode_ci, - `last_modified` datetime NOT NULL, - `policy_state_id` int(3) NOT NULL, - `policy_type_id` int(3) NOT NULL, - PRIMARY KEY (`uid`), - KEY `created_by` (`created_by`), - KEY `policy_state_id` (`policy_state_id`), - KEY `policy_type_id` (`policy_type_id`), - CONSTRAINT `FK_lams_lesson_1` FOREIGN KEY (`created_by`) REFERENCES `lams_user` (`user_id`), - CONSTRAINT `FK_lams_policy_2` FOREIGN KEY (`policy_state_id`) REFERENCES `lams_policy_state` (`policy_state_id`), - CONSTRAINT `FK_lams_policy_3` FOREIGN KEY (`policy_type_id`) REFERENCES `lams_policy_type` (`policy_type_id`) +CREATE TABLE lams_policy ( + uid bigint NOT NULL AUTO_INCREMENT, + policy_id bigint DEFAULT NULL, + created_by bigint NOT NULL, + policy_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + version mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + summary text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + full_policy text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + last_modified datetime NOT NULL, + policy_state_id int NOT NULL, + policy_type_id int NOT NULL, + PRIMARY KEY (uid), + KEY created_by (created_by), + KEY policy_state_id (policy_state_id), + KEY policy_type_id (policy_type_id), + CONSTRAINT FK_lams_lesson_1 FOREIGN KEY (created_by) REFERENCES lams_user (user_id), + CONSTRAINT FK_lams_policy_2 FOREIGN KEY (policy_state_id) REFERENCES lams_policy_state (policy_state_id), + CONSTRAINT FK_lams_policy_3 FOREIGN KEY (policy_type_id) REFERENCES lams_policy_type (policy_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_policy_consent; --- --- Table structure for table `lams_policy_consent` --- -DROP TABLE IF EXISTS `lams_policy_consent`; - -CREATE TABLE `lams_policy_consent` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `date_agreed_on` datetime NOT NULL, - `policy_uid` bigint(20) NOT NULL, - `user_id` bigint(20) NOT NULL, - PRIMARY KEY (`uid`), - KEY `policy_uid` (`policy_uid`), - KEY `user_id` (`user_id`), - CONSTRAINT `FK_lams_consent_1_1` FOREIGN KEY (`policy_uid`) REFERENCES `lams_policy` (`uid`), - CONSTRAINT `FK_lams_consent_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) +CREATE TABLE lams_policy_consent ( + uid bigint NOT NULL AUTO_INCREMENT, + date_agreed_on datetime NOT NULL, + policy_uid bigint NOT NULL, + user_id bigint NOT NULL, + PRIMARY KEY (uid), + KEY policy_uid (policy_uid), + KEY user_id (user_id), + CONSTRAINT FK_lams_consent_1_1 FOREIGN KEY (policy_uid) REFERENCES lams_policy (uid), + CONSTRAINT FK_lams_consent_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_policy_state; --- --- Table structure for table `lams_policy_state` --- -DROP TABLE IF EXISTS `lams_policy_state`; - - -CREATE TABLE `lams_policy_state` ( - `policy_state_id` int(3) NOT NULL, - `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`policy_state_id`) +CREATE TABLE lams_policy_state ( + policy_state_id int NOT NULL, + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (policy_state_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_policy_type; --- --- Table structure for table `lams_policy_type` --- -DROP TABLE IF EXISTS `lams_policy_type`; - -CREATE TABLE `lams_policy_type` ( - `policy_type_id` int(3) NOT NULL, - `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`policy_type_id`) +CREATE TABLE lams_policy_type ( + policy_type_id int NOT NULL, + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (policy_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_presence_chat_msgs; --- --- Table structure for table `lams_presence_chat_msgs` --- -DROP TABLE IF EXISTS `lams_presence_chat_msgs`; - -CREATE TABLE `lams_presence_chat_msgs` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `lesson_id` bigint(20) DEFAULT NULL, - `from_user` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `to_user` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `date_sent` datetime DEFAULT NULL, - `message` varchar(1023) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - PRIMARY KEY (`uid`), - KEY `FK_lams_presence_chat_msgs_lesson` (`lesson_id`), - KEY `idx_lams_presence_chat_msgs_from` (`from_user`), - KEY `idx_lams_presence_chat_msgs_to` (`to_user`), - CONSTRAINT `FK_lams_presence_chat_msgs_lesson` FOREIGN KEY (`lesson_id`) REFERENCES `lams_lesson` (`lesson_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_presence_chat_msgs ( + uid bigint NOT NULL AUTO_INCREMENT, + lesson_id bigint DEFAULT NULL, + from_user varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + to_user varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + date_sent datetime DEFAULT NULL, + message varchar(1023) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (uid), + KEY FK_lams_presence_chat_msgs_lesson (lesson_id), + KEY idx_lams_presence_chat_msgs_from (from_user), + KEY idx_lams_presence_chat_msgs_to (to_user), + CONSTRAINT FK_lams_presence_chat_msgs_lesson FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_presence_user; --- --- Table structure for table `lams_presence_user` --- -DROP TABLE IF EXISTS `lams_presence_user`; - -CREATE TABLE `lams_presence_user` ( - `nickname` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, - `lesson_id` bigint(20) NOT NULL, - `last_presence` datetime DEFAULT NULL, - PRIMARY KEY (`nickname`,`lesson_id`), - KEY `FK_lams_presence_user_lesson` (`lesson_id`), - CONSTRAINT `FK_lams_presence_user_lesson` FOREIGN KEY (`lesson_id`) REFERENCES `lams_lesson` (`lesson_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_presence_user ( + nickname varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + lesson_id bigint NOT NULL, + last_presence datetime DEFAULT NULL, + PRIMARY KEY (nickname,lesson_id), + KEY FK_lams_presence_user_lesson (lesson_id), + CONSTRAINT FK_lams_presence_user_lesson FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_progress_attempted; --- --- Table structure for table `lams_progress_attempted` --- -DROP TABLE IF EXISTS `lams_progress_attempted`; - -CREATE TABLE `lams_progress_attempted` ( - `learner_progress_id` bigint(20) NOT NULL, - `activity_id` bigint(20) NOT NULL, - `start_date_time` datetime DEFAULT NULL, - PRIMARY KEY (`learner_progress_id`,`activity_id`), - KEY `learner_progress_id` (`learner_progress_id`), - KEY `activity_id` (`activity_id`), - CONSTRAINT `FK_lams_progress_current_1` FOREIGN KEY (`learner_progress_id`) REFERENCES `lams_learner_progress` (`learner_progress_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_progress_current_2` FOREIGN KEY (`activity_id`) REFERENCES `lams_learning_activity` (`activity_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_progress_attempted ( + learner_progress_id bigint NOT NULL, + activity_id bigint NOT NULL, + start_date_time datetime DEFAULT NULL, + PRIMARY KEY (learner_progress_id,activity_id), + KEY learner_progress_id (learner_progress_id), + KEY activity_id (activity_id), + CONSTRAINT FK_lams_progress_current_1 FOREIGN KEY (learner_progress_id) REFERENCES lams_learner_progress (learner_progress_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_progress_current_2 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_progress_attempted_archive; --- --- Table structure for table `lams_progress_attempted_archive` --- -DROP TABLE IF EXISTS `lams_progress_attempted_archive`; - -CREATE TABLE `lams_progress_attempted_archive` ( - `learner_progress_id` bigint(20) NOT NULL, - `activity_id` bigint(20) NOT NULL, - `start_date_time` datetime DEFAULT NULL, - PRIMARY KEY (`learner_progress_id`,`activity_id`), - KEY `FK_lams_progress_current_archive_2` (`activity_id`), - CONSTRAINT `FK_lams_progress_current_archive_1` FOREIGN KEY (`learner_progress_id`) REFERENCES `lams_learner_progress_archive` (`learner_progress_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_progress_current_archive_2` FOREIGN KEY (`activity_id`) REFERENCES `lams_learning_activity` (`activity_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_progress_attempted_archive ( + learner_progress_id bigint NOT NULL, + activity_id bigint NOT NULL, + start_date_time datetime DEFAULT NULL, + PRIMARY KEY (learner_progress_id,activity_id), + KEY FK_lams_progress_current_archive_2 (activity_id), + CONSTRAINT FK_lams_progress_current_archive_1 FOREIGN KEY (learner_progress_id) REFERENCES lams_learner_progress_archive (learner_progress_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_progress_current_archive_2 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_progress_completed; --- --- Table structure for table `lams_progress_completed` --- -DROP TABLE IF EXISTS `lams_progress_completed`; - -CREATE TABLE `lams_progress_completed` ( - `learner_progress_id` bigint(20) NOT NULL, - `activity_id` bigint(20) NOT NULL, - `completed_date_time` datetime DEFAULT NULL, - `start_date_time` datetime DEFAULT NULL, - PRIMARY KEY (`learner_progress_id`,`activity_id`), - KEY `learner_progress_id` (`learner_progress_id`), - KEY `activity_id` (`activity_id`), - CONSTRAINT `FK_lams_progress_completed_1` FOREIGN KEY (`learner_progress_id`) REFERENCES `lams_learner_progress` (`learner_progress_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_progress_completed_2` FOREIGN KEY (`activity_id`) REFERENCES `lams_learning_activity` (`activity_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_progress_completed ( + learner_progress_id bigint NOT NULL, + activity_id bigint NOT NULL, + completed_date_time datetime DEFAULT NULL, + start_date_time datetime DEFAULT NULL, + PRIMARY KEY (learner_progress_id,activity_id), + KEY learner_progress_id (learner_progress_id), + KEY activity_id (activity_id), + CONSTRAINT FK_lams_progress_completed_1 FOREIGN KEY (learner_progress_id) REFERENCES lams_learner_progress (learner_progress_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_progress_completed_2 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_progress_completed_archive; --- --- Table structure for table `lams_progress_completed_archive` --- -DROP TABLE IF EXISTS `lams_progress_completed_archive`; - -CREATE TABLE `lams_progress_completed_archive` ( - `learner_progress_id` bigint(20) NOT NULL, - `activity_id` bigint(20) NOT NULL, - `completed_date_time` datetime DEFAULT NULL, - `start_date_time` datetime DEFAULT NULL, - PRIMARY KEY (`learner_progress_id`,`activity_id`), - KEY `FK_lams_progress_completed_archive_2` (`activity_id`), - CONSTRAINT `FK_lams_progress_completed_archive_1` FOREIGN KEY (`learner_progress_id`) REFERENCES `lams_learner_progress_archive` (`learner_progress_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_progress_completed_archive_2` FOREIGN KEY (`activity_id`) REFERENCES `lams_learning_activity` (`activity_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_progress_completed_archive ( + learner_progress_id bigint NOT NULL, + activity_id bigint NOT NULL, + completed_date_time datetime DEFAULT NULL, + start_date_time datetime DEFAULT NULL, + PRIMARY KEY (learner_progress_id,activity_id), + KEY FK_lams_progress_completed_archive_2 (activity_id), + CONSTRAINT FK_lams_progress_completed_archive_1 FOREIGN KEY (learner_progress_id) REFERENCES lams_learner_progress_archive (learner_progress_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_progress_completed_archive_2 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_rating; --- --- Table structure for table `lams_rating` --- -DROP TABLE IF EXISTS `lams_rating`; - -CREATE TABLE `lams_rating` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `rating_criteria_id` bigint(20) NOT NULL, - `item_id` bigint(20) DEFAULT NULL, - `user_id` bigint(20) NOT NULL, - `rating` float DEFAULT NULL, - `tool_session_id` bigint(20) DEFAULT NULL, - PRIMARY KEY (`uid`), - KEY `rating_criteria_id` (`rating_criteria_id`), - KEY `user_id` (`user_id`), - KEY `FK_lams_rating_3` (`tool_session_id`), - CONSTRAINT `FK_lams_rating_1` FOREIGN KEY (`rating_criteria_id`) REFERENCES `lams_rating_criteria` (`rating_criteria_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_rating_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_rating ( + uid bigint NOT NULL AUTO_INCREMENT, + rating_criteria_id bigint NOT NULL, + item_id bigint DEFAULT NULL, + user_id bigint NOT NULL, + rating float DEFAULT NULL, + tool_session_id bigint DEFAULT NULL, + PRIMARY KEY (uid), + KEY rating_criteria_id (rating_criteria_id), + KEY user_id (user_id), + KEY FK_lams_rating_3 (tool_session_id), + CONSTRAINT FK_lams_rating_1 FOREIGN KEY (rating_criteria_id) REFERENCES lams_rating_criteria (rating_criteria_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_rating_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_rating_comment; --- --- Table structure for table `lams_rating_comment` --- -DROP TABLE IF EXISTS `lams_rating_comment`; - -CREATE TABLE `lams_rating_comment` ( - `uid` bigint(20) NOT NULL AUTO_INCREMENT, - `rating_criteria_id` bigint(20) NOT NULL, - `item_id` bigint(20) DEFAULT NULL, - `user_id` bigint(20) NOT NULL, - `comment` mediumtext COLLATE utf8mb4_unicode_ci, - `posted_date` datetime DEFAULT NULL, - `tool_session_id` bigint(20) DEFAULT NULL, - PRIMARY KEY (`uid`), - KEY `rating_criteria_id` (`rating_criteria_id`), - KEY `user_id` (`user_id`), - KEY `FK_lams_rating_comment_3` (`tool_session_id`), - CONSTRAINT `FK_lams_rating_comment_1` FOREIGN KEY (`rating_criteria_id`) REFERENCES `lams_rating_criteria` (`rating_criteria_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_rating_comment_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_rating_comment ( + uid bigint NOT NULL AUTO_INCREMENT, + rating_criteria_id bigint NOT NULL, + item_id bigint DEFAULT NULL, + user_id bigint NOT NULL, + `comment` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + posted_date datetime DEFAULT NULL, + tool_session_id bigint DEFAULT NULL, + PRIMARY KEY (uid), + KEY rating_criteria_id (rating_criteria_id), + KEY user_id (user_id), + KEY FK_lams_rating_comment_3 (tool_session_id), + CONSTRAINT FK_lams_rating_comment_1 FOREIGN KEY (rating_criteria_id) REFERENCES lams_rating_criteria (rating_criteria_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_rating_comment_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_rating_criteria; --- --- Table structure for table `lams_rating_criteria` --- -DROP TABLE IF EXISTS `lams_rating_criteria`; - -CREATE TABLE `lams_rating_criteria` ( - `rating_criteria_id` bigint(20) NOT NULL AUTO_INCREMENT, - `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `rating_criteria_type_id` int(11) NOT NULL DEFAULT '0', - `comments_enabled` tinyint(1) NOT NULL DEFAULT '0', - `comments_min_words_limit` int(11) DEFAULT '0', - `order_id` int(11) NOT NULL, - `tool_content_id` bigint(20) DEFAULT NULL, - `item_id` bigint(20) DEFAULT NULL, - `lesson_id` bigint(20) DEFAULT NULL, - `rating_style` bigint(20) NOT NULL DEFAULT '1', - `max_rating` bigint(20) NOT NULL DEFAULT '5', - `minimum_rates` int(11) DEFAULT '0', - `maximum_rates` int(11) DEFAULT '0', - PRIMARY KEY (`rating_criteria_id`), - KEY `rating_criteria_type_id` (`rating_criteria_type_id`), - KEY `tool_content_id` (`tool_content_id`), - KEY `lesson_id` (`lesson_id`), - CONSTRAINT `FK_lams_rating_criteria_1` FOREIGN KEY (`rating_criteria_type_id`) REFERENCES `lams_rating_criteria_type` (`rating_criteria_type_id`), - CONSTRAINT `FK_lams_rating_criteria_2` FOREIGN KEY (`tool_content_id`) REFERENCES `lams_tool_content` (`tool_content_id`), - CONSTRAINT `FK_lams_rating_criteria_3` FOREIGN KEY (`lesson_id`) REFERENCES `lams_lesson` (`lesson_id`) +CREATE TABLE lams_rating_criteria ( + rating_criteria_id bigint NOT NULL AUTO_INCREMENT, + title varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + rating_criteria_type_id int NOT NULL DEFAULT '0', + comments_enabled tinyint(1) NOT NULL DEFAULT '0', + comments_min_words_limit int DEFAULT '0', + order_id int NOT NULL, + tool_content_id bigint DEFAULT NULL, + item_id bigint DEFAULT NULL, + lesson_id bigint DEFAULT NULL, + rating_style bigint NOT NULL DEFAULT '1', + max_rating bigint NOT NULL DEFAULT '5', + minimum_rates int DEFAULT '0', + maximum_rates int DEFAULT '0', + PRIMARY KEY (rating_criteria_id), + KEY rating_criteria_type_id (rating_criteria_type_id), + KEY tool_content_id (tool_content_id), + KEY lesson_id (lesson_id), + CONSTRAINT FK_lams_rating_criteria_1 FOREIGN KEY (rating_criteria_type_id) REFERENCES lams_rating_criteria_type (rating_criteria_type_id), + CONSTRAINT FK_lams_rating_criteria_2 FOREIGN KEY (tool_content_id) REFERENCES lams_tool_content (tool_content_id), + CONSTRAINT FK_lams_rating_criteria_3 FOREIGN KEY (lesson_id) REFERENCES lams_lesson (lesson_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_rating_criteria_type; --- --- Table structure for table `lams_rating_criteria_type` --- -DROP TABLE IF EXISTS `lams_rating_criteria_type`; - -CREATE TABLE `lams_rating_criteria_type` ( - `rating_criteria_type_id` int(11) NOT NULL, - `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`rating_criteria_type_id`) +CREATE TABLE lams_rating_criteria_type ( + rating_criteria_type_id int NOT NULL, + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (rating_criteria_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_role; --- --- Table structure for table `lams_role` --- -DROP TABLE IF EXISTS `lams_role`; - -CREATE TABLE `lams_role` ( - `role_id` int(6) NOT NULL, - `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, - `description` text COLLATE utf8mb4_unicode_ci, - `create_date` datetime NOT NULL, - PRIMARY KEY (`role_id`), - KEY `gname` (`name`) +CREATE TABLE lams_role ( + role_id int NOT NULL, + `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + create_date datetime NOT NULL, + PRIMARY KEY (role_id), + KEY gname (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_signup_organisation; --- --- Table structure for table `lams_signup_organisation` --- -DROP TABLE IF EXISTS `lams_signup_organisation`; - -CREATE TABLE `lams_signup_organisation` ( - `signup_organisation_id` bigint(20) NOT NULL AUTO_INCREMENT, - `organisation_id` bigint(20) NOT NULL, - `add_to_lessons` tinyint(1) DEFAULT '1', - `add_as_staff` tinyint(1) DEFAULT '0', - `add_with_author` tinyint(1) DEFAULT '0', - `add_with_monitor` tinyint(1) DEFAULT '0', - `email_verify` tinyint(1) DEFAULT '0', - `course_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `blurb` text COLLATE utf8mb4_unicode_ci, - `create_date` datetime DEFAULT NULL, - `disabled` tinyint(1) DEFAULT '0', - `conTEXT` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, - `login_tab_active` tinyint(1) DEFAULT '0', - PRIMARY KEY (`signup_organisation_id`), +CREATE TABLE lams_signup_organisation ( + signup_organisation_id bigint NOT NULL AUTO_INCREMENT, + organisation_id bigint NOT NULL, + add_to_lessons tinyint(1) DEFAULT '1', + add_as_staff tinyint(1) DEFAULT '0', + add_with_author tinyint(1) DEFAULT '0', + add_with_monitor tinyint(1) DEFAULT '0', + email_verify tinyint(1) DEFAULT '0', + course_key varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + blurb text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + create_date datetime DEFAULT NULL, + disabled tinyint(1) DEFAULT '0', + `conTEXT` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + login_tab_active tinyint(1) DEFAULT '0', + PRIMARY KEY (signup_organisation_id), UNIQUE KEY `conTEXT` (`conTEXT`), - KEY `organisation_id` (`organisation_id`) + KEY organisation_id (organisation_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_supported_locale; --- --- Table structure for table `lams_supported_locale` --- -DROP TABLE IF EXISTS `lams_supported_locale`; - -CREATE TABLE `lams_supported_locale` ( - `locale_id` int(11) NOT NULL AUTO_INCREMENT, - `language_iso_code` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL, - `country_iso_code` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `direction` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL, - `fckeditor_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - PRIMARY KEY (`locale_id`) +CREATE TABLE lams_supported_locale ( + locale_id int NOT NULL AUTO_INCREMENT, + language_iso_code varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + country_iso_code varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + direction varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + fckeditor_code varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (locale_id) ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_system_tool; --- --- Table structure for table `lams_system_tool` --- -DROP TABLE IF EXISTS `lams_system_tool`; +CREATE TABLE lams_system_tool ( + system_tool_id bigint NOT NULL AUTO_INCREMENT, + learning_activity_type_id int NOT NULL, + tool_display_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + learner_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + learner_preview_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + learner_progress_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + monitor_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + contribute_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + help_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + create_date_time datetime NOT NULL, + admin_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + pedagogical_planner_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + PRIMARY KEY (system_tool_id), + UNIQUE KEY UQ_systool_activity_type (learning_activity_type_id), + CONSTRAINT FK_lams_system_tool FOREIGN KEY (learning_activity_type_id) REFERENCES lams_learning_activity_type (learning_activity_type_id) +) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE `lams_system_tool` ( - `system_tool_id` bigint(20) NOT NULL AUTO_INCREMENT, - `learning_activity_type_id` int(11) NOT NULL, - `tool_display_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `description` text COLLATE utf8mb4_unicode_ci, - `learner_url` text COLLATE utf8mb4_unicode_ci, - `learner_preview_url` text COLLATE utf8mb4_unicode_ci, - `learner_progress_url` text COLLATE utf8mb4_unicode_ci, - `monitor_url` text COLLATE utf8mb4_unicode_ci, - `contribute_url` text COLLATE utf8mb4_unicode_ci, - `help_url` text COLLATE utf8mb4_unicode_ci, - `create_date_time` datetime NOT NULL, - `admin_url` text COLLATE utf8mb4_unicode_ci, - `pedagogical_planner_url` text COLLATE utf8mb4_unicode_ci, - PRIMARY KEY (`system_tool_id`), - UNIQUE KEY `UQ_systool_activity_type` (`learning_activity_type_id`), - CONSTRAINT `FK_lams_system_tool` FOREIGN KEY (`learning_activity_type_id`) REFERENCES `lams_learning_activity_type` (`learning_activity_type_id`) -) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_text_search_condition; --- --- Table structure for table `lams_text_search_condition` --- - -DROP TABLE IF EXISTS `lams_text_search_condition`; - -CREATE TABLE `lams_text_search_condition` ( - `condition_id` bigint(20) NOT NULL, - `text_search_all_words` text COLLATE utf8mb4_unicode_ci, - `text_search_phrase` text COLLATE utf8mb4_unicode_ci, - `text_search_any_words` text COLLATE utf8mb4_unicode_ci, - `text_search_excluded_words` text COLLATE utf8mb4_unicode_ci, - PRIMARY KEY (`condition_id`), - CONSTRAINT `TextSearchConditionInheritance` FOREIGN KEY (`condition_id`) REFERENCES `lams_branch_condition` (`condition_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_text_search_condition ( + condition_id bigint NOT NULL, + text_search_all_words text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + text_search_phrase text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + text_search_any_words text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + text_search_excluded_words text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + PRIMARY KEY (condition_id), + CONSTRAINT TextSearchConditionInheritance FOREIGN KEY (condition_id) REFERENCES lams_branch_condition (condition_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_theme; --- --- Table structure for table `lams_theme` --- -DROP TABLE IF EXISTS `lams_theme`; - -CREATE TABLE `lams_theme` ( - `theme_id` bigint(20) NOT NULL AUTO_INCREMENT, - `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, - `description` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `image_directory` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - PRIMARY KEY (`theme_id`), - UNIQUE KEY `UQ_name` (`name`) +CREATE TABLE lams_theme ( + theme_id bigint NOT NULL AUTO_INCREMENT, + `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `description` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + image_directory varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (theme_id), + UNIQUE KEY UQ_name (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_timezone; --- --- Table structure for table `lams_timezone` --- -DROP TABLE IF EXISTS `lams_timezone`; - -CREATE TABLE `lams_timezone` ( - `id` bigint(20) NOT NULL AUTO_INCREMENT, - `timezone_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `server_timezone` tinyint(1) DEFAULT '0', - PRIMARY KEY (`id`) +CREATE TABLE lams_timezone ( + id bigint NOT NULL AUTO_INCREMENT, + timezone_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + server_timezone tinyint(1) DEFAULT '0', + PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_tool; --- --- Table structure for table `lams_tool` --- -DROP TABLE IF EXISTS `lams_tool`; +CREATE TABLE lams_tool ( + tool_id bigint NOT NULL AUTO_INCREMENT, + tool_signature varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + service_name varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + tool_display_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + tool_identifier varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + tool_version varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + learning_library_id bigint DEFAULT NULL, + default_tool_content_id bigint DEFAULT NULL, + valid_flag tinyint(1) NOT NULL DEFAULT '1', + grouping_support_type_id int NOT NULL, + learner_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + learner_preview_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + learner_progress_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + author_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + monitor_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + pedagogical_planner_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + help_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + create_date_time datetime NOT NULL, + language_file varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + modified_date_time datetime DEFAULT NULL, + admin_url text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, + supports_outputs tinyint(1) DEFAULT '0', + ext_lms_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (tool_id), + UNIQUE KEY UQ_lams_tool_sig (tool_signature), + UNIQUE KEY UQ_lams_tool_class_name (service_name), + KEY learning_library_id (learning_library_id), + KEY grouping_support_type_id (grouping_support_type_id), + CONSTRAINT FK_lams_tool_1 FOREIGN KEY (learning_library_id) REFERENCES lams_learning_library (learning_library_id), + CONSTRAINT FK_lams_tool_2 FOREIGN KEY (grouping_support_type_id) REFERENCES lams_grouping_support_type (grouping_support_type_id) +) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE `lams_tool` ( - `tool_id` bigint(20) NOT NULL AUTO_INCREMENT, - `tool_signature` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, - `service_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, - `tool_display_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `description` text COLLATE utf8mb4_unicode_ci, - `tool_identifier` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, - `tool_version` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, - `learning_library_id` bigint(20) DEFAULT NULL, - `default_tool_content_id` bigint(20) DEFAULT NULL, - `valid_flag` tinyint(1) NOT NULL DEFAULT '1', - `grouping_support_type_id` int(3) NOT NULL, - `learner_url` text COLLATE utf8mb4_unicode_ci NOT NULL, - `learner_preview_url` text COLLATE utf8mb4_unicode_ci, - `learner_progress_url` text COLLATE utf8mb4_unicode_ci, - `author_url` text COLLATE utf8mb4_unicode_ci NOT NULL, - `monitor_url` text COLLATE utf8mb4_unicode_ci, - `pedagogical_planner_url` text COLLATE utf8mb4_unicode_ci, - `help_url` text COLLATE utf8mb4_unicode_ci, - `create_date_time` datetime NOT NULL, - `language_file` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `modified_date_time` datetime DEFAULT NULL, - `admin_url` text COLLATE utf8mb4_unicode_ci, - `supports_outputs` tinyint(1) DEFAULT '0', - `ext_lms_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - PRIMARY KEY (`tool_id`), - UNIQUE KEY `UQ_lams_tool_sig` (`tool_signature`), - UNIQUE KEY `UQ_lams_tool_class_name` (`service_name`), - KEY `learning_library_id` (`learning_library_id`), - KEY `grouping_support_type_id` (`grouping_support_type_id`), - CONSTRAINT `FK_lams_tool_1` FOREIGN KEY (`learning_library_id`) REFERENCES `lams_learning_library` (`learning_library_id`), - CONSTRAINT `FK_lams_tool_2` FOREIGN KEY (`grouping_support_type_id`) REFERENCES `lams_grouping_support_type` (`grouping_support_type_id`) -) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_tool_content; --- --- Table structure for table `lams_tool_content` --- +CREATE TABLE lams_tool_content ( + tool_content_id bigint NOT NULL AUTO_INCREMENT, + tool_id bigint NOT NULL, + PRIMARY KEY (tool_content_id), + KEY tool_id (tool_id), + CONSTRAINT FK_lams_tool_content_1 FOREIGN KEY (tool_id) REFERENCES lams_tool (tool_id) +) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -DROP TABLE IF EXISTS `lams_tool_content`; +DROP TABLE IF EXISTS lams_tool_session; -CREATE TABLE `lams_tool_content` ( - `tool_content_id` bigint(20) NOT NULL AUTO_INCREMENT, - `tool_id` bigint(20) NOT NULL, - PRIMARY KEY (`tool_content_id`), - KEY `tool_id` (`tool_id`), - CONSTRAINT `FK_lams_tool_content_1` FOREIGN KEY (`tool_id`) REFERENCES `lams_tool` (`tool_id`) -) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; - --- --- Table structure for table `lams_tool_session` --- - -DROP TABLE IF EXISTS `lams_tool_session`; - -CREATE TABLE `lams_tool_session` ( - `tool_session_id` bigint(20) NOT NULL AUTO_INCREMENT, - `tool_session_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `tool_session_type_id` int(3) NOT NULL, - `lesson_id` bigint(20) NOT NULL, - `activity_id` bigint(20) NOT NULL, - `tool_session_state_id` int(3) NOT NULL, - `create_date_time` datetime NOT NULL, - `group_id` bigint(20) DEFAULT NULL, - `user_id` bigint(20) DEFAULT NULL, - `unique_key` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`tool_session_id`), - UNIQUE KEY `UQ_lams_tool_session_1` (`unique_key`), - KEY `tool_session_state_id` (`tool_session_state_id`), - KEY `user_id` (`user_id`), - KEY `tool_session_type_id` (`tool_session_type_id`), - KEY `activity_id` (`activity_id`), - KEY `group_id` (`group_id`), - CONSTRAINT `FK_lams_tool_session_1` FOREIGN KEY (`group_id`) REFERENCES `lams_group` (`group_id`), - CONSTRAINT `FK_lams_tool_session_4` FOREIGN KEY (`tool_session_state_id`) REFERENCES `lams_tool_session_state` (`tool_session_state_id`), - CONSTRAINT `FK_lams_tool_session_5` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`), - CONSTRAINT `FK_lams_tool_session_7` FOREIGN KEY (`tool_session_type_id`) REFERENCES `lams_tool_session_type` (`tool_session_type_id`), - CONSTRAINT `FK_lams_tool_session_8` FOREIGN KEY (`activity_id`) REFERENCES `lams_learning_activity` (`activity_id`) +CREATE TABLE lams_tool_session ( + tool_session_id bigint NOT NULL AUTO_INCREMENT, + tool_session_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + tool_session_type_id int NOT NULL, + lesson_id bigint NOT NULL, + activity_id bigint NOT NULL, + tool_session_state_id int NOT NULL, + create_date_time datetime NOT NULL, + group_id bigint DEFAULT NULL, + user_id bigint DEFAULT NULL, + unique_key varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (tool_session_id), + UNIQUE KEY UQ_lams_tool_session_1 (unique_key), + KEY tool_session_state_id (tool_session_state_id), + KEY user_id (user_id), + KEY tool_session_type_id (tool_session_type_id), + KEY activity_id (activity_id), + KEY group_id (group_id), + CONSTRAINT FK_lams_tool_session_1 FOREIGN KEY (group_id) REFERENCES lams_group (group_id), + CONSTRAINT FK_lams_tool_session_4 FOREIGN KEY (tool_session_state_id) REFERENCES lams_tool_session_state (tool_session_state_id), + CONSTRAINT FK_lams_tool_session_5 FOREIGN KEY (user_id) REFERENCES lams_user (user_id), + CONSTRAINT FK_lams_tool_session_7 FOREIGN KEY (tool_session_type_id) REFERENCES lams_tool_session_type (tool_session_type_id), + CONSTRAINT FK_lams_tool_session_8 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_tool_session_state; --- --- Table structure for table `lams_tool_session_state` --- -DROP TABLE IF EXISTS `lams_tool_session_state`; - -CREATE TABLE `lams_tool_session_state` ( - `tool_session_state_id` int(3) NOT NULL, - `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`tool_session_state_id`) +CREATE TABLE lams_tool_session_state ( + tool_session_state_id int NOT NULL, + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (tool_session_state_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_tool_session_type; --- --- Table structure for table `lams_tool_session_type` --- -DROP TABLE IF EXISTS `lams_tool_session_type`; - -CREATE TABLE `lams_tool_session_type` ( - `tool_session_type_id` int(3) NOT NULL, - `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`tool_session_type_id`) +CREATE TABLE lams_tool_session_type ( + tool_session_type_id int NOT NULL, + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (tool_session_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_user; --- --- Table structure for table `lams_user` --- -DROP TABLE IF EXISTS `lams_user`; - -CREATE TABLE `lams_user` ( - `user_id` bigint(20) NOT NULL AUTO_INCREMENT, - `login` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL, - `password` char(64) COLLATE utf8mb4_unicode_ci NOT NULL, - `salt` char(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `two_factor_auth_enabled` tinyint(1) DEFAULT '0', - `two_factor_auth_secret` char(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `title` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `first_name` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `last_name` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `address_line_1` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `address_line_2` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `address_line_3` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `city` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `state` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `postcode` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `country` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `day_phone` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `evening_phone` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `mobile_phone` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `fax` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `email` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `email_verified` tinyint(1) DEFAULT '1', - `disabled_flag` tinyint(1) NOT NULL DEFAULT '0', - `create_date` datetime NOT NULL, - `authentication_method_id` bigint(20) NOT NULL DEFAULT '0', - `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, - `change_password` tinyint(1) DEFAULT '0', - `timezone` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `first_login` tinyint(1) DEFAULT '1', - `modified_date` datetime DEFAULT NULL, - `last_visited_organisation_id` bigint(20) DEFAULT NULL, - `failed_attempts` tinyint(4) DEFAULT NULL, - `lock_out_time` datetime DEFAULT NULL, - PRIMARY KEY (`user_id`), - UNIQUE KEY `UQ_lams_user_login` (`login`), - KEY `authentication_method_id` (`authentication_method_id`), - KEY `workspace_folder_id` (`workspace_folder_id`), - KEY `theme_id` (`theme_id`), - KEY `locale_id` (`locale_id`), - KEY `FK_lams_user_7` (`last_visited_organisation_id`), - KEY `email` (`email`), - CONSTRAINT `FK_lams_user_1` FOREIGN KEY (`authentication_method_id`) REFERENCES `lams_authentication_method` (`authentication_method_id`), - CONSTRAINT `FK_lams_user_2` FOREIGN KEY (`workspace_folder_id`) REFERENCES `lams_workspace_folder` (`workspace_folder_id`), - CONSTRAINT `FK_lams_user_5` FOREIGN KEY (`theme_id`) REFERENCES `lams_theme` (`theme_id`), - CONSTRAINT `FK_lams_user_6` FOREIGN KEY (`locale_id`) REFERENCES `lams_supported_locale` (`locale_id`), - CONSTRAINT `FK_lams_user_7` FOREIGN KEY (`last_visited_organisation_id`) REFERENCES `lams_organisation` (`organisation_id`) +CREATE TABLE lams_user ( + user_id bigint NOT NULL AUTO_INCREMENT, + login varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `password` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + salt char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + two_factor_auth_enabled tinyint(1) DEFAULT '0', + two_factor_auth_secret char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + title varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + first_name varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + last_name varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + address_line_1 varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + address_line_2 varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + address_line_3 varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + city varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + state varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + postcode varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + country varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + day_phone varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + evening_phone varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + mobile_phone varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + fax varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + email varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + email_verified tinyint(1) DEFAULT '1', + disabled_flag tinyint(1) NOT NULL DEFAULT '0', + create_date datetime NOT NULL, + authentication_method_id bigint NOT NULL DEFAULT '0', + workspace_folder_id bigint DEFAULT NULL, + theme_id bigint DEFAULT NULL, + locale_id int DEFAULT NULL, + portrait_uuid bigint DEFAULT NULL, + change_password tinyint(1) DEFAULT '0', + timezone varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + first_login tinyint(1) DEFAULT '1', + modified_date datetime DEFAULT NULL, + last_visited_organisation_id bigint DEFAULT NULL, + failed_attempts tinyint DEFAULT NULL, + lock_out_time datetime DEFAULT NULL, + PRIMARY KEY (user_id), + UNIQUE KEY UQ_lams_user_login (login), + KEY authentication_method_id (authentication_method_id), + KEY workspace_folder_id (workspace_folder_id), + KEY theme_id (theme_id), + KEY locale_id (locale_id), + KEY FK_lams_user_7 (last_visited_organisation_id), + KEY email (email), + CONSTRAINT FK_lams_user_1 FOREIGN KEY (authentication_method_id) REFERENCES lams_authentication_method (authentication_method_id), + CONSTRAINT FK_lams_user_2 FOREIGN KEY (workspace_folder_id) REFERENCES lams_workspace_folder (workspace_folder_id), + CONSTRAINT FK_lams_user_5 FOREIGN KEY (theme_id) REFERENCES lams_theme (theme_id), + CONSTRAINT FK_lams_user_6 FOREIGN KEY (locale_id) REFERENCES lams_supported_locale (locale_id), + CONSTRAINT FK_lams_user_7 FOREIGN KEY (last_visited_organisation_id) REFERENCES lams_organisation (organisation_id) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_user_group; --- --- Table structure for table `lams_user_group` --- -DROP TABLE IF EXISTS `lams_user_group`; - -CREATE TABLE `lams_user_group` ( - `user_id` bigint(20) NOT NULL, - `group_id` bigint(20) NOT NULL, - `scheduled_lesson_end_date` datetime DEFAULT NULL, - PRIMARY KEY (`user_id`,`group_id`), - KEY `user_id` (`user_id`), - KEY `group_id` (`group_id`), - CONSTRAINT `FK_lams_user_group_1` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`), - CONSTRAINT `FK_lams_user_group_2` FOREIGN KEY (`group_id`) REFERENCES `lams_group` (`group_id`) +CREATE TABLE lams_user_group ( + user_id bigint NOT NULL, + group_id bigint NOT NULL, + scheduled_lesson_end_date datetime DEFAULT NULL, + PRIMARY KEY (user_id,group_id), + KEY user_id (user_id), + KEY group_id (group_id), + CONSTRAINT FK_lams_user_group_1 FOREIGN KEY (user_id) REFERENCES lams_user (user_id), + CONSTRAINT FK_lams_user_group_2 FOREIGN KEY (group_id) REFERENCES lams_group (group_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_user_organisation; --- --- Table structure for table `lams_user_organisation` --- -DROP TABLE IF EXISTS `lams_user_organisation`; - -CREATE TABLE `lams_user_organisation` ( - `user_organisation_id` bigint(20) NOT NULL AUTO_INCREMENT, - `organisation_id` bigint(20) NOT NULL, - `user_id` bigint(20) NOT NULL, - PRIMARY KEY (`user_organisation_id`), - KEY `organisation_id` (`organisation_id`), - KEY `user_id` (`user_id`), - CONSTRAINT `FK_lams_user_organisation_1` FOREIGN KEY (`organisation_id`) REFERENCES `lams_organisation` (`organisation_id`), - CONSTRAINT `FK_lams_user_organisation_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) +CREATE TABLE lams_user_organisation ( + user_organisation_id bigint NOT NULL AUTO_INCREMENT, + organisation_id bigint NOT NULL, + user_id bigint NOT NULL, + PRIMARY KEY (user_organisation_id), + KEY organisation_id (organisation_id), + KEY user_id (user_id), + CONSTRAINT FK_lams_user_organisation_1 FOREIGN KEY (organisation_id) REFERENCES lams_organisation (organisation_id), + CONSTRAINT FK_lams_user_organisation_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_user_organisation_collapsed; --- --- Table structure for table `lams_user_organisation_group` --- -DROP TABLE IF EXISTS `lams_user_organisation_group`; - -CREATE TABLE `lams_user_organisation_group` ( - `group_id` bigint(20) NOT NULL, - `user_id` bigint(20) NOT NULL, - PRIMARY KEY (`group_id`,`user_id`), - KEY `FK_lams_user_organisation_group_2` (`user_id`), - CONSTRAINT `FK_lams_user_organisation_group_1` FOREIGN KEY (`group_id`) REFERENCES `lams_organisation_group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_lams_user_organisation_group_2` FOREIGN KEY (`user_id`) REFERENCES `lams_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE +CREATE TABLE lams_user_organisation_collapsed ( + uid bigint NOT NULL AUTO_INCREMENT, + organisation_id bigint NOT NULL, + user_id bigint 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; +DROP TABLE IF EXISTS lams_user_organisation_group; --- --- Table structure for table `lams_user_organisation_role` --- -DROP TABLE IF EXISTS `lams_user_organisation_role`; +CREATE TABLE lams_user_organisation_group ( + group_id bigint NOT NULL, + user_id bigint NOT NULL, + PRIMARY KEY (group_id,user_id), + KEY FK_lams_user_organisation_group_2 (user_id), + CONSTRAINT FK_lams_user_organisation_group_1 FOREIGN KEY (group_id) REFERENCES lams_organisation_group (group_id) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_lams_user_organisation_group_2 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE `lams_user_organisation_role` ( - `user_organisation_role_id` bigint(20) NOT NULL AUTO_INCREMENT, - `user_organisation_id` bigint(20) NOT NULL, - `role_id` int(6) NOT NULL, - PRIMARY KEY (`user_organisation_role_id`), - KEY `role_id` (`role_id`), - KEY `user_organisation_id` (`user_organisation_id`), - CONSTRAINT `FK_lams_user_organisation_role_2` FOREIGN KEY (`role_id`) REFERENCES `lams_role` (`role_id`), - CONSTRAINT `FK_lams_user_organisation_role_3` FOREIGN KEY (`user_organisation_id`) REFERENCES `lams_user_organisation` (`user_organisation_id`) -) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_user_organisation_role; --- --- Table structure for table `lams_workspace_folder` --- +CREATE TABLE lams_user_organisation_role ( + user_organisation_role_id bigint NOT NULL AUTO_INCREMENT, + user_organisation_id bigint NOT NULL, + role_id int NOT NULL, + PRIMARY KEY (user_organisation_role_id), + KEY role_id (role_id), + KEY user_organisation_id (user_organisation_id), + CONSTRAINT FK_lams_user_organisation_role_2 FOREIGN KEY (role_id) REFERENCES lams_role (role_id), + CONSTRAINT FK_lams_user_organisation_role_3 FOREIGN KEY (user_organisation_id) REFERENCES lams_user_organisation (user_organisation_id) +) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -DROP TABLE IF EXISTS `lams_workspace_folder`; +DROP TABLE IF EXISTS lams_workspace_folder; -CREATE TABLE `lams_workspace_folder` ( - `workspace_folder_id` bigint(20) NOT NULL AUTO_INCREMENT, - `parent_folder_id` bigint(20) DEFAULT NULL, - `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - `user_id` bigint(20) NOT NULL, - `organisation_id` bigint(20) DEFAULT NULL, - `create_date_time` datetime NOT NULL, - `last_modified_date_time` datetime DEFAULT NULL, - `lams_workspace_folder_type_id` int(3) NOT NULL, - PRIMARY KEY (`workspace_folder_id`), - KEY `parent_folder_id` (`parent_folder_id`), - KEY `lams_workspace_folder_type_id` (`lams_workspace_folder_type_id`), - CONSTRAINT `FK_lams_workspace_folder_2` FOREIGN KEY (`parent_folder_id`) REFERENCES `lams_workspace_folder` (`workspace_folder_id`), - CONSTRAINT `FK_lams_workspace_folder_4` FOREIGN KEY (`lams_workspace_folder_type_id`) REFERENCES `lams_workspace_folder_type` (`lams_workspace_folder_type_id`) + +CREATE TABLE lams_workspace_folder ( + workspace_folder_id bigint NOT NULL AUTO_INCREMENT, + parent_folder_id bigint DEFAULT NULL, + `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + user_id bigint NOT NULL, + organisation_id bigint DEFAULT NULL, + create_date_time datetime NOT NULL, + last_modified_date_time datetime DEFAULT NULL, + lams_workspace_folder_type_id int NOT NULL, + PRIMARY KEY (workspace_folder_id), + KEY parent_folder_id (parent_folder_id), + KEY lams_workspace_folder_type_id (lams_workspace_folder_type_id), + CONSTRAINT FK_lams_workspace_folder_2 FOREIGN KEY (parent_folder_id) REFERENCES lams_workspace_folder (workspace_folder_id), + CONSTRAINT FK_lams_workspace_folder_4 FOREIGN KEY (lams_workspace_folder_type_id) REFERENCES lams_workspace_folder_type (lams_workspace_folder_type_id) ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_workspace_folder_content; --- --- Table structure for table `lams_workspace_folder_content` --- -DROP TABLE IF EXISTS `lams_workspace_folder_content`; - -CREATE TABLE `lams_workspace_folder_content` ( - `folder_content_id` bigint(20) NOT NULL AUTO_INCREMENT, - `content_type_id` int(3) NOT NULL, - `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, - `description` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL, - `create_date_time` datetime NOT NULL, - `last_modified_date` datetime NOT NULL, - `workspace_folder_id` bigint(20) NOT NULL, - `uuid` bigint(20) DEFAULT NULL, - `version_id` bigint(20) DEFAULT NULL, - `mime_type` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`folder_content_id`), - UNIQUE KEY `unique_content_name` (`name`,`workspace_folder_id`,`mime_type`), - UNIQUE KEY `unique_node_version` (`workspace_folder_id`,`uuid`,`version_id`), - KEY `content_type_id` (`content_type_id`), - KEY `workspace_folder_id` (`workspace_folder_id`), - CONSTRAINT `FK_lams_workspace_folder_content_1` FOREIGN KEY (`workspace_folder_id`) REFERENCES `lams_workspace_folder` (`workspace_folder_id`) +CREATE TABLE lams_workspace_folder_content ( + folder_content_id bigint NOT NULL AUTO_INCREMENT, + content_type_id int NOT NULL, + `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + `description` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + create_date_time datetime NOT NULL, + last_modified_date datetime NOT NULL, + workspace_folder_id bigint NOT NULL, + uuid bigint DEFAULT NULL, + version_id bigint DEFAULT NULL, + mime_type varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (folder_content_id), + UNIQUE KEY unique_content_name (`name`,workspace_folder_id,mime_type), + UNIQUE KEY unique_node_version (workspace_folder_id,uuid,version_id), + KEY content_type_id (content_type_id), + KEY workspace_folder_id (workspace_folder_id), + CONSTRAINT FK_lams_workspace_folder_content_1 FOREIGN KEY (workspace_folder_id) REFERENCES lams_workspace_folder (workspace_folder_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS lams_workspace_folder_type; --- --- Table structure for table `lams_workspace_folder_type` --- -DROP TABLE IF EXISTS `lams_workspace_folder_type`; - -CREATE TABLE `lams_workspace_folder_type` ( - `lams_workspace_folder_type_id` int(3) NOT NULL, - `description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, - PRIMARY KEY (`lams_workspace_folder_type_id`) +CREATE TABLE lams_workspace_folder_type ( + lams_workspace_folder_type_id int NOT NULL, + `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (lams_workspace_folder_type_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +DROP TABLE IF EXISTS patches; --- --- Table structure for table `patches` --- -DROP TABLE IF EXISTS `patches`; - -CREATE TABLE `patches` ( - `system_name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL, - `patch_level` int(11) NOT NULL, - `patch_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, - `patch_in_progress` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'F', - PRIMARY KEY (`system_name`,`patch_level`) +CREATE TABLE patches ( + system_name varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, + patch_level int NOT NULL, + patch_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + patch_in_progress char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'F', + PRIMARY KEY (system_name,patch_level) ) 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 -r843bfb2a245193732040b8852349a5f5c4657893 --- 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 843bfb2a245193732040b8852349a5f5c4657893) @@ -6,7 +6,7 @@ -- 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),('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),('EtherpadServerUrl','http://localhost:9001','config.etherpad.server.url','config.header.etherpad','STRING', 0),('EtherpadApiKey','','config.etherpad.api.key','config.header.etherpad','STRING', 0),('EtherpadInstanceID','LAMS','config.etherpad.instance.id','config.header.etherpad','STRING', 0); UNLOCK TABLES; SET FOREIGN_KEY_CHECKS=1; Index: lams_common/db/sql/insert_types_data.sql =================================================================== diff -u -rf9d8fc434a8b7629a1353f532eb15e5c4f5f3cac -r843bfb2a245193732040b8852349a5f5c4657893 --- 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 843bfb2a245193732040b8852349a5f5c4657893) @@ -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,NULL,NULL); UNLOCK TABLES; -- @@ -78,7 +78,7 @@ -- LOCK TABLES `lams_learning_activity_type` WRITE; -INSERT INTO `lams_learning_activity_type` VALUES (1,'TOOL'),(2,'GROUPING'),(3,'GATE_SYNCH'),(4,'GATE_SCHEDULE'),(5,'GATE_PERMISSION'),(6,'PARALLEL'),(7,'OPTIONS'),(8,'SEQUENCE'),(9,'GATE_SYSTEM'),(10,'BRANCHING_CHOSEN'),(11,'BRANCHING_GROUP'),(12,'BRANCHING_TOOL'),(13,'OPTIONS_WITH_SEQUENCES'),(14,'GATE_CONDITION'),(15,'FLOATING'); +INSERT INTO `lams_learning_activity_type` VALUES (1,'TOOL'),(2,'GROUPING'),(3,'GATE_SYNCH'),(4,'GATE_SCHEDULE'),(5,'GATE_PERMISSION'),(6,'PARALLEL'),(7,'OPTIONS'),(8,'SEQUENCE'),(9,'GATE_SYSTEM'),(10,'BRANCHING_CHOSEN'),(11,'BRANCHING_GROUP'),(12,'BRANCHING_TOOL'),(13,'OPTIONS_WITH_SEQUENCES'),(14,'GATE_CONDITION'),(15,'FLOATING'),(16, 'GATE_PASSWORD'); UNLOCK TABLES; -- @@ -186,7 +186,7 @@ -- LOCK TABLES `lams_system_tool` WRITE; -INSERT INTO `lams_system_tool` VALUES (1,2,'Grouping','All types of grouping including random and chosen.','learning/grouping/performGrouping.do','learning/grouping/performGrouping.do','learning/grouping/viewGrouping.do?mode=teacher','monitoring/grouping/startGrouping.do','monitoring/grouping/startGrouping.do',NULL,NOW(),NULL,'pedagogicalPlanner/grouping.do?method=initGrouping'),(2,3,'Sync Gate','Gate: Synchronise Learners.','learning/gate/knockGate.do','learning/gate/knockGate.do',NULL,'monitoring/gate/viewGate.do','monitoring/gate/viewGate.do',NULL,NOW(),NULL,NULL),(3,4,'Schedule Gate','Gate: Opens/shuts at particular times.','learning/gate/knockGate.do','learning/gate/knockGate.do',NULL,'monitoring/gate/viewGate.do','monitoring/gate/viewGate.do',NULL,NOW(),NULL,NULL),(4,5,'Permission Gate','Gate: Opens under teacher or staff control.','learning/gate/knockGate.do','learning/gate/knockGate.do',NULL,'monitoring/gate/viewGate.do','monitoring/gate/viewGate.do',NULL,NOW(),NULL,NULL),(5,9,'System Gate','Gate: Opens under system control.','learning/gate/knockGate.do','learning/gate/knockGate.do',NULL,'monitoring/gate/viewGate.do','monitoring/gate/viewGate.do',NULL,NOW(),NULL,NULL),(6,10,'Monitor Chosen Branching','Select between multiple sequence activities, with the branch chosen in monitoring.','learning/branching/performBranching.do','learning/branching/performBranching.do','monitoring/complexProgress.do','monitoring/chosenBranching.do?method=assignBranch','monitoring/grouping/startGrouping.do',NULL,NOW(),NULL,NULL),(7,11,'Group Based Branching','Select between multiple sequence activities, with the branch chosen by an existing group.','learning/branching/performBranching.do','learning/branching/performBranching.do','monitoring/complexProgress.do','monitoring/groupedBranching.do?method=viewBranching','monitoring/groupedBranching.do?method=assignBranch',NULL,NOW(),NULL,NULL),(8,12,'Tool Output Based Branching','Select between multiple sequence activities, with the branch chosen on results of another activity.','learning/branching/performBranching.do','learning/branching/performBranching.do','monitoring/complexProgress.do','monitoring/toolBranching.do?method=viewBranching','monitoring/toolBranching.do?method=viewBranching',NULL,NOW(),NULL,NULL),(9,8,'Sequence','A sequence of activities','learning/SequenceActivity.do','learning/SequenceActivity.do','monitoring/complexProgress.do','monitoring/sequence/viewSequence.do','monitoring/sequence/viewSequence.do',NULL,NOW(),NULL,NULL),(10,14,'Condition Gate','Gate: Opens if conditions are met','learning/gate/knockGate.do','learning/gate/knockGate.do',NULL,'monitoring/gate/viewGate.do','monitoring/gate/viewGate.do',NULL,NOW(),NULL,NULL),(11,15,'Floating Activities','A collection of floating activities',NULL,NULL,NULL,'monitoring/floating.do?method=viewFloating','monitoring/floating.do?method=viewFloating',NULL,NOW(),NULL,NULL); +INSERT INTO `lams_system_tool` VALUES (1,2,'Grouping','All types of grouping including random and chosen.','learning/grouping/performGrouping.do','learning/grouping/performGrouping.do','learning/grouping/viewGrouping.do?mode=teacher','monitoring/grouping/startGrouping.do','monitoring/grouping/startGrouping.do',NULL,NOW(),NULL,'pedagogicalPlanner/grouping.do?method=initGrouping'),(2,3,'Sync Gate','Gate: Synchronise Learners.','learning/gate/knockGate.do','learning/gate/knockGate.do',NULL,'monitoring/gate/viewGate.do','monitoring/gate/viewGate.do',NULL,NOW(),NULL,NULL),(3,4,'Schedule Gate','Gate: Opens/shuts at particular times.','learning/gate/knockGate.do','learning/gate/knockGate.do',NULL,'monitoring/gate/viewGate.do','monitoring/gate/viewGate.do',NULL,NOW(),NULL,NULL),(4,5,'Permission Gate','Gate: Opens under teacher or staff control.','learning/gate/knockGate.do','learning/gate/knockGate.do',NULL,'monitoring/gate/viewGate.do','monitoring/gate/viewGate.do',NULL,NOW(),NULL,NULL),(5,9,'System Gate','Gate: Opens under system control.','learning/gate/knockGate.do','learning/gate/knockGate.do',NULL,'monitoring/gate/viewGate.do','monitoring/gate/viewGate.do',NULL,NOW(),NULL,NULL),(6,10,'Monitor Chosen Branching','Select between multiple sequence activities, with the branch chosen in monitoring.','learning/branching/performBranching.do','learning/branching/performBranching.do','monitoring/complexProgress.do','monitoring/chosenBranching.do?method=assignBranch','monitoring/grouping/startGrouping.do',NULL,NOW(),NULL,NULL),(7,11,'Group Based Branching','Select between multiple sequence activities, with the branch chosen by an existing group.','learning/branching/performBranching.do','learning/branching/performBranching.do','monitoring/complexProgress.do','monitoring/groupedBranching.do?method=viewBranching','monitoring/groupedBranching.do?method=assignBranch',NULL,NOW(),NULL,NULL),(8,12,'Tool Output Based Branching','Select between multiple sequence activities, with the branch chosen on results of another activity.','learning/branching/performBranching.do','learning/branching/performBranching.do','monitoring/complexProgress.do','monitoring/toolBranching.do?method=viewBranching','monitoring/toolBranching.do?method=viewBranching',NULL,NOW(),NULL,NULL),(9,8,'Sequence','A sequence of activities','learning/SequenceActivity.do','learning/SequenceActivity.do','monitoring/complexProgress.do','monitoring/sequence/viewSequence.do','monitoring/sequence/viewSequence.do',NULL,NOW(),NULL,NULL),(10,14,'Condition Gate','Gate: Opens if conditions are met','learning/gate/knockGate.do','learning/gate/knockGate.do',NULL,'monitoring/gate/viewGate.do','monitoring/gate/viewGate.do',NULL,NOW(),NULL,NULL),(11,15,'Floating Activities','A collection of floating activities',NULL,NULL,NULL,'monitoring/floating.do?method=viewFloating','monitoring/floating.do?method=viewFloating',NULL,NOW(),NULL,NULL),(12, 16, 'Password Gate', 'Gate: Opens if learner provides correct password','learning/gate/knockGate.do', 'learning/gate/knockGate.do', NULL,'monitoring/gate/viewGate.do','monitoring/gate/viewGate.do', NULL, now(), NULL, NULL); UNLOCK TABLES; Index: lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190103_updateTo31.sql =================================================================== diff -u -r5d24a968a822868ea91903c2a427469f98623b08 -r843bfb2a245193732040b8852349a5f5c4657893 --- lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190103_updateTo31.sql (.../patch20190103_updateTo31.sql) (revision 5d24a968a822868ea91903c2a427469f98623b08) +++ lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20190103_updateTo31.sql (.../patch20190103_updateTo31.sql) (revision 843bfb2a245193732040b8852349a5f5c4657893) @@ -1,7 +1,8 @@ SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0; --- This patch contains files patch20170209.sql to patch20181214.sql +-- This patch contains files patch20170209.sql to patch20200909.sql +-- but 4.0 introduced other patches in between, so we officially say it is just 20190103 patch so 4.0 patches execute -- It should upgrade LAMS to version 3.1 @@ -918,7 +919,7 @@ --- LDEV-4726 Update and add new creative common licenses 4.0 for sequences +-- LDEV-4726 Update and add new creative common licenses 4.0 for sequences -- Add an order_id column so we can sequence them how we like ALTER TABLE lams_license ADD COLUMN order_id TINYINT NULL DEFAULT '0'; @@ -963,6 +964,114 @@ VALUES (8, 'Public Domain', 'CC0', 'https://creativecommons.org/publicdomain/zero/1.0/', 0, '/images/license/publicdomain.svg', 7); + +-- 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-4589 Add column for LTI Membership service +ALTER TABLE lams_ext_server_org_map ADD COLUMN `membership_url` text COLLATE utf8mb4_unicode_ci; + + + +-- 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-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-4989 Introduce Password Gate +ALTER TABLE lams_learning_activity ADD COLUMN gate_password VARCHAR(32) AFTER gate_activity_completion_based; + +INSERT INTO lams_system_tool (system_tool_id, learning_activity_type_id, tool_display_name, description, + learner_url, learner_preview_url, monitor_url, + contribute_url, create_date_time) +VALUES (12, 16, 'Password Gate', 'Gate: Opens if learner provides correct password', + 'learning/gate/knockGate.do', 'learning/gate/knockGate.do', 'monitoring/gate/viewGate.do', + 'monitoring/gate/viewGate.do', now()); + +INSERT INTO lams_learning_activity_type (learning_activity_type_id, description) +VALUES (16, 'GATE_PASSWORD'); + + + +-- LDEV-4997 Etherpad as service +INSERT INTO lams_configuration (config_key, config_value, description_key, header_name, format, required) +VALUES ('EtherpadServerUrl', 'http://localhost:9001', 'config.etherpad.server.url', 'config.header.etherpad', 'STRING', 0), + ('EtherpadApiKey', '', 'config.etherpad.api.key', 'config.header.etherpad', 'STRING', 0), + ('EtherpadInstanceID','LAMS', 'config.etherpad.instance.id','config.header.etherpad', 'STRING', 0); + + + +-- LDEV-5027 Convert a boolean value of LTI user ID parameter name into string +ALTER TABLE lams_ext_server_org_map ADD COLUMN user_id_parameter_name VARCHAR(255); +UPDATE lams_ext_server_org_map SET user_id_parameter_name = 'user_id' WHERE use_alternative_user_id_parameter_name = 0; +UPDATE lams_ext_server_org_map SET user_id_parameter_name = 'lis_person_sourcedid' WHERE use_alternative_user_id_parameter_name = 1; +ALTER TABLE lams_ext_server_org_map DROP COLUMN use_alternative_user_id_parameter_name; + + -- If there were no errors, commit and restore autocommit to on COMMIT; SET AUTOCOMMIT = 1; Fisheye: Tag 843bfb2a245193732040b8852349a5f5c4657893 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 843bfb2a245193732040b8852349a5f5c4657893 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 843bfb2a245193732040b8852349a5f5c4657893 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 843bfb2a245193732040b8852349a5f5c4657893 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 843bfb2a245193732040b8852349a5f5c4657893 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 843bfb2a245193732040b8852349a5f5c4657893 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 843bfb2a245193732040b8852349a5f5c4657893 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 843bfb2a245193732040b8852349a5f5c4657893 refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20200315.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 843bfb2a245193732040b8852349a5f5c4657893 refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20200326.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 843bfb2a245193732040b8852349a5f5c4657893 refers to a dead (removed) revision in file `lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch20200909.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: lams_tool_assessment/db/sql/create_lams_tool_assessment.sql =================================================================== diff -u -r4ba184e420dd753ae8e2dcc9434581a794fa244d -r843bfb2a245193732040b8852349a5f5c4657893 --- 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 843bfb2a245193732040b8852349a5f5c4657893) @@ -28,6 +28,7 @@ reflect_instructions MEDIUMTEXT, numbered tinyint(1) DEFAULT 1, use_select_leader_tool_ouput tinyint(1) NOT NULL DEFAULT 0, + question_etherpad_enabled TINYINT(1) DEFAULT 0, enable_confidence_levels TINYINT(1) NOT NULL DEFAULT 0, PRIMARY KEY (uid), UNIQUE KEY content_id (content_id) @@ -123,7 +124,7 @@ sequence_id integer, question MEDIUMTEXT, option_string MEDIUMTEXT, - option_float float, + option_float double, accepted_error float, grade float, feedback MEDIUMTEXT, @@ -167,6 +168,7 @@ time_limit_launched_date datetime, primary key (uid), UNIQUE KEY `UQ_tl_laasse10_assessment_result_5` (`assessment_uid`,`user_uid`,`latest`), + INDEX (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) @@ -200,8 +202,11 @@ answer_boolean tinyint(1), answer_int integer, primary key (uid), + INDEX (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 tl_laasse10_question_option (uid) ON DELETE CASCADE ON UPDATE CASCADE ); Index: lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20190103_updateTo31.sql =================================================================== diff -u -r2bbc9f861c8ae11c17da087af6ce1a0cd49a966d -r843bfb2a245193732040b8852349a5f5c4657893 --- lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20190103_updateTo31.sql (.../patch20190103_updateTo31.sql) (revision 2bbc9f861c8ae11c17da087af6ce1a0cd49a966d) +++ lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20190103_updateTo31.sql (.../patch20190103_updateTo31.sql) (revision 843bfb2a245193732040b8852349a5f5c4657893) @@ -1,7 +1,8 @@ SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0; --- This patch contains files patch20170315.sql to patch20190103.sql +-- This patch contains files patch20170209.sql to patch20200626.sql +-- but 4.0 introduced other patches in between, so we officially say it is just 20190103 patch so 4.0 patches execute -- It should upgrade this tool to version 3.1 @@ -106,6 +107,28 @@ +-- LDEV-4813 Add a missing foreign key and index to speed up queries +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; + + + +-- 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-5002 Add question Etherpads +ALTER TABLE tl_laasse10_assessment ADD COLUMN question_etherpad_enabled TINYINT(1) DEFAULT 0 AFTER use_select_leader_tool_ouput; + + + +--LDEV-5047 Make numeric option more accurate as with large numbers it performs rounding +ALTER TABLE tl_laasse10_question_option MODIFY COLUMN option_float DOUBLE; + + + -- LDEV-4743 Update tools version for LAMS 3.1 release UPDATE lams_tool SET tool_version='20190103' WHERE tool_signature='laasse10'; Fisheye: Tag 843bfb2a245193732040b8852349a5f5c4657893 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 843bfb2a245193732040b8852349a5f5c4657893 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 843bfb2a245193732040b8852349a5f5c4657893 refers to a dead (removed) revision in file `lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20200420.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 843bfb2a245193732040b8852349a5f5c4657893 refers to a dead (removed) revision in file `lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/dbupdates/patch20200626.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: lams_tool_doku/db/sql/create_lams_tool_dokumaran.sql =================================================================== diff -u -r3be35f313edf8bef8b654f8fb53cfa19d526cd1c -r843bfb2a245193732040b8852349a5f5c4657893 --- lams_tool_doku/db/sql/create_lams_tool_dokumaran.sql (.../create_lams_tool_dokumaran.sql) (revision 3be35f313edf8bef8b654f8fb53cfa19d526cd1c) +++ lams_tool_doku/db/sql/create_lams_tool_dokumaran.sql (.../create_lams_tool_dokumaran.sql) (revision 843bfb2a245193732040b8852349a5f5c4657893) @@ -10,6 +10,7 @@ create_by bigint, title varchar(255), lock_on_finished tinyint, + description MEDIUMTEXT, instructions MEDIUMTEXT, content_in_use tinyint, define_later tinyint, @@ -64,26 +65,9 @@ ALTER TABLE tl_ladoku11_user ADD INDEX idx_user_user_id (user_id); ALTER TABLE tl_ladoku11_session ADD INDEX tl_ladoku11_session (group_leader_uid), ADD CONSTRAINT tl_ladoku11_session FOREIGN KEY (`group_leader_uid`) REFERENCES `tl_ladoku11_user` (`uid`) ON DELETE SET NULL ON UPDATE CASCADE; -INSERT INTO `tl_ladoku11_dokumaran` (`uid`, `create_date`, `update_date`, `create_by`, `title`, `lock_on_finished`, +INSERT INTO `tl_ladoku11_dokumaran` (`uid`, `create_date`, `update_date`, `create_by`, `title`, `lock_on_finished`,`description`, `instructions`, `content_in_use`, `define_later`, `content_id`, `show_chat`, `show_line_numbers`, `shared_pad_id`, `use_select_leader_tool_ouput`, `allow_multiple_leaders`, `reflect_on_activity`) VALUES - (1,NULL,NULL,NULL,'doKu','0','Instructions ',0,0,${default_content_id},0,0,NULL,0,0,0); + (1,NULL,NULL,NULL,'doKu','0','Instructions','Document',0,0,${default_content_id},0,0,NULL,0,0,0); --- Inserting the required config item into the config table --- Etherpad API key is added so the Etherpad works there automatically for each build -INSERT INTO `tl_ladoku11_configuration` ( - `config_key`, - `config_value` -) VALUES ( - 'EtherpadUrl', - 'http://0.0.0.0:9001' -); -INSERT INTO `tl_ladoku11_configuration` ( - `config_key`, - `config_value` -) VALUES ( - 'ApiKey', - 'd7404314414a7e9c9fed51316af9720efd52f0ea8fe12849e1bded9c8df88af8' -); - -SET FOREIGN_KEY_CHECKS=1; +SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Index: lams_tool_doku/db/sql/tool_insert.sql =================================================================== diff -u -r7475d08afc280b5e2e5ddf04e8bf35e3166aaf80 -r843bfb2a245193732040b8852349a5f5c4657893 --- lams_tool_doku/db/sql/tool_insert.sql (.../tool_insert.sql) (revision 7475d08afc280b5e2e5ddf04e8bf35e3166aaf80) +++ lams_tool_doku/db/sql/tool_insert.sql (.../tool_insert.sql) (revision 843bfb2a245193732040b8852349a5f5c4657893) @@ -48,5 +48,5 @@ 'org.lamsfoundation.lams.tool.dokumaran.ApplicationResources', NOW(), NOW(), -'tool/ladoku11/ladoku11admin.do' +NULL ) Index: lams_tool_doku/src/java/org/lamsfoundation/lams/tool/dokumaran/dbupdates/patch20190103_updateTo31.sql =================================================================== diff -u -r2bbc9f861c8ae11c17da087af6ce1a0cd49a966d -r843bfb2a245193732040b8852349a5f5c4657893 --- lams_tool_doku/src/java/org/lamsfoundation/lams/tool/dokumaran/dbupdates/patch20190103_updateTo31.sql (.../patch20190103_updateTo31.sql) (revision 2bbc9f861c8ae11c17da087af6ce1a0cd49a966d) +++ lams_tool_doku/src/java/org/lamsfoundation/lams/tool/dokumaran/dbupdates/patch20190103_updateTo31.sql (.../patch20190103_updateTo31.sql) (revision 843bfb2a245193732040b8852349a5f5c4657893) @@ -1,7 +1,7 @@ SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0; --- This patch contains files patch20170303.sql to patch20190103.sql +-- This patch contains files patch20170303.sql to patch20200329.sql -- It should upgrade this tool to version 3.1 -- LDEV-4259 Add time limit feature @@ -13,10 +13,30 @@ ALTER TABLE tl_ladoku11_dokumaran MODIFY instructions MEDIUMTEXT; ALTER TABLE tl_ladoku11_dokumaran MODIFY reflect_instructions MEDIUMTEXT; + + +-- LDEV-4994 Add description field +ALTER TABLE tl_ladoku11_dokumaran ADD COLUMN description MEDIUMTEXT; + +-- LDEV-5008 Rename doKumaran tool +-- Set default tool content +UPDATE tl_ladoku11_dokumaran SET description = 'Instructions', instructions = 'Document' where UID = 1; + + + +-- LDEV-4997 Migrate Etherpad to central as service +DROP TABLE tl_ladoku11_configuration; +UPDATE lams_tool SET admin_url = NULL WHERE tool_signature = 'ladoku11'; + +-- LDEV-5008 Rename doKumaran tool +UPDATE lams_learning_activity SET title = 'doKu' WHERE language_file = 'org.lamsfoundation.lams.tool.dokumaran.ApplicationResources'; +UPDATE tl_ladoku11_dokumaran SET title = 'doKu' WHERE uid = 1; + + + -- LDEV-4743 Update tools version for LAMS 3.1 release UPDATE lams_tool SET tool_version='20190103' WHERE tool_signature='ladoku11'; - -- If there were no errors, commit and restore autocommit to on COMMIT; SET AUTOCOMMIT = 1; Fisheye: Tag 843bfb2a245193732040b8852349a5f5c4657893 refers to a dead (removed) revision in file `lams_tool_doku/src/java/org/lamsfoundation/lams/tool/dokumaran/dbupdates/patch20200324.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 843bfb2a245193732040b8852349a5f5c4657893 refers to a dead (removed) revision in file `lams_tool_doku/src/java/org/lamsfoundation/lams/tool/dokumaran/dbupdates/patch20200329.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: lams_tool_forum/src/java/org/lamsfoundation/lams/tool/forum/dbupdates/patch20190103_updateTo31.sql =================================================================== diff -u -r2bbc9f861c8ae11c17da087af6ce1a0cd49a966d -r843bfb2a245193732040b8852349a5f5c4657893 --- lams_tool_forum/src/java/org/lamsfoundation/lams/tool/forum/dbupdates/patch20190103_updateTo31.sql (.../patch20190103_updateTo31.sql) (revision 2bbc9f861c8ae11c17da087af6ce1a0cd49a966d) +++ lams_tool_forum/src/java/org/lamsfoundation/lams/tool/forum/dbupdates/patch20190103_updateTo31.sql (.../patch20190103_updateTo31.sql) (revision 843bfb2a245193732040b8852349a5f5c4657893) @@ -1,7 +1,7 @@ SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0; --- This patch contains files patch20170308.sql to patch20190103.sql +-- This patch contains files patch20170308.sql to patch20190121.sql -- It should upgrade this tool to version 3.1 -- LDEV-4261 Remove optimistic locking management @@ -71,6 +71,11 @@ +-- LDEV-4756 Unable to add attachments in Forum +UPDATE lams_cr_credential set name = "forum" where name = "forumw"; + + + -- LDEV-4743 Update tools version for LAMS 3.1 release UPDATE lams_tool SET tool_version='20190103' WHERE tool_signature='lafrum11'; Fisheye: Tag 843bfb2a245193732040b8852349a5f5c4657893 refers to a dead (removed) revision in file `lams_tool_forum/src/java/org/lamsfoundation/lams/tool/forum/dbupdates/patch20190121.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: lams_tool_preview/db/sql/create_lams_tool_peerreview.sql =================================================================== diff -u -r8cb3b47e0bc1e7d89582929aa33ee29f0ef37189 -r843bfb2a245193732040b8852349a5f5c4657893 --- lams_tool_preview/db/sql/create_lams_tool_peerreview.sql (.../create_lams_tool_peerreview.sql) (revision 8cb3b47e0bc1e7d89582929aa33ee29f0ef37189) +++ lams_tool_preview/db/sql/create_lams_tool_peerreview.sql (.../create_lams_tool_peerreview.sql) (revision 843bfb2a245193732040b8852349a5f5c4657893) @@ -20,6 +20,7 @@ self_review tinyint(1) DEFAULT 0, notify_users_of_results tinyint(1) DEFAULT 1, show_ratings_left_by_user tinyint(1) DEFAULT 0, + tolerance TINYINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (uid), UNIQUE KEY content_id (content_id) ); Index: lams_tool_preview/src/java/org/lamsfoundation/lams/tool/peerreview/dbupdates/patch20190103_updateTo31.sql =================================================================== diff -u -r2bbc9f861c8ae11c17da087af6ce1a0cd49a966d -r843bfb2a245193732040b8852349a5f5c4657893 --- lams_tool_preview/src/java/org/lamsfoundation/lams/tool/peerreview/dbupdates/patch20190103_updateTo31.sql (.../patch20190103_updateTo31.sql) (revision 2bbc9f861c8ae11c17da087af6ce1a0cd49a966d) +++ lams_tool_preview/src/java/org/lamsfoundation/lams/tool/peerreview/dbupdates/patch20190103_updateTo31.sql (.../patch20190103_updateTo31.sql) (revision 843bfb2a245193732040b8852349a5f5c4657893) @@ -1,7 +1,7 @@ SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0; --- This patch contains files patch20170215.sql to patch20190103.sql +-- This patch contains files patch20170215.sql to patch20200607.sql -- It should upgrade this tool to version 3.1 -- LDEV-4233: Ability to remove users from Peer Review tool @@ -50,6 +50,11 @@ +--LDEV-5035 Add tolerance to SAP and SAPA factors in Peer Review +ALTER TABLE tl_laprev11_peerreview ADD COLUMN tolerance TINYINT UNSIGNED NOT NULL DEFAULT 0; + + + -- LDEV-4743 Update tool version to mark LAMS 3.1 release UPDATE lams_tool SET tool_version='20190103' WHERE tool_signature='laprev11'; Fisheye: Tag 843bfb2a245193732040b8852349a5f5c4657893 refers to a dead (removed) revision in file `lams_tool_preview/src/java/org/lamsfoundation/lams/tool/peerreview/dbupdates/patch20200607.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: lams_tool_sbmt/db/sql/create_lams_tool_sbmt.sql =================================================================== diff -u -r8cb3b47e0bc1e7d89582929aa33ee29f0ef37189 -r843bfb2a245193732040b8852349a5f5c4657893 --- lams_tool_sbmt/db/sql/create_lams_tool_sbmt.sql (.../create_lams_tool_sbmt.sql) (revision 8cb3b47e0bc1e7d89582929aa33ee29f0ef37189) +++ lams_tool_sbmt/db/sql/create_lams_tool_sbmt.sql (.../create_lams_tool_sbmt.sql) (revision 843bfb2a245193732040b8852349a5f5c4657893) @@ -18,6 +18,7 @@ mark_release_notify TINYINT(1) DEFAULT 0, file_submit_notify TINYINT(1) DEFAULT 0, use_select_leader_tool_ouput TINYINT(1) NOT NULL DEFAULT 0, + min_limit_upload_number bigint, primary key (content_id) ); Index: lams_tool_sbmt/src/java/org/lamsfoundation/lams/tool/sbmt/dbupdates/patch20190103_updateTo31.sql =================================================================== diff -u -r2bbc9f861c8ae11c17da087af6ce1a0cd49a966d -r843bfb2a245193732040b8852349a5f5c4657893 --- lams_tool_sbmt/src/java/org/lamsfoundation/lams/tool/sbmt/dbupdates/patch20190103_updateTo31.sql (.../patch20190103_updateTo31.sql) (revision 2bbc9f861c8ae11c17da087af6ce1a0cd49a966d) +++ lams_tool_sbmt/src/java/org/lamsfoundation/lams/tool/sbmt/dbupdates/patch20190103_updateTo31.sql (.../patch20190103_updateTo31.sql) (revision 843bfb2a245193732040b8852349a5f5c4657893) @@ -1,7 +1,7 @@ SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0; --- This patch contains files patch20171114.sql to patch20190103.sql +-- This patch contains files patch20171114.sql to patch20190128.sql -- It should upgrade this tool to version 3.1 -- LDEV-4482 @@ -60,6 +60,11 @@ +-- LDEV-4913 Include a minimum number of files to submit +ALTER TABLE tl_lasbmt11_content ADD COLUMN min_limit_upload_number bigint; + + + -- LDEV-4743 Update tool version to mark LAMS 3.1 release UPDATE lams_tool SET tool_version='20190103' WHERE tool_signature='lasbmt11'; Fisheye: Tag 843bfb2a245193732040b8852349a5f5c4657893 refers to a dead (removed) revision in file `lams_tool_sbmt/src/java/org/lamsfoundation/lams/tool/sbmt/dbupdates/patch20191128.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: lams_tool_scratchie/db/sql/create_lams_tool_scratchie.sql =================================================================== diff -u -r764e7e5bc34045468731408215fe24cf980c538c -r843bfb2a245193732040b8852349a5f5c4657893 --- 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 843bfb2a245193732040b8852349a5f5c4657893) @@ -14,6 +14,7 @@ extra_point TINYINT(1), submission_deadline datetime, burning_questions_enabled TINYINT(1) DEFAULT 1, + question_etherpad_enabled TINYINT(1) DEFAULT 0, time_limit int(11) DEFAULT 0, shuffle_items TINYINT(1) DEFAULT 0, confidence_levels_activity_uiid INT(11), Index: lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dbupdates/patch20190103_updateTo31.sql =================================================================== diff -u -r2bbc9f861c8ae11c17da087af6ce1a0cd49a966d -r843bfb2a245193732040b8852349a5f5c4657893 --- lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dbupdates/patch20190103_updateTo31.sql (.../patch20190103_updateTo31.sql) (revision 2bbc9f861c8ae11c17da087af6ce1a0cd49a966d) +++ lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dbupdates/patch20190103_updateTo31.sql (.../patch20190103_updateTo31.sql) (revision 843bfb2a245193732040b8852349a5f5c4657893) @@ -1,7 +1,8 @@ SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0; --- This patch contains files patch20170221.sql to patch20190103.sql +-- This patch contains files patch20170209.sql to patch20200413.sql +-- but 4.0 introduced other patches in between, so we officially say it is just 20190103 patch so 4.0 patches execute -- It should upgrade this tool to version 3.1 @@ -48,11 +49,20 @@ +-- LDEV-4817 Delete existing blank burning questions +DELETE FROM tl_lascrt11_burning_question WHERE question IS NULL OR TRIM(question) = ''; + + + +--LDEV-5002 Add question Etherpads +ALTER TABLE tl_lascrt11_scratchie ADD COLUMN question_etherpad_enabled TINYINT(1) DEFAULT 0 AFTER burning_questions_enabled; + + + -- LDEV-4743 Update tool version to mark LAMS 3.1 release UPDATE lams_tool SET tool_version='20190103' WHERE tool_signature='lascrt11'; - -- If there were no errors, commit and restore autocommit to on COMMIT; SET AUTOCOMMIT = 1; Fisheye: Tag 843bfb2a245193732040b8852349a5f5c4657893 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 843bfb2a245193732040b8852349a5f5c4657893 refers to a dead (removed) revision in file `lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/dbupdates/patch20200413.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: lams_tool_zoom/db/sql/create_lams_tool_zoom.sql =================================================================== diff -u -r6c62e5beb70cd0b72dcec44eaf80ba489a442fb4 -r843bfb2a245193732040b8852349a5f5c4657893 --- lams_tool_zoom/db/sql/create_lams_tool_zoom.sql (.../create_lams_tool_zoom.sql) (revision 6c62e5beb70cd0b72dcec44eaf80ba489a442fb4) +++ lams_tool_zoom/db/sql/create_lams_tool_zoom.sql (.../create_lams_tool_zoom.sql) (revision 843bfb2a245193732040b8852349a5f5c4657893) @@ -17,6 +17,7 @@ api_id bigint(20), meeting_id varchar(64), meeting_start_url varchar(1000), + meeting_password CHAR(6), PRIMARY KEY (uid), CONSTRAINT fk_lazoom10_zoom_to_api FOREIGN KEY (api_id) REFERENCES tl_lazoom10_api (uid) ON DELETE SET NULL ON UPDATE CASCADE Index: lams_tool_zoom/src/java/org/lamsfoundation/lams/tool/zoom/dbupdates/patch20190103_updateTo31.sql =================================================================== diff -u -r2bbc9f861c8ae11c17da087af6ce1a0cd49a966d -r843bfb2a245193732040b8852349a5f5c4657893 --- lams_tool_zoom/src/java/org/lamsfoundation/lams/tool/zoom/dbupdates/patch20190103_updateTo31.sql (.../patch20190103_updateTo31.sql) (revision 2bbc9f861c8ae11c17da087af6ce1a0cd49a966d) +++ lams_tool_zoom/src/java/org/lamsfoundation/lams/tool/zoom/dbupdates/patch20190103_updateTo31.sql (.../patch20190103_updateTo31.sql) (revision 843bfb2a245193732040b8852349a5f5c4657893) @@ -1,7 +1,7 @@ SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0; --- This patch contains files patch20180917.sql to patch20190103.sql +-- This patch contains files patch20180917.sql to patch20200514.sql -- It should upgrade this tool to version 3.1 @@ -19,6 +19,17 @@ +-- LDEV-5006 Password protect Zoom meetings +ALTER TABLE tl_lazoom10_zoom ADD COLUMN enable_meeting_password TINYINT(1) DEFAULT 0, + ADD COLUMN meeting_password CHAR(6); + + + +-- LDEV-5026 Always password protect Zoom meetings +ALTER TABLE tl_lazoom10_zoom DROP COLUMN enable_meeting_password; + + + -- LDEV-4743 Update tool version to mark LAMS 3.1 release UPDATE lams_tool SET tool_version='20190103' WHERE tool_signature='lazoom10'; Fisheye: Tag 843bfb2a245193732040b8852349a5f5c4657893 refers to a dead (removed) revision in file `lams_tool_zoom/src/java/org/lamsfoundation/lams/tool/zoom/dbupdates/patch20200410.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 843bfb2a245193732040b8852349a5f5c4657893 refers to a dead (removed) revision in file `lams_tool_zoom/src/java/org/lamsfoundation/lams/tool/zoom/dbupdates/patch20200514.sql'. Fisheye: No comparison available. Pass `N' to diff?