[Koha-bugs] [Bug 7806] Don't use 0000-00-00 to signal a non-existing date

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=7806

--- Comment #22 from Martin Renvoize <martin.renvoize at ptfs-europe.com> ---
(In reply to Marcel de Rooy from comment #19)
> Although this looks quite good to me, it is really hard to be convinced that
> all appearances of 0000-00-00 in the data now are caught correctly by the
> codebase.
> 
> I found at least one issue (no blocker):
> select count(*) from items where dateaccessioned= '0000-00-00';
> +----------+
> | count(*) |
> +----------+
> |      335 |
> +----------+
> 1 row in set (0.12 sec)
> This comes from a production database.
> So I tested with an item. Set dateaccessioned to 0*. Open item editor. You
> see the 0* on the form. Tab thru the field so that the date gets FOCUS. This
> will update the 0* in the old situation, but not in the new one (obviously).
> Note that the CLICK event uses the force parameter and will set the date.
> 
> Could you add this one to the atomicupdate too?
> And please provide me some evidence that we found the last case ;) For
> instance by checking date columns in a larger production database being used
> for a longer time..
> 
> PQA for now.

Thanks for QAing this Marcel, your right, I should have detailed my testing
:(.. I basically looked at where the code changed and tried to trigger those
actions via the UI to check things weren't broken.

As for this comment, I'm pretty sure we'll have left behind some bad data, but
it should get picked up by upgrades now that we throw errors in the
updatedatabase script.

We could I suppose write a one off atomicupdate to check and fix all possible
datefields at this point in time..

I used the following to get a list of such fields.... we have 210!

'''
SELECT col.table_schema     AS database_name,
       col.table_name,
       col.ordinal_position AS column_id,
       col.column_name,
       col.data_type,
       col.datetime_precision
FROM   information_schema.columns col
       JOIN information_schema.tables tab
         ON tab.table_schema = col.table_schema
            AND tab.table_name = col.table_name
            AND tab.table_type = 'BASE TABLE'
WHERE  col.data_type IN ( 'date', 'time', 'datetime', 'year', 'timestamp' )
       AND col.table_schema NOT IN ( 'information_schema', 'sys',
                                     'performance_schema',
                                     'mysql' )
       AND col.table_schema = 'database_name'
ORDER  BY col.table_schema,
          col.table_name,
          col.ordinal_position;
'''

-- 
You are receiving this mail because:
You are watching all bug changes.