-
Type:
New Feature
-
Resolution: Unresolved
-
Priority:
Unknown
-
None
-
None
-
None
-
None
-
None
-
None
-
None
-
None
HQL's insertion statement contains `ON CONFLICT ... ` feature which could be mapped to MQL's upsert translation.
For instance, below is an example HQL statement (there is a unique index on `title` column already):
insert into Book (id, title) values (1, "Holy Bible") on conflict (title) update set title = 'New Holy Bible"
that could be translated into the following MQL using `upsert` + `$setOnInsert`:
{
updateOne: {
filter: { title: "Holy Bible" },
update: {
$set: { title: "New Holy Bible" },
$setOnInsert: {
_id: 1
title: "Holy Bible"
}
},
upsert: true
}
}
Note that this translation only applies when all the following HQL conditions are satisfied:
- ON CONFLICT clause is used in HQL insertion statement
- conflict condition has to be a field list (not empty or unique index name)
- conflict action is not NOTHING
For reference, below is the BNF grammar for HQL's insertion statement:
/** * An 'insert' statement */ insertStatement : INSERT INTO? targetEntity targetFields (queryExpression | valuesList) conflictClause? ; ... ... /** * a 'conflict' clause in an 'insert' statement */ conflictClause : ON CONFLICT conflictTarget? DO conflictAction ; conflictTarget : ON CONSTRAINT identifier | LEFT_PAREN simplePath (COMMA simplePath)* RIGHT_PAREN ; conflictAction : NOTHING | UPDATE setClause whereClause? ;