feat: support on conflict for insert stmt in pg

taozhi8833998 · taozhi8833998 · commit c1b7f458818a · 2023-07-13T10:45:04.000+08:00
diff --git a/pegjs/postgresql.pegjs b/pegjs/postgresql.pegjs
@@ -2414,8 +2414,6 @@ set_item
       // => { column: ident; value: column_ref; table?: ident; keyword: 'values' }
       return { column: c, value: v, table: tbl && tbl[0], keyword: 'values' };
   }
-conflict_stmt
-  = KW_ON __ 'CONFLICT'i __
 
 returning_stmt
   = k:KW_RETURNING __ c:(column_clause / select_stmt) {
@@ -2440,18 +2438,64 @@ insert_partition
     return v
   }
 
+conflict_target
+  = LPAREN __ c:column_ref_list  __ RPAREN {
+    // => { type: 'column'; expr: column_ref_list; parentheses: true; }
+    return {
+      type: 'column',
+      expr: c,
+      parentheses: true,
+    }
+  }
+
+conflict_action
+  = 'DO'i __ 'NOTHING'i {
+    // => { keyword: "do"; expr: {type: 'origin'; value: string; }; }
+    return {
+      keyword: 'do',
+      expr: {
+        type: 'origin',
+        value: 'nothing'
+      }
+    }
+  }
+  / 'DO'i __ KW_UPDATE __ KW_SET __ s:set_list __ w:where_clause? {
+    // => { keyword: "do"; expr: {type: 'update'; set: set_list; where: where_clause; }; }
+    return {
+      keyword: 'do',
+      expr: {
+        type: 'update',
+        set: s,
+        where: w,
+      }
+    }
+  }
+
+on_conflict
+  = KW_ON __ 'CONFLICT'i __ ct:conflict_target? __ ca:conflict_action {
+    // => { type: "conflict"; keyword: "on"; target: conflict_target; action: conflict_action; }
+    return {
+      type: 'conflict',
+      keyword: 'on',
+      target: ct,
+      action: ca,
+    }
+  }
+
 replace_insert_stmt
   = ri:replace_insert       __
     KW_INTO?                 __
     t:table_name  __
     p:insert_partition? __ LPAREN __ c:column_list  __ RPAREN __
     v:insert_value_clause __
