Fix HtmlModule 10 upgrade issues with localized sites

[tvatavuk]

Fixes #6494

Summary

There is an upgrade issue affecting localized sites. After upgrading the HtmlModule to versions 10.00.00 or 10.00.01, exceptions may occur as explained in https://github.com/dnnsoftware/Dnn.Platform/issues/6494.

The migration from the built-in HTML workflow is not correctly mapped to DNN content workflows when workflow names and states are localized. This depends on the admin-selected language at the time of site creation.

This fix addresses that issue.

This PR makes upgrading to DNN 10.00.02+ to resolve these errors. Alternatively, for sites already on 10.00.00 or 10.00.01, running the SQL script at DesktopModules\HTML\Providers\DataProviders\SqlDataProvider\10.00.02.SqlDataProvider in the DNN SQL Console can also resolve the issues caused by content workflow localization.

TODO:

• [ ] Halt upgrade if custom HTML workflows are detected
• [x] SQL script to fix localization issues after HTML upgrade
• [x] Improved SQL script to create missing content workflows and states
• [ ] Migrate HTML workflow logic from SQL-only to C# for better flexibility during development
• [ ] Enhance HtmlModule to be more robust in similar scenarios
• [ ] Refine SQL scripts for best practices

[valadas]

Awesome, thanks for taking care of this @tvatavuk

[valadas]

@tvatavuk this completes #6618 correct ?

[tvatavuk]

Not yet - I have separate code for #6618, which I'm planning to complete for a future DNN release after 10.00.02, assuming this PR becomes part of that release. Additionally, I plan to introduce at least one more enhancement issue (with a corresponding PR) related to the migration of legacy html workflow settings at the site, tab, and module level.

[bdukes]

In testing, there was an error during step 1 from 10.00.00.03.SqlDataProvider:

Violation of UNIQUE KEY constraint 'IX_ContentWorkflows'. Cannot insert duplicate key in object 'dbo.ContentWorkflows'. The duplicate key value is (17, Content Approval).

This site has ContentWorkflows rows with WorkflowKey set to an empty string. The MERGE statement is matching on WorkflowKey, but the index is for PortalID and WorkflowName.

[tvatavuk]

Thanks a lot for testing and reporting this!

The issue occurs because the script attempts to insert a new content workflow when a workflow with the same WorkflowName and PortalID already exists - but with a different (or missing) WorkflowKey. Since the current logic relies on WorkflowKey to identify default workflows, this mismatch leads to a duplicate insert due to the IX_ContentWorkflows unique constraint on (PortalID, WorkflowName).

The complication arises because WorkflowName is sometimes localized based on the admin's UI culture at the time of portal creation, making WorkflowKey the more reliable identifier for defaults. However, as you noticed, sometimes WorkflowKey is empty or not set as expected.

Missing step was to normalize workflow keys, so I’ve added UPDATE statements to ensure that recognizable default workflow names are assigned their expected WorkflowKey values before the MERGE. This should prevent the reported error.

    /*------------------------------------------------------
        1.  Ensure known workflows has expected key
    ------------------------------------------------------*/

    -- ensure that each workflow with name 'Direct Publish' has workflow key 'DirectPublish'
    UPDATE {databaseOwner}{objectQualifier}ContentWorkflows
    SET WorkflowKey = 'DirectPublish'
    WHERE WorkflowName = N'Direct Publish' AND WorkflowKey <> 'DirectPublish';

    -- ensure that each workflow with name 'Save Draft' has workflow key 'SaveDraft'
    UPDATE {databaseOwner}{objectQualifier}ContentWorkflows
    SET WorkflowKey = 'SaveDraft'
    WHERE WorkflowName = N'Save Draft' AND WorkflowKey <> 'SaveDraft';
    -- ensure that each workflow with name 'Content Approval' has workflow key 'ContentApproval'
    UPDATE {databaseOwner}{objectQualifier}ContentWorkflows
    SET WorkflowKey = 'ContentApproval'
    WHERE WorkflowName = N'Content Approval' AND WorkflowKey <> 'ContentApproval';

I’ve tested against clean installs and dnn 9.13.9 upgrade scenario (with deleted WorkflowKey for ContentApproval) – all pass.

Thanks again for the quick report!

[bdukes]

Thanks for the quick update @tvatavuk! Unfortunately, I now get a similar error from the ContentWorkflowStates step:

Violation of UNIQUE KEY constraint 'IX_ContentWorkflowStates'. Cannot insert duplicate key in object 'dbo.ContentWorkflowStates'. The duplicate key value is (292, Published).

I'm testing this with Site A from my previous testing. This includes "Save Draft" workflows where Draft has Order set to 1, but Published has Order set to 3 (there is no workflow state with Order set to 2 in this workflow). Similarly, there are "Content Approval" workflows with Draft 1, Ready For Review 3 and Published 5.

There are also workflows named "Folders: Content Approval" which don't get a workflow key, but I don't think those are causing any issue at this point.

I guess there will need to be an additional check that the given StateName does not exist for the workflow before trying to insert, rather than only checking Order, to avoid violations of the unique constraint.

Thanks again for the quick turnaround on these changes!

[tvatavuk]

Thanks @bdukes for the follow-up!

I’ve pushed a fix for the IX_ContentWorkflowStates violation: the MERGE now matches on WorkflowID + StateName first (incl. translations), and only falls back to [Order] if no name match exists. That prevents inserting a duplicate (WorkflowID, StateName) when orders are sparse or reordered.

-- core of the change
MERGE dbo.ContentWorkflowStates AS tgt
USING StatesWanted AS src
ON  tgt.WorkflowID = src.WorkflowID
AND (
        tgt.StateName IN (SELECT Name FROM Translations WHERE Canon = src.StateName)  -- primary match
     OR tgt.[Order]    = src.[Order]                                                  -- fallback slot match
    )
WHEN NOT MATCHED BY TARGET THEN
    INSERT (WorkflowID, StateName, [Order], IsSystem, SendNotification, SendNotificationToAdministrators)
    VALUES (src.WorkflowID, src.StateName, src.[Order], src.IsSystem, src.SendNotification, src.SendNotificationToAdministrators);

Could you please test this against your Site A data and let me know if anything else pops up?

Thanks again!

[bdukes]

This worked successfully for me, thanks @tvatavuk!