Grover Ponders

I want to share with you the parts of Ruby on Rails that I've been finding to be eye opening for me. To do this, I need to start someplace. So what we'll do is define a simple application, that we will build on to illustrate the points. In this instalment, we will define that application, and setup our application environment, and then using Rails Migrations to setup our database.

The application

We want to demonstrate an application that has more than one database table, and relations between those tables, including at least one many-to-many relationship. We want to set up an administration system to create, read, update, and delete (CRUD operations) the data in those tables via web pages. We want to utilize some modern features in those web pages, such as AJAX, and animated transitions.

To meet these needs, we'll create a simple collection management system. (Think CD collections, butterflies, postage stamps etc.) We'll track our items, and where those items currently are. We'll also categorize our items as a way of easily finding them.

The approach

Part of the appeal of Ruby on Rails is that development is done very quickly. Read up a bit on Agile Software Development if you are not familiar with the term. Rails facilitates this approach to coding. Basically this boils down to getting to coding as quickly as possible, and going through rapid iterations of the application, with feedback from the people involved.

If we assume our sample application is being done for a customer, let's assume they just told us what they wanted. We know at this time that we have an incomplete picture. But we do have enough to get started. This means that we'll have to involve our customer when we get to the points where we don't have enough details. Is that a bad thing? Not really. Too often our customers change their minds about what they want as the project progresses. They see something that triggers a thought process that results in the inevitable question "can we do XXX too?". So involving the customer when we don't have the details we need to continue, allows them a chance to review and consider a better way to approach the application - at a time when it's not a large problem. These considerations AFTER the application is completed are MUCH harder to implement.

So, let's get started.

The development environment

We've already talked about how to get Rails installed in a previous post. If you are following along, but have not installed Rails yet, you should go do so now.

Let's create the application. I personally put all my coding projects into a folder called "dev". So I issue the following commands to create our application structures:

Next we need to create our database. I'm using MySQL, so you may need to use a different approach to create your database. Here's the commands for MySQL:

(remember to change the username if needed.)

If you want to tighten down the security of your application, you may want to create a separate user for your development database. In MySQL, this would be done with the GRANT command. I'll leave that up to you if you are going to do that, but keep in mind the username/password you assign for the next step.

With the database created, we need to tell our application which database to use. We do this by editing the config/database.yml file. Change the part of the file that looks like this:

Modify the file as needed to point to your database. If you set up a different username/password to access your database, make sure you put this information on the appropriate lines here.

Now we have our development environment set up. Let's analyze the data we need.

What data do we need

