module XMigra::MSSQLSpecifics
Constants
- DBNAME_PATTERN
- IDENTIFIER_SUBPATTERN
- ID_COLLATION
- STATISTICS_FILE
- SYSTEM_NAME
Public Class Methods
init_schema(schema_config)
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1221 def init_schema(schema_config) Console.output_section "Microsoft SQL Server Specifics" do if Console.yes_no("Use more verbose syntax compatible with SQL Server 2005", :no) schema_config.dbinfo["MSSQL 2005 compatible"] = true puts "Configured for SQL Server 2005 compatibility mode." end end end
object_type_codes(type)
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1209 def object_type_codes(type) case type when StoredProcedure then %w{P PC} when View then ['V'] when Function then %w{AF FN FS FT IF TF} end end
string_literal(s)
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1217 def string_literal(s) "N'#{s.gsub("'","''")}'" end
strip_identifier_quoting(s)
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1200 def strip_identifier_quoting(s) case when s.empty? then return s when s[0,1] == "[" && s[-1,1] == "]" then return s[1..-2] when s[0,1] == '"' && s[-1,1] == '"' then return s[1..-2] else return s end end
Public Instance Methods
alter_table_columns_sql_statements(col_pairs)
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1192 def alter_table_columns_sql_statements(col_pairs) col_pairs.map do |_, col| nullability = (col.nullable? ? "" : "NOT ") + "NULL" "ALTER TABLE #{name} ALTER COLUMN #{col.name} #{col.type} #{nullability};" end end
amend_script_parts(parts)
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 123 def amend_script_parts(parts) parts.insert_after( :create_and_fill_indexes_table_sql, :create_and_fill_statistics_table_sql ) parts.insert_after( :remove_undesired_indexes_sql, :remove_undesired_statistics_sql ) parts.insert_after(:create_new_indexes_sql, :create_new_statistics_sql) end
batch_separator()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1016 def batch_separator "GO\n" end
branch_id_literal()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1066 def branch_id_literal @mssql_branch_id_literal ||= MSSQLSpecifics.string_literal(XMigra.secure_digest(branch_identifier)) end
branch_upgrade_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1081 def branch_upgrade_sql return unless respond_to? :branch_identifier parts = [<<-"END_OF_SQL"] IF #{upgrading_to_new_branch_test_sql} BEGIN PRINT N'Migrating from previous schema branch:'; DECLARE @sqlcmd NVARCHAR(MAX); DECLARE CmdCursor CURSOR LOCAL FOR SELECT bu.[UpgradeSql] FROM [xmigra].[branch_upgrade] bu WHERE bu.[Next] = #{branch_id_literal} ORDER BY bu.[ApplicationOrder] ASC; OPEN CmdCursor; FETCH NEXT FROM CmdCursor INTO @sqlcmd; WHILE @@FETCH_STATUS = 0 BEGIN EXECUTE sp_executesql @sqlcmd; FETCH NEXT FROM CmdCursor INTO @sqlcmd; END; CLOSE CmdCursor; DEALLOCATE CmdCursor; DECLARE @applied NVARCHAR(80); DECLARE @old_branch NVARCHAR(80); SELECT TOP(1) @applied = [CompletesMigration], @old_branch = [Current] FROM [xmigra].[branch_upgrade] WHERE [Next] = #{branch_id_literal}; -- Delete the "applied" record for the migration if there was one, so that -- a new record with this ID can be inserted. DELETE FROM [xmigra].[applied] WHERE [MigrationID] = @applied; -- Create a "version bridge" record in the "applied" table for the branch upgrade INSERT INTO [xmigra].[applied] ([MigrationID], [VersionBridgeMark], [Description]) VALUES (@applied, 1, N'Branch upgrade from branch ' + @old_branch); END; DELETE FROM [xmigra].[branch_upgrade]; END_OF_SQL if branch_upgrade.applicable? migrations batch_template = <<-"END_OF_SQL" INSERT INTO [xmigra].[branch_upgrade] ([Current], [Next], [CompletesMigration], [UpgradeSql]) VALUES ( #{branch_id_literal}, #{MSSQLSpecifics.string_literal(branch_upgrade.target_branch)}, #{MSSQLSpecifics.string_literal(branch_upgrade.migration_completed_id)}, %s ); END_OF_SQL each_batch(branch_upgrade.sql) do |batch| # Insert the batch into the [xmigra].[branch_upgrade] table parts << (batch_template % MSSQLSpecifics.string_literal(batch)) end else # Insert a placeholder that only declares the current branch of the schema parts << <<-"END_OF_SQL" INSERT INTO [xmigra].[branch_upgrade] ([Current]) VALUES (#{branch_id_literal}); END_OF_SQL end return parts.join("\n") end
check_chain_continuity_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 475 def check_chain_continuity_sql <<-"END_OF_SQL" IF NOT #{upgrading_to_new_branch_test_sql} BEGIN PRINT N'Checking migration chain continuity:'; -- Get the [xmigra].[migrations] ApplicationOrder of the most recent version bridge migration DECLARE @BridgePoint INT; SET @BridgePoint = ( SELECT COALESCE(MAX(m.[ApplicationOrder]), 0) FROM [xmigra].[applied] a INNER JOIN [xmigra].[migrations] m ON a.[MigrationID] = m.[MigrationID] WHERE a.[VersionBridgeMark] <> 0 ); -- Test for previously applied migrations that break the continuity of the -- migration chain in this script: IF EXISTS ( SELECT * FROM [xmigra].[applied] a INNER JOIN [xmigra].[migrations] m ON a.[MigrationID] = m.[MigrationID] INNER JOIN [xmigra].[migrations] p ON m.[ApplicationOrder] - 1 = p.[ApplicationOrder] WHERE p.[ApplicationOrder] > @BridgePoint AND p.[MigrationID] NOT IN ( SELECT a2.[MigrationID] FROM [xmigra].[applied] a2 ) ) BEGIN RAISERROR( N'Previously applied migrations interrupt the continuity of the migration chain', 11, 1 ); END; END; END_OF_SQL end
check_execution_environment_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 135 def check_execution_environment_sql <<-"END_OF_SQL" PRINT N'Checking execution environment:'; IF DB_NAME() IN ('master', 'tempdb', 'model', 'msdb') BEGIN RAISERROR(N'Please select an appropriate target database for the update script.', 11, 1); END; END_OF_SQL end
check_existence_sql(for_existence, error_message)
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1020 def check_existence_sql(for_existence, error_message) error_message = sprintf(error_message, quoted_name) return <<-"END_OF_SQL" IF #{"NOT" if for_existence} #{existence_test_sql} RAISERROR(N'#{error_message}', 11, 1); END_OF_SQL end
check_preceding_migrations_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 433 def check_preceding_migrations_sql parts = [] parts << (<<-"END_OF_SQL") if production IF EXISTS ( SELECT TOP(1) * FROM [xmigra].[branch_upgrade] ) AND NOT EXISTS ( SELECT TOP(1) * FROM [xmigra].[branch_upgrade] WHERE #{branch_id_literal} IN ([Current], [Next]) ) RAISERROR (N'Existing database is from a different (and non-upgradable) branch.', 11, 1); END_OF_SQL parts << (<<-"END_OF_SQL") IF NOT #{upgrading_to_new_branch_test_sql} BEGIN PRINT N'Checking preceding migrations:'; -- Get the ApplicationOrder of the most recent version bridge migration DECLARE @VersionBridge INT; SET @VersionBridge = ( SELECT COALESCE(MAX([ApplicationOrder]), 0) FROM [xmigra].[applied] WHERE [VersionBridgeMark] <> 0 ); -- Check for existence of applied migrations after the latest version -- bridge that are not in [xmigra].[migrations] IF EXISTS ( SELECT * FROM [xmigra].[applied] a WHERE a.[ApplicationOrder] > @VersionBridge AND a.[MigrationID] NOT IN ( SELECT m.[MigrationID] FROM [xmigra].[migrations] m ) ) RAISERROR (N'Unknown in-branch migrations have been applied.', 11, 1); END; END_OF_SQL return parts.join('') end
create_and_fill_indexes_table_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 372 def create_and_fill_indexes_table_sql intro = <<-"END_OF_SQL" PRINT N'Creating and filling index manipulation table:'; IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xmigra].[updated_indexes]') AND type IN (N'U') ) BEGIN DROP TABLE [xmigra].[updated_indexes]; END; GO CREATE TABLE [xmigra].[updated_indexes] ( [IndexID] NVARCHAR(80) NOT NULL PRIMARY KEY ); GO END_OF_SQL insertion = <<-"END_OF_SQL" INSERT INTO [xmigra].[updated_indexes] ([IndexID]) VALUES END_OF_SQL strlit = MSSQLSpecifics.method :string_literal return intro + (insertion + indexes.collect do |index| "(#{strlit[index.id]})" end.join(",\n") + ";\n" unless indexes.empty?).to_s end
create_and_fill_migration_table_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 325 def create_and_fill_migration_table_sql intro = <<-"END_OF_SQL" IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xmigra].[migrations]') AND type IN (N'U') ) BEGIN DROP TABLE [xmigra].[migrations]; END; GO CREATE TABLE [xmigra].[migrations] ( [MigrationID] nvarchar(80) COLLATE #{ID_COLLATION} NOT NULL, [ApplicationOrder] int NOT NULL, [Description] ntext NOT NULL, [Install] bit NOT NULL DEFAULT(0) ); GO END_OF_SQL mig_insert = <<-"END_OF_SQL" INSERT INTO [xmigra].[migrations] ( [MigrationID], [ApplicationOrder], [Description] ) VALUES END_OF_SQL if (@db_info || {}).fetch('MSSQL 2005 compatible', false).eql?(true) parts = [intro] (0...migrations.length).each do |i| m = migrations[i] description_literal = MSSQLSpecifics.string_literal(m.description.strip) parts << mig_insert + "(N'#{m.id}', #{i + 1}, #{description_literal});\n" end return parts.join('') else return intro + mig_insert + (0...migrations.length).collect do |i| m = migrations[i] description_literal = MSSQLSpecifics.string_literal(m.description.strip) "(N'#{m.id}', #{i + 1}, #{description_literal})" end.join(",\n") + ";\n" end end
create_and_fill_statistics_table_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 402 def create_and_fill_statistics_table_sql intro = <<-"END_OF_SQL" PRINT N'Creating and filling statistics object manipulation table:'; IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xmigra].[updated_statistics]') AND type in (N'U') ) BEGIN DROP TABLE [xmigra].[updated_statistics]; END; GO CREATE TABLE [xmigra].[updated_statistics] ( [Name] nvarchar(100) NOT NULL PRIMARY KEY, [Columns] nvarchar(256) NOT NULL ); GO END_OF_SQL insertion = <<-"END_OF_SQL" INSERT INTO [xmigra].[updated_statistics] ([Name], [Columns]) VALUES END_OF_SQL strlit = MSSQLSpecifics.method :string_literal return intro + (insertion + stats_objs.collect do |stats_obj| "(#{strlit[stats_obj.name]}, #{strlit[stats_obj.columns]})" end.join(",\n") + ";\n" unless stats_objs.empty?).to_s end
create_new_indexes_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 749 def create_new_indexes_sql indexes.collect do |index| index_id_literal = MSSQLSpecifics.string_literal(index.id) index_name_literal = MSSQLSpecifics.string_literal(index.name) <<-"END_OF_SQL" PRINT N'Index ' + #{index_id_literal} + ':'; IF EXISTS( SELECT * FROM [xmigra].[updated_indexes] ui WHERE ui.[IndexID] = #{index_id_literal} AND ui.[IndexID] NOT IN ( SELECT i.[IndexID] FROM [xmigra].[indexes] i ) ) BEGIN IF EXISTS ( SELECT * FROM sys.indexes WHERE [name] = #{index_name_literal} ) BEGIN RAISERROR(N'An index already exists named %s', 11, 1, #{index_name_literal}); END; PRINT N' Creating...'; #{index.definition_sql}; IF (SELECT COUNT(*) FROM sys.indexes WHERE [name] = #{index_name_literal}) <> 1 BEGIN RAISERROR(N'Index %s was not created by its definition.', 11, 1, #{index_name_literal}); END; INSERT INTO [xmigra].[indexes] ([IndexID], [name]) VALUES (#{index_id_literal}, #{index_name_literal}); END ELSE BEGIN PRINT N' Already exists.'; END; END_OF_SQL end.join(ddl_block_separator) end
create_new_statistics_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 791 def create_new_statistics_sql stats_objs.collect do |stats_obj| stats_name = MSSQLSpecifics.string_literal(stats_obj.name) strlit = lambda {|s| MSSQLSpecifics.string_literal(s)} stats_obj.creation_sql <<-"END_OF_SQL" PRINT N'Statistics object #{stats_obj.name}:'; IF EXISTS ( SELECT * FROM [xmigra].[updated_statistics] us WHERE us.[Name] = #{stats_name} AND us.[Columns] NOT IN ( SELECT s.[Columns] FROM [xmigra].[statistics] s WHERE s.[Name] = us.[Name] ) ) BEGIN IF EXISTS ( SELECT * FROM sys.stats WHERE [name] = #{stats_name} ) BEGIN RAISERROR(N'A statistics object named %s already exists.', 11, 1, #{stats_name}) END; PRINT N' Creating...'; #{stats_obj.creation_sql} INSERT INTO [xmigra].[statistics] ([Name], [Columns]) VALUES (#{stats_name}, #{strlit[stats_obj.columns]}) END ELSE BEGIN PRINT N' Already exists.'; END; END_OF_SQL end.join(ddl_block_separator) end
creation_notice()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1030 def creation_notice return "PRINT " + MSSQLSpecifics.string_literal("Creating #{printable_type} #{quoted_name}:") + ";" end
ddl_block_separator()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 41 def ddl_block_separator; "\nGO\n"; end
each_batch(sql) { |batch| ... }
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 999 def each_batch(sql) current_batch_lines = [] sql.each_line do |line| if line.strip.upcase == 'GO' batch = current_batch_lines.join('') yield batch unless batch.strip.empty? current_batch_lines.clear else current_batch_lines << line end end unless current_batch_lines.empty? batch = current_batch_lines.join('') yield batch unless batch.strip.empty? end end
ensure_permissions_table_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 840 def ensure_permissions_table_sql strlit = MSSQLSpecifics.method(:string_literal) <<-"END_OF_SQL" -- ------------ SET UP XMIGRA PERMISSION TRACKING OBJECTS ------------ -- PRINT N'Setting up XMigra permission tracking:'; IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = N'xmigra' ) BEGIN EXEC sp_executesql N' CREATE SCHEMA [xmigra] AUTHORIZATION [dbo]; '; END; GO IF NOT EXISTS( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xmigra].[revokable_permissions]') AND type IN (N'U') ) BEGIN CREATE TABLE [xmigra].[revokable_permissions] ( [permissions] nvarchar(200) NOT NULL, [object] nvarchar(260) NOT NULL, [principal_id] int NOT NULL ) ON [PRIMARY]; END; GO IF EXISTS( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xmigra].[ip_prepare_revoke]') AND type IN (N'P', N'PC') ) BEGIN DROP PROCEDURE [xmigra].[ip_prepare_revoke]; END; GO CREATE PROCEDURE [xmigra].[ip_prepare_revoke] ( @permissions nvarchar(200), @object nvarchar(260), @principal sysname ) AS BEGIN INSERT INTO [xmigra].[revokable_permissions] ([permissions], [object], [principal_id]) VALUES (@permissions, @object, DATABASE_PRINCIPAL_ID(@principal)); END; END_OF_SQL end
ensure_version_tables_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 145 def ensure_version_tables_sql <<-"END_OF_SQL" PRINT N'Ensuring version tables:'; IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = N'xmigra' ) BEGIN EXEC sp_executesql N' CREATE SCHEMA [xmigra] AUTHORIZATION [dbo]; '; END; GO IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xmigra].[applied]') AND type IN (N'U') ) BEGIN CREATE TABLE [xmigra].[applied] ( [MigrationID] nvarchar(80) COLLATE #{ID_COLLATION} NOT NULL, [ApplicationOrder] int IDENTITY(1,1) NOT NULL, [VersionBridgeMark] bit NOT NULL, [Description] nvarchar(max) NOT NULL, CONSTRAINT [PK_version] PRIMARY KEY CLUSTERED ( [MigrationID] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]; END; GO IF NOT EXISTS ( SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[xmigra].[applied]') AND name = N'MigrationID' AND collation_name = N'#{ID_COLLATION}' ) BEGIN ALTER TABLE xmigra.applied DROP CONSTRAINT PK_version; ALTER TABLE xmigra.applied ALTER COLUMN [MigrationID] nvarchar(80) COLLATE Latin1_General_CS_AS NOT NULL; ALTER TABLE xmigra.applied ADD CONSTRAINT PK_version PRIMARY KEY ([MigrationID] ASC) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]; END; IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xmigra].[previous_states]') AND type IN (N'U') ) BEGIN CREATE TABLE [xmigra].[previous_states] ( [Changed] datetime NOT NULL, [MigrationApplicationOrder] int NOT NULL, [FromMigrationID] nvarchar(80) COLLATE #{ID_COLLATION}, [ToRangeStartMigrationID] nvarchar(80) COLLATE #{ID_COLLATION} NOT NULL, [ToRangeEndMigrationID] nvarchar(80) COLLATE #{ID_COLLATION} NOT NULL, CONSTRAINT [PK_previous_states] PRIMARY KEY CLUSTERED ( [Changed] ASC, [MigrationApplicationOrder] ASC ) ); END; GO IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xmigra].[DF_version_VersionBridgeMark]') AND type IN (N'D') ) BEGIN ALTER TABLE [xmigra].[applied] ADD CONSTRAINT [DF_version_VersionBridgeMark] DEFAULT (0) FOR [VersionBridgeMark]; END; GO IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xmigra].[access_objects]') AND type IN (N'U') ) BEGIN CREATE TABLE [xmigra].[access_objects] ( [type] nvarchar(40) NOT NULL, [name] nvarchar(256) NOT NULL, [order] int identity(1,1) NOT NULL, CONSTRAINT [PK_access_objects] PRIMARY KEY CLUSTERED ( [name] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY]; END; GO IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xmigra].[indexes]') AND type in (N'U') ) BEGIN CREATE TABLE [xmigra].[indexes] ( [IndexID] nvarchar(80) NOT NULL PRIMARY KEY, [name] nvarchar(256) NOT NULL ) ON [PRIMARY]; END; IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xmigra].[statistics]') AND type in (N'U') ) BEGIN CREATE TABLE [xmigra].[statistics] ( [Name] nvarchar(100) NOT NULL PRIMARY KEY, [Columns] nvarchar(256) NOT NULL ) ON [PRIMARY]; END; IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xmigra].[branch_upgrade]') AND type in (N'U') ) BEGIN CREATE TABLE [xmigra].[branch_upgrade] ( [ApplicationOrder] int identity(1,1) NOT NULL, [Current] nvarchar(80) COLLATE #{ID_COLLATION} NOT NULL PRIMARY KEY, [Next] nvarchar(80) COLLATE #{ID_COLLATION} NULL, [UpgradeSql] nvarchar(max) NULL, [CompletesMigration] nvarchar(80) COLLATE #{ID_COLLATION} NULL ) ON [PRIMARY]; END; GO ALTER TABLE [xmigra].[branch_upgrade] ALTER COLUMN [CompletesMigration] nvarchar(80) COLLATE #{ID_COLLATION} NULL; IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xmigra].[last_applied_migrations]') AND type IN (N'V') ) BEGIN DROP VIEW [xmigra].[last_applied_migrations]; END; GO CREATE VIEW [xmigra].[last_applied_migrations] AS SELECT ROW_NUMBER() OVER (ORDER BY a.[ApplicationOrder] DESC) AS [RevertOrder], a.[Description] FROM [xmigra].[applied] a WHERE a.[ApplicationOrder] > COALESCE(( SELECT TOP (1) ps.[MigrationApplicationOrder] FROM [xmigra].[previous_states] ps JOIN [xmigra].[applied] a2 ON ps.[ToRangeStartMigrationID] = a2.[MigrationID] ORDER BY ps.[Changed] DESC ), 0); END_OF_SQL end
existence_test_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1054 def existence_test_sql object_type_list = object_type_codes.collect {|t| "N'#{t}'"}.join(', ') return <<-"END_OF_SQL" EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'#{quoted_name}') AND type IN (#{object_type_list}) ) END_OF_SQL end
filename_metavariable()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 42 def filename_metavariable; "[{filename}]"; end
function_definition_template_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1180 def function_definition_template_sql <<-"END_OF_SQL" CREATE FUNCTION [{filename}] ( <<<parameters>>> ) RETURNS <<<return-type>>> AS BEGIN <<<statements>>> RETURN <<<return-value-expression>>>; END END_OF_SQL end
grant_permissions_sql(permissions, object, principal)
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 945 def grant_permissions_sql(permissions, object, principal) strlit = MSSQLSpecifics.method(:string_literal) permissions_string = permissions.to_a.join(', ') <<-"END_OF_SQL" PRINT N'Granting #{permissions_string} on #{object} to #{principal}:'; GRANT #{permissions_string} ON #{object} TO #{principal}; EXEC [xmigra].[ip_prepare_revoke] #{strlit[permissions_string]}, #{strlit[object]}, #{strlit[principal]}; END_OF_SQL end
granting_permissions_comment_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 937 def granting_permissions_comment_sql <<-"END_OF_SQL" -- ---------------------- GRANTING PERMISSIONS ----------------------- -- END_OF_SQL end
in_ddl_transaction(options={}) { || ... }
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 60 def in_ddl_transaction(options={}) parts = [] parts << <<-"END_OF_SQL" SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO SET NOCOUNT ON GO -------------------- ERROR REFERENCE LINE -------------------- DECLARE @BATCH_START_OFFSET INTEGER; SET @BATCH_START_OFFSET = 0; BEGIN TRY BEGIN TRAN; END_OF_SQL offset_lines = 5 each_batch(yield) do |batch| batch_literal = MSSQLSpecifics.string_literal("\n" + batch) parts << "SET @BATCH_START_OFFSET = #{offset_lines}; EXEC sp_executesql @statement = #{batch_literal}; SET @BATCH_START_OFFSET = 0;" offset_lines += parts[-1].count("\n") + 1 end if options[:dry_run] parts << " PRINT N'Dry-run successful. Rolling back changes.'; ROLLBACK TRAN;" else parts << " COMMIT TRAN;" end parts << <<-"END_OF_SQL" END TRY BEGIN CATCH ROLLBACK TRAN; DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; PRINT N'Update failed: ' + ERROR_MESSAGE(); PRINT N' State: ' + CAST(ERROR_STATE() AS NVARCHAR); PRINT N' Line: ' + CAST(@BATCH_START_OFFSET + ERROR_LINE() - 1 AS NVARCHAR) + N' after ERROR REFERENCE LINE' SELECT @ErrorMessage = N'Update failed: ' + ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); -- Use RAISERROR inside the CATCH block to return error -- information about the original error that caused -- execution to jump to the CATCH block. RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH; END_OF_SQL return parts.join("\n") end
index_template_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1156 def index_template_sql <<-"END_OF_SQL" CREATE INDEX [{filename}] ON <<<table>>> (<<<columns>>>); END_OF_SQL end
insert_access_creation_record_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 956 def insert_access_creation_record_sql name_literal = MSSQLSpecifics.string_literal(quoted_name) <<-"END_OF_SQL" INSERT INTO [xmigra].[access_objects] ([type], [name]) VALUES (N'#{self.class::OBJECT_TYPE}', #{name_literal}); END_OF_SQL end
migration_application_sql()
click to toggle source
Call on an extended Migration
object to get the SQL to execute.
# File lib/xmigra/db_support/mssql.rb, line 966 def migration_application_sql id_literal = MSSQLSpecifics.string_literal(id) template = <<-"END_OF_SQL" IF EXISTS ( SELECT * FROM [xmigra].[migrations] WHERE [MigrationID] = #{id_literal} AND [Install] <> 0 ) BEGIN PRINT #{MSSQLSpecifics.string_literal('Applying "' + File.basename(file_path) + '":')}; %s INSERT INTO [xmigra].[applied] ([MigrationID], [Description]) VALUES (#{id_literal}, #{MSSQLSpecifics.string_literal(description)}); END; END_OF_SQL parts = [] each_batch(sql) do |batch| parts << batch end return (template % parts.collect do |batch| "EXEC sp_executesql @statement = " + MSSQLSpecifics.string_literal(batch) + ";" end.join("\n")) end
name_parts()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1034 def name_parts if m = DBNAME_PATTERN.match(name) [m[1], m[2]].compact.collect do |p| MSSQLSpecifics.strip_identifier_quoting(p) end else raise XMigra::Error, "Invalid database object name" end end
object_type_codes()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1050 def object_type_codes MSSQLSpecifics.object_type_codes(self) end
procedure_definition_template_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1170 def procedure_definition_template_sql <<-"END_OF_SQL" CREATE PROCEDURE [{filename}] <<<parameters>>> AS BEGIN <<<statements>>> END END_OF_SQL end
production_config_check_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 585 def production_config_check_sql unless production id_literal = MSSQLSpecifics.string_literal(@migrations[0].id) <<-"END_OF_SQL" PRINT N'Checking for production status:'; IF EXISTS ( SELECT * FROM [xmigra].[migrations] WHERE [MigrationID] = #{id_literal} AND [Install] <> 0 ) BEGIN CREATE TABLE [xmigra].[development] ( [info] nvarchar(200) NOT NULL PRIMARY KEY ); END; GO IF NOT EXISTS ( SELECT * FROM [sys].[objects] WHERE object_id = OBJECT_ID(N'[xmigra].[development]') AND type = N'U' ) RAISERROR(N'Development script cannot be applied to a production database.', 11, 1); END_OF_SQL end end
quoted_name()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1044 def quoted_name name_parts.collect do |p| "[]".insert(1, p) end.join('.') end
remove_access_artifacts_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 612 def remove_access_artifacts_sql # Iterate the [xmigra].[access_objects] table and drop all access # objects previously created by xmigra return <<-"END_OF_SQL" PRINT N'Removing data access artifacts:'; DECLARE @sqlcmd NVARCHAR(1000); -- Built SQL command DECLARE @obj_name NVARCHAR(256); -- Name of object to drop DECLARE @obj_type NVARCHAR(40); -- Type of object to drop DECLARE AccObjs_cursor CURSOR LOCAL FOR SELECT [name], [type] FROM [xmigra].[access_objects] ORDER BY [order] DESC; OPEN AccObjs_cursor; FETCH NEXT FROM AccObjs_cursor INTO @obj_name, @obj_type; WHILE @@FETCH_STATUS = 0 BEGIN SET @sqlcmd = N'DROP ' + @obj_type + N' ' + @obj_name + N';'; EXEC sp_executesql @sqlcmd; FETCH NEXT FROM AccObjs_cursor INTO @obj_name, @obj_type; END; CLOSE AccObjs_cursor; DEALLOCATE AccObjs_cursor; DELETE FROM [xmigra].[access_objects]; END_OF_SQL end
remove_undesired_indexes_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 645 def remove_undesired_indexes_sql <<-"END_OF_SQL" PRINT N'Removing undesired indexes:'; -- Iterate over indexes in [xmigra].[indexes] that don't have an entry in -- [xmigra].[updated_indexes]. DECLARE @sqlcmd NVARCHAR(1000); -- Built SQL command DECLARE @index_name NVARCHAR(256); -- Name of index to drop DECLARE @table_name SYSNAME; -- Name of table owning index DECLARE @match_count INT; -- Number of matching index names DECLARE Index_cursor CURSOR LOCAL FOR SELECT xi.[name], MAX(QUOTENAME(OBJECT_SCHEMA_NAME(si.object_id)) + N'.' + QUOTENAME(OBJECT_NAME(si.object_id))), COUNT(*) FROM [xmigra].[indexes] xi INNER JOIN sys.indexes si ON si.[name] = xi.[name] WHERE xi.[IndexID] NOT IN ( SELECT [IndexID] FROM [xmigra].[updated_indexes] ) GROUP BY xi.[name]; OPEN Index_cursor; FETCH NEXT FROM Index_cursor INTO @index_name, @table_name, @match_count; WHILE @@FETCH_STATUS = 0 BEGIN IF @match_count > 1 BEGIN RAISERROR(N'Multiple indexes are named %s', 11, 1, @index_name); END; SET @sqlcmd = N'DROP INDEX ' + @index_name + N' ON ' + @table_name + N';'; EXEC sp_executesql @sqlcmd; PRINT N' Removed ' + @index_name + N'.'; FETCH NEXT FROM Index_cursor INTO @index_name, @table_name, @match_count; END; CLOSE Index_cursor; DEALLOCATE Index_cursor; DELETE FROM [xmigra].[indexes] WHERE [IndexID] NOT IN ( SELECT ui.[IndexID] FROM [xmigra].[updated_indexes] ui ); END_OF_SQL end
remove_undesired_statistics_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 696 def remove_undesired_statistics_sql <<-"END_OF_SQL" PRINT N'Removing undesired statistics objects:'; -- Iterate over statistics in [xmigra].[statistics] that don't have an entry in -- [xmigra].[updated_statistics]. DECLARE @sqlcmd NVARCHAR(1000); -- Built SQL command DECLARE @statsobj_name NVARCHAR(256); -- Name of statistics object to drop DECLARE @table_name SYSNAME; -- Name of table owning the statistics object DECLARE @match_count INT; -- Number of matching statistics object names DECLARE Stats_cursor CURSOR LOCAL FOR SELECT QUOTENAME(xs.[Name]), MAX(QUOTENAME(OBJECT_SCHEMA_NAME(ss.object_id)) + N'.' + QUOTENAME(OBJECT_NAME(ss.object_id))), COUNT(ss.object_id) FROM [xmigra].[statistics] xs INNER JOIN sys.stats ss ON ss.[name] = xs.[Name] WHERE xs.[Columns] NOT IN ( SELECT us.[Columns] FROM [xmigra].[updated_statistics] us WHERE us.[Name] = xs.[Name] ) GROUP BY xs.[Name]; OPEN Stats_cursor; FETCH NEXT FROM Stats_cursor INTO @statsobj_name, @table_name, @match_count; WHILE @@FETCH_STATUS = 0 BEGIN IF @match_count > 1 BEGIN RAISERROR(N'Multiple indexes are named %s', 11, 1, @statsobj_name); END; SET @sqlcmd = N'DROP STATISTICS ' + @table_name + N'.' + @statsobj_name + N';'; EXEC sp_executesql @sqlcmd; PRINT N' Removed statistics object ' + @statsobj_name + N'.' FETCH NEXT FROM Stats_cursor INTO @statsobj_name, @table_name, @match_count; END; CLOSE Stats_cursor; DEALLOCATE Stats_cursor; DELETE FROM [xmigra].[statistics] WHERE [Columns] NOT IN ( SELECT us.[Columns] FROM [xmigra].[updated_statistics] us WHERE us.[Name] = [Name] ); END_OF_SQL end
reversion_tracking_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 995 def reversion_tracking_sql "DELETE FROM [xmigra].[applied] WHERE [MigrationID] = '#{id}';\n" end
revoke_previous_permissions_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 895 def revoke_previous_permissions_sql <<-"END_OF_SQL" -- ------------- REVOKING PREVIOUSLY GRANTED PERMISSIONS ------------- -- PRINT N'Revoking previously granted permissions:'; -- Iterate over permissions listed in [xmigra].[revokable_permissions] DECLARE @sqlcmd NVARCHAR(1000); -- Built SQL command DECLARE @permissions NVARCHAR(200); DECLARE @object NVARCHAR(260); DECLARE @principal NVARCHAR(150); DECLARE Permission_cursor CURSOR LOCAL FOR SELECT xp.[permissions], xp.[object], QUOTENAME(sdp.name) FROM [xmigra].[revokable_permissions] xp INNER JOIN sys.database_principals sdp ON xp.principal_id = sdp.principal_id; OPEN Permission_cursor; FETCH NEXT FROM Permission_cursor INTO @permissions, @object, @principal; WHILE @@FETCH_STATUS = 0 BEGIN SET @sqlcmd = N'REVOKE ' + @permissions + N' ON ' + @object + ' FROM ' + @principal + N';'; BEGIN TRY EXEC sp_executesql @sqlcmd; END TRY BEGIN CATCH END CATCH FETCH NEXT FROM Permission_cursor INTO @permissions, @object, @principal; END; CLOSE Permission_cursor; DEALLOCATE Permission_cursor; DELETE FROM [xmigra].[revokable_permissions]; END_OF_SQL end
select_for_install_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 515 def select_for_install_sql <<-"END_OF_SQL" PRINT N'Selecting migrations to apply:'; DECLARE @BridgePoint INT; IF #{upgrading_to_new_branch_test_sql} BEGIN -- Get the [xmigra].[migrations] ApplicationOrder of the record corresponding to the branch transition SET @BridgePoint = ( SELECT MAX(m.[ApplicationOrder]) FROM [xmigra].[migrations] m INNER JOIN [xmigra].[branch_upgrade] bu ON m.[MigrationID] = bu.[CompletesMigration] ); UPDATE [xmigra].[migrations] SET [Install] = 1 WHERE [ApplicationOrder] > @BridgePoint; END ELSE BEGIN -- Get the [xmigra].[migrations] ApplicationOrder of the most recent version bridge migration SET @BridgePoint = ( SELECT COALESCE(MAX(m.[ApplicationOrder]), 0) FROM [xmigra].[applied] a INNER JOIN [xmigra].[migrations] m ON a.[MigrationID] = m.[MigrationID] WHERE a.[VersionBridgeMark] <> 0 ); UPDATE [xmigra].[migrations] SET [Install] = 1 WHERE [MigrationID] NOT IN ( SELECT a.[MigrationID] FROM [xmigra].[applied] a ) AND [ApplicationOrder] > @BridgePoint; END; INSERT INTO [xmigra].[previous_states] ( [Changed], [MigrationApplicationOrder], [FromMigrationID], [ToRangeStartMigrationID], [ToRangeEndMigrationID] ) SELECT TOP (1) CURRENT_TIMESTAMP, -- Application order of last installed migration -- COALESCE( ( SELECT TOP(1) [ApplicationOrder] FROM [xmigra].[applied] ORDER BY [ApplicationOrder] DESC ), 0 ), ( -- Last installed migration -- SELECT TOP (1) [MigrationID] FROM [xmigra].[applied] ORDER BY [ApplicationOrder] DESC ), m.[MigrationID], ( -- Last migration to install -- SELECT TOP(1) [MigrationID] FROM [xmigra].[migrations] WHERE [Install] <> 0 ORDER BY [ApplicationOrder] DESC ) FROM [xmigra].[migrations] m WHERE m.[Install] <> 0 ORDER BY m.[ApplicationOrder] ASC; END_OF_SQL end
stats_objs()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 44 def stats_objs return @stats_objs if @stats_objs begin stats_data = YAML::load_file(path.join(MSSQLSpecifics::STATISTICS_FILE)) rescue Errno::ENOENT return @stats_objs = [].freeze end @stats_objs = stats_data.collect {|item| StatisticsObject.new(*item)} @stats_objs.each {|o| o.freeze} @stats_objs.freeze return @stats_objs end
upgrade_cleanup_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 831 def upgrade_cleanup_sql <<-"END_OF_SQL" PRINT N'Cleaning up from the upgrade:'; DROP TABLE [xmigra].[migrations]; DROP TABLE [xmigra].[updated_indexes]; DROP TABLE [xmigra].[updated_statistics]; END_OF_SQL end
upgrading_to_new_branch_test_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1070 def upgrading_to_new_branch_test_sql return "(0 = 1)" unless respond_to? :branch_identifier (<<-"END_OF_SQL").chomp (EXISTS ( SELECT TOP(1) * FROM [xmigra].[branch_upgrade] WHERE [Next] = #{branch_id_literal} )) END_OF_SQL end
view_definition_template_sql()
click to toggle source
# File lib/xmigra/db_support/mssql.rb, line 1163 def view_definition_template_sql <<-"END_OF_SQL" CREATE VIEW [{filename}] AS SELECT <<<query>>>; END_OF_SQL end