executor

README

About ddl-executor

The ddl-executor is a golang library that can parse and execute MySQL DDL statements. The library maintains schema structures in memory, for examples: creates a new schema structure when a CREATE statement executed, modifys a schema structure when a ALTER statement executed.

What can it be used for ?

This library may be used for DDL analysis, binlog stream's schema tracking (like binlog_row_metadata=FULL in MySQL 8) and so on.

Usage

Here is an example, execute "CREATE TABLE test1" and "ALTER TABLE test1 ADD COLUMN" statement, and finally print the schema info of test1:

executor := NewExecutor("utf8")
err := executor.Exec(`
    create database test;
    create table test.test1(
        id int unsigned auto_increment primary key,
        name varchar(255) CHARACTER SET utf8 not null default '' unique key
    ) CHARACTER SET gbk;
`)
require.Nil(t, err)
 
err = executor.Exec(`
    alter table test.test1
        add column addr varchar(255),
        add column phone int not null unique
`)
require.Nil(t, err)                                                                                                                        
 
tableDef, err := executor.GetTableDef("test", "test1")
require.Nil(t, err)
 
for _, columnDef := range tableDef.Columns {
    fmt.Printf("%s.%s %s %s %s %s\n", 
        tableDef.Name, columnDef.Name, columnDef.Type, columnDef.Key, columnDef.Charset, columnDef.Nullable)
}

Internals

This library use TiDB 's parser to parse MySQL statement to generate AST(abstract syntax tree). Base on different AST result of different DDL, ddl-executor executes particular logics (like MySQL's DDL logics) to maintain schema structures in memory. For different DDL statements:

• CREATE DATABASE, DROP DATABASE
• CREATE SCHEMA, DROP SCHEMA
• CREATE INDEX, DROP INDEX
• CREATE TABLE, DROP TABLE
• ALTER TABLE
• RENAME TABLE
• ALTER DATABASE

What statements it supports ?

This library support 99% MySQL DDL statements.The ddl-executor can execute statements same as MySQL 5.7 identically, such as complicated statement like this:

# -----------------------------------------------
# It should be impossible to rename index that doesn't exists,
# dropped or added within the same ALTER TABLE.
#
alter table t1 rename key d to e;
alter table t1 drop key c, rename key c to d;
alter table t1 add key d(j), rename key d to e;
 
# -----------------------------------------------
# It should be impossible to rename index to a name
# which is already used by another index, or is used
# by index which is added within the same ALTER TABLE.
#
alter table t1 add key d(j);
alter table t1 rename key c to d;
alter table t1 drop key d;
alter table t1 add key d(j), rename key c to d;
 
# -----------------------------------------------
#
# Rename key is handled before add key, so, it would be error because 'key f not exsits'
alter table t1 add key d(j), add unique key e(i), rename key c to d , rename key f to d;
 
# -----------------------------------------------
# It should be possible to rename index to a name which
# belongs to index which is dropped within the  same ALTER TABLE.
#
alter table t1 add key d(j);
alter table t1 drop key c, rename key d to c;
drop table t1;

Those statements above come from MySQL' s test suit, and is part of our compatibility test cases.

What statements it doesn't support ?

Some DDL statement that are infrequent:

• ALTER with 'convert charset': ALTER TABLE t1 CONVERT TO CHARACTER SET latin1;
• ALTER with 'order by': ALTER TABLE t1 add column new_col int, ORDER BY payoutid, bandid;
• DDL with geo types: ALTER TABLE t1 ADD b GEOMETRY, ADD c POINT, ADD SPATIAL INDEX(b);
• CREATE TABLE with 'SELECT' clause;
• Some others unfrequent statement we don't know now;

Those statements above will raise error when executing whit this library.

Compatibility tests

You can have a look on 'github.com/bytewatch/ddl-executor/compatibility_test', which is a cmd line tool to test compatibility between this library and MySQL. Type command like this, will execute hundreds of DDL statements in file ddl_cases.sql using this library and MySQL. The command will print a diff between output of this library and MySQL's, tells what is not compatible.

go build
./test.sh ddl_cases.sql latin1 172.17.0.2 3306 root passwd123456

Of course, replace MySQL connect info by yourself, and replace 'latin1' with your MySQL's charset_server.

License

Apache License 2.0

Documentation

Index

• Constants
• Variables
• type ColumnDef
  • func (o *ColumnDef) Clone() *ColumnDef
• type Config
  • func NewDefaultConfig() *Config
• type DatabaseDef
• type Error
  • func NewError(code int, message string) *Error
  • func (o *Error) Code() int
  • func (o *Error) Error() string
  • func (o *Error) Gen(args ...interface{}) *Error
