class Mkxms::Mssql::AdoptionScriptWriter::DmlTriggerAdoptionChecks

Attributes

trigger[R]

Public Class Methods

new(trigger, tools) click to toggle source
Calls superclass method
# File lib/mkxms/mssql/adoption_script_writer.rb, line 1321
def initialize(trigger, tools)
  super()
  
  @tools = tools
  @trigger = trigger
  
  add_trigger_tests
  if trigger.clr_impl
    add_clr_impl_test
  else
    add_definition_test
  end
end

Public Instance Methods

add_clr_impl_test() click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 1482
def add_clr_impl_test
  dsl {
    # Check CLR implementation
    puts "IF NOT EXISTS(%s)" do
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        JOIN sys.assembly_modules asmmod ON tgr.object_id = asmmod.object_id
        JOIN sys.assemblies asm ON asmmod.assembly_id = asm.assembly_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
        AND QUOTENAME(asm.name) = #{trigger.clr_impl.assembly.sql_quoted}
        AND QUOTENAME(asmmod.assembly_class) = #{trigger.clr_impl.asm_class.sql_quoted}
        AND QUOTENAME(asmmod.assembly_method) = #{trigger.clr_impl.method.sql_quoted}
      }
    end
    puts "BEGIN".."END" do
      puts error_sql "Trigger #{trigger.qualified_name} does not invoke #{trigger.clr_impl.full_specifier}."
    end
  }
end
add_definition_test() click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 1505
def add_definition_test
  dsl {
    # Check definition
    puts "IF NOT EXISTS (%s)" do
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        JOIN sys.sql_modules sql ON tgr.object_id = sql.object_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
        AND #{check_definition_is("sql.definition", trigger.definition)}
      }
    end
    puts "BEGIN".."END" do
      puts error_sql "Trigger #{trigger.qualified_name} does not have the expected definition."
    end
  }
end
add_trigger_tests() click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 1345
def add_trigger_tests
  dsl {
    puts "IF NOT EXISTS (%s)" do
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
      }
    end
    puts "BEGIN".."END" do
      puts error_sql "Expected trigger #{trigger.qualified_name} does not exist."
    end
    
    puts "IF NOT EXISTS (%s)" do
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        JOIN sys.tables t ON tgr.parent_id = t.object_id
        JOIN sys.schemas ts ON t.schema_id = ts.schema_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
        AND QUOTENAME(ts.name) = #{trigger.table.schema.sql_quoted}
        AND QUOTENAME(t.name) = #{trigger.table.name.sql_quoted}
      }
    end
    puts "BEGIN".."END" do
      puts error_sql "Trigger #{trigger.qualified_name} does not apply to table #{trigger.table.qualified_name}."
    end
    
    puts "IF NOT EXISTS (%s)" do
      execution_identity_test = (if trigger.execute_as == 'OWNER'
        "COALESCE(sql.execute_as_principal_id, asmmod.execute_as_principal_id) = -2"
      elsif trigger.execute_as
        "(QUOTENAME(p.name) = #{trigger.execute_as.sql_quoted} OR QUOTENAME(p2.name) = #{trigger.execute_as.sql_quoted})"
      else
        "COALESCE(sql.execute_as_principal_id, asmmod.execute_as_principal_id) IS NULL"
      end)
      
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        LEFT JOIN sys.sql_modules sql ON tgr.object_id = sql.object_id
        LEFT JOIN sys.database_principals p ON p.principal_id = sql.execute_as_principal_id
        LEFT JOIN sys.assembly_modules asmmod ON tgr.object_id = asmmod.object_id
        LEFT JOIN sys.database_principals pa ON pa.principal_id = asmmod.execute_as_principal_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
        AND #{execution_identity_test}
      }
    end
    puts "BEGIN".."END" do
      if trigger.execute_as == 'OWNER'
        puts error_sql "Trigger #{trigger.qualified_name} does not execute as its owner."
      elsif trigger.execute_as
        puts error_sql "Trigger #{trigger.qualified_name} does not execute as #{trigger.execute_as}."
      else
        puts error_sql "Trigger #{trigger.qualified_name} does not execute as caller."
      end
    end
    
    puts "IF NOT EXISTS (%s)" do
      is_instead_of_trigger_value = (trigger.timing.downcase == "after") ? 0 : 1
      
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
        AND tgr.is_instead_of_trigger = #{is_instead_of_trigger_value}
      }
    end
    puts "BEGIN".."END" do
      puts error_sql %Q{Trigger #{trigger.qualified_name} must occur #{trigger.timing} the handled event(s).}
    end
    
    puts "IF NOT EXISTS (%s)" do
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
      }
      trigger.events.each do |ev|
        puts dedent %Q{
          AND EXISTS (
            SELECT *
            FROM sys.events ev
            WHERE ev.object_id = tgr.object_id
            AND ev.type_desc = #{ev.sql_quoted}
          )
        }
      end
    end
    puts "BEGIN".."END" do
      puts error_sql %Q{Trigger #{trigger.qualified_name} must occur #{trigger.timing.downcase} #{trigger.events.join(' and ')}.}
    end
    
    puts "IF NOT EXISTS (%s)" do
      is_not_for_replication_value = trigger.not_replicable ? 1 : 0
      
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
        AND tgr.is_not_for_replication = #{is_not_for_replication_value}
      }
    end
    puts "BEGIN".."END" do
      puts error_sql %Q{Trigger #{trigger.qualified_name} must#{' not' unless trigger.not_replicable} be configured "NOT FOR REPLICATION".}
    end
    
    puts "IF NOT EXISTS (%s)" do
      is_disabled_value = trigger.disabled ? 1 : 0
      
      puts dedent %Q{
        SELECT * FROM sys.triggers tgr
        JOIN sys.objects o ON tgr.object_id = o.object_id
        JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE QUOTENAME(s.name) = #{trigger.schema.sql_quoted}
        AND QUOTENAME(tgr.name) = #{trigger.name.sql_quoted}
        AND tgr.is_disabled = #{is_disabled_value}
      }
    end
    puts "BEGIN".."END" do
      puts error_sql "Trigger #{trigger.qualified_name} must#{' not' unless trigger.disabled} be disabled."
    end
  }
end
check_definition_is(*args) click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 1341
def check_definition_is(*args)
  @tools.definition_matches_by_hash(*args)
end
error_sql(s) click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 1337
def error_sql(s)
  @tools.adoption_error_sql(s)
end