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')
endThe 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"))
endAs 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.