Thanks Larry!!
I think that deserves a little DOH!! I threw the change in real quick
before I did a commit just now, and I got my bug update. So now I
consider this as a new, working integration script.
I am in the process of also setting up a repository viewer from which I
can then create a URL inside of the Bugzilla note to create a link back
to that particular file in the repository at that noted revision
number. I have started building that code into the script as well, and
will have an almost completely seamless two-way integration by the end
of next week (in the hour here or there that I have to put towards it).
Once I get this done, is there somewhere I can upload the script to
share?? I don't want to have to keep emailing it out to everyone who
asks. I'd rather throw it up for others to improve upon since I know it
will have some flaws.
Regards,
Frank
Larry Underhill wrote:
>Hi Frank,
>
>Looks like you have the following problematic lines:
>
>my $statement = $db_handle->prepare($sql)
>my $statement2 = $db_handle->prepare($sql)
>my $statement3 = $db_handle->prepare($sql)
>
>When you call execute() on your db handles, you are using the values
>held in $sql each time instead of $sql, $sql2, and $sql3.
>
>Change those and you should be all set!
>
>--Larry
>
>
>On Wed, 2005-03-23 at 14:44 -0500, Frank Gruman wrote:
>
>
>>Hello all,
>>
>>I am relatively new to the opensource and perl world, but am really
>>falling in love with both Bugzilla and Subversion.
>>I tried to find a good tool to bring the two of them together, but was
>>failing miserably. Each tool had different pros and cons,
>>but the biggest con I found was that I had to keep using Bugzilla's
>>email gateway. So I found the commit_email.pl script and hacked
>>together a file that will write to the database directly.
>>
>>Let me stress that I HACKED the file together. I am not a perl
>>programmer, and I pretty much built this on a wing and a prayer.
>>
>>At this point, I have the system writing to the Bugzilla database. The
>>issue is that when it writes, it is writing the same thing 3 times.
>>I cannot find where in my code it should be doing this. I've attached
>>the file (hopefully you can read it).
>>
>>Anyone feeling helpful??
>>
>>Frank
>>plain text document attachment (commit-mysql.pl)
>>#!/usr/bin/env perl
>>
>># ====================================================================
>># commit-email.pl: send a commit email for commit REVISION in
>># repository REPOS to some email addresses.
>>#
>># For usage, see the usage subroutine or run the script with no
>># command line arguments.
>>#
>># $HeadURL: http://svn.collab.net/repos/svn/branches/1.1.x/tools/hook-scripts/commit-email.pl.in $
>># $LastChangedDate: 2004-06-14 16:29:22 -0400 (Mon, 14 Jun 2004) $
>># $LastChangedBy: breser $
>># $LastChangedRevision: 9986 $
>>#
>># ====================================================================
>># Copyright (c) 2000-2004 CollabNet. All rights reserved.
>>#
>># This software is licensed as described in the file COPYING, which
>># you should have received as part of this distribution. The terms
>># are also available at http://subversion.tigris.org/license-1.html.
>># If newer versions of this license are posted there, you may use a
>># newer version instead, at your option.
>>#
>># This software consists of voluntary contributions made by many
>># individuals. For exact contribution history, see the revision
>># history and logs, available at http://subversion.tigris.org/.
>># ====================================================================
>>
>># Turn on warnings the best way depending on the Perl version.
>>BEGIN {
>> if ( $] >= 5.006_000)
>> { require warnings; import warnings; }
>> else
>> { $^W = 1; }
>>}
>>
>>use strict;
>>use Carp;
>>use DBI;
>>
>>######################################################################
>># Configuration section.
>>
>>#What is the Bugzilla userid NUMBER you want to use
>>my $buguser = 6;
>>
>># Database command-line executable path.
>># This is generic in the hopes that we will have database flexibility
>># for Bugzilla in the future.
>>my $dbexe = "/usr/bin/mysql";
>>
>># Svnlook path.
>>my $svnlook = "/usr/local/bin/svnlook";
>>
>># Repository Root is the URL for the root of the repository.
>># Since I am not good enough to figure out the projects thing, I am going
>># to set this as my one project.
>>my $repos_root = "http://web.server.name/projects/svn";
>>
>># By default, when a file is deleted from the repository, svnlook diff
>># prints the entire contents of the file. If you want to save space
>># in the log and commit messages by not printing the file, then set
>># $no_diff_deleted to 1.
>>my $no_diff_deleted = 0;
>>
>>my $i=0;
>># Since the path to svnlook depends upon the local installation
>># preferences, check that the required programs exist to insure that
>># the administrator has set up the script properly.
>>{
>> my $ok = 1;
>> foreach my $program ($dbexe, $svnlook)
>> {
>> if (-e $program)
>> {
>> unless (-x $program)
>> {
>> warn "$0: required program `$program' is not executable, ",
>> "edit $0.\n";
>> $ok = 0;
>> }
>> }
>> else
>> {
>> warn "$0: required program `$program' does not exist, edit $0.\n";
>> $ok = 0;
>> }
>> }
>> exit 1 unless $ok;
>>}
>>
>>
>>######################################################################
>># Initial setup/command-line handling.
>>
>># Each value in this array holds a hash reference which contains the
>># associated database information for one project. Start with an
>># implicit rule that matches all paths.
>>my @project_settings_list = (&new_project);
>>
>># Process the command line arguments till there are none left. The
>># first two arguments that are not used by a command line option are
>># the repository path and the revision number.
>>my $repos;
>>my $rev;
>>
>># Use the reference to the first project to populate.
>>my $current_project = $project_settings_list[0];
>>
>># This hash matches the command line option to the hash key in the
>># project. If a key exists but has a false value (''), then the
>># command line option is allowed but requires special handling.
>>my %opt_to_hash_key = ('-u' => 'username',
>> '-p' => 'password',
>> '-d' => 'db',
>> '-h' => 'hostname',
>> '-l' => 'log_file',
>> '-m' => '',
>> '-t' => 'db_type');
>>
>>while (@ARGV)
>> {
>> my $arg = shift @ARGV;
>> if ($arg =~ /^-/)
>> {
>> my $hash_key = $opt_to_hash_key{$arg};
>> unless (defined $hash_key)
>> {
>> die "$0: command line option `$arg' is not recognized.\n";
>> }
>>
>> unless (@ARGV)
>> {
>> die "$0: command line option `$arg' is missing a value.\n";
>> }
>> my $value = shift @ARGV;
>>
>> if ($hash_key)
>> {
>> $current_project->{$hash_key} = $value;
>> }
>> else
>> {
>> # Here handle -m.
>> unless ($arg eq '-m')
>> {
>> die "$0: internal error: should only handle -m here.\n";
>> }
>> $current_project = &new_project;
>> $current_project->{match_regex} = $value;
>> push(@project_settings_list, $current_project);
>> }
>> }
>> elsif ($arg =~ /^-/)
>> {
>> die "$0: command line option `$arg' is not recognized.\n";
>> }
>>
>> else
>> {
>> if (! defined $repos)
>> {
>> $repos = $arg;
>> }
>> elsif (! defined $rev)
>> {
>> $rev = $arg;
>> }
>> else
>> {
>> die "$0: Not enough values defined to run properly.\n";
>> }
>> }
>> }
>>
>>
>># If the revision number is undefined, then there were not enough
>># command line arguments.
>>&usage("$0: too few arguments.") unless defined $rev;
>>
>># Check the validity of the command line arguments. Check that the
>># revision is an integer greater than 0 and that the repository
>># directory exists.
>>unless ($rev =~ /^\d+/ and $rev > 0)
>> {
>> &usage("$0: revision number `$rev' must be an integer > 0.");
>> }
>>unless (-e $repos)
>> {
>> &usage("$0: repos directory `$repos' does not exist.");
>> }
>>unless (-d _)
>> {
>> &usage("$0: repos directory `$repos' is not a directory.");
>> }
>>
>># Check that all of the regular expressions can be compiled and
>># compile them.
>>{
>> my $okk = 1;
>> for ( $i=0; $i<@project_settings_list; ++$i)
>> {
>> my $match_regex = $project_settings_list[$i]->{match_regex};
>>
>> # To help users that automatically write regular expressions
>> # that match the root directory using ^/, remove the / character
>> # because subversion paths, while they start at the root level,
>> # do not begin with a /.
>> $match_regex =~ s#^\^/#^#;
>>
>> my $match_reg;
>> eval { $match_reg = qr/$match_regex/ };
>> if ($@)
>> {
>> warn "$0: -m regex #$i `$match_regex' does not compile:\n$@\n";
>> $okk = 0;
>> next;
>> }
>> $project_settings_list[$i]->{match_reg} = $match_reg;
>> }
>> exit 1 unless $okk;
>>}
>>
>>######################################################################
>># Harvest data using svnlook.
>>
>># Change into /tmp so that svnlook diff can create its .svnlook
>># directory.
>>my $tmp_dir = '/tmp';
>>chdir($tmp_dir)
>> or die "$0: cannot chdir `$tmp_dir': $!\n";
>>
>># Get the author, date, and log from svnlook.
>>my @svnlooklines = &read_from_process($svnlook, 'info', $repos, '-r', $rev);
>>my $author = shift @svnlooklines;
>>my $date = shift @svnlooklines;
>>shift @svnlooklines;
>>my $bugs = shift @svnlooklines;
>>my @log = map { "$_\n" } @svnlooklines;
>>
>># Figure out what directories have changed using svnlook.
>>my @dirschanged = &read_from_process($svnlook, 'dirs-changed', $repos,
>> '-r', $rev);
>>
>># Remove the regular bug expression 'Bug /Issue No: ' (assuming the use of
>># a template entry since we use TortoiseSVN with a template BUGID.
>>
>>$bugs = substr($bugs,15);
>>
>>my @allbugs = split /, /,$bugs;
>>
>>
>># Lose the trailing slash in the directory names if one exists, except
>># in the case of '/'.
>>my $rootchanged = 0;
>>for ( $i=0; $i<@dirschanged; ++$i)
>> {
>> if ($dirschanged[$i] eq '/')
>> {
>> $rootchanged = 1;
>> }
>> else
>> {
>> $dirschanged[$i] =~ s#^(.+)[/\\]$#$1#;
>> }
>> }
>>
>># Figure out what files have changed using svnlook.
>>@svnlooklines = &read_from_process($svnlook, 'changed', $repos, '-r', $rev);
>>
>># Parse the changed nodes.
>>my @adds;
>>my @dels;
>>my @mods;
>>foreach my $line (@svnlooklines)
>> {
>> my $path = '';
>> my $code = '';
>> my $weblink = '';
>> # Split the line up into the modification code and path, ignoring
>> # property modifications.
>> if ($line =~ /^(.). (.*)$/)
>> {
>> $code = $1;
>> $path = $2;
>> }
>>
>> if ($code eq 'A')
>> {
>> $weblink = "$repos_root/$path";
>> push(@adds, $weblink);
>> }
>> elsif ($code eq 'D')
>> {
>> push(@dels, $path);
>> }
>> else
>> {
>> push(@mods, "$repos_root/$path");
>> }
>> }
>>
>># Get the diff from svnlook.
>>my @no_diff_deleted = $no_diff_deleted ? ('--no-diff-deleted') : ();
>>my @difflines = &read_from_process($svnlook, 'diff', $repos,
>> '-r', $rev, @no_diff_deleted);
>>
>>######################################################################
>># Modified directory name collapsing.
>>
>># Collapse the list of changed directories only if the root directory
>># was not modified, because otherwise everything is under root and
>># there's no point in collapsing the directories, and only if more
>># than one directory was modified.
>>my $commondir = '';
>>if (!$rootchanged and @dirschanged > 1)
>> {
>> my $firstline = shift @dirschanged;
>> my @commonpieces = split('/', $firstline);
>> foreach my $lines (@dirschanged)
>> {
>> my @pieces = split('/', $lines);
>> my $a = 0;
>> while ($a < @pieces and $a < @commonpieces)
>> {
>> if ($pieces[$a] ne $commonpieces[$a])
>> {
>> splice(@commonpieces, $a, @commonpieces - $a);
>> last;
>> }
>> $a++;
>> }
>> }
>> unshift(@dirschanged, $firstline);
>>
>> if (@commonpieces)
>> {
>> $commondir = join('/', @commonpieces);
>> my @new_dirschanged;
>> foreach my $dir (@dirschanged)
>> {
>> if ($dir eq $commondir)
>> {
>> $dir = '.';
>> }
>> else
>> {
>> $dir =~ s#^$commondir/##;
>> }
>> push(@new_dirschanged, $dir);
>> }
>> @dirschanged = @new_dirschanged;
>> }
>> }
>>my $dirlist = join(' ', @dirschanged);
>>
>>######################################################################
>># Assembly of log message.
>>
>># Put together the body of the log message.
>>my @body;
>>push(@body, "Author: $author\n");
>>push(@body, "Date: $date\n");
>>push(@body, "New Revision: $rev\n");
>>push(@body, "\n");
>>if (@adds)
>> {
>> @adds = sort @adds;
>> push(@body, "Added:\n");
>> push(@body, map { " $_\n" } @adds);
>> }
>>if (@dels)
>> {
>> @dels = sort @dels;
>> push(@body, "Removed:\n");
>> push(@body, map { " $_\n" } @dels);
>> }
>>if (@mods)
>> {
>> @mods = sort @mods;
>> push(@body, "Modified:\n");
>> push(@body, map { " $_\n" } @mods);
>> }
>>push(@body, "Log:\n");
>>push(@body, @log);
>>push(@body, "\n");
>>push(@body, map { /[\r\n]+$/ ? $_ : "$_\n" } @difflines);
>>
>># Go through each project and see if there are any matches for this
>># project. If so, send the log out.
>>foreach my $project (@project_settings_list)
>> {
>>print "$project \n";
>> my $match_re = $project->{match_re};
>> my $match = 0;
>>
>># my @bugid = split /, /, $allbugs
>># for ( $i = 0; $bugid[i] ne "";$i++)
>> foreach my $bugid(@allbugs)
>>
>> {
>> print "$bugid \n";
>># foreach my $path (@dirschanged, @adds, @dels, @mods)
>># {
>># if ( $path =~ $match_re)
>># {
>># $match = 1;
>># last;
>># }
>># }
>>
>># next unless $match;
>>
>> my $username = $project->{username};
>> my $password = $project->{password};
>> my $database = $project->{database};
>> my $hostname = $project->{hostname};
>> my $log_file = $project->{log_file};
>> my $db_type = $project->{db_type};
>>
>>
>> ## Writing data to the Bugzilla database now
>>
>> my $dsn = "dbi:mysql:dbname=$database";
>>
>> my $db_handle = DBI->connect($dsn, $username, $password)
>> or die "Couldn't connect to database: $DBI::errstr\n";
>>
>>#Update the comments / text field on the bug.
>> my $sql = "insert into $database.longdescs values ($bugid,$buguser,now(),0,'@body',0,0)" ;
>>
>> my $statement = $db_handle->prepare($sql)
>> or die "Couldn't prepare insert LONGDESCS query '$sql': $DBI::errstr\n";
>>
>> $statement->execute()
>> or die "Couldn't executre insert LONGDESCS query '$sql': $DBI::errstr\n";
>>
>>#Updat the BUGS_ACTIVITY table to reflect addition of comments.
>>#This is to ensure that ALL changes are reflected in the activity log.
>># (Oh how I wish it was Oracle - could have put a trigger on a table instead of coding here
>># to keep everyone honest. :-)
>># Description of codes : the 33 code defines the field modified. In my shell, it is 33.
>># So I assume it is the same in a default shell because I did not change any field layout (yet).
>># The ADDED reflects the type of modification.
>> my $sql2 = "insert into $database.bugs_activity values ($bugid,NULL,$buguser,now(),33,'ADDED',' '";
>>
>> my $statement2 = $db_handle->prepare($sql)
>> or die "Couldn't prepare insert BUGS_ACTIVITY query '$sql': $DBI::errstr\n";
>>
>> $statement2->execute()
>> or die "Couldn't executre insert BUGS_ACTIVITY query '$sql': $DBI::errstr\n";
>>
>>#Update the BUGS table to set the "last change" timestamp to current time.
>> my $sql3 = "UPDATE $database.bugs SET delta_ts = now() WHERE bug_id = $bugid";
>>
>> my $statement3 = $db_handle->prepare($sql)
>> or die "Couldn't prepare insert BUGS query '$sql': $DBI::errstr\n";
>>
>> $statement3->execute()
>> or die "Couldn't execute insert BUGS query '$sql': $DBI::errstr\n";
>>
>>
>> # Dump the output to logfile (if its name is not empty).
>> if ($log_file =~ /\w/)
>> {
>> if (open(LOGFILE, ">> $log_file"))
>> {
>> print LOGFILE @body;
>> close LOGFILE
>> or warn "$0: error in closing `$log_file' for appending: $!\n";
>> }
>> else
>> {
>> warn "$0: cannot open `$log_file' for appending: $!\n";
>> }
>> }
>> }
>> }
>>
>>exit 0;
>>###############################################################################################
>>###############################################################################################
>>sub usage
>>{
>> warn "@_\n" if @_;
>> die "usage: $0 REPOS REVNUM [[-m regex] [options]] ...\n",
>> "options are\n",
>> " -u username Bugzilla database username(default=bugs)\n",
>> " -p password Password of specified user(default=bugs)\n",
>> " -d db Database schema (default = bugs)\n",
>> " -h hostname Hostname of server running Bugzilla\n",
>> " -l log_file Error log is commit not successful\n",
>> " Default is /tmp/svnbug.log\n",
>> " -m regex Regular expression to match committed path\n",
>> " or project name if using repository root\n",
>> " -t db_type Database type (default=mysql)\n",
>> "\n",
>> "This script supports a single repository with multiple projects,\n",
>> "where each project will be updated only for commits that modify that\n",
>> "project. A project is identified by using the -m command line\n",
>> "with a regular expression argument. If a commit has a path that\n",
>> "matches the regular expression, then the entire commit matches.\n",
>> "\n",
>> "To support a single project conveniently, the script initializes\n",
>> "itself with an implicit -m . rule that matches any modifications\n",
>> "to the repository. Therefore, to use the script for a single\n",
>> "project repository, just use the other comand line options.\n";
>>}
>>
>># Return a new hash data structure for a new empty project that
>># matches any modifications to the repository.
>>sub new_project
>>{
>> return {username => 'bugs',
>> password => 'bugs',
>> database => 'bugs',
>> hostname => 'localhost',
>> log_file => '',
>> match_regex => '.',
>> db_type => 'mysql'};
>>}
>>
>># Start a child process safely without using /bin/sh.
>>sub safe_read_from_pipe
>>{
>> unless (@_)
>> {
>> croak "$0: safe_read_from_pipe passed no arguments.\n";
>> }
>>
>> my $pid = open(SAFE_READ, '-|');
>> unless (defined $pid)
>> {
>> die "$0: cannot fork: $!\n";
>> }
>> unless ($pid)
>> {
>> open(STDERR, ">&STDOUT")
>> or die "$0: cannot dup STDOUT: $!\n";
>> exec(@_)
>> or die "$0: cannot exec `@_': $!\n";
>> }
>> my @output;
>> while (<SAFE_READ>)
>> {
>> s/[\r\n]+$//;
>> push(@output, $_);
>> }
>> close(SAFE_READ);
>> my $result = $?;
>> my $exit = $result >> 8;
>> my $signal = $result & 127;
>> my $cd = $result & 128 ? "with core dump" : "";
>> if ($signal or $cd)
>> {
>> warn "$0: pipe from `@_' failed $cd: exit=$exit signal=$signal\n";
>> }
>> if (wantarray)
>> {
>> return ($result, @output);
>> }
>> else
>> {
>> return $result;
>> }
>>}
>>
>># Use safe_read_from_pipe to start a child process safely and return
>># the output if it succeeded or an error message followed by the output
>># if it failed.
>>sub read_from_process
>>{
>> unless (@_)
>> {
>> croak "$0: read_from_process passed no arguments.\n";
>> }
>> my ($status, @output) = &safe_read_from_pipe(@_);
>> if ($status)
>> {
>> return ("$0: `@_' failed with this output:", @output);
>> }
>> else
>> {
>> return @output;
>> }
>>}
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: users-unsubscribe@subversion.tigris.org
>>For additional commands, e-mail: users-help@subversion.tigris.org
>>
>>
>
>
>
>
Received on Thu Mar 24 17:12:14 2005