Re: SQL-OS: A way of paging across all databases...

[Kevin Meyer - KMZ <ke...@kmz.co.za>]

Hi Dan,

I'm tossing this reply into the isis-users list, as I think its a domain 
question... :)

Responses inline, below.

> Before answering, just a point that - if the intent is to process all rows
> in the domain model, but in batches - then the general idea of paging seems
> flawed to me.
> 
> That is, your domain object could ask for rows 1~100, generate invoices for
> all, then move onto rows 101~200; however in the intervening time some
> other user could have come along and added or removed a record that could
> mess up the paging.  I could see either than you either forget to generate
> an invoice, or that you end up generating an invoice more than once.
> 
> I think better would be that your query is constructed to say:
> 
> "give me the first 100 rows that don't yet have an invoice", and then keep
> looping through that.

For a specific case where the system is likely to have parallel actions 
that could affect the result, I think your point is justified.

In my "simple" real word example, the treasurer is iterating through the 
existing member list and generating invoices.  In the current 
application, I believe this is a safe operation so long as the secretary 
does not create or delete a member *while this paging method is 
running*.

> 
> To do that, you'll need to add some general purpose query mechanism to the
> SQL OS; unless I'm mistaken, there doesn't seem to be one yet?

Correct, one does not exist.  And I'm tempted to say "use the Cayenne 
object store, and the Cayenne methods there". Of course, I still need 
to add the Cayenne stuff I've already written.

Thanks,
Kevin

> 
> Dan
> 
> 
> On 24 March 2012 14:54, Kevin Meyer wrote:
> 
> > Hi All,
> >
> > I was doing some reading about the general problem of how to
> > implement database paging in JDBC, and since paging is not part of
> > the SQL ANSI standard, JDBC doesn't apparently have a native (API)
> > method of supporting it.
> >
> > One of the suggestions is to manually add a "datarow" column, which
> > is auto-incremented by 1 in every row. Then paging queries can be
> > spoofed via the SQL's WHERE clause.
> >
> > This assumes that rows are never deleted, which is most of my cases
> > is a reasonable one.
> >
> > Opinions?
> >
> > Otherwise, I'll have to introduce a runtime-specified paging helper
> > class that returns valid SQL for the DB being used (MySQL and
> > PostgreSQL have a completely different, and incompatible, paging
> > syntax, if memory serves).
> >
> > Regards,
> > Kevin