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

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

The ActiveRecord connection adapter is meant to wrap and abstract away the underlying driver specific to database, and is meant to provide an interface which is common for database tasks such as creating and destroying databases, modifying tables, updating, deleting, and inserting data, managing transactions and running queries . The connection adapter is normally used internally by ActiveRecord but can be used without the help of ActiveRecord models as well.

A connection adapter can be obtained in the following manner:

 connection = Category.connection
 object = Category.find(1)
 connection = object.connection

Most applications connect to only one database which is defined in the database.yml file. In such a scenario every class which inherits from ActiveRecord::Base will be using the same connection. But in some special cases the application may also connect to a secondary database. That is the case in which some ActiveRecord classes connect to a secondary database. In such cases extra care need to take so that every class asks for a connection from the right database.

Rails generally opens several connections at once, and these connections are managed in a pool. Each connection adapter object forms a single connection to some database. Connections can run only one SQL statement at a time, so generally one connection is opened per thread. When a job needs a connection to database, it checks out one of the pool which is returned when it finishes for use by another task.

Running Low-Level Queries

ActiveRecord “model” objects are returned by ost calls in the standard ActiveRecord API. There might be cases in which you want to bypass the overhead involved in creating full ActiveRecord objects, or maybe want to query data that does not have a corresponding ActiveRecord class. SQL queries can be written using the connection adapter's low-level query methods.

In this first example, we get the “category_name” value from a single row in our “categoriess” table. If we only need the category name, we can grab the connection object and use the select_value method as shown below:

 connection = Category.connection
 category_name = connection.select_value("SELECT name FROM categories WHERE id=1")
 # => "Football"

Migrations

Migrations help to version the various changes made to databases. It also allow developers to track a set of changes made to production or development databases and to rollback to a previous version if needed.

Building a Migration

You can either build the migration on its own using

ruby script/generate migration User

Specific commands can be written afterwards to create custom SQL. A model can also be created that comes with the migration.

ruby script/generate model User name:string user_id:integer

The migration will generate a couple of new files under the "db" directory. The contents of such a generate file are as follows:

 # 9889904091223123_create_user.rb
 class CreateUsers < ActiveRecord::Migration
   def self.up
     create_table :users do |t|
       t.string :name
       t.integer :user_id
       t.timestamps
     end
   end

   def self.down
     drop_table :users
   end
 end

The "9889904091223123" at the beginning of the file-name is the timestamp. The timestamps will be different depending on the times of creation or modification the database. This is helpful to rollback to a previous state if needed. This way the developer need not remember how the current state is reached and how can he go back to a previous state.

The self.up from the previous code snippet creates the User table and add the columns. The self.down method is used to drop the table and to remove all the contents. The self.up and self.down methods are necessary to keep the database consistent after a rollback.

Rails adds an additional column called the "timestamps" to keep track of when each row was added. Rails also creates a primary key of the form "model_name"_id which increments automatically every time a row is added.

Different types of datatypes can be used with ActiveRecord. Some of the most commonly used ones are:

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

The migration file can be written into the database using the following command:

rake db:migrate

The above command will create the table and the various columns. This command can be used to migrate many files in one go. Connections between tables in the database can be introduced using references in the model.