CSC/ECE 517 Fall 2012/ch1b 1w63 dv

Active Records

Introduction

The Active Record pattern is a Design pattern in Software Engineering which deals with the approach to store and access data in a database. The interface of an object conforming to this pattern would contain functions to perform operations like Insert, Read, Update, and Delete. The Object will have properties that correspond to the columns in the underlying database table. This pattern is realised through ORM (Object-Relational Mapping) libraries in Programming languages.

ActiveRecord is a module for Ruby that can be used for ORM. Thus, it is obvious that ActiveRecord will form a part of the Model in an MVC application developed in Ruby. The rest of the article discusses ActiveRecord that is the Ruby module for implementing the Active Record pattern.

The ActiveRecord module insulates the developer from the need to use SQL in most cases. Internally, It will perform queries on the database which corresponds to the method invoked on the object. This module is compatible with most database systems (most used ones like MySQL, PostgreSQL and SQLite). Moreover, regardless of which database system the developer uses, the Active Record method format always remains the same.

Naming

The ActiveRecord module uses a convention for naming classes, tables and fields so that the amount of configuration needed to get the functionality working is minimal. There are naming conventions on file naming, class naming, table naming etc.

Reserved names and Attributes

Certain names are reserved and should not be used (even in the model as attributes). Some of them are listed below:

 lock_version.
 
 type - This is only used when you have single table inheritance and must contain a class name.
 
 id - Reserved for primary keys.
 
 table_name_count - Reserved for counter cache.
 
 position - Reserved for acts_as_list.
 
 parent_id - Reserved for acts_as_tree.
 
 lft - Reserved for acts_as_nested_set.
 
 rgt - Reserved for acts_as_nested_set.
 
 quote - Method in ActiveRecord::Base which is used to quote SQL.
 
 template.
 

Class Naming

ActiveRecord classes are named in singular form. e.g User

Table Naming

Tables for ActiveRecord objects are named in plural form by default. e.g. Users This naming convention can be circumvented by using below:

a) Set use_pluralization In the environment.rb file we can specify

  ActiveRecord::Base.use_pluralization = false. 

This will disable pluralization for all ActiveRecord objects.

b.) Use set_table_name You can call set_table_name to specify a custom table name for a particular model. For example:

 class User < ActiveRecord::Base
   set_table_name 'user'
 end

3 CRUD

CRUD stands for 'Create', 'Read' , 'Update' and 'Delete'. These are the four basic operations which are generally performed on tables in a database. The ActiveRecord module provides predefined methods for the basic CRUD operations for the model.

3.1 Create

A new record can be created in the table by invoking the “save” function on the model object whose record has to be created in the database. ActiveRecord will use the Object's attributes as the field values for the record. The data is not persisted in the database until we call the save function. 

  @user = User.new
  @user.name = “abc”
  @user.age = 23
  @user.save      #returns a boolean indicating whether the save was successful or not (whether a new record was created or not)

ActiveRecord provides another convinient way to create a new record without creating instantiating the model explicitly and then using “save”. To do this, we use the 'create' function. By default the primary key used in the table is “id” which is generated automatically.

   User.create(:name=>”xyz”, :age=”23”)

3.2 Read

A record can be read from the table by using the various functions like “find” (find the model record by specifying a value used in its primary key), “where”, “all” , “first” and “last”. All these functions instantiate a new Object for the model and populate its attributes using the fields of the record.

@user_first = User.first #Finds and returns the 1st User from the table

@user_last = User.last #Finds and returns the Last User from the table

@all_users = User.all # Returns all the Users from the table

@my_user = User.find(5) #Finds and returns the record from the users table whose id = 5

@my_other_user = User.where(:name=>”abc”) #Finds and returns the user whose “name” is “abc” .

Dynamic Finders

