Repository: AfterShip/clickhouse-sql-parser
Pull request #5 (2023-09-25 07:35:51)

Add the support of INSERT expression


Patch:
diff --git a/parser/ast.go b/parser/ast.go
index 2366972..67931d4 100644
--- a/parser/ast.go
+++ b/parser/ast.go
@@ -3281,3 +3281,104 @@ func (d *DeleteFromExpr) String(level int) string {
 	}
 	return builder.String()
 }
+
+type ColumnNamesExpr struct {
+	LeftParenPos  Pos
+	RightParenPos Pos
+	ColumnNames   []NestedIdentifier
+}
+
+func (c *ColumnNamesExpr) Pos() Pos {
+	return c.LeftParenPos
+}
+
+func (c *ColumnNamesExpr) End() Pos {
+	return c.RightParenPos
+}
+
+func (c *ColumnNamesExpr) String(level int) string {
+	var builder strings.Builder
+	builder.WriteByte('(')
+	for i, column := range c.ColumnNames {
+		if i > 0 {
+			builder.WriteString(", ")
+		}
+		builder.WriteString(column.String(level))
+	}
+	builder.WriteByte(')')
+	return builder.String()
+}
+
+type ValuesExpr struct {
+	LeftParenPos  Pos
+	RightParenPos Pos
+	Values        []Expr
+}
+
+func (v *ValuesExpr) Pos() Pos {
+	return v.LeftParenPos
+}
+
+func (v *ValuesExpr) End() Pos {
+	return v.RightParenPos
+}
+
+func (v *ValuesExpr) String(level int) string {
+	var builder strings.Builder
+	builder.WriteByte('(')
+	for i, value := range v.Values {
+		if i > 0 {
+			builder.WriteString(", ")
+		}
+		builder.WriteString(value.String(level))
+	}
+	builder.WriteByte(')')
+	return builder.String()
+}
+
+type InsertExpr struct {
+	InsertPos   Pos
+	Format      *FormatExpr
+	Table       Expr
+	ColumnNames *ColumnNamesExpr
+	Values      []*ValuesExpr
+	SelectExpr  *SelectExpr
+}
+
+func (i *InsertExpr) Pos() Pos {
+	return i.InsertPos
+}
+
+func (i *InsertExpr) End() Pos {
+	if i.SelectExpr != nil {
+		return i.SelectExpr.End()
+	}
+	return i.Values[len(i.Values)-1].End()
+}
+
+func (i *InsertExpr) String(level int) string {
+	var builder strings.Builder
+	builder.WriteString("INSERT INTO TABLE ")
+	builder.WriteString(i.Table.String(level))
+	if i.ColumnNames != nil {
+		builder.WriteString(NewLine(level + 1))
+		builder.WriteString(i.ColumnNames.String(level))
+	}
+	if i.Format != nil {
+		builder.WriteString(NewLine(level))
+		builder.WriteString(i.Format.String(level))
+	}
+
+	if i.SelectExpr != nil {
+		builder.WriteString(i.SelectExpr.String(level))
+	} else {
+		builder.WriteString(NewLine(level))
+		builder.WriteString("VALUES ")
+		for _, value := range i.Values {
+			builder.WriteString(NewLine(level + 1))
+			builder.WriteString(value.String(level))
+			builder.WriteByte(',')
+		}
+	}
+	return builder.String()
+}
diff --git a/parser/parser_table.go b/parser/parser_table.go
index 2e09123..7162855 100644
--- a/parser/parser_table.go
+++ b/parser/parser_table.go
@@ -896,6 +896,8 @@ func (p *Parser) parseStatement(pos Pos) (Expr, error) {
 		expr, err = p.parseSelectQuery(pos)
 	case p.matchKeyword(KeywordDelete):
 		expr, err = p.parseDeleteFrom(pos)
+	case p.matchKeyword(KeywordInsert):
+		expr, err = p.parseInsertExpr(p.Pos())
 	case p.matchKeyword(KeywordUse):
 		expr, err = p.parseUseStatement(pos)
 	case p.matchKeyword(KeywordSet):
@@ -1030,3 +1032,133 @@ func (p *Parser) parseDeleteFrom(pos Pos) (*DeleteFromExpr, error) {
 		WhereExpr: whereExpr,
 	}, nil
 }
