Skip to content

Add MySQL supports #3

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
huacnlee merged 3 commits into from
Jan 28, 2022
Merged

Add MySQL supports #3

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
3 commits
Select commit Hold shift + click to select a range
38de8c5
Remove pg-specific quote to adapt other db
marvinova Jan 28, 2022
d6b3ff5
support comment hint
marvinova Aug 4, 2021
234a197
Fix hint test
huacnlee Jan 28, 2022
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
2 changes: 1 addition & 1 deletion README.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2,7 +2,7 @@

[![Go](https://github.com/longbridgeapp/sqlparser/actions/workflows/go.yml/badge.svg)](https://github.com/longbridgeapp/sqlparser/actions/workflows/go.yml)

A SQL parser for PostgreSQL.
A SQL parser.

## Installation

Expand Down
13 changes: 10 additions & 3 deletions ast.go
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -236,13 +236,20 @@ type Constraint interface {
}

type Ident struct {
Name string // identifier name
Quoted bool // true if double quoted
Name string // identifier name
Quoted bool // true if double quoted
QuoteChar string // " for postgresql, ` for mysql, etc
}

// String returns the string representation of the expression.
func (i *Ident) String() string {
return `"` + strings.Replace(i.Name, `"`, `""`, -1) + `"`
if i.Quoted {
if i.QuoteChar == `"` {
return i.QuoteChar + strings.ReplaceAll(i.Name, `"`, `""`) + i.QuoteChar
}
return i.QuoteChar + i.Name + i.QuoteChar
}
return i.Name
}

// IdentName returns the name of ident. Returns a blank string if ident is nil.
Expand Down
91 changes: 59 additions & 32 deletions ast_test.go
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -49,66 +49,78 @@ func TestSplitExprTree(t *testing.T) {

func AssertSplitExprTree(tb testing.TB, s string, want []sqlparser.Expr) {
tb.Helper()
if diff := deep.Equal(sqlparser.SplitExprTree(StripExprPos(sqlparser.MustParseExprString(s))), want); diff != nil {
e := sqlparser.MustParseExprString(s)
if diff := deep.Equal(sqlparser.SplitExprTree(StripExprPos(e)), want); diff != nil {
tb.Fatal("mismatch: \n" + strings.Join(diff, "\n"))
}
}

func TestDeleteStatement_String(t *testing.T) {
AssertStatementStringer(t, &sqlparser.DeleteStatement{
TableName: &sqlparser.TableName{Name: &sqlparser.Ident{Name: "tbl"}, Alias: &sqlparser.Ident{Name: "tbl2"}},
}, `DELETE FROM "tbl" AS "tbl2"`)
TableName: &sqlparser.TableName{
Name: &sqlparser.Ident{Name: "tbl"},
Alias: &sqlparser.Ident{Name: "tbl2"},
},
}, `DELETE FROM tbl AS tbl2`)

AssertStatementStringer(t, &sqlparser.DeleteStatement{
TableName: &sqlparser.TableName{Name: &sqlparser.Ident{Name: "tbl"}},
TableName: &sqlparser.TableName{
Name: &sqlparser.Ident{Name: "tbl", Quoted: true, QuoteChar: `"`},
},
}, `DELETE FROM "tbl"`)

AssertStatementStringer(t, &sqlparser.DeleteStatement{
TableName: &sqlparser.TableName{
Name: &sqlparser.Ident{Name: "tbl", Quoted: true, QuoteChar: "`"},
},
}, "DELETE FROM `tbl`")

AssertStatementStringer(t, &sqlparser.DeleteStatement{
TableName: &sqlparser.TableName{Name: &sqlparser.Ident{Name: "tbl"}},
Condition: &sqlparser.BoolLit{Value: true},
}, `DELETE FROM "tbl" WHERE TRUE`)
}, `DELETE FROM tbl WHERE TRUE`)
}

func TestInsertStatement_String(t *testing.T) {
AssertStatementStringer(t, &sqlparser.InsertStatement{
TableName: &sqlparser.TableName{Name: &sqlparser.Ident{Name: "tbl"}},
DefaultValues: true,
}, `INSERT INTO "tbl" DEFAULT VALUES`)
}, `INSERT INTO tbl DEFAULT VALUES`)

