require 'spec_helper'
require 'flydata/table_def'

module Flydata
module TableDef

describe RedshiftTableDef do

  describe '.from_flydata_tabledef' do
    let(:flydata_tabledef) { {
        table_name: "test_table",
        columns: [
          { column: "id", type: "int4(11)", not_null: true, primary_key: true },
          { column: "age", type: "int4(11) unsigned" },
          { column: "value", type: "text" },
        ],
        default_charset: "utf8;"} }
    let(:option) { { flydata_ctl_table: false } }

    subject { described_class.from_flydata_tabledef(flydata_tabledef, option) }

    context 'with simple flydatadef' do
      it 'should return ddl' do
        expect(subject).to eq( <<EOT.strip )
DROP TABLE "test_table";
CREATE TABLE "test_table" (
  "id" int4 NOT NULL,
  "age" int8,
  "value" varchar(max),
  PRIMARY KEY (id)
);
DELETE FROM "flydata_ctl_columns" WHERE table_name = 'test_table';
INSERT INTO "flydata_ctl_columns" (table_name, column_name, src_data_type, ordinal_position) VALUES
('test_table', 'id', 'int4(11)', 1),
('test_table', 'age', 'int4(11) unsigned', 2),
('test_table', 'value', 'text', 3);
EOT
      end
    end

    context 'with flydata_ctl_columns option true' do
      let(:option) { { flydata_ctl_columns: true } }

      it 'should return ddl including flydata_ctl_columns creation' do
        expect(subject).to eq( <<EOT.strip )
CREATE TABLE "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);
DROP TABLE "test_table";
CREATE TABLE "test_table" (
  "id" int4 NOT NULL,
  "age" int8,
  "value" varchar(max),
  PRIMARY KEY (id)
);
DELETE FROM "flydata_ctl_columns" WHERE table_name = 'test_table';
INSERT INTO "flydata_ctl_columns" (table_name, column_name, src_data_type, ordinal_position) VALUES
('test_table', 'id', 'int4(11)', 1),
('test_table', 'age', 'int4(11) unsigned', 2),
('test_table', 'value', 'text', 3);
EOT
      end
    end

    context 'with flydatadef with comment' do
      let(:flydata_tabledef) { {
        table_name: "test_table",
        columns: [
          { column: "id", type: "int4(11)", not_null: true, primary_key: true },
          { column: "value", type: "text" },
          { column: "cmnt", type: "text", comment: "helloworld" },
        ],
        default_charset: "utf8;"} }

      it 'should add comment creation ddl' do
        expect(subject).to eq( <<EOT.strip )
DROP TABLE "test_table";
CREATE TABLE "test_table" (
  "id" int4 NOT NULL,
  "value" varchar(max),
  "cmnt" varchar(max),
  PRIMARY KEY (id)
);
COMMENT ON COLUMN "test_table"."cmnt"
  IS 'helloworld';
DELETE FROM "flydata_ctl_columns" WHERE table_name = 'test_table';
INSERT INTO "flydata_ctl_columns" (table_name, column_name, src_data_type, ordinal_position) VALUES
('test_table', 'id', 'int4(11)', 1),
('test_table', 'value', 'text', 2),
('test_table', 'cmnt', 'text', 3);
EOT
      end
    end

    context 'with schema_name' do
      let(:flydata_tabledef) { {
        table_name: "test_table",
        columns: [
          { column: "id", type: "int4(11)", not_null: true, primary_key: true },
          { column: "value", type: "text" },
          { column: "cmnt", type: "text", comment: "helloworld" },
        ],
        default_charset: "utf8;"} }
      let(:option) { { flydata_ctl_table: true, schema_name: 'test_schema' } }

      it 'should preappend schema name to table name' do
        expect(subject).to eq( <<EOT.strip )
CREATE TABLE "test_schema"."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);
DROP TABLE "test_schema"."test_table";
CREATE TABLE "test_schema"."test_table" (
  "id" int4 NOT NULL,
  "value" varchar(max),
  "cmnt" varchar(max),
  PRIMARY KEY (id)
);
COMMENT ON COLUMN "test_schema"."test_table"."cmnt"
  IS 'helloworld';
