Last week I introduced that my pair and I have started using SQL Views more often in our project. This week I was going to discuss finer points on implementation, thankfully I found Josh Davis’ has_one view post at HashRocket.
The rocket example I discussed covered an activity log of all the rocket’s states RocketActivity, with a custom view being used to return the most recent of these RocketCurrentActivity. One of a few solutions to this in SQL is a sub select on the same activities table.
CREATE VIEW rocket_current_activities AS
SELECT
rocket_activities.status AS status
A few weeks down the way we’re going to have some change in requirements, this is where we would drop the existing view a recreate it with the new SQL.
Getting started with migrations is helped immensely by lomba/schema_plus a gem which alongside index and foreign key improvements, also gives you a method for creating SQL views, which it then stores in you schema.rb.
class RocketActivityViewFixes < ActiveRecord::Migration
def change
create_view :rocket_activities, "SELECT * FROM...", force: true
end
Unfortunately views can get long and in-depth, can change multiple times before hitting any one environment and contain important information about available columns and data types. We needed more than a one line truncation of what a view is at any point in time.
To solve this we started to store copies of our sql and store them under db/views/name_of_view/timestamp_name_of_view.sql, and call them out something like this.
class RocketActivityViewFixes < ActiveRecord::Migration
def change
create_view :rocket_activities, view_sql('20130215155853', 'rocket_activities')
end
The view_sql method is rather simple, and just an example of how you could structure your query files.
ActiveRecord::Migration.class_eval do
def view_sql(timestamp,view)
File.read(Rails.root.join("db/views/#{view}/#{timestamp}_#{view}.sql"))
end
As simple as this is, if you can start to drop your data joining into the dbms without impacting on support for teams and multiple environments, you’re going to feel a lot less pain making the move to SQL views.
If this doesn’t convince you that views are the bees knees, maybe the next time I get around to posting about how we use views to power our search indexes will be exciting to you. Once again, stay tuned.
About the Author