1 package DBIx::Class::DeploymentHandler::DeployMethod::SQL::Translator;
4 # ABSTRACT: Manage your SQL and Perl migrations in nicely laid out directories
7 use Carp qw( carp croak );
8 use DBIx::Class::DeploymentHandler::Logger;
9 use Log::Contextual qw(:log :dlog), -default_logger =>
10 DBIx::Class::DeploymentHandler::Logger->new({
11 env_prefix => 'DBICDH'
14 use Method::Signatures::Simple;
18 require SQL::Translator::Diff;
20 require DBIx::Class::Storage; # loaded for type constraint
21 use DBIx::Class::DeploymentHandler::Types;
23 use File::Path 'mkpath';
24 use File::Spec::Functions;
26 with 'DBIx::Class::DeploymentHandler::HandlesDeploy';
35 isa => 'DBIx::Class::Schema',
41 isa => 'DBIx::Class::Storage',
46 method _build_storage {
47 my $s = $self->schema->storage;
48 $s->_determine_driver;
52 has sql_translator_args => (
55 default => sub { {} },
57 has script_directory => (
66 isa => 'DBIx::Class::DeploymentHandler::Databases',
68 default => sub { [qw( MySQL SQLite PostgreSQL )] },
77 has schema_version => (
83 # this will probably never get called as the DBICDH
84 # will be passing down a schema_version normally, which
85 # is built the same way, but we leave this in place
86 method _build_schema_version { $self->schema->schema_version }
88 method __ddl_consume_with_prefix($type, $versions, $prefix) {
89 my $base_dir = $self->script_directory;
91 my $main = catfile( $base_dir, $type );
92 my $generic = catfile( $base_dir, '_generic' );
94 catfile( $base_dir, '_common', $prefix, join q(-), @{$versions} );
98 $dir = catfile($main, $prefix, join q(-), @{$versions})
99 } elsif (-d $generic) {
100 $dir = catfile($generic, $prefix, join q(-), @{$versions});
102 croak "neither $main or $generic exist; please write/generate some SQL";
107 opendir my($dh), $dir;
109 map { $_ => "$dir/$_" }
110 grep { /\.(?:sql|pl|sql-\w+)$/ && -f "$dir/$_" }
114 die $_ unless $self->ignore_ddl;
117 opendir my($dh), $common;
118 for my $filename (grep { /\.(?:sql|pl)$/ && -f catfile($common,$_) } readdir $dh) {
119 unless ($files{$filename}) {
120 $files{$filename} = catfile($common,$filename);
126 return [@files{sort keys %files}]
129 method _ddl_preinstall_consume_filenames($type, $version) {
130 $self->__ddl_consume_with_prefix($type, [ $version ], 'preinstall')
133 method _ddl_schema_consume_filenames($type, $version) {
134 $self->__ddl_consume_with_prefix($type, [ $version ], 'schema')
137 method _ddl_protoschema_produce_filename($version) {
138 my $dirname = catfile( $self->script_directory, '_protoschema', $version );
139 mkpath($dirname) unless -d $dirname;
141 return catfile( $dirname, '001-auto.yml' );
144 method _ddl_schema_produce_filename($type, $version) {
145 my $dirname = catfile( $self->script_directory, $type, 'schema', $version );
146 mkpath($dirname) unless -d $dirname;
148 return catfile( $dirname, '001-auto.sql' );
151 method _ddl_schema_up_consume_filenames($type, $versions) {
152 $self->__ddl_consume_with_prefix($type, $versions, 'up')
155 method _ddl_schema_down_consume_filenames($type, $versions) {
156 $self->__ddl_consume_with_prefix($type, $versions, 'down')
159 method _ddl_schema_up_produce_filename($type, $versions) {
160 my $dir = $self->script_directory;
162 my $dirname = catfile( $dir, $type, 'up', join q(-), @{$versions});
163 mkpath($dirname) unless -d $dirname;
165 return catfile( $dirname, '001-auto.sql' );
168 method _ddl_schema_down_produce_filename($type, $versions, $dir) {
169 my $dirname = catfile( $dir, $type, 'down', join q(-), @{$versions} );
170 mkpath($dirname) unless -d $dirname;
172 return catfile( $dirname, '001-auto.sql');
175 method _run_sql_array($sql) {
176 my $storage = $self->storage;
179 $_ && # remove blank lines
180 !/^(BEGIN|BEGIN TRANSACTION|COMMIT)/ # strip txn's
182 s/^\s+//; s/\s+$//; # trim whitespace
183 join '', grep { !/^--/ } split /\n/ # remove comments
186 Dlog_trace { "Running SQL $_" } $sql;
187 foreach my $line (@{$sql}) {
188 $storage->_query_start($line);
189 # the whole reason we do this is so that we can see the line that was run
191 $storage->dbh_do (sub { $_[1]->do($line) });
194 die "$_ (running line '$line')"
196 $storage->_query_end($line);
198 return join "\n", @$sql
201 method _run_sql($filename) {
202 log_debug { "Running SQL from $filename" };
203 return $self->_run_sql_array($self->_read_sql_file($filename));
206 method _run_perl($filename) {
207 log_debug { "Running Perl from $filename" };
208 my $filedata = do { local( @ARGV, $/ ) = $filename; <> };
210 no warnings 'redefine';
211 my $fn = eval "$filedata";
213 Dlog_trace { "Running Perl $_" } $fn;
216 carp "$filename failed to compile: $@";
217 } elsif (ref $fn eq 'CODE') {
220 carp "$filename should define an anonymouse sub that takes a schema but it didn't!";
224 method _run_sql_and_perl($filenames, $sql_to_run) {
225 my @files = @{$filenames};
226 my $guard = $self->schema->txn_scope_guard if $self->txn_wrap;
228 $self->_run_sql_array($sql_to_run) if $self->ignore_ddl;
230 my $sql = ($sql_to_run)?join ";\n", @$sql_to_run:'';
232 for my $filename (@files) {
233 if ($self->ignore_ddl && $filename =~ /^[^_]*-auto.*\.sql$/) {
235 } elsif ($filename =~ /\.sql$/) {
236 $sql .= $self->_run_sql($filename)
237 } elsif ( $filename =~ /\.pl$/ ) {
238 $self->_run_perl($filename)
240 croak "A file ($filename) got to deploy that wasn't sql or perl!";
244 $guard->commit if $self->txn_wrap;
251 my $version = (shift @_ || {})->{version} || $self->schema_version;
252 log_info { "deploying version $version" };
253 my $sqlt_type = $self->storage->sqlt_type;
255 if ($self->ignore_ddl) {
256 $sql = $self->_sql_from_yaml({},
257 '_ddl_protoschema_produce_filename', $sqlt_type
260 return $self->_run_sql_and_perl($self->_ddl_schema_consume_filenames(
269 my $version = $args->{version} || $self->schema_version;
270 log_info { "preinstalling version $version" };
271 my $storage_type = $args->{storage_type} || $self->storage->sqlt_type;
273 my @files = @{$self->_ddl_preinstall_consume_filenames(
278 for my $filename (@files) {
279 # We ignore sql for now (till I figure out what to do with it)
280 if ( $filename =~ /^(.+)\.pl$/ ) {
281 my $filedata = do { local( @ARGV, $/ ) = $filename; <> };
283 no warnings 'redefine';
284 my $fn = eval "$filedata";
288 carp "$filename failed to compile: $@";
289 } elsif (ref $fn eq 'CODE') {
292 carp "$filename should define an anonymous sub but it didn't!";
295 croak "A file ($filename) got to preinstall_scripts that wasn't sql or perl!";
300 method _sqldiff_from_yaml($from_version, $to_version, $db) {
301 my $dir = $self->script_directory;
304 ignore_constraint_names => 1,
305 ignore_index_names => 1,
306 %{$self->sql_translator_args}
311 my $prefilename = $self->_ddl_protoschema_produce_filename($from_version, $dir);
313 # should probably be a croak
314 carp("No previous schema file found ($prefilename)")
315 unless -e $prefilename;
317 my $t = SQL::Translator->new({
321 parser => 'SQL::Translator::Parser::YAML',
324 my $out = $t->translate( $prefilename )
327 $source_schema = $t->schema;
329 $source_schema->name( $prefilename )
330 unless $source_schema->name;
335 my $filename = $self->_ddl_protoschema_produce_filename($to_version, $dir);
337 # should probably be a croak
338 carp("No next schema file found ($filename)")
341 my $t = SQL::Translator->new({
345 parser => 'SQL::Translator::Parser::YAML',
348 my $out = $t->translate( $filename )
351 $dest_schema = $t->schema;
353 $dest_schema->name( $filename )
354 unless $dest_schema->name;
356 return [SQL::Translator::Diff::schema_diff(
363 method _sql_from_yaml($sqltargs, $from_file, $db) {
364 my $schema = $self->schema;
365 my $version = $self->schema_version;
367 my $sqlt = SQL::Translator->new({
369 parser => 'SQL::Translator::Parser::YAML',
374 my $yaml_filename = $self->$from_file($version);
376 my @sql = $sqlt->translate($yaml_filename);
378 carp("Failed to translate to $db, skipping. (" . $sqlt->error . ")");
384 sub _prepare_install {
386 my $sqltargs = { %{$self->sql_translator_args}, %{shift @_} };
387 my $from_file = shift;
389 my $dir = $self->script_directory;
390 my $databases = $self->databases;
391 my $version = $self->schema_version;
393 foreach my $db (@$databases) {
394 my $sql = $self->_sql_from_yaml($sqltargs, $from_file, $db ) or next;
396 my $filename = $self->$to_file($db, $version, $dir);
398 carp "Overwriting existing DDL file - $filename";
401 open my $file, q(>), $filename;
402 print {$file} join ";\n", @$sql;
407 sub _resultsource_install_filename {
408 my ($self, $source_name) = @_;
410 my ($self, $type, $version) = @_;
411 my $dirname = catfile( $self->script_directory, $type, 'schema', $version );
412 mkpath($dirname) unless -d $dirname;
414 return catfile( $dirname, "001-auto-$source_name.sql" );
418 sub _resultsource_protoschema_filename {
419 my ($self, $source_name) = @_;
421 my ($self, $version) = @_;
422 my $dirname = catfile( $self->script_directory, '_protoschema', $version );
423 mkpath($dirname) unless -d $dirname;
425 return catfile( $dirname, "001-auto-$source_name.yml" );
429 sub install_resultsource {
430 my ($self, $args) = @_;
431 my $source = $args->{result_source};
432 my $version = $args->{version};
433 log_info { 'installing_resultsource ' . $source->source_name . ", version $version" };
434 my $rs_install_file =
435 $self->_resultsource_install_filename($source->source_name);
438 $self->$rs_install_file(
439 $self->storage->sqlt_type,
443 $self->_run_sql_and_perl($files);
446 sub prepare_resultsource_install {
448 my $source = (shift @_)->{result_source};
449 log_info { 'preparing install for resultsource ' . $source->source_name };
451 my $install_filename = $self->_resultsource_install_filename($source->source_name);
452 my $proto_filename = $self->_resultsource_protoschema_filename($source->source_name);
453 $self->prepare_protoschema({
454 parser_args => { sources => [$source->source_name], }
456 $self->_prepare_install({}, $proto_filename, $install_filename);
460 log_info { 'preparing deploy' };
462 $self->prepare_protoschema({}, '_ddl_protoschema_produce_filename');
463 $self->_prepare_install({}, '_ddl_protoschema_produce_filename', '_ddl_schema_produce_filename');
466 sub prepare_upgrade {
467 my ($self, $args) = @_;
469 "preparing upgrade from $args->{from_version} to $args->{to_version}"
471 $self->_prepare_changegrade(
472 $args->{from_version}, $args->{to_version}, $args->{version_set}, 'up'
476 sub prepare_downgrade {
477 my ($self, $args) = @_;
479 "preparing downgrade from $args->{from_version} to $args->{to_version}"
481 $self->_prepare_changegrade(
482 $args->{from_version}, $args->{to_version}, $args->{version_set}, 'down'
486 method _prepare_changegrade($from_version, $to_version, $version_set, $direction) {
487 my $schema = $self->schema;
488 my $databases = $self->databases;
489 my $dir = $self->script_directory;
491 my $schema_version = $self->schema_version;
492 my $diff_file_method = "_ddl_schema_${direction}_produce_filename";
493 foreach my $db (@$databases) {
494 my $diff_file = $self->$diff_file_method($db, $version_set, $dir );
496 carp("Overwriting existing $direction-diff file - $diff_file");
500 open my $file, q(>), $diff_file;
501 print {$file} join ";\n", @{$self->_sqldiff_from_yaml($from_version, $to_version, $db)};
506 method _read_sql_file($file) {
509 open my $fh, '<', $file;
510 my @data = split /;\n/, join '', <$fh>;
514 $_ && # remove blank lines
515 !/^(BEGIN|BEGIN TRANSACTION|COMMIT)/ # strip txn's
517 s/^\s+//; s/\s+$//; # trim whitespace
518 join '', grep { !/^--/ } split /\n/ # remove comments
524 sub downgrade_single_step {
526 my $version_set = (shift @_)->{version_set};
527 Dlog_info { "downgrade_single_step'ing $_" } $version_set;
529 my $sqlt_type = $self->storage->sqlt_type;
531 if ($self->ignore_ddl) {
532 $sql_to_run = $self->_sqldiff_from_yaml(
533 $version_set->[0], $version_set->[1], $sqlt_type
536 my $sql = $self->_run_sql_and_perl($self->_ddl_schema_down_consume_filenames(
544 sub upgrade_single_step {
546 my $version_set = (shift @_)->{version_set};
547 Dlog_info { "upgrade_single_step'ing $_" } $version_set;
549 my $sqlt_type = $self->storage->sqlt_type;
551 if ($self->ignore_ddl) {
552 $sql_to_run = $self->_sqldiff_from_yaml(
553 $version_set->[0], $version_set->[1], $sqlt_type
556 my $sql = $self->_run_sql_and_perl($self->_ddl_schema_up_consume_filenames(
563 sub prepare_protoschema {
565 my $sqltargs = { %{$self->sql_translator_args}, %{shift @_} };
568 = $self->$to_file($self->schema_version);
570 # we do this because the code that uses this sets parser args,
571 # so we just need to merge in the package
572 $sqltargs->{parser_args}{package} = $self->schema;
573 my $sqlt = SQL::Translator->new({
574 parser => 'SQL::Translator::Parser::DBIx::Class',
575 producer => 'SQL::Translator::Producer::YAML',
579 my $yml = $sqlt->translate;
581 croak("Failed to translate to YAML: " . $sqlt->error)
585 carp "Overwriting existing DDL-YML file - $filename";
589 open my $file, q(>), $filename;
594 __PACKAGE__->meta->make_immutable;
598 # vim: ts=2 sw=2 expandtab
604 This class is the meat of L<DBIx::Class::DeploymentHandler>. It takes care
605 of generating serialized schemata as well as sql files to move from one
606 version of a schema to the rest. One of the hallmark features of this class
607 is that it allows for multiple sql files for deploy and upgrade, allowing
608 developers to fine tune deployment. In addition it also allows for perl
609 files to be run at any stage of the process.
611 For basic usage see L<DBIx::Class::DeploymentHandler::HandlesDeploy>. What's
612 documented here is extra fun stuff or private methods.
614 =head1 DIRECTORY LAYOUT
616 Arguably this is the best feature of L<DBIx::Class::DeploymentHandler>. It's
617 heavily based upon L<DBIx::Migration::Directories>, but has some extensions and
618 modifications, so even if you are familiar with it, please read this. I feel
619 like the best way to describe the layout is with the following example:
637 | | `- 002-remove-customers.pl
640 | `- 002-generate-customers.pl
651 | `- 002-create-stored-procedures.sql
658 | |- 001-create_database.pl
659 | `- 002-create_users_and_permissions.pl
667 So basically, the code
671 on an C<SQLite> database that would simply run
672 C<$sql_migration_dir/SQLite/schema/1/001-auto.sql>. Next,
674 $dm->upgrade_single_step([1,2])
676 would run C<$sql_migration_dir/SQLite/up/1-2/001-auto.sql> followed by
677 C<$sql_migration_dir/_common/up/1-2/002-generate-customers.pl>.
679 C<.pl> files don't have to be in the C<_common> directory, but most of the time
680 they should be, because perl scripts are generally be database independent.
682 C<_generic> exists for when you for some reason are sure that your SQL is
683 generic enough to run on all databases. Good luck with that one.
685 Note that unlike most steps in the process, C<preinstall> will not run SQL, as
686 there may not even be an database at preinstall time. It will run perl scripts
687 just like the other steps in the process, but nothing is passed to them.
688 Until people have used this more it will remain freeform, but a recommended use
689 of preinstall is to have it prompt for username and password, and then call the
690 appropriate C<< CREATE DATABASE >> commands etc.
694 A perl script for this tool is very simple. It merely needs to contain an
695 anonymous sub that takes a L<DBIx::Class::Schema> as it's only argument.
696 A very basic perl script might look like:
706 $schema->resultset('Users')->create({
714 The L<DBIx::Class::Schema> (B<required>) that is used to talk to the database
715 and generate the DDL.
719 The L<DBIx::Class::Storage> that is I<actually> used to talk to the database
720 and generate the DDL. This is automatically created with L</_build_storage>.
722 =attr sql_translator_args
724 The arguments that get passed to L<SQL::Translator> when it's used.
726 =attr script_directory
728 The directory (default C<'sql'>) that scripts are stored in
732 The types of databases (default C<< [qw( MySQL SQLite PostgreSQL )] >>) to
737 Set to true (which is the default) to wrap all upgrades and deploys in a single
742 The version the schema on your harddrive is at. Defaults to
743 C<< $self->schema->schema_version >>.
747 =head2 __ddl_consume_with_prefix
749 $dm->__ddl_consume_with_prefix( 'SQLite', [qw( 1.00 1.01 )], 'up' )
751 This is the meat of the multi-file upgrade/deploy stuff. It returns a list of
752 files in the order that they should be run for a generic "type" of upgrade.
753 You should not be calling this in user code.
755 =head2 _ddl_schema_consume_filenames
757 $dm->__ddl_schema_consume_filenames( 'SQLite', [qw( 1.00 )] )
759 Just a curried L</__ddl_consume_with_prefix>. Get's a list of files for an
762 =head2 _ddl_schema_produce_filename
764 $dm->__ddl_schema_produce_filename( 'SQLite', [qw( 1.00 )] )
766 Returns a single file in which an initial schema will be stored.
768 =head2 _ddl_schema_up_consume_filenames
770 $dm->_ddl_schema_up_consume_filenames( 'SQLite', [qw( 1.00 )] )
772 Just a curried L</__ddl_consume_with_prefix>. Get's a list of files for an
775 =head2 _ddl_schema_down_consume_filenames
777 $dm->_ddl_schema_down_consume_filenames( 'SQLite', [qw( 1.00 )] )
779 Just a curried L</__ddl_consume_with_prefix>. Get's a list of files for a
782 =head2 _ddl_schema_up_produce_filenames
784 $dm->_ddl_schema_up_produce_filename( 'SQLite', [qw( 1.00 1.01 )] )
786 Returns a single file in which the sql to upgrade from one schema to another
789 =head2 _ddl_schema_down_produce_filename
791 $dm->_ddl_schema_down_produce_filename( 'SQLite', [qw( 1.00 1.01 )] )
793 Returns a single file in which the sql to downgrade from one schema to another
796 =head2 _resultsource_install_filename
798 my $filename_fn = $dm->_resultsource_install_filename('User');
799 $dm->$filename_fn('SQLite', '1.00')
801 Returns a function which in turn returns a single filename used to install a
802 single resultsource. Weird interface is convenient for me. Deal with it.
804 =head2 _run_sql_and_perl
806 $dm->_run_sql_and_perl([qw( list of filenames )])
808 Simply put, this runs the list of files passed to it. If the file ends in
809 C<.sql> it runs it as sql and if it ends in C<.pl> it runs it as a perl file.
811 Depending on L</txn_wrap> all of the files run will be wrapped in a single
814 =head2 _prepare_install
816 $dm->_prepare_install({ add_drop_table => 0 }, sub { 'file_to_create' })
818 Generates the sql file for installing the database. First arg is simply
819 L<SQL::Translator> args and the second is a coderef that returns the filename
822 =head2 _prepare_changegrade
824 $dm->_prepare_changegrade('1.00', '1.01', [qw( 1.00 1.01)], 'up')
826 Generates the sql file for migrating from one schema version to another. First
827 arg is the version to start from, second is the version to go to, third is the
828 L<version set|DBIx::Class::DeploymentHandler/VERSION SET>, and last is the
829 direction of the changegrade, be it 'up' or 'down'.
831 =head2 _read_sql_file
833 $dm->_read_sql_file('foo.sql')
835 Reads a sql file and returns lines in an C<ArrayRef>. Strips out comments,
836 transactions, and blank lines.