schemalex

README

schemalex

Generate the difference of two mysql schema

SYNOPSIS

This tool can be used to generate the difference, or more precisely, the statements required to migrate from/to, between two MySQL schema.

Suppose you have an existing SQL schema like the following:

CREATE TABLE hoge (
    id INTEGER NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

And you want "upgrade" your schema to the following:

CREATE TABLE hoge (
    id INTEGER NOT NULL AUTO_INCREMENT,
    c VARCHAR (20) NOT NULL DEFAULT "hoge",
    PRIMARY KEY (id)
);

CREATE TABLE fuga (
    id INTEGER NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

Using schemalex you can generate a set of commands to perform the migration:

schemalex old.sql new.sql

SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `fuga` (
`id` INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
);

ALTER TABLE `hoge` ADD COLUMN `c` VARCHAR (20) NOT NULL DEFAULT "hoge";

SET FOREIGN_KEY_CHECKS = 1;

COMMIT;

You can also use URI formatted strings as the sources to compare, which allow you to compare local files against online schema, a version committed to your git repository against another version, etc.

Please see the help command for a list

schemalex -version
schemalex [options...] before after

-v            Print out the version and exit
-o file	      Output the result to the specified file (default: stdout)
-t[=true]     Enable/Disable transaction in the output (default: true)

"before" and "after" may be a file path, or a URI.
Special URI schemes "mysql" and "local-git" are supported on top of
"file". If the special path "-" is used, it is treated as stdin

Examples:

* Compare local files
  schemalex /path/to/file /another/path/to/file
  schemalex file:///path/to/file /another/path/to/file

* Compare local file against online mysql schema
  schemalex /path/to/file "mysql://user:password@tcp(host:port)/dbname?option=value"

* Compare file in local git repository against local file
  schemalex local-git:///path/to/repo?file=foo.sql&commitish=deadbeaf /path/to/file

* Compare schema from stdin against local file
	.... | schemalex - /path/to/file

SYNOPSIS (Using the library)

Below is the equivalent of the previous SYNOPSIS.

package schemalex_test

import (
	"os"

	"github.com/cabify/schemalex/diff"
)

func Example() {
	const sql1 = `CREATE TABLE hoge (
    id INTEGER NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);`
	const sql2 = `CREATE TABLE hoge (
    id INTEGER NOT NULL AUTO_INCREMENT,
    c VARCHAR (20) NOT NULL DEFAULT "hoge",
    PRIMARY KEY (id)
);

CREATE TABLE fuga (
    id INTEGER NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);`

	diff.Strings(os.Stdout, sql1, sql2, diff.WithTransaction(true))

	// OUTPUT:
	// BEGIN;
	//
	// SET FOREIGN_KEY_CHECKS = 0;
	//
	// CREATE TABLE `fuga` (
	// `id` INTEGER NOT NULL AUTO_INCREMENT,
	// PRIMARY KEY (`id`)
	// );
	//
	// ALTER TABLE `hoge` ADD COLUMN `c` VARCHAR (20) NOT NULL DEFAULT "hoge";
	//
	// SET FOREIGN_KEY_CHECKS = 1;
	//
	// COMMIT;
}

SEE ALSO

• http://rspace.googlecode.com/hg/slide/lex.html#landing-slide
• http://blog.gopheracademy.com/advent-2014/parsers-lexers/
• https://github.com/soh335/git-schemalex

LICENSE

MIT

Documentation

Overview

Example

package main

import (
	"os"

	"github.com/cabify/schemalex/diff"
)

func main() {
	const sql1 = `CREATE TABLE hoge (
    id INTEGER NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);`
	const sql2 = `CREATE TABLE hoge (
    id INTEGER NOT NULL AUTO_INCREMENT,
    c VARCHAR (20) NOT NULL DEFAULT "hoge",
    PRIMARY KEY (id)
);

CREATE TABLE fuga (
    id INTEGER NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);`

	diff.Strings(os.Stdout, sql1, sql2, diff.WithTransaction(true))

}

Output:

BEGIN;

SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `fuga` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
);

ALTER TABLE `hoge` ADD COLUMN `c` VARCHAR (20) NOT NULL DEFAULT 'hoge' AFTER `id`;

SET FOREIGN_KEY_CHECKS = 1;

COMMIT;

Index

• Constants
• type Option
• type ParseError
• type Parser
  • func New() *Parser
  • func (p *Parser) Parse(src []byte) (model.Stmts, error)
  • func (p *Parser) ParseFile(fn string) (model.Stmts, error)
  • func (p *Parser) ParseString(src string) (model.Stmts, error)
• type SchemaSource
  • func NewLocalFileSource(s string) SchemaSource
  • func NewLocalGitSource(gitDir, file, commitish string) SchemaSource
  • func NewMySQLSource(s string) SchemaSource
  • func NewReaderSource(src io.Reader) SchemaSource
  • func NewSchemaSource(uri string) (SchemaSource, error)
• type Token
  • func NewToken(t TokenType, v string) *Token
• type TokenType
  • func (t TokenType) String() string

Examples

• Package

Constants

const Version = "v0.0.10"

Version contains the version number. Note that this does not necessarily reflect the current state of the source code.

Types

type Option

type Option interface {
	Name() string
	Value() interface{}
}

Option is a generic interface for objects that passes optional parameters to the various format functions in this package

type ParseError

type ParseError interface {
	error
	File() string
	Line() int
	Col() int
	Message() string
	EOF() bool
}

ParseError is returned from the various `Parse` methods when an invalid or unsupported SQL is found. When stringified, the result will look something like this:

   parse error: expected RPAREN at line 3 column 14
	      "CREATE TABLE foo " <---- AROUND HERE

type Parser

type Parser struct{}

Parser is responsible to parse a set of SQL statements

func New

func New() *Parser

New creates a new Parser

func (*Parser) Parse

func (p *Parser) Parse(src []byte) (model.Stmts, error)

Parse parses the given set of SQL statements and creates a model.Stmts structure. If it encounters errors while parsing, the returned error will be a ParseError type.

func (*Parser) ParseFile

func (p *Parser) ParseFile(fn string) (model.Stmts, error)

ParseFile parses a file containing SQL statements and creates a mode.Stmts structure. See Parse for details.

func (*Parser) ParseString

func (p *Parser) ParseString(src string) (model.Stmts, error)

ParseString parses a string containing SQL statements and creates a mode.Stmts structure. See Parse for details.

type SchemaSource

type SchemaSource interface {
	// WriteSchema is responsible for doing whatever necessary to retrieve
	// the database schema and write to the given io.Writer
	WriteSchema(io.Writer) error
}

SchemaSource is the interface used for objects that provide us with a MySQL database schema to work with.

func NewLocalFileSource

func NewLocalFileSource(s string) SchemaSource

NewLocalFileSource creates a SchemaSource whose contents are derived from the given local file

func NewLocalGitSource

func NewLocalGitSource(gitDir, file, commitish string) SchemaSource

NewLocalGitSource creates a SchemaSource whose contents are derived from the given file at the given commit ID in a git repository.

func NewMySQLSource

func NewMySQLSource(s string) SchemaSource

NewMySQLSource creates a SchemaSource whose contents are derived by accessing the specified MySQL instance.

MySQL sources respect extra parameters "ssl-ca", "ssl-cert", and "ssl-secret" (which all should point to local file names) when the "tls" parameter is set to some boolean true value. In this case, we register the given tls configuration using those values automatically.

Please note that the "tls" parameter MUST BE A BOOLEAN. Otherwise we expect that you have already registered your tls configuration manually, and that you gave us the name of that configuration

func NewReaderSource

func NewReaderSource(src io.Reader) SchemaSource

NewReaderSource creates a SchemaSource whose contents are read from the given io.Reader.

func NewSchemaSource

func NewSchemaSource(uri string) (SchemaSource, error)

NewSchemaSource creates a SchemaSource based on the given URI. Currently "-" (for stdin), "local-git://...", "mysql://...", and "file://..." are supported. A string that does not match any of the above patterns and has no scheme part is treated as a local file.

type Token

type Token struct {
	Type  TokenType
	Value string
	Pos   int
	Line  int
	Col   int
	EOF   bool
}

Token represents a token

func NewToken

func NewToken(t TokenType, v string) *Token

NewToken creates a new token of type `t`, with value `v`

type TokenType

type TokenType int

TokenType describes the possible types of tokens that schemalex understands

const (
	ILLEGAL TokenType = iota
	EOF
	SPACE
	IDENT
	BACKTICK_IDENT
	DOUBLE_QUOTE_IDENT
	SINGLE_QUOTE_IDENT
	NUMBER
	LPAREN        // (
	RPAREN        // )
	COMMA         // ,
	SEMICOLON     // ;
	DOT           // .
	SLASH         // /
	ASTERISK      // *
	DASH          // -
	PLUS          // +
	SINGLE_QUOTE  // '
	DOUBLE_QUOTE  // "
	EQUAL         // =
	COMMENT_IDENT // // /*   */, --, #
	ACTION
	AUTO_INCREMENT
	AVG_ROW_LENGTH
	BIGINT
	BINARY
	BIT
	BLOB
	BOOL
	BOOLEAN
	BTREE
	CASCADE
	CHAR
	CHARACTER
	CHARSET
	CHECK
	CHECKSUM
	COLLATE
	COMMENT
	COMPACT
	COMPRESSED
	CONNECTION
	CONSTRAINT
	CREATE
	CURRENT_TIMESTAMP
	DATA
	DATABASE
	DATE
	DATETIME
	DECIMAL
	DEFAULT
	DELAY_KEY_WRITE
	DELETE
	DIRECTORY
	DISK
	DOUBLE
	DROP
	DYNAMIC
	ENGINE
	ENUM
	EXISTS
	FALSE
	FIRST
	FIXED
	FLOAT
	FOREIGN
	FULL
	FULLTEXT
	HASH
	IF
	INDEX
	INSERT_METHOD
	INT
	INTEGER
	JSON
	KEY
	KEY_BLOCK_SIZE
	LAST
	LIKE
	LONGBLOB
	LONGTEXT
	MATCH
	MAX_ROWS
	MEDIUMBLOB
	MEDIUMINT
	MEDIUMTEXT
	MEMORY
	MIN_ROWS
	NO
	NOT
	NULL
	NUMERIC
	ON
	PACK_KEYS
	PARTIAL
	PASSWORD
	PRIMARY
	REAL
	REDUNDANT
	REFERENCES
	RESTRICT
	ROW_FORMAT
	SET
	SIMPLE
	SMALLINT
	SPATIAL
	STATS_AUTO_RECALC
	STATS_PERSISTENT
	STATS_SAMPLE_PAGES
	STORAGE
	TABLE
	TABLESPACE
	TEMPORARY
	TEXT
	TIME
	TIMESTAMP
	TINYBLOB
	TINYINT
	TINYTEXT
	TRUE
	UNION
	UNIQUE
	UNSIGNED
	UPDATE
	USE
	USING
	VARBINARY
	VARCHAR
	YEAR
	ZEROFILL
	ASC
	DESC
	NOW
)

List of possible tokens

func (TokenType) String

func (t TokenType) String() string