DELETE FROM "test_schema"."flydata_ctl_columns" WHERE table_name = 'test_table';
INSERT INTO "test_schema"."flydata_ctl_columns" (table_name, column_name, src_data_type, ordinal_position) VALUES
('test_table', 'id', 'int4(11)', 1),
('test_table', 'value', 'text', 2),
('test_table', 'cmnt', 'text', 3);
EOT
      end
    end
  end

  describe '.column_def_sql' do
    subject { described_class.column_def_sql(column, opt) }
    let(:column) { {} }

    let(:no_default_sql) { "" }
    shared_examples "default values" do |*examples|
      context "default nil" do
        before do
          column[:default] = nil
        end
        let(:default_sql) { " DEFAULT NULL" }
        it_behaves_like *examples
      end
      context "no default" do
        let(:default_sql) { no_default_sql }
        it_behaves_like *examples
      end
      context "defalut value" do
        before do
          column[:default] = default_value
        end
        let(:default_sql) { " DEFAULT #{default_value_sql}" }
        it_behaves_like *examples
      end
    end

    shared_examples "not null values" do |*examples|
      context "without not null" do
        let(:not_null_sql) { "" }
        it_behaves_like *examples
      end
      context "with not null false" do
        before do
          column[:not_null] = false
        end
        let(:not_null_sql) { "" }
        it_behaves_like *examples
      end
      context "with not null true" do
        before do
          column[:not_null] = true
        end
        let(:not_null_sql) { " NOT NULL" }
        it_behaves_like *examples
      end
    end

    shared_examples "test generated Redshift SQL for column" do
      it do
         expect(subject).to eq(expected_query)
      end
    end

    shared_examples "test column types" do |*examples|
      context 'with varchar column def' do
        context 'when size is smaller than max size' do
          let(:default_value) { "something" }
          let(:default_value_sql) { "'#{default_value}'" }
          let(:not_null_default_sql) { " DEFAULT ''" }
          before do
            column[:column] = "col_char"
            column[:type] = "varchar(18)"
          end
          let(:type_sql) { %Q|"col_char" varchar(18)| }

          it_behaves_like *examples
        end

        context 'when size exceeds varchar max size' do
          let(:default_value) { "something" }
          let(:default_value_sql) { "'#{default_value}'" }
          let(:not_null_default_sql) { " DEFAULT ''" }
          before do
            column[:column] = "col_char"
            column[:type] = "varchar(1000000000)"
          end
          let(:type_sql) { %Q|"col_char" varchar(65535)| }

          it_behaves_like *examples
        end

        context 'when size is max size' do
          let(:default_value) { "something" }
          let(:default_value_sql) { "'#{default_value}'" }
          let(:not_null_default_sql) { " DEFAULT ''" }
          before do
            column[:column] = "col_char"
            column[:type] = "varchar(max)"
          end
          let(:type_sql) { %Q|"col_char" varchar(max)| }

          it_behaves_like *examples
        end
      end
      
      context 'with medium blob column def' do
        context 'when size exceeds max size' do
          let(:default_value) { "something" }
          let(:default_value_sql) { "'#{default_value}'" }
          let(:not_null_default_sql) { " DEFAULT ''" }
          before do
            column[:column] = "col_medium_blob"
            column[:type] = "varbinary(16777215)"
          end
          let(:type_sql) { %Q|"col_medium_blob" varchar(65535)| }

          it_behaves_like *examples
        end    
      end
      
      context 'with unsigned column def' do
        let(:default_value) { 4 }
        let(:default_value_sql) { "'#{default_value}'" }
        let(:not_null_default_sql) { " DEFAULT '0'" }
        before do
          column[:column] = "value_small_int"
          column[:type] = "int2(5) unsigned"
        end
        let(:type_sql) { %Q|"value_small_int" int4| }

        it_behaves_like *examples
      end

      context 'with datetime column def' do
        let(:default_value) { '2014-10-17 22:22:22' }
        let(:default_value_sql) { "'#{default_value}'" }
        let(:not_null_default_sql) { " DEFAULT '0000-01-01'" }
        before do
          column[:column] = "value_datetime"
          column[:type] = "datetime"
        end
        let(:type_sql) { %Q|"value_datetime" timestamp| }

        it_behaves_like *examples
      end

      context 'with decimal column def' do
        context 'when precision exceeds max allowed' do
          let(:default_value) { 4 }
          let(:default_value_sql) { "'#{default_value}'" }
          let(:not_null_default_sql) { " DEFAULT '0'" }
          before do
            column[:column] = "value"
            column[:type] = "numeric(65,30)"
          end
          let(:type_sql) { %Q|"value" numeric(38,30)| }

          it_behaves_like *examples
        end
        context 'when scale exceeds max allowed' do #not possible
          let(:default_value) { 4 }
          let(:default_value_sql) { "'#{default_value}'" }
          let(:not_null_default_sql) { " DEFAULT '0'" }
          before do
            column[:column] = "value"
            column[:type] = "numeric(45,44)"
          end
          let(:type_sql) { %Q|"value" numeric(38,37)| }

          it_behaves_like *examples
        end

        context 'when precision and scale exceeds max allowed (for unsigned)' do
          let(:default_value) { 4 }
          let(:default_value_sql) { "'#{default_value}'" }
          let(:not_null_default_sql) { " DEFAULT '0'" }
          before do
            column[:column] = "value"
            column[:type] = "numeric(65,44) unsigned"
          end
          let(:type_sql) {%Q|"value" numeric(38,37)| }

          it_behaves_like *examples
        end

        context 'when precision and scale are within limit (for unsigned)' do
          let(:default_value) { 4 }
          let(:default_value_sql) { "'#{default_value}'" }
          let(:not_null_default_sql) { " DEFAULT '0'" }
          before do
            column[:column] = "value"
            column[:type] = "numeric(37,29) unsigned"
          end
          let(:type_sql) { %Q|"value" numeric(37,29)| }

          it_behaves_like *examples
        end
      end
    end

    context 'for create table' do
      let(:opt) { {} }
      let(:expected_query) { %Q|  #{type_sql}#{not_null_sql}#{default_sql}| }
      let(:no_default_sql) { "" }

      it_behaves_like "test column types", "not null values", "default values", "test generated Redshift SQL for column"
    end

    context 'for alter table' do
      let(:opt) { { for: :alter_table } }

      context "without not null" do
        let(:expected_query) { %Q|  #{type_sql}#{default_sql}| }
        let(:no_default_sql) { "" }
    
        it_behaves_like "test column types", "default values", "test generated Redshift SQL for column"
      end
      context "with not null false" do
        let(:expected_query) { %Q|  #{type_sql}#{default_sql}| }
        before do
          column[:not_null] = false
        end
        let(:no_default_sql) { "" }
    
        it_behaves_like "test column types", "default values", "test generated Redshift SQL for column"
      end
      context "with not null true" do
        let(:expected_query) { %Q|  #{type_sql} NOT NULL#{default_sql}| }
        before do
          column[:not_null] = true
        end
        let(:no_default_sql) { not_null_default_sql }
    
        it_behaves_like "test column types", "default values", "test generated Redshift SQL for column"
      end
    end
  end

end

end
end