Class: Mkxms::Mssql::AdoptionScriptWriter

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 15

def initialize(db_expectations)
  @db_expectations = db_expectations
  # Ex nihilo DB schema builder
  @xn_builder = XMigra::SchemaUpdater.new(@db_expectations.schema_dir)
end

Instance Attribute Details

#db_expectations ⇒ `Object` (readonly)

Returns the value of attribute db_expectations.



21
22
23

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 21

def db_expectations
  @db_expectations
end

Instance Method Details

#access_object_adoption_sql(type, qualified_name) ⇒ `Object`



1780
1781
1782

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1780

def access_object_adoption_sql(type, qualified_name)
  "INSERT INTO [xmigra].[access_objects] ([type], [name]) VALUES (N'#{type}', #{strlit qualified_name});"
end

#adopt_indexes_sql ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1686

def adopt_indexes_sql
  db_expectations.indexes.map do |index|
    index_builder = @xn_builder.indexes[index.name]
    IndexAdoptionChecks.new(index, method(:adoption_error_sql)).to_s +
    "\nINSERT INTO [xmigra].[indexes] ([IndexID], [name]) VALUES (%s, %s);" % [
      index_builder.id, index_builder.name
    ].map {|s| strlit(s)}
  end
end

#adopt_permissions_sql ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1934

