require 'spec_helper'
require 'flydata-core/table_def'
require 'logger'
$log = Logger.new(STDOUT)

module FlydataCore
module TableDef

describe MysqlTableDef do

  # file full path which exists in same directory.
  def full_path(file_name)
    File.join(File.dirname(__FILE__), file_name)
  end

  def file_io(file_name)
    File.new(full_path(file_name))
  end

  describe '.to_flydata_tabledef' do
    let(:dump_file_io) { raise "dump_file_io must be override on context." }
    subject { MysqlTableDef.create(dump_file_io).to_flydata_tabledef }

    context 'when dump file includes single table with all kind of format types' do
      let(:dump_file_io) { file_io('mysqldump_test_table_all.dump') }

      it 'should set correct table name' do
        expect(subject[:table_name]).to eq('test_table_all')
      end

      it 'should set correct table name' do
        expect(subject[:columns]).to eq(
          [
            {:column=>"id", :source_type=>"bigint(20)", :type=>"int8(20)", :auto_increment=>true, :not_null=>true, :primary_key=>true},
            {:column=>"col_binary", :source_type=>"binary(100)", :type=>"binary(202)", :default=>nil},
            {:column=>"col_blob", :source_type=>"blob", :type=>"varbinary(65535)"},
            {:column=>"col_bool", :source_type=>"tinyint(1)", :type=>"int1(1)", :default=>"'0'"},
            {:column=>"col_char", :source_type=>"char(6)", :type=>"varchar(18)", :default=>nil},
            {:column=>"col_date", :source_type=>"date", :type=>"date", :default=>nil},
            {:column=>"col_datetime", :source_type=>"datetime", :type=>"datetime", :default=>nil},
            {:column=>"col_decimal", :source_type=>"decimal(5,2)", :type=>"numeric(5,2)", :default=>nil},
            {:column=>"col_double", :source_type=>"double", :type=>"float8", :default=>nil},
            {:column=>"col_float", :source_type=>"float", :type=>"float4", :default=>nil},
            {:column=>"col_float_4_2", :source_type=>"float(4,2)", :type=>"float4(4,2)", :default=>nil},
            {:column=>"col_int", :source_type=>"int(11)", :type=>"int4(11)", :default=>nil},
            {:column=>"col_int_6", :source_type=>"int(6)", :type=>"int4(6)", :default=>nil},
            {:column=>"col_json", :source_type=>"json", :type=>"json", :default=>nil},
            {:column=>"col_longblob", :source_type=>"longblob", :type=>"varbinary(4294967295)"},
            {:column=>"col_longtext", :source_type=>"longtext", :type=>"text"},
            {:column=>"col_mediumblob", :source_type=>"mediumblob", :type=>"varbinary(16777215)"},
            {:column=>"col_mediumint", :source_type=>"mediumint(9)", :type=>"int3(9)", :default=>nil},
            {:column=>"col_mediumtext", :source_type=>"mediumtext", :type=>"text"},
            {:column=>"col_smallint", :source_type=>"smallint(6)", :type=>"int2(6)", :default=>nil},
            {:column=>"col_text", :source_type=>"text", :type=>"text"},
            {:column=>"col_time", :source_type=>"time", :type=>"time", :default=>nil},
            {:column=>"col_timestamp", :source_type=>"timestamp", :type=>"datetime", :not_null=>true, :default=>"CURRENT_TIMESTAMP"},
            {:column=>"col_tinyblob", :source_type=>"tinyblob", :type=>"varbinary(255)"},
            {:column=>"col_tinyint", :source_type=>"tinyint(4)", :type=>"int1(4)", :default=>nil},
            {:column=>"col_tinytext", :source_type=>"tinytext", :type=>"text"},
            {:column=>"col_varbinary", :source_type=>"varbinary(255)", :type=>"varbinary(512)", :default=>nil},
            {:column=>"col_varchar", :source_type=>"varchar(124)", :type=>"varchar(372)", :default=>nil},
            {:column=>"col_year", :source_type=>"year", :type=>"year", :default=>nil},
            {:column=>"col_year_4", :source_type=>"year(4)", :type=>"year(4)", :default=>nil},
            {:column=>"col_year_2", :source_type=>"year(2)", :type=>"year(2)", :default=>nil},
          ]
        )
      end

      it 'default_charset should be set' do
        expect(subject[:default_charset]).to eq('UTF_8')
      end

      it 'comment should be set' do
        expect(subject[:comment]).to eq('test table includes all kind of format type')
      end

      it 'return value with src_ddl' do
        src_ddl = "CREATE TABLE `test_table_all` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `col_binary` binary(100) DEFAULT NULL,
  `col_blob` blob,
  `col_bool` tinyint(1) DEFAULT '0',
  `col_char` char(6) DEFAULT NULL,
  `col_date` date DEFAULT NULL,
  `col_datetime` datetime DEFAULT NULL,
  `col_decimal` decimal(5,2) DEFAULT NULL,
  `col_double` double DEFAULT NULL,
  `col_float` float DEFAULT NULL,
  `col_float_4_2` float(4,2) DEFAULT NULL,
  `col_int` int(11) DEFAULT NULL,
  `col_int_6` int(6) DEFAULT NULL,
  `col_json` json DEFAULT NULL,
  `col_longblob` longblob,
  `col_longtext` longtext,
  `col_mediumblob` mediumblob,
  `col_mediumint` mediumint(9) DEFAULT NULL,
  `col_mediumtext` mediumtext,
  `col_smallint` smallint(6) DEFAULT NULL,
  `col_text` text,
  `col_time` time DEFAULT NULL,
  `col_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `col_tinyblob` tinyblob,
  `col_tinyint` tinyint(4) DEFAULT NULL,
  `col_tinytext` tinytext,
  `col_varbinary` varbinary(255) DEFAULT NULL,
  `col_varchar` varchar(124) DEFAULT NULL,
  `col_year` year DEFAULT NULL,
  `col_year_4` year(4) DEFAULT NULL,
  `col_year_2` year(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='test table includes all kind of format type';".split("\n").join
        expect(subject[:src_ddl]).to eq(src_ddl)
      end
    end

    context 'when table does not have either primary key or unique key' do
      let(:dump_file_io) { file_io('mysqldump_test_table_no_pk.dump') }
      it 'should raise an error' do
        expect{subject}.to raise_error(FlydataCore::TableDefError)
      end
    end

    context 'when table does not have primary key but has unique key' do
      let(:dump_file_io) { file_io('mysqldump_test_table_only_uk.dump') }
      it 'should not raise an error' do
        expect{subject}.not_to raise_error
      end

      it 'should create pk_override from uk' do
        expect(subject[:pk_override]).to eq( ['id'] )
      end
    end

    context 'when table which has a unique key using NULL column' do
      let(:dump_file_io) { file_io('mysqldump_test_null_uk.dump') }
      it 'should raise an error' do
        expect{subject}.to raise_error(FlydataCore::TableDefError)
      end
    end

    context 'when table is VIEW (unsupported)' do
      let(:dump_file_io) { file_io('mysqldump_test_view1.dump') }
      it 'should raise an error' do
        expect{subject}.to raise_error(FlydataCore::TableDefError)
      end
    end

    context 'when table is MEMORY ENGINE (unsupported)' do
      let(:dump_file_io) { file_io('mysqldump_test_memory_engine.dump') }
      it 'should raise an error' do
        expect{subject}.to raise_error(FlydataCore::TableDefError)
      end
    end

    context 'when table is BLACKHOLE ENGINE (unsupported)' do
      let(:dump_file_io) { file_io('mysqldump_test_blackhole_engine.dump') }
      it 'should raise an error' do
        expect{subject}.to raise_error(FlydataCore::TableDefError)
      end
    end

    context 'when column has comment' do
      let(:dump_file_io) { file_io('mysqldump_test_table_column_comment.dump') }
      it 'comment should be set' do
        expect(subject[:columns]).to eq(
          [
            {:column=>"id", :source_type=>"int(11)", :type=>"int4(11)", :not_null=>true, :default=>"'0'",
             :comment=>"this is primary key", :primary_key=>true},
            {:column=>"value", :source_type=>"text", :type=>"text"}
          ]
        )
      end

      context 'when column comment contains includes invalid chars' do
        let(:dump_file_io) { file_io('mysqldump_test_table_column_comment_including_invalid_char.dump') }
        it 'comment should be set with replaced chars' do
          expect(subject[:columns]).to eq(
            [
              {:column=>"id", :source_type=>"int(11)", :type=>"int4(11)", :not_null=>true, :default=>"'0'",
               :comment=>"a?2BY???i", :primary_key=>true},
               {:column=>"value", :source_type=>"text", :type=>"text", :comment=>"あいうえお"}
            ]
          )
        end
      end
    end

    context 'when table has multiple-column pk' do
      let(:dump_file_io) { file_io('mysqldump_test_table_multi_pk.dump') }
      it 'multi pk should be set' do
        expect(subject[:columns]).to eq(
          [
            {:column=>"id1", :source_type=>"int(11)", :type=>"int4(11)", :not_null=>true, :default=>"'0'", :primary_key=>true},
            {:column=>"id2", :source_type=>"int(11)", :type=>"int4(11)", :not_null=>true, :default=>"'0'", :primary_key=>true},
            {:column=>"value", :source_type=>"text", :type=>"text"}
          ]
        )
      end
    end

    context 'when table has enum column' do
      let(:dump_file_io) { file_io('mysqldump_test_table_enum.dump') }
      it 'enum columns should be parsed' do
        expect(subject[:columns]).to eq(
          [
            {:column=>"id", :source_type=>"int(11)", :type=>"int4(11)", :not_null=>true, :primary_key=>true},
            {:column=>"enum_1", :source_type=>"enum('apple','orange','banana')", :type=>"enum('apple','orange','banana')", :default=>nil},
            {:column=>"enum_2", :source_type=>"enum('a','b','c')", :type=>"enum('a','b','c')", :default=>"'a'"},
            {:column=>"enum_3", :source_type=>"enum('e','f','g')", :type=>"enum('e','f','g')", :not_null=>true},
          ]
        )
      end
    end

    context 'when table has index and foreign key constraints' do
      let(:dump_file_io) { file_io('mysqldump_test_foreign_key.dump') }
      it 'should parse the dump correctly' do
        expect(subject[:columns]).to eq(
          [
            {:column=>"no", :source_type=>"int(11)", :type=>"int4(11)", :auto_increment=>true, :not_null=>true, :primary_key=>true},
            {:column=>"product_category", :source_type=>"int(11)", :type=>"int4(11)", :not_null=>true},
            {:column=>"product_id", :source_type=>"int(11)", :type=>"int4(11)", :not_null=>true},
            {:column=>"customer_id", :source_type=>"int(11)", :type=>"int4(11)", :not_null=>true},
          ]
        )
      end
    end

    context 'when table has unsigned column' do
      let(:dump_file_io) { file_io('mysqldump_test_unsigned.dump')}
      it 'should include unsigned in column type' do
        expect(subject[:columns]).to eq(
          [
            {:column=>"id", :source_type=>"int(11)", :type=>"int4(11)", :auto_increment=>true, :not_null=>true, :primary_key=>true},
            {:column=>"value_int", :source_type=>"int(10)", :type=>"int4(10) unsigned", :default=>nil},
            {:column=>"value_float", :source_type=>"float", :type=>"float4 unsigned", :default=>nil},
            {:column=>"value_dec", :source_type=>"decimal(10,2)", :type=>"numeric(10,2) unsigned", :default=>nil},
            {:column=>"value_double", :source_type=>"double", :type=>"float8 unsigned", :default=>nil},
            {:column=>"name", :source_type=>"varchar(256)", :type=>"varchar(768)", :default=>nil},
            {:column=>"value_small_int", :source_type=>"smallint(5)", :type=>"int2(5) unsigned", :default=>nil}
          ]
        )
      end
    end

    context 'when table has unique keys' do
      let(:dump_file_io) { file_io('mysqldump_test_unique_key.dump')}
      it 'should include unique in column def' do
        expect(subject[:columns]).to eq(
          [
            {:column=>"id", :source_type=>"int(11)", :type=>"int4(11)", :auto_increment=>true, :not_null=>true, :primary_key=>true},
            {:column=>"title", :source_type=>"varchar(256)", :type=>"varchar(768)", :unique=>true, :default=>nil},
            {:column=>"name", :source_type=>"text", :type=>"text"},
            {:column=>"num", :source_type=>"int(11)", :type=>"int4(11)", :unique=>true, :default=>nil}
          ]
        )
      end
    end

    context 'when table has an unique key with a key name different than the column name' do
      let(:dump_file_io) { file_io('mysqldump_test_unique_key2.dump')}
      it 'should include unique in column def' do
        expect(subject[:columns]).to eq(
          [
            {:column=>"id", :source_type=>"int(11)", :type=>"int4(11)", :auto_increment=>true, :not_null=>true, :primary_key=>true},
            {:column=>"title", :source_type=>"varchar(256)", :type=>"varchar(768)", :unique=>true, :default=>nil},
            {:column=>"name", :source_type=>"text", :type=>"text"},
            {:column=>"num", :source_type=>"int(11)", :type=>"int4(11)", :unique=>true, :default=>nil}
          ]
        )
      end
    end

    context 'when table has unique keys with length specified' do
      let(:dump_file_io) { file_io('mysqldump_test_unique_key3.dump')}
      it 'should include unique in column def' do
        subject[:columns].each { |col|
          expect(col[:unique]).to eq(true) if ['app_id','overage_id','item_name'].include? col[:column]
        }
      end
    end

    context 'when table has bit with 0b default value' do
      let(:dump_file_io) { file_io('mysqldump_test_bit_table.dump')}
      it 'should extract default bit value appropriately' do
        expect(subject[:columns][1][:default]).to eq("b'1'")
      end
    end

    context 'when table has int with 0x default value' do
      let(:dump_file_io) { file_io('mysqldump_test_bit_table.dump')}
      it 'should extract default int value appropriately' do
        expect(subject[:columns][2][:default]).to eq("x'10'")
      end
    end

    context 'when a column has a character set' do
      let(:dump_file_io) { file_io('mysqldump_test_column_charset.dump')}
      it 'should extract charset value for the columns' do
        expect(subject[:columns][1][:charset]).to eq("ISO_8859_1")
      end
    end

    context 'when a column name has space in it' do
      let(:dump_file_io) { file_io('mysqldump_test_col_name_with_space.dump')}
      it 'should extract col name properly' do
        expect(subject[:columns][4][:column]).to eq("space test")
        expect(subject[:columns][4][:type]).to eq("varchar(300)")
      end
    end

    context 'when column comment has unique keyword' do
      let(:dump_file_io) { file_io('mysqldump_test_col_comment_with_unique_keyword.dump')}
      it 'should not include unique in column def' do
        expect(subject[:columns]).to eq(
           [
               {:column=>"id", :source_type=>"int(11)", :type=>"int4(11)", :auto_increment=>true, :not_null=>true, :primary_key=>true },
               {:column=>"title", :source_type=>"varchar(256)", :type=>"varchar(768)", :default=>nil , :comment=>"This column is not unique column."},
               {:column=>"name", :source_type=>"text", :type=>"text"},
               {:column=>"num", :source_type=>"int(11)", :type=>"int4(11)", :default=>nil}
           ]
        )
      end
    end

    context 'when column comment has unique keyword' do
      let(:dump_file_io) { file_io('mysqldump_test_col_comment_with_AUTO_INCREMENT_keyword.dump')}
      it 'should not include AUTO_INCREMENT in column def' do
        expect(subject[:columns]).to eq(
           [
               {:column=>"id", :source_type=>"int(11)", :type=>"int4(11)", :auto_increment=>true, :not_null=>true, :primary_key=>true },
               {:column=>"title", :source_type=>"varchar(256)", :type=>"varchar(768)", :default=>nil , :comment=>"This column is not AUTO_INCREMENT column."},
               {:column=>"name", :source_type=>"text", :type=>"text"},
               {:column=>"num", :source_type=>"int(11)", :type=>"int4(11)", :default=>nil}
           ]
        )
      end
    end

    context 'when column comment has unique keyword' do
      let(:dump_file_io) { file_io('mysqldump_test_col_comment_with_unsigned_keyword.dump')}
      it 'should not include unsigned in column def' do
        expect(subject[:columns]).to eq(
           [
               {:column=>"id", :source_type=>"int(11)", :type=>"int4(11)", :auto_increment=>true, :not_null=>true, :primary_key=>true },
               {:column=>"title", :source_type=>"varchar(256)", :type=>"varchar(768)", :default=>nil , :comment=>"This column is not unsigned column."},
               {:column=>"name", :source_type=>"text", :type=>"text"},
               {:column=>"num", :source_type=>"int(11)", :type=>"int4(11)", :default=>nil}
           ]
        )
      end
    end


    context 'when column comment has not null keyword' do
      let(:dump_file_io) { file_io('mysqldump_test_col_comment_with_not_null_keyword.dump')}
      it 'should not include not null in column def' do
        expect(subject[:columns]).to eq(
           [
               {:column=>"id", :source_type=>"int(11)", :type=>"int4(11)", :auto_increment=>true, :not_null=>true, :primary_key=>true },
               {:column=>"title", :source_type=>"varchar(256)", :type=>"varchar(768)", :default=>nil , :comment=>"This column is not null column."},
               {:column=>"name", :source_type=>"text", :type=>"text"},
               {:column=>"num", :source_type=>"int(11)", :type=>"int4(11)", :default=>nil}
           ]
        )
      end
    end
  end

  describe '.convert_to_flydata_type' do
    subject { described_class.convert_to_flydata_type(mysql_type) }
    shared_examples "converting the mysql data type to the expected flydata type" do
      # Use the context description as the value of "mysql_type"
      # RSpec.current_example.meatadata[:example_group] => the shared_examples "converting the mysql data type to the expected flydata type"
      # [:parent_example_group] => the context including the shared examples (e.g. context "bigint")
      # [:description] => the name of the example group (e.g. "bigint")
      let(:mysql_type) { RSpec.current_example.metadata[:example_group][:parent_example_group][:description] }
      it do
        expect(subject).to eq flydata_type
      end
    end
    context "with no-width mysql data type" do
      context "bigint" do
        let(:flydata_type) { 'int8(20)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "binary" do
        let(:flydata_type) { 'binary(4)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "bit" do
        let(:flydata_type) { 'bit(1)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "blob" do
        let(:flydata_type) { 'varbinary(65535)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "bool" do
        let(:flydata_type) { 'int1(1)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "boolean" do
        let(:flydata_type) { 'int1(1)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "char" do
        let(:flydata_type) { 'varchar(3)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "date" do
        let(:flydata_type) { 'date' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "datetime" do
        let(:flydata_type) { 'datetime' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "dec" do
        let(:flydata_type) { 'numeric(10,0)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "decimal" do
        let(:flydata_type) { 'numeric(10,0)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "double" do
        let(:flydata_type) { 'float8' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "fixed" do
        let(:flydata_type) { 'numeric(10,0)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "float" do
        let(:flydata_type) { 'float4' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "int" do
        let(:flydata_type) { 'int4(11)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "integer" do
        let(:flydata_type) { 'int4(11)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "longblob" do
        let(:flydata_type) { 'varbinary(4294967295)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "longtext" do
        let(:flydata_type) { 'text' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "mediumblob" do
        let(:flydata_type) { 'varbinary(16777215)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "mediumint" do
        let(:flydata_type) { 'int3(9)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "mediumtext" do
        let(:flydata_type) { 'text' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "numeric" do
        let(:flydata_type) { 'numeric(10,0)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "smallint" do
        let(:flydata_type) { 'int2(6)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "text" do
        let(:flydata_type) { 'text' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "time" do
        let(:flydata_type) { 'time' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "timestamp" do
        let(:flydata_type) { 'datetime' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "tinyblob" do
        let(:flydata_type) { 'varbinary(255)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "tinyint" do
        let(:flydata_type) { 'int1(4)' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "tinytext" do
        let(:flydata_type) { 'text' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
      context "year" do
        let(:flydata_type) { 'year' }
        it_behaves_like "converting the mysql data type to the expected flydata type"
      end
    end
  end
  describe '.convert_to_flydata_value' do
    subject { MysqlTableDef.convert_to_flydata_value(value, type) }

    let(:value_body) { '0xFE0040' }
    context 'binary type' do
      let(:type) { 'binary' }
      context 'value with padding' do
        let(:value) { "#{value_body}0000000000" }
        it 'truncates padding' do
          is_expected.to eq value_body
        end
      end
      context 'nil value' do
        let(:value) { nil }
        it 'returns nil' do
          is_expected.to eq nil
        end
      end
    end
    context 'varbinary type' do
      let(:type) { 'varbinary' }
      context 'value_with_padding' do
        let(:value) { "#{value_body}0000000000" }
        it 'returns the value as is' do
          is_expected.to eq value
        end
      end
    end
  end

  describe '.parse_one_column_def' do
    subject { described_class.parse_one_column_def(query) }

    let(:query) { "  `#{colname}` text," }
    let(:expected_result) {
      { column: colname,
        source_type: 'text',
        type: 'text',
      }
    }

    context 'with an ASCII column name' do
      let(:colname) { "name" }

      it { is_expected.to eq expected_result }
    end

    context 'with an non-ASCII column name' do
      let(:colname) { "在庫ID" }

      it { is_expected.to eq expected_result }
    end

    context 'with a column name including space' do
      let(:colname) { "full name" }

      it { is_expected.to eq expected_result }
    end

    context 'with a column name including all supported ASCII special characters' do
      # all special characters but '.' and '`'.
      # '.' has its own spec.  '`' should not come because MySQL doesn't allow it
      let(:colname) { %q| !"#$%&'()*+,-/:;<=>?@[\\]^_{\|}~| }

      it { is_expected.to eq expected_result }
    end

    context 'with a column name including comma' do
      let(:colname) { %q|my.column| }

      it "FlyData does not support a column name with a period in it.  However, the agent accepts it as is.  Necessary conversion happens at the server side." do
        is_expected.to eq expected_result
      end
    end
  end
end

end
end