Pull Request for Issue #28575 (comment).

Summary of Changes

On PostgreSQL databases, a sequence is used for ID columns where MySQL uses auto-increment.

In the installation and update SQL scripts, this sequence number has to be set to the next value for new records after having inserted any records in any table with having specified values for scolumns in the insert statement. The new value is the highest present ID plus one.

In the installation SQL base.sql, the value is wrong for the #__workflow_transitions table. In the update SQL script 4.0.0-2018-05-15.sql, the sequence next value initialization is completely missing.

This leads to getting SQL errors when creating new workflow stuff, depending on if you have a new J4 installation or one updated from 3.10, at least when you create that kind of new item for the very first time. See section "Actual result BEFORE applying this Pull Request" for the details.

This PR fixes these issues.

In addition, this PR provides a new update SQL script for PostgreSQL to fix the sequences when updating a J4 Beta 1 or Beta 2. This is necessary because we want to support updating between betas on J4.

Testing Instructions

This PR only applies to Joomla 4 using a PostgreSQL database. It is not relevant when using MySQL or MariaDB.

It makes no sense to test this PR with patchtester, because the patchtester will apply the changes to the SQL files but not run the SQL to apply the changes to the database.

Test 1: New installation without the fix to reproduce the issue with new installations

1. Have a clean, current Joomla 4 installation (Beta 3 or latest nightly or current 4.0-dev branch) available which uses a PostgreSQL database and has never used workflows, or make a new installation to have one.

2. In backend, go to "Content -> ...", e.g. "Content -> Articles", and use the "Options" button to edit com_content options, and in tab "Integration" enable workflows, and save the changes.

3. Go to "Content -> Workflows" and click the badge showing the number of transitions "7" in the default workflow to go to the transitions.

4. Use the "New" button to create a new workflow transition, enter some title and use "Save" or "Save & Close".

Result: The creation of the new transition fails with an SQL error, see first screenshot in section "Actual result BEFORE applying this Pull Request" below.

5. Try again to create a new workflow transition.

Result: Now it works.

Test 2: New installation without the fix, then update to this PR for the fix

This test makes sure the issue is fixed on a future J4 beta version which includes the fix from this PR and which has been updated from a previous J4 beta version which hasn't that fix included yet.

Because the problem exists only for the very first time you create a new workflow transition, it need to start again with a new installation here, we cant continue with what we have from the previous test.

1. Have a clean, current Joomla 4 installation (Beta 3 or latest nightly or current 4.0-dev branch) available which uses a PostgreSQL database and has never used workflows, or make a new installation to have one.

2. Using a databse client like e.g. phpPgAdmin, execute the SQL statements from the update SQL script https://github.com/joomla/joomla-cms/blob/52ea314d9ad132541a4f26f3271ba511f3d5c1cb/administrator/components/com_admin/sql/updates/postgresql/4.0.0-2020-08-01.sql added by this PR, having replaced #__ with your actual database prefix:

SELECT setval('#__workflows_id_seq', (SELECT MAX("id") FROM "#__workflows") + 1, false);
SELECT setval('#__workflow_stages_id_seq', (SELECT MAX("id") FROM "#__workflow_stages") + 1, false);
SELECT setval('#__workflow_transitions_id_seq', (SELECT MAX("id") FROM "#__workflow_transitions") + 1, false);

