FOR UPDATE and LOCK IN SHARE MODE modifiers for SELECT are missing

[FractalizeR]

Hello.

http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

As I can see they are missing, but when working with transactions,
they are really needed. How do I make them?

[Lukas Eder]

> http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html
>
> As I can see they are missing, but when working with transactions,
> they are really needed. How do I make them?

Good point. As for now, these kinds of clauses are not yet supported.
I will schedule an improvement in a future release:
https://sourceforge.net/apps/trac/jooq/ticket/420

In the mean time, I think you will have to fall back to a workaround.
Either you can try to use a plain SQL object (admittedly, not a very
nice workaround), or you create a wrapper for Select<?>, adding the
additional SQL at the end.

[FractalizeR]

How do I correctly implement wrapper? Do I need to override some
method in some class? Sorry, I'm not yet familiar with JOOQ internals.

[Lukas Eder]

> How do I correctly implement wrapper? Do I need to override some
> method in some class? Sorry, I'm not yet familiar with JOOQ internals.

You would have to create something like this:

// ------------------------------------
class SelectForUpdate implements Select<Record> {
private final Select<Record> delegate;

// ... delegate all methods to "delegate"

@Override
public String toSQLReference(Configuration configuration) {
return delegate.toSQLReference(configuration) + " FOR UPDATE";
}
}
// ------------------------------------

However, this solution is not really recommended if you're not
familiar with the internals. I'm not going to debug you through those,
You'd have to do that yourself... You'll need some time to get it
right. For now, I'd prefer the plain SQL hack:

// ------------------------------------
create.select().from(MY_TABLE).where(condition).and("1 = 1 FOR UPDATE");
// ------------------------------------

Another option: You could wrap that code in a stored procedure? jOOQ
also generates source code for those.

[FractalizeR]

Well, this hack is OK for now. Thanks!

[FractalizeR]

For now, I'd prefer the plain SQL hack:

// ------------------------------------
create.select().from(MY_TABLE).where(condition).and("1 = 1 FOR UPDATE");
// ------------------------------------

 Can you recommend me the same easy hack for non-DSL syntax? SelectQuery query.addConditions(); doesn't want to work with strings.

[Lukas Eder]

Hi

Use Factory.plainSQLCondition() to construct such conditions.

Cheers
Lukas

[FractalizeR]

Thanks a lot. You are really the best. I've never seen any developer as active and supporting as you.

[Lukas Eder]

> Thanks a lot. You are really the best. I've never seen any developer as
> active and supporting as you.

Thank you. I'm glad the "overall package" is well received. Think
about that, when I go closed source and 500$ per license ;-) just
kidding.
But you could do me a favor and blog about jOOQ in Russia. The more
users I get the more focused to real needs my developments will be.

Cheers
Lukas

[FractalizeR]

Yes, I will tomorrow or the day after tomorrow and will give you links. Hope Maven central offers some kind of download statistics for packages?

[Lukas Eder]

> Yes, I will tomorrow or the day after tomorrow and will give you links.

Thank you

> Hope Maven central offers some kind of download statistics for packages?

Yes, but they're updated only once a month. So I don't have any
statistics yet. These are good too, though:
http://sourceforge.net/downloads/jooq/stats_timeline

[FractalizeR]

BTW, how's the progress with GitHub? It's popular enoght in Russia.

[Lukas Eder]

> BTW, how's the progress with GitHub? It's popular enoght in Russia.

I haven't tried anymore. I don't know what that SVN import does. It
takes forever and then crashes. I'll try exporting from SVN into a new
Eclipse workspace, and then sharing it freshly on GitHub. I guess the
SVN history is not very important if I'm using Git as a secondary
version control system? I'll find a way eventually...

[FractalizeR]

I don't think there is a reason to import anything from SVN history. Since you use SVN as your primary development resource, I think you will use GitHub to publish actual state of the project and receive Pull requests (if there will be any).

You can use IDEA for that or just console git if you prefer.

[Lukas Eder]

> You can use IDEA for that or just console git if you prefer.

You win. I'll try that

[FractalizeR]

You know, there is an article on one Russian resource about JetBrains IDEA. And its author says the thing I like about IDEA (calling her his lovely Diana in the sentence):

