[svn.haxx.se] · SVN Dev · SVN Users · SVN Org · TSVN Dev · TSVN Users · Subclipse Dev · Subclipse Users · this month's index

Re: Subversion > Bugzilla integration through MySQL

From: Frank Gruman <fgatwork_at_verizon.net>
Date: 2005-04-29 17:39:05 CEST

I have finally gotten around to fixing up the couple items broken below,
and have a working script.

If anyone wants to find it, you can go to
http://www.thegrumans.net/projects/svn/commit-mysql.pl. It's on my
computer at home, so it won't be reliably fast (or even reliable ;-) ).
If anyone wants to grab it and put it on a real web server/host, feel free.

Here's a brief synopsis of the script and its current limitations:

    * Direct write into Bugzilla MySQL database when commit is done.
    * Writes a full comment with diff of the code changes for that revision.
    * Creates a URL link in the Bugzilla database that can link back to
      the code repository.
    * Current label parsing is set to 16 characters, including spaces
      (Bug / Issue No: )
    * There are several configuration items that can be set at the top
      of the script for repository access.
    * There are several flags that can be passed when calling the script.
          o The default database/username/password for MySQL are
            bugs/bugs/bugs

I have tested this on my test box at work and put it into production on
two different repositories. The environment is:
Bugzilla 2.19.2+
Subversion 1.2.0rc2
MySQL 4.1.10a
TortoiseSVN 1.2.0
    Requires setting the BUGTRAQ:LABEL property with the above label.

Words of warning : This file is provided AS-IS. I am not a Perl
programmer. I am a hack. I kludged this together from various googles
and some of the other files provided with Subversion. If you find
things wrong and can fix them, great! Please educate me. If you find
things wrong and expect me to fix them then you may be waiting a while,
although I would like to know.

Enjoy,
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 Fri Apr 29 17:44:18 2005

This is an archived mail posted to the Subversion Users mailing list.

This site is subject to the Apache Privacy Policy and the Apache Public Forum Archive Policy.