square / mysql-parser

A ddl parser for mysql

MySQLParser

This is a library to parse SQL commands. The only commands that are currently supported are ddl statements, specifically CREATE TABLE, ALTER TABLE, DROP VIEW, and DROP TABLE.

Installation

In command line:

> gem install mysql-parser.x.x.x.gem

Usage

In ruby:

> require 'mysql-parser'
> MySQLParser.new.parse "ALTER TABLE `table` DROP INDEX abc, DROP INDEX def"
=> {:tree=><root: [<S: [" "]>, <r_commands: [<r_ALTER_TABLE: ["ALTER", <S: [" "]>, <r_ONLINE_OFFLINE: []>, <r_opt_IGNORE: []>, "TABLE", <S: [" "]>, <r_tbl_name: [<r_tbl_name_int: [<ident: ["`", <opt_ident_in_backtick: [<opt_ident_in_backtick: []>, "table"]>, "`", <S: [" "]>]>]>]>, <r_opt_alter_commands: [<r_comma_separated_alter_specification: [<r_comma_separated_alter_specification: [<r_alter_specification: ["DROP", <S: [" "]>, <r_INDEX_or_KEY: ["INDEX", <S: [" "]>]>, <r_index_name: [<ident: [<raw_ident: ["abc", <S: [" "]>]>]>]>]>]>, <comma: [",", <S: [" "]>]>, <r_alter_specification: ["DROP", <S: [" "]>, <r_INDEX_or_KEY: ["INDEX", <S: [" "]>]>, <r_index_name: [<ident: [<raw_ident: ["def", <S: [" "]>]>]>]>]>]>]>, <r_opt_after_alter: []>, <r_opt_PARTITION_options: []>]>]>]>, :state=>{}}

Files

mysql.rex.rb

This file is a lexer. It determines that DROP is a command, and that 'abcdef' is a string. Most of this file is auto-generated by bin/generate-literal which reads mysql.y.rb and generates the required literals automatically. Therefore this file will not normally need to be edited. The following reasons might be reason to edit it:

1. Creating a synonym
2. Creating a long literal[1]
3. Creating a literal which doesn't exist in the parser already

[1] a long literal is a literal which is needed for a special purpose, for example, because it consists of spaces and needs a synonym to be assigned to it. Normally, however, we do not need them.

Convention

1. S_... means some symbol
2. A_... means some state
3. L_... means long literal
4. Everything else is literal

mysql.y.rb

This is the main file of this library. It contains all grammar and associated actions.

Grammar

The original MySQL grammar can be found at https://github.com/twitter/mysql/blob/master/sql/sql_yacc.yy. The documentation can be found under https://dev.mysql.com/doc/refman/5.6/en/

Conflict

sql_yacc.yy is not perfect. It contains a lot of conflicts. When translating to mysql.y.rb, please resolve those conflicts so that the rules can be predictable.

Debugging

Debugging mysql.y.rb can be done by passing true as debug argument.

MySQLParser.new(debug: true)

Literals

Introduction of a new literals can be done by just using it. bin/generate-literal.rb will read mysql.y.rb and make sure that new literals are created. Similarly, removing any literal can be done by just not using it.

Convention

Following is general convention.

1. Space is S
2. opt_... means that the rule is optional. The first branch should be empty.
3. {comma, space}_separated_... means a collection of items separated by a separator (comma or space)

bin/generate-literal.rb

This script scans mysql.y.rb (or actually, parser.output which is generated from mysql.y.rb) and adds new literals that don't exist, and removes literals that are unused.

bin/runner

This is a REPL. Just input whatever and send an end-of-file character (ctrl-d) to let the script process the input. To exit, just terminate using ctrl-c.

> DROP TABLE


table
^D
parse error on value "table" (TABLE)
>

bin/sanity_check

This script makes sure that there is no skipped action and that all action names are correct.

Development

After changing mysql.rex.rb or mysql.y.rb, run rake generate to generate the real lexer and parser. Run rake spec to run all test cases

License

Copyright 2015 Square, Inc.

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

   http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

mysql-parser's Issues

CREATE VIEW parsing issue

