SQL : Merge query support

[Timo Westkämper]

Provide a merge query that will work on DBMSs that do not support merge. It would work by using the data entered into the columns() and values() methods to generate a where clause automatically. An ID would be entered in the key() method and that data would be used to generate the Querydsl list() request. When a list of IDs is returned that list would be used to generate an update statement. The values that are not in the list of IDs would be used to generate an insert statement. The whole operation would return a unified list of IDs from the update command and the insert command. Many times, a merge command is used to insert or update a single record so it is mostly a convenience to write the lookup and update in one command.
http://www.h2database.com/html/grammar.html#merge

[Timo Westkämper (timo-westkamper)]

As the signature for returning the generated keys something like this?

  SQLMergeClause{

    List<Tuple> executeWithKeys();

  }

I commited an implementation that works with H2. The UPDATE + INSERT combo for other engines follows soon.

Please comment here if the direction is ok.

[rrmckinley (rrmckinley)]

This will be great. Very excited about this one. It can get a little tricky with exclusion joins (leftJoin(b)... where(b.col1.isNull()). If you want some ideas on how I have been doing this, let me know.

Thanks

[Timo Westkämper (timo-westkamper)]

I just commited a version with basic support for MERGE INTO for other databases. It works like this

* query for the ids based on the value bindings
* if no ids are found create an insert statement and execute it
* if ids are found then create an update statement and execute it

Subquery usage is not yet supported for the simulated MERGE INTO. I believe for the subquery part your flow could be used.

Could you provide a concrete example? I haven't used MERGE INTO before, so I am not familiar with MERGE INTO + subquery combos.

[rrmckinley (rrmckinley)]

I am working on a patch that will make an automatic left join complement query. It will allow for updates and inserts of multiple records on the same merge. I'll submit it tomorrow.

[Timo Westkämper (timo-westkamper)]

Any progress with the patch?

[rrmckinley (rrmckinley)]

I'm moving house until Sunday. I will submit my patch early next week.

On Mon, Jul 19, 2010 at 1:45 AM, Timo Westkämper
<email address hidden> wrote:
> Any progress with the patch?
>
> --
> SQL : Merge query support
> https://bugs.launchpad.net/bugs/593532
> You received this bug notification because you are a direct subscriber
> of the bug.
>
> Status in Querydsl: In Progress
>
> Bug description:
> Provide a merge query that will work on DBMSs that do not support merge. It would work by using the data entered into the columns() and values() methods to generate a where clause automatically. An ID would be entered in the key() method and that data would be used to generate the Querydsl list() request. When a list of IDs is returned that list would be used to generate an update statement. The values that are not in the list of IDs would be used to generate an insert statement. The whole operation would return a unified list of IDs from the update command and the insert command. Many times, a merge command is used to insert or update a single record so it is mostly a convenience to write the lookup and update in one command.
> http://www.h2database.com/html/grammar.html#merge
>
> To unsubscribe from this bug, go to:
> https://bugs.launchpad.net/querydsl/+bug/593532/+subscribe
>

[Timo Westkämper (timo-westkamper)]

What's up with the patch? Did the harddisk eat it? ;)

[FilipVercauteren (filip-vercauteren-agfa)]

I will be out of the office starting 21/07/2010 and will not return until
09/08/2010.

[rrmckinley (rrmckinley)]

I still have no Internet at my new place. Who knew moving across
contents would be easier than dealing with AT&T. I'm working from
cafes and friends' houses so every connected moment is for the day
job. I'll submit the simple but clever patch as soon as I can get
online in the after hours.

:)

On Sat, Jul 31, 2010 at 4:39 AM, Timo Westkämper
<email address hidden> wrote:
> What's up with the patch? Did the harddisk eat it? ;)
>
> --
> SQL : Merge query support
> https://bugs.launchpad.net/bugs/593532
> You received this bug notification because you are a direct subscriber
> of the bug.
>
> Status in Querydsl: In Progress
>
> Bug description:
> Provide a merge query that will work on DBMSs that do not support merge. It would work by using the data entered into the columns() and values() methods to generate a where clause automatically. An ID would be entered in the key() method and that data would be used to generate the Querydsl list() request. When a list of IDs is returned that list would be used to generate an update statement. The values that are not in the list of IDs would be used to generate an insert statement. The whole operation would return a unified list of IDs from the update command and the insert command. Many times, a merge command is used to insert or update a single record so it is mostly a convenience to write the lookup and update in one command.
> http://www.h2database.com/html/grammar.html#merge
>
> To unsubscribe from this bug, go to:
> https://bugs.launchpad.net/querydsl/+bug/593532/+subscribe
>

