Rietta
Rietta: Web Apps Where Security Matters
You are reading The Rietta Blog, a publication about the web since 2005.

Using Rails and SQL Views for a Report

Comments

This post is part of a series of using SQL views in Rails. The latest post covers adding a rake task to your Rails project to provide rake db:views to enhance your workflow.

On this Thanksgiving, 2013, I am sharing this post as the first in what I plan to become an annual tradition on this blog. Since I was a young teenager, I always looked forward to thanksgiving as a time to read and write. Now, I can share even more with you.

In this post, I will show you:

  • What a SQL view, also known as a Virtual Relation, is in a Relational Database
  • How to create one using a Rails migration
  • How to define a Rails ActiveRecord model that sits on top of it just as if it were a normal table

Why I use SQL Views in my Development Practice

Before I became a full time Ruby on Rails developer in 2011, I had worked as a web developer on other technology stacks for a decade. In each of those stacks the part that has changed the least is the underlaying SQL server. Therefore, starting with MySQL 3 in 1999, I have grown to love the power and flexibility that SQL gives the developer for working with relational data. I have now worked with MySQL, Microsoft SQL Server, and PostgreSQL extensively in my career.

So with this background, I look for ways to use the full power of the database server when it makes sense to do so. To me, Ruby on Rails may well be the best general purpose web application development platform to date. ActiveRecord is a wonderful ORM, but there come times when the SQL code that it generates is just not the best tool for the job. The SQL server has capabilities that go beyond what ActiveRecord is going to ever ask it to do.

In my projects, I have used SQL views to compute complex statistics, to discover connections among objects in the system (think of a search index with data mining) and then even have the PostgreSQL database tell the Rails application which objects are related by computing polymorphic associations.

These sorts of situations are beyond the scope of what is needed to show you so that you can try this pattern out for yourself. Just keep in mind that this technique goes well beyond the simple report example that I am sharing with you today. SQL is powerful, flexible, and fast!

What is a SQL View?

In formal terms, a base table in a relational database is called a Relation. But the magic is that these tables are only one type of relation and that all relations are fundamentally interchangeable! This property is called the Principle of Interchangeability and it gives rise to the ability to implement powerful object modeling, inheritance, and composition all from within the relational database!

For an even more formal definition, let’s take a look at the definition given in the database textbook that was co-authored by one of my undergraduate research advisors, Dr. Sham Navathe.

A view in SQL terminology is a single table that is derived from other tables. These other tables could be base tables or previously defined views. A view does not necessarily exist in physical form; it is considered a virtual table, in contrast to base tables, whose tuples are actually stored in the database. This limits the possible update operations that can be applied to views, but it does not provide any limitations on querying a view… (Elmasri, Navathe, Fundamentals of Database Systems Fourth Edition, 2004).

Drs. Elmasri and Navathe use the term Tuple, which is a formal computer science term for an ordered list of elements that have various formal properties. In this context, just think of it as a row in a database table.

The major SQL servers all have support for views, you can create one like this:

1
2
CREATE VIEW relation_name AS
  SELECT ... ;

The query can be simple or complex. It can involve a single table or it can perform tens or hundreds of joins. It can expose all fields from the constituent relations, or just a relevant subset, or it can compute new derived fields that are then presented as a ordinary data element in the resulting relation. In other words, it is a very flexible capability of the database server.

For more information about the syntax for your SQL server, see PostgreSQL: Create View, T-SQL (Microsoft) Create View, or MySQL Create View syntax.

Using a SQL View with Rails for a Report

At the most basic level, a SQL view is a stored query. It is a first class relation with its own name. To your Rails application, it is just like an ordinary table in the database that ActiveRecord can deal with without any special Ruby code.

Here is a concrete example of this pattern to create a reporting interface in a Rails application. The first example is a SQL view that I wrote for one of the reports in a project (in the migration that sets it up). The second is the Ruby example of the class that sits upon it.

