Pull Request for Issue # .

Summary of Changes

This pull request (PR) fixes the "pkg_search" extension being checked out after an update from 3.10 or from a previous 4.0 version with an update history back to 3.10 where com_search with all its extensions hasn't been uninstalled before the update.

This is fixed by adding new update SQL scripts "4.0.6-2021-12-23.sql" which updates the extension record for "pkg_search" if that extensions hasn't been really checked out meanwhile.

The reason for the issue can be found in the old update SQL script "4.0.0-2021-08-17.sql", which inserts the record for that package with value "0" instead of "NULL" for the "checked_out" column:

https://github.com/joomla/joomla-cms/blob/4.0-dev/administrator/components/com_admin/sql/updates/mysql/4.0.0-2021-08-17.sql#L1-L2

https://github.com/joomla/joomla-cms/blob/4.0-dev/administrator/components/com_admin/sql/updates/postgresql/4.0.0-2021-08-17.sql#L1-L2

In my opinion it would be a cleaner way to fix also these statements in these old SQL update scripts so they don't insert initially with a wrong value. Columns "checked_out" and "checked_out_time" should be removed from these inserts since they have the right default values.

But it was subject of arguments and also confusion in past when I had changed old update SQL scripts, which I'd like to avoid this time, and so I leave these old scripts alone.

With the later running update SQL "4.0.3-2021-09-04.sql", the extension record handled by this PR is removed again in case if no other extension than that package is installed.

So the new SQL script added by this PR will fix the record for the package in case if it is still there at the end.

Of course this fix should later be merged up into 4.1-dev, too.

What could be worth a discussion

It's another question if a value of zero in the "checked_out" column shall be treated as checked out or not by com_checkout and at other places.

Currently it is shown as checked out, and if people notice that and properly check in the item, all will be good.

But I observed in diverse presentations with J4 that people don't care.

That's why this PR here makes sense in my opinion regardless if we fix that behaviour or not.

I think even if we make the core handle "legacy" values for "checked_out" and "checked_out_time" columns in a b/c way in case if 3rd party extensions still do it the wrong way, the core should not do it in the wrong way for com_search.

Testing Instructions

1. Have a Joomla 3.10 installation of whichever kind (current 3.10-dev branch, latest 3.10 nightly or any 3.10.x stable release) on which com_search has not been removed and which currently has not any checked out items.
2. Update to 4.0.5 or latest 4.0-dev nightly build.
3. Check in the System Dashboard if there are checked out items shown in section "Maintenance", item "Global Check In".
   Result: 1 checked out item.
4. Go to "Global Check In" and check in which database table the item is checked out.
   Result: It's the extensions table.
5. Check in your database which extensions record has a checked_out value not being NULL.
   Result: It's the "pkg_search" extension.
6. Start again with step 1 with the same starting conditions and then update to the package or custom update URL created by drone for this PR.
   Alternatively, if you are too lazy to do that (shame on you), just run the SQL statement from the new update SQL script added by this PR for your database type, replacing the database prefix in the table name by the right one.
7. Repeat steps 3 and 4.
   Result: Nothing checked out.

Actual result BEFORE applying this Pull Request

Expected result AFTER applying this Pull Request

Documentation Changes Required

None.