Index: lams_common/db/model/lams_11.clay =================================================================== RCS file: /usr/local/cvsroot/lams_common/db/model/lams_11.clay,v diff -u -r1.6 -r1.7 --- lams_common/db/model/lams_11.clay 6 Dec 2004 05:42:03 -0000 1.6 +++ lams_common/db/model/lams_11.clay 14 Dec 2004 05:57:57 -0000 1.7 @@ -186,9 +186,9 @@ - + - + @@ -519,7 +519,7 @@
- + @@ -557,9 +557,9 @@
- + - + @@ -795,9 +795,9 @@
- + - + @@ -864,9 +864,9 @@
- + - + @@ -961,7 +961,7 @@ - + @@ -1011,7 +1011,7 @@ - + @@ -1147,7 +1147,7 @@ - + @@ -1318,7 +1318,7 @@
- + @@ -1381,7 +1381,7 @@ - + @@ -1633,7 +1633,7 @@ - + @@ -1711,9 +1711,9 @@
- + - + @@ -1793,7 +1793,7 @@ - + @@ -1849,7 +1849,7 @@ - + @@ -1923,9 +1923,9 @@
- + - + @@ -1986,8 +1986,8 @@ - + @@ -2011,8 +2011,8 @@ - + @@ -2068,9 +2068,9 @@
- + - + @@ -2083,7 +2083,7 @@ - + @@ -2096,7 +2096,7 @@ - + @@ -2109,6 +2109,31 @@ + + + + + + + + + + + + + + + + + + + + + + + + + @@ -2137,7 +2162,7 @@ - + @@ -2150,12 +2175,46 @@ - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + @@ -2169,12 +2228,6 @@ - - - - - - @@ -2188,52 +2241,31 @@ - + - + - - - - - - - - - + + - + - - - - - - - - - - - - - - - + - + @@ -2244,15 +2276,24 @@ - + + + + + + + + + +
- + - + @@ -2349,7 +2390,7 @@
- + @@ -2404,7 +2445,7 @@
- + @@ -2459,7 +2500,7 @@
- + @@ -2567,7 +2608,7 @@
- + @@ -2655,13 +2696,13 @@
- + - + @@ -2686,15 +2727,15 @@
- +
- + - + - + @@ -2703,7 +2744,7 @@ - + @@ -2717,25 +2758,19 @@ - + + - + - + - - - - - - -
Index: lams_common/db/sql/create_lams_11_tables.sql =================================================================== RCS file: /usr/local/cvsroot/lams_common/db/sql/create_lams_11_tables.sql,v diff -u -r1.6 -r1.7 --- lams_common/db/sql/create_lams_11_tables.sql 12 Dec 2004 05:24:13 -0000 1.6 +++ lams_common/db/sql/create_lams_11_tables.sql 14 Dec 2004 05:57:57 -0000 1.7 @@ -1,6 +1,6 @@ # Connection: ROOT LOCAL # Host: localhost -# Saved: 2004-12-06 16:37:52 +# Saved: 2004-12-14 16:57:02 # CREATE TABLE lams_gate_activity_level ( gate_activity_level_id INT(11) NOT NULL DEFAULT 0 @@ -21,7 +21,7 @@ )TYPE=InnoDB; CREATE TABLE lams_learning_library ( - learning_library_id BIGINT(20) NOT NULL DEFAULT 0 + 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' @@ -46,18 +46,21 @@ CREATE INDEX gname ON lams_role (name ASC); 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 - , default_tool_content_id BIGINT(20) NOT NULL + 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 - , class_name TEXT NOT NULL + , 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; @@ -96,7 +99,7 @@ )TYPE=InnoDB; CREATE TABLE lams_workspace_folder ( - workspace_folder_id BIGINT(20) NOT NULL DEFAULT 0 + 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 @@ -107,7 +110,7 @@ )TYPE=InnoDB; CREATE TABLE lams_workspace ( - workspace_id BIGINT(20) NOT NULL DEFAULT 0 + 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) @@ -116,9 +119,8 @@ )TYPE=InnoDB; - CREATE TABLE lams_grouping ( - grouping_id BIGINT(20) NOT NULL DEFAULT 0 + 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) @@ -130,7 +132,7 @@ )TYPE=InnoDB; CREATE TABLE lams_group ( - group_id BIGINT(20) NOT NULL DEFAULT 0 + 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) @@ -140,7 +142,7 @@ )TYPE=InnoDB; CREATE TABLE lams_organisation ( - organisation_id BIGINT(20) NOT NULL DEFAULT 0 + organisation_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT , name VARCHAR(250) , description VARCHAR(250) , parent_organisation_id BIGINT(20) @@ -160,7 +162,7 @@ )TYPE=InnoDB; CREATE TABLE lams_user ( - user_id BIGINT(20) NOT NULL DEFAULT 0 + user_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT , login VARCHAR(20) NOT NULL , password VARCHAR(50) NOT NULL , title VARCHAR(32) @@ -181,7 +183,8 @@ , 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) + , 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) @@ -197,7 +200,7 @@ CREATE INDEX login ON lams_user (login ASC); CREATE TABLE lams_learning_design ( - learning_design_id BIGINT(20) NOT NULL DEFAULT 0 + learning_design_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT , id INT(11) , description TEXT , title VARCHAR(255) @@ -226,8 +229,9 @@ )TYPE=InnoDB; CREATE INDEX idx_design_first_act ON lams_learning_design (first_activity_id ASC); + CREATE TABLE lams_learning_activity ( - activity_id BIGINT(20) NOT NULL DEFAULT 0 + activity_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT , id INT(11) , description TEXT , title VARCHAR(255) @@ -274,7 +278,7 @@ )TYPE=InnoDB; CREATE TABLE lams_user_organisation ( - user_organisation_id BIGINT(20) NOT NULL DEFAULT 0 + 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) @@ -287,7 +291,7 @@ )TYPE=InnoDB; CREATE TABLE lams_lesson ( - lesson_id BIGINT(20) NOT NULL + 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 @@ -315,9 +319,12 @@ )TYPE=InnoDB; CREATE TABLE lams_learner_progress ( - user_id BIGINT(20) NOT NULL DEFAULT 0 + learner_progress_id BIGINT(20) NOT NULL AUTO_INCREMENT + , user_id BIGINT(20) NOT NULL DEFAULT 0 , lesson_id BIGINT(20) NOT NULL - , learner_progress_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) @@ -328,7 +335,7 @@ )TYPE=InnoDB; CREATE TABLE lams_tool_session ( - tool_session_id BIGINT(20) NOT NULL + 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 @@ -344,7 +351,7 @@ )TYPE=InnoDB; CREATE TABLE lams_user_organisation_role ( - user_organisation_role_id BIGINT(20) NOT NULL DEFAULT 0 + 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) @@ -408,19 +415,17 @@ REFERENCES lams_group (group_id) )TYPE=InnoDB; -CREATE TABLE lams_lesson_learner ( - lesson_id BIGINT(20) NOT NULL - , user_id BIGINT(20) NOT NULL DEFAULT 0 - , INDEX (lesson_id) - , CONSTRAINT FK_lams_lesson_learner_1 FOREIGN KEY (lesson_id) - REFERENCES lams_lesson (lesson_id) - , INDEX (user_id) - , CONSTRAINT FK_lams_lesson_learner_2 FOREIGN KEY (user_id) - REFERENCES lams_user (user_id) +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_learning_transition ( - transition_id BIGINT(20) NOT NULL DEFAULT 0 + transition_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT , id INT(11) , description TEXT , title VARCHAR(255) Index: lams_common/db/sql/drop_lams_11_tables.sql =================================================================== RCS file: /usr/local/cvsroot/lams_common/db/sql/drop_lams_11_tables.sql,v diff -u -r1.3 -r1.4 --- lams_common/db/sql/drop_lams_11_tables.sql 6 Dec 2004 05:42:03 -0000 1.3 +++ lams_common/db/sql/drop_lams_11_tables.sql 14 Dec 2004 05:57:57 -0000 1.4 @@ -48,7 +48,7 @@ DROP INDEX idx_transition_from_activity ON lams_learning_transition; DROP TABLE lams_learning_transition; -DROP TABLE lams_lesson_learner; +DROP TABLE lams_tool_content; DROP TABLE lams_user_group; DROP TABLE lams_user_tool_session; DROP TABLE lams_progress_current; @@ -110,3 +110,279 @@ +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_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_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_user + ADD CONSTRAINT FK_lams_user_3 + FOREIGN KEY (base_organisation_id) + REFERENCES lams_organisation (organisation_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) + 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_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_lesson + ADD CONSTRAINT FK_lams_lesson_3 + FOREIGN KEY (organisation_id) + REFERENCES lams_organisation (organisation_id); + +ALTER TABLE lams_lesson + ADD CONSTRAINT FK_lams_lesson_4 + FOREIGN KEY (lams_lesson_state_id) + REFERENCES lams_lesson_state (lams_lesson_state_id); + +ALTER TABLE lams_lesson + ADD CONSTRAINT FK_lams_lesson_5 + FOREIGN KEY (class_grouping_id) + REFERENCES lams_grouping (grouping_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_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_tool_content + ADD CONSTRAINT FK_lams_tool_content_1 + FOREIGN KEY (tool_id) + REFERENCES lams_tool (tool_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; +