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.



459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 459

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.



478
479
480
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 478

def cnstr
  @cnstr
end

#cnstr_id ⇒ Object (readonly)

Returns the value of attribute cnstr_id.



478
479
480
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 478

def cnstr_id
  @cnstr_id
end

#constraint_type ⇒ Object (readonly)

Returns the value of attribute constraint_type.



478
479
480
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 478

def constraint_type
  @constraint_type
end

Instance Method Details

#add_column_sequence_test(index_column) ⇒ Object 



631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 631

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 



484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 484

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 



540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 540

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 



617
618
619
620
621
622
623
624
625
626
627
628
629
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 617

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 



601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 601

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 



480
481
482
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 480

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