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 );
9 use Method::Signatures::Simple;
13 require SQL::Translator::Diff;
15 require DBIx::Class::Storage; # loaded for type constraint
16 use DBIx::Class::DeploymentHandler::Types;
18 use File::Path 'mkpath';
19 use File::Spec::Functions;
21 with 'DBIx::Class::DeploymentHandler::HandlesDeploy';
24 isa => 'DBIx::Class::Schema',
30 isa => 'DBIx::Class::Storage',
35 method _build_storage {
36 my $s = $self->schema->storage;
37 $s->_determine_driver;
41 has sql_translator_args => (
44 default => sub { {} },
46 has upgrade_directory => (
55 isa => 'DBIx::Class::DeploymentHandler::Databases',
57 default => sub { [qw( MySQL SQLite PostgreSQL )] },
66 has schema_version => (
71 method _build_schema_version { $self->schema->schema_version }
73 method __ddl_consume_with_prefix($type, $versions, $prefix) {
74 my $base_dir = $self->upgrade_directory;
76 my $main = catfile( $base_dir, $type );
77 my $generic = catfile( $base_dir, '_generic' );
79 catfile( $base_dir, '_common', $prefix, join q(-), @{$versions} );
83 $dir = catfile($main, $prefix, join q(-), @{$versions})
84 } elsif (-d $generic) {
85 $dir = catfile($generic, $prefix, join q(-), @{$versions});
87 croak "neither $main or $generic exist; please write/generate some SQL";
90 opendir my($dh), $dir;
91 my %files = map { $_ => "$dir/$_" } grep { /\.(?:sql|pl)$/ && -f "$dir/$_" } readdir $dh;
95 opendir my($dh), $common;
96 for my $filename (grep { /\.(?:sql|pl)$/ && -f catfile($common,$_) } readdir $dh) {
97 unless ($files{$filename}) {
98 $files{$filename} = catfile($common,$filename);
104 return [@files{sort keys %files}]
107 method _ddl_schema_consume_filenames($type, $version) {
108 $self->__ddl_consume_with_prefix($type, [ $version ], 'schema')
111 method _ddl_schema_produce_filename($type, $version) {
112 my $dirname = catfile( $self->upgrade_directory, $type, 'schema', $version );
113 mkpath($dirname) unless -d $dirname;
115 return catfile( $dirname, '001-auto.sql' );
118 method _ddl_schema_up_consume_filenames($type, $versions) {
119 $self->__ddl_consume_with_prefix($type, $versions, 'up')
122 method _ddl_schema_down_consume_filenames($type, $versions) {
123 $self->__ddl_consume_with_prefix($type, $versions, 'down')
126 method _ddl_schema_up_produce_filename($type, $versions) {
127 my $dir = $self->upgrade_directory;
129 my $dirname = catfile( $dir, $type, 'up', join q(-), @{$versions});
130 mkpath($dirname) unless -d $dirname;
132 return catfile( $dirname, '001-auto.sql'
136 method _ddl_schema_down_produce_filename($type, $versions, $dir) {
137 my $dirname = catfile( $dir, $type, 'down', join q(-), @{$versions} );
138 mkpath($dirname) unless -d $dirname;
140 return catfile( $dirname, '001-auto.sql');
143 method _run_sql_and_perl($filenames) {
144 my @files = @{$filenames};
145 my $storage = $self->storage;
148 my $guard = $self->schema->txn_scope_guard if $self->txn_wrap;
151 for my $filename (@files) {
152 if ($filename =~ /\.sql$/) {
153 my @sql = @{$self->_read_sql_file($filename)};
154 $sql .= join "\n", @sql;
156 foreach my $line (@sql) {
157 $storage->_query_start($line);
159 # do a dbh_do cycle here, as we need some error checking in
160 # place (even though we will ignore errors)
161 $storage->dbh_do (sub { $_[1]->do($line) });
164 carp "$_ (running '${line}')"
166 $storage->_query_end($line);
168 } elsif ( $filename =~ /^(.+)\.pl$/ ) {
170 my $filedata = do { local( @ARGV, $/ ) = $filename; <> };
171 # make the package name more palateable to perl
172 $package =~ s/\W/_/g;
174 no warnings 'redefine';
175 eval "package $package;\n\n$filedata";
178 if (my $fn = $package->can('run')) {
179 $fn->($self->schema);
181 carp "$filename should define a run method that takes a schema but it didn't!";
184 croak "A file got to deploy that wasn't sql or perl!";
188 $guard->commit if $self->txn_wrap;
195 my $version = shift || $self->schema_version;
197 return $self->_run_sql_and_perl($self->_ddl_schema_consume_filenames(
198 $self->storage->sqlt_type,
203 sub _prepare_install {
205 my $sqltargs = { %{$self->sql_translator_args}, %{shift @_} };
207 my $schema = $self->schema;
208 my $databases = $self->databases;
209 my $dir = $self->upgrade_directory;
210 my $version = $self->schema_version;
212 my $sqlt = SQL::Translator->new({
214 ignore_constraint_names => 1,
215 ignore_index_names => 1,
216 parser => 'SQL::Translator::Parser::DBIx::Class',
220 my $sqlt_schema = $sqlt->translate( data => $schema )
221 or croak($sqlt->error);
223 foreach my $db (@$databases) {
225 $sqlt->{schema} = $sqlt_schema;
226 $sqlt->producer($db);
228 my $filename = $self->$to_file($db, $version, $dir);
230 carp "Overwriting existing DDL file - $filename";
234 my $output = $sqlt->translate;
236 carp("Failed to translate to $db, skipping. (" . $sqlt->error . ")");
239 open my $file, q(>), $filename;
240 print {$file} $output;
245 sub _resultsource_install_filename {
246 my ($self, $source_name) = @_;
248 my ($self, $type, $version) = @_;
249 my $dirname = catfile( $self->upgrade_directory, $type, 'schema', $version );
250 mkpath($dirname) unless -d $dirname;
252 return catfile( $dirname, "001-auto-$source_name.sql" );
256 sub install_resultsource {
257 my ($self, $source, $version) = @_;
259 my $rs_install_file =
260 $self->_resultsource_install_filename($source->source_name);
263 $self->$rs_install_file(
264 $self->storage->sqlt_type,
268 $self->_run_sql_and_perl($files);
271 sub prepare_resultsource_install {
275 my $filename = $self->_resultsource_install_filename($source->source_name);
276 $self->_prepare_install({
277 parser_args => { sources => [$source->source_name], }
283 $self->_prepare_install({}, '_ddl_schema_produce_filename');
286 sub prepare_upgrade {
287 my ($self, $from_version, $to_version, $version_set) = @_;
288 $self->_prepare_changegrade($from_version, $to_version, $version_set, 'up');
291 sub prepare_downgrade {
292 my ($self, $from_version, $to_version, $version_set) = @_;
294 $self->_prepare_changegrade($from_version, $to_version, $version_set, 'down');
297 method _prepare_changegrade($from_version, $to_version, $version_set, $direction) {
298 my $schema = $self->schema;
299 my $databases = $self->databases;
300 my $dir = $self->upgrade_directory;
301 my $sqltargs = $self->sql_translator_args;
303 my $schema_version = $self->schema_version;
307 ignore_constraint_names => 1,
308 ignore_index_names => 1,
312 my $sqlt = SQL::Translator->new( $sqltargs );
314 $sqlt->parser('SQL::Translator::Parser::DBIx::Class');
315 my $sqlt_schema = $sqlt->translate( data => $schema )
316 or croak($sqlt->error);
318 foreach my $db (@$databases) {
320 $sqlt->{schema} = $sqlt_schema;
321 $sqlt->producer($db);
323 my $prefilename = $self->_ddl_schema_produce_filename($db, $from_version, $dir);
324 unless(-e $prefilename) {
325 carp("No previous schema file found ($prefilename)");
328 my $diff_file_method = "_ddl_schema_${direction}_produce_filename";
329 my $diff_file = $self->$diff_file_method($db, $version_set, $dir );
331 carp("Overwriting existing $direction-diff file - $diff_file");
337 my $t = SQL::Translator->new({
343 $t->parser( $db ) # could this really throw an exception?
346 my $out = $t->translate( $prefilename )
349 $source_schema = $t->schema;
351 $source_schema->name( $prefilename )
352 unless $source_schema->name;
355 # The "new" style of producers have sane normalization and can support
356 # diffing a SQL file against a DBIC->SQLT schema. Old style ones don't
357 # And we have to diff parsed SQL against parsed SQL.
358 my $dest_schema = $sqlt_schema;
360 unless ( "SQL::Translator::Producer::$db"->can('preprocess_schema') ) {
361 my $t = SQL::Translator->new({
367 $t->parser( $db ) # could this really throw an exception?
370 my $filename = $self->_ddl_schema_produce_filename($db, $to_version, $dir);
371 my $out = $t->translate( $filename )
374 $dest_schema = $t->schema;
376 $dest_schema->name( $filename )
377 unless $dest_schema->name;
380 my $diff = SQL::Translator::Diff::schema_diff(
385 open my $file, q(>), $diff_file;
391 method _read_sql_file($file) {
394 open my $fh, '<', $file;
395 my @data = split /;\n/, join '', <$fh>;
399 $_ && # remove blank lines
400 !/^(BEGIN|BEGIN TRANSACTION|COMMIT)/ # strip txn's
402 s/^\s+//; s/\s+$//; # trim whitespace
403 join '', grep { !/^--/ } split /\n/ # remove comments
409 sub downgrade_single_step {
411 my $version_set = shift @_;
413 my $sql = $self->_run_sql_and_perl($self->_ddl_schema_down_consume_filenames(
414 $self->storage->sqlt_type,
421 sub upgrade_single_step {
423 my $version_set = shift @_;
425 my $sql = $self->_run_sql_and_perl($self->_ddl_schema_up_consume_filenames(
426 $self->storage->sqlt_type,
432 __PACKAGE__->meta->make_immutable;
436 # vim: ts=2 sw=2 expandtab
442 This class is the meat of L<DBIx::Class::DeploymentHandler>. It takes care of
443 generating sql files representing schemata as well as sql files to move from
444 one version of a schema to the rest. One of the hallmark features of this
445 class is that it allows for multiple sql files for deploy and upgrade, allowing
446 developers to fine tune deployment. In addition it also allows for perl files
447 to be run at any stage of the process.
449 For basic usage see L<DBIx::Class::DeploymentHandler::HandlesDeploy>. What's
450 documented here is extra fun stuff or private methods.
452 =head1 DIRECTORY LAYOUT
454 Arguably this is the best feature of L<DBIx::Class::DeploymentHandler>. It's
455 heavily based upon L<DBIx::Migration::Directories>, but has some extensions and
456 modifications, so even if you are familiar with it, please read this. I feel
457 like the best way to describe the layout is with the following example:
475 | | `- 002-remove-customers.pl
478 | `- 002-generate-customers.pl
489 | `- 002-create-stored-procedures.sql
501 So basically, the code
505 on an C<SQLite> database that would simply run
506 C<$sql_migration_dir/SQLite/schema/1/001-auto.sql>. Next,
508 $dm->upgrade_single_step([1,2])
510 would run C<$sql_migration_dir/SQLite/up/1-2/001-auto.sql> followed by
511 C<$sql_migration_dir/_common/up/1-2/002-generate-customers.pl>.
513 Now, a C<.pl> file doesn't have to be in the C<_common> directory, but most of
514 the time it probably should be, since perl scripts will mostly be database
517 C<_generic> exists for when you for some reason are sure that your SQL is
518 generic enough to run on all databases. Good luck with that one.
522 A perl script for this tool is very simple. It merely needs to contain a
523 sub called C<run> that takes a L<DBIx::Class::Schema> as it's only argument.
524 A very basic perl script might look like:
534 $schema->resultset('Users')->create({
542 The L<DBIx::Class::Schema> (B<required>) that is used to talk to the database
543 and generate the DDL.
547 The L<DBIx::Class::Storage> that is I<actually> used to talk to the database
548 and generate the DDL. This is automatically created with L</_build_storage>.
550 =attr sql_translator_args
552 The arguments that get passed to L<SQL::Translator> when it's used.
554 =attr upgrade_directory
556 The directory (default C<'sql'>) that upgrades are stored in
560 The types of databases (default C<< [qw( MySQL SQLite PostgreSQL )] >>) to
565 Set to true (which is the default) to wrap all upgrades and deploys in a single
570 The version the schema on your harddrive is at. Defaults to
571 C<< $self->schema->schema_version >>.
573 =method __ddl_consume_with_prefix
575 $dm->__ddl_consume_with_prefix( 'SQLite', [qw( 1.00 1.01 )], 'up' )
577 This is the meat of the multi-file upgrade/deploy stuff. It returns a list of
578 files in the order that they should be run for a generic "type" of upgrade.
579 You should not be calling this in user code.
581 =method _ddl_schema_consume_filenames
583 $dm->__ddl_schema_consume_filenames( 'SQLite', [qw( 1.00 )] )
585 Just a curried L</__ddl_consume_with_prefix>. Get's a list of files for an
588 =method _ddl_schema_produce_filename
590 $dm->__ddl_schema_produce_filename( 'SQLite', [qw( 1.00 )] )
592 Returns a single file in which an initial schema will be stored.
594 =method _ddl_schema_up_consume_filenames
596 $dm->_ddl_schema_up_consume_filenames( 'SQLite', [qw( 1.00 )] )
598 Just a curried L</__ddl_consume_with_prefix>. Get's a list of files for an
601 =method _ddl_schema_down_consume_filenames
603 $dm->_ddl_schema_down_consume_filenames( 'SQLite', [qw( 1.00 )] )
605 Just a curried L</__ddl_consume_with_prefix>. Get's a list of files for a
608 =method _ddl_schema_up_produce_filenames
610 $dm->_ddl_schema_up_produce_filename( 'SQLite', [qw( 1.00 1.01 )] )
612 Returns a single file in which the sql to upgrade from one schema to another
615 =method _ddl_schema_down_produce_filename
617 $dm->_ddl_schema_down_produce_filename( 'SQLite', [qw( 1.00 1.01 )] )
619 Returns a single file in which the sql to downgrade from one schema to another
622 =method _resultsource_install_filename
624 my $filename_fn = $dm->_resultsource_install_filename('User');
625 $dm->$filename_fn('SQLite', '1.00')
627 Returns a function which in turn returns a single filename used to install a
628 single resultsource. Weird interface is convenient for me. Deal with it.
630 =method _run_sql_and_perl
632 $dm->_run_sql_and_perl([qw( list of filenames )])
634 Simply put, this runs the list of files passed to it. If the file ends in
635 C<.sql> it runs it as sql and if it ends in C<.pl> it runs it as a perl file.
637 Depending on L</txn_wrap> all of the files run will be wrapped in a single
640 =method _prepare_install
642 $dm->_prepare_install({ add_drop_table => 0 }, sub { 'file_to_create' })
644 Generates the sql file for installing the database. First arg is simply
645 L<SQL::Translator> args and the second is a coderef that returns the filename
648 =method _prepare_changegrade
650 $dm->_prepare_changegrade('1.00', '1.01', [qw( 1.00 1.01)], 'up')
652 Generates the sql file for migrating from one schema version to another. First
653 arg is the version to start from, second is the version to go to, third is the
654 L<version set|DBIx::Class::DeploymentHandler/VERSION SET>, and last is the
655 direction of the changegrade, be it 'up' or 'down'.
657 =method _read_sql_file
659 $dm->_read_sql_file('foo.sql')
661 Reads a sql file and returns lines in an C<ArrayRef>. Strips out comments,
662 transactions, and blank lines.