Hi, Fallowing spec does not work as expected:

    it 'tests for create view' do
      @result = @evaluator.parse('CREATE VIEW asd AS select * FROM def')
      test.to eq(' CREATE VIEW asd AS select * FROM def ')
    end

 1) Testing the Parser tests for create view
     Failure/Error: raise  ScanError, "can not match: '" + text + "'"
     
     MySQLParser::ScanError:
       can not match: '* FROM def '
     # ./lib/lexer.rb:635:in `_next_token'
     # ./lib/lexer.rb:50:in `next_token'
     # ./lib/lexer.rb:726:in `tokenize'
     # mysql.y.rb:1284:in `parse'
     # ./spec/parser_spec.rb:63:in `block (2 levels) in <class:MySQLParserTester>'

Racc::ParseError: parse can be raised for correct SQL statements.

When trying to parse a alter table query containing DEFAULT 0 NOT NULL, Racc::ParseError: parse error on value "NOT" (NOT) is raised. According to the MySQL documentation, this query is using the correct syntax.

Interestingly, if we replace DEFAULT 0 NOT NULL with NOT NULL DEFAULT 0, the parsing is successful.

irb(main):001:0> require 'mysql-parser'
=> true
irb(main):002:0> query = "ALTER TABLE `some_table` ADD `something` int DEFAULT 0 NOT NULL"
=> "ALTER TABLE `some_table` ADD `something` int DEFAULT 0 NOT NULL"
irb(main):003:0> MySQLParser.new.parse(query)
Racc::ParseError:
parse error on value "NOT" (NOT)
	from /Users/gabrieltanase/.rbenv/versions/2.4.5/lib/ruby/2.4.0/racc/parser.rb:528:in `on_error'
	from /Users/gabrieltanase/.rbenv/versions/2.4.5/lib/ruby/2.4.0/racc/parser.rb:259:in `_racc_do_parse_c'
	from /Users/gabrieltanase/.rbenv/versions/2.4.5/lib/ruby/2.4.0/racc/parser.rb:259:in `do_parse'
	from /Users/gabrieltanase/.rbenv/versions/2.4.5/lib/ruby/gems/2.4.0/gems/mysql-parser-0.0.3/lib/lexer.rb:29:in `scan_str'
	from mysql.y.rb:1288:in `parse'
	from (irb):3
	from /Users/gabrieltanase/.rbenv/versions/2.4.5/bin/irb:11:in `<main>'
irb(main):004:0>
irb(main):005:0*
irb(main):006:0* query = "ALTER TABLE `some_table` ADD `something` int NOT NULL DEFAULT 0"
=> "ALTER TABLE `some_table` ADD `something` int NOT NULL DEFAULT 0"
irb(main):007:0> MySQLParser.new.parse(query)
=> {:tree=><root: [<S: [" "]>, <r_commands: [<r_ALTER_TABLE: ["ALTER", <S: [" "]>, <r_ONLINE_OFFLINE: []>, <r_opt_IGNORE: []>, "TABLE", <S: [" "]>, <r_tbl_name: [<r_tbl_name_int: [<ident: ["`", <opt_ident_in_backtick: [<opt_ident_in_backtick: []>, "some_table"]>, "`", <S: [" "]>]>]>]>, <r_opt_alter_commands: [<r_comma_separated_alter_specification: [<r_alter_specification: ["ADD", <S: [" "]>, <r_opt_COLUMN: []>, <r_col_name: [<ident: ["`", <opt_ident_in_backtick: [<opt_ident_in_backtick: []>, "something"]>, "`", <S: [" "]>]>]>, <r_column_definition: [<r_datatype: ["int", <S: [" "]>, <r_opt_datatype_int: [<r_opt_length_int: []>, <r_opt_UNSIGNED: []>, <r_opt_ZEROFILL: []>]>]>, <r_opt_NULL_status: ["NOT", <S: [" "]>, <null: ["NULL", <S: [" "]>]>]>, <r_opt_DEFAULT_with_val: ["DEFAULT", <S: [" "]>, <value: [<number: [<integer: [<nat: [<binary: ["0", <S: [" "]>]>]>]>]>]>]>, <r_opt_AUTO_INCREMENT: []>, <r_opt_UNIQUE_or_PRIMARY: []>, <r_opt_COMMENT_with_val: []>, <r_opt_COLUMN_FORMAT: []>, <r_opt_STORAGE: []>, <r_opt_COLUMN_ON_UPDATE: []>]>, <r_opt_pos_column: []>]>]>]>, <r_opt_after_alter: []>, <r_opt_PARTITION_options: []>]>]>]>, :state=>{}}

Seems related to https://github.com/square/mysql-parser/blob/master/mysql.y.rb#L418-L422