Re: Shipping a build

Hello Franck,

Yes, I was thinking along similar lines after I posted.   The primary key is the big issue.  (And having multiple queries with different select functions return the records with the same keys.)

Things I am now sure of are:-

1.  Doing the full job like I had proposed is too hard in the short term.  Things like optimistic values would require SFieldInstance objects -- we are going down a slippery path.

2.  SRecordInstance and SRecordGeneric (say) should share a common super type, and should implement Map.   (The data conversion code should be uncopied.)  One should be able to print a list of records (say) regardless of their type.  (Incidentally, if you extend LinkedHashMap you do not also need to implement Map.)  

3.  I still think SRecordAggregate is not about aggregates.  It is about arbitrary SQL queries that produce results that do not have primary keys and cannot be added to a DataSet.  It should have a method rawSelect(String clause).  And later other methods to poke arbitrary bits of SQL in.  Maybe even Unions etc.  And may want a rawSelect and an aggregate function in the same query.

So I think maybe work along the lines that I had said were the wrong way.

Rename SQueryAggregate to SQuerySpecial (or maybe SQueryTransient?), which returns SRecordSpecial (or SRecordTransient...), which extend SRecordSuper (or SRecordGeneric).  

It would be good if SRecordInstance also extended SRecordGeneric and implement the Map get methods, but by just looking up SFieldMetas and thence the values from the array.  (No real map.)  Easy and safe.  I would prefer not to introduce SRecordSuper without SRecordInstance extending it, makes SimpleOrm look clumsy.

Later, we should probably be able to specify an SFieldScaler as an extra parameter to the query, so that we use JDBC to do type conversions consistently with SQuery.

(Hibernate just returns arrays of objects for ad hoc queries, or you can poke them in to a non-persisted class.  Maps of objects are much neater and easier.)

An outstanding issue is what if we want to query Departments + AVG(salary) later.   Now we have aggregations in the SQuery.  Would suggest that SQuery and SQueryAggregate should be combined, and maybe just having a null record type makes the difference.  But I think it is probably OK not to worry about this for now.

What do you think?

Regards,

Anthony