def adopt_permissions_sql
  table = 'expected_permissions'
  [
    # Create a temporary table
    dedent(%Q{
      CREATE TABLE [xmigra].[#{table}] (
        [state] CHAR(1) NOT NULL,
        [subject] NVARCHAR(150) NOT NULL,
        [permission] NVARCHAR(128) NOT NULL,
        [object_type] NVARCHAR(25) NOT NULL,
        [object_schema] NVARCHAR(150) NULL,
        [object] NVARCHAR(150) NULL,
        [column] NVARCHAR(150) NULL
      );
    }),
    # Insert permission rows into the table
    [].tap do |inserts|
      db_expectations.permissions.each do |pg|
        pg.permissions.each do |pmsn|
          state = case pg.action[0].downcase
          when 'g' then pmsn.grant_option? ? 'W' : 'G'
          else pg.action[0].upcase
          end
          nls = ->(s) {s.nil? ? 'NULL' : strlit(s)}
          row_values = [state, pg.subject, pmsn.name] + pmsn.object_id_parts
          inserts << dedent(%Q{
            INSERT INTO [xmigra].[#{table}] (state, subject, permission, object_type, object_schema, object, [column])
            VALUES (%s);
          } % row_values.map(&nls).join(', '))
        end
      end
    end.join("\n"),
    # Write an adoption error for each missing permission
    dedent(%Q{
      WITH
        PermissionTarget AS (
          SELECT
            0 AS "class",
            0 AS major_id,
            0 AS minor_id,
            'DATABASE' AS "class_desc",
            NULL AS "class_specifier",
            NULL AS "schema_name",
            NULL AS "object_name",
            NULL AS "column_name"
          UNION
          SELECT
            1,
            o.object_id,
            0,
            'OBJECT',
            NULL,
            s.name,
            o.name,
            NULL
          FROM sys.objects o
          JOIN sys.schemas s ON o.schema_id = s.schema_id
          UNION
          SELECT
            1,
            c.object_id,
            c.column_id,
            'COLUMN',
            NULL,
            s.name,
            o.name,
            c.name
          FROM sys.columns c
          JOIN sys.objects o ON c.object_id = o.object_id
          JOIN sys.schemas s ON o.schema_id = s.schema_id
          UNION
          SELECT
            3,
            s.schema_id,
            0,
            'SCHEMA',
            'SCHEMA',
            NULL,
            s.name,
            NULL
          FROM sys.schemas s
          UNION
          SELECT
            4,               -- class
            r.principal_id,  -- major_id
            0,               -- minor_id
            'ROLE',          -- class description
            'ROLE',          -- class specifier
            NULL,            -- schema_name
            r.name,          -- object_name
            NULL             -- column_name
          FROM sys.database_principals r
          WHERE r.type = 'R'
          UNION
          SELECT
            5,               -- class
            a.assembly_id,   -- major_id
            0,               -- minor_id
            'ASSEMBLY',      -- class description
            'ASSEMBLY',      -- class specifier
            NULL,            -- schema_name
            a.name,          -- object_name
            NULL             -- column_name
          FROM sys.assemblies a
          UNION
          SELECT
            6,               -- class
            t.user_type_id,  -- major_id
            0,               -- minor_id
            'TYPE',          -- class description
            'TYPE',          -- class specifier
            s.name,          -- schema_name
            t.name,          -- object_name
            NULL             -- column_name
          FROM sys.types t
          JOIN sys.schemas s ON t.schema_id = s.schema_id
          UNION
          SELECT
            10,                        -- class
            xsc.xml_collection_id,     -- major_id
            0,                         -- minor_id
            'XML_SCHEMA_COLLECTION',   -- class description
            'XML SCHEMA COLLECTION',   -- class specifier
            s.name,                    -- schema_name
            xsc.name,                  -- object_name
            NULL                       -- column_name
          FROM sys.xml_schema_collections xsc
          JOIN sys.schemas s ON xsc.schema_id = s.schema_id
        ),
        Permissions AS (
          SELECT
            p.state,
            QUOTENAME(pi.name) AS "subject",
            p.permission_name AS "permission",
            t.class_desc AS "object_type",
            QUOTENAME(t.schema_name) AS "object_schema",
            QUOTENAME(t.object_name) AS "object",
            QUOTENAME(t.column_name) AS "column"
          FROM sys.database_permissions p
          JOIN sys.database_principals pi ON p.grantee_principal_id = pi.principal_id
          JOIN PermissionTarget t 
            ON p.class = t.class 
            AND p.major_id = t.major_id
            AND p.minor_id = t.minor_id
          LEFT JOIN sys.database_principals grantor ON p.grantor_principal_id = grantor.principal_id
          AND (p.class <> 4 OR (
            SELECT dp.type FROM sys.database_principals dp
            WHERE dp.principal_id = p.major_id
          ) = 'R')
          AND (p.class <> 1 OR p.major_id IN (
            SELECT o.object_id FROM sys.objects o
          ))
        )
      INSERT INTO [xmigra].[adoption_errors] ([message])
      SELECT
        e.permission + N' is ' +
        CASE e.state
        WHEN 'G' THEN 
          CASE (
            SELECT p.state
            FROM Permissions p
            WHERE p.subject = e.subject
            AND p.permission = e.permission
            AND p.object_type = e.object_type
            AND COALESCE(p.object_schema, N'.') = COALESCE(e.object_schema, N'.')
            AND COALESCE(p.object, N'.') = COALESCE(e.object, N'.')
            AND COALESCE(p.[column], N'.') = COALESCE(e.[column], N'.')
          )
          WHEN 'W' THEN 'GRANTed with (unexpected) grant option to '
          ELSE N'not GRANTed to '
          END
        WHEN 'W' THEN N'not GRANTed (with grant option) to '
        WHEN 'D' THEN N'not DENYed to '
        WHEN 'R' THEN N'not REVOKEd from '
        END + e.subject + N' on ' + e.object_type +
        CASE 
        WHEN e.object_schema IS NULL THEN N''
        ELSE e.object_schema + N'.'
        END +
        CASE
        WHEN e.object IS NULL THEN N''
        ELSE e.object
        END +
        CASE
        WHEN e.[column] IS NULL THEN N''
        ELSE N' (' + e.[column] + N')'
        END + N'.'
      FROM (
        SELECT state, subject, permission, object_type, object_schema, object, [column]
        FROM [xmigra].[#{table}]
        EXCEPT
        SELECT 
          state COLLATE SQL_Latin1_General_CP1_CI_AS, 
          subject, 
          permission COLLATE SQL_Latin1_General_CP1_CI_AS, 
          object_type, 
          object_schema, 
          object, 
          [column]
        FROM Permissions
      ) e
    }),
    # Record adopted permissions
    db_expectations.permissions.map do |pg|
      pg.regular_permissions.map do |pmsn|
        "EXEC [xmigra].[ip_prepare_revoke] #{[pmsn.name, pmsn.target, pg.subject].map {|s| strlit(unquoted_identifier s)}.join(', ')};"
      end
    end.flatten.join("\n"),
    # Drop the temporary table
    "DROP TABLE [xmigra].[#{table}];",
  ]
end

#adopt_statistics_sql ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1770

def adopt_statistics_sql
  db_expectations.statistics.map do |statistics|
    StatisticsAdoptionChecks.new(statistics, method(:adoption_error_sql)).to_s +
    "\nINSERT INTO [xmigra].[statistics] ([Name], [Columns]) VALUES (%s, %s);" % [
      statistics.name,
      statistics.columns.join(', ')
    ].map {|s| strlit(s)}
  end
end

#adopt_stored_procedures_sql ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1838

def adopt_stored_procedures_sql
  db_expectations.procedures.map do |sproc|
    IndentedStringBuilder.dsl {
      puts "IF NOT EXISTS (%s)" do
        puts dedent %Q{
          SELECT * FROM sys.procedures p
          JOIN sys.schemas s ON p.schema_id = s.schema_id
          WHERE s.name = #{strlit(unquoted_identifier sproc.schema)}
          AND p.name = #{strlit(unquoted_identifier sproc.name)}
        }
      end
      puts "BEGIN"
      indented {
        puts adoption_error_sql "Stored procedure #{sproc.qualified_name} does not exist."
      }
      puts "END ELSE IF NOT EXISTS (%s)" do
        if sproc.respond_to?(:clr_impl)
          puts dedent %Q{
            SELECT * FROM sys.objects sproc
            JOIN sys.assembly_modules asmmod ON sproc.object_id = asmmod.object_id
            JOIN sys.assemblies asm ON asmmod.assembly_id = asm.assembly_id
            JOIN sys.schemas s ON sproc.schema_id = s.schema_id
            WHERE sproc.type = 'PC'
            AND QUOTENAME(asm.name) = #{sproc.clr_impl.assembly.sql_quoted}
            AND QUOTENAME(asmmod.assembly_class) = #{sproc.clr_impl.asm_class.sql_quoted}
            AND QUOTENAME(asmmod.assembly_method) = #{sproc.clr_impl.method.sql_quoted}
          }
        else
          puts dedent %Q{
            SELECT * FROM sys.procedures p
            JOIN sys.schemas s ON p.schema_id = s.schema_id
            JOIN sys.sql_modules sql ON p.object_id = sql.object_id
            WHERE s.name = #{strlit(unquoted_identifier sproc.schema)}
            AND p.name = #{strlit(unquoted_identifier sproc.name)}
            AND #{definition_matches_by_hash('sql.definition', sproc.definition)}
          }
        end
      end
      puts "BEGIN"..."END" do
        puts adoption_error_sql "Stored procedure #{sproc.qualified_name} does not have the expected definition."
      end
      puts access_object_adoption_sql(:PROCEDURE, sproc.qualified_name)
    }
  end
end

#adopt_udfs_sql ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1884

def adopt_udfs_sql
  db_expectations.udfs.map do |udf|
    IndentedStringBuilder.dsl {
      puts "IF NOT EXISTS (%s)" do
        puts dedent %Q{
          SELECT * FROM sys.objects fn
          JOIN sys.schemas s ON fn.schema_id = s.schema_id
          WHERE s.name = #{strlit(unquoted_identifier udf.schema)}
          AND fn.name = #{strlit(unquoted_identifier udf.name)}
          AND fn.type IN ('FN', 'FS', 'FT', 'IF', 'TF')
        }
      end
      puts "BEGIN"
      indented {
        puts adoption_error_sql "Function #{udf.qualified_name} does not exist."
      }
      puts "END ELSE IF NOT EXISTS (%s)" do
        if udf.respond_to?(:clr_impl)
          puts dedent %Q{
            SELECT * FROM sys.objects fn
            JOIN sys.schemas s ON fn.schema_id = s.schema_id
            JOIN sys.assembly_modules asmmod ON fn.object_id = asmmod.object_id
            JOIN sys.assemblies asm ON asmmod.assembly_id = asm.assembly_id
            WHERE QUOTENAME(s.name) = #{udf.schema.sql_quoted}
            AND QUOTENAME(fn.name) = #{udf.name.sql_quoted}
            AND QUOTENAME(asm.name) = #{udf.clr_impl.assembly.sql_quoted}
            AND QUOTENAME(asmmod.assembly_class) = #{udf.clr_impl.asm_class.sql_quoted}
            AND QUOTENAME(asmmod.assembly_method) = #{udf.clr_impl.method.sql_quoted}
          }
        else
          puts dedent %Q{
            SELECT * FROM sys.objects fn
            JOIN sys.schemas s ON fn.schema_id = s.schema_id
            JOIN sys.sql_modules sql ON fn.object_id = sql.object_id
            WHERE s.name = #{strlit(unquoted_identifier udf.schema)}
            AND fn.name = #{strlit(unquoted_identifier udf.name)}
            AND #{definition_matches_by_hash 'sql.definition', udf.definition}
          }
        end
      end
      puts "BEGIN"
      indented {
        puts adoption_error_sql "Function #{udf.qualified_name} does not have the expected definition."
      }
      puts "END"
      puts access_object_adoption_sql(:FUNCTION, udf.qualified_name)
    }
  end
end

#adopt_views_sql ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1803

def adopt_views_sql
  db_expectations.views.map do |view|
    IndentedStringBuilder.dsl {
      puts "IF NOT EXISTS (%s)" do
        puts dedent %Q{
          SELECT * FROM sys.views v
          JOIN sys.schemas s ON v.schema_id = s.schema_id
          WHERE s.name = #{strlit(unquoted_identifier view.schema)}
          AND v.name = #{strlit(unquoted_identifier view.name)}
        }
      end
      puts "BEGIN"
      indented do
        puts adoption_error_sql "View #{view.qualified_name} does not exist."
      end
      puts "END ELSE IF NOT EXISTS (%s)" do
        puts dedent %Q{
          SELECT * FROM sys.views v
          JOIN sys.schemas s ON v.schema_id = s.schema_id
          JOIN sys.sql_modules sql ON v.object_id = sql.object_id
          WHERE s.name = #{strlit(unquoted_identifier view.schema)}
          AND v.name = #{strlit(unquoted_identifier view.name)}
          AND #{definition_matches_by_hash 'sql.definition', view.definition}
        }
      end
      puts "BEGIN"
      indented {
        puts adoption_error_sql "View #{view.qualified_name} does not have the expected definition."
      }
      puts "END"
      puts access_object_adoption_sql(:VIEW, view.qualified_name)
    }
  end
end

#adoption_sql ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 33

def adoption_sql
  in_ddl_transaction(dry_run: Utils.dry_run?) do
    script_parts = [
      # Check for blatantly incorrect application of script, e.g. running
      # on master or template database.
      :check_execution_environment_sql,
      
      # Create schema version control (SVC) tables if they don't exist
      :ensure_version_tables_sql,
      :ensure_permissions_table_sql,
      
      # Create an error table
      :create_adoption_error_table_sql,
      
      # Check CLR assemblies
      :check_clr_assemblies,
      
      # Check roles
      :check_expected_roles_exist_sql,
      :check_expected_role_membership_sql,
      
      # Check schemas
      :check_expected_schemas_exist_sql,
      
      # Check user-defined types
      :check_user_defined_types_sql,
      
      # Check CLR types
      :check_clr_types,
      
      # Check CLR aggregates
      :check_clr_aggregates,
      
      # Check tables (including columns)
      :check_tables_exist_and_structured_as_expected_sql,
      
      # Check column defaults
      :check_expected_column_defaults_exist_sql,
      
      # Check primary key and unique constraints
      :check_primary_key_and_unique_constraints_sql,
      
      # Check foreign key constraints
      :check_foreign_key_constraints_sql,
      
      # Check check constraints
      :check_check_constraints_sql,
      
      # Check DML triggers
      :check_dml_triggers,
      
      # Adopt indexes
      :adopt_indexes_sql,
      
      # Adopt statistics
      :adopt_statistics_sql,
      
      # Adopt views
      :adopt_views_sql,
      
      # Adopt stored procedures
      :adopt_stored_procedures_sql,
      
      # Adopt user defined functions
      :adopt_udfs_sql,
      
      # Adopt permissions
      :adopt_permissions_sql,
      
      # Error out if there are any entries in the error table
      :check_adoption_error_table_empty_sql,
      
      # Write version bridge record to xmigra.applied
      :write_version_bridge_record_sql,
    ]
    
    #script_parts = script_parts.map {|mn| self.send(mn)}.flatten.compact
    script_parts = script_parts.map do |mn|
      [
        %Q{PRINT N'ADOPTION STEP: #{mn}';},
        self.send(mn)
      ]
    end.flatten.compact
    script_parts.join(ddl_block_separator)
  end
end

#check_check_constraints_sql ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1312

def check_check_constraints_sql
  db_expectations.check_constraints.map do |cnstr|
    CheckConstraintAdoptionChecks.new(cnstr, method(:adoption_error_sql)).to_s
  end # Do not join -- each needs a separate batch (they use variables)
end

#check_clr_aggregates ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 510

def check_clr_aggregates
  db_expectations.aggregates.map do |agg|
    dedent %Q{
      IF NOT EXISTS (
        SELECT *
        FROM sys.objects fn
        JOIN sys.schemas s ON fn.schema_id = s.schema_id
        WHERE fn.type = 'AF'
        AND QUOTENAME(s.name) = #{agg.schema.sql_quoted}
        AND QUOTENAME(fn.name) = #{agg.name.sql_quoted}
      )
      BEGIN
        #{adoption_error_sql "CLR aggregate #{agg.qualified_name} does not exist."}
      END
      
      IF NOT EXISTS (
        SELECT *
        FROM sys.objects fn
        JOIN sys.schemas s ON fn.schema_id = s.schema_id
        JOIN sys.assembly_modules asmmod ON fn.object_id = asmmod.object_id
        JOIN sys.assemblies asm ON asmmod.assembly_id = asm.assembly_id
        WHERE fn.type = 'AF'
        AND QUOTENAME(s.name) = #{agg.schema.sql_quoted}
        AND QUOTENAME(fn.name) = #{agg.name.sql_quoted}
        AND QUOTENAME(asm.name) = #{agg.clr_impl.assembly.sql_quoted}
      )
      BEGIN
        #{adoption_error_sql "CLR aggregate #{agg.qualified_name} does not reference assembly #{agg.clr_impl.assembly}."}
      END
      
      IF NOT EXISTS (
        SELECT *
        FROM sys.objects fn
        JOIN sys.schemas s ON fn.schema_id = s.schema_id
        JOIN sys.assembly_modules asmmod ON fn.object_id = asmmod.object_id
        JOIN sys.assemblies asm ON asmmod.assembly_id = asm.assembly_id
        WHERE fn.type = 'AF'
        AND QUOTENAME(s.name) = #{agg.schema.sql_quoted}
        AND QUOTENAME(fn.name) = #{agg.name.sql_quoted}
        AND QUOTENAME(asm.name) = #{agg.clr_impl.assembly.sql_quoted}
        AND QUOTENAME(asmmod.assembly_class) = #{agg.clr_impl.asm_class.sql_quoted}
      )
      BEGIN
        #{adoption_error_sql "CLR aggregate #{agg.qualified_name} does not reference class #{agg.clr_impl.asm_class} of #{agg.clr_impl.assembly}."}
      END
      
      IF NOT EXISTS (
        SELECT *
        FROM sys.objects fn
        JOIN sys.schemas s ON fn.schema_id = s.schema_id
        JOIN sys.assembly_modules asmmod ON fn.object_id = asmmod.object_id
        WHERE fn.type = 'AF'
        AND QUOTENAME(s.name) = #{agg.schema.sql_quoted}
        AND QUOTENAME(fn.name) = #{agg.name.sql_quoted}
        AND asmmod.execute_as_principal_id #{
          case agg.execute_as
          when nil
            'IS NULL'
          when 'OWNER'
            "= -2"
          else
            "= DATABASE_PRINCIPAL_ID(#{agg.execute_as.sql_quoted})"
          end
        }
      )
      BEGIN
        #{adoption_error_sql "CLR aggregate #{agg.qualified_name} does not execute as #{
          case agg.execute_as
          when nil
            'CALLER'
          else
            agg.execute_as
          end
        }."}
      END
    }
  end