+
+func (p *Parser) parseColumnNamesExpr(pos Pos) (*ColumnNamesExpr, error) {
+	if _, err := p.consumeTokenKind("("); err != nil {
+		return nil, err
+	}
+
+	var columnNames []NestedIdentifier
+	for !p.lexer.isEOF() && p.tryConsumeTokenKind(")") == nil {
+		name, err := p.ParseNestedIdentifier(p.Pos())
+		if err != nil {
+			return nil, err
+		}
+		if p.tryConsumeTokenKind(",") == nil {
+			break
+		}
+		columnNames = append(columnNames, *name)
+	}
+	rightParenPos := p.Pos()
+	if _, err := p.consumeTokenKind(")"); err != nil {
+		return nil, err
+	}
+	return &ColumnNamesExpr{
+		LeftParenPos:  pos,
+		RightParenPos: rightParenPos,
+		ColumnNames:   columnNames,
+	}, nil
+}
+
+func (p *Parser) parseValuesExpr(pos Pos) (*ValuesExpr, error) {
+	if _, err := p.consumeTokenKind("("); err != nil {
+		return nil, err
+	}
+
+	var value Expr
+	var err error
+	values := make([]Expr, 0)
+	for !p.lexer.isEOF() && p.tryConsumeTokenKind(")") == nil {
+		switch {
+		case p.matchTokenKind("("):
+			value, err = p.parseValuesExpr(p.Pos())
+		default:
+			value, err = p.parseExpr(p.Pos())
+		}
+		if err != nil {
+			return nil, err
+		}
+		values = append(values, value)
+		if p.tryConsumeTokenKind(",") == nil {
+			break
+		}
+	}
+	rightParenPos := p.Pos()
+	if _, err := p.consumeTokenKind(")"); err != nil {
+		return nil, err
+	}
+
+	return &ValuesExpr{
+		LeftParenPos:  pos,
+		RightParenPos: rightParenPos,
+		Values:        values,
+	}, nil
+}
+
+func (p *Parser) parseInsertExpr(pos Pos) (*InsertExpr, error) {
+	if err := p.consumeKeyword(KeywordInsert); err != nil {
+		return nil, err
+	}
+	if err := p.consumeKeyword(KeywordInto); err != nil {
+		return nil, err
+	}
+	_ = p.tryConsumeKeyword(KeywordTable)
+
+	var table Expr
+	var err error
+	if p.tryConsumeKeyword(KeywordFunction) != nil {
+		table, err = p.parseFunctionExpr(p.Pos())
+	} else {
+		table, err = p.parseTableIdentifier(p.Pos())
+	}
+	if err != nil {
+		return nil, err
+	}
+
+	insertExpr := &InsertExpr{
+		InsertPos: pos,
+		Table:     table,
+	}
+
+	for i := 0; i < 1; i++ {
+		switch {
+		case p.matchKeyword(KeywordFormat):
+			insertExpr.Format, err = p.parseFormatExpr(p.Pos())
+		case p.matchKeyword(KeywordValues):
+			// consume VALUES keyword
+			_ = p.lexer.consumeToken()
+		case p.matchKeyword(KeywordSelect):
+			insertExpr.SelectExpr, err = p.parseSelectQuery(p.Pos())
+			if err != nil {
+				return nil, err
+			}
+			return insertExpr, nil
+		default:
+			if insertExpr.ColumnNames == nil {
+				// columns
+				insertExpr.ColumnNames, err = p.parseColumnNamesExpr(p.Pos())
+				// need another pass to parse keywords
+				i--
+			}
+		}
+	}
+
+	if err != nil {
+		return nil, err
+	}
+
+	values := make([]*ValuesExpr, 0)
+	for !p.lexer.isEOF() {
+		value, err := p.parseValuesExpr(p.Pos())
+		if err != nil {
+			return nil, err
+		}
+		values = append(values, value)
+		if p.tryConsumeTokenKind(",") == nil {
+			break
+		}
+	}
+	insertExpr.Values = values
+
+	return insertExpr, nil
+}


