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

FreeBSD Manual Pages

  
 
  

home | help
DBIx::Class::MigrationUserDContributed:PerlaDocumenttorial::SecondMigration(3)

NAME
       DBIx::Class::Migration::Tutorial::SecondMigration - Upgrade the
       Database

GOAL
       In this section you will	change your database and learn how to create
       upgrade files.  You will	also create some custom	fixture	configurations
       and learn about creating	seed data.

Changing Requirements
       So far your "MusicBase" application is doing fine, but some new
       requirements come down.	In addition to storing the Artist's name, we
       now want	to store some geographical information,	in this	case a
       "Country" that can be associated	with the "Artist".  Right now we are
       just going to care about	Artists	that can be located in one of the
       three following Countries:

	   Canada
	   Mexico
	   USA

       We'll need to seed that data to the database and	also track it via some
       custom fixture configurations.  That way	if you need to start a new
       database	from go	you can	get both the table structure and the essential
       running information (in this case the list of countries we care about
       could be	considered system / domain data, not transactional data).
       Let's do	it!

   Change the Schema
       We will need to add a new table and then	link that table	to Artist.
       From the	project	home directory (containing the "dist.ini" file)
       execute the following command in	your terminal shell.

	   touch lib/MusicBase/Schema/Result/Country.pm

       Then open this file in your text	editor of choice and add the following
       code:

	   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',
	     },
	     'name' => {
	       data_type => 'varchar',
	       size => '96',
	     });

	   __PACKAGE__->set_primary_key('country_id');
	   __PACKAGE__->add_unique_constraint(['name']);
	   __PACKAGE__->has_many(
	     'artist_rs' => "MusicBase::Schema::Result::Artist",
	     {'foreign.country_fk'=>'self.country_id'});

	   1;

       This will establish a new Result	class with a one to many relationship
       to the Artist Result class.  You	probably notice	that we	are
       referencing a column in the Artist Result class that does not yet
       exist.  Lets add	that as	well

       Open "lib/MusicBase/Schema/Result/Artist.pm" in your editor and change
       it to look like this:

	   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(
	     'cd_rs' =>	'MusicBase::Schema::Result::Cd',
	     {'foreign.artist_fk'=>'self.artist_id'});

	   1;

       So we added the foreign key field "country_fk" and we added the other
       end of our new relationship to Country (called "has_country").

       Great, we've altered our	schema to fit the new requirement.  Let's up
       the $VERSION and	commit if you are using	a repository.

       Alter file "lib/MusicBase/Schema.pm"

	   package MusicBase::Schema;

	   use strict;
	   use warnings;

	   use base 'DBIx::Class::Schema';

	   our $VERSION	= 2;

	   __PACKAGE__->load_namespaces;

	   1;

       So we just change $VERSION from 1 to 2, which is	how the	default
       configuration of	DBIx::Class::DeploymentHandler works.

