Rails equivalent of Delphi SetRange()

rubyonrails-talk
1

I am currently converting a large Delphi webbroker application to RoR. The Delphi application makes extensive use of client datasets and setrange which can limit the range of records returned to a client dataset in an SQL call without the overhead of making another SQL call. The setrange call can be issued in a loop to repeatedly change the range of records viewable in the dataset.

My app frequently issues the setrange command to a dataset in a loop in order to test for previous game time bookings in a sports application. Is there any way I can do this in Rails or do I need to repeatedly make SQL queries to return the data subset?

2

There are a few ways to do this.

One would be using a named_scope in Rails 2 or just plain scope in Rails 3

class Bookings < ActiveRecord::Base   named_scope :discount, :conditions => { :discount > .5 }

I like to model class methods for pulling specific information from the database

  def self.inactive     find( :all, :conditions => ["inactive = ?", 1], :order => :last_calibrated_on )   end And then in the controller:

@inactive = Item,inactive

In Rails 3 there is a new query interface ( http://m.onkey.org/2010/1/22/active-record-query-interface )

The neat thing here is that it seems to map well to what you're doing. You start off creating a relation that you can then manipulate, but the query doesn't fire until you want it too.

Getting those datasets or updating the limits could be done through javascript on the view page, periodically_call_remote or an observer on a field.

3

Thanks for your reply. A couple of further comments / question. I am a noob in Rails.

I like to model class methods for pulling specific information from the database

  def self.inactive     find( :all, :conditions => ["inactive = ?", 1], :order => :last_calibrated_on )   end And then in the controller:

@inactive = Item.inactive

This looks like it will still fire off a query which is what I want to avoid.

In Rails 3 there is a new query interface ( http://m.onkey.org/2010/1/22/active-record-query-interface )

Model scoping looks like it might do the job. My problem is that the loop may reset the client dataset range (its not really the client as in the browser, the ISAPI sits on the server and the client dataset is a query result) many thousands of times while testing for a court booking or a game draw which is not a close repeat of a previous instance. Making a new query would be very detrimental to performance.

In the above link you provided there is a paragraph on model scoping and the following example seems to do the job:-

cars = Car.scoped rich_ppls_cars = cars.order('cars.price DESC').limit(10)

It appears that the call to cars fires the query but the call to rich_ppls_cars only works on the resultant dataset and does not fire a new query. Would that be correct? For example, I could then say poor_ppls_cars = cars.order('cars.price ASC').limit(10) and I would get different data without a new query?

4

Mark Horrocks wrote:

I am currently converting a large Delphi webbroker application to RoR. The Delphi application makes extensive use of client datasets and setrange which can limit the range of records returned to a client dataset in an SQL call without the overhead of making another SQL call. The setrange call can be issued in a loop to repeatedly change the range of records viewable in the dataset

This is probably not a great way of doing things. If you have 1000 records that you're paging through 10 at a time, there is no reason to fetch all 1000 if you only want to read 10. Certainly you should avoid extra queries, but you should also avoid unnecessarily large results. In the example I gave, I think separate queries would be the better solution. If you can describe your use case in more detail, it might help.

5

Marnen Laibow-Koser wrote: If you have 1000

records that you're paging through 10 at a time, there is no reason to fetch all 1000 if you only want to read 10.

There aren't anywhere near that many. Its just that I change the view of the records returned many times while testing for a game time that hasn't been booked by the two teams for the longest period of time while re booking fixtures. The use case is that game fixtures may be redrawn and then require new court bookings part way through the season. Here is some code excerpts.

while not cdsGamesToBook.Eof do     begin

    teamno := cdsGamesToBook.FieldByName('teamA').Value;

    cdsTeamsToBook.SetRange([teamno],[teamno]);

    if cdsTeamsToBook.RecordCount = 0 then       begin

      cdsTeamsToBook.CancelRange;

      cdsTeamsToBook.Insert;

      cdsTeamsToBook.FieldByName('teamno').Value := teamno;

      cdsTeamsToBook.Post;

      end;

    teamno := cdsGamesToBook.FieldByName('teamB').Value;

    cdsTeamsToBook.SetRange([teamno],[teamno]);

In this case the code is just iterating through a list of teams so the max interations might be around 16 here for 16 teams. The use of setrange saves querying the DB 16 times. This loop would in turn be in a separate loop which would issue setrange on the previous games played in order to find the longest time either team has not previously been booked to play at a particular time. The result will be that several small datasets will have setrange applied maybe a dozen times each but the tests may be many. The actual number of queries performed on the DB will be very few.

6

Mark Horrocks wrote:

Marnen Laibow-Koser wrote: If you have 1000

records that you're paging through 10 at a time, there is no reason to fetch all 1000 if you only want to read 10.

There aren't anywhere near that many. Its just that I change the view of the records returned many times while testing for a game time that hasn't been booked by the two teams for the longest period of time while re booking fixtures. The use case is that game fixtures may be redrawn and then require new court bookings part way through the season.

I'm still not completely sure I understand exactly what you're doing, but it looks to me like you're running onto trouble because you're trying to have the app do queries that should be done on the DB side. Can you have the database do the calculations and return only the records you actually want?

7

Marnen Laibow-Koser wrote:

I'm still not completely sure I understand exactly what you're doing, but it looks to me like you're running onto trouble because you're

If I want to re book courts after a game redraw part of the way through a competition, its necessary to check the previous game time bookings and find a time which has not been played before or find the time which has not bee played for the longest time.

So, I will get a list of possible times to book and the id numbers of both teams. If I check through many past games I will normally find that all times have previously been booked in past games. Say 8 weeks have previously been played for 6 teams and there are 4 game times available. Odds are that every time has previously been booked at some time. So the next iteration, I will reduce the number of past games visible and test each time for both teams.

Eventually, by reducing the number of visible past games on each iteration, I will find a time that has not been booked for either team for the greatest number of weeks and this will be the best time to book the future game being tested. Because I have 2 teams and many future games to book I will repeat this process on a dataset of past games many times.

It doesn't make sense to get that list of past games many times. SetRange allows me to reduce the number of past games visible on each pass of a dataset and then return it to the full set for the next future game to be tested or the next team to be tested, or the next time to be tested. This way I can query the past games only once, and then change the range of visible games on it many times while looking for optimal times to book each future game to be booked.

I can't find this ability in Rails.

8

Marnen Laibow-Koser wrote:

Huh? A Rails "recordset" is simply an array. You can take a piece of it just as you would from any other Ruby array. There's no explicit SetRange because you can just use array slicing.

Thanks! I guess this is just what I am looking for. Setrange gives the ability to slice the array based on the data contained within it rather than just by the index number. I am hoping i can find a way to do this in Rails. As I mentioned, I am new at Ruby on Rails but have made several simple applications already. I am now ready to tackle a far more complex app.

9

It might help you a bunch to think of it as Ruby and not Rails. Most of what your doing is writing Ruby and in many situations you will need to know how. Look through the Array methods here and you should get an idea of what can be done;

Rails also injects some of it's own methods into Array;

http://api.rubyonrails.org/ under: ActiveSupport::CoreExtensions::Array

You should find everything you need by doing some googling. Also I would try to come at it from different angles to find the best solution. Sometimes when you rewrite an app you try to copy it verbatim when what is needed is rethinking of the problem.

10

> And then in the controller:

> @inactive = Item.inactive

This looks like it will still fire off a query which is what I want to avoid.

The query would only fire (once) prior to rendering the view. For example, if you set the variable in the index action of the controller the query would only be run after calling the index action.

cars = Car.scoped rich_ppls_cars = cars.order('cars.price DESC').limit(10)

It appears that the call to cars fires the query but the call to rich_ppls_cars only works on the resultant dataset and does not fire a new query. Would that be correct? For example, I could then say poor_ppls_cars = cars.order('cars.price ASC').limit(10) and I would get different data without a new query?

In Rails 3 the cars example above, you are creating a relation. The query is fired when you call rich_people_cars.first or .all

Looking at some of the other posts here I agree there could be some misunderstanding of what you're trying to do.

If it's 16 teams * 16 games that's only 256 results. You can query all the records and then logically view the remainder in instance variables.

Also, if you're looking at the bookings for holes then perhaps you set the query to return records based on the date and venue. There are only so many dates and venues available after all.

Interesting problem though and a good way to learn Rails.

11

Bb Serviss wrote:

Many thanks to everyone who replied, this is a great forum. Delphi has been kind to me with the (visual component library) client data set which allows me to make a single query, then scope the remaining records in any way I see fit and loop through them, breaking out of the loop anytime I see fit. I can also use summary functions on the cds to return sum and count, etc, all without any further queries. Anyway, enough of Delphi, the requirement to use Windows servers has finally killed Delphi for me.

The basic tasks I need to learn in Ruby are

1. create an array of hashes (records). I guess this is the result of a query. (cars = Car.all) but I also need to create them by hand sometimes.

2. Loop through the array, reading each record (I figure I can test each record rather than limit the records in the apparent dataset ala SetRange and perform some processing.

3. Breaking out of the loop anytime a condition has been met (e.g. the game has been booked).

I am sure there are plenty of material available to learn from. What I really get from forums like this are hints as the the *best* way to go about stuff. So far I am really enjoying RoR and have been playing with it for over a year now and have made a couple of applications similar to the Depot sample in the excellent book Agile Web Programming.

12

Mark Horrocks wrote:

Bb Serviss wrote:

Many thanks to everyone who replied, this is a great forum. Delphi has been kind to me with the (visual component library) client data set which allows me to make a single query, then scope the remaining records in any way I see fit and loop through them, breaking out of the loop anytime I see fit.

Delphi has been unkind to you, then. That's a bad way to interact with a database. In general, it is much better practice to have the DB narrow down the data as much as possible and loop through as few records as possible on the app side.

I can also use summary functions on the cds to return sum and count, etc, all without any further queries.

Your database can already do this. Please learn about SQL aggregate functions, which are the correct way to achieve this. (Rails abstracts these in the Calculations module.)

Anyway, enough of Delphi, the requirement to use Windows servers has finally killed Delphi for me.

Nobody's made an open-source Kylix clone?

The basic tasks I need to learn in Ruby are

1. create an array of hashes (records). I guess this is the result of a query. (cars = Car.all) but I also need to create them by hand sometimes.

2. Loop through the array, reading each record (I figure I can test each record rather than limit the records in the apparent dataset ala SetRange and perform some processing.

3. Breaking out of the loop anytime a condition has been met (e.g. the game has been booked).

No. You're going about this entirely the wrong way. You seem to be trying to copy your Delphi application line by line. If that is even possible, it will lead to unidiomatic Rails.

What I would recommend instead is this: learn the Rails framework's basics (from the Rails Guides or elsewhere), then reimplement the *functionality* of the Delphi app in Rails, not its *architecture*.

Remember to do all development test-first (I recommend RSpec and Cucumber). In fact, if you can write Cucumber stories for your Delphi app, you'll know when you've copied the functionality properly.

I am sure there are plenty of material available to learn from. What I really get from forums like this are hints as the the *best* way to go about stuff.

Hint: don't try to write Delphi in Rails. If you really want to do that, I'm sure you can find a Pascal Web framework...

So far I am really enjoying RoR and have been playing with it for over a year now and have made a couple of applications similar to the Depot sample in the excellent book Agile Web Programming.

Then you should already have a basic idea of how a Rails app works, and you should try to use that structure instead of what you're used to from Delphi.

Bonus tip: your lack of familiarity with the Array class suggests that you need to get more familiar with basic Ruby, apart from Rails.

Good luck! Please don't hesitate to ask if you have further questions.

Best,

13

Marnen Laibow-Koser wrote:

Mark Horrocks wrote:

Bb Serviss wrote:

Delphi has been unkind to you, then. That's a bad way to interact with a database. In general, it is much better practice to have the DB narrow down the data as much as possible and loop through as few records as possible on the app side.

Given that the returned records were relatively few (less than 200) and that I needed to progressively narrow the view of those records down then repeatedly restore the original view and go through the process again with different teams and times, I had thought it was more efficient to do this on the app side.

Nobody's made an open-source Kylix clone?

Even if they had it would not enjoy the same large user base and support that RoR has.

No. You're going about this entirely the wrong way. You seem to be trying to copy your Delphi application line by line. If that is even possible, it will lead to unidiomatic Rails.

That was my original intention but I will take your advice on this. It was always apparent that there was going to be a great deal of refactoring in any case.

What I would recommend instead is this: learn the Rails framework's basics (from the Rails Guides or elsewhere), then reimplement the *functionality* of the Delphi app in Rails, not its *architecture*.

Bonus tip: your lack of familiarity with the Array class suggests that you need to get more familiar with basic Ruby, apart from Rails.

That's for sure and I have been doing a lot of reading of late.

Good luck! Please don't hesitate to ask if you have further questions.

Thanks! I appreciate the feedback I get from this forum as a noob.

14

Mark Horrocks wrote:

Marnen Laibow-Koser wrote:

Mark Horrocks wrote:

Bb Serviss wrote:

Delphi has been unkind to you, then. That's a bad way to interact with a database. In general, it is much better practice to have the DB narrow down the data as much as possible and loop through as few records as possible on the app side.

Given that the returned records were relatively few (less than 200) and that I needed to progressively narrow the view of those records down then repeatedly restore the original view and go through the process again with different teams and times, I had thought it was more efficient to do this on the app side.

Well, if you're really retrieving a small recordset and doing lots of different operations on it, then yes, it *might* be more efficient do to this in the application. Maybe.

[...]

No. You're going about this entirely the wrong way. You seem to be trying to copy your Delphi application line by line. If that is even possible, it will lead to unidiomatic Rails.

That was my original intention but I will take your advice on this. It was always apparent that there was going to be a great deal of refactoring in any case.

Hmm. You *could* do a line-by-line port, then refactor to a more Railsy architecture -- but it will be a herculean refactoring. Rewriting to the new architecture from scratch will probably be easier.

Best,

15

Marnen Laibow-Koser wrote:

Hmm. You *could* do a line-by-line port, then refactor to a more Railsy architecture -- but it will be a herculean refactoring. Rewriting to the new architecture from scratch will probably be easier.

I will do it from scratch. The algorithms for game draws and court bookings are quite complex.

In the case of the game draw, there may be previous games played and the draw is a redraw for part of the season due to the addition or removal/transfer of teams. I need to ensure that the teams (up to 16 teams) have not played each other for the longest time. Its a matter of testing each future week in the draw template to find the best week to start.

In the case of court game time bookings, I need to ensure that neither team has been booked at the time and court combo being tested for the longest time. The teams will complain that another team has had x early games while they have had x late games. Further, the system must support clash teams where a team playing volleyball will not want to booked at the same time as a team playing basketball if some players play in both teams. Teams may also have times when they can't turn up to play.

My customers are commercial social sports centers with potentially fluid competitions as teams may come and go during a season or there may be team regrades after several weeks if a team is either much better or worse than the other teams in its league. As you can see the temptation to port to what works already is very compelling but it seems the best way forward is to refactor from scratch. Pity RoR wasn't around in 2001 when I originally wrote it. The website is sportdata.com.au

16

I use Delphi (Lazarus) at work and I constantly miss ruby features :slight_smile:

I can only echo Marnen's remarks about Rails, Delphi does some weird things and I've found that doing it the "Rails" way is usually simpler and much more efficient.

As far as the bookings, you should do a bottom up review by starting off with what would be the perfect view for you. Something by venue that not only shows the two teams but their win/loss percentage and amount of late/early games which would aid in scheduling. Make something that makes sense to you.

Clashes though - shouldn't that be the player's responsibility?

Also, don't forget unit tests and a revision control system (like git) as both of these will help you in many ways.

17

Bb Serviss wrote:

I use Delphi (Lazarus) at work and I constantly miss ruby features :slight_smile:

I still use Delphi 7 because later versions don't like some of the com stuff in the app exe. Its a nightmare just to refactor that. I have numerous third party dependencies like patches to Internet Express and some of these won't work in later versions either due to unit location changes and the like. Not only, but the Internet Express javascript has i.e. extensions which won't work in mozilla browsers. That said, I like the Internet Express grid as it has multi record browsing, editing and updates in a single trip to the web server. Probably not so much of a problem anymore with faster internet but its a nice feature.

I can only echo Marnen's remarks about Rails, Delphi does some weird things and I've found that doing it the "Rails" way is usually simpler and much more efficient.

Thanks, I have resolved to go that way.

As far as the bookings, you should do a bottom up review by starting off with what would be the perfect view for you. Something by venue that not only shows the two teams but their amount of late/early games which would aid in scheduling. Make something that makes sense to you.

That was the original plan long ago but with unknown numbers of courts and game times, what is a late game? I found that its better to simply distribute the courts and game times evenly, starting with the game time and then the courts.

Clashes though - shouldn't that be the player's responsibility?

No, the sports centre allocates the game times and courts.

Also, don't forget unit tests and a revision control system (like git) as both of these will help you in many ways.

I have git organized, tests are high on my list. The stock test all bomb because of the PK / FK relationships. I need to construct the fixtures accordingly. Right now I am still sorting out the easy part which is the CRUD on the organizations / sports / leagues / teams | games / players and members, holidays, courts, etc.

Next I will attempt a game draw. Expect further questions :slight_smile:

Thanks for the input.

Mark