Class: Mkxms::Mssql::AdoptionScriptWriter::IndexAdoptionChecks

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(index, error_sql_proc) ⇒ IndexAdoptionChecks

Returns a new instance of IndexAdoptionChecks.



1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1566

def initialize(index, error_sql_proc)
  super()
  
  @index = index
  @error_sql_proc = error_sql_proc
  
  @index_id = "index #{@index.name} on #{@index.qualified_relation}"
  
  dsl {
    puts "DECLARE @relation_id INT, @index_id INT;"
    puts dedent %Q{
      SELECT @relation_id = i.object_id, @index_id = i.index_id
      FROM sys.indexes i
      JOIN sys.objects rel ON i.object_id = rel.object_id
      JOIN sys.schemas s ON rel.schema_id = s.schema_id
      WHERE s.name = #{strlit(unquoted_identifier index.schema)}
      AND rel.name = #{strlit(unquoted_identifier index.relation)}
      AND i.name = #{strlit(unquoted_identifier index.name)}
    }
    puts "IF @index_id IS NULL"
    puts "BEGIN"
    indented {
      puts error_sql "#{index_id.capitalize} does not exist."
    }
    puts "END ELSE BEGIN"
    indented {
      add_index_property_checks
    }
    puts "END"
  }
end

Instance Attribute Details

#index ⇒ Object (readonly)

Returns the value of attribute index.



1598
1599
1600
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1598

def index
  @index
end

#index_id ⇒ Object (readonly)

Returns the value of attribute index_id.



1598
1599
1600
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1598

def index_id
  @index_id
end

Instance Method Details

#add_index_property_checks ⇒ Object 



1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1604

def add_index_property_checks
  dsl {
    puts property_verification("is_unique", index.unique?, "be unique")
    puts property_verification("ignore_dup_key", index.ignore_duplicates?, "ignore duplicate keys")
    
    # Key columns
    QueryCursor.new(
      dedent(%Q{
        SELECT c.name, ic.is_descending_key
        FROM sys.index_columns ic
        JOIN sys.columns c 
          ON ic.object_id = c.object_id 
          AND ic.column_id = c.column_id
        WHERE ic.object_id = @relation_id
        AND ic.index_id = @index_id
        AND ic.key_ordinal >= 1
        ORDER BY ic.key_ordinal
      }),
      "@column_name SYSNAME, @is_sorted_descending BIT",
      output_to: self
    ).expectations(
      on_extra: ->{puts error_sql "#{index_id.capitalize} has one or more unexpected key columns."}
    ) do |test|
      index.columns.each.with_index do |column, i|
        test.row(
          on_missing: ->{puts error_sql "#{index_id.capitalize} is missing expected column #{column.name}."}
        ) {
          puts "IF QUOTENAME(@column_name) <> #{strlit column.name}"
          puts "BEGIN"
          indented {
            puts error_sql "Expected #{column.name} as column #{i + 1} in #{index_id}."
          }
          puts "END ELSE IF #{bit_test('@is_sorted_descending', column.direction != :descending)}"
          puts "BEGIN"
          indented {
            puts error_sql "Expected #{column.name} to be sorted #{column.direction} in #{index_id}."
          }
          puts "END"
        }
      end
    end
    
    # Included columns
    unless (included_column_names = index.included_columns.map {|c| c.name}).empty?
      puts "IF (%s) < #{included_column_names.length}" do
        puts dedent %Q{
          SELECT COUNT(*) FROM sys.index_columns ic
          JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
          WHERE ic.object_id = @relation_id
          AND ic.index_id = @index_id
          AND ic.key_ordinal = 0
          AND QUOTENAME(c.name) IN (#{included_column_names.map {|s| strlit s}.join(', ')})
        }
      end
      puts "BEGIN".."END" do
        puts error_sql "#{index_id.capitalize} is missing one or more expected included columns."
      end
    end
  }
  
  add_spatial_property_checks(index) if index.spatial_index_geometry
end

#error_sql(s) ⇒ Object 



1600
1601
1602
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1600

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

#index_property_check(expectation, expectation_desc) ⇒ Object 



1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1667

def index_property_check(expectation, expectation_desc)
  %Q{
    IF NOT EXISTS (
      SELECT * FROM sys.indexes i
      WHERE i.object_id = @relation_id
      AND i.index_id = @index_id
      AND i.#{expectation}
    )
    BEGIN
      #{error_sql "#{@index_id.capitalize} should #{expectation_desc}."}
    END
  }.strip.gsub(/\s+/, ' ')
end

#property_verification(f, v, d) ⇒ Object 



1681
1682
1683
 
# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1681

def property_verification(f, v, d)
  index_property_check(bit_test(f, v), boolean_desc(v, d))
end