Hello guys,
- jOOQ's main mission is to model SQL as an internal domain specific
language in Java, and to ease the "JDBC pain".
- jOOQ's secondary mission is to model a database's tables and records
and other artefacts in a typesafe way.
Now, the "jOOQ CRUD API" is a nice side-effect of having an
active-record or ADO.net like model of the underlying database tables.
This model does act like a cache, just like JDBC ResultSets, JPA
entities, .NET EntityFramework entities and many other database
resource models "cache" data from the underlying database.
Now, I completely agree that jOOQ shouldn't assume or even impose a
transactional model from the many possible models. This is why I said
that these things have to be done very carefully - if they're going to
be done at all. I think that improving the CRUD API by adding
transaction listeners could be a viable way to solve Ryan's problems
in the future. But just having a couple of "awesome" (thanks Ryan)
features is not a compelling enough reason to introduce this kind of
complexity. Over the time, I'm collecting use-cases for introducing a
generic transaction model, but it is certainly not a priority for jOOQ
3.0, 3.1 and other early 3.x versions, as I don't have a clear view on
the problem domain, yet.
As for optimistic locking, I do agree with Joachim. I'm not 100%
convinced that it is implemented in the right way (yet, i.e.
generically covering all use cases). It covers a lot of use-cases,
though. And the ones that are missing can probably be added in the
future.
[Ryan] I guess what I mean is, we seem to be thinking down a path that in
distinctly different from the core Jooq focus and it would seem necessary to
draw a line somewhere
Yes, drawing a line is tough, as the short-term added value for users
is often very tempting. Another area where this gets challenging is
the mapping of Result / Record to POJOs.
The point here is: jOOQ may always need to draw new lines. While the
main mission is very clear and finite in scope, the secondary mission
is not. It is open-ended and it should be debated again and again on
this user group. jOOQ's internals have a very high degree of cohesion,
meaning that all the features closely interact together. This is a
good sign, and an effect of having added features slowly, after
careful consideration (features, which aren't low-hanging fruit). Be
assured that a transaction model will be added *very* slowly, if it is
added at all.
[Ryan] Except in
ADO.net you work on a "table"/"view" level rather than record level. Anyway,
2 separate projects...
Funny, I had just recently thought about .store(), .delete(),
.refresh() methods on Result, performing batch operations under the
hood... Do you see anything worth implementing in jOOQ? Note, that
updatable results are on the roadmap:
https://github.com/jOOQ/jOOQ/issues/1846
Other nice ideas?
Cheers
Lukas
2013/2/24 Ryan How <[email protected]>:
Hi Jo,
It almost seems like an entire project on it's own that has a different
focus than Jooq. Apart from the couple of methods (.store(), .delete() and
.refresh()), then Jooq doesn't really do anything too "smart" (it only does
"awesome"). And I think that works really well, you can use it just like
SQL, but much better integration with Java!. It makes it easy to learn, work
in a predictable manner and just provides benefits!. I haven't really found
a drawback yet, you can even just resort back to executing SQL if you want!,
or part of a statement in SQL.
So it is like the Jooq CRUD API (I'm not quite sure what to call this part
of Jooq, is that a good name?), gives a little taste of a whole new world of
possibilities that can be built on top of JOOQ. Maybe it should come under a
different project and then can be built and maintained separately. But it
seems it still needs to tightly integrate with JOOQ too.
It almost seems if it was going as far as to provide support for all the
different transaction models listed, it might as well handle validation and
plugging in other things, such as logging changes (audit trail) and things
like that? Why not put a cache in there too so then you can have the
permanent-sync case.
I guess what I mean is, we seem to be thinking down a path that in
distinctly different from the core Jooq focus and it would seem necessary to
draw a line somewhere. If the core Jooq could provide the necessary hooks
and database integration, then these things could be plugged in to support
any desired use case without Jooq itself becoming too specific to one model.
On a side note, does Jooq and the "crud" operations in any way remind you of
Linq and Ado.net ?, I haven't used them for a while, but the ADO.net
datatable is a bit like the Jooq Result and Record objects, where you can do
changes, it tracks those changes, then you can store the result. Except in
ADO.net you work on a "table"/"view" level rather than record level. Anyway,
2 separate projects...
Thanks, Ryan
On 21/02/2013 8:40 PM, Durchholz, Joachim wrote:
I'm having similar thoughts as Ryan.
Let me ramble a bit; I'm not really sure how well this fits with the
direction Jooq is heading. I hope it helps "stepping back from the drawing
board" and taking a look from a broader perspective - then maybe it doesn't.
There's also the caveat that I have been unable to follow all the details
of the discussion, so I may be missing important details or beating a dead
horse. My apologies for that.
That said, I hope it's more useful than distracting. Feel free to ignore.
Tailoring the store operation to a specific use case is making me very
nervous; I've been finding time and again that my transaction model doesn't
quite fit the model the library designer had in mind.
I suspect that this is partly due to the fact that as soon as you have a
cache for the purpose of avoiding unnecessary UPDATEs, you also get
optimistic locking for free, so you model the entire transaction model
around optimistic locking. Which has the unfortunate side effect that all
transaction models that do not use optimistic locking will have less
support.
So... I'm thinking about the transactional models I have been seeing and
using.
One model is the classic pessimistic locking (PL) model.
It's the one that databases support out of the box; other transactional
models are built on top of it.
It's unsuitable for online processing if a transaction can run longer than
a handful of seconds.
It's also surprisingly unsuitable for anything that runs longer than about
four hours, because it (a) accumulated a gigantic heap of rollback log and
(b) if you need to rollback, you usually end up with having a failed batch
run and not enough time to retry.
The gist of all this is that while PL is the best, people have always been
splitting PL transactions into smaller, shorter transactions, almost since
RDBMSes exist.
There's the idempotent transaction model. You run mostly nontransactional,
but you make sure that each processing step can be repeated without ill
effect, and that failure at any point won't damage the data. Essentially,
you read the data but don't worry about whether there are any concurrent
updates, and only the insert/update/delete operations need to run in a PL
transaction (and sometimes even that isn't strictly required, the RDBMS's
internal data structures would probably need transactional protection but
writing inconsistent data can easily be corrected by rerunning the
transaction).
This works well for all processing steps that transform some constant data
into some other, non-overlapping data.
You need tight control over caching for this model: Some data you want to
keep cached right at the beginning of the batch run until it is finished
(configuration data that doesn't change except under administrator
supervision). Some data you do not want to even see a cache: the bulk rows
that are streamed through the update process. Some data you want to cache
for a while and then throw away: information about a group of data (say, all
data relating to a specific building while you're iterating over the data on
the wares stored there and collect some statistics).
Another model is the read-process-write with optimistic locking
transaction. You read everything in one PL transaction, process the data,
and write in a different PL transaction; you apply some clever tricks to
fail when this would end overwriting data that has been modified by others.
This is a very hairy approach: Sometimes, you want to fail when just the
fields being overwritten have been modified elsewhere (e.g. customer name);
sometimes, you want to fail if some other fields in the same record were
modified (don't overwrite just the house number if somebody else modified
the street name); sometimes, you want to fail if a record in a different
table was updated (don't update the new price sum in the order record if
some other order detail was modified - you might have to apply a different
discount scheme now).
Ultimately, it's the call of the application developer what kinds of
changes might have to be checked before an update can go through. Just
calling UPDATE with expected old values in the WHERE condition, then
counting how many records were actually modified, is an excellent trick, but
it's not covering all needs, just the most basic one.
Then there's read-process-read-process-write model with optimistic
locking. It's not imposing any additional requirements on the database
library I think, it's just that the application programmer needs to be aware
that the data he's working with might be outdated.
Then there's the cache-indefinitely case. You read some data such as the
list of company locations, which is never going to be modified except under
administrator control who'll manually flush any cache and tell J2SE
application users to restart their clients so they get a fresh copy of the
cache. Automation is possible but runs through extra channels that the DB
lib isn't aware of.
Then there's the permanent-sync case. The application side gets notified
of changes in any records that it has available locally. This isn't really
different than optimistic locking, except that the optimistic locking
exception might fly earlier. In the read-process-read-process-write case, it
might avoid some exceptions. The downside is that it's entirely
nonstandardized and very hard to implement. On the plus side, the entire
data movement between application and database is automated and can be
optimized; essentially, you're running (or faking, if you will) a database
cluster where the application is keeping some records in the form of a Java
object.
It's the thing every developer wants and nobody gets :-)
What I'm after is that I wouldn't want Jooq to support a specific
transactional model preferentially. This runs the risk of less support for
the other models.
It's my main gripe about Hibernate, which works really well for
read-process-write-with-optimistic-lock but sucks at every other model. I'm
really worried that Jooq is about to enter the same kind of slippery slope,
though maybe with a different transactional model.
Just my 0.02 cents.
Regards,
Jo
--
You received this message because you are subscribed to the Google Groups
"jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
