Stored Procedures is not the rails way…
All features that make our database, [business] smart we
hate…
It’s hard to disagree with that. However, I’m working (playing
actually) with an interesting quandary, specifically with the PostGIS
extensions to PostgreSql. Not really stored procedures, but much of the
functionality is encapsulated as functions within the database. If you
want to know the airports within 100 km of Berlin, you create a SELECT
statement with a function call embedded in it.
This kinda rubs me the wrong way. If I’m using Active Record, this is
not just an attribute I can get to, I’ll have to make another round trip
to the database. But, I can’t see another way. There are some
geo-functions that could be calculated on the object already retrieved
(like say, what are the dimensions of a rectangle that encloses this
geo-shape) and which could be encapsulated at the object level, but
clearly finding how distinct geo-shapes relate to each other needs a
PostGIS query. Can this still be considered “the rails way”?
Please do tell me if you find my thinking flawed, I’d love to be set
straight.
Cheers,
Curtis Olson
I have been watching the rails stored procedure discussion that keeps
popping up here and elsewhere for a while.
Has anyone suggested that stored procedures be allowed if they are
associated as methods on models? ActiveRecord could include code to
form
the association (e.g. set_stored_proc_method) and code to handle the
return
values and to refresh the model if necessary.
For example:
:stored_proc should be optional if the stored procedure name is the
same
as the method name
e.g. :doFunDBStuff has a stored procedure named doFunDBStuff
overrides for parameters could also be allowed in case they cannot be
pulled from the schema
or need a type definition or name change
class myModel < ActiveRecord::Base
set_stored_proc_method :doFunDBStuff, :stored_proc => “myStoredProc”
end
What do you guys think? I know this “isn’t the Rails way”, but wouldn’t
this make Rails even more useful without changing the preferred way of
working with Rails?
That’s how I’ve been using stored procs.
It’s fine to say stored procs aren’t part of “the Rails way”, but the
reality is that for many many apps that are a good fit for Rails,
they’re mandatory. Stored procs are widely used to enforce security,
by ensuring that only a controlled set of interfaces (i.e. stored
procs and views) are made available to people wishing to access data.
In these cases, either you use stored procs with Rails, or you use
stored procs with something else (C#, Java, …). I’m not prepared to
sacrifice the productivity that comes with Rails when I need to work
with stored procs.
Stored procs also have some significant performance/scalability
benefits compared to other solutions.
Finally, many businesses quite rightly realise that their data is
absolutely fundamental to their continued operation, and just aren’t
going to throw open their databases to any (Rails) developer that
might come along. You want to access their data - you play by their
rules. If you want to use Rails for only green fields apps, or for
small department-level stuff, that’s fine, but I think doing so
greatly limits your ability to leverage Rails’ productivity benefits.
Look at it this way - if Rails gives (say) a 5x productivity increase
over Java, where are the truly noteworthy savings going to be? The
app that would take 5 man-months in Java, or the app that would take
15 man-years in Java?
On the other hand, I’m finding it hard to use an agile approach to app
development that includes stored procs - for anything non-trivial,
dependencies within and between stored procs start to become too
difficult to track. If I’ve got (say) 50 stored procs and need to
change one to e.g. cater for a new field, there can be a sizeable
amount of testing required to ensure that the change doesn’t break
another stored proc somewhere. If anyone can point me to a reference
detailing a solution for this issue, I’d greatly appreciate it.
Regards
Dave M.