?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Rubber-stamped by Brady Eidson.
[webbrowser.git] / BugsSite / Bugzilla / Install / DB.pm
blob1bda135f25598a1428e85df39647440526b86413
1 # -*- Mode: perl; indent-tabs-mode: nil -*-
2 #
3 # The contents of this file are subject to the Mozilla Public
4 # License Version 1.1 (the "License"); you may not use this file
5 # except in compliance with the License. You may obtain a copy of
6 # the License at http://www.mozilla.org/MPL/
7 #
8 # Software distributed under the License is distributed on an "AS
9 # IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
10 # implied. See the License for the specific language governing
11 # rights and limitations under the License.
12 #
13 # The Original Code is the Bugzilla Bug Tracking System.
14 #
15 # Contributor(s): Max Kanat-Alexander <mkanat@bugzilla.org>
16 # Noel Cragg <noel@red-bean.com>
17
18 package Bugzilla::Install::DB;
19
20 # NOTE: This package may "use" any modules that it likes,
21 # localconfig is available, and params are up to date.
22
23 use strict;
24
25 use Bugzilla::Constants;
26 use Bugzilla::Hook;
27 use Bugzilla::Install::Util qw(indicate_progress install_string);
28 use Bugzilla::Util;
29 use Bugzilla::Series;
30
31 use Date::Parse;
32 use Date::Format;
33 use IO::File;
34
35 # NOTE: This is NOT the function for general table updates. See
36 # update_table_definitions for that. This is only for the fielddefs table.
37 sub update_fielddefs_definition {
38 my $dbh = Bugzilla->dbh;
39
40 # 2005-02-21 - LpSolit@gmail.com - Bug 279910
41 # qacontact_accessible and assignee_accessible field names no longer exist
42 # in the 'bugs' table. Their corresponding entries in the 'bugs_activity'
43 # table should therefore be marked as obsolete, meaning that they cannot
44 # be used anymore when querying the database - they are not deleted in
45 # order to keep track of these fields in the activity table.
46 if (!$dbh->bz_column_info('fielddefs', 'obsolete')) {
47 $dbh->bz_add_column('fielddefs', 'obsolete',
48 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'});
49 print "Marking qacontact_accessible and assignee_accessible as",
50 " obsolete fields...\n";
51 $dbh->do("UPDATE fielddefs SET obsolete = 1
52 WHERE name = 'qacontact_accessible'
53 OR name = 'assignee_accessible'");
54 }
55
56 # 2005-08-10 Myk Melez <myk@mozilla.org> bug 287325
57 # Record each field's type and whether or not it's a custom field,
58 # in fielddefs.
59 $dbh->bz_add_column('fielddefs', 'type',
60 {TYPE => 'INT2', NOTNULL => 1, DEFAULT => 0});
61 $dbh->bz_add_column('fielddefs', 'custom',
62 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'});
63
64 $dbh->bz_add_column('fielddefs', 'enter_bug',
65 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'});
66
67 # Change the name of the fieldid column to id, so that fielddefs
68 # can use Bugzilla::Object easily. We have to do this up here, because
69 # otherwise adding these field definitions will fail.
70 $dbh->bz_rename_column('fielddefs', 'fieldid', 'id');
71
72 # If the largest fielddefs sortkey is less than 100, then
73 # we're using the old sorting system, and we should convert
74 # it to the new one before adding any new definitions.
75 if (!$dbh->selectrow_arrayref(
76 'SELECT COUNT(id) FROM fielddefs WHERE sortkey >= 100'))
77 {
78 print "Updating the sortkeys for the fielddefs table...\n";
79 my $field_ids = $dbh->selectcol_arrayref(
80 'SELECT id FROM fielddefs ORDER BY sortkey');
81 my $sortkey = 100;
82 foreach my $field_id (@$field_ids) {
83 $dbh->do('UPDATE fielddefs SET sortkey = ? WHERE id = ?',
84 undef, $sortkey, $field_id);
85 $sortkey += 100;
86 }
87 }
88
89 # Remember, this is not the function for adding general table changes.
90 # That is below. Add new changes to the fielddefs table above this
91 # comment.
92 }
93
94 # Small changes can be put directly into this function.
95 # However, larger changes (more than three or four lines) should
96 # go into their own private subroutine, and you should call that
97 # subroutine from this function. That keeps this function readable.
98 #
99 # This function runs in historical order--from upgrades that older
100 # installations need, to upgrades that newer installations need.
101 # The order of items inside this function should only be changed if
102 # absolutely necessary.
103 #
104 # The subroutines should have long, descriptive names, so that you
105 # can easily see what is being done, just by reading this function.
106 #
107 # This function is mostly self-documenting. If you're curious about
108 # what each of the added/removed columns does, you should see the schema
109 # docs at:
110 # http://www.ravenbrook.com/project/p4dti/tool/cgi/bugzilla-schema/
111 #
112 # When you add a change, you should only add a comment if you want
113 # to describe why the change was made. You don't need to describe
114 # the purpose of a column.
115 #
116 sub update_table_definitions {
117 my $old_params = shift;
118 my $dbh = Bugzilla->dbh;
119 _update_pre_checksetup_bugzillas();
120
121 $dbh->bz_add_column('attachments', 'submitter_id',
122 {TYPE => 'INT3', NOTNULL => 1}, 0);
123
124 $dbh->bz_rename_column('bugs_activity', 'when', 'bug_when');
125
126 _add_bug_vote_cache();
127 _update_product_name_definition();
128 _add_bug_keyword_cache();
129
130 $dbh->bz_add_column('profiles', 'disabledtext',
131 {TYPE => 'MEDIUMTEXT', NOTNULL => 1}, '');
132
133 _populate_longdescs();
134 _update_bugs_activity_field_to_fieldid();
135
136 if (!$dbh->bz_column_info('bugs', 'lastdiffed')) {
137 $dbh->bz_add_column('bugs', 'lastdiffed', {TYPE =>'DATETIME'});
138 $dbh->do('UPDATE bugs SET lastdiffed = NOW()');
139 }
140
141 _add_unique_login_name_index_to_profiles();
142
143 $dbh->bz_add_column('profiles', 'mybugslink',
144 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'TRUE'});
145
146 _update_component_user_fields_to_ids();
147
148 $dbh->bz_add_column('bugs', 'everconfirmed',
149 {TYPE => 'BOOLEAN', NOTNULL => 1}, 1);
150
151 $dbh->bz_add_column('products', 'maxvotesperbug',
152 {TYPE => 'INT2', NOTNULL => 1, DEFAULT => '10000'});
153 $dbh->bz_add_column('products', 'votestoconfirm',
154 {TYPE => 'INT2', NOTNULL => 1}, 0);
155
156 _populate_milestones_table();
157
158 # 2000-03-22 Changed the default value for target_milestone to be "---"
159 # (which is still not quite correct, but much better than what it was
160 # doing), and made the size of the value field in the milestones table match
161 # the size of the target_milestone field in the bugs table.
162 $dbh->bz_alter_column('bugs', 'target_milestone',
163 {TYPE => 'varchar(20)', NOTNULL => 1, DEFAULT => "'---'"});
164 $dbh->bz_alter_column('milestones', 'value',
165 {TYPE => 'varchar(20)', NOTNULL => 1});
166
167 _add_products_defaultmilestone();
168
169 # 2000-03-24 Added unique indexes into the cc and keyword tables. This
170 # prevents certain database inconsistencies, and, moreover, is required for
171 # new generalized list code to work.
172 if (!$dbh->bz_index_info('cc', 'cc_bug_id_idx')
173 || !$dbh->bz_index_info('cc', 'cc_bug_id_idx')->{TYPE})
174 {
175 $dbh->bz_drop_index('cc', 'cc_bug_id_idx');
176 $dbh->bz_add_index('cc', 'cc_bug_id_idx',
177 {TYPE => 'UNIQUE', FIELDS => [qw(bug_id who)]});
178 }
179 if (!$dbh->bz_index_info('keywords', 'keywords_bug_id_idx')
180 || !$dbh->bz_index_info('keywords', 'keywords_bug_id_idx')->{TYPE})
181 {
182 $dbh->bz_drop_index('keywords', 'keywords_bug_id_idx');
183 $dbh->bz_add_index('keywords', 'keywords_bug_id_idx',
184 {TYPE => 'UNIQUE', FIELDS => [qw(bug_id keywordid)]});
185 }
186
187 _copy_from_comments_to_longdescs();
188 _populate_duplicates_table();
189
190 if (!$dbh->bz_column_info('email_setting', 'user_id')) {
191 $dbh->bz_add_column('profiles', 'emailflags', {TYPE => 'MEDIUMTEXT'});
192 }
193
194 $dbh->bz_add_column('groups', 'isactive',
195 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'TRUE'});
196
197 $dbh->bz_add_column('attachments', 'isobsolete',
198 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'});
199
200 $dbh->bz_drop_column("profiles", "emailnotification");
201 $dbh->bz_drop_column("profiles", "newemailtech");
202
203 # 2003-11-19; chicks@chicks.net; bug 225973: fix field size to accommodate
204 # wider algorithms such as Blowfish. Note that this needs to be run
205 # before recrypting passwords in the following block.
206 $dbh->bz_alter_column('profiles', 'cryptpassword',
207 {TYPE => 'varchar(128)'});
208
209 _recrypt_plaintext_passwords();
210
211 # 2001-06-15 kiko@async.com.br - Change bug:version size to avoid
212 # truncates re http://bugzilla.mozilla.org/show_bug.cgi?id=9352
213 $dbh->bz_alter_column('bugs', 'version',
214 {TYPE => 'varchar(64)', NOTNULL => 1});
215
216 _update_bugs_activity_to_only_record_changes();
217
218 # bug 90933: Make disabledtext NOT NULL
219 if (!$dbh->bz_column_info('profiles', 'disabledtext')->{NOTNULL}) {
220 $dbh->bz_alter_column("profiles", "disabledtext",
221 {TYPE => 'MEDIUMTEXT', NOTNULL => 1}, '');
222 }
223
224 $dbh->bz_add_column("bugs", "reporter_accessible",
225 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'TRUE'});
226 $dbh->bz_add_column("bugs", "cclist_accessible",
227 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'TRUE'});
228
229 $dbh->bz_add_column("bugs_activity", "attach_id", {TYPE => 'INT3'});
230
231 _delete_logincookies_cryptpassword_and_handle_invalid_cookies();
232
233 # qacontact/assignee should always be able to see bugs: bug 97471
234 $dbh->bz_drop_column("bugs", "qacontact_accessible");
235 $dbh->bz_drop_column("bugs", "assignee_accessible");
236
237 # 2002-02-20 jeff.hedlund@matrixsi.com - bug 24789 time tracking
238 $dbh->bz_add_column("longdescs", "work_time",
239 {TYPE => 'decimal(5,2)', NOTNULL => 1, DEFAULT => '0'});
240 $dbh->bz_add_column("bugs", "estimated_time",
241 {TYPE => 'decimal(5,2)', NOTNULL => 1, DEFAULT => '0'});
242 $dbh->bz_add_column("bugs", "remaining_time",
243 {TYPE => 'decimal(5,2)', NOTNULL => 1, DEFAULT => '0'});
244 $dbh->bz_add_column("bugs", "deadline", {TYPE => 'DATETIME'});
245
246 _use_ip_instead_of_hostname_in_logincookies();
247
248 $dbh->bz_add_column('longdescs', 'isprivate',
249 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'});
250 $dbh->bz_add_column('attachments', 'isprivate',
251 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'});
252
253 $dbh->bz_add_column("bugs", "alias", {TYPE => "varchar(20)"});
254 $dbh->bz_add_index('bugs', 'bugs_alias_idx',
255 {TYPE => 'UNIQUE', FIELDS => [qw(alias)]});
256
257 _move_quips_into_db();
258
259 $dbh->bz_drop_column("namedqueries", "watchfordiffs");
260
261 _use_ids_for_products_and_components();
262 _convert_groups_system_from_groupset();
263 _convert_attachment_statuses_to_flags();
264 _remove_spaces_and_commas_from_flagtypes();
265 _setup_usebuggroups_backward_compatibility();
266 _remove_user_series_map();
267
268 # 2006-08-03 remi_zara@mac.com bug 346241, make series.creator nullable
269 # This must happen before calling _copy_old_charts_into_database().
270 if ($dbh->bz_column_info('series', 'creator')->{NOTNULL}) {
271 $dbh->bz_alter_column('series', 'creator', {TYPE => 'INT3'});
272 $dbh->do("UPDATE series SET creator = NULL WHERE creator = 0");
273 }
274
275 _copy_old_charts_into_database();
276
277 _add_user_group_map_grant_type();
278 _add_group_group_map_grant_type();
279
280 $dbh->bz_add_column("profiles", "extern_id", {TYPE => 'varchar(64)'});
281
282 $dbh->bz_add_column('flagtypes', 'grant_group_id', {TYPE => 'INT3'});
283 $dbh->bz_add_column('flagtypes', 'request_group_id', {TYPE => 'INT3'});
284
285 # mailto is no longer just userids
286 $dbh->bz_rename_column('whine_schedules', 'mailto_userid', 'mailto');
287 $dbh->bz_add_column('whine_schedules', 'mailto_type',
288 {TYPE => 'INT2', NOTNULL => 1, DEFAULT => '0'});
289
290 _add_longdescs_already_wrapped();
291
292 # Moved enum types to separate tables so we need change the old enum
293 # types to standard varchars in the bugs table.
294 $dbh->bz_alter_column('bugs', 'bug_status',
295 {TYPE => 'varchar(64)', NOTNULL => 1});
296 # 2005-03-23 Tomas.Kopal@altap.cz - add default value to resolution,
297 # bug 286695
298 $dbh->bz_alter_column('bugs', 'resolution',
299 {TYPE => 'varchar(64)', NOTNULL => 1, DEFAULT => "''"});
300 $dbh->bz_alter_column('bugs', 'priority',
301 {TYPE => 'varchar(64)', NOTNULL => 1});
302 $dbh->bz_alter_column('bugs', 'bug_severity',
303 {TYPE => 'varchar(64)', NOTNULL => 1});
304 $dbh->bz_alter_column('bugs', 'rep_platform',
305 {TYPE => 'varchar(64)', NOTNULL => 1}, '');
306 $dbh->bz_alter_column('bugs', 'op_sys',
307 {TYPE => 'varchar(64)', NOTNULL => 1});
308
309 # When migrating quips from the '$datadir/comments' file to the DB,
310 # the user ID should be NULL instead of 0 (which is an invalid user ID).
311 if ($dbh->bz_column_info('quips', 'userid')->{NOTNULL}) {
312 $dbh->bz_alter_column('quips', 'userid', {TYPE => 'INT3'});
313 print "Changing owner to NULL for quips where the owner is",
314 " unknown...\n";
315 $dbh->do('UPDATE quips SET userid = NULL WHERE userid = 0');
316 }
317
318 _convert_attachments_filename_from_mediumtext();
319
320 $dbh->bz_add_column('quips', 'approved',
321 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'TRUE'});
322
323 # 2002-12-20 Bug 180870 - remove manual shadowdb replication code
324 $dbh->bz_drop_table("shadowlog");
325
326 _rename_votes_count_and_force_group_refresh();
327
328 # 2004/02/15 - Summaries shouldn't be null - see bug 220232
329 if (!exists $dbh->bz_column_info('bugs', 'short_desc')->{NOTNULL}) {
330 $dbh->bz_alter_column('bugs', 'short_desc',
331 {TYPE => 'MEDIUMTEXT', NOTNULL => 1}, '');
332 }
333
334 $dbh->bz_add_column('products', 'classification_id',
335 {TYPE => 'INT2', NOTNULL => 1, DEFAULT => '1'});
336
337 _fix_group_with_empty_name();
338
339 $dbh->bz_add_index('bugs_activity', 'bugs_activity_who_idx', [qw(who)]);
340
341 # Add defaults for some fields that should have them but didn't.
342 $dbh->bz_alter_column('bugs', 'status_whiteboard',
343 {TYPE => 'MEDIUMTEXT', NOTNULL => 1, DEFAULT => "''"});
344 $dbh->bz_alter_column('bugs', 'keywords',
345 {TYPE => 'MEDIUMTEXT', NOTNULL => 1, DEFAULT => "''"});
346 $dbh->bz_alter_column('bugs', 'votes',
347 {TYPE => 'INT3', NOTNULL => 1, DEFAULT => '0'});
348
349 $dbh->bz_alter_column('bugs', 'lastdiffed', {TYPE => 'DATETIME'});
350
351 # 2005-03-09 qa_contact should be NULL instead of 0, bug 285534
352 if ($dbh->bz_column_info('bugs', 'qa_contact')->{NOTNULL}) {
353 $dbh->bz_alter_column('bugs', 'qa_contact', {TYPE => 'INT3'});
354 $dbh->do("UPDATE bugs SET qa_contact = NULL WHERE qa_contact = 0");
355 }
356
357 # 2005-03-27 initialqacontact should be NULL instead of 0, bug 287483
358 if ($dbh->bz_column_info('components', 'initialqacontact')->{NOTNULL}) {
359 $dbh->bz_alter_column('components', 'initialqacontact',
360 {TYPE => 'INT3'});
361 }
362 $dbh->do("UPDATE components SET initialqacontact = NULL " .
363 "WHERE initialqacontact = 0");
364
365 _migrate_email_prefs_to_new_table();
366 _initialize_dependency_tree_changes_email_pref();
367 _change_all_mysql_booleans_to_tinyint();
368
369 # make classification_id field type be consistent with DB:Schema
370 $dbh->bz_alter_column('products', 'classification_id',
371 {TYPE => 'INT2', NOTNULL => 1, DEFAULT => '1'});
372
373 # initialowner was accidentally NULL when we checked-in Schema,
374 # when it really should be NOT NULL.
375 $dbh->bz_alter_column('components', 'initialowner',
376 {TYPE => 'INT3', NOTNULL => 1}, 0);
377
378 # 2005-03-28 - bug 238800 - index flags.type_id for editflagtypes.cgi
379 $dbh->bz_add_index('flags', 'flags_type_id_idx', [qw(type_id)]);
380
381 # For a short time, the flags_type_id_idx was misnamed in upgraded installs.
382 $dbh->bz_drop_index('flags', 'type_id');
383
384 # 2005-04-28 - LpSolit@gmail.com - Bug 7233: add an index to versions
385 $dbh->bz_alter_column('versions', 'value',
386 {TYPE => 'varchar(64)', NOTNULL => 1});
387 _add_versions_product_id_index();
388
389 if (!exists $dbh->bz_column_info('milestones', 'sortkey')->{DEFAULT}) {
390 $dbh->bz_alter_column('milestones', 'sortkey',
391 {TYPE => 'INT2', NOTNULL => 1, DEFAULT => 0});
392 }
393
394 # 2005-06-14 - LpSolit@gmail.com - Bug 292544
395 $dbh->bz_alter_column('bugs', 'creation_ts', {TYPE => 'DATETIME'});
396
397 _fix_whine_queries_title_and_op_sys_value();
398 _fix_attachments_submitter_id_idx();
399 _copy_attachments_thedata_to_attach_data();
400 _fix_broken_all_closed_series();
401
402 # 2005-08-14 bugreport@peshkin.net -- Bug 304583
403 # Get rid of leftover DERIVED group permissions
404 use constant GRANT_DERIVED => 1;
405 $dbh->do("DELETE FROM user_group_map WHERE grant_type = " . GRANT_DERIVED);
406
407 # PUBLIC is a reserved word in Oracle.
408 $dbh->bz_rename_column('series', 'public', 'is_public');
409
410 # 2005-09-28 bugreport@peshkin.net Bug 149504
411 $dbh->bz_add_column('attachments', 'isurl',
412 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 0});
413
414 # 2005-10-21 LpSolit@gmail.com - Bug 313020
415 $dbh->bz_add_column('namedqueries', 'query_type',
416 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 0});
417
418 # 2005-11-04 LpSolit@gmail.com - Bug 305927
419 $dbh->bz_alter_column('groups', 'userregexp',
420 {TYPE => 'TINYTEXT', NOTNULL => 1, DEFAULT => "''"});
421
422 # 2005-09-26 - olav@bkor.dhs.org - Bug 119524
423 $dbh->bz_alter_column('logincookies', 'cookie',
424 {TYPE => 'varchar(16)', PRIMARYKEY => 1, NOTNULL => 1});
425
426 _clean_control_characters_from_short_desc();
427
428 # 2005-12-07 altlst@sonic.net -- Bug 225221
429 $dbh->bz_add_column('longdescs', 'comment_id',
430 {TYPE => 'MEDIUMSERIAL', NOTNULL => 1, PRIMARYKEY => 1});
431
432 _stop_storing_inactive_flags();
433 _change_short_desc_from_mediumtext_to_varchar();
434
435 # 2006-07-01 wurblzap@gmail.com -- Bug 69000
436 $dbh->bz_add_column('namedqueries', 'id',
437 {TYPE => 'MEDIUMSERIAL', NOTNULL => 1, PRIMARYKEY => 1});
438 _move_namedqueries_linkinfooter_to_its_own_table();
439
440 _add_classifications_sortkey();
441 _move_data_nomail_into_db();
442
443 # The products table lacked sensible defaults.
444 $dbh->bz_alter_column('products', 'milestoneurl',
445 {TYPE => 'TINYTEXT', NOTNULL => 1, DEFAULT => "''"});
446 $dbh->bz_alter_column('products', 'disallownew',
447 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 0});
448 $dbh->bz_alter_column('products', 'votesperuser',
449 {TYPE => 'INT2', NOTNULL => 1, DEFAULT => 0});
450 $dbh->bz_alter_column('products', 'votestoconfirm',
451 {TYPE => 'INT2', NOTNULL => 1, DEFAULT => 0});
452
453 # 2006-08-04 LpSolit@gmail.com - Bug 305941
454 $dbh->bz_drop_column('profiles', 'refreshed_when');
455 $dbh->bz_drop_column('groups', 'last_changed');
456
457 # 2006-08-06 LpSolit@gmail.com - Bug 347521
458 $dbh->bz_alter_column('flagtypes', 'id',
459 {TYPE => 'SMALLSERIAL', NOTNULL => 1, PRIMARYKEY => 1});
460
461 $dbh->bz_alter_column('keyworddefs', 'id',
462 {TYPE => 'SMALLSERIAL', NOTNULL => 1, PRIMARYKEY => 1});
463
464 # 2006-08-19 LpSolit@gmail.com - Bug 87795
465 $dbh->bz_alter_column('tokens', 'userid', {TYPE => 'INT3'});
466
467 $dbh->bz_drop_index('bugs', 'bugs_short_desc_idx');
468
469 # The profiles table was missing some defaults.
470 $dbh->bz_alter_column('profiles', 'disabledtext',
471 {TYPE => 'MEDIUMTEXT', NOTNULL => 1, DEFAULT => "''"});
472 $dbh->bz_alter_column('profiles', 'realname',
473 {TYPE => 'varchar(255)', NOTNULL => 1, DEFAULT => "''"});
474
475 _update_longdescs_who_index();
476
477 $dbh->bz_add_column('setting', 'subclass', {TYPE => 'varchar(32)'});
478
479 $dbh->bz_alter_column('longdescs', 'thetext',
480 {TYPE => 'LONGTEXT', NOTNULL => 1}, '');
481
482 # 2006-10-20 LpSolit@gmail.com - Bug 189627
483 $dbh->bz_add_column('group_control_map', 'editcomponents',
484 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'});
485 $dbh->bz_add_column('group_control_map', 'editbugs',
486 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'});
487 $dbh->bz_add_column('group_control_map', 'canconfirm',
488 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'});
489
490 # 2006-11-07 LpSolit@gmail.com - Bug 353656
491 $dbh->bz_add_column('longdescs', 'type',
492 {TYPE => 'INT2', NOTNULL => 1, DEFAULT => '0'});
493 $dbh->bz_add_column('longdescs', 'extra_data', {TYPE => 'varchar(255)'});
494
495 $dbh->bz_add_column('versions', 'id',
496 {TYPE => 'MEDIUMSERIAL', NOTNULL => 1, PRIMARYKEY => 1});
497 $dbh->bz_add_column('milestones', 'id',
498 {TYPE => 'MEDIUMSERIAL', NOTNULL => 1, PRIMARYKEY => 1});
499
500 _fix_uppercase_custom_field_names();
501 _fix_uppercase_index_names();
502
503 # 2007-05-17 LpSolit@gmail.com - Bug 344965
504 _initialize_workflow($old_params);
505
506 # 2007-08-08 LpSolit@gmail.com - Bug 332149
507 $dbh->bz_add_column('groups', 'icon_url', {TYPE => 'TINYTEXT'});
508
509 # 2007-08-21 wurblzap@gmail.com - Bug 365378
510 _make_lang_setting_dynamic();
511
512 # 2007-11-29 xiaoou.wu@oracle.com - Bug 153129
513 _change_text_types();
514
515 # 2007-09-09 LpSolit@gmail.com - Bug 99215
516 _fix_attachment_modification_date();
517
518 # This had the wrong definition in DB::Schema.
519 $dbh->bz_alter_column('namedqueries', 'query_type',
520 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 0});
521
522 $dbh->bz_drop_index('longdescs', 'longdescs_thetext_idx');
523 _populate_bugs_fulltext();
524
525 # 2008-01-18 xiaoou.wu@oracle.com - Bug 414292
526 $dbh->bz_alter_column('series', 'query',
527 { TYPE => 'MEDIUMTEXT', NOTNULL => 1 });
528
529 ################################################################
530 # New --TABLE-- changes should go *** A B O V E *** this point #
531 ################################################################
532
533 Bugzilla::Hook::process('install-update_db');
534
535 $dbh->bz_setup_foreign_keys();
536 }
537
538 # Subroutines should be ordered in the order that they are called.
539 # Thus, newer subroutines should be at the bottom.
540
541 sub _update_pre_checksetup_bugzillas {
542 my $dbh = Bugzilla->dbh;
543 # really old fields that were added before checksetup.pl existed
544 # but aren't in very old bugzilla's (like 2.1)
545 # Steve Stock (sstock@iconnect-inc.com)
546
547 $dbh->bz_add_column('bugs', 'target_milestone',
548 {TYPE => 'varchar(20)', NOTNULL => 1, DEFAULT => "'---'"});
549 $dbh->bz_add_column('bugs', 'qa_contact', {TYPE => 'INT3'});
550 $dbh->bz_add_column('bugs', 'status_whiteboard',
551 {TYPE => 'MEDIUMTEXT', NOTNULL => 1, DEFAULT => "''"});
552 $dbh->bz_add_column('products', 'disallownew',
553 {TYPE => 'BOOLEAN', NOTNULL => 1}, 0);
554 $dbh->bz_add_column('products', 'milestoneurl',
555 {TYPE => 'TINYTEXT', NOTNULL => 1}, '');
556 $dbh->bz_add_column('components', 'initialqacontact',
557 {TYPE => 'TINYTEXT'});
558 $dbh->bz_add_column('components', 'description',
559 {TYPE => 'MEDIUMTEXT', NOTNULL => 1}, '');
560 }
561
562 sub _add_bug_vote_cache {
563 my $dbh = Bugzilla->dbh;
564 # 1999-10-11 Restructured voting database to add a cached value in each
565 # bug recording how many total votes that bug has. While I'm at it,
566 # I removed the unused "area" field from the bugs database. It is
567 # distressing to realize that the bugs table has reached the maximum
568 # number of indices allowed by MySQL (16), which may make future
569 # enhancements awkward.
570 # (P.S. All is not lost; it appears that the latest betas of MySQL
571 # support a new table format which will allow 32 indices.)
572
573 $dbh->bz_drop_column('bugs', 'area');
574 if (!$dbh->bz_column_info('bugs', 'votes')) {
575 $dbh->bz_add_column('bugs', 'votes', {TYPE => 'INT3', NOTNULL => 1,
576 DEFAULT => 0});
577 $dbh->bz_add_index('bugs', 'bugs_votes_idx', [qw(votes)]);
578 }
579 $dbh->bz_add_column('products', 'votesperuser',
580 {TYPE => 'INT2', NOTNULL => 1}, 0);
581 }
582
583 sub _update_product_name_definition {
584 my $dbh = Bugzilla->dbh;
585 # The product name used to be very different in various tables.
586 #
587 # It was varchar(16) in bugs
588 # tinytext in components
589 # tinytext in products
590 # tinytext in versions
591 #
592 # tinytext is equivalent to varchar(255), which is quite huge, so I change
593 # them all to varchar(64).
594
595 # Only do this if these fields still exist - they're removed in
596 # a later change
597 if ($dbh->bz_column_info('products', 'product')) {
598 $dbh->bz_alter_column('bugs', 'product',
599 {TYPE => 'varchar(64)', NOTNULL => 1});
600 $dbh->bz_alter_column('components', 'program', {TYPE => 'varchar(64)'});
601 $dbh->bz_alter_column('products', 'product', {TYPE => 'varchar(64)'});
602 $dbh->bz_alter_column('versions', 'program',
603 {TYPE => 'varchar(64)', NOTNULL => 1});
604 }
605 }
606
607 sub _add_bug_keyword_cache {
608 my $dbh = Bugzilla->dbh;
609 # 2000-01-16 Added a "keywords" field to the bugs table, which
610 # contains a string copy of the entries of the keywords table for this
611 # bug. This is so that I can easily sort and display a keywords
612 # column in bug lists.
613
614 if (!$dbh->bz_column_info('bugs', 'keywords')) {
615 $dbh->bz_add_column('bugs', 'keywords',
616 {TYPE => 'MEDIUMTEXT', NOTNULL => 1, DEFAULT => "''"});
617
618 my @kwords;
619 print "Making sure 'keywords' field of table 'bugs' is empty...\n";
620 $dbh->do("UPDATE bugs SET keywords = '' WHERE keywords != ''");
621 print "Repopulating 'keywords' field of table 'bugs'...\n";
622 my $sth = $dbh->prepare("SELECT keywords.bug_id, keyworddefs.name " .
623 "FROM keywords, keyworddefs " .
624 "WHERE keyworddefs.id = keywords.keywordid " .
625 "ORDER BY keywords.bug_id, keyworddefs.name");
626 $sth->execute;
627 my @list;
628 my $bugid = 0;
629 my @row;
630 while (1) {
631 my ($b, $k) = ($sth->fetchrow_array());
632 if (!defined $b || $b ne $bugid) {
633 if (@list) {
634 $dbh->do("UPDATE bugs SET keywords = " .
635 $dbh->quote(join(', ', @list)) .
636 " WHERE bug_id = $bugid");
637 }
638 last if !$b;
639 $bugid = $b;
640 @list = ();
641 }
642 push(@list, $k);
643 }
644 }
645 }
646
647 # A helper for the function below.
648 sub _write_one_longdesc {
649 my ($id, $who, $when, $buffer) = (@_);
650 my $dbh = Bugzilla->dbh;
651 $buffer = trim($buffer);
652 return if !$buffer;
653 $dbh->do("INSERT INTO longdescs (bug_id, who, bug_when, thetext)
654 VALUES (?,?,?,?)", undef, $id, $who,
655 time2str("%Y/%m/%d %H:%M:%S", $when), $buffer);
656 }
657
658 sub _populate_longdescs {
659 my $dbh = Bugzilla->dbh;
660 # 2000-01-20 Added a new "longdescs" table, which is supposed to have
661 # all the long descriptions in it, replacing the old long_desc field
662 # in the bugs table. The below hideous code populates this new table
663 # with things from the old field, with ugly parsing and heuristics.
664
665 if ($dbh->bz_column_info('bugs', 'long_desc')) {
666 my ($total) = $dbh->selectrow_array("SELECT COUNT(*) FROM bugs");
667
668 print "Populating new long_desc table. This is slow. There are",
669 " $total\nbugs to process; a line of dots will be printed",
670 " for each 50.\n\n";
671 local $| = 1;
672
673 # On MySQL, longdescs doesn't benefit from transactions, but this
674 # doesn't hurt.
675 $dbh->bz_start_transaction();
676
677 $dbh->do('DELETE FROM longdescs');
678
679 my $sth = $dbh->prepare("SELECT bug_id, creation_ts, reporter,
680 long_desc FROM bugs ORDER BY bug_id");
681 $sth->execute();
682 my $count = 0;
683 while (my ($id, $createtime, $reporterid, $desc) =
684 $sth->fetchrow_array())
685 {
686 $count++;
687 indicate_progress({ total => $total, current => $count });
688 $desc =~ s/\r//g;
689 my $who = $reporterid;
690 my $when = str2time($createtime);
691 my $buffer = "";
692 foreach my $line (split(/\n/, $desc)) {
693 $line =~ s/\s+$//g; # Trim trailing whitespace.
694 if ($line =~ /^------- Additional Comments From ([^\s]+)\s+(\d.+\d)\s+-------$/)
695 {
696 my $name = $1;
697 my $date = str2time($2);
698 # Oy, what a hack. The creation time is accurate to the
699 # second. But the long text only contains things accurate
700 # to the And so, if someone makes a comment within a
701 # minute of the original bug creation, then the comment can
702 # come *before* the bug creation. So, we add 59 seconds to
703 # the time of all comments, so that they are always
704 # considered to have happened at the *end* of the given
705 # minute, not the beginning.
706 $date += 59;
707 if ($date >= $when) {
708 _write_one_longdesc($id, $who, $when, $buffer);
709 $buffer = "";
710 $when = $date;
711 my $s2 = $dbh->prepare("SELECT userid FROM profiles " .
712 "WHERE login_name = ?");
713 $s2->execute($name);
714 ($who) = ($s2->fetchrow_array());
715
716 if (!$who) {
717 # This username doesn't exist. Maybe someone
718 # renamed him or something. Invent a new profile
719 # entry disabled, just to represent him.
720 $dbh->do("INSERT INTO profiles (login_name,
721 cryptpassword, disabledtext)
722 VALUES (?,?,?)", undef, $name, '*',
723 "Account created only to maintain"
724 . " database integrity");
725 $who = $dbh->bz_last_key('profiles', 'userid');
726 }
727 next;
728 }
729 }
730 $buffer .= $line . "\n";
731 }
732 _write_one_longdesc($id, $who, $when, $buffer);
733 } # while loop
734
735 print "\n\n";
736 $dbh->bz_drop_column('bugs', 'long_desc');
737 $dbh->bz_commit_transaction();
738 } # main if
739 }
740
741 sub _update_bugs_activity_field_to_fieldid {
742 my $dbh = Bugzilla->dbh;
743
744 # 2000-01-18 Added a new table fielddefs that records information about the
745 # different fields we keep an activity log on. The bugs_activity table
746 # now has a pointer into that table instead of recording the name directly.
747 if ($dbh->bz_column_info('bugs_activity', 'field')) {
748 $dbh->bz_add_column('bugs_activity', 'fieldid',
749 {TYPE => 'INT3', NOTNULL => 1}, 0);
750
751 $dbh->bz_add_index('bugs_activity', 'bugs_activity_fieldid_idx',
752 [qw(fieldid)]);
753 print "Populating new bugs_activity.fieldid field...\n";
754
755 $dbh->bz_start_transaction();
756
757 my $ids = $dbh->selectall_arrayref(
758 'SELECT DISTINCT fielddefs.id, bugs_activity.field
759 FROM bugs_activity LEFT JOIN fielddefs
760 ON bugs_activity.field = fielddefs.name', {Slice=>{}});
761
762 foreach my $item (@$ids) {
763 my $id = $item->{id};
764 my $field = $item->{field};
765 # If the id is NULL
766 if (!$id) {
767 $dbh->do("INSERT INTO fielddefs (name, description) VALUES " .
768 "(?, ?)", undef, $field, $field);
769 $id = $dbh->bz_last_key('fielddefs', 'id');
770 }
771 $dbh->do("UPDATE bugs_activity SET fieldid = ? WHERE field = ?",
772 undef, $id, $field);
773 }
774 $dbh->bz_commit_transaction();
775
776 $dbh->bz_drop_column('bugs_activity', 'field');
777 }
778 }
779
780 sub _add_unique_login_name_index_to_profiles {
781 my $dbh = Bugzilla->dbh;
782
783 # 2000-01-22 The "login_name" field in the "profiles" table was not
784 # declared to be unique. Sure enough, somehow, I got 22 duplicated entries
785 # in my database. This code detects that, cleans up the duplicates, and
786 # then tweaks the table to declare the field to be unique. What a pain.
787 if (!$dbh->bz_index_info('profiles', 'profiles_login_name_idx')
788 || !$dbh->bz_index_info('profiles', 'profiles_login_name_idx')->{TYPE})
789 {
790 print "Searching for duplicate entries in the profiles table...\n";
791 while (1) {
792 # This code is weird in that it loops around and keeps doing this
793 # select again. That's because I'm paranoid about deleting entries
794 # out from under us in the profiles table. Things get weird if
795 # there are *three* or more entries for the same user...
796 my $sth = $dbh->prepare("SELECT p1.userid, p2.userid, p1.login_name
797 FROM profiles AS p1, profiles AS p2
798 WHERE p1.userid < p2.userid
799 AND p1.login_name = p2.login_name
800 ORDER BY p1.login_name");
801 $sth->execute();
802 my ($u1, $u2, $n) = ($sth->fetchrow_array);
803 last if !$u1;
804
805 print "Both $u1 & $u2 are ids for $n! Merging $u2 into $u1...\n";
806 foreach my $i (["bugs", "reporter"],
807 ["bugs", "assigned_to"],
808 ["bugs", "qa_contact"],
809 ["attachments", "submitter_id"],
810 ["bugs_activity", "who"],
811 ["cc", "who"],
812 ["votes", "who"],
813 ["longdescs", "who"]) {
814 my ($table, $field) = (@$i);
815 print " Updating $table.$field...\n";
816 $dbh->do("UPDATE $table SET $field = $u1 " .
817 "WHERE $field = $u2");
818 }
819 $dbh->do("DELETE FROM profiles WHERE userid = $u2");
820 }
821 print "OK, changing index type to prevent duplicates in the",
822 " future...\n";
823
824 $dbh->bz_drop_index('profiles', 'profiles_login_name_idx');
825 $dbh->bz_add_index('profiles', 'profiles_login_name_idx',
826 {TYPE => 'UNIQUE', FIELDS => [qw(login_name)]});
827 }
828 }
829
830 sub _update_component_user_fields_to_ids {
831 my $dbh = Bugzilla->dbh;
832
833 # components.initialowner
834 my $comp_init_owner = $dbh->bz_column_info('components', 'initialowner');
835 if ($comp_init_owner && $comp_init_owner->{TYPE} eq 'TINYTEXT') {
836 my $sth = $dbh->prepare("SELECT program, value, initialowner
837 FROM components");
838 $sth->execute();
839 while (my ($program, $value, $initialowner) = $sth->fetchrow_array()) {
840 my ($id) = $dbh->selectrow_array(
841 "SELECT userid FROM profiles WHERE login_name = ?",
842 undef, $initialowner);
843
844 unless (defined $id) {
845 print "Warning: You have an invalid default assignee",
846 " '$initialowner'\n in component '$value' of program",
847 " '$program'!\n";
848 $id = 0;
849 }
850
851 $dbh->do("UPDATE components SET initialowner = ?
852 WHERE program = ? AND value = ?", undef,
853 $id, $program, $value);
854 }
855 $dbh->bz_alter_column('components','initialowner',{TYPE => 'INT3'});
856 }
857
858 # components.initialqacontact
859 my $comp_init_qa = $dbh->bz_column_info('components', 'initialqacontact');
860 if ($comp_init_qa && $comp_init_qa->{TYPE} eq 'TINYTEXT') {
861 my $sth = $dbh->prepare("SELECT program, value, initialqacontact
862 FROM components");
863 $sth->execute();
864 while (my ($program, $value, $initialqacontact) =
865 $sth->fetchrow_array())
866 {
867 my ($id) = $dbh->selectrow_array(
868 "SELECT userid FROM profiles WHERE login_name = ?",
869 undef, $initialqacontact);
870
871 unless (defined $id) {
872 if ($initialqacontact) {
873 print "Warning: You have an invalid default QA contact",
874 " $initialqacontact' in program '$program',",
875 " component '$value'!\n";
876 }
877 $id = 0;
878 }
879
880 $dbh->do("UPDATE components SET initialqacontact = ?
881 WHERE program = ? AND value = ?", undef,
882 $id, $program, $value);
883 }
884
885 $dbh->bz_alter_column('components','initialqacontact',{TYPE => 'INT3'});
886 }
887 }
888
889 sub _populate_milestones_table {
890 my $dbh = Bugzilla->dbh;
891 # 2000-03-21 Adding a table for target milestones to
892 # database - matthew@zeroknowledge.com
893 # If the milestones table is empty, and we're still back in a Bugzilla
894 # that has a bugs.product field, that means that we just created
895 # the milestones table and it needs to be populated.
896 my $milestones_exist = $dbh->selectrow_array(
897 "SELECT DISTINCT 1 FROM milestones");
898 if (!$milestones_exist && $dbh->bz_column_info('bugs', 'product')) {
899 print "Replacing blank milestones...\n";
900
901 $dbh->do("UPDATE bugs
902 SET target_milestone = '---'
903 WHERE target_milestone = ' '");
904
905 # If we are upgrading from 2.8 or earlier, we will have *created*
906 # the milestones table with a product_id field, but Bugzilla expects
907 # it to have a "product" field. So we change the field backward so
908 # other code can run. The change will be reversed later in checksetup.
909 if ($dbh->bz_column_info('milestones', 'product_id')) {
910 # Dropping the column leaves us with a milestones_product_id_idx
911 # index that is only on the "value" column. We need to drop the
912 # whole index so that it can be correctly re-created later.
913 $dbh->bz_drop_index('milestones', 'milestones_product_id_idx');
914 $dbh->bz_drop_column('milestones', 'product_id');
915 $dbh->bz_add_column('milestones', 'product',
916 {TYPE => 'varchar(64)', NOTNULL => 1}, '');
917 }
918
919 # Populate the milestone table with all existing values in the database
920 my $sth = $dbh->prepare("SELECT DISTINCT target_milestone, product
921 FROM bugs");
922 $sth->execute();
923
924 print "Populating milestones table...\n";
925
926 while (my ($value, $product) = $sth->fetchrow_array()) {
927 # check if the value already exists
928 my $sortkey = substr($value, 1);
929 if ($sortkey !~ /^\d+$/) {
930 $sortkey = 0;
931 } else {
932 $sortkey *= 10;
933 }
934 my $ms_exists = $dbh->selectrow_array(
935 "SELECT value FROM milestones
936 WHERE value = ? AND product = ?", undef, $value, $product);
937
938 if (!$ms_exists) {
939 $dbh->do("INSERT INTO milestones(value, product, sortkey)
940 VALUES (?,?,?)", undef, $value, $product, $sortkey);
941 }
942 }
943 }
944 }
945
946 sub _add_products_defaultmilestone {
947 my $dbh = Bugzilla->dbh;
948
949 # 2000-03-23 Added a defaultmilestone field to the products table, so that
950 # we know which milestone to initially assign bugs to.
951 if (!$dbh->bz_column_info('products', 'defaultmilestone')) {
952 $dbh->bz_add_column('products', 'defaultmilestone',
953 {TYPE => 'varchar(20)', NOTNULL => 1, DEFAULT => "'---'"});
954 my $sth = $dbh->prepare(
955 "SELECT product, defaultmilestone FROM products");
956 $sth->execute();
957 while (my ($product, $default_ms) = $sth->fetchrow_array()) {
958 my $exists = $dbh->selectrow_array(
959 "SELECT value FROM milestones
960 WHERE value = ? AND product = ?",
961 undef, $default_ms, $product);
962 if (!$exists) {
963 $dbh->do("INSERT INTO milestones(value, product) " .
964 "VALUES (?, ?)", undef, $default_ms, $product);
965 }
966 }
967 }
968 }
969
970 sub _copy_from_comments_to_longdescs {
971 my $dbh = Bugzilla->dbh;
972 # 2000-11-27 For Bugzilla 2.5 and later. Copy data from 'comments' to
973 # 'longdescs' - the new name of the comments table.
974 if ($dbh->bz_table_info('comments')) {
975 my $quoted_when = $dbh->quote_identifier('when');
976 $dbh->do("INSERT INTO longdescs (bug_when, bug_id, who, thetext)
977 SELECT $quoted_when, bug_id, who, comment
978 FROM comments");
979 $dbh->bz_drop_table("comments");
980 }
981 }
982
983 sub _populate_duplicates_table {
984 my $dbh = Bugzilla->dbh;
985 # 2000-07-15 Added duplicates table so Bugzilla tracks duplicates in a
986 # better way than it used to. This code searches the comments to populate
987 # the table initially. It's executed if the table is empty; if it's
988 # empty because there are no dupes (as opposed to having just created
989 # the table) it won't have any effect anyway, so it doesn't matter.
990 my ($dups_exist) = $dbh->selectrow_array(
991 "SELECT DISTINCT 1 FROM duplicates");
992 # We also check against a schema change that happened later.
993 if (!$dups_exist && !$dbh->bz_column_info('groups', 'isactive')) {
994 # populate table
995 print "Populating duplicates table from comments...\n";
996
997 my $sth = $dbh->prepare(
998 "SELECT longdescs.bug_id, thetext
999 FROM longdescs LEFT JOIN bugs
1000 ON longdescs.bug_id = bugs.bug_id
1001 WHERE (" . $dbh->sql_regexp("thetext",
1002 "'[.*.]{3} This bug has been marked as a duplicate"
1003 . " of [[:digit:]]+ [.*.]{3}'")
1004 . ")
1005 AND resolution = 'DUPLICATE'
1006 ORDER BY longdescs.bug_when");
1007 $sth->execute();
1008
1009 my (%dupes, $key);
1010 # Because of the way hashes work, this loop removes all but the
1011 # last dupe resolution found for a given bug.
1012 while (my ($dupe, $dupe_of) = $sth->fetchrow_array()) {
1013 $dupes{$dupe} = $dupe_of;
1014 }
1015
1016 foreach $key (keys(%dupes)){
1017 $dupes{$key} =~ /^.*\*\*\* This bug has been marked as a duplicate of (\d+) \*\*\*$/ms;
1018 $dupes{$key} = $1;
1019 $dbh->do("INSERT INTO duplicates VALUES(?, ?)", undef,
1020 $dupes{$key}, $key);
1021 # BugItsADupeOf Dupe
1022 }
1023 }
1024 }
1025
1026 sub _recrypt_plaintext_passwords {
1027 my $dbh = Bugzilla->dbh;
1028 # 2001-06-12; myk@mozilla.org; bugs 74032, 77473:
1029 # Recrypt passwords using Perl &crypt instead of the mysql equivalent
1030 # and delete plaintext passwords from the database.
1031 if ($dbh->bz_column_info('profiles', 'password')) {
1032
1033 print <<ENDTEXT;
1034 Your current installation of Bugzilla stores passwords in plaintext
1035 in the database and uses mysql's encrypt function instead of Perl's
1036 crypt function to crypt passwords. Passwords are now going to be
1037 re-crypted with the Perl function, and plaintext passwords will be
1038 deleted from the database. This could take a while if your
1039 installation has many users.
1040 ENDTEXT
1041
1042 # Re-crypt everyone's password.
1043 my $total = $dbh->selectrow_array('SELECT COUNT(*) FROM profiles');
1044 my $sth = $dbh->prepare("SELECT userid, password FROM profiles");
1045 $sth->execute();
1046
1047 my $i = 1;
1048
1049 print "Fixing passwords...\n";
1050 while (my ($userid, $password) = $sth->fetchrow_array()) {
1051 my $cryptpassword = $dbh->quote(bz_crypt($password));
1052 $dbh->do("UPDATE profiles " .
1053 "SET cryptpassword = $cryptpassword " .
1054 "WHERE userid = $userid");
1055 indicate_progress({ total => $total, current => $i, every => 10 });
1056 }
1057 print "\n";
1058
1059 # Drop the plaintext password field.
1060 $dbh->bz_drop_column('profiles', 'password');
1061 }
1062 }
1063
1064 sub _update_bugs_activity_to_only_record_changes {
1065 my $dbh = Bugzilla->dbh;
1066 # 2001-07-20 jake@bugzilla.org - Change bugs_activity to only record changes
1067 # http://bugzilla.mozilla.org/show_bug.cgi?id=55161
1068 if ($dbh->bz_column_info('bugs_activity', 'oldvalue')) {
1069 $dbh->bz_add_column("bugs_activity", "removed", {TYPE => "TINYTEXT"});
1070 $dbh->bz_add_column("bugs_activity", "added", {TYPE => "TINYTEXT"});
1071
1072 # Need to get field id's for the fields that have multiple values
1073 my @multi;
1074 foreach my $f ("cc", "dependson", "blocked", "keywords") {
1075 my $sth = $dbh->prepare("SELECT id " .
1076 "FROM fielddefs " .
1077 "WHERE name = '$f'");
1078 $sth->execute();
1079 my ($fid) = $sth->fetchrow_array();
1080 push (@multi, $fid);
1081 }
1082
1083 # Now we need to process the bugs_activity table and reformat the data
1084 print "Fixing activity log...\n";
1085 my $total = $dbh->selectrow_array('SELECT COUNT(*) FROM bugs_activity');
1086 my $sth = $dbh->prepare("SELECT bug_id, who, bug_when, fieldid,
1087 oldvalue, newvalue FROM bugs_activity");
1088 $sth->execute;
1089 my $i = 0;
1090 while (my ($bug_id, $who, $bug_when, $fieldid, $oldvalue, $newvalue)
1091 = $sth->fetchrow_array())
1092 {
1093 $i++;
1094 indicate_progress({ total => $total, current => $i, every => 10 });
1095 # Make sure (old|new)value isn't null (to suppress warnings)
1096 $oldvalue ||= "";
1097 $newvalue ||= "";
1098 my ($added, $removed) = "";
1099 if (grep ($_ eq $fieldid, @multi)) {
1100 $oldvalue =~ s/[\s,]+/ /g;
1101 $newvalue =~ s/[\s,]+/ /g;
1102 my @old = split(" ", $oldvalue);
1103 my @new = split(" ", $newvalue);
1104 my (@add, @remove) = ();
1105 # Find values that were "added"
1106 foreach my $value(@new) {
1107 if (! grep ($_ eq $value, @old)) {
1108 push (@add, $value);
1109 }
1110 }
1111 # Find values that were removed
1112 foreach my $value(@old) {
1113 if (! grep ($_ eq $value, @new)) {
1114 push (@remove, $value);
1115 }
1116 }
1117 $added = join (", ", @add);
1118 $removed = join (", ", @remove);
1119 # If we can't determine what changed, put a ? in both fields
1120 unless ($added || $removed) {
1121 $added = "?";
1122 $removed = "?";
1123 }
1124 # If the original field (old|new)value was full, then this
1125 # could be incomplete data.
1126 if (length($oldvalue) == 255 || length($newvalue) == 255) {
1127 $added = "? $added";
1128 $removed = "? $removed";
1129 }
1130 } else {
1131 $removed = $oldvalue;
1132 $added = $newvalue;
1133 }
1134 $added = $dbh->quote($added);
1135 $removed = $dbh->quote($removed);
1136 $dbh->do("UPDATE bugs_activity
1137 SET removed = $removed, added = $added
1138 WHERE bug_id = $bug_id AND who = $who
1139 AND bug_when = '$bug_when'
1140 AND fieldid = $fieldid");
1141 }
1142 print "\n";
1143 $dbh->bz_drop_column("bugs_activity", "oldvalue");
1144 $dbh->bz_drop_column("bugs_activity", "newvalue");
1145 }
1146 }
1147
1148 sub _delete_logincookies_cryptpassword_and_handle_invalid_cookies {
1149 my $dbh = Bugzilla->dbh;
1150 # 2002-02-04 bbaetz@student.usyd.edu.au bug 95732
1151 # Remove logincookies.cryptpassword, and delete entries which become
1152 # invalid
1153 if ($dbh->bz_column_info("logincookies", "cryptpassword")) {
1154 # We need to delete any cookies which are invalid before dropping the
1155 # column
1156 print "Removing invalid login cookies...\n";
1157
1158 # mysql doesn't support DELETE with multi-table queries, so we have
1159 # to iterate
1160 my $sth = $dbh->prepare("SELECT cookie FROM logincookies, profiles " .
1161 "WHERE logincookies.cryptpassword != " .
1162 "profiles.cryptpassword AND " .
1163 "logincookies.userid = profiles.userid");
1164 $sth->execute();
1165 while (my ($cookie) = $sth->fetchrow_array()) {
1166 $dbh->do("DELETE FROM logincookies WHERE cookie = $cookie");
1167 }
1168
1169 $dbh->bz_drop_column("logincookies", "cryptpassword");
1170 }
1171 }
1172
1173 sub _use_ip_instead_of_hostname_in_logincookies {
1174 my $dbh = Bugzilla->dbh;
1175
1176 # 2002-03-15 bbaetz@student.usyd.edu.au - bug 129466
1177 # 2002-05-13 preed@sigkill.com - bug 129446 patch backported to the
1178 # BUGZILLA-2_14_1-BRANCH as a security blocker for the 2.14.2 release
1179 #
1180 # Use the ip, not the hostname, in the logincookies table
1181 if ($dbh->bz_column_info("logincookies", "hostname")) {
1182 # We've changed what we match against, so all entries are now invalid
1183 $dbh->do("DELETE FROM logincookies");
1184
1185 # Now update the logincookies schema
1186 $dbh->bz_drop_column("logincookies", "hostname");
1187 $dbh->bz_add_column("logincookies", "ipaddr",
1188 {TYPE => 'varchar(40)', NOTNULL => 1}, '');
1189 }
1190 }
1191
1192 sub _move_quips_into_db {
1193 my $dbh = Bugzilla->dbh;
1194 my $datadir = bz_locations->{'datadir'};
1195 # 2002-07-15 davef@tetsubo.com - bug 67950
1196 # Move quips to the db.
1197 if (-e "$datadir/comments") {
1198 print "Populating quips table from $datadir/comments...\n";
1199 my $comments = new IO::File("$datadir/comments", 'r')
1200 || die "$datadir/comments: $!";
1201 $comments->untaint;
1202 while (<$comments>) {
1203 chomp;
1204 $dbh->do("INSERT INTO quips (quip) VALUES (?)", undef, $_);
1205 }
1206
1207 print <<EOT;
1208
1209 Quips are now stored in the database, rather than in an external file.
1210 The quips previously stored in $datadir/comments have been copied into
1211 the database, and that file has been renamed to $datadir/comments.bak
1212 You may delete the renamed file once you have confirmed that all your
1213 quips were moved successfully.
1214
1215 EOT
1216 $comments->close;
1217 rename("$datadir/comments", "$datadir/comments.bak")
1218 || warn "Failed to rename: $!";
1219 }
1220 }
1221
1222 sub _use_ids_for_products_and_components {
1223 my $dbh = Bugzilla->dbh;
1224 # 2002-08-12 jake@bugzilla.org/bbaetz@student.usyd.edu.au - bug 43600
1225 # Use integer IDs for products and components.
1226 if ($dbh->bz_column_info("products", "product")) {
1227 print "Updating database to use product IDs.\n";
1228
1229 # First, we need to remove possible NULL entries
1230 # NULLs may exist, but won't have been used, since all the uses of them
1231 # are in NOT NULL fields in other tables
1232 $dbh->do("DELETE FROM products WHERE product IS NULL");
1233 $dbh->do("DELETE FROM components WHERE value IS NULL");
1234
1235 $dbh->bz_add_column("products", "id",
1236 {TYPE => 'SMALLSERIAL', NOTNULL => 1, PRIMARYKEY => 1});
1237 $dbh->bz_add_column("components", "product_id",
1238 {TYPE => 'INT2', NOTNULL => 1}, 0);
1239 $dbh->bz_add_column("versions", "product_id",
1240 {TYPE => 'INT2', NOTNULL => 1}, 0);
1241 $dbh->bz_add_column("milestones", "product_id",
1242 {TYPE => 'INT2', NOTNULL => 1}, 0);
1243 $dbh->bz_add_column("bugs", "product_id",
1244 {TYPE => 'INT2', NOTNULL => 1}, 0);
1245
1246 # The attachstatusdefs table was added in version 2.15, but
1247 # removed again in early 2.17. If it exists now, we still need
1248 # to perform this change with product_id because the code later on
1249 # which converts the attachment statuses to flags depends on it.
1250 # But we need to avoid this if the user is upgrading from 2.14
1251 # or earlier (because it won't be there to convert).
1252 if ($dbh->bz_table_info("attachstatusdefs")) {
1253 $dbh->bz_add_column("attachstatusdefs", "product_id",
1254 {TYPE => 'INT2', NOTNULL => 1}, 0);
1255 }
1256
1257 my %products;
1258 my $sth = $dbh->prepare("SELECT id, product FROM products");
1259 $sth->execute;
1260 while (my ($product_id, $product) = $sth->fetchrow_array()) {
1261 if (exists $products{$product}) {
1262 print "Ignoring duplicate product $product\n";
1263 $dbh->do("DELETE FROM products WHERE id = $product_id");
1264 next;
1265 }
1266 $products{$product} = 1;
1267 $dbh->do("UPDATE components SET product_id = $product_id " .
1268 "WHERE program = " . $dbh->quote($product));
1269 $dbh->do("UPDATE versions SET product_id = $product_id " .
1270 "WHERE program = " . $dbh->quote($product));
1271 $dbh->do("UPDATE milestones SET product_id = $product_id " .
1272 "WHERE product = " . $dbh->quote($product));
1273 $dbh->do("UPDATE bugs SET product_id = $product_id " .
1274 "WHERE product = " . $dbh->quote($product));
1275 $dbh->do("UPDATE attachstatusdefs SET product_id = $product_id " .
1276 "WHERE product = " . $dbh->quote($product))
1277 if $dbh->bz_table_info("attachstatusdefs");
1278 }
1279
1280 print "Updating the database to use component IDs.\n";
1281
1282 $dbh->bz_add_column("components", "id",
1283 {TYPE => 'SMALLSERIAL', NOTNULL => 1, PRIMARYKEY => 1});
1284 $dbh->bz_add_column("bugs", "component_id",
1285 {TYPE => 'INT2', NOTNULL => 1}, 0);
1286
1287 my %components;
1288 $sth = $dbh->prepare("SELECT id, value, product_id FROM components");
1289 $sth->execute;
1290 while (my ($component_id, $component, $product_id)
1291 = $sth->fetchrow_array())
1292 {
1293 if (exists $components{$component}) {
1294 if (exists $components{$component}{$product_id}) {
1295 print "Ignoring duplicate component $component for",
1296 " product $product_id\n";
1297 $dbh->do("DELETE FROM components WHERE id = $component_id");
1298 next;
1299 }
1300 } else {
1301 $components{$component} = {};
1302 }
1303 $components{$component}{$product_id} = 1;
1304 $dbh->do("UPDATE bugs SET component_id = $component_id " .
1305 "WHERE component = " . $dbh->quote($component) .
1306 " AND product_id = $product_id");
1307 }
1308 print "Fixing Indexes and Uniqueness.\n";
1309 $dbh->bz_drop_index('milestones', 'milestones_product_idx');
1310
1311 $dbh->bz_add_index('milestones', 'milestones_product_id_idx',
1312 {TYPE => 'UNIQUE', FIELDS => [qw(product_id value)]});
1313
1314 $dbh->bz_drop_index('bugs', 'bugs_product_idx');
1315 $dbh->bz_add_index('bugs', 'bugs_product_id_idx', [qw(product_id)]);
1316 $dbh->bz_drop_index('bugs', 'bugs_component_idx');
1317 $dbh->bz_add_index('bugs', 'bugs_component_id_idx', [qw(component_id)]);
1318
1319 print "Removing, renaming, and retyping old product and",
1320 " component fields.\n";
1321 $dbh->bz_drop_column("components", "program");
1322 $dbh->bz_drop_column("versions", "program");
1323 $dbh->bz_drop_column("milestones", "product");
1324 $dbh->bz_drop_column("bugs", "product");
1325 $dbh->bz_drop_column("bugs", "component");
1326 $dbh->bz_drop_column("attachstatusdefs", "product")
1327 if $dbh->bz_table_info("attachstatusdefs");
1328 $dbh->bz_rename_column("products", "product", "name");
1329 $dbh->bz_alter_column("products", "name",
1330 {TYPE => 'varchar(64)', NOTNULL => 1});
1331 $dbh->bz_rename_column("components", "value", "name");
1332 $dbh->bz_alter_column("components", "name",
1333 {TYPE => 'varchar(64)', NOTNULL => 1});
1334
1335 print "Adding indexes for products and components tables.\n";
1336 $dbh->bz_add_index('products', 'products_name_idx',
1337 {TYPE => 'UNIQUE', FIELDS => [qw(name)]});
1338 $dbh->bz_add_index('components', 'components_product_id_idx',
1339 {TYPE => 'UNIQUE', FIELDS => [qw(product_id name)]});
1340 $dbh->bz_add_index('components', 'components_name_idx', [qw(name)]);
1341 }
1342 }
1343
1344 # Helper for the below function.
1345 #
1346 # _list_bits(arg) returns a list of UNKNOWN<n> if the group
1347 # has been deleted for all bits set in arg. When the activity
1348 # records are converted from groupset numbers to lists of
1349 # group names, _list_bits is used to fill in a list of references
1350 # to groupset bits for groups that no longer exist.
1351 sub _list_bits {
1352 my ($num) = @_;
1353 my $dbh = Bugzilla->dbh;
1354 my @res;
1355 my $curr = 1;
1356 while (1) {
1357 # Convert a big integer to a list of bits
1358 my $sth = $dbh->prepare("SELECT ($num & ~$curr) > 0,
1359 ($num & $curr),
1360 ($num & ~$curr),
1361 $curr << 1");
1362 $sth->execute;
1363 my ($more, $thisbit, $remain, $nval) = $sth->fetchrow_array;
1364 push @res,"UNKNOWN<$curr>" if ($thisbit);
1365 $curr = $nval;
1366 $num = $remain;
1367 last if !$more;
1368 }
1369 return @res;
1370 }
1371
1372 sub _convert_groups_system_from_groupset {
1373 my $dbh = Bugzilla->dbh;
1374 # 2002-09-22 - bugreport@peshkin.net - bug 157756
1375 #
1376 # If the whole groups system is new, but the installation isn't,
1377 # convert all the old groupset groups, etc...
1378 #
1379 # This requires:
1380 # 1) define groups ids in group table
1381 # 2) populate user_group_map with grants from old groupsets
1382 # and blessgroupsets
1383 # 3) populate bug_group_map with data converted from old bug groupsets
1384 # 4) convert activity logs to use group names instead of numbers
1385 # 5) identify the admin from the old all-ones groupset
1386
1387 # The groups system needs to be converted if groupset exists
1388 if ($dbh->bz_column_info("profiles", "groupset")) {
1389 # Some mysql versions will promote any unique key to primary key
1390 # so all unique keys are removed first and then added back in
1391 $dbh->bz_drop_index('groups', 'groups_bit_idx');
1392 $dbh->bz_drop_index('groups', 'groups_name_idx');
1393 my @primary_key = $dbh->primary_key(undef, undef, 'groups');
1394 if (@primary_key) {
1395 $dbh->do("ALTER TABLE groups DROP PRIMARY KEY");
1396 }
1397
1398 $dbh->bz_add_column('groups', 'id',
1399 {TYPE => 'MEDIUMSERIAL', NOTNULL => 1, PRIMARYKEY => 1});
1400
1401 $dbh->bz_add_index('groups', 'groups_name_idx',
1402 {TYPE => 'UNIQUE', FIELDS => [qw(name)]});
1403
1404 # Convert all existing groupset records to map entries before removing
1405 # groupset fields or removing "bit" from groups.
1406 my $sth = $dbh->prepare("SELECT bit, id FROM groups WHERE bit > 0");
1407 $sth->execute();
1408 while (my ($bit, $gid) = $sth->fetchrow_array) {
1409 # Create user_group_map membership grants for old groupsets.
1410 # Get each user with the old groupset bit set
1411 my $sth2 = $dbh->prepare("SELECT userid FROM profiles
1412 WHERE (groupset & $bit) != 0");
1413 $sth2->execute();
1414 while (my ($uid) = $sth2->fetchrow_array) {
1415 # Check to see if the user is already a member of the group
1416 # and, if not, insert a new record.
1417 my $query = "SELECT user_id FROM user_group_map
1418 WHERE group_id = $gid AND user_id = $uid
1419 AND isbless = 0";
1420 my $sth3 = $dbh->prepare($query);
1421 $sth3->execute();
1422 if ( !$sth3->fetchrow_array() ) {
1423 $dbh->do("INSERT INTO user_group_map
1424 (user_id, group_id, isbless, grant_type)
1425 VALUES ($uid, $gid, 0, " . GRANT_DIRECT . ")");
1426 }
1427 }
1428 # Create user can bless group grants for old groupsets, but only
1429 # if we're upgrading from a Bugzilla that had blessing.
1430 if($dbh->bz_column_info('profiles', 'blessgroupset')) {
1431 # Get each user with the old blessgroupset bit set
1432 $sth2 = $dbh->prepare("SELECT userid FROM profiles
1433 WHERE (blessgroupset & $bit) != 0");
1434 $sth2->execute();
1435 while (my ($uid) = $sth2->fetchrow_array) {
1436 $dbh->do("INSERT INTO user_group_map
1437 (user_id, group_id, isbless, grant_type)
1438 VALUES ($uid, $gid, 1, " . GRANT_DIRECT . ")");
1439 }
1440 }
1441 # Create bug_group_map records for old groupsets.
1442 # Get each bug with the old group bit set.
1443 $sth2 = $dbh->prepare("SELECT bug_id FROM bugs
1444 WHERE (groupset & $bit) != 0");
1445 $sth2->execute();
1446 while (my ($bug_id) = $sth2->fetchrow_array) {
1447 # Insert the bug, group pair into the bug_group_map.
1448 $dbh->do("INSERT INTO bug_group_map (bug_id, group_id)
1449 VALUES ($bug_id, $gid)");
1450 }
1451 }
1452 # Replace old activity log groupset records with lists of names
1453 # of groups.
1454 $sth = $dbh->prepare("SELECT id FROM fielddefs
1455 WHERE name = " . $dbh->quote('bug_group'));
1456 $sth->execute();
1457 my ($bgfid) = $sth->fetchrow_array;
1458 # Get the field id for the old groupset field
1459 $sth = $dbh->prepare("SELECT id FROM fielddefs
1460 WHERE name = " . $dbh->quote('groupset'));
1461 $sth->execute();
1462 my ($gsid) = $sth->fetchrow_array;
1463 # Get all bugs_activity records from groupset changes
1464 if ($gsid) {
1465 $sth = $dbh->prepare("SELECT bug_id, bug_when, who, added, removed
1466 FROM bugs_activity WHERE fieldid = $gsid");
1467 $sth->execute();
1468 while (my ($bug_id, $bug_when, $who, $added, $removed) =
1469 $sth->fetchrow_array)
1470 {
1471 $added ||= 0;
1472 $removed ||= 0;
1473 # Get names of groups added.
1474 my $sth2 = $dbh->prepare("SELECT name FROM groups
1475 WHERE (bit & $added) != 0
1476 AND (bit & $removed) = 0");
1477 $sth2->execute();
1478 my @logadd;
1479 while (my ($n) = $sth2->fetchrow_array) {
1480 push @logadd, $n;
1481 }
1482 # Get names of groups removed.
1483 $sth2 = $dbh->prepare("SELECT name FROM groups
1484 WHERE (bit & $removed) != 0
1485 AND (bit & $added) = 0");
1486 $sth2->execute();
1487 my @logrem;
1488 while (my ($n) = $sth2->fetchrow_array) {
1489 push @logrem, $n;
1490 }
1491 # Get list of group bits added that correspond to
1492 # missing groups.
1493 $sth2 = $dbh->prepare("SELECT ($added & ~BIT_OR(bit))
1494 FROM groups");
1495 $sth2->execute();
1496 my ($miss) = $sth2->fetchrow_array;
1497 if ($miss) {
1498 push @logadd, _list_bits($miss);
1499 print "\nWARNING - GROUPSET ACTIVITY ON BUG $bug_id",
1500 " CONTAINS DELETED GROUPS\n";
1501 }
1502 # Get list of group bits deleted that correspond to
1503 # missing groups.
1504 $sth2 = $dbh->prepare("SELECT ($removed & ~BIT_OR(bit))
1505 FROM groups");
1506 $sth2->execute();
1507 ($miss) = $sth2->fetchrow_array;
1508 if ($miss) {
1509 push @logrem, _list_bits($miss);
1510 print "\nWARNING - GROUPSET ACTIVITY ON BUG $bug_id",
1511 " CONTAINS DELETED GROUPS\n";
1512 }
1513 my $logr = "";
1514 my $loga = "";
1515 $logr = join(", ", @logrem) . '?' if @logrem;
1516 $loga = join(", ", @logadd) . '?' if @logadd;
1517 # Replace to old activity record with the converted data.
1518 $dbh->do("UPDATE bugs_activity SET fieldid = $bgfid, added = " .
1519 $dbh->quote($loga) . ", removed = " .
1520 $dbh->quote($logr) .
1521 " WHERE bug_id = $bug_id AND bug_when = " .
1522 $dbh->quote($bug_when) .
1523 " AND who = $who AND fieldid = $gsid");
1524 }
1525 # Replace groupset changes with group name changes in
1526 # profiles_activity. Get profiles_activity records for groupset.
1527 $sth = $dbh->prepare(
1528 "SELECT userid, profiles_when, who, newvalue, oldvalue " .
1529 "FROM profiles_activity " .
1530 "WHERE fieldid = $gsid");
1531 $sth->execute();
1532 while (my ($uid, $uwhen, $uwho, $added, $removed) =
1533 $sth->fetchrow_array)
1534 {
1535 $added ||= 0;
1536 $removed ||= 0;
1537 # Get names of groups added.
1538 my $sth2 = $dbh->prepare("SELECT name FROM groups
1539 WHERE (bit & $added) != 0
1540 AND (bit & $removed) = 0");
1541 $sth2->execute();
1542 my @logadd;
1543 while (my ($n) = $sth2->fetchrow_array) {
1544 push @logadd, $n;
1545 }
1546 # Get names of groups removed.
1547 $sth2 = $dbh->prepare("SELECT name FROM groups
1548 WHERE (bit & $removed) != 0
1549 AND (bit & $added) = 0");
1550 $sth2->execute();
1551 my @logrem;
1552 while (my ($n) = $sth2->fetchrow_array) {
1553 push @logrem, $n;
1554 }
1555 my $ladd = "";
1556 my $lrem = "";
1557 $ladd = join(", ", @logadd) . '?' if @logadd;
1558 $lrem = join(", ", @logrem) . '?' if @logrem;
1559 # Replace profiles_activity record for groupset change
1560 # with group list.
1561 $dbh->do("UPDATE profiles_activity " .
1562 "SET fieldid = $bgfid, newvalue = " .
1563 $dbh->quote($ladd) . ", oldvalue = " .
1564 $dbh->quote($lrem) .
1565 " WHERE userid = $uid AND profiles_when = " .
1566 $dbh->quote($uwhen) .
1567 " AND who = $uwho AND fieldid = $gsid");
1568 }
1569 }
1570
1571 # Identify admin group.
1572 my ($admin_gid) = $dbh->selectrow_array(
1573 "SELECT id FROM groups WHERE name = 'admin'");
1574 if (!$admin_gid) {
1575 $dbh->do(q{INSERT INTO groups (name, description)
1576 VALUES ('admin', 'Administrators')});
1577 $admin_gid = $dbh->bz_last_key('groups', 'id');
1578 }
1579 # Find current admins
1580 my @admins;
1581 # Don't lose admins from DBs where Bug 157704 applies
1582 $sth = $dbh->prepare(
1583 "SELECT userid, (groupset & 65536), login_name " .
1584 "FROM profiles " .
1585 "WHERE (groupset | 65536) = 9223372036854775807");
1586 $sth->execute();
1587 while ( my ($userid, $iscomplete, $login_name)
1588 = $sth->fetchrow_array() )
1589 {
1590 # existing administrators are made members of group "admin"
1591 print "\nWARNING - $login_name IS AN ADMIN IN SPITE OF BUG",
1592 " 157704\n\n" if (!$iscomplete);
1593 push(@admins, $userid) unless grep($_ eq $userid, @admins);
1594 }
1595 # Now make all those users admins directly. They were already
1596 # added to every other group, above, because of their groupset.
1597 foreach my $admin_id (@admins) {
1598 $dbh->do("INSERT INTO user_group_map
1599 (user_id, group_id, isbless, grant_type)
1600 VALUES (?, ?, ?, ?)",
1601 undef, $admin_id, $admin_gid, $_, GRANT_DIRECT)
1602 foreach (0, 1);
1603 }
1604
1605 $dbh->bz_drop_column('profiles','groupset');
1606 $dbh->bz_drop_column('profiles','blessgroupset');
1607 $dbh->bz_drop_column('bugs','groupset');
1608 $dbh->bz_drop_column('groups','bit');
1609 $dbh->do("DELETE FROM fielddefs WHERE name = "
1610 . $dbh->quote('groupset'));
1611 }
1612 }
1613
1614 sub _convert_attachment_statuses_to_flags {
1615 my $dbh = Bugzilla->dbh;
1616
1617 # September 2002 myk@mozilla.org bug 98801
1618 # Convert the attachment statuses tables into flags tables.
1619 if ($dbh->bz_table_info("attachstatuses")
1620 && $dbh->bz_table_info("attachstatusdefs"))
1621 {
1622 print "Converting attachment statuses to flags...\n";
1623
1624 # Get IDs for the old attachment status and new flag fields.
1625 my ($old_field_id) = $dbh->selectrow_array(
1626 "SELECT id FROM fielddefs WHERE name='attachstatusdefs.name'")
1627 || 0;
1628 my ($new_field_id) = $dbh->selectrow_array(
1629 "SELECT id FROM fielddefs WHERE name = 'flagtypes.name'");
1630
1631 # Convert attachment status definitions to flag types. If more than one
1632 # status has the same name and description, it is merged into a single
1633 # status with multiple inclusion records.
1634
1635 my $sth = $dbh->prepare(
1636 "SELECT id, name, description, sortkey, product_id
1637 FROM attachstatusdefs");
1638
1639 # status definition IDs indexed by name/description
1640 my $def_ids = {};
1641
1642 # merged IDs and the IDs they were merged into. The key is the old ID,
1643 # the value is the new one. This allows us to give statuses the right
1644 # ID when we convert them over to flags. This map includes IDs that
1645 # weren't merged (in this case the old and new IDs are the same), since
1646 # it makes the code simpler.
1647 my $def_id_map = {};
1648
1649 $sth->execute();
1650 while (my ($id, $name, $desc, $sortkey, $prod_id) =
1651 $sth->fetchrow_array())
1652 {
1653 my $key = $name . $desc;
1654 if (!$def_ids->{$key}) {
1655 $def_ids->{$key} = $id;
1656 my $quoted_name = $dbh->quote($name);
1657 my $quoted_desc = $dbh->quote($desc);
1658 $dbh->do("INSERT INTO flagtypes (id, name, description,
1659 sortkey, target_type)
1660 VALUES ($id, $quoted_name, $quoted_desc,
1661 $sortkey,'a')");
1662 }
1663 $def_id_map->{$id} = $def_ids->{$key};
1664 $dbh->do("INSERT INTO flaginclusions (type_id, product_id)
1665 VALUES ($def_id_map->{$id}, $prod_id)");
1666 }
1667
1668 # Note: even though we've converted status definitions, we still
1669 # can't drop the table because we need it to convert the statuses
1670 # themselves.
1671
1672 # Convert attachment statuses to flags. To do this we select
1673 # the statuses from the status table and then, for each one,
1674 # figure out who set it and when they set it from the bugs
1675 # activity table.
1676 my $id = 0;
1677 $sth = $dbh->prepare(
1678 "SELECT attachstatuses.attach_id, attachstatusdefs.id,
1679 attachstatusdefs.name, attachments.bug_id
1680 FROM attachstatuses, attachstatusdefs, attachments
1681 WHERE attachstatuses.statusid = attachstatusdefs.id
1682 AND attachstatuses.attach_id = attachments.attach_id");
1683
1684 # a query to determine when the attachment status was set and who set it
1685 my $sth2 = $dbh->prepare("SELECT added, who, bug_when
1686 FROM bugs_activity
1687 WHERE bug_id = ? AND attach_id = ?
1688 AND fieldid = $old_field_id
1689 ORDER BY bug_when DESC");
1690
1691 $sth->execute();
1692 while (my ($attach_id, $def_id, $status, $bug_id) =
1693 $sth->fetchrow_array())
1694 {
1695 ++$id;
1696
1697 # Determine when the attachment status was set and who set it.
1698 # We should always be able to find out this info from the bug
1699 # activity, but we fall back to default values just in case.
1700 $sth2->execute($bug_id, $attach_id);
1701 my ($added, $who, $when);
1702 while (($added, $who, $when) = $sth2->fetchrow_array()) {
1703 last if $added =~ /(^|[, ]+)\Q$status\E([, ]+|$)/;
1704 }
1705 $who = $dbh->quote($who); # "NULL" by default if $who is undefined
1706 $when = $when ? $dbh->quote($when) : "NOW()";
1707
1708
1709 $dbh->do("INSERT INTO flags (id, type_id, status, bug_id,
1710 attach_id, creation_date, modification_date,
1711 requestee_id, setter_id)
1712 VALUES ($id, $def_id_map->{$def_id}, '+', $bug_id,
1713 $attach_id, $when, $when, NULL, $who)");
1714 }
1715
1716 # Now that we've converted both tables we can drop them.
1717 $dbh->bz_drop_table("attachstatuses");
1718 $dbh->bz_drop_table("attachstatusdefs");
1719
1720 # Convert activity records for attachment statuses into records
1721 # for flags.
1722 $sth = $dbh->prepare("SELECT attach_id, who, bug_when, added,
1723 removed
1724 FROM bugs_activity
1725 WHERE fieldid = $old_field_id");
1726 $sth->execute();
1727 while (my ($attach_id, $who, $when, $old_added, $old_removed) =
1728 $sth->fetchrow_array())
1729 {
1730 my @additions = split(/[, ]+/, $old_added);
1731 @additions = map("$_+", @additions);
1732 my $new_added = $dbh->quote(join(", ", @additions));
1733
1734 my @removals = split(/[, ]+/, $old_removed);
1735 @removals = map("$_+", @removals);
1736 my $new_removed = $dbh->quote(join(", ", @removals));
1737
1738 $old_added = $dbh->quote($old_added);
1739 $old_removed = $dbh->quote($old_removed);
1740 $who = $dbh->quote($who);
1741 $when = $dbh->quote($when);
1742
1743 $dbh->do("UPDATE bugs_activity SET fieldid = $new_field_id, " .
1744 "added = $new_added, removed = $new_removed " .
1745 "WHERE attach_id = $attach_id AND who = $who " .
1746 "AND bug_when = $when AND fieldid = $old_field_id " .
1747 "AND added = $old_added AND removed = $old_removed");
1748 }
1749
1750 # Remove the attachment status field from the field definitions.
1751 $dbh->do("DELETE FROM fielddefs WHERE name='attachstatusdefs.name'");
1752
1753 print "done.\n";
1754 }
1755 }
1756
1757 sub _remove_spaces_and_commas_from_flagtypes {
1758 my $dbh = Bugzilla->dbh;
1759 # Get all names and IDs, to find broken ones and to
1760 # check for collisions when renaming.
1761 my $sth = $dbh->prepare("SELECT name, id FROM flagtypes");
1762 $sth->execute();
1763
1764 my %flagtypes;
1765 my @badflagnames;
1766 # find broken flagtype names, and populate a hash table
1767 # to check for collisions.
1768 while (my ($name, $id) = $sth->fetchrow_array()) {
1769 $flagtypes{$name} = $id;
1770 if ($name =~ /[ ,]/) {
1771 push(@badflagnames, $name);
1772 }
1773 }
1774 if (@badflagnames) {
1775 print "Removing spaces and commas from flag names...\n";
1776 my ($flagname, $tryflagname);
1777 my $sth = $dbh->prepare("UPDATE flagtypes SET name = ? WHERE id = ?");
1778 foreach $flagname (@badflagnames) {
1779 print " Bad flag type name \"$flagname\" ...\n";
1780 # find a new name for this flagtype.
1781 ($tryflagname = $flagname) =~ tr/ ,/__/;
1782 # avoid collisions with existing flagtype names.
1783 while (defined($flagtypes{$tryflagname})) {
1784 print " ... can't rename as \"$tryflagname\" ...\n";
1785 $tryflagname .= "'";
1786 if (length($tryflagname) > 50) {
1787 my $lastchanceflagname = (substr $tryflagname, 0, 47) . '...';
1788 if (defined($flagtypes{$lastchanceflagname})) {
1789 print " ... last attempt as \"$lastchanceflagname\" still failed.'\n",
1790 "Rename the flag by hand and run checksetup.pl again.\n";
1791 die("Bad flag type name $flagname");
1792 }
1793 $tryflagname = $lastchanceflagname;
1794 }
1795 }
1796 $sth->execute($tryflagname, $flagtypes{$flagname});
1797 print " renamed flag type \"$flagname\" as \"$tryflagname\"\n";
1798 $flagtypes{$tryflagname} = $flagtypes{$flagname};
1799 delete $flagtypes{$flagname};
1800 }
1801 print "... done.\n";
1802 }
1803 }
1804
1805 sub _setup_usebuggroups_backward_compatibility {
1806 my $dbh = Bugzilla->dbh;
1807 # 2002-11-24 - bugreport@peshkin.net - bug 147275
1808 #
1809 # If group_control_map is empty, backward-compatibility
1810 # usebuggroups-equivalent records should be created.
1811 my $entry = Bugzilla->params->{'useentrygroupdefault'};
1812 my ($maps_exist) = $dbh->selectrow_array(
1813 "SELECT DISTINCT 1 FROM group_control_map");
1814 if (!$maps_exist) {
1815 # Initially populate group_control_map.
1816 # First, get all the existing products and their groups.
1817 my $sth = $dbh->prepare("SELECT groups.id, products.id, groups.name,
1818 products.name
1819 FROM groups, products
1820 WHERE isbuggroup != 0");
1821 $sth->execute();
1822 while (my ($groupid, $productid, $groupname, $productname)
1823 = $sth->fetchrow_array())
1824 {
1825 if ($groupname eq $productname) {
1826 # Product and group have same name.
1827 $dbh->do("INSERT INTO group_control_map " .
1828 "(group_id, product_id, entry, membercontrol, " .
1829 "othercontrol, canedit) " .
1830 "VALUES ($groupid, $productid, $entry, " .
1831 CONTROLMAPDEFAULT . ", " .
1832 CONTROLMAPNA . ", 0)");
1833 } else {
1834 # See if this group is a product group at all.
1835 my $sth2 = $dbh->prepare("SELECT id FROM products
1836 WHERE name = " .$dbh->quote($groupname));
1837 $sth2->execute();
1838 my ($id) = $sth2->fetchrow_array();
1839 if (!$id) {
1840 # If there is no product with the same name as this
1841 # group, then it is permitted for all products.
1842 $dbh->do("INSERT INTO group_control_map " .
1843 "(group_id, product_id, entry, membercontrol, " .
1844 "othercontrol, canedit) " .
1845 "VALUES ($groupid, $productid, 0, " .
1846 CONTROLMAPSHOWN . ", " .
1847 CONTROLMAPNA . ", 0)");
1848 }
1849 }
1850 }
1851 }
1852 }
1853
1854 sub _remove_user_series_map {
1855 my $dbh = Bugzilla->dbh;
1856 # 2004-07-17 GRM - Remove "subscriptions" concept from charting, and add
1857 # group-based security instead.
1858 if ($dbh->bz_table_info("user_series_map")) {
1859 # Oracle doesn't like "date" as a column name, and apparently some DBs
1860 # don't like 'value' either. We use the changes to subscriptions as
1861 # something to hang these renamings off.
1862 $dbh->bz_rename_column('series_data', 'date', 'series_date');
1863 $dbh->bz_rename_column('series_data', 'value', 'series_value');
1864
1865 # series_categories.category_id produces a too-long column name for the
1866 # auto-incrementing sequence (Oracle again).
1867 $dbh->bz_rename_column('series_categories', 'category_id', 'id');
1868
1869 $dbh->bz_add_column("series", "public",
1870 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'});
1871
1872 # Migrate public-ness across from user_series_map to new field
1873 my $sth = $dbh->prepare("SELECT series_id from user_series_map " .
1874 "WHERE user_id = 0");
1875 $sth->execute();
1876 while (my ($public_series_id) = $sth->fetchrow_array()) {
1877 $dbh->do("UPDATE series SET public = 1 " .
1878 "WHERE series_id = $public_series_id");
1879 }
1880
1881 $dbh->bz_drop_table("user_series_map");
1882 }
1883 }
1884
1885 sub _copy_old_charts_into_database {
1886 my $dbh = Bugzilla->dbh;
1887 my $datadir = bz_locations()->{'datadir'};
1888 # 2003-06-26 Copy the old charting data into the database, and create the
1889 # queries that will keep it all running. When the old charting system goes
1890 # away, if this code ever runs, it'll just find no files and do nothing.
1891 my $series_exists = $dbh->selectrow_array("SELECT 1 FROM series " .
1892 $dbh->sql_limit(1));
1893 if (!$series_exists && -d "$datadir/mining" && -e "$datadir/mining/-All-") {
1894 print "Migrating old chart data into database...\n";
1895
1896 # We prepare the handle to insert the series data
1897 my $seriesdatasth = $dbh->prepare(
1898 "INSERT INTO series_data (series_id, series_date, series_value)
1899 VALUES (?, ?, ?)");
1900
1901 my $deletesth = $dbh->prepare(
1902 "DELETE FROM series_data WHERE series_id = ? AND series_date = ?");
1903
1904 my $groupmapsth = $dbh->prepare(
1905 "INSERT INTO category_group_map (category_id, group_id)
1906 VALUES (?, ?)");
1907
1908 # Fields in the data file (matches the current collectstats.pl)
1909 my @statuses =
1910 qw(NEW ASSIGNED REOPENED UNCONFIRMED RESOLVED VERIFIED CLOSED);
1911 my @resolutions =
1912 qw(FIXED INVALID WONTFIX LATER REMIND DUPLICATE WORKSFORME MOVED);
1913 my @fields = (@statuses, @resolutions);
1914
1915 # We have a localization problem here. Where do we get these values?
1916 my $all_name = "-All-";
1917 my $open_name = "All Open";
1918
1919 my $products = $dbh->selectall_arrayref("SELECT name FROM products");
1920
1921 foreach my $product ((map { $_->[0] } @$products), "-All-") {
1922 # First, create the series
1923 my %queries;
1924 my %seriesids;
1925
1926 my $query_prod = "";
1927 if ($product ne "-All-") {
1928 $query_prod = "product=" . html_quote($product) . "&";
1929 }
1930
1931 # The query for statuses is different to that for resolutions.
1932 $queries{$_} = ($query_prod . "bug_status=$_") foreach (@statuses);
1933 $queries{$_} = ($query_prod . "resolution=$_")
1934 foreach (@resolutions);
1935
1936 foreach my $field (@fields) {
1937 # Create a Series for each field in this product.
1938 my $series = new Bugzilla::Series(undef, $product, $all_name,
1939 $field, undef, 1,
1940 $queries{$field}, 1);
1941 $series->writeToDatabase();
1942 $seriesids{$field} = $series->{'series_id'};
1943 }
1944
1945 # We also add a new query for "Open", so that migrated products get
1946 # the same set as new products (see editproducts.cgi.)
1947 my @openedstatuses = ("UNCONFIRMED", "NEW", "ASSIGNED", "REOPENED");
1948 my $query = join("&", map { "bug_status=$_" } @openedstatuses);
1949 my $series = new Bugzilla::Series(undef, $product, $all_name,
1950 $open_name, undef, 1,
1951 $query_prod . $query, 1);
1952 $series->writeToDatabase();
1953 $seriesids{$open_name} = $series->{'series_id'};
1954
1955 # Now, we attempt to read in historical data, if any
1956 # Convert the name in the same way that collectstats.pl does
1957 my $product_file = $product;
1958 $product_file =~ s/\//-/gs;
1959 $product_file = "$datadir/mining/$product_file";
1960
1961 # There are many reasons that this might fail (e.g. no stats
1962 # for this product), so we don't worry if it does.
1963 my $in = new IO::File($product_file) or next;
1964
1965 # The data files should be in a standard format, even for old
1966 # Bugzillas, because of the conversion code further up this file.
1967 my %data;
1968 my $last_date = "";
1969
1970 while (<$in>) {
1971 if (/^(\d+\|.*)/) {
1972 my @numbers = split(/\||\r/, $1);
1973
1974 # Only take the first line for each date; it was possible to
1975 # run collectstats.pl more than once in a day.
1976 next if $numbers[0] eq $last_date;
1977
1978 for my $i (0 .. $#fields) {
1979 # $numbers[0] is the date
1980 $data{$fields[$i]}{$numbers[0]} = $numbers[$i + 1];
1981
1982 # Keep a total of the number of open bugs for this day
1983 if (grep { $_ eq $fields[$i] } @openedstatuses) {
1984 $data{$open_name}{$numbers[0]} += $numbers[$i + 1];
1985 }
1986 }
1987
1988 $last_date = $numbers[0];
1989 }
1990 }
1991
1992 $in->close;
1993
1994 foreach my $field (@fields, $open_name) {
1995 # Insert values into series_data: series_id, date, value
1996 my %fielddata = %{$data{$field}};
1997 foreach my $date (keys %fielddata) {
1998 # We need to delete in case the text file had duplicate
1999 # entries in it.
2000 $deletesth->execute($seriesids{$field}, $date);
2001
2002 # We prepared this above
2003 $seriesdatasth->execute($seriesids{$field},
2004 $date, $fielddata{$date} || 0);
2005 }
2006 }
2007
2008 # Create the groupsets for the category
2009 my $category_id =
2010 $dbh->selectrow_array("SELECT id FROM series_categories " .
2011 "WHERE name = " . $dbh->quote($product));
2012 my $product_id =
2013 $dbh->selectrow_array("SELECT id FROM products " .
2014 "WHERE name = " . $dbh->quote($product));
2015
2016 if (defined($category_id) && defined($product_id)) {
2017
2018 # Get all the mandatory groups for this product
2019 my $group_ids =
2020 $dbh->selectcol_arrayref("SELECT group_id " .
2021 "FROM group_control_map " .
2022 "WHERE product_id = $product_id " .
2023 "AND (membercontrol = " . CONTROLMAPMANDATORY .
2024 " OR othercontrol = " . CONTROLMAPMANDATORY . ")");
2025
2026 foreach my $group_id (@$group_ids) {
2027 $groupmapsth->execute($category_id, $group_id);
2028 }
2029 }
2030 }
2031 }
2032 }
2033
2034 sub _add_user_group_map_grant_type {
2035 my $dbh = Bugzilla->dbh;
2036 # 2004-04-12 - Keep regexp-based group permissions up-to-date - Bug 240325
2037 if ($dbh->bz_column_info("user_group_map", "isderived")) {
2038 $dbh->bz_add_column('user_group_map', 'grant_type',
2039 {TYPE => 'INT1', NOTNULL => 1, DEFAULT => '0'});
2040 $dbh->do("DELETE FROM user_group_map WHERE isderived != 0");
2041 $dbh->do("UPDATE user_group_map SET grant_type = " . GRANT_DIRECT);
2042 $dbh->bz_drop_column("user_group_map", "isderived");
2043
2044 $dbh->bz_drop_index('user_group_map', 'user_group_map_user_id_idx');
2045 $dbh->bz_add_index('user_group_map', 'user_group_map_user_id_idx',
2046 {TYPE => 'UNIQUE',
2047 FIELDS => [qw(user_id group_id grant_type isbless)]});
2048 }
2049 }
2050
2051 sub _add_group_group_map_grant_type {
2052 my $dbh = Bugzilla->dbh;
2053 # 2004-07-16 - Make it possible to have group-group relationships other than
2054 # membership and bless.
2055 if ($dbh->bz_column_info("group_group_map", "isbless")) {
2056 $dbh->bz_add_column('group_group_map', 'grant_type',
2057 {TYPE => 'INT1', NOTNULL => 1, DEFAULT => '0'});
2058 $dbh->do("UPDATE group_group_map SET grant_type = " .
2059 "IF(isbless, " . GROUP_BLESS . ", " .
2060 GROUP_MEMBERSHIP . ")");
2061 $dbh->bz_drop_index('group_group_map', 'group_group_map_member_id_idx');
2062 $dbh->bz_drop_column("group_group_map", "isbless");
2063 $dbh->bz_add_index('group_group_map', 'group_group_map_member_id_idx',
2064 {TYPE => 'UNIQUE',
2065 FIELDS => [qw(member_id grantor_id grant_type)]});
2066 }
2067 }
2068
2069 sub _add_longdescs_already_wrapped {
2070 my $dbh = Bugzilla->dbh;
2071 # 2005-01-29 - mkanat@bugzilla.org
2072 if (!$dbh->bz_column_info('longdescs', 'already_wrapped')) {
2073 # Old, pre-wrapped comments should not be auto-wrapped
2074 $dbh->bz_add_column('longdescs', 'already_wrapped',
2075 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'}, 1);
2076 # If an old comment doesn't have a newline in the first 81 characters,
2077 # (or doesn't contain a newline at all) and it contains a space,
2078 # then it's probably a mis-wrapped comment and we should wrap it
2079 # at display-time.
2080 print "Fixing old, mis-wrapped comments...\n";
2081 $dbh->do(q{UPDATE longdescs SET already_wrapped = 0
2082 WHERE (} . $dbh->sql_position(q{'\n'}, 'thetext') . q{ > 81
2083 OR } . $dbh->sql_position(q{'\n'}, 'thetext') . q{ = 0)
2084 AND SUBSTRING(thetext FROM 1 FOR 80) LIKE '% %'});
2085 }
2086 }
2087
2088 sub _convert_attachments_filename_from_mediumtext {
2089 my $dbh = Bugzilla->dbh;
2090 # 2002 November, myk@mozilla.org, bug 178841:
2091 #
2092 # Convert the "attachments.filename" column from a ridiculously large
2093 # "mediumtext" to a much more sensible "varchar(100)". Also takes
2094 # the opportunity to remove paths from existing filenames, since they
2095 # shouldn't be there for security. Buggy browsers include them,
2096 # and attachment.cgi now takes them out, but old ones need converting.
2097 my $ref = $dbh->bz_column_info("attachments", "filename");
2098 if ($ref->{TYPE} ne 'varchar(100)') {
2099 print "Removing paths from filenames in attachments table...\n";
2100
2101 my $sth = $dbh->prepare("SELECT attach_id, filename FROM attachments " .
2102 "WHERE " . $dbh->sql_position(q{'/'}, 'filename') . " > 0 OR " .
2103 $dbh->sql_position(q{'\\\\'}, 'filename') . " > 0");
2104 $sth->execute;
2105
2106 while (my ($attach_id, $filename) = $sth->fetchrow_array) {
2107 $filename =~ s/^.*[\/\\]//;
2108 my $quoted_filename = $dbh->quote($filename);
2109 $dbh->do("UPDATE attachments SET filename = $quoted_filename " .
2110 "WHERE attach_id = $attach_id");
2111 }
2112
2113 print "Done.\n";
2114
2115 print "Resizing attachments.filename from mediumtext to",
2116 " varchar(100).\n";
2117 $dbh->bz_alter_column("attachments", "filename",
2118 {TYPE => 'varchar(100)', NOTNULL => 1});
2119 }
2120 }
2121
2122 sub _rename_votes_count_and_force_group_refresh {
2123 my $dbh = Bugzilla->dbh;
2124 # 2003-04-27 - bugzilla@chimpychompy.org (GavinS)
2125 #
2126 # Bug 180086 (http://bugzilla.mozilla.org/show_bug.cgi?id=180086)
2127 #
2128 # Renaming the 'count' column in the votes table because Sybase doesn't
2129 # like it
2130 if ($dbh->bz_column_info('votes', 'count')) {
2131 $dbh->bz_rename_column('votes', 'count', 'vote_count');
2132 }
2133 }
2134
2135 sub _fix_group_with_empty_name {
2136 my $dbh = Bugzilla->dbh;
2137 # 2005-01-12 Nick Barnes <nb@ravenbrook.com> bug 278010
2138 # Rename any group which has an empty name.
2139 # Note that there can be at most one such group (because of
2140 # the SQL index on the name column).
2141 my ($emptygroupid) = $dbh->selectrow_array(
2142 "SELECT id FROM groups where name = ''");
2143 if ($emptygroupid) {
2144 # There is a group with an empty name; find a name to rename it
2145 # as. Must avoid collisions with existing names. Start with
2146 # group_$gid and add _<n> if necessary.
2147 my $trycount = 0;
2148 my $trygroupname;
2149 my $sth = $dbh->prepare("SELECT 1 FROM groups where name = ?");
2150 my $name_exists = 1;
2151
2152 while ($name_exists) {
2153 $trygroupname = "group_$emptygroupid";
2154 if ($trycount > 0) {
2155 $trygroupname .= "_$trycount";
2156 }
2157 $name_exists = $dbh->selectrow_array($sth, undef, $trygroupname);
2158 $trycount++;
2159 }
2160 $dbh->do("UPDATE groups SET name = ? WHERE id = ?",
2161 undef, $trygroupname, $emptygroupid);
2162 print "Group $emptygroupid had an empty name; renamed as",
2163 " '$trygroupname'.\n";
2164 }
2165 }
2166
2167 # A helper for the emailprefs subs below
2168 sub _clone_email_event {
2169 my ($source, $target) = @_;
2170 my $dbh = Bugzilla->dbh;
2171
2172 my $sth1 = $dbh->prepare("SELECT user_id, relationship FROM email_setting
2173 WHERE event = $source");
2174 my $sth2 = $dbh->prepare("INSERT into email_setting " .
2175 "(user_id, relationship, event) VALUES (" .
2176 "?, ?, $target)");
2177
2178 $sth1->execute();
2179
2180 while (my ($userid, $relationship) = $sth1->fetchrow_array()) {
2181 $sth2->execute($userid, $relationship);
2182 }
2183 }
2184
2185 sub _migrate_email_prefs_to_new_table {
2186 my $dbh = Bugzilla->dbh;
2187 # 2005-03-29 - gerv@gerv.net - bug 73665.
2188 # Migrate email preferences to new email prefs table.
2189 if ($dbh->bz_column_info("profiles", "emailflags")) {
2190 print "Migrating email preferences to new table...\n";
2191
2192 # These are the "roles" and "reasons" from the original code, mapped to
2193 # the new terminology of relationships and events.
2194 my %relationships = ("Owner" => REL_ASSIGNEE,
2195 "Reporter" => REL_REPORTER,
2196 "QAcontact" => REL_QA,
2197 "CClist" => REL_CC,
2198 "Voter" => REL_VOTER);
2199
2200 my %events = ("Removeme" => EVT_ADDED_REMOVED,
2201 "Comments" => EVT_COMMENT,
2202 "Attachments" => EVT_ATTACHMENT,
2203 "Status" => EVT_PROJ_MANAGEMENT,
2204 "Resolved" => EVT_OPENED_CLOSED,
2205 "Keywords" => EVT_KEYWORD,
2206 "CC" => EVT_CC,
2207 "Other" => EVT_OTHER,
2208 "Unconfirmed" => EVT_UNCONFIRMED);
2209
2210 # Request preferences
2211 my %requestprefs = ("FlagRequestee" => EVT_FLAG_REQUESTED,
2212 "FlagRequester" => EVT_REQUESTED_FLAG);
2213
2214 # We run the below code in a transaction to speed things up.
2215 $dbh->bz_start_transaction();
2216
2217 # Select all emailflags flag strings
2218 my $total = $dbh->selectrow_array('SELECT COUNT(*) FROM profiles');
2219 my $sth = $dbh->prepare("SELECT userid, emailflags FROM profiles");
2220 $sth->execute();
2221 my $i = 0;
2222
2223 while (my ($userid, $flagstring) = $sth->fetchrow_array()) {
2224 $i++;
2225 indicate_progress({ total => $total, current => $i, every => 10 });
2226 # If the user has never logged in since emailprefs arrived, and the
2227 # temporary code to give them a default string never ran, then
2228 # $flagstring will be null. In this case, they just get all mail.
2229 $flagstring ||= "";
2230
2231 # The 255 param is here, because without a third param, split will
2232 # trim any trailing null fields, which causes Perl to eject lots of
2233 # warnings. Any suitably large number would do.
2234 my %emailflags = split(/~/, $flagstring, 255);
2235
2236 my $sth2 = $dbh->prepare("INSERT into email_setting " .
2237 "(user_id, relationship, event) VALUES (" .
2238 "$userid, ?, ?)");
2239 foreach my $relationship (keys %relationships) {
2240 foreach my $event (keys %events) {
2241 my $key = "email$relationship$event";
2242 if (!exists($emailflags{$key})
2243 || $emailflags{$key} eq 'on')
2244 {
2245 $sth2->execute($relationships{$relationship},
2246 $events{$event});
2247 }
2248 }
2249 }
2250 # Note that in the old system, the value of "excludeself" is
2251 # assumed to be off if the preference does not exist in the
2252 # user's list, unlike other preferences whose value is
2253 # assumed to be on if they do not exist.
2254 #
2255 # This preference has changed from global to per-relationship.
2256 if (!exists($emailflags{'ExcludeSelf'})
2257 || $emailflags{'ExcludeSelf'} ne 'on')
2258 {
2259 foreach my $relationship (keys %relationships) {
2260 $dbh->do("INSERT into email_setting " .
2261 "(user_id, relationship, event) VALUES (" .
2262 $userid . ", " .
2263 $relationships{$relationship}. ", " .
2264 EVT_CHANGED_BY_ME . ")");
2265 }
2266 }
2267
2268 foreach my $key (keys %requestprefs) {
2269 if (!exists($emailflags{$key}) || $emailflags{$key} eq 'on') {
2270 $dbh->do("INSERT into email_setting " .
2271 "(user_id, relationship, event) VALUES (" .
2272 $userid . ", " . REL_ANY . ", " .
2273 $requestprefs{$key} . ")");
2274 }
2275 }
2276 }
2277 print "\n";
2278
2279 # EVT_ATTACHMENT_DATA should initially have identical settings to
2280 # EVT_ATTACHMENT.
2281 _clone_email_event(EVT_ATTACHMENT, EVT_ATTACHMENT_DATA);
2282
2283 $dbh->bz_commit_transaction();
2284 $dbh->bz_drop_column("profiles", "emailflags");
2285 }
2286 }
2287
2288 sub _initialize_dependency_tree_changes_email_pref {
2289 my $dbh = Bugzilla->dbh;
2290 # Check for any "new" email settings that wouldn't have been ported over
2291 # during the block above. Since these settings would have otherwise
2292 # fallen under EVT_OTHER, we'll just clone those settings. That way if
2293 # folks have already disabled all of that mail, there won't be any change.
2294 my %events = ("Dependency Tree Changes" => EVT_DEPEND_BLOCK);
2295
2296 foreach my $desc (keys %events) {
2297 my $event = $events{$desc};
2298 my $sth = $dbh->prepare("SELECT COUNT(*) FROM email_setting
2299 WHERE event = $event");
2300 $sth->execute();
2301 if (!($sth->fetchrow_arrayref()->[0])) {
2302 # No settings in the table yet, so we assume that this is the
2303 # first time it's being set.
2304 print "Initializing \"$desc\" email_setting ...\n";
2305 _clone_email_event(EVT_OTHER, $event);
2306 }
2307 }
2308 }
2309
2310 sub _change_all_mysql_booleans_to_tinyint {
2311 my $dbh = Bugzilla->dbh;
2312 # 2005-03-27: Standardize all boolean fields to plain "tinyint"
2313 if ( $dbh->isa('Bugzilla::DB::Mysql') ) {
2314 # This is a change to make things consistent with Schema, so we use
2315 # direct-database access methods.
2316 my $quip_info_sth = $dbh->column_info(undef, undef, 'quips', '%');
2317 my $quips_cols = $quip_info_sth->fetchall_hashref("COLUMN_NAME");
2318 my $approved_col = $quips_cols->{'approved'};
2319 if ( $approved_col->{TYPE_NAME} eq 'TINYINT'
2320 and $approved_col->{COLUMN_SIZE} == 1 )
2321 {
2322 # series.public could have been renamed to series.is_public,
2323 # and so wouldn't need to be fixed manually.
2324 if ($dbh->bz_column_info('series', 'public')) {
2325 $dbh->bz_alter_column_raw('series', 'public',
2326 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => '0'});
2327 }
2328 $dbh->bz_alter_column_raw('bug_status', 'isactive',
2329 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => '1'});
2330 $dbh->bz_alter_column_raw('rep_platform', 'isactive',
2331 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => '1'});
2332 $dbh->bz_alter_column_raw('resolution', 'isactive',
2333 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => '1'});
2334 $dbh->bz_alter_column_raw('op_sys', 'isactive',
2335 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => '1'});
2336 $dbh->bz_alter_column_raw('bug_severity', 'isactive',
2337 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => '1'});
2338 $dbh->bz_alter_column_raw('priority', 'isactive',
2339 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => '1'});
2340 $dbh->bz_alter_column_raw('quips', 'approved',
2341 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => '1'});
2342 }
2343 }
2344 }
2345
2346 # A helper for the below function.
2347 sub _de_dup_version {
2348 my ($product_id, $version) = @_;
2349 my $dbh = Bugzilla->dbh;
2350 print "Fixing duplicate version $version in product_id $product_id...\n";
2351 $dbh->do('DELETE FROM versions WHERE product_id = ? AND value = ?',
2352 undef, $product_id, $version);
2353 $dbh->do('INSERT INTO versions (product_id, value) VALUES (?,?)',
2354 undef, $product_id, $version);
2355 }
2356
2357 sub _add_versions_product_id_index {
2358 my $dbh = Bugzilla->dbh;
2359 if (!$dbh->bz_index_info('versions', 'versions_product_id_idx')) {
2360 my $dup_versions = $dbh->selectall_arrayref(
2361 'SELECT product_id, value FROM versions
2362 GROUP BY product_id, value HAVING COUNT(value) > 1', {Slice=>{}});
2363 foreach my $dup_version (@$dup_versions) {
2364 _de_dup_version($dup_version->{product_id}, $dup_version->{value});
2365 }
2366
2367 $dbh->bz_add_index('versions', 'versions_product_id_idx',
2368 {TYPE => 'UNIQUE', FIELDS => [qw(product_id value)]});
2369 }
2370 }
2371
2372 sub _fix_whine_queries_title_and_op_sys_value {
2373 my $dbh = Bugzilla->dbh;
2374 if (!exists $dbh->bz_column_info('whine_queries', 'title')->{DEFAULT}) {
2375 # The below change actually has nothing to do with the whine_queries
2376 # change, it just has to be contained within a schema change so that
2377 # it doesn't run every time we run checksetup.
2378
2379 # Old Bugzillas have "other" as an OS choice, new ones have "Other"
2380 # (capital O).
2381 print "Setting any 'other' op_sys to 'Other'...\n";
2382 $dbh->do('UPDATE op_sys SET value = ? WHERE value = ?',
2383 undef, "Other", "other");
2384 $dbh->do('UPDATE bugs SET op_sys = ? WHERE op_sys = ?',
2385 undef, "Other", "other");
2386 if (Bugzilla->params->{'defaultopsys'} eq 'other') {
2387 # We can't actually fix the param here, because WriteParams() will
2388 # make $datadir/params unwriteable to the webservergroup.
2389 # It's too much of an ugly hack to copy the permission-fixing code
2390 # down to here. (It would create more potential future bugs than
2391 # it would solve problems.)
2392 print "WARNING: Your 'defaultopsys' param is set to 'other', but"
2393 . " Bugzilla now\n"
2394 . " uses 'Other' (capital O).\n";
2395 }
2396
2397 # Add a DEFAULT to whine_queries stuff so that editwhines.cgi
2398 # works on PostgreSQL.
2399 $dbh->bz_alter_column('whine_queries', 'title', {TYPE => 'varchar(128)',
2400 NOTNULL => 1, DEFAULT => "''"});
2401 }
2402 }
2403
2404 sub _fix_attachments_submitter_id_idx {
2405 my $dbh = Bugzilla->dbh;
2406 # 2005-06-29 bugreport@peshkin.net, bug 299156
2407 if ($dbh->bz_index_info('attachments', 'attachments_submitter_id_idx')
2408 && (scalar(@{$dbh->bz_index_info('attachments',
2409 'attachments_submitter_id_idx'
2410 )->{FIELDS}}) < 2))
2411 {
2412 $dbh->bz_drop_index('attachments', 'attachments_submitter_id_idx');
2413 }
2414 $dbh->bz_add_index('attachments', 'attachments_submitter_id_idx',
2415 [qw(submitter_id bug_id)]);
2416 }
2417
2418 sub _copy_attachments_thedata_to_attach_data {
2419 my $dbh = Bugzilla->dbh;
2420 # 2005-08-25 - bugreport@peshkin.net - Bug 305333
2421 if ($dbh->bz_column_info("attachments", "thedata")) {
2422 print "Migrating attachment data to its own table...\n";
2423 print "(This may take a very long time)\n";
2424 $dbh->do("INSERT INTO attach_data (id, thedata)
2425 SELECT attach_id, thedata FROM attachments");
2426 $dbh->bz_drop_column("attachments", "thedata");
2427 }
2428 }
2429
2430 sub _fix_broken_all_closed_series {
2431 my $dbh = Bugzilla->dbh;
2432
2433 # 2005-11-26 - wurblzap@gmail.com - Bug 300473
2434 # Repair broken automatically generated series queries for non-open bugs.
2435 my $broken_series_indicator =
2436 'field0-0-0=resolution&type0-0-0=notequals&value0-0-0=---';
2437 my $broken_nonopen_series =
2438 $dbh->selectall_arrayref("SELECT series_id, query FROM series
2439 WHERE query LIKE '$broken_series_indicator%'");
2440 if (@$broken_nonopen_series) {
2441 print 'Repairing broken series...';
2442 my $sth_nuke =
2443 $dbh->prepare('DELETE FROM series_data WHERE series_id = ?');
2444 # This statement is used to repair a series by replacing the broken
2445 # query with the correct one.
2446 my $sth_repair =
2447 $dbh->prepare('UPDATE series SET query = ? WHERE series_id = ?');
2448 # The corresponding series for open bugs look like one of these two
2449 # variations (bug 225687 changed the order of bug states).
2450 # This depends on the set of bug states representing open bugs not
2451 # to have changed since series creation.
2452 my $open_bugs_query_base_old =
2453 join("&", map { "bug_status=" . url_quote($_) }
2454 ('UNCONFIRMED', 'NEW', 'ASSIGNED', 'REOPENED'));
2455 my $open_bugs_query_base_new =
2456 join("&", map { "bug_status=" . url_quote($_) }
2457 ('NEW', 'REOPENED', 'ASSIGNED', 'UNCONFIRMED'));
2458 my $sth_openbugs_series =
2459 $dbh->prepare("SELECT series_id FROM series WHERE query IN (?, ?)");
2460 # Statement to find the series which has collected the most data.
2461 my $sth_data_collected =
2462 $dbh->prepare('SELECT count(*) FROM series_data
2463 WHERE series_id = ?');
2464 # Statement to select a broken non-open bugs count data entry.
2465 my $sth_select_broken_nonopen_data =
2466 $dbh->prepare('SELECT series_date, series_value FROM series_data' .
2467 ' WHERE series_id = ?');
2468 # Statement to select an open bugs count data entry.
2469 my $sth_select_open_data =
2470 $dbh->prepare('SELECT series_value FROM series_data' .
2471 ' WHERE series_id = ? AND series_date = ?');
2472 # Statement to fix a broken non-open bugs count data entry.
2473 my $sth_fix_broken_nonopen_data =
2474 $dbh->prepare('UPDATE series_data SET series_value = ?' .
2475 ' WHERE series_id = ? AND series_date = ?');
2476 # Statement to delete an unfixable broken non-open bugs count data
2477 # entry.
2478 my $sth_delete_broken_nonopen_data =
2479 $dbh->prepare('DELETE FROM series_data' .
2480 ' WHERE series_id = ? AND series_date = ?');
2481 foreach (@$broken_nonopen_series) {
2482 my ($broken_series_id, $nonopen_bugs_query) = @$_;
2483
2484 # Determine the product-and-component part of the query.
2485 if ($nonopen_bugs_query =~ /^$broken_series_indicator(.*)$/) {
2486 my $prodcomp = $1;
2487
2488 # If there is more than one series for the corresponding
2489 # open-bugs series, we pick the one with the most data,
2490 # which should be the one which was generated on creation.
2491 # It's a pity we can't do subselects.
2492 $sth_openbugs_series->execute(
2493 $open_bugs_query_base_old . $prodcomp,
2494 $open_bugs_query_base_new . $prodcomp);
2495
2496 my ($found_open_series_id, $datacount) = (undef, -1);
2497 foreach my $open_ser_id ($sth_openbugs_series->fetchrow_array) {
2498 $sth_data_collected->execute($open_ser_id);
2499 my ($this_datacount) = $sth_data_collected->fetchrow_array;
2500 if ($this_datacount > $datacount) {
2501 $datacount = $this_datacount;
2502 $found_open_series_id = $open_ser_id;
2503 }
2504 }
2505
2506 if ($found_open_series_id) {
2507 # Move along corrupted series data and correct it. The
2508 # corruption consists of it being the number of all bugs
2509 # instead of the number of non-open bugs, so we calculate
2510 # the correct count by subtracting the number of open bugs.
2511 # If there is no corresponding open-bugs count for some
2512 # reason (shouldn't happen), we drop the data entry.
2513 print " $broken_series_id...";
2514 $sth_select_broken_nonopen_data->execute($broken_series_id);
2515 while (my $rowref =
2516 $sth_select_broken_nonopen_data->fetchrow_arrayref)
2517 {
2518 my ($date, $broken_value) = @$rowref;
2519 my ($openbugs_value) =
2520 $dbh->selectrow_array($sth_select_open_data, undef,
2521 $found_open_series_id, $date);
2522 if (defined($openbugs_value)) {
2523 $sth_fix_broken_nonopen_data->execute
2524 ($broken_value - $openbugs_value,
2525 $broken_series_id, $date);
2526 }
2527 else {
2528 print <<EOT;
2529
2530 WARNING - During repairs of series $broken_series_id, the irreparable data
2531 entry for date $date was encountered and is being deleted.
2532
2533 Continuing repairs...
2534 EOT
2535 $sth_delete_broken_nonopen_data->execute
2536 ($broken_series_id, $date);
2537 }
2538 }
2539
2540 # Fix the broken query so that it collects correct data
2541 # in the future.
2542 $nonopen_bugs_query =~
2543 s/^$broken_series_indicator/field0-0-0=resolution&type0-0-0=regexp&value0-0-0=./;
2544 $sth_repair->execute($nonopen_bugs_query,
2545 $broken_series_id);
2546 }
2547 else {
2548 print <<EOT;
2549
2550 WARNING - Series $broken_series_id was meant to collect non-open bug
2551 counts, but it has counted all bugs instead. It cannot be repaired
2552 automatically because no series that collected open bug counts was found.
2553 You'll probably want to delete or repair collected data for
2554 series $broken_series_id manually
2555
2556 Continuing repairs...
2557 EOT
2558 } # if ($found_open_series_id)
2559 } # if ($nonopen_bugs_query =~
2560 } # foreach (@$broken_nonopen_series)
2561 print " done.\n";
2562 } # if (@$broken_nonopen_series)
2563 }
2564
2565 sub _clean_control_characters_from_short_desc {
2566 my $dbh = Bugzilla->dbh;
2567
2568 # Fixup for Bug 101380
2569 # "Newlines, nulls, leading/trailing spaces are getting into summaries"
2570
2571 my $controlchar_bugs =
2572 $dbh->selectall_arrayref("SELECT short_desc, bug_id FROM bugs WHERE " .
2573 $dbh->sql_regexp('short_desc', "'[[:cntrl:]]'"));
2574 if (scalar(@$controlchar_bugs)) {
2575 my $msg = 'Cleaning control characters from bug summaries...';
2576 my $found = 0;
2577 foreach (@$controlchar_bugs) {
2578 my ($short_desc, $bug_id) = @$_;
2579 my $clean_short_desc = clean_text($short_desc);
2580 if ($clean_short_desc ne $short_desc) {
2581 print $msg if !$found;
2582 $found = 1;
2583 print " $bug_id...";
2584 $dbh->do("UPDATE bugs SET short_desc = ? WHERE bug_id = ?",
2585 undef, $clean_short_desc, $bug_id);
2586 }
2587 }
2588 print " done.\n" if $found;
2589 }
2590 }
2591
2592 sub _stop_storing_inactive_flags {
2593 my $dbh = Bugzilla->dbh;
2594 # 2006-03-02 LpSolit@gmail.com - Bug 322285
2595 # Do not store inactive flags in the DB anymore.
2596 if ($dbh->bz_column_info('flags', 'id')->{'TYPE'} eq 'INT3') {
2597 # We first have to remove all existing inactive flags.
2598 if ($dbh->bz_column_info('flags', 'is_active')) {
2599 $dbh->do('DELETE FROM flags WHERE is_active = 0');
2600 }
2601
2602 # Now we convert the id column to the auto_increment format.
2603 $dbh->bz_alter_column('flags', 'id',
2604 {TYPE => 'MEDIUMSERIAL', NOTNULL => 1, PRIMARYKEY => 1});
2605
2606 # And finally, we remove the is_active column.
2607 $dbh->bz_drop_column('flags', 'is_active');
2608 }
2609 }
2610
2611 sub _change_short_desc_from_mediumtext_to_varchar {
2612 my $dbh = Bugzilla->dbh;
2613 # short_desc should not be a mediumtext, fix anything longer than 255 chars.
2614 if($dbh->bz_column_info('bugs', 'short_desc')->{TYPE} eq 'MEDIUMTEXT') {
2615 # Move extremely long summaries into a comment ("from" the Reporter),
2616 # and then truncate the summary.
2617 my $long_summary_bugs = $dbh->selectall_arrayref(
2618 'SELECT bug_id, short_desc, reporter
2619 FROM bugs WHERE CHAR_LENGTH(short_desc) > 255');
2620
2621 if (@$long_summary_bugs) {
2622 print <<EOT;
2623
2624 WARNING: Some of your bugs had summaries longer than 255 characters.
2625 They have had their original summary copied into a comment, and then
2626 the summary was truncated to 255 characters. The affected bug numbers were:
2627 EOT
2628 my $comment_sth = $dbh->prepare(
2629 'INSERT INTO longdescs (bug_id, who, thetext, bug_when)
2630 VALUES (?, ?, ?, NOW())');
2631 my $desc_sth = $dbh->prepare('UPDATE bugs SET short_desc = ?
2632 WHERE bug_id = ?');
2633 my @affected_bugs;
2634 foreach my $bug (@$long_summary_bugs) {
2635 my ($bug_id, $summary, $reporter_id) = @$bug;
2636 my $summary_comment = "The original summary for this bug"
2637 . " was longer than 255 characters, and so it was truncated"
2638 . " when Bugzilla was upgraded. The original summary was:"
2639 . "\n\n$summary";
2640 $comment_sth->execute($bug_id, $reporter_id, $summary_comment);
2641 my $short_summary = substr($summary, 0, 252) . "...";
2642 $desc_sth->execute($short_summary, $bug_id);
2643 push(@affected_bugs, $bug_id);
2644 }
2645 print join(', ', @affected_bugs) . "\n\n";
2646 }
2647
2648 $dbh->bz_alter_column('bugs', 'short_desc', {TYPE => 'varchar(255)',
2649 NOTNULL => 1});
2650 }
2651 }
2652
2653 sub _move_namedqueries_linkinfooter_to_its_own_table {
2654 my $dbh = Bugzilla->dbh;
2655 if ($dbh->bz_column_info("namedqueries", "linkinfooter")) {
2656 # Move link-in-footer information into a table of its own.
2657 my $sth_read = $dbh->prepare('SELECT id, userid
2658 FROM namedqueries
2659 WHERE linkinfooter = 1');
2660 my $sth_write = $dbh->prepare('INSERT INTO namedqueries_link_in_footer
2661 (namedquery_id, user_id) VALUES (?, ?)');
2662 $sth_read->execute();
2663 while (my ($id, $userid) = $sth_read->fetchrow_array()) {
2664 $sth_write->execute($id, $userid);
2665 }
2666 $dbh->bz_drop_column("namedqueries", "linkinfooter");
2667 }
2668 }
2669
2670 sub _add_classifications_sortkey {
2671 my $dbh = Bugzilla->dbh;
2672 # 2006-07-07 olav@bkor.dhs.org - Bug 277377
2673 # Add a sortkey to the classifications
2674 if (!$dbh->bz_column_info('classifications', 'sortkey')) {
2675 $dbh->bz_add_column('classifications', 'sortkey',
2676 {TYPE => 'INT2', NOTNULL => 1, DEFAULT => 0});
2677
2678 my $class_ids = $dbh->selectcol_arrayref(
2679 'SELECT id FROM classifications ORDER BY name');
2680 my $sth = $dbh->prepare('UPDATE classifications SET sortkey = ? ' .
2681 'WHERE id = ?');
2682 my $sortkey = 0;
2683 foreach my $class_id (@$class_ids) {
2684 $sth->execute($sortkey, $class_id);
2685 $sortkey += 100;
2686 }
2687 }
2688 }
2689
2690 sub _move_data_nomail_into_db {
2691 my $dbh = Bugzilla->dbh;
2692 my $datadir = bz_locations()->{'datadir'};
2693 # 2006-07-14 karl@kornel.name - Bug 100953
2694 # If a nomail file exists, move its contents into the DB
2695 $dbh->bz_add_column('profiles', 'disable_mail',
2696 { TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE' });
2697 if (-e "$datadir/nomail") {
2698 # We have a data/nomail file, read it in and delete it
2699 my %nomail;
2700 print "Found a data/nomail file. Moving nomail entries into DB...\n";
2701 my $nomail_file = new IO::File("$datadir/nomail", 'r');
2702 while (<$nomail_file>) {
2703 $nomail{trim($_)} = 1;
2704 }
2705 $nomail_file->close;
2706
2707 # Go through each entry read. If a user exists, set disable_mail.
2708 my $query = $dbh->prepare('UPDATE profiles
2709 SET disable_mail = 1
2710 WHERE userid = ?');
2711 foreach my $user_to_check (keys %nomail) {
2712 my $uid;
2713 if ($uid = Bugzilla::User::login_to_id($user_to_check)) {
2714 my $user = new Bugzilla::User($uid);
2715 print "\tDisabling email for user ", $user->email, "\n";
2716 $query->execute($user->id);
2717 delete $nomail{$user->email};
2718 }
2719 }
2720
2721 # If there are any nomail entries remaining, move them to nomail.bad
2722 # and say something to the user.
2723 if (scalar(keys %nomail)) {
2724 print <<EOT;
2725
2726 WARNING: The following users were listed in data/nomail, but do not
2727 have an account here. The unmatched entries have been moved
2728 to $datadir/nomail.bad:
2729 EOT
2730 my $nomail_bad = new IO::File("$datadir/nomail.bad", '>>');
2731 foreach my $unknown_user (keys %nomail) {
2732 print "\t$unknown_user\n";
2733 print $nomail_bad "$unknown_user\n";
2734 delete $nomail{$unknown_user};
2735 }
2736 $nomail_bad->close;
2737 print "\n";
2738 }
2739
2740 # Now that we don't need it, get rid of the nomail file.
2741 unlink "$datadir/nomail";
2742 }
2743 }
2744
2745 sub _update_longdescs_who_index {
2746 my $dbh = Bugzilla->dbh;
2747 # When doing a search on who posted a comment, longdescs is joined
2748 # against the bugs table. So we need an index on both of these,
2749 # not just on "who".
2750 my $who_index = $dbh->bz_index_info('longdescs', 'longdescs_who_idx');
2751 if (!$who_index || scalar @{$who_index->{FIELDS}} == 1) {
2752 # If the index doesn't exist, this will harmlessly do nothing.
2753 $dbh->bz_drop_index('longdescs', 'longdescs_who_idx');
2754 $dbh->bz_add_index('longdescs', 'longdescs_who_idx', [qw(who bug_id)]);
2755 }
2756 }
2757
2758 sub _fix_uppercase_custom_field_names {
2759 # Before the final release of 3.0, custom fields could be
2760 # created with mixed-case names.
2761 my $dbh = Bugzilla->dbh;
2762 my $fields = $dbh->selectall_arrayref(
2763 'SELECT name, type FROM fielddefs WHERE custom = 1');
2764 foreach my $row (@$fields) {
2765 my ($name, $type) = @$row;
2766 if ($name ne lc($name)) {
2767 $dbh->bz_rename_column('bugs', $name, lc($name));
2768 $dbh->bz_rename_table($name, lc($name))
2769 if $type == FIELD_TYPE_SINGLE_SELECT;
2770 $dbh->do('UPDATE fielddefs SET name = ? WHERE name = ?',
2771 undef, lc($name), $name);
2772 }
2773 }
2774 }
2775
2776 sub _fix_uppercase_index_names {
2777 # We forgot to fix indexes in the above code.
2778 my $dbh = Bugzilla->dbh;
2779 my $fields = $dbh->selectcol_arrayref(
2780 'SELECT name FROM fielddefs WHERE type = ? AND custom = 1',
2781 undef, FIELD_TYPE_SINGLE_SELECT);
2782 foreach my $field (@$fields) {
2783 my $indexes = $dbh->bz_table_indexes($field);
2784 foreach my $name (keys %$indexes) {
2785 next if $name eq lc($name);
2786 my $index = $indexes->{$name};
2787 # Lowercase the name and everything in the definition.
2788 my $new_name = lc($name);
2789 my @new_fields = map {lc($_)} @{$index->{FIELDS}};
2790 my $new_def = {FIELDS => \@new_fields, TYPE => $index->{TYPE}};
2791 $new_def = \@new_fields if !$index->{TYPE};
2792 $dbh->bz_drop_index($field, $name);
2793 $dbh->bz_add_index($field, $new_name, $new_def);
2794 }
2795 }
2796 }
2797
2798 sub _initialize_workflow {
2799 my $old_params = shift;
2800 my $dbh = Bugzilla->dbh;
2801
2802 $dbh->bz_add_column('bug_status', 'is_open',
2803 {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'TRUE'});
2804
2805 # Till now, bug statuses were not customizable. Nevertheless, local
2806 # changes are possible and so we will try to respect these changes.
2807 # This means: get the status of bugs having a resolution different from ''
2808 # and mark these statuses as 'closed', even if some of these statuses are
2809 # expected to be open statuses. Bug statuses we have no information about
2810 # are left as 'open'.
2811 my @closed_statuses =
2812 @{$dbh->selectcol_arrayref('SELECT DISTINCT bug_status FROM bugs
2813 WHERE resolution != ?', undef, '')};
2814
2815 # Append the default list of closed statuses *unless* we detect at least
2816 # one closed state in the DB (i.e. with is_open = 0). This would mean that
2817 # the DB has already been updated at least once and maybe the admin decided
2818 # that e.g. 'RESOLVED' is now an open state, in which case we don't want to
2819 # override this attribute. At least one bug status has to be a closed state
2820 # anyway (due to the 'duplicate_or_move_bug_status' parameter) so it's safe
2821 # to use this criteria.
2822 my $num_closed_states = $dbh->selectrow_array('SELECT COUNT(*) FROM bug_status
2823 WHERE is_open = 0');
2824
2825 if (!$num_closed_states) {
2826 @closed_statuses =
2827 map {$dbh->quote($_)} (@closed_statuses, qw(RESOLVED VERIFIED CLOSED));
2828
2829 print "Marking closed bug statuses as such...\n";
2830 $dbh->do('UPDATE bug_status SET is_open = 0 WHERE value IN (' .
2831 join(', ', @closed_statuses) . ')');
2832 }
2833
2834 # Populate the status_workflow table. We do nothing if the table already
2835 # has entries. If all bug status transitions have been deleted, the
2836 # workflow will be restored to its default schema.
2837 my $count = $dbh->selectrow_array('SELECT COUNT(*) FROM status_workflow');
2838
2839 if (!$count) {
2840 # Make sure the variables below are defined as
2841 # status_workflow.require_comment cannot be NULL.
2842 my $create = $old_params->{'commentoncreate'} || 0;
2843 my $confirm = $old_params->{'commentonconfirm'} || 0;
2844 my $accept = $old_params->{'commentonaccept'} || 0;
2845 my $resolve = $old_params->{'commentonresolve'} || 0;
2846 my $verify = $old_params->{'commentonverify'} || 0;
2847 my $close = $old_params->{'commentonclose'} || 0;
2848 my $reopen = $old_params->{'commentonreopen'} || 0;
2849 # This was till recently the only way to get back to NEW for
2850 # confirmed bugs, so we use this parameter here.
2851 my $reassign = $old_params->{'commentonreassign'} || 0;
2852
2853 # This is the default workflow.
2854 my @workflow = ([undef, 'UNCONFIRMED', $create],
2855 [undef, 'NEW', $create],
2856 [undef, 'ASSIGNED', $create],
2857 ['UNCONFIRMED', 'NEW', $confirm],
2858 ['UNCONFIRMED', 'ASSIGNED', $accept],
2859 ['UNCONFIRMED', 'RESOLVED', $resolve],
2860 ['NEW', 'ASSIGNED', $accept],
2861 ['NEW', 'RESOLVED', $resolve],
2862 ['ASSIGNED', 'NEW', $reassign],
2863 ['ASSIGNED', 'RESOLVED', $resolve],
2864 ['REOPENED', 'NEW', $reassign],
2865 ['REOPENED', 'ASSIGNED', $accept],
2866 ['REOPENED', 'RESOLVED', $resolve],
2867 ['RESOLVED', 'UNCONFIRMED', $reopen],
2868 ['RESOLVED', 'REOPENED', $reopen],
2869 ['RESOLVED', 'VERIFIED', $verify],
2870 ['RESOLVED', 'CLOSED', $close],
2871 ['VERIFIED', 'UNCONFIRMED', $reopen],
2872 ['VERIFIED', 'REOPENED', $reopen],
2873 ['VERIFIED', 'CLOSED', $close],
2874 ['CLOSED', 'UNCONFIRMED', $reopen],
2875 ['CLOSED', 'REOPENED', $reopen]);
2876
2877 print "Now filling the 'status_workflow' table with valid bug status transitions...\n";
2878 my $sth_select = $dbh->prepare('SELECT id FROM bug_status WHERE value = ?');
2879 my $sth = $dbh->prepare('INSERT INTO status_workflow (old_status, new_status,
2880 require_comment) VALUES (?, ?, ?)');
2881
2882 foreach my $transition (@workflow) {
2883 my ($from, $to);
2884 # If it's an initial state, there is no "old" value.
2885 $from = $dbh->selectrow_array($sth_select, undef, $transition->[0])
2886 if $transition->[0];
2887 $to = $dbh->selectrow_array($sth_select, undef, $transition->[1]);
2888 # If one of the bug statuses doesn't exist, the transition is invalid.
2889 next if (($transition->[0] && !$from) || !$to);
2890
2891 $sth->execute($from, $to, $transition->[2] ? 1 : 0);
2892 }
2893 }
2894
2895 # Make sure the bug status used by the 'duplicate_or_move_bug_status'
2896 # parameter has all the required transitions set.
2897 Bugzilla::Status::add_missing_bug_status_transitions();
2898 }
2899
2900 sub _make_lang_setting_dynamic {
2901 my $dbh = Bugzilla->dbh;
2902 my $count = $dbh->selectrow_array(q{SELECT 1 FROM setting
2903 WHERE name = 'lang'
2904 AND subclass IS NULL});
2905 if ($count) {
2906 $dbh->do(q{UPDATE setting SET subclass = 'Lang' WHERE name = 'lang'});
2907 $dbh->do(q{DELETE FROM setting_value WHERE name = 'lang'});
2908 }
2909 }
2910
2911 sub _fix_attachment_modification_date {
2912 my $dbh = Bugzilla->dbh;
2913 if (!$dbh->bz_column_info('attachments', 'modification_time')) {
2914 # Allow NULL values till the modification time has been set.
2915 $dbh->bz_add_column('attachments', 'modification_time', {TYPE => 'DATETIME'});
2916
2917 print "Setting the modification time for attachments...\n";
2918 $dbh->do('UPDATE attachments SET modification_time = creation_ts');
2919
2920 # Now force values to be always defined.
2921 $dbh->bz_alter_column('attachments', 'modification_time',
2922 {TYPE => 'DATETIME', NOTNULL => 1});
2923
2924 # Update the modification time for attachments which have been modified.
2925 my $attachments =
2926 $dbh->selectall_arrayref('SELECT attach_id, MAX(bug_when) FROM bugs_activity
2927 WHERE attach_id IS NOT NULL ' .
2928 $dbh->sql_group_by('attach_id'));
2929
2930 my $sth = $dbh->prepare('UPDATE attachments SET modification_time = ?
2931 WHERE attach_id = ?');
2932 $sth->execute($_->[1], $_->[0]) foreach (@$attachments);
2933 }
2934 # We add this here to be sure to have the index being added, due to the original
2935 # patch omitting it.
2936 $dbh->bz_add_index('attachments', 'attachments_modification_time_idx',
2937 [qw(modification_time)]);
2938 }
2939
2940 sub _change_text_types {
2941 my $dbh = Bugzilla->dbh;
2942 return if
2943 $dbh->bz_column_info('namedqueries', 'query')->{TYPE} eq 'LONGTEXT';
2944 _check_content_length('attachments', 'mimetype', 255, 'attach_id');
2945 _check_content_length('fielddefs', 'description', 255, 'id');
2946 _check_content_length('attachments', 'description', 255, 'attach_id');
2947
2948 $dbh->bz_alter_column('bugs', 'bug_file_loc',
2949 { TYPE => 'MEDIUMTEXT'});
2950 $dbh->bz_alter_column('longdescs', 'thetext',
2951 { TYPE => 'LONGTEXT', NOTNULL => 1 });
2952 $dbh->bz_alter_column('attachments', 'description',
2953 { TYPE => 'TINYTEXT', NOTNULL => 1 });
2954 $dbh->bz_alter_column('attachments', 'mimetype',
2955 { TYPE => 'TINYTEXT', NOTNULL => 1 });
2956 # This also changes NULL to NOT NULL.
2957 $dbh->bz_alter_column('flagtypes', 'description',
2958 { TYPE => 'MEDIUMTEXT', NOTNULL => 1 }, '');
2959 $dbh->bz_alter_column('fielddefs', 'description',
2960 { TYPE => 'TINYTEXT', NOTNULL => 1 });
2961 $dbh->bz_alter_column('groups', 'description',
2962 { TYPE => 'MEDIUMTEXT', NOTNULL => 1 });
2963 $dbh->bz_alter_column('quips', 'quip',
2964 { TYPE => 'MEDIUMTEXT', NOTNULL => 1 });
2965 $dbh->bz_alter_column('namedqueries', 'query',
2966 { TYPE => 'LONGTEXT', NOTNULL => 1 });
2967
2968 }
2969
2970 sub _check_content_length {
2971 my ($table_name, $field_name, $max_length, $id_field) = @_;
2972 my $dbh = Bugzilla->dbh;
2973 my %contents = @{ $dbh->selectcol_arrayref(
2974 "SELECT $id_field, $field_name FROM $table_name
2975 WHERE CHAR_LENGTH($field_name) > ?", {Columns=>[1,2]}, $max_length) };
2976
2977 if (scalar keys %contents) {
2978 print install_string('install_data_too_long',
2979 { column => $field_name,
2980 id_column => $id_field,
2981 table => $table_name,
2982 max_length => $max_length });
2983 foreach my $id (keys %contents) {
2984 my $string = $contents{$id};
2985 # Don't dump the whole string--it could be 16MB.
2986 if (length($string) > 80) {
2987 $string = substr($string, 0, 30) . "..."
2988 . substr($string, -30) . "\n";
2989 }
2990 print "$id: $string\n";
2991 }
2992 exit 3;
2993 }
2994 }
2995
2996 sub _populate_bugs_fulltext {
2997 my $dbh = Bugzilla->dbh;
2998 my $fulltext = $dbh->selectrow_array('SELECT 1 FROM bugs_fulltext '
2999 . $dbh->sql_limit(1));
3000 # We only populate the table if it's empty...
3001 if (!$fulltext) {
3002 # ... and if there are bugs in the bugs table.
3003 my $bug_ids = $dbh->selectcol_arrayref('SELECT bug_id FROM bugs');
3004 return if !@$bug_ids;
3005
3006 # Populating bugs_fulltext can be very slow for large installs,
3007 # so we special-case any DB that supports GROUP_CONCAT, which is
3008 # a much faster way to do things.
3009 if (UNIVERSAL::can($dbh, 'sql_group_concat')) {
3010 print "Populating bugs_fulltext...";
3011 print " (this can take a long time.)\n";
3012 $dbh->do(
3013 q{INSERT INTO bugs_fulltext (bug_id, short_desc, comments,
3014 comments_noprivate)
3015 SELECT bugs.bug_id, bugs.short_desc, }
3016 . $dbh->sql_group_concat('longdescs.thetext', '\'\n\'')
3017 . ', ' . $dbh->sql_group_concat('nopriv.thetext', '\'\n\'') .
3018 q{ FROM bugs
3019 LEFT JOIN longdescs
3020 ON bugs.bug_id = longdescs.bug_id
3021 LEFT JOIN longdescs AS nopriv
3022 ON longdescs.comment_id = nopriv.comment_id
3023 AND nopriv.isprivate = 0 }
3024 . $dbh->sql_group_by('bugs.bug_id', 'bugs.short_desc'));
3025 }
3026 # The slow way, without group_concat.
3027 else {
3028 print "Populating bugs_fulltext.short_desc...\n";
3029 $dbh->do('INSERT INTO bugs_fulltext (bug_id, short_desc)
3030 SELECT bug_id, short_desc FROM bugs');
3031
3032 my $count = 1;
3033 my $sth_all = $dbh->prepare('SELECT thetext FROM longdescs
3034 WHERE bug_id = ?');
3035 my $sth_nopriv = $dbh->prepare(
3036 'SELECT thetext FROM longdescs
3037 WHERE bug_id = ? AND isprivate = 0');
3038 my $sth_update = $dbh->prepare(
3039 'UPDATE bugs_fulltext SET comments = ?, comments_noprivate = ?
3040 WHERE bug_id = ?');
3041
3042 print "Populating bugs_fulltext comment fields...\n";
3043 foreach my $id (@$bug_ids) {
3044 my $all = $dbh->selectcol_arrayref($sth_all, undef, $id);
3045 my $nopriv = $dbh->selectcol_arrayref($sth_nopriv, undef, $id);
3046 $sth_update->execute(join("\n", @$all), join("\n", @$nopriv), $id);
3047 indicate_progress({ total => scalar @$bug_ids, every => 100,
3048 current => $count++ });
3049 }
3050 print "\n";
3051 }
3052 }
3053 }
3054
3055 1;
3056
3057 __END__
3058
3059 =head1 NAME
3060
3061 Bugzilla::Install::DB - Fix up the database during installation.
3062
3063 =head1 SYNOPSIS
3064
3065 use Bugzilla::Install::DB qw(indicate_progress);
3066 Bugzilla::Install::DB::update_table_definitions();
3067
3068 indicate_progress({ total => $total, current => $count, every => 10 });
3069
3070 =head1 DESCRIPTION
3071
3072 This module is used primarily by L<checksetup.pl> to modify the
3073 database during upgrades.
3074
3075 =head1 SUBROUTINES
3076
3077 =over
3078
3079 =item C<update_table_definitions()>
3080
3081 Description: This is the primary code that updates table definitions
3082 during upgrades. If you modify the schema in some
3083 way, you should add code to the end of this function to
3084 make sure that your modifications happen over all installations.
3085
3086 Params: none
3087
3088 Returns: nothing
3089
3090 =item C<update_fielddefs_definition()>
3091
3092 Description: L<checksetup.pl> depends on the fielddefs table having
3093 its schema adjusted before the rest of the tables. So
3094 these schema updates happen in a separate function from
3095 L</update_table_definitions()>.
3096
3097 Params: none
3098
3099 Returns: nothing
3100
3101 =back
Experimental WebKit port