Index: lams_common/db/sql/create_lams_11_tables.sql =================================================================== diff -u -r7475d08afc280b5e2e5ddf04e8bf35e3166aaf80 -rf9d8fc434a8b7629a1353f532eb15e5c4f5f3cac --- lams_common/db/sql/create_lams_11_tables.sql (.../create_lams_11_tables.sql) (revision 7475d08afc280b5e2e5ddf04e8bf35e3166aaf80) +++ lams_common/db/sql/create_lams_11_tables.sql (.../create_lams_11_tables.sql) (revision f9d8fc434a8b7629a1353f532eb15e5c4f5f3cac) @@ -1,1531 +1,2445 @@ -CREATE TABLE lams_gate_activity_level ( - gate_activity_level_id INT(11) NOT NULL - , description VARCHAR(128) NOT NULL - , PRIMARY KEY (gate_activity_level_id) -); +SET FOREIGN_KEY_CHECKS=0; +SET NAMES utf8mb4 ; -CREATE TABLE lams_grouping_type ( - grouping_type_id INT(11) NOT NULL - , description VARCHAR(128) NOT NULL - , PRIMARY KEY (grouping_type_id) -); +-- +-- Table structure for table `lams_activity_category` +-- -CREATE TABLE lams_learning_activity_type ( - learning_activity_type_id INT(11) NOT NULL - , description VARCHAR(255) NOT NULL - , PRIMARY KEY (learning_activity_type_id) -); +DROP TABLE IF EXISTS `lams_activity_category`; -CREATE TABLE lams_learning_library ( - learning_library_id BIGINT(20) NOT NULL AUTO_INCREMENT - , description TEXT - , title VARCHAR(255) - , valid_flag TINYINT(1) NOT NULL DEFAULT 1 - , create_date_time DATETIME NOT NULL - , PRIMARY KEY (learning_library_id) -); +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_organisation_type ( - organisation_type_id INT(3) NOT NULL - , name VARCHAR(64) NOT NULL - , description VARCHAR(255) NOT NULL - , PRIMARY KEY (organisation_type_id) - , UNIQUE KEY UQ_lams_organisation_type_name (name) -); +-- +-- Table structure for table `lams_activity_evaluation` +-- -CREATE TABLE lams_role ( - role_id INT(6) NOT NULL - , name VARCHAR(64) NOT NULL - , description TEXT - , create_date DATETIME NOT NULL - , PRIMARY KEY (role_id) - , KEY gname (name) -); +DROP TABLE IF EXISTS `lams_activity_evaluation`; -CREATE TABLE lams_tool_session_state ( - tool_session_state_id INT(3) NOT NULL - , description VARCHAR(255) NOT NULL - , PRIMARY KEY (tool_session_state_id) -); +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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_lesson_state ( - lesson_state_id INT(3) NOT NULL - , description VARCHAR(255) NOT NULL - , PRIMARY KEY (lesson_state_id) -); +-- +-- Table structure for table `lams_auth_method_type` +-- -CREATE TABLE lams_tool_session_type ( - tool_session_type_id INT(3) NOT NULL - , description VARCHAR(255) NOT NULL - , PRIMARY KEY (tool_session_type_id) -); +DROP TABLE IF EXISTS `lams_auth_method_type`; -CREATE TABLE lams_license ( - license_id BIGINT(20) NOT NULL - , name VARCHAR(200) NOT NULL - , code VARCHAR(20) NOT NULL - , url VARCHAR(256) - , default_flag TINYINT(1) NOT NULL DEFAULT 0 - , picture_url VARCHAR(256) - , PRIMARY KEY (license_id) -); +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_copy_type ( - copy_type_id TINYINT(4) NOT NULL - , description VARCHAR(255) NOT NULL - , PRIMARY KEY (copy_type_id) -); -CREATE TABLE lams_activity_category ( - activity_category_id INT(3) NOT NULL - , description VARCHAR(255) NOT NULL - , PRIMARY KEY (activity_category_id) -); +-- +-- Table structure for table `lams_authentication_method` +-- -CREATE TABLE lams_cr_workspace ( - workspace_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT - , name VARCHAR(255) NOT NULL - , PRIMARY KEY (workspace_id) -); +DROP TABLE IF EXISTS `lams_authentication_method`; -CREATE TABLE lams_cr_credential ( - credential_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT - , name VARCHAR(255) NOT NULL - , password VARCHAR(255) NOT NULL - , PRIMARY KEY (credential_id) -); +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_workspace_folder_type ( - lams_workspace_folder_type_id INT(3) NOT NULL - , description VARCHAR(255) NOT NULL - , PRIMARY KEY (lams_workspace_folder_type_id) -); +-- +-- Table structure for table `lams_branch_activity_entry` +-- -CREATE TABLE lams_grouping_support_type ( - grouping_support_type_id INT(3) NOT NULL - , description VARCHAR(64) NOT NULL - , PRIMARY KEY (grouping_support_type_id) -); +DROP TABLE IF EXISTS `lams_branch_activity_entry`; -CREATE TABLE lams_log_event_type ( - log_event_type_id INT(5) NOT NULL - , description VARCHAR(255) NOT NULL - , PRIMARY KEY (log_event_type_id) -); +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_privilege ( - privilege_id BIGINT(20) NOT NULL AUTO_INCREMENT - , code VARCHAR(10) NOT NULL - , description VARCHAR(255) - , PRIMARY KEY (privilege_id) - , UNIQUE KEY IX_lams_privilege_code (code) -); -CREATE TABLE lams_organisation_state ( - organisation_state_id INT(3) NOT NULL - , description VARCHAR(255) - , PRIMARY KEY (organisation_state_id) -); +-- +-- Table structure for table `lams_branch_condition` +-- -CREATE TABLE lams_supported_locale ( - locale_id INTEGER NOT NULL AUTO_INCREMENT - , language_iso_code VARCHAR(2) NOT NULL - , country_iso_code VARCHAR(2) - , description VARCHAR(255) NOT NULL - , direction VARCHAR(3) NOT NULL - , fckeditor_code VARCHAR(10) - , PRIMARY KEY (locale_id) -); +DROP TABLE IF EXISTS `lams_branch_condition`; -CREATE TABLE lams_auth_method_type ( - authentication_method_type_id INT(3) NOT NULL - , description VARCHAR(64) NOT NULL - , PRIMARY KEY (authentication_method_type_id) -); +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -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) NOT NULL - , UNIQUE UQ_lams_authentication_method_1 (authentication_method_name) - , PRIMARY KEY (authentication_method_id) - , KEY (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) ON DELETE NO ACTION ON UPDATE NO ACTION -); -CREATE TABLE lams_workspace_folder ( - workspace_folder_id BIGINT(20) NOT NULL AUTO_INCREMENT - , parent_folder_id BIGINT(20) - , name VARCHAR(255) NOT NULL - , user_id BIGINT(20) NOT NULL - , organisation_id BIGINT(20) - , create_date_time DATETIME NOT NULL - , last_modified_date_time DATETIME - , lams_workspace_folder_type_id INT(3) NOT NULL - , PRIMARY KEY (workspace_folder_id) - , KEY (parent_folder_id) - , KEY (lams_workspace_folder_type_id) - , CONSTRAINT FK_lams_workspace_folder_4 FOREIGN KEY (lams_workspace_folder_type_id) - REFERENCES lams_workspace_folder_type (lams_workspace_folder_type_id) -); +-- +-- Table structure for table `lams_comment` +-- -ALTER TABLE lams_workspace_folder ADD CONSTRAINT FK_lams_workspace_folder_2 FOREIGN KEY (parent_folder_id) - REFERENCES lams_workspace_folder (workspace_folder_id) ON DELETE NO ACTION ON UPDATE NO ACTION; +DROP TABLE IF EXISTS `lams_comment`; -CREATE TABLE lams_organisation ( - organisation_id BIGINT(20) NOT NULL AUTO_INCREMENT - , name VARCHAR(250) NOT NULL - , code VARCHAR(20) - , description TEXT - , parent_organisation_id BIGINT(20) - , 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_monitor_gradebook 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 - , locale_id INTEGER - , archived_date DATETIME - , ordered_lesson_ids TEXT - , PRIMARY KEY (organisation_id) - , KEY (organisation_type_id) - , CONSTRAINT FK_lams_organisation_1 FOREIGN KEY (organisation_type_id) - REFERENCES lams_organisation_type (organisation_type_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , KEY (parent_organisation_id) - , KEY (organisation_state_id) - , CONSTRAINT FK_lams_organisation_4 FOREIGN KEY (organisation_state_id) - REFERENCES lams_organisation_state (organisation_state_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , KEY (locale_id) - , CONSTRAINT FK_lams_organisation_5 FOREIGN KEY (locale_id) - REFERENCES lams_supported_locale (locale_id) -); +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -ALTER TABLE lams_organisation ADD CONSTRAINT FK_lams_organisation_3 FOREIGN KEY (parent_organisation_id) - REFERENCES lams_organisation (organisation_id) ON DELETE NO ACTION ON UPDATE NO ACTION; -CREATE TABLE lams_theme ( - theme_id BIGINT(20) NOT NULL AUTO_INCREMENT - , name VARCHAR(100) NOT NULL - , description VARCHAR(100) - , image_directory VARCHAR(100) - , PRIMARY KEY (theme_id) - , UNIQUE KEY UQ_name (name) -); +-- +-- Table structure for table `lams_comment_likes` +-- -CREATE TABLE lams_user ( - user_id BIGINT(20) NOT NULL AUTO_INCREMENT - , login VARCHAR(191) NOT NULL - , password CHAR(64) NOT NULL - , salt CHAR(64) - , two_factor_auth_enabled TINYINT(1) DEFAULT 0 - , two_factor_auth_secret CHAR(64) - , title VARCHAR(32) - , first_name VARCHAR(128) - , last_name VARCHAR(128) - , address_line_1 VARCHAR(64) - , address_line_2 VARCHAR(64) - , address_line_3 VARCHAR(64) - , city VARCHAR(64) - , state VARCHAR(64) - , postcode VARCHAR(10) - , country VARCHAR(64) - , day_phone VARCHAR(64) - , evening_phone VARCHAR(64) - , mobile_phone VARCHAR(64) - , fax VARCHAR(64) - , email VARCHAR(128) - , 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) - , theme_id BIGINT(20) - , locale_id INTEGER - , portrait_uuid BIGINT(20) - , change_password TINYINT(1) DEFAULT 0 - , lams_community_token VARCHAR(255) - , lams_community_username VARCHAR(255) - , timezone VARCHAR(255) - , tutorials_disabled TINYINT(1) DEFAULT 0 - , first_login TINYINT(1) DEFAULT 1 - , modified_date DATETIME - , last_visited_organisation_id BIGINT(20) - , failed_attempts TINYINT(4) - , lock_out_time DATETIME - , PRIMARY KEY (user_id) - , KEY (authentication_method_id) - , CONSTRAINT FK_lams_user_1 FOREIGN KEY (authentication_method_id) - REFERENCES lams_authentication_method (authentication_method_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , KEY (workspace_folder_id) - , CONSTRAINT FK_lams_user_2 FOREIGN KEY (workspace_folder_id) - REFERENCES lams_workspace_folder (workspace_folder_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , KEY (theme_id) - , CONSTRAINT FK_lams_user_5 FOREIGN KEY (theme_id) - REFERENCES lams_theme (theme_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , KEY (locale_id) - , CONSTRAINT FK_lams_user_6 FOREIGN KEY (locale_id) - REFERENCES lams_supported_locale (locale_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , CONSTRAINT FK_lams_user_7 FOREIGN KEY (last_visited_organisation_id) - REFERENCES lams_organisation (organisation_id) - , UNIQUE KEY UQ_lams_user_login (login) - , KEY email (email) -); +DROP TABLE IF EXISTS `lams_comment_likes`; -CREATE TABLE lams_learning_design ( - learning_design_id BIGINT(20) NOT NULL AUTO_INCREMENT - , learning_design_ui_id INT(11) - , description TEXT - , title VARCHAR(255) - , first_activity_id BIGINT(20) - , floating_activity_id BIGINT(20) - , max_id INT(11) - , valid_design_flag TINYINT(1) NOT NULL - , read_only_flag TINYINT(1) NOT NULL - , date_read_only DATETIME - , user_id BIGINT(20) NOT NULL - , original_user_id BIGINT(20) NOT NULL - , help_text TEXT - , copy_type_id TINYINT(4) NOT NULL - , create_date_time DATETIME NOT NULL - , version VARCHAR(56) - , original_learning_design_id BIGINT(20) - , workspace_folder_id BIGINT(20) - , duration BIGINT(38) - , license_id BIGINT(20) - , license_TEXT TEXT - , last_modified_date_time DATETIME - , content_folder_id VARCHAR(32) - , edit_override_lock TINYINT(1) DEFAULT 0 - , edit_override_user_id BIGINT(20) - , design_version INTEGER DEFAULT 1 - , removed TINYINT(1) NOT NULL DEFAULT 0 - , design_type VARCHAR(255) - , PRIMARY KEY (learning_design_id) - , KEY (user_id) - , CONSTRAINT FK_lams_learning_design_3 FOREIGN KEY (user_id) - REFERENCES lams_user (user_id) - , KEY (workspace_folder_id) - , CONSTRAINT FK_lams_learning_design_4 FOREIGN KEY (workspace_folder_id) - REFERENCES lams_workspace_folder (workspace_folder_id) - , KEY (license_id) - , CONSTRAINT FK_lams_learning_design_5 FOREIGN KEY (license_id) - REFERENCES lams_license (license_id) - , KEY (copy_type_id) - , CONSTRAINT FK_lams_learning_design_6 FOREIGN KEY (copy_type_id) - REFERENCES lams_copy_type (copy_type_id) - , KEY (edit_override_user_id) - , CONSTRAINT FK_lams_learning_design_7 FOREIGN KEY (edit_override_user_id) - REFERENCES lams_user (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) -); +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_grouping ( - grouping_id BIGINT(20) NOT NULL AUTO_INCREMENT - , grouping_ui_id INT(11) - , grouping_type_id INT(11) NOT NULL - , number_of_groups INT(11) - , learners_per_group INT(11) - , staff_group_id BIGINT(20) DEFAULT 0 - , max_number_of_groups INT(3) - , 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) - , CONSTRAINT FK_lams_learning_grouping_1 FOREIGN KEY (grouping_type_id) - REFERENCES lams_grouping_type (grouping_type_id) ON DELETE NO ACTION ON UPDATE NO ACTION -); -CREATE TABLE lams_group ( - group_id BIGINT(20) NOT NULL AUTO_INCREMENT - , group_name VARCHAR(255) NOT NULL - , grouping_id BIGINT(20) NOT NULL - , order_id INT(6) NOT NULL DEFAULT 1 - , group_ui_id INT(11) - , PRIMARY KEY (group_id) - , KEY (grouping_id) - , CONSTRAINT FK_lams_learning_group_1 FOREIGN KEY (grouping_id) - REFERENCES lams_grouping (grouping_id) ON DELETE NO ACTION ON UPDATE NO ACTION -); +-- +-- Table structure for table `lams_comment_session` +-- -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) NOT NULL - , description TEXT - , learner_url TEXT - , learner_preview_url TEXT - , learner_progress_url TEXT - , monitor_url TEXT - , contribute_url TEXT - , help_url TEXT - , create_date_time DATETIME NOT NULL - , admin_url TEXT - , pedagogical_planner_url TEXT - , 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) ON DELETE NO ACTION ON UPDATE NO ACTION -); +DROP TABLE IF EXISTS `lams_comment_session`; -CREATE TABLE lams_tool ( - tool_id BIGINT(20) NOT NULL AUTO_INCREMENT - , tool_signature VARCHAR(64) NOT NULL - , service_name VARCHAR(191) NOT NULL - , tool_display_name VARCHAR(255) NOT NULL - , description TEXT - , tool_identifier VARCHAR(64) NOT NULL - , tool_version VARCHAR(10) NOT NULL - , learning_library_id BIGINT(20) - , default_tool_content_id BIGINT(20) - , valid_flag TINYINT(1) NOT NULL DEFAULT 1 - , grouping_support_type_id INT(3) NOT NULL - , learner_url TEXT NOT NULL - , learner_preview_url TEXT - , learner_progress_url TEXT - , author_url TEXT NOT NULL - , monitor_url TEXT - , pedagogical_planner_url TEXT - , help_url TEXT - , create_date_time DATETIME NOT NULL - , language_file VARCHAR(255) - , modified_date_time DATETIME - , admin_url TEXT - , supports_outputs TINYINT(1) DEFAULT 0 - , ext_lms_id VARCHAR(255) - , 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) - , CONSTRAINT FK_lams_tool_1 FOREIGN KEY (learning_library_id) - REFERENCES lams_learning_library (learning_library_id) - , KEY (grouping_support_type_id) - , CONSTRAINT FK_lams_tool_2 FOREIGN KEY (grouping_support_type_id) - REFERENCES lams_grouping_support_type (grouping_support_type_id) -); - -CREATE TABLE lams_learning_activity ( - activity_id BIGINT(20) NOT NULL AUTO_INCREMENT - , activity_ui_id INT(11) - , description TEXT - , title VARCHAR(255) - , xcoord INT(11) - , ycoord INT(11) - , parent_activity_id BIGINT(20) - , parent_ui_id INT(11) - , 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) - , grouping_ui_id INT(11) - , order_id INT(11) - , learning_design_id BIGINT(20) - , learning_library_id BIGINT(20) - , create_date_time DATETIME NOT NULL - , max_number_of_options INT(5) - , min_number_of_options INT(5) - , options_instructions TEXT - , tool_id BIGINT(20) - , tool_content_id BIGINT(20) - , activity_category_id INT(3) NOT NULL - , gate_activity_level_id INT(11) - , gate_open_flag TINYINT(1) - , gate_start_time_offset BIGINT(38) - , gate_end_time_offset BIGINT(38) - , gate_activity_completion_based TINYINT(1) - , library_activity_ui_image VARCHAR(255) - , create_grouping_id BIGINT(20) - , create_grouping_ui_id INT(11) - , library_activity_id BIGINT(20) - , language_file VARCHAR(255) - , system_tool_id BIGINT(20) - , read_only TINYINT(1) DEFAULT 0 - , initialised TINYINT(1) DEFAULT 0 - , default_activity_id BIGINT(20) - , start_xcoord INT(11) - , start_ycoord INT(11) - , end_xcoord INT(11) - , end_ycoord INT(11) - , stop_after_activity TINYINT(1) NOT NULL DEFAULT 0 - , transition_to_id BIGINT(20) - , transition_from_id BIGINT(20) - , PRIMARY KEY (activity_id) - , KEY lams_learning_activity_tool_content_id (tool_content_id) - , KEY (learning_library_id) - , CONSTRAINT FK_lams_learning_activity_7 FOREIGN KEY (learning_library_id) - REFERENCES lams_learning_library (learning_library_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , KEY (learning_design_id) - , CONSTRAINT FK_lams_learning_activity_6 FOREIGN KEY (learning_design_id) - REFERENCES lams_learning_design (learning_design_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , KEY (parent_activity_id) - , CONSTRAINT FK_learning_activity_2 FOREIGN KEY (parent_activity_id) - REFERENCES lams_learning_activity (activity_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , KEY (learning_activity_type_id) - , CONSTRAINT FK_learning_activity_3 FOREIGN KEY (learning_activity_type_id) - REFERENCES lams_learning_activity_type (learning_activity_type_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , KEY (grouping_id) - , CONSTRAINT FK_learning_activity_6 FOREIGN KEY (grouping_id) - REFERENCES lams_grouping (grouping_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , KEY (tool_id) - , CONSTRAINT FK_lams_learning_activity_8 FOREIGN KEY (tool_id) - REFERENCES lams_tool (tool_id) - , KEY (gate_activity_level_id) - , CONSTRAINT FK_lams_learning_activity_10 FOREIGN KEY (gate_activity_level_id) - REFERENCES lams_gate_activity_level (gate_activity_level_id) - , KEY (create_grouping_id) - , CONSTRAINT FK_lams_learning_activity_9 FOREIGN KEY (create_grouping_id) - REFERENCES lams_grouping (grouping_id) - , KEY (library_activity_id) - , CONSTRAINT FK_lams_learning_activity_11 FOREIGN KEY (library_activity_id) - REFERENCES lams_learning_activity (activity_id) - , KEY (activity_category_id) - , CONSTRAINT FK_lams_learning_activity_12 FOREIGN KEY (activity_category_id) - REFERENCES lams_activity_category (activity_category_id) - , KEY (grouping_support_type_id) - , CONSTRAINT FK_lams_learning_activity_13 FOREIGN KEY (grouping_support_type_id) - REFERENCES lams_grouping_support_type (grouping_support_type_id) - , KEY (system_tool_id) - , CONSTRAINT FK_lams_learning_activity_14 FOREIGN KEY (system_tool_id) - REFERENCES lams_system_tool (system_tool_id) -); +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -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) - , 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_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) - , CONSTRAINT FK_lams_input_activity_1 FOREIGN KEY (activity_id) - REFERENCES lams_learning_activity (activity_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , CONSTRAINT FK_lams_input_activity_2 FOREIGN KEY (activity_id) - REFERENCES lams_learning_activity (activity_id) -); +-- +-- Table structure for table `lams_competence` +-- -CREATE TABLE lams_branch_condition ( - condition_id BIGINT(20) NOT NULL AUTO_INCREMENT - , condition_ui_id INT(11) - , order_id INT(11) - , name VARCHAR(255) NOT NULL - , display_name VARCHAR(255) - , type VARCHAR(30) NOT NULL - , start_value VARCHAR(255) - , end_value VARCHAR(255) - , exact_match_value VARCHAR(255) - , PRIMARY KEY (condition_id) -); +DROP TABLE IF EXISTS `lams_competence`; -CREATE TABLE lams_text_search_condition ( - condition_id BIGINT(20) NOT NULL - , text_search_all_words TEXT - , text_search_phrase TEXT - , text_search_any_words TEXT - , text_search_excluded_words TEXT - , 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_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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_branch_activity_entry ( - entry_id BIGINT(20) NOT NULL AUTO_INCREMENT - , entry_ui_id INT(11) - , group_id BIGINT(20) - , sequence_activity_id BIGINT(20) - , branch_activity_id BIGINT(20) NOT NULL - , condition_id BIGINT(20) - , open_gate TINYINT(1) - , PRIMARY KEY (entry_id) - , KEY (group_id) - , CONSTRAINT FK_lams_group_activity_1 FOREIGN KEY (group_id) - REFERENCES lams_group (group_id) - , KEY (sequence_activity_id) - , CONSTRAINT FK_lams_branch_map_sequence FOREIGN KEY (sequence_activity_id) - REFERENCES lams_learning_activity (activity_id) - , KEY (branch_activity_id) - , CONSTRAINT FK_lams_branch_map_branch FOREIGN KEY (branch_activity_id) - REFERENCES lams_learning_activity (activity_id) - , KEY (condition_id) - , CONSTRAINT FK_lams_branch_activity_entry_4 FOREIGN KEY (condition_id) - REFERENCES lams_branch_condition (condition_id) -); +-- +-- Table structure for table `lams_competence_mapping` +-- +DROP TABLE IF EXISTS `lams_competence_mapping`; -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) NOT NULL - , description TEXT - , create_date_time DATETIME NOT NULL - , organisation_id BIGINT(20) - , class_grouping_id BIGINT(20) - , lesson_state_id INT(3) NOT NULL - , start_date_time DATETIME - , scheduled_number_days_to_lesson_finish INT(3) - , schedule_start_date_time DATETIME - , end_date_time DATETIME - , schedule_end_date_time DATETIME - , previous_state_id INT(3) - , 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 - , version INTEGER DEFAULT 1 - , 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 - , PRIMARY KEY (lesson_id) - , KEY (learning_design_id) - , CONSTRAINT FK_lams_lesson_1_1 FOREIGN KEY (learning_design_id) - REFERENCES lams_learning_design (learning_design_id) - , KEY (user_id) - , CONSTRAINT FK_lams_lesson_2 FOREIGN KEY (user_id) - REFERENCES lams_user (user_id) - , KEY (organisation_id) - , CONSTRAINT FK_lams_lesson_3 FOREIGN KEY (organisation_id) - REFERENCES lams_organisation (organisation_id) - , KEY (lesson_state_id) - , CONSTRAINT FK_lams_lesson_4 FOREIGN KEY (lesson_state_id) - REFERENCES lams_lesson_state (lesson_state_id) - , KEY (class_grouping_id) - , CONSTRAINT FK_lams_lesson_5 FOREIGN KEY (class_grouping_id) - REFERENCES lams_grouping (grouping_id) -); +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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -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) - , CONSTRAINT FK_lams_user_organisation_1 FOREIGN KEY (organisation_id) - REFERENCES lams_organisation (organisation_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , KEY (user_id) - , CONSTRAINT FK_lams_user_organisation_2 FOREIGN KEY (user_id) - REFERENCES lams_user (user_id) ON DELETE NO ACTION ON UPDATE NO ACTION -); +-- +-- Table structure for table `lams_configuration` +-- -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 NOT NULL - , start_date_time DATETIME NOT NULL - , finish_date_time DATETIME - , current_activity_id BIGINT(20) - , next_activity_id BIGINT(20) - , previous_activity_id BIGINT(20) - , 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) - , CONSTRAINT FK_lams_learner_progress_1 FOREIGN KEY (user_id) - REFERENCES lams_user (user_id) - , KEY (lesson_id) - , CONSTRAINT FK_lams_learner_progress_2 FOREIGN KEY (lesson_id) - REFERENCES lams_lesson (lesson_id) - , KEY (current_activity_id) - , CONSTRAINT FK_lams_learner_progress_3 FOREIGN KEY (current_activity_id) - REFERENCES lams_learning_activity (activity_id) - , KEY (next_activity_id) - , CONSTRAINT FK_lams_learner_progress_4 FOREIGN KEY (next_activity_id) - REFERENCES lams_learning_activity (activity_id) - , KEY (previous_activity_id) - , CONSTRAINT FK_lams_learner_progress_5 FOREIGN KEY (previous_activity_id) - REFERENCES lams_learning_activity (activity_id) -); +DROP TABLE IF EXISTS `lams_configuration`; -CREATE TABLE lams_cr_node ( - node_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT - , workspace_id BIGINT(20) UNSIGNED NOT NULL - , path VARCHAR(255) - , type VARCHAR(255) NOT NULL - , created_date_time DATETIME NOT NULL - , next_version_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 1 - , parent_nv_id BIGINT(20) UNSIGNED - , PRIMARY KEY (node_id) - , KEY (workspace_id) - , CONSTRAINT FK_lams_cr_node_1 FOREIGN KEY (workspace_id) - REFERENCES lams_cr_workspace (workspace_id) -); +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -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) - , CONSTRAINT FK_lams_cr_node_version_2 FOREIGN KEY (node_id) - REFERENCES lams_cr_node (node_id) -); +-- +-- Table structure for table `lams_copy_type` +-- -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) - , CONSTRAINT FK_lams_user_organisation_role_2 FOREIGN KEY (role_id) - REFERENCES lams_role (role_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , KEY (user_organisation_id) - , CONSTRAINT FK_lams_user_organisation_role_3 FOREIGN KEY (user_organisation_id) - REFERENCES lams_user_organisation (user_organisation_id) ON DELETE NO ACTION ON UPDATE NO ACTION -); +DROP TABLE IF EXISTS `lams_copy_type`; -CREATE TABLE lams_tool_session ( - tool_session_id BIGINT(20) NOT NULL AUTO_INCREMENT - , tool_session_name VARCHAR(255) 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) - , user_id BIGINT(20) - , unique_key VARCHAR(128) NOT NULL - , PRIMARY KEY (tool_session_id) - , UNIQUE UQ_lams_tool_session_1 (unique_key) - , KEY (tool_session_state_id) - , CONSTRAINT FK_lams_tool_session_4 FOREIGN KEY (tool_session_state_id) - REFERENCES lams_tool_session_state (tool_session_state_id) - , KEY (user_id) - , CONSTRAINT FK_lams_tool_session_5 FOREIGN KEY (user_id) - REFERENCES lams_user (user_id) - , KEY (tool_session_type_id) - , CONSTRAINT FK_lams_tool_session_7 FOREIGN KEY (tool_session_type_id) - REFERENCES lams_tool_session_type (tool_session_type_id) - , KEY (activity_id) - , CONSTRAINT FK_lams_tool_session_8 FOREIGN KEY (activity_id) - REFERENCES lams_learning_activity (activity_id) - , KEY (group_id) - , CONSTRAINT FK_lams_tool_session_1 FOREIGN KEY (group_id) - REFERENCES lams_group (group_id) -); +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_progress_completed ( - learner_progress_id BIGINT(20) NOT NULL - , activity_id BIGINT(20) NOT NULL - , completed_date_time DATETIME - , start_date_time DATETIME - , PRIMARY KEY (learner_progress_id, activity_id) - , KEY (learner_progress_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 - , KEY (activity_id) - , 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_attempted ( - learner_progress_id BIGINT(20) NOT NULL - , activity_id BIGINT(20) NOT NULL - , start_date_time DATETIME - , PRIMARY KEY (learner_progress_id, activity_id) - , KEY (learner_progress_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 - , KEY (activity_id) - , CONSTRAINT FK_lams_progress_current_2 FOREIGN KEY (activity_id) - REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE -)ENGINE=InnoDB; +-- +-- Table structure for table `lams_cr_credential` +-- -CREATE TABLE lams_user_group ( - user_id BIGINT(20) NOT NULL - , group_id BIGINT(20) NOT NULL - , scheduled_lesson_end_date DATETIME - , PRIMARY KEY (user_id, group_id) - , KEY (user_id) - , CONSTRAINT FK_lams_user_group_1 FOREIGN KEY (user_id) - REFERENCES lams_user (user_id) - , KEY (group_id) - , CONSTRAINT FK_lams_user_group_2 FOREIGN KEY (group_id) - REFERENCES lams_group (group_id) -); +DROP TABLE IF EXISTS `lams_cr_credential`; -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) - , CONSTRAINT FK_lams_tool_content_1 FOREIGN KEY (tool_id) - REFERENCES lams_tool (tool_id) -); +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_gate_allowed_learners ( - user_id BIGINT(20) NOT NULL - , activity_id BIGINT(20) NOT NULL - , KEY (user_id) - , CONSTRAINT FK_TABLE_32_1 FOREIGN KEY (user_id) - REFERENCES lams_user (user_id) - , KEY (activity_id) - , CONSTRAINT FK_TABLE_32_2 FOREIGN KEY (activity_id) - REFERENCES lams_learning_activity (activity_id) -); -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) - , CONSTRAINT FK_lams_cr_workspace_credential_1 FOREIGN KEY (workspace_id) - REFERENCES lams_cr_workspace (workspace_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , KEY (credential_id) - , CONSTRAINT FK_lams_cr_workspace_credential_2 FOREIGN KEY (credential_id) - REFERENCES lams_cr_credential (credential_id) ON DELETE NO ACTION ON UPDATE NO ACTION -); +-- +-- Table structure for table `lams_cr_node` +-- -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) NOT NULL - , value VARCHAR(255) NOT NULL - , type TINYINT NOT NULL - , PRIMARY KEY (id) - , KEY (nv_id) - , CONSTRAINT FK_lams_cr_node_version_property_1 FOREIGN KEY (nv_id) - REFERENCES lams_cr_node_version (nv_id) -); +DROP TABLE IF EXISTS `lams_cr_node`; -CREATE TABLE lams_log_event ( - id BIGINT(20) NOT NULL AUTO_INCREMENT - , log_event_type_id INT(5) NOT NULL - , user_id BIGINT(20) NOT NULL - , occurred_date_time DATETIME NOT NULL - , learning_design_id BIGINT(20) - , lesson_id BIGINT(20) - , activity_id BIGINT(20) - , PRIMARY KEY (id) - , KEY (occurred_date_time) - , CONSTRAINT FK_lams_event_log_1 FOREIGN KEY (log_event_type_id) - REFERENCES lams_log_event_type (log_event_type_id) - , KEY (user_id) - , CONSTRAINT FK_lams_event_log_2 FOREIGN KEY (user_id) - REFERENCES lams_user (user_id) - , CONSTRAINT FK_lams_event_log_3 FOREIGN KEY (learning_design_id) - REFERENCES lams_learning_design (learning_design_id) ON DELETE CASCADE - , CONSTRAINT FK_lams_event_log_4 FOREIGN KEY (lesson_id) - REFERENCES lams_lesson (lesson_id) - , CONSTRAINT FK_lams_event_log_5 FOREIGN KEY (activity_id) - REFERENCES lams_learning_activity (activity_id) -); +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -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) NOT NULL - , description VARCHAR(64) NOT NULL - , create_date_time DATETIME NOT NULL - , last_modified_date DATETIME NOT NULL - , workspace_folder_id BIGINT(20) NOT NULL - , uuid BIGINT(20) - , version_id BIGINT(20) - , mime_type VARCHAR(10) 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) - , KEY (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_learning_transition ( - transition_id BIGINT(20) NOT NULL AUTO_INCREMENT - , transition_ui_id INT(11) - , description TEXT - , title VARCHAR(255) - , to_activity_id BIGINT(20) NOT NULL - , from_activity_id BIGINT(20) NOT NULL - , learning_design_id BIGINT(20) - , create_date_time DATETIME NOT NULL - , to_ui_id INT(11) - , from_ui_id INT(11) - , transition_type TINYINT NOT NULL DEFAULT 0 - , PRIMARY KEY (transition_id) - , KEY (from_activity_id) - , CONSTRAINT FK_learning_transition_3 FOREIGN KEY (from_activity_id) - REFERENCES lams_learning_activity (activity_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , KEY (to_activity_id) - , CONSTRAINT FK_learning_transition_2 FOREIGN KEY (to_activity_id) - REFERENCES lams_learning_activity (activity_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , KEY (learning_design_id) - , CONSTRAINT lddefn_transition_ibfk_1 FOREIGN KEY (learning_design_id) - REFERENCES lams_learning_design (learning_design_id) ON DELETE NO ACTION ON UPDATE NO ACTION -); -ALTER TABLE lams_learning_activity - ADD CONSTRAINT FK_lams_learning_activity_15 FOREIGN KEY (transition_to_id) - REFERENCES lams_learning_transition (transition_id) - , ADD CONSTRAINT FK_lams_learning_activity_16 FOREIGN KEY (transition_from_id) - REFERENCES lams_learning_transition (transition_id); +-- +-- Table structure for table `lams_cr_node_version` +-- -CREATE TABLE lams_role_privilege ( - rp_id BIGINT(20) NOT NULL AUTO_INCREMENT - , role_id INT(6) NOT NULL - , privilege_id BIGINT(20) NOT NULL - , PRIMARY KEY (rp_id) - , KEY (privilege_id) - , CONSTRAINT FK_lams_role_privilege_1 FOREIGN KEY (privilege_id) - REFERENCES lams_privilege (privilege_id) - , KEY (role_id) - , CONSTRAINT FK_lams_role_privilege_2 FOREIGN KEY (role_id) - REFERENCES lams_role (role_id) -); +DROP TABLE IF EXISTS `lams_cr_node_version`; -CREATE TABLE lams_configuration ( - config_key VARCHAR(30) NOT NULL - , config_value VARCHAR(255) - , description_key VARCHAR(255) - , header_name VARCHAR(50) - , format VARCHAR(30) - , required TINYINT(1) NOT NULL DEFAULT 0 - , PRIMARY KEY (config_key) -); +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_timezone ( - id BIGINT(20) NOT NULL AUTO_INCREMENT - , timezone_id VARCHAR(255) NOT NULL - , server_timezone TINYINT(1) DEFAULT 0 - , PRIMARY KEY (id) -); -CREATE TABLE lams_password_request ( - request_id BIGINT(20) NOT NULL AUTO_INCREMENT - , user_id BIGINT(20) NOT NULL - , request_key VARCHAR(32) NOT NULL - , request_date DATETIME NOT NULL - , PRIMARY KEY (request_id) - , UNIQUE KEY IX_lams_psswd_rqst_key (request_key) -); +-- +-- Table structure for table `lams_cr_node_version_property` +-- -CREATE TABLE lams_notification_event ( - uid BIGINT NOT NULL AUTO_INCREMENT - , scope VARCHAR(128) NOT NULL - , name VARCHAR(128) NOT NULL - , event_session_id BIGINT - , subject VARCHAR(255) - , message TEXT - , fail_time DATETIME - , html_format TINYINT(1) DEFAULT 0 - , KEY (scope,name,event_session_id) - , PRIMARY KEY (uid) -); +DROP TABLE IF EXISTS `lams_cr_node_version_property`; -CREATE TABLE lams_notification_subscription ( - uid BIGINT NOT NULL AUTO_INCREMENT - , user_id BIGINT - , event_uid BIGINT - , delivery_method_id TINYINT UNSIGNED - , last_operation_message TEXT - , PRIMARY KEY (uid) - , CONSTRAINT EventSubscriptionsToUsers FOREIGN KEY (user_id) - REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE - , KEY (event_uid) - , CONSTRAINT EventSubscriptionsToEvent FOREIGN KEY (event_uid) - REFERENCES lams_notification_event (uid) ON DELETE CASCADE ON UPDATE CASCADE -); +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_competence ( - competence_id BIGINT NOT NULL UNIQUE AUTO_INCREMENT - , learning_design_id BIGINT - , description TEXT - , title VARCHAR(191) - , UNIQUE KEY (learning_design_id, title) - , PRIMARY KEY (competence_id) - , CONSTRAINT LearningDesignCompetenceMap FOREIGN KEY (learning_design_id) - REFERENCES lams_learning_design(learning_design_id) ON DELETE CASCADE ON UPDATE CASCADE -); -CREATE TABLE lams_competence_mapping ( - competence_mapping_id BIGINT NOT NULL UNIQUE AUTO_INCREMENT - , competence_id BIGINT - , activity_id BIGINT - , PRIMARY KEY (competence_mapping_id) - , UNIQUE KEY (competence_id, activity_id) - , KEY (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 -); +-- +-- Table structure for table `lams_cr_workspace` +-- -CREATE TABLE patches ( - system_name VARCHAR(30) NOT NULL - , patch_level INTEGER(11) NOT NULL - , patch_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP - , patch_in_progress CHAR(1) NOT NULL DEFAULT 'F' - , PRIMARY KEY(system_name, patch_level) -); +DROP TABLE IF EXISTS `lams_cr_workspace`; -CREATE TABLE lams_registration ( - uid BIGINT NOT NULL AUTO_INCREMENT - , site_name VARCHAR(255) NOT NULL - , organisation VARCHAR(255) NOT NULL - , name VARCHAR(255) NOT NULL - , email VARCHAR(255) NOT NULL - , server_country VARCHAR(2) NOT NULL - , public_directory TINYINT(1) DEFAULT 1 - , enable_lams_community TINYINT(1) DEFAULT 0 - , server_key VARCHAR(255) NOT NULL - , server_id VARCHAR(255) NOT NULL - , PRIMARY KEY (uid) -); +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_planner_nodes ( - uid BIGINT(20) NOT NULL AUTO_INCREMENT - , parent_uid BIGINT(20) - , order_id TINYINT UNSIGNED NOT NULL - , locked TINYINT(1) NOT NULL DEFAULT 0 - , content_folder_id VARCHAR(32) - , title VARCHAR(255) NOT NULL - , brief_desc TEXT - , full_desc TEXT - , ld_id BIGINT(20) - , user_id BIGINT(20) - , permissions INTEGER - , PRIMARY KEY (uid) - , UNIQUE KEY (parent_uid, order_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 -); -CREATE TABLE lams_activity_evaluation ( - activity_evaluation_id BIGINT(20) NOT NULL AUTO_INCREMENT - , activity_id BIGINT(20) NOT NULL - , tool_output_definition VARCHAR(255) NOT NULL - , KEY (activity_id) - , CONSTRAINT FK_lams_activity_evaluation_1 FOREIGN KEY (activity_id) - REFERENCES lams_learning_activity (activity_id) ON DELETE CASCADE ON UPDATE CASCADE - , PRIMARY KEY (activity_evaluation_id) -); +-- +-- Table structure for table `lams_cr_workspace_credential` +-- -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 PRECISION - , feedback TEXT - , marked_in_gradebook TINYINT(1) NOT NULL DEFAULT 0 - , update_date DATETIME - , PRIMARY KEY (uid) - , KEY (activity_id, 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 +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -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 PRECISION - , feedback TEXT - , PRIMARY KEY (uid) - , KEY (lesson_id, 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_data_flow ( - data_flow_object_id BIGINT(20) NOT NULL AUTO_INCREMENT - , transition_id BIGINT(20) NOT NULL - , order_id INT(11) - , name VARCHAR(255) NOT NULL - , display_name VARCHAR(255) - , tool_assigment_id INT(11) - , PRIMARY KEY (data_flow_object_id) - , CONSTRAINT FK_lams_learning_transition_1 FOREIGN KEY (transition_id) - REFERENCES lams_learning_transition (transition_id) ON DELETE CASCADE ON UPDATE CASCADE -); +-- +-- Table structure for table `lams_data_flow` +-- -CREATE TABLE lams_user_disabled_tutorials ( - user_id BIGINT(20) NOT NULL - , page_str CHAR(5) NOT NULL - , PRIMARY KEY (user_id,page_str) - , CONSTRAINT FK_lams_user_disabled_tutorials_1 FOREIGN KEY (user_id) - REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE -); +DROP TABLE IF EXISTS `lams_data_flow`; -CREATE TABLE lams_planner_recent_learning_designs ( - user_id BIGINT(20) NOT NULL - , learning_design_id BIGINT(20) NOT NULL - , last_modified_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP - , PRIMARY KEY (user_id,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_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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -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) - , CONSTRAINT FK_planner_node_role_user FOREIGN KEY (user_id) - REFERENCES lams_user (user_id) ON DELETE CASCADE ON UPDATE NO ACTION - , CONSTRAINT FK_planner_node_role_node FOREIGN KEY (node_uid) - REFERENCES lams_planner_nodes (uid) ON DELETE CASCADE ON UPDATE NO ACTION - , CONSTRAINT FK_planner_node_role_role FOREIGN KEY (role_id) - REFERENCES lams_role (role_id) ON DELETE CASCADE ON UPDATE NO ACTION -); -CREATE TABLE lams_lesson_dependency ( - lesson_id BIGINT(20) - , preceding_lesson_id BIGINT(20) - , PRIMARY KEY (lesson_id,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 -); +-- +-- Table structure for table `lams_email_notification_archive` +-- +DROP TABLE IF EXISTS `lams_email_notification_archive`; -CREATE TABLE lams_notebook_entry ( - uid BIGINT(20) NOT NULL AUTO_INCREMENT - , external_id BIGINT(20) - , external_id_type INT(11) - , external_signature VARCHAR(255) - , user_id INT(11) - , title VARCHAR(255) - , entry TEXT - , create_date DATETIME - , last_modified DATETIME - , 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_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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_presence_chat_msgs ( - uid BIGINT NOT NULL AUTO_INCREMENT - , lesson_id BIGINT(20) - , from_user VARCHAR(191) - , to_user VARCHAR(191) - , date_sent DATETIME - , message VARCHAR(1023) - , 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 -); +-- +-- Table structure for table `lams_email_notification_recipient_archive` +-- +DROP TABLE IF EXISTS `lams_email_notification_recipient_archive`; -CREATE TABLE lams_ext_server_type ( - server_type_id INT(11) NOT NULL - , description VARCHAR(255) NOT NULL - , PRIMARY KEY (server_type_id) -); +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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_ext_server_org_map ( - sid INT(11) NOT NULL AUTO_INCREMENT - , serverid VARCHAR(191) NOT NULL - , serverkey TEXT NOT NULL - , servername VARCHAR(255) NOT NULL - , serverdesc TEXT - , prefix VARCHAR(11) NOT NULL - , userinfo_url TEXT NOT NULL - , server_url VARCHAR(255) - , timeout_url TEXT NOT NULL - , lesson_finish_url TEXT - , disabled BIT(1) NOT NULL - , time_to_live_login_request INT(11) DEFAULT 80 - , time_to_live_login_request_enabled tinyINT(1) NOT NULL DEFAULT 0 - , ext_groups_url TEXT - , server_type_id INT(11) NOT NULL DEFAULT 1 - , lti_consumer_monitor_roles TEXT - , 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_course_class_map ( - sid INT(11) NOT NULL AUTO_INCREMENT - , courseid VARCHAR(255) 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 -); +-- +-- Table structure for table `lams_ext_course_class_map` +-- +DROP TABLE IF EXISTS `lams_ext_course_class_map`; -CREATE TABLE lams_ext_user_userid_map ( - sid INT(11) NOT NULL AUTO_INCREMENT - , external_username VARCHAR(250) NOT NULL - , user_id BIGINT(20) NOT NULL - , ext_server_org_map_id INT(11) NOT NULL - , tc_gradebook_id TEXT - , 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_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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -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_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) - , 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 -); +-- +-- Table structure for table `lams_ext_server_lesson_map` +-- -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, - course_key VARCHAR(255), - blurb TEXT, - create_date DATETIME, - disabled TINYINT(1) DEFAULT 0, - conTEXT VARCHAR(191) NOT NULL, - login_tab_active TINYINT(1) DEFAULT 0, - PRIMARY KEY (signup_organisation_id), - UNIQUE KEY conTEXT (conTEXT), - KEY organisation_id (organisation_id) -); +DROP TABLE IF EXISTS `lams_ext_server_lesson_map`; -CREATE TABLE lams_presence_user ( - nickname VARCHAR(191) NOT NULL, - lesson_id BIGINT(20) NOT NULL, - last_presence DATETIME, - 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_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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_organisation_grouping ( - grouping_id BIGINT(20) NOT NULL AUTO_INCREMENT, - organisation_id BIGINT(20) NOT NULL, - name VARCHAR(255), - 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_group ( - group_id BIGINT(20) NOT NULL AUTO_INCREMENT, - grouping_id BIGINT(20) NOT NULL, - name VARCHAR(255), - 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 -); +-- +-- Table structure for table `lams_ext_server_org_map` +-- -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 -); +DROP TABLE IF EXISTS `lams_ext_server_org_map`; -CREATE TABLE lams_learning_design_annotation ( - uid BIGINT(20) NOT NULL AUTO_INCREMENT, - learning_design_id BIGINT(20) NOT NULL, - ui_id INT(11), - title VARCHAR(1024), - xcoord INT(11), - ycoord INT(11), - end_xcoord INT(11), - end_ycoord INT(11), - color char(7), - 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_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`) +) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_learning_design_access ( - learning_design_id BIGINT(20) NOT NULL, - user_id BIGINT(20) NOT NULL, - access_date DATETIME, - 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_library_group ( - group_id INT(11) NOT NULL AUTO_INCREMENT, - name VARCHAR(64) NOT NULL, - PRIMARY KEY (group_id) -); +-- +-- Table structure for table `lams_ext_server_tool_map` +-- -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) -); +DROP TABLE IF EXISTS `lams_ext_server_tool_map`; -CREATE TABLE lams_rating_criteria_type ( - rating_criteria_type_id INT(11) NOT NULL, - description VARCHAR(255) NOT NULL, - PRIMARY KEY (rating_criteria_type_id) -); +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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_rating_criteria ( - rating_criteria_id BIGINT(20) NOT NULL AUTO_INCREMENT, - title VARCHAR(255), - 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), - item_id BIGINT(20), - lesson_id BIGINT(20), - 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) ON DELETE NO ACTION ON UPDATE NO ACTION, - CONSTRAINT FK_lams_rating_criteria_3 FOREIGN KEY (lesson_id) - REFERENCES lams_lesson (lesson_id) ON DELETE NO ACTION ON UPDATE NO ACTION -); -CREATE TABLE lams_rating ( - uid bigint(20) NOT NULL AUTO_INCREMENT, - rating_criteria_id bigint(20) NOT NULL, - item_id bigint(20), - user_id bigint(20) NOT NULL, - rating float, - PRIMARY KEY (uid), - KEY rating_criteria_id (rating_criteria_id), - KEY user_id (user_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 -); +-- +-- Table structure for table `lams_ext_server_type` +-- -CREATE TABLE lams_rating_comment ( - uid bigint(20) NOT NULL AUTO_INCREMENT, - rating_criteria_id bigint(20) NOT NULL, - item_id bigint(20), - user_id bigint(20) NOT NULL, - comment text, - posted_date datetime, - PRIMARY KEY (uid), - KEY rating_criteria_id (rating_criteria_id), - KEY user_id (user_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 -); +DROP TABLE IF EXISTS `lams_ext_server_type`; -CREATE TABLE lams_comment_session ( - uid BIGINT(20) NOT NULL AUTO_INCREMENT, - external_id BIGINT(20), - external_id_type INT(1), - external_signature VARCHAR(40), - PRIMARY KEY (uid), - UNIQUE KEY comment_ext_sig_user (external_id,external_id_type,external_signature) -); +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_comment ( - uid BIGINT(20) NOT NULL AUTO_INCREMENT, - session_id BIGINT(20) NOT NULL, - body TEXT, - create_by BIGINT(20), - create_date DATETIME, - update_date DATETIME, - update_by BIGINT(20), - last_modified DATETIME, - last_reply_date DATETIME, - reply_number INT(11), - hide_flag SMALLINT(6), - parent_uid BIGINT(20), - root_comment_uid BIGINT(20), - comment_level SMALLINT(6), - thread_comment_uid BIGINT(20), - sticky SMALLINT(6) DEFAULT 0, - monitor 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_likes ( - uid BIGINT(20) NOT NULL AUTO_INCREMENT, - comment_uid BIGINT(20) NOT NULL, - user_id BIGINT(20) NOT NULL, - vote TINYINT(1), - 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) -); +-- +-- Table structure for table `lams_ext_user_userid_map` +-- -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, - current_activity_id BIGINT(20), - 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 -); +DROP TABLE IF EXISTS `lams_ext_user_userid_map`; -CREATE TABLE lams_progress_attempted_archive ( - learner_progress_id BIGINT(20) NOT NULL, - activity_id BIGINT(20) NOT NULL, - start_date_time DATETIME, - 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_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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_progress_completed_archive ( - learner_progress_id BIGINT(20) NOT NULL, - activity_id BIGINT(20) NOT NULL, - completed_date_time DATETIME, - start_date_time DATETIME, - 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_learning_command ( - uid BIGINT(20) NOT NULL AUTO_INCREMENT, - lesson_id BIGINT(20), - user_name VARCHAR(191), - create_date DATETIME NOT NULL, - command_TEXT TEXT, - PRIMARY KEY (uid), - KEY idx_lesson_id (lesson_id), - KEY idx_user_name (user_name), - KEY idx_create_date (create_date) -); +-- +-- Table structure for table `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) ON DELETE NO ACTION ON UPDATE NO ACTION, - CONSTRAINT FK_lams_favorite_organisation_2 FOREIGN KEY (user_id) - REFERENCES lams_user (user_id) ON DELETE NO ACTION ON UPDATE NO ACTION -); +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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, + `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; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- Table structure for table `lams_learning_library` +-- + +DROP TABLE IF EXISTS `lams_learning_library`; + +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; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`), + KEY `name` (`name`), + KEY `code` (`code`), + KEY `FK_lams_outcome_1` (`organisation_id`), + KEY `FK_lams_outcome_2` (`scale_id`), + CONSTRAINT `FK_lams_outcome_1` FOREIGN KEY (`organisation_id`) REFERENCES `lams_organisation` (`organisation_id`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `FK_lams_outcome_2` FOREIGN KEY (`scale_id`) REFERENCES `lams_outcome_scale` (`scale_id`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`), + 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 +) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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, + `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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`), + UNIQUE KEY `conTEXT` (`conTEXT`), + KEY `organisation_id` (`organisation_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- Table structure for table `lams_system_tool` +-- + +DROP TABLE IF EXISTS `lams_system_tool`; + +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; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- Table structure for table `lams_tool` +-- + +DROP TABLE IF EXISTS `lams_tool`; + +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; + + +-- +-- Table structure for table `lams_tool_content` +-- + +DROP TABLE IF EXISTS `lams_tool_content`; + +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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- Table structure for table `lams_user_organisation_role` +-- + +DROP TABLE IF EXISTS `lams_user_organisation_role`; + +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; + + +-- +-- Table structure for table `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`) +) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + +-- +-- 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`) +) 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/create_quartz_table.sql =================================================================== diff -u -r7475d08afc280b5e2e5ddf04e8bf35e3166aaf80 -rf9d8fc434a8b7629a1353f532eb15e5c4f5f3cac --- lams_common/db/sql/create_quartz_table.sql (.../create_quartz_table.sql) (revision 7475d08afc280b5e2e5ddf04e8bf35e3166aaf80) +++ lams_common/db/sql/create_quartz_table.sql (.../create_quartz_table.sql) (revision f9d8fc434a8b7629a1353f532eb15e5c4f5f3cac) @@ -1,173 +1,252 @@ -# Quartz 2.2.3 +-- Quartz 2.2.3 +SET FOREIGN_KEY_CHECKS=0; +SET NAMES utf8mb4 ; -DROP TABLE IF EXISTS lams_qtz_FIRED_TRIGGERS; -DROP TABLE IF EXISTS lams_qtz_PAUSED_TRIGGER_GRPS; -DROP TABLE IF EXISTS lams_qtz_SCHEDULER_STATE; -DROP TABLE IF EXISTS lams_qtz_LOCKS; -DROP TABLE IF EXISTS lams_qtz_SIMPLE_TRIGGERS; -DROP TABLE IF EXISTS lams_qtz_SIMPROP_TRIGGERS; -DROP TABLE IF EXISTS lams_qtz_CRON_TRIGGERS; -DROP TABLE IF EXISTS lams_qtz_BLOB_TRIGGERS; -DROP TABLE IF EXISTS lams_qtz_TRIGGERS; -DROP TABLE IF EXISTS lams_qtz_JOB_DETAILS; -DROP TABLE IF EXISTS lams_qtz_CALENDARS; +-- +-- Table structure for table `lams_qtz_BLOB_TRIGGERS` +-- -CREATE TABLE lams_qtz_JOB_DETAILS( -SCHED_NAME VARCHAR(120) NOT NULL, -JOB_NAME VARCHAR(200) NOT NULL, -JOB_GROUP VARCHAR(200) NOT NULL, -DESCRIPTION VARCHAR(250) NULL, -JOB_CLASS_NAME VARCHAR(250) NOT NULL, -IS_DURABLE VARCHAR(1) NOT NULL, -IS_NONCONCURRENT VARCHAR(1) NOT NULL, -IS_UPDATE_DATA VARCHAR(1) NOT NULL, -REQUESTS_RECOVERY VARCHAR(1) NOT NULL, -JOB_DATA BLOB NULL, -PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)) -ENGINE=InnoDB; +DROP TABLE IF EXISTS `lams_qtz_BLOB_TRIGGERS`; -CREATE TABLE lams_qtz_TRIGGERS ( -SCHED_NAME VARCHAR(120) NOT NULL, -TRIGGER_NAME VARCHAR(200) NOT NULL, -TRIGGER_GROUP VARCHAR(200) NOT NULL, -JOB_NAME VARCHAR(200) NOT NULL, -JOB_GROUP VARCHAR(200) NOT NULL, -DESCRIPTION VARCHAR(250) NULL, -NEXT_FIRE_TIME BIGINT(13) NULL, -PREV_FIRE_TIME BIGINT(13) NULL, -PRIORITY INTEGER NULL, -TRIGGER_STATE VARCHAR(16) NOT NULL, -TRIGGER_TYPE VARCHAR(8) NOT NULL, -START_TIME BIGINT(13) NOT NULL, -END_TIME BIGINT(13) NULL, -CALENDAR_NAME VARCHAR(200) NULL, -MISFIRE_INSTR SMALLINT(2) NULL, -JOB_DATA BLOB NULL, -PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), -FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP) -REFERENCES lams_qtz_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)) -ENGINE=InnoDB; +CREATE TABLE `lams_qtz_BLOB_TRIGGERS` ( + `SCHED_NAME` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL, + `TRIGGER_NAME` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `TRIGGER_GROUP` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `BLOB_DATA` blob, + PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`), + KEY `SCHED_NAME` (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`), + CONSTRAINT `lams_qtz_BLOB_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `lams_qtz_TRIGGERS` (`sched_name`, `trigger_name`, `trigger_group`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_qtz_SIMPLE_TRIGGERS ( -SCHED_NAME VARCHAR(120) NOT NULL, -TRIGGER_NAME VARCHAR(200) NOT NULL, -TRIGGER_GROUP VARCHAR(200) NOT NULL, -REPEAT_COUNT BIGINT(7) NOT NULL, -REPEAT_INTERVAL BIGINT(12) NOT NULL, -TIMES_TRIGGERED BIGINT(10) NOT NULL, -PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), -FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) -REFERENCES lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)) -ENGINE=InnoDB; +-- +-- Table structure for table `lams_qtz_CALENDARS` +-- -CREATE TABLE lams_qtz_CRON_TRIGGERS ( -SCHED_NAME VARCHAR(120) NOT NULL, -TRIGGER_NAME VARCHAR(200) NOT NULL, -TRIGGER_GROUP VARCHAR(200) NOT NULL, -CRON_EXPRESSION VARCHAR(120) NOT NULL, -TIME_ZONE_ID VARCHAR(80), -PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), -FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) -REFERENCES lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)) -ENGINE=InnoDB; +DROP TABLE IF EXISTS `lams_qtz_CALENDARS`; -CREATE TABLE lams_qtz_SIMPROP_TRIGGERS - ( - SCHED_NAME VARCHAR(120) NOT NULL, - TRIGGER_NAME VARCHAR(200) NOT NULL, - TRIGGER_GROUP VARCHAR(200) NOT NULL, - STR_PROP_1 VARCHAR(512) NULL, - STR_PROP_2 VARCHAR(512) NULL, - STR_PROP_3 VARCHAR(512) NULL, - INT_PROP_1 INT NULL, - INT_PROP_2 INT NULL, - LONG_PROP_1 BIGINT NULL, - LONG_PROP_2 BIGINT NULL, - DEC_PROP_1 NUMERIC(13,4) NULL, - DEC_PROP_2 NUMERIC(13,4) NULL, - BOOL_PROP_1 VARCHAR(1) NULL, - BOOL_PROP_2 VARCHAR(1) NULL, - PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), - FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) - REFERENCES lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)) -ENGINE=InnoDB; +CREATE TABLE `lams_qtz_CALENDARS` ( + `SCHED_NAME` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL, + `CALENDAR_NAME` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `CALENDAR` blob NOT NULL, + PRIMARY KEY (`SCHED_NAME`,`CALENDAR_NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_qtz_BLOB_TRIGGERS ( -SCHED_NAME VARCHAR(120) NOT NULL, -TRIGGER_NAME VARCHAR(200) NOT NULL, -TRIGGER_GROUP VARCHAR(200) NOT NULL, -BLOB_DATA BLOB NULL, -PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), -INDEX (SCHED_NAME,TRIGGER_NAME, TRIGGER_GROUP), -FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) -REFERENCES lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)) -ENGINE=InnoDB; +-- +-- Table structure for table `lams_qtz_CRON_TRIGGERS` +-- -CREATE TABLE lams_qtz_CALENDARS ( -SCHED_NAME VARCHAR(120) NOT NULL, -CALENDAR_NAME VARCHAR(200) NOT NULL, -CALENDAR BLOB NOT NULL, -PRIMARY KEY (SCHED_NAME,CALENDAR_NAME)) -ENGINE=InnoDB; +DROP TABLE IF EXISTS `lams_qtz_CRON_TRIGGERS`; -CREATE TABLE lams_qtz_PAUSED_TRIGGER_GRPS ( -SCHED_NAME VARCHAR(120) NOT NULL, -TRIGGER_GROUP VARCHAR(200) NOT NULL, -PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP)) -ENGINE=InnoDB; +CREATE TABLE `lams_qtz_CRON_TRIGGERS` ( + `SCHED_NAME` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL, + `TRIGGER_NAME` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `TRIGGER_GROUP` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `CRON_EXPRESSION` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL, + `TIME_ZONE_ID` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`), + CONSTRAINT `lams_qtz_CRON_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `lams_qtz_TRIGGERS` (`sched_name`, `trigger_name`, `trigger_group`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE TABLE lams_qtz_FIRED_TRIGGERS ( -SCHED_NAME VARCHAR(120) NOT NULL, -ENTRY_ID VARCHAR(95) NOT NULL, -TRIGGER_NAME VARCHAR(200) NOT NULL, -TRIGGER_GROUP VARCHAR(200) NOT NULL, -INSTANCE_NAME VARCHAR(200) NOT NULL, -FIRED_TIME BIGINT(13) NOT NULL, -SCHED_TIME BIGINT(13) NOT NULL, -PRIORITY INTEGER NOT NULL, -STATE VARCHAR(16) NOT NULL, -JOB_NAME VARCHAR(200) NULL, -JOB_GROUP VARCHAR(200) NULL, -IS_NONCONCURRENT VARCHAR(1) NULL, -REQUESTS_RECOVERY VARCHAR(1) NULL, -PRIMARY KEY (SCHED_NAME,ENTRY_ID)) -ENGINE=InnoDB; +-- +-- Table structure for table `lams_qtz_FIRED_TRIGGERS` +-- -CREATE TABLE lams_qtz_SCHEDULER_STATE ( -SCHED_NAME VARCHAR(120) NOT NULL, -INSTANCE_NAME VARCHAR(200) NOT NULL, -LAST_CHECKIN_TIME BIGINT(13) NOT NULL, -CHECKIN_INTERVAL BIGINT(13) NOT NULL, -PRIMARY KEY (SCHED_NAME,INSTANCE_NAME)) -ENGINE=InnoDB; +DROP TABLE IF EXISTS `lams_qtz_FIRED_TRIGGERS`; -CREATE TABLE lams_qtz_LOCKS ( -SCHED_NAME VARCHAR(120) NOT NULL, -LOCK_NAME VARCHAR(40) NOT NULL, -PRIMARY KEY (SCHED_NAME,LOCK_NAME)) -ENGINE=InnoDB; +CREATE TABLE `lams_qtz_FIRED_TRIGGERS` ( + `SCHED_NAME` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL, + `ENTRY_ID` varchar(95) COLLATE utf8mb4_unicode_ci NOT NULL, + `TRIGGER_NAME` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `TRIGGER_GROUP` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `INSTANCE_NAME` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `FIRED_TIME` bigint(13) NOT NULL, + `SCHED_TIME` bigint(13) NOT NULL, + `PRIORITY` int(11) NOT NULL, + `STATE` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL, + `JOB_NAME` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `JOB_GROUP` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `IS_NONCONCURRENT` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `REQUESTS_RECOVERY` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (`SCHED_NAME`,`ENTRY_ID`), + KEY `IDX_lams_qtz_FT_TRIG_INST_NAME` (`SCHED_NAME`,`INSTANCE_NAME`), + KEY `IDX_lams_qtz_FT_INST_JOB_REQ_RCVRY` (`SCHED_NAME`,`INSTANCE_NAME`,`REQUESTS_RECOVERY`), + KEY `IDX_lams_qtz_FT_J_G` (`SCHED_NAME`,`JOB_NAME`,`JOB_GROUP`), + KEY `IDX_lams_qtz_FT_JG` (`SCHED_NAME`,`JOB_GROUP`), + KEY `IDX_lams_qtz_FT_T_G` (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`), + KEY `IDX_lams_qtz_FT_TG` (`SCHED_NAME`,`TRIGGER_GROUP`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -CREATE INDEX IDX_lams_qtz_J_REQ_RECOVERY ON lams_qtz_JOB_DETAILS(SCHED_NAME,REQUESTS_RECOVERY); -CREATE INDEX IDX_lams_qtz_J_GRP ON lams_qtz_JOB_DETAILS(SCHED_NAME,JOB_GROUP); +-- +-- Table structure for table `lams_qtz_JOB_DETAILS` +-- -CREATE INDEX IDX_lams_qtz_T_J ON lams_qtz_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP); -CREATE INDEX IDX_lams_qtz_T_JG ON lams_qtz_TRIGGERS(SCHED_NAME,JOB_GROUP); -CREATE INDEX IDX_lams_qtz_T_C ON lams_qtz_TRIGGERS(SCHED_NAME,CALENDAR_NAME); -CREATE INDEX IDX_lams_qtz_T_G ON lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_GROUP); -CREATE INDEX IDX_lams_qtz_T_STATE ON lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_STATE); -CREATE INDEX IDX_lams_qtz_T_N_STATE ON lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE); -CREATE INDEX IDX_lams_qtz_T_N_G_STATE ON lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_GROUP,TRIGGER_STATE); -CREATE INDEX IDX_lams_qtz_T_NEXT_FIRE_TIME ON lams_qtz_TRIGGERS(SCHED_NAME,NEXT_FIRE_TIME); -CREATE INDEX IDX_lams_qtz_T_NFT_ST ON lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME); -CREATE INDEX IDX_lams_qtz_T_NFT_MISFIRE ON lams_qtz_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME); -CREATE INDEX IDX_lams_qtz_T_NFT_ST_MISFIRE ON lams_qtz_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE); -CREATE INDEX IDX_lams_qtz_T_NFT_ST_MISFIRE_GRP ON lams_qtz_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE); +DROP TABLE IF EXISTS `lams_qtz_JOB_DETAILS`; -CREATE INDEX IDX_lams_qtz_FT_TRIG_INST_NAME ON lams_qtz_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME); -CREATE INDEX IDX_lams_qtz_FT_INST_JOB_REQ_RCVRY ON lams_qtz_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY); -CREATE INDEX IDX_lams_qtz_FT_J_G ON lams_qtz_FIRED_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP); -CREATE INDEX IDX_lams_qtz_FT_JG ON lams_qtz_FIRED_TRIGGERS(SCHED_NAME,JOB_GROUP); -CREATE INDEX IDX_lams_qtz_FT_T_G ON lams_qtz_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP); -CREATE INDEX IDX_lams_qtz_FT_TG ON lams_qtz_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_GROUP); +CREATE TABLE `lams_qtz_JOB_DETAILS` ( + `SCHED_NAME` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL, + `JOB_NAME` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `JOB_GROUP` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `DESCRIPTION` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `JOB_CLASS_NAME` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL, + `IS_DURABLE` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL, + `IS_NONCONCURRENT` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL, + `IS_UPDATE_DATA` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL, + `REQUESTS_RECOVERY` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL, + `JOB_DATA` blob, + PRIMARY KEY (`SCHED_NAME`,`JOB_NAME`,`JOB_GROUP`), + KEY `IDX_lams_qtz_J_REQ_RECOVERY` (`SCHED_NAME`,`REQUESTS_RECOVERY`), + KEY `IDX_lams_qtz_J_GRP` (`SCHED_NAME`,`JOB_GROUP`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -commit; \ No newline at end of file +-- +-- Dumping data for table `lams_qtz_JOB_DETAILS` +-- + +LOCK TABLES `lams_qtz_JOB_DETAILS` WRITE; +INSERT INTO `lams_qtz_JOB_DETAILS` VALUES ('LAMS','Resend Messages Job','DEFAULT',NULL,'org.lamsfoundation.lams.events.ResendMessagesJob','1','0','0','0',0xACED0005737200156F72672E71756172747A2E4A6F62446174614D61709FB083E8BFA9B0CB020000787200266F72672E71756172747A2E7574696C732E537472696E674B65794469727479466C61674D61708208E8C3FBC55D280200015A0013616C6C6F77735472616E7369656E74446174617872001D6F72672E71756172747A2E7574696C732E4469727479466C61674D617013E62EAD28760ACE0200025A000564697274794C00036D617074000F4C6A6176612F7574696C2F4D61703B787000737200116A6176612E7574696C2E486173684D61700507DAC1C31660D103000246000A6C6F6164466163746F724900097468726573686F6C6478703F40000000000010770800000010000000007800); +UNLOCK TABLES; + +-- +-- Table structure for table `lams_qtz_LOCKS` +-- + +DROP TABLE IF EXISTS `lams_qtz_LOCKS`; + +CREATE TABLE `lams_qtz_LOCKS` ( + `SCHED_NAME` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL, + `LOCK_NAME` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (`SCHED_NAME`,`LOCK_NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Dumping data for table `lams_qtz_LOCKS` +-- + +LOCK TABLES `lams_qtz_LOCKS` WRITE; +INSERT INTO `lams_qtz_LOCKS` VALUES ('LAMS','TRIGGER_ACCESS'); +UNLOCK TABLES; + +-- +-- Table structure for table `lams_qtz_PAUSED_TRIGGER_GRPS` +-- + +DROP TABLE IF EXISTS `lams_qtz_PAUSED_TRIGGER_GRPS`; + +CREATE TABLE `lams_qtz_PAUSED_TRIGGER_GRPS` ( + `SCHED_NAME` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL, + `TRIGGER_GROUP` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + PRIMARY KEY (`SCHED_NAME`,`TRIGGER_GROUP`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table `lams_qtz_SCHEDULER_STATE` +-- + +DROP TABLE IF EXISTS `lams_qtz_SCHEDULER_STATE`; + +CREATE TABLE `lams_qtz_SCHEDULER_STATE` ( + `SCHED_NAME` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL, + `INSTANCE_NAME` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `LAST_CHECKIN_TIME` bigint(13) NOT NULL, + `CHECKIN_INTERVAL` bigint(13) NOT NULL, + PRIMARY KEY (`SCHED_NAME`,`INSTANCE_NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table `lams_qtz_SIMPLE_TRIGGERS` +-- + +DROP TABLE IF EXISTS `lams_qtz_SIMPLE_TRIGGERS`; + +CREATE TABLE `lams_qtz_SIMPLE_TRIGGERS` ( + `SCHED_NAME` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL, + `TRIGGER_NAME` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `TRIGGER_GROUP` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `REPEAT_COUNT` bigint(7) NOT NULL, + `REPEAT_INTERVAL` bigint(12) NOT NULL, + `TIMES_TRIGGERED` bigint(10) NOT NULL, + PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`), + CONSTRAINT `lams_qtz_SIMPLE_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `lams_qtz_TRIGGERS` (`sched_name`, `trigger_name`, `trigger_group`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Dumping data for table `lams_qtz_SIMPLE_TRIGGERS` +-- + +LOCK TABLES `lams_qtz_SIMPLE_TRIGGERS` WRITE; +INSERT INTO `lams_qtz_SIMPLE_TRIGGERS` VALUES ('LAMS','Resend Messages Job Trigger','DEFAULT',-1,3600000,1); +UNLOCK TABLES; + +-- +-- Table structure for table `lams_qtz_SIMPROP_TRIGGERS` +-- + +DROP TABLE IF EXISTS `lams_qtz_SIMPROP_TRIGGERS`; + +CREATE TABLE `lams_qtz_SIMPROP_TRIGGERS` ( + `SCHED_NAME` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL, + `TRIGGER_NAME` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `TRIGGER_GROUP` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `STR_PROP_1` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `STR_PROP_2` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `STR_PROP_3` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `INT_PROP_1` int(11) DEFAULT NULL, + `INT_PROP_2` int(11) DEFAULT NULL, + `LONG_PROP_1` bigint(20) DEFAULT NULL, + `LONG_PROP_2` bigint(20) DEFAULT NULL, + `DEC_PROP_1` decimal(13,4) DEFAULT NULL, + `DEC_PROP_2` decimal(13,4) DEFAULT NULL, + `BOOL_PROP_1` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `BOOL_PROP_2` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`), + CONSTRAINT `lams_qtz_SIMPROP_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `lams_qtz_TRIGGERS` (`sched_name`, `trigger_name`, `trigger_group`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Table structure for table `lams_qtz_TRIGGERS` +-- + +DROP TABLE IF EXISTS `lams_qtz_TRIGGERS`; + +CREATE TABLE `lams_qtz_TRIGGERS` ( + `SCHED_NAME` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL, + `TRIGGER_NAME` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `TRIGGER_GROUP` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `JOB_NAME` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `JOB_GROUP` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, + `DESCRIPTION` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `NEXT_FIRE_TIME` bigint(13) DEFAULT NULL, + `PREV_FIRE_TIME` bigint(13) DEFAULT NULL, + `PRIORITY` int(11) DEFAULT NULL, + `TRIGGER_STATE` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL, + `TRIGGER_TYPE` varchar(8) COLLATE utf8mb4_unicode_ci NOT NULL, + `START_TIME` bigint(13) NOT NULL, + `END_TIME` bigint(13) DEFAULT NULL, + `CALENDAR_NAME` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `MISFIRE_INSTR` smallint(2) DEFAULT NULL, + `JOB_DATA` blob, + PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`), + KEY `IDX_lams_qtz_T_J` (`SCHED_NAME`,`JOB_NAME`,`JOB_GROUP`), + KEY `IDX_lams_qtz_T_JG` (`SCHED_NAME`,`JOB_GROUP`), + KEY `IDX_lams_qtz_T_C` (`SCHED_NAME`,`CALENDAR_NAME`), + KEY `IDX_lams_qtz_T_G` (`SCHED_NAME`,`TRIGGER_GROUP`), + KEY `IDX_lams_qtz_T_STATE` (`SCHED_NAME`,`TRIGGER_STATE`), + KEY `IDX_lams_qtz_T_N_STATE` (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`,`TRIGGER_STATE`), + KEY `IDX_lams_qtz_T_N_G_STATE` (`SCHED_NAME`,`TRIGGER_GROUP`,`TRIGGER_STATE`), + KEY `IDX_lams_qtz_T_NEXT_FIRE_TIME` (`SCHED_NAME`,`NEXT_FIRE_TIME`), + KEY `IDX_lams_qtz_T_NFT_ST` (`SCHED_NAME`,`TRIGGER_STATE`,`NEXT_FIRE_TIME`), + KEY `IDX_lams_qtz_T_NFT_MISFIRE` (`SCHED_NAME`,`MISFIRE_INSTR`,`NEXT_FIRE_TIME`), + KEY `IDX_lams_qtz_T_NFT_ST_MISFIRE` (`SCHED_NAME`,`MISFIRE_INSTR`,`NEXT_FIRE_TIME`,`TRIGGER_STATE`), + KEY `IDX_lams_qtz_T_NFT_ST_MISFIRE_GRP` (`SCHED_NAME`,`MISFIRE_INSTR`,`NEXT_FIRE_TIME`,`TRIGGER_GROUP`,`TRIGGER_STATE`), + CONSTRAINT `lams_qtz_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `JOB_NAME`, `JOB_GROUP`) REFERENCES `lams_qtz_JOB_DETAILS` (`sched_name`, `job_name`, `job_group`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + +-- +-- Dumping data for table `lams_qtz_TRIGGERS` +-- + +LOCK TABLES `lams_qtz_TRIGGERS` WRITE; +INSERT INTO `lams_qtz_TRIGGERS` VALUES ('LAMS','Resend Messages Job Trigger','DEFAULT','Resend Messages Job','DEFAULT',NULL,1546640319849,1546636719849,0,'WAITING','SIMPLE',1546636719849,0,NULL,0,''); +UNLOCK TABLES; + +SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Index: lams_common/db/sql/insert_lams_config_data.sql =================================================================== diff -u -r7475d08afc280b5e2e5ddf04e8bf35e3166aaf80 -rf9d8fc434a8b7629a1353f532eb15e5c4f5f3cac --- lams_common/db/sql/insert_lams_config_data.sql (.../insert_lams_config_data.sql) (revision 7475d08afc280b5e2e5ddf04e8bf35e3166aaf80) +++ lams_common/db/sql/insert_lams_config_data.sql (.../insert_lams_config_data.sql) (revision f9d8fc434a8b7629a1353f532eb15e5c4f5f3cac) @@ -1,302 +1,12 @@ -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('ServerURL','http://localhost:8080/lams/', 'config.server.url', 'config.header.system', 'STRING', 1); +SET FOREIGN_KEY_CHECKS=0; +SET NAMES utf8mb4 ; -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('ServerURLContextPath','lams/', 'config.server.url.context.path', 'config.header.system', 'STRING', 1); +-- +-- Dumping data for table `lams_configuration` +-- -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('Version','3.0', 'config.version', 'config.header.system', 'STRING', 1); +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); +UNLOCK TABLES; -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('TempDir','@temp.directory@', 'config.temp.dir', 'config.header.system', 'STRING', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('EARDir','@ear.directory@', 'config.ear.dir', 'config.header.system', 'STRING', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('EnableServerRegistration', 'false', 'config.server2server.registration.enable', 'config.header.system', 'BOOLEAN', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('SMTPServer','', 'config.smtp.server', 'config.header.email', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LamsSupportEmail','', 'config.lams.support.email', 'config.header.email', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('ContentRepositoryPath','@contentrepository.directory@', 'config.content.repository.path', 'config.header.uploads', 'STRING', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('UploadFileMaxSize','10485760', 'config.upload.file.max.size', 'config.header.uploads', 'LONG', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('UploadLargeFileMaxSize','104857600', 'config.upload.large.file.max.size', 'config.header.uploads', 'LONG', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('UploadFileMaxMemorySize','4096', 'config.upload.file.max.memory.size', 'config.header.uploads', 'LONG', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('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); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('UserInactiveTimeout','10800', 'config.user.inactive.timeout', 'config.header.system', 'LONG', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('CleanupPreviewOlderThanDays','7', 'config.cleanup.preview.older.than.days', 'config.header.system', 'LONG', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('AuthoringActivitiesColour', 'true', 'config.authoring.activities.colour', 'config.header.look.feel', 'BOOLEAN', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('AuthoringClientVersion','3.0.0.201701010000', 'config.authoring.client.version', 'config.header.versions', 'STRING', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('MonitorClientVersion','3.0.0.201701010000', 'config.monitor.client.version', 'config.header.versions', 'STRING', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LearnerClientVersion','3.0.0.201701010000', 'config.learner.client.version', 'config.header.versions', 'STRING', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('ServerVersionNumber','3.0.0.201701010000', 'config.server.version.number', 'config.header.versions', 'STRING', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('ServerLanguage','en_AU', 'config.server.language', 'config.header.look.feel', 'STRING', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('ServerPageDirection','LTR', 'config.server.page.direction', 'config.header.look.feel', 'STRING', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('DictionaryDateCreated','2017-01-01', 'config.dictionary.date.created', 'config.header.versions', 'STRING', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('HelpURL','http://wiki.lamsfoundation.org/display/lamsdocs/', 'config.help.url', 'config.header.system', 'STRING', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('DefaultTheme','defaultHTML', 'config.default.html.theme', 'config.header.look.feel', 'STRING', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('AllowDirectLessonLaunch','false', 'config.allow.direct.lesson.launch', 'config.header.features', 'BOOLEAN', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LAMS_Community_enable','false', 'config.community.enable', 'config.header.features', 'BOOLEAN', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('AllowLiveEdit','true', 'config.allow.live.edit', 'config.header.features', 'BOOLEAN', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('ShowAllMyLessonLink','true', 'config.show.all.my.lesson.link', 'config.header.features', 'BOOLEAN', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('DisplayPrintButton','false', 'config.display.print.button', 'config.header.features', 'BOOLEAN', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPProvisioningEnabled','false', 'config.ldap.provisioning.enabled', 'config.header.ldap', 'BOOLEAN', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPProviderURL','ldap://192.168.111.15', 'config.ldap.provider.url', 'config.header.ldap', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPSecurityAuthentication','simple', 'config.ldap.security.authentication', 'config.header.ldap', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPSearchFilter','(cn={0})', 'config.ldap.search.filter', 'config.header.ldap', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPBaseDN','ou=Users,dc=melcoe,dc=mq,dc=edu,dc=au', 'config.ldap.base.dn', 'config.header.ldap', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPBindUserDN','', 'config.ldap.bind.user.dn', 'config.header.ldap', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPBindUserPassword','', 'config.ldap.bind.user.password', 'config.header.ldap', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPSecurityProtocol','', 'config.ldap.security.protocol', 'config.header.ldap', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPLoginAttr','uid', 'admin.user.login', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPFNameAttr','givenName', 'admin.user.first_name', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPLNameAttr','sn', 'admin.user.last_name', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPEmailAttr','mail', 'admin.user.email', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPAddr1Attr','postalAddress', 'admin.user.address_line_1', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPAddr2Attr','', 'admin.user.address_line_2', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPAddr3Attr','', 'admin.user.address_line_3', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPCityAttr','l', 'admin.user.city', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPStateAttr','st', 'admin.user.state', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPPostcodeAttr','postalCode', 'admin.user.postcode', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPCountryAttr','', 'admin.user.country', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPDayPhoneAttr','telephoneNumber', 'admin.user.day_phone', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPEveningPhoneAttr','homePhone', 'admin.user.evening_phone', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPFaxAttr','facsimileTelephoneNumber', 'admin.user.fax', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPMobileAttr','mobile', 'admin.user.mobile_phone', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPLocaleAttr','preferredLanguage', 'admin.organisation.locale', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPDisabledAttr','!accountStatus', 'sysadmin.disabled', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPOrgAttr','schoolCode', 'admin.course', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPRolesAttr','memberOf', 'admin.user.roles', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPLearnerMap','Student;SchoolSupportStaff;Teacher;SeniorStaff;Principal', 'config.ldap.learner.map', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPMonitorMap','SchoolSupportStaff;Teacher;SeniorStaff;Principal', 'config.ldap.monitor.map', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPAuthorMap','Teacher;SeniorStaff;Principal', 'config.ldap.author.map', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPGroupAdminMap','Teacher;SeniorStaff', 'config.ldap.group.admin.map', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPGroupManagerMap','Principal', 'config.ldap.group.manager.map', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPUpdateOnLogin', 'true', 'config.ldap.update.on.login', 'config.header.ldap', 'BOOLEAN', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPOrgField', 'code', 'config.ldap.org.field', 'config.header.ldap.attributes', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPOnlyOneOrg', 'true', 'config.ldap.only.one.org', 'config.header.ldap', 'BOOLEAN', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LDAPSearchResultsPageSize', '100', 'config.ldap.search.results.page.size', 'config.header.ldap', 'LONG', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LearnerProgressBatchSize', '10', 'config.learner.progress.batch.size', 'config.header.look.feel', 'LONG', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('CustomTabLink','', 'config.custom.tab.link', 'config.header.look.feel', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('CustomTabTitle','', 'config.custom.tab.title', 'config.header.look.feel', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('AuthoringScreenSize','1280x720', 'config.authoring.screen.size', 'config.header.look.feel', 'STRING', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('MonitorScreenSize','1280x720', 'config.monitor.screen.size', 'config.header.look.feel', 'STRING', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LearnerScreenSize','1280x720', 'config.learner.screen.size', 'config.header.look.feel', 'STRING', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('AdminScreenSize','1280x720', 'config.admin.screen.size', 'config.header.look.feel', 'STRING', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('SMTPUser','', 'config.smtp.user', 'config.header.email', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('SMTPPassword','', 'config.smtp.password', 'config.header.email', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('Red5ServerUrl','', 'config.red5.server.url', 'config.header.red5', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('Red5RecordingsUrl','', 'config.red5.recordings.url', 'config.header.red5', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('ProfileEditEnable','true', 'config.profile.edit.enable', 'config.header.features', 'BOOLEAN', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('ProfilePartialEditEnable','true', 'config.profile.partial.edit.enable', 'config.header.features', 'BOOLEAN', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('KalturaServer','http://www.kaltura.com', 'config.kaltura.server', 'config.header.kaltura', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('KalturaPartnerId','1073272', 'config.kaltura.partner.id', 'config.header.kaltura', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('KalturaSubPartnerId','107327200', 'config.kaltura.sub.partner.id', 'config.header.kaltura', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('KalturaUserSecret','f6b13f7e128e081b5cc9bb9664fd8717', 'config.kaltura.user.secret', 'config.header.kaltura', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('KalturaKCWUiConfId','1000741', 'config.kaltura.kcw.uiconfid', 'config.header.kaltura', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('KalturaKDPUiConfId','6308762', 'config.kaltura.kdp.uiconfid', 'config.header.kaltura', 'STRING', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('ConfigCacheRefreshInterval', 0, 'config.cache.refresh', 'config.header.system', 'LONG', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('ErrorStackTrace','true', 'config.stacktrace.error', 'config.header.system', 'BOOLEAN', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('FailedAttempts','3', 'config.failed.attempts', 'config.header.password.policy', 'LONG', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LockOutTime','5', 'config.lock.out.time', 'config.header.password.policy', 'LONG', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('LearnerCollapsProgressPanel','true', 'config.learner.collapsible.progress.panel', 'config.header.features', 'BOOLEAN', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('PasswordPolicyMinChars','8', 'config.password.minimum.characters', 'config.header.password.policy', 'LONG', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('PasswordPolicyUppercase','true', 'config.password.uppercase', 'config.header.password.policy', 'BOOLEAN', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('PasswordPolicyLowercase','true', 'config.password.lowercase', 'config.header.password.policy', 'BOOLEAN', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('PasswordPolicyNumerics','true', 'config.password.numerics', 'config.header.password.policy', 'BOOLEAN', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('PasswordPolicySymbols','false', 'config.password.symbols', 'config.header.password.policy', 'BOOLEAN', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('ShowTimezoneWarning','true', 'config.show.timezone.warning', 'config.header.features', 'BOOLEAN', 1); - -INSERT INTO lams_configuration (config_key, config_value, description_key, header_name, format, required) -VALUES ('SiteName','LAMS', 'config.site.name', 'config.header.system', 'STRING', 1); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('SuffixImportedLD','false', 'config.authoring.suffix', 'config.header.features', 'BOOLEAN', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('UserValidationUsername','true', 'config.user.validation.username', 'config.header.user.validation', 'BOOLEAN', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('UserValidationFirstLastName','true', 'config.user.validation.first.last.name', 'config.header.user.validation', 'BOOLEAN', 0); - -insert into lams_configuration (config_key, config_value, description_key, header_name, format, required) -values ('UserValidationEmail','true', 'config.user.validation.emails', 'config.header.user.validation', 'BOOLEAN', 0); +SET FOREIGN_KEY_CHECKS=1; Index: lams_common/db/sql/insert_lams_users.sql =================================================================== diff -u -r7475d08afc280b5e2e5ddf04e8bf35e3166aaf80 -rf9d8fc434a8b7629a1353f532eb15e5c4f5f3cac --- lams_common/db/sql/insert_lams_users.sql (.../insert_lams_users.sql) (revision 7475d08afc280b5e2e5ddf04e8bf35e3166aaf80) +++ lams_common/db/sql/insert_lams_users.sql (.../insert_lams_users.sql) (revision f9d8fc434a8b7629a1353f532eb15e5c4f5f3cac) @@ -1,250 +1,53 @@ - - SET FOREIGN_KEY_CHECKS=0; +SET NAMES utf8mb4 ; +-- +-- Dumping data for table `lams_theme` +-- --- themes and styles -INSERT INTO lams_theme (theme_id, name, description, image_directory) VALUES (1, "defaultHTML", "Default HTML style", "css"); +LOCK TABLES `lams_theme` WRITE; +INSERT INTO `lams_theme` VALUES (1,'defaultHTML','Default HTML style','css'),(2,'purple','Purple Theme','css'); +UNLOCK TABLES; -insert into lams_workspace_folder (workspace_folder_id,parent_folder_id,name,user_id,organisation_id,create_date_time,last_modified_date_time,lams_workspace_folder_type_id) -values(1,null,'ROOT',1,1,'20041223','20041223',1); +-- +-- Dumping data for table `lams_workspace_folder` +-- -insert into lams_workspace_folder (workspace_folder_id,parent_folder_id,name,user_id,organisation_id,create_date_time,last_modified_date_time,lams_workspace_folder_type_id) -values(2,1,'Developers Playpen',1,2,'20041223','20041223',1); +LOCK TABLES `lams_workspace_folder` WRITE; +INSERT INTO `lams_workspace_folder` VALUES (1,NULL,'ROOT',1,1,NOW(),NOW(),1),(2,1,'Developers Playpen',1,2,NOW(),NOW(),1),(3,1,'MATH111',1,4,NOW(),NOW(),1),(4,NULL,'Mary Morgan',4,NULL,NOW(),NOW(),1),(5,NULL,'One Test',5,NULL,NOW(),NOW(),1),(6,NULL,'Two Test',6,NULL,NOW(),NOW(),1),(7,NULL,'Three Test',7,NULL,NOW(),NOW(),1),(8,NULL,'Four Test',8,NULL,NOW(),NOW(),1),(22,2,'Lesson Sequence Folder',1,2,NOW(),NOW(),2),(23,3,'Lesson Sequence Folder',1,4,NOW(),NOW(),2),(40,1,'Moodle Test',1,7,NOW(),NOW(),2),(41,40,'Lesson Sequence Folder',1,7,NOW(),NOW(),2),(45,NULL,'System Administrator',1,NULL,NOW(),NOW(),1),(46,1,'Public Folder',1,NULL,NOW(),NOW(),3); +UNLOCK TABLES; -insert into lams_workspace_folder (workspace_folder_id,parent_folder_id,name,user_id,organisation_id,create_date_time,last_modified_date_time,lams_workspace_folder_type_id) -values(22,2,'Lesson Sequence Folder',1,2,'20041223','20041223',2); -insert into lams_workspace_folder (workspace_folder_id,parent_folder_id,name,user_id,organisation_id,create_date_time,last_modified_date_time,lams_workspace_folder_type_id) -values(3,1,'MATH111',1,4,'20041223','20041223',1); +-- +-- Dumping data for table `lams_organisation` +-- -insert into lams_workspace_folder (workspace_folder_id,parent_folder_id,name,user_id,organisation_id,create_date_time,last_modified_date_time,lams_workspace_folder_type_id) -values(23,3,'Lesson Sequence Folder',1,4,'20041223','20041223',2); +LOCK TABLES `lams_organisation` WRITE; +INSERT INTO `lams_organisation` VALUES (1,'Root',NULL,'Root Organisation',NULL,1,NOW(),1,1,0,0,0,0,0,0,1,1,0,NULL,NULL),(2,'Playpen','PP101','Developers Playpen',1,2,NOW(),1,1,0,0,0,0,1,1,1,1,0,NULL,NULL),(3,'Everybody',NULL,'All People In Course',2,3,NOW(),1,1,0,0,0,0,0,0,1,1,0,NULL,NULL),(4,'Mathematics 1','MATH111','Mathematics 1',1,2,NOW(),1,1,0,0,0,0,1,1,1,1,0,NULL,NULL),(5,'Tutorial Group A','TUTA','Tutorial Group A',4,3,NOW(),1,1,0,0,0,0,0,0,1,1,0,NULL,NULL),(6,'Tutorial Group B','TUTB','Tutorial Group B',4,3,NOW(),1,1,0,0,0,0,0,0,1,1,0,NULL,NULL),(7,'Moodle','Moodle','Moodle Test',1,2,NOW(),1,2,0,0,0,0,0,0,1,1,0,NULL,NULL); +UNLOCK TABLES; -insert into lams_workspace_folder (workspace_folder_id,parent_folder_id,name,user_id,organisation_id,create_date_time,last_modified_date_time,lams_workspace_folder_type_id) -values(4,null,'Mary Morgan',4,NULL,'20041223','20041223',1); +-- +-- Dumping data for table `lams_user` +-- -insert into lams_workspace_folder (workspace_folder_id,parent_folder_id,name,user_id,organisation_id,create_date_time,last_modified_date_time,lams_workspace_folder_type_id) -values(5,null,'One Test',5,NULL,'20041223','20041223',1); +LOCK TABLES `lams_user` WRITE; +INSERT INTO `lams_user` VALUES (1,'sysadmin','a159b7ae81ba3552af61e9731b20870515944538',NULL,0,NULL,'The','System','Administrator',NULL,NULL,NULL,'Sydney','NSW',NULL,NULL,NULL,NULL,NULL,NULL,'sysadmin@x.x',1,0,NOW(),1,45,1,1,NULL,0,'Etc/GMT',1,NULL,NULL,NULL,NULL),(2,'test','a94a8fe5ccb19ba61c4c0873d391e987982fbbd3',NULL,0,NULL,'Dr','Testing','LDAP',NULL,NULL,NULL,'Sydney','NSW',NULL,NULL,NULL,NULL,NULL,NULL,'test@xx.xx.xx',1,0,NOW(),3,NULL,1,3,NULL,0,'Etc/GMT',1,NULL,NULL,NULL,NULL),(4,'mmm','91223fd10ce86fc852b449583aa2196c304bf6e0',NULL,0,NULL,'Ms','Mary','Morgan','99','First Ave',NULL,'Parramatta','NSW',NULL,NULL,'0295099999','0298939999','0499999999','0299999999','mmmmmmm@xx.os',1,0,NOW(),1,4,1,1,NULL,0,'Etc/GMT',1,NULL,NULL,NULL,NULL),(5,'test1','b444ac06613fc8d63795be9ad0beaf55011936ac',NULL,0,NULL,'Dr','One','Test','1','Test Ave',NULL,'Nowhere','NSW',NULL,NULL,'0211111111','0211111112','0411111111','0211111113','test1@xx.os',1,0,NOW(),1,5,1,1,NULL,0,'Etc/GMT',1,NULL,NULL,NULL,NULL),(6,'test2','109f4b3c50d7b0df729d299bc6f8e9ef9066971f',NULL,0,NULL,'Dr','Two','Test','2','Test Ave',NULL,'Nowhere','NSW',NULL,NULL,'0211111111','0211111112','0411111111','0211111113','test2@xx.os',1,0,NOW(),1,6,1,1,NULL,0,'Etc/GMT',1,NULL,NULL,NULL,NULL),(7,'test3','3ebfa301dc59196f18593c45e519287a23297589',NULL,0,NULL,'Dr','Three','Test','3','Test Ave',NULL,'Nowhere','NSW',NULL,NULL,'0211111111','0211111112','0411111111','0211111113','test3@xx.os',1,0,NOW(),1,7,1,1,NULL,0,'Etc/GMT',1,NULL,NULL,NULL,NULL),(8,'test4','1ff2b3704aede04eecb51e50ca698efd50a1379b',NULL,0,NULL,'Dr','Four','Test','4','Test Ave',NULL,'Nowhere','NSW',NULL,NULL,'0211111111','0211111112','0411111111','0211111113','test4@xx.os',1,0,NOW(),1,8,1,1,NULL,0,'Etc/GMT',1,NULL,NULL,NULL,NULL); +UNLOCK TABLES; -insert into lams_workspace_folder (workspace_folder_id,parent_folder_id,name,user_id,organisation_id,create_date_time,last_modified_date_time,lams_workspace_folder_type_id) -values(6,null,'Two Test',6,NULL,'20041223','20041223',1); +-- +-- Dumping data for table `lams_user_organisation` +-- -insert into lams_workspace_folder (workspace_folder_id,parent_folder_id,name,user_id,organisation_id,create_date_time,last_modified_date_time,lams_workspace_folder_type_id) -values(7,null,'Three Test',7,NULL,'20041223','20041223',1); +LOCK TABLES `lams_user_organisation` WRITE; +INSERT INTO `lams_user_organisation` VALUES (1,1,1),(2,2,2),(4,2,4),(5,2,5),(6,2,6),(7,2,7),(8,2,8),(9,3,2),(11,3,4),(12,3,5),(13,3,6),(14,3,7),(15,3,8),(16,4,2),(18,4,4),(19,4,5),(20,4,6),(21,4,7),(22,4,8),(23,5,2),(25,5,4),(26,5,5),(27,5,6),(28,6,7),(29,6,8); +UNLOCK TABLES; -insert into lams_workspace_folder (workspace_folder_id,parent_folder_id,name,user_id,organisation_id,create_date_time,last_modified_date_time,lams_workspace_folder_type_id) -values(8,null,'Four Test',8,NULL,'20041223','20041223',1); -insert into lams_workspace_folder (workspace_folder_id,parent_folder_id,name,user_id,organisation_id,create_date_time,last_modified_date_time,lams_workspace_folder_type_id) -values(40,1,'Moodle Test',1,7,'20041223','20041223',2); +-- +-- Dumping data for table `lams_user_organisation_role` +-- -insert into lams_workspace_folder (workspace_folder_id,parent_folder_id,name,user_id,organisation_id,create_date_time,last_modified_date_time,lams_workspace_folder_type_id) -values(41,40,'Lesson Sequence Folder',1,7,'20041223','20041223',2); +LOCK TABLES `lams_user_organisation_role` WRITE; +INSERT INTO `lams_user_organisation_role` VALUES (1,1,1),(2,2,3),(4,4,3),(5,5,3),(6,6,3),(7,7,3),(8,8,3),(9,2,4),(11,4,4),(12,5,4),(13,6,4),(14,7,4),(15,8,4),(16,2,5),(18,4,5),(19,5,5),(20,6,5),(21,7,5),(22,8,5),(23,9,4),(25,11,4),(26,12,4),(27,13,4),(28,14,4),(29,16,4),(30,9,5),(32,11,5),(33,12,5),(34,13,5),(35,14,5),(36,15,5),(37,22,2),(38,16,3),(40,18,3),(41,19,3),(42,20,3),(43,21,3),(44,22,3),(45,16,4),(47,18,4),(48,19,4),(49,20,4),(50,21,4),(51,22,4),(52,16,5),(54,18,5),(55,19,5),(56,20,5),(57,21,5),(58,22,5),(60,23,4),(62,25,4),(63,26,4),(64,27,4),(65,28,4),(66,29,4),(67,23,5),(69,25,5),(70,26,5),(71,27,5),(72,28,5),(73,29,5),(74,18,2); +UNLOCK TABLES; -insert into lams_workspace_folder (workspace_folder_id,parent_folder_id,name,user_id,organisation_id,create_date_time,last_modified_date_time,lams_workspace_folder_type_id) -values(45,null,'System Administrator',1,NULL,'20061101','20061101',1); - -insert into lams_workspace_folder (workspace_folder_id,parent_folder_id,name,user_id,organisation_id,create_date_time,last_modified_date_time,lams_workspace_folder_type_id) -values(46,1,'Public Folder',1,NULL,'20041223','20041223',3); - -INSERT INTO lams_organisation (organisation_id, name, code, description, parent_organisation_id, organisation_type_id, create_date, created_by, locale_id, organisation_state_id) - VALUES (1, 'Root', null, 'Root Organisation',null,1,NOW(),1,1,1); -INSERT INTO lams_organisation (organisation_id, name, code, description, parent_organisation_id, organisation_type_id, create_date, created_by, locale_id, organisation_state_id, enable_course_notifications, enable_monitor_gradebook, enable_learner_gradebook) - VALUES (2, 'Playpen', 'PP101', 'Developers Playpen',1,2,NOW(),1,2,1,1,1,1); -INSERT INTO lams_organisation (organisation_id, name, code, description, parent_organisation_id, organisation_type_id, create_date, created_by, locale_id, organisation_state_id) - VALUES (3, 'Everybody', null, 'All People In Course',2,3,NOW(),1,1,1); -INSERT INTO lams_organisation (organisation_id, name, code, description, parent_organisation_id, organisation_type_id, create_date, created_by, locale_id, organisation_state_id, enable_course_notifications, enable_monitor_gradebook, enable_learner_gradebook) - VALUES (4, 'Mathematics 1', 'MATH111', 'Mathematics 1',1,2,NOW(),1,2,1,1,1,1); -INSERT INTO lams_organisation (organisation_id, name, code, description, parent_organisation_id, organisation_type_id, create_date, created_by, locale_id, organisation_state_id) - VALUES (5, 'Tutorial Group A', 'TUTA', 'Tutorial Group A',4,3,NOW(),1,2,1); -INSERT INTO lams_organisation (organisation_id, name, code, description, parent_organisation_id, organisation_type_id, create_date, created_by, locale_id, organisation_state_id) - VALUES (6, 'Tutorial Group B', 'TUTB', 'Tutorial Group B',4,3,NOW(),1,2,1); -INSERT INTO lams_organisation (organisation_id, name, code, description, parent_organisation_id, organisation_type_id, create_date, created_by, locale_id, organisation_state_id) - VALUES (7, 'Moodle', 'Moodle', 'Moodle Test',1,2,NOW(),1,1,2); - --- users -INSERT INTO lams_user (user_id,login,password,title,first_name,last_name,address_line_1,address_line_2,address_line_3, -city,state,country,day_phone,evening_phone,mobile_phone,fax,email, -disabled_flag,create_date,authentication_method_id,workspace_folder_id, -locale_id, theme_id, timezone) -VALUES(1, 'sysadmin',SHA1('sysadmin'),'The','System','Administrator',null,null,null, -'Sydney','NSW','Australia',null,null,null,null,'sysadmin@x.x', -0,NOW(),1,45, -1,1,'Etc/GMT'); - -INSERT INTO lams_user (user_id,login,password,title,first_name,last_name,address_line_1,address_line_2,address_line_3, -city,state,country,day_phone,evening_phone,mobile_phone,fax,email, -disabled_flag,create_date,authentication_method_id,workspace_folder_id, -locale_id, theme_id, timezone) -VALUES(2, 'test',SHA1('test'),'Dr','Testing','LDAP',null,null,null, -'Sydney','NSW','Australia',null,null,null,null,'test@xx.xx.xx', -0,NOW(),3,null, -3,1,'Etc/GMT'); - -insert into lams_user (user_id,login,password,title,first_name,last_name,address_line_1,address_line_2,address_line_3, -city,state,country,day_phone,evening_phone,mobile_phone, -fax,email, -disabled_flag,create_date,authentication_method_id,workspace_folder_id, -locale_id, theme_id, timezone) -values -(4,'mmm',SHA1('mmm'),'Ms','Mary','Morgan','99','First Ave',null, -'Parramatta','NSW','Australia','0295099999','0298939999','0499999999', -'0299999999','mmmmmmm@xx.os', -0,'20041223',1,4,1,1,'Etc/GMT'); - -insert into lams_user (user_id,login,password,title,first_name,last_name,address_line_1,address_line_2,address_line_3, -city,state,country,day_phone,evening_phone,mobile_phone, -fax,email, -disabled_flag,create_date,authentication_method_id,workspace_folder_id, -locale_id, theme_id, timezone) -values -(5,'test1',SHA1('test1'),'Dr','One','Test','1','Test Ave',null, -'Nowhere','NSW','Australia','0211111111','0211111112','0411111111', -'0211111113','test1@xx.os', -0,'20041223',1,5,1,1,'Etc/GMT'); - -insert into lams_user (user_id,login,password,title,first_name,last_name,address_line_1,address_line_2,address_line_3, -city,state,country,day_phone,evening_phone,mobile_phone, -fax,email, -disabled_flag,create_date,authentication_method_id,workspace_folder_id, -locale_id, theme_id, timezone) -values -(6,'test2',SHA1('test2'),'Dr','Two','Test','2','Test Ave',null, -'Nowhere','NSW','Australia','0211111111','0211111112','0411111111', -'0211111113','test2@xx.os', -0,'20041223',1,6,1,1,'Etc/GMT'); - -insert into lams_user (user_id,login,password,title,first_name,last_name,address_line_1,address_line_2,address_line_3, -city,state,country,day_phone,evening_phone,mobile_phone, -fax,email, -disabled_flag,create_date,authentication_method_id,workspace_folder_id, -locale_id, theme_id, timezone) -values -(7,'test3',SHA1('test3'),'Dr','Three','Test','3','Test Ave',null, -'Nowhere','NSW','Australia','0211111111','0211111112','0411111111', -'0211111113','test3@xx.os', -0,'20041223',1,7,1,1,'Etc/GMT'); - -insert into lams_user (user_id,login,password,title,first_name,last_name,address_line_1,address_line_2,address_line_3, -city,state,country,day_phone,evening_phone,mobile_phone, -fax,email, -disabled_flag,create_date,authentication_method_id,workspace_folder_id, -locale_id, theme_id, timezone) -values -(8,'test4',SHA1('test4'),'Dr','Four','Test','4','Test Ave',null, -'Nowhere','NSW','Australia','0211111111','0211111112','0411111111', -'0211111113','test4@xx.os', -0,'20041223',1,8,1,1,'Etc/GMT'); - ---- sysadmin only belongs to root and public -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (1, 1, 1); - ---- all other users belong to Playpen -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (2, 2, 2); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (4, 2, 4); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (5, 2, 5); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (6, 2, 6); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (7, 2, 7); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (8, 2, 8); ---- all other users belong to Playpen's class Everybody -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (9, 3, 2); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (11, 3, 4); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (12, 3, 5); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (13, 3, 6); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (14, 3, 7); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (15, 3, 8); ---- all other users belong to MATH111 -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (16, 4, 2); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (18, 4, 4); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (19, 4, 5); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (20, 4, 6); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (21, 4, 7); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (22, 4, 8); ---- all test3, test4 users belong to Tutorial B, the other users belong to Tutorial A -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (23, 5, 2); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (25, 5, 4); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (26, 5, 5); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (27, 5, 6); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (28, 6, 7); -INSERT INTO lams_user_organisation (user_organisation_id, organisation_id, user_id) VALUES (29, 6, 8); - --- sysadmin has sysadmin role only -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (1, 1, 1); - --- test, lamskh01 and mmm are authors, staff and learners in Playpen --- test1, test2, test3 and test4 are authors, staff and learners in Playpen -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (2, 2, 3); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (4, 4, 3); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (5, 5, 3); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (6, 6, 3); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (7, 7, 3); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (8, 8, 3); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (9, 2, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (11, 4, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (12, 5, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (13, 6, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (14, 7, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (15, 8, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (16, 2, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (18, 4, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (19, 5, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (20, 6, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (21, 7, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (22, 8, 5); - --- test, lamskh01 and mmm are staff and learners in Everybody --- test1, test2, test3 and test4 are staff and learners in Everybody -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (23, 9, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (25, 11, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (26, 12, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (27, 13, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (28, 14, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (29, 16, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (30, 9, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (32, 11, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (33, 12, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (34, 13, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (35, 14, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (36, 15, 5); - --- test, lamskh01 and mmm are authors, staff and learners in MATH111 --- test1, test2, test3 and test4 are authors, staff and learners in MATH111 --- test4 is group manager, mmm is group admin in MATH111 -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (37, 22, 2); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (38, 16, 3); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (40, 18, 3); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (41, 19, 3); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (42, 20, 3); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (43, 21, 3); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (44, 22, 3); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (45, 16, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (47, 18, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (48, 19, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (49, 20, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (50, 21, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (51, 22, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (52, 16, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (54, 18, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (55, 19, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (56, 20, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (57, 21, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (58, 22, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (59, 18, 6); - --- test, lamskh01 and mmm are staff and learners in Tutorial A --- test1, test2 are staff and learners in Tutorial A --- test3 and test4 are staff and learners in Tutorial B -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (60, 23, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (62, 25, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (63, 26, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (64, 27, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (65, 28, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (66, 29, 4); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (67, 23, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (69, 25, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (70, 26, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (71, 27, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (72, 28, 5); -INSERT INTO lams_user_organisation_role (user_organisation_role_id, user_organisation_id, role_id) VALUES (73, 29, 5); - -SET FOREIGN_KEY_CHECKS=1; +SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Index: lams_common/db/sql/insert_types_data.sql =================================================================== diff -u -r7475d08afc280b5e2e5ddf04e8bf35e3166aaf80 -rf9d8fc434a8b7629a1353f532eb15e5c4f5f3cac --- lams_common/db/sql/insert_types_data.sql (.../insert_types_data.sql) (revision 7475d08afc280b5e2e5ddf04e8bf35e3166aaf80) +++ lams_common/db/sql/insert_types_data.sql (.../insert_types_data.sql) (revision f9d8fc434a8b7629a1353f532eb15e5c4f5f3cac) @@ -1,321 +1,235 @@ -INSERT INTO lams_privilege VALUES (1,'Z','do anything'); -INSERT INTO lams_privilege VALUES (2,'A','add/remove/modify classes within the course'); -INSERT INTO lams_privilege VALUES (3,'B','create running instances of sequences and assign those to a class'); -INSERT INTO lams_privilege VALUES (4,'C','stop/start running sequences'); -INSERT INTO lams_privilege VALUES (5,'D','monitor the progress of learners'); -INSERT INTO lams_privilege VALUES (6,'E','participates in sequences'); -INSERT INTO lams_privilege VALUES (7,'F','export their progress on each running sequence'); -INSERT INTO lams_privilege VALUES (8,'G','write/create/delete permissions in course content folder'); -INSERT INTO lams_privilege VALUES (9,'H','read course content folder'); -INSERT INTO lams_privilege VALUES (10,'I','create new users'); -INSERT INTO lams_privilege VALUES (11,'J','create guest users'); -INSERT INTO lams_privilege VALUES (12,'K','change status of course'); -INSERT INTO lams_privilege VALUES (13,'L','browse all users in the system'); -INSERT INTO lams_privilege VALUES (14,'M','write/create/delete permissions in all course content folders'); +SET FOREIGN_KEY_CHECKS=0; +SET NAMES utf8mb4 ; -INSERT INTO lams_role VALUES (1, 'SYSADMIN', 'LAMS System Adminstrator', NOW()); -INSERT INTO lams_role VALUES (2, 'GROUP MANAGER', 'Group Manager', NOW()); -INSERT INTO lams_role VALUES (3, 'AUTHOR', 'Authors Learning Designs', NOW()); -INSERT INTO lams_role VALUES (4, 'MONITOR', 'Member of Staff', NOW()); -INSERT INTO lams_role VALUES (5, 'LEARNER', 'Student', NOW()); -INSERT INTO lams_role VALUES (6, 'GROUP ADMIN', 'Group Administrator', NOW()); +-- +-- Dumping data for table `lams_activity_category` +-- -INSERT INTO lams_role_privilege VALUES (NULL,1,1); -INSERT INTO lams_role_privilege VALUES (NULL,2,2); -INSERT INTO lams_role_privilege VALUES (NULL,2,3); -INSERT INTO lams_role_privilege VALUES (NULL,2,4); -INSERT INTO lams_role_privilege VALUES (NULL,2,5); -INSERT INTO lams_role_privilege VALUES (NULL,2,8); -INSERT INTO lams_role_privilege VALUES (NULL,2,9); -INSERT INTO lams_role_privilege VALUES (NULL,2,10); -INSERT INTO lams_role_privilege VALUES (NULL,2,12); -INSERT INTO lams_role_privilege VALUES (NULL,2,13); -INSERT INTO lams_role_privilege VALUES (NULL,3,8); -INSERT INTO lams_role_privilege VALUES (NULL,3,9); -INSERT INTO lams_role_privilege VALUES (NULL,4,3); -INSERT INTO lams_role_privilege VALUES (NULL,4,4); -INSERT INTO lams_role_privilege VALUES (NULL,4,5); -INSERT INTO lams_role_privilege VALUES (NULL,5,6); -INSERT INTO lams_role_privilege VALUES (NULL,5,7); -INSERT INTO lams_role_privilege VALUES (NULL,6,2); -INSERT INTO lams_role_privilege VALUES (NULL,6,10); -INSERT INTO lams_role_privilege VALUES (NULL,6,12); -INSERT INTO lams_role_privilege VALUES (NULL,6,13); +LOCK TABLES `lams_activity_category` WRITE; +INSERT INTO `lams_activity_category` VALUES (1,'SYSTEM'),(2,'COLLABORATION'),(3,'ASSESSMENT'),(4,'CONTENT'),(5,'SPLIT'),(6,'RESPONSE'); +UNLOCK TABLES; +-- +-- Dumping data for table `lams_auth_method_type` +-- -INSERT INTO lams_auth_method_type VALUES(1, 'LAMS'); -INSERT INTO lams_auth_method_type VALUES(2, 'WEB_AUTH'); -INSERT INTO lams_auth_method_type VALUES(3, 'LDAP'); +LOCK TABLES `lams_auth_method_type` WRITE; +INSERT INTO `lams_auth_method_type` VALUES (1,'LAMS'),(2,'WEB_AUTH'),(3,'LDAP'); +UNLOCK TABLES; -INSERT INTO lams_organisation_type VALUES(1, 'ROOT ORGANISATION', 'root all other organisations: controlled by Sysadmin'); -INSERT INTO lams_organisation_type VALUES(2, 'COURSE ORGANISATION', 'main organisation level - equivalent to an entire course.'); -INSERT INTO lams_organisation_type VALUES(3, 'CLASS', 'runtime organisation level - lessons are run for classes.'); +-- +-- Dumping data for table `lams_authentication_method` +-- -INSERT INTO lams_organisation_state VALUES (1, 'ACTIVE'); -INSERT INTO lams_organisation_state VALUES (2, 'HIDDEN'); -INSERT INTO lams_organisation_state VALUES (3, 'ARCHIVED'); -INSERT INTO lams_organisation_state VALUES (4, 'REMOVED'); +LOCK TABLES `lams_authentication_method` WRITE; +INSERT INTO `lams_authentication_method` VALUES (1,1,'LAMS-Database'),(3,3,'MQ-LDAP'); +UNLOCK TABLES; -INSERT INTO lams_grouping_type VALUES (1, 'RANDOM_GROUPING'); -INSERT INTO lams_grouping_type VALUES (2, 'CHOSEN_GROUPING'); -INSERT INTO lams_grouping_type VALUES (3, 'CLASS_GROUPING'); -INSERT INTO lams_grouping_type VALUES (4, 'LEARNER_CHOICE_GROUPING'); +-- +-- Dumping data for table `lams_copy_type` +-- -INSERT INTO lams_tool_session_type VALUES (1, 'NON_GROUPED'); -INSERT INTO lams_tool_session_type VALUES (2, 'GROUPED'); +LOCK TABLES `lams_copy_type` WRITE; +INSERT INTO `lams_copy_type` VALUES (1,'NONE'),(2,'LESSON'),(3,'PREVIEW'); +UNLOCK TABLES; -INSERT INTO lams_learning_activity_type VALUES (1, 'TOOL'); -INSERT INTO lams_learning_activity_type VALUES (2, 'GROUPING'); -INSERT INTO lams_learning_activity_type VALUES (3, 'GATE_SYNCH'); -INSERT INTO lams_learning_activity_type VALUES (4, 'GATE_SCHEDULE'); -INSERT INTO lams_learning_activity_type VALUES (5, 'GATE_PERMISSION'); -INSERT INTO lams_learning_activity_type VALUES (6, 'PARALLEL'); -INSERT INTO lams_learning_activity_type VALUES (7, 'OPTIONS'); -INSERT INTO lams_learning_activity_type VALUES (8, 'SEQUENCE'); -INSERT INTO lams_learning_activity_type VALUES (9, 'GATE_SYSTEM'); -INSERT INTO lams_learning_activity_type VALUES (10, 'BRANCHING_CHOSEN'); -INSERT INTO lams_learning_activity_type VALUES (11, 'BRANCHING_GROUP'); -INSERT INTO lams_learning_activity_type VALUES (12, 'BRANCHING_TOOL'); -INSERT INTO lams_learning_activity_type VALUES (13, 'OPTIONS_WITH_SEQUENCES'); -INSERT INTO lams_learning_activity_type VALUES (14, 'GATE_CONDITION'); -INSERT INTO lams_learning_activity_type VALUES (15, 'FLOATING'); +-- +-- Dumping data for table `lams_ext_server_org_map` +-- -INSERT INTO lams_gate_activity_level VALUES (1, 'LEARNER'); -INSERT INTO lams_gate_activity_level VALUES (2, 'GROUP'); -INSERT INTO lams_gate_activity_level VALUES (3, 'CLASS'); +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); +UNLOCK TABLES; -INSERT INTO lams_tool_session_state VALUES (1, 'STARTED'); -INSERT INTO lams_tool_session_state VALUES (2, 'ENDED'); +-- +-- Dumping data for table `lams_ext_server_type` +-- -INSERT INTO lams_lesson_state VALUES (1, 'CREATED'); -INSERT INTO lams_lesson_state VALUES (2, 'NOT_STARTED'); -INSERT INTO lams_lesson_state VALUES (3, 'STARTED'); -INSERT INTO lams_lesson_state VALUES (4, 'SUSPENDED'); -INSERT INTO lams_lesson_state VALUES (5, 'FINISHED'); -INSERT INTO lams_lesson_state VALUES (6, 'ARCHIVED'); -INSERT INTO lams_lesson_state VALUES (7, 'REMOVED'); +LOCK TABLES `lams_ext_server_type` WRITE; +INSERT INTO `lams_ext_server_type` VALUES (1,'INTEGRATED SERVER'),(2,'LTI TOOL CONSUMER'); +UNLOCK TABLES; -INSERT into lams_license VALUES (1, 'LAMS Recommended: CC Attribution-Noncommercial-ShareAlike 2.5', 'by-nc-sa', 'http://creativecommons.org/licenses/by-nc-sa/2.5/', 1, '/images/license/byncsa.jpg'); -INSERT into lams_license VALUES (2, 'CC Attribution-No Derivatives 2.5', 'by-nd', 'http://creativecommons.org/licenses/by-nd/2.5/',0,'/images/license/bynd.jpg'); -INSERT into lams_license VALUES (3, 'CC Attribution-Noncommercial-No Derivatives 2.5', 'by-nc-nd', 'http://creativecommons.org/licenses/by-nc-nd/2.5/',0, '/images/license/byncnd.jpg'); -INSERT into lams_license VALUES (4, 'CC Attribution-Noncommercial 2.5', 'by-nc', 'http://creativecommons.org/licenses/by-nc/2.5/',0,'/images/license/bync.jpg'); -INSERT into lams_license VALUES (5, 'CC Attribution-ShareAlike 2.5', 'by-sa', 'http://creativecommons.org/licenses/by-sa/2.5/',0,'/images/license/byncsa.jpg'); -INSERT into lams_license VALUES (6, 'Other Licensing Agreement', 'other', '',0, ''); +-- +-- Dumping data for table `lams_gate_activity_level` +-- -INSERT into lams_copy_type VALUES(1,'NONE'); -INSERT into lams_copy_type VALUES(2,'LESSON'); -INSERT into lams_copy_type VALUES(3,'PREVIEW'); +LOCK TABLES `lams_gate_activity_level` WRITE; +INSERT INTO `lams_gate_activity_level` VALUES (1,'LEARNER'),(2,'GROUP'),(3,'CLASS'); +UNLOCK TABLES; -INSERT into lams_workspace_folder_type VALUES (1, 'NORMAL'); -INSERT into lams_workspace_folder_type VALUES (2, 'RUN SEQUENCES'); -INSERT into lams_workspace_folder_type VALUES (3, 'PUBLIC SEQUENCES'); +-- +-- Dumping data for table `lams_grouping_support_type` +-- -INSERT INTO lams_authentication_method VALUES (1, 1, 'LAMS-Database'); -INSERT INTO lams_authentication_method VALUES (3, 3, 'MQ-LDAP'); +LOCK TABLES `lams_grouping_support_type` WRITE; +INSERT INTO `lams_grouping_support_type` VALUES (1,'NONE'),(2,'OPTIONAL'),(3,'REQUIRED'); +UNLOCK TABLES; -INSERT INTO lams_activity_category VALUES (1 ,'SYSTEM'); -INSERT INTO lams_activity_category VALUES (2 ,'COLLABORATION'); -INSERT INTO lams_activity_category VALUES (3 ,'ASSESSMENT'); -INSERT INTO lams_activity_category VALUES (4 ,'CONTENT'); -INSERT INTO lams_activity_category VALUES (5 ,'SPLIT'); -INSERT INTO lams_activity_category VALUES (6 ,'RESPONSE'); +-- +-- Dumping data for table `lams_grouping_type` +-- -INSERT INTO lams_grouping_support_type VALUES (1 ,'NONE'); -INSERT INTO lams_grouping_support_type VALUES (2 ,'OPTIONAL'); -INSERT INTO lams_grouping_support_type VALUES (3 ,'REQUIRED'); +LOCK TABLES `lams_grouping_type` WRITE; +INSERT INTO `lams_grouping_type` VALUES (1,'RANDOM_GROUPING'),(2,'CHOSEN_GROUPING'),(3,'CLASS_GROUPING'),(4,'LEARNER_CHOICE_GROUPING'); +UNLOCK TABLES; -INSERT INTO lams_log_event_type VALUES (1, 'TYPE_TEACHER_LEARNING_DESIGN_CREATE'); -INSERT INTO lams_log_event_type VALUES (2, 'TYPE_TEACHER_LESSON_CREATE'); -INSERT INTO lams_log_event_type VALUES (3, 'TYPE_TEACHER_LESSON_START'); -INSERT INTO lams_log_event_type VALUES (4, 'TYPE_TEACHER_LESSON_CHANGE_STATE'); -INSERT INTO lams_log_event_type VALUES (5, 'TYPE_LEARNER_ACTIVITY_START'); -INSERT INTO lams_log_event_type VALUES (6, 'TYPE_LEARNER_ACTIVITY_FINISH'); +-- +-- Dumping data for table `lams_learning_activity_type` +-- -INSERT INTO lams_system_tool (system_tool_id, learning_activity_type_id, tool_display_name, description, - learner_url, learner_preview_url, learner_progress_url, monitor_url, contribute_url, create_date_time,pedagogical_planner_url) -VALUES (1, 2, 'Grouping', 'All types of grouping including random and chosen.', - 'learning/grouping.do?method=performGrouping', 'learning/grouping.do?method=performGrouping', - 'learning/grouping.do?method=viewGrouping&mode=teacher', - 'monitoring/grouping.do?method=startGrouping', - 'monitoring/grouping.do?method=startGrouping', now(), - 'pedagogicalPlanner/grouping.do?method=initGrouping'); +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'); +UNLOCK TABLES; -INSERT INTO lams_system_tool (system_tool_id, learning_activity_type_id, tool_display_name, description, - learner_url, learner_preview_url, learner_progress_url, monitor_url, contribute_url, create_date_time) -VALUES (2, 3, 'Sync Gate', 'Gate: Synchronise Learners.', - 'learning/gate.do?method=knockGate', 'learning/gate.do?method=knockGate', null, - 'monitoring/gate.do?method=viewGate', - 'monitoring/gate.do?method=viewGate', now() ); +-- +-- Dumping data for table `lams_lesson_state` +-- -INSERT INTO lams_system_tool (system_tool_id, learning_activity_type_id, tool_display_name, description, - learner_url, learner_preview_url, learner_progress_url, monitor_url, contribute_url, create_date_time) -VALUES (3, 4, 'Schedule Gate', 'Gate: Opens/shuts at particular times.', - 'learning/gate.do?method=knockGate', 'learning/gate.do?method=knockGate', null, - 'monitoring/gate.do?method=viewGate', - 'monitoring/gate.do?method=viewGate', now() ); +LOCK TABLES `lams_lesson_state` WRITE; +INSERT INTO `lams_lesson_state` VALUES (1,'CREATED'),(2,'NOT_STARTED'),(3,'STARTED'),(4,'SUSPENDED'),(5,'FINISHED'),(6,'ARCHIVED'),(7,'REMOVED'); +UNLOCK TABLES; -INSERT INTO lams_system_tool (system_tool_id, learning_activity_type_id, tool_display_name, description, - learner_url, learner_preview_url, learner_progress_url, monitor_url, contribute_url, create_date_time) -VALUES (4, 5, 'Permission Gate', 'Gate: Opens under teacher or staff control.', - 'learning/gate.do?method=knockGate', 'learning/gate.do?method=knockGate', null, - 'monitoring/gate.do?method=viewGate', - 'monitoring/gate.do?method=viewGate', now() ); +-- +-- Dumping data for table `lams_license` +-- -INSERT INTO lams_system_tool (system_tool_id, learning_activity_type_id, tool_display_name, description, - learner_url, learner_preview_url, learner_progress_url, monitor_url, contribute_url, create_date_time) -VALUES (5, 9, 'System Gate', 'Gate: Opens under system control.', - 'learning/gate.do?method=knockGate', 'learning/gate.do?method=knockGate', null, - 'monitoring/gate.do?method=viewGate', - 'monitoring/gate.do?method=viewGate', now() ); - -INSERT INTO lams_system_tool (system_tool_id, learning_activity_type_id, tool_display_name, description, - learner_url, learner_preview_url, learner_progress_url, monitor_url, contribute_url, create_date_time) -VALUES (6, 10, 'Monitor Chosen Branching', 'Select between multiple sequence activities, with the branch chosen in monitoring.', - 'learning/branching.do?method=performBranching', 'learning/branching.do?method=performBranching', - 'monitoring/complexProgress.do', 'monitoring/chosenBranching.do?method=assignBranch', - 'monitoring/grouping.do?method=startGrouping', now()); +LOCK TABLES `lams_license` WRITE; +INSERT INTO `lams_license` VALUES (1,'LAMS Recommended: CC Attribution-Noncommercial-ShareAlike 4.0','by-nc-sa','https://creativecommons.org/licenses/by-nc-sa/4.0/',1,'/images/license/by-nc-sa.eu.svg',1),(2,'CC Attribution-No Derivatives 4.0','by-nd','https://creativecommons.org/licenses/by-nd/4.0/',0,'/images/license/by-nd.svg',2),(3,'CC Attribution-Noncommercial-No Derivatives 4.0','by-nc-nd','https://creativecommons.org/licenses/by-nc-nd/4.0/',0,'/images/license/by-nc-nd.svg',3),(4,'CC Attribution-Noncommercial 4.0','by-nc','https://creativecommons.org/licenses/by-nc/4.0/',0,'/images/license/by-nc.eu.svg',4),(5,'CC Attribution-ShareAlike 4.0','by-sa','https://creativecommons.org/licenses/by-sa/4.0/',0,'/images/license/by-sa.svg',5),(6,'Other Licensing Agreement','other','',0,'',8),(7,'CC Attribution 4.0','by','https://creativecommons.org/licenses/by/4.0/',0,'/images/license/by.svg',6),(8,'Public Domain','CC0','https://creativecommons.org/publicdomain/zero/1.0/',0,'/images/license/publicdomain.svg',7); +UNLOCK TABLES; -INSERT INTO lams_system_tool (system_tool_id, learning_activity_type_id, tool_display_name, description, - learner_url, learner_preview_url, learner_progress_url, monitor_url, contribute_url, create_date_time) -VALUES (7, 11, 'Group Based Branching', 'Select between multiple sequence activities, with the branch chosen by an existing group.', - 'learning/branching.do?method=performBranching', 'learning/branching.do?method=performBranching', - 'monitoring/complexProgress.do', 'monitoring/groupedBranching.do?method=viewBranching', - 'monitoring/groupedBranching.do?method=assignBranch', now()); +-- +-- Dumping data for table `lams_log_event_type` +-- -INSERT INTO lams_system_tool (system_tool_id, learning_activity_type_id, tool_display_name, description, - learner_url, learner_preview_url, learner_progress_url, monitor_url, contribute_url, create_date_time) -VALUES (8, 12, 'Tool Output Based Branching', 'Select between multiple sequence activities, with the branch chosen on results of another activity.', - 'learning/branching.do?method=performBranching', 'learning/branching.do?method=performBranching', - 'monitoring/complexProgress.do', 'monitoring/toolBranching.do?method=viewBranching', - 'monitoring/toolBranching.do?method=viewBranching', now()); +LOCK TABLES `lams_log_event_type` WRITE; +INSERT INTO `lams_log_event_type` VALUES (1,'TYPE_TEACHER_LEARNING_DESIGN_CREATE','LESSON'),(2,'TYPE_TEACHER_LESSON_CREATE','LESSON'),(3,'TYPE_TEACHER_LESSON_START','LESSON'),(4,'TYPE_TEACHER_LESSON_CHANGE_STATE','LESSON'),(5,'TYPE_LEARNER_ACTIVITY_START','LESSON'),(6,'TYPE_LEARNER_ACTIVITY_FINISH','LESSON'),(7,'TYPE_LEARNER_LESSON_COMPLETE','LESSON'),(8,'TYPE_LEARNER_LESSON_MARK_SUBMIT','LESSON'),(9,'TYPE_ACTIVITY_EDIT','LESSON'),(10,'TYPE_FORCE_COMPLETE','LESSON'),(11,'TYPE_USER_ORG_ADMIN','SECURITY'),(12,'TYPE_LOGIN_AS','SECURITY'),(13,'TYPE_PASSWORD_CHANGE','SECURITY'),(14,'TYPE_ROLE_FAILURE','SECURITY'),(15,'TYPE_ACCOUNT_LOCKED','SECURITY'),(16,'TYPE_NOTIFICATION','NOTIFICATION'),(17,'TYPE_MARK_UPDATED','MARKS'),(18,'TYPE_MARK_RELEASED','MARKS'),(19,'TYPE_LEARNER_CONTENT_UPDATED','LEARNER_CONTENT'),(20,'TYPE_LEARNER_CONTENT_SHOW_HIDE','LEARNER_CONTENT'),(21,'TYPE_UNKNOWN','UNKNOWN'),(22,'TYPE_LIVE_EDIT','LESSON'),(23,'TYPE_TOOL_MARK_RELEASED','MARKS'); +UNLOCK TABLES; -INSERT INTO lams_system_tool (system_tool_id, learning_activity_type_id, tool_display_name, description, - learner_url, learner_preview_url, learner_progress_url, monitor_url, contribute_url, create_date_time) -VALUES (9, 8, 'Sequence', 'A sequence of activities', - 'learning/SequenceActivity.do', 'learning/SequenceActivity.do', - 'monitoring/complexProgress.do', 'monitoring/sequence.do?method=viewSequence', - 'monitoring/sequence.do?method=viewSequence', now()); -INSERT INTO lams_system_tool (system_tool_id, learning_activity_type_id, tool_display_name, description, - learner_url, learner_preview_url, learner_progress_url, monitor_url, contribute_url, create_date_time) -VALUES (10, 14, 'Condition Gate', 'Gate: Opens if conditions are met', - 'learning/gate.do?method=knockGate', 'learning/gate.do?method=knockGate', null, - 'monitoring/gate.do?method=viewGate', - 'monitoring/gate.do?method=viewGate', now() ); +-- +-- Dumping data for table `lams_organisation_state` +-- -INSERT INTO lams_system_tool (system_tool_id, learning_activity_type_id, tool_display_name, description, - learner_url, learner_preview_url, learner_progress_url, monitor_url, contribute_url, create_date_time) -VALUES (11, 15, 'Floating Activities', 'A collection of floating activities', - NULL, NULL, null, 'monitoring/floating.do?method=viewFloating', - 'monitoring/floating.do?method=viewFloating', now() ); +LOCK TABLES `lams_organisation_state` WRITE; +INSERT INTO `lams_organisation_state` VALUES (1,'ACTIVE'),(2,'HIDDEN'),(3,'ARCHIVED'),(4,'REMOVED'); +UNLOCK TABLES; --- Supported Locales -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (1, 'en', 'AU', 'English (Australia)', 'LTR', 'en-au'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (2, 'es', 'ES', 'Español', 'LTR', 'es'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (3, 'mi', 'NZ', 'Māori', 'LTR', 'en-au'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (4, 'de', 'DE', 'Deutsch', 'LTR', 'de'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (5, 'zh', 'CN', '简体中文', 'LTR', 'zh-cn'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (6, 'fr', 'FR', 'Français', 'LTR', 'fr'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (7, 'it', 'IT', 'Italiano', 'LTR', 'it'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (8, 'no', 'NO', 'Norsk', 'LTR', 'no'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (9, 'sv', 'SE', 'Svenska', 'LTR', 'sv'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (10, 'ko', 'KR', '한국어', 'LTR', 'ko'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (11, 'pl', 'PL', 'Polski', 'LTR', 'pl'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (12, 'pt', 'BR', 'Português (Brasil)', 'LTR', 'pt-br'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (13, 'hu', 'HU', 'Magyar', 'LTR', 'hu'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (14, 'bg', 'BG', 'Български', 'LTR', 'bg'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (15, 'cy', 'GB', 'Cymraeg (Cymru)', 'LTR', 'en-au'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (16, 'th', 'TH', 'Thai', 'LTR', 'th'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (17, 'el', 'GR', 'Ελληνικά', 'LTR', 'el'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (18, 'nl', 'BE', 'Nederlands (België)', 'LTR', 'nl'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (19, 'ar', 'JO', 'عربي', 'RTL', 'ar'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (20, 'da', 'DK', 'Dansk', 'LTR', 'da'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (21, 'ru', 'RU', 'Русский', 'LTR', 'ru'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (22, 'vi', 'VN', 'Tiếng Việt', 'LTR', 'vi'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (23, 'zh', 'TW', 'Chinese (Taiwan)', 'LTR', 'zh'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (24, 'ja', 'JP', '日本語', 'LTR', 'ja'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (25, 'ms', 'MY', 'Malay (Malaysia)', 'LTR', 'ms'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (26, 'tr', 'TR', 'Türkçe', 'LTR', 'tr'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (27, 'ca', 'ES', 'Català', 'LTR', 'ca'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (28, 'sl', 'SI', 'Slovenščina', 'LTR', 'sl'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (29, 'es', 'MX', 'Español (México)', 'LTR', 'es'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (30, 'cs', 'CZ', 'Čeština', 'LTR', 'cs'); -INSERT INTO lams_supported_locale (locale_id, language_iso_code, country_iso_code, description, direction, fckeditor_code) -VALUES (31, 'id', 'ID', 'Indonesian', 'LTR', 'en-au'); +-- +-- Dumping data for table `lams_organisation_type` +-- +LOCK TABLES `lams_organisation_type` WRITE; +INSERT INTO `lams_organisation_type` VALUES (1,'ROOT ORGANISATION','root all other organisations: controlled by Sysadmin'),(2,'COURSE ORGANISATION','main organisation level - equivalent to an entire course.'),(3,'CLASS','runtime organisation level - lessons are run for classes.'); +UNLOCK TABLES; --- Default TimeZones -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT+14'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT+13'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT+12'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT+11'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT+10'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT+9'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT+8'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT+7'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT+6'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT+5'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT+4'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT+3'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT+2'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT+1'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT-1'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT-2'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT-3'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT-4'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT-5'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT-6'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT-7'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT-8'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT-9'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT-10'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT-11'); -INSERT INTO lams_timezone (timezone_id) VALUES ('Etc/GMT-12'); -INSERT INTO lams_ext_server_type VALUES (1, 'INTEGRATED SERVER'); -INSERT INTO lams_ext_server_type VALUES (2, 'LTI TOOL CONSUMER'); +-- +-- Dumping data for table `lams_outcome_scale` +-- --- external server mapping to a lams organisation -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%', '', - 'http://dummy', '', true,80,0,NULL,1,NULL); +LOCK TABLES `lams_outcome_scale` WRITE; +INSERT INTO `lams_outcome_scale` VALUES (1,NULL,'Default attainment scale','default','Default global scale',NULL,1,NOW()); +UNLOCK TABLES; --- LDEV-3450 Implement peer review feature -INSERT INTO lams_rating_criteria_type VALUES (1, 'TOOL_ACTIVITY'); -INSERT INTO lams_rating_criteria_type VALUES (2, 'AUTHORED_ITEM'); -INSERT INTO lams_rating_criteria_type VALUES (3, 'LEARNER_ITEM'); -INSERT INTO lams_rating_criteria_type VALUES (4, 'LESSON'); - --- initialise db version -INSERT INTO patches VALUES ('lams', 20170101, NOW(), 'F'); \ No newline at end of file +-- +-- Dumping data for table `lams_outcome_scale_item` +-- + +LOCK TABLES `lams_outcome_scale_item` WRITE; +INSERT INTO `lams_outcome_scale_item` VALUES (1,1,0,'Not yet attained'),(2,1,1,'Attained'); +UNLOCK TABLES; + +-- +-- Dumping data for table `lams_policy_state` +-- + +LOCK TABLES `lams_policy_state` WRITE; +INSERT INTO `lams_policy_state` VALUES (1,'ACTIVE'),(2,'INACTIVE'); +UNLOCK TABLES; + +-- +-- Dumping data for table `lams_policy_type` +-- + +LOCK TABLES `lams_policy_type` WRITE; +INSERT INTO `lams_policy_type` VALUES (1,'TYPE_SITE_POLICY'),(2,'TYPE_PRIVACY_POLICY'),(3,'TYPE_THIRD_PARTIES_POLICY'),(4,'TYPE_OTHER'); +UNLOCK TABLES; + + +-- +-- Dumping data for table `lams_rating_criteria_type` +-- + +LOCK TABLES `lams_rating_criteria_type` WRITE; +INSERT INTO `lams_rating_criteria_type` VALUES (1,'TOOL_ACTIVITY'),(2,'AUTHORED_ITEM'),(3,'LEARNER_ITEM'),(4,'LESSON'); +UNLOCK TABLES; + + +-- +-- Dumping data for table `lams_role` +-- + +LOCK TABLES `lams_role` WRITE; +INSERT INTO `lams_role` VALUES (1,'SYSADMIN','LAMS System Adminstrator',NOW()),(2,'GROUP MANAGER','Group Manager',NOW()),(3,'AUTHOR','Authors Learning Designs',NOW()),(4,'MONITOR','Member of Staff',NOW()),(5,'LEARNER','Student',NOW()); +UNLOCK TABLES; + +-- +-- Dumping data for table `lams_supported_locale` +-- + +LOCK TABLES `lams_supported_locale` WRITE; +INSERT INTO `lams_supported_locale` VALUES (1,'en','AU','English (Australia)','LTR','en-au'),(2,'es','ES','Español','LTR','es'),(3,'mi','NZ','Māori','LTR','en-au'),(4,'de','DE','Deutsch','LTR','de'),(5,'zh','CN','简体中文','LTR','zh-cn'),(6,'fr','FR','Français','LTR','fr'),(7,'it','IT','Italiano','LTR','it'),(8,'no','NO','Norsk','LTR','no'),(9,'sv','SE','Svenska','LTR','sv'),(10,'ko','KR','한국어','LTR','ko'),(11,'pl','PL','Polski','LTR','pl'),(12,'pt','BR','Português (Brasil)','LTR','pt-br'),(13,'hu','HU','Magyar','LTR','hu'),(14,'bg','BG','Български','LTR','bg'),(15,'cy','GB','Cymraeg (Cymru)','LTR','cy'),(16,'th','TH','Thai','LTR','th'),(17,'el','GR','Ελληνικά','LTR','el'),(18,'nl','BE','Nederlands (België)','LTR','nl'),(19,'ar','JO','عربي','RTL','ar'),(20,'da','DK','Dansk','LTR','da'),(21,'ru','RU','Русский','LTR','ru'),(22,'vi','VN','Tiếng Việt','LTR','vi'),(23,'zh','TW','Chinese (Taiwan)','LTR','zh'),(24,'ja','JP','日本語','LTR','ja'),(25,'ms','MY','Malay (Malaysia)','LTR','ms'),(26,'tr','TR','Türkçe','LTR','tr'),(27,'ca','ES','Català','LTR','ca'),(28,'sl','SI','Slovenščina','LTR','sl'),(29,'es','MX','Español (México)','LTR','es'),(30,'cs','CZ','Čeština','LTR','cs'),(31,'id','ID','Indonesian','LTR','id'); +UNLOCK TABLES; + +-- +-- Dumping data for table `lams_system_tool` +-- + +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); +UNLOCK TABLES; + + +-- +-- Dumping data for table `lams_timezone` +-- + +LOCK TABLES `lams_timezone` WRITE; +INSERT INTO `lams_timezone` VALUES (1,'Etc/GMT+14',0),(2,'Etc/GMT+13',0),(3,'Etc/GMT+12',0),(4,'Etc/GMT+11',0),(5,'Etc/GMT+10',0),(6,'Etc/GMT+9',0),(7,'Etc/GMT+8',0),(8,'Etc/GMT+7',0),(9,'Etc/GMT+6',0),(10,'Etc/GMT+5',0),(11,'Etc/GMT+4',0),(12,'Etc/GMT+3',0),(13,'Etc/GMT+2',0),(14,'Etc/GMT+1',0),(15,'Etc/GMT',0),(16,'Etc/GMT-1',0),(17,'Etc/GMT-2',0),(18,'Etc/GMT-3',0),(19,'Etc/GMT-4',0),(20,'Etc/GMT-5',0),(21,'Etc/GMT-6',0),(22,'Etc/GMT-7',0),(23,'Etc/GMT-8',0),(24,'Etc/GMT-9',0),(25,'Etc/GMT-10',0),(26,'Etc/GMT-11',0),(27,'Etc/GMT-12',0); +UNLOCK TABLES; + +-- +-- Dumping data for table `lams_tool_session_state` +-- + +LOCK TABLES `lams_tool_session_state` WRITE; +INSERT INTO `lams_tool_session_state` VALUES (1,'STARTED'),(2,'ENDED'); +UNLOCK TABLES; + +-- +-- Dumping data for table `lams_tool_session_type` +-- + +LOCK TABLES `lams_tool_session_type` WRITE; +INSERT INTO `lams_tool_session_type` VALUES (1,'NON_GROUPED'),(2,'GROUPED'); +UNLOCK TABLES; + + + +-- +-- Dumping data for table `lams_workspace_folder_type` +-- + +LOCK TABLES `lams_workspace_folder_type` WRITE; +INSERT INTO `lams_workspace_folder_type` VALUES (1,'NORMAL'),(2,'RUN SEQUENCES'),(3,'PUBLIC SEQUENCES'); +UNLOCK TABLES; + +-- +-- Dumping data for table `patches` +-- + +LOCK TABLES `patches` WRITE; +INSERT INTO patches VALUES ('lams', 20190103, NOW(), 'F'); +UNLOCK TABLES; + +SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file