class Mkxms::Mssql::ClrAssembly

Constants

RaiserrorSource

Attributes

access[RW]
error_stmt[R]
lib_name[RW]
name[R]
owner[RW]
warning_stmt[R]

Public Class Methods

new(name, lib_name = "", access:, owner: nil) click to toggle source
# File lib/mkxms/mssql/clr_assembly_handler.rb, line 11
def initialize(name, lib_name = "", access:, owner: nil)
  @name = name
  @error_stmt = RaiserrorSource.next_statement("ERROR".sql_quoted, name.sql_quoted, severity: :error)
  @warning_stmt = RaiserrorSource.next_statement("WARNING".sql_quoted, name.sql_quoted, severity: :warning)
  @lib_name = lib_name
  @access = access
  @owner = owner
end
setup_sql() click to toggle source
# File lib/mkxms/mssql/clr_assembly_handler.rb, line 23
def self.setup_sql
  [].tap do |s|
    s << "IF NOT EXISTS (SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID(N'xmigra.ignored_clr_assemblies'))"
    s << "    CREATE TABLE xmigra.ignored_clr_assemblies (name SYSNAME PRIMARY KEY);"
    
    s << "" # Give a newline at the end
  end.join("\n")
end

Public Instance Methods

to_sql() click to toggle source
# File lib/mkxms/mssql/clr_assembly_handler.rb, line 32
def to_sql
  [].tap do |s|
    s << "IF NOT EXISTS ("
    s << "  SELECT asm.name"
    s << "  FROM sys.assemblies asm"
    s << "  WHERE asm.is_visible = 1"
    s << "  AND QUOTENAME(asm.name) = #{name.sql_quoted}"
    s << "  UNION ALL"
    s << "  SELECT asm.name"
    s << "  FROM xmigra.ignored_clr_assemblies asm"
    s << "  WHERE asm.name = #{name.sql_quoted}"
    s << ") #{error_stmt};"
    
    s << "IF NOT EXISTS ("
    s << "  SELECT asm.name, QUOTENAME(owner.name) as owner, REPLACE(LOWER(asm.permission_set_desc), '_', '-') as permission_set, asm.clr_name as library"
    s << "  FROM sys.assemblies asm"
    s << "  JOIN sys.database_principals owner ON asm.principal_id = owner.principal_id" if owner
    s << "  WHERE asm.is_visible = 1"
    s << "  AND QUOTENAME(asm.name) = #{name.sql_quoted}"
    s << "  -- #{warning_stmt.error_marker} Run the query up to this point for assembly configuration --"
    cols = [
      ["owner", owner],
      ["permission_set", access],
      ["library", lib_name],
    ].map {|t, v| [t.ljust(v.length), v.ljust(t.length)]}
    s << ("  --                  " + cols.map {|e| e[0]}.join('   ') + ' --')
    s << ("  -- Expected values: " + cols.map {|e| e[1]}.join('   ') + ' --')
    s << "  AND QUOTENAME(owner.name) = #{owner.sql_quoted}" if owner
    s << "  AND REPLACE(LOWER(asm.permission_set_desc), '_', '-') = #{access.sql_quoted}"
    s << "  AND asm.clr_name = #{lib_name.sql_quoted}"
    s << "  UNION ALL"
    s << "  SELECT asm.name, NULL, NULL, NULL"
    s << "  FROM xmigra.ignored_clr_assemblies asm"
    s << "  WHERE asm.name = #{name.sql_quoted}"
    s << ") #{warning_stmt};"
    
    s << "" # Gives a newline at the end
  end.join("\n")
end