Class: Mkxms::Mssql::AdoptionScriptWriter::TableAdoptionChecks

Inherits:
IndentedStringBuilder show all
Extended by:
SqlStringManipulators
Includes:
SqlStringManipulators
Defined in:
lib/mkxms/mssql/adoption_script_writer.rb

Constant Summary collapse

NON_ANSI_PADDABLE_TYPES = 
%w[char varchar binary varbinary]
UNICODE_CHAR_TYPES = 
%w[nchar nvarchar]

Constants included from SqlStringManipulators

SqlStringManipulators::MSSQL

Constants inherited from IndentedStringBuilder

IndentedStringBuilder::NAMED_SUBSTITUTIONS

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from SqlStringManipulators

bit_test, boolean_desc, dedent, stresc, strlit, unquoted_identifier

Methods inherited from IndentedStringBuilder

dsl, #dsl, #each, #indented, #puts, #to_s

Constructor Details

#initialize(table, error_sql_proc) ⇒ TableAdoptionChecks

Returns a new instance of TableAdoptionChecks.



593
594
595
596
597
598
599
600
601
602
603
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 593

def initialize(table, error_sql_proc)
  super()
  
  @table = table
  @schema_name_literal = strlit(unquoted_identifier table.schema)
  @table_name_literal = strlit(unquoted_identifier table.name)
  @table_id = [table.schema, table.name].join('.')
  @error_sql_proc = error_sql_proc
  
  add_table_tests
end

Instance Attribute Details

#schema_name_literal ⇒ Object (readonly)

Returns the value of attribute schema_name_literal.



605
606
607
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 605

def schema_name_literal
  @schema_name_literal
end

#table ⇒ Object (readonly)

Returns the value of attribute table.



605
606
607
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 605

def table
  @table
end

#table_id ⇒ Object (readonly)

Returns the value of attribute table_id.



605
606
607
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 605

def table_id
  @table_id
end

#table_name_literal ⇒ Object (readonly)

Returns the value of attribute table_name_literal.



605
606
607
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 605

def table_name_literal
  @table_name_literal
end

Instance Method Details

#add_column_properties_test(column) ⇒ Object 



713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 713