end

#check_clr_assemblies ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 160

def check_clr_assemblies
  db_expectations.clr_assemblies.map do |asm|
    dedent %Q{
      IF NOT EXISTS (
        SELECT * FROM sys.assemblies asm
        WHERE asm.is_visible = 1 AND QUOTENAME(asm.name) = #{asm.name.sql_quoted}
      )
      BEGIN
        #{adoption_error_sql "CLR assembly #{asm.name} does not exist."}
      END
      
      IF NOT EXISTS (
        SELECT * FROM sys.assemblies asm
        JOIN sys.database_principals owner ON asm.principal_id = owner.principal_id
        WHERE asm.is_visible = 1 AND QUOTENAME(asm.name) = #{asm.name.sql_quoted}
        AND QUOTENAME(owner.name) = #{asm.owner.sql_quoted}
      )
      BEGIN
        #{adoption_error_sql "CLR assembly #{asm.name} should be owned by #{asm.owner}"}
      END
      
      IF NOT EXISTS (
        SELECT * FROM sys.assemblies asm
        WHERE asm.is_visible = 1 AND QUOTENAME(asm.name) = #{asm.name.sql_quoted}
        AND REPLACE(LOWER(asm.permission_set_desc), '_', '-') = #{asm.access.sql_quoted}
      )
      BEGIN
        #{adoption_error_sql "CLR assembly #{asm.name} should have permission set #{asm.access}"}
      END
      
      IF NOT EXISTS (
        SELECT * FROM sys.assemblies asm
        WHERE asm.is_visible = 1 AND QUOTENAME(asm.name) = #{asm.name.sql_quoted}
        AND asm.clr_name = #{asm.lib_name.sql_quoted}
      )
      BEGIN
        #{adoption_error_sql %Q{CLR assembly #{asm.name} should reference library "#{asm.lib_name}".}}
      END
    }
  end
