migrate-db-1.9.0-1.10.0-pre.sql 17 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249
  1. -- This script updates the databases structure before migrating the data from
  2. -- version 1.9.0 (or version 1.9.2, 1.9.4) to version 1.10.0
  3. -- it is intended as a standalone script, however, because of the multiple
  4. -- databases related difficulties, it should be parsed by a PHP script in
  5. -- order to connect to and update the right databases.
  6. -- There is one line per query, allowing the PHP function file() to read
  7. -- all lines separately into an array. The xxMAINxx-type markers are there
  8. -- to tell the PHP script which database we're talking about.
  9. -- By always using the keyword "TABLE" in the queries, we should be able
  10. -- to retrieve and modify the table name from the PHP script if needed, which
  11. -- will allow us to deal with the unique-database-type installations
  12. -- xxMAINxx
  13. -- Optimize tracking query very often queried on busy campuses
  14. ALTER TABLE track_e_online ADD INDEX idx_trackonline_uat (login_user_id, access_url_id, login_date);
  15. ALTER TABLE track_e_default ADD COLUMN session_id INT NOT NULL DEFAULT 0;
  16. INSERT INTO settings_current (variable, subkey, type, category, selected_value, title, comment, scope, subkeytext, access_url_changeable) VALUES ('session_tutor_reports_visibility', NULL, 'radio', 'Session', 'true', 'SessionTutorsCanSeeExpiredSessionsResultsTitle', 'SessionTutorsCanSeeExpiredSessionsResultsComment', NULL, NULL, 1);
  17. INSERT INTO settings_options (variable, value, display_text) VALUES ('session_tutor_reports_visibility', 'true', 'Yes');
  18. INSERT INTO settings_options (variable, value, display_text) VALUES ('session_tutor_reports_visibility', 'false', 'No');
  19. INSERT INTO settings_current (variable, subkey, type, category, selected_value, title, comment, scope, subkeytext, access_url_changeable) VALUES ('gradebook_show_percentage_in_reports', NULL,'radio','Gradebook','true','GradebookShowPercentageInReportsTitle','GradebookShowPercentageInReportsComment', NULL, NULL, 0);
  20. INSERT INTO settings_options (variable, value, display_text) VALUES ('gradebook_show_percentage_in_reports', 'true', 'Yes');
  21. INSERT INTO settings_options (variable, value, display_text) VALUES ('gradebook_show_percentage_in_reports', 'false', 'No');
  22. ALTER TABLE notification ADD COLUMN sender_id INT NOT NULL DEFAULT 0;
  23. ALTER TABLE session_rel_user ADD COLUMN moved_to INT NOT NULL DEFAULT 0;
  24. ALTER TABLE session_rel_user ADD COLUMN moved_status INT NOT NULL DEFAULT 0;
  25. ALTER TABLE session_rel_user ADD COLUMN moved_at datetime NOT NULL default '0000-00-00 00:00:00';
  26. ALTER TABLE session ADD COLUMN display_start_date datetime NOT NULL default '0000-00-00 00:00:00';
  27. ALTER TABLE session ADD COLUMN display_end_date datetime NOT NULL default '0000-00-00 00:00:00';
  28. ALTER TABLE session ADD COLUMN access_start_date datetime NOT NULL default '0000-00-00 00:00:00';
  29. ALTER TABLE session ADD COLUMN access_end_date datetime NOT NULL default '0000-00-00 00:00:00';
  30. ALTER TABLE session ADD COLUMN coach_access_start_date datetime NOT NULL default '0000-00-00 00:00:00';
  31. ALTER TABLE session ADD COLUMN coach_access_end_date datetime NOT NULL default '0000-00-00 00:00:00';
  32. ALTER TABLE grade_components ADD COLUMN prefix VARCHAR(255) DEFAULT NULL;
  33. ALTER TABLE grade_components ADD COLUMN exclusions INT DEFAULT 0;
  34. ALTER TABLE grade_components ADD COLUMN count_elements INT DEFAULT 0;
  35. CREATE TABLE IF NOT EXISTS session_field_options (id int NOT NULL auto_increment, field_id int NOT NULL, option_value text, option_display_text varchar(255), option_order int, tms DATETIME NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (id));
  36. CREATE TABLE IF NOT EXISTS course_field_options (id int NOT NULL auto_increment, field_id int NOT NULL, option_value text, option_display_text varchar(255), option_order int, tms DATETIME NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (id));
  37. ALTER TABLE session_field_options ADD INDEX idx_session_field_options_field_id(field_id);
  38. ALTER TABLE session_field_values ADD INDEX idx_session_field_values_session_id(session_id);
  39. ALTER TABLE session_field_values ADD INDEX idx_session_field_values_field_id(field_id);
  40. ALTER TABLE session MODIFY COLUMN name CHAR(150) NOT NULL DEFAULT '';
  41. ALTER TABLE session MODIFY COLUMN id INT unsigned NOT NULL;
  42. ALTER TABLE session_rel_course MODIFY COLUMN id_session INT unsigned NOT NULL;
  43. ALTER TABLE session_rel_course ADD COLUMN c_id INT NOT NULL DEFAULT '0';
  44. ALTER TABLE session_rel_course DROP PRIMARY KEY;
  45. -- remove course_code
  46. ALTER TABLE session_rel_course ADD COLUMN id INT NOT NULL;
  47. ALTER TABLE session_rel_course MODIFY COLUMN id int unsigned AUTO_INCREMENT;
  48. ALTER TABLE session_rel_course ADD INDEX idx_session_rel_course_course_id (c_id);
  49. ALTER TABLE session_rel_course ADD PRIMARY KEY (id);
  50. ALTER TABLE session_rel_course_rel_user MODIFY COLUMN id_session INT unsigned NOT NULL;
  51. ALTER TABLE session_rel_course_rel_user ADD COLUMN c_id INT NOT NULL DEFAULT '0';
  52. ALTER TABLE session_rel_course_rel_user ADD COLUMN id INT NOT NULL;
  53. ALTER TABLE session_rel_course_rel_user DROP PRIMARY KEY;
  54. ALTER TABLE session_rel_course_rel_user ADD PRIMARY KEY (id);
  55. ALTER TABLE session_rel_course_rel_user ADD INDEX idx_session_rel_course_rel_user_id_user (id_user);
  56. ALTER TABLE session_rel_course_rel_user ADD INDEX idx_session_rel_course_rel_user_course_id (c_id);
  57. ALTER TABLE session_rel_user ADD INDEX idx_session_rel_user_id_user_moved (id_user, moved_to);
  58. -- Courses changes c_XXX
  59. -- ALTER TABLE c_lp_item ADD INDEX idx_c_lp_item_cid_lp_id (c_id, lp_id);
  60. -- ALTER TABLE c_lp_item_view ADD INDEX idx_c_lp_item_view_cid_lp_view_id_lp_item_id(c_id, lp_view_id, lp_item_id);
  61. ALTER TABLE c_item_property ADD INDEX idx_itemprop_id_tool (c_id, tool(8));
  62. ALTER TABLE c_tool_intro MODIFY COLUMN intro_text MEDIUMTEXT NOT NULL;
  63. ALTER TABLE c_quiz_answer ADD INDEX idx_quiz_answer_c_q (c_id, question_id);
  64. CREATE TABLE c_quiz_order( iid bigint unsigned NOT NULL auto_increment, c_id int unsigned NOT NULL, session_id int unsigned NOT NULL, exercise_id int NOT NULL, exercise_order INT NOT NULL, PRIMARY KEY (iid));
  65. CREATE TABLE c_quiz_question_rel_category ( iid int unsigned NOT NULL AUTO_INCREMENT, c_id int NOT NULL, question_id int NOT NULL, category_id int NOT NULL, PRIMARY KEY (iid));
  66. ALTER TABLE session ADD INDEX idx_id_coach (id_coach);
  67. ALTER TABLE session ADD INDEX idx_id_session_admin_id (session_admin_id);
  68. ALTER TABLE c_quiz_question ADD COLUMN parent_id INT unsigned NOT NULL DEFAULT 0;
  69. CREATE TABLE IF NOT EXISTS gradebook_evaluation_type(id INT unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT, name varchar(255), external_id INT unsigned NOT NULL DEFAULT 0);
  70. ALTER TABLE gradebook_evaluation ADD COLUMN evaluation_type_id INT NOT NULL DEFAULT 0;
  71. ALTER TABLE gradebook_link ADD COLUMN evaluation_type_id INT NOT NULL DEFAULT 0;
  72. INSERT INTO settings_options(variable, value) VALUES ('last_transaction_id','0');
  73. ALTER TABLE access_url ADD COLUMN url_type tinyint unsigned default 1;
  74. CREATE TABLE branch_sync( id int unsigned not null AUTO_INCREMENT PRIMARY KEY, access_url_id int unsigned not null, branch_name varchar(250) default '', branch_ip varchar(40) default '', latitude decimal(15,7), longitude decimal(15,7), dwn_speed int unsigned default null, up_speed int unsigned default null, delay int unsigned default null, admin_mail varchar(250) default '', admin_name varchar(250) default '', admin_phone varchar(250) default '', last_sync_trans_id bigint unsigned default 0, last_sync_trans_date datetime, last_sync_type char(20) default 'full');
  75. CREATE TABLE branch_sync_log( id bigint unsigned not null AUTO_INCREMENT PRIMARY KEY, branch_sync_id int unsigned not null, sync_trans_id bigint unsigned default 0, sync_trans_date datetime, sync_type char(20));
  76. CREATE TABLE branch_transaction_status (id tinyint not null PRIMARY KEY AUTO_INCREMENT, title char(20));
  77. INSERT INTO branch_transaction_status VALUES (1, 'To be executed'), (2, 'Executed successfully'), (3, 'Execution deprecated'), (4, 'Execution failed');
  78. CREATE TABLE branch_transaction (id bigint unsigned not null AUTO_INCREMENT, transaction_id bigint unsigned, branch_id int unsigned not null default 0, action char(20), item_id char(36), orig_id char(36), dest_id char(36), info char(20), status_id tinyint not null default 0, time_insert datetime NOT NULL DEFAULT '0000-00-00 00:00:00', time_update datetime NOT NULL DEFAULT '0000-00-00 00:00:00', message VARCHAR(255) default '' , PRIMARY KEY (id, transaction_id, branch_id));
  79. ALTER TABLE settings_current ADD INDEX idx_settings_current_au_cat (access_url, category(5));
  80. INSERT INTO settings_current (variable, subkey, type, category, selected_value, title, comment, scope, subkeytext, access_url_changeable) VALUES ('session_page_enabled', NULL, 'radio', 'Session', 'true', 'SessionPageEnabledTitle', 'SessionPageEnabledComment', NULL, NULL, 1);
  81. INSERT INTO settings_options (variable, value, display_text) VALUES ('session_page_enabled', 'true', 'Yes');
  82. INSERT INTO settings_options (variable, value, display_text) VALUES ('session_page_enabled', 'false', 'No');
  83. INSERT INTO settings_current (variable, subkey, type, category, selected_value, title, comment, scope, subkeytext, access_url_changeable) VALUES ('settings_latest_update', NULL, NULL, NULL, '', '','', NULL, NULL, 0);
  84. ALTER TABLE course_module change `row` row_module int unsigned NOT NULL default '0';
  85. ALTER TABLE course_module change `column` column_module int unsigned NOT NULL default '0';
  86. ALTER TABLE c_survey_invitation ADD COLUMN group_id INT NOT NULL DEFAULT 0;
  87. ALTER TABLE c_lp ADD COLUMN category_id INT unsigned NOT NULL default 0;
  88. ALTER TABLE c_lp ADD COLUMN max_attempts INT NOT NULL default 0;
  89. ALTER TABLE c_lp ADD COLUMN subscribe_users INT NOT NULL default 0;
  90. CREATE TABLE c_lp_category (id int unsigned NOT NULL auto_increment, c_id INT unsigned NOT NULL, name VARCHAR(255), position INT, PRIMARY KEY (id));
  91. ALTER TABLE user MODIFY COLUMN hr_dept_id int unsigned NOT NULL default 0;
  92. ALTER TABLE session MODIFY COLUMN id INT unsigned NOT NULL auto_increment;
  93. ALTER TABLE session MODIFY COLUMN nbr_courses int unsigned NOT NULL default 0;
  94. ALTER TABLE session MODIFY COLUMN nbr_users int unsigned NOT NULL default 0;
  95. ALTER TABLE session MODIFY COLUMN nbr_classes int unsigned NOT NULL default 0;
  96. ALTER TABLE session_rel_course MODIFY COLUMN nbr_users int unsigned NOT NULL default 0;
  97. ALTER TABLE track_e_exercices MODIFY COLUMN session_id int unsigned NOT NULL default 0;
  98. ALTER TABLE track_e_exercices MODIFY COLUMN exe_exo_id int unsigned NOT NULL default 0;
  99. ALTER TABLE course_rel_user ADD COLUMN c_id int unsigned NOT NULL default 0;
  100. ALTER TABLE course_rel_user DROP PRIMARY KEY;
  101. ALTER TABLE course_rel_user ADD COLUMN id int unsigned AUTO_INCREMENT, ADD PRIMARY KEY (id);
  102. ALTER TABLE course_rel_user ADD INDEX (c_id, user_id);
  103. ALTER TABLE c_item_property MODIFY id INT NOT NULL;
  104. ALTER TABLE c_item_property DROP PRIMARY KEY;
  105. ALTER TABLE c_item_property DROP COLUMN id;
  106. ALTER TABLE c_item_property ADD COLUMN id bigint unsigned NOT NULL auto_increment, ADD PRIMARY KEY (id);
  107. ALTER TABLE c_item_property MODIFY id_session INT default NULL;
  108. ALTER TABLE c_item_property MODIFY COLUMN start_visible datetime default NULL;
  109. ALTER TABLE c_item_property MODIFY COLUMN end_visible datetime default NULL;
  110. INSERT INTO settings_current (variable, subkey, type, category, selected_value, title, comment, scope, subkeytext, access_url_changeable) VALUES ('user_name_order', NULL, 'textfield', 'Platform', '', 'UserNameOrderTitle', 'UserNameOrderComment', NULL, NULL, 1);
  111. ALTER TABLE c_group_info MODIFY id INT NOT NULL;
  112. ALTER TABLE c_group_info MODIFY c_id INT NOT NULL;
  113. ALTER TABLE c_group_info MODIFY session_id INT NOT NULL;
  114. ALTER TABLE c_group_info DROP PRIMARY KEY;
  115. ALTER TABLE c_group_info ADD COLUMN iid INT unsigned NOT NULL auto_increment PRIMARY KEY;
  116. INSERT INTO settings_current (variable, subkey, type, category, selected_value, title, comment, scope, subkeytext, access_url_changeable) VALUES ('allow_teachers_to_create_sessions', NULL,'radio','Session','false','AllowTeachersToCreateSessionsTitle','AllowTeachersToCreateSessionsComment', NULL, NULL, 0);
  117. INSERT INTO settings_options (variable, value, display_text) VALUES ('allow_teachers_to_create_sessions', 'true', 'Yes');
  118. INSERT INTO settings_options (variable, value, display_text) VALUES ('allow_teachers_to_create_sessions', 'false', 'No');
  119. UPDATE course_field SET field_type = 3 WHERE field_variable = 'special_course';
  120. ALTER TABLE usergroup ADD COLUMN group_type INT unsigned NOT NULL default 0;
  121. ALTER TABLE usergroup ADD COLUMN picture varchar(255) NOT NULL;
  122. ALTER TABLE usergroup ADD COLUMN url varchar(255) NOT NULL;
  123. ALTER TABLE usergroup ADD COLUMN visibility varchar(255) NOT NULL;
  124. ALTER TABLE usergroup ADD COLUMN updated_on varchar(255) NOT NULL;
  125. ALTER TABLE usergroup ADD COLUMN created_on varchar(255) NOT NULL;
  126. CREATE TABLE IF NOT EXISTS usergroup_rel_tag( id int NOT NULL AUTO_INCREMENT, tag_id int NOT NULL, usergroup_id int NOT NULL, PRIMARY KEY (id));
  127. ALTER TABLE usergroup_rel_tag ADD INDEX ( usergroup_id );
  128. ALTER TABLE usergroup_rel_tag ADD INDEX ( tag_id );
  129. ALTER TABLE usergroup_rel_user ADD relation_type int NOT NULL default 0;
  130. ALTER TABLE usergroup_rel_user ADD INDEX ( usergroup_id );
  131. ALTER TABLE usergroup_rel_user ADD INDEX ( user_id );
  132. ALTER TABLE usergroup_rel_user ADD INDEX ( relation_type );
  133. CREATE TABLE IF NOT EXISTS usergroup_rel_usergroup (id int NOT NULL AUTO_INCREMENT, group_id int NOT NULL, subgroup_id int NOT NULL, relation_type int NOT NULL, PRIMARY KEY (id));
  134. ALTER TABLE usergroup_rel_usergroup ADD INDEX ( group_id );
  135. ALTER TABLE usergroup_rel_usergroup ADD INDEX ( subgroup_id );
  136. ALTER TABLE usergroup_rel_usergroup ADD INDEX ( relation_type );
  137. ALTER TABLE announcement_rel_group DROP PRIMARY KEY;
  138. ALTER TABLE announcement_rel_group ADD COLUMN id INT unsigned NOT NULL auto_increment PRIMARY KEY;
  139. ALTER TABLE track_e_hotpotatoes ADD COLUMN c_id int unsigned NOT NULL default 0;
  140. ALTER TABLE track_e_exercices ADD COLUMN c_id int unsigned NOT NULL default 0;
  141. ALTER TABLE track_e_attempt ADD COLUMN c_id int unsigned NOT NULL default 0;
  142. ALTER TABLE track_e_hotspot ADD COLUMN c_id int unsigned NOT NULL default 0;
  143. ALTER TABLE track_e_course_access ADD COLUMN c_id INT NOT NULL DEFAULT 0;
  144. ALTER TABLE access_url_rel_course ADD COLUMN c_id int unsigned NOT NULL default 0;
  145. ALTER TABLE track_e_lastaccess ADD COLUMN c_id int unsigned NOT NULL default 0;
  146. ALTER TABLE track_e_access ADD COLUMN c_id int unsigned NOT NULL default 0;
  147. ALTER TABLE track_e_downloads ADD COLUMN c_id int unsigned NOT NULL default 0;
  148. ALTER TABLE track_e_links ADD COLUMN c_id int unsigned NOT NULL default 0;
  149. ALTER TABLE track_e_course_access MODIFY COLUMN course_access_id bigint unsigned;
  150. ALTER TABLE track_e_lastaccess ADD INDEX ( c_id, access_user_id ) ;
  151. ALTER TABLE c_quiz ADD COLUMN autolaunch int DEFAULT 0;
  152. RENAME TABLE c_quiz_question_category TO c_quiz_category;
  153. ALTER TABLE c_quiz_category ADD COLUMN parent_id int unsigned NOT NULL default 0;
  154. CREATE TABLE c_quiz_rel_category (iid bigint unsigned NOT NULL auto_increment, c_id INT unsigned default 0, category_id int unsigned NOT NULL, exercise_id int unsigned NOT NULL, count_questions int NOT NULL default 0, PRIMARY KEY(iid));
  155. ALTER TABLE c_quiz DROP INDEX session_id;
  156. ALTER TABLE c_quiz MODIFY id INT NOT NULL;
  157. ALTER TABLE c_quiz MODIFY c_id INT NOT NULL;
  158. ALTER TABLE c_quiz DROP PRIMARY KEY;
  159. ALTER TABLE c_quiz ADD COLUMN iid INT unsigned NOT NULL auto_increment PRIMARY KEY;
  160. ALTER TABLE c_quiz_question MODIFY id INT NOT NULL;
  161. ALTER TABLE c_quiz_question MODIFY c_id INT NOT NULL;
  162. ALTER TABLE c_quiz_question DROP PRIMARY KEY;
  163. ALTER TABLE c_quiz_question ADD COLUMN iid INT unsigned NOT NULL auto_increment PRIMARY KEY;
  164. ALTER TABLE c_quiz_answer MODIFY id INT NOT NULL;
  165. ALTER TABLE c_quiz_answer MODIFY c_id INT NOT NULL;
  166. ALTER TABLE c_quiz_answer MODIFY id_auto INT NOT NULL;
  167. ALTER TABLE c_quiz_answer DROP PRIMARY KEY;
  168. ALTER TABLE c_quiz_answer ADD COLUMN iid INT unsigned NOT NULL auto_increment PRIMARY KEY;
  169. ALTER TABLE c_quiz_question_option MODIFY id INT NOT NULL;
  170. ALTER TABLE c_quiz_question_option MODIFY c_id INT NOT NULL;
  171. ALTER TABLE c_quiz_question_option DROP PRIMARY KEY;
  172. ALTER TABLE c_quiz_question_option ADD COLUMN iid INT unsigned NOT NULL auto_increment PRIMARY KEY;
  173. ALTER TABLE c_quiz_rel_question MODIFY id INT NOT NULL;
  174. ALTER TABLE c_quiz_rel_question MODIFY question_id INT NOT NULL;
  175. ALTER TABLE c_quiz_rel_question MODIFY exercice_id INT NOT NULL;
  176. ALTER TABLE c_quiz_rel_question DROP PRIMARY KEY;
  177. ALTER TABLE c_quiz_rel_question ADD COLUMN iid INT unsigned NOT NULL auto_increment PRIMARY KEY;
  178. ALTER TABLE c_quiz_category MODIFY id INT NOT NULL;
  179. ALTER TABLE c_quiz_category MODIFY c_id INT NOT NULL;
  180. ALTER TABLE c_quiz_category DROP PRIMARY KEY;
  181. ALTER TABLE c_quiz_category ADD COLUMN iid INT unsigned NOT NULL auto_increment PRIMARY KEY;
  182. -- Do not move this
  183. UPDATE settings_current SET selected_value = '1.10.0.001' WHERE variable = 'chamilo_database_version';