def add_column_properties_test(column)
  conditions = []
  if column.computed_expression
    mismatch_message = "does not have the expected definition"
    
    conditions << %Q{c.is_computed = 1}
    conditions << compose_sql {
      puts "EXISTS (SELECT * FROM sys.computed_columns cc WHERE %s)" do
        puts "cc.object_id = c.object_id"
        puts "AND cc.column_id = c.column_id"
        puts "AND cc.definition = %s" do
          puts strlit(column.computed_expression)
        end
        puts "AND %s" do
          puts(bit_test "cc.is_persisted", column.persisted?)
        end
      end
    }
  else
    type_str = [].tap {|parts| column.each_type_part {|part| parts << part}}.join(' ')
    mismatch_message = "is not #{type_str}"
    
    conditions << "ct.name = %s" % [strlit(unquoted_identifier column.type_info[:type])]
    type_schema = column.type_info[:type_schema] || 'sys'
    col_type_is_sys_type = unquoted_identifier(type_schema).downcase == 'sys'
    comparable_col_type = unquoted_identifier(column.type_info[:type]).downcase
    conditions << compose_sql {
      puts "EXISTS (SELECT * FROM sys.schemas cts WHERE %s)" do
        puts "cts.schema_id = ct.schema_id"
        puts "AND cts.name = #{strlit(unquoted_identifier type_schema)}"
      end
    }
    if precision = column.type_info[:precision]
      conditions << %Q{c.precision = #{precision}}
    end
    if scale = column.type_info[:scale]
      conditions << %Q{c.scale = #{scale}}
    end
    if capacity = column.type_info[:capacity]
      conditions << (if capacity == 'max'
        %Q{c.max_length = -1}
      elsif col_type_is_sys_type && %w[nchar nvarchar].include?(comparable_col_type)
        %Q{c.max_length = #{capacity.to_i * 2}}
      else
        %Q{c.max_length = #{capacity}}
      end)
    end
    conditions << %Q{c.collation_name = #{strlit column.collation}} if column.collation
    conditions << bit_test("c.is_identity", column.identity?)
    conditions << bit_test("c.is_rowguidcol", column.rowguid?)
    conditions << bit_test("c.is_filestream", column.filestream?)
    conditions << bit_test("c.is_nullable", column.nullable?)
    if col_type_is_sys_type && NON_ANSI_PADDABLE_TYPES.include?(comparable_col_type)
      conditions << bit_test("c.is_ansi_padded", true)
    end
  end
  
  dsl {
    puts "IF NOT EXISTS (%s)" do
      puts dedent %Q{
        SELECT * FROM sys.columns c
        INNER JOIN sys.types ct ON c.user_type_id = ct.user_type_id
        WHERE c.object_id = @column_object
        AND c.column_id = @column_id
      }
      conditions.each {|c| puts "AND " + c, :sub => nil}
    end
    puts "BEGIN".."END" do
      puts error_sql "Column #{column.name} of #{table_id} #{mismatch_message}"
    end
  }
end

#add_column_tests(column) ⇒ Object 



671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 671

def add_column_tests(column)
  column_name_literal = strlit(unquoted_identifier column.name)
  
  dsl {
    puts "IF NOT EXISTS (%s)" do
      puts dedent %Q{
        SELECT * FROM sys.columns c
        WHERE c.object_id = @column_object
        AND c.column_id = @column_id
        AND c.name = #{column_name_literal}
      }
    end
    puts "BEGIN"
    indented {
      puts dedent %Q{
        SET @column_id = (
          SELECT c.column_id FROM sys.columns c
          WHERE c.object_id = @column_object
          AND c.name = #{column_name_literal}
        );
      }
      puts "IF @column_id IS NULL"
      puts "BEGIN"
      indented {
        puts error_sql "Column #{column.name} not found in #{table_id}."
      }
      puts "END ELSE BEGIN"
      indented {
        puts error_sql "Column #{column.name} not found in expected position in #{table_id}."
      }
      puts "END"
    }
    puts "END"
    puts "IF @column_id IS NOT NULL"
    puts "BEGIN".."END" do
      add_column_properties_test(column)
    end
  }
end

#add_table_tests ⇒ Object 



611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 611

def add_table_tests
  dsl {
    puts "IF NOT EXISTS (%s)" do
      puts dedent %Q{
        SELECT * FROM sys.tables t
        INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
        WHERE t.name = #{table_name_literal}
        AND s.name = #{schema_name_literal}
      }
    end
    puts "BEGIN"
    indented {
      puts error_sql "Table #{table_id} does not exist."
    }
    puts "END ELSE IF NOT EXISTS (%s)" do
      puts dedent %Q{
        SELECT * FROM sys.tables t
        INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
        LEFT JOIN sys.database_principals r ON t.principal_id = r.principal_id
        WHERE t.name = #{table_name_literal}
        AND s.name = #{schema_name_literal}
        AND r.name #{table.owner ? "= " + strlit(unquoted_identifier(table.owner)) : "IS NULL"}
      }
    end
    puts "BEGIN"
    indented {
      puts error_sql(
        if table.owner
          "Table #{table_id} is not owned (explicitly) by #{table.owner}."
        else
          "Table #{table_id} is specified as other than the schema owner."
        end
      )
    }
    puts "END"
    puts
  }
  QueryCursor.new(
    dedent(%Q{
      SELECT c.object_id, c.column_id
      FROM sys.columns c
      INNER JOIN sys.tables t ON c.object_id = t.object_id
      INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
      WHERE t.name = #{table_name_literal}
      AND s.name = #{schema_name_literal}
      ORDER BY c.column_id;
    }),
    "@column_object INT, @column_id INT",
    output_to: self
  ).expectations(
    on_extra: ->{puts error_sql "Table #{table_id} has one or more unexpected columns."},
  ) do |test|
    table.columns.each do |column|
      test.row(
        on_missing: ->{puts error_sql "Column #{column.name} not found where expected in #{table_id}."},
      ) {add_column_tests(column)}
    end
  end
end

#compose_sql(&blk) ⇒ Object 



786
787
788
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 786

def compose_sql(&blk)
  IndentedStringBuilder.dsl(&blk)
end

#error_sql(s) ⇒ Object 



607
608
609
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 607

def error_sql(s)
  @error_sql_proc.call(s)
end