• type Executor
  • func NewExecutor(cfg *Config) *Executor
  • func (o *Executor) Enter(in ast.Node) (out ast.Node, skipChildren bool)
  • func (o *Executor) Exec(sql string) error
  • func (o *Executor) GetCurrentDatabase() string
  • func (o *Executor) GetDatabases() []string
  • func (o *Executor) GetTableDef(databaseName, tableName string) (*TableDef, error)
  • func (o *Executor) GetTables(databaseName string) ([]string, error)
  • func (o *Executor) IsDdl(sql string) (bool, error)
  • func (o *Executor) Leave(in ast.Node) (out ast.Node, ok bool)
  • func (o *Executor) Reset()
  • func (o *Executor) Restore(data []byte) error
  • func (o *Executor) Snapshot() ([]byte, error)
• type IndexDef
  • func (o *IndexDef) Clone() *IndexDef
• type IndexType
• type Indices
  • func (o Indices) Len() int
  • func (o Indices) Less(i, j int) bool
  • func (o Indices) Swap(i, j int)
• type TableDef
  • func (o *TableDef) Clone() *TableDef

Constants

const (
	IndexType_NONE IndexType = ""
	IndexType_PRI            = "PRI"
	IndexType_UNI            = "UNI"
	IndexType_MUL            = "MUL"
)

const (
	// Mark whether an index is generated implicitly
	IndexFlag_Generated uint = 1 << iota
	IndexFlag_FullText
	IndexFlag_HasNullPart
)

const (
	TypeDecimal   byte = 0
	TypeTiny      byte = 1
	TypeShort     byte = 2
	TypeLong      byte = 3
	TypeFloat     byte = 4
	TypeDouble    byte = 5
	TypeNull      byte = 6
	TypeTimestamp byte = 7
	TypeLonglong  byte = 8
	TypeInt24     byte = 9
	TypeDate      byte = 10
	/* Original name was TypeTime, renamed to Duration to resolve the conflict with Go type Time.*/
	TypeDuration byte = 11
	TypeDatetime byte = 12
	TypeYear     byte = 13
	TypeNewDate  byte = 14
	TypeVarchar  byte = 15
	TypeBit      byte = 16

	TypeJSON       byte = 0xf5
	TypeNewDecimal byte = 0xf6
	TypeEnum       byte = 0xf7
	TypeSet        byte = 0xf8
	TypeTinyBlob   byte = 0xf9
	TypeMediumBlob byte = 0xfa
	TypeLongBlob   byte = 0xfb
	TypeBlob       byte = 0xfc
	TypeVarString  byte = 0xfd
	TypeString     byte = 0xfe
	TypeGeometry   byte = 0xff
)

MySQL type information.

Variables

var (
	ErrParse = NewError(mysql.ErrParse, "")
	// ErrNoDB return for no database selected
	ErrNoDB = NewError(mysql.ErrNoDB, "No database selected")
	// ErrDBDropExists returns for dropping a non-existent database.
	ErrDBDropExists = NewError(mysql.ErrDBDropExists, "Can't drop database '%s'; database doesn't exist")
	// ErrBadDB returns for database not exists.
	ErrBadDB = NewError(mysql.ErrBadDB, "Unknown database '%s'")
	// ErrNoSuchTable returns for table not exists.
	ErrNoSuchTable = NewError(mysql.ErrNoSuchTable, "Table '%s.%s' doesn't exist")
	// ErrErrBadField returns for column not exists.
	ErrErrBadField = NewError(mysql.ErrBadField, "Unknown column '%s' in '%s'")
	// ErrErrWrongFkDef returns for foreign key not match.
	ErrErrWrongFkDef = NewError(mysql.ErrWrongFkDef, "Incorrect foreign key definition for '%s': Key reference and table reference don't match")
	// ErrCannotAddForeign returns for foreign key exists.
	ErrCannotAddForeign = NewError(mysql.ErrCannotAddForeign, "Cannot add foreign key constraint")
	// ErrCantDropFieldOrKey returns for foreign key not exists.
	ErrCantDropFieldOrKey = NewError(mysql.ErrCantDropFieldOrKey, "Can't DROP '%s'; check that column/key exists")
	// ErrErrDBCreateExists returns for database already exists.
	ErrErrDBCreateExists = NewError(mysql.ErrDBCreateExists, "Can't create database '%s'; database exists")
	// ErrTableExists returns for table already exists.
	ErrTableExists = NewError(mysql.ErrTableExists, "Table '%s' already exists")
	// ErrBadTable returns for dropping a non-existent table.
	ErrBadTable = NewError(mysql.ErrBadTable, "Unknown table '%s.%s'")
	// ErrDupFieldName returns for column already exists.
	ErrDupFieldName = NewError(mysql.ErrDupFieldName, "Duplicate column name '%s'")
	// ErrDupIndex returns for index already exists.
	ErrDupIndex = NewError(mysql.ErrDupIndex, "Duplicate Index")
	// ErrDupKeyName returns for index duplicate when rename index.
	ErrDupKeyName = NewError(mysql.ErrDupKeyName, "Duplicate key name '%s'")
	// ErrKeyDoesNotExist returns for index not exists.
	ErrKeyDoesNotExist = NewError(mysql.ErrKeyDoesNotExist, "Key '%s' doesn't exist in table '%s'")
	// ErrMultiplePriKey returns for multiple primary keys.
	ErrMultiplePriKey = NewError(mysql.ErrMultiplePriKey, "Multiple primary key defined")
	// ErrTooManyKeyParts returns for too many key parts.
	ErrTooManyKeyParts = NewError(mysql.ErrTooManyKeyParts, "Too many key parts specified; max %d parts allowed")
)

