New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL : Merge query support #7

Closed
timowest opened this Issue Oct 3, 2011 · 4 comments

Comments

Projects
None yet
2 participants
@timowest
Member

timowest commented Oct 3, 2011

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

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Oct 3, 2011

Member

Timo Westkämper (timo-westkamper) on 2010-06-15
summary: - Merge query support

  • SQL : Merge query support
    Timo Westkämper (timo-westkamper) wrote on 2010-06-20: #1

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.
Changed in querydsl:
status: New → In Progress
McKinley1411 (mckinley1411) wrote on 2010-06-20: #2

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) wrote on 2010-07-03: #3

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.
McKinley1411 (mckinley1411) wrote on 2010-07-07: #4

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) wrote on 2010-07-19: #5

Any progress with the patch?
McKinley1411 (mckinley1411) wrote on 2010-07-19: Re: [Bug 593532] Re: SQL : Merge query support #6

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
timo.westkamper@mysema.com wrote:
[...]
Timo Westkämper (timo-westkamper) wrote on 2010-07-31: #7

What's up with the patch? Did the harddisk eat it? ;)
FilipVercauteren (filip-vercauteren-agfa) wrote on 2010-07-31: Filip Vercauteren is out of the office. #8

I will be out of the office starting 21/07/2010 and will not return until
09/08/2010.
McKinley1411 (mckinley1411) wrote on 2010-08-03: Re: [Bug 593532] Re: SQL : Merge query support #9

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
timo.westkamper@mysema.com wrote:
[...]
Timo Westkämper (timo-westkamper) on 2010-08-25
Changed in querydsl:
importance: Undecided → Medium
Timo Westkämper (timo-westkamper) on 2010-08-26
Changed in querydsl:
milestone: none → 2.0
Timo Westkämper (timo-westkamper) wrote on 2010-09-01: #10

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.
McKinley1411 (mckinley1411) wrote on 2010-09-04: #11

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
timo.westkamper@mysema.com wrote:
[...]
Timo Westkämper (timo-westkamper) wrote on 2010-10-01: #12

What's the status on this one? It would be great to get it into Querydsl 2.0.1.
Timo Westkämper (timo-westkamper) wrote on 2011-01-19: #13

McKinley, do you still have the patch for this one?
McKinley1411 (mckinley1411) wrote on 2011-01-20: #14

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 <
timo.westkamper@mysema.com> wrote:

[...]
cowwoc (gili) wrote on 2011-04-18: #15

McKinley,

Three months later, any update? :)
cowwoc (gili) wrote on 2011-06-02: #16

Timo,

SQLMergeClause.executeWithKey() doesn't seem to exist. How are we supposed to get auto-generated keys inserted by a merge operation?
cowwoc (gili) wrote on 2011-06-13: #17

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

Member

timowest commented Oct 3, 2011

Timo Westkämper (timo-westkamper) on 2010-06-15
summary: - Merge query support

  • SQL : Merge query support
    Timo Westkämper (timo-westkamper) wrote on 2010-06-20: #1

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.
Changed in querydsl:
status: New → In Progress
McKinley1411 (mckinley1411) wrote on 2010-06-20: #2

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) wrote on 2010-07-03: #3

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.
McKinley1411 (mckinley1411) wrote on 2010-07-07: #4

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) wrote on 2010-07-19: #5

Any progress with the patch?
McKinley1411 (mckinley1411) wrote on 2010-07-19: Re: [Bug 593532] Re: SQL : Merge query support #6

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
timo.westkamper@mysema.com wrote:
[...]
Timo Westkämper (timo-westkamper) wrote on 2010-07-31: #7

What's up with the patch? Did the harddisk eat it? ;)
FilipVercauteren (filip-vercauteren-agfa) wrote on 2010-07-31: Filip Vercauteren is out of the office. #8

