Index: lams_tool_deploy/build.xml =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/build.xml,v diff -u -r1.3 -r1.4 --- lams_tool_deploy/build.xml 6 Apr 2005 06:23:05 -0000 1.3 +++ lams_tool_deploy/build.xml 7 Apr 2005 06:22:14 -0000 1.4 @@ -86,7 +86,7 @@ - + @@ -97,6 +97,8 @@ + + Index: lams_tool_deploy/src/java/org/lamsfoundation/lams/tool/deploy/FileTokenReplacer.java =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/src/java/org/lamsfoundation/lams/tool/deploy/FileTokenReplacer.java,v diff -u -r1.2 -r1.3 --- lams_tool_deploy/src/java/org/lamsfoundation/lams/tool/deploy/FileTokenReplacer.java 6 Apr 2005 06:23:05 -0000 1.2 +++ lams_tool_deploy/src/java/org/lamsfoundation/lams/tool/deploy/FileTokenReplacer.java 7 Apr 2005 06:22:14 -0000 1.3 @@ -29,10 +29,21 @@ public static final String TOKEN_REGEX_PREFIX = "\\$\\{"; public static final String TOKEN_REGEX_SUFFIX = "\\}"; - public static final Pattern TOKEN_PATTERN = Pattern.compile("^\\$\\{[A-Za-z0-9]+\\}$"); + public static final Pattern TOKEN_PATTERN = Pattern.compile("\\$\\{\\w+\\}"); //pattern for ${letters} + protected static String makeToken(final String tokenValue) + { + StringBuffer buf = new StringBuffer(TOKEN_PREFIX); + buf.append(tokenValue); + buf.append(TOKEN_SUFFIX); + return buf.toString(); + } + protected static boolean isValidToken(String token) + { + return TOKEN_PATTERN.matcher(token).matches(); + } /** Creates a new instance of FileTokenReplacer */ @@ -67,7 +78,7 @@ String token = makeToken(key); if (!isValidToken(token)) { - throw new DeployException(key +" does not make a valid token"); + throw new DeployException(key +" does not make a valid token ("+token+")"); } fileString = fileString.replaceAll(makeTokenRegex(key), value); @@ -76,18 +87,7 @@ return fileString; } - protected String makeToken(final String tokenValue) - { - StringBuffer buf = new StringBuffer(TOKEN_PREFIX); - buf.append(tokenValue); - buf.append(TOKEN_SUFFIX); - return buf.toString(); - } - protected boolean isValidToken(String token) - { - return TOKEN_PATTERN.matcher(token).matches(); - } protected String makeTokenRegex(String tokenValue) throws DeployException { Index: lams_tool_deploy/src/java/org/lamsfoundation/lams/tool/deploy/ScriptRunner.java =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/src/java/org/lamsfoundation/lams/tool/deploy/ScriptRunner.java,v diff -u -r1.1 -r1.2 --- lams_tool_deploy/src/java/org/lamsfoundation/lams/tool/deploy/ScriptRunner.java 6 Apr 2005 06:23:05 -0000 1.1 +++ lams_tool_deploy/src/java/org/lamsfoundation/lams/tool/deploy/ScriptRunner.java 7 Apr 2005 06:22:14 -0000 1.2 @@ -23,7 +23,8 @@ public class ScriptRunner { public static final String STATEMENT_DELIMITER = ";"; - public static final String COMMENT_PATTERN = "[#|\\-\\-].*"; + public static final String HASH_COMMENT_PATTERN = "#.*"; + public static final String DASH_COMMENT_PATTERN = "\\-{2,}.*"; public static final String LINE_BREAK_PATTERN = "\\r\\n|\\n|\\r|\\u0085|\\u2028|\\u2029"; public static final String LARGE_WHITE_SPACE_PATTERN = "\\s{2,}"; @@ -56,8 +57,9 @@ */ protected String[] parseScript(String script) throws DeployException { - //remove comments (lines beginning with --) - script = script.replaceAll(COMMENT_PATTERN,""); + //remove comments (beginning with # & --) + script = script.replaceAll(HASH_COMMENT_PATTERN,""); + script = script.replaceAll(DASH_COMMENT_PATTERN,""); //remove line breaks script = script.replaceAll(LINE_BREAK_PATTERN, ""); Index: lams_tool_deploy/src/java/org/lamsfoundation/lams/tool/deploy/ToolDBActivateTask.java =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/src/java/org/lamsfoundation/lams/tool/deploy/ToolDBActivateTask.java,v diff -u -r1.3 -r1.4 --- lams_tool_deploy/src/java/org/lamsfoundation/lams/tool/deploy/ToolDBActivateTask.java 6 Apr 2005 07:13:16 -0000 1.3 +++ lams_tool_deploy/src/java/org/lamsfoundation/lams/tool/deploy/ToolDBActivateTask.java 7 Apr 2005 06:22:14 -0000 1.4 @@ -38,9 +38,10 @@ Connection conn = getConnection(); try { + conn.setAutoCommit(false); activateTool(toolId, conn); activateLibrary(learningLibraryId, conn); - activateActivity(learningLibraryId, conn); + //activateActivity(learningLibraryId, conn); conn.commit(); } catch (SQLException sqlex) @@ -125,20 +126,20 @@ } - private void activateActivity(long libraryId, Connection conn) throws SQLException - { - PreparedStatement stmt = null; - try - { - stmt = conn.prepareStatement("UPDATE lams_learning_activity SET valid_flag = 1 WHERE learning_library_id = ?"); - stmt.setLong(1, libraryId); - stmt.execute(); - } - finally - { - DbUtils.closeQuietly(stmt); - } - - } +// private void activateActivity(long libraryId, Connection conn) throws SQLException +// { +// PreparedStatement stmt = null; +// try +// { +// stmt = conn.prepareStatement("UPDATE lams_learning_activity SET valid_flag = 1 WHERE learning_library_id = ?"); +// stmt.setLong(1, libraryId); +// stmt.execute(); +// } +// finally +// { +// DbUtils.closeQuietly(stmt); +// } +// +// } } Index: lams_tool_deploy/src/java/org/lamsfoundation/lams/tool/deploy/ToolDBDeployTask.java =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/src/java/org/lamsfoundation/lams/tool/deploy/ToolDBDeployTask.java,v diff -u -r1.4 -r1.5 --- lams_tool_deploy/src/java/org/lamsfoundation/lams/tool/deploy/ToolDBDeployTask.java 6 Apr 2005 07:13:16 -0000 1.4 +++ lams_tool_deploy/src/java/org/lamsfoundation/lams/tool/deploy/ToolDBDeployTask.java 7 Apr 2005 06:22:14 -0000 1.5 @@ -113,19 +113,19 @@ updateToolDefaultContentId(toolId, defaultContentId, conn); //put the tool id into the tool library script - Map replacementMap = new HashMap(1); - replacementMap.put("tool_id", Long.toString(toolId)); - FileTokenReplacer libraryScriptReplacer = new FileTokenReplacer(toolLibraryInsertScript, replacementMap); - String libraryScriptSQL = libraryScriptReplacer.replace(); +// Map replacementMap = new HashMap(1); +// replacementMap.put("tool_id", Long.toString(toolId)); +// FileTokenReplacer libraryScriptReplacer = new FileTokenReplacer(toolLibraryInsertScript, replacementMap); +// String libraryScriptSQL = libraryScriptReplacer.replace(); //run tool library script and get the library id back - learningLibraryId = runLibraryScript(libraryScriptSQL, conn); + learningLibraryId = runLibraryScript(readFile(toolLibraryInsertScript), conn); //update tool record to include library id updateToolLibraryId(toolId, learningLibraryId, conn); //put the library id into the activity insert script - replacementMap = new HashMap(1); + Map replacementMap = new HashMap(1); replacementMap.put("tool_id", Long.toString(toolId)); replacementMap.put("learning_library_id", Long.toString(learningLibraryId)); FileTokenReplacer activityScriptReplacer = new FileTokenReplacer(toolActivityInsertScript, replacementMap); @@ -245,7 +245,7 @@ PreparedStatement stmt = null; try { - stmt = conn.prepareStatement("UPDATE lams_tool SET default_content_id = ? WHERE tool_id = ?"); + stmt = conn.prepareStatement("UPDATE lams_tool SET default_tool_content_id = ? WHERE tool_id = ?"); stmt.setLong(1, defaultContentId); stmt.setLong(2, toolId); stmt.execute(); Fisheye: Tag 1.2 refers to a dead (removed) revision in file `lams_tool_deploy/test/file/application.xml'. Fisheye: No comparison available. Pass `N' to diff? Index: lams_tool_deploy/test/file/sql/activity_insert.sql =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/test/file/sql/Attic/activity_insert.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_tool_deploy/test/file/sql/activity_insert.sql 7 Apr 2005 06:22:14 -0000 1.1 @@ -0,0 +1,84 @@ +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2005-04-07 11:08:32 +# +INSERT INTO lams_learning_activity +( +activity_ui_id +, description +, title +, help_text +, xcoord +, ycoord +, parent_activity_id +, parent_ui_id +, learning_activity_type_id +, grouping_support_type_id +, apply_grouping_flag +, grouping_id +, grouping_ui_id +, order_id +, define_later_flag +, learning_design_id +, learning_library_id +, create_date_time +, run_offline_flag +, offline_instructions +, online_instructions +, max_number_of_options +, min_number_of_options +, options_instructions +, tool_id +, tool_content_id +, activity_category_id +, gate_activity_level_id +, gate_open_flag +, gate_start_time_offset +, gate_end_time_offset +, gate_start_date_time +, gate_end_date_time +, library_activity_ui_image +, create_grouping_id +, create_grouping_ui_id +, library_activity_id +) +VALUES +( +NULL +, 'TEST DEPLOY TOOL ACTIVITY' +, 'Test Deploy Tool' +, 'Does this help?' +, NULL +, NULL +, NULL +, NULL +, 1 +, 1 +, 0 +, NULL +, NULL +, NULL +, 0 +, NULL +, ${learning_library_id} +, NOW() +, 0 +, 'Do it yourself' +, 'Do it in LAMS' +, NULL +, NULL +, NULL +, ${tool_id} +, NULL +, 4 +, NULL +, NULL +, NULL +, NULL +, NULL +, NULL +, '/lams/tool/dptst1/images/tool.gif' +, NULL +, NULL +, NULL +) Index: lams_tool_deploy/test/file/sql/create_tool_tables.sql =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/test/file/sql/Attic/create_tool_tables.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_tool_deploy/test/file/sql/create_tool_tables.sql 7 Apr 2005 06:22:14 -0000 1.1 @@ -0,0 +1,52 @@ +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2005-04-07 15:02:07 +# + + +DROP TABLE IF EXISTS tool_imscp_content; +DROP TABLE IF EXISTS tool_imscp_userprogress; +DROP TABLE IF EXISTS tool_imscp_package; + +-- +-- Table structure for table 'tool_imscp_package' +-- Records the details of an IMS context package +-- creator_user_id is a user id from the LAMS user management service +CREATE TABLE tool_imscp_package ( + sid bigint(20) unsigned NOT NULL auto_increment, + tool_content_id bigint(20) unsigned, + define_later tinyint(1) unsigned default 0, + title varchar(255), + description text, + ims_schema varchar(255), + date_created datetime, + creator_user_id bigint(20) unsigned, + cr_uuid bigint(20) unsigned, + cr_version_id bigint(20) unsigned, + initial_item varchar(255), + organizationXML text, + PRIMARY KEY (sid) +) TYPE=InnoDB COMMENT='Details of an IMS Content Package'; + +-- +-- Table structure for table 'tool_imscp_userprogress' +-- Records the current state for a learner in a lesson. +-- Column learner_id is a user id from the LAMS user management service. +-- Column lesson_id is lesson id from the LAMS core. +-- Put another index on this table when we have worked out +-- how we are likely to access the values - the package_id index +-- exists to keep the foriegn key happy. +CREATE TABLE tool_imscp_userprogress ( + sid bigint(20) unsigned NOT NULL auto_increment, + learner_id bigint(20) unsigned NOT NULL, + lesson_id bigint(20) unsigned NOT NULL, + package_id bigint(20) unsigned NOT NULL, + current_item varchar(255), + PRIMARY KEY (sid), + INDEX (package_id), + CONSTRAINT FK_tool_imscp_userprogress_package + FOREIGN KEY (package_id) + REFERENCES tool_imscp_package(sid) + ON DELETE NO ACTION ON UPDATE NO ACTION +) TYPE=InnoDB COMMENT='Current state for a learner in a session'; + Index: lams_tool_deploy/test/file/sql/insert_test_records.sql =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/test/file/sql/Attic/insert_test_records.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_tool_deploy/test/file/sql/insert_test_records.sql 7 Apr 2005 06:22:14 -0000 1.1 @@ -0,0 +1,148 @@ + +INSERT INTO lams_tool +( +tool_signature, +service_name, +tool_display_name, +description, +learning_library_id, +default_tool_content_id, +valid_flag, +grouping_support_type_id, +supports_define_later_flag, +supports_run_offline_flag, +supports_moderation_flag, +supports_contribute_flag, +learner_url, +author_url, +define_later_url, +export_portfolio_url, +monitor_url, +contribute_url, +moderation_url, +create_date_time +) +VALUES +( +'DPTST1', +'test.test.deploy.Test', +'DEPLOY TEST TOOL 1', +'TEST DATA FOR DEPLOY APP', +NULL, +NULL, +0, +1, +1, +1, +1, +0, +'/lams/tool/dptst1/foo.jsp', +'/lams/tool/dptst1/foo.jsp', +'/lams/tool/dptst1/foo.jsp', +'/lams/tool/dptst1/foo.jsp', +'/lams/tool/dptst1/foo.jsp', +'/lams/tool/dptst1/foo.jsp', +'/lams/tool/dptst1/foo.jsp', +NOW() +); + + + +INSERT INTO lams_learning_library +( +description, +title, +valid_flag, +create_date_time +) +VALUES +( +'TEST DEPLOY TOOL LIBRARY', +'Test Deploy Library', +0, +NOW() +); + + + + +INSERT INTO lams_learning_activity +( +activity_ui_id +, description +, title +, help_text +, xcoord +, ycoord +, parent_activity_id +, parent_ui_id +, learning_activity_type_id +, grouping_support_type_id +, apply_grouping_flag +, grouping_id +, grouping_ui_id +, order_id +, define_later_flag +, learning_design_id +, learning_library_id +, create_date_time +, run_offline_flag +, offline_instructions +, online_instructions +, max_number_of_options +, min_number_of_options +, options_instructions +, tool_id +, tool_content_id +, activity_category_id +, gate_activity_level_id +, gate_open_flag +, gate_start_time_offset +, gate_end_time_offset +, gate_start_date_time +, gate_end_date_time +, library_activity_ui_image +, create_grouping_id +, create_grouping_ui_id +, library_activity_id +) +VALUES +( +NULL +, 'TEST DEPLOY TOOL ACTIVITY' +, 'Test Deploy Tool' +, 'Does this help?' +, NULL +, NULL +, NULL +, NULL +, 1 +, 1 +, 0 +, NULL +, NULL +, NULL +, 0 +, NULL +, 1 +, NOW() +, 0 +, 'Do it yourself' +, 'Do it in LAMS' +, NULL +, NULL +, NULL +, 1 +, NULL +, 4 +, NULL +, NULL +, NULL +, NULL +, NULL +, NULL +, '/lams/tool/dptst1/images/tool.gif' +, NULL +, NULL +, NULL +); \ No newline at end of file Index: lams_tool_deploy/test/file/sql/library_insert.sql =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/test/file/sql/Attic/library_insert.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_tool_deploy/test/file/sql/library_insert.sql 7 Apr 2005 06:22:14 -0000 1.1 @@ -0,0 +1,18 @@ +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2005-04-07 10:50:55 +# +INSERT INTO lams_learning_library +( +description, +title, +valid_flag, +create_date_time +) +VALUES +( +'TEST DEPLOY TOOL LIBRARY', +'Test Deploy Library', +0, +NOW() +) Fisheye: Tag 1.2 refers to a dead (removed) revision in file `lams_tool_deploy/test/file/sql/test-script.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: lams_tool_deploy/test/file/sql/test_script.sql =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/test/file/sql/Attic/test_script.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_tool_deploy/test/file/sql/test_script.sql 7 Apr 2005 06:22:14 -0000 1.1 @@ -0,0 +1,31 @@ +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2005-04-06 15:44:33 +# +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2005-04-06 15:15:40 +# +-- SCRIPT RUNNER TESTING SCRIPT +-- IF YOU ALTER THIS YOU WILL BREAK THE TEST! +CREATE TABLE TEST_1 ( +TEST_1_INT INT, +TEST_1_STR VARCHAR(255), #asdadasdsd +TEST_1_DATE DATETIME +) TYPE=InnoDB; + +-- more comments +INSERT INTO TEST_1 VALUES (1, 'foo', NOW()); +INSERT INTO TEST_1 VALUES (2, 'foo-bar', NOW()); + +-- comments +-- comments +ALTER TABLE TEST_1 -- asdasdasd +ADD INDEX IDX_TEST_1 (TEST_1_INT); + +-- -- double comments +UPDATE TEST_1 SET TEST_1_STR = 'bar' WHERE TEST_1_INT = 1; + + +-- more comments! +DROP TABLE TEST_1; \ No newline at end of file Index: lams_tool_deploy/test/file/sql/tool_insert.sql =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/test/file/sql/Attic/tool_insert.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_tool_deploy/test/file/sql/tool_insert.sql 7 Apr 2005 06:22:14 -0000 1.1 @@ -0,0 +1,50 @@ +# Connection: ROOT LOCAL +# Host: localhost +# Saved: 2005-04-07 10:42:43 +# +INSERT INTO lams_tool +( +tool_signature, +service_name, +tool_display_name, +description, +learning_library_id, +default_tool_content_id, +valid_flag, +grouping_support_type_id, +supports_define_later_flag, +supports_run_offline_flag, +supports_moderation_flag, +supports_contribute_flag, +learner_url, +author_url, +define_later_url, +export_portfolio_url, +monitor_url, +contribute_url, +moderation_url, +create_date_time +) +VALUES +( +'DPTST1', +'test.test.deploy.Test', +'DEPLOY TEST TOOL 1', +'TEST DATA FOR DEPLOY APP', +NULL, +NULL, +0, +1, +1, +1, +1, +0, +'/lams/tool/dptst1/foo.jsp', +'/lams/tool/dptst1/foo.jsp', +'/lams/tool/dptst1/foo.jsp', +'/lams/tool/dptst1/foo.jsp', +'/lams/tool/dptst1/foo.jsp', +'/lams/tool/dptst1/foo.jsp', +'/lams/tool/dptst1/foo.jsp', +NOW() +) Index: lams_tool_deploy/test/file/sql/lams_common/create_lams_11_tables.sql =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/test/file/sql/lams_common/Attic/create_lams_11_tables.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_tool_deploy/test/file/sql/lams_common/create_lams_11_tables.sql 7 Apr 2005 06:22:14 -0000 1.1 @@ -0,0 +1,675 @@ +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 + , 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 AUTO_INCREMENT + , description TEXT + , title VARCHAR(255) + , valid_flag TINYINT(1) NOT NULL DEFAULT 1 + , create_date_time DATETIME NOT NULL + , PRIMARY KEY (learning_library_id) +)TYPE=InnoDB; + +CREATE TABLE lams_organisation_type ( + organisation_type_id INT(3) NOT NULL + , name VARCHAR(64) NOT NULL + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (organisation_type_id) +)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 DATETIME NOT NULL + , PRIMARY KEY (role_id) +)TYPE=InnoDB; +CREATE INDEX gname ON lams_role (name ASC); + +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_lesson_state ( + lesson_state_id INT(3) NOT NULL + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (lesson_state_id) +)TYPE=InnoDB; + +CREATE TABLE lams_tool_session_type ( + tool_session_type_id INT(3) NOT NULL + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (tool_session_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_license ( + license_id BIGINT(20) NOT NULL + , name VARCHAR(200) NOT NULL + , code VARCHAR(20) NOT NULL + , url VARCHAR(256) + , default_flag TINYINT(1) NOT NULL DEFAULT 0 + , picture_url VARCHAR(256) + , PRIMARY KEY (license_id) +)TYPE=InnoDB; + +CREATE TABLE lams_copy_type ( + copy_type_id TINYINT(4) NOT NULL + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (copy_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_activity_category ( + activity_category_id INT(3) NOT NULL + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (activity_category_id) +)TYPE=InnoDB; + +CREATE TABLE lams_cr_workspace ( + workspace_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT + , name VARCHAR(255) NOT NULL + , PRIMARY KEY (workspace_id) +)TYPE=InnoDB; +ALTER TABLE lams_cr_workspace COMMENT='Content repository workspace'; + +CREATE TABLE lams_cr_credential ( + 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.'; + +CREATE TABLE lams_workspace_folder_type ( + lams_workspace_folder_type_id INT(3) NOT NULL + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (lams_workspace_folder_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_grouping_support_type ( + grouping_support_type_id INT(3) NOT NULL + , description VARCHAR(64) NOT NULL + , PRIMARY KEY (grouping_support_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_log_event_type ( + log_event_type_id INT(5) NOT NULL + , description VARCHAR(255) NOT NULL + , PRIMARY KEY (log_event_type_id) +)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_authentication_method ( + authentication_method_id BIGINT(20) NOT NULL + , authentication_method_type_id INT(3) NOT NULL DEFAULT 0 + , authentication_method_name VARCHAR(255) NOT NULL + , UNIQUE UQ_lams_authentication_method_1 (authentication_method_name) + , 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 AUTO_INCREMENT + , parent_folder_id BIGINT(20) + , name VARCHAR(64) NOT NULL + , workspace_id BIGINT(20) NOT NULL + , user_id BIGINT(20) NOT NULL + , create_date_time DATETIME NOT NULL + , last_modified_date_time DATETIME + , lams_workspace_folder_type_id INT(3) NOT NULL + , PRIMARY KEY (workspace_folder_id) + , 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 + , INDEX (lams_workspace_folder_type_id) + , CONSTRAINT FK_lams_workspace_folder_4 FOREIGN KEY (lams_workspace_folder_type_id) + REFERENCES lams_workspace_folder_type (lams_workspace_folder_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_workspace ( + workspace_id BIGINT(20) NOT NULL AUTO_INCREMENT + , root_folder_id BIGINT(20) + , name VARCHAR(255) + , 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_organisation ( + organisation_id BIGINT(20) NOT NULL 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 + , 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 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 + , authentication_method_id BIGINT(20) NOT NULL DEFAULT 0 + , workspace_id BIGINT(20) + , 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 AUTO_INCREMENT + , learning_design_ui_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 + , user_id BIGINT(20) NOT NULL + , help_text TEXT + , copy_type_id TINYINT(4) NOT NULL + , create_date_time DATETIME NOT NULL + , version VARCHAR(56) + , parent_learning_design_id BIGINT(20) + , workspace_folder_id BIGINT(20) + , duration BIGINT(38) + , license_id BIGINT(20) + , license_text TEXT + , lesson_org_id BIGINT(20) + , lesson_org_name VARCHAR(255) + , lesson_id BIGINT(20) + , lesson_start_date_time DATETIME + , lesson_name VARCHAR(255) + , last_modified_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) + , INDEX (workspace_folder_id) + , CONSTRAINT FK_lams_learning_design_4 FOREIGN KEY (workspace_folder_id) + REFERENCES lams_workspace_folder (workspace_folder_id) + , INDEX (license_id) + , CONSTRAINT FK_lams_learning_design_5 FOREIGN KEY (license_id) + REFERENCES lams_license (license_id) + , INDEX (copy_type_id) + , CONSTRAINT FK_lams_learning_design_6 FOREIGN KEY (copy_type_id) + REFERENCES lams_copy_type (copy_type_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 AUTO_INCREMENT + , grouping_ui_id INT(11) + , grouping_type_id INT(11) NOT NULL + , number_of_groups INT(11) + , learners_per_group INT(11) + , staff_group_id BIGINT(20) DEFAULT 0 + , max_number_of_groups INT(3) + , 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_group ( + group_id BIGINT(20) NOT NULL AUTO_INCREMENT + , grouping_id BIGINT(20) NOT NULL + , 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 AUTO_INCREMENT + , organisation_id BIGINT(20) NOT NULL + , user_id BIGINT(20) 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_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 + , learning_library_id BIGINT(20) + , default_tool_content_id BIGINT(20) + , valid_flag TINYINT(1) NOT NULL DEFAULT 1 + , grouping_support_type_id INT(3) NOT NULL + , supports_define_later_flag TINYINT(1) NOT NULL DEFAULT 0 + , supports_run_offline_flag TINYINT(1) NOT NULL + , supports_moderation_flag TINYINT(1) NOT NULL + , supports_contribute_flag TINYINT(1) NOT NULL + , 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 + , contribute_url TEXT + , moderation_url TEXT + , create_date_time DATETIME NOT NULL + , UNIQUE UQ_lams_tool_sig (tool_signature) + , UNIQUE UQ_lams_tool_class_name (service_name) + , PRIMARY KEY (tool_id) + , INDEX (learning_library_id) + , CONSTRAINT FK_lams_tool_1 FOREIGN KEY (learning_library_id) + REFERENCES lams_learning_library (learning_library_id) + , INDEX (grouping_support_type_id) + , CONSTRAINT FK_lams_tool_2 FOREIGN KEY (grouping_support_type_id) + REFERENCES lams_grouping_support_type (grouping_support_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_learning_activity ( + activity_id BIGINT(20) NOT NULL AUTO_INCREMENT + , activity_ui_id INT(11) + , description TEXT + , title VARCHAR(255) + , help_text TEXT + , xcoord INT(11) + , ycoord INT(11) + , parent_activity_id BIGINT(20) + , parent_ui_id INT(11) + , learning_activity_type_id INT(11) NOT NULL DEFAULT 0 + , grouping_support_type_id INT(3) NOT NULL + , apply_grouping_flag TINYINT(1) NOT NULL + , grouping_id BIGINT(20) + , grouping_ui_id INT(11) + , order_id INT(11) + , define_later_flag TINYINT(4) NOT NULL DEFAULT 0 + , learning_design_id BIGINT(20) + , 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 + , tool_id BIGINT(20) + , tool_content_id BIGINT(20) + , activity_category_id INT(3) NOT NULL + , gate_activity_level_id INT(11) + , gate_open_flag TINYINT(1) + , gate_start_time_offset BIGINT(38) + , gate_end_time_offset BIGINT(38) + , gate_start_date_time DATETIME + , gate_end_date_time DATETIME + , library_activity_ui_image VARCHAR(255) + , create_grouping_id BIGINT(20) + , create_grouping_ui_id INT(11) + , library_activity_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) + , INDEX (library_activity_id) + , CONSTRAINT FK_lams_learning_activity_11 FOREIGN KEY (library_activity_id) + REFERENCES lams_learning_activity (activity_id) + , INDEX (activity_category_id) + , CONSTRAINT FK_lams_learning_activity_12 FOREIGN KEY (activity_category_id) + REFERENCES lams_activity_category (activity_category_id) + , INDEX (grouping_support_type_id) + , CONSTRAINT FK_lams_learning_activity_13 FOREIGN KEY (grouping_support_type_id) + REFERENCES lams_grouping_support_type (grouping_support_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_learner_progress ( + learner_progress_id BIGINT(20) NOT NULL AUTO_INCREMENT + , user_id BIGINT(20) NOT NULL + , lesson_id BIGINT(20) NOT NULL + , lesson_completed_flag TINYINT(1) NOT NULL DEFAULT 0 + , waiting_flag TINYINT(1) NOT NULL + , start_date_time DATETIME NOT NULL + , finish_date_time DATETIME + , current_activity_id BIGINT(20) + , next_activity_id BIGINT(20) + , previous_activity_id BIGINT(20) + , requires_restart_flag TINYINT(1) NOT NULL + , PRIMARY KEY (learner_progress_id) + , 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) + , INDEX (current_activity_id) + , CONSTRAINT FK_lams_learner_progress_3 FOREIGN KEY (current_activity_id) + REFERENCES lams_learning_activity (activity_id) + , INDEX (next_activity_id) + , CONSTRAINT FK_lams_learner_progress_4 FOREIGN KEY (next_activity_id) + REFERENCES lams_learning_activity (activity_id) + , INDEX (previous_activity_id) + , CONSTRAINT FK_lams_learner_progress_5 FOREIGN KEY (previous_activity_id) + REFERENCES lams_learning_activity (activity_id) +)TYPE=InnoDB; + +CREATE TABLE lams_cr_node ( + node_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT + , workspace_id BIGINT(20) UNSIGNED NOT NULL + , path VARCHAR(255) + , type VARCHAR(255) NOT NULL + , created_date_time DATETIME NOT NULL + , next_version_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 1 + , parent_nv_id BIGINT(20) UNSIGNED + , nv_id BIGINT(20) UNSIGNED NOT NULL + , PRIMARY KEY (node_id) + , INDEX (workspace_id) + , CONSTRAINT FK_lams_cr_node_1 FOREIGN KEY (workspace_id) + REFERENCES lams_cr_workspace (workspace_id) +)TYPE=InnoDB; +ALTER TABLE lams_cr_node COMMENT='The main table containing the node definition'; + +CREATE TABLE lams_cr_node_version ( + nv_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT + , node_id BIGINT(20) UNSIGNED NOT NULL + , version_id BIGINT(20) UNSIGNED NOT NULL + , created_date_time DATETIME NOT NULL + , id BIGINT(20) UNSIGNED NOT NULL + , PRIMARY KEY (nv_id) + , INDEX (node_id) + , CONSTRAINT FK_lams_cr_node_version_2 FOREIGN KEY (node_id) + REFERENCES lams_cr_node (node_id) +)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 + , role_id INT(6) NOT NULL + , 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_tool_session ( + tool_session_id BIGINT(20) NOT NULL AUTO_INCREMENT + , tool_session_type_id INT(3) NOT NULL + , lesson_id BIGINT(20) NOT NULL + , activity_id BIGINT(20) NOT NULL + , tool_session_state_id INT(3) NOT NULL + , create_date_time DATETIME NOT NULL + , group_id BIGINT(20) + , user_id BIGINT(20) + , unique_key VARCHAR(128) NOT NULL + , 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) + , INDEX (tool_session_type_id) + , CONSTRAINT FK_lams_tool_session_7 FOREIGN KEY (tool_session_type_id) + REFERENCES lams_tool_session_type (tool_session_type_id) + , INDEX (activity_id) + , CONSTRAINT FK_lams_tool_session_8 FOREIGN KEY (activity_id) + REFERENCES lams_learning_activity (activity_id) +)TYPE=InnoDB; + +CREATE TABLE lams_progress_completed ( + learner_progress_id BIGINT(20) NOT NULL + , activity_id BIGINT(20) NOT NULL + , 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_attempted ( + learner_progress_id BIGINT(20) NOT NULL + , activity_id BIGINT(20) NOT NULL + , 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_group ( + user_id BIGINT(20) NOT NULL + , group_id BIGINT(20) NOT NULL + , 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 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_lesson_learner ( + lesson_id BIGINT(20) NOT NULL + , user_id BIGINT(20) NOT NULL + , 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) +)TYPE=InnoDB; + +CREATE TABLE lams_cr_workspace_credential ( + wc_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT + , workspace_id BIGINT(20) UNSIGNED NOT NULL + , credential_id BIGINT(20) UNSIGNED NOT NULL + , PRIMARY KEY (wc_id) + , INDEX (workspace_id) + , CONSTRAINT FK_lams_cr_workspace_credential_1 FOREIGN KEY (workspace_id) + REFERENCES lams_cr_workspace (workspace_id) ON DELETE NO ACTION ON UPDATE NO ACTION + , INDEX (credential_id) + , CONSTRAINT FK_lams_cr_workspace_credential_2 FOREIGN KEY (credential_id) + REFERENCES lams_cr_credential (credential_id) ON DELETE NO ACTION ON UPDATE NO ACTION +)TYPE=InnoDB; +ALTER TABLE lams_cr_workspace_credential COMMENT='Maps tools access to workspaces'; + +CREATE TABLE lams_cr_node_version_property ( + id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT + , nv_id BIGINT(20) UNSIGNED NOT NULL + , name VARCHAR(255) NOT NULL + , value VARCHAR(255) NOT NULL + , type TINYINT NOT NULL + , PRIMARY KEY (id) + , INDEX (nv_id) + , CONSTRAINT FK_lams_cr_node_version_property_1 FOREIGN KEY (nv_id) + REFERENCES lams_cr_node_version (nv_id) +)TYPE=InnoDB; + +CREATE TABLE lams_log_event ( + log_event_id BIGINT(20) NOT NULL AUTO_INCREMENT + , log_event_type_id INT(5) NOT NULL + , user_id BIGINT(20) + , timestamp DATETIME NOT NULL + , ref_id BIGINT(20) + , message VARCHAR(255) + , PRIMARY KEY (log_event_id) + , INDEX (log_event_type_id) + , CONSTRAINT FK_lams_event_log_1 FOREIGN KEY (log_event_type_id) + REFERENCES lams_log_event_type (log_event_type_id) +)TYPE=InnoDB; + +CREATE TABLE lams_learning_transition ( + transition_id BIGINT(20) NOT NULL AUTO_INCREMENT + , transition_ui_id INT(11) + , description TEXT + , title VARCHAR(255) + , to_activity_id BIGINT(20) NOT NULL + , from_activity_id BIGINT(20) NOT NULL + , learning_design_id BIGINT(20) NOT NULL DEFAULT 0 + , create_date_time DATETIME NOT NULL + , to_ui_id INT(11) + , from_ui_id INT(11) + , 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_tool_deploy/test/file/sql/lams_common/drop_lams_11_tables.sql =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/test/file/sql/lams_common/Attic/drop_lams_11_tables.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_tool_deploy/test/file/sql/lams_common/drop_lams_11_tables.sql 7 Apr 2005 06:22:14 -0000 1.1 @@ -0,0 +1,77 @@ +# 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; +DROP TABLE IF EXISTS lams_activity_learners; +DROP TABLE IF EXISTS lams_authentication_method; +DROP TABLE IF EXISTS lams_authentication_method_type; +DROP TABLE IF EXISTS lams_copy_type; +DROP TABLE IF EXISTS lams_cr_credential; +DROP TABLE IF EXISTS lams_cr_node; +DROP TABLE IF EXISTS lams_cr_node_version; +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_gate_activity_level; +DROP TABLE IF EXISTS lams_group; +DROP TABLE IF EXISTS lams_grouping; +DROP TABLE IF EXISTS lams_grouping_support_type; +DROP TABLE IF EXISTS lams_grouping_type; +DROP TABLE IF EXISTS lams_learner_progress; +DROP TABLE IF EXISTS lams_learning_activity; +DROP TABLE IF EXISTS lams_learning_activity_type; +DROP TABLE IF EXISTS lams_learning_design; +DROP TABLE IF EXISTS lams_learning_transition; +DROP TABLE IF EXISTS lams_learning_library; +DROP TABLE IF EXISTS lams_lesson; +DROP TABLE IF EXISTS lams_lesson_learner; +DROP TABLE IF EXISTS lams_lesson_state; +DROP TABLE IF EXISTS lams_license; +DROP TABLE IF EXISTS lams_log_event; +DROP TABLE IF EXISTS lams_log_event_type; +DROP TABLE IF EXISTS lams_organisation; +DROP TABLE IF EXISTS lams_organisation_type; +DROP TABLE IF EXISTS lams_progress_attempted; +DROP TABLE IF EXISTS lams_progress_completed; +DROP TABLE IF EXISTS lams_role; +DROP TABLE IF EXISTS lams_tool; +DROP TABLE IF EXISTS lams_tool_content; +DROP TABLE IF EXISTS lams_tool_session; +DROP TABLE IF EXISTS lams_tool_session_state; +DROP TABLE IF EXISTS lams_tool_session_type; +DROP TABLE IF EXISTS lams_user; +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_workspace; +DROP TABLE IF EXISTS lams_workspace_folder; +DROP TABLE IF EXISTS lams_workspace_folder_type; + + + +SET FOREIGN_KEY_CHECKS=1; Index: lams_tool_deploy/test/file/sql/lams_common/insert_test_data.sql =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/test/file/sql/lams_common/Attic/insert_test_data.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_tool_deploy/test/file/sql/lams_common/insert_test_data.sql 7 Apr 2005 06:22:14 -0000 1.1 @@ -0,0 +1,682 @@ +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,1); +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,2); +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,3); + +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_workspace_folder +(parent_folder_id,name,workspace_id, +user_id,create_date_time,last_modified_date_time, +lams_workspace_folder_type_id) + values +(null,'Test Workspace',1, +1,'20042312','20042312', +1); + + +-- Populates the lams_learning_library table with default libraries + +insert into lams_learning_library (learning_library_id,description,title,create_date_time) values (1,'Notebook','Notebook','20050207'); +insert into lams_learning_library (learning_library_id,description,title,create_date_time) values (2,'NoticeBoard','NoticeBoard','20050207'); +insert into lams_learning_library (learning_library_id,description,title,create_date_time) values (3,'Multiple Choice Questions','Multiple Choice','20050207'); +insert into lams_learning_library (learning_library_id,description,title,create_date_time) values (4,'Questions and Answer','Question And Answer','20050207'); +insert into lams_learning_library (learning_library_id,description,title,create_date_time) values (5,'Message Board','Message Board','20050207'); +insert into lams_learning_library (learning_library_id,description,title,create_date_time) values (6,'Survey','Survey','20050207'); +insert into lams_learning_library (learning_library_id,description,title,create_date_time) values (7,'Chat','Chat','20050207'); +insert into lams_learning_library (learning_library_id,description,title,create_date_time) values (8,'Shared Resources','Shared Resources','20050207'); +insert into lams_learning_library (learning_library_id,description,title,create_date_time) values (9,'Voting','Voting','20050207'); +insert into lams_learning_library (learning_library_id,description,title,create_date_time) values (10,'Submit Files','Submit Files','20050207'); +insert into lams_learning_library (learning_library_id,description,title,create_date_time) values (11,'Instant Messaging','Instant Messaging','20050207'); + +-- Populates the lams_tool table with default tools corresponding to each of the above libraries + +insert into lams_tool (tool_id,tool_signature,service_name,tool_display_name,description,default_tool_content_id,grouping_support_type_id,supports_define_later_flag, +supports_run_offline_flag,supports_moderation_flag,supports_contribute_flag, +learner_url,author_url,define_later_url,export_portfolio_url,monitor_url,valid_flag,learning_library_id,create_date_time) +values(1,'notebook_signature','notebookService','Notebook','Notebook Description',1,1,0, +1,1,1, +'learner_url','author_url','define_later_url','export_portfolio_url','monitor_url',1,1,'2005-02-25 17:37:25'); + +insert into lams_tool (tool_id,tool_signature,service_name,tool_display_name,description,default_tool_content_id,grouping_support_type_id,supports_define_later_flag, +supports_run_offline_flag,supports_moderation_flag,supports_contribute_flag, +learner_url,author_url,define_later_url,export_portfolio_url,monitor_url,valid_flag,learning_library_id,create_date_time) +values(2,'noticeboard_signature','noticeboardService','Noticeboard','Noticeboard Description',2,1,0, +1,1,1, +'learner_url','author_url','define_later_url','export_portfolio_url','monitor_url',1,2,'2005-02-25 17:37:25'); + +insert into lams_tool (tool_id,tool_signature,service_name,tool_display_name,description,default_tool_content_id,grouping_support_type_id,supports_define_later_flag, +supports_run_offline_flag,supports_moderation_flag,supports_contribute_flag, +learner_url,author_url,define_later_url,export_portfolio_url,monitor_url,valid_flag,learning_library_id,create_date_time) +values(3,'multiple_choice_signature','multipleChoiceService','Multiple Choice','Multiple Coice Description',3,3,0, +1,1,1, +'learner_url','author_url','define_later_url','export_portfolio_url','monitor_url',1,3,'2005-02-25 17:37:25'); + +insert into lams_tool (tool_id,tool_signature,service_name,tool_display_name,description,default_tool_content_id,grouping_support_type_id,supports_define_later_flag, +supports_run_offline_flag,supports_moderation_flag,supports_contribute_flag, +learner_url,author_url,define_later_url,export_portfolio_url,monitor_url,valid_flag,learning_library_id,create_date_time) +values(4,'question_answer_signature','questionAnswerService','Question and Answers','Question and Answers Description',4,3,0, +1,1,1, +'learner_url','author_url','define_later_url','export_portfolio_url','monitor_url',1,4,'2005-02-25 17:37:25'); + +insert into lams_tool (tool_id,tool_signature,service_name,tool_display_name,description,default_tool_content_id,grouping_support_type_id,supports_define_later_flag, +supports_run_offline_flag,supports_moderation_flag,supports_contribute_flag, +learner_url,author_url,define_later_url,export_portfolio_url,monitor_url,valid_flag,learning_library_id,create_date_time) +values(5,'message_board_signature','messageBoardService','Message Board','Message Board Description',5,3,0, +1,1,1, +'learner_url','author_url','define_later_url','export_portfolio_url','monitor_url',1,5,'2005-02-25 17:37:25'); + +insert into lams_tool (tool_id,tool_signature,service_name,tool_display_name,description,default_tool_content_id,grouping_support_type_id,supports_define_later_flag, +supports_run_offline_flag,supports_moderation_flag,supports_contribute_flag, +learner_url,author_url,define_later_url,export_portfolio_url,monitor_url,valid_flag,learning_library_id,create_date_time) +values(6,'survey_signature','surveyService','Survey','Survey Description',6,1,0, +1,1,1, +'/lams_tool_survey/tool/survey/survey.do?method=loadQuestionnaire','/lams_tool_survey/tool/survey/authoring.do?method=loadSurvey','define_later_url','export_portfolio_url','/lams_tool_survey/tool/survey/report.do?method=loadMonitorReport',1,6,'2005-02-25 17:37:25'); + +insert into lams_tool (tool_id,tool_signature,service_name,tool_display_name,description,default_tool_content_id,grouping_support_type_id,supports_define_later_flag, +supports_run_offline_flag,supports_moderation_flag,supports_contribute_flag, +learner_url,author_url,define_later_url,export_portfolio_url,monitor_url,valid_flag,learning_library_id,create_date_time) +values(7,'chat_signature','chatService','Chat','Chat Description',7,3,0, +1,1,1, +'learner_url','author_url','define_later_url','export_portfolio_url','monitor_url',1,7,'2005-02-25 17:37:25'); + +insert into lams_tool (tool_id,tool_signature,service_name,tool_display_name,description,default_tool_content_id,grouping_support_type_id,supports_define_later_flag, +supports_run_offline_flag,supports_moderation_flag,supports_contribute_flag, +learner_url,author_url,define_later_url,export_portfolio_url,monitor_url,valid_flag,learning_library_id,create_date_time) +values(8,'shared_resources_signature','sharedResourcesService','Shared Resources','Shared Resources Description',8,3,0, +1,1,1, +'learner_url','author_url','define_later_url','export_portfolio_url','monitor_url',1,8,'2005-02-25 17:37:25'); + +insert into lams_tool (tool_id,tool_signature,service_name,tool_display_name,description,default_tool_content_id,grouping_support_type_id,supports_define_later_flag, +supports_run_offline_flag,supports_moderation_flag,supports_contribute_flag, +learner_url,author_url,define_later_url,export_portfolio_url,monitor_url,valid_flag,learning_library_id,create_date_time) +values(9,'voting_signature','votingService','Voting','Voting Description',9,3,0, +1,1,1, +'learner_url','author_url','define_later_url','export_portfolio_url','monitor_url',1,9,'2005-02-25 17:37:25'); + +insert into lams_tool (tool_id,tool_signature,service_name,tool_display_name,description,default_tool_content_id,grouping_support_type_id,supports_define_later_flag, +supports_run_offline_flag,supports_moderation_flag,supports_contribute_flag, +learner_url,author_url,define_later_url,export_portfolio_url,monitor_url,valid_flag,learning_library_id,create_date_time) +values(10,'submit_files_signature','submitFilesService','Submit Files','Submit Files Description',10,1,0, +1,1,1, +'learner_url','author_url','define_later_url','export_portfolio_url','monitor_url',1,10,'2005-02-25 17:37:25'); + +insert into lams_tool (tool_id,tool_signature,service_name,tool_display_name,description,default_tool_content_id,grouping_support_type_id,supports_define_later_flag, +supports_run_offline_flag,supports_moderation_flag,supports_contribute_flag, +learner_url,author_url,define_later_url,export_portfolio_url,monitor_url,valid_flag,learning_library_id,create_date_time) +values(11,'instant_messaging_signature','instantMessagingService','Instant Messaging','Instant Messaging Description',11,1,0, +1,1,1, +'learner_url','author_url','define_later_url','export_portfolio_url','monitor_url',1,11,'2005-02-25 17:37:25'); + +-- Populates the lams_tool_content table with dummy default content for all above tools + +insert into lams_tool_content (tool_content_id,tool_id) values (1,1); +insert into lams_tool_content (tool_content_id,tool_id) values (2,2); +insert into lams_tool_content (tool_content_id,tool_id) values (3,3); +insert into lams_tool_content (tool_content_id,tool_id) values (4,4); +insert into lams_tool_content (tool_content_id,tool_id) values (5,5); +insert into lams_tool_content (tool_content_id,tool_id) values (6,6); +insert into lams_tool_content (tool_content_id,tool_id) values (7,7); +insert into lams_tool_content (tool_content_id,tool_id) values (8,8); +insert into lams_tool_content (tool_content_id,tool_id) values (9,9); +insert into lams_tool_content (tool_content_id,tool_id) values (10,10); +insert into lams_tool_content (tool_content_id,tool_id) values (11,11); +insert into lams_tool_content (tool_content_id,tool_id) values (12,1); +insert into lams_tool_content (tool_content_id,tool_id) values (13,2); +insert into lams_tool_content (tool_content_id,tool_id) values (14,3); +insert into lams_tool_content (tool_content_id,tool_id) values (15,4); +insert into lams_tool_content (tool_content_id,tool_id) values (16,5); +insert into lams_tool_content (tool_content_id,tool_id) values (17,6); +insert into lams_tool_content (tool_content_id,tool_id) values (18,7); +insert into lams_tool_content (tool_content_id,tool_id) values (19,8); +insert into lams_tool_content (tool_content_id,tool_id) values (20,4); +insert into lams_tool_content (tool_content_id,tool_id) values (21,4); + +--insert a testing grouping +insert into lams_grouping values(99,99,1,2,-1,0,-1); +insert into lams_grouping values(100,100,2,1,-1,0,-1); +INSERT INTO lams_grouping values(101, NULL, 3, NULL, NULL, 89, NULL); + +insert into lams_group values(87,99,1); +insert into lams_group values(88,100,1); +insert into lams_group values(89, 101, 1); +insert into lams_group values(90, 101, 1); + +insert into lams_user_group values(1,87); +insert into lams_user_group values(1,88); +insert into lams_user_group values(2,88); +insert into lams_user_group values(2, 90); +insert into lams_user_group values(3, 89); + +insert into lams_lesson values (1, 1, 1,'Test_Lesson','This is for testing', '2005-02-23 17:37:25', 1, 101,3, NULL,'2005-02-25 17:37:25',NULL,'2005-02-26 17:37:25' ); + +-- Populates the lams_learning_activity table with default activity templates that would be avaialbe to the +-- author in the left panel and a default dummy learning_design defination + +insert into lams_learning_activity (activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(1,1,'Notebook Template','Notebook',10,20,null,'Help Text for Activity', +1,null,0,0, +null,1,'20050101','Offline Instructions ',0, +null,null,1,1, +null,null,null,'NotebookImage', +1,0,'Online Instructions',1); + +insert into lams_learning_activity (activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(2,2,'NoticeBoard Template','NoticeBoard',10,20,null,'Help Text for Activity', +1,null,0,0, +null,2,'20050101','Offline Instructions ',0, +null,null,2,2, +null,null,null,'NoticeBoardImage', +1,0,'Online Instructions',1); + +insert into lams_learning_activity (activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(3,3,'Multiple Choice Template','Multiple Choice',10,20,null,'Help Text for Activity', +1,null,0,0, +null,3,'20050101','Offline Instructions ',0, +null,null,3,3, +null,null,null,'MultipleChoiceImage', +1,0,'Online Instructions',1); + +insert into lams_learning_activity (activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(4,4,'Question and Answer Template','Question and Answer',10,20,null,'Help Text for Activity', +1,null,0,0, +null,4,'20050101','Offline Instructions ',0, +null,null,4,4, +null,null,null,'QAImage', +1,1,'Online Instructions',1); + +insert into lams_learning_activity (activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(5,5,'MessageBoard Template','MessageBoard',10,20,null,'Help Text for Activity', +1,null,0,0, +null,5,'20050101','Offline Instructions ',0, +null,null,5,5, +null,null,null,'MBImage', +1,1,'Online Instructions',1); + +insert into lams_learning_activity (activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(6,6,'Survey Template','Survey',10,20,null,'Help Text for Activity', +1,null,0,0, +null,6,'20050101','Offline Instructions ',0, +null,null,6,6, +null,null,null,'SurveyImage', +1,0,'Online Instructions',1); + +insert into lams_learning_activity (activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(7,7,'Chat Template','Chat',10,20,null,'Help Text for Activity', +1,null,0,0, +null,7,'20050101','Offline Instructions ',0, +null,null,7,7, +null,null,null,'ChatImage', +1,1,'Online Instructions',1); + +insert into lams_learning_activity (activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(8,8,'Shared Resources Template','Shared Resources',10,20,null,'Help Text for Activity', +1,null,0,0, +null,8,'20050101','Offline Instructions ',0, +null,null,8,8, +null,null,null,'SRImage', +1,1,'Online Instructions',1); + +insert into lams_learning_activity (activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(9,9,'Voting Template','Voting',10,20,null,'Help Text for Activity', +1,null,0,0, +null,9,'20050101','Offline Instructions ',0, +null,null,9,9, +null,null,null,'VotingImage', +1,1,'Online Instructions',1); + +insert into lams_learning_activity (activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(10,10,'Submit Files Template','Submit Files',10,20,null,'Help Text for Activity', +1,null,0,0, +null,10,'20050101','Offline Instructions ',0, +null,null,10,10, +null,null,null,'SubmitFilesImage', +1,0,'Online Instructions',1); + +insert into lams_learning_activity (activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(11,11,'Instant Messaging Template','Instant Messaging',10,20,null,'Help Text for Activity', +1,null,0,0, +null,11,'20050101','Offline Instructions ',0, +null,null,11,11, +null,null,null,'IMImage', +1,0,'Online Instructions',1); + +insert into lams_learning_activity +(activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(12,12,'Optional Activity Description','Optional Activity Title',10,20,null,'Help Text for Activity', +7,null,1,0, +1,null,'20050101','Offline Instructions ',0, +1,1,null,null, +null,'20050101','20050101','image', +1,0,'Online Instructions',1); + +insert into lams_learning_activity +(activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(13,13,'Parallel Activity Description','Parallel Activity Title',10,20,null,'Help Text for Activity', +6,null,1,0, +1,null,'20050101','Parallel Activity Offline Instructions ',0, +null,null,null,null, +null,null,null,'image', +1,0,'Online Instructions',1); + +insert into lams_learning_activity +(activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(14,14,'Sequence Activity Description','Sequence Activity Title',10,20,null,'Help Text for Activity', +8,null,1,0, +1,null,'20050101','Sequence Activity Offline Instructions ',0, +null,null,null,null, +null,null,null,'image', +1,0,'Online Instructions',1); + +insert into lams_learning_activity +(activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(15,1,'Notebook Activity Description','Notebook Activity Title',10,20,null,'Help Text for Activity', +1,null,1,0, +1,1,'20050101','Notebook Activity Offline Instructions ',0, +null,null,1,12, +null,null,null,'image', +1,0,'Online Instructions',1); + +insert into lams_learning_activity +(activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(16,2,'NoticeBoard Activity','NoticeBoard Activity',10,20,12,'Help Text for Activity', +1,null,0,0, +1,2,'20050101','NoticeBoard Activity Offline Instructions ',0, +null,null,2,13, +null,null,null,'image', +1,0,'Online Instructions',1); + +insert into lams_learning_activity +(activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(17,3,'Multiple Choice Activity ','Multiple Choice',10,20,12,'Help Text for Activity', +1,null,1,0, +1,3,'20050101','Tool Activity Offline Instructions ',0, +null,null,3,14, +null,null,null,'image', +1,0,'Online Instructions',1); + +insert into lams_learning_activity +(activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(18,4,'Question and Answer Activity','Question and Answer',10,20,13,'Help Text for Activity', +1,null,0,0, +1,4,'20050101','Tool Activity Offline Instructions ',0, +null,null,4,15, +null,null,null,'image', +1,1,'Online Instructions',1); + +insert into lams_learning_activity +(activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(19,5,'Message Board Activity','Message Board',10,20,13,'Help Text for Activity', +1,100,1,0, +1,5,'20050101','Tool Activity Offline Instructions ',0, +null,null,5,16, +null,null,null,'image', +1,1,'Online Instructions',1); + +insert into lams_learning_activity +(activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(20,6,'Survey Activity','Survey Title',10,20,null,'Help Text for Activity', +1,null,0,0, +1,6,'20050101','Tool Activity Offline Instructions ',0, +null,null,6,17, +null,null,null,'image', +1,0,'Online Instructions',1); + +insert into lams_learning_activity +(activity_id,activity_ui_id, +description,title,help_text, +xcoord,ycoord, +parent_activity_id,parent_ui_id,learning_activity_type_id, +grouping_id,grouping_ui_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time, +run_offline_flag,offline_instructions, +max_number_of_options,min_number_of_options,options_instructions, +tool_id,tool_content_id, +gate_activity_level_id,gate_open_flag, +gate_start_time_offset,gate_end_time_offset, +gate_start_date_time,gate_end_date_time, +library_activity_ui_image, +create_grouping_id,create_grouping_ui_id, +library_activity_id, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(21,7, +'Chat Activity Description','Chat Title','Help Text for Chat Activity', +10,20, +null,null,1, +99,99,0,0, +1,7,'20050101', +1,'Tool Activity Offline Instructions ', +null,null,null, +7,18, +null,null, +null,null, +null,null, +'ChatImage', +null,null, +7, +1,1,'Online Instructions',1); + +insert into lams_learning_activity +(activity_id,activity_ui_id,description,title,xcoord,ycoord,parent_activity_id,help_text, +learning_activity_type_id,grouping_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time,offline_instructions,run_offline_flag, +max_number_of_options,min_number_of_options,tool_id,tool_content_id, +gate_activity_level_id,gate_start_time_offset,gate_end_time_offset,library_activity_ui_image, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(22,8,'Shared Resources Activity','Shared Resources',10,20,14,'Help Text for Activity', +1,null,0,0, +1,8,'20050101','Tool Activity Offline Instructions ',0, +null,null,8,19, +null,null,null,'image', +1,1,'Online Instructions',1); + +-- Sample GroupingActivity with RandomGrouping + +insert into lams_learning_activity +(activity_id,activity_ui_id, +description,title,help_text, +xcoord,ycoord, +parent_activity_id,parent_ui_id,learning_activity_type_id, +grouping_id,grouping_ui_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time, +run_offline_flag,offline_instructions, +max_number_of_options,min_number_of_options,options_instructions, +tool_id,tool_content_id, +gate_activity_level_id,gate_open_flag, +gate_start_time_offset,gate_end_time_offset, +gate_start_date_time,gate_end_date_time, +library_activity_ui_image, +create_grouping_id,create_grouping_ui_id, +library_activity_id, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(23,23, +'Random Grouping Activity Description','RandomGroupingActivity Title','Help Text for RandomGroupingActivity', +10,20, +null,null,2, +null,null,0,0, +1,null,'20050101', +0,null, +null,null,null, +null,null, +null,null, +null,null, +null,null, +'RandomGroupingActivityImage', +99,99, +null, +1,0,'Online Instructions',1); + +insert into lams_learning_activity +(activity_id,activity_ui_id, +description,title,help_text, +xcoord,ycoord, +parent_activity_id,parent_ui_id,learning_activity_type_id, +grouping_id,grouping_ui_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time, +run_offline_flag,offline_instructions, +max_number_of_options,min_number_of_options,options_instructions, +tool_id,tool_content_id, +gate_activity_level_id,gate_open_flag, +gate_start_time_offset,gate_end_time_offset, +gate_start_date_time,gate_end_date_time, +library_activity_ui_image, +create_grouping_id,create_grouping_ui_id, +library_activity_id, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(24,24, +'QNA Activity Description','QNA Activity Title','Help Text for QNA Activity', +10,20, +null,null,1, +99,99,0,0, +1,4,'20050101', +1,'QNA Activity Offline Instructions ', +null,null,null, +4,20, +null,null, +null,null, +null,null, +'QNAImage', +null,null, +4, +1,1,'Online Instructions',1); + +insert into lams_learning_activity +(activity_id,activity_ui_id, +description,title,help_text, +xcoord,ycoord, +parent_activity_id,parent_ui_id,learning_activity_type_id, +grouping_id,grouping_ui_id,order_id,define_later_flag, +learning_design_id,learning_library_id,create_date_time, +run_offline_flag,offline_instructions, +max_number_of_options,min_number_of_options,options_instructions, +tool_id,tool_content_id, +gate_activity_level_id,gate_open_flag, +gate_start_time_offset,gate_end_time_offset, +gate_start_date_time,gate_end_date_time, +library_activity_ui_image, +create_grouping_id,create_grouping_ui_id, +library_activity_id, +grouping_support_type_id, apply_grouping_flag,online_instructions,activity_category_id) +values +(25,25, +'QNA Activity Description','QNA Activity Title','Help Text for QNA Activity', +10,20, +14,14,1, +null,null,1,0, +1,4,'20050101', +1,'QNA Activity Offline Instructions ', +null,null,null, +4,21, +null,null, +null,null, +null,null, +'QNAImage', +null,null, +4, +1,1,'Online Instructions',1); + +-- Populates the lams_learning_transition table with various transitions for the dummy design + +insert into lams_learning_transition(transition_id,transition_ui_id,description,title,to_activity_id,from_activity_id,learning_design_id,create_date_time,to_ui_id,from_ui_id) +values(1,1,'Survey to NoteBook','Survey to NoteBook',15,20,1,'20050207',1,6); + +insert into lams_learning_transition(transition_id,transition_ui_id,description,title,to_activity_id,from_activity_id,learning_design_id,create_date_time,to_ui_id,from_ui_id) +values(2,2,'Notebook to GroupingActivity','Notebook to GroupingActivity',23,15,1,'20050207',23,1); + +insert into lams_learning_transition(transition_id,transition_ui_id,description,title,to_activity_id,from_activity_id,learning_design_id,create_date_time,to_ui_id,from_ui_id) +values(3,3,'GroupingActivity to Chat','GroupingActivity to Chat',21,23,1,'20050207',7,23); + +insert into lams_learning_transition(transition_id,transition_ui_id,description,title,to_activity_id,from_activity_id,learning_design_id,create_date_time,to_ui_id,from_ui_id) +values(4,4,'Chat to QNA','Chat to QNA',24,21,1,'20050207',24,7); + +insert into lams_learning_transition(transition_id,transition_ui_id,description,title,to_activity_id,from_activity_id,learning_design_id,create_date_time,to_ui_id,from_ui_id) +values(5,5,'QNA to OptionsActivity','QNA to OptionsActivity',12,24,1,'20050207',12,24); + +--insert into lams_learning_transition(transition_id,transition_ui_id,description,title,to_activity_id,from_activity_id,learning_design_id,create_date_time,to_ui_id,from_ui_id) +--values(6,6,'OptionsActivity to Notebook','OptionsActivity to Notebook',16,12,1,'20050207',2,12); + +--insert into lams_learning_transition(transition_id,transition_ui_id,description,title,to_activity_id,from_activity_id,learning_design_id,create_date_time,to_ui_id,from_ui_id) +--values(7,7,'Notebook to MultipleChoice','Notebook to MultipleChoice',17,16,1,'20050207',3,2); + +--insert into lams_learning_transition(transition_id,transition_ui_id,description,title,to_activity_id,from_activity_id,learning_design_id,create_date_time,to_ui_id,from_ui_id) +--values(8,8,'MultipleChoice to ParallelActivity','MultipleChoice to ParallelActivity',13,17,1,'20050207',13,3); + +insert into lams_learning_transition(transition_id,transition_ui_id,description,title,to_activity_id,from_activity_id,learning_design_id,create_date_time,to_ui_id,from_ui_id) +values(6,6,'OptionsActivity to ParallelActivity','OptionsActivity to ParallelActivity',13,12,1,'20050207',13,12); + +--insert into lams_learning_transition(transition_id,transition_ui_id,description,title,to_activity_id,from_activity_id,learning_design_id,create_date_time,to_ui_id,from_ui_id) +--values(9,9,'ParallelActivity to QNA','ParallelActivity to QNA',18,13,1,'20050207',4,13); + +--insert into lams_learning_transition(transition_id,transition_ui_id,description,title,to_activity_id,from_activity_id,learning_design_id,create_date_time,to_ui_id,from_ui_id) +--values(10,10,'QNA to MessageBoard','QNA to MessageBoard',19,18,1,'20050207',5,4); + +insert into lams_learning_transition(transition_id,transition_ui_id,description,title,to_activity_id,from_activity_id,learning_design_id,create_date_time,to_ui_id,from_ui_id) +values(7,7,'ParallelActivity to SequenceActivity','ParallelActivity to SequenceActivity',14,13,1,'20050207',14,13); + +--insert into lams_learning_transition(transition_id,transition_ui_id,description,title,to_activity_id,from_activity_id,learning_design_id,create_date_time,to_ui_id,from_ui_id) +--values(12,12,'SequenceActivity to Shared Resources','SequenceActivity to Shared Resources',22,14,1,'20050207',8,14); + +--insert into lams_learning_transition(transition_id,transition_ui_id,description,title,to_activity_id,from_activity_id,learning_design_id,create_date_time,to_ui_id,from_ui_id) +--values(13,13,'Shared Resources to QNA','Shared Resources to QNA',25,22,1,'20050207',25,8); + + +insert into lams_learning_design(learning_design_ui_id,description,title,first_activity_id,max_id,valid_design_flag, + read_only_flag,user_id,help_text,copy_type_id,create_date_time,version, + parent_learning_design_id,workspace_folder_id) + values + (1,'Test Learning Design','Test Learning Design title',20,1,1,0,1, + 'Help Text',1,'20041223','1.0',null,1); +SET FOREIGN_KEY_CHECKS=1; \ No newline at end of file Index: lams_tool_deploy/test/file/sql/lams_common/insert_types_data.sql =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/test/file/sql/lams_common/Attic/insert_types_data.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_tool_deploy/test/file/sql/lams_common/insert_types_data.sql 7 Apr 2005 06:22:14 -0000 1.1 @@ -0,0 +1,95 @@ +# 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()); +INSERT INTO lams_role VALUES (4, 'STAFF', 'Member of Staff', NOW()); +INSERT INTO lams_role VALUES (5, 'LEARNER', 'Student', NOW()); +INSERT INTO lams_role VALUES (6, 'TEACHER', '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-ORGANISATION', 'sub organization of a base organization'); + +INSERT INTO lams_grouping_type VALUES (1, 'RANDOM_GROUPING'); +INSERT INTO lams_grouping_type VALUES (2, 'CHOSEN_GROUPING'); +INSERT INTO lams_grouping_type VALUES (3, 'CLASS_GROUPING'); + +INSERT INTO lams_tool_session_type VALUES (1, 'NON_GROUPED'); +INSERT INTO lams_tool_session_type VALUES (2, 'GROUPED'); + + +INSERT INTO lams_learning_activity_type VALUES (1, 'TOOL'); +INSERT INTO lams_learning_activity_type VALUES (2, 'GROUPING'); +INSERT INTO lams_learning_activity_type VALUES (3, 'GATE_SYNCH'); +INSERT INTO lams_learning_activity_type VALUES (4, 'GATE_SCHEDULE'); +INSERT INTO lams_learning_activity_type VALUES (5, 'GATE_PERMISSION'); +INSERT INTO lams_learning_activity_type VALUES (6, 'PARALLEL'); +INSERT INTO lams_learning_activity_type VALUES (7, 'OPTIONS'); +INSERT INTO lams_learning_activity_type VALUES (8, 'SEQUENCE'); + + +INSERT INTO lams_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, 'ENDED'); + + +INSERT INTO lams_lesson_state VALUES (1, 'CREATED'); +INSERT INTO lams_lesson_state VALUES (2, 'NOT_STARTED'); +INSERT INTO lams_lesson_state VALUES (3, 'STARTED'); +INSERT INTO lams_lesson_state VALUES (4, 'SUSPENDED'); +INSERT INTO lams_lesson_state VALUES (5, 'FINISHED'); +INSERT INTO lams_lesson_state VALUES (6, 'ARCHIVED'); + +INSERT into lams_license VALUES (1, 'Attribution-Noncommercial-ShareAlike', 'by-nc-sa', 'http://creativecommons.org/licenses/by-nc-sa/2.0/', 1, ''); +INSERT into lams_license VALUES (2, 'Other Licensing Agreement', 'other', '',0, ''); +INSERT into lams_license VALUES (3, 'Attribution-No Derivatives', 'by-nd', 'http://creativecommons.org/licenses/by-nd/2.0/',0,''); +INSERT into lams_license VALUES (4, 'Attribution-Noncommercial-No Derivatives', 'by-nc-nd', 'http://creativecommons.org/licenses/by-nc-nd/2.0/',0, ''); +INSERT into lams_license VALUES (5, 'Attribution-Noncommercial', 'by-nc', 'http://creativecommons.org/licenses/by-nc/2.0/',0,''); +INSERT into lams_license VALUES (6, 'Attribution-ShareAlike', 'by-sa', 'http://creativecommons.org/licenses/by-sa/2.0/',0,''); + +INSERT into lams_copy_type VALUES(1,'NONE'); +INSERT into lams_copy_type VALUES(2,'LESSON'); +INSERT into lams_copy_type VALUES(3,'PREVIEW'); + +INSERT into lams_workspace_folder_type VALUES (1, 'NORMAL'); +INSERT into lams_workspace_folder_type VALUES (2, 'RUN SEQUENCES'); + +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'); + +INSERT INTO lams_activity_category VALUES (1 ,'SYSTEM'); +INSERT INTO lams_activity_category VALUES (2 ,'COLLABORATION'); +INSERT INTO lams_activity_category VALUES (3 ,'ASSESSMENT'); +INSERT INTO lams_activity_category VALUES (4 ,'CONTENT'); +INSERT INTO lams_activity_category VALUES (5 ,'SPLIT'); + +INSERT INTO lams_grouping_support_type VALUES (1 ,'NONE'); +INSERT INTO lams_grouping_support_type VALUES (2 ,'OPTIONAL'); +INSERT INTO lams_grouping_support_type VALUES (3 ,'REQUIRED'); + +INSERT INTO lams_log_event_type VALUES (1, 'LEARNER_LESSON_START'); +INSERT INTO lams_log_event_type VALUES (2, 'LEARNER_LESSON_FINISH'); +INSERT INTO lams_log_event_type VALUES (3, 'LEARNER_LESSON_EXIT'); +INSERT INTO lams_log_event_type VALUES (4, 'LEARNER_LESSON_RESUME'); +INSERT INTO lams_log_event_type VALUES (5, 'LEARNER_ACTIVITY_START'); +INSERT INTO lams_log_event_type VALUES (6, 'LEARNER_ACTIVITY_FINISH'); + + + + + Index: lams_tool_deploy/test/java/org/lamsfoundation/lams/tool/deploy/FileTokenReplacerTest.java =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/test/java/org/lamsfoundation/lams/tool/deploy/Attic/FileTokenReplacerTest.java,v diff -u -r1.2 -r1.3 --- lams_tool_deploy/test/java/org/lamsfoundation/lams/tool/deploy/FileTokenReplacerTest.java 31 Mar 2005 06:54:16 -0000 1.2 +++ lams_tool_deploy/test/java/org/lamsfoundation/lams/tool/deploy/FileTokenReplacerTest.java 7 Apr 2005 06:22:14 -0000 1.3 @@ -116,7 +116,15 @@ System.out.println(output); } + public void testMakeToken() throws Exception + { + assertEquals("${test_id}", FileTokenReplacer.makeToken("test_id")); + } + public void testIsValidToken() throws Exception + { + assertTrue(FileTokenReplacer.isValidToken("${test_id}")); + } public static junit.framework.Test suite() { Index: lams_tool_deploy/test/java/org/lamsfoundation/lams/tool/deploy/ScriptRunnerTest.java =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/test/java/org/lamsfoundation/lams/tool/deploy/Attic/ScriptRunnerTest.java,v diff -u -r1.1 -r1.2 --- lams_tool_deploy/test/java/org/lamsfoundation/lams/tool/deploy/ScriptRunnerTest.java 6 Apr 2005 06:23:05 -0000 1.1 +++ lams_tool_deploy/test/java/org/lamsfoundation/lams/tool/deploy/ScriptRunnerTest.java 7 Apr 2005 06:22:14 -0000 1.2 @@ -41,7 +41,7 @@ { try { - testScript = loadTestScript("test/file/sql/test-script.sql"); + testScript = loadTestScript("test/file/sql/test_script.sql"); DBConnector connector = new DBConnector("test/file/test.properties"); conn = connector.connect(); conn.setAutoCommit(false); @@ -85,7 +85,7 @@ System.out.println(statements[i]); } assertNotNull(statements); - assertEquals(statements.length, 5); + assertEquals(6, statements.length); } Index: lams_tool_deploy/test/java/org/lamsfoundation/lams/tool/deploy/ToolDBActivateTaskTest.java =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/test/java/org/lamsfoundation/lams/tool/deploy/Attic/ToolDBActivateTaskTest.java,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_tool_deploy/test/java/org/lamsfoundation/lams/tool/deploy/ToolDBActivateTaskTest.java 7 Apr 2005 06:22:14 -0000 1.1 @@ -0,0 +1,60 @@ +/* + * ToolDBActivateTaskTest.java + * JUnit based test + * + * Created on 07 April 2005, 16:06 + */ + +package org.lamsfoundation.lams.tool.deploy; + +import junit.framework.*; +import java.sql.Connection; +import java.sql.SQLException; +import java.sql.PreparedStatement; +import org.apache.commons.dbutils.DbUtils; + +/** + * + * @author chris + */ +public class ToolDBActivateTaskTest extends ToolDBTest +{ + + public ToolDBActivateTaskTest(String testName) + { + super(testName); + } + + protected void setUp() throws java.lang.Exception + { + super.setUp(); + insertTestRecords("test/file/test.properties"); + } + + protected void tearDown() throws java.lang.Exception + { + super.tearDown(); + } + + public void testExecute() throws Exception + { + ToolDBActivateTask task = new ToolDBActivateTask(); + task.setDbUsername(props.getString("dbUsername")); + task.setDbPassword(props.getString("dbPassword")); + task.setDbDriverClass(props.getString("dbDriverClass")); + task.setDbDriverUrl(props.getString("dbDriverUrl")); + task.setToolId(1); + task.setLearningLibraryId(1); + task.execute(); + } + + public static junit.framework.Test suite() + { + junit.framework.TestSuite suite = new junit.framework.TestSuite(ToolDBActivateTaskTest.class); + + return suite; + } + + + +} Index: lams_tool_deploy/test/java/org/lamsfoundation/lams/tool/deploy/ToolDBDeployTaskTest.java =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/test/java/org/lamsfoundation/lams/tool/deploy/Attic/ToolDBDeployTaskTest.java,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_tool_deploy/test/java/org/lamsfoundation/lams/tool/deploy/ToolDBDeployTaskTest.java 7 Apr 2005 06:22:14 -0000 1.1 @@ -0,0 +1,98 @@ +/* + * ToolDBDeployTaskTest.java + * JUnit based test + * + * Created on 07 April 2005, 11:21 + */ + +package org.lamsfoundation.lams.tool.deploy; + +import junit.framework.*; +import java.util.Map; +import java.util.HashMap; +import java.io.File; +import java.sql.Connection; +import java.sql.SQLException; +import java.sql.ResultSet; +import java.sql.PreparedStatement; +import org.apache.commons.dbutils.DbUtils; +import org.apache.commons.configuration.PropertiesConfiguration; +import org.apache.commons.io.FileUtils; + +/** + * + * @author chris + */ +public class ToolDBDeployTaskTest extends ToolDBTest +{ + + File toolInsertScript = null; + File libraryInsertScript = null; + File toolCreateScript = null; + File activityInsertScript = null; + + public ToolDBDeployTaskTest(String testName) + { + super(testName); + } + + protected void setUp() throws java.lang.Exception + { + super.setUp(); + toolInsertScript = new File("test/file/sql/tool_insert.sql"); + if (!toolInsertScript.exists()) + { + throw new Exception("can't find test tool insert script!"); + } + libraryInsertScript = new File("test/file/sql/library_insert.sql"); + if (!libraryInsertScript.exists()) + { + throw new Exception("can't find test library insert script!"); + } + toolCreateScript = new File("test/file/sql/create_tool_tables.sql"); + if (!toolCreateScript.exists()) + { + throw new Exception("can't find test tool create script!"); + } + activityInsertScript = new File("test/file/sql/activity_insert.sql"); + if (!activityInsertScript.exists()) + { + throw new Exception("can't find test activity insert script!"); + } + + } + + protected void tearDown() throws java.lang.Exception + { + super.tearDown(); + } + + + + public static junit.framework.Test suite() + { + junit.framework.TestSuite suite = new junit.framework.TestSuite(ToolDBDeployTaskTest.class); + + return suite; + } + + public void testExecute() throws Exception + { + ToolDBDeployTask task = new ToolDBDeployTask(); + task.setDbUsername(props.getString("dbUsername")); + task.setDbPassword(props.getString("dbPassword")); + task.setDbDriverClass(props.getString("dbDriverClass")); + task.setDbDriverUrl(props.getString("dbDriverUrl")); + task.setToolInsertScript(toolInsertScript); + task.setToolLibraryInsertScript(libraryInsertScript); + task.setToolActivityInsertScript(activityInsertScript); + task.setToolTablesScript(toolCreateScript); + task.execute(); + assertTrue(task.getToolId() > 0L); + assertTrue(task.getLearningLibraryId() > 0L); + assertTrue(task.getDefaultContentId() > 0L); + } + + + +} Index: lams_tool_deploy/test/java/org/lamsfoundation/lams/tool/deploy/ToolDBTest.java =================================================================== RCS file: /usr/local/cvsroot/lams_tool_deploy/test/java/org/lamsfoundation/lams/tool/deploy/Attic/ToolDBTest.java,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_tool_deploy/test/java/org/lamsfoundation/lams/tool/deploy/ToolDBTest.java 7 Apr 2005 06:22:14 -0000 1.1 @@ -0,0 +1,123 @@ +/* + * ToolDBTest.java + * + * Created on 07 April 2005, 16:03 + */ + +package org.lamsfoundation.lams.tool.deploy; + +import junit.framework.*; +import java.util.Map; +import java.util.HashMap; +import java.io.File; +import java.sql.Connection; +import java.sql.SQLException; +import java.sql.ResultSet; +import java.sql.PreparedStatement; +import org.apache.commons.dbutils.DbUtils; +import org.apache.commons.configuration.PropertiesConfiguration; +import org.apache.commons.io.FileUtils; +/** + * + * @author chris + */ +public abstract class ToolDBTest extends TestCase +{ + protected PropertiesConfiguration props = null; + + /** Creates a new instance of ToolDBTest */ + public ToolDBTest(String testName) + { + super(testName); + } + + protected void setUp() throws java.lang.Exception + { + props = new PropertiesConfiguration("test/file/test.properties"); + dropLamsDB("test/file/test.properties"); + createLamsDB("test/file/test.properties"); + } + + protected void tearDown() throws java.lang.Exception + { + dropLamsDB("test/file/test.properties"); + } + + protected void createLamsDB(String propsFilePath) throws Exception + { + Connection conn = null; + try + { + DBConnector connector = new DBConnector(propsFilePath); + conn = connector.connect(); + conn.setAutoCommit(false); + File createLamsTables = new File("test/file/sql/lams_common/create_lams_11_tables.sql"); + File insertTypesData = new File("test/file/sql/lams_common/insert_types_data.sql"); + + ScriptRunner createRunner = new ScriptRunner(FileUtils.readFileToString(createLamsTables, "UTF8"),conn); + createRunner.run(); + + ScriptRunner typesRunner = new ScriptRunner(FileUtils.readFileToString(insertTypesData, "UTF8"),conn); + typesRunner.run(); + conn.commit(); + } + catch (Exception ex) + { + conn.rollback(); + throw ex; + } + finally + { + DbUtils.closeQuietly(conn); + } + } + + protected void dropLamsDB(String propsFilePath) throws Exception + { + Connection conn = null; + try + { + DBConnector connector = new DBConnector(propsFilePath); + conn = connector.connect(); + conn.setAutoCommit(false); + File dropLamsTables = new File("test/file/sql/lams_common/drop_lams_11_tables.sql"); + ScriptRunner dropRunner = new ScriptRunner(FileUtils.readFileToString(dropLamsTables, "UTF8"),conn); + dropRunner.run(); + conn.commit(); + } + catch (Exception ex) + { + conn.rollback(); + throw ex; + } + finally + { + DbUtils.closeQuietly(conn); + } + } + + protected void insertTestRecords(String propsFilePath) throws Exception + { + Connection conn = null; + try + { + DBConnector connector = new DBConnector(propsFilePath); + conn = connector.connect(); + conn.setAutoCommit(false); + File insertTestRecords = new File("test/file/sql/insert_test_records.sql"); + ScriptRunner insertRunner = new ScriptRunner(FileUtils.readFileToString(insertTestRecords , "UTF8"),conn); + insertRunner.run(); + conn.commit(); + } + catch (Exception ex) + { + conn.rollback(); + throw ex; + } + finally + { + DbUtils.closeQuietly(conn); + } + } + +}