[Timo Westkämper (timo-westkamper)]

McKinley, any changes to your internet connectivity? I am fixing issues to be able to release Querydsl 2.0 in a few weeks. If you want you can also post an informal patch as a code snippet or describe how you did it.

[rrmckinley (rrmckinley)]

Yes, I am finally getting around to side projects again after to much
work. I'll get a patch out this weekend.

On Wed, Sep 1, 2010 at 8:31 AM, Timo Westkämper
<email address hidden> wrote:
> McKinley, any changes to your internet connectivity? I am fixing issues
> to be able to release Querydsl 2.0 in a few weeks. If you want you can
> also post an informal patch as a code snippet or describe how you did
> it.
>
> --
> SQL : Merge query support
> https://bugs.launchpad.net/bugs/593532
> You received this bug notification because you are a direct subscriber
> of the bug.
>
> Status in Querydsl: In Progress
>
> Bug description:
> Provide a merge query that will work on DBMSs that do not support merge. It would work by using the data entered into the columns() and values() methods to generate a where clause automatically. An ID would be entered in the key() method and that data would be used to generate the Querydsl list() request. When a list of IDs is returned that list would be used to generate an update statement. The values that are not in the list of IDs would be used to generate an insert statement. The whole operation would return a unified list of IDs from the update command and the insert command. Many times, a merge command is used to insert or update a single record so it is mostly a convenience to write the lookup and update in one command.
> http://www.h2database.com/html/grammar.html#merge
>
> To unsubscribe from this bug, go to:
> https://bugs.launchpad.net/querydsl/+bug/593532/+subscribe
>

[Timo Westkämper (timo-westkamper)]

What's the status on this one? It would be great to get it into Querydsl 2.0.1.

[Timo Westkämper (timo-westkamper)]

McKinley, do you still have the patch for this one?

[rrmckinley (rrmckinley)]

Yes, I'm getting back into the patching mode so that I can submit it. I'll
post it in a day or two I think.

On Wed, Jan 19, 2011 at 12:17 PM, Timo Westkämper <
<email address hidden>> wrote:

> McKinley, do you still have the patch for this one?
>
> --
> You received this bug notification because you are a direct subscriber
> of the bug.
> https://bugs.launchpad.net/bugs/593532
>
> Title:
> SQL : Merge query support
>
> Status in Querydsl:
> In Progress
>
> Bug description:
> Provide a merge query that will work on DBMSs that do not support merge.
> It would work by using the data entered into the columns() and values()
> methods to generate a where clause automatically. An ID would be entered in
> the key() method and that data would be used to generate the Querydsl list()
> request. When a list of IDs is returned that list would be used to generate
> an update statement. The values that are not in the list of IDs would be
> used to generate an insert statement. The whole operation would return a
> unified list of IDs from the update command and the insert command. Many
> times, a merge command is used to insert or update a single record so it is
> mostly a convenience to write the lookup and update in one command.
> http://www.h2database.com/html/grammar.html#merge
>
> To unsubscribe from this bug, go to:
> https://bugs.launchpad.net/querydsl/+bug/593532/+subscribe
>

[cowwoc (gili)]

McKinley,

Three months later, any update? :)

[cowwoc (gili)]

Timo,

SQLMergeClause.executeWithKey() doesn't seem to exist. How are we supposed to get auto-generated keys inserted by a merge operation?

[cowwoc (gili)]

I stand corrected. It seems it isn't possible to MERGE into tables with AUTO_INCREMENT columns unless you explicitly specify their value. Therefore executeWithKey() is never needed. Source: http://stackoverflow.com/questions/6306592/merge-into-table-containing-auto-increment-columns

[Timo Westkämper (timo-westkamper)]

Migrated to GitHub : https://github.com/mysema/querydsl/issues/7