Open ID fix (5028)

This SQL fix is usually required when upgrading to POS version 0.13.0.

It fixes an Orchard database migration problem with the Open ID module that we enable for the Cart App and Order Pad authentication.

Diagnostics

You know that this SQL needs to be run if diagnostics indicate that the Open ID data migrations have not run, and the log contains errors including something like the following:

Microsoft.Data.SqlClient.SqlException (0x80131904): The DELETE statement conflicted
with the REFERENCE constraint "FK_OpenIdApplicationIndex". The conflict occurred in
database "Orchard1", table "dbo.OpenIdApplicationIndex", column 'DocumentId'.

at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean 
breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject 
stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, 
TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreResults(Boolean& moreResults)
at Microsoft.Data.SqlClient.SqlDataReader.TryNextResult(Boolean& more)
at Microsoft.Data.SqlClient.SqlDataReader.NextResult()
at YesSql.Commands.CreateIndexCommand.<AddToBatch>b__6_0(DbDataReader dr)
at YesSql.Commands.BatchCommand.ExecuteAsync(DbConnection connection, DbTransaction 
transaction, ISqlDialect dialect, ILogger logger) at YesSql.Session.FlushAsync()
at YesSql.Session.FlushAsync()
at OrchardCore.OpenId.YesSql.Migrations.OpenIdMigrations.UpdateFrom7Async()
at OrchardCore.Data.Migration.DataMigrationManager.UpdateAsync(String featureId) 
ClientConnectionId:d2926d66-485c-4c48-80d8-510633a40753 Error Number:547,State:0,Class:16

You may also see a similar error that prevents you from logging in.

SQL

The SQL script is:

-- NOTE AFTER RUNNING SCRIPTS, 'RELOAD' TENANT TO GET MIGRATION TO RUN AGAIN
-- This will drop foreign keys, and then re-add them as dummy constraints
-- so that Orchard migrations will work correctly.
-- See https://github.com/OrchardCMS/OrchardCore/discussions/9119

-- FK_OpenIdAppByRoleNameIndex_Document_DocumentId

IF EXISTS (SELECT * 
  FROM sys.foreign_keys 
   WHERE object_id = OBJECT_ID(N'FK_OpenIdAppByRoleNameIndex_Document_DocumentId')
   AND parent_object_id = OBJECT_ID(N'OpenIdAppByRoleNameIndex_Document')
)
BEGIN
    PRINT 'Replacing FK_OpenIdAppByRoleNameIndex_Document_DocumentId.'
    ALTER TABLE [dbo].[OpenIdAppByRoleNameIndex_Document] DROP CONSTRAINT 
        [FK_OpenIdAppByRoleNameIndex_Document_DocumentId]


    ALTER TABLE [dbo].[OpenIdAppByRoleNameIndex_Document] ADD CONSTRAINT
        [FK_OpenIdAppByRoleNameIndex_Document_DocumentId] CHECK (DocumentId > 0)
END
ELSE
BEGIN
    PRINT 'No need to replace FK_OpenIdAppByRoleNameIndex_Document_DocumentId'
END
GO

-- FK_OpenIdApplicationIndex

IF EXISTS (SELECT * 
  FROM sys.foreign_keys 
   WHERE object_id = OBJECT_ID(N'FK_OpenIdApplicationIndex')
   AND parent_object_id = OBJECT_ID(N'[OpenIdApplicationIndex]')
)
BEGIN
    PRINT 'Replacing FK_OpenIdApplicationIndex.'
    ALTER TABLE [dbo].[OpenIdApplicationIndex] DROP CONSTRAINT 
        [FK_OpenIdApplicationIndex]

    ALTER TABLE [dbo].[OpenIdApplicationIndex] ADD CONSTRAINT
        [FK_OpenIdApplicationIndex] CHECK (DocumentId > 0)
END
ELSE
BEGIN
    PRINT 'No need to replace FK_OpenIdApplicationIndex'
END
GO

-- FK_OpenIdAppByLogoutUriIndex_Document_DocumentId

IF EXISTS (SELECT * 
  FROM sys.foreign_keys 
   WHERE object_id = OBJECT_ID(N'FK_OpenIdAppByLogoutUriIndex_Document_DocumentId')
   AND parent_object_id = OBJECT_ID(N'[OpenIdAppByLogoutUriIndex_Document]')
)
BEGIN
    PRINT 'Replacing FK_OpenIdAppByLogoutUriIndex_Document_DocumentId.'
    ALTER TABLE [dbo].[OpenIdAppByLogoutUriIndex_Document] DROP CONSTRAINT 
        [FK_OpenIdAppByLogoutUriIndex_Document_DocumentId]

    ALTER TABLE [dbo].[OpenIdAppByLogoutUriIndex_Document] ADD CONSTRAINT
        [FK_OpenIdAppByLogoutUriIndex_Document_DocumentId] CHECK (DocumentId > 0)
END
ELSE
BEGIN
    PRINT 'No need to replace FK_OpenIdAppByLogoutUriIndex_Document_DocumentId'
END
GO

-- FK_OpenIdAppByRedirectUriIndex_Document_DocumentId

IF EXISTS (SELECT * 
  FROM sys.foreign_keys 
   WHERE object_id = OBJECT_ID(N'FK_OpenIdAppByRedirectUriIndex_Document_DocumentId')
   AND parent_object_id = OBJECT_ID(N'[OpenIdAppByRedirectUriIndex_Document]')
)
BEGIN
    PRINT 'Replacing FK_OpenIdAppByRedirectUriIndex_Document_DocumentId.'
    ALTER TABLE [dbo].[OpenIdAppByRedirectUriIndex_Document] DROP CONSTRAINT 
        [FK_OpenIdAppByRedirectUriIndex_Document_DocumentId]

    ALTER TABLE [dbo].[OpenIdAppByRedirectUriIndex_Document] ADD CONSTRAINT
        [FK_OpenIdAppByRedirectUriIndex_Document_DocumentId] CHECK (DocumentId > 0)
END
ELSE
BEGIN
    PRINT 'No need to replace FK_OpenIdAppByRedirectUriIndex_Document_DocumentId'
END
GO

After running the script, you must restart the tenant so that the Orchard migrations can run again.