Types

type ColumnDef

type ColumnDef struct {
	// original case
	Name      string    `json:"name"`
	Type      string    `json:"type"`
	InnerType byte      `json:"inner_type"`
	Key       IndexType `json:"key"`
	Charset   string    `json:"charset"`
	Unsigned  bool      `json:"unsigned"`
	Nullable  bool      `json:"nullable"`
}

func (*ColumnDef) Clone

func (o *ColumnDef) Clone() *ColumnDef

type Config

type Config struct {
	// This server's default charset, like mysql's charset_server
	CharsetServer string

	LowerCaseTableNames bool

	// If needAtomic is true, Exec() will have no effects if error happens
	NeedAtomic bool
}

func NewDefaultConfig

func NewDefaultConfig() *Config

type DatabaseDef

type DatabaseDef struct {
	Name    string               `json:"name"`
	Tables  map[string]*TableDef `json:tables`
	Charset string               `json:"charset"`
}

type Error

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

func NewError

func NewError(code int, message string) *Error

func (*Error) Code

func (o *Error) Code() int

func (*Error) Error

func (o *Error) Error() string

func (*Error) Gen

func (o *Error) Gen(args ...interface{}) *Error

type Executor

type Executor struct {
	sync.Mutex
	// contains filtered or unexported fields
}

func NewExecutor

func NewExecutor(cfg *Config) *Executor

func (*Executor) Enter

func (o *Executor) Enter(in ast.Node) (out ast.Node, skipChildren bool)

func (*Executor) Exec

func (o *Executor) Exec(sql string) error

func (*Executor) GetCurrentDatabase

func (o *Executor) GetCurrentDatabase() string

func (*Executor) GetDatabases

func (o *Executor) GetDatabases() []string

Show all database names

func (*Executor) GetTableDef

func (o *Executor) GetTableDef(databaseName, tableName string) (*TableDef, error)

Get definition of specified table

func (*Executor) GetTables

func (o *Executor) GetTables(databaseName string) ([]string, error)

Show all table names in specified database

func (*Executor) IsDdl

func (o *Executor) IsDdl(sql string) (bool, error)

func (*Executor) Leave

func (o *Executor) Leave(in ast.Node) (out ast.Node, ok bool)

func (*Executor) Reset

func (o *Executor) Reset()

Reset everything under this Executor

func (*Executor) Restore

func (o *Executor) Restore(data []byte) error

Restore from snaphot

func (*Executor) Snapshot

func (o *Executor) Snapshot() ([]byte, error)

Take a snapshot of this Executor, returned bytes is json encoded

type IndexDef

type IndexDef struct {
	// original case
	Name    string    `json:"name"`
	Columns []string  `json:"columns"`
	Key     IndexType `json:"key"`
	Flag    uint      `json:"flag"`
}

func (*IndexDef) Clone

func (o *IndexDef) Clone() *IndexDef

type IndexType

type IndexType string

type Indices

type Indices []*IndexDef

func (Indices) Len

func (o Indices) Len() int

func (Indices) Less

func (o Indices) Less(i, j int) bool

return true if o[i] is before o[j]

func (Indices) Swap

func (o Indices) Swap(i, j int)

type TableDef

type TableDef struct {
	Database string       `json:"database"`
	Name     string       `json:"name"`
	Columns  []*ColumnDef `json:"columns"`
	Indices  []*IndexDef  `json:"indices"`
	Charset  string       `json:"charset"`
}

func (*TableDef) Clone

func (o *TableDef) Clone() *TableDef