I will be out of the office starting 21/07/2010 and will not return until
09/08/2010.
McKinley1411 (mckinley1411) wrote on 2010-08-03: Re: [Bug 593532] Re: SQL : Merge query support #9

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
timo.westkamper@mysema.com wrote:
[...]
Timo Westkämper (timo-westkamper) on 2010-08-25
Changed in querydsl:
importance: Undecided → Medium
Timo Westkämper (timo-westkamper) on 2010-08-26
Changed in querydsl:
milestone: none → 2.0
Timo Westkämper (timo-westkamper) wrote on 2010-09-01: #10

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.
McKinley1411 (mckinley1411) wrote on 2010-09-04: #11

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
timo.westkamper@mysema.com wrote:
[...]
Timo Westkämper (timo-westkamper) wrote on 2010-10-01: #12

What's the status on this one? It would be great to get it into Querydsl 2.0.1.
Timo Westkämper (timo-westkamper) wrote on 2011-01-19: #13

McKinley, do you still have the patch for this one?
McKinley1411 (mckinley1411) wrote on 2011-01-20: #14

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 <
timo.westkamper@mysema.com> wrote:

[...]
cowwoc (gili) wrote on 2011-04-18: #15

McKinley,

Three months later, any update? :)
cowwoc (gili) wrote on 2011-06-02: #16

Timo,

SQLMergeClause.executeWithKey() doesn't seem to exist. How are we supposed to get auto-generated keys inserted by a merge operation?
cowwoc (gili) wrote on 2011-06-13: #17

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

@timowest timowest closed this Jun 29, 2012

@cowwoc

This comment has been minimized.

Show comment
Hide comment
@cowwoc

cowwoc Jun 10, 2013

Contributor

Timo,

Can you please reopen this issue? We need to revise the API in response to an updated answer: http://stackoverflow.com/a/6307884/14731

Specifically:

Given customer[id identity, email varchar(30), count int] you could merge into customer(id, email, count) key(email) values((select max(id) from customer c2 where c2.email='test@acme.com'), 'test@acme.com', 10). Meaning, re-use the id if a record exists, use null otherwise.

This demonstrates that it is possible to MERGE into tables containing AUTO_INCREMENT columns without specifying their value. In the above example, it's not clear how to get the generated customer.id using QueryDSL. Currently, SQLMergeClause.executeWithKey() does not exist. How are we supposed to retrieve the generated id?

Contributor

cowwoc commented Jun 10, 2013

Timo,

Can you please reopen this issue? We need to revise the API in response to an updated answer: http://stackoverflow.com/a/6307884/14731

Specifically:

Given customer[id identity, email varchar(30), count int] you could merge into customer(id, email, count) key(email) values((select max(id) from customer c2 where c2.email='test@acme.com'), 'test@acme.com', 10). Meaning, re-use the id if a record exists, use null otherwise.

This demonstrates that it is possible to MERGE into tables containing AUTO_INCREMENT columns without specifying their value. In the above example, it's not clear how to get the generated customer.id using QueryDSL. Currently, SQLMergeClause.executeWithKey() does not exist. How are we supposed to retrieve the generated id?

@cowwoc

This comment has been minimized.

Show comment
Hide comment
@cowwoc

cowwoc Jun 10, 2013

Contributor

I also found http://stackoverflow.com/a/16392399/14731 which provides an even easier way to implement MERGE in a portable manner.

Contributor

cowwoc commented Jun 10, 2013

I also found http://stackoverflow.com/a/16392399/14731 which provides an even easier way to implement MERGE in a portable manner.

@timowest timowest reopened this Jun 10, 2013

timowest added a commit that referenced this issue Jul 8, 2013

timowest added a commit that referenced this issue Jul 8, 2013

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jul 29, 2013

Member

Released in 3.2.2

Member

timowest commented Jul 29, 2013

Released in 3.2.2

@timowest timowest closed this Jul 29, 2013

@timowest timowest added this to the 3.2.2 milestone Apr 14, 2014

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment