API tools faq
paste
Advertisement
DataCCIW

dev-database-deploy-sanitize_thewellcommunity

DataCCIW
Nov 9th, 2022
1,777
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 20.16 KB | None | 0 0
raw download clone embed print report
  1. -- IMPORTANT --
  2. -- USE THE BELOW SQL TO TAKE A PREVIOUSLY RESTORED SQL DATABASE AND MAKE IT USABLE FOR SANDBOX, DEV, OR TEST USAGE --
  3. -- IMPORTANT --
  4. USE [Rock-Dev-SQL-YYMMDD]
  5.  
  6. DECLARE @Domain AS varchar(250) = 'YOURCHURCHDOMAIN.org'
  7. DECLARE @DevGAuthID AS varchar(250) = 'YOURGOOGLEAUTHID.apps.googleusercontent.com'
  8. DECLARE @DevGAuthSecret AS varchar(250) = 'YOURGOOGLEAUTHSECRETKEY'
  9. DECLARE @DevGAPIKey AS varchar(250) = 'YOURGOOGLEAPIKEY'
  10.  
  11. DECLARE @PublicDomain AS varchar(250) = @Domain
  12. DECLARE @RockDomain AS varchar(250) = 'rock.' + @Domain
  13. DECLARE @CheckInDomain AS varchar(250) = 'checkin.' + @Domain
  14.  
  15. DECLARE @DevPublicDomain AS varchar(250) = 'publicdev.' + @Domain
  16. DECLARE @DevRockDomain AS varchar(250) = 'rockdev.' + @Domain
  17. DECLARE @DevCheckInDomain AS varchar(250) = 'checkindev.' + @Domain
  18.  
  19. DECLARE @RightNow AS datetime2 = GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time'
  20.  
  21. PRINT 'Create Remove Non-Alpha Function'
  22. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufnWell_RemoveNonAlpha]') AND type = 'FN')
  23. DROP FUNCTION [dbo].[ufnWell_RemoveNonAlpha]
  24. GO
  25. CREATE FUNCTION [dbo].[ufnWell_RemoveNonAlpha](@Temp VarChar(1000))
  26. RETURNS VARCHAR(1000)
  27. AS
  28. BEGIN
  29.     WHILE PATINDEX('%[^a-Z]%', @Temp) > 0
  30.         SET @Temp = STUFF(@Temp, PATINDEX('%[^a-Z]%', @Temp), 1, '')
  31.  
  32.     RETURN @Temp
  33. END
  34. GO
  35.  
  36. BEGIN TRY
  37.     BEGIN TRANSACTION;
  38.         DECLARE @CRLF varchar(2) = CHAR(13) + CHAR(10)
  39.        
  40.         PRINT @CRLF + '---------------------------------------------------------------'
  41.         PRINT 'Add "Development" notice to Rock header area'
  42.         UPDATE B
  43.         SET [PreHtml] = '<h4 style="position:absolute;left:80px;top:6px;background:#fe2;padding:6px 14px;border:3px solid #600;color:#a00;"><i class="fas fa-exclamation-triangle"></i> You are connected to the <strong>DEVELOPMENT</strong> server <small style="color:#e30">(updated ' + FORMAT(@RightNow, 'M/d/yyyy') + ')</small></h4><script>$(''.navbar-fixed-top, .navbar-brand-corner'').css(''background-color'',''#c10'');</script>',
  44.             [ModifiedDateTime] = @RightNow
  45.         FROM [Block] B INNER JOIN
  46.             [BlockType] T ON T.[Id] = B.[BlockTypeId]
  47.         WHERE T.[Path] = '~/Blocks/Core/SmartSearch.ascx'
  48.             AND B.[Zone] = 'Header'
  49.            
  50.         PRINT @CRLF + '---------------------------------------------------------------'
  51.         PRINT 'Apend "DEVELOPMENT" to all site names'
  52.         UPDATE Site SET Name = 'DEVELOPMENT ' + Name
  53.         WHERE Name NOT LIKE 'DEVELOPMENT%'
  54.            
  55.         PRINT @CRLF + '---------------------------------------------------------------'
  56.         PRINT 'Turn off SSL for all pages'
  57.         UPDATE [Page] SET [RequiresEncryption] = 0
  58.        
  59.         PRINT @CRLF + '---------------------------------------------------------------'
  60.         PRINT 'Turn off SSL for all sites'
  61.         UPDATE [Site] SET [RequiresEncryption] = 0
  62.        
  63.         PRINT @CRLF + '---------------------------------------------------------------'
  64.         PRINT 'Deactivate all jobs'
  65.         UPDATE [ServiceJob] SET [IsActive] = 0
  66.        
  67.         PRINT @CRLF + '---------------------------------------------------------------'
  68.         PRINT 'Replace non-staff emails with dummy @example.org addresses'
  69.         BEGIN
  70.         UPDATE [Person]
  71.         SET [Email] = LOWER(dbo.[ufnWell_RemoveNonAlpha]([NickName])) + LOWER(dbo.[ufnWell_RemoveNonAlpha]([LastName])) + '@example.org'
  72.         WHERE [Email] IS NOT NULL AND [Email] NOT LIKE '%@' + @Domain and [Email] != '' AND IsSystem != 1
  73.         END
  74.        
  75.         PRINT @CRLF + '---------------------------------------------------------------'
  76.         PRINT 'Deactivate all communication transports'
  77.         BEGIN
  78.         UPDATE [AttributeValue] SET [Value] = 'False'
  79.         WHERE AttributeId IN
  80.             (SELECT a.id
  81.             FROM [EntityType] et INNER JOIN
  82.                 [Attribute] a ON a.EntityTypeId = et.Id AND a.[Key] = 'Active'
  83.             WHERE et.name LIKE '%Communication.Transport%')
  84.         END
  85.        
  86.         PRINT @CRLF + '---------------------------------------------------------------'
  87.         PRINT 'Update mail settings to use SMTP with localhost:25'
  88.         DECLARE @SMTPEntityTypeId int = (SELECT TOP 1 [Id] FROM [EntityType] WHERE [Name] = 'Rock.Communication.Transport.SMTP')
  89.         DECLARE @MailEntityTypeId int = (SELECT TOP 1 [Id] FROM [EntityType] WHERE [Name] = 'Rock.Communication.Medium.Email')
  90.        
  91.         PRINT @CRLF + '-SMTP server'
  92.         DECLARE @SMTPServerAttrID int = (SELECT TOP 1 [Id] FROM [Attribute] WHERE [EntityTypeId] = @SMTPEntityTypeId AND [Key] = 'Server')
  93.         UPDATE [AttributeValue] SET [Value] = 'localhost' WHERE [AttributeId] = @SMTPServerAttrID
  94.        
  95.         PRINT @CRLF + '-SMTP port'
  96.         DECLARE @SMTPPortAttrID int = (SELECT TOP 1 [Id] FROM [Attribute] WHERE [EntityTypeId] = @SMTPEntityTypeId AND [Key] = 'Port')
  97.         UPDATE [AttributeValue] SET [Value] = '25' WHERE [AttributeId] = @SMTPPortAttrID
  98.        
  99.         PRINT @CRLF + '-SMTP username'
  100.         DECLARE @SMTPUsernameAttrID int = (SELECT TOP 1 [Id] FROM [Attribute] WHERE [EntityTypeId] = @SMTPEntityTypeId AND [Key] = 'UserName')
  101.         UPDATE [AttributeValue] SET [Value] = '' WHERE [AttributeId] = @SMTPUsernameAttrID
  102.        
  103.         PRINT @CRLF + '-SMTP password'
  104.         DECLARE @SMTPPasswordAttrID int = (SELECT TOP 1 [Id] FROM [Attribute] WHERE [EntityTypeId] = @SMTPEntityTypeId AND [Key] = 'Password')
  105.         UPDATE [AttributeValue] SET [Value] = '' WHERE [AttributeId] = @SMTPPasswordAttrID
  106.        
  107.         PRINT @CRLF + '-SMTP use SSL'
  108.         DECLARE @SMTPSSLAttrID int = (SELECT TOP 1 [Id] FROM [Attribute] WHERE [EntityTypeId] = @SMTPEntityTypeId AND [Key] = 'UseSSL')
  109.         UPDATE [AttributeValue] SET [Value] = 'False' WHERE [AttributeId] = @SMTPSSLAttrID
  110.        
  111.         PRINT @CRLF + '-Set Mail Transport'
  112.         DECLARE @SMTPEntityTypeGuid varchar(50) = (SELECT LOWER(CAST([Guid] as varchar(50))) FROM [EntityType] WHERE [Id] = @SMTPEntityTypeId)
  113.         DECLARE @TransportAttrID int = (SELECT TOP 1 [Id] FROM [Attribute] WHERE [EntityTypeId] = @MailEntityTypeId AND [Key] = 'TransportContainer')
  114.         UPDATE [AttributeValue] SET [Value] = @SMTPEntityTypeGuid WHERE [AttributeId] = @TransportAttrID
  115.  
  116.         --Re-enable SMTP transport??
  117.         -- UPDATE [AttributeValue] SET [Value] = 'True'
  118.         -- WHERE AttributeId IN
  119.         --     (SELECT a.id
  120.         --     FROM [EntityType] et INNER JOIN
  121.         --         [Attribute] a ON a.EntityTypeId = et.Id AND a.[Key] = 'Active'
  122.         --     WHERE et.name = 'Rock.Communication.Transport.SMTP')
  123.        
  124.         PRINT @CRLF + '---------------------------------------------------------------'
  125.         PRINT 'Deactivate all financial gateways'
  126.         UPDATE FinancialGateway SET IsActive = 0
  127.        
  128.         PRINT @CRLF + '---------------------------------------------------------------'
  129.         PRINT 'Reactivate test financial gateway'
  130.         DECLARE @TestGatewayEntityTypeID AS int = (SELECT TOP 1 ID FROM EntityType WHERE Name = 'Rock.Financial.TestGateway')
  131.         UPDATE FinancialGateway SET IsActive = 1 WHERE EntityTypeID = @TestGatewayEntityTypeID
  132.        
  133.         -- PRINT @CRLF + '---------------------------------------------------------------'
  134.         -- PRINT 'Deactivate PushPay account'
  135.         -- UPDATE _com_pushPay_RockRMS_Account
  136.         -- SET IsActive = 0, AccessToken = NULL, RefreshToken = NULL
  137.        
  138.         PRINT @CRLF + '---------------------------------------------------------------'
  139.         PRINT 'Update Google auth service keys'
  140.         DECLARE @GAuthEntityTypeID AS int = (SELECT TOP 1 ID FROM EntityType WHERE Name = 'Rock.Security.ExternalAuthentication.Google')
  141.         DECLARE @GAuthIDAttrID AS int = (SELECT TOP 1 ID FROM Attribute WHERE EntityTypeID = @GAuthEntityTypeID AND [Key] = 'ClientID')
  142.         DECLARE @GAuthSecretAttrID AS int = (SELECT TOP 1 ID FROM Attribute WHERE EntityTypeID = @GAuthEntityTypeID AND [Key] = 'ClientSecret')
  143.         UPDATE AttributeValue SET [Value] = @DevGAuthID WHERE AttributeID = @GAuthIDAttrID
  144.         UPDATE AttributeValue SET [Value] = @DevGAuthSecret WHERE AttributeID = @GAuthSecretAttrID
  145.        
  146.         PRINT @CRLF + '---------------------------------------------------------------'
  147.         PRINT 'Update Azure blob storage key'
  148.         DECLARE @AzureBlobEntityTypeID AS int = (SELECT TOP 1 ID FROM EntityType WHERE Name = 'rocks.pillars.AzureStorageProvider.AzureBlobStorage')
  149.         DECLARE @AzureBlobKeyAttrID AS int = (SELECT TOP 1 ID FROM Attribute WHERE EntityTypeID = @AzureBlobEntityTypeID AND [Key] = 'AccountKey')
  150.         UPDATE AttributeValue SET [Value] = 'DISABLED' WHERE AttributeID = @AzureBlobKeyAttrID
  151.        
  152.         PRINT @CRLF + '---------------------------------------------------------------'
  153.         PRINT 'Upgate Azure storage provider key'
  154.         DECLARE @AzureStoreEntityTypeID AS int = (SELECT TOP 1 ID FROM EntityType WHERE Name = 'Rock.Model.AssetStorageProvider')
  155.         DECLARE @AzureStoreKeyAttrID AS int = (SELECT TOP 1 ID FROM Attribute WHERE EntityTypeID = @AzureStoreEntityTypeID AND [Key] = 'AccountAccessKey')
  156.         UPDATE AttributeValue SET [Value] = 'DISABLED' WHERE AttributeID = @AzureStoreKeyAttrID
  157.        
  158.         PRINT @CRLF + '---------------------------------------------------------------'
  159.         PRINT 'Update global attributes'
  160.        
  161.         PRINT @CRLF + '-Google API Key'
  162.         DECLARE @GAPIAttrID AS int = (SELECT TOP 1 ID FROM Attribute WHERE EntityTypeID IS NULL AND [Key] = 'GoogleAPIKey')
  163.         UPDATE AttributeValue SET [Value] = @DevGAPIKey WHERE AttributeID = @GAPIAttrID AND EntityID IS NULL
  164.        
  165.         -- PRINT @CRLF + '-Managed Missions Account'
  166.         -- DECLARE @MMAccountAttrID AS int = (SELECT TOP 1 ID FROM Attribute WHERE EntityTypeID IS NULL AND [Key] = 'MMAccount')
  167.         -- UPDATE AttributeValue SET [Value] = 'DISABLED' WHERE AttributeID = @MMAccountAttrID AND EntityID IS NULL
  168.        
  169.         -- PRINT @CRLF + '-Managed Missions API ID'
  170.         -- DECLARE @MMAPIIDAttrID AS int = (SELECT TOP 1 ID FROM Attribute WHERE EntityTypeID IS NULL AND [Key] = 'MMAPI')
  171.         -- UPDATE AttributeValue SET [Value] = 'DISABLED' WHERE AttributeID = @MMAPIIDAttrID AND EntityID IS NULL
  172.        
  173.         -- PRINT @CRLF + '-Managed Missions API Code'
  174.         -- DECLARE @MMAPICodeAttrID AS int = (SELECT TOP 1 ID FROM Attribute WHERE EntityTypeID IS NULL AND [Key] = 'MMAPICODE')
  175.         -- UPDATE AttributeValue SET [Value] = 'DISABLED' WHERE AttributeID = @MMAPICodeAttrID AND EntityID IS NULL
  176.        
  177.         PRINT @CRLF + '---------------------------------------------------------------'
  178.         PRINT 'Update ACME SSL domains'
  179.         DECLARE @GroupEntityTypeID AS int = (SELECT TOP 1 ID FROM EntityType WHERE Name = 'Rock.Model.Group')
  180.         DECLARE @ACMEGroupTypeID AS int = (SELECT TOP 1 ID FROM GroupType WHERE Name = 'Acme Certificates')
  181.         DECLARE @ACMEDomainsAttrID AS int = (SELECT TOP 1 ID FROM Attribute WHERE EntityTypeID = @GroupEntityTypeID AND EntityTypeQualifierColumn = 'GroupTypeId' AND EntityTypeQualifierValue = @ACMEGroupTypeID AND [Key] = 'Domains')
  182.         UPDATE AttributeValue SET [Value] = REPLACE(REPLACE([Value],'.' + @Domain,'dev.' + @Domain),'|' + @Domain,'|publicdev.' + @Domain) WHERE AttributeID = @ACMEDomainsAttrID AND [Value] NOT LIKE '%dev.' + @Domain + '%'
  183.        
  184.         PRINT @CRLF + '---------------------------------------------------------------'
  185.         PRINT 'Update domain references'
  186.        
  187.         PRINT @CRLF + '-Attribute values'
  188.         UPDATE AttributeValue SET [Value] = REPLACE([Value],'/' + @PublicDomain, '/' + @DevPublicDomain) WHERE [Value] LIKE '%/' + @PublicDomain + '%'
  189.         UPDATE AttributeValue SET [Value] = REPLACE([Value],'/' + @RockDomain, '/' + @DevRockDomain) WHERE [Value] LIKE '%/' + @RockDomain + '%'    
  190.         UPDATE AttributeValue SET [Value] = REPLACE([Value],'/' + @CheckInDomain, '/' + @DevCheckInDomain) WHERE [Value] LIKE '%/' + @CheckInDomain + '%'    
  191.        
  192.         PRINT @CRLF + '-HTML content'
  193.         UPDATE HtmlContent SET Content = REPLACE(Content,'/' + @PublicDomain, '/' + @DevPublicDomain) WHERE Content LIKE '%/' + @PublicDomain + '%'
  194.         UPDATE HtmlContent SET Content = REPLACE(Content,'/' + @RockDomain, '/' + @DevRockDomain) WHERE Content LIKE '%/' + @RockDomain + '%'
  195.         UPDATE HtmlContent SET Content = REPLACE(Content,'/' + @CheckInDomain, '/' + @DevCheckInDomain) WHERE Content LIKE '%/' + @CheckInDomain + '%'
  196.        
  197.         PRINT @CRLF + '-Content channel items'
  198.         UPDATE ContentChannelItem SET Content = REPLACE(Content,'/' + @PublicDomain, '/' + @DevPublicDomain) WHERE Content LIKE '%/' + @PublicDomain + '%'
  199.         UPDATE ContentChannelItem SET Content = REPLACE(Content,'/' + @RockDomain, '/' + @DevRockDomain) WHERE Content LIKE '%/' + @RockDomain + '%'
  200.         UPDATE ContentChannelItem SET Content = REPLACE(Content,'/' + @CheckInDomain, '/' + @DevCheckInDomain) WHERE Content LIKE '%/' + @CheckInDomain + '%'
  201.        
  202.         PRINT @CRLF + '-Lava shortcodes'
  203.         UPDATE LavaShortcode SET Markup = REPLACE(Markup,'/' + @PublicDomain, '/' + @DevPublicDomain) WHERE Markup LIKE '%/' + @PublicDomain + '%'
  204.         UPDATE LavaShortcode SET Markup = REPLACE(Markup,'/' + @RockDomain, '/' + @DevRockDomain) WHERE Markup LIKE '%/' + @RockDomain + '%'
  205.         UPDATE LavaShortcode SET Markup = REPLACE(Markup,'/' + @CheckInDomain, '/' + @DevCheckInDomain) WHERE Markup LIKE '%/' + @CheckInDomain + '%'
  206.        
  207.         PRINT @CRLF + '-Report fields'
  208.         UPDATE ReportField SET Selection = REPLACE(Selection,'/' + @PublicDomain, '/' + @DevPublicDomain) WHERE Selection LIKE '%/' + @PublicDomain + '%'
  209.         UPDATE ReportField SET Selection = REPLACE(Selection,'/' + @RockDomain, '/' + @DevRockDomain) WHERE Selection LIKE '%/' + @RockDomain + '%'
  210.         UPDATE ReportField SET Selection = REPLACE(Selection,'/' + @CheckInDomain, '/' + @DevCheckInDomain) WHERE Selection LIKE '%/' + @CheckInDomain + '%'
  211.        
  212.         PRINT @CRLF + '-Metrics'
  213.         UPDATE Metric SET SourceLava = REPLACE(SourceLava,'/' + @PublicDomain, '/' + @DevPublicDomain) WHERE SourceLava LIKE '%/' + @PublicDomain + '%'
  214.         UPDATE Metric SET SourceLava = REPLACE(SourceLava,'/' + @RockDomain, '/' + @DevRockDomain) WHERE SourceLava LIKE '%/' + @RockDomain + '%'
  215.         UPDATE Metric SET SourceLava = REPLACE(SourceLava,'/' + @CheckInDomain, '/' + @DevCheckInDomain) WHERE SourceLava LIKE '%/' + @CheckInDomain + '%'
  216.        
  217.         PRINT @CRLF + '-Communication templates'
  218.         UPDATE CommunicationTemplate
  219.         SET [Message] = REPLACE([Message],'/' + @PublicDomain, '/' + @DevPublicDomain),
  220.             PushMessage = REPLACE(PushMessage,'/' + @PublicDomain, '/' + @DevPublicDomain),
  221.             SMSMessage = REPLACE(SMSMessage,'/' + @PublicDomain, '/' + @DevPublicDomain)
  222.         WHERE [Message] LIKE '%/' + @PublicDomain + '%' OR PushMessage LIKE '%/' + @PublicDomain + '%' OR SMSMessage LIKE '%/' + @PublicDomain + '%'
  223.         UPDATE CommunicationTemplate
  224.         SET [Message] = REPLACE([Message],'/' + @RockDomain, '/' + @DevRockDomain),
  225.             PushMessage = REPLACE(PushMessage,'/' + @RockDomain, '/' + @DevRockDomain),
  226.             SMSMessage = REPLACE(SMSMessage,'/' + @RockDomain, '/' + @DevRockDomain)
  227.         WHERE [Message] LIKE '%/' + @RockDomain + '%' OR PushMessage LIKE '%/' + @RockDomain + '%' OR SMSMessage LIKE '%/' + @RockDomain + '%'
  228.         UPDATE CommunicationTemplate
  229.         SET [Message] = REPLACE([Message],'/' + @CheckInDomain, '/' + @DevCheckInDomain),
  230.             PushMessage = REPLACE(PushMessage,'/' + @CheckInDomain, '/' + @DevCheckInDomain),
  231.             SMSMessage = REPLACE(SMSMessage,'/' + @CheckInDomain, '/' + @DevCheckInDomain)
  232.         WHERE [Message] LIKE '%/' + @CheckInDomain + '%' OR PushMessage LIKE '%/' + @CheckInDomain + '%' OR SMSMessage LIKE '%/' + @CheckInDomain + '%'
  233.        
  234.         PRINT @CRLF + '-System communications'
  235.         UPDATE SystemCommunication
  236.         SET Body = REPLACE(Body,'/' + @PublicDomain, '/' + @DevPublicDomain),
  237.             PushMessage = REPLACE(PushMessage,'/' + @PublicDomain, '/' + @DevPublicDomain),
  238.             SMSMessage = REPLACE(SMSMessage,'/' + @PublicDomain, '/' + @DevPublicDomain)
  239.         WHERE Body LIKE '%/' + @PublicDomain + '%' OR PushMessage LIKE '%/' + @PublicDomain + '%' OR SMSMessage LIKE '%/' + @PublicDomain + '%'
  240.         UPDATE SystemCommunication
  241.         SET Body = REPLACE(Body,'/' + @RockDomain, '/' + @DevRockDomain),
  242.             PushMessage = REPLACE(PushMessage,'/' + @RockDomain, '/' + @DevRockDomain),
  243.             SMSMessage = REPLACE(SMSMessage,'/' + @RockDomain, '/' + @DevRockDomain)
  244.         WHERE Body LIKE '%/' + @RockDomain + '%' OR PushMessage LIKE '%/' + @RockDomain + '%' OR SMSMessage LIKE '%/' + @RockDomain + '%'
  245.         UPDATE SystemCommunication
  246.         SET Body = REPLACE(Body,'/' + @CheckInDomain, '/' + @DevCheckInDomain),
  247.             PushMessage = REPLACE(PushMessage,'/' + @CheckInDomain, '/' + @DevCheckInDomain),
  248.             SMSMessage = REPLACE(SMSMessage,'/' + @CheckInDomain, '/' + @DevCheckInDomain)
  249.         WHERE Body LIKE '%/' + @CheckInDomain + '%' OR PushMessage LIKE '%/' + @CheckInDomain + '%' OR SMSMessage LIKE '%/' + @CheckInDomain + '%'
  250.        
  251.         PRINT @CRLF + '-System email'
  252.         UPDATE SystemEmail SET Body = REPLACE(Body,'/' + @PublicDomain, '/' + @DevPublicDomain) WHERE Body LIKE '%/' + @PublicDomain + '%'
  253.         UPDATE SystemEmail SET Body = REPLACE(Body,'/' + @RockDomain, '/' + @DevRockDomain) WHERE Body LIKE '%/' + @RockDomain + '%'
  254.         UPDATE SystemEmail SET Body = REPLACE(Body,'/' + @CheckInDomain, '/' + @DevCheckInDomain) WHERE Body LIKE '%/' + @CheckInDomain + '%'
  255.  
  256.         PRINT @CRLF + '---------------------------------------------------------------'
  257.         PRINT 'Update personal link domains'
  258.         UPDATE PersonalLink SET [URL] = REPLACE(REPLACE([URL], '.' + @Domain, 'dev.' + @Domain), '/' + @Domain, '/publicdev.' + @Domain) WHERE [URL] LIKE '%' + @Domain + '%' AND [URL] NOT LIKE '%dev.' + @Domain + '%'
  259.        
  260.         PRINT @CRLF + '---------------------------------------------------------------'
  261.         PRINT 'Update site domains'
  262.         DECLARE @PublicDomainGUID AS uniqueidentifier = (SELECT TOP 1 GUID FROM SiteDomain WHERE Domain = @Domain)
  263.         UPDATE SiteDomain SET Domain = @DevPublicDomain WHERE GUID = @PublicDomainGUID
  264.         UPDATE SiteDomain SET Domain = REPLACE(Domain,'.' + @Domain,'dev.' + @Domain) WHERE GUID <> @PublicDomainGUID AND Domain NOT LIKE '%dev.' + @Domain + '%'
  265.         UPDATE Site SET AllowedFrameDomains = REPLACE(REPLACE(REPLACE(AllowedFrameDomains, @Domain, '|' + @Domain), '.|' + @Domain, 'dev.' + @Domain), '|' + @Domain, @DevPublicDomain) WHERE AllowedFrameDomains NOT LIKE '%dev.' + @Domain + '%'
  266.         UPDATE Site SET IndexStartingLocation = REPLACE(REPLACE(REPLACE(IndexStartingLocation, @Domain, '|' + @Domain), '.|' + @Domain, 'dev.' + @Domain), '|' + @Domain, @DevPublicDomain) WHERE IndexStartingLocation NOT LIKE '%dev.' + @Domain + '%'
  267.  
  268.         PRINT @CRLF + '---------------------------------------------------------------'
  269.         PRINT 'Enable database login for internal site'
  270.         DECLARE @BlockEntityTypeID AS int = (SELECT TOP 1 ID FROM EntityType WHERE Name = 'Rock.Model.Block')
  271.         DECLARE @InternalLoginAttrID AS int = (SELECT TOP 1 ID FROM Attribute WHERE EntityTypeID = @BlockEntityTypeID AND [Key] = 'ShowInternalLogin')
  272.         DECLARE @BlockTypeID AS int = (SELECT TOP 1 ID FROM BlockType WHERE [GUID] = '7B83D513-1178-429E-93FF-E76430E038E4')
  273.         DECLARE @BlockID AS int = (SELECT TOP 1 B.ID FROM [Block] B INNER JOIN [Page] P ON P.ID = B.PageID INNER JOIN Layout L ON L.ID = P.LayoutID AND L.SiteID = 1 WHERE B.BlockTypeID = @BlockTypeID)
  274.         UPDATE AttributeValue SET [Value] = 'True' WHERE AttributeID = @InternalLoginAttrID AND EntityID = @BlockID
  275.        
  276.     COMMIT TRANSACTION;
  277.    
  278.     PRINT @CRLF + '========================================================='
  279.     PRINT 'Updates Completed'
  280. END TRY
  281. BEGIN CATCH
  282.     IF (@@TRANCOUNT > 0)
  283.         ROLLBACK TRANSACTION;
  284.  
  285.     THROW;
  286.    
  287.     PRINT @CRLF + '========================================================='
  288.     PRINT 'No Changes Made'
  289. END CATCH
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!