schemalex

README

schemalex

Generate difference sql of two mysql schema

SYNOPSIS (COMMAND LINE)

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;

SYNOPSIS (Using the library)

Below is the equivalent of the previous SYNOPSIS.

package schemalex_test

import (
	"os"

	"github.com/schemalex/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/schemalex/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` 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;

Index

• Constants
• 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 Token
  • func NewToken(t TokenType, v string) *Token
• type TokenType
  • func (t TokenType) String() string

Examples

• Package

Constants

const Version = "v0.0.4"

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

Types

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 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
	BTREE
	CASCADE
	CHAR
	CHARACTER
	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
	FIRST
	FIXED
	FLOAT
	FOREIGN
	FULL
	FULLTEXT
	HASH
	IF
	INDEX
	INSERT_METHOD
	INT
	INTEGER
	KEY
	KEY_BLOCK_SIZE
	LAST
	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
	UNION
	UNIQUE
	UNSIGNED
	UPDATE
	USE
	USING
	VARBINARY
	VARCHAR
	YEAR
	ZEROFILL
)

List of possible tokens

func (TokenType) String

func (t TokenType) String() string