require 'spec_helper'
require 'flydata-core/table_def'

module FlydataCore
  module TableDef
    describe 'generate redshift table ddl from mysqldump' 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

      let(:dump_file_name) { raise "file_name must be override on context." }
      let(:dump_file_io) { file_io(dump_file_name) }

      subject {
        RedshiftTableDef.from_flydata_tabledef(
        MysqlTableDef.create(dump_file_io).to_flydata_tabledef)
      }

      context 'with mysqldump_test_table_all' do
        let(:dump_file_name) { 'mysqldump_test_table_all.dump' }

        it 'should return ddl' do
          expect(subject).to eq( <<EOT.strip )
CREATE TABLE IF NOT EXISTS "flydata_ctl_columns"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  column_name varchar(128) NOT NULL,
  src_data_type varchar(1024) NOT NULL,
  revision int NOT NULL DEFAULT 1,
  ordinal_position int NOT NULL,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
CREATE TABLE IF NOT EXISTS "flydata_ctl_tables"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  attribute varchar(128) NOT NULL,
  value varchar(max),
  created_at timestamp DEFAULT SYSDATE,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
DROP TABLE IF EXISTS "test_table_all";
CREATE TABLE "test_table_all" (
  "id" int8,
  "col_binary" varchar(202) DEFAULT NULL,
  "col_blob" varchar(65535),
  "col_bool" int2 DEFAULT '0',
  "col_char" varchar(18) DEFAULT NULL,
  "col_date" date DEFAULT NULL,
  "col_datetime" timestamp DEFAULT NULL,
  "col_decimal" numeric(5,2) DEFAULT NULL,
  "col_double" float8 DEFAULT NULL,
  "col_float" float4 DEFAULT NULL,
  "col_float_4_2" float4 DEFAULT NULL,
  "col_int" int4 DEFAULT NULL,
  "col_int_6" int4 DEFAULT NULL,
  "col_longblob" varchar(65535),
  "col_longtext" varchar(max),
  "col_mediumblob" varchar(65535),
  "col_mediumint" int4 DEFAULT NULL,
  "col_mediumtext" varchar(max),
  "col_smallint" int2 DEFAULT NULL,
  "col_text" varchar(max),
  "col_time" timestamp DEFAULT NULL,
  "col_timestamp" timestamp DEFAULT SYSDATE,
  "col_tinyblob" varchar(255),
  "col_tinyint" int2 DEFAULT NULL,
  "col_tinytext" varchar(max),
  "col_varbinary" varchar(512) DEFAULT NULL,
  "col_varchar" varchar(372) DEFAULT NULL,
  "col_year" date DEFAULT NULL,
  "col_year_4" date DEFAULT NULL,
  "col_year_2" date DEFAULT NULL,
  PRIMARY KEY ("id")
) DISTKEY("id") SORTKEY("id");
DELETE FROM "flydata_ctl_columns" WHERE table_name = 'test_table_all';
INSERT INTO "flydata_ctl_columns" (table_name, column_name, src_data_type, ordinal_position) VALUES
('test_table_all', 'id', 'int8(20)', 1),
('test_table_all', 'col_binary', 'binary(202)', 2),
('test_table_all', 'col_blob', 'varbinary(65535)', 3),
('test_table_all', 'col_bool', 'int1(1)', 4),
('test_table_all', 'col_char', 'varchar(18)', 5),
('test_table_all', 'col_date', 'date', 6),
('test_table_all', 'col_datetime', 'datetime', 7),
('test_table_all', 'col_decimal', 'numeric(5,2)', 8),
('test_table_all', 'col_double', 'float8', 9),
('test_table_all', 'col_float', 'float4', 10),
('test_table_all', 'col_float_4_2', 'float4(4,2)', 11),
('test_table_all', 'col_int', 'int4(11)', 12),
('test_table_all', 'col_int_6', 'int4(6)', 13),
('test_table_all', 'col_longblob', 'varbinary(4294967295)', 14),
('test_table_all', 'col_longtext', 'text', 15),
('test_table_all', 'col_mediumblob', 'varbinary(16777215)', 16),
('test_table_all', 'col_mediumint', 'int3(9)', 17),
('test_table_all', 'col_mediumtext', 'text', 18),
('test_table_all', 'col_smallint', 'int2(6)', 19),
('test_table_all', 'col_text', 'text', 20),
('test_table_all', 'col_time', 'time', 21),
('test_table_all', 'col_timestamp', 'datetime', 22),
('test_table_all', 'col_tinyblob', 'varbinary(255)', 23),
('test_table_all', 'col_tinyint', 'int1(4)', 24),
('test_table_all', 'col_tinytext', 'text', 25),
('test_table_all', 'col_varbinary', 'varbinary(512)', 26),
('test_table_all', 'col_varchar', 'varchar(372)', 27),
('test_table_all', 'col_year', 'year', 28),
('test_table_all', 'col_year_4', 'year(4)', 29),
('test_table_all', 'col_year_2', 'year(2)', 30);
DELETE FROM "flydata_ctl_tables" WHERE table_name = 'test_table_all';
INSERT INTO "flydata_ctl_tables" (table_name, attribute, value) VALUES
('test_table_all', 'cs', 'UTF_8'),
('test_table_all', 'revision', 1),
('test_table_all', '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_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}');
EOT
        end
      end

      context 'with mysqldump_test_bit_table.dump' do
        let(:dump_file_name) { 'mysqldump_test_bit_table.dump' }

        it 'should return ddl' do
          expect(subject).to eq( <<EOT.strip )