The Migration to Set Up the SQL View

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
  class CreateReportStatePopularities < ActiveRecord::Migration
    def up
      self.connection.execute %Q( CREATE OR REPLACE VIEW report_state_popularities AS
          SELECT
              MAX(M.id) AS id,
              UPPER(A.state) AS state,
              country,
              COUNT(M.id) AS popularity
            FROM submitted_requests M INNER JOIN addresses A ON
              M.address_id = A.id
            WHERE A.state != ''
            GROUP BY UPPER(A.state), country
            ORDER BY popularity DESC;
          )
    end

    def down
      self.connection.execute "DROP VIEW IF EXISTS report_state_popularities;"
    end
  end

Notice that the class is in the Report namespace and the view is given the appropriate relation name that ActiveRecord expects by default.

Read-only Report Model

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
  # == Schema Information
  #
  # Table name: report_state_popularities
  #
  #  id         :integer
  #  state      :text
  #  country    :string(255)
  #  popularity :integer
  #

  # This is a report from a SQL View
  module Report
    class StatePopularity < ActiveRecord::Base
      self.table_name = 'report_state_popularities'

      def to_s
        "#{state}, #{popularity}"
      end

      def name
        state
      end

      def self.us_region
        StatePopularity.where(country: "United States")
      end

      protected

      # The report_state_popularities relation is a SQL view,
      # so there is no need to try to edit its records ever.
      # Doing otherwise, will result in an exception being thrown
      # by the database connection.
      def readonly?
        true
      end
    end # class
  end # module

That’s all there is to the basic model. Now, you can do any ActiveRecord calls with it that you want, except for those that would update the model being that it is a read-only relation.

The Report’s Controller

1
2
3
4
5
6
7
8
  class PopularityController < ApplicationController
    def state_popularity
      @states = Report::StatePopularity.us_region
      respond_to do |format|
        format.html {}
      end
    end
  end

The Report’s Rails View

1
2
3
4
5
6
7
8
9
10
11
12
  <table>
    <tr>
      <th>State</th>
      <th>Popularity</th>
    </tr>
    <% @states.each do |state| %>
      <tr>
        <td><%= state.name %></td>
        <td><%= state.popularity %></td>
      </tr>
    <% end %>
  </table>

Preserving the SQL views in your schema

One last important step is to configure the application to store its schema in SQL because the default schema.rb does not understand views.

Rails provides an option to configure the format that the schema is stored in, as explained in the Configuring Rails Applications Guide:

config.active_record.schema_format controls the format for dumping the database schema to a file. The options are :ruby (the default) for a database-independent version that depends on migrations, or :sql for a set of (potentially database-dependent) SQL statements.

Since SQL views are one of those SQL features that is not supported by all possible database servers that is the option that needs to be set.

In config/application.rb, uncomment the line that sets the schema format to SQL. Like this:

1
2
  # create a db specific dump in structure.sql file rather than schema.rb. IIRC when :sql is set rails test code loads that instead of the schema.rb
  config.active_record.schema_format = :sql

And then re-run you migrations with

1
  rake db:migrate db:test:prepare

That should build the structure.sql file that replaces the default schema.rb so that you views will be preserved in your test database. Finally remove the now unused schema.rb from the project.

Concluding Remarks

SQL views are a powerful capability of a relational database server. This functionality can be easily used to implement important reporting, data mining, and other functionality to a web application built with Ruby on Rails in a manner that may be faster and more concise than an implementation that uses only typical ActiveRecord patterns. The complexity of dealing with the relational data is abstracted away from the Rails application that is a consumer of the data, keeping the model, controllers, and Rails view concisely focused on their appropriate roles.

I invite you to experiment with this pattern and to consider using it in your next project. It is a powerful tool that has served me well over the years.

Invitation to the Web Application Topics Newsletter

This is an archived issue of the Web Application Topics Newsletter. If you are interested in having future issues sent directly to your e-mail, please sign up for free, today. For back issues, see the Web Application Topics category on this blog.

Additional Reading on this Topic

About Frank Rietta

Frank Rietta's photo

Frank Rietta is specialized in working with startups, new Internet businesses, and in developing with the Ruby on Rails platform to build scalable businesses. He is a computer scientist with a Masters in Information Security from the College of Computing at the Georgia Institute of Technology. He teaches about security topics and is a contributor to the security chapter of the 7th edition of the "Fundamentals of Database Systems" textbook published by Addison-Wesley.

Comments