end

#check_clr_types ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 474

def check_clr_types
  db_expectations.clr_types.map do |t|
    dedent %Q{
      IF NOT EXISTS (
        SELECT * FROM sys.assembly_types t
        JOIN sys.schemas s ON t.schema_id = s.schema_id
        WHERE QUOTENAME(s.name) = #{t.schema.sql_quoted} AND QUOTENAME(t.name) = #{t.name.sql_quoted}
      )
      BEGIN
        #{adoption_error_sql "CLR type #{t.qualified_name} does not exist."}
      END
      
      IF NOT EXISTS (
        SELECT * FROM sys.assembly_types t
        JOIN sys.schemas s ON t.schema_id = s.schema_id
        JOIN sys.assemblies asm ON t.assembly_id = asm.assembly_id
        WHERE QUOTENAME(s.name) = #{t.schema.sql_quoted} AND QUOTENAME(t.name) = #{t.name.sql_quoted}
        AND QUOTENAME(asm.name) = #{t.assembly.sql_quoted}
      )
      BEGIN
        #{adoption_error_sql "CLR type #{t.qualified_name} does not reference assembly #{t.assembly}."}
      END
      
      IF NOT EXISTS (
        SELECT * FROM sys.assembly_types t
        JOIN sys.schemas s ON t.schema_id = s.schema_id
        WHERE QUOTENAME(s.name) = #{t.schema.sql_quoted} AND QUOTENAME(t.name) = #{t.name.sql_quoted}
        AND QUOTENAME(t.assembly_class) = #{t.clr_class.sql_quoted}
      )
      BEGIN
        #{adoption_error_sql "CLR type #{t.qualified_name} does not reference class #{t.clr_class} of #{t.assembly}."}
      END
    }
  end
