Create Table Statement to NewTable format?

[mgale]

Looking through the examples I found the memory database helpful and am able to get an instance of go-mysql-server up and running. I am able to populate databases and table names from my custom source but am running into an issue with the table schema.

In the examples I see the follow which works:
table := NewTable(tableName, sql.Schema{
{Name: "name", Type: sql.Text, Nullable: false, Source: tableName},
{Name: "email", Type: sql.Text, Nullable: false, Source: tableName},
{Name: "phone_numbers", Type: sql.JSON, Nullable: false, Source: tableName},
{Name: "created_at", Type: sql.Timestamp, Nullable: false, Source: tableName},
})

But for my custom backend I actually have the table definitions already in SQL create table format:
CREATE TABLE fancy_table1 (
param_num smallint(6) NOT NULL DEFAULT '0',
name varchar(255) NOT NULL DEFAULT '',
units varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (param_num)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I can create the tables from the cli via:
mysql --host 127.0.0.1 -u root mydb < createtables.sql

However I am wondering if there is a way using the parser already build into go-mysql-server to convert the SQL query into something I can easily feed into NewTable?

[mgale]

I have found that the following seems to work:

func (d *Database) GetTableInsensitive(ctx *sql.Context, tblName string) (sql.Table, bool, error) {
        // Get the SQL create table statement I just happen to have handy
	sqlTblDef := getTableDefinition(d.name, tblName)

	if len(sqlTblDef) == 0 {
		return nil, false, nil
	}

	tblNode, err := sqleparse.Parse(ctx, sqlTblDef)

	if err != nil {
		fmt.Printf("Error parsing SQL: %v\n", err)
		return nil, false, nil
	}
	table := NewTable(tblName, tblNode.Schema())
	return table, ok, nil
}

Because of how my data is exported from the other systems I have the output of equivalent of show create table tablename handy in my data structures.

The above code allows me to just parse that and return the required data type.

[zachmu]

Hi @mgale,

There's no simple way to do what you want, but the parse.TableSpecToSchema method should get you most of the way there. It doesn't handle foreign keys or indexes (since those aren't part of the sql.Schema definition), but it should work otherwise. To use it, you need to use the sqlparser.parse() method on your CREATE TABLE statement, then pull out the TableSpec object from that. See convertCreateTable in parse/parse.go for an example of how to do this.

[mgale]

Thanks for the advice and feedback.