Read only views from a legacy database

Rails
1

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?

2

On Feb 15, 8:30 am, “kreiggers” [email protected] 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.

3

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 :wink:

4

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