=head1 NAME
DBIx::Class::Migration::Tutorial::ThirdMigration - A more advanced database change
=head1 GOAL
In the last version we added a new table C<country> and enforced a one to many
relationship between C<country> and C<artist> such that every C<artist> belonged
to a single C<country>. These types of database changes, when you add a table
and some columns, tend to be some the easier types of migrations to perform.
Lets take on a more complicated migration problem, and add some more fixture
configurations and seed data. This time we will add a table and change the
nature of an existing relationship. Is L<DBIx::Class::Migration> up to the
job? Let's find out!
=head1 The New Business Requirement
In version 1 and 2 the relationship between Artist and CD is one to many; for
each Artist there are zero to many CDs, and for each Cd there is one and only
one artist. Now we realize that a CD could have a bunch of Artists working
together as a collaboration. We need to change our database to reflect this
need, and we additionally need to migrate our existing data to this new schema.
Let's get to it!
=head1 Change your Schema
Let's make the Version 3 schema. We need to to add a Bridge class between
Artist and Cd so that we can express the idea of 'many to many'.
touch lib/MusicBase/Schema/Result/ArtistCd.pm
And then open that in your text editor, enter the following code:
package MusicBase::Schema::Result::ArtistCd;
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__->table('artist_cd');
__PACKAGE__->add_columns(
artist_fk => {
data_type => 'integer',
},
cd_fk => {
data_type => 'integer',
});
__PACKAGE__->set_primary_key('artist_fk','cd_fk');
__PACKAGE__->belongs_to(
'artist' => "MusicBase::Schema::Result::Artist",
{'foreign.artist_id'=>'self.artist_fk'});
__PACKAGE__->belongs_to(
'cd' => 'MusicBase::Schema::Result::Cd',
{'foreign.cd_id'=>'self.cd_fk'});
1;
So if you are familiar with L<DBIx::Class> you'll already know this is a normal
pattern. We have a bridge table where each row points to one Artist and on CD
row, and no duplications are allowed (the Same Artist can't be linked to the
same CD twice, for example).
Now we need to change the Artist and CD Result classes. Open your editor on
C<lib/MusicBase/Schema/Result/Artist.pm>:
package MusicBase::Schema::Result::Artist;
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__->table('artist');
__PACKAGE__->add_columns(
artist_id => {
data_type => 'integer',
},
country_fk => {
data_type => 'integer',
},
name => {
data_type => 'varchar',
size => '96',
});
__PACKAGE__->set_primary_key('artist_id');
__PACKAGE__->belongs_to(
'has_country' => 'MusicBase::Schema::Result::Country',
{'foreign.country_id'=>'self.country_fk'});
__PACKAGE__->has_many(
'artist_cd_rs' => 'MusicBase::Schema::Result::ArtistCd',
{'foreign.artist_fk'=>'self.artist_id'});
__PACKAGE__->many_to_many(artist_cds => artist_cd_rs => 'cd');
1;
So we didn't change the columns, but we removed the direct relationship to Cd
and replaced it with a relationship to the new ArtistCd result class. We also
created one pseudo 'many to many' relationship across the bridge so that we can
directly pick up all the Cds for a given Artist.
Now we need to change the Cd. Open C<lib/MusicBase/Schema/Result/Cd.pm>
and make the following change:
package MusicBase::Schema::Result::Cd;
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__->table('cd');
__PACKAGE__->add_columns(
'cd_id' => {
data_type => 'integer',
},
'title' => {
data_type => 'varchar',
size => '96',
});
__PACKAGE__->set_primary_key('cd_id');
__PACKAGE__->has_many(
'track_rs' => 'MusicBase::Schema::Result::Track',
{'foreign.cd_fk'=>'self.cd_id'});
__PACKAGE__->has_many(
'artist_cd_rs' => 'MusicBase::Schema::Result::ArtistCd',
{'foreign.cd_fk'=>'self.cd_id'});
__PACKAGE__->many_to_many(artists_cd => artist_cd_rs => 'artist');
1;
Changes here are a bit deeper. First we removed the C<artist_fk> column since
we no longer constrain each Cd to a single Artist. Then we removed the
relationship directly to Artist (since we no longer have that) and replaced it
with a new relationship C<artist_cd_rs> which connects us to the C<ArtistCD>
bridge table. Last, we added another pseudo relationship so that you can
easily cross the bridge table and get all the Artists related to a given Cd.
If any of this is confusing to you, you should stop now and review the
L<DBIx::Class> manual, covering relationships.
As always, you should remember to up the C<$VERSION> in your
C<MusicBase::Schema>:
package MusicBase::Schema;
use strict;
use warnings;
use base 'DBIx::Class::Schema';
our $VERSION = 3;
__PACKAGE__->load_namespaces();
1;
Very good, we've changed the database to reflect our new requirement. Lets
start working on the migration.
=head2 Prepare the migrations
dbic-migration -Ilib status
Schema is 3
Deployed database is 2
So first off we've check the status of our system and can see the database is
one version behind. Let's prepare the migration:
dbic-migration -Ilib prepare
And as usual we can see a bunch of new version 3 directories and some 2-3
directories related to upgrades. As in the previous migration, we'll skip
the 3-2 downgrade, instead covering downgrades in a later section. Here's
some of the new directory structure:
/share
/fixtures
/1
(Same as before)
/2
(Same as before)
/3
/conf
all_tables.json
countries.json
/migrations
/_common
(Same as before)
/_source
(System files for DBIC::DeploymentHandler)
/SQLite
/deploy
/1
(Same as before)
/2
(Same as before)
/3
001-auto-_VERSION.sql
001-auto.sql
/downgrade
/2-1
/3-2
/upgrade
/1-2
(Same as before)
/2-3
001-auto.sql
musicbase-schema.db
At this point there should be no surprises. You'll notice that C<prepare>
made a fresh C<all_tables.json> for you, which has added your new bridge table
and it also copied over your custom C<countries.json> from the previous version.
Please note that this is a dumb copy; no attempt has been made to convert the
configuration to make sense with any database structural changes. You'll need
to examine your custom configurations and manually make any needed changes. In
this case the existing custom configuration is fine for this version, so we can
move on to reviewing the deploy and upgrades.
There should be no surprises under C<deploy/3/*>. The only reason you'll need
to poke into these files is if you need to see the full DDL as part of helping
you craft the upgrades correctly, or if your DBA needs to tweak aspects of the
design (change indexes, storage engines, etc.)
The real fun begins under C<share/migrations/SQLite/upgrade/2-3/*>. Again you
have the C<001-auto.sql> file, which is the first, best guess on how to upgrade
the database. We need to poke into that and shape it into something that can
work for both our database structure and our data. Let's look at the the
suggestion. Open C<share/migrations/SQLite/upgrade/2-3/001-auto.sql> in your
text editor:
BEGIN;
CREATE TABLE artist_cd (
artist_fk integer NOT NULL,
cd_fk integer NOT NULL,
PRIMARY KEY (artist_fk, cd_fk),
FOREIGN KEY(artist_fk) REFERENCES artist(artist_id),
FOREIGN KEY(cd_fk) REFERENCES cd(cd_id)
);
CREATE INDEX artist_cd_idx_artist_fk ON artist_cd (artist_fk);
CREATE INDEX artist_cd_idx_cd_fk ON artist_cd (cd_fk);
CREATE TEMPORARY TABLE cd_temp_alter (
cd_id INTEGER PRIMARY KEY NOT NULL,
title varchar(96) NOT NULL
);
INSERT INTO cd_temp_alter SELECT cd_id, title FROM cd;
DROP TABLE cd;
CREATE TABLE cd (
cd_id INTEGER PRIMARY KEY NOT NULL,
title varchar(96) NOT NULL
);
INSERT INTO cd SELECT cd_id, title FROM cd_temp_alter;
DROP TABLE cd_temp_alter;
COMMIT;
This doesn't look too bad. From first review it looks to me like we are just
missing transfering data from the old relationship to the new bridge table. So
like last time we will bust this up into a few steps, under new file names but
under this directory. Lets handle this in bits.
touch share/migrations/SQLite/upgrade/2-3/001-create_artist_cd.sql
And then open that in your editor. We will copy the first bit of the
C<001-auto.sql>, having to do with creating the new table and indexes over:
BEGIN;
CREATE TABLE artist_cd (
artist_fk integer NOT NULL,
cd_fk integer NOT NULL,
PRIMARY KEY (artist_fk, cd_fk),
FOREIGN KEY(artist_fk) REFERENCES artist(artist_id),
FOREIGN KEY(cd_fk) REFERENCES cd(cd_id)
);
CREATE INDEX artist_cd_idx_artist_fk ON artist_cd (artist_fk);
CREATE INDEX artist_cd_idx_cd_fk ON artist_cd (cd_fk);
COMMIT;
Feel free to add additional SQL style comments, in order to help anyone down
the road understand what you are doing!
Now we need to populate that with existing data. We will make a separate
upgrade step for that:
touch share/migrations/SQLite/upgrade/2-3/002-populate_artist_cd.sql
Open C<share/migrations/SQLite/upgrade/2-3/002-populate_artist_cd.sql> and
here's the SQL to enter:
;
BEGIN;
INSERT INTO artist_cd(artist_fk,cd_fk) select artist_fk,cd_id FROM cd;
COMMIT;
Luckily everything we need exists in the current C<cd> table, so this is a
straightup insert. In this case I didn't use a Perl deploy run script since
I felt the performance benefit of a native SQL approach outweighed the
advantage of database portability. If I ever needed to make this work on say
MySQL or Pg, I'd need to rewrite it, and there's not a lot of SQL so I am
willing to take that risk.
Lastly, we need to alter the C<cd> table to get rid of the now unneeded
relationship:
touch share/migrations/SQLite/upgrade/2-3/003-alter_cd.sql
Then open that in your text editor, and lets bring over the last part from
C<001-auto.sql>:
BEGIN;
CREATE TEMPORARY TABLE cd_temp_alter (
cd_id INTEGER PRIMARY KEY NOT NULL,
title varchar(96) NOT NULL
);
INSERT INTO cd_temp_alter SELECT cd_id, title FROM cd;
DROP TABLE cd;
CREATE TABLE cd (
cd_id INTEGER PRIMARY KEY NOT NULL,
title varchar(96) NOT NULL
);
INSERT INTO cd SELECT cd_id, title FROM cd_temp_alter;
DROP TABLE cd_temp_alter;
COMMIT;
Again, since SQLite doesn't have any DDL alter to remove FK's we need to
roundtrip the data via a temporary table. If you had a million+ rows you
might worry about this approach :) Again, I will accept this DDL change for
the scope of our limited requirement.
So, that's all the changes. Like last time, remember to remove the suggested
upgrade script:
rm share/migrations/SQLite/upgrade/2-3/001-auto.sql
And you'd probably wish to commit these files now if you are using an source
control system (and if you are not, prepare for pain!)
Next step will be to perform the upgrade.
=head1 Upgrade your database.
$ dbic-migration -Ilib status
Schema is 3
Deployed database is 2
SO the database is in the expected state. If you've been messing with the data
you might which to 'clean' things up, with something like:
## optional step to 'clean up' database
$ dbic-migration -Ilib delete_table_rows
$ dbic-migration -Ilib populate
Reading configurations from ...MusicBase/share/fixtures/2/conf
Restored set all_tables to database
As you might notice above, even though the schema is version 2, we installed
fixtures from version one. The tool will always try to match fixture populates
to the current database version. And remember, if you don't tell C<populate>
which fixture set to restore, it will always use the C<all_tables> set.
Let's go ahead with the upgrade:
$ dbic-migration -Ilib upgrade
$ dbic-migration -Ilib status
Schema is 3
Deployed database is 3
Looks good. Lets peek in the database and do a quick sanity check. You should
also consider writing some test cases similar to what we did in the previous
section:
$sqlite3 share/musicbase-schema.db
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
artist country
artist_cd dbix_class_deploymenthandler_versions
cd track
sqlite> select * from artist_cd;
1|1
1|2
2|3
sqlite> .q
So that looks pretty good. Normally at this point you'd be getting ready to
dump fixtures but then...
=head2 Requirements Change! Mistakes Happen!
Just about when you are finished there is a sudden requirement change to the
version. Or maybe you realize your new schema isn't exactly what you need. In
any case you find yourself in a situation where you've already updated and now
you need to step back, change the migration, and upgrade again.
This really isn't a problem at all. Its actually very easy to step back and
redo your version. You could handle this in two ways. Either you've been very
good and making sure you made good downgrades (we didn't :) ) or you can force
install the database to an arbitrary older version and start again with the
C<prepare> command. We will take this second option for this tutorial.
So for the purposes of our tutorial, lets say that suddenly we realize our
design for the C<country> table is terrible wrong. We've been putting real
country names in the table, and in English, but now we want to internationalize
our site. That means we should avoid English words in our seed data, and
instead use normalized codes that our UI layer can use and leverage existing
internationalization and localization tools against. So we need to change that
country table, and do so in a way to make sure we keep our existing country
information correct. Lastly, we want to add a few new countries to the list
as well as one more artist to the system. Thats a bunch of changes, so lets
get to it!
Changes to be made:
1 - Change Country table to use codes not English names
2 - Update the table data to match above
3 - Add new countries to list
4 - Add one additional artist
Let's start by altering C<lib/MusicBase/Schema/Result/Country.pm>
package MusicBase::Schema::Result::Country;
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__->table('country');
__PACKAGE__->add_columns(
'country_id' => {
data_type => 'integer',
},
'code' => {
data_type => 'char',
size => '3',
});
__PACKAGE__->set_primary_key('country_id');
__PACKAGE__->add_unique_constraint(['code']);
__PACKAGE__->has_many(
'artist_rs' => "MusicBase::Schema::Result::Artist",
{'foreign.country_fk'=>'self.country_id'});
1;
So the change here is we drop the 'name' column, and replace it with a 'code'
column, that is fixed as a 3 character datatype. We decided that we'd use the
standard international 3 digit codes for countries, that's something we can use
to wrap internationalization UI around. We also change the unique constraint
that used to be on 'name' to 'code'. Everything else stays the same.
There's no other database changes, the rest will be handled in the migration so
let's set our database back to version 2 (so that we have something to diff
against and also migrate data from).
$ dbic-migration -Ilib drop_tables
Dropping table country
Dropping table cd
Dropping table artist_cd
Dropping table track
Dropping table artist
Dropping table dbix_class_deploymenthandler_versions
$ dbic-migration -Ilib install --to_version 2
$ dbic-migration -Ilib populate
Reading configurations from .../MusicBase/share/fixtures/2/conf
Restored set all_tables to database
When the tables are small like this, you can get away with just dropping them
and rebuilding everything from the ground up. Once the tables are large you
will probably need to actually write correct downgrades, since that would be
much more efficient.
Now we are ready to prepare the version (again):
dbic-migration -Ilib prepare --force_overwrite
We need to use the C<force_overwrite> flag to tell L<DBIx::Class::DeploymentHandler>
that it is ok to overwrite the generated files. Since you've been making all
your customizations in new files we don't have to worry about accidentally
blowing away anything important. You'd expect some output like so:
Overwriting existing DDL-YML file - ...
Overwriting existing DDL file - ...
Overwriting existing DDL-YML file - ...
Overwriting existing DDL file - ...
Your Database version must be lower than than your schema version
in order to prepare upgrades / downgrades
Copying Fixture Confs from .../MusicBase/share/fixtures/2/conf to
.../MusicBase/share/fixtures/3/conf
Output above has been abbreviated a bit to highlight the important information.
Don't worry about that "Copying Fixture Confs from ..." overwriting any of your
custom changes, if there is a file in the target directory matching we just
skip the copy (we always assume if the file is there that you may have made
some changes you'd rather not lose).
Now, let's look at the new C<share/migrations/SQLite/upgrade/2-3/001-auto.sql>
CREATE TEMPORARY TABLE country_temp_alter (
country_id INTEGER PRIMARY KEY NOT NULL,
code char(3) NOT NULL
);
INSERT INTO country_temp_alter SELECT country_id, code FROM country;
DROP TABLE country;
CREATE TABLE country (
country_id INTEGER PRIMARY KEY NOT NULL,
code char(3) NOT NULL
);
CREATE UNIQUE INDEX country_code02 ON country (code);
INSERT INTO country SELECT country_id, code FROM country_temp_alter;
DROP TABLE country_temp_alter;
Again, to be brief I've only included above the new statements related to our
changes to the C<country> table. This actually seems pretty good. Lets
break that out into a separate file, and add some statements to move data
from the old name to the new code columns:
touch share/migrations/SQLite/upgrade/2-3/004-alter_country.sql
And open that new file in your editor, add the following:
BEGIN;
CREATE TEMPORARY TABLE country_temp_alter (
country_id INTEGER PRIMARY KEY NOT NULL,
code char(3) NOT NULL
);
-- Match to current data
INSERT INTO country_temp_alter SELECT country_id, 'can' FROM country where name='Canada';
INSERT INTO country_temp_alter SELECT country_id, 'usa' FROM country where name='USA';
INSERT INTO country_temp_alter SELECT country_id, 'mex' FROM country where name='Mexico';
-- End Match
;
DROP TABLE country;
CREATE TABLE country (
country_id INTEGER PRIMARY KEY NOT NULL,
code char(3) NOT NULL
);
CREATE UNIQUE INDEX country_code02 ON country (code);
INSERT INTO country SELECT country_id, code FROM country_temp_alter;
DROP TABLE country_temp_alter;
COMMIT;
So I basically just took that change and swapped:
INSERT INTO country_temp_alter SELECT country_id, code FROM country;
with the three separate INSERTs so I could properly map the English country
names to the new 3 character country codes. Otherwise I've kept the rest.
This again is a good example of how your diff should both change the database
and alter your data in a consistent manner.
Don't forget to delete the C<001-auto.sql> file:
rm share/migrations/SQLite/upgrade/2-3/001-auto.sql
In order to complete our new requirements, lets create some Perl run files to
add some new country codes, and one new Artist:
touch share/migrations/SQLite/upgrade/2-3/005-new_countries.pl
touch share/migrations/SQLite/upgrade/2-3/006-new_artist.pl
Open C<share/migrations/SQLite/upgrade/2-3/005-new_countries.pl> in your editor
and add the following:
use strict;
use warnings;
use DBIx::Class::Migration::RunScript;
migrate {
shift->schema
->resultset('Country')
->populate([
['code'],
['bel'],
['deu'],
['fra'],
]);
}
Finally open C<share/migrations/SQLite/upgrade/2-3/006-new_artist.pl> in
your editor and add the following:
use strict;
use warnings;
use DBIx::Class::Migration::RunScript;
migrate {
shift->schema
->resultset('Artist')
->create({
name => 'JoJo',
country_fk => {code=>'usa'},
artist_cds => [
{ cd_fk => {
title=>'My Cool New Album'}
}
],
});
}
You might notice that the relationship names in C<006-new_artist.pl> don't
exactly match those in our schema. As I mentioned before, this is because the
C<$schema> that is passed as the first (and only) argument to your anonymous
subroutinues is NOT the schema that comes from L<MusicBase::Schema> but instead
it is generated directly from the database using L<DBIx::Class::Schema::Loader>
This is because your schema is going to change a lot, we can't rely on it always
being backwardly compatible with every version of the database.
If you every get confused about what the auto generated schema looks like, you
can always use the C<make_schema> command:
## example command, don't need to run as part of the tutorial
dbic-migration -Ilib make_schema
And that will dump the current database version generated schema to
C<share/dumped_db>. You can also set a debugging %ENV variable which will
dump to STDOUT the generated classes:
## example command, don't need to run as part of the tutorial
export DBIC_MIGRATION_DEBUG=1
Everything is ready to go. Lets run the upgrade:
dbic-migration -Ilib upgrade
As before, let's peek inside the database for a quick sanity check:
$sqlite3 share/musicbase-schema.db
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
artist country
artist_cd dbix_class_deploymenthandler_versions
cd track
sqlite> select * from country;
1|can
2|mex
3|usa
4|bel
5|deu
6|fra
sqlite> .q
Of course, we really should have some test cases! But first, don't forget to
dump your fixtures:
dbic-migration -Ilib dump_all_sets
=head1 Adding some tests
As usual after changing your version, you will need to add a few tests, and
check your existing ones. First, add a test for the 2 to 3 migration:
touch t/upgrade-2to3.t
Then open that in you text editor, add the following:
#!/usr/bin/env perl
use Test::Most;
use Test::DBIx::Class
-schema_class=>'MusicBase::Schema',
-fixture_class => '::Population',
qw(Artist Country);
plan skip_all => 'not correct schema version'
if Schema->schema_version != 3;
fixtures_ok ['all_tables'];
is Country->count, 6,
'Correct Number of Tests';
ok my $artist = Artist->first,
'Got one artist';
is $artist->has_country->code, 'can',
'Oh Canada!';
is scalar($artist->artist_cds), 2,
'has two cd';
done_testing;
So this is just a basic test to see that all the new countries exist and that
the new many to many between Artist and Cd works. Lets run the test suite:
prove -l t
This gives you:
t/more-than-1.t ... 1/? DBIx::Class::ResultSet::count(): No such relationship cd_rs on Artist
t/more-than-1.t ... Dubious, test returned 9 (wstat 2304, 0x900)
All 1 subtests passed
t/upgrade-1to2.t .. skipped: not correct schema version
t/upgrade-2to3.t .. ok
t/use.t ........... ok
Test Summary Report
-------------------
t/more-than-1.t (Wstat: 2304 Tests: 1 Failed: 0)
Non-zero exit status: 9
Parse errors: No plan found in TAP output
Files=4, Tests=6, 2 wallclock secs ( ... )
Result: FAIL
Oops, we broke our resultset method when we changed the relationship! Luckily
we can fix it in one place (instead of having to hunt through all you code if
you didn't bother to use an ORM!)
Open C<lib/MusicBase/Schema/ResultSet/Artist.pm> and make the following change:
package MusicBase::Schema::ResultSet::Artist;
use strict;
use warnings;
use base 'DBIx::Class::ResultSet';
sub has_more_than_one_cds {
my $me = (my $self = shift)->current_source_alias;
$self->search(
{},
{
join=>['artist_cd_rs'],
'+select'=> [ { count => 'artist_cd_rs.cd_fk', -as => 'cd_count'} ],
'+as'=> ['cd_count'],
group_by=>["$me.artist_id"],
having => { cd_count => \'> 1' }
}
);
}
1;
We just change the join condition to match the new relationship, and try again:
$prove -l t
t/more-than-1.t ... ok
t/upgrade-1to2.t .. skipped: not correct schema version
t/upgrade-2to3.t .. ok
t/use.t ........... ok
All tests successful.
Files=4, Tests=7, 2 wallclock secs ( ... )
Result: PASS
=head1 SUMMARY
Ok, that was a big section to get through, but we covered a lot of ground. You
have seen how to handle a more complicated change set, and we added some tests
and dealt with changing requirements mid task.
=head1 NEXT STEPS
Proceed to L<DBIx::Class::Migration::Tutorial::AddMySQL>
=head1 AUTHOR
See L<DBIx::Class::Migration> for author information
=head1 COPYRIGHT & LICENSE
See L<DBIx::Class::Migration> for copyright and license information
=cut