Introduction
If you’ve worked with PostgreSQL in other environments and your database tables have associated metadata based on their column values, you might be accustomed to creating views for those tables.
Active Record doesn’t have out-of-the-box support for views. But with a small amount of work, it’s possible to back your AR classes with “updateable” views that emulate tables.
Motivation
Why would you want to create views in the database itself, rather than simply implementing pseudo-attributes in your Active Record classes?
- Using views can vastly simplify the process of ordering selected rows based on multiple columns or columns from other tables.
- Since views are implemented as rules, they can be optimized to perform better than equivalent queries from Active Record.
I’ll illustrate the first point in this Howto.
An Example: Quote Database
Let’s say we are creating a model that, alongside a quotation, has tallies of votes for and against it. For each quote, we want to know- the score, or the number of votes for that quote minus the number of votes against it,
- the votes, or the sum of votes for and against the quote, and
- the activity, or the ratio of the quote’s votes to the largest number of votes of any quote.
We’ll use a view because we want to be able to easily sort on any of these meta-columns.
Our starting SQL
Here’s the SQL we’ll start with—a backing table, a few procedures to calculate the stuff we need, and a read-only view to put it all together:
-- The backing table
CREATE SEQUENCE quotes_id_seq;
CREATE TABLE quotes_table (
id integer NOT NULL DEFAULT nextval('public.quotes_id_seq'::text),
content text DEFAULT ''::text NOT NULL,
votes_for integer DEFAULT 0 NOT NULL,
votes_against integer DEFAULT 0 NOT NULL
);
ALTER TABLE ONLY quotes_table
ADD CONSTRAINT quotes_table_pkey PRIMARY KEY (id);
-- Returns the score of the quote with the given id
CREATE FUNCTION quote_score(integer) RETURNS integer
AS 'SELECT votes_for - votes_against FROM quotes_table WHERE id = $1;'
LANGUAGE sql;
-- Returns the number of total votes of the quote with the given id
CREATE FUNCTION quote_votes(integer) RETURNS integer
AS 'SELECT votes_for + votes_against FROM quotes_table WHERE id = $1;'
LANGUAGE sql;
-- Returns the highest number of votes of any quote
CREATE FUNCTION quote_most_votes() RETURNS integer
AS 'SELECT quote_votes(id) AS votes FROM quotes_table
ORDER BY votes DESC LIMIT 1;'
LANGUAGE sql;
-- Returns the activity of the quote with the given id
CREATE FUNCTION quote_activity(integer) RETURNS numeric
AS 'SELECT CASE WHEN most_votes = 0 OR most_votes IS NULL then 0.0
ELSE 100*(votes/(most_votes + 0.0)) END
FROM (SELECT quote_most_votes() AS most_votes,
quote_votes($1) AS votes
FROM quotes_table WHERE id = $1) AS quotes_source;'
LANGUAGE sql;
-- The view, with score, votes, and activity for each row
CREATE VIEW quotes AS
SELECT id, content, votes_for, votes_against,
quote_score(id) AS score,
quote_votes(id) AS votes,
quote_activity(id) AS activity
FROM quotes_table;
Our starting Active Record class
require 'active_record'
class Quote < ActiveRecord::Base
end
So what happens?
Let’s try using what we have so far:
$ irb -r config/environment.rb
irb(main):001:0> require 'quote'
=> true
irb(main):002:0> quote = Quote.new 'content' => 'hello', 'votes_for' => 4,
'votes_against' => 1
=> #<Quote:0x408a8ae4 @attributes={"score"=>nil, "votes_for"=>4, "votes"=>nil,
"activity"=>nil, "votes_against"=>1, "content"=>"hello"}, @new_record=true>
irb(main):003:0> quote.save
ActiveRecord::StatementInvalid: ERROR: cannot insert into a view
HINT: You need an unconditional ON INSERT DO INSTEAD rule.
: INSERT INTO quotes ("score", "votes_for", "votes", "votes_against",
"activity", "content") VALUES(NULL, '4', NULL, '1', NULL, 'hello')
We need to write SQL rules so that Active Record can INSERT INTO the view. And we need to tell Active Record that our special view columns are read-only.
Making our view updateable
N.B.: This isn’t as clean or as robust as it should be, but it works—as long as you deliver SQL in the exact form that these rules require. I’d really appreciate it if someone with more SQL knowledge than I would revise this section appropriately.
We can use PostgreSQL rules to implement INSERT INTO, UPDATE, and DELETE FROM functionality for our view:
-- Implement INSERT INTO for the quotes view
CREATE RULE quotes_insert AS ON INSERT TO quotes DO INSTEAD
INSERT INTO quotes_table (content, votes_for, votes_against)
VALUES (new.content, new.votes_for, new.votes_against);
-- Implement UPDATE for the quotes view
CREATE RULE quotes_update AS ON UPDATE TO quotes DO INSTEAD
UPDATE quotes_table SET id = new.id, content = new.content,
votes_for = new.votes_for,
votes_against = new.votes_against
WHERE id = old.id;
-- Implement DELETE FROM for the quotes view
CREATE RULE quotes_delete AS ON DELETE TO quotes DO INSTEAD
DELETE FROM quotes_table WHERE id = old.id;
Making our meta-columns read-only
The score, votes, and activity columns shouldn’t be mutable, and thanks to our view rules, they aren’t. (Those columns are simply ignored when we specify them in an INSERT or UPDATE.) But that doesn’t stop us from changing the associated attributes in our AR model. That’s not good!
Active Record doesn’t have support for making columns read-only, but you can simulate the effect by using attr_protected and defining empty writer methods:
require 'active_record'
class Quote < ActiveRecord::Base
attr_protected :score, :votes, :activity
def score=(ignored) end
def votes=(ignored) end
def activity=(ignored) end
end
So what happens? part deux
We’ll open up irb again and see:
$ irb -r config/environment.rb
irb(main):001:0> require 'quote'
=> true
irb(main):002:0> quote = Quote.new 'content' => 'hello', 'votes_for' => 4,
'votes_against' => 1
=> #<Quote:0x408a8ae4 @attributes={"score"=>nil, "votes_for"=>4, "votes"=>nil,
"activity"=>nil, "votes_against"=>1, "content"=>"hello"}, @new_record=true>
irb(main):003:0> quote.save
=> true
If we have used “serial” instead of “integer” (and the default value) we had encountered an error: “PGError: ERROR: relation “quotes_id_seq” does not exist”. Active Record’s PostgreSQL adapter assumes a standard sequence name so that it can find the ID of the last record created. Our setup would be incompatible, and we would need to accommodate for it.
So what happens? part are-we-finished-yet
One last time:
$ irb -r config/environment.rb
irb(main):001:0> require 'quote'
=> true
irb(main):002:0> quote = Quote.new 'content' => 'hello', 'votes_for' => 4,
'votes_against' => 1
=> #<Quote:0x408a8ae4 @attributes={"score"=>nil, "votes_for"=>4, "votes"=>nil,
"activity"=>nil, "votes_against"=>1, "content"=>"hello"}, @new_record=true>
irb(main):003:0> quote.save
=> true
Excellent! The new quote was added to the database. Let’s test updating and deleting:
irb(main):004:0> quote.content = 'goodbye'
=> "goodbye"
irb(main):005:0> quote.save
=> true
irb(main):006:0> quote.destroy
=> []
irb(main):007:0> Quote.find_all
=> []
And now we can finally test sorting on our meta-columns:
irb(main):008:0> 5.times do |i|
irb(main):009:1* quote = Quote.new 'content' => i
irb(main):010:1> quote.votes_for = (rand*10).to_i
irb(main):011:1> quote.votes_against = (rand*10).to_i
irb(main):012:1> quote.save
irb(main):013:1> end
=> 5
irb(main):014:0> Quote.find_all(nil, 'activity DESC').map do |quote|
irb(main):015:1* [quote.id, quote.activity.round]
irb(main):016:1> end
=> [[3, 100], [2, 100], [6, 79], [4, 64], [5, 57]]
—CesarBallardini? Made to work on PostgreSQL 8.1.
category:Howto
Introduction
If you’ve worked with PostgreSQL in other environments and your database tables have associated metadata based on their column values, you might be accustomed to creating views for those tables.
Active Record doesn’t have out-of-the-box support for views. But with a small amount of work, it’s possible to back your AR classes with “updateable” views that emulate tables.
Motivation
Why would you want to create views in the database itself, rather than simply implementing pseudo-attributes in your Active Record classes?
- Using views can vastly simplify the process of ordering selected rows based on multiple columns or columns from other tables.
- Since views are implemented as rules, they can be optimized to perform better than equivalent queries from Active Record.
I’ll illustrate the first point in this Howto.
An Example: Quote Database
Let’s say we are creating a model that, alongside a quotation, has tallies of votes for and against it. For each quote, we want to know- the score, or the number of votes for that quote minus the number of votes against it,
- the votes, or the sum of votes for and against the quote, and
- the activity, or the ratio of the quote’s votes to the largest number of votes of any quote.
We’ll use a view because we want to be able to easily sort on any of these meta-columns.
Our starting SQL
Here’s the SQL we’ll start with—a backing table, a few procedures to calculate the stuff we need, and a read-only view to put it all together:
-- The backing table
CREATE SEQUENCE quotes_id_seq;
CREATE TABLE quotes_table (
id integer NOT NULL DEFAULT nextval('public.quotes_id_seq'::text),
content text DEFAULT ''::text NOT NULL,
votes_for integer DEFAULT 0 NOT NULL,
votes_against integer DEFAULT 0 NOT NULL
);
ALTER TABLE ONLY quotes_table
ADD CONSTRAINT quotes_table_pkey PRIMARY KEY (id);
-- Returns the score of the quote with the given id
CREATE FUNCTION quote_score(integer) RETURNS integer
AS 'SELECT votes_for - votes_against FROM quotes_table WHERE id = $1;'
LANGUAGE sql;
-- Returns the number of total votes of the quote with the given id
CREATE FUNCTION quote_votes(integer) RETURNS integer
AS 'SELECT votes_for + votes_against FROM quotes_table WHERE id = $1;'
LANGUAGE sql;
-- Returns the highest number of votes of any quote
CREATE FUNCTION quote_most_votes() RETURNS integer
AS 'SELECT quote_votes(id) AS votes FROM quotes_table
ORDER BY votes DESC LIMIT 1;'
LANGUAGE sql;
-- Returns the activity of the quote with the given id
CREATE FUNCTION quote_activity(integer) RETURNS numeric
AS 'SELECT CASE WHEN most_votes = 0 OR most_votes IS NULL then 0.0
ELSE 100*(votes/(most_votes + 0.0)) END
FROM (SELECT quote_most_votes() AS most_votes,
quote_votes($1) AS votes
FROM quotes_table WHERE id = $1) AS quotes_source;'
LANGUAGE sql;
-- The view, with score, votes, and activity for each row
CREATE VIEW quotes AS
SELECT id, content, votes_for, votes_against,
quote_score(id) AS score,
quote_votes(id) AS votes,
quote_activity(id) AS activity
FROM quotes_table;
Our starting Active Record class
require 'active_record'
class Quote < ActiveRecord::Base
end
So what happens?
Let’s try using what we have so far:
$ irb -r config/environment.rb
irb(main):001:0> require 'quote'
=> true
irb(main):002:0> quote = Quote.new 'content' => 'hello', 'votes_for' => 4,
'votes_against' => 1
=> #<Quote:0x408a8ae4 @attributes={"score"=>nil, "votes_for"=>4, "votes"=>nil,
"activity"=>nil, "votes_against"=>1, "content"=>"hello"}, @new_record=true>
irb(main):003:0> quote.save
ActiveRecord::StatementInvalid: ERROR: cannot insert into a view
HINT: You need an unconditional ON INSERT DO INSTEAD rule.
: INSERT INTO quotes ("score", "votes_for", "votes", "votes_against",
"activity", "content") VALUES(NULL, '4', NULL, '1', NULL, 'hello')
We need to write SQL rules so that Active Record can INSERT INTO the view. And we need to tell Active Record that our special view columns are read-only.
Making our view updateable
N.B.: This isn’t as clean or as robust as it should be, but it works—as long as you deliver SQL in the exact form that these rules require. I’d really appreciate it if someone with more SQL knowledge than I would revise this section appropriately.
We can use PostgreSQL rules to implement INSERT INTO, UPDATE, and DELETE FROM functionality for our view:
-- Implement INSERT INTO for the quotes view
CREATE RULE quotes_insert AS ON INSERT TO quotes DO INSTEAD
INSERT INTO quotes_table (content, votes_for, votes_against)
VALUES (new.content, new.votes_for, new.votes_against);
-- Implement UPDATE for the quotes view
CREATE RULE quotes_update AS ON UPDATE TO quotes DO INSTEAD
UPDATE quotes_table SET id = new.id, content = new.content,
votes_for = new.votes_for,
votes_against = new.votes_against
WHERE id = old.id;
-- Implement DELETE FROM for the quotes view
CREATE RULE quotes_delete AS ON DELETE TO quotes DO INSTEAD
DELETE FROM quotes_table WHERE id = old.id;
Making our meta-columns read-only
The score, votes, and activity columns shouldn’t be mutable, and thanks to our view rules, they aren’t. (Those columns are simply ignored when we specify them in an INSERT or UPDATE.) But that doesn’t stop us from changing the associated attributes in our AR model. That’s not good!
Active Record doesn’t have support for making columns read-only, but you can simulate the effect by using attr_protected and defining empty writer methods:
require 'active_record'
class Quote < ActiveRecord::Base
attr_protected :score, :votes, :activity
def score=(ignored) end
def votes=(ignored) end
def activity=(ignored) end
end
So what happens? part deux
We’ll open up irb again and see:
$ irb -r config/environment.rb
irb(main):001:0> require 'quote'
=> true
irb(main):002:0> quote = Quote.new 'content' => 'hello', 'votes_for' => 4,
'votes_against' => 1
=> #<Quote:0x408a8ae4 @attributes={"score"=>nil, "votes_for"=>4, "votes"=>nil,
"activity"=>nil, "votes_against"=>1, "content"=>"hello"}, @new_record=true>
irb(main):003:0> quote.save
=> true
If we have used “serial” instead of “integer” (and the default value) we had encountered an error: “PGError: ERROR: relation “quotes_id_seq” does not exist”. Active Record’s PostgreSQL adapter assumes a standard sequence name so that it can find the ID of the last record created. Our setup would be incompatible, and we would need to accommodate for it.
So what happens? part are-we-finished-yet
One last time:
$ irb -r config/environment.rb
irb(main):001:0> require 'quote'
=> true
irb(main):002:0> quote = Quote.new 'content' => 'hello', 'votes_for' => 4,
'votes_against' => 1
=> #<Quote:0x408a8ae4 @attributes={"score"=>nil, "votes_for"=>4, "votes"=>nil,
"activity"=>nil, "votes_against"=>1, "content"=>"hello"}, @new_record=true>
irb(main):003:0> quote.save
=> true
Excellent! The new quote was added to the database. Let’s test updating and deleting:
irb(main):004:0> quote.content = 'goodbye'
=> "goodbye"
irb(main):005:0> quote.save
=> true
irb(main):006:0> quote.destroy
=> []
irb(main):007:0> Quote.find_all
=> []
And now we can finally test sorting on our meta-columns:
irb(main):008:0> 5.times do |i|
irb(main):009:1* quote = Quote.new 'content' => i
irb(main):010:1> quote.votes_for = (rand*10).to_i
irb(main):011:1> quote.votes_against = (rand*10).to_i
irb(main):012:1> quote.save
irb(main):013:1> end
=> 5
irb(main):014:0> Quote.find_all(nil, 'activity DESC').map do |quote|
irb(main):015:1* [quote.id, quote.activity.round]
irb(main):016:1> end
=> [[3, 100], [2, 100], [6, 79], [4, 64], [5, 57]]
—CesarBallardini? Made to work on PostgreSQL 8.1.
category:Howto