class Mkxms::Mssql::AdoptionScriptWriter
Attributes
db_expectations[R]
Public Class Methods
new(db_expectations)
click to toggle source
# 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
Public Instance Methods
access_object_adoption_sql(type, qualified_name)
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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_error_sql(message)
click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 141 def adoption_error_sql(message) "INSERT INTO [xmigra].[adoption_errors] (message) VALUES (#{strlit(message)});" end
adoption_sql()
click to toggle source
# 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_adoption_error_table_empty_sql()
click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 145 def check_adoption_error_table_empty_sql dedent %Q{ IF EXISTS ( SELECT TOP 1 * FROM [xmigra].[adoption_errors] ) BEGIN SELECT * FROM [xmigra].[adoption_errors]; RAISERROR (N'Database adoption failed.', 11, 1); END DROP TABLE [xmigra].[adoption_errors]; } end
check_check_constraints_sql()
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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)
click to toggle source
# 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()
click to toggle source
# 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()
click to toggle source
# 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)
click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 120 def compose_sql(&blk) IndentedStringBuilder.dsl(&blk) end
create_adoption_error_table_sql()
click to toggle source
# File lib/mkxms/mssql/adoption_script_writer.rb, line 125 def create_adoption_error_table_sql dedent %Q{ IF EXISTS ( SELECT * FROM sys.objects o WHERE o.object_id = OBJECT_ID(N'[xmigra].[adoption_errors]') ) BEGIN DROP TABLE [xmigra].[adoption_errors]; END GO CREATE TABLE [xmigra].[adoption_errors] ( [message] nvarchar(1000) ); } end
create_script(path)
click to toggle source
# 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: ' ')
click to toggle source
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()
click to toggle source
# 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