Index: lams_tool_deploy/test/file/sql/lams_common/create_lams_11_tables.sql =================================================================== diff -u -rbba5edf85e23d78559441e9c8b647756eeef4368 -r2dc5d899da948b767363372a7c8349a760e64ec9 --- lams_tool_deploy/test/file/sql/lams_common/create_lams_11_tables.sql (.../create_lams_11_tables.sql) (revision bba5edf85e23d78559441e9c8b647756eeef4368) +++ lams_tool_deploy/test/file/sql/lams_common/create_lams_11_tables.sql (.../create_lams_11_tables.sql) (revision 2dc5d899da948b767363372a7c8349a760e64ec9) @@ -93,7 +93,6 @@ credential_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT , name VARCHAR(255) NOT NULL , password VARCHAR(255) NOT NULL - , wc_id BIGINT(20) UNSIGNED NOT NULL , PRIMARY KEY (credential_id) )TYPE=InnoDB; ALTER TABLE lams_cr_credential COMMENT='Records the identification properties for a tool.'; @@ -116,6 +115,12 @@ , PRIMARY KEY (log_event_type_id) )TYPE=InnoDB; +CREATE TABLE lams_workspace_folder_content_type ( + content_type_id INT(3) NOT NULL AUTO_INCREMENT + , description VARCHAR(64) NOT NULL + , PRIMARY KEY (content_type_id) +)TYPE=InnoDB; + CREATE TABLE lams_authentication_method_type ( authentication_method_type_id INT(3) NOT NULL , description VARCHAR(64) NOT NULL @@ -181,6 +186,42 @@ REFERENCES lams_organisation (organisation_id) ON DELETE NO ACTION ON UPDATE NO ACTION )TYPE=InnoDB; +CREATE TABLE lams_css_theme_ve ( + theme_ve_id BIGINT(20) NOT NULL AUTO_INCREMENT + , name VARCHAR(100) NOT NULL + , description VARCHAR(100) + , parent_id BIGINT(20) + , theme_flag TINYINT(1) NOT NULL DEFAULT 0 + , PRIMARY KEY (theme_ve_id) + , INDEX (parent_id) + , CONSTRAINT FK_lams_css_theme_ve_2 FOREIGN KEY (parent_id) + REFERENCES lams_css_theme_ve (theme_ve_id) ON DELETE NO ACTION ON UPDATE NO ACTION +)TYPE=InnoDB; +ALTER TABLE lams_css_theme_ve COMMENT='Stores both the Flash theme and visual element'; + +CREATE TABLE lams_css_style ( + style_id BIGINT(20) NOT NULL AUTO_INCREMENT + , theme_ve_id BIGINT(20) NOT NULL + , PRIMARY KEY (style_id) + , INDEX (theme_ve_id) + , CONSTRAINT FK_lams_css_style_1 FOREIGN KEY (theme_ve_id) + REFERENCES lams_css_theme_ve (theme_ve_id) ON DELETE NO ACTION ON UPDATE NO ACTION +)TYPE=InnoDB; +ALTER TABLE lams_css_style COMMENT='Groups lams_css_property into a CSSStyleDeclaration.'; + +CREATE TABLE lams_css_property ( + property_id BIGINT(20) NOT NULL AUTO_INCREMENT + , style_id BIGINT(20) NOT NULL + , name VARCHAR(255) NOT NULL + , value VARCHAR(100) NOT NULL + , style_subset VARCHAR(20) + , type TINYINT NOT NULL + , PRIMARY KEY (property_id) + -- , INDEX (style_id) + -- , CONSTRAINT FK_lams_css_property_1 FOREIGN KEY (style_id) + -- REFERENCES lams_css_style (style_id) ON DELETE NO ACTION ON UPDATE NO ACTION +)TYPE=InnoDB; + CREATE TABLE lams_user ( user_id BIGINT(20) NOT NULL AUTO_INCREMENT , login VARCHAR(20) NOT NULL @@ -204,6 +245,9 @@ , authentication_method_id BIGINT(20) NOT NULL DEFAULT 0 , workspace_id BIGINT(20) , base_organisation_id BIGINT(20) NOT NULL DEFAULT 0 + , locale_language CHAR(2) NOT NULL DEFAULT 'en' + , locale_country CHAR(2) + , theme_id BIGINT(20) , PRIMARY KEY (user_id) , INDEX (authentication_method_id) , CONSTRAINT FK_lams_user_1 FOREIGN KEY (authentication_method_id) @@ -213,7 +257,10 @@ 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) + REFERENCES lams_organisation (organisation_id) ON DELETE NO ACTION ON UPDATE NO ACTION + , INDEX (theme_id) + , CONSTRAINT FK_lams_user_4 FOREIGN KEY (theme_id) + REFERENCES lams_css_theme_ve (theme_ve_id) ON DELETE NO ACTION ON UPDATE NO ACTION )TYPE=InnoDB; CREATE UNIQUE INDEX UQ_lams_user_login ON lams_user (login ASC); CREATE INDEX login ON lams_user (login ASC); @@ -230,6 +277,8 @@ , date_read_only DATETIME , user_id BIGINT(20) NOT NULL , help_text TEXT + , online_instructions TEXT + , offline_instructions TEXT , copy_type_id TINYINT(4) NOT NULL , create_date_time DATETIME NOT NULL , version VARCHAR(56) @@ -287,57 +336,14 @@ 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 AUTO_INCREMENT - , organisation_id BIGINT(20) NOT NULL - , user_id BIGINT(20) NOT NULL - , PRIMARY KEY (user_organisation_id) - , INDEX (user_id) - , CONSTRAINT FK_lams_user_organisation_1 FOREIGN KEY (user_id) - REFERENCES lams_user (user_id) ON DELETE NO ACTION ON UPDATE NO ACTION - , INDEX (organisation_id) - , CONSTRAINT FK_lams_user_organisation_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 - , user_id BIGINT(20) NOT NULL - , name VARCHAR(255) NOT NULL - , description TEXT - , create_date_time DATETIME NOT NULL - , organisation_id BIGINT(20) - , class_grouping_id BIGINT(20) - , lesson_state_id INT(3) NOT NULL - , start_date_time DATETIME - , schedule_start_date_time DATETIME - , end_date_time DATETIME - , schedule_end_date_time DATETIME - , 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 (lesson_state_id) - , CONSTRAINT FK_lams_lesson_4 FOREIGN KEY (lesson_state_id) - REFERENCES lams_lesson_state (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_tool ( tool_id BIGINT(20) NOT NULL AUTO_INCREMENT , tool_signature VARCHAR(64) NOT NULL , service_name VARCHAR(255) NOT NULL , tool_display_name VARCHAR(255) NOT NULL , description TEXT + , tool_identifier VARCHAR(64) NOT NULL + , tool_version VARCHAR(10) NOT NULL , learning_library_id BIGINT(20) , default_tool_content_id BIGINT(20) , valid_flag TINYINT(1) NOT NULL DEFAULT 1 @@ -386,8 +392,6 @@ , learning_library_id BIGINT(20) , create_date_time DATETIME NOT NULL , run_offline_flag TINYINT(1) NOT NULL - , offline_instructions TEXT - , online_instructions TEXT , max_number_of_options INT(5) , min_number_of_options INT(5) , options_instructions TEXT @@ -440,6 +444,52 @@ REFERENCES lams_grouping_support_type (grouping_support_type_id) )TYPE=InnoDB; +CREATE TABLE lams_lesson ( + lesson_id BIGINT(20) NOT NULL AUTO_INCREMENT + , learning_design_id BIGINT(20) NOT NULL + , user_id BIGINT(20) NOT NULL + , name VARCHAR(255) NOT NULL + , description TEXT + , create_date_time DATETIME NOT NULL + , organisation_id BIGINT(20) + , class_grouping_id BIGINT(20) + , lesson_state_id INT(3) NOT NULL + , start_date_time DATETIME + , schedule_start_date_time DATETIME + , end_date_time DATETIME + , schedule_end_date_time DATETIME + , 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 (lesson_state_id) + , CONSTRAINT FK_lams_lesson_4 FOREIGN KEY (lesson_state_id) + REFERENCES lams_lesson_state (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_user_organisation ( + user_organisation_id BIGINT(20) NOT NULL AUTO_INCREMENT + , organisation_id BIGINT(20) NOT NULL + , user_id BIGINT(20) NOT NULL + , PRIMARY KEY (user_organisation_id) + , INDEX (organisation_id) + , CONSTRAINT FK_lams_user_organisation_1 FOREIGN KEY (organisation_id) + REFERENCES lams_organisation (organisation_id) ON DELETE NO ACTION ON UPDATE NO ACTION + , INDEX (user_id) + , CONSTRAINT FK_lams_user_organisation_2 FOREIGN KEY (user_id) + REFERENCES lams_user (user_id) ON DELETE NO ACTION ON UPDATE NO ACTION +)TYPE=InnoDB; + CREATE TABLE lams_learner_progress ( learner_progress_id BIGINT(20) NOT NULL AUTO_INCREMENT , user_id BIGINT(20) NOT NULL @@ -499,6 +549,7 @@ )TYPE=InnoDB; ALTER TABLE lams_cr_node_version COMMENT='Represents a version of a node'; + CREATE TABLE lams_user_organisation_role ( user_organisation_role_id BIGINT(20) NOT NULL AUTO_INCREMENT , user_organisation_id BIGINT(20) NOT NULL @@ -524,15 +575,9 @@ , unique_key VARCHAR(128) NOT NULL , UNIQUE UQ_lams_tool_session_1 (unique_key) , 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) - , INDEX (group_id) - , CONSTRAINT FK_lams_tool_session_3 FOREIGN KEY (group_id) - REFERENCES lams_group (group_id) , INDEX (user_id) , CONSTRAINT FK_lams_tool_session_5 FOREIGN KEY (user_id) REFERENCES lams_user (user_id) @@ -542,6 +587,9 @@ , INDEX (activity_id) , CONSTRAINT FK_lams_tool_session_8 FOREIGN KEY (activity_id) REFERENCES lams_learning_activity (activity_id) + , INDEX (group_id) + , CONSTRAINT FK_lams_tool_session_1 FOREIGN KEY (group_id) + REFERENCES lams_group (group_id) )TYPE=InnoDB; CREATE TABLE lams_progress_completed ( @@ -650,6 +698,28 @@ REFERENCES lams_log_event_type (log_event_type_id) )TYPE=InnoDB; +CREATE TABLE lams_workspace_folder_content ( + folder_content_id BIGINT(20) NOT NULL AUTO_INCREMENT + , content_type_id INT(3) NOT NULL + , name VARCHAR(64) NOT NULL + , description VARCHAR(64) NOT NULL + , create_date_time DATETIME NOT NULL + , last_modified_date DATETIME NOT NULL + , workspace_folder_id BIGINT(20) NOT NULL + , uuid BIGINT(20) + , version_id BIGINT(20) + , mime_type VARCHAR(10) NOT NULL + , UNIQUE unique_content_name (name, workspace_folder_id, mime_type) + , UNIQUE unique_node_version (workspace_folder_id, uuid, version_id) + , PRIMARY KEY (folder_content_id) + , INDEX (workspace_folder_id) + , CONSTRAINT FK_lams_workspace_folder_content_1 FOREIGN KEY (workspace_folder_id) + REFERENCES lams_workspace_folder (workspace_folder_id) + , INDEX (content_type_id) + , CONSTRAINT FK_lams_workspace_folder_content_2 FOREIGN KEY (content_type_id) + REFERENCES lams_workspace_folder_content_type (content_type_id) +)TYPE=InnoDB; + CREATE TABLE lams_learning_transition ( transition_id BIGINT(20) NOT NULL AUTO_INCREMENT , transition_ui_id INT(11) Index: lams_tool_deploy/test/file/sql/lams_common/drop_lams_11_tables.sql =================================================================== diff -u -r922473f5ee7930d8f2ddbf3f3e2d1d00cd9deee9 -r2dc5d899da948b767363372a7c8349a760e64ec9 --- lams_tool_deploy/test/file/sql/lams_common/drop_lams_11_tables.sql (.../drop_lams_11_tables.sql) (revision 922473f5ee7930d8f2ddbf3f3e2d1d00cd9deee9) +++ lams_tool_deploy/test/file/sql/lams_common/drop_lams_11_tables.sql (.../drop_lams_11_tables.sql) (revision 2dc5d899da948b767363372a7c8349a760e64ec9) @@ -1,29 +1,3 @@ -# Connection: ROOT LOCAL -# Host: localhost -# Saved: 2005-04-07 15:19:11 -# -# Connection: ROOT LOCAL -# Host: localhost -# Saved: 2005-03-18 15:25:57 -# -# Connection: ROOT LOCAL -# Host: localhost -# Saved: 2005-03-18 15:04:50 -# -# Connection: ROOT LOCAL -# Host: localhost -# Saved: 2005-03-15 13:43:35 -# -# Connection: ROOT LOCAL -# Host: localhost -# Saved: 2005-03-10 14:16:48 -# -# Connection: ROOT LOCAL -# Host: localhost -# Saved: 2005-02-16 16:04:11 - - - SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS lams_activity_category; @@ -37,6 +11,9 @@ DROP TABLE IF EXISTS lams_cr_node_version_property; DROP TABLE IF EXISTS lams_cr_workspace; DROP TABLE IF EXISTS lams_cr_workspace_credential; +DROP TABLE IF EXISTS lams_css_property; +DROP TABLE IF EXISTS lams_css_style; +DROP TABLE IF EXISTS lams_css_theme_ve; DROP TABLE IF EXISTS lams_gate_activity_level; DROP TABLE IF EXISTS lams_group; DROP TABLE IF EXISTS lams_grouping; @@ -68,10 +45,12 @@ DROP TABLE IF EXISTS lams_user_group; DROP TABLE IF EXISTS lams_user_organisation; DROP TABLE IF EXISTS lams_user_organisation_role; +DROP TABLE IF EXISTS lams_user_preference; DROP TABLE IF EXISTS lams_workspace; DROP TABLE IF EXISTS lams_workspace_folder; DROP TABLE IF EXISTS lams_workspace_folder_type; +DROP TABLE IF EXISTS lams_workspace_folder_content_type; +DROP TABLE IF EXISTS lams_workspace_folder_content; - SET FOREIGN_KEY_CHECKS=1; Index: lams_tool_deploy/test/file/sql/lams_common/insert_types_data.sql =================================================================== diff -u -r922473f5ee7930d8f2ddbf3f3e2d1d00cd9deee9 -r2dc5d899da948b767363372a7c8349a760e64ec9 --- lams_tool_deploy/test/file/sql/lams_common/insert_types_data.sql (.../insert_types_data.sql) (revision 922473f5ee7930d8f2ddbf3f3e2d1d00cd9deee9) +++ lams_tool_deploy/test/file/sql/lams_common/insert_types_data.sql (.../insert_types_data.sql) (revision 2dc5d899da948b767363372a7c8349a760e64ec9) @@ -1,11 +1,3 @@ -# Connection: ROOT LOCAL -# Host: localhost -# Saved: 2005-03-18 15:28:01 -# -# Connection: ROOT LOCAL -# Host: localhost -# Saved: 2005-03-18 14:55:46 -# 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()); @@ -89,6 +81,8 @@ INSERT INTO lams_log_event_type VALUES (5, 'LEARNER_ACTIVITY_START'); INSERT INTO lams_log_event_type VALUES (6, 'LEARNER_ACTIVITY_FINISH'); +INSERT INTO lams_workspace_folder_content_type VALUES (1,'FILE'); +INSERT INTO lams_workspace_folder_content_type VALUES (2,'PACKAGE');