##Active Record
###ORM
Object-Relational-Mapper
It's critical to understand exactly what an ORM is, and by turn then gain an understanding of ActiveRecord and the things it can do for us. Imagine we have the following structure in our database:
//A Table for a Band
//3 columns named GIG,PAY,DATE
+------------------BAND---------------------------+
| GIG | PAY | DATE |
| | | |
|---------+----------------------+----------------|
| Bob's | | |
| | $50.00 | 10/29/2012 |
|---------+----------------------+----------------|
| E. | $125.00 | 11/15/2012 |
|Tavern | | |
+-------------------------------------------------+
And in our application we have a model that looks something like:
#A class with 3 attributes that match a table with 3 columns in our db
class Band < ActiveRecord::Base
attr_accessor :gig,:pay,:date
endHow does our data get from a to b? In other words, how do we get the data that sits in our database, which is a completely separate program from rails, into our rails application and back out again? If you guessed that the title of this section 'ORM' may have been a spoiler, you're a good guesser.
Let's look at life without an ORM. Below is some C# code that essentially hand rolls an object-relational-mapper on the fly. Examine it carefully and see if you can tell what's going on.
conn.Open();
SqlCommand cmd = new SqlCommand("GetProfile", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@UserName", System.Data.SqlDbType.NVarChar).Value = UserName;
using (cmd)
using (SqlDataReader readertest = cmd.ExecuteReader())
{
while (readertest.Read())
{
profile.FirstName = readertest.GetString(0);
profile.LastName = readertest.GetString(1);
profile.City = readertest.GetString(2);
profile.State = readertest.GetString(3);
profile.Email = readertest.GetString(4);
profile.ZipCode = readertest.GetString(5);
profile.DateRegisterd = readertest.GetDateTime(6);
profile.TimeZone = readertest.GetString(7);
profile.PaperChartEnabled = readertest.GetBoolean(8);
profile.StreetAddress = readertest.GetString(9);
readertest.NextResult();
}
}
return profile;The steps:
- We first have to ask for a connection to the database, which is likely running on a separate server entirely.
- We have to tell it what type of command we want to execute. In this case a stored procedure, we need the name as a param,and the connection object.
- We have to wrap our calls to the database in 'using' blocks because we don't want to suck at programming. Using blocks in C# are essentially 'try catch finally' blocks. Pro tip: whenever your program accesses external resources wrap those calls in try catch finally blocks if they're available in that language. This takes care of resource cleanup if something should go wrong
- We have to set up a while loop to read all the potential results.
- We also have to set up a reader object that will parse our result set
- Inside the loop we must access each returned field by it's indice, using it to populate our Profile model object.
Let's look at the the code in Rails to do the same thing: retrieve a particular user profile
class Controller < ApplicationController
#grab a particular profile by id
def index
@profile = Profile.find(params[:id])
end
endThe above is actually the code for the entire controller, the one line inside index is actually doing the same thing as all that C# code.
Q: Why is rails used by start ups? A: See above code block
##Migrations
Above we saw the structure of a table in a database, we can see that a database table is row-column based, exactly like a spreadsheet. With respect to our rails application, how does our table get created and structured in the first place?
In other frameworks we might need to switch back and forth between the database and the code we are writing. The workflow might look something like this:
- I need to change my user model by adding a 'tel-number' to it
- Okay, make the modifications to my User model.
- Go to the database and login, update the User table to reflect the new column. Set up any necessary default values for records already existing.
- Repeat this for any other changes, always making sure everything stays in sync.
You can probably already tell that this dance is complicated. It's very easy to update one and not the other, and when applications are built this way you tend to get errors when things go out of sync.
Luckily, rails gives us an extremely powerful tool for dealing with the database in this regard: Migrations
Migrations amount to a DSL(Domain Specific Language) used for specifying changes to the database.
Here's what the flow looks like to add a table to our project's database:
$ rails g migration create_bandsThe command will generate the migration below in the db/migrate folder of the project. The syntax inside this class is the DSL, or domain specific language that rails provides for specifying changes to the database. Take the time to understand the syntax as you will need to use stand alone migrations, where you write the code inside the change method.
class CreateBands < ActiveRecord::Migration
def change
create_table :bands do |t|
end
end
endNotice how the console command is snake case 'create_bands' and the resulting migrations class is camel case, starting with an uppercase letter.
We can now run this migration, and roll it back as well to get a feel for the migration process.
#this will run the CreateBands Migration, creating the Bands table in the database
$ rake db:migrate
#we can revert the migration and drop the table with this command
$ rake db:rollback
#this command would do the same thing as above, however it's
#good to know that we can go back as many steps as we need.
$ rake db:rollback STEP=1
Now what we did is create an empty table, which obviously is not very useful on it's own. We would need to add columns to this database in order to actually make use of the table. Normally we will create a table and add the initial columns with the same migration. However models change over time, and it's really important to know how to adjust our models as they change over time. This is the stand alone migration.
So say we want to add the columns: gig, pay, date to our database table, we would create a migration, then fill in the code for the change statement.
#this will generate a migration with an empty change method
$ rails g migration add_columns_to_bandsclass AddColumnsToBands < ActiveRecord::Migration
def change
#we will add three columns to our bands table
add_column :bands, :gig, :string
add_column :bands, :pay, :string
add_column :bands, :date, :string
end
endDropping a table
class DropBands < ActiveRecord::Migration
def change
remove_table :bands
end
end Removing a column
class RemoveGigFromBands < ActiveRecord::Migration
def change
remove_column :bands,:gig
end
end Renaming a column
class RenameGigInBands < ActiveRecord::Migration
def change
rename_column :bands,:gig, :show
end
end If you're starting work on a rails project that already has a schema, run the following command to get set up.
//A schema already exists so load it up.
$ rake db:schema:load##Models
All this migrating back and forth is great, but where are our models in all of this? As you may have suspected, inheriting from the ActiveRecord class is what gave us all these crazy abilities. I put the attr_accessor just to show you that we were dealing with 3 attributes, but in real life using ActiveRecord we don't even have to do that. This is what our class would look like:
class Band < ActiveRecord::Base
endWTF? It's empty.
Well.....it is and isn't. There's a lot of functionality here, you just can't see it. The contents of the all important schema.rb file act as your clue that there is more here than meets the eye. ActiveRecord(hence forth abbreviated as AR) is always acutely aware of the state of your database schema...and if you have a model called Band(singular), which inherits from AR, and a table called Bands(plural i.e all your rows of Band objects), there is some collaboration happening.
In other words instances of your Band class represent rows in your Bands table. Your Band object is tracking the schema of your bands table. This is an extremely important concept to grasp in rails. That's why you don't see any attributes defined on the model, the model is designed to always reflect the lastest schema.
To put it a different way..your Band class( which you might create an instance of with the code directly below)...is mirroring the Bands table. It has properties that match the columns of your Bands table. Super critical point, let it soak in.
All the data in your table Bands will be reflected in the Band object.
#good to go with accessors for fields
band = Band.first
gig = band.gigStill, the Band model looks pretty empty and lonely, is there anything we can do to cheer it up?? Sure.....you can, and probably always should, decorate your models.
I'll now introduce you to one of the single most important concepts in all of web development...so important in fact...that I'll bold the whole sentence: NEVER,NEVER,NEVER trust user data. Always ensure that your data is valid. We're lucky that AR makes this extremely easy to do, so easy in fact that there's no excuse for not doing the minimum of data validation.
class Band < ActiveRecord::Base
validates_presence_of :gig #make gig required
validates_length_of :gig, maximum: 100 #limit the length of characters for gig
endAn exhaustive list can be found here. Please don't hesitate to use any and all that are applicable to your models :)
##Queries
One thing you will of course need to do is get data from the database. Let's look at a few ways we can do that, and discuss some best practices.
One of the simplest queries is just to return all the rows in a particular table. So if I wanted to get all the rows in the Bands table I could issue a query like this:
#Here we have a controller with an index method.
#@bands contains all the records in the Bands table and is
#available to the view
class BandController < ApplicationController
def index
@bands = Band.all
end
end
#and so in your view code you can loop over @bands and display each record to the user
<% @bands.each do |band| %>
<div><%= band.name %> </div>
<% end %>> For the rest of the examples that follow let's also assume that we are working inside of a controller with our models and queries. We'll continue to use the simple example of the Band model, but all of these queries can be generalized to all models.
Often in your applications you won't want to return all the records, but rather just one. The classic example of this is finding and retrieving a particular user in your database.
With that in mind, it's a good time to talk about primary keys. When dealing with a database it's necessary to have a rock solid understanding of this concept. Above, there is that awesome rendering of the Bands database table schema, that while clearly an ASCII art masterpiece, is actually inaccurate.
A better representation would be like below, note the addition of the ID column:
+-------------------Bands--------------------------------+
| id | gig | pay | date |
| | | | |
|-----+---------+----------------------+----------------|
| | | | |
| 1 | Bob's | $50.00 | 10/29/2012 |
|-----+---------+----------------------+----------------|
| | E. | | |
| 2 | Tavern | $125.00 | 11/15/2012 |
+-------------------------------------------------------+
The ID column serves as our primary key. It is a column that contains unique values, thereby allowing us to always retrieve a particular record using that unique value. In theory, any column whose values are guaranteed to be unique can be used as a primary key for that table.
In practice however, we usually have a column serve this express purpose for us. In the overwhelming majority of cases, this column is called ID. Many database systems have facilities for creating this column for you as you insert each row. Some databases may opt to use an integer that increments with each row inserted, others may use what's called a GUID. A GUID is really drinking the uniqueness koolaid, it stands for Globally Unique Id, and it's often also called a UUID, Universally Unique Identifier. It looks like this:
- A GUID : {25892e17-80f6-415f-9c65-7395632f0223}
It's likely that this ID is unique in the universe, hence the name.
These different keys serve the same end goal: We must have a column in our db whose values are guaranteed to be unique, thereby allowing us to uniquely identify an individual row. Primary keys are a hugely important concept, so take the time to fully understand it. We'll see how important they become when we deal with relationships.
Armed with this knowledge, we can see how if we have an ID value, we can return a unique record from our database.
#The find method takes a primary key as a parameter, and returns an object it has
#built from the individual row it found.
@band_record = Band.find(id)
#you can also pass in an array to return multiple records
@band_records = Band.find([5,8,22])It is important to know about order when retrieving records. Often times this is necessary, especially in the case of dates. It's very common in applications that items of all types be displayed to users in an ordering based on time.
#order will by default assume you want all the records
@gigs = Band.order('date asc')If however, you don't want all the records, you can use the limit method to do exactly that.
#now only the first 5 records will be retrieved
@gigs = Band.order('date asc').limit(5)A couple other useful methods that allow you to limit the number of records returned are first, and last. It's pretty easy to guess what they'll do.
#this will return the record that was inserted first into the table..
#so that means the first row created. It does this by looking for
#the record #with the lowest 'id', which is typically the primary key also known as the unique
#id for that row.
@first_gig = Band.first;
#the opposite of first, this is the most recently created record in the table
@last_gig = Band.last;The most common workflow when querying a database is usually asking it for particular records, depending on what the application does. In other words you will need to know how to formulate queries to get back just the data you are looking for. These are queries based on conditions, that is, return row where a particular condition is met. It looks like this:
#This will return only the gig field, not the whole row, and only
# the gig value from the row where the pay column value = $50.00
@query_result = Band.select('gig').where('pay = ?',"$50.00")There's something savagely important you should take note of in the above code. What's up with the '?' in the where clause? What you are looking at is properly parameterized SQL. In this type of query each '?' is a placeholder for a value, and that value is the second parameter to the where method...in this case "$50.00". So the first question mark is matched with the first parameter and so on down the line. You can have many question marks and they will be matched in order with the parameters following the statement.
An example with multiple parameters:
@query_result = Band.where(' gig = ? and pay = ?', "Bob's", "$50.00")If instead of using parameterized SQL, you used string interpolation like the example below...go ahead and give yourself a round of applause, you just made it onto the "Let's party list" of every hacker in Eastern Europe, Russia, China...and let's be honest..the US as well. What's so bad about this? If you take user input (params[:gig] is user input ) and use it directly in an SQL query, you are opening your application up to a classic SQL injection attack. It's exactly like it sounds, instead of entering a gig name, a user will insert a cleverly formatted SQL string that may return unauthorized records or cause all sorts of other mayhem. AVOID....AVOID
#DON'T DO THIS
@query_result = Band.select("gig").where("gig = '${params[:gig]}'")Rails also provides us with a simpler syntax for issuing these types of commands:
@query_result = Band.select("gig").where(gig: params[:gig])Ok, now that you've learned that super important lesson, on to lighter topics.
Check if a band has a particular gig. Checking for the existence of records can often be useful.
@gig = Band.exists?(gig:'Staples Center') ##Relationships
Our Band model is pretty functional, we can save an retrieve values and generally have it interact with the database which is pretty cool. But in reality we likely want to have our models interact with one another. AR gives us the ability to do this by defining relationships on our models.
So to start let's create an Artist class, because what good is a band without an artist. We'll then associate this artist with a particular band record. This will in some sense give us the ability to work with a Band and Artist as one collective record, while maintaining their distinction as separate entities, living in separate tables. Let's look an Artist and Band class that exist in this type of relationship.
class Artist < ActiveRecord::Base
#an artist is in just one band
belongs_to :band
end
class Band < ActiveRecord::Base
#we'll assume that a band only has one artist, which in reality
#is probably not a bad assumption
has_one :artist
end
#the belonging record is the one that houses the key of
#the owning object: owning object being the one with the 'has_one'
#attribute, in this case Band
+-------------------Artists----------------+
| id | band_id | name |
| | | |
|-----+-------------+----------------------+
| | | |
| 1 | 2 | sting |
|-----+-------------+----------------------+
| | | |
| 2 | 3 | madonna |
+-------------------------------------------Let's turn our attention to the Artists table above. The key thing to note is the band_id that gets embedded in the Artists table. This band_id is the id column from the Band table. The key to understanding relationships:
- The primary key from one table is embedded in another table, and this embedding is the link that forms the relationship.
If we asked rails: "Hey rails, can you get me the Artist that belongs to this band I have? I'm currently holding a Band object with an id of 2, does that help?"
rails would say: "no problem, let me look at Artists table and match the id of the band your holding with the band_id column, if I find a match I'll give you back that artist row I find."
And now how does that look in practice?
#create and save a new artist
artist = Artist.create(name:'Sting')
#grab a band record
band = Band.first
#make the artist 'belong to' a band.
band.artist = artistThis is one brief example of a simple yet useful 'has_one:belongs_to' relationship. We will go much deeper into relationships, but for now a clear understanding of how this one works is sufficient.
###Generators
Generating models in Rails is a very important process as it not only involves the application, but the database as well. A resource to spend some time with can be found here
####Exercise walk through
Please take the time to work through the commands here. Sharpen your picture of AR and you will sharpen your picture of rails.