Class: Mkxms::Mssql::AdoptionScriptWriter::ForeignKeyAdoptionChecks

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(keys, error_sql_proc) ⇒ ForeignKeyAdoptionChecks

Returns a new instance of ForeignKeyAdoptionChecks.



1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
 
# 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

Instance Attribute Details

#named_keys ⇒ Object (readonly)

Returns the value of attribute named_keys.



1063
1064
1065
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1063

def named_keys
  @named_keys
end

#unnamed_keys ⇒ Object (readonly)

Returns the value of attribute unnamed_keys.



1063
1064
1065
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1063

def unnamed_keys
  @unnamed_keys
end

Instance Method Details

#add_named_key_tests ⇒ Object 



1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
 
# 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 ⇒ Object 



1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
 
# 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) ⇒ Object 



1065
1066
1067
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1065

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