Read only views from a legacy database

kreiggers · February 14, 2007, 9:30pm

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 (c) 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?

askegg · February 14, 2007, 11:27pm

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.

kreiggers · February 15, 2007, 12:43am

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

kreiggers · February 21, 2007, 9:19pm

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

kreiggers · February 21, 2007, 9:24pm

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).