Replace zero datetime with nullable datetime

[nikosdion]

In the past we have been using DATETIME and TIMESTAMP fields which are set to NOT NULL and have a default value of 0000-00-00 00:00:00. Zero date usage has been discouraged since MySQL 5.7 released in 2013. The default SQL mode in MySQL 5.7 and later includes NO_ZERO_DATE.

In and of itself it's not a big problem. Joomla still sets the SQL mode to STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION which notably does not include the NO_ZERO_DATE mode, allowing the use of zero dates. If it didn't do that you could never migrate from Joomla 3.

Strategy:

• Update the mysql.xml file adding upgrade blocks similar to:

      <action table="#__foo_bar" canfail="1">
          <condition type="nullable" value="field_name" operator="not"/>
          <query><![CDATA[
      ALTER TABLE `#__foo_bar` MODIFY `field_name` DATETIME NULL DEFAULT NULL;
      ]]></query>
          <query><![CDATA[
      UPDATE `#__foo_bar` SET `field_name` = NULL WHERE `field_name` = '0000-00-00 00:00:00';
      ]]></query>
      </action>

• Update mysql.xml, the CREATE TABLE parts.
• Find any references to 0000-00-00 00:00:00 in the code.
• Find any references to nullDate in the code.
• If the extension has an Updates model with a createFakePackageExtension method the line $db->q($db->getNullDate()) . ',' . needs to be changed to (version_compare(JVERSION, '3.9999.9999', 'le') ? $db->q($db->getNullDate()) : 'NULL') . ',' .