From 6dcda41d4c40a5617757a3b0ec9bbaeebfde6b55 Mon Sep 17 00:00:00 2001 From: "terry%mozilla.org" <> Date: Sat, 22 Jan 2000 12:24:39 +0000 Subject: 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. Set up a new, highly experimental email-notification scheme. To turn it on, the maintainer has to turn on the "New email tech" param, and then individual users have to turn on the "New email tech" preference. --- CGI.pl | 11 +-- buglist.cgi | 2 +- changepassword.cgi | 25 +++++- checksetup.pl | 117 +++++++++++++++++++++++++- defparams.pl | 8 ++ globals.pl | 44 ++++++++-- process_bug.cgi | 20 ++++- processmail | 238 ++++++++++++++++++++++++++++++++++++++++++++++++++++- sanitycheck.cgi | 16 +++- 9 files changed, 454 insertions(+), 27 deletions(-) diff --git a/CGI.pl b/CGI.pl index 033961424..ecd56860a 100644 --- a/CGI.pl +++ b/CGI.pl @@ -777,13 +777,14 @@ sub DumpBugActivity { $datepart = "and bugs_activity.bug_when >= $starttime"; } my $query = " - select bugs_activity.field, bugs_activity.bug_when, + SELECT fielddefs.name, bugs_activity.bug_when, bugs_activity.oldvalue, bugs_activity.newvalue, profiles.login_name - from bugs_activity,profiles - where bugs_activity.bug_id = $id $datepart - and profiles.userid = bugs_activity.who - order by bugs_activity.bug_when"; + FROM bugs_activity,profiles,fielddefs + WHERE bugs_activity.bug_id = $id $datepart + AND fielddefs.fieldid = bugs_activity.fieldid + AND profiles.userid = bugs_activity.who + ORDER BY bugs_activity.bug_when"; SendSQL($query); diff --git a/buglist.cgi b/buglist.cgi index 2309b3205..7b53b9a15 100755 --- a/buglist.cgi +++ b/buglist.cgi @@ -452,7 +452,7 @@ if (defined $ref && 0 < @$ref) { my @list; foreach my $f (@$ref) { - push(@list, "\nbugs_activity.field = " . SqlQuote($f)); + push(@list, "\nbugs_activity.fieldid = " . GetFieldID($f)); } $query .= "and bugs_activity.bug_id = bugs.bug_id and (" . join(' or ', @list) . ") "; diff --git a/changepassword.cgi b/changepassword.cgi index 93b736e55..3e433ed04 100755 --- a/changepassword.cgi +++ b/changepassword.cgi @@ -35,9 +35,9 @@ if (! defined $::FORM{'pwd1'}) { $qacontactpart = ", the current QA Contact"; } my $loginname = SqlQuote($::COOKIE{'Bugzilla_login'}); - SendSQL("select emailnotification,realname from profiles where login_name = " . + SendSQL("select emailnotification,realname,newemailtech from profiles where login_name = " . $loginname); - my ($emailnotification, $realname) = (FetchSQLData()); + my ($emailnotification, $realname, $newemailtech) = (FetchSQLData()); $realname = value_quote($realname); print qq{
@@ -79,6 +79,21 @@ On which of these bugs would you like email notification of changes? +"; + if (Param("newemailtech")) { + my $checkedpart = $newemailtech ? "CHECKED" : ""; + print qq{ +
+New! Bugzilla has a new email +notification scheme. It is experimental and bleeding edge and will +hopefully evolve into a brave new happy world where all the spam and ugliness +of the old notifications will go away. If you wish to sign up for this (and +risk any bugs), check here. +New email tech + +}; + } + print "
@@ -126,8 +141,10 @@ Please click Back and try again.\n"; } -SendSQL("update profiles set emailnotification='$::FORM{'emailnotification'}' where login_name = " . - SqlQuote($::COOKIE{'Bugzilla_login'})); +SendSQL("UPDATE profiles " . + "SET emailnotification='$::FORM{'emailnotification'}', " . + " newemailtech = '$::FORM{'newemailtech'}' " . + "WHERE login_name = " . SqlQuote($::COOKIE{'Bugzilla_login'})); my $newrealname = $::FORM{'realname'}; diff --git a/checksetup.pl b/checksetup.pl index 14445c2b6..da79319ab 100755 --- a/checksetup.pl +++ b/checksetup.pl @@ -493,13 +493,13 @@ $table{bugs_activity} = 'bug_id mediumint not null, who mediumint not null, bug_when datetime not null, - field varchar(64) not null, + fieldid mediumint not null, oldvalue tinytext, newvalue tinytext, index (bug_id), index (bug_when), - index (field)'; + index (fieldid)'; $table{attachments} = @@ -542,6 +542,7 @@ $table{bugs} = keywords mediumtext not null, ' # Note: keywords field is only a cache; # the real data comes from the keywords table. . ' + lastdiffed datetime not null, index (assigned_to), index (creation_ts), @@ -643,10 +644,31 @@ $table{profiles} = groupset bigint not null, emailnotification enum("ExcludeSelfChanges", "CConly", "All") not null default "ExcludeSelfChanges", disabledtext mediumtext not null, + newemailtech tinyint not null, index(login_name)'; +# This isn't quite cooked yet... +# +# $table{diffprefs} = +# 'userid mediumint not null, +# fieldid mediumint not null, +# mailhead tinyint not null, +# maildiffs tinyint not null, +# +# index(userid)'; + +$table{fielddefs} = + 'fieldid mediumint not null auto_increment primary key, + name varchar(64) not null, + description mediumtext not null, + mailhead tinyint not null default 0, + sortkey smallint not null, + + unique(name), + index(sortkey)'; + $table{versions} = 'value tinytext, program varchar(64)'; @@ -793,6 +815,47 @@ unless ($sth->rows) { +########################################################################### +# Populate the list of fields. +########################################################################### + +my $headernum = 1; + +sub AddFDef ($$$) { + my ($name, $description, $mailhead) = (@_); + + $name = $dbh->quote($name); + $description = $dbh->quote($description); + + $dbh->do("REPLACE INTO fielddefs " . + "(name, description, mailhead, sortkey) VALUES " . + "($name, $description, $mailhead, $headernum)"); + $headernum++; +} + + +AddFDef("bug_id", "Bug \#", 1); +AddFDef("short_desc", "Summary", 1); +AddFDef("product", "Product", 1); +AddFDef("version", "Version", 1); +AddFDef("rep_platform", "Platform", 1); +AddFDef("op_sys", "OS/Version", 1); +AddFDef("bug_status", "Status", 1); +AddFDef("resolution", "Resolution", 1); +AddFDef("bug_severity", "Severity", 1); +AddFDef("priority", "Priority", 1); +AddFDef("component", "Component", 1); +AddFDef("assigned_to", "AssignedTo", 1); +AddFDef("reporter", "ReportedBy", 1); +AddFDef("qa_contact", "QAContact", 0); +AddFDef("cc", "CC", 0); +AddFDef("dependson", "BugsThisDependsOn", 0); +AddFDef("blocked", "OtherBugsDependingOnThis", 0); +AddFDef("target_milestone", "Target Milestone", 0); + + + + ########################################################################### # Detect changed local settings ########################################################################### @@ -1188,6 +1251,56 @@ if (GetFieldDef('bugs', 'long_desc')) { } +# 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 (GetFieldDef('bugs_activity', 'field')) { + AddField('bugs_activity', 'fieldid', + 'mediumint not null, ADD INDEX (fieldid)'); + print "Populating new fieldid field ...\n"; + + $dbh->do("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 fieldid FROM fielddefs WHERE name = $q"); + $s2->execute(); + my ($id) = ($s2->fetchrow_array()); + if (!$id) { + $dbh->do("INSERT INTO fielddefs (name, description) VALUES " . + "($q, $q)"); + $s2 = $dbh->prepare("SELECT LAST_INSERT_ID()"); + $s2->execute(); + ($id) = ($s2->fetchrow_array()); + } + $dbh->do("UPDATE bugs_activity SET fieldid = $id WHERE field = $q"); + } + $dbh->do("UNLOCK TABLES"); + + DropField('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 a user pref whether a user wants to use the brand new experimental +# stuff. + +if (!GetFieldDef('bugs', 'lastdiffed')) { + AddField('bugs', 'lastdiffed', 'datetime not null'); + $dbh->do('UPDATE bugs SET lastdiffed = delta_ts, delta_ts = delta_ts'); +} + +AddField('profiles', 'newemailtech', 'tinyint not null') + + + + # # If you had to change the --TABLE-- definition in any way, then add your # differential change code *** A B O V E *** this comment. diff --git a/defparams.pl b/defparams.pl index 342b06fa9..6ddd1cef0 100644 --- a/defparams.pl +++ b/defparams.pl @@ -246,6 +246,14 @@ Subject: [Bug %bugid%] %neworchanged% - %summary% %diffs%"); +DefParam("newemailtech", +q{There is now experimental code in Bugzilla to do the email diffs in a +new and exciting way. But this stuff is not very cooked yet. So, right +now, to use it, the maintainer has to turn on this checkbox, and each user +has to then turn on the "New email tech" preference.}, + "b", + 0); + DefParam("whinedays", diff --git a/globals.pl b/globals.pl index 009b8b235..8d921f04d 100644 --- a/globals.pl +++ b/globals.pl @@ -119,6 +119,22 @@ sub AppendComment { SendSQL("UPDATE bugs SET delta_ts = now() WHERE bug_id = $bugid"); } +sub GetFieldID { + my ($f) = (@_); + SendSQL("SELECT fieldid FROM fielddefs WHERE name = " . SqlQuote($f)); + my $fieldid = FetchOneColumn(); + if (!$fieldid) { + my $q = SqlQuote($f); + SendSQL("REPLACE INTO fielddefs (name, description) VALUES ($q, $q)"); + SendSQL("SELECT LAST_INSERT_ID()"); + $fieldid = FetchOneColumn(); + } + return $fieldid; +} + + + + sub lsearch { my ($list,$item) = (@_); my $count = 0; @@ -282,7 +298,7 @@ sub GenerateVersionTable { my $cols = LearnAboutColumns("bugs"); @::log_columns = @{$cols->{"-list-"}}; - foreach my $i ("bug_id", "creation_ts", "delta_ts", "long_desc") { + foreach my $i ("bug_id", "creation_ts", "delta_ts", "lastdiffed") { my $w = lsearch(\@::log_columns, $i); if ($w >= 0) { splice(@::log_columns, $w, 1); @@ -463,15 +479,27 @@ sub DBNameToIdAndCheck { } sub GetLongDescription { - my ($id) = (@_); + my ($id, $start, $end) = (@_); my $result = ""; - SendSQL("SELECT profiles.login_name, longdescs.bug_when, " . - " longdescs.thetext " . - "FROM longdescs, profiles " . - "WHERE profiles.userid = longdescs.who " . - " AND longdescs.bug_id = $id " . - "ORDER BY longdescs.bug_when"); my $count = 0; + my ($query) = ("SELECT profiles.login_name, longdescs.bug_when, " . + " longdescs.thetext " . + "FROM longdescs, profiles " . + "WHERE profiles.userid = longdescs.who " . + " AND longdescs.bug_id = $id "); + + if ($start && $start =~ /[1-9]/) { + # If the start is all zeros, then don't do this (because we want to + # not emit a leading "Addition Comments" line in that case.) + $query .= "AND longdescs.bug_when > '$start'"; + $count = 1; + } + if ($end) { + $query .= "AND longdescs.bug_when <= '$end'"; + } + + $query .= "ORDER BY longdescs.bug_when"; + SendSQL($query); while (MoreSQLData()) { my ($who, $when, $text) = (FetchSQLData()); if ($count) { diff --git a/process_bug.cgi b/process_bug.cgi index 027579cee..e61fb39b2 100755 --- a/process_bug.cgi +++ b/process_bug.cgi @@ -464,7 +464,10 @@ sub LogDependencyActivity { my ($i, $oldstr, $target, $me) = (@_); my $newstr = SnapShotDeps($i, $target, $me); if ($oldstr ne $newstr) { - SendSQL("insert into bugs_activity (bug_id,who,bug_when,field,oldvalue,newvalue) values ($i,$whoid,$timestamp,'$target','$oldstr','$newstr')"); + my $fieldid = GetFieldID($target); + SendSQL("INSERT INTO bugs_activity " . + "(bug_id,who,bug_when,fieldid,oldvalue,newvalue) VALUES " . + "($i,$whoid,$timestamp,$fieldid,'$oldstr','$newstr')"); return 1; } return 0; @@ -476,7 +479,7 @@ sub LogDependencyActivity { # foreach my $id (@idlist) { my %dependencychanged; - SendSQL("lock tables bugs write, bugs_activity write, cc write, profiles write, dependencies write, votes write, keywords write, longdescs write, keyworddefs read"); + SendSQL("lock tables bugs write, bugs_activity write, cc write, profiles write, dependencies write, votes write, keywords write, longdescs write, fielddefs write, keyworddefs read"); my @oldvalues = SnapShotBug($id); if (defined $::FORM{'delta_ts'} && $::FORM{'delta_ts'} ne $delta_ts) { @@ -628,6 +631,15 @@ The changes made were: foreach my $ccid (keys %ccids) { SendSQL("insert into cc (bug_id, who) values ($id, $ccid)"); } + my $newcclist = ShowCcList($id); + if ($newcclist ne $origcclist) { + my $col = GetFieldID('cc'); + my $origq = SqlQuote($origcclist); + my $newq = SqlQuote($newcclist); + SendSQL("INSERT INTO bugs_activity " . + "(bug_id,who,bug_when,fieldid,oldvalue,newvalue) VALUES " . + "($id,$whoid,'$timestamp',$col,$origq,$newq)"); + } } @@ -713,10 +725,10 @@ The changes made were: RemoveVotes($id, "This bug has been moved to a different product"); } - $col = SqlQuote($col); + $col = GetFieldID($col); $old = SqlQuote($old); $new = SqlQuote($new); - my $q = "insert into bugs_activity (bug_id,who,bug_when,field,oldvalue,newvalue) values ($id,$whoid,'$timestamp',$col,$old,$new)"; + my $q = "insert into bugs_activity (bug_id,who,bug_when,fieldid,oldvalue,newvalue) values ($id,$whoid,'$timestamp',$col,$old,$new)"; # puts "
$q
" SendSQL($q); } diff --git a/processmail b/processmail index e85a5eaf8..432ded3e1 100755 --- a/processmail +++ b/processmail @@ -265,6 +265,7 @@ $::bug{'long_desc'} my $didexclude = 0; my %seen; +my @sentlist; sub fixaddresses { my ($field, $list) = (@_); my @result; @@ -307,8 +308,233 @@ sub Log { Unlock(); } + +sub FormatTriple { + my ($a, $b, $c) = (@_); + $^A = ""; + my $temp = formline << 'END', $a, $b, $c; +^>>>>>>>>>>>>>>>>>>|^<<<<<<<<<<<<<<<<<<<<<<<<<<<|^<<<<<<<<<<<<<<<<<<<<<<<<<<~~ +END + ; # This semicolon appeases my emacs editor macros. :-) + return $^A; +} + +sub FormatDouble { + my ($a, $b) = (@_); + $a .= ":"; + $^A = ""; + my $temp = formline << 'END', $a, $b; +^>>>>>>>>>>>>>>>>>> ^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<~~ +END + ; # This semicolon appeases my emacs editor macros. :-) + return $^A; +} + + +sub NewProcessOneBug { + my ($id) = (@_); + + my @headerlist; + my %values; + my %defmailhead; + my %fielddescription; + + my $msg = ""; + + SendSQL("SELECT name, description, mailhead FROM fielddefs " . + "ORDER BY sortkey"); + while (MoreSQLData()) { + my ($field, $description, $mailhead) = (FetchSQLData()); + push(@headerlist, $field); + $defmailhead{$field} = $mailhead; + $fielddescription{$field} = $description; + } + SendSQL("SELECT " . join(',', @::log_columns) . ", lastdiffed, now() " . + "FROM bugs WHERE bug_id = $id"); + my @row = FetchSQLData(); + foreach my $i (@::log_columns) { + $values{$i} = shift(@row); + } + my ($start, $end) = (@row); + $values{'cc'} = ShowCcList($id); + + + $values{'assigned_to'} = DBID_to_name($values{'assigned_to'}); + $values{'reporter'} = DBID_to_name($values{'reporter'}); + if ($values{'qa_contact'}) { + $values{'qa_contact'} = DBID_to_name($values{'qa_contact'}); + } + + my @diffs; + + + SendSQL("SELECT profiles.login_name, fielddefs.description, " . + " bug_when, oldvalue, newvalue " . + "FROM bugs_activity, fielddefs, profiles " . + "WHERE bug_id = $id " . + " AND fielddefs.fieldid = bugs_activity.fieldid " . + " AND profiles.userid = who " . + " AND bug_when > '$start' " . + " AND bug_when <= '$end' " . + "ORDER BY bug_when" + ); + + while (MoreSQLData()) { + my @row = FetchSQLData(); + push(@diffs, \@row); + } + + my $difftext = ""; + my $lastwho = ""; + foreach my $ref (@diffs) { + my ($who, $what, $when, $old, $new) = (@$ref); + if ($who ne $lastwho) { + $lastwho = $who; + $difftext .= "\n$who changed:\n\n"; + $difftext .= FormatTriple("What ", "Old Value", "New Value"); + $difftext .= ('-' x 76) . "\n"; + } + $difftext .= FormatTriple($what, $old, $new); + } + + $difftext = trim($difftext); + + + my $newcomments = GetLongDescription($id, $start, $end); + + my $count = 0; + for my $person ($values{'assigned_to'}, $values{'reporter'}, + split(/,/, $values{'cc'}), + @forcecc) { + $count++; + if ($seen{$person}) { + next; + } + + SendSQL("SELECT userid, emailnotification, newemailtech," . + " groupset & $values{'groupset'} " . + "FROM profiles WHERE login_name = " . SqlQuote($person)); + my ($userid, $emailnotification, $newemailtech, + $groupset) = (FetchSQLData()); + if (!$newemailtech || !Param('newemailtech')) { + next; + } + $seen{$person} = 1; + if ($groupset ne $values{'groupset'}) { + next; + } + if ($emailnotification eq "ExcludeSelfChanges" && + lc($person) eq $nametoexclude) { + $didexclude = 1; + next; + } + if ($emailnotification eq "CCOnly" && $count < 3) { + next; + } + + my %mailhead = %defmailhead; + +# SendSQL("SELECT name, mailhead, maildiffs FROM diffprefs, fielddefs WHERE fielddefs.fieldid = diffprefs.fieldid AND userid = $userid"); +# while (MoreSQLData()) { +# my ($field, $h, $d) = (FetchSQLData()); +# $mailhead{$field} = $h; +# $maildiffs{$field} = $d; +# } + +# my $maxlen = 0; +# foreach my $f (keys %mailhead) { +# if ($mailhead{$f}) { +# my $l = length($fielddescription{$f}); +# if ($maxlen < $l) { +# $maxlen = $l; +# } +# } +# } + + my $head = ""; + + foreach my $f (@headerlist) { + if ($mailhead{$f}) { + my $value = $values{$f}; + if (!defined $value) { + # Probaby ought to whine or something. ### + next; + } + my $desc = $fielddescription{$f}; + $head .= FormatDouble($desc, $value); + +# my $extra = $maxlen - length($desc); +# $head .= ($extra x " "); +# $head .= $desc . ": "; +# while (1) { +# if (length($value) < 70) { +# $head .= $value . "\n"; +# last; +# } +# my $pos = rindex($value, " ", 70); +# if ($pos < 0) { +# $pos = rindex($value, ",", 70); +# if ($pos < 0) { +# $pos = 70; +# } +# } +# $head .= substr($value, 0, 70) . "\n"; +# $head .= (($extra + 2) x " "); +# $value = substr($value, 70); +# } + } + } + if ($difftext eq "" && $newcomments eq "") { + # Whoops, no differences! + next; + } + + my $isnew = ($start !~ m/[1-9]/); + + my %substs; + $substs{"neworchanged"} = $isnew ? "New" : "Changed"; + $substs{"to"} = $person; + $substs{"cc"} = ''; + $substs{"bugid"} = $id; + if ($isnew) { + $substs{"diffs"} = $head . "\n\n" . $newcomments; + } else { + $substs{"diffs"} = $difftext . "\n\n" . $newcomments; + } + $substs{"summary"} = $values{'short_desc'}; + + # my $template = Param("changedmail"); + my $template = "From: bugzilla-daemon +To: %to% +Cc: %cc% +Subject: [Bug %bugid%] %neworchanged% - %summary% + +%urlbase%show_bug.cgi?id=%bugid% + +%diffs%"; + + my $msg = PerformSubsts(Param("changedmail"), \%substs); + open(SENDMAIL, "|/usr/lib/sendmail -t") || + die "Can't open sendmail"; + + print SENDMAIL trim($msg); + close SENDMAIL; + push(@sentlist, $person); + } + + + + + + SendSQL("UPDATE bugs SET lastdiffed = '$end', delta_ts = delta_ts " . + "WHERE bug_id = $id"); +} + + + sub ProcessOneBug { my $i = $_[0]; + NewProcessOneBug($i); my $old = "shadow/$i"; my $new = "shadow/$i.tmp.$$"; my $diffs = "shadow/$i.diffs.$$"; @@ -364,8 +590,16 @@ sub ProcessOneBug { print SENDMAIL $msg; close SENDMAIL; + foreach my $n (split(/[, ]+/, "$tolist,$cclist")) { + if ($n ne "") { + push(@sentlist, $n); + } + } + $logstr = "$logstr; mail sent to $tolist, $cclist"; - print "Email sent to: $tolist $cclist\n"; + } + if (@sentlist) { + print "Email sent to: " . join(", ", @sentlist) . "\n"; if ($didexclude) { print "Excluding: $nametoexclude (change your preferences if you wish not to be excluded)\n"; } @@ -380,11 +614,13 @@ sub ProcessOneBug { print "$i "; } %seen = (); + @sentlist = (); } # Code starts here ConnectToDatabase(); +GetVersionTable(); Lock(); if (open(FID, "