end

#check_dml_triggers ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1526

def check_dml_triggers
  db_expectations.dml_triggers.map do |tgr|
    DmlTriggerAdoptionChecks.new(tgr, self).to_s
  end
end

#check_expected_column_defaults_exist_sql ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 797

def check_expected_column_defaults_exist_sql
  db_expectations.column_defaults.map do |col_dflt|
    constraint_id = (col_dflt.name || "on #{col_dflt.column}") + " of #{col_dflt.qualified_table}"
    compose_sql {
      puts "IF NOT EXISTS (%s)" do
        puts "SELECT * FROM sys.default_constraints dc"
        puts "INNER JOIN sys.schemas s ON dc.schema_id = s.schema_id"
        puts "INNER JOIN sys.tables t ON dc.parent_object_id = t.object_id"
        puts "INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id"
        puts "WHERE dc.name = %s" do
          puts strlit(unquoted_identifier col_dflt.name)
        end if col_dflt.name
      end
      puts "BEGIN"
      indented {puts adoption_error_sql(
        "Expected column default constraint #{constraint_id} does not exist."
      )}
      puts "END ELSE BEGIN"
      indented {
        puts "IF NOT EXISTS (%s)" do
          puts "SELECT * FROM sys.default_constraints dc"
          puts "INNER JOIN sys.schemas s ON dc.schema_id = s.schema_id"
          puts "INNER JOIN sys.tables t ON dc.parent_object_id = t.object_id"
          puts "INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id"
          puts "WHERE dc.definition = %s" do
            puts strlit col_dflt.expression
          end
          puts "AND dc.name = %s" do
            puts strlit(unquoted_identifier col_dflt.name)
          end if col_dflt.name
        end
        puts("BEGIN".."END") {
          puts adoption_error_sql("Column default constraint #{constraint_id} does not have the expected definition.")
        }
      }
      puts "END"
    }
  end.join("\n")
