Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
DBIx::Class::MigrationUsertContributed:PerlrDocumenTutorial::ThirdMigration(3)

NAME
       DBIx::Class::Migration::Tutorial::ThirdMigration	- A more advanced
       database	change

GOAL
       In the last version we added a new table	"country" and enforced a one
       to many relationship between "country" and "artist" such	that every
       "artist"	belonged to a single "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
       DBIx::Class::Migration up to the	job?  Let's find out!

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!

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 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 "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 "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 "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 "artist_cd_rs" which
       connects	us to the "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
       DBIx::Class manual, covering relationships.

       As always, you should remember to up the	$VERSION in your
       "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.

   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
       "prepare" made a	fresh "all_tables.json"	for you, which has added your
       new bridge table	and it also copied over	your custom "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 "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 "share/migrations/SQLite/upgrade/2-3/*".
       Again you have the "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
       "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
       "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 "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	"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 "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 "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.

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 "populate" which fixture set to restore, it will always
       use the "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...

   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	"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 "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 "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 "force_overwrite" flag to tell
       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
       "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 "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 "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 "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 "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 "006-new_artist.pl"
       don't exactly match those in our	schema.	 As I mentioned	before,	this
       is because the $schema that is passed as	the first (and only) argument
       to your anonymous subroutinues is NOT the schema	that comes from
       MusicBase::Schema but instead it	is generated directly from the
       database	using 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 "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
       "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

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 "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

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.

NEXT STEPS
       Proceed to DBIx::Class::Migration::Tutorial::AddMySQL

AUTHOR
       See DBIx::Class::Migration for author information

COPYRIGHT & LICENSE
       See DBIx::Class::Migration for copyright	and license information

perl v5.32.1		   DBIx::Class::Migration::Tutorial::ThirdMigration(3)

NAME | GOAL | The New Business Requirement | Change your Schema | Upgrade your database. | Adding some tests | SUMMARY | NEXT STEPS | AUTHOR | COPYRIGHT & LICENSE

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=DBIx::Class::Migration::Tutorial::ThirdMigration&sektion=3&manpath=FreeBSD+13.0-RELEASE+and+Ports>

home | help