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 NOT NULL, "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 NOT NULL 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 NOT NULL, "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 NOT NULL, "product_category" int4 NOT NULL, "product_id" int4 NOT NULL, "customer_id" int4 NOT NULL, 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 NOT NULL 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 NOT NULL, "enum_1" varchar encode bytedict DEFAULT NULL, "enum_2" varchar encode bytedict DEFAULT 'a', "enum_3" varchar encode bytedict NOT NULL, 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 NOT NULL DEFAULT '0', "id2" int4 NOT NULL 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 NOT NULL, "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 NOT NULL, "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 NOT NULL, "app_id" int4 NOT NULL, "subscription_id" int4 NOT NULL, "overage_id" varchar(765) NOT NULL, "stripe_invoice_item_id" varchar(765) DEFAULT NULL, "stripe_error" varchar(765) DEFAULT NULL, "synced_to_stripe" int2 NOT NULL DEFAULT '0', "description" varchar(max), "item_name" varchar(765) NOT NULL, "item_cost" int4 NOT NULL, "item_count" int4 NOT NULL, "total_cost" int4 NOT NULL, "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 NOT NULL 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 NOT NULL, "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