class Mkxms::Mssql::AdoptionScriptWriter::KeylikeConstraintAdoptionChecks

Attributes

cnstr[R]
cnstr_id[R]
constraint_type[R]

Public Class Methods

new(cnstr, error_sql_proc) click to toggle source
Calls superclass method
# File lib/mkxms/mssql/adoption_script_writer.rb, line 840
def initialize(cnstr, error_sql_proc)
  super()
  
  @cnstr = cnstr
  @error_sql_proc = error_sql_proc
  @constraint_type = cnstr.sql_constraint_type.downcase
  @cnstr_id = (
    "#{constraint_type} constraint%s on #{cnstr.qualified_table}" % [
      cnstr.name ? " " + cnstr.name : ''
    ]
  )
  
  if cnstr.name
    add_named_constraint_tests
  else
    add_unnamed_constraint_tests
  end
end

Public Instance Methods

add_column_sequence_test(index_column) { |"Column #{name} not found where expected in #{cnstr_id}."| ... } click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 1012
def add_column_sequence_test(index_column)
  dsl {
    puts %Q{
      FETCH NEXT FROM column_cursor INTO @column_name, @column_sorted_descending;
      IF @@FETCH_STATUS <> 0
    }
    puts "BEGIN"
    indented {
      yield "Column #{index_column.name} not found where expected in #{cnstr_id}."
    }
    puts "END ELSE IF NOT (%s)" do
      puts "@column_name = %s" do
        puts strlit(unquoted_identifier index_column.name)
      end
    end
    puts "BEGIN"
    indented {
      yield "Other column found where #{index_column.name} expected in #{cnstr_id}."
    }
    puts "END ELSE IF NOT (%s)" do
      puts bit_test("@column_sorted_descending", index_column.direction == :descending)
    end
    puts "BEGIN"
    indented {
      yield "Column #{index_column.name} should be sorted #{index_column.direction} in #{cnstr_id}."
    }
    puts "END"
  }
end
add_named_constraint_tests() click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 865
def add_named_constraint_tests
  dsl {
    puts "IF NOT EXISTS (%s)" do
      puts dedent %Q{
        SELECT * FROM sys.key_constraints kc
        INNER JOIN sys.schemas s ON kc.schema_id = s.schema_id
        INNER JOIN sys.tables t ON kc.parent_object_id = t.object_id
        INNER JOIN sys.indexes i ON kc.parent_object_id = i.object_id AND kc.unique_index_id = i.index_id
      }
      puts "WHERE s.name = %s" do
        puts strlit(unquoted_identifier cnstr.schema)
      end
      puts "AND t.name = %s" do
        puts strlit(unquoted_identifier cnstr.table)
      end
      puts "AND kc.name = %s" do
        puts strlit(unquoted_identifier cnstr.name)
      end
    end
    puts "BEGIN"
    indented {
      puts error_sql "#{cnstr_id.capitalize} does not exist."
    }
    puts "END ELSE BEGIN"
    indented {
      # Check that this constraint covers the correct fields, noting
      # that the constraint doesn't exist if cnstr.name.nil? or that
      # it doesn't have the expected fields, otherwise.
      declare_column_sequence_cursor_with_conditions {
        puts dedent %Q{
          INNER JOIN sys.schemas s ON kc.schema_id = s.schema_id
          INNER JOIN sys.tables t ON kc.parent_object_id = t.object_id
        }
        puts "WHERE s.name = %s" do
          puts strlit(unquoted_identifier cnstr.schema)
        end
        puts "AND t.name = %s" do
          puts strlit(unquoted_identifier cnstr.table)
        end
        puts "AND kc.name = %s" do
          puts strlit(unquoted_identifier cnstr.name)
        end
      }
      
      cnstr.columns.each do |index_column|
        add_column_sequence_test(index_column) do |error_message|
          puts error_sql error_message
        end
      end
      
      check_column_sequence_end
    }
    puts "END"
  }
end
add_unnamed_constraint_tests() click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 921
def add_unnamed_constraint_tests
  dsl {
    puts dedent %Q{
      DECLARE @constraint_id INT;
      
      DECLARE constraint_cursor CURSOR FOR
      SELECT kc.object_id
      FROM sys.key_constraints kc
      INNER JOIN sys.schemas s ON kc.schema_id = s.schema_id
      INNER JOIN sys.tables t ON kc.parent_object_id = t.object_id
    }
    puts "WHERE s.name = %s" do
      puts strlit(unquoted_identifier cnstr.schema)
    end
    puts "AND t.name = %s" do
      puts strlit(unquoted_identifier cnstr.table)
    end
    puts ";"
    puts "OPEN constraint_cursor;"
    
    puts dedent %Q{
      DECLARE @constraint_found BIT, @constraint_match_error BIT;
      SET @constraint_found = 0;
      FETCH NEXT FROM constraint_cursor INTO @constraint_id;
      WHILE @@FETCH_STATUS = 0 AND @constraint_found = 0
      BEGIN
    }
    indented {
      puts "SET @constraint_match_error = 0;"
      declare_column_sequence_cursor_with_conditions {
        puts "WHERE kc.object_id = @constraint_id"
      }
      
      cnstr.columns.each do |index_column|
        add_column_sequence_test(index_column) do |error_message|
          puts "SET @constraint_match_error = 1;"
        end
      end
      
      check_column_sequence_end
      
      puts %Q{
        IF @constraint_match_error = 0
        BEGIN
          SET @constraint_found = 1;
        END
      }
    }
    puts "END"
    puts dedent %Q{
      CLOSE constraint_cursor;
      DEALLOCATE constraint_cursor;
      
      IF @constraint_found = 0
    }
    puts "BEGIN".."END" do
      puts error_sql "Expected #{cnstr_id} does not exist."
    end
  }
end
check_column_sequence_end() click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 998
def check_column_sequence_end
  dsl {
    puts dedent %Q{
      FETCH NEXT FROM column_cursor INTO @column_name, @column_sorted_descending;
      IF @@FETCH_STATUS = 0
    }
    puts "BEGIN".."END" do
      puts error_sql "#{cnstr_id.capitalize} has one or more unexpected columns."
    end
    puts "CLOSE column_cursor;"
    puts "DEALLOCATE column_cursor;"
  }
end
declare_column_sequence_cursor_with_conditions() { || ... } click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 982
def declare_column_sequence_cursor_with_conditions
  dsl {
    puts dedent %Q{
      DECLARE @column_name SYSNAME, @column_sorted_descending BIT;
      DECLARE column_cursor CURSOR FOR
      SELECT c.name, ic.is_descending_key
      FROM sys.key_constraints kc
      INNER JOIN sys.index_columns ic ON kc.parent_object_id = ic.object_id AND kc.unique_index_id = ic.index_id
      INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    }
    yield
    puts "ORDER BY ic.index_column_id;"
    puts "OPEN column_cursor;"
  }
end
error_sql(s) click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 861
def error_sql(s)
  @error_sql_proc.call(s)
end