Tomorrow when I’m back to my PC first thing I’ll do is review and test. First look on my mobile phone looks good.

@wilsonge What did you mean with "Has the side effect of this being parseable by the schema checker"? The update statements? Those are DML (data manipulation language part of SQL) and so still not parsed by the database schema checker/fixer.

In table #__content, the column checked_out_time is NULL instead of 1970-01-01 00:00:00 (PostgreSQL).
But publish_up, publish_downcolumns have a null date value.

@twister65 As far as I understand this PR, it is desired that each of these 3 columns checked_out_time, publish_up and publish_down of table #__content shall be nullable and that for records where such a column has value '1970-01-01 00:00:00' it is changed to NULL by running the sql commands in file administrator/components/com_admin/sql/updates/postgresql/4.0.0-2019-09-14.sql in PhpPgAdmin. Did you do it that way, and did you have for each column an example which should have value NULL aftert that?

I applied the patch with patchtester, and click on Fix in the system -> database.
After that, I created a new unpublished article.
I can see in the database that publish_up and publish_down columns have a null date value, but not ˋchecked_out_timeˋ which is NULL.

"fix" button does only run DDL (data definition language) part of SQL language, i.e. ALTER TABLE, DROP TABLE, CREATE TABLE, but not DML (data manipulation language), i.e. INSERT, UPDATE, DELETE, so the Fix button only changes default values of columns but does not run the update statements for changing values of existing records.

One of the secrets of the Fixer ?

P.S.: You have to run the update statements of the update sql scripts in PhpPgAdmin.

P.P.S: I.e. DDL changes data structure, DML changes data content. DB checker/fixer can only handle DDL, the DML statements are reported as "xxx statements did not alter structure and so were skipped" (or so).

I also applied this patch in joomla.sql before a new installation with the same result.

In table #__content, the column checked_out_time is NULL instead of 1970-01-01 00:00:00 (PostgreSQL).

@twister65 To be honest, I did not fully understand your comment anyway. Do you mean the default value of column checked_out_time is NULL? Or do you mean the value for some records from some sample data? If the latter: This PR does not change the sample data values.

It works fine with columns publish_up and publish_down.

And what did you mean with that? What works? I would expect them to be null and have default value of null, too.

Maybe you could explain to mea bit more detailled? I really want to be sure I don't understand anything wrong. I could not test this PR myself yet on PostgreSQL.

I mean the values of my new unpublished article created. In the table structure, these three timestamp columns have a default value of NULL.
Therefore, these three columns must be NULL for a new unpublished article.

Sorry, I misunderstand this PR, I thought there was a parser / checker in the code behind for null date values.

An update container based on nightly build of today + this PR applied for testing update of 3.9.11 or current staging to 4.0-alpha12-dev using the "Upload & Install" tab of the Joomla Update Component can be found here: Obsolete link removed

@wilsonge I've just tested new install on MySQL. Com_content columns are like they should regarding definition. When I create a new article in backend, checked out time column has value null, but puplished up and down are still inserted with the '0000-00-00...' datetime value. When I change this to null in PhpMyAdmin, all works fine, i.e. I can set publish up and down, change article status and so on, all fine it seems. So it seems that it needs only to change some code for inserting new articles to get rid of the '0000-00-00...' for the nullable columns (not nullable ones are other thing). Maybe we can take that from @Hackwar 's PR #25760 . SHall I make a PR to your branch, George?

@wilsonge For saving new articles with null value for all nullable columns, i.e. also publish up and down, see wilsonge#38. In addition it also sets the modified column to the created value if modified is null.

In addition it also sets the modified column to the created value if modified is null.

@richard67 Shouldn't modified date be nullable?

@SharkyKZ No, we decided to have it not nullable, and equal to created date for new records.

What's the reasoning behind this?

Was George's idea, maybe for BC reasons or for versioning.

By the way don't we need to update the values in UCM content table?

i think so

Oh yes ucm ... right, they have to be updated ... will work on it tomorrow ... George is a bit busy so I‘ll help.

