xsqlparser

package module

v1.0.0 Latest Latest Go to latest Published: May 23, 2022 License: Apache-2.0 Imports: 13 Imported by: 3

Details

Valid go.mod file

The Go module system was introduced in Go 1.11 and is the official dependency management solution for Go.
Redistributable license

Redistributable licenses place minimal restrictions on how software can be used, modified, and redistributed.
Tagged version

Modules with tagged versions give importers more predictable builds.
Stable version

When a project reaches major version v1 it is considered stable.
Learn more about best practices

Repository

github.com/akito0107/xsqlparser

Links

Open Source Insights

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/akito0107/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/akito0107/xsqlparser"
	"github.com/akito0107/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/akito0107/xsqlparser"
	"github.com/akito0107/xsqlparser/sqlast"
	"github.com/akito0107/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/akito0107/xsqlparser"
	"github.com/akito0107/xsqlparser/sqlast"
	"github.com/akito0107/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

Documentation ¶

Index ¶

Variables
func CompareWithoutMarker(a, b interface{}) string
type Parser
- func NewParser(src io.Reader, dialect dialect.Dialect, opts ...ParserOption) (*Parser, error)
- func NewParserWithOptions(opts ...ParserOption) *Parser
type ParserOption
- func ParseComment() ParserOption

Constants ¶

This section is empty.

Variables ¶

View Source

var EOF = errors.New("tokens are already consumed")

View Source

var IgnoreMarker = cmp.FilterPath(func(paths cmp.Path) bool {
	s := paths.Last().Type()
	name := s.Name()
	r := []rune(name)
	return s.Kind() == reflect.Struct && len(r) > 0 && unicode.IsLower(r[0])
}, cmp.Ignore())

Functions ¶

func CompareWithoutMarker ¶

func CompareWithoutMarker(a, b interface{}) string

Types ¶

type Parser ¶

type Parser struct {
	// contains filtered or unexported fields
}

func NewParser ¶

func NewParser(src io.Reader, dialect dialect.Dialect, opts ...ParserOption) (*Parser, error)

func NewParserWithOptions ¶

func NewParserWithOptions(opts ...ParserOption) *Parser

func (*Parser) Debug ¶

func (p *Parser) Debug()

func (*Parser) ParseDataType ¶

func (p *Parser) ParseDataType() (sqlast.Type, error)

func (*Parser) ParseExpr ¶

func (p *Parser) ParseExpr() (sqlast.Node, error)

func (*Parser) ParseFile ¶

func (p *Parser) ParseFile() (*sqlast.File, error)

func (*Parser) ParseSQL ¶

func (p *Parser) ParseSQL() ([]sqlast.Stmt, error)

func (*Parser) ParseStatement ¶

func (p *Parser) ParseStatement() (sqlast.Stmt, error)

func (*Parser) SetTokens ¶

func (p *Parser) SetTokens(tokens []*sqltoken.Token)

workaround FIXME: create appropriate parse function

type ParserOption ¶

type ParserOption func(*Parser)

func ParseComment ¶

func ParseComment() ParserOption

Source Files ¶

View all Source files

Directories ¶

Path	Synopsis
cmd
astprinter
dialect
example
sqlast Package sqlast declares types that used to represent abstract syntax tree of sql.	Package sqlast declares types that used to represent abstract syntax tree of sql.
sqlastutil
sqltoken
tools
genmark

?	: This menu
/	: Search site
f or F	: Jump to
y or Y	: Canonical URL