AssertStatementStringer(t, &sqlparser.InsertStatement{
TableName: &sqlparser.TableName{
Name: &sqlparser.Ident{Name: "tbl"},
Alias: &sqlparser.Ident{Name: "x"},
},
DefaultValues: true,
}, `INSERT INTO "tbl" AS "x" DEFAULT VALUES`)
}, `INSERT INTO tbl AS x DEFAULT VALUES`)

AssertStatementStringer(t, &sqlparser.InsertStatement{
TableName: &sqlparser.TableName{Name: &sqlparser.Ident{Name: "tbl"}},
Query: &sqlparser.SelectStatement{
Columns: &sqlparser.OutputNames{&sqlparser.ResultColumn{Star: true}},
},
}, `INSERT INTO "tbl" SELECT *`)
}, `INSERT INTO tbl SELECT *`)

AssertStatementStringer(t, &sqlparser.InsertStatement{
TableName: &sqlparser.TableName{Name: &sqlparser.Ident{Name: "tbl"}},
ColumnNames: []*sqlparser.Ident{
{Name: "x"},
{Name: "y"},
{Name: "y", Quoted: true, QuoteChar: `"`},
},
Expressions: []*sqlparser.Exprs{
{Exprs: []sqlparser.Expr{&sqlparser.NullLit{}, &sqlparser.NullLit{}}},
{Exprs: []sqlparser.Expr{&sqlparser.NullLit{}, &sqlparser.NullLit{}}},
},
}, `INSERT INTO "tbl" ("x", "y") VALUES (NULL, NULL), (NULL, NULL)`)
}, `INSERT INTO tbl (x, "y") VALUES (NULL, NULL), (NULL, NULL)`)

AssertStatementStringer(t, &sqlparser.InsertStatement{
TableName: &sqlparser.TableName{Name: &sqlparser.Ident{Name: "tbl"}},
DefaultValues: true,
UpsertClause: &sqlparser.UpsertClause{
DoNothing: true,
},
}, `INSERT INTO "tbl" DEFAULT VALUES ON CONFLICT DO NOTHING`)
}, `INSERT INTO tbl DEFAULT VALUES ON CONFLICT DO NOTHING`)

AssertStatementStringer(t, &sqlparser.InsertStatement{
TableName: &sqlparser.TableName{Name: &sqlparser.Ident{Name: "tbl"}},
Expand All @@ -125,13 +137,13 @@ func TestInsertStatement_String(t *testing.T) {
},
UpdateWhereExpr: &sqlparser.BoolLit{Value: false},
},
}, `INSERT INTO "tbl" DEFAULT VALUES ON CONFLICT ("x" ASC, "y" DESC) WHERE TRUE DO UPDATE SET "x" = 100, ("y", "z") = 200 WHERE FALSE`)
}, `INSERT INTO tbl DEFAULT VALUES ON CONFLICT (x ASC, y DESC) WHERE TRUE DO UPDATE SET x = 100, (y, z) = 200 WHERE FALSE`)

AssertStatementStringer(t, &sqlparser.InsertStatement{
TableName: &sqlparser.TableName{Name: &sqlparser.Ident{Name: "tbl"}},
DefaultValues: true,
OutputExpressions: &sqlparser.OutputNames{&sqlparser.ResultColumn{Star: true}},
}, `INSERT INTO "tbl" DEFAULT VALUES RETURNING *`)
}, `INSERT INTO tbl DEFAULT VALUES RETURNING *`)

AssertStatementStringer(t, &sqlparser.InsertStatement{
TableName: &sqlparser.TableName{Name: &sqlparser.Ident{Name: "tbl"}},
Expand All @@ -140,7 +152,7 @@ func TestInsertStatement_String(t *testing.T) {
&sqlparser.ResultColumn{Expr: &sqlparser.Ident{Name: "x"}, Alias: &sqlparser.Ident{Name: "y"}},
&sqlparser.ResultColumn{Expr: &sqlparser.Ident{Name: "z"}},
},
}, `INSERT INTO "tbl" DEFAULT VALUES RETURNING "x" AS "y", "z"`)
}, `INSERT INTO tbl DEFAULT VALUES RETURNING x AS y, z`)
}

