Repository: cloudspannerecosystem/memefish
Pull request #79 (2024-05-29 02:20:53)

 Add support of 'OR UPDATE|IGNORE' syntax for INSERT DML
see: https://cloud.google.com/spanner/docs/reference/standard-sql/dml-syntax#insert-ignore


Patch:
diff --git a/ast/ast.go b/ast/ast.go
index a089543..03fbe48 100644
--- a/ast/ast.go
+++ b/ast/ast.go
@@ -1633,7 +1633,7 @@ type AlterTable struct {
 
 	Alter token.Pos // position of "ALTER" keyword
 
-	Name             *Ident
+	Name            *Ident
 	TableAlteration TableAlteration
 }
 
@@ -1646,7 +1646,7 @@ type AlterIndex struct {
 
 	Alter token.Pos // position of "ALTER" keyword
 
-	Name             *Ident
+	Name            *Ident
 	IndexAlteration IndexAlteration
 }
 
@@ -1659,7 +1659,7 @@ type AlterChangeStream struct {
 
 	Alter token.Pos // position of "ALTER" keyword
 
-	Name                    *Ident
+	Name                   *Ident
 	ChangeStreamAlteration ChangeStreamAlteration
 }
 
@@ -2213,13 +2213,15 @@ type ArraySchemaType struct {
 
 // Insert is INSERT statement node.
 //
-//	INSERT INTO {{.TableName | sql}} ({{.Columns | sqlJoin ","}}) {{.Input | sql}}
+//	INSERT {{if .InsertOrType}}OR .InsertOrType{{end}}INTO {{.TableName | sql}} ({{.Columns | sqlJoin ","}}) {{.Input | sql}}
 type Insert struct {
 	// pos = Insert
 	// end = Input.end
 
 	Insert token.Pos // position of "INSERT" keyword
 
+	InsertOrType InsertOrType
+
 	TableName *Ident
 	Columns   []*Ident
 	Input     InsertInput
diff --git a/ast/const.go b/ast/const.go
index 57840e2..9e3a974 100644
--- a/ast/const.go
+++ b/ast/const.go
@@ -122,3 +122,10 @@ const (
 	SecurityTypeInvoker SecurityType = "INVOKER"
 	SecurityTypeDefiner SecurityType = "DEFINER"
 )
+
+type InsertOrType string
+
+const (
+	InsertOrTypeUpdate InsertOrType = "UPDATE"
+	InsertOrTypeIgnore InsertOrType = "IGNORE"
+)
diff --git a/ast/sql.go b/ast/sql.go
index 4c94d30..1574db0 100644
--- a/ast/sql.go
+++ b/ast/sql.go
@@ -1223,7 +1223,11 @@ func (a *ArraySchemaType) SQL() string {
 // ================================================================================
 
 func (i *Insert) SQL() string {
-	sql := "INSERT INTO " + i.TableName.SQL() + " ("
+	sql := "INSERT "
+	if i.InsertOrType != "" {
+		sql += "OR " + string(i.InsertOrType) + " "
+	}
+	sql += "INTO " + i.TableName.SQL() + " ("
 	for i, c := range i.Columns {
 		if i != 0 {
 			sql += ", "
diff --git a/parser.go b/parser.go
index 4978e76..3362f1b 100644
--- a/parser.go
+++ b/parser.go
@@ -2704,8 +2704,8 @@ func (p *Parser) parseAlterTable(pos token.Pos) *ast.AlterTable {
 	}
 
 	return &ast.AlterTable{
-		Alter:            pos,
-		Name:             name,
+		Alter:           pos,
+		Name:            name,
 		TableAlteration: alteration,
 	}
 }
@@ -2869,8 +2869,8 @@ func (p *Parser) parseAlterIndex(pos token.Pos) *ast.AlterIndex {
 	}
 
 	return &ast.AlterIndex{
-		Alter:            pos,
-		Name:             name,
+		Alter:           pos,
+		Name:            name,
 		IndexAlteration: alteration,
 	}
 }
@@ -3235,6 +3235,20 @@ func (p *Parser) parseDML() ast.DML {
 }
 
 func (p *Parser) parseInsert(pos token.Pos) *ast.Insert {
+	var insertOrType ast.InsertOrType
+	if p.Token.Kind == "OR" {
+		p.nextToken()
+		switch {
+		case p.Token.IsKeywordLike("UPDATE"):
+			insertOrType = ast.InsertOrTypeUpdate
+		case p.Token.Kind == "IGNORE":
+			insertOrType = ast.InsertOrTypeIgnore
+		default:
+			p.panicfAtToken(&p.Token, "expected pseudo keyword: UPDATE, IGNORE, but: %s", p.Token.AsString)
+		}
+		p.nextToken()
+	}
+
 	if p.Token.Kind == "INTO" {
 		p.nextToken()
 	}
@@ -3262,10 +3276,11 @@ func (p *Parser) parseInsert(pos token.Pos) *ast.Insert {
 	}
 
 	return &ast.Insert{
-		Insert:    pos,
-		TableName: name,
-		Columns:   columns,
-		Input:     input,
+		Insert:       pos,
+		InsertOrType: insertOrType,
+		TableName:    name,
+		Columns:      columns,
+		Input:        input,
 	}
 }
 


Test patch:
diff --git a/testdata/input/dml/insert_or_ignore.sql b/testdata/input/dml/insert_or_ignore.sql
new file mode 100644
index 0000000..f003df2
--- /dev/null
+++ b/testdata/input/dml/insert_or_ignore.sql
@@ -0,0 +1,2 @@
+INSERT OR IGNORE INTO foo
+(foo, bar) VALUES (1, 2)
\ No newline at end of file
diff --git a/testdata/input/dml/insert_or_update.sql b/testdata/input/dml/insert_or_update.sql
new file mode 100644
index 0000000..2dfca8c
--- /dev/null
+++ b/testdata/input/dml/insert_or_update.sql
@@ -0,0 +1,2 @@
+INSERT OR UPDATE INTO foo
+(foo, bar) VALUES (1, 2)
\ No newline at end of file
diff --git a/testdata/result/dml/insert_into_values.sql.txt b/testdata/result/dml/insert_into_values.sql.txt
index d4e6a3b..047a68e 100644
--- a/testdata/result/dml/insert_into_values.sql.txt
+++ b/testdata/result/dml/insert_into_values.sql.txt
@@ -4,8 +4,9 @@ values (1, 2, 3),
        (4, 5, 6)
 --- AST
 &ast.Insert{
-  Insert:    0,
-  TableName: &ast.Ident{
+  Insert:       0,
+  InsertOrType: "",
+  TableName:    &ast.Ident{
     NamePos: 12,
     NameEnd: 15,
     Name:    "foo",
diff --git a/testdata/result/dml/insert_or_ignore.sql.txt b/testdata/result/dml/insert_or_ignore.sql.txt
new file mode 100644
index 0000000..28adbf4
--- /dev/null
+++ b/testdata/result/dml/insert_or_ignore.sql.txt
@@ -0,0 +1,59 @@
+--- insert_or_ignore.sql
+INSERT OR IGNORE INTO foo
+(foo, bar) VALUES (1, 2)
+--- AST
+&ast.Insert{
+  Insert:       0,
+  InsertOrType: "IGNORE",
+  TableName:    &ast.Ident{
+    NamePos: 22,
+    NameEnd: 25,
+    Name:    "foo",
+  },
+  Columns: []*ast.Ident{
+    &ast.Ident{
+      NamePos: 27,
+      NameEnd: 30,
+      Name:    "foo",
+    },
+    &ast.Ident{
+      NamePos: 32,
+      NameEnd: 35,
+      Name:    "bar",
+    },
+  },
+  Input: &ast.ValuesInput{
+    Values: 37,
+    Rows:   []*ast.ValuesRow{
+      &ast.ValuesRow{
+        Lparen: 44,
+        Rparen: 49,
+        Exprs:  []*ast.DefaultExpr{
+          &ast.DefaultExpr{
+            DefaultPos: -1,
+            Default:    false,
+            Expr:       &ast.IntLiteral{
+              ValuePos: 45,
+              ValueEnd: 46,
+              Base:     10,
+              Value:    "1",
+            },
+          },
+          &ast.DefaultExpr{
+            DefaultPos: -1,
+            Default:    false,
+            Expr:       &ast.IntLiteral{
+              ValuePos: 48,
+              ValueEnd: 49,
+              Base:     10,
+              Value:    "2",
+            },
+          },
+        },
+      },
+    },
+  },
+}
+
+--- SQL
+INSERT OR IGNORE INTO foo (foo, bar) VALUES (1, 2)
diff --git a/testdata/result/dml/insert_or_update.sql.txt b/testdata/result/dml/insert_or_update.sql.txt
new file mode 100644
index 0000000..fbda7b8
--- /dev/null
+++ b/testdata/result/dml/insert_or_update.sql.txt
@@ -0,0 +1,59 @@
+--- insert_or_update.sql
+INSERT OR UPDATE INTO foo
+(foo, bar) VALUES (1, 2)
+--- AST
+&ast.Insert{
+  Insert:       0,
+  InsertOrType: "UPDATE",
+  TableName:    &ast.Ident{
+    NamePos: 22,
+    NameEnd: 25,
+    Name:    "foo",
+  },
+  Columns: []*ast.Ident{
+    &ast.Ident{
+      NamePos: 27,
+      NameEnd: 30,
+      Name:    "foo",
+    },
+    &ast.Ident{
+      NamePos: 32,
+      NameEnd: 35,
+      Name:    "bar",
+    },
+  },
+  Input: &ast.ValuesInput{
+    Values: 37,
+    Rows:   []*ast.ValuesRow{
+      &ast.ValuesRow{
+        Lparen: 44,
+        Rparen: 49,
+        Exprs:  []*ast.DefaultExpr{
+          &ast.DefaultExpr{
+            DefaultPos: -1,
+            Default:    false,
+            Expr:       &ast.IntLiteral{
+              ValuePos: 45,
+              ValueEnd: 46,
+              Base:     10,
+              Value:    "1",
+            },
+          },
+          &ast.DefaultExpr{
+            DefaultPos: -1,
+            Default:    false,
+            Expr:       &ast.IntLiteral{
+              ValuePos: 48,
+              ValueEnd: 49,
+              Base:     10,
+              Value:    "2",
+            },
+          },
+        },
+      },
+    },
+  },
+}
+
+--- SQL
+INSERT OR UPDATE INTO foo (foo, bar) VALUES (1, 2)
diff --git a/testdata/result/dml/insert_select.sql.txt b/testdata/result/dml/insert_select.sql.txt
index 96fdfb7..690f8a3 100644
--- a/testdata/result/dml/insert_select.sql.txt
+++ b/testdata/result/dml/insert_select.sql.txt
@@ -3,8 +3,9 @@ insert foo (foo, bar)
 select * from unnest([(1, 2), (3, 4)])
 --- AST
 &ast.Insert{
-  Insert:    0,
-  TableName: &ast.Ident{
+  Insert:       0,
+  InsertOrType: "",
+  TableName:    &ast.Ident{
     NamePos: 7,
     NameEnd: 10,
     Name:    "foo",
diff --git a/testdata/result/dml/insert_values.sql.txt b/testdata/result/dml/insert_values.sql.txt
index 2690fb3..09897f4 100644
--- a/testdata/result/dml/insert_values.sql.txt
+++ b/testdata/result/dml/insert_values.sql.txt
@@ -4,8 +4,9 @@ values (1, 2, 3),
        (4, 5, 6)
 --- AST
 &ast.Insert{
-  Insert:    0,
-  TableName: &ast.Ident{
+  Insert:       0,
+  InsertOrType: "",
+  TableName:    &ast.Ident{
     NamePos: 7,
     NameEnd: 10,
     Name:    "foo",
diff --git a/testdata/result/dml/insert_values_default.sql.txt b/testdata/result/dml/insert_values_default.sql.txt
index cc53875..ae7d07c 100644
--- a/testdata/result/dml/insert_values_default.sql.txt
+++ b/testdata/result/dml/insert_values_default.sql.txt
@@ -3,8 +3,9 @@ insert foo (foo, bar)
 values (1, default)
 --- AST
 &ast.Insert{
-  Insert:    0,
-  TableName: &ast.Ident{
+  Insert:       0,
+  InsertOrType: "",
+  TableName:    &ast.Ident{
     NamePos: 7,
     NameEnd: 10,
     Name:    "foo",
diff --git a/testdata/result/dml/insert_with_sequence_function.sql.txt b/testdata/result/dml/insert_with_sequence_function.sql.txt
index b93db39..1928314 100644
--- a/testdata/result/dml/insert_with_sequence_function.sql.txt
+++ b/testdata/result/dml/insert_with_sequence_function.sql.txt
@@ -3,8 +3,9 @@ INSERT INTO foo(bar) VALUES (GET_NEXT_SEQUENCE_VALUE(SEQUENCE my_sequence))
 
 --- AST
 &ast.Insert{
-  Insert:    0,
-  TableName: &ast.Ident{
+  Insert:       0,
+  InsertOrType: "",
+  TableName:    &ast.Ident{
     NamePos: 12,
     NameEnd: 15,
     Name:    "foo",
diff --git a/testdata/result/statement/insert_into_values.sql.txt b/testdata/result/statement/insert_into_values.sql.txt
index d4e6a3b..047a68e 100644
--- a/testdata/result/statement/insert_into_values.sql.txt
+++ b/testdata/result/statement/insert_into_values.sql.txt
@@ -4,8 +4,9 @@ values (1, 2, 3),
        (4, 5, 6)
 --- AST
 &ast.Insert{
-  Insert:    0,
-  TableName: &ast.Ident{
+  Insert:       0,
+  InsertOrType: "",
+  TableName:    &ast.Ident{
     NamePos: 12,
     NameEnd: 15,
     Name:    "foo",
diff --git a/testdata/result/statement/insert_or_ignore.sql.txt b/testdata/result/statement/insert_or_ignore.sql.txt
new file mode 100644
index 0000000..28adbf4
--- /dev/null
+++ b/testdata/result/statement/insert_or_ignore.sql.txt
@@ -0,0 +1,59 @@
+--- insert_or_ignore.sql
+INSERT OR IGNORE INTO foo
+(foo, bar) VALUES (1, 2)
+--- AST
+&ast.Insert{
+  Insert:       0,
+  InsertOrType: "IGNORE",
+  TableName:    &ast.Ident{
+    NamePos: 22,
+    NameEnd: 25,
+    Name:    "foo",
+  },
+  Columns: []*ast.Ident{
+    &ast.Ident{
+      NamePos: 27,
+      NameEnd: 30,
+      Name:    "foo",
+    },
+    &ast.Ident{
+      NamePos: 32,
+      NameEnd: 35,
+      Name:    "bar",
+    },
+  },
+  Input: &ast.ValuesInput{
+    Values: 37,
+    Rows:   []*ast.ValuesRow{
+      &ast.ValuesRow{
+        Lparen: 44,
+        Rparen: 49,
+        Exprs:  []*ast.DefaultExpr{
+          &ast.DefaultExpr{
+            DefaultPos: -1,
+            Default:    false,
+            Expr:       &ast.IntLiteral{
+              ValuePos: 45,
+              ValueEnd: 46,
+              Base:     10,
+              Value:    "1",
+            },
+          },
+          &ast.DefaultExpr{
+            DefaultPos: -1,
+            Default:    false,
+            Expr:       &ast.IntLiteral{
+              ValuePos: 48,
+              ValueEnd: 49,
+              Base:     10,
+              Value:    "2",
+            },
+          },
+        },
+      },
+    },
+  },
+}
+
+--- SQL
+INSERT OR IGNORE INTO foo (foo, bar) VALUES (1, 2)
diff --git a/testdata/result/statement/insert_or_update.sql.txt b/testdata/result/statement/insert_or_update.sql.txt
new file mode 100644
index 0000000..fbda7b8
--- /dev/null
+++ b/testdata/result/statement/insert_or_update.sql.txt
@@ -0,0 +1,59 @@
+--- insert_or_update.sql
+INSERT OR UPDATE INTO foo
+(foo, bar) VALUES (1, 2)
+--- AST
+&ast.Insert{
+  Insert:       0,
+  InsertOrType: "UPDATE",
+  TableName:    &ast.Ident{
+    NamePos: 22,
+    NameEnd: 25,
+    Name:    "foo",
+  },
+  Columns: []*ast.Ident{
+    &ast.Ident{
+      NamePos: 27,
+      NameEnd: 30,
+      Name:    "foo",
+    },
+    &ast.Ident{
+      NamePos: 32,
+      NameEnd: 35,
+      Name:    "bar",
+    },
+  },
+  Input: &ast.ValuesInput{
+    Values: 37,
+    Rows:   []*ast.ValuesRow{
+      &ast.ValuesRow{
+        Lparen: 44,
+        Rparen: 49,
+        Exprs:  []*ast.DefaultExpr{
+          &ast.DefaultExpr{
+            DefaultPos: -1,
+            Default:    false,
+            Expr:       &ast.IntLiteral{
+              ValuePos: 45,
+              ValueEnd: 46,
+              Base:     10,
+              Value:    "1",
+            },
+          },
+          &ast.DefaultExpr{
+            DefaultPos: -1,
+            Default:    false,
+            Expr:       &ast.IntLiteral{
+              ValuePos: 48,
+              ValueEnd: 49,
+              Base:     10,
+              Value:    "2",
+            },
+          },
+        },
+      },
+    },
+  },
+}
+
+--- SQL
+INSERT OR UPDATE INTO foo (foo, bar) VALUES (1, 2)
diff --git a/testdata/result/statement/insert_select.sql.txt b/testdata/result/statement/insert_select.sql.txt
index 96fdfb7..690f8a3 100644
--- a/testdata/result/statement/insert_select.sql.txt
+++ b/testdata/result/statement/insert_select.sql.txt
@@ -3,8 +3,9 @@ insert foo (foo, bar)
 select * from unnest([(1, 2), (3, 4)])
 --- AST
 &ast.Insert{
-  Insert:    0,
-  TableName: &ast.Ident{
+  Insert:       0,
+  InsertOrType: "",
+  TableName:    &ast.Ident{
     NamePos: 7,
     NameEnd: 10,
     Name:    "foo",
diff --git a/testdata/result/statement/insert_values.sql.txt b/testdata/result/statement/insert_values.sql.txt
index 2690fb3..09897f4 100644
--- a/testdata/result/statement/insert_values.sql.txt
+++ b/testdata/result/statement/insert_values.sql.txt
@@ -4,8 +4,9 @@ values (1, 2, 3),
        (4, 5, 6)
 --- AST
 &ast.Insert{
-  Insert:    0,
-  TableName: &ast.Ident{
+  Insert:       0,
+  InsertOrType: "",
+  TableName:    &ast.Ident{
     NamePos: 7,
     NameEnd: 10,
     Name:    "foo",
diff --git a/testdata/result/statement/insert_values_default.sql.txt b/testdata/result/statement/insert_values_default.sql.txt
index cc53875..ae7d07c 100644
--- a/testdata/result/statement/insert_values_default.sql.txt
+++ b/testdata/result/statement/insert_values_default.sql.txt
@@ -3,8 +3,9 @@ insert foo (foo, bar)
 values (1, default)
 --- AST
 &ast.Insert{
-  Insert:    0,
-  TableName: &ast.Ident{
+  Insert:       0,
+  InsertOrType: "",
+  TableName:    &ast.Ident{
     NamePos: 7,
     NameEnd: 10,
     Name:    "foo",
diff --git a/testdata/result/statement/insert_with_sequence_function.sql.txt b/testdata/result/statement/insert_with_sequence_function.sql.txt
index b93db39..1928314 100644
--- a/testdata/result/statement/insert_with_sequence_function.sql.txt
+++ b/testdata/result/statement/insert_with_sequence_function.sql.txt
@@ -3,8 +3,9 @@ INSERT INTO foo(bar) VALUES (GET_NEXT_SEQUENCE_VALUE(SEQUENCE my_sequence))
 
 --- AST
 &ast.Insert{
-  Insert:    0,
-  TableName: &ast.Ident{
+  Insert:       0,
+  InsertOrType: "",
+  TableName:    &ast.Ident{
     NamePos: 12,
     NameEnd: 15,
     Name:    "foo",