Get table name from SQL query

0
votes

I am using golang SQL parser to fetch query related information from actual SQL query string. I am able to find the type of query using following code:

queryType := sqlparser.StmtType(sqlparser.Preview(sql))
fmt.Println(queryType)

but i am not sure how to get actual table names from sql query. The documentation is not clear as well. Only information i get from parse function is a statement

Can someone guide me how can get this information using golang sqlparser?

3
sqlgosql-parser
What do you mean by 'the actual table names'? A SQL String could contain an infinite number of tables involved in SELECT, joins, subqueries, unions, etc.etc. do you just want a list of any table involved in the query? Do you have example input and output? - JeffUK
@JeffUK Yeah actually i just want to get FROM part of tables for now but if there is some functionality inside the parser API that can give me complete list of tables affected by query, that will be awesome - Hassnain Alvi

3 Answers

2
votes

To get all the table names, you will have to pull them out from the Statement returned by Parse, possibly using reflection. If you run the following code: 

stmt, _ := sqlparser.Parse("insert into my_table set my_column=1")
fmt.Printf("%#v\n", stmt)

you get the output (indented for readability):

&sqlparser.Insert{
    Action:"insert", 
    Comments:sqlparser.Comments(nil), 
    Ignore:"", 
    Table:sqlparser.TableName{
        Name:sqlparser.TableIdent{v:"my_table"}, 
        Qualifier:sqlparser.TableIdent{v:""}
    }, 
    Partitions:sqlparser.Partitions(nil), 
    Columns:sqlparser.Columns{sqlparser.ColIdent{_:[0]struct { _ []uint8 }{}, val:"my_column", lowered:""}}, 
    Rows:sqlparser.Values{sqlparser.ValTuple{(*sqlparser.SQLVal)(0xc00000a0c0)}}, 
    OnDup:sqlparser.OnDup(nil)
}

as you can see, this contains a (sub)field of type TableIdent which contains the requested table from the statement.

1
votes

I took the snippet from the comments by @rob74 which was fantastic and modified it to only return table names. The original snippet also returned aliases of tables. E.g. 

select * from my_table as mt join other_table using(my_key)
original snippet returns: [my_table, mt, other_table]
new snippet returns:      [my_table, other_table]

original snippet by rob74: play.golang.org/p/B31wr2w1AL8

package main

import (
    "fmt"
    "github.com/xwb1989/sqlparser"
    "reflect"
)

func main() {
    stmt, _ := sqlparser.Parse("select * from my_table as mt join other_table using(my_key)")
    var tables []string
    tables = getTableNames(reflect.Indirect(reflect.ValueOf(stmt)), tables, 0, false)
    fmt.Printf("%s", tables)
}

func getTableNames(v reflect.Value, tables []string, level int, isTable bool) []string {
    switch v.Kind() {
    case reflect.Struct:
        if v.Type().Name() == "TableIdent" {
            // if this is a TableIdent struct, extract the table name
            tableName := v.FieldByName("v").String()
            if tableName != "" && isTable{
                tables = append(tables, tableName)
            }
        } else {
            // otherwise enumerate all fields of the struct and process further
            for i := 0; i < v.NumField(); i++ {
                tables = getTableNames(reflect.Indirect(v.Field(i)), tables, level+1, isTable)
            }
        }
    case reflect.Array, reflect.Slice:
        for i := 0; i < v.Len(); i++ {
            // enumerate all elements of an array/slice and process further
            tables = getTableNames(reflect.Indirect(v.Index(i)), tables, level+1, isTable)
        }
    case reflect.Interface:
        if v.Type().Name() == "SimpleTableExpr" {
            isTable = true
        }
        // get the actual object that satisfies an interface and process further
        tables = getTableNames(reflect.Indirect(reflect.ValueOf(v.Interface())), tables, level+1, isTable)
    }

    return tables
}
0
votes

I wrote some string manipulation library for SQL Query to get table names:

queryString := sqlstr.NewQueryString(`SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;`)

tableNames := queryString.TableNames()

fmt.Println(tableNames)

// Output:
// [table1 table2]