end

#check_expected_role_membership_sql ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 227

def check_expected_role_membership_sql
  [].tap do |tests|
    db_expectations.roles.each do |r|
      r.encompassing_roles.each do |er_name|
        tests << dedent(%Q{
          IF NOT EXISTS (
            SELECT * FROM sys.database_role_members rm
            INNER JOIN sys.database_principals r ON rm.member_principal_id = r.principal_id
            INNER JOIN sys.database_principals er ON rm.role_principal_id = er.principal_id
            WHERE r.name = #{strlit(unquoted_identifier r.name)} 
            AND er.name = #{strlit(unquoted_identifier er_name)}
          )
          BEGIN
            #{adoption_error_sql "Role #{r.name} should be a member of #{er_name}."}
          END
        })
      end
    end
  end.join("\n")
end

#check_expected_roles_exist_sql ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 202

def check_expected_roles_exist_sql
  db_expectations.roles.map do |r|
    dedent %Q{
      IF NOT EXISTS (
        SELECT * FROM sys.database_principals r
        WHERE r.name = #{strlit(unquoted_identifier r.name)}
        AND r.type = 'R'
      )
      BEGIN
        #{adoption_error_sql "Role #{r.name} does not exist."}
      END
      
      IF EXISTS (
        SELECT * FROM sys.database_principals r
        INNER JOIN sys.database_principals o ON r.owning_principal_id = o.principal_id
        WHERE r.name = #{strlit(unquoted_identifier r.name)}
        AND o.name <> #{strlit(unquoted_identifier r.owner)}
      )
      BEGIN
        #{adoption_error_sql "Role #{r.name} should be owned by #{r.owner}."}
      END
    }
  end.join("\n")
end

#check_expected_schemas_exist_sql ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 248

def check_expected_schemas_exist_sql
  db_expectations.schemas.map do |schema|
    dedent %Q{
      IF NOT EXISTS (
        SELECT * FROM sys.schemas s
        WHERE s.name = #{strlit(unquoted_identifier schema.name)}
      )
      BEGIN
        #{adoption_error_sql "Schema #{schema.name} does not exist."}
      END ELSE IF NOT EXISTS (
        SELECT * FROM sys.schemas s
        INNER JOIN sys.database_principals r ON s.principal_id = r.principal_id
        WHERE s.name = #{strlit(unquoted_identifier schema.name)}
        AND r.name = #{strlit(unquoted_identifier schema.owner)}
      )
      BEGIN
        #{adoption_error_sql "Schema #{schema.name} is not owned by #{schema.owner}."}
      END
    }
  end
end

#check_foreign_key_constraints_sql ⇒ `Object`



1224
1225
1226

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1224

def check_foreign_key_constraints_sql
  ForeignKeyAdoptionChecks.new(db_expectations.foreign_keys, method(:adoption_error_sql)).to_s
end

#check_primary_key_and_unique_constraints_sql ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1043

def check_primary_key_and_unique_constraints_sql
  db_expectations.pku_constraints.map do |cnstr|
    KeylikeConstraintAdoptionChecks.new(cnstr, method(:adoption_error_sql)).to_s
  end # Do not join -- each needs a separate batch (they use variables)
end

#check_synonyms ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1532