Prepare	the new	migration
       Now that	we've altered the schema and upped the version,	we use the
       "prepare" command to create the version 2 files.	 First,	let's verify
       the status of our system:

	   dbic-migration -Ilib	status

       Should return

	   Schema is 2
	   Deployed database is	1

       That looks correct.  Let's get the database up to date.	First prepare
       the files.

	   dbic-migration -Ilib	prepare

       That will generate a bunch of new files.	 Lets see the directory
       structure now.

	   /share
	     /fixtures
	       /1
		 (Same as before)
	       /2
		 /conf
		   all_tables.json
	     /migrations
	       /_common
		 (Same as before)
	       /_source
		 (system files for DBIC::DeploymentHandler.  Leave alone!)
	       /SQLite
		 /deploy
		   /1
		     (Same as before)
		   /2
		     001-auto-_VERSION.sql
		     001-auto.sql
		 /downgrade
		   /2-1
		     001-auto.sql
		 /upgrade
		   /1-2
		     001-auto.sql
	     musicbase-schema.db

       Ok, we got a bunch more files.  You can see that	we've created a	new
       "all_tables.json" fixture configuration,	which if you peek inside, you
       will see	has been updated to include your new Country Result class.

       Additionally you	can see	we have	a new version 2	directory under
       "deploy"	which contains the full	DDL for	you new	schema,	as well	as the
       special metadata	table that DBIx::Class::DeploymentHandler uses to
       manage deployment history.  You should take a quick look	inside those
       as well,	and see	that the new country table has been added.

       So far all this is similar to Version 1.	 What is different is you have
       the new "downgrade" and "upgrade" directories.  For simplicity I	will
       not deal	with downgrades	for this section of the	tutorial, and instead
       focus on	the upgrade path.

       Lets take a closer look at "/upgrade":

	   /upgrade
	     /1-2
	       001-auto.sql

       Since our schema	was at version 2 and the database was at version 1, we
       created a 1 to 2	upgrade	path, which will allow us to get the database
       in sync with our	schema.	 DBIx::Class::DeploymentHandler	will
       introspect your schema and database using SQL::Translator and try to
       suggest some DDL	for this.  You should treat this initial
       "001-auto.sql" file as a	suggestion and as a guide.  You	will need to
       make changes to it based	on your	data change need (SQL::Translator
       knows about your	table structure, but not your data) as well as your
       performance and uptime needs.  For example, when	changing a table that
       has 1 million rows you might need to take an alternative	approach than
       what is suggested.

       This is the part	of the job where you need to exercise the most
       planning	and good judgment, and you might need to involve your DBA to
       help you	craft a	change set that	can work for your particular needs.
       In this case we have a very small database using	sqlite,	so our needs
       are similarly small, however that should	not excuse us from planning!

       Let's look at the proposed DDL change.  In the editor of	your choice
       open "share/migrations/SQLite/upgrade/1-2/001-auto.sql"

	   BEGIN;

	   CREATE TABLE	country	(
	     country_id	INTEGER	PRIMARY	KEY NOT	NULL,
	     name varchar(96) NOT NULL
	   );

	   CREATE UNIQUE INDEX country_name ON country (name);

	   ALTER TABLE artist ADD COLUMN country_fk integer NOT	NULL;

	   CREATE INDEX	artist_idx_country_fk ON artist	(country_fk);

	   COMMIT;

       So the first part of this that adds the "country" table is pretty
       straight	forward.  Lets break that part out into	its own	upgrade	step.
       Generally even if the DDL change	proposed is perfect, I prefer to move
       the code	to a file name other than the default "001-auto.sql" since if
       I need to prepare the upgrade several times (as you might if you	are
       building	a new version and realize you make a mistake and need to re
       prepare it, as we'll see	in a later step) each time you do it will
       overwrite that file, blowing away any customization you made.  So lets
       bust out	the first part:

	   touch share/migrations/SQLite/upgrade/1-2/001-add_country.sql

       And then	open it	in your	text editor and	add the	following (copied from
       the "001-auto.sql" file).

	   BEGIN;

	   ;
	   CREATE TABLE	country	(
	     country_id	INTEGER	PRIMARY	KEY NOT	NULL,
	     name varchar(96) NOT NULL
	   );

	   CREATE UNIQUE INDEX country_name ON country (name);

	   ;
	   COMMIT;

       Great, now we have a step that makes a table.  Next, we need to add the
       list of default countries.  For this we will use	a Perl script similar
       to the one we did for version 1.	 You could do this in SQL if you
       wanted, just if I can use a Perl	script I would prefer that since it
       would be	more portable across other databases (and eventually you will
       need something other than SQLite	for production).  Lets add that	now:

	   mkdir share/migrations/_common/upgrade
	   mkdir share/migrations/_common/upgrade/1-2
	   touch share/migrations/_common/upgrade/1-2/002-insert_countries.pl

       We are putting this in the "_common" migrations directory so that later
       on if we	add another database (such as MySQL) we'd be able have it run
       for both.  Since	Perl migration run files are going to be database
       agnostic, it makes sense	to do this.  You could have just as easily
       created a file:

	   ## example, you don't need to do this!
	   touch share/migrations/SQLite/upgrade/1-2/002-insert_countries.pl

       And that	would have made	a script that would only run on	SQLite
       installs.

       Lets edit our Perl run file:

       "share/migrations/_common/upgrade/1-2/002-insert_countries.pl"

	   use strict;
	   use warnings;
	   use DBIx::Class::Migration::RunScript;

	   migrate {
	     shift->schema
	       ->resultset('Country')
	       ->populate([
		 ['name'],
		 ['Canada'],
		 ['Mexico'],
		 ['USA'],
	     ]);
	   };

       Since the "populate" method uses	bulk insertion,	its generally my
       favored way to insert rows for migrations.  Its going to	be much	faster
       than doing separate inserts.  In	this case we don't have	a lot of data,
       so it didn't make a big difference, just	that later on if you data
       needs are larger	it would have an impact.

       Ok, so let's look at the	remaining part of the "001-auto.sql" file that
       we have not yet examined:

	   ALTER TABLE artist ADD COLUMN country_fk integer NOT	NULL;

	   CREATE INDEX	artist_idx_country_fk ON artist	(country_fk);

       So that only does part of the job.  First of all, we can't really add a
       NOT NULL	column without default values, since that's going to be	a
       constraint.  Also the foreign key is missing.  That's because it	seems
       SQLite doesn't let you alter a table to add that	kind of	constraint.
       Let's look at the DDL for the new artist	table from the full ddl	in
       "share/migrations/SQLite/deploy/2/001-auto.sql":

	   CREATE TABLE	artist (
	     artist_id INTEGER PRIMARY KEY NOT NULL,
	     country_fk	integer	NOT NULL,
	     name varchar(96) NOT NULL,
	     FOREIGN KEY(country_fk) REFERENCES	country(country_id)
	   );
	   CREATE INDEX	artist_idx_country_fk ON artist	(country_fk);

       Quite often when	pondering a DDL	change,	it is valuable to glance at
       the full	DDL that we give you in	the deploy directory, so that you can
       understand better what the target is.  In this case it is really	clear
       the diff	is only	getting	you partway there.

       Alright,	lets try to fix	it.  Lets create a file	to hold	our code:

	   touch share/migrations/SQLite/upgrade/1-2/003-change_artist.sql

       So this is going	to be the third	step for this upgrade (the second one
       was to add the country rows we need).  Open the file in your text
       editor and enter:

	   BEGIN;

	   CREATE TEMPORARY TABLE artist_temp_alter (
	     artist_id INTEGER PRIMARY KEY NOT NULL,
	     name varchar(96) NOT NULL
	   );

	   INSERT INTO artist_temp_alter SELECT	artist_id, name	FROM artist;

	   DROP	TABLE artist;

	   CREATE TABLE	artist (
	     artist_id INTEGER PRIMARY KEY NOT NULL,
	     country_fk	integer	NOT NULL DEFAULT 1,
	     name varchar(96) NOT NULL,
	     FOREIGN KEY(country_fk) REFERENCES	country(country_id)
	   );

	   CREATE INDEX	artist_idx_country_fk ON artist	(country_fk);

	   INSERT INTO artist SELECT artist_id,	1, name	FROM artist_temp_alter;

	   DROP	TABLE artist_temp_alter;

	   COMMIT;

       So this was my personal best shot, but of course	there is going to be
       more than one way to do it.  I decided it would be best to create a
       temporary table to hold the current artist info,	build a	new artist
       table with the correct FK constraint and	new column, and	then copy back
       from the	temp table.  Now, if I had a million rows this wouldn't	work
       out very	well, but with a database that big you'd probably have moved
       on to something that would actually allow you to	add an FK in an	alter
       statement.  So I'll go with this	since it does the job.

       Notice that when	I copy back to the new artist table from the temporary
       table I make all	the artists live in the	country	matching country_id =
       1 (Canada).  I'll leave it this way for the section of the tutorial,
       just to keep it simple but in reality you would probably	need to	copy
       things more careful, or maybe add an "UNKNOWN" country option to	the
       list of countries.  We'll do an example of something like that in a
       later section.  I just want to point out	that when you craft your
       migration code you need to interpret the	suggested DDL change and keep
       in mind how you data is put together.  That way you can craft a good
       change set that keeps you database well constrained and organized.

       Now that	your are done converting the suggested DDL change, you should
       delete the generated auto file, otherwise when later you	run the
       migration, you will end up doing	more changes and get some errors.

	   rm share/migrations/SQLite/upgrade/1-2/001-auto.sql

       Ok, so now you have a good set of migrations to move from version 1 to
       2.  Lets	try that out now.

Upgrade	the database
       Once you	have good migrations, its time to upgrade.  Typically I	first
       make sure the database is in a good known state:

	   dbic-migration -Ilib	drop_tables

       That will blow away your	tables,	so be careful when using this command!
       You should see:

	   Dropping table cd
	   Dropping table track
	   Dropping table artist
	   Dropping table dbix_class_deploymenthandler_versions

       Great, now the database is clear.  Let's	install	version	1

	   dbic-migration -Ilib	install	--to_version 1

       Since the schema	is version 2 we	need to	explicitly mention the target
       version we are deploying.  Otherwise DBIx::Class::Migration will	want
       to deploy a version that	matches	the current schema.  We	want to	do an
       upgrade,	not an install,	since we need to modify	both the tables	AND
       our data, then we can build some	good new fixtures.  Lets do the
       upgrade:

	   dbic-migration -Ilib	upgrade
	   dbic-migration -Ilib	status

       That should give	you:

	   Schema is 2
	   Deployed database is	2

       Let's peek in the database and see if everything	is good:

	   sqlite3 share/musicbase-schema.db
	   sqlite> .tables

       Should give you:

	   artist		   dbix_class_deploymenthandler_versions
	   cd			   track
	   country

       So there's our new table.  Lets make sure our data is correct:

	   sqlite> select * from country;
	   1|Canada
	   2|Mexico
	   3|USA

	   sqlite> select * from artist;
	   1|1|Michael Jackson
	   2|1|Eminem

       So looks	like out migration worked out.	Later on we'll learn how to
       write some test cases for our database, and I would highly suggestion
       that you	write some tests that make sure	your migration worked as
       expected, rather	than doing the manual inspect as above

       You are done with the upgrade, lets move	on to make new fixtures.  Exit
       the "sqlite" shell:

	   sqlite> .q

Customize Fixtures and Serializing Fixtures
       When you	"prepared" the new version, in addition	to creating the
       database	structure migrations, we also got a new	version	2 directory
       for the fixtures.

	   /share
	     /fixtures
	       /1
		 (Same as before)
	       /2
		 /conf
		   all_tables.json

       By default we build you a fresh "all_tables.json" that should reflect
       any new or removed tables.  Additionally, if there were any custom
       fixture configurations, we would	have copied those from the version 1.
       We didn't have any custom files so nothing was copied.  Lets make a
       custom fixture config now:

	   touch share/fixtures/2/conf/countries.json

       We will make a fixture configuration to dump all	the countries.	Since
       the list	of countries is	part of	your domain data (and not transaction
       data) you should	create fixtures	for it so that when someone is setting
       up a new	version	2 database they	can install a version that has both
       the structure and the data needed to have a functioning database.  Data
       that is part of the domain we call 'seed' data.	You'll need to create
       fixtures	for all	types of seed data, such as country lists, roles,
       product names, etc.

       So you can use fixtures for a variety of	jobs, from created demo
       databases to show clients, databases for	developer, fixtures for
       running tests, and fixtures to preserve necessary domain	data.  You can
       then install a database and setup whatever list of fixtures is needed
       for the job at hand.  Additionally since	our migrations perform updates
       on the database data, we	can just dump new fixtures after each clean
       update, and expect to have properly updated fixtures.

       Here's our fixture configuration	for
       "share/fixtures/2/conf/countries.json":

	   {
	      "sets" : [
		 {
		    "quantity" : "all",
		    "class" : "Country"
		 }
	      ],
	      "might_have" : {
		 "fetch" : 0
	      },
	      "belongs_to" : {
		 "fetch" : 0
	      },
	      "has_many" : {
		 "fetch" : 0
	      }
	   }

       This will dump all the countries, and you will be able to load them
       later.  You could review	DBIx::Class::Fixtures for a better
       understanding of	the way	fixture	rule sets are creating,	but this is
       basically saying: "Dump everything in the Country source, and don't
       follow any relationships	from Country or	to Country."

       Now that	you have the new, custom fixture, lets dump all	the sets:

	   dbic-migration -Ilib	dump_all_sets

       Please note that	if you 'played'	with the database after	doing the
       update, any new additional data will now	become part of your fixtures.
       This may	or may not be desirable.  Let's	see the	list of	new fixtures:

	   /share
	     /fixtures
	       /1
		 (Same as before)
	       /2
		 /all_tables
		   _dumper_version
		   /artist
		     1.fix
		     2.fix
		   /cd
		     (1-3.fix)
		   /country
		     (1-3.fix)
		   /track
		     (1-7.fix)
		 /countries
		   _dumper_version
		   /country
		     (1-3.fix)
		 /conf
		   all_tables.json
		   countries.json

       So now you have two sets	of fixtures, the "all_tables" and the
       "countries" sets!

SUMMARY
	   dbic-migration -Ilib	status
	   Schema is 2
	   Deployed database is	2

       You've successful crafted a migration to	move your database structure
       and your	data from version 1 to version 2.  You've also updated your
       fixtures	and created a custom fixture configuration for managing	your
       seed data.  You now have	a good system where a new developer can	walk
       in and run one or two commands to get a solid working database!
       Congrats.

NEXT STEPS
       Proceed to DBIx::Class::Migration::Tutorial::Testing.

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::SecondMigration(3)

NAME | GOAL | Changing Requirements | Prepare the new migration | Upgrade the database | Customize Fixtures and Serializing Fixtures | 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::SecondMigration&sektion=3&manpath=FreeBSD+13.0-RELEASE+and+Ports>

home | help