Use alternate default for standard date fields?

Chris Paschen commented

2022-08-05 17:58:01 +00:00

Member

Presently, when building an Admin view, all the 'system' date fields get set with a default of '0000-00-00 00:00:00'.
This causes errors in our MySQL version (5.7.31). [We are stuck with this for now]

created DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
modified DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
checked_out_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',

The main problem is that further changes to the tables result in an 'invalid default' notice from mySQL.

We could (and have) manually changed those (to '1001-01-01 00:00:00') in the Component Updates view after first adding the admin view/table; however, that requires adding the view, and compiling, changing the defaults in the Component Updates view, uninstalling the component, re-compiling and re-installing the component to get the new date values.

We can easily deal with this for our own datetime/calendar fields by changing the db field settings in the FieldTypes area; however, it appears that JCB doesn't look there when building these fields.

I also know that we could manually override these by creating our own 'versions' of these fields, overriding the defaults, for each Admin View that we create; however, when creating dozens of admin views for a complex component that gets a bit long.

Is there any present way to change the default for these system fields default values?

Presently, when building an Admin view, all the 'system' date fields get set with a default of '0000-00-00 00:00:00'. This causes errors in our MySQL version (5.7.31). [We are stuck with this for now] `created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', `modified` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', `checked_out_time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', The main problem is that further changes to the tables result in an 'invalid default' notice from mySQL. We could (and have) manually changed those (to '1001-01-01 00:00:00') in the Component Updates view after first adding the admin view/table; however, that requires adding the view, and compiling, changing the defaults in the Component Updates view, uninstalling the component, re-compiling and re-installing the component to get the new date values. We can easily deal with this for our own datetime/calendar fields by changing the db field settings in the FieldTypes area; however, it appears that JCB doesn't look there when building these fields. I also know that we could manually override these by creating our own 'versions' of these fields, overriding the defaults, for each Admin View that we create; however, when creating dozens of admin views for a complex component that gets a bit long. Is there any present way to change the default for these system fields default values?

Chris Paschen commented

2022-08-05 20:08:01 +00:00

Author

Member

Looks like I spoke too soon.
I just tried to manually override this and it doesn't work.

In the Component Updates area I changed the above entries in the CREATE TABLE areas to:

created DATETIME NOT NULL DEFAULT '1900-01-01 00:00:00',
modified DATETIME NOT NULL DEFAULT '1900-01-01 00:00:00',
checked_out_time DATETIME NOT NULL DEFAULT '1900-01-01 00:00:00',

Then uninstalled the component, then re-compiled and re-installed.

But the defaults for these fields are all set to "0000-00-00 00:00:00"

And I have some subsequent ALTER TABLE entries (in later version updates) that are not being applied to the tables (although some are).

This is happening with JCB3.1.4 on mysql 5.7.31.

I guess we'll just have to see if we can push to get MySQL upgraded and see if that has any effect on it.

Looks like I spoke too soon. I just tried to manually override this and it doesn't work. In the Component Updates area I changed the above entries in the CREATE TABLE areas to: `created` DATETIME NOT NULL DEFAULT '1900-01-01 00:00:00', `modified` DATETIME NOT NULL DEFAULT '1900-01-01 00:00:00', `checked_out_time` DATETIME NOT NULL DEFAULT '1900-01-01 00:00:00', Then uninstalled the component, then re-compiled and re-installed. But the defaults for these fields are all set to "0000-00-00 00:00:00" And I have some subsequent ALTER TABLE entries (in later version updates) that are not being applied to the tables (although some are). This is happening with JCB3.1.4 on mysql 5.7.31. I guess we'll just have to see if we can push to get MySQL upgraded and see if that has any effect on it.

Llewellyn commented

2022-08-08 06:49:12 +00:00

Owner

This tutorial shows you how to overide a default field.

https://www.youtube.com/watch?v=FHQfIhWHYyQ&list=PLQRGFI8XZ_wtGvPQZWBfDzzlERLQgpMRE

This tutorial shows you how to overide a default field. https://www.youtube.com/watch?v=FHQfIhWHYyQ&list=PLQRGFI8XZ_wtGvPQZWBfDzzlERLQgpMRE

Llewellyn closed this issue

2022-08-08 06:49:12 +00:00

Chris Paschen commented

2022-08-12 19:49:36 +00:00

Author

Member

But this then means that we have to override each of the standard date fields in every table we use in a component.
When we have 10+ tables that gets to be a bit long.

Is there a possibility that we could somehow do this with powers (either now or in the future)?

But this then means that we have to override each of the standard date fields in every table we use in a component. When we have 10+ tables that gets to be a bit long. Is there a possibility that we could somehow do this with powers (either now or in the future)?

Llewellyn commented

2022-08-15 11:25:00 +00:00

Owner

I get it... This field has been an issue, and we should update the defaults. I will look at this again, and see what our options will be.

Llewellyn reopened this issue

2022-08-15 11:25:00 +00:00

Chris Paschen commented

2022-08-15 15:09:31 +00:00

Author

Member

BTW ... I'm wondering if this (at least what I'm runing into with issues with the 0000-00-00 default), is related to mysql versions.

If it is, maybe rather than trying to solve it with code, just setting a higher minimum MySQL value would 'resolve' the issue. (And force me to push to get the server upgraded :-) )

With J4, if the MySQL minimums are higher and avoid this issue, I don't think there's any reason to spend time tracking this down.

So ... if someone can confirm that this is a MySQL issue, just tell me to 'go get an update' and we could close this :-)

BTW ... I'm wondering if this (at least what I'm runing into with issues with the 0000-00-00 default), is related to mysql versions. If it is, maybe rather than trying to solve it with code, just setting a higher minimum MySQL value would 'resolve' the issue. (And force me to push to get the server upgraded :-) ) With J4, if the MySQL minimums are higher and avoid this issue, I don't think there's any reason to spend time tracking this down. So ... if someone can confirm that this is a MySQL issue, just tell me to 'go get an update' and we could close this :-)

Llewellyn closed this issue

2024-03-26 10:10:09 +00:00

Use alternate default for standard date fields? #945