Extract table name from select statement with joins

[Hassnain-Alvi]

I am using sqlparse to get table names from queries. My queries are complex and contain joins as well. Fetching table name from an insert statement is easy but with select i am getting hard time Here is my code:

    stmt, err := sqlparser.Parse(sql)
    if err != nil {
       fmt.Println("Error: " + err.Error())
    }
    switch stmt := stmt.(type) {
    case *sqlparser.Select:
        var sel = stmt
        var tbl = sel.From[0]
        switch tblst := tbl.(type) {
        case *sqlparser.JoinTableExpr:
            var s = tblst
            var ss = s.RightExpr
            switch expr := ss.(type) {
            case *sqlparser.AliasedTableExpr:
                var cExpr = expr.Expr
                fmt.Println(reflect.TypeOf(cExpr))
                fmt.Printf("%#v\n", cExpr)
            }
        }
    case *sqlparser.Insert:
        var ins = stmt
        fmt.Println(ins.Table.Name)
    }

I am stuck after that because it returns a table indent and the fields are not exported Sorry, i am new to golang so not sure if its the right way to do this Can you please guide me to extract all tables from a complex select query with joins?

[fengbeihong]

buffer:= sqlparser.NewTrackedBuffer(nil) stmt.From.Format(buffer) fmt.Println(buffer)

[raintean]

@Hassnain-Alvi

stmt, err := sqlparser.Parse(sql)
if err != nil {
    fmt.Println("Error: " + err.Error())
}
tableNames := make([]string, 0)
_ = sqlparser.Walk(func(node sqlparser.SQLNode) (kontinue bool, err error) {
    switch node := node.(type) {
    case sqlparser.TableName:
        tableNames = append(tableNames, node.Name.CompliantName())
    }
    return true, nil
}, stmt)
fmt.Println(tableNames)

[prince612mittal]

hey @Hassnain-Alvi can u please help I am new to sqlParser and I am not able to know its usage

[Hassnain-Alvi]

hey @Hassnain-Alvi can u please help I am new to sqlParser and I am not able to know its usage

sure whats the problem you are facing ?

[prince612mittal]

hey @Hassnain-Alvi can u please help I am new to sqlParser and I am not able to know its usage

sure whats the problem you are facing ?

I have started today only and I am unable to get through its documentation for parsing table name from simple expression

[prince612mittal]

@Hassnain-Alvi I am not even able to use ins.Table.name reference your above mentioned code it shows error

[sananguliyev]

@Hassnain-Alvi

stmt, err := sqlparser.Parse(sql)
if err != nil {
    fmt.Println("Error: " + err.Error())
}
tableNames := make([]string, 0)
_ = sqlparser.Walk(func(node sqlparser.SQLNode) (kontinue bool, err error) {
    switch node := node.(type) {
    case sqlparser.TableName:
        tableNames = append(tableNames, node.Name.CompliantName())
    }
    return true, nil
}, stmt)
fmt.Println(tableNames)

@raintean thanks for the solution but it's not valid if query has alias of the join or subquery. E.g. The result of this query select * from db.my_table as mt, (select * from db2.users) as u join db3.other_table ot on ot.user_id = u.id is [my_table users other_table ot u] which is not correct.

[virgiliosanz]

@sananguliyev Have you found a solution for that?

[sananguliyev]

@virgiliosanz I have stopped working on that since more than a year. I remember I had workaround but do not remember what was that. In case I find I will let you know.

[sananguliyev]

@virgiliosanz I have found my workaround. Instead of using Walk method of the SQL parser, I switched to using the Go's reflect in a recursive function.

[virgiliosanz]

But how do you check it, because I cannot see the difference in type when it is an alias or a real table name.... Do you check the 2 or 3 previous nodes.or something liike that?

El mié, 14 ago 2024, 21:04, Sanan Guliyev @.***> escribió:

@virgiliosanz https://github.com/virgiliosanz I have found my workaround. I was using Go reflect instead of using Walk method of sql parser.

— Reply to this email directly, view it on GitHub https://github.com/xwb1989/sqlparser/issues/46#issuecomment-2289620249, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACBAVU2QENGBL2SAXI6OPLZROS4FAVCNFSM6AAAAABMP6YQE2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEOBZGYZDAMRUHE . You are receiving this because you were mentioned.Message ID: @.***>

[sananguliyev]

