After updating blueprints, template listing still shows old templates if a template was previously modified

Steps to reproduce

1. Install a blueprint plugin containing templates
2. Click the "Create" button to populate the template listing
3. Go to Confluence Admin > Global Templates and Blueprints and modify a template from the blueprint
4. Install a newer version of the blueprint plugin with some templates added or removed
5. Click the "Create" button and observe the template listing

Expected behavior

• New templates from the updated blueprint plugin are added to the Create dialog
• Templates removed from the updated blueprint plugin are removed from the Create dialog
• Templates that have not changed in the updated blueprint plugin, but was previously modified in the UI should keep their modifications
• Templates that have changed with the updated blueprint plugin, and was also previously modified in the UI should be "reset" to the new default template provided by the plugin, OR continue to be overridden by modifications

Actual behavior

In the Create dialog, none of the templates actually reflect the templates packaged with the updated blueprint plugin. To the user, it effectively appears as if the blueprint was never updated

Technical details

This issue concerns the following three tables:

• AO_54C900_CONTENT_BLUEPRINT_AO - where the blueprint metadata is stored
• AO_54C900_C_TEMPLATE_REF - where the template references are stored
• pagetemplates - where the actual contents of any modified templates are stored

When a blueprint plugin is installed, and the user opens the Create dialog the following occurs:

• A row is added to "AO_54C900_CONTENT_BLUEPRINT_AO" with the "PLUGIN_CLONE" set to "t". This indicates that none of the blueprint's templates have been modified from the defaults.
• Similarly, "AO_54C900_C_TEMPLATE_REF" stores all of the template references, and is tied to the first table via the "CB_INDEX_PARENTID" and "CB_PARENTID" columns. All of the "TEMPLATE_ID" values are set to "0", which indicates they have not been modified from the default.

When a template is modified, the following occurs:

• "AO_54C900_CONTENT_BLUEPRINT_AO" creates a new row, with "PLUGIN_CLONE" set to "f" indicating that one or more of its templates have deviated from the default
• "AO_54C900_C_TEMPLATE_REF" creates a new set of templates (on top of the existing set), each referencing the new ID of the new row in the first table. The modified template also has a non-zero "TEMPLATE_ID" value
• Rows are added to the "pagetemplates" containing the modified template contents, while referencing to the previous table by "template_id" as well as "prevver".
• The Create dialog now displays the modified template listing associated with the "AO_54C900_CONTENT_BLUEPRINT_AO" whose "PLUGIN_CLONE" value is "f"

Finally, when a new version of the blueprint plugin is subsequently updated, the following occurs:

• "AO_54C900_CONTENT_BLUEPRINT_AO" remains unchanged
• "AO_54C900_C_TEMPLATE_REF" updates its template listing, now referencing the unmodified blueprint ("PLUGIN_CLONE" = "t") in the first table. All of the entries referencing the modified blueprint ("PLUGIN_CLONE" = "f") remains unchanged
• The Create dialog continues to show all the templates associated with the "modified" blueprint, effectively ignoring all of the changes from the new blueprint update

Proposed solution

The blueprint plugin update should correctly update the "AO_54C900_C_TEMPLATE_REF" template references to point to the modified blueprint, as well as retain any template modifications (assuming the template themselves have not changed between versions)

Workaround

• Back up (if desired) and remove all template customizations for that blueprint via SQL (see below) in order for the templates from the new blueprint to show up.
• Add the customizations back in afterwards, as needed

SQL to remove blueprint template customizations

Backup your database prior to running the below. It is also recommended to run this on your test environment first
In all queries below, "my.blueprint.plugin.key" is a placeholder and should be substituted with your actual blueprint plugin key.

DELETE FROM pagetemplates
WHERE prevver IN (
  SELECT "TEMPLATE_ID" FROM "AO_54C900_C_TEMPLATE_REF"
  WHERE "PLUGIN_MODULE_KEY" LIKE 'my.blueprint.plugin.key%'
    AND "TEMPLATE_ID" != 0)
OR templateid IN (
  SELECT "TEMPLATE_ID" FROM "AO_54C900_C_TEMPLATE_REF"
  WHERE "PLUGIN_MODULE_KEY" LIKE 'my.blueprint.plugin.key%'
    AND "TEMPLATE_ID" != 0);

DELETE FROM "AO_54C900_C_TEMPLATE_REF"
WHERE "CB_INDEX_PARENTID" IN (
  SELECT "ID"
  FROM "AO_54C900_CONTENT_BLUEPRINT_AO"
  WHERE "PLUGIN_MODULE_KEY" LIKE 'my.blueprint.plugin.key%'
    AND "PLUGIN_CLONE" = 'f')
OR "CB_PARENTID" IN (
  SELECT "ID"
  FROM "AO_54C900_CONTENT_BLUEPRINT_AO"
  WHERE "PLUGIN_MODULE_KEY" LIKE 'my.blueprint.plugin.key%'
    AND "PLUGIN_CLONE" = 'f');

DELETE FROM "AO_54C900_CONTENT_BLUEPRINT_AO"
WHERE "PLUGIN_MODULE_KEY" LIKE 'my.blueprint.plugin.key%'
  AND "PLUGIN_CLONE" = 'f';