Aggregations etc.

Hello Franck,

SQueryAggregate is an interesting concept which raises many questions.  It is really a special case of doing general ad hoc queries and should certainly not be made into a special case.  I avoided these issues with the simple/istic rawQuery methods.  But if we open the box then we should probably consider all the daemons that lie within.


=== RETURN TYPE ===

I really wonder about SFieldAggregate.  It is quite different from other SFields as there are instances per row.  It is also somewhat less efficient -- many more object instances at the field level.  Why not use

  result.getLong("numberOfEmps")

which is more consistent with normal record/field access.

We just create a new class SRecordAdHoc extends Map.   Later SRecordInstance could extend SRecordAdHoc, and thus provide the Map interface I was talking about later.  This should also uncopy the code between SRecordInstance.getDouble and SFieldAggregate.getDouble (say) -- please never copy code.


Once this is done, then rawQuery should also be changed to also return lists of SRecordAdHoc, which should not break compatibility because SRecordAdHoc extends Map.  The same type conversion facility would then be available to rawQuery for free.


Later we could possibly add 
  .count().as(new SFieldLong("numberOfEmps"))
Which could use JDBC to convert the type, which would be consistent with the rest of SimpleORM and possibly produce better results.  But we would still want result.getLong(...), just like SimpleORM.


=== MULTIPLE RETURNED INSTANCES ===

We could clean up the one/single business by making it a method on the result set.  Ie.
  result.oneOrNone()
or 
  result.exactlyOne().getLong("numberOfEmps") 

That return the one row in the result.  (Both exception if multiple results, .one() exception if no results.)  The reason to separate .one from .single is to be able to produce a decent exception message that includes query parameters etc. rather than just NullPointerException.  

To achieve this create SQueryResult that extends ArrayList, give it those methods, plus a reference to the SQuery instance so that decent exception messages can be provided.


== METHOD AND CLASS STRUCTURE  ==

It feels like we should have an SRawQuery class, that is like SQuery but has a .rawSelectField() method to add select fields, and also returns a list of maps like SAggregateQuery.  Then SAggregateQuery should extend SRawQuery.  Maybe SQuery should also extend it, so that they all can be executed by the .query method.   Alternatively, we leave SQuery separate, have SAggregateQuery extend SRawQuery and be called by a different method, maybe .adhocQuery.  Not sure.

(I did not like the query method being overloaded in a way that it returns two different types of results.  Confusing.  But if we are unifying the result it might be OK.  But please go easy on polymorphism.)


== SRECORD INSTANCE as REAL MAPS ==

To be more general I had thought of changing SRecordInstance internally to be a Map of values rather than an array.  This means that extra fields can be added to records on an ad hoc basis.  This not only works for ad hoc queries, but also makes it even easier to extend a base schema.  I had done some crude performance tests and it is OK, despite the poor Java implementation of HashMaps that creates Entry objects for each node.  (We could easily improve that later if desired.) 

If this was done then ad hoc queries could be unified with queries that retrieve real records.   Eg. the Departments plus the sum of their salaries in one query returning one augmented record type (Department).    It also means that SRecordInstance really would extend SRecordAdHoc.

But this suggests that the aggregation goes on the SQuery, not the SAggregateQuery.  In general you can join several tables, aggregate of parts of some of them etc.

So if there is only SQuery.  It has a list of joined SQueryTables.  Some of those tables are aggregated over, in which case they do not create records in the result.  Any values that correspond to a record get added to it (eg. Dept.sum(salaries)).  Any left over values end up in the result set as SRecordAdHocs.  Something like that.

This definitely needs more thought.  We should have a full API sketched out before we start implementing part of it.  (There was something beautifully simple about rawQueryMaps...)



(Of course a Map interface could still be put on top of SRecordInstance even if it is still actually implemented as an ArrayList  -- it just looks up the field number from the field name.)


== MISC ==

GroupBy is in general a list of fields.

Please do try to commit between updates.  Ie the refactor of SQueryExecute vs the Aggregates.  Makes it hard to follow the changes otherwise.

For important structural changes a short design doc would be good, ideally before coding.  It can be written as an extension to the white paper -- API usages are the most important.

Let us leave Batch Updates go until we sort out the other issues.  They are a bit messy because of the awful way that they are implemented by JDBC -- the updates need to be grouped together by table to be effective.  There is also alternative implementation strategy, which is simply to use an updatable cursor -- can be much faster for some situations.  For OLTP it is unclear how much is really gained.

I have not looked at other restructuring, but note that SDriver.whereSQL looks like it should be called from the first selectSQL -- copied code (probably old).


Thanks again for your help,

Anthony

(PS.  Emails like this one take several hours of thought to produce.  And it is still rough.)


Dr Anthony Berglas, [email protected]       Mobile: +61 4 4838 8874
Just because it is possible to push twigs along the ground with ones nose
does not necessarily mean that is the best way to collect firewood.



------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/SimpleORM/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/SimpleORM/join
    (Yahoo! ID required)

<*> To change settings via email:
    mailto:[email protected] 
    mailto:[email protected]

<*> To unsubscribe from this group, send an email to:
    [email protected]

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/