Что такое IntelliJ IDEA и что она может? Проще сказать что она не может. Она не умеет варить кофе (только косвенно, через IDE Talk). В силу этого сравнение ее с Flex Builder равносильно сравнению возможностей пешехода с гоночной машиной в контексте прохождения дистанции на скорость. Даже если вы придете домой пьяный, Диана вас накормит и не будет возмущаться, что вы какой-то сторонний файл не принадлежащий ни к одному из проектов, а безропотно сделает все, что в ее силах из того, что делала когда вы были чистый и трезвый. Она и в самом деле весьма интеллектуальна.

Translation:

What is IntelliJ IDEA and what can she do? It's easier to say what she can't. It can't make a good coffee (only indirectly, using IDE Talk). And because of this comparing her with Flex Builder is like comparing pedestrian with a sport racing car. If you ever come home drunk, Diana will feed you and will never complain that you are some alien file and not from her project at all. She will do whatever she can for you pretending you are clean and sober. She is really very intellectual.

The sentence sounds silly, but on some reason when I think of IDEA I recall it every time ;) I assure you, this phrase applies not only to Flex Builder comparison. This lovely girl is charming :)

[FractalizeR]

What if I need to make FOR UPDATE for a query with a limit clause? Limit seems not to allow plain string as an argument. Am I in trouble? :)

[Lukas Eder]

> What if I need to make FOR UPDATE for a query with a limit clause? Limit
> seems not to allow plain string as an argument. Am I in trouble? :)

With the existing workaround possibilities, yes. The last thing you
could try is to make nested selects. I don't know if MySQL will like
that. But you could have the inner select apply the LIMIT and the
outer select apply the FOR UPDATE. Or vice versa. If that doesn't work
and if this is urgent, I recommend patching
AbstractResultProviderSelectQuery.toSQLReference() for this. That's
the single central place, where a SELECT statement's SQL is created...

[FractalizeR]

Ok, thanks ;) May be you will release a new Jooq when I finish my piece of software ;)

[Lukas Eder]

I have uploaded a snapshot of the SVN trunk. Feel free to beta-test
the upcoming version 1.5.8
https://oss.sonatype.org/content/repositories/snapshots/org/jooq/

Apart from other things, it now includes a fix for
https://sourceforge.net/apps/trac/jooq/ticket/420

Cheers
Lukas

[FractalizeR]

Is it installable via Maven? If I set version to 1.5.8 or 1.5.8-SNAPSHOT Maven complains.

[Lukas Eder]

> Is it installable via Maven? If I set version to 1.5.8 or 1.5.8-SNAPSHOT
> Maven complains.

No, that's why I pointed you to the sonatype repository. Maven central
doesn't accept snapshot deployments...

[FractalizeR]

I've found a way to install SNAPSHOTs with Maven.
All you need to do is just to add a snapshot repository to your POM root like this:

    <repositories>
        <repository>
            <id>jooq-snapshots</id>
            <url>https://oss.sonatype.org/content/repositories/snapshots</url>
            <snapshots>
                <updatePolicy>always</updatePolicy>
            </snapshots>
            <releases>
                <updatePolicy>always</updatePolicy>
            </releases>
        </repository>
    </repositories>

And then you can write your dependencies as usual in the POM

        <dependency>
            <groupId>org.jooq</groupId>
            <artifactId>jooq</artifactId>
            <version>1.5.8-SNAPSHOT</version>
        </dependency>
        <dependency>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-codegen</artifactId>
            <version>1.5.8-SNAPSHOT</version>
        </dependency>

[Lukas Eder]

That's discouraged by Maven central:
https://docs.sonatype.org/display/Repository/Central+Sync+Requirements

It's understandable, too. Once an artefact's version "goes central",
it will be published forever. On the other hand, the Sonatype
repository may be subject to change (both the Sonatype URL's *and* my
choice to actually use Sonatype).

So I'm guessing, in order to get latest releases AND snapshots, you'll
have to manually set up the Sonatype repository in your workspace, for
now.

[FractalizeR]

Ok, thanks.

[FractalizeR]

What about non-DSL syntax? SelectQuery seem not to have an appropriate method.

[Lukas Eder]

> What about non-DSL syntax? SelectQuery seem not to have an appropriate
> method.

Check again:
http://jooq.svn.sourceforge.net/viewvc/jooq/jOOQ/src/main/java/org/jooq/LockProvider.java?view=markup

[FractalizeR]

Thanks ;)