Index: lams_common/db/sql/old/add_workspace_tables.sql =================================================================== diff -u --- lams_common/db/sql/old/add_workspace_tables.sql (revision 0) +++ lams_common/db/sql/old/add_workspace_tables.sql (revision 84be344bfb89ab3182fc4abda27266eddd7662fe) @@ -0,0 +1,31 @@ +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2004-11-09 16:13:07 +# +CREATE TABLE lams_workspace_folder ( + workspace_folder_id INT(11) NOT NULL + , parent_folder_id INT(11) + , name VARCHAR(64) NOT NULL + , workspace_id INT(11) NOT NULL + , PRIMARY KEY (workspace_folder_id) + , INDEX (parent_folder_id) + , CONSTRAINT FK_lams_workspace_folder_2 FOREIGN KEY (parent_folder_id) + REFERENCES lams_workspace_folder (workspace_folder_id) +)TYPE=InnoDB; + +CREATE TABLE lams_workspace ( + workspace_id INT(11) NOT NULL + , root_folder_id INT(11) NOT NULL + , PRIMARY KEY (workspace_id) + , INDEX (root_folder_id) + , CONSTRAINT FK_lams_workspace_1 FOREIGN KEY (root_folder_id) + REFERENCES lams_workspace_folder (workspace_folder_id) +)TYPE=InnoDB; + +ALTER TABLE lams_user ADD workspace_id INT(11); +ALTER TABLE lams_user ADD INDEX idx_lams_user_workspace_id (workspace_id); +ALTER TABLE lams_user ADD FOREIGN KEY (workspace_id) REFERENCES lams_workspace (workspace_id); + +ALTER TABLE lams_organisation ADD workspace_id INT(11); +ALTER TABLE lams_organisation ADD INDEX idx_lams_organisation_workspace_id (workspace_id); +ALTER TABLE lams_organisation ADD FOREIGN KEY (workspace_id) REFERENCES lams_workspace (workspace_id); \ No newline at end of file Index: lams_common/db/sql/old/create_lams_11_tables.sql =================================================================== diff -u --- lams_common/db/sql/old/create_lams_11_tables.sql (revision 0) +++ lams_common/db/sql/old/create_lams_11_tables.sql (revision 84be344bfb89ab3182fc4abda27266eddd7662fe) @@ -0,0 +1,462 @@ +CREATE TABLE lams_gate_activity_level ( + gate_activity_level_id INT(11) NOT NULL DEFAULT 0 + , description VARCHAR(128) NOT NULL + , PRIMARY KEY (gate_activity_level_id) +)TYPE=InnoDB; + +CREATE TABLE lams_grouping_type ( + grouping_type_id INT(11) NOT NULL DEFAULT 0 + , description VARCHAR(128) NOT NULL + , PRIMARY KEY (grouping_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_learning_activity_type ( + learning_activity_type_id INT(11) NOT NULL DEFAULT 0 + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (learning_activity_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_learning_library ( + learning_library_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , description TEXT + , title VARCHAR(255) + , create_date_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' + , PRIMARY KEY (learning_library_id) +)TYPE=InnoDB; + +CREATE TABLE lams_organisation_type ( + organisation_type_id INT(3) NOT NULL DEFAULT 0 + , name VARCHAR(64) NOT NULL + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (organisation_type_id) +)TYPE=InnoDB; +CREATE UNIQUE INDEX UQ_lams_organisation_type_name ON lams_organisation_type (name ASC); + +CREATE TABLE lams_role ( + role_id INT(6) NOT NULL DEFAULT 0 + , name VARCHAR(64) NOT NULL + , description TEXT + , create_date BIGINT(20) + , PRIMARY KEY (role_id) +)TYPE=InnoDB; +CREATE INDEX gname ON lams_role (name ASC); + +CREATE TABLE lams_tool ( + tool_id BIGINT(20) NOT NULL AUTO_INCREMENT + , tool_signature VARCHAR(64) NOT NULL + , class_name VARCHAR(255) NOT NULL + , tool_display_name VARCHAR(255) NOT NULL + , description TEXT + , default_tool_content_id BIGINT(20) NOT NULL + , supports_grouping_flag TINYINT(1) NOT NULL DEFAULT 0 + , supports_define_later_flag TINYINT(1) NOT NULL DEFAULT 0 + , learner_url TEXT NOT NULL + , author_url TEXT NOT NULL + , define_later_url TEXT + , export_portfolio_url TEXT NOT NULL + , monitor_url TEXT NOT NULL + , UNIQUE UQ_lams_tool_sig (tool_signature) + , UNIQUE UQ_lams_tool_class_name (class_name) + , PRIMARY KEY (tool_id) +)TYPE=InnoDB; + +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) +)TYPE=InnoDB; + +CREATE TABLE lams_user_tool_session_state ( + user_tool_session_state_id INT(3) NOT NULL + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (user_tool_session_state_id) +)TYPE=InnoDB; + +CREATE TABLE lams_lesson_state ( + lams_lesson_state_id INT(3) NOT NULL + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (lams_lesson_state_id) +)TYPE=InnoDB; + +CREATE TABLE lams_authentication_method_type ( + authentication_method_type_id INT(3) NOT NULL DEFAULT 0 + , description VARCHAR(64) NOT NULL + , PRIMARY KEY (authentication_method_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_authentication_method ( + authentication_method_id BIGINT(20) NOT NULL DEFAULT 0 + , authentication_method_type_id INT(3) NOT NULL DEFAULT 0 + , parameters_file_name VARCHAR(255) NOT NULL + , PRIMARY KEY (authentication_method_id) + , INDEX (authentication_method_type_id) + , CONSTRAINT FK_lams_authorization_method_1 FOREIGN KEY (authentication_method_type_id) + REFERENCES lams_authentication_method_type (authentication_method_type_id) ON DELETE NO ACTION ON UPDATE NO ACTION +)TYPE=InnoDB; + +CREATE TABLE lams_workspace_folder ( + workspace_folder_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , parent_folder_id BIGINT(20) + , name VARCHAR(64) NOT NULL + , workspace_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (workspace_folder_id) + , INDEX (parent_folder_id) + , 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 +)TYPE=InnoDB; + +CREATE TABLE lams_workspace ( + workspace_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , root_folder_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (workspace_id) + , INDEX (root_folder_id) + , CONSTRAINT FK_lams_workspace_1 FOREIGN KEY (root_folder_id) + REFERENCES lams_workspace_folder (workspace_folder_id) ON DELETE NO ACTION ON UPDATE NO ACTION +)TYPE=InnoDB; + +CREATE TABLE lams_grouping ( + grouping_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , grouping_type_id INT(11) NOT NULL DEFAULT 0 + , number_of_groups INT(11) + , learners_per_group INT(11) + , staff_group_id BIGINT(20) DEFAULT 0 + , max_number_of_groups INT(3) + , PRIMARY KEY (grouping_id) + , INDEX (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 +)TYPE=InnoDB; + +CREATE TABLE lams_organisation ( + organisation_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , name VARCHAR(250) + , description VARCHAR(250) + , parent_organisation_id BIGINT(20) + , organisation_type_id INT(3) NOT NULL DEFAULT 0 + , create_date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' + , workspace_id BIGINT(20) + , PRIMARY KEY (organisation_id) + , INDEX (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 + , INDEX (workspace_id) + , CONSTRAINT FK_lams_organisation_2 FOREIGN KEY (workspace_id) + REFERENCES lams_workspace (workspace_id) ON DELETE NO ACTION ON UPDATE NO ACTION + , INDEX (parent_organisation_id) + , CONSTRAINT FK_lams_organisation_3 FOREIGN KEY (parent_organisation_id) + REFERENCES lams_organisation (organisation_id) ON DELETE NO ACTION ON UPDATE NO ACTION +)TYPE=InnoDB; + +CREATE TABLE lams_user ( + user_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , login VARCHAR(20) NOT NULL + , password VARCHAR(50) NOT NULL + , title VARCHAR(32) + , first_name VARCHAR(64) + , 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) + , 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 DEFAULT '0000-00-00 00:00:00' + , authentication_method_id BIGINT(20) NOT NULL DEFAULT 0 + , workspace_id BIGINT(20) + , user_organisation_id BIGINT(20) NOT NULL DEFAULT 0 + , base_organisation_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (user_id) + , INDEX (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 + , INDEX (workspace_id) + , CONSTRAINT FK_lams_user_2 FOREIGN KEY (workspace_id) + REFERENCES lams_workspace (workspace_id) ON DELETE NO ACTION ON UPDATE NO ACTION + , INDEX (base_organisation_id) + , CONSTRAINT FK_lams_user_3 FOREIGN KEY (base_organisation_id) + REFERENCES lams_organisation (organisation_id) +)TYPE=InnoDB; +CREATE UNIQUE INDEX UQ_lams_user_login ON lams_user (login ASC); +CREATE INDEX login ON lams_user (login ASC); + +CREATE TABLE lams_learning_design ( + learning_design_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , id INT(11) + , description TEXT + , title VARCHAR(255) + , first_activity_id BIGINT(20) + , max_id INT(11) + , valid_design_flag TINYINT(4) NOT NULL + , read_only_flag TINYINT(4) NOT NULL + , date_read_only DATETIME + , read_access BIGINT(20) + , write_access BIGINT(20) + , user_id BIGINT(20) NOT NULL + , help_text TEXT + , lesson_copy_flag TINYINT(4) NOT NULL DEFAULT 0 + , create_date_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' + , version VARCHAR(56) NOT NULL + , parent_learning_design_id BIGINT(20) + , open_date_time DATETIME + , close_date_time DATETIME + , workspace_folder_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (learning_design_id) + , INDEX (parent_learning_design_id) + , CONSTRAINT FK_lams_learning_design_2 FOREIGN KEY (parent_learning_design_id) + REFERENCES lams_learning_design (learning_design_id) ON DELETE NO ACTION ON UPDATE NO ACTION + , INDEX (user_id) + , CONSTRAINT FK_lams_learning_design_3 FOREIGN KEY (user_id) + REFERENCES lams_user (user_id) + , INDEX (workspace_folder_id) + , CONSTRAINT FK_lams_learning_design_4 FOREIGN KEY (workspace_folder_id) + REFERENCES lams_workspace_folder (workspace_folder_id) +)TYPE=InnoDB; +CREATE INDEX idx_design_first_act ON lams_learning_design (first_activity_id ASC); + +CREATE TABLE lams_group ( + group_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , grouping_id BIGINT(20) NOT NULL DEFAULT 0 + , order_id INT(6) NOT NULL DEFAULT 1 + , PRIMARY KEY (group_id) + , INDEX (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 +)TYPE=InnoDB; + +CREATE TABLE lams_user_organisation ( + user_organisation_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , organisation_id BIGINT(20) NOT NULL DEFAULT 0 + , user_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (user_organisation_id) + , INDEX (user_id) + , CONSTRAINT u_user_organisation_ibfk_1 FOREIGN KEY (user_id) + REFERENCES lams_user (user_id) ON DELETE NO ACTION ON UPDATE NO ACTION + , INDEX (organisation_id) + , CONSTRAINT u_user_organisation_ibfk_2 FOREIGN KEY (organisation_id) + REFERENCES lams_organisation (organisation_id) ON DELETE NO ACTION ON UPDATE NO ACTION +)TYPE=InnoDB; + +CREATE TABLE lams_lesson ( + lesson_id BIGINT(20) NOT NULL AUTO_INCREMENT + , learning_design_id BIGINT(20) NOT NULL DEFAULT 0 + , user_id BIGINT(20) NOT NULL DEFAULT 0 + , create_date_time DATETIME NOT NULL + , organisation_id BIGINT(20) NOT NULL DEFAULT 0 + , lams_lesson_state_id INT(3) NOT NULL + , start_date_time DATETIME + , end_date_time DATETIME + , class_grouping_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (lesson_id) + , INDEX (learning_design_id) + , CONSTRAINT FK_lams_lesson_1_1 FOREIGN KEY (learning_design_id) + REFERENCES lams_learning_design (learning_design_id) + , INDEX (user_id) + , CONSTRAINT FK_lams_lesson_2 FOREIGN KEY (user_id) + REFERENCES lams_user (user_id) + , INDEX (organisation_id) + , CONSTRAINT FK_lams_lesson_3 FOREIGN KEY (organisation_id) + REFERENCES lams_organisation (organisation_id) + , INDEX (lams_lesson_state_id) + , CONSTRAINT FK_lams_lesson_4 FOREIGN KEY (lams_lesson_state_id) + REFERENCES lams_lesson_state (lams_lesson_state_id) + , INDEX (class_grouping_id) + , CONSTRAINT FK_lams_lesson_5 FOREIGN KEY (class_grouping_id) + REFERENCES lams_grouping (grouping_id) +)TYPE=InnoDB; + +CREATE TABLE lams_learning_activity ( + activity_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , id INT(11) + , description TEXT + , title VARCHAR(255) + , xcoord INT(11) + , ycoord INT(11) + , parent_activity_id BIGINT(20) + , learning_activity_type_id INT(11) NOT NULL DEFAULT 0 + , grouping_id BIGINT(20) + , order_id INT(11) + , define_later_flag TINYINT(4) NOT NULL DEFAULT 0 + , learning_design_id BIGINT(20) DEFAULT 0 + , learning_library_id BIGINT(20) DEFAULT 0 + , create_date_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' + , offline_instructions TEXT + , max_number_of_options INT(5) + , min_number_of_options INT(5) + , tool_id BIGINT(20) + , tool_content_id BIGINT(20) + , gate_activity_level_id INT(11) DEFAULT 0 + , gate_start_date_time DATETIME + , gate_end_date_time DATETIME + , library_activity_ui_image VARCHAR(255) + , create_grouping_id BIGINT(20) + , PRIMARY KEY (activity_id) + , INDEX (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 + , INDEX (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 + , INDEX (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 + , INDEX (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 + , INDEX (grouping_id) + , CONSTRAINT FK_learning_activity_6 FOREIGN KEY (grouping_id) + REFERENCES lams_grouping (grouping_id) ON DELETE NO ACTION ON UPDATE NO ACTION + , INDEX (tool_id) + , CONSTRAINT FK_lams_learning_activity_8 FOREIGN KEY (tool_id) + REFERENCES lams_tool (tool_id) + , INDEX (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) + , INDEX (create_grouping_id) + , CONSTRAINT FK_lams_learning_activity_9 FOREIGN KEY (create_grouping_id) + REFERENCES lams_grouping (grouping_id) +)TYPE=InnoDB; + +CREATE TABLE lams_learner_progress ( + learner_progress_id BIGINT(20) NOT NULL AUTO_INCREMENT + , user_id BIGINT(20) NOT NULL DEFAULT 0 + , lesson_id BIGINT(20) NOT NULL + , lesson_completed_flag TINYINT(1) NOT NULL DEFAULT 0 + , start_date_time DATETIME NOT NULL + , finish_date_time DATETIME + , PRIMARY KEY (learner_progress_id) + , INDEX (user_id) + , CONSTRAINT FK_lams_learner_progress_1 FOREIGN KEY (user_id) + REFERENCES lams_user (user_id) + , INDEX (lesson_id) + , CONSTRAINT FK_lams_learner_progress_2 FOREIGN KEY (lesson_id) + REFERENCES lams_lesson (lesson_id) +)TYPE=InnoDB; + +CREATE TABLE lams_tool_session ( + tool_session_id BIGINT(20) NOT NULL AUTO_INCREMENT + , group_id BIGINT(20) DEFAULT 0 + , activity_id BIGINT(20) NOT NULL DEFAULT 0 + , tool_session_key BIGINT(20) NOT NULL + , tool_session_state_id INT(3) NOT NULL + , create_date_time DATETIME NOT NULL + , PRIMARY KEY (tool_session_id) + , INDEX (group_id) + , CONSTRAINT FK_lams_tool_session_1 FOREIGN KEY (group_id) + REFERENCES lams_group (group_id) + , INDEX (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) +)TYPE=InnoDB; + +CREATE TABLE lams_user_organisation_role ( + user_organisation_role_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , user_organisation_id BIGINT(20) NOT NULL DEFAULT 0 + , role_id INT(6) NOT NULL DEFAULT 0 + , PRIMARY KEY (user_organisation_role_id) + , INDEX (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 + , INDEX (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 +)TYPE=InnoDB; + +CREATE TABLE lams_progress_completed ( + learner_progress_id BIGINT(20) NOT NULL + , activity_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (learner_progress_id, activity_id) + , INDEX (learner_progress_id) + , CONSTRAINT FK_lams_progress_completed_1 FOREIGN KEY (learner_progress_id) + REFERENCES lams_learner_progress (learner_progress_id) + , INDEX (activity_id) + , CONSTRAINT FK_lams_progress_completed_2 FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id) +)TYPE=InnoDB; + +CREATE TABLE lams_progress_current ( + learner_progress_id BIGINT(20) NOT NULL + , activity_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (learner_progress_id, activity_id) + , INDEX (learner_progress_id) + , CONSTRAINT FK_lams_progress_current_1 FOREIGN KEY (learner_progress_id) + REFERENCES lams_learner_progress (learner_progress_id) + , INDEX (activity_id) + , CONSTRAINT FK_lams_progress_current_2 FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id) +)TYPE=InnoDB; + +CREATE TABLE lams_user_tool_session ( + tool_session_id BIGINT(20) NOT NULL + , user_id BIGINT(20) NOT NULL DEFAULT 0 + , user_tool_session_state_id INT(3) NOT NULL + , PRIMARY KEY (tool_session_id, user_id) + , INDEX (tool_session_id) + , CONSTRAINT FK_lams_user_tool_session_1 FOREIGN KEY (tool_session_id) + REFERENCES lams_tool_session (tool_session_id) + , INDEX (user_id) + , CONSTRAINT FK_lams_user_tool_session_2 FOREIGN KEY (user_id) + REFERENCES lams_user (user_id) + , INDEX (user_tool_session_state_id) + , CONSTRAINT FK_lams_user_tool_session_3 FOREIGN KEY (user_tool_session_state_id) + REFERENCES lams_user_tool_session_state (user_tool_session_state_id) +)TYPE=InnoDB; + +CREATE TABLE lams_user_group ( + user_id BIGINT(20) NOT NULL DEFAULT 0 + , group_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (user_id, group_id) + , INDEX (user_id) + , CONSTRAINT FK_lams_user_group_1 FOREIGN KEY (user_id) + REFERENCES lams_user (user_id) + , INDEX (group_id) + , CONSTRAINT FK_lams_user_group_2 FOREIGN KEY (group_id) + REFERENCES lams_group (group_id) +)TYPE=InnoDB; + +CREATE TABLE lams_tool_content ( + tool_content_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , tool_id BIGINT(20) NOT NULL + , PRIMARY KEY (tool_content_id) + , INDEX (tool_id) + , CONSTRAINT FK_lams_tool_content_1 FOREIGN KEY (tool_id) + REFERENCES lams_tool (tool_id) +)TYPE=InnoDB; + +CREATE TABLE lams_activity_learners ( + user_id BIGINT(20) NOT NULL DEFAULT 0 + , activity_id BIGINT(20) NOT NULL DEFAULT 0 + , INDEX (user_id) + , CONSTRAINT FK_TABLE_32_1 FOREIGN KEY (user_id) + REFERENCES lams_user (user_id) + , INDEX (activity_id) + , CONSTRAINT FK_TABLE_32_2 FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id) +)TYPE=InnoDB; + +CREATE TABLE lams_learning_transition ( + transition_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , id INT(11) + , description TEXT + , title VARCHAR(255) + , to_activity_id BIGINT(20) NOT NULL DEFAULT 0 + , from_activity_id BIGINT(20) NOT NULL DEFAULT 0 + , learning_design_id BIGINT(20) NOT NULL DEFAULT 0 + , create_date_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' + , PRIMARY KEY (transition_id) + , INDEX (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 + , INDEX (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 + , INDEX (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 +)TYPE=InnoDB; + Index: lams_common/db/sql/old/create_lams_db.sql =================================================================== diff -u --- lams_common/db/sql/old/create_lams_db.sql (revision 0) +++ lams_common/db/sql/old/create_lams_db.sql (revision 84be344bfb89ab3182fc4abda27266eddd7662fe) @@ -0,0 +1,568 @@ +-- MySQL Script +-- Modify your server name +-- Modify username and password for the lams db +-- Create lams database for LAMS +-- 13/DEC/2004 + +SET FOREIGN_KEY_CHECKS=0; +DROP DATABASE IF EXISTS lams; +CREATE DATABASE lams; +SET FOREIGN_KEY_CHECKS=1; + +USE mysql; + +DELETE FROM user WHERE user=''; +-- DELETE FROM user WHERE user IS NULL; + +DELETE FROM db WHERE user=''; +-- DELETE FROM db WHERE user IS NULL; + +GRANT ALL PRIVILEGES ON *.* TO lams@localhost IDENTIFIED BY 'lamsdemo'; +REVOKE PROCESS,SUPER ON *.* from lams@localhost; + +GRANT ALL PRIVILEGES ON *.* TO lams@developer02 IDENTIFIED BY 'lamsdemo'; +REVOKE PROCESS,SUPER ON *.* from lams@developer02; + +GRANT ALL PRIVILEGES ON *.* TO lams@'%' IDENTIFIED BY 'lamsdemo'; +REVOKE PROCESS,SUPER ON *.* from lams@'%'; + +FLUSH PRIVILEGES; + +GRANT ALL PRIVILEGES ON lams.* TO lams@localhost IDENTIFIED BY 'lamsdemo' WITH GRANT OPTION; +--The line below should be uncommented and 'developer02' should be replaced with the actual host name +--GRANT ALL PRIVILEGES ON lams.* TO lams@developer02 IDENTIFIED BY 'lamsdemo' WITH GRANT OPTION; +GRANT ALL PRIVILEGES ON lams.* TO lams@'%' IDENTIFIED BY 'lamsdemo' WITH GRANT OPTION; + +FLUSH PRIVILEGES; + +USE lams; + +-- Drop, Create and Populate LAMS Tables + +SET FOREIGN_KEY_CHECKS=0; + +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2004-12-14 16:57:02 +# + +DROP TABLE IF EXISTS lams_gate_activity_level; +CREATE TABLE lams_gate_activity_level ( + gate_activity_level_id INT(11) NOT NULL DEFAULT 0 + , description VARCHAR(128) NOT NULL + , PRIMARY KEY (gate_activity_level_id) +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_grouping_type; +CREATE TABLE lams_grouping_type ( + grouping_type_id INT(11) NOT NULL DEFAULT 0 + , description VARCHAR(128) NOT NULL + , PRIMARY KEY (grouping_type_id) +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_learning_activity_type; +CREATE TABLE lams_learning_activity_type ( + learning_activity_type_id INT(11) NOT NULL DEFAULT 0 + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (learning_activity_type_id) +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_learning_library; +CREATE TABLE lams_learning_library ( + learning_library_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , description TEXT + , title VARCHAR(255) + , create_date_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' + , PRIMARY KEY (learning_library_id) +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_organisation_type; +CREATE TABLE lams_organisation_type ( + organisation_type_id INT(3) NOT NULL DEFAULT 0 + , name VARCHAR(64) NOT NULL + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (organisation_type_id) +)TYPE=InnoDB; +CREATE UNIQUE INDEX UQ_lams_organisation_type_name ON lams_organisation_type (name ASC); + +DROP TABLE IF EXISTS lams_role; +CREATE TABLE lams_role ( + role_id INT(6) NOT NULL DEFAULT 0 + , name VARCHAR(64) NOT NULL + , description TEXT + , create_date BIGINT(20) + , PRIMARY KEY (role_id) +)TYPE=InnoDB; +CREATE INDEX gname ON lams_role (name ASC); + +DROP TABLE IF EXISTS lams_tool; +CREATE TABLE lams_tool ( + tool_id BIGINT(20) NOT NULL AUTO_INCREMENT + , tool_signature VARCHAR(64) NOT NULL + , class_name VARCHAR(255) NOT NULL + , tool_display_name VARCHAR(255) NOT NULL + , description TEXT + , default_tool_content_id BIGINT(20) NOT NULL + , supports_grouping_flag TINYINT(1) NOT NULL DEFAULT 0 + , supports_define_later_flag TINYINT(1) NOT NULL DEFAULT 0 + , learner_url TEXT NOT NULL + , author_url TEXT NOT NULL + , define_later_url TEXT + , export_portfolio_url TEXT NOT NULL + , monitor_url TEXT NOT NULL + , UNIQUE UQ_lams_tool_sig (tool_signature) + , UNIQUE UQ_lams_tool_class_name (class_name) + , PRIMARY KEY (tool_id) +)TYPE=InnoDB; + +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) NOT NULL + , PRIMARY KEY (tool_session_state_id) +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_user_tool_session_state; +CREATE TABLE lams_user_tool_session_state ( + user_tool_session_state_id INT(3) NOT NULL + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (user_tool_session_state_id) +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_lesson_state; +CREATE TABLE lams_lesson_state ( + lams_lesson_state_id INT(3) NOT NULL + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (lams_lesson_state_id) +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_authentication_method_type; +CREATE TABLE lams_authentication_method_type ( + authentication_method_type_id INT(3) NOT NULL DEFAULT 0 + , description VARCHAR(64) NOT NULL + , PRIMARY KEY (authentication_method_type_id) +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_authentication_method; +CREATE TABLE lams_authentication_method ( + authentication_method_id BIGINT(20) NOT NULL DEFAULT 0 + , authentication_method_type_id INT(3) NOT NULL DEFAULT 0 + , authentication_method_name VARCHAR(255) NOT NULL + , PRIMARY KEY (authentication_method_id) + , INDEX (authentication_method_type_id) + , CONSTRAINT FK_lams_authorization_method_1 FOREIGN KEY (authentication_method_type_id) + REFERENCES lams_authentication_method_type (authentication_method_type_id) ON DELETE NO ACTION ON UPDATE NO ACTION +)TYPE=InnoDB; +CREATE UNIQUE INDEX UQ_lams_authentication_method_name ON lams_authentication_method (authentication_method_name ASC); + +DROP TABLE IF EXISTS lams_workspace_folder; +CREATE TABLE lams_workspace_folder ( + workspace_folder_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , parent_folder_id BIGINT(20) + , name VARCHAR(64) NOT NULL + , workspace_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (workspace_folder_id) + , INDEX (parent_folder_id) + , 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 +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_workspace; +CREATE TABLE lams_workspace ( + workspace_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , root_folder_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (workspace_id) + , INDEX (root_folder_id) + , CONSTRAINT FK_lams_workspace_1 FOREIGN KEY (root_folder_id) + REFERENCES lams_workspace_folder (workspace_folder_id) ON DELETE NO ACTION ON UPDATE NO ACTION +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_grouping; +CREATE TABLE lams_grouping ( + grouping_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , grouping_type_id INT(11) NOT NULL DEFAULT 0 + , number_of_groups INT(11) + , learners_per_group INT(11) + , staff_group_id BIGINT(20) DEFAULT 0 + , max_number_of_groups INT(3) + , PRIMARY KEY (grouping_id) + , INDEX (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 +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_group; +CREATE TABLE lams_group ( + group_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , grouping_id BIGINT(20) NOT NULL DEFAULT 0 + , order_id INT(6) NOT NULL DEFAULT 1 + , PRIMARY KEY (group_id) + , INDEX (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 +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_organisation; +CREATE TABLE lams_organisation ( + organisation_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , name VARCHAR(250) + , description VARCHAR(250) + , parent_organisation_id BIGINT(20) + , organisation_type_id INT(3) NOT NULL DEFAULT 0 + , create_date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' + , workspace_id BIGINT(20) + , PRIMARY KEY (organisation_id) + , INDEX (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 + , INDEX (workspace_id) + , CONSTRAINT FK_lams_organisation_2 FOREIGN KEY (workspace_id) + REFERENCES lams_workspace (workspace_id) ON DELETE NO ACTION ON UPDATE NO ACTION + , INDEX (parent_organisation_id) + , CONSTRAINT FK_lams_organisation_3 FOREIGN KEY (parent_organisation_id) + REFERENCES lams_organisation (organisation_id) ON DELETE NO ACTION ON UPDATE NO ACTION +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_user; +CREATE TABLE lams_user ( + user_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , login VARCHAR(20) NOT NULL + , password VARCHAR(50) NOT NULL + , title VARCHAR(32) + , first_name VARCHAR(64) + , 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) + , 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 DEFAULT '0000-00-00 00:00:00' + , authentication_method_id BIGINT(20) NOT NULL DEFAULT 0 + , workspace_id BIGINT(20) + , base_organisation_id BIGINT(20) + , PRIMARY KEY (user_id) + , INDEX (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 + , INDEX (workspace_id) + , CONSTRAINT FK_lams_user_2 FOREIGN KEY (workspace_id) + REFERENCES lams_workspace (workspace_id) ON DELETE NO ACTION ON UPDATE NO ACTION + , INDEX (base_organisation_id) + , CONSTRAINT FK_lams_user_3 FOREIGN KEY (base_organisation_id) + REFERENCES lams_organisation (organisation_id) +)TYPE=InnoDB; +CREATE UNIQUE INDEX UQ_lams_user_login ON lams_user (login ASC); +CREATE INDEX login ON lams_user (login ASC); + +DROP TABLE IF EXISTS lams_learning_design; +CREATE TABLE lams_learning_design ( + learning_design_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , id INT(11) + , description TEXT + , title VARCHAR(255) + , first_activity_id BIGINT(20) + , max_id INT(11) + , valid_design_flag TINYINT(4) NOT NULL + , read_only_flag TINYINT(4) NOT NULL + , date_read_only DATETIME + , read_access BIGINT(20) + , write_access BIGINT(20) + , user_id BIGINT(20) NOT NULL + , help_text TEXT + , lesson_copy_flag TINYINT(4) NOT NULL DEFAULT 0 + , create_date_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' + , version VARCHAR(56) NOT NULL + , parent_learning_design_id BIGINT(20) + , open_date_time DATETIME + , close_date_time DATETIME + , PRIMARY KEY (learning_design_id) + , INDEX (parent_learning_design_id) + , CONSTRAINT FK_lams_learning_design_2 FOREIGN KEY (parent_learning_design_id) + REFERENCES lams_learning_design (learning_design_id) ON DELETE NO ACTION ON UPDATE NO ACTION + , INDEX (user_id) + , CONSTRAINT FK_lams_learning_design_3 FOREIGN KEY (user_id) + REFERENCES lams_user (user_id) +)TYPE=InnoDB; +CREATE INDEX idx_design_first_act ON lams_learning_design (first_activity_id ASC); + +DROP TABLE IF EXISTS lams_learning_activity; +CREATE TABLE lams_learning_activity ( + activity_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , id INT(11) + , description TEXT + , title VARCHAR(255) + , xcoord INT(11) + , ycoord INT(11) + , parent_activity_id BIGINT(20) + , learning_activity_type_id INT(11) NOT NULL DEFAULT 0 + , grouping_id BIGINT(20) + , order_id INT(11) + , define_later_flag TINYINT(4) NOT NULL DEFAULT 0 + , learning_design_id BIGINT(20) DEFAULT 0 + , learning_library_id BIGINT(20) DEFAULT 0 + , create_date_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' + , offline_instructions TEXT + , max_number_of_options INT(5) + , min_number_of_options INT(5) + , tool_id BIGINT(20) + , tool_content_id BIGINT(20) + , gate_activity_level_id INT(11) DEFAULT 0 + , gate_start_date_time DATETIME + , gate_end_date_time DATETIME + , library_activity_ui_image VARCHAR(255) + , PRIMARY KEY (activity_id) + , INDEX (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 + , INDEX (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 + , INDEX (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 + , INDEX (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 + , INDEX (grouping_id) + , CONSTRAINT FK_learning_activity_6 FOREIGN KEY (grouping_id) + REFERENCES lams_grouping (grouping_id) ON DELETE NO ACTION ON UPDATE NO ACTION + , INDEX (tool_id) + , CONSTRAINT FK_lams_learning_activity_8 FOREIGN KEY (tool_id) + REFERENCES lams_tool (tool_id) + , INDEX (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) +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_user_organisation; +CREATE TABLE lams_user_organisation ( + user_organisation_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , organisation_id BIGINT(20) NOT NULL DEFAULT 0 + , user_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (user_organisation_id) + , INDEX (user_id) + , CONSTRAINT u_user_organisation_ibfk_1 FOREIGN KEY (user_id) + REFERENCES lams_user (user_id) ON DELETE NO ACTION ON UPDATE NO ACTION + , INDEX (organisation_id) + , CONSTRAINT u_user_organisation_ibfk_2 FOREIGN KEY (organisation_id) + REFERENCES lams_organisation (organisation_id) ON DELETE NO ACTION ON UPDATE NO ACTION +)TYPE=InnoDB; + +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 DEFAULT 0 + , user_id BIGINT(20) NOT NULL DEFAULT 0 + , create_date_time DATETIME NOT NULL + , organisation_id BIGINT(20) NOT NULL DEFAULT 0 + , lams_lesson_state_id INT(3) NOT NULL + , start_date_time DATETIME + , end_date_time DATETIME + , class_grouping_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (lesson_id) + , INDEX (learning_design_id) + , CONSTRAINT FK_lams_lesson_1_1 FOREIGN KEY (learning_design_id) + REFERENCES lams_learning_design (learning_design_id) + , INDEX (user_id) + , CONSTRAINT FK_lams_lesson_2 FOREIGN KEY (user_id) + REFERENCES lams_user (user_id) + , INDEX (organisation_id) + , CONSTRAINT FK_lams_lesson_3 FOREIGN KEY (organisation_id) + REFERENCES lams_organisation (organisation_id) + , INDEX (lams_lesson_state_id) + , CONSTRAINT FK_lams_lesson_4 FOREIGN KEY (lams_lesson_state_id) + REFERENCES lams_lesson_state (lams_lesson_state_id) + , INDEX (class_grouping_id) + , CONSTRAINT FK_lams_lesson_5 FOREIGN KEY (class_grouping_id) + REFERENCES lams_grouping (grouping_id) +)TYPE=InnoDB; + +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 DEFAULT 0 + , lesson_id BIGINT(20) NOT NULL + , lesson_completed_flag TINYINT(1) NOT NULL DEFAULT 0 + , start_date_time DATETIME NOT NULL + , finish_date_time DATETIME + , PRIMARY KEY (learner_progress_id) + , INDEX (user_id) + , CONSTRAINT FK_lams_learner_progress_1 FOREIGN KEY (user_id) + REFERENCES lams_user (user_id) + , INDEX (lesson_id) + , CONSTRAINT FK_lams_learner_progress_2 FOREIGN KEY (lesson_id) + REFERENCES lams_lesson (lesson_id) +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_tool_session; +CREATE TABLE lams_tool_session ( + tool_session_id BIGINT(20) NOT NULL AUTO_INCREMENT + , group_id BIGINT(20) DEFAULT 0 + , activity_id BIGINT(20) NOT NULL DEFAULT 0 + , tool_session_key BIGINT(20) NOT NULL + , tool_session_state_id INT(3) NOT NULL + , create_date_time DATETIME NOT NULL + , PRIMARY KEY (tool_session_id) + , INDEX (group_id) + , CONSTRAINT FK_lams_tool_session_1 FOREIGN KEY (group_id) + REFERENCES lams_group (group_id) + , INDEX (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) +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_user_organisation_role; +CREATE TABLE lams_user_organisation_role ( + user_organisation_role_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , user_organisation_id BIGINT(20) NOT NULL DEFAULT 0 + , role_id INT(6) NOT NULL DEFAULT 0 + , PRIMARY KEY (user_organisation_role_id) + , INDEX (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 + , INDEX (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 +)TYPE=InnoDB; + +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 DEFAULT 0 + , PRIMARY KEY (learner_progress_id, activity_id) + , INDEX (learner_progress_id) + , CONSTRAINT FK_lams_progress_completed_1 FOREIGN KEY (learner_progress_id) + REFERENCES lams_learner_progress (learner_progress_id) + , INDEX (activity_id) + , CONSTRAINT FK_lams_progress_completed_2 FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id) +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_progress_current; +CREATE TABLE lams_progress_current ( + learner_progress_id BIGINT(20) NOT NULL + , activity_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (learner_progress_id, activity_id) + , INDEX (learner_progress_id) + , CONSTRAINT FK_lams_progress_current_1 FOREIGN KEY (learner_progress_id) + REFERENCES lams_learner_progress (learner_progress_id) + , INDEX (activity_id) + , CONSTRAINT FK_lams_progress_current_2 FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id) +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_user_tool_session; +CREATE TABLE lams_user_tool_session ( + tool_session_id BIGINT(20) NOT NULL + , user_id BIGINT(20) NOT NULL DEFAULT 0 + , user_tool_session_state_id INT(3) NOT NULL + , PRIMARY KEY (tool_session_id, user_id) + , INDEX (tool_session_id) + , CONSTRAINT FK_lams_user_tool_session_1 FOREIGN KEY (tool_session_id) + REFERENCES lams_tool_session (tool_session_id) + , INDEX (user_id) + , CONSTRAINT FK_lams_user_tool_session_2 FOREIGN KEY (user_id) + REFERENCES lams_user (user_id) + , INDEX (user_tool_session_state_id) + , CONSTRAINT FK_lams_user_tool_session_3 FOREIGN KEY (user_tool_session_state_id) + REFERENCES lams_user_tool_session_state (user_tool_session_state_id) +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_user_group; +CREATE TABLE lams_user_group ( + user_id BIGINT(20) NOT NULL DEFAULT 0 + , group_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (user_id, group_id) + , INDEX (user_id) + , CONSTRAINT FK_lams_user_group_1 FOREIGN KEY (user_id) + REFERENCES lams_user (user_id) + , INDEX (group_id) + , CONSTRAINT FK_lams_user_group_2 FOREIGN KEY (group_id) + REFERENCES lams_group (group_id) +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_tool_content; +CREATE TABLE lams_tool_content ( + tool_content_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , tool_id BIGINT(20) NOT NULL + , PRIMARY KEY (tool_content_id) + , INDEX (tool_id) + , CONSTRAINT FK_lams_tool_content_1 FOREIGN KEY (tool_id) + REFERENCES lams_tool (tool_id) +)TYPE=InnoDB; + +DROP TABLE IF EXISTS lams_learning_transition; +CREATE TABLE lams_learning_transition ( + transition_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT + , id INT(11) + , description TEXT + , title VARCHAR(255) + , to_activity_id BIGINT(20) NOT NULL DEFAULT 0 + , from_activity_id BIGINT(20) NOT NULL DEFAULT 0 + , learning_design_id BIGINT(20) NOT NULL DEFAULT 0 + , create_date_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' + , PRIMARY KEY (transition_id) + , INDEX (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 + , INDEX (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 + , INDEX (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 +)TYPE=InnoDB; + + +INSERT INTO lams_role VALUES (1, 'SYSADMIN', 'LAMS System Adminstrator', NOW()); +INSERT INTO lams_role VALUES (2, 'ADMIN', 'Organization Adminstrator', NOW()); +INSERT INTO lams_role VALUES (3, 'AUTHOR', 'Authors Learning Designs', NOW()); +INSERT INTO lams_role VALUES (4, 'STAFF', 'Member of Staff', NOW()); +INSERT INTO lams_role VALUES (5, 'LEARNER', 'Student', NOW()); + +INSERT INTO lams_authentication_method_type VALUES(1, 'LAMS'); +INSERT INTO lams_authentication_method_type VALUES(2, 'WEB_AUTH'); +INSERT INTO lams_authentication_method_type VALUES(3, 'LDAP'); + +INSERT INTO lams_organisation_type VALUES(1, 'ROOT ORGANISATION', 'root all other organisations: controlled by Sysadmin'); +INSERT INTO lams_organisation_type VALUES(2, 'BASE ORGANISATION', 'base organization: represents a real seperate organization sucha s a university '); +INSERT INTO lams_organisation_type VALUES(3, 'SUB-ORGANIZATION', 'sub organization of a base organization'); + +INSERT INTO lams_grouping_type VALUES (1, 'NORMAL'); +INSERT INTO lams_grouping_type VALUES (2, 'CLASS'); + +INSERT INTO lams_learning_activity_type VALUES (1, 'TOOL'); +INSERT INTO lams_learning_activity_type VALUES (2, 'GROUPING_RANDOM'); +INSERT INTO lams_learning_activity_type VALUES (3, 'GROUPING_CHOSEN'); +INSERT INTO lams_learning_activity_type VALUES (4, 'GATE_SYNCH'); +INSERT INTO lams_learning_activity_type VALUES (5, 'GATE_SCHEDULE'); +INSERT INTO lams_learning_activity_type VALUES (6, 'GATE_PERMISSION'); +INSERT INTO lams_learning_activity_type VALUES (7, 'PARALLEL'); +INSERT INTO lams_learning_activity_type VALUES (8, 'OPTIONS'); +INSERT INTO lams_learning_activity_type VALUES (9, 'SEQUENCE'); +INSERT INTO lams_learning_activity_type VALUES (10, 'BRANCH_GROUP_CONTENT'); + +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'); + +INSERT INTO lams_tool_session_state VALUES (1, 'STARTED'); +INSERT INTO lams_tool_session_state VALUES (2, 'FINISHED'); +INSERT INTO lams_tool_session_state VALUES (3, 'SUSPENDED'); +INSERT INTO lams_tool_session_state VALUES (4, 'RESUMED'); +INSERT INTO lams_tool_session_state VALUES (5, 'ERROR'); + +INSERT INTO lams_user_tool_session_state VALUES (1, 'JOINED'); +INSERT INTO lams_user_tool_session_state VALUES (2, 'LEFT' ); +INSERT INTO lams_user_tool_session_state VALUES (3, 'SUSPENDED'); +INSERT INTO lams_user_tool_session_state VALUES (4, 'RESUMED'); +INSERT INTO lams_user_tool_session_state VALUES (5, 'ERROR'); + +SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Index: lams_common/db/sql/old/create_learning_design_tables.sql =================================================================== diff -u --- lams_common/db/sql/old/create_learning_design_tables.sql (revision 0) +++ lams_common/db/sql/old/create_learning_design_tables.sql (revision 84be344bfb89ab3182fc4abda27266eddd7662fe) @@ -0,0 +1,270 @@ +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2004-11-22 15:34:31 +# +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2004-11-22 15:21:41 +# +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2004-11-22 14:57:54 +# +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2004-11-22 13:18:58 +# +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2004-11-22 11:59:03 +# +-- Tables are not sorted because: +-- Circular dependency between table DEFAULT_SCHEMA.lams_learning_activity and table DEFAULT_SCHEMA.lams_learning_design: both have foreign keys that reference the other table. +-- Try generating Foreign Keys with ALTER TABLE statements. +CREATE TABLE lams_learning_activity ( + activity_id BIGINT DEFAULT '0' NOT NULL + , id INT + , description TEXT + , title VARCHAR(255) + , xcoord INT + , ycoord INT + , parent_activity_id BIGINT + , learning_activity_type_id INT NOT NULL + , learning_grouping_id BIGINT + , order_id INT + , define_later_flag TINYINT DEFAULT '0' NOT NULL + , learning_design_id BIGINT DEFAULT '0' + , learning_library_id BIGINT DEFAULT '0' + , create_date_time DATETIME NOT NULL + , PRIMARY KEY (activity_id) +)TYPE=InnoDB; +CREATE INDEX idx_activity_library_id ON lams_learning_activity (learning_library_id); +CREATE INDEX idx_activity_design_id ON lams_learning_activity (learning_design_id); +CREATE INDEX idx_activity_grouping_id ON lams_learning_activity (learning_grouping_id); +CREATE INDEX idx_activity_activity_type_id ON lams_learning_activity (learning_activity_type_id); +CREATE INDEX idx_activity_parent_id ON lams_learning_activity (parent_activity_id); + + +CREATE TABLE lams_learning_design ( + learning_design_id BIGINT DEFAULT '0' NOT NULL + , id INT + , description TEXT + , title VARCHAR(255) + , first_activity_id BIGINT + , max_id INT + , object_type VARCHAR(255) + , valid_design_flag TINYINT + , read_only_flag TINYINT + , date_read_only DATETIME + , read_access BIGINT + , write_access BIGINT + , owning_user_id BIGINT + , help_text TEXT + , lesson_copy_flag TINYINT DEFAULT '0' NOT NULL + , create_date_time DATETIME NOT NULL + , version VARCHAR(56) NOT NULL + , parent_learning_design_id BIGINT + , open_date_time DATETIME + , close_date_time DATETIME + , PRIMARY KEY (learning_design_id) +)TYPE=InnoDB; +CREATE INDEX idx_design_parent_id ON lams_learning_design (parent_learning_design_id); +CREATE INDEX idx_design_user_id ON lams_learning_design (owning_user_id); +CREATE INDEX idx_design_first_act ON lams_learning_design (first_activity_id); + +CREATE TABLE lams_learning_library ( + learning_library_id BIGINT DEFAULT '0' NOT NULL + , description TEXT + , title VARCHAR(255) + , create_date_time DATETIME NOT NULL + , PRIMARY KEY (learning_library_id) +)TYPE=InnoDB; + +CREATE TABLE lams_learning_transition ( + transition_id BIGINT DEFAULT '0' NOT NULL + , id INT + , description TEXT + , title VARCHAR(255) + , to_activity_id BIGINT NOT NULL + , from_activity_id BIGINT NOT NULL + , learning_design_id BIGINT NOT NULL + , create_date_time DATETIME NOT NULL + , PRIMARY KEY (transition_id) +)TYPE=InnoDB; +CREATE INDEX ldId ON lams_learning_transition (learning_design_id); +CREATE INDEX idx_transition_to_activity ON lams_learning_transition (to_activity_id); +CREATE INDEX idx_transition_from_activity ON lams_learning_transition (from_activity_id); + + +CREATE TABLE lams_learning_grouping ( + learning_grouping_id BIGINT NOT NULL + , grouping_type_id INT NOT NULL + , number_of_groups INT + , learners_per_group INT + , PRIMARY KEY (learning_grouping_id) +)TYPE=InnoDB; +CREATE INDEX idx_grouping_grouping_type ON lams_learning_grouping (grouping_type_id); + +CREATE TABLE lams_learning_group ( + learning_group_id BIGINT NOT NULL + , learning_grouping_id BIGINT NOT NULL + , PRIMARY KEY (learning_group_id) +)TYPE=InnoDB; +CREATE INDEX idx_group_grouping ON lams_learning_group (learning_grouping_id); + +CREATE TABLE lams_learning_activity_type ( + learning_activity_type_id INT NOT NULL + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (learning_activity_type_id) +)TYPE=InnoDB; + + + +CREATE TABLE lams_learning_tool_activity ( + activity_id BIGINT DEFAULT '0' NOT NULL + , tool_id BIGINT NOT NULL + , tool_content_id BIGINT + , PRIMARY KEY (activity_id) +)TYPE=InnoDB; +CREATE INDEX idx_toole_act_activity ON lams_learning_tool_activity (activity_id); +CREATE INDEX idx_toole_act_tool ON lams_learning_tool_activity (tool_id); +CREATE INDEX idx_toole_act_tool_content ON lams_learning_tool_activity (tool_content_id); + +CREATE TABLE lams_learning_gate_activity ( + activity_id BIGINT DEFAULT '0' NOT NULL + , start_date_time DATETIME + , end_date_time DATETIME + , gate_activity_level_id INT NOT NULL + , PRIMARY KEY (activity_id) +)TYPE=InnoDB; +CREATE INDEX idx_gate_act_activity ON lams_learning_gate_activity (activity_id); +CREATE INDEX idx_gate_act_level ON lams_learning_gate_activity (gate_activity_level_id); + +CREATE TABLE lams_grouping_type ( + grouping_type_id INT NOT NULL + , description VARCHAR(128) NOT NULL + , PRIMARY KEY (grouping_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_gate_activity_level ( + gate_activity_level_id INT NOT NULL + , description VARCHAR(128) NOT NULL + , PRIMARY KEY (gate_activity_level_id) +)TYPE=InnoDB; + +CREATE TABLE lams_learning_options_activity ( + activity_id BIGINT DEFAULT '0' NOT NULL + , min_number_of_options INT + , max_number_of_options INT + , PRIMARY KEY (activity_id) +)TYPE=InnoDB; +CREATE INDEX idx_options_act_activity ON lams_learning_options_activity (activity_id); + +CREATE TABLE lams_learning_grouping_activity ( + activity_id BIGINT DEFAULT '0' NOT NULL + , learning_grouping_id BIGINT NOT NULL + , PRIMARY KEY (activity_id) +)TYPE=InnoDB; +CREATE INDEX idx_grouping_act_activity ON lams_learning_grouping_activity (activity_id); +CREATE INDEX idx_grouping_act_grouping ON lams_learning_grouping_activity (learning_grouping_id); + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_learning_activity_2 + FOREIGN KEY (parent_activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_learning_activity_3 + FOREIGN KEY (learning_activity_type_id) + REFERENCES lams_learning_activity_type (learning_activity_type_id); + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_learning_activity_4 + FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_learning_activity_5 + FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_learning_activity_6 + FOREIGN KEY (learning_grouping_id) + REFERENCES lams_learning_grouping (learning_grouping_id); + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_lams_learning_activity_6 + FOREIGN KEY (learning_design_id) + REFERENCES lams_learning_design (learning_design_id); + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_lams_learning_activity_7 + FOREIGN KEY (learning_library_id) + REFERENCES lams_learning_library (learning_library_id); + +ALTER TABLE lams_learning_design + ADD CONSTRAINT FK_lams_learning_design_1 + FOREIGN KEY (first_activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_design + ADD CONSTRAINT FK_lams_learning_design_2 + FOREIGN KEY (parent_learning_design_id) + REFERENCES lams_learning_design (learning_design_id); + +ALTER TABLE lams_learning_transition + ADD CONSTRAINT lddefn_transition_ibfk_1 + FOREIGN KEY (learning_design_id) + REFERENCES lams_learning_design (learning_design_id); + +ALTER TABLE lams_learning_transition + ADD CONSTRAINT FK_learning_transition_2 + FOREIGN KEY (to_activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_transition + ADD CONSTRAINT FK_learning_transition_3 + FOREIGN KEY (from_activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_grouping + ADD CONSTRAINT FK_lams_learning_grouping_1 + FOREIGN KEY (grouping_type_id) + REFERENCES lams_grouping_type (grouping_type_id); + +ALTER TABLE lams_learning_group + ADD CONSTRAINT FK_lams_learning_group_1 + FOREIGN KEY (learning_grouping_id) + REFERENCES lams_learning_grouping (learning_grouping_id); + +ALTER TABLE lams_learning_tool_activity + ADD CONSTRAINT FK_lams_learning_tool_activity_1 + FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_gate_activity + ADD CONSTRAINT FK_lams_learning_gate_activity_1 + FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_gate_activity + ADD CONSTRAINT FK_lams_learning_gate_activity_2 + FOREIGN KEY (gate_activity_level_id) + REFERENCES lams_gate_activity_level (gate_activity_level_id); + +ALTER TABLE lams_learning_options_activity + ADD CONSTRAINT FK_lams_learning_options_activity_1 + FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_grouping_activity + ADD CONSTRAINT FK_lams_learning_grouping_activity_1 + FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_grouping_activity + ADD CONSTRAINT FK_lams_learning_grouping_activity_2 + FOREIGN KEY (learning_grouping_id) + REFERENCES lams_learning_grouping (learning_grouping_id); + Index: lams_common/db/sql/old/create_user_management_tables.sql =================================================================== diff -u --- lams_common/db/sql/old/create_user_management_tables.sql (revision 0) +++ lams_common/db/sql/old/create_user_management_tables.sql (revision 84be344bfb89ab3182fc4abda27266eddd7662fe) @@ -0,0 +1,142 @@ +CREATE TABLE lams_organisation_type ( + organisation_type_id INT(3) NOT NULL + , name VARCHAR(64) NOT NULL + , description VARCHAR(255) NOT NULL + , UNIQUE UQ_lams_organisation_type_name (name) + , PRIMARY KEY (organisation_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_authentication_method_type ( + authentication_method_type_id INT(3) NOT NULL + , description VARCHAR(64) NOT NULL + , PRIMARY KEY (authentication_method_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_role ( + role_id INT(6) NOT NULL DEFAULT 0 + , name VARCHAR(64) NOT NULL + , description TEXT + , create_date BIGINT(20) + , PRIMARY KEY (role_id) +)TYPE=InnoDB; +CREATE INDEX gname ON lams_role (name ASC); + +CREATE TABLE lams_authentication_method ( + authentication_method_id INT(11) NOT NULL + , authentication_method_type_id INT(3) NOT NULL + , PRIMARY KEY (authentication_method_id) + , INDEX (authentication_method_type_id) + , CONSTRAINT FK_lams_authorization_method_1 FOREIGN KEY (authentication_method_type_id) + REFERENCES lams_authentication_method_type (authentication_method_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_workspace_folder ( + workspace_folder_id INT(11) NOT NULL + , parent_folder_id INT(11) + , name VARCHAR(64) NOT NULL + , workspace_id INT(11) NOT NULL + , PRIMARY KEY (workspace_folder_id) + , INDEX (parent_folder_id) + , CONSTRAINT FK_lams_workspace_folder_2 FOREIGN KEY (parent_folder_id) + REFERENCES lams_workspace_folder (workspace_folder_id) +)TYPE=InnoDB; + +CREATE TABLE lams_workspace ( + workspace_id INT(11) NOT NULL + , root_folder_id INT(11) NOT NULL + , PRIMARY KEY (workspace_id) + , INDEX (root_folder_id) + , CONSTRAINT FK_lams_workspace_1 FOREIGN KEY (root_folder_id) + REFERENCES lams_workspace_folder (workspace_folder_id) +)TYPE=InnoDB; + +CREATE TABLE lams_user ( + user_id INT(11) NOT NULL DEFAULT 0 + , login VARCHAR(20) NOT NULL + , password VARCHAR(50) NOT NULL + , title VARCHAR(32) + , first_name VARCHAR(64) + , 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) + , country VARCHAR(64) + , day_phone VARCHAR(64) + , evening_phone VARCHAR(64) + , mobile_phone VARCHAR(64) + , fax VARCHAR(64) + , email VARCHAR(128) + , disabled_flag BOOL NOT NULL DEFAULT 0 + , create_date DATETIME NOT NULL + , authentication_method_id INT(11) NOT NULL + , workspace_id INT(11) + , UNIQUE UQ_lams_user_login (login) + , PRIMARY KEY (user_id) + , INDEX (authentication_method_id) + , CONSTRAINT FK_lams_user_1 FOREIGN KEY (authentication_method_id) + REFERENCES lams_authentication_method (authentication_method_id) + , INDEX (workspace_id) + , CONSTRAINT FK_lams_user_2 FOREIGN KEY (workspace_id) + REFERENCES lams_workspace (workspace_id) +)TYPE=InnoDB; +CREATE INDEX login ON lams_user (login ASC); + +CREATE TABLE lams_organisation ( + organisation_id INT(11) NOT NULL DEFAULT 0 + , name VARCHAR(250) + , description VARCHAR(250) + , parent_organisation_id INT(11) + , organisation_type_id INT(3) NOT NULL + , create_date DATETIME NOT NULL + , workspace_id INT(11) + , PRIMARY KEY (organisation_id) + , INDEX (organisation_type_id) + , CONSTRAINT FK_lams_organisation_1 FOREIGN KEY (organisation_type_id) + REFERENCES lams_organisation_type (organisation_type_id) + , INDEX (workspace_id) + , CONSTRAINT FK_lams_organisation_2 FOREIGN KEY (workspace_id) + REFERENCES lams_workspace (workspace_id) + , INDEX (parent_organisation_id) + , CONSTRAINT FK_lams_organisation_3 FOREIGN KEY (parent_organisation_id) + REFERENCES lams_organisation (organisation_id) +)TYPE=InnoDB; + +CREATE TABLE lams_user_organisation ( + user_organisation_id INT(11) NOT NULL + , organisation_id INT(11) NOT NULL + , user_id INT(11) NOT NULL + , PRIMARY KEY (user_organisation_id) + , INDEX (user_id) + , CONSTRAINT u_user_organisation_ibfk_1 FOREIGN KEY (user_id) + REFERENCES lams_user (user_id) ON DELETE NO ACTION ON UPDATE NO ACTION + , INDEX (organisation_id) + , CONSTRAINT u_user_organisation_ibfk_2 FOREIGN KEY (organisation_id) + REFERENCES lams_organisation (organisation_id) ON DELETE NO ACTION ON UPDATE NO ACTION +)TYPE=InnoDB; + +CREATE TABLE lams_authentication_method_parameter ( + authentication_parameter_id INT(11) NOT NULL + , authentication_method_id INT(11) NOT NULL + , name VARCHAR(128) NOT NULL + , value VARCHAR(255) + , PRIMARY KEY (authentication_parameter_id) + , INDEX (authentication_method_id) + , CONSTRAINT FK_lams_authorization_method_parameter_1 FOREIGN KEY (authentication_method_id) + REFERENCES lams_authentication_method (authentication_method_id) +)TYPE=InnoDB; + +CREATE TABLE lams_user_organisation_role ( + user_organisation_role_id INT(11) NOT NULL + , user_organisation_id INT(11) NOT NULL + , role_id INT(6) NOT NULL DEFAULT 0 + , PRIMARY KEY (user_organisation_role_id) + , INDEX (role_id) + , CONSTRAINT FK_lams_user_organisation_role_2 FOREIGN KEY (role_id) + REFERENCES lams_role (role_id) + , INDEX (user_organisation_id) + , CONSTRAINT FK_lams_user_organisation_role_3 FOREIGN KEY (user_organisation_id) + REFERENCES lams_user_organisation (user_organisation_id) +)TYPE=InnoDB; + Index: lams_common/db/sql/old/drop_lams_11_tables.sql =================================================================== diff -u --- lams_common/db/sql/old/drop_lams_11_tables.sql (revision 0) +++ lams_common/db/sql/old/drop_lams_11_tables.sql (revision 84be344bfb89ab3182fc4abda27266eddd7662fe) @@ -0,0 +1,114 @@ +DROP INDEX PRIMARY ON lams_gate_activity_level; +DROP INDEX PRIMARY ON lams_grouping_type; +DROP INDEX PRIMARY ON lams_learning_activity_type; +DROP INDEX PRIMARY ON lams_learning_library; +DROP INDEX PRIMARY ON lams_organisation_type; +DROP INDEX UQ_lams_organisation_type_name ON lams_organisation_type; +DROP INDEX PRIMARY ON lams_role; +DROP INDEX gname ON lams_role; +DROP INDEX PRIMARY ON lams_authentication_method_type; +DROP INDEX PRIMARY ON lams_authentication_method; +DROP INDEX authentication_method_type_id ON lams_authentication_method; +DROP INDEX PRIMARY ON lams_workspace_folder; +DROP INDEX parent_folder_id ON lams_workspace_folder; +DROP INDEX PRIMARY ON lams_workspace; +DROP INDEX root_folder_id ON lams_workspace; +DROP INDEX PRIMARY ON lams_grouping; +DROP INDEX idx_grouping_grouping_type ON lams_grouping; +DROP INDEX PRIMARY ON lams_organisation; +DROP INDEX organisation_type_id ON lams_organisation; +DROP INDEX workspace_id ON lams_organisation; +DROP INDEX parent_organisation_id ON lams_organisation; +DROP INDEX PRIMARY ON lams_user; +DROP INDEX UQ_lams_user_login ON lams_user; +DROP INDEX authentication_method_id ON lams_user; +DROP INDEX workspace_id ON lams_user; +DROP INDEX login ON lams_user; +DROP INDEX PRIMARY ON lams_learning_design; +DROP INDEX idx_design_parent_id ON lams_learning_design; +DROP INDEX idx_design_user_id ON lams_learning_design; +DROP INDEX idx_design_first_act ON lams_learning_design; +DROP INDEX PRIMARY ON lams_group; +DROP INDEX idx_group_grouping ON lams_group; +DROP INDEX PRIMARY ON lams_user_organisation; +DROP INDEX user_id ON lams_user_organisation; +DROP INDEX organisation_id ON lams_user_organisation; +DROP INDEX PRIMARY ON lams_learning_activity; +DROP INDEX idx_activity_library_id ON lams_learning_activity; +DROP INDEX idx_activity_design_id ON lams_learning_activity; +DROP INDEX idx_activity_grouping_id ON lams_learning_activity; +DROP INDEX idx_activity_activity_type_id ON lams_learning_activity; +DROP INDEX idx_activity_parent_id ON lams_learning_activity; +DROP INDEX PRIMARY ON lams_user_organisation_role; +DROP INDEX role_id ON lams_user_organisation_role; +DROP INDEX user_organisation_id ON lams_user_organisation_role; +DROP INDEX PRIMARY ON lams_learning_transition; +DROP INDEX ldId ON lams_learning_transition; +DROP INDEX idx_transition_to_activity ON lams_learning_transition; +DROP INDEX idx_transition_from_activity ON lams_learning_transition; + +DROP TABLE lams_learning_transition; +DROP TABLE lams_activity_learners; +DROP TABLE lams_tool_content; +DROP TABLE lams_user_group; +DROP TABLE lams_user_tool_session; +DROP TABLE lams_progress_current; +DROP TABLE lams_progress_completed; +DROP TABLE lams_user_organisation_role; +DROP TABLE lams_tool_session; +DROP TABLE lams_learner_progress; +DROP TABLE lams_learning_activity; +DROP TABLE lams_lesson; +DROP TABLE lams_user_organisation; +DROP TABLE lams_group; +DROP TABLE lams_learning_design; +DROP TABLE lams_user; +DROP TABLE lams_organisation; +DROP TABLE lams_grouping; +DROP TABLE lams_workspace; +DROP TABLE lams_workspace_folder; +DROP TABLE lams_authentication_method; +DROP TABLE lams_authentication_method_type; +DROP TABLE lams_lesson_state; +DROP TABLE lams_user_tool_session_state; +DROP TABLE lams_tool_session_state; +DROP TABLE lams_tool; +DROP TABLE lams_role; +DROP TABLE lams_organisation_type; +DROP TABLE lams_learning_library; +DROP TABLE lams_learning_activity_type; +DROP TABLE lams_grouping_type; +DROP TABLE lams_gate_activity_level; + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Index: lams_common/db/sql/old/drop_learning_design_tables.sql =================================================================== diff -u --- lams_common/db/sql/old/drop_learning_design_tables.sql (revision 0) +++ lams_common/db/sql/old/drop_learning_design_tables.sql (revision 84be344bfb89ab3182fc4abda27266eddd7662fe) @@ -0,0 +1,140 @@ +-- Tables are not sorted because: +-- Circular dependency between table DEFAULT_SCHEMA.lams_learning_activity and table DEFAULT_SCHEMA.lams_learning_design: both have foreign keys that reference the other table. +-- Try generating Foreign Keys with ALTER TABLE statements. +DROP INDEX PRIMARY; +DROP INDEX ldId; +DROP INDEX optional_activity_id; +DROP INDEX PRIMARY; +DROP INDEX idx_learning_design_libary_id; +DROP INDEX PRIMARY; +DROP INDEX PRIMARY; +DROP INDEX ldId; +DROP INDEX activity_id; + +DROP TABLE lams_learning_grouping_activity; +DROP TABLE lams_learning_options_activity; +DROP TABLE lams_gate_activity_level; +DROP TABLE lams_grouping_type; +DROP TABLE lams_learning_gate_activity; +DROP TABLE lams_learning_tool_activity; +DROP TABLE lams_learning_activity_type; +DROP TABLE lams_learning_group; +DROP TABLE lams_learning_grouping; +DROP TABLE lams_learning_transition; +DROP TABLE lams_learning_library; +DROP TABLE lams_learning_design; +DROP TABLE lams_learning_activity; + + + + + + + + + + + + + + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_learning_activity_2 + FOREIGN KEY (parent_activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_learning_activity_3 + FOREIGN KEY (learning_activity_type_id) + REFERENCES lams_learning_activity_type (learning_activity_type_id); + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_learning_activity_4 + FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_learning_activity_5 + FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_learning_activity_6 + FOREIGN KEY (learning_grouping_id) + REFERENCES lams_learning_grouping (learning_grouping_id); + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_lams_learning_activity_6 + FOREIGN KEY (learning_design_id) + REFERENCES lams_learning_design (learning_design_id); + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_lams_learning_activity_7 + FOREIGN KEY (learning_library_id) + REFERENCES lams_learning_library (learning_library_id); + +ALTER TABLE lams_learning_design + ADD CONSTRAINT FK_lams_learning_design_1 + FOREIGN KEY (first_activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_design + ADD CONSTRAINT FK_lams_learning_design_2 + FOREIGN KEY (parent_learning_design_id) + REFERENCES lams_learning_design (learning_design_id); + +ALTER TABLE lams_learning_transition + ADD CONSTRAINT lddefn_transition_ibfk_1 + FOREIGN KEY (learning_design_id) + REFERENCES lams_learning_design (learning_design_id); + +ALTER TABLE lams_learning_transition + ADD CONSTRAINT FK_learning_transition_2 + FOREIGN KEY (to_activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_transition + ADD CONSTRAINT FK_learning_transition_3 + FOREIGN KEY (from_activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_grouping + ADD CONSTRAINT FK_lams_learning_grouping_1 + FOREIGN KEY (grouping_type_id) + REFERENCES lams_grouping_type (grouping_type_id); + +ALTER TABLE lams_learning_group + ADD CONSTRAINT FK_lams_learning_group_1 + FOREIGN KEY (learning_grouping_id) + REFERENCES lams_learning_grouping (learning_grouping_id); + +ALTER TABLE lams_learning_tool_activity + ADD CONSTRAINT FK_lams_learning_tool_activity_1 + FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_gate_activity + ADD CONSTRAINT FK_lams_learning_gate_activity_1 + FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_gate_activity + ADD CONSTRAINT FK_lams_learning_gate_activity_2 + FOREIGN KEY (gate_activity_level_id) + REFERENCES lams_gate_activity_level (gate_activity_level_id); + +ALTER TABLE lams_learning_options_activity + ADD CONSTRAINT FK_lams_learning_options_activity_1 + FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_grouping_activity + ADD CONSTRAINT FK_lams_learning_grouping_activity_1 + FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_learning_grouping_activity + ADD CONSTRAINT FK_lams_learning_grouping_activity_2 + FOREIGN KEY (learning_grouping_id) + REFERENCES lams_learning_grouping (learning_grouping_id); + Index: lams_common/db/sql/old/drop_user_management_tables.sql =================================================================== diff -u --- lams_common/db/sql/old/drop_user_management_tables.sql (revision 0) +++ lams_common/db/sql/old/drop_user_management_tables.sql (revision 84be344bfb89ab3182fc4abda27266eddd7662fe) @@ -0,0 +1,31 @@ +DROP INDEX PRIMARY ON lams_role; +DROP INDEX gname ON lams_role; +DROP INDEX PRIMARY ON lams_user; +DROP INDEX login ON lams_user; +DROP INDEX PRIMARY ON lams_organisation; +DROP INDEX idx_user_org_org_id ON lams_user_organisation; +DROP INDEX user_id ON lams_user_organisation; + +DROP TABLE lams_user_organisation_role; +DROP TABLE lams_authentication_method_parameter; +DROP TABLE lams_user_organisation; +DROP TABLE lams_organisation; +DROP TABLE lams_user; +DROP TABLE lams_workspace; +DROP TABLE lams_workspace_folder; +DROP TABLE lams_authentication_method; +DROP TABLE lams_role; +DROP TABLE lams_authentication_method_type; +DROP TABLE lams_organisation_type; + + + + + + + + + + + + Index: lams_common/db/sql/old/insert_test_data.sql =================================================================== diff -u --- lams_common/db/sql/old/insert_test_data.sql (revision 0) +++ lams_common/db/sql/old/insert_test_data.sql (revision 84be344bfb89ab3182fc4abda27266eddd7662fe) @@ -0,0 +1,39 @@ +SET FOREIGN_KEY_CHECKS=0; + +INSERT INTO lams_organisation VALUES (1, 'Root', 'Root Organisation',null,1,NOW(),null); +INSERT INTO lams_organisation VALUES (2, 'Maquarie Uni', 'Macquarie University',1,2,NOW(),null); +INSERT INTO lams_organisation VALUES (3, 'MELCOE', 'Macquarie E-learning Center',2,3,NOW(),null); +INSERT INTO lams_organisation VALUES (4, 'LAMS', 'Lams Project Team',3,3,NOW(),null); +INSERT INTO lams_organisation VALUES (5, 'MAMS', 'Mams Project Team',3,3,NOW(),null); + +INSERT INTO lams_user VALUES(1, 'sysadmin','sysadmin','Mr','Fei','Yang',null,null,null,'Sydney','NSW','Australia',null,null,null,null,'fyang@melcoe.mq.edu.au',0,NOW(),1,null,null); +INSERT INTO lams_user VALUES(2, 'test','test','Mr','Kevin','Han',null,null,null,'Sydney','NSW','Australia',null,null,null,null,'khan@melcoe.mq.edu.au',0,NOW(),3,null,null); +INSERT INTO lams_user VALUES(3, 'lamskh01','dummy','Mr','Jacky','Fang',null,null,null,'Sydney','NSW','Australia',null,null,null,null,'jfang@melcoe.mq.edu.au',0,NOW(),2,null,null); + +INSERT INTO lams_user_organisation VALUES (1, 1, 1); +INSERT INTO lams_user_organisation VALUES (2, 2, 2); +INSERT INTO lams_user_organisation VALUES (3, 3, 2); +INSERT INTO lams_user_organisation VALUES (4, 4, 2); +INSERT INTO lams_user_organisation VALUES (5, 2, 3); +INSERT INTO lams_user_organisation VALUES (6, 3, 3); + +INSERT INTO lams_user_organisation_role VALUES (1, 1, 1); +INSERT INTO lams_user_organisation_role VALUES (2, 2, 2); +INSERT INTO lams_user_organisation_role VALUES (3, 2, 3); +INSERT INTO lams_user_organisation_role VALUES (4, 2, 4); +INSERT INTO lams_user_organisation_role VALUES (5, 3, 2); +INSERT INTO lams_user_organisation_role VALUES (6, 3, 3); +INSERT INTO lams_user_organisation_role VALUES (7, 3, 4); +INSERT INTO lams_user_organisation_role VALUES (8, 4, 2); +INSERT INTO lams_user_organisation_role VALUES (9, 4, 3); +INSERT INTO lams_user_organisation_role VALUES (10, 4, 4); +INSERT INTO lams_user_organisation_role VALUES (11, 4, 5); +INSERT INTO lams_user_organisation_role VALUES (12, 5, 4); +INSERT INTO lams_user_organisation_role VALUES (13, 5, 5); +INSERT INTO lams_user_organisation_role VALUES (14, 6, 3); + +INSERT INTO lams_authentication_method VALUES (1, 1, 'LAMS-Database'); +INSERT INTO lams_authentication_method VALUES (2, 2, 'Oxford-WebAuth'); +INSERT INTO lams_authentication_method VALUES (3, 3, 'MQ-LDAP'); + +SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Index: lams_common/db/sql/old/insert_types_data.sql =================================================================== diff -u --- lams_common/db/sql/old/insert_types_data.sql (revision 0) +++ lams_common/db/sql/old/insert_types_data.sql (revision 84be344bfb89ab3182fc4abda27266eddd7662fe) @@ -0,0 +1,62 @@ +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2004-11-26 11:35:47 +# +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2004-11-22 11:44:40 +# +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2004-11-09 15:04:37 +# +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2004-11-09 15:01:18 +# +INSERT INTO lams_role VALUES (1, 'SYSADMIN', 'LAMS System Adminstrator', NOW()); +INSERT INTO lams_role VALUES (2, 'ADMIN', 'Organization Adminstrator', NOW()); +INSERT INTO lams_role VALUES (3, 'AUTHOR', 'Authors Learning Designs', NOW()); +INSERT INTO lams_role VALUES (4, 'STAFF', 'Member of Staff', NOW()); +INSERT INTO lams_role VALUES (5, 'LEARNER', 'Student', NOW()); + +INSERT INTO lams_authentication_method_type VALUES(1, 'LAMS'); +INSERT INTO lams_authentication_method_type VALUES(2, 'WEB_AUTH'); +INSERT INTO lams_authentication_method_type VALUES(3, 'LDAP'); + +INSERT INTO lams_organisation_type VALUES(1, 'ROOT ORGANISATION', 'root all other organisations: controlled by Sysadmin'); +INSERT INTO lams_organisation_type VALUES(2, 'BASE ORGANISATION', 'base organization: represents a real seperate organization sucha s a university '); +INSERT INTO lams_organisation_type VALUES(3, 'SUB-ORGANIZATION', 'sub organization of a base organization'); + +INSERT INTO lams_grouping_type VALUES (1, 'NORMAL'); +INSERT INTO lams_grouping_type VALUES (2, 'CLASS'); + +INSERT INTO lams_learning_activity_type VALUES (1, 'TOOL'); +INSERT INTO lams_learning_activity_type VALUES (2, 'GROUPING_RANDOM'); +INSERT INTO lams_learning_activity_type VALUES (3, 'GROUPING_CHOSEN'); +INSERT INTO lams_learning_activity_type VALUES (4, 'GATE_SYNCH'); +INSERT INTO lams_learning_activity_type VALUES (5, 'GATE_SCHEDULE'); +INSERT INTO lams_learning_activity_type VALUES (6, 'GATE_PERMISSION'); +INSERT INTO lams_learning_activity_type VALUES (7, 'PARALLEL'); +INSERT INTO lams_learning_activity_type VALUES (8, 'OPTIONS'); +INSERT INTO lams_learning_activity_type VALUES (9, 'SEQUENCE'); +INSERT INTO lams_learning_activity_type VALUES (10, 'BRANCH_GROUP_CONTENT'); + +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'); + +INSERT INTO lams_tool_session_state VALUES (1, 'STARTED'); +INSERT INTO lams_tool_session_state VALUES (2, 'FINISHED'); +INSERT INTO lams_tool_session_state VALUES (3, 'SUSPENDED'); +INSERT INTO lams_tool_session_state VALUES (4, 'RESUMED'); +INSERT INTO lams_tool_session_state VALUES (5, 'ERROR'); + +INSERT INTO lams_user_tool_session_state VALUES (1, 'JOINED'); +INSERT INTO lams_user_tool_session_state VALUES (2, 'LEFT' ); +INSERT INTO lams_user_tool_session_state VALUES (3, 'SUSPENDED'); +INSERT INTO lams_user_tool_session_state VALUES (4, 'RESUMED'); +INSERT INTO lams_user_tool_session_state VALUES (5, 'ERROR'); + + + Index: lams_common/db/sql/old/insert_user_management_data.sql =================================================================== diff -u --- lams_common/db/sql/old/insert_user_management_data.sql (revision 0) +++ lams_common/db/sql/old/insert_user_management_data.sql (revision 84be344bfb89ab3182fc4abda27266eddd7662fe) @@ -0,0 +1,26 @@ +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2004-11-22 11:44:40 +# +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2004-11-09 15:04:37 +# +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2004-11-09 15:01:18 +# +INSERT INTO lams_role VALUES (1, 'SYSADMIN', 'LAMS System Adminstrator', NOW()); +INSERT INTO lams_role VALUES (2, 'ADMIN', 'Organization Adminstrator', NOW()); +INSERT INTO lams_role VALUES (3, 'AUTHOR', 'Authors Learning Designs', NOW()); +INSERT INTO lams_role VALUES (4, 'STAFF', 'Member of Staff', NOW()); +INSERT INTO lams_role VALUES (5, 'LEARNER', 'Student', NOW()); + +INSERT INTO lams_authentication_method_type VALUES(1, 'LAMS'); +INSERT INTO lams_authentication_method_type VALUES(2, 'WEB_AUTH'); +INSERT INTO lams_authentication_method_type VALUES(3, 'LDAP'); + +INSERT INTO lams_organisation_type VALUES(1, 'ROOT ORGANISATION', 'root all other organisations: controlled by Sysadmin'); +INSERT INTO lams_organisation_type VALUES(2, 'BASE ORGANISATION', 'base organization: represents a real seperate organization sucha s a university '); +INSERT INTO lams_organisation_type VALUES(3, 'SUB-ORGANIZATION', 'sub organization of a base organization'); + Index: lams_common/db/sql/old/lams_11_scratch.sql =================================================================== diff -u --- lams_common/db/sql/old/lams_11_scratch.sql (revision 0) +++ lams_common/db/sql/old/lams_11_scratch.sql (revision 84be344bfb89ab3182fc4abda27266eddd7662fe) @@ -0,0 +1,547 @@ +CREATE TABLE lams_gate_activity_level ( + gate_activity_level_id INT(11) NOT NULL DEFAULT 0 + , description VARCHAR(128) NOT NULL + , PRIMARY KEY (gate_activity_level_id) +)TYPE=InnoDB; + +CREATE TABLE lams_grouping_type ( + grouping_type_id INT(11) NOT NULL DEFAULT 0 + , description VARCHAR(128) NOT NULL + , PRIMARY KEY (grouping_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_learning_activity_type ( + learning_activity_type_id INT(11) NOT NULL DEFAULT 0 + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (learning_activity_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_learning_library ( + learning_library_id BIGINT(20) NOT NULL DEFAULT 0 + , description TEXT + , title VARCHAR(255) + , create_date_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' + , PRIMARY KEY (learning_library_id) +)TYPE=InnoDB; + +CREATE TABLE lams_organisation_type ( + organisation_type_id INT(3) NOT NULL DEFAULT 0 + , name VARCHAR(64) NOT NULL + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (organisation_type_id) +)TYPE=InnoDB; +CREATE UNIQUE INDEX UQ_lams_organisation_type_name ON lams_organisation_type (name ASC); + +CREATE TABLE lams_role ( + role_id INT(6) NOT NULL DEFAULT 0 + , name VARCHAR(64) NOT NULL + , description TEXT + , create_date BIGINT(20) + , PRIMARY KEY (role_id) +)TYPE=InnoDB; +CREATE INDEX gname ON lams_role (name ASC); + +CREATE TABLE lams_tool_content ( + tool_content_id BIGINT(20) NOT NULL + , tool_content_key BIGINT(20) NOT NULL + , PRIMARY KEY (tool_content_id) +)TYPE=InnoDB; + +CREATE TABLE lams_tool ( + tool_id BIGINT(20) NOT NULL + , learner_url TEXT NOT NULL + , supports_grouping_flag TINYINT(1) NOT NULL DEFAULT 0 + , author_url TEXT + , supports_define_later_flag TINYINT(1) NOT NULL DEFAULT 0 + , define_later_url TEXT + , PRIMARY KEY (tool_id) +)TYPE=InnoDB; + +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) +)TYPE=InnoDB; + +CREATE TABLE lams_user_tool_session_state ( + user_tool_session_state_id INT(3) NOT NULL + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (user_tool_session_state_id) +)TYPE=InnoDB; + +CREATE TABLE lams_authentication_method_type ( + authentication_method_type_id INT(3) NOT NULL DEFAULT 0 + , description VARCHAR(64) NOT NULL + , PRIMARY KEY (authentication_method_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_authentication_method ( + authentication_method_id BIGINT(20) NOT NULL DEFAULT 0 + , authentication_method_type_id INT(3) NOT NULL DEFAULT 0 + , PRIMARY KEY (authentication_method_id) +)TYPE=InnoDB; + +CREATE TABLE lams_workspace_folder ( + workspace_folder_id BIGINT(20) NOT NULL DEFAULT 0 + , parent_folder_id BIGINT(20) + , name VARCHAR(64) NOT NULL + , workspace_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (workspace_folder_id) +)TYPE=InnoDB; + +CREATE TABLE lams_workspace ( + workspace_id BIGINT(20) NOT NULL DEFAULT 0 + , root_folder_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (workspace_id) +)TYPE=InnoDB; + +CREATE TABLE lams_user ( + user_id BIGINT(20) NOT NULL DEFAULT 0 + , login VARCHAR(20) NOT NULL + , password VARCHAR(50) NOT NULL + , title VARCHAR(32) + , first_name VARCHAR(64) + , 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) + , 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 DEFAULT '0000-00-00 00:00:00' + , authentication_method_id BIGINT(20) NOT NULL DEFAULT 0 + , workspace_id BIGINT(20) + , PRIMARY KEY (user_id) +)TYPE=InnoDB; +CREATE UNIQUE INDEX UQ_lams_user_login ON lams_user (login ASC); +CREATE INDEX login ON lams_user (login ASC); + +CREATE TABLE lams_learning_design ( + learning_design_id BIGINT(20) NOT NULL DEFAULT 0 + , id INT(11) + , description TEXT + , title VARCHAR(255) + , first_activity_id BIGINT(20) + , max_id INT(11) + , valid_design_flag TINYINT(4) NOT NULL + , read_only_flag TINYINT(4) NOT NULL + , date_read_only DATETIME + , read_access BIGINT(20) + , write_access BIGINT(20) + , user_id BIGINT(20) NOT NULL + , help_text TEXT + , lesson_copy_flag TINYINT(4) NOT NULL DEFAULT 0 + , create_date_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' + , version VARCHAR(56) NOT NULL + , parent_learning_design_id BIGINT(20) + , open_date_time DATETIME + , close_date_time DATETIME + , PRIMARY KEY (learning_design_id) +)TYPE=InnoDB; +CREATE INDEX idx_design_first_act ON lams_learning_design (first_activity_id ASC); + +CREATE TABLE lams_grouping ( + grouping_id BIGINT(20) NOT NULL DEFAULT 0 + , grouping_type_id INT(11) NOT NULL DEFAULT 0 + , number_of_groups INT(11) + , learners_per_group INT(11) + , staff_group_id BIGINT(20) DEFAULT 0 + , PRIMARY KEY (grouping_id) +)TYPE=InnoDB; + +CREATE TABLE lams_group ( + group_id BIGINT(20) NOT NULL DEFAULT 0 + , grouping_id BIGINT(20) NOT NULL DEFAULT 0 + , order_id INT(6) NOT NULL DEFAULT 1 + , PRIMARY KEY (group_id) +)TYPE=InnoDB; + +CREATE TABLE lams_organisation ( + organisation_id BIGINT(20) NOT NULL DEFAULT 0 + , name VARCHAR(250) + , description VARCHAR(250) + , parent_organisation_id BIGINT(20) + , organisation_type_id INT(3) NOT NULL DEFAULT 0 + , create_date DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' + , workspace_id BIGINT(20) + , PRIMARY KEY (organisation_id) +)TYPE=InnoDB; + +CREATE TABLE lams_learning_activity ( + activity_id BIGINT(20) NOT NULL DEFAULT 0 + , id INT(11) + , description TEXT + , title VARCHAR(255) + , xcoord INT(11) + , ycoord INT(11) + , parent_activity_id BIGINT(20) + , learning_activity_type_id INT(11) NOT NULL DEFAULT 0 + , grouping_id BIGINT(20) + , order_id INT(11) + , define_later_flag TINYINT(4) NOT NULL DEFAULT 0 + , learning_design_id BIGINT(20) DEFAULT 0 + , learning_library_id BIGINT(20) DEFAULT 0 + , create_date_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' + , offline_instructions TEXT + , max_number_of_options INT(5) + , min_number_of_options INT(5) + , tool_id BIGINT(20) + , tool_content_id BIGINT(20) + , gate_activity_level_id INT(11) DEFAULT 0 + , gate_start_date_time DATETIME + , gate_end_date_time DATETIME + , PRIMARY KEY (activity_id) +)TYPE=InnoDB; + +CREATE TABLE lams_user_organisation ( + user_organisation_id BIGINT(20) NOT NULL DEFAULT 0 + , organisation_id BIGINT(20) NOT NULL DEFAULT 0 + , user_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (user_organisation_id) +)TYPE=InnoDB; + +CREATE TABLE lams_lesson ( + lesson_id BIGINT(20) NOT NULL + , learning_design_id BIGINT(20) NOT NULL DEFAULT 0 + , user_id BIGINT(20) NOT NULL DEFAULT 0 + , create_date_time DATETIME NOT NULL + , PRIMARY KEY (lesson_id) +)TYPE=InnoDB; + +CREATE TABLE lams_learner_progress ( + user_id BIGINT(20) NOT NULL DEFAULT 0 + , lesson_id BIGINT(20) NOT NULL + , learner_progress_id BIGINT(20) NOT NULL + , PRIMARY KEY (learner_progress_id) +)TYPE=InnoDB; + +CREATE TABLE lams_tool_session ( + tool_session_id BIGINT(20) NOT NULL + , group_id BIGINT(20) DEFAULT 0 + , activity_id BIGINT(20) NOT NULL DEFAULT 0 + , tool_session_key BIGINT(20) NOT NULL + , tool_session_state_id INT(3) NOT NULL + , PRIMARY KEY (tool_session_id) +)TYPE=InnoDB; + +CREATE TABLE lams_learning_transition ( + transition_id BIGINT(20) NOT NULL DEFAULT 0 + , id INT(11) + , description TEXT + , title VARCHAR(255) + , to_activity_id BIGINT(20) NOT NULL DEFAULT 0 + , from_activity_id BIGINT(20) NOT NULL DEFAULT 0 + , learning_design_id BIGINT(20) NOT NULL DEFAULT 0 + , create_date_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' + , PRIMARY KEY (transition_id) +)TYPE=InnoDB; + +CREATE TABLE lams_user_organisation_role ( + user_organisation_role_id BIGINT(20) NOT NULL DEFAULT 0 + , user_organisation_id BIGINT(20) NOT NULL DEFAULT 0 + , role_id INT(6) NOT NULL DEFAULT 0 + , PRIMARY KEY (user_organisation_role_id) +)TYPE=InnoDB; + +CREATE TABLE lams_progress_completed ( + learner_progress_id BIGINT(20) NOT NULL + , activity_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (learner_progress_id, activity_id) +)TYPE=InnoDB; + +CREATE TABLE lams_progress_current ( + learner_progress_id BIGINT(20) NOT NULL + , activity_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (learner_progress_id, activity_id) +)TYPE=InnoDB; + +CREATE TABLE lams_user_tool_session ( + tool_session_id BIGINT(20) NOT NULL + , user_id BIGINT(20) NOT NULL DEFAULT 0 + , user_tool_session_state_id INT(3) NOT NULL + , PRIMARY KEY (tool_session_id, user_id) +)TYPE=InnoDB; + +CREATE TABLE lams_user_group ( + user_id BIGINT(20) NOT NULL DEFAULT 0 + , group_id BIGINT(20) NOT NULL DEFAULT 0 + , PRIMARY KEY (user_id, group_id) +)TYPE=InnoDB; + +CREATE TABLE lams_authentication_method_parameter ( + authentication_parameter_id BIGINT(20) NOT NULL DEFAULT 0 + , authentication_method_id BIGINT(20) NOT NULL DEFAULT 0 + , name VARCHAR(128) NOT NULL + , value VARCHAR(255) + , PRIMARY KEY (authentication_parameter_id) +)TYPE=InnoDB; + +ALTER TABLE lams_authentication_method + ADD CONSTRAINT FK_lams_authorization_method_1 + FOREIGN KEY (authentication_method_type_id) + REFERENCES lams_authentication_method_type (authentication_method_type_id) + ON DELETE NO ACTION + ON UPDATE NO ACTION; + +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; + +ALTER TABLE lams_workspace + ADD CONSTRAINT FK_lams_workspace_1 + FOREIGN KEY (root_folder_id) + REFERENCES lams_workspace_folder (workspace_folder_id) + ON DELETE NO ACTION + ON UPDATE NO ACTION; + +ALTER TABLE lams_user + ADD 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; + +ALTER TABLE lams_user + ADD CONSTRAINT FK_lams_user_2 + FOREIGN KEY (workspace_id) + REFERENCES lams_workspace (workspace_id) + ON DELETE NO ACTION + ON UPDATE NO ACTION; + +ALTER TABLE lams_learning_design + ADD CONSTRAINT FK_lams_learning_design_2 + FOREIGN KEY (parent_learning_design_id) + REFERENCES lams_learning_design (learning_design_id) + ON DELETE NO ACTION + ON UPDATE NO ACTION; + +ALTER TABLE lams_learning_design + ADD CONSTRAINT FK_lams_learning_design_3 + FOREIGN KEY (user_id) + REFERENCES lams_user (user_id); + +ALTER TABLE lams_grouping + ADD 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; + +ALTER TABLE lams_group + ADD CONSTRAINT FK_lams_learning_group_1 + FOREIGN KEY (grouping_id) + REFERENCES lams_grouping (grouping_id) + ON DELETE NO ACTION + ON UPDATE NO ACTION; + +ALTER TABLE lams_organisation + ADD 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; + +ALTER TABLE lams_organisation + ADD CONSTRAINT FK_lams_organisation_2 + FOREIGN KEY (workspace_id) + REFERENCES lams_workspace (workspace_id) + ON DELETE NO ACTION + ON UPDATE NO ACTION; + +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; + +ALTER TABLE lams_learning_activity + ADD 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; + +ALTER TABLE lams_learning_activity + ADD 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; + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_learning_activity_2 + FOREIGN KEY (parent_activity_id) + REFERENCES lams_learning_activity (activity_id) + ON DELETE NO ACTION + ON UPDATE NO ACTION; + +ALTER TABLE lams_learning_activity + ADD 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; + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_learning_activity_6 + FOREIGN KEY (grouping_id) + REFERENCES lams_grouping (grouping_id) + ON DELETE NO ACTION + ON UPDATE NO ACTION; + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_lams_learning_activity_8 + FOREIGN KEY (tool_id) + REFERENCES lams_tool (tool_id); + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_lams_learning_activity_9 + FOREIGN KEY (tool_content_id) + REFERENCES lams_tool_content (tool_content_id); + +ALTER TABLE lams_learning_activity + ADD CONSTRAINT FK_lams_learning_activity_10 + FOREIGN KEY (gate_activity_level_id) + REFERENCES lams_gate_activity_level (gate_activity_level_id); + +ALTER TABLE lams_user_organisation + ADD CONSTRAINT u_user_organisation_ibfk_1 + FOREIGN KEY (user_id) + REFERENCES lams_user (user_id) + ON DELETE NO ACTION + ON UPDATE NO ACTION; + +ALTER TABLE lams_user_organisation + ADD CONSTRAINT u_user_organisation_ibfk_2 + FOREIGN KEY (organisation_id) + REFERENCES lams_organisation (organisation_id) + ON DELETE NO ACTION + ON UPDATE NO ACTION; + +ALTER TABLE lams_lesson + ADD CONSTRAINT FK_lams_lesson_1_1 + FOREIGN KEY (learning_design_id) + REFERENCES lams_learning_design (learning_design_id); + +ALTER TABLE lams_lesson + ADD CONSTRAINT FK_lams_lesson_2 + FOREIGN KEY (user_id) + REFERENCES lams_user (user_id); + +ALTER TABLE lams_learner_progress + ADD CONSTRAINT FK_lams_learner_progress_1 + FOREIGN KEY (user_id) + REFERENCES lams_user (user_id); + +ALTER TABLE lams_learner_progress + ADD CONSTRAINT FK_lams_learner_progress_2 + FOREIGN KEY (lesson_id) + REFERENCES lams_lesson (lesson_id); + +ALTER TABLE lams_tool_session + ADD CONSTRAINT FK_lams_tool_session_1 + FOREIGN KEY (group_id) + REFERENCES lams_group (group_id); + +ALTER TABLE lams_tool_session + ADD CONSTRAINT FK_lams_tool_session_4 + FOREIGN KEY (tool_session_state_id) + REFERENCES lams_tool_session_state (tool_session_state_id); + +ALTER TABLE lams_learning_transition + ADD CONSTRAINT FK_learning_transition_3 + FOREIGN KEY (from_activity_id) + REFERENCES lams_learning_activity (activity_id) + ON DELETE NO ACTION + ON UPDATE NO ACTION; + +ALTER TABLE lams_learning_transition + ADD CONSTRAINT FK_learning_transition_2 + FOREIGN KEY (to_activity_id) + REFERENCES lams_learning_activity (activity_id) + ON DELETE NO ACTION + ON UPDATE NO ACTION; + +ALTER TABLE lams_learning_transition + ADD 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_user_organisation_role + ADD CONSTRAINT FK_lams_user_organisation_role_2 + FOREIGN KEY (role_id) + REFERENCES lams_role (role_id) + ON DELETE NO ACTION + ON UPDATE NO ACTION; + +ALTER TABLE lams_user_organisation_role + ADD 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; + +ALTER TABLE lams_progress_completed + ADD CONSTRAINT FK_lams_progress_completed_1 + FOREIGN KEY (learner_progress_id) + REFERENCES lams_learner_progress (learner_progress_id); + +ALTER TABLE lams_progress_completed + ADD CONSTRAINT FK_lams_progress_completed_2 + FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_progress_current + ADD CONSTRAINT FK_lams_progress_current_1 + FOREIGN KEY (learner_progress_id) + REFERENCES lams_learner_progress (learner_progress_id); + +ALTER TABLE lams_progress_current + ADD CONSTRAINT FK_lams_progress_current_2 + FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id); + +ALTER TABLE lams_user_tool_session + ADD CONSTRAINT FK_lams_user_tool_session_1 + FOREIGN KEY (tool_session_id) + REFERENCES lams_tool_session (tool_session_id); + +ALTER TABLE lams_user_tool_session + ADD CONSTRAINT FK_lams_user_tool_session_2 + FOREIGN KEY (user_id) + REFERENCES lams_user (user_id); + +ALTER TABLE lams_user_tool_session + ADD CONSTRAINT FK_lams_user_tool_session_3 + FOREIGN KEY (user_tool_session_state_id) + REFERENCES lams_user_tool_session_state (user_tool_session_state_id); + +ALTER TABLE lams_user_group + ADD CONSTRAINT FK_lams_user_group_1 + FOREIGN KEY (user_id) + REFERENCES lams_user (user_id); + +ALTER TABLE lams_user_group + ADD CONSTRAINT FK_lams_user_group_2 + FOREIGN KEY (group_id) + REFERENCES lams_group (group_id); + +ALTER TABLE lams_authentication_method_parameter + ADD CONSTRAINT FK_lams_authorization_method_parameter_1 + FOREIGN KEY (authentication_method_id) + REFERENCES lams_authentication_method (authentication_method_id) + ON DELETE NO ACTION + ON UPDATE NO ACTION; + Index: lams_common/db/sql/old/tables.txt =================================================================== diff -u --- lams_common/db/sql/old/tables.txt (revision 0) +++ lams_common/db/sql/old/tables.txt (revision 84be344bfb89ab3182fc4abda27266eddd7662fe) @@ -0,0 +1,43 @@ +# MySQLCC - [ROOT LOCAL] Query Window +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2004-11-24 14:12:25 +# +# Query: +# show tables +# +'Tables_in_scratch' +'lams_authentication_method' +'lams_authentication_method_parameter' +'lams_authentication_method_type' +'lams_gate_activity_level' +'lams_grouping_type' +'lams_learner_progress' +'lams_learning_activity' +'lams_learning_activity_type' +'lams_learning_design' +'lams_learning_gate_activity' +'lams_learning_group' +'lams_learning_grouping' +'lams_learning_grouping_activity' +'lams_learning_library' +'lams_learning_options_activity' +'lams_learning_tool_activity' +'lams_learning_transition' +'lams_lesson' +'lams_organisation' +'lams_organisation_type' +'lams_progress_completed' +'lams_progress_current' +'lams_role' +'lams_tool' +'lams_tool_content' +'lams_tool_session' +'lams_tool_session_state' +'lams_user' +'lams_user_organisation' +'lams_user_organisation_role' +'lams_user_tool_session' +'lams_user_tool_session_state' +'lams_workspace' +'lams_workspace_folder'