(For this test here the last one would be sufficient, but the other two won't do any harm.)

As an alternative to steps 1 and 2 you can update the J4 without the fix to a later J4 with the fix by using the update package or custom URL of that package which has been build by Drone for this PR. That package already includes the patch of the SQL. You can find that update package and custom URL here: https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/30251/downloads/34433/.

3. In backend, go to "Content -> ...", e.g. "Content -> Articles", and use the "Options" button to edit com_content options, and in tab "Integration" enable workflows, and save the changes.

4. Go to "Content -> Workflows" and click the badge showing the number of transitions "7" in the default workflow to go to the transitions.

5. Use the "New" button to create a new workflow transition, enter some title and use "Save" or "Save & Close".

Result: The new workflow transition can be created without any SQL error. The ID of the new transition is 8 on a clean J4 installation where workflow has never been used before.

Test 3: New installation with the fix

This test makes sure that on a new installation of a future J4 version which includes the fix from this PR, creating a new workflow transition always works, also at the very first time.

1. Apply the patch of this PR on a clean, current installation of J4 (Beta 3 or latest nightly or current 4.0-dev branch), or if you just have executed Test 2 before, just continue with the same installation.

2. Delete configuration.php and make a new installation using a PostgreSQL database.

Alernatively to steps 1 and 2 you can make a new installation using the installation package which Drone has built for this PR, also using a PostgreSQL database. You can find that installation package here: https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/30251/downloads/34433/.

3. In backend, go to "Content -> ...", e.g. "Content -> Articles", and use the "Options" button to edit com_content options, and in tab "Integration" enable workflows, and save the changes.

4. Go to "Content -> Workflows" and click the badge showing the number of transitions "7" in the default workflow to go to the transitions.

5. Use the "New" button to create a new workflow transition, enter some title and use "Save" or "Save & Close".

Result: The new workflow transition can be created without any SQL error. The ID of the new transition is 8 on a clean J4 installation where workflow has never been used before.

Test 4: Updated installation without the fix to reproduce the issue with updated installations

1. Have a clean, current Joomla 3.10 installation (Alpha or latest nightly or current 3.10-dev branch) available which uses a PostgreSQL database, or make a new installation to have one.

2. Update to Joomla 4 using the Joomla Update Component. You can use custom URL or download package of latest 4.0dev nightly as they can be found here https://developer.joomla.org/nightly-builds.html, or you can update to 4.0 Beta 3 using Upload & Update of the Joomla Update Component and a previously downloaded 4.0 Beta 3 zip package.

3. In backend, go to "Content -> ...", e.g. "Content -> Articles", and use the "Options" button to edit com_content options, and in tab "Integration" enable workflows, and save the changes.

4. Go to "Content -> Workflows" and click the badge showing the number of stages "1" in the default workflow to go to the stages.

5. Use the "New" button to create a new workflow stage, enter some title and use "Save" or "Save & Close".

Result: The creation of the new stage fails with an SQL error about duplicate key.

6. Try again to create a new workflow stage.

Result: Now it works.

7. Go back to the workflows list and click the number of transitions "7" in the default workflow to go to the transitions.

8. Use the "New" button to create a new workflow transition, enter some title and use "Save" or "Save & Close".

Result: The creation of the new transition fails with an SQL error about duplicate key.

9. Try again to create a new workflow transition.

Result: Now it works.

10. Go back to the workflows list and use the "New" button to create a new workflow. Give it some title and then "Save" or "Save & Close".

Result: The creation of the new workflow fails with an SQL error about duplicate key.

11. Try again to create a new workflow transition.

Result: Now it works.

Test 5: Updated installation with the fix

This test makes sure that on a future J4 version which includes the fix of this PR and has been updated from 3.10, creating any workflow item always works, also when you create that kind of item for the very first time.

Because the problem exists only for the very first time you create a particular kind of new workflow item, it need to start again with a new Joomla 3.10 update to 4 here, we cant continue with what we have from the previous test. This test makes sure the issue is fixed when updating a 3.10 to on a future J4 version which includes the fix from this PR.

1. Have a clean, current Joomla 3.10 installation (Alpha or latest nightly or current 3.10-dev branch) available which uses a PostgreSQL database, or make a new installation to have one.

2. Update to Joomla 4 using the Joomla Update Component. You can use custom URL or download package of latest 4.0dev nightly as they can be found here https://developer.joomla.org/nightly-builds.html, or you can update to 4.0 Beta 3 using Upload & Update of the Joomla Update Component and a previously downloaded 4.0 Beta 3 zip package.

3. Using a databse client like e.g. phpPgAdmin, execute the SQL statements from the update SQL script https://github.com/joomla/joomla-cms/blob/52ea314d9ad132541a4f26f3271ba511f3d5c1cb/administrator/components/com_admin/sql/updates/postgresql/4.0.0-2020-08-01.sql added by this PR, having replaced #__ with your actual database prefix:

SELECT setval('#__workflows_id_seq', (SELECT MAX("id") FROM "#__workflows") + 1, false);
SELECT setval('#__workflow_stages_id_seq', (SELECT MAX("id") FROM "#__workflow_stages") + 1, false);
SELECT setval('#__workflow_transitions_id_seq', (SELECT MAX("id") FROM "#__workflow_transitions") + 1, false);

As an alternative to steps 2 and 3 you can update the 3.10 directly to a J4 which includes the fix of this PR by using the update package or custom URL of that package which has been build by Drone for this PR. That package already includes the patch of the SQL. You can find that update package and custom URL here: https://ci.joomla.org/artifacts/joomla/joomla-cms/4.0-dev/30251/downloads/34433/.

4. Go to "Content -> Workflows" and click the badge showing the number of stages "1" in the default workflow to go to the stages.

5. Use the "New" button to create a new workflow stage, enter some title and use "Save" or "Save & Close".

Result: The new workflow stage can be created without any SQL error. The ID of the new stage is 2 on a clean J4 installation where workflow has never been used before.

7. Go back to the workflows list and click the number of transitions "7" in the default workflow to go to the transitions.

8. Use the "New" button to create a new workflow transition, enter some title and use "Save" or "Save & Close".

Result: The new workflow transition can be created without any SQL error. The ID of the new transition is 8 on a clean J4 installation where workflow has never been used before.

10. Go back to the workflows list and use the "New" button to create a new workflow. Give it some title and then "Save" or "Save & Close".

Result: The new workflow can be created without any SQL error. The ID of the new workflow is 2 on a clean J4 installation where workflow has never been used before.

Actual result BEFORE applying this Pull Request

Creating new workflow transitions fails on a new installation of J4 with a PostgreSQL database when you do it for the very first time:

On a J4 with a PostgreSQL database having been updated from 3.10, creating new workflows, workflow stages or workflow transitions fails with the same kind of SQL error when you try to add that kind of item for the very first time.

Expected result AFTER applying this Pull Request

Creating new workflow transitions always works on a new installation of J4 with a PostgreSQL database, also when doing it for the very first time.

Creating new workflows, workflow stages and workflow transitions always works on a J4 with a PostgreSQL database coming from an update from 3.10, also trying to create that kind of item for the very first time.

Documentation Changes Required

None.