groovy

provide method rows() and firstRow() on groovy.sql.DataSet

Details

  • Type: Improvement Improvement
  • Status: Closed Closed
  • Priority: Minor Minor
  • Resolution: Fixed
  • Affects Version/s: 1.0-JSR-3
  • Fix Version/s: 1.1-beta-2
  • Component/s: None
  • Labels:
    None
  • Number of attachments :
    3

Description

allow
data = new groovy.sql.DataSet(tablename)
List rows = data.rows()
to fetch all rows at once analogous to Sql.rows(...)

Attachments

  1. Text File
    DataSet.txt
    24/Mar/07 8:19 PM
    3 kB
    John Carnell
  2. Text File
    DataSetV2.txt
    26/Mar/07 9:35 PM
    2 kB
    John Carnell
  3. Text File
    SqlCompleteTest.txt
    24/Mar/07 8:19 PM
    1 kB
    John Carnell

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
John Carnell added a comment -

Added two patches for the proposed enhancement to the DataSet class. I have added the rows() method
and the firstRow() method. I have also updated the SqlCompleteTest class to test these two methods.

However, I do have a question on this open improvement. The way I read the "improvement" is that the rows() method
will return all of the rows on the underlying tableset. To implement this I simply did a SELECT * FROM the table to
build the data. However, what happens if the user is trying to filter based on a where. Shouldn't the call really return all of the rows for the DataSet.

Please let me know how exactly this code should work. I can change the code to behave whatever way needed.

Show
John Carnell added a comment - Added two patches for the proposed enhancement to the DataSet class. I have added the rows() method and the firstRow() method. I have also updated the SqlCompleteTest class to test these two methods. However, I do have a question on this open improvement. The way I read the "improvement" is that the rows() method will return all of the rows on the underlying tableset. To implement this I simply did a SELECT * FROM the table to build the data. However, what happens if the user is trying to filter based on a where. Shouldn't the call really return all of the rows for the DataSet. Please let me know how exactly this code should work. I can change the code to behave whatever way needed.
Hide
Permalink
John Carnell added a comment -

Came to the realization that I could just re-use the rows() method on the SQL class and just pass in a SQL
string. D**mba@@ to go and cut-and paste code. I uploaded the revised dataset class. The changes still work and the unit tests pass.

Show
John Carnell added a comment - Came to the realization that I could just re-use the rows() method on the SQL class and just pass in a SQL string. D**mba@@ to go and cut-and paste code. I uploaded the revised dataset class. The changes still work and the unit tests pass.
Hide
Permalink
blackdrag blackdrag added a comment -

implemented

Show
blackdrag blackdrag added a comment - implemented
Hide
Permalink
Paul King added a comment -

I think John Carnell's query is an important one here:

"... However, what happens if the user is trying to filter based on a where. Shouldn't the call really return all of the rows for the DataSet."

I think the answer to this is yes.

Here is some code:

def athletes = [
    [firstname: 'Paul', lastname:'Tergat', dateOfBirth:'1969-06-17'],
    [firstname: 'Khalid', lastname:'Khannouchi', dateOfBirth:'1971-12-22'],
    [firstname: 'Ronaldo', lastname:'da Costa', dateOfBirth:'1970-06-07'],
    [firstname: 'Paula', lastname:'Radcliffe', dateOfBirth:'1973-12-17']
]

athleteSet = db.dataSet('Athlete')
athletes.each { a -> athleteSet.add(a) }
youngsters = athleteSet.findAll{ it.dateOfBirth > '1970-1-1'}
def rows = []
youngsters.each { rows += it.lastname }
println rows.join(', ')           // => Khannouchi, da Costa, Radcliffe
println youngsters.rows().size()  // => 4

I would expect rows() for youngsters to return the three youngster rows not the entire original table (4 rows here).

Show
Paul King added a comment - I think John Carnell's query is an important one here: "... However, what happens if the user is trying to filter based on a where. Shouldn't the call really return all of the rows for the DataSet." I think the answer to this is yes. Here is some code: 
def athletes = [
    [firstname: 'Paul', lastname:'Tergat', dateOfBirth:'1969-06-17'],
    [firstname: 'Khalid', lastname:'Khannouchi', dateOfBirth:'1971-12-22'],
    [firstname: 'Ronaldo', lastname:'da Costa', dateOfBirth:'1970-06-07'],
    [firstname: 'Paula', lastname:'Radcliffe', dateOfBirth:'1973-12-17']
]

athleteSet = db.dataSet('Athlete')
athletes.each { a -> athleteSet.add(a) }
youngsters = athleteSet.findAll{ it.dateOfBirth > '1970-1-1'}
def rows = []
youngsters.each { rows += it.lastname }
println rows.join(', ')           // => Khannouchi, da Costa, Radcliffe
println youngsters.rows().size()  // => 4
I would expect rows() for youngsters to return the three youngster rows not the entire original table (4 rows here).
Hide
Permalink
blackdrag blackdrag added a comment -

do I see it right, that we just need to replace

public List rows() throws SQLException {
        String sql  = "SELECT * FROM " + table;
        return rows(sql);

    }

with 

public List rows() throws SQLException {
        String sql  = getSql();
        return rows(sql);
    }

in DataSet?

Show
blackdrag blackdrag added a comment - do I see it right, that we just need to replace 
public List rows() throws SQLException {
        String sql  = "SELECT * FROM " + table;
        return rows(sql);

    }
with 
public List rows() throws SQLException {
        String sql  = getSql();
        return rows(sql);
    }
in DataSet?
Hide
Permalink
Paul King added a comment -

Fixed

Show
Paul King added a comment - Fixed
Hide
Permalink
Paul King added a comment -

Thought you would have already clocked off, so went ahead and fixed it.

Yes, your suggestion was almost what I did - just:

rows(getSql(), getParameters())

because sql is stored proc with params.

Show
Paul King added a comment - Thought you would have already clocked off, so went ahead and fixed it. Yes, your suggestion was almost what I did - just: 
rows(getSql(), getParameters())
because sql is stored proc with params.

People

Vote (0)
Watch (3)

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Codehaus. Try JIRA - bug tracking software for your team.