3 # Debian-specific script to upgrade the database between releases
4 # Roland Mas <lolando@debian.org>
13 use vars qw/$dbh @reqlist $query/ ;
14 use vars qw/$sys_default_domain $sys_scm_host $sys_download_host
15 $sys_shell_host $sys_users_host $sys_docs_host $sys_lists_host
16 $sys_dns1_host $sys_dns2_host $FTPINCOMING_DIR $FTPFILES_DIR
17 $sys_urlroot $sf_cache_dir $sys_name $sys_themeroot
18 $sys_news_group $sys_dbhost $sys_dbname $sys_dbuser $sys_dbpasswd
19 $sys_ldap_base_dn $sys_ldap_host $admin_login $admin_password
20 $server_admin $domain_name $newsadmin_groupid $statsadmin_groupid
24 sub convert_column_to_charset ( $$$$$ ) ;
26 require ("/usr/share/gforge/lib/include.pl") ; # Include a few predefined functions
27 require ("/usr/share/gforge/lib/sqlparser.pm") ; # Our magic SQL parser
29 debug "You'll see some debugging info during this installation." ;
30 debug "Do not worry unless told otherwise." ;
34 # debug "Connected to the database OK." ;
36 $dbh->{AutoCommit} = 0;
37 $dbh->{RaiseError} = 1;
39 my $from = "latin-1" ;
42 convert_column_to_charset ('canned_responses', 'response_title', $from, $to, 25) ;
43 convert_column_to_charset ('canned_responses', 'response_text', $from, $to, -1) ;
45 convert_column_to_charset ('db_images', 'description', $from, $to, -1) ;
46 convert_column_to_charset ('db_images', 'bin_data', $from, $to, -1) ;
47 convert_column_to_charset ('db_images', 'filename', $from, $to, -1) ;
48 convert_column_to_charset ('db_images', 'filetype', $from, $to, -1) ;
50 convert_column_to_charset ('doc_data', 'title', $from, $to, 255) ;
51 convert_column_to_charset ('doc_data', 'data', $from, $to, -1) ;
52 convert_column_to_charset ('doc_data', 'description', $from, $to, -1) ;
53 convert_column_to_charset ('doc_data', 'filename', $from, $to, -1) ;
54 convert_column_to_charset ('doc_data', 'filetype', $from, $to, -1) ;
56 convert_column_to_charset ('doc_groups', 'groupname', $from, $to, 255) ;
58 convert_column_to_charset ('doc_states', 'name', $from, $to, 255) ;
60 convert_column_to_charset ('forum', 'subject', $from, $to, -1) ;
61 convert_column_to_charset ('forum', 'body', $from, $to, -1) ;
63 convert_column_to_charset ('forum_group_list', 'forum_name', $from, $to, -1) ;
64 convert_column_to_charset ('forum_group_list', 'description', $from, $to, -1) ;
65 convert_column_to_charset ('forum_group_list', 'send_all_posts_to', $from, $to, -1) ;
67 convert_column_to_charset ('frs_file', 'filename', $from, $to, -1) ;
69 convert_column_to_charset ('frs_filetype', 'name', $from, $to, -1) ;
71 convert_column_to_charset ('frs_package', 'name', $from, $to, -1) ;
73 convert_column_to_charset ('frs_processor', 'name', $from, $to, -1) ;
75 convert_column_to_charset ('frs_release', 'name', $from, $to, -1) ;
76 convert_column_to_charset ('frs_release', 'notes', $from, $to, -1) ;
77 convert_column_to_charset ('frs_release', 'changes', $from, $to, -1) ;
79 convert_column_to_charset ('frs_status', 'name', $from, $to, -1) ;
81 convert_column_to_charset ('group_history', 'field_name', $from, $to, -1) ;
82 convert_column_to_charset ('group_history', 'old_value', $from, $to, -1) ;
84 convert_column_to_charset ('groups', 'group_name', $from, $to, 40) ;
85 convert_column_to_charset ('groups', 'homepage', $from, $to, 128) ;
86 convert_column_to_charset ('groups', 'unix_group_name', $from, $to, 30) ;
87 convert_column_to_charset ('groups', 'unix_box', $from, $to, 20) ;
88 convert_column_to_charset ('groups', 'http_domain', $from, $to, 80) ;
89 convert_column_to_charset ('groups', 'short_description', $from, $to, 255) ;
90 convert_column_to_charset ('groups', 'cvs_box', $from, $to, 20) ;
91 convert_column_to_charset ('groups', 'license', $from, $to, 16) ;
92 convert_column_to_charset ('groups', 'register_purpose', $from, $to, -1) ;
93 convert_column_to_charset ('groups', 'license_other', $from, $to, -1) ;
94 convert_column_to_charset ('groups', 'rand_hash', $from, $to, -1) ;
95 convert_column_to_charset ('groups', 'new_doc_address', $from, $to, -1) ;
97 convert_column_to_charset ('mail_group_list', 'list_name', $from, $to, -1) ;
98 convert_column_to_charset ('mail_group_list', 'password', $from, $to, 16) ;
99 convert_column_to_charset ('mail_group_list', 'description', $from, $to, -1) ;
101 convert_column_to_charset ('news_bytes', 'summary', $from, $to, -1) ;
102 convert_column_to_charset ('news_bytes', 'details', $from, $to, -1) ;
104 convert_column_to_charset ('people_job', 'title', $from, $to, -1) ;
105 convert_column_to_charset ('people_job', 'description', $from, $to, -1) ;
107 convert_column_to_charset ('people_job_category', 'name', $from, $to, -1) ;
109 convert_column_to_charset ('people_job_status', 'name', $from, $to, -1) ;
111 convert_column_to_charset ('people_skill', 'name', $from, $to, -1) ;
113 convert_column_to_charset ('people_skill_level', 'name', $from, $to, -1) ;
115 convert_column_to_charset ('people_skill_year', 'name', $from, $to, -1) ;
117 convert_column_to_charset ('project_group_list', 'project_name', $from, $to, -1) ;
118 convert_column_to_charset ('project_group_list', 'description', $from, $to, -1) ;
119 convert_column_to_charset ('project_group_list', 'send_all_posts_to', $from, $to, -1) ;
121 convert_column_to_charset ('project_history', 'field_name', $from, $to, -1) ;
122 convert_column_to_charset ('project_history', 'old_value', $from, $to, -1) ;
124 convert_column_to_charset ('project_status', 'status_name', $from, $to, -1) ;
126 convert_column_to_charset ('project_task', 'summary', $from, $to, -1) ;
127 convert_column_to_charset ('project_task', 'details', $from, $to, -1) ;
129 convert_column_to_charset ('snippet', 'name', $from, $to, -1) ;
130 convert_column_to_charset ('snippet', 'description', $from, $to, -1) ;
131 convert_column_to_charset ('snippet', 'license', $from, $to, -1) ;
133 convert_column_to_charset ('snippet_package', 'name', $from, $to, -1) ;
134 convert_column_to_charset ('snippet_package', 'description', $from, $to, -1) ;
136 convert_column_to_charset ('snippet_package_version', 'changes', $from, $to, -1) ;
137 convert_column_to_charset ('snippet_package_version', 'version', $from, $to, -1) ;
139 convert_column_to_charset ('snippet_version', 'changes', $from, $to, -1) ;
140 convert_column_to_charset ('snippet_version', 'version', $from, $to, -1) ;
141 convert_column_to_charset ('snippet_version', 'code', $from, $to, -1) ;
143 convert_column_to_charset ('survey_question_types', 'type', $from, $to, -1) ;
145 convert_column_to_charset ('survey_questions', 'question', $from, $to, -1) ;
147 convert_column_to_charset ('survey_responses', 'response', $from, $to, -1) ;
149 convert_column_to_charset ('surveys', 'survey_title', $from, $to, -1) ;
150 convert_column_to_charset ('surveys', 'survey_questions', $from, $to, -1) ;
152 convert_column_to_charset ('trove_cat', 'shortname', $from, $to, 80) ;
153 convert_column_to_charset ('trove_cat', 'fullname', $from, $to, 80) ;
154 convert_column_to_charset ('trove_cat', 'description', $from, $to, 255) ;
155 convert_column_to_charset ('trove_cat', 'fullpath', $from, $to, -1) ;
156 convert_column_to_charset ('trove_cat', 'fullpath_ids', $from, $to, -1) ;
158 convert_column_to_charset ('user_bookmarks', 'bookmark_url', $from, $to, -1) ;
159 convert_column_to_charset ('user_bookmarks', 'bookmark_title', $from, $to, -1) ;
161 convert_column_to_charset ('user_diary', 'summary', $from, $to, -1) ;
162 convert_column_to_charset ('user_diary', 'details', $from, $to, -1) ;
164 convert_column_to_charset ('user_preferences', 'preference_name', $from, $to, 20) ;
165 convert_column_to_charset ('user_preferences', 'preference_value', $from, $to, -1) ;
167 convert_column_to_charset ('users', 'user_name', $from, $to, -1) ;
168 convert_column_to_charset ('users', 'email', $from, $to, -1) ;
169 convert_column_to_charset ('users', 'user_pw', $from, $to, 32) ;
170 convert_column_to_charset ('users', 'realname', $from, $to, 32) ;
171 convert_column_to_charset ('users', 'shell', $from, $to, 20) ;
172 convert_column_to_charset ('users', 'unix_pw', $from, $to, 40) ;
173 convert_column_to_charset ('users', 'unix_box', $from, $to, 10) ;
174 convert_column_to_charset ('users', 'confirm_hash', $from, $to, 32) ;
175 convert_column_to_charset ('users', 'authorized_keys', $from, $to, -1) ;
176 convert_column_to_charset ('users', 'email_new', $from, $to, -1) ;
177 convert_column_to_charset ('users', 'people_resume', $from, $to, -1) ;
178 convert_column_to_charset ('users', 'timezone', $from, $to, 64) ;
179 convert_column_to_charset ('users', 'jabber_address', $from, $to, -1) ;
181 convert_column_to_charset ('prdb_dbs', 'dbname', $from, $to, -1) ;
182 convert_column_to_charset ('prdb_dbs', 'dbusername', $from, $to, -1) ;
183 convert_column_to_charset ('prdb_dbs', 'dbuserpass', $from, $to, -1) ;
185 convert_column_to_charset ('prdb_states', 'statename', $from, $to, -1) ;
187 convert_column_to_charset ('prdb_types', 'dbservername', $from, $to, -1) ;
188 convert_column_to_charset ('prdb_types', 'dbsoftware', $from, $to, -1) ;
190 convert_column_to_charset ('prweb_vhost', 'vhost_name', $from, $to, -1) ;
191 convert_column_to_charset ('prweb_vhost', 'docdir', $from, $to, -1) ;
192 convert_column_to_charset ('prweb_vhost', 'cgidir', $from, $to, -1) ;
194 convert_column_to_charset ('artifact_group_list', 'name', $from, $to, -1) ;
195 convert_column_to_charset ('artifact_group_list', 'description', $from, $to, -1) ;
196 convert_column_to_charset ('artifact_group_list', 'email_address', $from, $to, -1) ;
197 convert_column_to_charset ('artifact_group_list', 'submit_instructions', $from, $to, -1) ;
198 convert_column_to_charset ('artifact_group_list', 'browse_instructions', $from, $to, -1) ;
200 convert_column_to_charset ('artifact_resolution', 'resolution_name', $from, $to, -1) ;
202 convert_column_to_charset ('artifact_category', 'category_name', $from, $to, -1) ;
204 convert_column_to_charset ('artifact_group', 'group_name', $from, $to, -1) ;
206 convert_column_to_charset ('artifact_status', 'status_name', $from, $to, -1) ;
208 convert_column_to_charset ('artifact', 'summary', $from, $to, -1) ;
209 convert_column_to_charset ('artifact', 'details', $from, $to, -1) ;
211 convert_column_to_charset ('artifact_history', 'field_name', $from, $to, -1) ;
212 convert_column_to_charset ('artifact_history', 'old_value', $from, $to, -1) ;
214 convert_column_to_charset ('artifact_file', 'description', $from, $to, -1) ;
215 convert_column_to_charset ('artifact_file', 'bin_data', $from, $to, -1) ;
216 convert_column_to_charset ('artifact_file', 'filename', $from, $to, -1) ;
217 convert_column_to_charset ('artifact_file', 'filetype', $from, $to, -1) ;
219 convert_column_to_charset ('artifact_message', 'from_email', $from, $to, -1) ;
220 convert_column_to_charset ('artifact_message', 'body', $from, $to, -1) ;
222 convert_column_to_charset ('artifact_monitor', 'email', $from, $to, -1) ;
224 convert_column_to_charset ('artifact_canned_responses', 'title', $from, $to, -1) ;
225 convert_column_to_charset ('artifact_canned_responses', 'body', $from, $to, -1) ;
227 convert_column_to_charset ('massmail_queue', 'type', $from, $to, 8) ;
228 convert_column_to_charset ('massmail_queue', 'subject', $from, $to, -1) ;
229 convert_column_to_charset ('massmail_queue', 'message', $from, $to, -1) ;
231 convert_column_to_charset ('activity_log', 'browser', $from, $to, 8) ;
232 convert_column_to_charset ('activity_log', 'platform', $from, $to, 8) ;
233 convert_column_to_charset ('activity_log', 'page', $from, $to, -1) ;
235 convert_column_to_charset ('trove_agg', 'group_name', $from, $to, 40) ;
236 convert_column_to_charset ('trove_agg', 'unix_group_name', $from, $to, 30) ;
237 convert_column_to_charset ('trove_agg', 'short_description', $from, $to, 255) ;
239 convert_column_to_charset ('frs_dlstats_file', 'ip_address', $from, $to, -1) ;
241 convert_column_to_charset ('group_cvs_history', 'user_name', $from, $to, 80) ;
243 convert_column_to_charset ('themes', 'dirname', $from, $to, 80) ;
244 convert_column_to_charset ('themes', 'fullname', $from, $to, 80) ;
246 convert_column_to_charset ('supported_languages', 'name', $from, $to, -1) ;
247 convert_column_to_charset ('supported_languages', 'filename', $from, $to, -1) ;
248 convert_column_to_charset ('supported_languages', 'classname', $from, $to, -1) ;
250 convert_column_to_charset ('skills_data_types', 'type_name', $from, $to, 25) ;
252 convert_column_to_charset ('skills_data', 'title', $from, $to, 100) ;
253 convert_column_to_charset ('skills_data', 'keywords', $from, $to, 255) ;
255 convert_column_to_charset ('project_category', 'category_name', $from, $to, -1) ;
257 convert_column_to_charset ('project_messages', 'body', $from, $to, -1) ;
259 convert_column_to_charset ('plugins', 'plugin_name', $from, $to, 32) ;
260 convert_column_to_charset ('plugins', 'plugin_desc', $from, $to, -1) ;
264 debug "It seems your database conversion went well and smoothly. That's cool." ;
265 debug "Please enjoy using Gforge." ;
267 # There should be a commit at the end of every block above.
268 # If there is not, then it might be symptomatic of a problem.
269 # For safety, we roll back.
274 warn "Transaction aborted because $@" ;
275 debug "Transaction aborted because $@" ;
276 debug "Last SQL query was:\n$query\n(end of query)" ;
278 debug "Please report this bug on the Debian bug-tracking system." ;
279 debug "Please include the previous messages as well to help debugging." ;
280 debug "You should not worry too much about this," ;
281 debug "your DB is still in a consistent state and should be usable." ;
288 sub convert_column_to_charset ( $$$$$ ) {
289 my $table = shift or die "Not enough arguments" ;
290 my $column = shift or die "Not enough arguments" ;
291 my $from = shift or die "Not enough arguments" ;
292 my $to = shift or die "Not enough arguments" ;
293 my $size = shift or die "Not enough arguments" ;
296 $query = "UPDATE $table SET $column = substr (convert ($column, '$from', '$to'), 0, $size)" ;
298 $query = "UPDATE $table SET $column = convert ($column, '$from', '$to')" ;
301 my $sth = $dbh->prepare ($query) ;
309 print STDERR "$v\n" ;