Topic: Convert a db stored procedure to rails or not?

I am rewriting a Delphi app to RoR and want to know the best way of dealing with a stored proc. Will find_by_sql just use the original stored proc without rails even noticing it? Or should I convert the stored proc to a nested sql statement? Can I use a migration to create the stored proc? The stored proc was written for Firebird but the new app is using Postgres

Here is the stored proc

Begin
  For
    Select Team1
    From Clashes
    Where (Team2 = :TeamA) Or (Team2 = :TeamB)
    Union All
    Select Team2
    From Clashes
    Where (Team1 = :TeamA) Or (Team1 = :TeamB)
    Into :TeamNo
  Do
    Suspend;
End

Here is my find_by_sql which will use the stored proc.

      @booking_clashes = Game.find_by_sql(
          "select c.team_1, c.team_2, g.game_time," +
          " cast(null as time) as end_time" +
          " from games g" +
          " left join clashes c" +
          " on ((c.team_1 = g.team_a_id)" +
          " or  (c.team_1 = g.team_b_id)" +
          " or  (c.team_2 = g.team_a_id)" +
          " or  (c.team_2 = g.team_b_id))" +
          " where" +
          " ((g.team_a_id in" +
           " (select team_id" +
           " from OtherClashTeams(#{team_a}, #{team_b})))" +
          " or" +
          " (g.team_b_id in" +
          " (select team_id" +
          " from OtherClashTeams(#{team_a}, #{team_b}))))" +
          " and" +
          " g.game_date = #{game_date}" +
          " g.venue_id is not null"
      )

Last edited by markhorrocks (2011-07-25 02:44:52)

Re: Convert a db stored procedure to rails or not?

In Rails stored proc's are not normally used; though there's nothing that explicitly stops you from using them.  We like to have the code in the project (app) not in the DB; not to speak of database dependency like in your case.  Most often these would go into the model, either as a method (esp. in Rails 3 where they can be AREL methods -- the SQL part, I mean) or as scopes.

Let me know if you need help re-code your select into AREL (although it's OK to leave it almost as-is as a start and refactor it later to be more legible (if it needs to be more legible at all...)).

Re: Convert a db stored procedure to rails or not?

boomerang wrote:

need help re-code your select into AREL (although it's OK to leave it almost as-is as a start and refactor it later to be more legible (if it needs to be more legible at all...)).

Yes, definitely, I would appreciate the help. ARel queries look very interesting and I am still near the beginning of converting a large web app to RoR. It would be of great benefit as I have already found myself using sql for most queries. Meantime, I am reading up on ARel.

Even help in refactoring this into a stand alone sql query would be appreciated.

Last edited by markhorrocks (2011-07-25 08:52:51)

Re: Convert a db stored procedure to rails or not?

At this point it would be most helpful if I understood your business requirements and model a bit more -- else I have to resort to re-coding blindly what you have at hand; and I'm not 100% sure that it's the simplest or the best (sorry).

It seems like you have Games and Teams.  Games have scheduled time and (optionally) venue.  A Game has exactly two teams (a and b) and a Team has many Games.  (Tell me if I'm wrong with any of this.)

(Note: a problem that we will wrestle with is that you hard-code a has_many relationship into the Game model.  It may be OK for the particular problem just be warned.  If you don't do this, however, you face other problems (such as enforcing the exactly-two-participants rule).)

Now, can you please, explain what you would like to retrieve?  (I also have no idea what the OtherClashTeams stored procedure should return.)

PS: If you feel like you can share the project or parts of it in private (or in public) via github or by other means; it makes iterations faster but obviously has the consequence of me (and perhaps others) getting access to your code.

Re: Convert a db stored procedure to rails or not?

The app supports competition management in indoor sports centres of a kind found mainly in Australia. They host many different sports such as basketball, volleyball, indoor soccer, netball, etc. Teams play weekly. Games can be created with one team (a bye) in the case of odd numbers of teams. They are created without time and venue bookings but these are set in the function I am working on. Only games with 2 teams get a venue booking.

struct = sports => leagues => games
                                          => teams

Leagues has_many games, teams

games belongs_to league

teams belongs_to league

Players may play in several sports and would not want their games booked at the same time, this is called a clash. I need to find each game booking for a clash team.

A game has 2 teams, team a and team b. The clash tables looks like this (it stores only team id numbers)

my_Team Another_Team
my_Team Aces
Yankees my_Team

The union all statement will pick up the other team and the game time for any clash teams of either team a or team b currently being booked. So we are picking up all clash teams for either team in the current game being booked.


I'm not so worried about sharing my code as nobody could get enough of it to make a difference.

Last edited by markhorrocks (2011-07-25 09:06:20)

Re: Convert a db stored procedure to rails or not?

I sent you a mail yesterday; please, contact me in email.