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