# README
xsqlparser
sql parser for golang.
This repo is ported of sqlparser-rs in Go.
Getting Started
Prerequisites
- Go 1.16+
Installing
$ go get -u github.com/moomou/xsqlparser/...
How to use
Parser
Currently supports SELECT,CREATE TABLE, DROP TABLE, CREATE VIEW,INSERT,UPDATE,DELETE, ALTER TABLE, CREATE INDEX, DROP INDEX, EXPLAIN.
- simple case
package main
import (
"bytes"
"log"
"github.com/k0kubun/pp"
"github.com/moomou/xsqlparser"
"github.com/moomou/xsqlparser/dialect"
)
...
str := "SELECT * from test_table"
parser, err := xsqlparser.NewParser(bytes.NewBufferString(str), &dialect.GenericSQLDialect{})
if err != nil {
log.Fatal(err)
}
stmt, err := parser.ParseStatement()
if err != nil {
log.Fatal(err)
}
pp.Println(stmt)
got:
&sqlast.Query{
stmt: sqlast.stmt{},
CTEs: []*sqlast.CTE{},
Body: &sqlast.SQLSelect{
sqlSetExpr: sqlast.sqlSetExpr{},
Distinct: false,
Projection: []sqlast.SQLSelectItem{
&sqlast.UnnamedSelectItem{
sqlSelectItem: sqlast.sqlSelectItem{},
Node: &sqlast.Wildcard{},
},
},
FromClause: []sqlast.TableReference{
&sqlast.Table{
tableFactor: sqlast.tableFactor{},
tableReference: sqlast.tableReference{},
Name: &sqlast.ObjectName{
Idents: []*sqlast.Ident{
&"test_table",
},
},
Alias: (*sqlast.Ident)(nil),
Args: []sqlast.Node{},
WithHints: []sqlast.Node{},
},
},
WhereClause: nil,
GroupByClause: []sqlast.Node{},
HavingClause: nil,
},
OrderBy: []*sqlast.OrderByExpr{},
Limit: (*sqlast.LimitExpr)(nil),
}
You can also create sql from ast via ToSQLString().
log.Println(stmt.ToSQLString())
got:
2019/05/07 11:59:36 SELECT * FROM test_table
- complicated select
str := "SELECT orders.product, SUM(orders.quantity) AS product_units, accounts.* " +
"FROM orders LEFT JOIN accounts ON orders.account_id = accounts.id " +
"WHERE orders.region IN (SELECT region FROM top_regions) " +
"ORDER BY product_units LIMIT 100"
parser, err := xsqlparser.NewParser(bytes.NewBufferString(str), &dialect.GenericSQLDialect{})
if err != nil {
log.Fatal(err)
}
stmt, err := parser.ParseStatement()
if err != nil {
log.Fatal(err)
}
pp.Println(stmt)
got:
&sqlast.Query{
stmt: sqlast.stmt{},
CTEs: []*sqlast.CTE{},
Body: &sqlast.SQLSelect{
sqlSetExpr: sqlast.sqlSetExpr{},
Distinct: false,
Projection: []sqlast.SQLSelectItem{
&sqlast.UnnamedSelectItem{
sqlSelectItem: sqlast.sqlSelectItem{},
Node: &sqlast.CompoundIdent{
Idents: []*sqlast.Ident{
&"orders",
&"product",
},
},
},
&sqlast.AliasSelectItem{
sqlSelectItem: sqlast.sqlSelectItem{},
Expr: &sqlast.Function{
Name: &sqlast.ObjectName{
Idents: []*sqlast.Ident{
&"SUM",
},
},
Args: []sqlast.Node{
&sqlast.CompoundIdent{
Idents: []*sqlast.Ident{
&"orders",
&"quantity",
},
},
},
Over: (*sqlast.WindowSpec)(nil),
},
Alias: &"product_units",
},
&sqlast.QualifiedWildcardSelectItem{
sqlSelectItem: sqlast.sqlSelectItem{},
Prefix: &sqlast.ObjectName{
Idents: []*sqlast.Ident{
&"accounts",
},
},
},
},
FromClause: []sqlast.TableReference{
&sqlast.QualifiedJoin{
tableReference: sqlast.tableReference{},
LeftElement: &sqlast.TableJoinElement{
joinElement: sqlast.joinElement{},
Ref: &sqlast.Table{
tableFactor: sqlast.tableFactor{},
tableReference: sqlast.tableReference{},
Name: &sqlast.ObjectName{
Idents: []*sqlast.Ident{
&"orders",
},
},
Alias: (*sqlast.Ident)(nil),
Args: []sqlast.Node{},
WithHints: []sqlast.Node{},
},
},
Type: 1,
RightElement: &sqlast.TableJoinElement{
joinElement: sqlast.joinElement{},
Ref: &sqlast.Table{
tableFactor: sqlast.tableFactor{},
tableReference: sqlast.tableReference{},
Name: &sqlast.ObjectName{
Idents: []*sqlast.Ident{
&"accounts",
},
},
Alias: (*sqlast.Ident)(nil),
Args: []sqlast.Node{},
WithHints: []sqlast.Node{},
},
},
Spec: &sqlast.JoinCondition{
joinSpec: sqlast.joinSpec{},
SearchCondition: &sqlast.BinaryExpr{
Left: &sqlast.CompoundIdent{
Idents: []*sqlast.Ident{
&"orders",
&"account_id",
},
},
Op: 9,
Right: &sqlast.CompoundIdent{
Idents: []*sqlast.Ident{
&"accounts",
&"id",
},
},
},
},
},
},
WhereClause: &sqlast.InSubQuery{
Expr: &sqlast.CompoundIdent{
Idents: []*sqlast.Ident{
&"orders",
&"region",
},
},
SubQuery: &sqlast.Query{
stmt: sqlast.stmt{},
CTEs: []*sqlast.CTE{},
Body: &sqlast.SQLSelect{
sqlSetExpr: sqlast.sqlSetExpr{},
Distinct: false,
Projection: []sqlast.SQLSelectItem{
&sqlast.UnnamedSelectItem{
sqlSelectItem: sqlast.sqlSelectItem{},
Node: &"region",
},
},
FromClause: []sqlast.TableReference{
&sqlast.Table{
tableFactor: sqlast.tableFactor{},
tableReference: sqlast.tableReference{},
Name: &sqlast.ObjectName{
Idents: []*sqlast.Ident{
&"top_regions",
},
},
Alias: (*sqlast.Ident)(nil),
Args: []sqlast.Node{},
WithHints: []sqlast.Node{},
},
},
WhereClause: nil,
GroupByClause: []sqlast.Node{},
HavingClause: nil,
},
OrderBy: []*sqlast.OrderByExpr{},
Limit: (*sqlast.LimitExpr)(nil),
},
Negated: false,
},
GroupByClause: []sqlast.Node{},
HavingClause: nil,
},
OrderBy: []*sqlast.OrderByExpr{
&sqlast.OrderByExpr{
Expr: &"product_units",
ASC: (*bool)(nil),
},
},
Limit: &sqlast.LimitExpr{
All: false,
LimitValue: &100,
OffsetValue: (*sqlast.LongValue)(nil),
},
}
- with CTE
str := "WITH regional_sales AS (" +
"SELECT region, SUM(amount) AS total_sales " +
"FROM orders GROUP BY region) " +
"SELECT product, SUM(quantity) AS product_units " +
"FROM orders " +
"WHERE region IN (SELECT region FROM top_regions) " +
"GROUP BY region, product"
parser, err := xsqlparser.NewParser(bytes.NewBufferString(str), &dialect.GenericSQLDialect{})
if err != nil {
log.Fatal(err)
}
stmt, err := parser.ParseStatement()
if err != nil {
log.Fatal(err)
}
pp.Println(stmt)
got:
&sqlast.Query{
stmt: sqlast.stmt{},
CTEs: []*sqlast.CTE{
&sqlast.CTE{
Alias: &"regional_sales",
Query: &sqlast.Query{
stmt: sqlast.stmt{},
CTEs: []*sqlast.CTE{},
Body: &sqlast.SQLSelect{
sqlSetExpr: sqlast.sqlSetExpr{},
Distinct: false,
Projection: []sqlast.SQLSelectItem{
&sqlast.UnnamedSelectItem{
sqlSelectItem: sqlast.sqlSelectItem{},
Node: &"region",
},
&sqlast.AliasSelectItem{
sqlSelectItem: sqlast.sqlSelectItem{},
Expr: &sqlast.Function{
Name: &sqlast.ObjectName{
Idents: []*sqlast.Ident{
&"SUM",
},
},
Args: []sqlast.Node{
&"amount",
},
Over: (*sqlast.WindowSpec)(nil),
},
Alias: &"total_sales",
},
},
FromClause: []sqlast.TableReference{
&sqlast.Table{
tableFactor: sqlast.tableFactor{},
tableReference: sqlast.tableReference{},
Name: &sqlast.ObjectName{
Idents: []*sqlast.Ident{
&"orders",
},
},
Alias: (*sqlast.Ident)(nil),
Args: []sqlast.Node{},
WithHints: []sqlast.Node{},
},
},
WhereClause: nil,
GroupByClause: []sqlast.Node{
&"region",
},
HavingClause: nil,
},
OrderBy: []*sqlast.OrderByExpr{},
Limit: (*sqlast.LimitExpr)(nil),
},
},
},
Body: &sqlast.SQLSelect{
sqlSetExpr: sqlast.sqlSetExpr{},
Distinct: false,
Projection: []sqlast.SQLSelectItem{
&sqlast.UnnamedSelectItem{
sqlSelectItem: sqlast.sqlSelectItem{},
Node: &"product",
},
&sqlast.AliasSelectItem{
sqlSelectItem: sqlast.sqlSelectItem{},
Expr: &sqlast.Function{
Name: &sqlast.ObjectName{
Idents: []*sqlast.Ident{
&"SUM",
},
},
Args: []sqlast.Node{
&"quantity",
},
Over: (*sqlast.WindowSpec)(nil),
},
Alias: &"product_units",
},
},
FromClause: []sqlast.TableReference{
&sqlast.Table{
tableFactor: sqlast.tableFactor{},
tableReference: sqlast.tableReference{},
Name: &sqlast.ObjectName{
Idents: []*sqlast.Ident{
&"orders",
},
},
Alias: (*sqlast.Ident)(nil),
Args: []sqlast.Node{},
WithHints: []sqlast.Node{},
},
},
WhereClause: &sqlast.InSubQuery{
Expr: &"region",
SubQuery: &sqlast.Query{
stmt: sqlast.stmt{},
CTEs: []*sqlast.CTE{},
Body: &sqlast.SQLSelect{
sqlSetExpr: sqlast.sqlSetExpr{},
Distinct: false,
Projection: []sqlast.SQLSelectItem{
&sqlast.UnnamedSelectItem{
sqlSelectItem: sqlast.sqlSelectItem{},
Node: &"region",
},
},
FromClause: []sqlast.TableReference{
&sqlast.Table{
tableFactor: sqlast.tableFactor{},
tableReference: sqlast.tableReference{},
Name: &sqlast.ObjectName{
Idents: []*sqlast.Ident{
&"top_regions",
},
},
Alias: (*sqlast.Ident)(nil),
Args: []sqlast.Node{},
WithHints: []sqlast.Node{},
},
},
WhereClause: nil,
GroupByClause: []sqlast.Node{},
HavingClause: nil,
},
OrderBy: []*sqlast.OrderByExpr{},
Limit: (*sqlast.LimitExpr)(nil),
},
Negated: false,
},
GroupByClause: []sqlast.Node{
&"region",
&"product",
},
HavingClause: nil,
},
OrderBy: []*sqlast.OrderByExpr{},
Limit: (*sqlast.LimitExpr)(nil),
}
Visitor(s)
- Using
Inspect
create AST List
package main
import (
"bytes"
"log"
"github.com/k0kubun/pp"
"github.com/moomou/xsqlparser"
"github.com/moomou/xsqlparser/sqlast"
"github.com/moomou/xsqlparser/dialect"
)
func main() {
src := `WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders GROUP BY region)
SELECT product, SUM(quantity) AS product_units
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;`
parser, err := xsqlparser.NewParser(bytes.NewBufferString(src), &dialect.GenericSQLDialect{})
if err != nil {
log.Fatal(err)
}
stmt, err := parser.ParseStatement()
if err != nil {
log.Fatal(err)
}
var list []sqlast.Node
sqlast.Inspect(stmt, func(node sqlast.Node) bool {
switch node.(type) {
case nil:
return false
default:
list = append(list, node)
return true
}
})
pp.Println(list)
}
also available Walk().
CommentMap
Experimental Feature
package main
import (
"bytes"
"log"
"github.com/k0kubun/pp"
"github.com/moomou/xsqlparser"
"github.com/moomou/xsqlparser/sqlast"
"github.com/moomou/xsqlparser/dialect"
)
func main() {
src := `
/*associate with stmts1*/
CREATE TABLE test (
/*associate with columndef*/
col0 int primary key, --columndef
/*with constraints*/
col1 integer constraint test_constraint check (10 < col1 and col1 < 100),
foreign key (col0, col1) references test2(col1, col2), --table constraints1
--table constraints2
CONSTRAINT test_constraint check(col1 > 10)
); --associate with stmts2
`
parser, err := xsqlparser.NewParser(bytes.NewBufferString(src), &dialect.GenericSQLDialect{}, xsqlparser.ParseComment)
if err != nil {
log.Fatal(err)
}
file, err := parser.ParseFile()
if err != nil {
log.Fatal(err)
}
m := sqlast.NewCommentMap(file)
createTable := file.Stmts[0].(*sqlast.CreateTableStmt)
pp.Println(m[createTable.Elements[0]]) // you can show `associate with columndef` and `columndef` comments
}
License
This project is licensed under the Apache License 2.0 License - see the LICENSE file for details
# Packages
No description provided by the author
No description provided by the author
No description provided by the author
No description provided by the author
Package sqlast declares types that used to represent abstract syntax tree of sql.
No description provided by the author
No description provided by the author
No description provided by the author
# Functions
No description provided by the author
No description provided by the author
No description provided by the author
No description provided by the author
# Type aliases
No description provided by the author