func TestSelectStatement_String(t *testing.T) {
Expand All @@ -149,23 +161,28 @@ func TestSelectStatement_String(t *testing.T) {
&sqlparser.ResultColumn{Expr: &sqlparser.Ident{Name: "x"}, Alias: &sqlparser.Ident{Name: "y"}},
&sqlparser.ResultColumn{Expr: &sqlparser.Ident{Name: "z"}},
},
}, `SELECT "x" AS "y", "z"`)
}, `SELECT x AS y, z`)

AssertStatementStringer(t, &sqlparser.SelectStatement{
Distinct: true,
Columns: &sqlparser.OutputNames{&sqlparser.ResultColumn{
Expr: &sqlparser.Ident{Name: "x"},
}},
}, `SELECT DISTINCT "x"`)
}, `SELECT DISTINCT x`)

AssertStatementStringer(t, &sqlparser.SelectStatement{
All: true,
Columns: &sqlparser.OutputNames{&sqlparser.ResultColumn{Expr: &sqlparser.Ident{Name: "x"}}},
}, `SELECT ALL "x"`)
}, `SELECT ALL x`)

AssertStatementStringer(t, &sqlparser.SelectStatement{
Hint: &sqlparser.Hint{Value: "HINT"},
Columns: &sqlparser.OutputNames{&sqlparser.ResultColumn{Expr: &sqlparser.Ident{Name: "x"}}},
}, `SELECT /* HINT */ x`)

AssertStatementStringer(t, &sqlparser.SelectStatement{
Hint: &sqlparser.Hint{Value: "HINT"},
Columns: &sqlparser.OutputNames{&sqlparser.ResultColumn{Expr: &sqlparser.Ident{Name: "x", Quoted: true, QuoteChar: `"`}}},
}, `SELECT /* HINT */ "x"`)

AssertStatementStringer(t, &sqlparser.SelectStatement{
Expand All @@ -174,15 +191,15 @@ func TestSelectStatement_String(t *testing.T) {
Condition: &sqlparser.BoolLit{Value: true},
GroupingElements: []sqlparser.Expr{&sqlparser.Ident{Name: "x"}, &sqlparser.Ident{Name: "y"}},
HavingCondition: &sqlparser.Ident{Name: "z"},
}, `SELECT * FROM "tbl" WHERE TRUE GROUP BY "x", "y" HAVING "z"`)
}, `SELECT * FROM tbl WHERE TRUE GROUP BY x, y HAVING z`)

AssertStatementStringer(t, &sqlparser.SelectStatement{
Columns: &sqlparser.OutputNames{&sqlparser.ResultColumn{Star: true}},
FromItems: &sqlparser.ParenSource{
X: &sqlparser.SelectStatement{Columns: &sqlparser.OutputNames{&sqlparser.ResultColumn{Star: true}}},
Alias: &sqlparser.Ident{Name: "tbl"},
},
}, `SELECT * FROM (SELECT *) AS "tbl"`)
}, `SELECT * FROM (SELECT *) AS tbl`)

AssertStatementStringer(t, &sqlparser.SelectStatement{
Columns: &sqlparser.OutputNames{&sqlparser.ResultColumn{Star: true}},
Expand Down Expand Up @@ -230,7 +247,7 @@ func TestSelectStatement_String(t *testing.T) {
{X: &sqlparser.Ident{Name: "x"}},
{X: &sqlparser.Ident{Name: "y"}},
},
}, `SELECT * ORDER BY "x", "y"`)
}, `SELECT * ORDER BY x, y`)

AssertStatementStringer(t, &sqlparser.SelectStatement{
Columns: &sqlparser.OutputNames{&sqlparser.ResultColumn{Star: true}},
Expand All @@ -245,7 +262,7 @@ func TestSelectStatement_String(t *testing.T) {
Operator: &sqlparser.JoinOperator{Comma: true},
Y: &sqlparser.TableName{Name: &sqlparser.Ident{Name: "y"}},
},
}, `SELECT * FROM "x", "y"`)
}, `SELECT * FROM x, y`)

AssertStatementStringer(t, &sqlparser.SelectStatement{
Columns: &sqlparser.OutputNames{&sqlparser.ResultColumn{Star: true}},
Expand All @@ -255,7 +272,7 @@ func TestSelectStatement_String(t *testing.T) {
Y: &sqlparser.TableName{Name: &sqlparser.Ident{Name: "y"}},
Constraint: &sqlparser.OnConstraint{X: &sqlparser.BoolLit{Value: true}},
},
}, `SELECT * FROM "x" JOIN "y" ON TRUE`)
}, `SELECT * FROM x JOIN y ON TRUE`)

AssertStatementStringer(t, &sqlparser.SelectStatement{
Columns: &sqlparser.OutputNames{&sqlparser.ResultColumn{Star: true}},
Expand All @@ -267,7 +284,7 @@ func TestSelectStatement_String(t *testing.T) {
Columns: []*sqlparser.Ident{{Name: "a"}, {Name: "b"}},
},
},
}, `SELECT * FROM "x" NATURAL INNER JOIN "y" USING ("a", "b")`)
}, `SELECT * FROM x NATURAL INNER JOIN y USING (a, b)`)

AssertStatementStringer(t, &sqlparser.SelectStatement{
Columns: &sqlparser.OutputNames{&sqlparser.ResultColumn{Star: true}},
Expand All @@ -276,7 +293,7 @@ func TestSelectStatement_String(t *testing.T) {
Operator: &sqlparser.JoinOperator{Left: true},
Y: &sqlparser.TableName{Name: &sqlparser.Ident{Name: "y"}},
},
}, `SELECT * FROM "x" LEFT JOIN "y"`)
}, `SELECT * FROM x LEFT JOIN y`)

AssertStatementStringer(t, &sqlparser.SelectStatement{
Columns: &sqlparser.OutputNames{&sqlparser.ResultColumn{Star: true}},
Expand All @@ -285,7 +302,7 @@ func TestSelectStatement_String(t *testing.T) {
Operator: &sqlparser.JoinOperator{Left: true, Outer: true},
Y: &sqlparser.TableName{Name: &sqlparser.Ident{Name: "y"}},
},
}, `SELECT * FROM "x" LEFT OUTER JOIN "y"`)
}, `SELECT * FROM x LEFT OUTER JOIN y`)

AssertStatementStringer(t, &sqlparser.SelectStatement{
Columns: &sqlparser.OutputNames{&sqlparser.ResultColumn{Star: true}},
Expand All @@ -294,7 +311,7 @@ func TestSelectStatement_String(t *testing.T) {
Operator: &sqlparser.JoinOperator{Cross: true},
Y: &sqlparser.TableName{Name: &sqlparser.Ident{Name: "y"}},
},
}, `SELECT * FROM "x" CROSS JOIN "y"`)
}, `SELECT * FROM x CROSS JOIN y`)
}

func TestUpdateStatement_String(t *testing.T) {
Expand All @@ -305,12 +322,13 @@ func TestUpdateStatement_String(t *testing.T) {
{Columns: []*sqlparser.Ident{{Name: "y"}}, Expr: &sqlparser.NumberLit{Value: "200"}},
},
Condition: &sqlparser.BoolLit{Value: true},
}, `UPDATE "tbl" SET "x" = 100, "y" = 200 WHERE TRUE`)
}, `UPDATE tbl SET x = 100, y = 200 WHERE TRUE`)
}

func TestIdent_String(t *testing.T) {
AssertExprStringer(t, &sqlparser.Ident{Name: "foo"}, `"foo"`)
AssertExprStringer(t, &sqlparser.Ident{Name: "foo \" bar"}, `"foo "" bar"`)
AssertExprStringer(t, &sqlparser.Ident{Name: "foo"}, `foo`)
AssertExprStringer(t, &sqlparser.Ident{Name: "foo \" bar", Quoted: true, QuoteChar: `"`}, `"foo "" bar"`)
AssertExprStringer(t, &sqlparser.Ident{Name: `foo " bar`, Quoted: true, QuoteChar: "`"}, "`foo \" bar`")
}

