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