implement upsert MQL translation from HQL's insertion conflict clause

XMLWordPrintableJSON

    • 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?
          ; 

            Assignee:
            Unassigned
            Reporter:
            Nathan Xu
            None
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated: