Index: lams_common/db/sql/create_lams_db.sql =================================================================== diff -u -rdfdab93ee3c3a78cb054e114e844039482f35080 -r7b8226a1234ecedac9073ffd3f6f7081104d3d6d --- lams_common/db/sql/create_lams_db.sql (.../create_lams_db.sql) (revision dfdab93ee3c3a78cb054e114e844039482f35080) +++ lams_common/db/sql/create_lams_db.sql (.../create_lams_db.sql) (revision 7b8226a1234ecedac9073ffd3f6f7081104d3d6d) @@ -41,6 +41,11 @@ 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 @@ -64,7 +69,7 @@ DROP TABLE IF EXISTS lams_learning_library; 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' @@ -92,18 +97,21 @@ DROP TABLE IF EXISTS lams_tool; 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; @@ -148,7 +156,7 @@ DROP TABLE IF EXISTS lams_workspace_folder; 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 @@ -160,18 +168,17 @@ DROP TABLE IF EXISTS lams_workspace; 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) , 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 + 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) @@ -184,7 +191,7 @@ DROP TABLE IF EXISTS lams_group; 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) @@ -195,7 +202,7 @@ DROP TABLE IF EXISTS lams_organisation; 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) @@ -216,7 +223,7 @@ DROP TABLE IF EXISTS lams_user; 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) @@ -237,7 +244,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) @@ -254,7 +262,7 @@ DROP TABLE IF EXISTS lams_learning_design; 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) @@ -285,7 +293,7 @@ DROP TABLE IF EXISTS lams_learning_activity; 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) @@ -333,7 +341,7 @@ DROP TABLE IF EXISTS lams_user_organisation; 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) @@ -347,7 +355,7 @@ DROP TABLE IF EXISTS lams_lesson; 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 @@ -376,9 +384,12 @@ DROP TABLE IF EXISTS lams_learner_progress; 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) @@ -390,7 +401,7 @@ DROP TABLE IF EXISTS lams_tool_session; 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 @@ -407,7 +418,7 @@ DROP TABLE IF EXISTS lams_user_organisation_role; 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) @@ -475,21 +486,19 @@ REFERENCES lams_group (group_id) )TYPE=InnoDB; -DROP TABLE IF EXISTS lams_lesson_learner; -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) +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 + transition_id BIGINT(20) NOT NULL DEFAULT 0 AUTO_INCREMENT , id INT(11) , description TEXT , title VARCHAR(255) @@ -509,6 +518,7 @@ 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());