Test patch:
diff --git a/parser/testdata/dml/format/insert_values.sql b/parser/testdata/dml/format/insert_values.sql
new file mode 100644
index 0000000..dfdfdd2
--- /dev/null
+++ b/parser/testdata/dml/format/insert_values.sql
@@ -0,0 +1,15 @@
+-- Origin SQL:
+INSERT INTO helloworld.my_first_table (user_id, message, timestamp, metric) VALUES
+    (101, 'Hello, ClickHouse!',                                 now(),       -1.0    ),
+    (102, 'Insert a lot of rows per batch',                     yesterday(), 1.41421 ),
+    (102, 'Sort your data based on your commonly-used queries', today(),     2.718   ),
+    (101, 'Granules are the smallest chunks of data read',      now() + 5,   3.14159 )
+
+-- Format SQL:
+INSERT INTO TABLE helloworld.my_first_table
+  (user_id, message, timestamp)
+VALUES 
+  (101, 'Hello, ClickHouse!', now(), -1.0),
+  (102, 'Insert a lot of rows per batch', yesterday(), 1.41421),
+  (102, 'Sort your data based on your commonly-used queries', today(), 2.718),
+  (101, 'Granules are the smallest chunks of data read', now() + 5, 3.14159),;
diff --git a/parser/testdata/dml/format/insert_with_select.sql b/parser/testdata/dml/format/insert_with_select.sql
new file mode 100644
index 0000000..0455694
--- /dev/null
+++ b/parser/testdata/dml/format/insert_with_select.sql
@@ -0,0 +1,18 @@
+-- Origin SQL:
+INSERT INTO test.visits_null
+SELECT
+    CounterID,
+    StartDate,
+    Sign,
+    UserID
+FROM test.visits;
+
+-- Format SQL:
+INSERT INTO TABLE test.visits_null
+SELECT 
+  CounterID,
+  StartDate,
+  Sign,
+  UserID
+FROM
+  test.visits;
diff --git a/parser/testdata/dml/insert_values.sql b/parser/testdata/dml/insert_values.sql
new file mode 100644
index 0000000..946b314
--- /dev/null
+++ b/parser/testdata/dml/insert_values.sql
@@ -0,0 +1,5 @@
+INSERT INTO helloworld.my_first_table (user_id, message, timestamp, metric) VALUES
+    (101, 'Hello, ClickHouse!',                                 now(),       -1.0    ),
+    (102, 'Insert a lot of rows per batch',                     yesterday(), 1.41421 ),
+    (102, 'Sort your data based on your commonly-used queries', today(),     2.718   ),
+    (101, 'Granules are the smallest chunks of data read',      now() + 5,   3.14159 )
\ No newline at end of file
diff --git a/parser/testdata/dml/insert_with_select.sql b/parser/testdata/dml/insert_with_select.sql
new file mode 100644
index 0000000..d7187ab
--- /dev/null
+++ b/parser/testdata/dml/insert_with_select.sql
@@ -0,0 +1,7 @@
+INSERT INTO test.visits_null
+SELECT
+    CounterID,
+    StartDate,
+    Sign,
+    UserID
+FROM test.visits;
\ No newline at end of file
diff --git a/parser/testdata/dml/output/insert_values.sql.golden.json b/parser/testdata/dml/output/insert_values.sql.golden.json
new file mode 100644
index 0000000..99ffdca
--- /dev/null
+++ b/parser/testdata/dml/output/insert_values.sql.golden.json
@@ -0,0 +1,226 @@
+[
+  {
+    "InsertPos": 0,
+    "Format": null,
+    "Table": {
+      "Database": {
+        "Name": "helloworld",
+        "Unquoted": false,
+        "NamePos": 12,
+        "NameEnd": 22
+      },
+      "Table": {
+        "Name": "my_first_table",
+        "Unquoted": false,
+        "NamePos": 23,
+        "NameEnd": 37
+      }
+    },
+    "ColumnNames": {
+      "LeftParenPos": 38,
+      "RightParenPos": 74,
+      "ColumnNames": [
+        {
+          "Ident": {
+            "Name": "user_id",
+            "Unquoted": false,
+            "NamePos": 39,
+            "NameEnd": 46
+          },
+          "DotIdent": null
+        },
+        {
+          "Ident": {
+            "Name": "message",
+            "Unquoted": false,
+            "NamePos": 48,
+            "NameEnd": 55
+          },
+          "DotIdent": null
+        },
+        {
+          "Ident": {
+            "Name": "timestamp",
+            "Unquoted": false,
+            "NamePos": 57,
+            "NameEnd": 66
+          },
+          "DotIdent": null
+        }
+      ]
+    },
+    "Values": [
+      {
+        "LeftParenPos": 87,
+        "RightParenPos": 168,
+        "Values": [
+          {
+            "NumPos": 88,
+            "NumEnd": 91,
+            "Literal": "101",
+            "Base": 10
+          },
+          {
+            "LiteralPos": 94,
+            "LiteralEnd": 112,
+            "Literal": "Hello, ClickHouse!"
+          },
+          {
+            "Name": {
+              "Name": "now",
+              "Unquoted": false,
+              "NamePos": 147,
+              "NameEnd": 150
+            },
+            "Params": {
+              "LeftParenPos": 150,
+              "RightParenPos": 151,
+              "Items": {
+                "ListPos": 151,
+                "ListEnd": 151,
+                "Items": []
+              },
+              "ColumnArgList": null
+            }
+          },
+          {
+            "FloatPos": 160,
+            "FloatEnd": 164,
+            "Literal": "-1.0"
+          }
+        ]
+      },
+      {
+        "LeftParenPos": 175,
+        "RightParenPos": 256,
+        "Values": [
+          {
+            "NumPos": 176,
+            "NumEnd": 179,
+            "Literal": "102",
+            "Base": 10
+          },
+          {
+            "LiteralPos": 182,
+            "LiteralEnd": 212,
+            "Literal": "Insert a lot of rows per batch"
+          },
+          {
+            "Name": {
+              "Name": "yesterday",
+              "Unquoted": false,
+              "NamePos": 235,
+              "NameEnd": 244
+            },
+            "Params": {
+              "LeftParenPos": 244,
+              "RightParenPos": 245,
+              "Items": {
+                "ListPos": 245,
+                "ListEnd": 245,
+                "Items": []
+              },
+              "ColumnArgList": null
+            }
+          },
+          {
+            "FloatPos": 248,
+            "FloatEnd": 255,
+            "Literal": "1.41421"
+          }
+        ]
+      },
+      {
+        "LeftParenPos": 263,
+        "RightParenPos": 344,
+        "Values": [
+          {
+            "NumPos": 264,
+            "NumEnd": 267,
+            "Literal": "102",
+            "Base": 10
+          },
+          {
+            "LiteralPos": 270,
+            "LiteralEnd": 320,
+            "Literal": "Sort your data based on your commonly-used queries"
+          },
+          {
+            "Name": {
+              "Name": "today",
+              "Unquoted": false,
+              "NamePos": 323,
+              "NameEnd": 328
+            },
+            "Params": {
+              "LeftParenPos": 328,
+              "RightParenPos": 329,
+              "Items": {
+                "ListPos": 329,
+                "ListEnd": 329,
+                "Items": []
+              },
+              "ColumnArgList": null
+            }
+          },
+          {
+            "FloatPos": 336,
+            "FloatEnd": 341,
+            "Literal": "2.718"
+          }
+        ]
+      },
+      {
+        "LeftParenPos": 351,
+        "RightParenPos": 432,
+        "Values": [
+          {
+            "NumPos": 352,
+            "NumEnd": 355,
+            "Literal": "101",
+            "Base": 10
+          },
+          {
+            "LiteralPos": 358,
+            "LiteralEnd": 403,
+            "Literal": "Granules are the smallest chunks of data read"
+          },
+          {
+            "LeftExpr": {
+              "Name": {
+                "Name": "now",
+                "Unquoted": false,
+                "NamePos": 411,
+                "NameEnd": 414
+              },
+              "Params": {
+                "LeftParenPos": 414,
+                "RightParenPos": 415,
+                "Items": {
+                  "ListPos": 415,
+                  "ListEnd": 415,
+                  "Items": []
+                },
+                "ColumnArgList": null
+              }
+            },
+            "Operation": "+",
+            "RightExpr": {
+              "NumPos": 419,
+              "NumEnd": 420,
+              "Literal": "5",
+              "Base": 10
+            },
+            "HasNot": false
+          },
+          {
+            "FloatPos": 424,
+            "FloatEnd": 431,
+            "Literal": "3.14159"
+          }
+        ]
+      }
+    ],
+    "SelectExpr": null
+  }
+]
\ No newline at end of file
diff --git a/parser/testdata/dml/output/insert_with_select.sql.golden.json b/parser/testdata/dml/output/insert_with_select.sql.golden.json
new file mode 100644
index 0000000..639f308
--- /dev/null
+++ b/parser/testdata/dml/output/insert_with_select.sql.golden.json
@@ -0,0 +1,89 @@
+[
+  {
+    "InsertPos": 0,
+    "Format": null,
+    "Table": {
+      "Database": {
+        "Name": "test",
+        "Unquoted": false,
+        "NamePos": 12,
+        "NameEnd": 16
+      },
+      "Table": {
+        "Name": "visits_null",
+        "Unquoted": false,
+        "NamePos": 17,
+        "NameEnd": 28
+      }
+    },
+    "ColumnNames": null,
+    "Values": null,
+    "SelectExpr": {
+      "SelectPos": 29,
+      "StatementEnd": 103,
+      "With": null,
+      "Top": null,
+      "SelectColumns": {
+        "ListPos": 40,
+        "ListEnd": 86,
+        "Items": [
+          {
+            "Name": "CounterID",
+            "Unquoted": false,
+            "NamePos": 40,
+            "NameEnd": 49
+          },
+          {
+            "Name": "StartDate",
+            "Unquoted": false,
+            "NamePos": 55,
+            "NameEnd": 64
+          },
+          {
+            "Name": "Sign",
+            "Unquoted": false,
+            "NamePos": 70,
+            "NameEnd": 74
+          },
+          {
+            "Name": "UserID",
+            "Unquoted": false,
+            "NamePos": 80,
+            "NameEnd": 86
+          }
+        ]
+      },
+      "From": {
+        "FromPos": 87,
+        "Expr": {
+          "TablePos": 92,
+          "TableEnd": 103,
+          "Alias": null,
+          "Expr": {
+            "Database": {
+              "Name": "test",
+              "Unquoted": false,
+              "NamePos": 92,
+              "NameEnd": 96
+            },
+            "Table": {
+              "Name": "visits",
+              "Unquoted": false,
+              "NamePos": 97,
+              "NameEnd": 103
+            }
+          }
+        }
+      },
+      "ArrayJoin": null,
+      "Window": null,
+      "Prewhere": null,
+      "Where": null,
+      "GroupBy": null,
+      "Having": null,
+      "OrderBy": null,
+      "LimitBy": null,
+      "Settings": null
+    }
+  }
+]
\ No newline at end of file