But how do you check it, because I cannot see the difference in type when it is an alias or a real table name.... Do you check the 2 or 3 previous nodes.or something liike that? El mié, 14 ago 2024, 21:04, Sanan Guliyev @.> escribió: … @virgiliosanz https://github.com/virgiliosanz I have found my workaround. I was using Go reflect instead of using Walk method of sql parser. — Reply to this email directly, view it on GitHub <#46 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACBAVU2QENGBL2SAXI6OPLZROS4FAVCNFSM6AAAAABMP6YQE2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEOBZGYZDAMRUHE . You are receiving this because you were mentioned.Message ID: @.>

@virgiliosanz yes, I was passing previous node type whether it's SimpleTableExpr if yes then next node is TableIdent I was labelling it as table name. This approach was covering all the use cases, simple, aliased and join queries.

[virgiliosanz]

Hi @sananguliyev, many thanks. I make it work. I past my code here just for future reference:

// getAffectedTables returns the affected tables for a given query.
func getSelectAffectedTables(stmt *sqlparser.Statement) ([]string, error) {
    affectedTables := make([]string, 0)
    var previous sqlparser.SQLNode

    walkLog := func(node sqlparser.SQLNode) (kontinue bool, err error) {
        switch n := node.(type) {
        case sqlparser.TableName:
            switch previous.(type) {
            case *sqlparser.AliasedTableExpr:
                table := normalizeTableName(n.Name.CompliantName())
                affectedTables = append(affectedTables, table)
                // fmt.Printf("%s: TableName: %s\n", queryType.String(), table)
            }
            // default:
            //  fmt.Printf("%s: <%T> [%v]\n", queryType.String(), n, n)
        }

        previous = node
        return true, nil
    }

    sqlparser.Walk(walkLog, *stmt)

    return uniqueNonEmptyElementsOf(affectedTables), nil
}

func getAffectedTables(stmt *sqlparser.Statement) ([]string, error) {
    affectedTables := make([]string, 0)

    walkLog := func(node sqlparser.SQLNode) (kontinue bool, err error) {
        switch n := node.(type) {
        case sqlparser.TableName:
            table := normalizeTableName(n.Name.CompliantName())
            // fmt.Printf("%s: TableName: %s\n", queryType.String(), table)
            affectedTables = append(affectedTables, table)
            // default:
            //  fmt.Printf("%s: <%T> [%v]\n", queryType.String(), n, n)
        }
        return true, nil
    }

    sqlparser.Walk(walkLog, *stmt)

    return uniqueNonEmptyElementsOf(affectedTables), nil
}

[sananguliyev]

Hi @sananguliyev, many thanks. I make it work. I past my code here just for future reference:

// getAffectedTables returns the affected tables for a given query.
func getSelectAffectedTables(stmt *sqlparser.Statement) ([]string, error) {
  affectedTables := make([]string, 0)
  var previous sqlparser.SQLNode

  walkLog := func(node sqlparser.SQLNode) (kontinue bool, err error) {
      switch n := node.(type) {
      case sqlparser.TableName:
          switch previous.(type) {
          case *sqlparser.AliasedTableExpr:
              table := normalizeTableName(n.Name.CompliantName())
              affectedTables = append(affectedTables, table)
              // fmt.Printf("%s: TableName: %s\n", queryType.String(), table)
          }
          // default:
          //  fmt.Printf("%s: <%T> [%v]\n", queryType.String(), n, n)
      }

      previous = node
      return true, nil
  }

  sqlparser.Walk(walkLog, *stmt)

  return uniqueNonEmptyElementsOf(affectedTables), nil
}

func getAffectedTables(stmt *sqlparser.Statement) ([]string, error) {
  affectedTables := make([]string, 0)

  walkLog := func(node sqlparser.SQLNode) (kontinue bool, err error) {
      switch n := node.(type) {
      case sqlparser.TableName:
          table := normalizeTableName(n.Name.CompliantName())
          // fmt.Printf("%s: TableName: %s\n", queryType.String(), table)
          affectedTables = append(affectedTables, table)
          // default:
          //  fmt.Printf("%s: <%T> [%v]\n", queryType.String(), n, n)
      }
      return true, nil
  }

  sqlparser.Walk(walkLog, *stmt)

  return uniqueNonEmptyElementsOf(affectedTables), nil
}

Thanks for sharing this. That would be the another way to keep the previous node in order to only extract table names, not aliases.