1

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?

Community
  • 1
  • 1
Hassnain Alvi
  • 87
  • 2
  • 12
  • 1
    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 Jan 14 '19 at 10:11
  • @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 Jan 14 '19 at 11:33

3 Answers3

3

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.

rob74
  • 4,939
  • 29
  • 31
  • Thanks @rob74 i am able to get the query table name with your above insert statement using > stmt.Table.Name But this somehow does not work with select statement because the structure is little complex with select and pointers addresses were returned when we print it. Can you please tell me how to do it with the select statement? – Hassnain Alvi Jan 15 '19 at 13:00
  • 1
    That's what I meant by using reflection - the `TableName` properties may occur in several places, so you can use reflection to (recursively) enumerate all fields of structs, elements of arrays etc. looking for values of type "TableName" and collect all table names. Here's an example: https://play.golang.org/p/B31wr2w1AL8 – rob74 Jan 16 '19 at 14:43
  • Hi @rob74 your code snippet is awesome but also prints aliases of tables. Do you have an idea how I can adapt it to exclude them? I am new to golang so am missing knowledge to understand the datastructures. – kadir Mar 25 '19 at 00:22
2

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
}
kadir
  • 1,417
  • 11
  • 35
  • This works fine but the problem is it's handling qualifier as a table. E.g. if you have `select * from db.table` it returns [db table] as a result which is wrong. – Sanan Guliyev Feb 03 '21 at 23:52
0

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]
iman tung
  • 63
  • 2