This project is archived and is in readonly mode.

#888 ✓wontfix
Adam Sindelar

Add SQL methods to models

Reported by Adam Sindelar | August 23rd, 2008 @ 03:37 PM | in 2.x

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.

Comments and changes to this ticket

  • Pratik

    Pratik August 23rd, 2008 @ 09:44 PM

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

  • Jeremy Kemper

    Jeremy Kemper August 23rd, 2008 @ 09:48 PM

    • State changed from “wontfix” to “new”

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

  • Jeremy Kemper

    Jeremy Kemper August 23rd, 2008 @ 09:48 PM

    • State changed from “new” to “wontfix”
  • Adam Sindelar

    Adam Sindelar August 23rd, 2008 @ 11:23 PM

    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

  • Jeremy Kemper

    Jeremy Kemper August 24th, 2008 @ 12:33 AM

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

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

People watching this ticket

Attachments

Pages