def check_synonyms
  db_expectations.synonyms.map do |syn|
    dedent %Q{
      IF NOT EXISTS (
        SELECT * FROM sys.synonyms syn
        JOIN sys.schemas s ON syn.schema_id = s.schema_id
        WHERE QUOTENAME(s.name) = #{syn.schema.sql_quoted}
        AND QUOTENAME(syn.name) = #{syn.name.sql_quoted}
      )
      BEGIN
        #{adoption_error_sql "Synonym #{syn.qualified_name} does not exist."}
      END
      
      IF NOT EXISTS (
        SELECT * FROM sys.synonyms syn
        JOIN sys.schemas s ON syn.schema_id = s.schema_id
        WHERE QUOTENAME(s.name) = #{syn.schema.sql_quoted}
        AND QUOTENAME(syn.name) = #{syn.name.sql_quoted}
        AND (
          OBJECT_ID(syn.base_object_name) IS NULL
          OR
          OBJECT_ID(syn.base_object_name) = OBJECT_ID(#{syn.referent.sql_quoted})
        )
      )
      BEGIN
        #{adoption_error_sql "Synonym #{syn.qualified_name} does not reference #{syn.referent}."}
      END
    }
  end
end

#check_table_type_components_sql(t) ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 390

def check_table_type_components_sql(t)
  [].tap do |tests|
    t.columns.each do |col|
      # The column itself
      tests << (dedent %Q{
        IF NOT EXISTS (
          SELECT * FROM sys.columns c
          JOIN sys.table_types tt ON tt.type_table_object_id = c.object_id
          JOIN sys.schemas ts ON ts.schema_id = tt.schema_id
          JOIN sys.types ct ON ct.user_type_id = c.user_type_id
          JOIN sys.schemas cts ON cts.schema_id = ct.schema_id
          WHERE QUOTENAME(c.name) = #{col.name.sql_quoted}
          AND QUOTENAME(cts.name) = #{(col.type_schema || "[sys]").sql_quoted}
          AND QUOTENAME(ct.name) = #{col.type_name.sql_quoted}
        )
        BEGIN
          #{adoption_error_sql "Table type #{t.qualified_name} does not have a column named #{col.name}."}
        END
        
        IF NOT EXISTS (
          SELECT * FROM sys.columns c
          JOIN sys.table_types tt ON tt.type_table_object_id = c.object_id
          JOIN sys.schemas ts ON ts.schema_id = tt.schema_id
          JOIN sys.types ct ON ct.user_type_id = c.user_type_id
          JOIN sys.schemas cts ON cts.schema_id = ct.schema_id
          WHERE QUOTENAME(c.name) = #{col.name.sql_quoted}
          AND QUOTENAME(cts.name) = #{(col.type_schema || "[sys]").sql_quoted}
          AND QUOTENAME(ct.name) = #{col.type_name.sql_quoted}
          AND c.is_nullable = #{col.nullable? ? 1 : 0}
          #{"AND c.max_length = #{col.max_byte_consumption}" if col.capacity}
          #{"AND c.collation_name = #{col.collation.sql_quoted}" if col.collation}
          #{"AND c.precision = #{col.precision}" if col.precision}
          #{"AND c.scale = #{col.scale}" if col.scale}
        )
        BEGIN
          #{adoption_error_sql "Column #{col.name} of #{t.qualified_name} is not defined as #{col.type_spec}."}
        END
      })
      
      # Column constraints
      col.check_constraints.each do |cstrt|
        tests << (dedent %Q{
          IF NOT EXISTS (
            SELECT * FROM sys.check_constraints cc
            JOIN sys.columns c
              ON c.object_id = cc.parent_object_id
              AND c.column_id = cc.parent_column_id
            JOIN sys.table_types tt ON tt.type_table_object_id = c.object_id
            JOIN sys.schemas s ON s.schema_id = tt.schema_id
            WHERE QUOTENAME(s.name) = #{t.schema.sql_quoted}
            AND QUOTENAME(tt.name) = #{t.name.sql_quoted}
            AND QUOTENAME(c.name) = #{col.name.sql_quoted}
            AND cc.definition = #{cstrt.expression.sql_quoted}
          )
          BEGIN
            #{adoption_error_sql "Expected CHECK constraint on #{col.name} of #{t.qualified_name} for #{cstrt.expression} not present."}
          END
        })
      end
    end
    
    # Table constraints
    t.constraints.select {|c| ['PRIMARY KEY', 'UNIQUE'].include? c.type}.each do |c|
      tests << TableTypeKeyConstraintChecks.new(t, c, method(:adoption_error_sql)).to_s
    end
    t.constraints.select {|c| c.type == 'CHECK'}.each do |c|
      tests << (dedent %Q{
        IF NOT EXISTS (
          SELECT * FROM sys.check_constraints cc
          JOIN sys.table_types tt ON tt.type_table_object_id = cc.parent_object_id
          JOIN sys.schemas s ON s.schema_id = tt.schema_id
          WHERE QUOTENAME(s.name) = #{t.schema.sql_quoted}
          AND QUOTENAME(tt.name) = #{t.name.sql_quoted}
          AND cc.parent_column_id = 0
          AND cc.definition = #{c.expression.sql_quoted}
        )
        BEGIN
          #{adoption_error_sql "Expected CHECK constraint for #{c.expression} on #{t.qualified_name} not present."}
        END
      })
    end
  end.join("\n")
end

#check_tables_exist_and_structured_as_expected_sql ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 791

def check_tables_exist_and_structured_as_expected_sql
  db_expectations.tables.map do |table|
    TableAdoptionChecks.new(table, method(:adoption_error_sql)).to_s
  end # Do not join -- each needs a separate batch (they use variables)
end

#check_user_defined_types_sql ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 270

def check_user_defined_types_sql
  db_expectations.types.map do |t|
    dedent %Q{
      IF NOT EXISTS (
        SELECT * FROM sys.types t
        JOIN sys.schemas s ON t.schema_id = s.schema_id
        WHERE QUOTENAME(s.name) = #{t.schema.sql_quoted}
        AND QUOTENAME(t.name) = #{t.name.sql_quoted}
      )
      BEGIN
        #{adoption_error_sql "User-defined scalar type #{t.qualified_name} is not defined."}
      END
      
      IF NOT EXISTS (
        SELECT * FROM sys.types t
        JOIN sys.schemas s ON t.schema_id = s.schema_id
        WHERE t.is_user_defined <> 0
        AND t.is_assembly_type = 0
        AND t.user_type_id NOT IN (
          SELECT st.system_type_id
          FROM sys.types st
        )
        AND QUOTENAME(s.name) = #{t.schema.sql_quoted}
        AND QUOTENAME(t.name) = #{t.name.sql_quoted}
      )
      BEGIN
        #{adoption_error_sql "#{t.qualified_name} is not a user-defined type."}
      END
      
    } + (
      if t.respond_to?(:columns)
        check_table_type_components_sql(t)
      else
        dedent %Q{
          IF NOT EXISTS (
            SELECT * FROM sys.types t
            JOIN sys.schemas s ON t.schema_id = s.schema_id
            JOIN sys.types bt ON t.system_type_id = bt.user_type_id
            WHERE t.is_user_defined <> 0
            AND QUOTENAME(s.name) = #{t.schema.sql_quoted}
            AND QUOTENAME(t.name) = #{t.name.sql_quoted}
            AND QUOTENAME(bt.name) = #{t.base_type.sql_quoted}
            AND t.is_nullable #{t.nullable? ? "<>" : "="} 0
            #{
              case t.capacity
              when 'max'
                "AND t.max_length = -1"
              when Integer
                "AND t.max_length = #{t.element_size * t.capacity}"
              end
            }
            #{"AND t.precision = #{t.precision}" if t.precision}
            #{"AND t.scale = #{t.scale}" if t.scale}
          )
          BEGIN
            #{adoption_error_sql "#{t.qualified_name} is not defined as #{t.type_spec}."}
          END
        }
      end
    )
  end
