Add upsert support and allow arbitrary queries in INSERT, UPDATE and DELETE#85
Merged
shane-circuithub merged 3 commits intomasterfrom Jul 14, 2021
Merged
Add upsert support and allow arbitrary queries in INSERT, UPDATE and DELETE#85shane-circuithub merged 3 commits intomasterfrom
shane-circuithub merged 3 commits intomasterfrom
Conversation
3025f48 to
8484d22
Compare
c0cee0c to
3ef5ae5
Compare
8484d22 to
ad0b915
Compare
5bdc21f to
e360dc5
Compare
ad0b915 to
297a73b
Compare
e360dc5 to
5f6f897
Compare
1fb2f67 to
c9d0bd3
Compare
5f6f897 to
9747d8b
Compare
ocharles
reviewed
Jul 12, 2021
bb7de8a to
9e927c1
Compare
Contributor
Author
|
This also includes a fix for #80. |
f2e1d33 to
ab8b0a3
Compare
ocharles
reviewed
Jul 13, 2021
ocharles
reviewed
Jul 13, 2021
ab8b0a3 to
6a38040
Compare
ocharles
reviewed
Jul 14, 2021
Contributor
ocharles
left a comment
There was a problem hiding this comment.
I'll give this a test soon, but just wanted to mention that I don't like Set and Where as type aliases, and find they just get in the way. I won't fight this corner, though - if you disagree, we can keep these.
Contributor
Author
|
I actually agree, I was going to get rid of them. I think they had some value when |
…DELETE This PR makes several changes to our "manipulation" functions (`insert`, `update`, `delete`). Firstly, we now support `ON CONFLICT DO UPDATE`, aka "upsert". Secondly, we now allow the insertion of arbitrary queries (not just static `VALUES`). `values` recovers the old behaviour. Thirdly, our `Update` and `Delete` statements now support `FROM` and `USING` clauses respectively, allowing joining against other tables. Fourthly, `Returning` is now an `Applicative`, which means you can say `returning = pure ()` if you don't care about the number of rows affected. In terms of generating the SQL to implement these features, it was unfortunately significantly less work to roll our own here than to add this upstream to Opaleye proper, because it would have required more refactoring than I felt comfortable doing.
872385c to
b271b0d
Compare
ocharles
approved these changes
Jul 14, 2021
shane-circuithub
added a commit
that referenced
this pull request
Jul 16, 2021
When printing the SQL, we analyse the `PrimExpr`s and `PrimQuery` to detect the case where `rows` is set to `rows = values _` and, if so, we output a bare `VALUES` statement instead of a `SELECT`, because `DEFAULT` is only syntactically valid in a bare `VALUES` clause.
shane-circuithub
added a commit
that referenced
this pull request
Jul 16, 2021
When printing the SQL, we analyse the `PrimExpr`s and `PrimQuery` to detect the case where `rows` is set to `rows = values _` and, if so, we output a bare `VALUES` statement instead of a `SELECT`, because `DEFAULT` is only syntactically valid in a bare `VALUES` clause.
ocharles
pushed a commit
that referenced
this pull request
Jul 17, 2021
When printing the SQL, we analyse the `PrimExpr`s and `PrimQuery` to detect the case where `rows` is set to `rows = values _` and, if so, we output a bare `VALUES` statement instead of a `SELECT`, because `DEFAULT` is only syntactically valid in a bare `VALUES` clause.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
2 participants
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
This PR makes several changes to our "manipulation" functions (
insert,update,delete).Firstly, we now support
ON CONFLICT DO UPDATE, aka "upsert".Secondly, we now allow the insertion of arbitrary queries (not just static
VALUES).valuesrecovers the old behaviour.Thirdly, our
UpdateandDeletestatements now supportFROMandUSINGclauses respectively, allowing joining against other tables.Fourthly,
Returningis now anApplicative, which means you can sayreturning = pure ()if you don't care about the number of rows affected.In terms of generating the SQL to implement these features, it was unfortunately significantly less work to roll our own here than to add this upstream to Opaleye proper, because it would have required more refactoring than I felt comfortable doing.