class Mkxms::Mssql::AdoptionScriptWriter::IndexAdoptionChecks

Attributes

index[R]
index_id[R]

Public Class Methods

new(index, error_sql_proc) click to toggle source
Calls superclass method
# File lib/mkxms/mssql/adoption_script_writer.rb, line 1566
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

Public Instance Methods

add_index_property_checks() click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 1604
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.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)}"
          puts "BEGIN"
          indented {
            puts error_sql "Expected #{column.name} to be sorted #{column.direction} in #{index_id}."
          }
          puts "END"
        }
      end
    end
    
    # Included columns
    unless (included_column_names = index.included_columns.map {|c| c.name}).empty?
      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.column_id = c.column_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
    end
  }
  
  add_spatial_property_checks(index) if index.spatial_index_geometry
end
error_sql(s) click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 1600
def error_sql(s)
  @error_sql_proc.call(s)
end
index_property_check(expectation, expectation_desc) click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 1667
def index_property_check(expectation, expectation_desc)
  %Q{
    IF NOT EXISTS (
      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) click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 1681
def property_verification(f, v, d)
  index_property_check(bit_test(f, v), boolean_desc(v, d))
end