[Dbix-class] DBIx::Class::Fixtures suggestions/patches
Drew Taylor
taylor.andrew.j at gmail.com
Mon May 19 10:11:38 BST 2008
(warning long post)
I have written a Fixtures wrapper for my particular workflow for use
at $work, along with some customizations to D::C::Fixtures and wanted
to get opinions about pushing them to svn. To get a big performance
boost for population I'm now using the following procedure when
creating a dump:
1) Dump from source database using Fixtures
2) Populate dump into fixtures database
3) Run pg_dump from fixtures database into separate dump file
4) Delete fixtures dump dir (actually using File::Temp::tempdir)
sub dump
{
my $self = shift;
my $params = {...};
$self->fixtures()->dump($params);
# populate the data in to the fixtures db
$self->fixtures()->populate({
directory => $data_dir,
ddl => 'name.sql'),
post_ddl => 'name_post.sql'),
cascade => 1,
connection_details => [...],
});
my $dump_file = $self->_full_dump_file();
unlink $dump_file if -e $dump_file; # pg_dump won't overwrite existing file
system("pg_dump ...");
}
And population goes like:
1) clear fixtures database
2) import pg_dump file
3) run post_ddl bits
sub populate # (my wrapper)
{
my $self = shift;
# clear existing data
my $schema = $self->fixtures()->_generate_schema({
ddl => 'name.sql',
cascade => 1,
connection_details => [...'],
});
# import dump data
$self->import_pg_dump_file($self->_full_dump_file());
# add constraints/fk
$self->fixtures()->do_post_ddl({
schema => $schema, post_ddl => 'name_post.sql'),
});
}
When I populate I just read in the pg_dump file, resulting in test
runs in 1/4 of the time (12-15 seconds to populate & run a test file
vs 45 seconds doing populate via DBIx::Class::Fixtures::populate().
This is not a criticism of Fixtures, because I love the ability to
easily specify what database tables/rows to dump. I was wondering
about creating an alternate set of dump/populate methods which would
encapsulate this workflow, but be db agnostic. It dramatically sped up
my tests, so much so that I'm likely to run them more often now. :-)
Some other changes I've made locally:
1. Move the post_ddl bits into a separate method. I then call this
method from my wrapper. Diff against latest 1.001/trunk:
--- Fixtures.pm 2008-05-16 14:50:12.000000000 +0100
+++ Fixtures.pm.proposed 2008-05-19 09:55:42.000000000 +0100
@@ -853,14 +853,7 @@
$rs->populate(\@rows);
}
- if ($params->{post_ddl}) {
- my $data = _read_sql($params->{post_ddl});
- foreach (@$data) {
- eval { $schema->storage->dbh->do($_) or warn "SQL was:\n $_"};
- if ($@) { die "SQL was:\n $_\n$@"; }
- }
- $self->msg("- finished importing post-populate DDL into DB");
- }
+ $self->do_post_ddl({schema=>$schema,
post_ddl=>$params->{post_ddl}}) if $params->{post_ddl};
$self->msg("- fixtures imported");
$self->msg("- cleaning up");
@@ -870,6 +863,18 @@
return 1;
}
+sub do_post_ddl {
+ my ($self, $params) = @_;
+
+ my $schema = $params->{schema};
+ my $data = _read_sql($params->{post_ddl});
+ foreach (@$data) {
+ eval { $schema->storage->dbh->do($_) or warn "SQL was:\n $_"};
+ if ($@) { die "SQL was:\n $_\n$@"; }
+ }
+ $self->msg("- finished importing post-populate DDL into DB");
+}
+
sub msg {
my $self = shift;
my $subject = shift || return;
2. Support for "grouping" in ddl files. I ran into a major problem
where some of my ddl files had stored procedures with semicolons. The
original _read_sql(), while clever and simple, would break horribly
with the embedded semicolons in my procedures. I added (crude) support
for grouping to avoid this problem. The DDL has comments which trigger
the feature.
--GROUPBEGIN
CREATE OR REPLACE FUNCTION booking_last_modified_lcupdate() RETURNS "trigger"
AS $$ begin NEW.last_modified = now(); return NEW; end; $$
LANGUAGE plpgsql;
--GROUPEND
The diff (also against 1.001/trunk)
--- Fixtures.pm 2008-05-16 14:50:12.000000000 +0100
+++ Fixtures.pm.proposed 2008-05-19 10:02:34.000000000 +0100
@@ -726,11 +726,43 @@
my $fh;
open $fh, "<$ddl_file" or die ("Can't open DDL file, $ddl_file ($!)");
my @data = split(/\n/, join('', <$fh>));
- @data = grep(!/^--/, @data);
- @data = split(/;/, join('', @data));
close($fh);
- @data = grep { $_ && $_ !~ /^-- / } @data;
- return \@data;
+
+ my @commands;
+ my $group_started = 0;
+ my $buffer = '';
+ foreach my $line (@data)
+ {
+ chomp $line;
+ my ($group) = $line =~ /--GROUP(BEGIN|END)/;
+ $group ||= '';
+ if ($group) {
+ $group_started = $group eq 'BEGIN' ? 1 : 0;
+ }
+
+ $line =~ s/--.*//; # remove comments
+
+ # when closing a group, flush on the "END" command
+ if ($group_started || $group) {
+ $buffer .= $line;
+ if ($group eq 'END') {
+ push @commands, $buffer;
+ $buffer = '';
+ }
+ }
+ else {
+ while ($line && $line =~ s/(.*;?)//) {
+ my $cmd = $1;
+ $buffer .= $cmd;
+ # warn " [$cmd] [$line] [$buffer]\n";
+ if ($cmd =~ /;/) {
+ push @commands, $buffer;
+ $buffer = '';
+ }
+ }
+ }
+ }
+ return \@commands;
}
=head2 populate
Thoughts? (and thanks for reading this far)
--
----------------------------------------------------------------
Drew Taylor * Web development & consulting
Email: drew at drewtaylor.com * Site implementation & hosting
Web : www.drewtaylor.com * perl/mod_perl/DBI/mysql/postgres
----------------------------------------------------------------
More information about the DBIx-Class
mailing list