At 07:15 PM 29/07/2009, you wrote:
>  
>
>Hi,
>
>there is a point I don't get... there are big differences between
>SRecordGeneric and SRecordInstance, that make me think it would be hard
>to unify them:
>
>SRecordInstance can be persisted,
>SRecordInstance have a primary key and are guaranteed to be unique
>within a session/dataset,
>SRecordInstance can have optimistic values, etc.
>
>On the other hand, SRecordGeneric are really read-only,
>SRecordGeneric cannot be updated,
>SRecordGeneric will be unique only per query result, not on a
>transaction/dataset basis (they have no primary key).
>
>So I understand how it would be interesting to be able to add arbitrary
>read-only fields to SRecordInstance as in you example (and make it
>implement Map or even backed by a Map), but I don't get how we could
>pretend SRecordInstance and SRecordGeneric are the same beast...
>
>What do you think of it ?
>
>Franck
>
>Le mercredi 29 juillet 2009 à  13:09 +1000, <mailto:anthony%40berglas.org>[email protected] a écrit :
>> 
>> Hello Franck,
>> 
>> Good to hear that you are back on this. Producing a new release is way
>> over due. I have also been busy.
>> 
>> The reasoning is that aggregates is only one thing that it can do. The
>> thing that is special about it is that it returns SRecordGenerics
>> rather than SRecordInstances, with select list specified. It can do
>> much more than simple aggregates, even though that was the initial
>> motivation.
>> 
>> While in this space I would love to unify SRecordGeneric,
>> SRecordInstance and Maps. It is a messy at the moment. A lazy
>> incremental approach is as follows.
>> 
>> WRONG WAY?
>> 
>> SRecordGeneric becomes an abstract class with stubs for
>> getDouble(String) etc.
>> 
>> SRecordInstance and a new SRecordMap both extend SRecordGeneric.
>> 
>> SQuerySelect returns lists of SRecordMap. SRecordMap is not a public
>> class, just package local.
>> 
>> SRecordGeneric implements Map. For SRecordInstance that means
>> implementing the map methods to call Instance methods. This makes it
>> much more compatible with JSP, JSF etc.
>> 
>> The copied code in SRecordMap from SRecordInstance that does type
>> conversion is pushed into SRecordGeneric. (I really hate copied code.
>> It invariably drifts apart. At the very least create common static
>> methods.)
>> 
>> Ideally, and probably later, SDataSets can contain SRecordGenerics
>> rather than SRecordInstances. 
>> 
>> Also, SRecordInstances should implement a Map of fields rather than an
>> Array of fields. Then SRecordInstances can have additional, undeclared
>> fields, and so SRecordMap disappears. I have done some experiments and
>> the overhead is negligible even given Map's unfortunate implementation
>> (an extra object for every key/value pair -- exactly what I was
>> avoiding with not having SFieldInstance).
>> 
>> CLEANER ALTERNATIVE
>> 
>> The above is really a bit back to front. I wonder how hard it would
>> really be to take the array out of SRecordInstance and just replace it
>> with a Map that it implements, indexed by field name. As stated
>> before, the overhead is minimal. Then your SQuerySelect could just
>> return SRecordInstances, and there would be no need for
>> SRecordGeneric. SQuerySelect should be unified with SQuery, so that ad
>> hoc columns can be combined with known fields. 
>> 
>> For example, Select Department Name, Manager, AVG(employee Sallary) as
>> Department record instances. Could then do dept.getString(MANAGER),
>> dept.getString("avgSal"). (Given that "avgSal" is an ad hoc field not
>> declared for Department.
>> 
>> Much neater. And demonstrates the advantage of not using rigid POJOs.
>> (Will also behave better if there are deserialization issues.)
>> 
>> The more I think of this the more I do not like adding SRecordGeneric.
>> It forks the concept of Record.
>> 
>> The Alternative is probably less work in the medium turn. Just making
>> the field index a map should be fairly trivial -- we just remove
>> SFieldMeta.index as it is no longer needed and replace
>> 
>> public Object getRawArrayValue(SFieldMeta fmeta) {
>> return fieldValues[fmeta.index];
>> }
>> 
>> with
>> 
>> public Object getRawArrayValue(SFieldMeta fmeta) {
>> return this.get(fmeta.fieldName);
>> }
>> 
>> I'll do the latter if you will remove SRecordGeneric and unify the
>> SQueries. 
>> 
>> (SQueryAggregate just becomes additional methods to SQuery. Or maybe
>> delegated from SQuery as SQuery is getting too large.)
>> 
>> What do you think?
>> 
>> Regards,
>> 
>> Anthony
>> 
>> At 02:44 AM 29/07/2009, you wrote:
>> > 
>> >
>> >Hi Anthony,
>> >
>> >it's been a long time since I said I would rename SAggregateQuery to
>> >SQuerySelect, as soon as... well. I'm ready now :)
>> >
>> >I just come back to make sure I understand you:
>> >
>> >We want to rename SAggregateQuery to SQuerySelect to make the name
>> more
>> >generic, right ?
>> >
>> >But right now, SAggregateQuery is really only capable of returning
>> >aggregates, that is SRecordGeneric whose fields are sums, averages,
>> etc.
>> >The specific methods of SAggregateQuery require that you do
>> aggregates
>> >and won't allow you to query arbitrary fields out of joined tables
>> for
>> >example.
>> >
>> >SO, do you want to rename SAggregateQuery in order to make it more
>> >generic afterwards, or do you mean SAggreagateQuery and SQuery should
>> >share a common interface or a common base (abstract ?) class that
>> would
>> >be called SQuerySelect ?
>> >
>> >Regards,
>> >
>> >Franck
>> >
>> >Le dimanche 24 mai 2009 Ã 17:52 +1000, <mailto:berglas%
>> 40SpreadsheetDetective.com><mailto:berglas%40SpreadsheetDetective.com>[email protected]
>> >a écrit :
>> >> 
>> >> 
>> >> Sounds good. I'll have some time over the next few weeks.
>> >> 
>> >> Anthony
>> >> 
>> >> At 08:19 PM 18/05/2009, you wrote:
>> >> 
>> >> >Hello,
>> >> >
>> >> >I have a few other commits waiting here that I would like to go in
>> >> the
>> >> >build.
>> >> >
>> >> >Mainly, it allows to do:
>> >> >
>> >> >aggQuery.sum("some arbitrary sql here")
>> >> >
>> >> >(or groupBy, avg, max, min, count)
>> >> >
>> >> >For example sum("case where myfield = "TRUE" then 1 when
>> >> myfield="FALSE"
>> >> >then 0 end")
>> >> >or
>> >> >sum("(exract(epoch from myEndTimestamp) - extract(epoch from
>> >> >myBeginTimestamp")) / 3600") as durationindays
>> >> >
>> >> >I also added a hasTable(alias) method to SQuery to ease building
>> of
>> >> >conditionnal quieries.
>> >> >
>> >> >I'll commit it to let you have a look if you want.
>> >> >
>> >> >Then I'll rename SAggregateQuery to SQuerySelect...
>> >> >
>> >> >Regards,
>> >> >Franck
>> >> >
>> >> >Le lundi 18 mai 2009 Ã 14:02 +1000, <mailto:anthony%
>> >> 40berglas.org><mailto:anthony%40berglas.org><mailto:anthony%40berglas.org>[email protected] a
>> écrit :
>> >> >> 
>> >> >> 
>> >> >> Hello Franck,
>> >> >> 
>> >> >> I would like to package up the current subversion state fairly
>> soon
>> >> >> and make it the next build. There are a few tweak I would add.
>> >> >> 
>> >> >> Could you have a bit of a think about what needs to be cleaned
>> up
>> >> etc.
>> >> >> for this to happen. Rename SQueryAggregate to something like
>> >> >> SQuerySelect is one.
>> >> >> 
>> >> >> I'd prefer that you made changes related to your application so
>> >> that
>> >> >> you do not break compatibility.
>> >> >> 
>> >> >> Regards,
>> >> >> 
>> >> >> Anthony
>> >> >> 
>> >> >> >Hello Franck,
>> >> >> >
>> >> >> >I see your point about SQueryGeneric. How about SQuerySelect,
>> >> because
>> >> >> it lets you explicitly specify a select list? 
>> >> >> >
>> >> >> >I realize that you are busy but it would be good to at least do
>> >> just
>> >> >> the rename plus comment as soon as possible. (I won't do it
>> because
>> >> it
>> >> >> will break your code.)
>> >> >> >
>> >> >> >I am a little concerned about getting too clever in SDriver
>> about
>> >> >> portability. Supporting this type of thing for multiple
>> databases
>> >> is
>> >> >> very hard. I think that I would prefer that you just do this at
>> the
>> >> >> application layer. You just have little methods that provide the
>> >> right
>> >> >> query for different DBs. Could just return strings, or possibly
>> >> >> SQuerySelect (given an SQuerySelect parameter). I prefer
>> strings,
>> >> it
>> >> >> is simpler.
>> >> >> >
>> >> >> >Regards,
>> >> >> >
>> >> >> >Anthony
>> >> >> >
>> >> >> >At 04:49 PM 19/04/2009, you wrote:
>> >> >> >>Hello Anthony,
>> >> >> >>
>> >> >> >>I've not be changing anything recently, except adding a
>> >> rawInnerJoin
>> >> >> >>method to SQuery, that truly adds an arbitrary table to a
>> query
>> >> (a
>> >> >> table
>> >> >> >>that has no explicit SFieldReference to the query).
>> >> >> >>
>> >> >> >>But I have also found I would need rawSelect(), so it is
>> >> definitely
>> >> >> a
>> >> >> >>good idea :-)
>> >> >> >>
>> >> >> >>The point is we are also releasing a new version of our
>> flgship
>> >> >> product
>> >> >> >>by the end of the month, so I'll be quite busy right until
>> >> then...
>> >> >> >>
>> >> >> >>Just a few more comments:
>> >> >> >>
>> >> >> >>> I think that specifically SQueryAggregate and friends should
>> be
>> >> >> renamed into something like SQueryGeneric. That is because the
>> >> thing
>> >> >> that is special about it is that it returns SRecordGenerics
>> rather
>> >> >> than SRecordInstances. One use of SQueryGeneric is to do
>> >> aggregates,
>> >> >> but it is more general than that.
>> >> >> >>
>> >> >> >>Yes, but then, when SRecordInstance will extend
>> SRecordGeneric,
>> >> >> SQuery
>> >> >> >>will also return SRecordGeneric... So, are we going to have an
>> >> >> abstract
>> >> >> >>SQueryGeneric, with SQuery and SAggregateQuery extending it,
>> or
>> >> do
>> >> >> you
>> >> >> >>think both should be merged in the long term ? (right now I
>> >> wouldn't
>> >> >> say
>> >> >> >>so, but I'm not sure)
>> >> >> >>> 
>> >> >> >>> Thus we should also add the easy 
>> >> >> >>> .rawSelect(String rawSqlExpression, Object[]... parameters) 
>> >> >> >>> which adds an arbitrary an arbitrary expression,
>> >> plus .rawClause
>> >> >> for completeness.
>> >> >> >>
>> >> >> >>Yes, that would be interesting...
>> >> >> >>Another thing I found I'm missing is doing arbitray
>> calculation
>> >> and
>> >> >> then
>> >> >> >>doing an aggregate on it, but rawSelect wouldn't solve my
>> problem
>> >> >> >>altogether, as calculation might include database functions
>> that
>> >> are
>> >> >> not
>> >> >> >>standard.
>> >> >> >>Typical example is I want to make a sum of durations for my
>> >> records,
>> >> >> >>grouped by whatever. Duration is defined by the difference
>> >> between
>> >> >> two
>> >> >> >>timestamps... and this is something that in not standard in
>> sql.
>> >> So
>> >> >> >>having portable code is not easy.
>> >> >> >>
>> >> >> >>eg. select sum( extract(epoch from endts) - extract(epoch from
>> >> >> begints))
>> >> >> >>will work for postgresql, but not for oracle etc.
>> >> >> >>
>> >> >> >>So another idea I had was to be able to augment SDriver (and
>> >> >> >>SQueryGeneric) with specific functions (like duration(ts1,
>> ts1))
>> >> >> that
>> >> >> >>could be used in queries to allow to generate sql that will
>> work
>> >> in
>> >> >> each
>> >> >> >>database... I didn't do anything for now, as I'm not sure
>> about
>> >> the
>> >> >> >>right way to do this (just add the functions I need, or think
>> >> about
>> >> >> a
>> >> >> >>more clever pluggable functions system, ...)
>> >> >> >>
>> >> >> >>> 
>> >> >> >>> The simple unification of SRecordInstance with
>> SRecordGeneric
>> >> >> would complete the process, but that can be done later. I might
>> >> make
>> >> >> time do it myself because it will not affect your code. (It is
>> >> easy,
>> >> >> just have SRecordInstance implement map, with 
>> >> >> >>> 
>> >> >> >>Fine.
>> >> >> >>As I said, I won't have much time for the next two or hree
>> weeks,
>> >> >> but it
>> >> >> >>should be better after that.
>> >> >> >>
>> >> >> >>Best regards,
>> >> >> >>Franck
>> >> >> >
>> >> >> >Dr Anthony Berglas, <mailto:anthony%
>> 40berglas.org><mailto:anthony%
>> >> 40berglas.org><mailto:anthony%40berglas.org><mailto:anthony%40berglas.org>[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.
>> >> >> >
>> >> >> >
>> >> >> 
>> >> >> Dr Anthony Berglas, <mailto:anthony%
>> >> 40berglas.org><mailto:anthony%40berglas.org><mailto:anthony%40berglas.org>[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.
>> >> >> 
>> >> >> 
>> >> >> 
>> >> >> 
>> >> >> 
>> >> >
>> >> >
>> >> 
>> >> Spreadsheet Detective,
>> >> Southern Cross Software Queensland Pty Limited
>> >> 54 Gerler Street
>> >> Bardon, Queensland 4065, Australia.
>> >> 
>> >> Email: <mailto:berglas%
>> 40spreadsheetdetective.com><mailto:berglas%40spreadsheetdetective.com>[email protected]
>> >> www.SpreadsheetDetective.com
>> >> Ph: +61 427 830248 (Australian Eastern Standard Time)
>> >> 
>> >> "If the model seems correct only because the numbers look right, 
>> >> then why build the model in the first place?"
>> >> 
>> >> 
>> >> 
>> >> 
>> >> 
>> >
>> >
>> 
>> Dr Anthony Berglas, <mailto:anthony%40berglas.org>[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.
>> 
>> 
>> 
>> 
>> 
>
>


Spreadsheet Detective,
Southern Cross Software Queensland Pty Limited
54 Gerler Street
Bardon, Queensland 4065, Australia.

Email: [email protected]
www.SpreadsheetDetective.com
Ph: +61 427 830248 (Australian Eastern Standard Time)

"If the model seems correct only because the numbers look right, 
then why build the model in the first place?"



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

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/