+    oc:on_conflict? __
     r:returning_stmt? {
       /*
        export interface replace_insert_stmt_node {
          type: 'insert' | 'replace';
          table?: [table_name];
          columns: column_list;
+         conflict?: on_clifict;
          values: insert_value_clause;
          partition?: insert_partition;
          returning?: returning_stmt;
@@ -2482,6 +2526,7 @@ replace_insert_stmt
           columns: c,
           values: v,
           partition: p,
+          conflict: oc,
           returning: r,
         }
       };
diff --git a/src/insert.js b/src/insert.js
@@ -1,6 +1,7 @@
 import { tablesToSQL } from './tables'
 import { exprToSQL } from './expr'
-import { identifierToSql, commonOptionConnector, hasVal, toUpper, returningToSQL } from './util'
+import { columnRefToSQL } from './column'
+import { identifierToSql, commonOptionConnector, hasVal, toUpper, returningToSQL, literalToSQL } from './util'
 import { selectToSQL } from './select'
 import { setToSQL } from './update'
 
@@ -27,12 +28,44 @@ function partitionToSQL(partition) {
   return partitionArr.filter(hasVal).join('')
 }
 
+function conflictTargetToSQL(conflictTarget) {
+  if (!conflictTarget) return ''
+  const { type } = conflictTarget
+  switch (type) {
+    case 'column':
+      return `(${conflictTarget.expr.map(columnRefToSQL).join(', ')})`
+  }
+}
+
+function conflictActionToSQL(conflictAction) {
+  const { expr, keyword } = conflictAction
+  const { type } = expr
+  const result = [toUpper(keyword)]
+  switch (type) {
+    case 'origin':
+      result.push(literalToSQL(expr))
+      break
+    case 'update':
+      result.push('UPDATE', commonOptionConnector('SET', setToSQL, expr.set), commonOptionConnector('WHERE', exprToSQL, expr.where))
+      break
+  }
+  return result.filter(hasVal).join(' ')
+}
+
+function conflictToSQL(conflict) {
+  if (!conflict) return ''
+  const { action, target } = conflict
+  const result = [conflictTargetToSQL(target), conflictActionToSQL(action)]
+  return result.filter(hasVal).join(' ')
+}
+
 function insertToSQL(stmt) {
   const {
     table,
     type,
     prefix = 'into',
     columns,
+    conflict,
     values,
     where,
     on_duplicate_update: onDuplicateUpdate,
@@ -44,6 +77,7 @@ function insertToSQL(stmt) {
   const clauses = [toUpper(type), toUpper(prefix), tablesToSQL(table), partitionToSQL(partition)]
   if (Array.isArray(columns)) clauses.push(`(${columns.map(identifierToSql).join(', ')})`)
   clauses.push(commonOptionConnector(Array.isArray(values) ? 'VALUES' : '', valuesToSQL, values))
+  clauses.push(commonOptionConnector('ON CONFLICT', conflictToSQL, conflict))
   clauses.push(commonOptionConnector('SET', setToSQL, set))
   clauses.push(commonOptionConnector('WHERE', exprToSQL, where))
   clauses.push(returningToSQL(returning))
@@ -52,6 +86,7 @@ function insertToSQL(stmt) {
 }
 
 export {
+  conflictToSQL,
   insertToSQL,
   valuesToSQL,
 }
diff --git a/test/postgres.spec.js b/test/postgres.spec.js
@@ -1,4 +1,5 @@
 const { expect } = require('chai')
+const { conflictToSQL } = require('../src/insert')
 const Parser = require('../src/parser').default
 
 describe('Postgres', () => {
@@ -922,6 +923,42 @@ describe('Postgres', () => {
         `SELECT * FROM partitions WHERE code ~ xyz;`,
         `SELECT * FROM "partitions" WHERE "code" ~ "xyz"`
       ]
+    },
+    {
+      title: 'insert stmt',
+      sql: [
+        `insert into table1 (id, firstname, lastname, email)
+        values ($id, $firstname, $lastname, $email)
+        RETURNING *`,
+        'INSERT INTO "table1" ("id", "firstname", "lastname", "email") VALUES ($id,$firstname,$lastname,$email) RETURNING *'
+      ]
+    },
+    {
+      title: 'insert with on conflict do update',
+      sql: [
+        `insert into table1 (id, firstname, lastname, email)
+        values ($id, $firstname, $lastname, $email)
+        on conflict (id)
+        do
+        update set
+        firstname = $firstname,
+        lastname = $lastname,
+        email = $email,
+        updatedon = CURRENT_TIMESTAMP
+        RETURNING *`,
+        'INSERT INTO "table1" ("id", "firstname", "lastname", "email") VALUES ($id,$firstname,$lastname,$email) ON CONFLICT ("id") DO UPDATE SET "firstname" = $firstname, "lastname" = $lastname, "email" = $email, "updatedon" = CURRENT_TIMESTAMP RETURNING *'
+      ]
+    },
+    {
+      title: 'insert with on conflict do nothing',
+      sql: [
+        `insert into table1 (id, firstname, lastname, email)
+        values ($id, $firstname, $lastname, $email)
+        on conflict
+        do nothing
+        RETURNING *`,
+        'INSERT INTO "table1" ("id", "firstname", "lastname", "email") VALUES ($id,$firstname,$lastname,$email) ON CONFLICT DO NOTHING RETURNING *'
+      ]
     }
   ]
   function neatlyNestTestedSQL(sqlList){
@@ -1100,5 +1137,10 @@ describe('Postgres', () => {
       expect(ast.columnList).to.be.eql(['select::null::Id'])
       expect(parser.sqlify(ast.ast, opt)).to.be.equals(sql.slice(0, -1))
     })
+
+    it('should support conflict be empty', () => {
+      expect(conflictToSQL(null)).to.be.equal('')
+    })
   })
+
 })
