> 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
on 2005-12-21 01:43
on 2005-12-21 02:55
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?
on 2005-12-21 03:34
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.