func TestStringLit_String(t *testing.T) {
Expand Down Expand Up @@ -394,7 +412,7 @@ func TestCaseExpr_String(t *testing.T) {
{Condition: &sqlparser.NumberLit{Value: "2"}, Body: &sqlparser.BoolLit{Value: false}},
},
ElseExpr: &sqlparser.NullLit{},
}, `CASE "foo" WHEN 1 THEN TRUE WHEN 2 THEN FALSE ELSE NULL END`)
}, `CASE foo WHEN 1 THEN TRUE WHEN 2 THEN FALSE ELSE NULL END`)

AssertExprStringer(t, &sqlparser.CaseExpr{
Blocks: []*sqlparser.CaseBlock{
Expand All @@ -409,8 +427,17 @@ func TestExprs_String(t *testing.T) {
}

func TestQualifiedRef_String(t *testing.T) {
AssertExprStringer(t, &sqlparser.QualifiedRef{Table: &sqlparser.Ident{Name: "tbl"}, Column: &sqlparser.Ident{Name: "col"}}, `"tbl"."col"`)
AssertExprStringer(t, &sqlparser.QualifiedRef{Table: &sqlparser.Ident{Name: "tbl"}, Star: true}, `"tbl".*`)
AssertExprStringer(t,
&sqlparser.QualifiedRef{
Table: &sqlparser.Ident{Name: "tbl"},
Column: &sqlparser.Ident{Name: "col"},
},
`tbl.col`,
)
AssertExprStringer(t,
&sqlparser.QualifiedRef{Table: &sqlparser.Ident{Name: "tbl"}, Star: true},
`tbl.*`,
)
}

func TestCall_String(t *testing.T) {
Expand Down
18 changes: 10 additions & 8 deletions lexer.go
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -37,8 +37,8 @@ func (l *Lexer) Lex() (pos Pos, token Token, lit string) {
return l.lexBlob()
} else if isAlpha(ch) || ch == '_' {
return l.lexUnquotedIdent(l.pos, "")
} else if ch == '"' {
return l.lexQuotedIdent()
} else if ch == '"' || ch == '`' {
return l.lexQuotedIdent(ch)
} else if ch == '\'' {
return l.lexString()
} else if ch == '?' || ch == ':' || ch == '@' || ch == '$' {
Expand Down Expand Up @@ -125,21 +125,23 @@ func (l *Lexer) lexUnquotedIdent(pos Pos, prefix string) (Pos, Token, string) {
return pos, tok, lit
}

func (l *Lexer) lexQuotedIdent() (Pos, Token, string) {
func (l *Lexer) lexQuotedIdent(char rune) (Pos, Token, string) {
ch, pos := l.read()
assert(ch == '"')
assert(ch == char)

l.buf.Reset()
l.buf.WriteRune(char)
for {
ch, _ := l.read()
if ch == -1 {
return pos, ILLEGAL, `"` + l.buf.String()
} else if ch == '"' {
if l.peek() == '"' { // escaped quote
return pos, ILLEGAL, l.buf.String()
} else if ch == char {
if l.peek() == char { // escaped quote
l.read()
l.buf.WriteRune('"')
l.buf.WriteRune(char)
continue
}
l.buf.WriteRune(char)
return pos, QIDENT, l.buf.String()
}
l.buf.WriteRune(ch)
Expand Down
5 changes: 4 additions & 1 deletion lexer_test.go
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -13,7 +13,10 @@ func TestLexer_Lex(t *testing.T) {
AssertLex(t, `foo_BAR123`, sqlparser.IDENT, `foo_BAR123`)
})
t.Run("Quoted", func(t *testing.T) {
AssertLex(t, `"crazy ~!#*&# column name"" foo"`, sqlparser.QIDENT, `crazy ~!#*&# column name" foo`)
AssertLex(t, `"crazy ~!#*&# column name"" foo"`, sqlparser.QIDENT, `"crazy ~!#*&# column name" foo"`)
})
t.Run("BackQuoted", func(t *testing.T) {
AssertLex(t, "`crazy ~!#*&# column name foo`", sqlparser.QIDENT, "`crazy ~!#*&# column name foo`")
})
t.Run("NoEndQuote", func(t *testing.T) {
AssertLex(t, `"unfinished`, sqlparser.ILLEGAL, `"unfinished`)
Expand Down
Loading