Some of the most common searchs performed on databases are to return the rows where a column matches a given value. In many other languages and frameworks, we would generally need to construct SQL queries to perform these searches. ActiveRecord uses Ruby’s dynamic power to do this for us. [edit] Connecting to the Database For example, our User model has attributes such as name and age. We can use these names in finder methods to return rows where the corresponding columns match some value:

@my_user = User.find_by_name(“abc”) @my_user = User.find_by_age(15)

3.3 Update

A record in the table corresponding to a given model instance can be Updated by using the function “save”.

@my_user = User.find(5) #Finds and returns the record from the users table whose id = 5 @my_user.name = “test” @my_user.save

Moreover, we can combine the functions of reading a row and updating it using the class methods update and update_all. The update method takes an id parameter and a set of attributes. It fetches the corresponding row, updates the given attributes, saves the result to the database, and returns the model object.

@my_user = User.update(1, :name=”test3”) @result = User.update_all(“age= age+1”)

3.4 Delete

A record can be deleted from the table by invoking the “destroy” functionality on the instance of the object. The destroy instance method deletes from the database the row corresponding to a particular model object. It then freezes the contents of that object, preventing future changes to the attributes.

@my_user = User.find(5) #Finds and returns the record from the users table whose id = 5 @my_user.destroy # deletes the record corresponding to the user with id = 5 from the table # ... my_user is now frozen

It also has two class-level methods, delete and delete_all. The delete method takes a single id or an array of ids and deletes the corresponding row(s) in the underlying table. delete_all deletes rows matching a given condition. User.delete(1) User.delete([2,3,4,5]) User.delete_all(["age < ?" , 18])

The “delete” methods bypass the ActiveRecord callback and validation functions that may be defined for the model class, while the “destroy” methods ensure that they are all invoked. Hence, it is better to use the “destroy” methods as it ensures that our database is as per the business rules defined in the model.

Connecting to the Database

Hidden underneath ActiveRecord class is a useful low-level object called the ActiveRecord connection adapter. It wraps and abstracts away the underlying database-specific driver, and provides a common interface for database tasks such as creating and destroying databases, creating and modifying tables, inserting, updating, and deleting data, running queries, and managing transactions. Normally, the connection adapter is used internally by ActiveRecord, but can be accessed yourself if you want to talk to the database directly without using ActiveRecord “models.”

To obtain a connection adapter object, simply call the connection method on your ActiveRecord class or any ActiveRecord object:

 connection = User.connection
 obj = User.find(1)
 connection = obj.connection

In most Rails applications, you talk to just one database, as defined in the database.yml file. For such an application, every ActiveRecord class, including ActiveRecord::Base, will give you the same connection. So in those cases, it doesn’t matter which class you use. However, if the Rails application connects to a secondary database for some ActiveRecord classes (using the establish_connection method) then those classes will yield a connection object pointing at the secondary database. In those cases, you will need to pay attention to which database you need to talk to, and ask for a connection from the right class.

Each connection adapter object represents a single connection to a database. Rails generally opens several connections at once and manages them in a connection pool. When a task needs a database connection, it checks one out of the pool; when it finishes, it checks the connection back in so that the next task can use it. Connections can run only one SQL statement at a time, so generally one connection is opened per thread.

Migrations

<ref>Excerpts modified and republished from Steve Eichert's Ruby on rails Migrations Explained article.</ref> Migrations meant to solve the problem of rolling out changes to your database. By defining the changes to your database schema in Ruby files, development teams can ensure that all changes to the database are properly versioned. Additionally migrations help to ensure that rolling out changes to fellow developers as well as other servers (development, QA, production) is handled in a consistent and manageable fashion.

Building a Migration

You can either build the migration on its own using

ruby script/generate migration Category

and write the specific commands afterwards (if you want to create custom SQL, this is the way to go) or you can create a model that comes with the migration using

ruby script/generate model Category name:string amount:integer

The console tells you that there were some files created and some already existent. As mentioned before, Rails will never overwrite existing files unless stated otherwise.

