From e11aa06d3418909a579b43bf02bac3f474a9a2ff Mon Sep 17 00:00:00 2001 From: "mkanat%bugzilla.org" <> Date: Thu, 3 Aug 2006 06:39:51 +0000 Subject: Bug 346815: Move most of checksetup's schema updates into Bugzilla::Install::DB Patch By Max Kanat-Alexander (module owner) a=myk --- checksetup.pl | 2111 +-------------------------------------------------------- 1 file changed, 2 insertions(+), 2109 deletions(-) (limited to 'checksetup.pl') diff --git a/checksetup.pl b/checksetup.pl index 7fa0e65fa..ae3cda5e5 100755 --- a/checksetup.pl +++ b/checksetup.pl @@ -326,6 +326,7 @@ import Bugzilla::Install::Localconfig qw(read_localconfig update_localconfig); require Bugzilla::Install::Filesystem; import Bugzilla::Install::Filesystem qw(update_filesystem create_htaccess fix_all_file_permissions); +require Bugzilla::Install::DB; require Bugzilla::DB; require Bugzilla::Template; @@ -691,2071 +692,7 @@ if (!$class_count) { # Update the tables to the current definition --TABLE-- ########################################################################### -# Both legacy code and modern code need this variable. -my @admins = (); - -# really old fields that were added before checksetup.pl existed -# but aren't in very old bugzilla's (like 2.1) -# Steve Stock (sstock@iconnect-inc.com) - -$dbh->bz_add_column('bugs', 'target_milestone', - {TYPE => 'varchar(20)', NOTNULL => 1, DEFAULT => "'---'"}); -$dbh->bz_add_column('bugs', 'qa_contact', {TYPE => 'INT3'}); -$dbh->bz_add_column('bugs', 'status_whiteboard', - {TYPE => 'MEDIUMTEXT', NOTNULL => 1, DEFAULT => "''"}); -$dbh->bz_add_column('products', 'disallownew', - {TYPE => 'BOOLEAN', NOTNULL => 1}, 0); -$dbh->bz_add_column('products', 'milestoneurl', - {TYPE => 'TINYTEXT', NOTNULL => 1}, ''); -$dbh->bz_add_column('components', 'initialqacontact', - {TYPE => 'TINYTEXT'}); -$dbh->bz_add_column('components', 'description', - {TYPE => 'MEDIUMTEXT', NOTNULL => 1}, ''); - -# 1999-06-22 Added an entry to the attachments table to record who the -# submitter was. Nothing uses this yet, but it still should be recorded. -$dbh->bz_add_column('attachments', 'submitter_id', - {TYPE => 'INT3', NOTNULL => 1}, 0); - -# -# One could even populate this field automatically, e.g. with -# -# unless (GetField('attachments', 'submitter_id') { -# $dbh->bz_add_column ... -# populate -# } -# -# For now I was too lazy, so you should read the documentation :-) - - - -# 1999-9-15 Apparently, newer alphas of MySQL won't allow you to have "when" -# as a column name. So, I have had to rename a column in the bugs_activity -# table. - -$dbh->bz_rename_column('bugs_activity', 'when', 'bug_when'); - - - -# 1999-10-11 Restructured voting database to add a cached value in each bug -# recording how many total votes that bug has. While I'm at it, I removed -# the unused "area" field from the bugs database. It is distressing to -# realize that the bugs table has reached the maximum number of indices -# allowed by MySQL (16), which may make future enhancements awkward. -# (P.S. All is not lost; it appears that the latest betas of MySQL support -# a new table format which will allow 32 indices.) - -$dbh->bz_drop_column('bugs', 'area'); -if (!$dbh->bz_column_info('bugs', 'votes')) { - $dbh->bz_add_column('bugs', 'votes', {TYPE => 'INT3', NOTNULL => 1, - DEFAULT => 0}); - $dbh->bz_add_index('bugs', 'bugs_votes_idx', [qw(votes)]); -} -$dbh->bz_add_column('products', 'votesperuser', - {TYPE => 'INT2', NOTNULL => 1}, 0); - - -# The product name used to be very different in various tables. -# -# It was varchar(16) in bugs -# tinytext in components -# tinytext in products -# tinytext in versions -# -# tinytext is equivalent to varchar(255), which is quite huge, so I change -# them all to varchar(64). - -# Only do this if these fields still exist - they're removed below, in -# a later change -if ($dbh->bz_column_info('products', 'product')) { - $dbh->bz_alter_column('bugs', 'product', - {TYPE => 'varchar(64)', NOTNULL => 1}); - $dbh->bz_alter_column('components', 'program', {TYPE => 'varchar(64)'}); - $dbh->bz_alter_column('products', 'product', {TYPE => 'varchar(64)'}); - $dbh->bz_alter_column('versions', 'program', - {TYPE => 'varchar(64)', NOTNULL => 1}); -} - -# 2000-01-16 Added a "keywords" field to the bugs table, which -# contains a string copy of the entries of the keywords table for this -# bug. This is so that I can easily sort and display a keywords -# column in bug lists. - -if (!$dbh->bz_column_info('bugs', 'keywords')) { - $dbh->bz_add_column('bugs', 'keywords', - {TYPE => 'MEDIUMTEXT', NOTNULL => 1, DEFAULT => "''"}); - - my @kwords; - print "Making sure 'keywords' field of table 'bugs' is empty ...\n"; - $dbh->do("UPDATE bugs SET keywords = '' " . - "WHERE keywords != ''"); - print "Repopulating 'keywords' field of table 'bugs' ...\n"; - my $sth = $dbh->prepare("SELECT keywords.bug_id, keyworddefs.name " . - "FROM keywords, keyworddefs " . - "WHERE keyworddefs.id = keywords.keywordid " . - "ORDER BY keywords.bug_id, keyworddefs.name"); - $sth->execute; - my @list; - my $bugid = 0; - my @row; - while (1) { - my ($b, $k) = ($sth->fetchrow_array()); - if (!defined $b || $b ne $bugid) { - if (@list) { - $dbh->do("UPDATE bugs SET keywords = " . - $dbh->quote(join(', ', @list)) . - " WHERE bug_id = $bugid"); - } - if (!$b) { - last; - } - $bugid = $b; - @list = (); - } - push(@list, $k); - } -} - - -# 2000-01-18 Added a "disabledtext" field to the profiles table. If not -# empty, then this account has been disabled, and this field is to contain -# text describing why. -$dbh->bz_add_column('profiles', 'disabledtext', - {TYPE => 'MEDIUMTEXT', NOTNULL => 1}, ''); - - -# 2000-01-20 Added a new "longdescs" table, which is supposed to have all the -# long descriptions in it, replacing the old long_desc field in the bugs -# table. The below hideous code populates this new table with things from -# the old field, with ugly parsing and heuristics. - -sub WriteOneDesc { - my ($id, $who, $when, $buffer) = (@_); - $buffer = trim($buffer); - if ($buffer eq '') { - return; - } - $dbh->do("INSERT INTO longdescs (bug_id, who, bug_when, thetext) VALUES " . - "($id, $who, " . time2str("'%Y/%m/%d %H:%M:%S'", $when) . - ", " . $dbh->quote($buffer) . ")"); -} - - -if ($dbh->bz_column_info('bugs', 'long_desc')) { - eval("use Date::Parse"); - eval("use Date::Format"); - my $sth = $dbh->prepare("SELECT count(*) FROM bugs"); - $sth->execute(); - my ($total) = ($sth->fetchrow_array); - - print "Populating new long_desc table. This is slow. There are $total\n"; - print "bugs to process; a line of dots will be printed for each 50.\n\n"; - $| = 1; - - $dbh->bz_lock_tables('bugs write', 'longdescs write', 'profiles write', - 'bz_schema WRITE'); - - $dbh->do('DELETE FROM longdescs'); - - $sth = $dbh->prepare("SELECT bug_id, creation_ts, reporter, long_desc " . - "FROM bugs ORDER BY bug_id"); - $sth->execute(); - my $count = 0; - while (1) { - my ($id, $createtime, $reporterid, $desc) = ($sth->fetchrow_array()); - if (!$id) { - last; - } - print "."; - $count++; - if ($count % 10 == 0) { - print " "; - if ($count % 50 == 0) { - print "$count/$total (" . int($count * 100 / $total) . "%)\n"; - } - } - $desc =~ s/\r//g; - my $who = $reporterid; - my $when = str2time($createtime); - my $buffer = ""; - foreach my $line (split(/\n/, $desc)) { - $line =~ s/\s+$//g; # Trim trailing whitespace. - if ($line =~ /^------- Additional Comments From ([^\s]+)\s+(\d.+\d)\s+-------$/) { - my $name = $1; - my $date = str2time($2); - $date += 59; # Oy, what a hack. The creation time is - # accurate to the second. But we the long - # text only contains things accurate to the - # minute. And so, if someone makes a comment - # within a minute of the original bug creation, - # then the comment can come *before* the - # bug creation. So, we add 59 seconds to - # the time of all comments, so that they - # are always considered to have happened at - # the *end* of the given minute, not the - # beginning. - if ($date >= $when) { - WriteOneDesc($id, $who, $when, $buffer); - $buffer = ""; - $when = $date; - my $s2 = $dbh->prepare("SELECT userid FROM profiles " . - "WHERE login_name = " . - $dbh->quote($name)); - $s2->execute(); - ($who) = ($s2->fetchrow_array()); - if (!$who) { - # This username doesn't exist. Try a special - # netscape-only hack (sorry about that, but I don't - # think it will hurt any other installations). We - # have many entries in the bugsystem from an ancient - # world where the "@netscape.com" part of the loginname - # was omitted. So, look up the user again with that - # appended, and use it if it's there. - if ($name !~ /\@/) { - my $nsname = $name . "\@netscape.com"; - $s2 = - $dbh->prepare("SELECT userid FROM profiles " . - "WHERE login_name = " . - $dbh->quote($nsname)); - $s2->execute(); - ($who) = ($s2->fetchrow_array()); - } - } - - if (!$who) { - # This username doesn't exist. Maybe someone renamed - # him or something. Invent a new profile entry, - # disabled, just to represent him. - $dbh->do("INSERT INTO profiles " . - "(login_name, cryptpassword," . - " disabledtext) VALUES (" . - $dbh->quote($name) . - ", " . $dbh->quote(bz_crypt('okthen')) . - ", " . - "'Account created only to maintain database integrity')"); - $who = $dbh->bz_last_key('profiles', 'userid'); - } - next; - } else { -# print "\nDecided this line of bug $id has a date of " . -# time2str("'%Y/%m/%d %H:%M:%S'", $date) . -# "\nwhich is less than previous line:\n$line\n\n"; - } - - } - $buffer .= $line . "\n"; - } - WriteOneDesc($id, $who, $when, $buffer); - } - - - print "\n\n"; - - $dbh->bz_drop_column('bugs', 'long_desc'); - - $dbh->bz_unlock_tables(); -} - - -# 2000-01-18 Added a new table fielddefs that records information about the -# different fields we keep an activity log on. The bugs_activity table -# now has a pointer into that table instead of recording the name directly. - -if ($dbh->bz_column_info('bugs_activity', 'field')) { - $dbh->bz_add_column('bugs_activity', 'fieldid', - {TYPE => 'INT3', NOTNULL => 1}, 0); - - $dbh->bz_add_index('bugs_activity', 'bugs_activity_fieldid_idx', - [qw(fieldid)]); - print "Populating new fieldid field ...\n"; - - $dbh->bz_lock_tables('bugs_activity WRITE', 'fielddefs WRITE'); - - my $sth = $dbh->prepare('SELECT DISTINCT field FROM bugs_activity'); - $sth->execute(); - my %ids; - while (my ($f) = ($sth->fetchrow_array())) { - my $q = $dbh->quote($f); - my $s2 = - $dbh->prepare("SELECT id FROM fielddefs WHERE name = $q"); - $s2->execute(); - my ($id) = ($s2->fetchrow_array()); - if (!$id) { - $dbh->do("INSERT INTO fielddefs (name, description) VALUES " . - "($q, $q)"); - $id = $dbh->bz_last_key('fielddefs', 'id'); - } - $dbh->do("UPDATE bugs_activity SET fieldid = $id WHERE field = $q"); - } - $dbh->bz_unlock_tables(); - - $dbh->bz_drop_column('bugs_activity', 'field'); -} - - - -# 2000-01-18 New email-notification scheme uses a new field in the bug to -# record when email notifications were last sent about this bug. Also, -# added 'newemailtech' field to record if user wants to use the experimental -# stuff. -# 2001-04-29 jake@bugzilla.org - The newemailtech field is no longer needed -# http://bugzilla.mozilla.org/show_bugs.cgi?id=71552 - -if (!$dbh->bz_column_info('bugs', 'lastdiffed')) { - $dbh->bz_add_column('bugs', 'lastdiffed', {TYPE =>'DATETIME'}); - $dbh->do('UPDATE bugs SET lastdiffed = now()'); -} - - -# 2000-01-22 The "login_name" field in the "profiles" table was not -# declared to be unique. Sure enough, somehow, I got 22 duplicated entries -# in my database. This code detects that, cleans up the duplicates, and -# then tweaks the table to declare the field to be unique. What a pain. -if (!$dbh->bz_index_info('profiles', 'profiles_login_name_idx') || - !$dbh->bz_index_info('profiles', 'profiles_login_name_idx')->{TYPE}) { - print "Searching for duplicate entries in the profiles table ...\n"; - while (1) { - # This code is weird in that it loops around and keeps doing this - # select again. That's because I'm paranoid about deleting entries - # out from under us in the profiles table. Things get weird if - # there are *three* or more entries for the same user... - $sth = $dbh->prepare("SELECT p1.userid, p2.userid, p1.login_name " . - "FROM profiles AS p1, profiles AS p2 " . - "WHERE p1.userid < p2.userid " . - "AND p1.login_name = p2.login_name " . - "ORDER BY p1.login_name"); - $sth->execute(); - my ($u1, $u2, $n) = ($sth->fetchrow_array); - if (!$u1) { - last; - } - print "Both $u1 & $u2 are ids for $n! Merging $u2 into $u1 ...\n"; - foreach my $i (["bugs", "reporter"], - ["bugs", "assigned_to"], - ["bugs", "qa_contact"], - ["attachments", "submitter_id"], - ["bugs_activity", "who"], - ["cc", "who"], - ["votes", "who"], - ["longdescs", "who"]) { - my ($table, $field) = (@$i); - print " Updating $table.$field ...\n"; - $dbh->do("UPDATE $table SET $field = $u1 " . - "WHERE $field = $u2"); - } - $dbh->do("DELETE FROM profiles WHERE userid = $u2"); - } - print "OK, changing index type to prevent duplicates in the future ...\n"; - - $dbh->bz_drop_index('profiles', 'profiles_login_name_idx'); - $dbh->bz_add_index('profiles', 'profiles_login_name_idx', - {TYPE => 'UNIQUE', FIELDS => [qw(login_name)]}); -} - - -# 2000-01-24 Added a new field to let people control whether the "My -# bugs" link appears at the bottom of each page. Also can control -# whether each named query should show up there. - -$dbh->bz_add_column('profiles', 'mybugslink', {TYPE => 'BOOLEAN', NOTNULL => 1, - DEFAULT => 'TRUE'}); - -my $comp_init_owner = $dbh->bz_column_info('components', 'initialowner'); -if ($comp_init_owner && $comp_init_owner->{TYPE} eq 'TINYTEXT') { - $sth = $dbh->prepare( - "SELECT program, value, initialowner, initialqacontact " . - "FROM components"); - $sth->execute(); - while (my ($program, $value, $initialowner) = $sth->fetchrow_array()) { - $initialowner =~ s/([\\\'])/\\$1/g; $initialowner =~ s/\0/\\0/g; - $program =~ s/([\\\'])/\\$1/g; $program =~ s/\0/\\0/g; - $value =~ s/([\\\'])/\\$1/g; $value =~ s/\0/\\0/g; - - my $s2 = $dbh->prepare("SELECT userid " . - "FROM profiles " . - "WHERE login_name = '$initialowner'"); - $s2->execute(); - - my $initialownerid = $s2->fetchrow_array(); - - unless (defined $initialownerid) { - print "Warning: You have an invalid default assignee '$initialowner'\n" . - "in component '$value' of program '$program'. !\n"; - $initialownerid = 0; - } - - my $update = - "UPDATE components " . - "SET initialowner = $initialownerid " . - "WHERE program = '$program' " . - "AND value = '$value'"; - my $s3 = $dbh->prepare("UPDATE components " . - "SET initialowner = $initialownerid " . - "WHERE program = '$program' " . - "AND value = '$value';"); - $s3->execute(); - } - - $dbh->bz_alter_column('components','initialowner',{TYPE => 'INT3'}); -} - -my $comp_init_qa = $dbh->bz_column_info('components', 'initialqacontact'); -if ($comp_init_qa && $comp_init_qa->{TYPE} eq 'TINYTEXT') { - $sth = $dbh->prepare( - "SELECT program, value, initialqacontact, initialqacontact " . - "FROM components"); - $sth->execute(); - while (my ($program, $value, $initialqacontact) = $sth->fetchrow_array()) { - $initialqacontact =~ s/([\\\'])/\\$1/g; $initialqacontact =~ s/\0/\\0/g; - $program =~ s/([\\\'])/\\$1/g; $program =~ s/\0/\\0/g; - $value =~ s/([\\\'])/\\$1/g; $value =~ s/\0/\\0/g; - - my $s2 = $dbh->prepare("SELECT userid " . - "FROM profiles " . - "WHERE login_name = '$initialqacontact'"); - $s2->execute(); - - my $initialqacontactid = $s2->fetchrow_array(); - - unless (defined $initialqacontactid) { - if ($initialqacontact ne '') { - print "Warning: You have an invalid default QA contact $initialqacontact' in program '$program', component '$value'!\n"; - } - $initialqacontactid = 0; - } - - my $update = "UPDATE components " . - "SET initialqacontact = $initialqacontactid " . - "WHERE program = '$program' AND value = '$value'"; - my $s3 = $dbh->prepare("UPDATE components " . - "SET initialqacontact = $initialqacontactid " . - "WHERE program = '$program' " . - "AND value = '$value';"); - $s3->execute(); - } - - $dbh->bz_alter_column('components','initialqacontact',{TYPE => 'INT3'}); -} - - -if (!$dbh->bz_column_info('bugs', 'everconfirmed')) { - $dbh->bz_add_column('bugs', 'everconfirmed', - {TYPE => 'BOOLEAN', NOTNULL => 1}, 1); -} -$dbh->bz_add_column('products', 'maxvotesperbug', - {TYPE => 'INT2', NOTNULL => 1, DEFAULT => '10000'}); -$dbh->bz_add_column('products', 'votestoconfirm', - {TYPE => 'INT2', NOTNULL => 1}, 0); - -# 2000-03-21 Adding a table for target milestones to -# database - matthew@zeroknowledge.com -# If the milestones table is empty, and we're still back in a Bugzilla -# that has a bugs.product field, that means that we just created -# the milestones table and it needs to be populated. -my $milestones_exist = $dbh->selectrow_array("SELECT 1 FROM milestones"); -if (!$milestones_exist && $dbh->bz_column_info('bugs', 'product')) { - print "Replacing blank milestones...\n"; - - $dbh->do("UPDATE bugs " . - "SET target_milestone = '---' " . - "WHERE target_milestone = ' '"); - - # If we are upgrading from 2.8 or earlier, we will have *created* - # the milestones table with a product_id field, but Bugzilla expects - # it to have a "product" field. So we change the field backward so - # other code can run. The change will be reversed later in checksetup. - if ($dbh->bz_column_info('milestones', 'product_id')) { - # Dropping the column leaves us with a milestones_product_id_idx - # index that is only on the "value" column. We need to drop the - # whole index so that it can be correctly re-created later. - $dbh->bz_drop_index('milestones', 'milestones_product_id_idx'); - $dbh->bz_drop_column('milestones', 'product_id'); - $dbh->bz_add_column('milestones', 'product', - {TYPE => 'varchar(64)', NOTNULL => 1}, ''); - } - - # Populate the milestone table with all existing values in the database - $sth = $dbh->prepare("SELECT DISTINCT target_milestone, product FROM bugs"); - $sth->execute(); - - print "Populating milestones table...\n"; - - my $value; - my $product; - while(($value, $product) = $sth->fetchrow_array()) - { - # check if the value already exists - my $sortkey = substr($value, 1); - if ($sortkey !~ /^\d+$/) { - $sortkey = 0; - } else { - $sortkey *= 10; - } - $value = $dbh->quote($value); - $product = $dbh->quote($product); - my $s2 = $dbh->prepare("SELECT value " . - "FROM milestones " . - "WHERE value = $value " . - "AND product = $product"); - $s2->execute(); - - if(!$s2->fetchrow_array()) - { - $dbh->do("INSERT INTO milestones(value, product, sortkey) VALUES($value, $product, $sortkey)"); - } - } -} - -# 2000-03-22 Changed the default value for target_milestone to be "---" -# (which is still not quite correct, but much better than what it was -# doing), and made the size of the value field in the milestones table match -# the size of the target_milestone field in the bugs table. - -$dbh->bz_alter_column('bugs', 'target_milestone', - {TYPE => 'varchar(20)', NOTNULL => 1, DEFAULT => "'---'"}); -$dbh->bz_alter_column('milestones', 'value', - {TYPE => 'varchar(20)', NOTNULL => 1}); - - -# 2000-03-23 Added a defaultmilestone field to the products table, so that -# we know which milestone to initially assign bugs to. - -if (!$dbh->bz_column_info('products', 'defaultmilestone')) { - $dbh->bz_add_column('products', 'defaultmilestone', - {TYPE => 'varchar(20)', NOTNULL => 1, DEFAULT => "'---'"}); - $sth = $dbh->prepare("SELECT product, defaultmilestone FROM products"); - $sth->execute(); - while (my ($product, $defaultmilestone) = $sth->fetchrow_array()) { - $product = $dbh->quote($product); - $defaultmilestone = $dbh->quote($defaultmilestone); - my $s2 = $dbh->prepare("SELECT value FROM milestones " . - "WHERE value = $defaultmilestone " . - "AND product = $product"); - $s2->execute(); - if (!$s2->fetchrow_array()) { - $dbh->do("INSERT INTO milestones(value, product) " . - "VALUES ($defaultmilestone, $product)"); - } - } -} - -# 2000-03-24 Added unique indexes into the cc and keyword tables. This -# prevents certain database inconsistencies, and, moreover, is required for -# new generalized list code to work. - -if (!$dbh->bz_index_info('cc', 'cc_bug_id_idx')->{TYPE}) { - - # XXX should eliminate duplicate entries before altering - # - $dbh->bz_drop_index('cc', 'cc_bug_id_idx'); - $dbh->bz_add_index('cc', 'cc_bug_id_idx', - {TYPE => 'UNIQUE', FIELDS => [qw(bug_id who)]}); -} - -if (!$dbh->bz_index_info('keywords', 'keywords_bug_id_idx')->{TYPE}) { - - # XXX should eliminate duplicate entries before altering - # - $dbh->bz_drop_index('keywords', 'keywords_bug_id_idx'); - $dbh->bz_add_index('keywords', 'keywords_bug_id_idx', - {TYPE => 'UNIQUE', FIELDS => [qw(bug_id keywordid)]}); -} - -# 2000-07-15 Added duplicates table so Bugzilla tracks duplicates in a better -# way than it used to. This code searches the comments to populate the table -# initially. It's executed if the table is empty; if it's empty because there -# are no dupes (as opposed to having just created the table) it won't have -# any effect anyway, so it doesn't matter. -$sth = $dbh->prepare("SELECT count(*) from duplicates"); -$sth->execute(); -if (!($sth->fetchrow_arrayref()->[0])) { - # populate table - print("Populating duplicates table...\n") unless $silent; - - $sth = $dbh->prepare( - "SELECT longdescs.bug_id, thetext " . - "FROM longdescs " . - "LEFT JOIN bugs ON longdescs.bug_id = bugs.bug_id " . - "WHERE (" . $dbh->sql_regexp("thetext", - "'[.*.]{3} This bug has been marked as a duplicate of [[:digit:]]+ [.*.]{3}'") . ") " . - "AND (resolution = 'DUPLICATE') " . - "ORDER BY longdescs.bug_when"); - $sth->execute(); - - my %dupes; - my $key; - - # Because of the way hashes work, this loop removes all but the last dupe - # resolution found for a given bug. - while (my ($dupe, $dupe_of) = $sth->fetchrow_array()) { - $dupes{$dupe} = $dupe_of; - } - - foreach $key (keys(%dupes)){ - $dupes{$key} =~ /^.*\*\*\* This bug has been marked as a duplicate of (\d+) \*\*\*$/ms; - $dupes{$key} = $1; - $dbh->do("INSERT INTO duplicates VALUES('$dupes{$key}', '$key')"); - # BugItsADupeOf Dupe - } -} - -# 2000-12-18. Added an 'emailflags' field for storing preferences about -# when email gets sent on a per-user basis. -if (!$dbh->bz_column_info('profiles', 'emailflags') && - !$dbh->bz_column_info('email_setting', 'user_id')) { - $dbh->bz_add_column('profiles', 'emailflags', {TYPE => 'MEDIUMTEXT'}); -} - -# 2000-11-27 For Bugzilla 2.5 and later. Copy data from 'comments' to -# 'longdescs' - the new name of the comments table. -if ($dbh->bz_table_info('comments')) { - my $quoted_when = $dbh->quote_identifier('when'); - # This is MySQL-specific syntax, but that's OK because it will only - # ever run on MySQL. - $dbh->do("INSERT INTO longdescs (bug_when, bug_id, who, thetext) - SELECT $quoted_when, bug_id, who, comment - FROM comments"); - $dbh->bz_drop_table("comments"); -} - -# -# 2001-04-10 myk@mozilla.org: -# isactive determines whether or not a group is active. An inactive group -# cannot have bugs added to it. Deactivation is a much milder form of -# deleting a group that allows users to continue to work on bugs in the group -# without enabling them to extend the life of the group by adding bugs to it. -# http://bugzilla.mozilla.org/show_bug.cgi?id=75482 -# -$dbh->bz_add_column('groups', 'isactive', - {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'TRUE'}); - -# -# 2001-06-15 myk@mozilla.org: -# isobsolete determines whether or not an attachment is pertinent/relevant/valid. -# -$dbh->bz_add_column('attachments', 'isobsolete', - {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'}); - -# 2001-04-29 jake@bugzilla.org - Remove oldemailtech -# http://bugzilla.mozilla.org/show_bugs.cgi?id=71552 -if (-d 'shadow') { - print "Removing shadow directory...\n"; - unlink glob("shadow/*"); - unlink glob("shadow/.*"); - rmdir "shadow"; -} -$dbh->bz_drop_column("profiles", "emailnotification"); -$dbh->bz_drop_column("profiles", "newemailtech"); - - -# 2003-11-19; chicks@chicks.net; bug 225973: fix field size to accommodate -# wider algorithms such as Blowfish. Note that this needs to be run -# before recrypting passwords in the following block. -$dbh->bz_alter_column('profiles', 'cryptpassword', {TYPE => 'varchar(128)'}); - -# 2001-06-12; myk@mozilla.org; bugs 74032, 77473: -# Recrypt passwords using Perl &crypt instead of the mysql equivalent -# and delete plaintext passwords from the database. -if ($dbh->bz_column_info('profiles', 'password')) { - - print <prepare("SELECT userid, password FROM profiles"); - $sth->execute(); - - my $i = 1; - - print "Fixing password #1... "; - while (my ($userid, $password) = $sth->fetchrow_array()) { - my $cryptpassword = $dbh->quote(bz_crypt($password)); - $dbh->do("UPDATE profiles " . - "SET cryptpassword = $cryptpassword " . - "WHERE userid = $userid"); - ++$i; - # Let the user know where we are at every 500 records. - print "$i... " if !($i%500); - } - print "$i... Done.\n"; - - # Drop the plaintext password field. - $dbh->bz_drop_column('profiles', 'password'); -} - -# -# 2001-06-06 justdave@syndicomm.com: -# There was no index on the 'who' column in the long descriptions table. -# This caused queries by who posted comments to take a LONG time. -# http://bugzilla.mozilla.org/show_bug.cgi?id=57350 -$dbh->bz_add_index('longdescs', 'longdescs_who_idx', [qw(who)]); - -# 2001-06-15 kiko@async.com.br - Change bug:version size to avoid -# truncates re http://bugzilla.mozilla.org/show_bug.cgi?id=9352 -$dbh->bz_alter_column('bugs', 'version', - {TYPE => 'varchar(64)', NOTNULL => 1}); - -# 2001-07-20 jake@bugzilla.org - Change bugs_activity to only record changes -# http://bugzilla.mozilla.org/show_bug.cgi?id=55161 -if ($dbh->bz_column_info('bugs_activity', 'oldvalue')) { - $dbh->bz_add_column("bugs_activity", "removed", {TYPE => "TINYTEXT"}); - $dbh->bz_add_column("bugs_activity", "added", {TYPE => "TINYTEXT"}); - - # Need to get field id's for the fields that have multiple values - my @multi = (); - foreach my $f ("cc", "dependson", "blocked", "keywords") { - my $sth = $dbh->prepare("SELECT id " . - "FROM fielddefs " . - "WHERE name = '$f'"); - $sth->execute(); - my ($fid) = $sth->fetchrow_array(); - push (@multi, $fid); - } - - # Now we need to process the bugs_activity table and reformat the data - my $i = 0; - print "Fixing activity log "; - my $sth = $dbh->prepare("SELECT bug_id, who, bug_when, fieldid, - oldvalue, newvalue FROM bugs_activity"); - $sth->execute; - while (my ($bug_id, $who, $bug_when, $fieldid, $oldvalue, $newvalue) = $sth->fetchrow_array()) { - # print the iteration count every 500 records - # so the user knows we didn't die - print "$i..." if !($i++ % 500); - # Make sure (old|new)value isn't null (to suppress warnings) - $oldvalue ||= ""; - $newvalue ||= ""; - my ($added, $removed) = ""; - if (grep ($_ eq $fieldid, @multi)) { - $oldvalue =~ s/[\s,]+/ /g; - $newvalue =~ s/[\s,]+/ /g; - my @old = split(" ", $oldvalue); - my @new = split(" ", $newvalue); - my (@add, @remove) = (); - # Find values that were "added" - foreach my $value(@new) { - if (! grep ($_ eq $value, @old)) { - push (@add, $value); - } - } - # Find values that were removed - foreach my $value(@old) { - if (! grep ($_ eq $value, @new)) { - push (@remove, $value); - } - } - $added = join (", ", @add); - $removed = join (", ", @remove); - # If we can't determine what changed, put a ? in both fields - unless ($added || $removed) { - $added = "?"; - $removed = "?"; - } - # If the original field (old|new)value was full, then this - # could be incomplete data. - if (length($oldvalue) == 255 || length($newvalue) == 255) { - $added = "? $added"; - $removed = "? $removed"; - } - } else { - $removed = $oldvalue; - $added = $newvalue; - } - $added = $dbh->quote($added); - $removed = $dbh->quote($removed); - $dbh->do("UPDATE bugs_activity SET removed = $removed, added = $added - WHERE bug_id = $bug_id AND who = $who - AND bug_when = '$bug_when' AND fieldid = $fieldid"); - } - print ". Done.\n"; - $dbh->bz_drop_column("bugs_activity", "oldvalue"); - $dbh->bz_drop_column("bugs_activity", "newvalue"); -} - -$dbh->bz_alter_column("profiles", "disabledtext", - {TYPE => 'MEDIUMTEXT', NOTNULL => 1}, ''); - -# 2001-07-26 myk@mozilla.org bug 39816 (original) -# 2002-02-06 bbaetz@student.usyd.edu.au bug 97471 (revision) -# Add fields to the bugs table that record whether or not the reporter -# and users on the cc: list can see bugs even when -# they are not members of groups to which the bugs are restricted. -$dbh->bz_add_column("bugs", "reporter_accessible", - {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'TRUE'}); -$dbh->bz_add_column("bugs", "cclist_accessible", - {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'TRUE'}); - -# 2001-08-21 myk@mozilla.org bug84338: -# Add a field to the bugs_activity table for the attachment ID, so installations -# using the attachment manager can record changes to attachments. -$dbh->bz_add_column("bugs_activity", "attach_id", {TYPE => 'INT3'}); - -# 2002-02-04 bbaetz@student.usyd.edu.au bug 95732 -# Remove logincookies.cryptpassword, and delete entries which become -# invalid -if ($dbh->bz_column_info("logincookies", "cryptpassword")) { - # We need to delete any cookies which are invalid before dropping the - # column - - print "Removing invalid login cookies...\n"; - - # mysql doesn't support DELETE with multi-table queries, so we have - # to iterate - my $sth = $dbh->prepare("SELECT cookie FROM logincookies, profiles " . - "WHERE logincookies.cryptpassword != " . - "profiles.cryptpassword AND " . - "logincookies.userid = profiles.userid"); - $sth->execute(); - while (my ($cookie) = $sth->fetchrow_array()) { - $dbh->do("DELETE FROM logincookies WHERE cookie = $cookie"); - } - - $dbh->bz_drop_column("logincookies", "cryptpassword"); -} - -# 2002-02-13 bbaetz@student.usyd.edu.au - bug 97471 -# qacontact/assignee should always be able to see bugs, -# so remove their restriction column -if ($dbh->bz_column_info("bugs", "qacontact_accessible")) { - print "Removing restrictions on bugs for assignee and qacontact...\n"; - - $dbh->bz_drop_column("bugs", "qacontact_accessible"); - $dbh->bz_drop_column("bugs", "assignee_accessible"); -} - -# 2002-02-20 jeff.hedlund@matrixsi.com - bug 24789 time tracking -$dbh->bz_add_column("longdescs", "work_time", - {TYPE => 'decimal(5,2)', NOTNULL => 1, DEFAULT => '0'}); -$dbh->bz_add_column("bugs", "estimated_time", - {TYPE => 'decimal(5,2)', NOTNULL => 1, DEFAULT => '0'}); -$dbh->bz_add_column("bugs", "remaining_time", - {TYPE => 'decimal(5,2)', NOTNULL => 1, DEFAULT => '0'}); -$dbh->bz_add_column("bugs", "deadline", {TYPE => 'DATETIME'}); - -# 2002-03-15 bbaetz@student.usyd.edu.au - bug 129466 -# 2002-05-13 preed@sigkill.com - bug 129446 patch backported to the -# BUGZILLA-2_14_1-BRANCH as a security blocker for the 2.14.2 release -# -# Use the ip, not the hostname, in the logincookies table -if ($dbh->bz_column_info("logincookies", "hostname")) { - # We've changed what we match against, so all entries are now invalid - $dbh->do("DELETE FROM logincookies"); - - # Now update the logincookies schema - $dbh->bz_drop_column("logincookies", "hostname"); - $dbh->bz_add_column("logincookies", "ipaddr", - {TYPE => 'varchar(40)', NOTNULL => 1}, ''); -} - -# 2002-08-19 - bugreport@peshkin.net bug 143826 -# Add private comments and private attachments on less-private bugs -$dbh->bz_add_column('longdescs', 'isprivate', - {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'}); -$dbh->bz_add_column('attachments', 'isprivate', - {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'}); - - -# 2002-07-03 myk@mozilla.org bug99203: -# Add a bug alias field to the bugs table so bugs can be referenced by alias -# in addition to ID. -if (!$dbh->bz_column_info("bugs", "alias")) { - $dbh->bz_add_column("bugs", "alias", {TYPE => "varchar(20)"}); - $dbh->bz_add_index('bugs', 'bugs_alias_idx', - {TYPE => 'UNIQUE', FIELDS => [qw(alias)]}); -} - -# 2002-07-15 davef@tetsubo.com - bug 67950 -# Move quips to the db. -if (-r "$datadir/comments" && -s "$datadir/comments" - && open (COMMENTS, "<$datadir/comments")) { - print "Populating quips table from $datadir/comments...\n\n"; - while () { - chomp; - $dbh->do("INSERT INTO quips (quip) VALUES (" - . $dbh->quote($_) . ")"); - } - print "Quips are now stored in the database, rather than in an external file.\n" . - "The quips previously stored in $datadir/comments have been copied into\n" . - "the database, and that file has been renamed to $datadir/comments.bak\n" . - "You may delete the renamed file once you have confirmed that all your \n" . - "quips were moved successfully.\n\n"; - close COMMENTS; - rename("$datadir/comments", "$datadir/comments.bak"); -} - -# 2002-07-31 bbaetz@student.usyd.edu.au bug 158236 -# Remove unused column -if ($dbh->bz_column_info("namedqueries", "watchfordiffs")) { - $dbh->bz_drop_column("namedqueries", "watchfordiffs"); -} - -# 2002-08-12 jake@bugzilla.org/bbaetz@student.usyd.edu.au - bug 43600 -# Use integer IDs for products and components. -if ($dbh->bz_column_info("products", "product")) { - print "Updating database to use product IDs.\n"; - - # First, we need to remove possible NULL entries - # NULLs may exist, but won't have been used, since all the uses of them - # are in NOT NULL fields in other tables - $dbh->do("DELETE FROM products WHERE product IS NULL"); - $dbh->do("DELETE FROM components WHERE value IS NULL"); - - $dbh->bz_add_column("products", "id", - {TYPE => 'SMALLSERIAL', NOTNULL => 1, PRIMARYKEY => 1}); - $dbh->bz_add_column("components", "product_id", - {TYPE => 'INT2', NOTNULL => 1}, 0); - $dbh->bz_add_column("versions", "product_id", - {TYPE => 'INT2', NOTNULL => 1}, 0); - $dbh->bz_add_column("milestones", "product_id", - {TYPE => 'INT2', NOTNULL => 1}, 0); - $dbh->bz_add_column("bugs", "product_id", - {TYPE => 'INT2', NOTNULL => 1}, 0); - - # The attachstatusdefs table was added in version 2.15, but removed again - # in early 2.17. If it exists now, we still need to perform this change - # with product_id because the code further down which converts the - # attachment statuses to flags depends on it. But we need to avoid this - # if the user is upgrading from 2.14 or earlier (because it won't be - # there to convert). - if ($dbh->bz_table_info("attachstatusdefs")) { - $dbh->bz_add_column("attachstatusdefs", "product_id", - {TYPE => 'INT2', NOTNULL => 1}, 0); - } - - my %products; - my $sth = $dbh->prepare("SELECT id, product FROM products"); - $sth->execute; - while (my ($product_id, $product) = $sth->fetchrow_array()) { - if (exists $products{$product}) { - print "Ignoring duplicate product $product\n"; - $dbh->do("DELETE FROM products WHERE id = $product_id"); - next; - } - $products{$product} = 1; - $dbh->do("UPDATE components SET product_id = $product_id " . - "WHERE program = " . $dbh->quote($product)); - $dbh->do("UPDATE versions SET product_id = $product_id " . - "WHERE program = " . $dbh->quote($product)); - $dbh->do("UPDATE milestones SET product_id = $product_id " . - "WHERE product = " . $dbh->quote($product)); - $dbh->do("UPDATE bugs SET product_id = $product_id " . - "WHERE product = " . $dbh->quote($product)); - $dbh->do("UPDATE attachstatusdefs SET product_id = $product_id " . - "WHERE product = " . $dbh->quote($product)) - if $dbh->bz_table_info("attachstatusdefs"); - } - - print "Updating the database to use component IDs.\n"; - $dbh->bz_add_column("components", "id", - {TYPE => 'SMALLSERIAL', NOTNULL => 1, PRIMARYKEY => 1}); - $dbh->bz_add_column("bugs", "component_id", - {TYPE => 'INT2', NOTNULL => 1}, 0); - - my %components; - $sth = $dbh->prepare("SELECT id, value, product_id FROM components"); - $sth->execute; - while (my ($component_id, $component, $product_id) = $sth->fetchrow_array()) { - if (exists $components{$component}) { - if (exists $components{$component}{$product_id}) { - print "Ignoring duplicate component $component for product $product_id\n"; - $dbh->do("DELETE FROM components WHERE id = $component_id"); - next; - } - } else { - $components{$component} = {}; - } - $components{$component}{$product_id} = 1; - $dbh->do("UPDATE bugs SET component_id = $component_id " . - "WHERE component = " . $dbh->quote($component) . - " AND product_id = $product_id"); - } - print "Fixing Indexes and Uniqueness.\n"; - $dbh->bz_drop_index('milestones', 'milestones_product_idx'); - - $dbh->bz_add_index('milestones', 'milestones_product_id_idx', - {TYPE => 'UNIQUE', FIELDS => [qw(product_id value)]}); - - $dbh->bz_drop_index('bugs', 'bugs_product_idx'); - $dbh->bz_add_index('bugs', 'bugs_product_id_idx', [qw(product_id)]); - $dbh->bz_drop_index('bugs', 'bugs_component_idx'); - $dbh->bz_add_index('bugs', 'bugs_component_id_idx', [qw(component_id)]); - - print "Removing, renaming, and retyping old product and component fields.\n"; - $dbh->bz_drop_column("components", "program"); - $dbh->bz_drop_column("versions", "program"); - $dbh->bz_drop_column("milestones", "product"); - $dbh->bz_drop_column("bugs", "product"); - $dbh->bz_drop_column("bugs", "component"); - $dbh->bz_drop_column("attachstatusdefs", "product") - if $dbh->bz_table_info("attachstatusdefs"); - $dbh->bz_rename_column("products", "product", "name"); - $dbh->bz_alter_column("products", "name", - {TYPE => 'varchar(64)', NOTNULL => 1}); - $dbh->bz_rename_column("components", "value", "name"); - $dbh->bz_alter_column("components", "name", - {TYPE => 'varchar(64)', NOTNULL => 1}); - - print "Adding indexes for products and components tables.\n"; - $dbh->bz_add_index('products', 'products_name_idx', - {TYPE => 'UNIQUE', FIELDS => [qw(name)]}); - $dbh->bz_add_index('components', 'components_product_id_idx', - {TYPE => 'UNIQUE', FIELDS => [qw(product_id name)]}); - $dbh->bz_add_index('components', 'components_name_idx', [qw(name)]); -} - -# 2002-09-22 - bugreport@peshkin.net - bug 157756 -# -# If the whole groups system is new, but the installation isn't, -# convert all the old groupset groups, etc... -# -# This requires: -# 1) define groups ids in group table -# 2) populate user_group_map with grants from old groupsets and blessgroupsets -# 3) populate bug_group_map with data converted from old bug groupsets -# 4) convert activity logs to use group names instead of numbers -# 5) identify the admin from the old all-ones groupset -# -# ListBits(arg) returns a list of UNKNOWN if the group -# has been deleted for all bits set in arg. When the activity -# records are converted from groupset numbers to lists of -# group names, ListBits is used to fill in a list of references -# to groupset bits for groups that no longer exist. -# -sub ListBits { - my ($num) = @_; - my @res = (); - my $curr = 1; - while (1) { - # Convert a big integer to a list of bits - my $sth = $dbh->prepare("SELECT ($num & ~$curr) > 0, - ($num & $curr), - ($num & ~$curr), - $curr << 1"); - $sth->execute; - my ($more, $thisbit, $remain, $nval) = $sth->fetchrow_array; - push @res,"UNKNOWN<$curr>" if ($thisbit); - $curr = $nval; - $num = $remain; - last if (!$more); - } - return @res; -} - -# The groups system needs to be converted if groupset exists -if ($dbh->bz_column_info("profiles", "groupset")) { - $dbh->bz_add_column('groups', 'last_changed', - {TYPE => 'DATETIME', NOTNULL => 1}, '0000-00-00 00:00:00'); - - # Some mysql versions will promote any unique key to primary key - # so all unique keys are removed first and then added back in - $dbh->bz_drop_index('groups', 'groups_bit_idx'); - $dbh->bz_drop_index('groups', 'groups_name_idx'); - if ($dbh->primary_key(undef, undef, 'groups')) { - $dbh->do("ALTER TABLE groups DROP PRIMARY KEY"); - } - - $dbh->bz_add_column('groups', 'id', - {TYPE => 'MEDIUMSERIAL', NOTNULL => 1, PRIMARYKEY => 1}); - - $dbh->bz_add_index('groups', 'groups_name_idx', - {TYPE => 'UNIQUE', FIELDS => [qw(name)]}); - $dbh->bz_add_column('profiles', 'refreshed_when', - {TYPE => 'DATETIME', NOTNULL => 1}, '0000-00-00 00:00:00'); - - # Convert all existing groupset records to map entries before removing - # groupset fields or removing "bit" from groups. - $sth = $dbh->prepare("SELECT bit, id FROM groups - WHERE bit > 0"); - $sth->execute(); - while (my ($bit, $gid) = $sth->fetchrow_array) { - # Create user_group_map membership grants for old groupsets. - # Get each user with the old groupset bit set - my $sth2 = $dbh->prepare("SELECT userid FROM profiles - WHERE (groupset & $bit) != 0"); - $sth2->execute(); - while (my ($uid) = $sth2->fetchrow_array) { - # Check to see if the user is already a member of the group - # and, if not, insert a new record. - my $query = "SELECT user_id FROM user_group_map - WHERE group_id = $gid AND user_id = $uid - AND isbless = 0"; - my $sth3 = $dbh->prepare($query); - $sth3->execute(); - if ( !$sth3->fetchrow_array() ) { - $dbh->do("INSERT INTO user_group_map - (user_id, group_id, isbless, grant_type) - VALUES($uid, $gid, 0, " . GRANT_DIRECT . ")"); - } - } - # Create user can bless group grants for old groupsets, but only - # if we're upgrading from a Bugzilla that had blessing. - if($dbh->bz_column_info('profiles', 'blessgroupset')) { - # Get each user with the old blessgroupset bit set - $sth2 = $dbh->prepare("SELECT userid FROM profiles - WHERE (blessgroupset & $bit) != 0"); - $sth2->execute(); - while (my ($uid) = $sth2->fetchrow_array) { - $dbh->do("INSERT INTO user_group_map - (user_id, group_id, isbless, grant_type) - VALUES($uid, $gid, 1, " . GRANT_DIRECT . ")"); - } - } - # Create bug_group_map records for old groupsets. - # Get each bug with the old group bit set. - $sth2 = $dbh->prepare("SELECT bug_id FROM bugs - WHERE (groupset & $bit) != 0"); - $sth2->execute(); - while (my ($bug_id) = $sth2->fetchrow_array) { - # Insert the bug, group pair into the bug_group_map. - $dbh->do("INSERT INTO bug_group_map - (bug_id, group_id) - VALUES($bug_id, $gid)"); - } - } - # Replace old activity log groupset records with lists of names of groups. - $sth = $dbh->prepare("SELECT id " . - "FROM fielddefs " . - "WHERE name = " . $dbh->quote('bug_group')); - $sth->execute(); - my ($bgfid) = $sth->fetchrow_array; - # Get the field id for the old groupset field - $sth = $dbh->prepare("SELECT id " . - "FROM fielddefs " . - "WHERE name = " . $dbh->quote('groupset')); - $sth->execute(); - my ($gsid) = $sth->fetchrow_array; - # Get all bugs_activity records from groupset changes - if ($gsid) { - $sth = $dbh->prepare("SELECT bug_id, bug_when, who, added, removed - FROM bugs_activity WHERE fieldid = $gsid"); - $sth->execute(); - while (my ($bug_id, $bug_when, $who, $added, $removed) = $sth->fetchrow_array) { - $added ||= 0; - $removed ||= 0; - # Get names of groups added. - my $sth2 = $dbh->prepare("SELECT name " . - "FROM groups " . - "WHERE (bit & $added) != 0 " . - "AND (bit & $removed) = 0"); - $sth2->execute(); - my @logadd = (); - while (my ($n) = $sth2->fetchrow_array) { - push @logadd, $n; - } - # Get names of groups removed. - $sth2 = $dbh->prepare("SELECT name " . - "FROM groups " . - "WHERE (bit & $removed) != 0 " . - "AND (bit & $added) = 0"); - $sth2->execute(); - my @logrem = (); - while (my ($n) = $sth2->fetchrow_array) { - push @logrem, $n; - } - # Get list of group bits added that correspond to missing groups. - $sth2 = $dbh->prepare("SELECT ($added & ~BIT_OR(bit)) FROM groups"); - $sth2->execute(); - my ($miss) = $sth2->fetchrow_array; - if ($miss) { - push @logadd, ListBits($miss); - print "\nWARNING - GROUPSET ACTIVITY ON BUG $bug_id CONTAINS DELETED GROUPS\n"; - } - # Get list of group bits deleted that correspond to missing groups. - $sth2 = $dbh->prepare("SELECT ($removed & ~BIT_OR(bit)) FROM groups"); - $sth2->execute(); - ($miss) = $sth2->fetchrow_array; - if ($miss) { - push @logrem, ListBits($miss); - print "\nWARNING - GROUPSET ACTIVITY ON BUG $bug_id CONTAINS DELETED GROUPS\n"; - } - my $logr = ""; - my $loga = ""; - $logr = join(", ", @logrem) . '?' if @logrem; - $loga = join(", ", @logadd) . '?' if @logadd; - # Replace to old activity record with the converted data. - $dbh->do("UPDATE bugs_activity SET fieldid = $bgfid, added = " . - $dbh->quote($loga) . ", removed = " . - $dbh->quote($logr) . - " WHERE bug_id = $bug_id AND bug_when = " . - $dbh->quote($bug_when) . - " AND who = $who AND fieldid = $gsid"); - - } - # Replace groupset changes with group name changes in profiles_activity. - # Get profiles_activity records for groupset. - $sth = $dbh->prepare( - "SELECT userid, profiles_when, who, newvalue, oldvalue " . - "FROM profiles_activity " . - "WHERE fieldid = $gsid"); - $sth->execute(); - while (my ($uid, $uwhen, $uwho, $added, $removed) = $sth->fetchrow_array) { - $added ||= 0; - $removed ||= 0; - # Get names of groups added. - my $sth2 = $dbh->prepare("SELECT name " . - "FROM groups " . - "WHERE (bit & $added) != 0 " . - "AND (bit & $removed) = 0"); - $sth2->execute(); - my @logadd = (); - while (my ($n) = $sth2->fetchrow_array) { - push @logadd, $n; - } - # Get names of groups removed. - $sth2 = $dbh->prepare("SELECT name " . - "FROM groups " . - "WHERE (bit & $removed) != 0 " . - "AND (bit & $added) = 0"); - $sth2->execute(); - my @logrem = (); - while (my ($n) = $sth2->fetchrow_array) { - push @logrem, $n; - } - my $ladd = ""; - my $lrem = ""; - $ladd = join(", ", @logadd) . '?' if @logadd; - $lrem = join(", ", @logrem) . '?' if @logrem; - # Replace profiles_activity record for groupset change - # with group list. - $dbh->do("UPDATE profiles_activity " . - "SET fieldid = $bgfid, newvalue = " . - $dbh->quote($ladd) . ", oldvalue = " . - $dbh->quote($lrem) . - " WHERE userid = $uid AND profiles_when = " . - $dbh->quote($uwhen) . - " AND who = $uwho AND fieldid = $gsid"); - - } - } - # Identify admin group. - my $sth = $dbh->prepare("SELECT id FROM groups - WHERE name = 'admin'"); - $sth->execute(); - my ($adminid) = $sth->fetchrow_array(); - # find existing admins - # Don't lose admins from DBs where Bug 157704 applies - $sth = $dbh->prepare( - "SELECT userid, (groupset & 65536), login_name " . - "FROM profiles " . - "WHERE (groupset | 65536) = 9223372036854775807"); - $sth->execute(); - while ( my ($userid, $iscomplete, $login_name) = $sth->fetchrow_array() ) { - # existing administrators are made members of group "admin" - print "\nWARNING - $login_name IS AN ADMIN IN SPITE OF BUG 157704\n\n" - if (!$iscomplete); - push @admins, $userid; - } - $dbh->bz_drop_column('profiles','groupset'); - $dbh->bz_drop_column('profiles','blessgroupset'); - $dbh->bz_drop_column('bugs','groupset'); - $dbh->bz_drop_column('groups','bit'); - $dbh->do("DELETE FROM fielddefs WHERE name = " . $dbh->quote('groupset')); -} - -# September 2002 myk@mozilla.org bug 98801 -# Convert the attachment statuses tables into flags tables. -if ($dbh->bz_table_info("attachstatuses") - && $dbh->bz_table_info("attachstatusdefs")) -{ - print "Converting attachment statuses to flags...\n"; - - # Get IDs for the old attachment status and new flag fields. - my ($old_field_id) = $dbh->selectrow_array( - "SELECT id FROM fielddefs WHERE name='attachstatusdefs.name'") - || 0; - - $sth = $dbh->prepare("SELECT id FROM fielddefs " . - "WHERE name='flagtypes.name'"); - $sth->execute(); - my $new_field_id = $sth->fetchrow_arrayref()->[0]; - - # Convert attachment status definitions to flag types. If more than one - # status has the same name and description, it is merged into a single - # status with multiple inclusion records. - $sth = $dbh->prepare("SELECT id, name, description, sortkey, product_id " . - "FROM attachstatusdefs"); - - # status definition IDs indexed by name/description - my $def_ids = {}; - - # merged IDs and the IDs they were merged into. The key is the old ID, - # the value is the new one. This allows us to give statuses the right - # ID when we convert them over to flags. This map includes IDs that - # weren't merged (in this case the old and new IDs are the same), since - # it makes the code simpler. - my $def_id_map = {}; - - $sth->execute(); - while (my ($id, $name, $desc, $sortkey, $prod_id) = $sth->fetchrow_array()) { - my $key = $name . $desc; - if (!$def_ids->{$key}) { - $def_ids->{$key} = $id; - my $quoted_name = $dbh->quote($name); - my $quoted_desc = $dbh->quote($desc); - $dbh->do("INSERT INTO flagtypes (id, name, description, sortkey, " . - "target_type) VALUES ($id, $quoted_name, $quoted_desc, " . - "$sortkey, 'a')"); - } - $def_id_map->{$id} = $def_ids->{$key}; - $dbh->do("INSERT INTO flaginclusions (type_id, product_id) " . - "VALUES ($def_id_map->{$id}, $prod_id)"); - } - - # Note: even though we've converted status definitions, we still can't drop - # the table because we need it to convert the statuses themselves. - - # Convert attachment statuses to flags. To do this we select the statuses - # from the status table and then, for each one, figure out who set it - # and when they set it from the bugs activity table. - my $id = 0; - $sth = $dbh->prepare( - "SELECT attachstatuses.attach_id, attachstatusdefs.id, " . - "attachstatusdefs.name, attachments.bug_id " . - "FROM attachstatuses, attachstatusdefs, attachments " . - "WHERE attachstatuses.statusid = attachstatusdefs.id " . - "AND attachstatuses.attach_id = attachments.attach_id"); - - # a query to determine when the attachment status was set and who set it - my $sth2 = $dbh->prepare("SELECT added, who, bug_when " . - "FROM bugs_activity " . - "WHERE bug_id = ? AND attach_id = ? " . - "AND fieldid = $old_field_id " . - "ORDER BY bug_when DESC"); - - $sth->execute(); - while (my ($attach_id, $def_id, $status, $bug_id) = $sth->fetchrow_array()) { - ++$id; - - # Determine when the attachment status was set and who set it. - # We should always be able to find out this info from the bug activity, - # but we fall back to default values just in case. - $sth2->execute($bug_id, $attach_id); - my ($added, $who, $when); - while (($added, $who, $when) = $sth2->fetchrow_array()) { - last if $added =~ /(^|[, ]+)\Q$status\E([, ]+|$)/; - } - $who = $dbh->quote($who); # "NULL" by default if $who is undefined - $when = $when ? $dbh->quote($when) : "NOW()"; - - - $dbh->do("INSERT INTO flags (id, type_id, status, bug_id, attach_id, " . - "creation_date, modification_date, requestee_id, setter_id) " . - "VALUES ($id, $def_id_map->{$def_id}, '+', $bug_id, " . - "$attach_id, $when, $when, NULL, $who)"); - } - - # Now that we've converted both tables we can drop them. - $dbh->bz_drop_table("attachstatuses"); - $dbh->bz_drop_table("attachstatusdefs"); - - # Convert activity records for attachment statuses into records for flags. - my $sth = $dbh->prepare("SELECT attach_id, who, bug_when, added, removed " . - "FROM bugs_activity WHERE fieldid = $old_field_id"); - $sth->execute(); - while (my ($attach_id, $who, $when, $old_added, $old_removed) = - $sth->fetchrow_array()) - { - my @additions = split(/[, ]+/, $old_added); - @additions = map("$_+", @additions); - my $new_added = $dbh->quote(join(", ", @additions)); - - my @removals = split(/[, ]+/, $old_removed); - @removals = map("$_+", @removals); - my $new_removed = $dbh->quote(join(", ", @removals)); - - $old_added = $dbh->quote($old_added); - $old_removed = $dbh->quote($old_removed); - $who = $dbh->quote($who); - $when = $dbh->quote($when); - - $dbh->do("UPDATE bugs_activity SET fieldid = $new_field_id, " . - "added = $new_added, removed = $new_removed " . - "WHERE attach_id = $attach_id AND who = $who " . - "AND bug_when = $when AND fieldid = $old_field_id " . - "AND added = $old_added AND removed = $old_removed"); - } - - # Remove the attachment status field from the field definitions. - $dbh->do("DELETE FROM fielddefs WHERE name='attachstatusdefs.name'"); - - print "done.\n"; -} - -# 2004-12-13 Nick.Barnes@pobox.com bug 262268 -# Check for spaces and commas in flag type names; if found, rename them. -if ($dbh->bz_table_info("flagtypes")) { - # Get all names and IDs, to find broken ones and to - # check for collisions when renaming. - $sth = $dbh->prepare("SELECT name, id FROM flagtypes"); - $sth->execute(); - - my %flagtypes; - my @badflagnames; - - # find broken flagtype names, and populate a hash table - # to check for collisions. - while (my ($name, $id) = $sth->fetchrow_array()) { - $flagtypes{$name} = $id; - if ($name =~ /[ ,]/) { - push(@badflagnames, $name); - } - } - if (@badflagnames) { - print "Removing spaces and commas from flag names...\n"; - my ($flagname, $tryflagname); - my $sth = $dbh->prepare("UPDATE flagtypes SET name = ? WHERE id = ?"); - foreach $flagname (@badflagnames) { - print " Bad flag type name \"$flagname\" ...\n"; - # find a new name for this flagtype. - ($tryflagname = $flagname) =~ tr/ ,/__/; - # avoid collisions with existing flagtype names. - while (defined($flagtypes{$tryflagname})) { - print " ... can't rename as \"$tryflagname\" ...\n"; - $tryflagname .= "'"; - if (length($tryflagname) > 50) { - my $lastchanceflagname = (substr $tryflagname, 0, 47) . '...'; - if (defined($flagtypes{$lastchanceflagname})) { - print " ... last attempt as \"$lastchanceflagname\" still failed.'\n", - "Rename the flag by hand and run checksetup.pl again.\n"; - die("Bad flag type name $flagname"); - } - $tryflagname = $lastchanceflagname; - } - } - $sth->execute($tryflagname, $flagtypes{$flagname}); - print " renamed flag type \"$flagname\" as \"$tryflagname\"\n"; - $flagtypes{$tryflagname} = $flagtypes{$flagname}; - delete $flagtypes{$flagname}; - } - print "... done.\n"; - } -} - -# 2002-11-24 - bugreport@peshkin.net - bug 147275 -# -# If group_control_map is empty, backward-compatibility -# usebuggroups-equivalent records should be created. -my $entry = Bugzilla->params->{'useentrygroupdefault'}; -$sth = $dbh->prepare("SELECT COUNT(*) FROM group_control_map"); -$sth->execute(); -my ($mapcnt) = $sth->fetchrow_array(); -if ($mapcnt == 0) { - # Initially populate group_control_map. - # First, get all the existing products and their groups. - $sth = $dbh->prepare("SELECT groups.id, products.id, groups.name, " . - "products.name FROM groups, products " . - "WHERE isbuggroup != 0"); - $sth->execute(); - while (my ($groupid, $productid, $groupname, $productname) - = $sth->fetchrow_array()) { - if ($groupname eq $productname) { - # Product and group have same name. - $dbh->do("INSERT INTO group_control_map " . - "(group_id, product_id, entry, membercontrol, " . - "othercontrol, canedit) " . - "VALUES ($groupid, $productid, $entry, " . - CONTROLMAPDEFAULT . ", " . - CONTROLMAPNA . ", 0)"); - } else { - # See if this group is a product group at all. - my $sth2 = $dbh->prepare("SELECT id FROM products WHERE name = " . - $dbh->quote($groupname)); - $sth2->execute(); - my ($id) = $sth2->fetchrow_array(); - if (!$id) { - # If there is no product with the same name as this - # group, then it is permitted for all products. - $dbh->do("INSERT INTO group_control_map " . - "(group_id, product_id, entry, membercontrol, " . - "othercontrol, canedit) " . - "VALUES ($groupid, $productid, 0, " . - CONTROLMAPSHOWN . ", " . - CONTROLMAPNA . ", 0)"); - } - } - } -} - -# 2004-07-17 GRM - Remove "subscriptions" concept from charting, and add -# group-based security instead. -if ($dbh->bz_table_info("user_series_map")) { - # Oracle doesn't like "date" as a column name, and apparently some DBs - # don't like 'value' either. We use the changes to subscriptions as - # something to hang these renamings off. - $dbh->bz_rename_column('series_data', 'date', 'series_date'); - $dbh->bz_rename_column('series_data', 'value', 'series_value'); - - # series_categories.category_id produces a too-long column name for the - # auto-incrementing sequence (Oracle again). - $dbh->bz_rename_column('series_categories', 'category_id', 'id'); - - $dbh->bz_add_column("series", "public", - {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'}); - - # Migrate public-ness across from user_series_map to new field - $sth = $dbh->prepare("SELECT series_id from user_series_map " . - "WHERE user_id = 0"); - $sth->execute(); - while (my ($public_series_id) = $sth->fetchrow_array()) { - $dbh->do("UPDATE series SET public = 1 " . - "WHERE series_id = $public_series_id"); - } - - $dbh->bz_drop_table("user_series_map"); -} - -# 2003-06-26 Copy the old charting data into the database, and create the -# queries that will keep it all running. When the old charting system goes -# away, if this code ever runs, it'll just find no files and do nothing. -my $series_exists = $dbh->selectrow_array("SELECT 1 FROM series " . - $dbh->sql_limit(1)); -if (!$series_exists) { - print "Migrating old chart data into database ...\n" unless $silent; - - require Bugzilla::Series; - - # We prepare the handle to insert the series data - my $seriesdatasth = $dbh->prepare("INSERT INTO series_data " . - "(series_id, series_date, series_value) " . - "VALUES (?, ?, ?)"); - - my $deletesth = $dbh->prepare("DELETE FROM series_data - WHERE series_id = ? AND series_date = ?"); - - my $groupmapsth = $dbh->prepare("INSERT INTO category_group_map " . - "(category_id, group_id) " . - "VALUES (?, ?)"); - - # Fields in the data file (matches the current collectstats.pl) - my @statuses = - qw(NEW ASSIGNED REOPENED UNCONFIRMED RESOLVED VERIFIED CLOSED); - my @resolutions = - qw(FIXED INVALID WONTFIX LATER REMIND DUPLICATE WORKSFORME MOVED); - my @fields = (@statuses, @resolutions); - - # We have a localisation problem here. Where do we get these values? - my $all_name = "-All-"; - my $open_name = "All Open"; - - my $products = $dbh->selectall_arrayref("SELECT name FROM products"); - - foreach my $product ((map { $_->[0] } @$products), "-All-") { - # First, create the series - my %queries; - my %seriesids; - - my $query_prod = ""; - if ($product ne "-All-") { - $query_prod = "product=" . html_quote($product) . "&"; - } - - # The query for statuses is different to that for resolutions. - $queries{$_} = ($query_prod . "bug_status=$_") foreach (@statuses); - $queries{$_} = ($query_prod . "resolution=$_") foreach (@resolutions); - - foreach my $field (@fields) { - # Create a Series for each field in this product. - # user ID = 0 is used. - my $series = new Bugzilla::Series(undef, $product, $all_name, - $field, 0, 1, - $queries{$field}, 1); - $series->writeToDatabase(); - $seriesids{$field} = $series->{'series_id'}; - } - - # We also add a new query for "Open", so that migrated products get - # the same set as new products (see editproducts.cgi.) - my @openedstatuses = ("UNCONFIRMED", "NEW", "ASSIGNED", "REOPENED"); - my $query = join("&", map { "bug_status=$_" } @openedstatuses); - my $series = new Bugzilla::Series(undef, $product, $all_name, - $open_name, 0, 1, - $query_prod . $query, 1); - $series->writeToDatabase(); - $seriesids{$open_name} = $series->{'series_id'}; - - # Now, we attempt to read in historical data, if any - # Convert the name in the same way that collectstats.pl does - my $product_file = $product; - $product_file =~ s/\//-/gs; - $product_file = "$datadir/mining/$product_file"; - - # There are many reasons that this might fail (e.g. no stats for this - # product), so we don't worry if it does. - open(IN, $product_file) or next; - - # The data files should be in a standard format, even for old - # Bugzillas, because of the conversion code further up this file. - my %data; - my $last_date = ""; - - while () { - if (/^(\d+\|.*)/) { - my @numbers = split(/\||\r/, $1); - - # Only take the first line for each date; it was possible to - # run collectstats.pl more than once in a day. - next if $numbers[0] eq $last_date; - - for my $i (0 .. $#fields) { - # $numbers[0] is the date - $data{$fields[$i]}{$numbers[0]} = $numbers[$i + 1]; - - # Keep a total of the number of open bugs for this day - if (is_open_state($fields[$i])) { - $data{$open_name}{$numbers[0]} += $numbers[$i + 1]; - } - } - - $last_date = $numbers[0]; - } - } - - close(IN); - - foreach my $field (@fields, $open_name) { - # Insert values into series_data: series_id, date, value - my %fielddata = %{$data{$field}}; - foreach my $date (keys %fielddata) { - # We need to delete in case the text file had duplicate entries - # in it. - $deletesth->execute($seriesids{$field}, - $date); - - # We prepared this above - $seriesdatasth->execute($seriesids{$field}, - $date, - $fielddata{$date} || 0); - } - } - - # Create the groupsets for the category - my $category_id = - $dbh->selectrow_array("SELECT id " . - "FROM series_categories " . - "WHERE name = " . $dbh->quote($product)); - my $product_id = - $dbh->selectrow_array("SELECT id " . - "FROM products " . - "WHERE name = " . $dbh->quote($product)); - - if (defined($category_id) && defined($product_id)) { - - # Get all the mandatory groups for this product - my $group_ids = - $dbh->selectcol_arrayref("SELECT group_id " . - "FROM group_control_map " . - "WHERE product_id = $product_id " . - "AND (membercontrol = " . CONTROLMAPMANDATORY . - " OR othercontrol = " . CONTROLMAPMANDATORY . ")"); - - foreach my $group_id (@$group_ids) { - $groupmapsth->execute($category_id, $group_id); - } - } - } -} - -Bugzilla::Field::create_or_update( - {name => "owner_idle_time", desc => "Time Since Assignee Touched"}); - -# 2004-04-12 - Keep regexp-based group permissions up-to-date - Bug 240325 -if ($dbh->bz_column_info("user_group_map", "isderived")) { - $dbh->bz_add_column('user_group_map', 'grant_type', - {TYPE => 'INT1', NOTNULL => 1, DEFAULT => '0'}); - $dbh->do("DELETE FROM user_group_map WHERE isderived != 0"); - $dbh->do("UPDATE user_group_map SET grant_type = " . GRANT_DIRECT); - $dbh->bz_drop_column("user_group_map", "isderived"); - - $dbh->bz_drop_index('user_group_map', 'user_group_map_user_id_idx'); - $dbh->bz_add_index('user_group_map', 'user_group_map_user_id_idx', - {TYPE => 'UNIQUE', - FIELDS => [qw(user_id group_id grant_type isbless)]}); - - # Make sure groups get rederived - $dbh->do("UPDATE groups SET last_changed = NOW() WHERE name = 'admin'"); -} - -# 2004-07-16 - Make it possible to have group-group relationships other than -# membership and bless. -if ($dbh->bz_column_info("group_group_map", "isbless")) { - $dbh->bz_add_column('group_group_map', 'grant_type', - {TYPE => 'INT1', NOTNULL => 1, DEFAULT => '0'}); - $dbh->do("UPDATE group_group_map SET grant_type = " . - "IF(isbless, " . GROUP_BLESS . ", " . - GROUP_MEMBERSHIP . ")"); - $dbh->bz_drop_index('group_group_map', 'group_group_map_member_id_idx'); - $dbh->bz_drop_column("group_group_map", "isbless"); - $dbh->bz_add_index('group_group_map', 'group_group_map_member_id_idx', - {TYPE => 'UNIQUE', FIELDS => [qw(member_id grantor_id grant_type)]}); -} - -# Allow profiles to optionally be linked to a unique identifier in an outside -# login data source -$dbh->bz_add_column("profiles", "extern_id", {TYPE => 'varchar(64)'}); - -# 2004-11-20 - LpSolit@netscape.net - Bug 180879 -# Add grant and request groups for flags -$dbh->bz_add_column('flagtypes', 'grant_group_id', {TYPE => 'INT3'}); -$dbh->bz_add_column('flagtypes', 'request_group_id', {TYPE => 'INT3'}); - -# 2004-01-03 - bug 253721 erik@dasbistro.com -# mailto is no longer just userids -$dbh->bz_rename_column('whine_schedules', 'mailto_userid', 'mailto'); -$dbh->bz_add_column('whine_schedules', 'mailto_type', - {TYPE => 'INT2', NOTNULL => 1, DEFAULT => '0'}); - -# 2005-01-29 - mkanat@bugzilla.org -if (!$dbh->bz_column_info('longdescs', 'already_wrapped')) { - # Old, pre-wrapped comments should not be auto-wrapped - $dbh->bz_add_column('longdescs', 'already_wrapped', - {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'FALSE'}, 1); - # If an old comment doesn't have a newline in the first 80 characters, - # (or doesn't contain a newline at all) and it contains a space, - # then it's probably a mis-wrapped comment and we should wrap it - # at display-time. - print "Fixing old, mis-wrapped comments...\n"; - $dbh->do(q{UPDATE longdescs SET already_wrapped = 0 - WHERE (} . $dbh->sql_position(q{'\n'}, 'thetext') . q{ > 80 - OR } . $dbh->sql_position(q{'\n'}, 'thetext') . q{ = 0) - AND SUBSTRING(thetext FROM 1 FOR 80) LIKE '% %'}); -} - -# 2001-09-03 (landed 2005-02-24) dkl@redhat.com bug 17453 -# Moved enum types to separate tables so we need change the old enum types to -# standard varchars in the bugs table. -$dbh->bz_alter_column('bugs', 'bug_status', - {TYPE => 'varchar(64)', NOTNULL => 1}); -# 2005-03-23 Tomas.Kopal@altap.cz - add default value to resolution, bug 286695 -$dbh->bz_alter_column('bugs', 'resolution', - {TYPE => 'varchar(64)', NOTNULL => 1, DEFAULT => "''"}); -$dbh->bz_alter_column('bugs', 'priority', - {TYPE => 'varchar(64)', NOTNULL => 1}); -$dbh->bz_alter_column('bugs', 'bug_severity', - {TYPE => 'varchar(64)', NOTNULL => 1}); -$dbh->bz_alter_column('bugs', 'rep_platform', - {TYPE => 'varchar(64)', NOTNULL => 1}, ''); -$dbh->bz_alter_column('bugs', 'op_sys', - {TYPE => 'varchar(64)', NOTNULL => 1}); - - -# 2005-02-20 - LpSolit@gmail.com - Bug 277504 -# When migrating quips from the '$datadir/comments' file to the DB, -# the user ID should be NULL instead of 0 (which is an invalid user ID). -if ($dbh->bz_column_info('quips', 'userid')->{NOTNULL}) { - $dbh->bz_alter_column('quips', 'userid', {TYPE => 'INT3'}); - print "Changing owner to NULL for quips where the owner is unknown...\n"; - $dbh->do('UPDATE quips SET userid = NULL WHERE userid = 0'); -} - -# Add fulltext indexes for bug summaries and descriptions/comments. -if (!$dbh->bz_index_info('bugs', 'bugs_short_desc_idx')) { - print "Adding full-text index for short_desc column in bugs table...\n"; - $dbh->bz_add_index('bugs', 'bugs_short_desc_idx', - {TYPE => 'FULLTEXT', FIELDS => [qw(short_desc)]}); -} -# Right now, we only create the "thetext" index on MySQL. -if ($dbh->isa('Bugzilla::DB::Mysql') - && !$dbh->bz_index_info('longdescs', 'longdescs_thetext_idx')) -{ - print "Adding full-text index for thetext column in longdescs table...\n"; - $dbh->bz_add_index('longdescs', 'longdescs_thetext_idx', - {TYPE => 'FULLTEXT', FIELDS => [qw(thetext)]}); -} - -# 2002 November, myk@mozilla.org, bug 178841: -# -# Convert the "attachments.filename" column from a ridiculously large -# "mediumtext" to a much more sensible "varchar(100)". Also takes -# the opportunity to remove paths from existing filenames, since they -# shouldn't be there for security. Buggy browsers include them, -# and attachment.cgi now takes them out, but old ones need converting. -# -{ - my $ref = $dbh->bz_column_info("attachments", "filename"); - if ($ref->{TYPE} ne 'varchar(100)') { - print "Removing paths from filenames in attachments table...\n"; - - $sth = $dbh->prepare("SELECT attach_id, filename FROM attachments " . - "WHERE " . $dbh->sql_position(q{'/'}, 'filename') . " > 0 OR " . - $dbh->sql_position(q{'\\\\'}, 'filename') . " > 0"); - $sth->execute; - - while (my ($attach_id, $filename) = $sth->fetchrow_array) { - $filename =~ s/^.*[\/\\]//; - my $quoted_filename = $dbh->quote($filename); - $dbh->do("UPDATE attachments SET filename = $quoted_filename " . - "WHERE attach_id = $attach_id"); - } - - print "Done.\n"; - - print "Resizing attachments.filename from mediumtext to varchar(100).\n"; - $dbh->bz_alter_column("attachments", "filename", - {TYPE => 'varchar(100)', NOTNULL => 1}); - } -} - -# 2003-01-11, burnus@net-b.de, bug 184309 -# Support for quips approval -$dbh->bz_add_column('quips', 'approved', - {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => 'TRUE'}); - -# 2002-12-20 Bug 180870 - remove manual shadowdb replication code -$dbh->bz_drop_table("shadowlog"); - -# 2003-04-27 - bugzilla@chimpychompy.org (GavinS) -# -# Bug 180086 (http://bugzilla.mozilla.org/show_bug.cgi?id=180086) -# -# Renaming the 'count' column in the votes table because Sybase doesn't -# like it -if ($dbh->bz_column_info('votes', 'count')) { - # 2003-04-24 - myk@mozilla.org/bbaetz@acm.org, bug 201018 - # Force all cached groups to be updated at login, due to security bug - # Do this here, inside the next schema change block, so that it doesn't - # get invalidated on every checksetup run. - $dbh->do("UPDATE profiles SET refreshed_when='1900-01-01 00:00:00'"); - - $dbh->bz_rename_column('votes', 'count', 'vote_count'); -} - -# 2004/02/15 - Summaries shouldn't be null - see bug 220232 -if (!exists $dbh->bz_column_info('bugs', 'short_desc')->{NOTNULL}) { - $dbh->bz_alter_column('bugs', 'short_desc', - {TYPE => 'MEDIUMTEXT', NOTNULL => 1}, ''); -} - -# 2003-10-24 - alt@sonic.net, bug 224208 -# Support classification level -$dbh->bz_add_column('products', 'classification_id', - {TYPE => 'INT2', NOTNULL => 1, DEFAULT => '1'}); - -# 2005-01-12 Nick Barnes bug 278010 -# Rename any group which has an empty name. -# Note that there can be at most one such group (because of -# the SQL index on the name column). -$sth = $dbh->prepare("SELECT id FROM groups where name = ''"); -$sth->execute(); -my ($emptygroupid) = $sth->fetchrow_array(); -if ($emptygroupid) { - # There is a group with an empty name; find a name to rename it - # as. Must avoid collisions with existing names. Start with - # group_$gid and add _ if necessary. - my $trycount = 0; - my $trygroupname; - my $trygroupsth = $dbh->prepare("SELECT id FROM groups where name = ?"); - do { - $trygroupname = "group_$emptygroupid"; - if ($trycount > 0) { - $trygroupname .= "_$trycount"; - } - $trygroupsth->execute($trygroupname); - if ($trygroupsth->rows > 0) { - $trycount ++; - } - } while ($trygroupsth->rows > 0); - $sth = $dbh->prepare("UPDATE groups SET name = ? " . - "WHERE id = $emptygroupid"); - $sth->execute($trygroupname); - print "Group $emptygroupid had an empty name; renamed as '$trygroupname'.\n"; -} - -# 2005-02-12 bugreport@peshkin.net, bug 281787 -$dbh->bz_add_index('bugs_activity', 'bugs_activity_who_idx', [qw(who)]); - -# This lastdiffed change and these default changes are unrelated, -# but in order for MySQL to successfully run these default changes only once, -# they have to be inside this block. -# If bugs.lastdiffed is NOT NULL... -if($dbh->bz_column_info('bugs', 'lastdiffed')->{NOTNULL}) { - # Add defaults for some fields that should have them but didn't. - $dbh->bz_alter_column('bugs', 'status_whiteboard', - {TYPE => 'MEDIUMTEXT', NOTNULL => 1, DEFAULT => "''"}); - $dbh->bz_alter_column('bugs', 'keywords', - {TYPE => 'MEDIUMTEXT', NOTNULL => 1, DEFAULT => "''"}); - $dbh->bz_alter_column('bugs', 'votes', - {TYPE => 'INT3', NOTNULL => 1, DEFAULT => '0'}); - # And change lastdiffed to NULL - $dbh->bz_alter_column('bugs', 'lastdiffed', {TYPE => 'DATETIME'}); -} - -# 2005-03-09 qa_contact should be NULL instead of 0, bug 285534 -if ($dbh->bz_column_info('bugs', 'qa_contact')->{NOTNULL}) { - $dbh->bz_alter_column('bugs', 'qa_contact', {TYPE => 'INT3'}); - $dbh->do("UPDATE bugs SET qa_contact = NULL WHERE qa_contact = 0"); -} - -# 2005-03-27 initialqacontact should be NULL instead of 0, bug 287483 -if ($dbh->bz_column_info('components', 'initialqacontact')->{NOTNULL}) { - $dbh->bz_alter_column('components', 'initialqacontact', {TYPE => 'INT3'}); - $dbh->do("UPDATE components SET initialqacontact = NULL " . - "WHERE initialqacontact = 0"); -} - -# 2005-03-29 - gerv@gerv.net - bug 73665. -# Migrate email preferences to new email prefs table. -if ($dbh->bz_column_info("profiles", "emailflags")) { - print "Migrating email preferences to new table ...\n" unless $silent; - - # These are the "roles" and "reasons" from the original code, mapped to - # the new terminology of relationships and events. - my %relationships = ("Owner" => REL_ASSIGNEE, - "Reporter" => REL_REPORTER, - "QAcontact" => REL_QA, - "CClist" => REL_CC, - "Voter" => REL_VOTER); - - my %events = ("Removeme" => EVT_ADDED_REMOVED, - "Comments" => EVT_COMMENT, - "Attachments" => EVT_ATTACHMENT, - "Status" => EVT_PROJ_MANAGEMENT, - "Resolved" => EVT_OPENED_CLOSED, - "Keywords" => EVT_KEYWORD, - "CC" => EVT_CC, - "Other" => EVT_OTHER, - "Unconfirmed" => EVT_UNCONFIRMED); - - # Request preferences - my %requestprefs = ("FlagRequestee" => EVT_FLAG_REQUESTED, - "FlagRequester" => EVT_REQUESTED_FLAG); - - # Select all emailflags flag strings - my $sth = $dbh->prepare("SELECT userid, emailflags FROM profiles"); - $sth->execute(); - - while (my ($userid, $flagstring) = $sth->fetchrow_array()) { - # If the user has never logged in since emailprefs arrived, and the - # temporary code to give them a default string never ran, then - # $flagstring will be null. In this case, they just get all mail. - $flagstring ||= ""; - - # The 255 param is here, because without a third param, split will - # trim any trailing null fields, which causes Perl to eject lots of - # warnings. Any suitably large number would do. - my %emailflags = split(/~/, $flagstring, 255); # Appease my editor: / - - my $sth2 = $dbh->prepare("INSERT into email_setting " . - "(user_id, relationship, event) VALUES (" . - "$userid, ?, ?)"); - foreach my $relationship (keys %relationships) { - foreach my $event (keys %events) { - my $key = "email$relationship$event"; - if (!exists($emailflags{$key}) || $emailflags{$key} eq 'on') { - $sth2->execute($relationships{$relationship}, - $events{$event}); - } - } - } - - # Note that in the old system, the value of "excludeself" is assumed to - # be off if the preference does not exist in the user's list, unlike - # other preferences whose value is assumed to be on if they do not - # exist. - # - # This preference has changed from global to per-relationship. - if (!exists($emailflags{'ExcludeSelf'}) - || $emailflags{'ExcludeSelf'} ne 'on') - { - foreach my $relationship (keys %relationships) { - $dbh->do("INSERT into email_setting " . - "(user_id, relationship, event) VALUES (" . - $userid . ", " . - $relationships{$relationship}. ", " . - EVT_CHANGED_BY_ME . ")"); - } - } - - foreach my $key (keys %requestprefs) { - if (!exists($emailflags{$key}) || $emailflags{$key} eq 'on') { - $dbh->do("INSERT into email_setting " . - "(user_id, relationship, event) VALUES (" . - $userid . ", " . REL_ANY . ", " . - $requestprefs{$key} . ")"); - } - } - } - - # EVT_ATTACHMENT_DATA should initially have identical settings to - # EVT_ATTACHMENT. - CloneEmailEvent(EVT_ATTACHMENT, EVT_ATTACHMENT_DATA); - - $dbh->bz_drop_column("profiles", "emailflags"); -} - -# Check for any "new" email settings that wouldn't have been ported over -# during the block above. Since these settings would have otherwise -# fallen under EVT_OTHER, we'll just clone those settings. That way if -# folks have already disabled all of that mail, there won't be any change. -{ - my %events = ("Dependency Tree Changes" => EVT_DEPEND_BLOCK); - - foreach my $desc (keys %events) { - my $event = $events{$desc}; - $sth = $dbh->prepare("SELECT count(*) FROM email_setting WHERE event = $event"); - $sth->execute(); - if (!($sth->fetchrow_arrayref()->[0])) { - # No settings in the table yet, so we assume that this is the - # first time it's being set. - print "Initializing \"$desc\" email_setting ...\n" unless $silent; - CloneEmailEvent(EVT_OTHER, $event); - } - } -} - -sub CloneEmailEvent { - my ($source, $target) = @_; - - my $sth1 = $dbh->prepare("SELECT user_id, relationship FROM email_setting - WHERE event = $source"); - my $sth2 = $dbh->prepare("INSERT into email_setting " . - "(user_id, relationship, event) VALUES (" . - "?, ?, $target)"); - - $sth1->execute(); - - while (my ($userid, $relationship) = $sth1->fetchrow_array()) { - $sth2->execute($userid, $relationship); - } -} - -# 2005-03-27: Standardize all boolean fields to plain "tinyint" -if ( $dbh->isa('Bugzilla::DB::Mysql') ) { - # This is a change to make things consistent with Schema, so we use - # direct-database access methods. - my $quip_info_sth = $dbh->column_info(undef, undef, 'quips', '%'); - my $quips_cols = $quip_info_sth->fetchall_hashref("COLUMN_NAME"); - my $approved_col = $quips_cols->{'approved'}; - if ( $approved_col->{TYPE_NAME} eq 'TINYINT' - and $approved_col->{COLUMN_SIZE} == 1 ) - { - # series.public could have been renamed to series.is_public, - # and so wouldn't need to be fixed manually. - if ($dbh->bz_column_info('series', 'public')) { - $dbh->bz_alter_column_raw('series', 'public', - {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => '0'}); - } - $dbh->bz_alter_column_raw('bug_status', 'isactive', - {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => '1'}); - $dbh->bz_alter_column_raw('rep_platform', 'isactive', - {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => '1'}); - $dbh->bz_alter_column_raw('resolution', 'isactive', - {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => '1'}); - $dbh->bz_alter_column_raw('op_sys', 'isactive', - {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => '1'}); - $dbh->bz_alter_column_raw('bug_severity', 'isactive', - {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => '1'}); - $dbh->bz_alter_column_raw('priority', 'isactive', - {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => '1'}); - $dbh->bz_alter_column_raw('quips', 'approved', - {TYPE => 'BOOLEAN', NOTNULL => 1, DEFAULT => '1'}); - } -} +Bugzilla::Install::DB::update_table_definitions(); # 2005-04-07 - alt@sonic.net, bug 289455 # make classification_id field type be consistent with DB:Schema @@ -3293,50 +1230,6 @@ sub bailout { # this is just in case we get interrupted while getting passwd exit 1; } -if (@admins) { - # Identify admin group. - my $sth = $dbh->prepare("SELECT id FROM groups - WHERE name = 'admin'"); - $sth->execute(); - my ($adminid) = $sth->fetchrow_array(); - foreach my $userid (@admins) { - $dbh->do("INSERT INTO user_group_map - (user_id, group_id, isbless, grant_type) - VALUES ($userid, $adminid, 0, " . GRANT_DIRECT . ")"); - # Existing administrators are made blessers of group "admin" - # but only explicitly defined blessers can bless group admin. - # Other groups can be blessed by any admin (by default) or additional - # defined blessers. - $dbh->do("INSERT INTO user_group_map - (user_id, group_id, isbless, grant_type) - VALUES ($userid, $adminid, 1, " . GRANT_DIRECT . ")"); - } - - $dbh->bz_lock_tables('groups READ', - 'group_group_map WRITE'); - $dbh->do('DELETE FROM group_group_map WHERE member_id = ?', - undef, $adminid); - $sth = $dbh->prepare("SELECT id FROM groups"); - $sth->execute(); - while ( my ($id) = $sth->fetchrow_array() ) { - # Admins can bless every group. - $dbh->do("INSERT INTO group_group_map - (member_id, grantor_id, grant_type) - VALUES ($adminid, $id," . GROUP_BLESS . ")"); - # Admins can see every group. - $dbh->do("INSERT INTO group_group_map - (member_id, grantor_id, grant_type) - VALUES ($adminid, $id," . GROUP_VISIBLE . ")"); - # Admins are initially members of every group. - next if ($id == $adminid); - $dbh->do("INSERT INTO group_group_map - (member_id, grantor_id, grant_type) - VALUES ($adminid, $id," . GROUP_MEMBERSHIP . ")"); - } - $dbh->bz_unlock_tables(); -} - - my @groups = (); $sth = $dbh->prepare("SELECT id FROM groups"); $sth->execute(); -- cgit v1.2.3-24-g4f1b