@alikon Could you test? Update: Sorry, wait, is still 1 update in the pipeline.

@alikon @Quy @SharkyKZ @twister65 Hint for testers: You can test as described in PR #26372 for the banners, just here check for the articles of com_content instead of the banners. But the procedure is the same.

There is 1 open issue we haven't solved in this PR: When you make an article be checked out, e.g. by editing and leading the edit page without saving, all is fine, checkout time is correct, lock icon shown in articles list and so on. But if you then check-in the article using either the lock icon in the articles list or com_checkin, then the checkout time is set to '0000-00-00 00:00:00' on MySQL, and I would bet (not tested yet) to '1970-01-01 00:00:00' on PostgreSQL. This is because the checking is something global being made to work with not only 1 table. This should not stop this PR because everything seems to work as well as before, but it has to be fixed later when all PRs making checkout times nullable have been done.

@wilsonge Do you agree that we fix this later, or shall we try to fix it in this PR here? If the latter: George and everybody else help me with ideas of how to do that so it still works for tables where that column is not nullable.

New installation looks good on MySQL 5.7 and PosgreSQL 10.10, with respect to the checkin issue mentioned in my previous comment. Now will make modified update container from nightly build + this PR for testing update path from 3.9.12/staging.

Update test looks good on MySQL. Created some article before on staging, added some tag to one of the articles, checked dates in db, then updated to 4.0.0-alpha12-dev using "Upload & Update" and checked dates in DB again: Nullable stuff which should have NULL has NULL, other datetimes are correct, too, no sql or php errors. Other testers can find my modified noghtly build from today with this PR included here: https://test5.richard-fath.de/Joomla_4.0.0-alpha12-dev-Development-Update_Package_2019-09-29_pr-26295.zip.

On PostgreSQL the update fails with SQL error due to an error in the sql update script for com_workflow. This will be fixed when PR #26428 is merged. For joomla.sql this error has arleady been fixed with 81d0526 so it happens only when updating.

I've just updated the modified update zip package I've linked in my comment above , so it includes the changes from PR #26428 to fix failing update on PostgreSQL. So it does not need to wait for PR #26428 being merged until we can test update path for this PR here.

I have tested this item ✅ successfully on f05c532

Tested new installation with this PR applied and update from current staging to a nightly build of today with an update container with this PR applied and for PostgreSQL corrections from PR #26428 so that the update does not fail due to reasons not related to this PR here.

Each test I've done on MySQL 5.7 (with MySQLi client) and PostgreSQL 10.10 (PDO).

Hints for other testers:

You can download the modified update zip container here: https://test5.richard-fath.de/Joomla_4.0.0-alpha12-dev-Development-Update_Package_2019-10-03_pr-26295.zip.

The testing instructions would be the same as in PR #26428 , except that here you have to check tables #__content, #__ucm_content and #__ucm_history for proper datetime/timestamp without time zone values.

To be done:

When checking in a previously checked out article, the checked_out_time is not set to null but to '0000-00-00' on MySQL and '1970-01-01' on PostgreSQL.

This is a global problem but does not do any harm. Com_content seems to work well with those values also when this PR here has been applied.

We have to fix that independently from this PR.

@wilsonge Not sure if I can be counted as a neutral tester since I've contributet a lot to this PR, so @alikon @Quy @SharkyKZ @twister65 and all others: More testers are welcome. Some instructions you can find in my test result.

@alikon @Quy @SharkyKZ @twister65 and all others: More testers are needed. Instructions you can find in my test result above. I've updated the zip package for update test and the link to it so it is like current 4.0-dev plus this PR.

Thankyou for all you've done to help with on this @richard67 <3

I have tested this item ✅ successfully on f05c532

on mysql 8.0.16-0ubuntu3

RTC

Please commit the 2 suggested changes.

@Quy The ucm_content table is updated for each component in the particular update sql script, see e.g. the other update sql in this PR.

@wilsonge I guess @Quy means the 2 changes in code comments. Other comments are clarified, I thinks.

Thanks @richard67 !

Thanks @wilsonge .. it was team work.