end

#compose_sql(&blk) ⇒ `Object`



120
121
122

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 120

def compose_sql(&blk)
  IndentedStringBuilder.dsl(&blk)
end

#create_script(path) ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 23

def create_script(path)
  if Utils.dry_run?
    base, _, ext = path.to_s.rpartition('.')
    path = [base, 'dry-run', ext].join('.')
  end
  Pathname(path).open('w') do |script|
    script.puts adoption_sql
  end
end

#definition_matches_by_hash(expr, definition, indent: ' ') ⇒ `Object`

indent gives indentation for 2nd and later lines, or may be false to put all hash comparisons on the same line

*** NEW IMPLEMENTATION IGNORES WHITESPACE WHEN COMPARING FUNCTION DEFINITIONS ***

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 1788

def definition_matches_by_hash(expr, definition, indent: '  ')
  digest_alg = Digest::MD5
  defn_reduced = definition.gsub(/\r|\n| /, "").encode(Encoding::UTF_16LE)
  (0..defn_reduced.length).step(4000).map do |start|
    begin
      digest_alg.hexdigest(defn_reduced[start, 4000])
    rescue Exception
      require 'pry'; binding.pry unless $STOP_PRYING || ($STOP_PRYING_FOR || {})[:context]
      raise
    end
    hash = digest_alg.hexdigest(defn_reduced[start, 4000])
    "HASHBYTES('md5', SUBSTRING(REPLACE(REPLACE(REPLACE(#{expr}, NCHAR(10), ''), NCHAR(13), ''), ' ', ''), #{start + 1}, 4000)) = 0x#{hash}"
  end.join("#{indent ? "\n" + indent : ' '}AND ")
end

#write_version_bridge_record_sql ⇒ `Object`

# File 'lib/mkxms/mssql/adoption_script_writer.rb', line 2147

def write_version_bridge_record_sql
  dedent %Q{
    INSERT INTO [xmigra].[applied] ([MigrationID], [VersionBridgeMark], [Description])
    VALUES (#{strlit @xn_builder.migrations.last.id}, 1, N'Adoption of existing structure.');
  }
end

Class: Mkxms::Mssql::AdoptionScriptWriter

Defined Under Namespace

Constant Summary

Constants included from SqlStringManipulators

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from SqlStringManipulators

Constructor Details

#initialize(db_expectations) ⇒ AdoptionScriptWriter

Instance Attribute Details

#db_expectations ⇒ Object (readonly)

Instance Method Details

#access_object_adoption_sql(type, qualified_name) ⇒ Object

#adopt_indexes_sql ⇒ Object

#adopt_permissions_sql ⇒ Object

#adopt_statistics_sql ⇒ Object

#adopt_stored_procedures_sql ⇒ Object

#adopt_udfs_sql ⇒ Object

#adopt_views_sql ⇒ Object

#adoption_sql ⇒ Object

#check_check_constraints_sql ⇒ Object

#check_clr_aggregates ⇒ Object

#check_clr_assemblies ⇒ Object

#check_clr_types ⇒ Object

#check_dml_triggers ⇒ Object

#check_expected_column_defaults_exist_sql ⇒ Object

#check_expected_role_membership_sql ⇒ Object

#check_expected_roles_exist_sql ⇒ Object

#check_expected_schemas_exist_sql ⇒ Object

#check_foreign_key_constraints_sql ⇒ Object

#check_primary_key_and_unique_constraints_sql ⇒ Object

#check_synonyms ⇒ Object

#check_table_type_components_sql(t) ⇒ Object

#check_tables_exist_and_structured_as_expected_sql ⇒ Object

#check_user_defined_types_sql ⇒ Object

#compose_sql(&blk) ⇒ Object

#create_script(path) ⇒ Object

#definition_matches_by_hash(expr, definition, indent: ' ') ⇒ Object

#write_version_bridge_record_sql ⇒ Object

#initialize(db_expectations) ⇒ `AdoptionScriptWriter`