require 'spec_helper' describe 'MysqlAlterTableParser' do before do #load parser @parser_class = Mysql::ParserProvider.parser(:mysql, :mysql_alter_table) end let(:query) { "" } let(:parser) { @parser_class.new } subject { parser.parse(query).tree } describe '#parse' do context 'with alter table add column syntax' do context 'with normal alter table query' do let(:query) { "alter table test_table add column value varchar(26)" } it do expect(subject).to eq({ type: :alter_table, table_name: "test_table", actions: [{ action: :add_column, column: "value", type: "varchar(78)" }] }) end end context 'without the keyword "COLUMN"' do let(:query) { "alter table test_table add value varchar(26)" } it do expect(subject).to eq({ type: :alter_table, table_name: "test_table", actions: [{ action: :add_column, column: "value", type: "varchar(78)" }] }) end end context 'with multiple columns' do let(:query) { "alter table test_table add column (value1 varchar(26), value2 varchar(26))" } it do expect(subject).to eq({ type: :alter_table, table_name: "test_table", actions: [{ action: :add_column, column: "value1", type: "varchar(78)" },{ action: :add_column, column: "value2", type: "varchar(78)" }] }) end end context 'with multiple columns and without the keyword "COLUMN"' do let(:query) { "alter table test_table add ( value1 varchar(26), value2 varchar(26) )" } it do expect(subject).to eq({ type: :alter_table, table_name: "test_table", actions: [{ action: :add_column, column: "value1", type: "varchar(78)" },{ action: :add_column, column: "value2", type: "varchar(78)" }] }) end end context 'with multiple columns that have more detailed definitions,without the keyword "COLUMN"' do let(:query) { "alter table test_table add (value1 varchar(26) not null default 'flydata', value2 int auto_increment)" } it do expect(subject).to eq({ type: :alter_table, table_name: "test_table", actions: [{ action: :add_column, column: "value1", type: "varchar(78)", not_null: true, default: "flydata", },{ action: :add_column, column: "value2", type: "int4", auto_increment: true, }] }) end end context 'with backticks' do let(:query) { "alter table `test_table` add (`value1` varchar(26) not null default 'flydata', `value2` int auto_increment)" } it do expect(subject).to eq({ type: :alter_table, table_name: "test_table", actions: [{ action: :add_column, column: "value1", type: "varchar(78)", not_null: true, default: "flydata", },{ action: :add_column, column: "value2", type: "int4", auto_increment: true, }] }) end end context 'with after option' do let(:query) { "alter table test_table add column value varchar(26) after id" } it do expect(subject).to eq({ type: :alter_table, table_name: "test_table", actions: [{ action: :add_column, column: "value", type: "varchar(78)", after: 'id', }] }) end end context 'with after option, backticks only on table name and a comment' do let(:query) { %Q|alter table `test_table` add column value varchar(26) comment 'Ignorable comment' after id| } it do expect(subject).to eq({ type: :alter_table, table_name: "test_table", actions: [{ action: :add_column, column: "value", type: "varchar(78)", after: 'id', }] }) end end context 'with first option' do let(:query) { "alter table test_table add column value varchar(26) first" } it do expect(subject).to eq({ type: :alter_table, table_name: "test_table", actions: [{ action: :add_column, column: "value", type: "varchar(78)", position: :first, }] }) end end context 'with first, unique' do let(:query) { "alter table test_table add column value varchar(26) UNIQUE first" } it do expect(subject).to eq({ type: :alter_table, table_name: "test_table", actions: [{ action: :add_column, column: "value", type: "varchar(78)", unique: true, position: :first, }] }) end end end context 'with alter table drop column syntax' do let(:query) { "alter table test_table drop column value" } it do expect(subject).to eq( type: :alter_table, table_name: "test_table", actions: [{ action: :drop_column, column: "value" }]) end end context 'without column keyword' do let(:query) { "alter table test_table drop value" } it do expect(subject).to eq( type: :alter_table, table_name: "test_table", actions: [{ action: :drop_column, column: "value" }]) end end context 'when column_name is wrapped with backquote' do let(:query) { "alter table test_table drop `value`" } it do expect(subject).to eq( type: :alter_table, table_name: "test_table", actions: [{ action: :drop_column, column: "value" }]) end end context 'when table_name is wrapped with backquote' do let(:query) { "alter table `test_table` drop value" } it do expect(subject).to eq( type: :alter_table, table_name: "test_table", actions: [{ action: :drop_column, column: "value" }]) end end context 'when keywords are capitalized' do let(:query) { "ALTER TABLE `test_table` DROP value" } it do expect(subject).to eq( type: :alter_table, table_name: "test_table", actions: [{ action: :drop_column, column: "value" }]) end end context 'when table_name includes schema' do let(:query) { "alter table test_schema.test_table drop value" } it do expect(subject).to eq( type: :alter_table, schema_name: "test_schema", table_name: "test_table", actions: [{ action: :drop_column, column: "value" }]) end end context 'when table_name includes schema with quotes' do let(:query) { "alter table `test_schema`.`test_table` drop `value`" } it do expect(subject).to eq( type: :alter_table, schema_name: "test_schema", table_name: "test_table", actions: [{ action: :drop_column, column: "value" }]) end end context 'when query has unsupported drop dextension' do subject { parser.parse(query) } context 'with primary key drop' do let(:query) { "alter table test_table drop PRIMARY KEY" } it { expect(subject).to be_nil } end context 'with index drop' do let(:query) { "alter table test_table drop Index index_name" } it { expect(subject).to be_nil } end context 'with key drop' do let(:query) { "alter table test_table drop key index_name" } it { expect(subject).to be_nil } end context 'with foreign key drop' do let(:query) { "alter table test_table drop foreign key foreign_key_name" } it { expect(subject).to be_nil } end end context 'with multiple alter specs' do context 'with add column and drop column' do let(:query) { "alter table test_table add column value varchar(26) after id, drop old_value" } it do expect(subject).to eq({ type: :alter_table, table_name: "test_table", actions: [{ action: :add_column, column: "value", type: "varchar(78)", after: 'id', },{ action: :drop_column, column: "old_value", }] }) end end end end end