Now lets take a look at the migration

  # 20090409120944_create_categories.rb
  class CreateCategories < ActiveRecord::Migration
    def self.up
      create_table :categories do |t|
        t.string :name
        t.integer :amount

        t.timestamps
      end
    end

    def self.down
      drop_table :categories
    end
  end

First of all, take a look at the number (20090409120944) in front of your file. This is the timestamp of your file and important for the creation of the database tables. This timestamp will always be different, depending on the exact time of the creation of your migration. The idea behind this is to have a "history" of all your migrations available.

But why is this important?

Imagine that you work on a Rails project and you create tables, alter columns or remove columns from your database via migrations. After some time, your client changes his mind and he wants only very basic features and you already started to create advanced features and altered the database. Because you can't remember all the changes that went into the database and their order, you will either spend a lot of time working on the database to have the "old" state available or you have to start from scratch because it would take too long to remember and redo all changes. This is where migration come in handy, because of the timestamp, Rails is able to recognize the changes in their actual order and all changes can be undone easily. Never alter the timestamp manually. This will certainly cause problems. For more on those topic, check out the "managing migrations" section

Speaking of undoing and redoing: notice the two methods inside your migration self.up and self.down. Both of them do exactly the opposite of each other. While self.up creates our categories table with all columns, self.down removes (drops) the table from the database with all its contents(!!). When Rails sees that the migration has not been moved to the database, it will use the self.up method, if you undo the migration, the self.down method gets executed. This way you can make sure that you will always be able to go back to a past state of your database. Keep in mind when writing own migrations always include a self.up and a self.down method to assure that the database state will be consistent after an rollback.

OK, let's start with the migration itself:

create_table :categories do |t|
      t.string :name
      t.integer :amount
      t.timestamps
end

We want to create a table called categories(create_table :categories) that has a name and an amount column. Additionally Rails adds an timestamp for us where it will store the creation date and the update date for each row. Rails will also create an primary key called model_id that auto-increments (1,2,3,...) with every row.

You can choose from a variety of datatypes that go with ActiveRecord. The most common types are:

• string
• text
• integer
• decimal
• timestamp
• references
• boolean

But wait, there is not yet a single table nor column in our database. We need to write the migration file into the database.

rake db:migrate

handles this job. The command is able to create the table and all the necessary columns inside the table. This command is not limited to migrating a single file so you can migrate an unlimited number of files at once. Rake also knows what migrations are already in the database so it won't overwrite your tables. For more info see "Managing Migrations".

To add a connection between your tables we want to add references in our model. References are comparable to foreign keys (Rails doesn't use foreign keys by default because not all databases can handle foreign keys but you can write custom SQL to make use of foreign keys) and tell your table where to look for further data.

Let's add another model to our already existent database. We want to create a category that has multiple products. So we need to reference this product in our category. We want to create a model:

ruby script/generate model Products name:string category:references

and insert it into the database

rake db:migrate

Note the type :references for the category. This tells Rails to create a column inside the database that holds a reference to our category. Inside our database there is now a category_id column for our product. (In order to work with these two models, we need to add associations inside our models, see Associations)

Managing Migrations

We already talked about how migrations can help you to organise your database in a very convenient manner. Now we will take a look at how this is achieved. You already know that the timestamp in the filename tells rails when the migration was created and Rake know what migrations are already inside the database.

To restore the state of the database as it was, say 5 migrations before the current, we can use

 $rake db:rollback STEP=5

This will undo the last 5 migrations that have been committed to the database.

To redo the last 5 steps, we can use a similar command

 $ rake db:migrate:redo STEP=5

You can also rollback or redo a specific version of a migration state, you just need to provide the timestamp:

 $ rake db:migrate:up VERSION=20080906120000

Choose whether you want the db_migrate:up method to be executed or the db_migrate:down method

Keep in mind, that restoring your database to a previous state will delete already inserted data completely!

References

<references/>