# Read only views from a legacy database

**URL:** <https://www.ruby-forum.com/t/read-only-views-from-a-legacy-database/85616>\
**Category:** Rails\
**Created:** [February 14, 2007, 10:30pm UTC](https://www.ruby-forum.com/t/read-only-views-from-a-legacy-database/85616 "2007-02-14T22:30:44Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![kreiggers](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/ac91a4/32.png) [@kreiggers](https://www.ruby-forum.com/u/kreiggers)\
**Post date:** [February 14, 2007, 10:30pm UTC](https://www.ruby-forum.com/t/read-only-views-from-a-legacy-database/85616/1 "2007-02-14T22:30:44Z")

</div>

I’m connecting to MS SQL Server to an _ugly_ legacy database schema  
and trying to create a read-only dashboard view that joins a few  
tables.

I’ve got two tables that I need to join and display a subset of  
columns from both tables. It seems that I don’t need all of  
ActiveRecord’s magic as (a) I’d need to override most of it anyway,  
(b) the “model” is about 50-50 across two tables, and © no write  
access needed or allowed.

Should I go with just a bare controller and query sql directly from  
there, or is there an even simpiler way to go about this?

---

<div class="post-metadata">

**Author:** ![kreiggers](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/ac91a4/32.png) [@kreiggers](https://www.ruby-forum.com/u/kreiggers)\
**Post date:** [February 15, 2007, 12:28am UTC](https://www.ruby-forum.com/t/read-only-views-from-a-legacy-database/85616/2 "2007-02-15T00:28:57Z")

</div>

On Feb 15, 8:30 am, “kreiggers” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Should I go with just a bare controller and query sql directly from  
> there, or is there an even simpiler way to go about this?

I am not an expert in these matters, but could you get create a view  
in the database for you to use for this application, then base your  
model off that? This has the added advantage of naming the view after  
the Rails conventions.  
If you are worried about access , create a db user with no rights.

---

<div class="post-metadata">

**Author:** ![kreiggers](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/ac91a4/32.png) [@kreiggers](https://www.ruby-forum.com/u/kreiggers)\
**Post date:** [February 15, 2007, 1:45am UTC](https://www.ruby-forum.com/t/read-only-views-from-a-legacy-database/85616/3 "2007-02-15T01:45:04Z")

</div>

> I am not an expert in these matters, but could you get create a view  
> in the database for you to use for this application, then base your  
> model off that? This has the added advantage of naming the view after  
> the Rails conventions.  
> If you are worried about access , create a db user with no rights.

A view would have worked, but I don’t have access to create the view.

I did end up trying the ActiveRecord model and it worked much easier  
than I expected.

I have an empty ActiveRecord class that gives me the connection to the  
SQL Server 2003 database and I just used  
ModelClass.find\_by\_sql(my\_query) and it works just fine. As I don’t  
have any controller methods other than the one or two that I’m using  
to get my data I don’t think I have to worry about any of the other  
ActiveRecord bits. I was assuming that AR would get confused and try  
and give me more attributes that I needed named after the horrible  
legacy schema I’m using, but it returns exactly what I ask for in the  
query and nothing extra.

Here’s what it ends up looking like:

class RequestQueue\< ActiveRecord::Base  
end

class RequestQueueController \< ApplicationController  
require ‘RequestQueue’

def index  
sql =\<\<EOF  
select fp.mrID as id,  
fp.mrTITLE as title,  
cr.mrID as request,  
fp.mrPRIORITY as severity,  
fp.mrSTATUS as status  
from master14 as fp  
left join master3 as cr  
on fp.mrID = cr.Ticket  
where fp.Hotfix = ‘on’ and fp.mrSUBMITDATE \> ‘2007-01-01 00:00:00.000’  
order by fp.mrID desc  
EOF  
@tickets = RequestQueue.find\_by\_sql(sql)  
end  
end

The end result is an object with attributes id, title, request,  
severity, and status, which is all I was needing. I guess I was just  
over thinking it before, thinking I needed to write my own model class  
or to futz around with the raw result from DBI. It’s just,  
unfortunaely, way easier than that 😉

---

<div class="post-metadata">

**Author:** ![kreiggers](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/ac91a4/32.png) [@kreiggers](https://www.ruby-forum.com/u/kreiggers)\
**Post date:** [February 22, 2007, 12:20am UTC](https://www.ruby-forum.com/t/read-only-views-from-a-legacy-database/85616/4 "2007-02-22T00:20:41Z")

</div>

After using an empty ActiveRecord class as my model and just using  
MyModel.find\_by\_sql in my views I found that I was unhappy with my  
code duplication that only involved minor changes tot he sql script as  
I started adding different views, plus I missed the ease of adding  
conditionals etc the rails way. So after digging around in  
ActiveRecord and following through find and find\_by\_sql etc… I  
decided the best way for me to tackle this was to override  
construct\_finder\_sql in my model.It took me a few times to get it just  
right – I found I needed to use set\_table\_name and set\_primary\_key  
even though I specifically call the table name in the sql as rails  
uses the table\_name and the primary\_key when using a find(:id) call,  
also, I found that I also had to define a method for id, otherwise my  
model would not have an id (probably due to it not finding mrID as  
part of the attributes).

It would seem that setting the id via set\_primary\_key means the  
original implementation looks to return the id via a call to  
read\_attribute looking for the ‘mrID’ that I set it to via the call to  
set\_primary\_key. Of course my sql actually selects “fp.mrID as id”, so  
the call to set\_primary\_key confuses the issue as the query appears to  
not return the primary key (it’s always nil). Re-defining the method  
for id fixes this.

class MyModel \< ActiveRecord::Base  
set\_table\_name ‘fp’  
set\_primary\_key ‘mrID’

def id  
read\_attribute(:id)  
end

class \<\< self  
def construct\_finder\_sql(options)  
# my custom select statement includes a join to get my ‘view’  
across two tables  
# statement leaves out any here or order by clauses as they are  
added by the rest of the code  
sql = “SELECT some columns blah, blah FROM master AS fp LEFT  
JOIN master2 as cr ON fp.mrID = …” # etc… you get the idea

```
  # this is the original code from ActiveRecord's implementation

```

that deals with rails-like where, order\_by etc…  
add\_conditions!(sql, options[:conditions], scope)  
sql \<\< " GROUP BY #{options[:group]} " if options[:group]  
add\_order!(sql, options[:order], scope)  
add\_limit!(sql, options, scope)  
add\_lock!(sql, options, scope)  
sql  
end  
end  
end