Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

Add SQL methods to models

#888

Hello,

as stated on http://groups.google.com/group/r..., I'm submitting the first of what could be many minor enhancements made to ActiveRecord done as part of a government grant project for the Czech Technical University.

This patch adds support for using sql methods directly from ActiveRecord models. The simplest use scenario looks something like this:

class MyModel < ActiveRecord::Base sql_method :compute_something # call SQL function my_model_compute_something end

The most common reason why someone may want to do something like this is when they want to load a complex data structure from the database that may be impossible or expensive to load in a single step. The SQL/plpgsql function may do this easily, and return a complete set of results that the ruby end only has to convert to their correct types.

I had to extract this functionality directly from our SVN repository, and even though I was careful to include all functionality that's required for the feature to work properly, this patch is most likely not up to high standards. That said, if you can give me a list of things to improve or fix, my employer is willing to let me work on it during business hours.

I hope you'll find this worthwhile.

Thanks, Adam

Note: A reference to the object on which the method is called is currently not passed to the SQL function. There's no specific reason for this inconsistency, and I plan to fix it as soon as there's time to work on it.

Reported by Adam Sindelar · August 23rd, 2008 @ 03:37 PM

State: wontfix
Milestone: 2.x
Assigned to: nobody
Importance: none

Activity

  1. Pratik
    Pratik
    • State changed from new to wontfix

    It's not exactly 'Rails way' to use stored procedures. Having said that, Rails should make it simple to write such plugins nevertheless. So my suggestion is to release this feature as a plugin, and you could submit refactoring to Rails ( if needed ) to make such plugins less brittle and less hackish.

    Thanks.

    August 23rd, 2008 @ 09:44 PM

  2. Jeremy Kemper
    Jeremy Kemper
    • State changed from wontfix to new

    Agreed. Great patch, but this is a non-core feature.

    August 23rd, 2008 @ 09:48 PM

  3. Jeremy Kemper
    Jeremy Kemper
    • State changed from new to wontfix

    August 23rd, 2008 @ 09:48 PM

  4. Adam Sindelar
    Adam Sindelar

    All right then, I'll make this into a plugin. However, in order to this to function properly, PostgreSQLAdapter needs to be aware of the possible schema in the name of a relation (table, function... anything...).

    In Rails 2.0 ActiveRecord didn't attempt to quote table names (at least not in PostgreSQL), and everything sort of worked, but table-name-quoting introduced in ActiveRecord 2.1 broke that compatibility - so, would a patch that makes ActiveRecord aware of the schema part of a fully qualified name of a database relation be acceptable instead? It would involve changes to only several methods and would not affect any existing functionality.

    Current behavior is that if we call...

    set_table_table 'schema.table'

    ..we later get: SELECT [...] FROM "schema.table"

    ...which the database doesn't understand. Instead we should have: SELECT [...] FROM "schema"."table"

    The obvious question is, why would someone want to use more than one schema per database in Rails. There are a number of reasons, but the most important are:

    • many database-clustering solutions work on per-schema basis; having separate schemata allows us to control which parts of the DB get synced, how often, and which are local only

    • some databases (like PostgreSQL) let the admin define different sets of permissions for each schema

    • sometimes, the database structure can get very large, and keeping tables logically sorted into several schemata helps not only navigating the structure, but also improves the ability to drop and load different parts of the database quickly

    August 23rd, 2008 @ 11:23 PM

  5. Jeremy Kemper
    Jeremy Kemper

    Yes, that's reasonable. The MySQL adapter already does.

    August 24th, 2008 @ 12:33 AM