CREATE TABLE IF NOT EXISTS "flydata_ctl_columns"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  column_name varchar(128) NOT NULL,
  src_data_type varchar(1024) NOT NULL,
  revision int NOT NULL DEFAULT 1,
  ordinal_position int NOT NULL,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
CREATE TABLE IF NOT EXISTS "flydata_ctl_tables"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  attribute varchar(128) NOT NULL,
  value varchar(max),
  created_at timestamp DEFAULT SYSDATE,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
DROP TABLE IF EXISTS "bit_test_def_1";
CREATE TABLE "bit_test_def_1" (
  "id" int4,
  "bit_value" bigint DEFAULT 1,
  "int_value" int4 DEFAULT 16,
  PRIMARY KEY ("id")
) DISTKEY("id") SORTKEY("id");
DELETE FROM "flydata_ctl_columns" WHERE table_name = 'bit_test_def_1';
INSERT INTO "flydata_ctl_columns" (table_name, column_name, src_data_type, ordinal_position) VALUES
('bit_test_def_1', 'id', 'int4(11)', 1),
('bit_test_def_1', 'bit_value', 'bit(1)', 2),
('bit_test_def_1', 'int_value', 'int4(11)', 3);
DELETE FROM "flydata_ctl_tables" WHERE table_name = 'bit_test_def_1';
INSERT INTO "flydata_ctl_tables" (table_name, attribute, value) VALUES
('bit_test_def_1', 'cs', 'UTF_8'),
('bit_test_def_1', 'revision', 1),
('bit_test_def_1', 'src_ddl', '#{"CREATE TABLE `bit_test_def_1` (
  `id` int(11) NOT NULL,
  `bit_value` bit(1) DEFAULT b\\'1\\',
  `int_value` int(11) DEFAULT x\\'10\\',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;".split("\n").join}');
EOT
        end
      end

      context 'with mysqldump_test_foreign_key.dump' do
        let(:dump_file_name) { 'mysqldump_test_foreign_key.dump' }

        it 'should return ddl' do
          expect(subject).to eq( <<EOT.strip )
CREATE TABLE IF NOT EXISTS "flydata_ctl_columns"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  column_name varchar(128) NOT NULL,
  src_data_type varchar(1024) NOT NULL,
  revision int NOT NULL DEFAULT 1,
  ordinal_position int NOT NULL,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
CREATE TABLE IF NOT EXISTS "flydata_ctl_tables"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  attribute varchar(128) NOT NULL,
  value varchar(max),
  created_at timestamp DEFAULT SYSDATE,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
DROP TABLE IF EXISTS "product_order";
CREATE TABLE "product_order" (
  "no" int4,
  "product_category" int4,
  "product_id" int4,
  "customer_id" int4,
  PRIMARY KEY ("no")
) DISTKEY("no") SORTKEY("no");
DELETE FROM "flydata_ctl_columns" WHERE table_name = 'product_order';
INSERT INTO "flydata_ctl_columns" (table_name, column_name, src_data_type, ordinal_position) VALUES
('product_order', 'no', 'int4(11)', 1),
('product_order', 'product_category', 'int4(11)', 2),
('product_order', 'product_id', 'int4(11)', 3),
('product_order', 'customer_id', 'int4(11)', 4);
DELETE FROM "flydata_ctl_tables" WHERE table_name = 'product_order';
INSERT INTO "flydata_ctl_tables" (table_name, attribute, value) VALUES
('product_order', 'cs', 'UTF_8'),
('product_order', 'revision', 1),
('product_order', 'src_ddl', '#{"CREATE TABLE `product_order` (
  `no` int(11) NOT NULL AUTO_INCREMENT,
  `product_category` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  PRIMARY KEY (`no`),
  KEY `product_category` (`product_category`,`product_id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `product_order_ibfk_1` FOREIGN KEY (`product_category`, `product_id`) REFERENCES `product` (`category`, `id`) ON UPDATE CASCADE,
  CONSTRAINT `product_order_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;".split("\n").join}');
EOT
        end
      end

      context 'with mysqldump_test_table_column_comment.dump' do
        let(:dump_file_name) { 'mysqldump_test_table_column_comment.dump' }

        it 'should return ddl' do
          expect(subject).to eq( <<EOT.strip )
CREATE TABLE IF NOT EXISTS "flydata_ctl_columns"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  column_name varchar(128) NOT NULL,
  src_data_type varchar(1024) NOT NULL,
  revision int NOT NULL DEFAULT 1,
  ordinal_position int NOT NULL,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
CREATE TABLE IF NOT EXISTS "flydata_ctl_tables"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  attribute varchar(128) NOT NULL,
  value varchar(max),
  created_at timestamp DEFAULT SYSDATE,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
DROP TABLE IF EXISTS "test_table_column_comment";
CREATE TABLE "test_table_column_comment" (
  "id" int4 DEFAULT '0',
  "value" varchar(max),
  PRIMARY KEY ("id")
) DISTKEY("id") SORTKEY("id");
COMMENT ON COLUMN "test_table_column_comment"."id"
  IS 'this is primary key';
DELETE FROM "flydata_ctl_columns" WHERE table_name = 'test_table_column_comment';
INSERT INTO "flydata_ctl_columns" (table_name, column_name, src_data_type, ordinal_position) VALUES
('test_table_column_comment', 'id', 'int4(11)', 1),
('test_table_column_comment', 'value', 'text', 2);
DELETE FROM "flydata_ctl_tables" WHERE table_name = 'test_table_column_comment';
INSERT INTO "flydata_ctl_tables" (table_name, attribute, value) VALUES
('test_table_column_comment', 'cs', 'UTF_8'),
('test_table_column_comment', 'revision', 1),
('test_table_column_comment', 'src_ddl', '#{"CREATE TABLE `test_table_column_comment` (
  `id` int(11) NOT NULL DEFAULT \\'0\\' COMMENT \\'this is primary key\\',
  `value` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=\\'column comment test\\';".split("\n").join}');
EOT
        end
      end

      context 'with mysqldump_test_table_enum.dump' do
        let(:dump_file_name) { 'mysqldump_test_table_enum.dump' }

        it 'should return ddl' do
          expect(subject).to eq( <<EOT.strip )
CREATE TABLE IF NOT EXISTS "flydata_ctl_columns"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  column_name varchar(128) NOT NULL,
  src_data_type varchar(1024) NOT NULL,
  revision int NOT NULL DEFAULT 1,
  ordinal_position int NOT NULL,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
CREATE TABLE IF NOT EXISTS "flydata_ctl_tables"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  attribute varchar(128) NOT NULL,
  value varchar(max),
  created_at timestamp DEFAULT SYSDATE,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
DROP TABLE IF EXISTS "test_table_enum";
CREATE TABLE "test_table_enum" (
  "id" int4,
  "enum_1" varchar encode bytedict DEFAULT NULL,
  "enum_2" varchar encode bytedict DEFAULT 'a',
  "enum_3" varchar encode bytedict,
  PRIMARY KEY ("id")
) DISTKEY("id") SORTKEY("id");
DELETE FROM "flydata_ctl_columns" WHERE table_name = 'test_table_enum';
INSERT INTO "flydata_ctl_columns" (table_name, column_name, src_data_type, ordinal_position) VALUES
('test_table_enum', 'id', 'int4(11)', 1),
('test_table_enum', 'enum_1', 'enum(\\'apple\\',\\'orange\\',\\'banana\\')', 2),
('test_table_enum', 'enum_2', 'enum(\\'a\\',\\'b\\',\\'c\\')', 3),
('test_table_enum', 'enum_3', 'enum(\\'e\\',\\'f\\',\\'g\\')', 4);
DELETE FROM "flydata_ctl_tables" WHERE table_name = 'test_table_enum';
INSERT INTO "flydata_ctl_tables" (table_name, attribute, value) VALUES
('test_table_enum', 'cs', 'UTF_8'),
('test_table_enum', 'revision', 1),
('test_table_enum', 'src_ddl', '#{"CREATE TABLE `test_table_enum` (
  `id` int(11) NOT NULL,
  `enum_1` enum(\\'apple\\',\\'orange\\',\\'banana\\') DEFAULT NULL,
  `enum_2` enum(\\'a\\',\\'b\\',\\'c\\') DEFAULT \\'a\\',
  `enum_3` enum(\\'e\\',\\'f\\',\\'g\\') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=\\'with enum column\\';".split("\n").join}');
EOT
        end
      end

      context 'with mysqldump_test_table_multi_pk.dump' do
        let(:dump_file_name) { 'mysqldump_test_table_multi_pk.dump' }

        it 'should return ddl' do
          expect(subject).to eq( <<EOT.strip )
CREATE TABLE IF NOT EXISTS "flydata_ctl_columns"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  column_name varchar(128) NOT NULL,
  src_data_type varchar(1024) NOT NULL,
  revision int NOT NULL DEFAULT 1,
  ordinal_position int NOT NULL,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
CREATE TABLE IF NOT EXISTS "flydata_ctl_tables"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  attribute varchar(128) NOT NULL,
  value varchar(max),
  created_at timestamp DEFAULT SYSDATE,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
DROP TABLE IF EXISTS "test_table_multi_pk";
CREATE TABLE "test_table_multi_pk" (
  "id1" int4 DEFAULT '0',
  "id2" int4 DEFAULT '0',
  "value" varchar(max),
  PRIMARY KEY ("id1","id2")
) DISTKEY("id1") SORTKEY("id1","id2");
DELETE FROM "flydata_ctl_columns" WHERE table_name = 'test_table_multi_pk';
INSERT INTO "flydata_ctl_columns" (table_name, column_name, src_data_type, ordinal_position) VALUES
('test_table_multi_pk', 'id1', 'int4(11)', 1),
('test_table_multi_pk', 'id2', 'int4(11)', 2),
('test_table_multi_pk', 'value', 'text', 3);
DELETE FROM "flydata_ctl_tables" WHERE table_name = 'test_table_multi_pk';
INSERT INTO "flydata_ctl_tables" (table_name, attribute, value) VALUES
('test_table_multi_pk', 'cs', 'UTF_8'),
('test_table_multi_pk', 'revision', 1),
('test_table_multi_pk', 'src_ddl', '#{"CREATE TABLE `test_table_multi_pk` (
  `id1` int(11) NOT NULL DEFAULT \\'0\\',
  `id2` int(11) NOT NULL DEFAULT \\'0\\',
  `value` text,
  PRIMARY KEY (`id1`,`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=\\'multiple primary key\\';".split("\n").join}');
EOT
        end
      end

      context 'with mysqldump_test_table_no_pk.dump' do
        let(:dump_file_name) { 'mysqldump_test_table_no_pk.dump' }

        it 'should return ddl' do
          expect{subject}.to raise_error(FlydataCore::TableDefError)
        end
      end

      context 'with mysqldump_test_unique_key.dump' do
        let(:dump_file_name) { 'mysqldump_test_unique_key.dump' }

        it 'should return ddl' do
          expect(subject).to eq( <<EOT.strip )
CREATE TABLE IF NOT EXISTS "flydata_ctl_columns"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  column_name varchar(128) NOT NULL,
  src_data_type varchar(1024) NOT NULL,
  revision int NOT NULL DEFAULT 1,
  ordinal_position int NOT NULL,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
CREATE TABLE IF NOT EXISTS "flydata_ctl_tables"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  attribute varchar(128) NOT NULL,
  value varchar(max),
  created_at timestamp DEFAULT SYSDATE,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
DROP TABLE IF EXISTS "sample1";
CREATE TABLE "sample1" (
  "id" int4,
  "title" varchar(768) DEFAULT NULL,
  "name" varchar(max),
  "num" int4 DEFAULT NULL,
  PRIMARY KEY ("id")
) DISTKEY("id") SORTKEY("id");
DELETE FROM "flydata_ctl_columns" WHERE table_name = 'sample1';
INSERT INTO "flydata_ctl_columns" (table_name, column_name, src_data_type, ordinal_position) VALUES
('sample1', 'id', 'int4(11)', 1),
('sample1', 'title', 'varchar(768)', 2),
('sample1', 'name', 'text', 3),
('sample1', 'num', 'int4(11)', 4);
DELETE FROM "flydata_ctl_tables" WHERE table_name = 'sample1';
INSERT INTO "flydata_ctl_tables" (table_name, attribute, value) VALUES
('sample1', 'cs', 'ISO_8859_1'),
('sample1', 'revision', 1),
('sample1', 'src_ddl', '#{"CREATE TABLE `sample1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(256) DEFAULT NULL,
  `name` text,
  `num` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;".split("\n").join}');
EOT
        end
      end

      context 'with mysqldump_test_unique_key2.dump' do
        let(:dump_file_name) { 'mysqldump_test_unique_key2.dump' }

        it 'should return ddl' do
          expect(subject).to eq( <<EOT.strip )
CREATE TABLE IF NOT EXISTS "flydata_ctl_columns"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  column_name varchar(128) NOT NULL,
  src_data_type varchar(1024) NOT NULL,
  revision int NOT NULL DEFAULT 1,
  ordinal_position int NOT NULL,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
CREATE TABLE IF NOT EXISTS "flydata_ctl_tables"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  attribute varchar(128) NOT NULL,
  value varchar(max),
  created_at timestamp DEFAULT SYSDATE,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
DROP TABLE IF EXISTS "sample1";
CREATE TABLE "sample1" (
  "id" int4,
  "title" varchar(768) DEFAULT NULL,
  "name" varchar(max),
  "num" int4 DEFAULT NULL,
  PRIMARY KEY ("id")
) DISTKEY("id") SORTKEY("id");
DELETE FROM "flydata_ctl_columns" WHERE table_name = 'sample1';
INSERT INTO "flydata_ctl_columns" (table_name, column_name, src_data_type, ordinal_position) VALUES
('sample1', 'id', 'int4(11)', 1),
('sample1', 'title', 'varchar(768)', 2),
('sample1', 'name', 'text', 3),
('sample1', 'num', 'int4(11)', 4);
DELETE FROM "flydata_ctl_tables" WHERE table_name = 'sample1';
INSERT INTO "flydata_ctl_tables" (table_name, attribute, value) VALUES
('sample1', 'cs', 'ISO_8859_1'),
('sample1', 'revision', 1),
('sample1', 'src_ddl', '#{"CREATE TABLE `sample1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(256) DEFAULT NULL,
  `name` text,
  `num` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`),
  UNIQUE KEY `index_num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;".split("\n").join}');
EOT
        end
      end

      context 'with mysqldump_test_unique_key3.dump' do
        let(:dump_file_name) { 'mysqldump_test_unique_key3.dump' }

        it 'should return ddl' do
          expect(subject).to eq( <<EOT.strip )
CREATE TABLE IF NOT EXISTS "flydata_ctl_columns"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  column_name varchar(128) NOT NULL,
  src_data_type varchar(1024) NOT NULL,
  revision int NOT NULL DEFAULT 1,
  ordinal_position int NOT NULL,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
CREATE TABLE IF NOT EXISTS "flydata_ctl_tables"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  attribute varchar(128) NOT NULL,
  value varchar(max),
  created_at timestamp DEFAULT SYSDATE,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
DROP TABLE IF EXISTS "invoice_items";
CREATE TABLE "invoice_items" (
  "id" int4,
  "app_id" int4,
  "subscription_id" int4,
  "overage_id" varchar(765),
  "stripe_invoice_item_id" varchar(765) DEFAULT NULL,
  "stripe_error" varchar(765) DEFAULT NULL,
  "synced_to_stripe" int2 DEFAULT '0',
  "description" varchar(max),
  "item_name" varchar(765),
  "item_cost" int4,
  "item_count" int4,
  "total_cost" int4,
  "created_at" timestamp DEFAULT NULL,
  "updated_at" timestamp DEFAULT NULL,
  "bill_ahead" int2 DEFAULT '0',
  "bill_ahead_resolved_at" timestamp DEFAULT NULL,
  "stripe_invoice_id" varchar(765) DEFAULT NULL,
  "is_vat" int2 DEFAULT '0',
  PRIMARY KEY ("id")
) DISTKEY("id") SORTKEY("id");
DELETE FROM "flydata_ctl_columns" WHERE table_name = 'invoice_items';
INSERT INTO "flydata_ctl_columns" (table_name, column_name, src_data_type, ordinal_position) VALUES
('invoice_items', 'id', 'int4(11)', 1),
('invoice_items', 'app_id', 'int4(11)', 2),
('invoice_items', 'subscription_id', 'int4(11)', 3),
('invoice_items', 'overage_id', 'varchar(765)', 4),
('invoice_items', 'stripe_invoice_item_id', 'varchar(765)', 5),
('invoice_items', 'stripe_error', 'varchar(765)', 6),
('invoice_items', 'synced_to_stripe', 'int1(1)', 7),
('invoice_items', 'description', 'text', 8),
('invoice_items', 'item_name', 'varchar(765)', 9),
('invoice_items', 'item_cost', 'int4(11)', 10),
('invoice_items', 'item_count', 'int4(11)', 11),
('invoice_items', 'total_cost', 'int4(11)', 12),
('invoice_items', 'created_at', 'datetime', 13),
('invoice_items', 'updated_at', 'datetime', 14),
('invoice_items', 'bill_ahead', 'int1(1)', 15),
('invoice_items', 'bill_ahead_resolved_at', 'datetime', 16),
('invoice_items', 'stripe_invoice_id', 'varchar(765)', 17),
('invoice_items', 'is_vat', 'int1(1)', 18);
DELETE FROM "flydata_ctl_tables" WHERE table_name = 'invoice_items';
INSERT INTO "flydata_ctl_tables" (table_name, attribute, value) VALUES
('invoice_items', 'cs', 'UTF_8'),
('invoice_items', 'revision', 1),
('invoice_items', 'src_ddl', '#{"CREATE TABLE `invoice_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `app_id` int(11) NOT NULL,
  `subscription_id` int(11) NOT NULL,
  `overage_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `stripe_invoice_item_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `stripe_error` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `synced_to_stripe` tinyint(1) NOT NULL DEFAULT \\'0\\',
  `description` text COLLATE utf8mb4_unicode_ci,
  `item_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `item_cost` int(11) NOT NULL,
  `item_count` int(11) NOT NULL,
  `total_cost` int(11) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `bill_ahead` tinyint(1) DEFAULT \\'0\\',
  `bill_ahead_resolved_at` datetime DEFAULT NULL,
  `stripe_invoice_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_vat` tinyint(1) NOT NULL DEFAULT \\'0\\',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_invoice_items_on_app_id_and_overage_id_and_item_name` (`app_id`,`overage_id`(32),`item_name`(32)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=16654 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;".split("\n").join}');
EOT
        end
      end

      context 'with mysqldump_test_unsigned.dump' do
        let(:dump_file_name) { 'mysqldump_test_unsigned.dump' }

        it 'should return ddl' do
          expect(subject).to eq( <<EOT.strip )
CREATE TABLE IF NOT EXISTS "flydata_ctl_columns"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  column_name varchar(128) NOT NULL,
  src_data_type varchar(1024) NOT NULL,
  revision int NOT NULL DEFAULT 1,
  ordinal_position int NOT NULL,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
CREATE TABLE IF NOT EXISTS "flydata_ctl_tables"(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  attribute varchar(128) NOT NULL,
  value varchar(max),
  created_at timestamp DEFAULT SYSDATE,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
DROP TABLE IF EXISTS "zerofill_table";
CREATE TABLE "zerofill_table" (
  "id" int4,
  "value_int" int8 DEFAULT NULL,
  "value_float" float4 DEFAULT NULL,
  "value_dec" numeric(10,2) DEFAULT NULL,
  "value_double" float8 DEFAULT NULL,
  "name" varchar(768) DEFAULT NULL,
  "value_small_int" int4 DEFAULT NULL,
  PRIMARY KEY ("id")
) DISTKEY("id") SORTKEY("id");
DELETE FROM "flydata_ctl_columns" WHERE table_name = 'zerofill_table';
INSERT INTO "flydata_ctl_columns" (table_name, column_name, src_data_type, ordinal_position) VALUES
('zerofill_table', 'id', 'int4(11)', 1),
('zerofill_table', 'value_int', 'int4(10) unsigned', 2),
('zerofill_table', 'value_float', 'float4 unsigned', 3),
('zerofill_table', 'value_dec', 'numeric(10,2) unsigned', 4),
('zerofill_table', 'value_double', 'float8 unsigned', 5),
('zerofill_table', 'name', 'varchar(768)', 6),
('zerofill_table', 'value_small_int', 'int2(5) unsigned', 7);
DELETE FROM "flydata_ctl_tables" WHERE table_name = 'zerofill_table';
INSERT INTO "flydata_ctl_tables" (table_name, attribute, value) VALUES
('zerofill_table', 'cs', 'ISO_8859_1'),
('zerofill_table', 'revision', 1),
('zerofill_table', 'src_ddl', '#{"CREATE TABLE `zerofill_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value_int` int(10) unsigned zerofill DEFAULT NULL,
  `value_float` float unsigned zerofill DEFAULT NULL,
  `value_dec` decimal(10,2) unsigned zerofill DEFAULT NULL,
  `value_double` double unsigned zerofill DEFAULT NULL,
  `name` varchar(256) DEFAULT NULL,
  `value_small_int` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;".split("\n").join}');
EOT
        end
      end
    end
  end
end