1 ###############################################################
\r
2 # Database UTF-8 database conversion for ATutor < 1.6
\r
3 # This script only applies to those databases that have UTF-8
\r
4 # data in non-UTF-8 tables.
\r
5 # ref: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
\r
7 ###############################################################
\r
9 # 1.6 UTF 8 Conversion to Binary
\r
10 ALTER TABLE `admins` MODIFY COLUMN `real_name` BLOB;
\r
11 ALTER TABLE `assignments` MODIFY COLUMN `title` BLOB;
\r
12 ALTER TABLE `backups` MODIFY COLUMN `description` BLOB, MODIFY COLUMN `file_name` BLOB;
\r
13 ALTER TABLE `blog_posts` MODIFY COLUMN `title` BLOB;
\r
14 ALTER TABLE `content` MODIFY COLUMN `keywords` BLOB, MODIFY COLUMN `content_path` BLOB,
\r
15 MODIFY COLUMN `title` BLOB;
\r
16 ALTER TABLE `courses` MODIFY COLUMN `title` BLOB;
\r
17 ALTER TABLE `course_cats` MODIFY COLUMN `cat_name` BLOB;
\r
18 ALTER TABLE `external_resources` MODIFY COLUMN `title` BLOB, MODIFY COLUMN `author` BLOB,
\r
19 MODIFY COLUMN `publisher` BLOB, MODIFY COLUMN `comments` BLOB;
\r
20 ALTER TABLE `faq_entries` MODIFY COLUMN `question` BLOB;
\r
21 ALTER TABLE `faq_topics` MODIFY COLUMN `name` BLOB;
\r
22 ALTER TABLE `folders` MODIFY COLUMN `title` BLOB;
\r
23 ALTER TABLE `forums` MODIFY COLUMN `title` BLOB;
\r
24 ALTER TABLE `forums_threads` MODIFY COLUMN `subject` BLOB;
\r
25 ALTER TABLE `glossary` MODIFY COLUMN `word` BLOB;
\r
26 ALTER TABLE `groups` MODIFY COLUMN `title` BLOB;
\r
27 ALTER TABLE `groups_types` MODIFY COLUMN `title` BLOB;
\r
28 ALTER TABLE `links` MODIFY COLUMN `Description` BLOB,
\r
29 MODIFY COLUMN `SubmitName` BLOB, MODIFY COLUMN `SubmitEmail` BLOB;
\r
30 ALTER TABLE `links_categories` MODIFY COLUMN `name` BLOB;
\r
31 ALTER TABLE `mail_queue` MODIFY COLUMN `subject` BLOB;
\r
32 ALTER TABLE `members` MODIFY COLUMN `first_name` BLOB, MODIFY COLUMN `second_name` BLOB,
\r
33 MODIFY COLUMN `last_name` BLOB, MODIFY COLUMN `address` BLOB, MODIFY COLUMN `city` BLOB,
\r
34 MODIFY COLUMN `province` BLOB, MODIFY COLUMN `country` BLOB;
\r
35 ALTER TABLE `messages` MODIFY COLUMN `subject` BLOB;
\r
36 ALTER TABLE `messages_sent` MODIFY COLUMN `subject` BLOB;
\r
37 ALTER TABLE `news` MODIFY COLUMN `title` BLOB;
\r
38 ALTER TABLE `polls` MODIFY COLUMN `question` BLOB, MODIFY COLUMN `choice1` BLOB,
\r
39 MODIFY COLUMN `choice2` BLOB, MODIFY COLUMN `choice3` BLOB, MODIFY COLUMN `choice4` BLOB,
\r
40 MODIFY COLUMN `choice5` BLOB, MODIFY COLUMN `choice6` BLOB, MODIFY COLUMN `choice7` BLOB;
\r
41 ALTER TABLE `tests` MODIFY COLUMN `title` BLOB;
\r
42 ALTER TABLE `tests_questions` MODIFY COLUMN `choice_0` BLOB,
\r
43 MODIFY COLUMN `choice_1` BLOB,
\r
44 MODIFY COLUMN `choice_2` BLOB,
\r
45 MODIFY COLUMN `choice_3` BLOB,
\r
46 MODIFY COLUMN `choice_4` BLOB,
\r
47 MODIFY COLUMN `choice_5` BLOB,
\r
48 MODIFY COLUMN `choice_6` BLOB,
\r
49 MODIFY COLUMN `choice_7` BLOB,
\r
50 MODIFY COLUMN `choice_8` BLOB,
\r
51 MODIFY COLUMN `choice_9` BLOB,
\r
52 MODIFY COLUMN `option_0` BLOB,
\r
53 MODIFY COLUMN `option_1` BLOB,
\r
54 MODIFY COLUMN `option_2` BLOB,
\r
55 MODIFY COLUMN `option_3` BLOB,
\r
56 MODIFY COLUMN `option_4` BLOB,
\r
57 MODIFY COLUMN `option_5` BLOB,
\r
58 MODIFY COLUMN `option_6` BLOB,
\r
59 MODIFY COLUMN `option_7` BLOB,
\r
60 MODIFY COLUMN `option_8` BLOB,
\r
61 MODIFY COLUMN `option_9` BLOB;
\r
64 # 1.6 UTF 8 table VARCHAR * 4 changes
\r
65 ALTER TABLE `admins` MODIFY COLUMN `real_name` VARCHAR(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
66 ALTER TABLE `assignments` MODIFY COLUMN `title` VARCHAR(240) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
67 ALTER TABLE `backups` MODIFY COLUMN `description` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
68 MODIFY COLUMN `file_name` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
69 ALTER TABLE `blog_posts` MODIFY COLUMN `title` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
70 ALTER TABLE `content` MODIFY COLUMN `keywords` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
71 MODIFY COLUMN `content_path` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
72 MODIFY COLUMN `title` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
73 ALTER TABLE `courses` MODIFY COLUMN `title` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
74 ALTER TABLE `course_cats` MODIFY COLUMN `cat_name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
75 ALTER TABLE `external_resources` MODIFY COLUMN `title` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
76 MODIFY COLUMN `author` VARCHAR(150) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
77 MODIFY COLUMN `publisher` VARCHAR(150) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
78 MODIFY COLUMN `comments` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
79 ALTER TABLE `faq_entries` MODIFY COLUMN `question` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
80 ALTER TABLE `faq_topics` MODIFY COLUMN `name` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
81 ALTER TABLE `folders` MODIFY COLUMN `title` VARCHAR(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
82 ALTER TABLE `forums` MODIFY COLUMN `title` VARCHAR(240) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
83 ALTER TABLE `forums_threads` MODIFY COLUMN `subject` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
84 ALTER TABLE `glossary` MODIFY COLUMN `word` VARCHAR(240) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
85 ALTER TABLE `groups` MODIFY COLUMN `title` VARCHAR(80) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
86 ALTER TABLE `groups_types` MODIFY COLUMN `title` VARCHAR(80) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
87 ALTER TABLE `links` MODIFY COLUMN `Description` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
88 MODIFY COLUMN `SubmitName` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
89 MODIFY COLUMN `SubmitEmail` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
90 ALTER TABLE `links_categories` MODIFY COLUMN `name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
91 ALTER TABLE `mail_queue` MODIFY COLUMN `subject` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
92 ALTER TABLE `members` MODIFY COLUMN `first_name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
93 MODIFY COLUMN `second_name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
94 MODIFY COLUMN `last_name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
95 MODIFY COLUMN `address` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
96 MODIFY COLUMN `city` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
97 MODIFY COLUMN `province` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
98 MODIFY COLUMN `country` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
99 ALTER TABLE `messages` MODIFY COLUMN `subject` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
100 ALTER TABLE `messages_sent` MODIFY COLUMN `subject` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
101 ALTER TABLE `news` MODIFY COLUMN `title` VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
102 ALTER TABLE `polls` MODIFY COLUMN `question` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
103 MODIFY COLUMN `choice1` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
104 MODIFY COLUMN `choice2` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
105 MODIFY COLUMN `choice3` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
106 MODIFY COLUMN `choice4` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
107 MODIFY COLUMN `choice5` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
108 MODIFY COLUMN `choice6` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
109 MODIFY COLUMN `choice7` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
110 ALTER TABLE `tests` MODIFY COLUMN `title` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
111 ALTER TABLE `tests_questions` MODIFY COLUMN `choice_0` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
112 MODIFY COLUMN `choice_1` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
113 MODIFY COLUMN `choice_2` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
114 MODIFY COLUMN `choice_3` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
115 MODIFY COLUMN `choice_4` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
116 MODIFY COLUMN `choice_5` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
117 MODIFY COLUMN `choice_6` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
118 MODIFY COLUMN `choice_7` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
119 MODIFY COLUMN `choice_8` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
120 MODIFY COLUMN `choice_9` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
121 MODIFY COLUMN `option_0` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
122 MODIFY COLUMN `option_1` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
123 MODIFY COLUMN `option_2` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
124 MODIFY COLUMN `option_3` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
125 MODIFY COLUMN `option_4` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
126 MODIFY COLUMN `option_5` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
127 MODIFY COLUMN `option_6` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
128 MODIFY COLUMN `option_7` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
129 MODIFY COLUMN `option_8` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
130 MODIFY COLUMN `option_9` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
131 ALTER TABLE `tests_questions_categories` MODIFY COLUMN `title` CHAR(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
132 ALTER TABLE `themes` MODIFY COLUMN `title` VARCHAR(80) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
\r
133 MODIFY COLUMN `extra_info` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
\r
135 # 1.6.1 Change all table to UTF-8 collation
\r
136 ALTER TABLE `admins` CONVERT TO CHARACTER SET utf8;
\r
137 ALTER TABLE `admin_log` CONVERT TO CHARACTER SET utf8;
\r
138 ALTER TABLE `assignments` CONVERT TO CHARACTER SET utf8;
\r
139 ALTER TABLE `auto_enroll` CONVERT TO CHARACTER SET utf8;
\r
140 ALTER TABLE `auto_enroll_courses` CONVERT TO CHARACTER SET utf8;
\r
141 ALTER TABLE `backups` CONVERT TO CHARACTER SET utf8;
\r
142 ALTER TABLE `blog_posts` CONVERT TO CHARACTER SET utf8;
\r
143 ALTER TABLE `blog_posts_comments` CONVERT TO CHARACTER SET utf8;
\r
144 ALTER TABLE `config` CONVERT TO CHARACTER SET utf8;
\r
145 ALTER TABLE `content` CONVERT TO CHARACTER SET utf8;
\r
146 ALTER TABLE `courses` CONVERT TO CHARACTER SET utf8;
\r
147 ALTER TABLE `course_access` CONVERT TO CHARACTER SET utf8;
\r
148 ALTER TABLE `course_cats` CONVERT TO CHARACTER SET utf8;
\r
149 ALTER TABLE `course_enrollment` CONVERT TO CHARACTER SET utf8;
\r
150 ALTER TABLE `course_stats` CONVERT TO CHARACTER SET utf8;
\r
151 ALTER TABLE `external_resources` CONVERT TO CHARACTER SET utf8;
\r
152 ALTER TABLE `faq_entries` CONVERT TO CHARACTER SET utf8;
\r
153 ALTER TABLE `faq_topics` CONVERT TO CHARACTER SET utf8;
\r
154 ALTER TABLE `feeds` CONVERT TO CHARACTER SET utf8;
\r
155 ALTER TABLE `files` CONVERT TO CHARACTER SET utf8;
\r
156 ALTER TABLE `files_comments` CONVERT TO CHARACTER SET utf8;
\r
157 ALTER TABLE `file_storage_groups` CONVERT TO CHARACTER SET utf8;
\r
158 ALTER TABLE `folders` CONVERT TO CHARACTER SET utf8;
\r
159 ALTER TABLE `forums` CONVERT TO CHARACTER SET utf8;
\r
160 ALTER TABLE `forums_accessed` CONVERT TO CHARACTER SET utf8;
\r
161 ALTER TABLE `forums_courses` CONVERT TO CHARACTER SET utf8;
\r
162 ALTER TABLE `forums_groups` CONVERT TO CHARACTER SET utf8;
\r
163 ALTER TABLE `forums_subscriptions` CONVERT TO CHARACTER SET utf8;
\r
164 ALTER TABLE `forums_threads` CONVERT TO CHARACTER SET utf8;
\r
165 ALTER TABLE `glossary` CONVERT TO CHARACTER SET utf8;
\r
166 ALTER TABLE `groups` CONVERT TO CHARACTER SET utf8;
\r
167 ALTER TABLE `groups_members` CONVERT TO CHARACTER SET utf8;
\r
168 ALTER TABLE `groups_types` CONVERT TO CHARACTER SET utf8;
\r
169 ALTER TABLE `handbook_notes` CONVERT TO CHARACTER SET utf8;
\r
170 ALTER TABLE `instructor_approvals` CONVERT TO CHARACTER SET utf8;
\r
171 ALTER TABLE `languages` CONVERT TO CHARACTER SET utf8;
\r
172 ALTER TABLE `language_pages` CONVERT TO CHARACTER SET utf8;
\r
173 ALTER TABLE `language_text` CONVERT TO CHARACTER SET utf8;
\r
174 ALTER TABLE `links` CONVERT TO CHARACTER SET utf8;
\r
175 ALTER TABLE `links_categories` CONVERT TO CHARACTER SET utf8;
\r
176 ALTER TABLE `mail_queue` CONVERT TO CHARACTER SET utf8;
\r
177 ALTER TABLE `master_list` CONVERT TO CHARACTER SET utf8;
\r
178 ALTER TABLE `members` CONVERT TO CHARACTER SET utf8;
\r
179 ALTER TABLE `member_track` CONVERT TO CHARACTER SET utf8;
\r
180 ALTER TABLE `messages` CONVERT TO CHARACTER SET utf8;
\r
181 ALTER TABLE `messages_sent` CONVERT TO CHARACTER SET utf8;
\r
182 ALTER TABLE `modules` CONVERT TO CHARACTER SET utf8;
\r
183 ALTER TABLE `news` CONVERT TO CHARACTER SET utf8;
\r
184 ALTER TABLE `polls` CONVERT TO CHARACTER SET utf8;
\r
185 ALTER TABLE `polls_members` CONVERT TO CHARACTER SET utf8;
\r
186 ALTER TABLE `reading_list` CONVERT TO CHARACTER SET utf8;
\r
187 ALTER TABLE `related_content` CONVERT TO CHARACTER SET utf8;
\r
188 ALTER TABLE `tests` CONVERT TO CHARACTER SET utf8;
\r
189 ALTER TABLE `tests_answers` CONVERT TO CHARACTER SET utf8;
\r
190 ALTER TABLE `tests_groups` CONVERT TO CHARACTER SET utf8;
\r
191 ALTER TABLE `tests_questions` CONVERT TO CHARACTER SET utf8;
\r
192 ALTER TABLE `tests_questions_assoc` CONVERT TO CHARACTER SET utf8;
\r
193 ALTER TABLE `tests_questions_categories` CONVERT TO CHARACTER SET utf8;
\r
194 ALTER TABLE `tests_results` CONVERT TO CHARACTER SET utf8;
\r
195 ALTER TABLE `themes` CONVERT TO CHARACTER SET utf8;
\r
196 ALTER TABLE `users_online` CONVERT TO CHARACTER SET utf8;
\r