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.
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!
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.
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.
So we just change $VERSION from 1 to 2, which is how the default configuration of DBIx::Class::DeploymentHandler works.
That looks correct. Let's get the database up to date. First prepare the files.
That will generate a bunch of new files. Lets see the directory structure now.
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.
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!
And then open it in your text editor and add the following (copied from the 001-auto.sql file).
## example, you don't need to do this!
And that would have made a script that would only run on SQLite installs.
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.
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.
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.
Ok, so now you have a good set of migrations to move from version 1 to 2. Lets try that out now.
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.
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.
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."
So now you have two sets of fixtures, the all_tables and the countries sets!
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.