The application description we have so far has 3 key elements - items, locations, and categories. It'll probably be safe to assume we need tables for these elements. But what information do we store in there? We don't have enough information. So we call over our customer and ask them. (Or more likely, we'll just look up from our laptop to ask them, as we create the development environment.) Our customer responds by telling us that their collection items can be clearly identified by the name of the item, as well as the location where the item might be. And categories just need a name too. (ok, this is VERY simple, but on purpose...)

We now know that our database will have three tables - items, locations, and categories - and each will have a name field. If you have a database background, you'll probably make the logical next step and assign an ID to each table so we can identify specific records. That's fine, but let's do things the "rails" way. This means we can take the mass of database training/specialization and well, ignore it. It is STILL very helpful to know the database theory, such as foreign keys, cascading relationships, etc. But we can ignore this if we allow Rails it's assumptions...

One final point - the relationships. The item table is related to a location, and to one or more categories. So it would make sense that the item table will have a location_id to identify which location, and a cross reference table to link it to it's categories. These are addressed in more detail below.

Creating the database the Rails way

Now we have the information to create the first draft of our database. To do so, we need to tell Rails that we want to create some models. In Rails, a "model" relates directly to a database table, and is used to provide our interactions with that table. We can create our models with the following three commands:

As you can see, we are creating a model for each of our entities. After each command you'll see output similar to this:

If you see any errors, you'll have to address them. There's lots going on here, but the two things to note from this is the "create app/models/item.rb" line, and the "create db/migrate/001_create_items.rb" line. The first creates our model, while the second creates our migration file. We'll address the model in the next posting, but be aware that it's already created for us.

Notice the filename of the migration file. It starts with a three digit number. This is a sequence to indicate what order the migrations should be done. Also notice that it says "create_ITEMS", not "item". Rails is smart enough to pluralize our words for us. Yep, it will properly convert "child" to "children", "person" to "people", and "category" to "categories". There's some smarts here. It's these migration files that will do our magic for us.

We'll edit each of the migration files in turn. We'll do them in the same order they were created. Modify each of the files to reflect the corresponding part below:

db/migrate/001_create_items.rb

db/migrate/002_create_locations.rb

db/migrate/<003_create_categories.rb

Looks a little cryptic right? It's nice and clear once you understand the points. Look at the items file. The only thing we've added there is the two lines that start with "t.column". The rest of it was created for us with the script/generate commands. So what are we doing here?

There are two parts here - "def self.up" and "def self.down". These are methods (aka functions, in case you understand that term better). Each method starts with a def and ends with a corresponding end. The self.up method will be executed when a migration is being applied to a database. The self.down method is executed when we are removing a migration. So whatever we do in the up method needs to be undone by the down method. With regards to creating tables, this usually means just dropping the table.

Inside the self.up method, we issue the create_table command. The ":items" part is the name of the table. This can be a little misleading at first, but using the : notation, is the same as saying "items" (with the quotes). The : notation is called a "symbol", and is used extensively. But in your mind you can equate :item == "item". One of the Rails assumptions is that all database tables will be plural. This will make sense later, as you start talking about the data in the table (i.e. "it's in the items table") - it makes using plain english easier.

The "do |t|" bit is setting up a loop for us, where we'll use the variable "t" to represent the table we are creating. So each line between the create_table and it's end are related to the new table. In this case we are simply adding some columns.

The column command takes three arguments - the name of the column, it's datatype, and any other options for the field. Here we've used symbol notation. If we wanted to limit how long our name field is, we might add the third parameter so that the line looked like this:

There can only be one third argument (and no forth, fith, etc.). So we group all the remaining items into a "hash". (think "associative array" if you're not familiar with hashes - it's not quite the same, but the concepts are similar.) This example would put a limit of 250 characts to our item name, and demand that every record MUST have a name.

For full details of what you can do with the migrations, and the other options you can specify for the columns, you really should go to the Rails API page for migrations.

One more thing to note for the items table - we've added a field to reference a location. There is another assumption made in this case. All foreign key fields will start with the singular form of the foreign table, followed by "_id". And that brings us to the IDs.

Unless otherwise specified, Rails will create an "id" field, that is an auto incrementing integer. This is another assumption. In the past I would have called that field "item_id", but that's not the Rails way. Nope, it just names it "id". So our items table actually has three fields.

Our Locations table is very similar, except it only has a name field.

Our categories file is a little different. Here we are creating two tables. First we create the actual categories table, then we create the cross reference table to link up categories and items. Again, there is an assumption made here. When creating these many to many relationship tables, it is assumed the table name will join the two tables involved, IN ALPHABETICAL ORDER. Hence we get a table called categories_items. Now, we could use an ID for this table, and this would be perfectly valid if we wanted to track any other information about the relationship (when the relationship was created maybe?). But if that were the case, we would be better off creating this table as a separate model. Our requirements didn't call for anything special regarding the relationship, so we'll simply assume an item can have one or more categories. We've included the fields to reflect this, but we also told create_table NOT to create the "id" field.

And the only other change here is that in the self.down method, we need to drop the second table. Order is important - we should drop the relationships before we drop the categories. Some database systems would raise an error if we didn't.

Migration

Now that we have the migration files created, we can apply them. We've already taken care of all the steps to make sure our application knows how to talk to the database. So we can just tell rails to apply the migration files. To do this we use the "rake" command.

That's it. You'll get some output indicating the progress. If you encounter any errors, you'll need to address them. Normally this is a typing error (I've found) in the migrations files.

What if you want to undo the changes? This is where the sequence numbers come into play. When a migration is applied a table called schema_info is created. This table contains a single field "version" that indicates what migration version the database sits at. So if we were to now add 3 more migration files, it would only apply 4, 5, and 6 - it would know that 1,2 and 3 were already applied. But this means we can go backwards as well. Let's imagine we wanted to forget the idea of categories for phase one of our application. We could enter the command

This would roll back the database to include only the first two migration files. It does so by applying the self.down method of the 003 migration file.

I've gotten used to rolling back to VERSION=0 and re-applying all my migrations with "rake db:migrate". The application I'm working on often modifies one or more earlier tables to make new methods work. And seeing as I don't have a production version of the code yet, this is just too easy. If I had a production version, then I would simply add other migration files that would do things like "add_column" - which creates the appropriate alter table statements.

Populating Lookup Data

The one other thing you might want to do with your migrations is to pre-populate a new table with some values. The locations table would be a prime candidate for this. We could do so by modifying the migration file to look like this:

db/migrate/002_create_locations.rb

The magic is in the "Location.create" lines. In this case the "Location" object represents our model. Our models will be the singular form of the table name, and will have an initial capital letter. The create method on the object creates a new record, and sets the listed fields to the corresponding values (:name => "Canada").

There are other ways to pre-populate the tables, but this will get us started.

Conclusion

And with that, we have a database created for our application. This is such a simple app that you may not see the benefits of migrations just yet. But just imagine if you wanted to change the database system. We'd have to modify the config/database.yml file accordingly, but then we can just run "rake db:migrate" again. No other changes needed. The database system has been abstracted and we no longer need to worry about the differences between those systems. Or better yet, we can now do incremental changes to our database, in such a way that multiple developers do not need a common database. Each developer can set their development database to the appropriate migration level for their work.

In our next instalment, we'll talk about updating the models to work with the tables in the most efficient manner possible.