class Mkxms::Mssql::AdoptionScriptWriter::ForeignKeyAdoptionChecks

Attributes

named_keys[R]
unnamed_keys[R]

Public Class Methods

new(keys, error_sql_proc) click to toggle source
Calls superclass method
# File lib/mkxms/mssql/adoption_script_writer.rb, line 1052
def initialize(keys, error_sql_proc)
  super()
  
  @error_sql_proc = error_sql_proc
  @named_keys = keys.reject {|k| k.unnamed?}
  @unnamed_keys = keys.select {|k| k.unnamed?}
  
  add_named_key_tests
  add_unnamed_key_tests
end

Public Instance Methods

add_named_key_tests() click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 1069
def add_named_key_tests
  table = 'expected_named_foreign_keys'
  dsl {
    # Create a temporary table
    puts dedent %Q{
      CREATE TABLE [xmigra].[#{table}] (
        [name] NVARCHAR(150) NOT NULL,
        [position] INTEGER NOT NULL,
        [from_table] NVARCHAR(300) NOT NULL,
        [from_column] NVARCHAR(150) NOT NULL,
        [to_table] NVARCHAR(300) NOT NULL,
        [to_column] NVARCHAR(150) NOT NULL
      );
      GO
    }
    
    # Insert a record for each column linkage for each named foreign key
    named_keys.each do |fkey|
      fkey.links.each.with_index do |cols, i|
        values = [
          strlit(fkey.name),
          i + 1,
          strlit(fkey.qualified_table),
          strlit(cols[0]),
          strlit(fkey.references.join '.'),
          strlit(cols[1])
        ]
        puts dedent(%Q{
          INSERT INTO [xmigra].[#{table}] (name, position, from_table, from_column, to_table, to_column)
          VALUES (%s);
        } % [values.join(', ')])
      end
    end
    
    # Write an adoption error for each missing/misdefined foreign key
    puts dedent %Q{
      WITH
        MissingLinks AS (
          SELECT
            [name],
            [position],
            [from_table],
            [from_column],
            [to_table],
            [to_column]
          FROM [xmigra].[#{table}]
          EXCEPT
          SELECT
            QUOTENAME(fk.name) AS name,
            RANK() OVER(PARTITION BY fk.object_id ORDER BY fkc.constraint_column_id ASC) AS position,
            QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) AS from_table,
            QUOTENAME(from_col.name) AS from_col,
            QUOTENAME(rs.name) + N'.' + QUOTENAME(r.name) AS to_table,
            QUOTENAME(to_col.name) AS to_col
          FROM sys.foreign_keys fk
          JOIN sys.tables t ON fk.parent_object_id = t.object_id
          JOIN sys.schemas s ON t.schema_id = s.schema_id
          JOIN sys.objects r ON fk.referenced_object_id = r.object_id
          JOIN sys.schemas rs ON r.schema_id = rs.schema_id
          JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
          JOIN sys.columns from_col
            ON fk.parent_object_id = from_col.object_id
            AND fkc.parent_column_id = from_col.column_id
          JOIN sys.columns to_col
            ON fk.referenced_object_id = to_col.object_id
            AND fkc.referenced_column_id = to_col.column_id
        )
      INSERT INTO [xmigra].[adoption_errors] ([message])
      SELECT DISTINCT
        N'Constraint ' + ml.[name] + N' on ' + ml.[from_table] + N' (referencing' + ml.[to_table] + N') does not have the expected definition.'
      FROM MissingLinks ml;
      GO
    }
    
    # Drop the temporary table
    puts "DROP TABLE [xmigra].[#{table}];\nGO"
  }
end
add_unnamed_key_tests() click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 1148
def add_unnamed_key_tests
  table = 'expected_unnamed_foreign_keys'
  dsl {
    # Create a temporary table
    puts dedent %Q{
      CREATE TABLE [xmigra].[#{table}] (
        [position] INTEGER NOT NULL,
        [from_table] NVARCHAR(300) NOT NULL,
        [from_column] NVARCHAR(150) NOT NULL,
        [to_table] NVARCHAR(300) NOT NULL,
        [to_column] NVARCHAR(150) NOT NULL
      );
      GO
    }
    
    # Insert a record for each column linkage for each unnamed foreign key
    unnamed_keys.each do |fkey|
      fkey.links.each.with_index do |cols, i|
        values = [
          i + 1,
          strlit(fkey.qualified_table),
          strlit(cols[0]),
          strlit(fkey.references.join '.'),
          strlit(cols[1])
        ]
        puts dedent(%Q{
          INSERT INTO [xmigra].[#{table}] (position, from_table, from_column, to_table, to_column)
          VALUES (%s);
        } % [values.join(', ')])
      end
    end
    
    # Write an adoption error for each missing/misdefined key
    puts dedent %Q{
      WITH
        MissingLinks AS (
          SELECT
            [position],
            [from_table],
            [from_column],
            [to_table],
            [to_column]
          FROM [xmigra].[#{table}]
          EXCEPT
          SELECT
            RANK() OVER(PARTITION BY fk.object_id ORDER BY fkc.constraint_column_id ASC) AS position,
            QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) AS from_table,
            QUOTENAME(from_col.name) AS from_col,
            QUOTENAME(rs.name) + N'.' + QUOTENAME(r.name) AS to_table,
            QUOTENAME(to_col.name) AS to_col
          FROM sys.foreign_keys fk
          JOIN sys.tables t ON fk.parent_object_id = t.object_id
          JOIN sys.schemas s ON t.schema_id = s.schema_id
          JOIN sys.objects r ON fk.referenced_object_id = r.object_id
          JOIN sys.schemas rs ON r.schema_id = rs.schema_id
          JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
          JOIN sys.columns from_col
            ON fk.parent_object_id = from_col.object_id
            AND fkc.parent_column_id = from_col.column_id
          JOIN sys.columns to_col
            ON fk.referenced_object_id = to_col.object_id
            AND fkc.referenced_column_id = to_col.column_id
        )
      INSERT INTO [xmigra].[adoption_errors] ([message])
      SELECT DISTINCT
        N'Expected constraint on ' + ml.[from_table] + N' (referencing ' + ml.[to_table] + N') not found.'
      FROM MissingLinks ml;
      GO
    }
    
    # Drop the temporary table
    puts "DROP TABLE [xmigra].[#{table}];\nGO"
  }
end
error_sql(s) click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 1065
def error_sql(s)
  @error_sql_proc.call(s)
end