Class: Mkxms::Mssql::AdoptionScriptWriter::KeylikeConstraintAdoptionChecks

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(cnstr, error_sql_proc) ⇒ KeylikeConstraintAdoptionChecks

Returns a new instance of KeylikeConstraintAdoptionChecks.



840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
 
# 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

Instance Attribute Details

#cnstr ⇒ Object (readonly)

Returns the value of attribute cnstr.



859
860
861
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 859

def cnstr
  @cnstr
end

#cnstr_id ⇒ Object (readonly)

Returns the value of attribute cnstr_id.



859
860
861
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 859

def cnstr_id
  @cnstr_id
end

#constraint_type ⇒ Object (readonly)

Returns the value of attribute constraint_type.



859
860
861
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 859

def constraint_type
  @constraint_type
end

Instance Method Details

#add_column_sequence_test(index_column) ⇒ Object 



1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
 
# 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 ⇒ Object 



865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
 
# 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 ⇒ Object 



921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
 
# 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 ⇒ Object 



998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
 
# 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 ⇒ Object 



982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
 
# 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) ⇒ Object 



861
862
863
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 861

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