class Mkxms::Mssql::AdoptionScriptWriter::TableAdoptionChecks

Constants

NON_ANSI_PADDABLE_TYPES
UNICODE_CHAR_TYPES

Attributes

schema_name_literal[R]
table[R]
table_id[R]
table_name_literal[R]

Public Class Methods

new(table, error_sql_proc) click to toggle source
Calls superclass method
# 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

Public Instance Methods

add_column_properties_test(column) click to toggle source
# 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) click to toggle source
# 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() click to toggle source
# 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) click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 786
def compose_sql(&blk)
  IndentedStringBuilder.dsl(&blk)
end
error_sql(s) click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 607
def error_sql(s)
  @error_sql_proc.call(s)
end