Class: Mkxms::Mssql::AdoptionScriptWriter::IndexAdoptionChecks

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

Constant Summary

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(index, error_sql_proc) ⇒ IndexAdoptionChecks 



940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 940

def initialize(index, error_sql_proc)
  super()
  
  @index = index
  @error_sql_proc = error_sql_proc
  
  @index_id = "index #{@index.name} on #{@index.qualified_relation}"
  
  dsl {
    puts "DECLARE @relation_id INT, @index_id INT;"
    puts dedent %Q{
      SELECT @relation_id = i.object_id, @index_id = i.index_id
      FROM sys.indexes i
      JOIN sys.objects rel ON i.object_id = rel.object_id
      JOIN sys.schemas s ON rel.schema_id = s.schema_id
      WHERE s.name = #{strlit(unquoted_identifier index.schema)}
      AND rel.name = #{strlit(unquoted_identifier index.relation)}
      AND i.name = #{strlit(unquoted_identifier index.name)}
    }
    puts "IF @index_id IS NULL"
    puts "BEGIN"
    indented {
      puts error_sql "#{index_id.capitalize} does not exist."
    }
    puts "END ELSE BEGIN"
    indented {
      add_index_property_checks
    }
    puts "END"
  }
end

Instance Attribute Details

#index ⇒ Object (readonly)

Returns the value of attribute index.



972
973
974
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 972

def index
  @index
end

#index_id ⇒ Object (readonly)

Returns the value of attribute index_id.



972
973
974
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 972

def index_id
  @index_id
end

Instance Method Details

#add_index_property_checks ⇒ Object 



978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 978

def add_index_property_checks
  dsl {
    puts property_verification("is_unique", index.unique?, "be unique")
    puts property_verification("ignore_dup_key", index.ignore_duplicates?, "ignore duplicate keys")
    
    # Key columns
    QueryCursor.new(
      dedent(%Q{
        SELECT c.column_name, ic.is_descending_key
        FROM sys.index_columns ic
        JOIN sys.columns c 
          ON ic.object_id = c.object_id 
          AND ic.column_id = c.column_id
        WHERE ic.object_id = @relation_id
        AND ic.index_id = @index_id
        AND ic.key_ordinal >= 1
        ORDER BY ic.key_ordinal
      }),
      "@column_name SYSNAME, @is_sorted_descending BIT",
      output_to: self
    ).expectations(
      on_extra: ->{puts error_sql "#{index_id.capitalize} has one or more unexpected key columns."}
    ) do |test|
      index.columns.each.with_index do |column, i|
        test.row(
          on_missing: ->{puts error_sql "#{index_id.capitalize} is missing expected column #{column.name}."}
        ) {
          puts "IF QUOTENAME(@column_name) <> #{strlit column.name}"
          puts "BEGIN"
          indented {
            puts error_sql "Expected #{column.name} as column #{i + 1} in #{index_id}."
          }
          puts "END ELSE IF #{bit_test('@is_sorted_descending', column.direction == :descending)}"
          indented {
            puts error_sql "Expected #{column.name} to be sorted #{column.direction} in #{index_id}."
          }
          puts "END;"
        }
      end
    end
    
    # Included columns
    included_column_names = index.included_columns.map {|c| c.name}
    puts "IF (%s) < #{included_column_names.length}" do
      puts dedent %Q{
        SELECT COUNT(*) FROM sys.index_columns ic
        JOIN sys.columns c ON ic.object_id = c.object_id AND ic.index_id = c.index_id
        WHERE ic.object_id = @relation_id
        AND ic.index_id = @index_id
        AND ic.key_ordinal = 0
        AND QUOTENAME(c.name) IN (#{included_column_names.map {|s| strlit s}.join(', ')})
      }
    end
    puts "BEGIN".."END" do
      puts error_sql "#{index_id.capitalize} is missing one or more expected included columns."
    end
  }
  
  add_spatial_property_checks(index) if index.spatial_index_geometry
end

#error_sql(s) ⇒ Object 



974
975
976
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 974

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

#index_property_check(expectation, expectation_desc) ⇒ Object 



1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1039

def index_property_check(expectation, expectation_desc)
  %Q{
    IF NOT EXIST (
      SELECT * FROM sys.indexes i
      WHERE i.object_id = @relation_id
      AND i.index_id = @index_id
      AND i.#{expectation}
    )
    BEGIN
      #{error_sql "#{@index_id.capitalize} should #{expectation_desc}."}
    END;
  }.strip.gsub(/\s+/, ' ')
end

#property_verification(f, v, d) ⇒ Object 



1053
1054
1055
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1053

def property_verification(f, v, d)
  index_property_check(bit_test(f, v), boolean_desc(v, d))
end