I have a question regarding the standard practices for handling text database fields.
I found this: NOT NULL fields using a DEFAULT '' clause
Still couldn't find the exact answer that I wanted. So, here is the situation. The install.xml file for the lesson module defines the lesson_answer table's response field as text, cannot be null and no default. In the code, when adding a new question, if the response to a question is not set, then the response is not set. This results in a SQL error because response is now NULL and there is no default. See MDL-8196.
So, should the response field be set to allow NULLs since the Moodle documentation defines empty strings as silly and problematic?
Thanks for any help on this.
Cheers,
Mark
Hi Mark,
current situation is that practically all char/text db fields are defined as NOT NULL DEFAULT '', independently if the field can be NULL (not defined) at insert or no.
The idea (Moodle 2.0) is to have all those fields properly defined, declaring them as NULL if they can be undefined and as NOT NULL if they must have ALWAYS a value.
So, starting with Moodle 1.7 and the new install.xml files, we have done this:
- For CHAR columns, in the install.xml files, all the DEFAULT ''" clauses are out. For compatibility, the XMLDB stuff will automatically re-add such default clauses to work with current code without changes. This allows to have the fields properly defined in our XML schema without breaking anything at code. Along the time, a lot of that fields will be converted to NULL (if they really allow NULL-undefined values).
- For TEXT columns we are a bit advanced and the transition has began, mainly because the number of fields affected was really lower and because a lot of DB don't allow to specify a default clause for TEXT fields. So, in NOT NULL fields using a DEFAULT '' clause you will find a list of TEXT fields that have been converted to NULL both for 1.7 and 1.8.
IMO, the basic question to answer for each field is: "At insert, can this field be undefined?" If so, the field must be "NULL". In the opposite, if the answer is no, the field must be declared as "NOT NULL". Sounds simple, eh?
And then, apart of all the NULL/NOT NULL decision comes the second question: "Does this field have any MEANINGFUL value that could be used as default?" If so, the field can have a DEFAULT clause, else no (note again that default clauses are ONLY for CHAR fields, not for TEXT fields).
So, going to your initial question, in the lesson_answer table, the question is: When a record is inserted in that table, is the response field defined with some meaningful value or no? As far as I know, following the logic of the module, it seems that the response field can be undefined at insert, so then It MUST be NULL. I would add it to http://tracker.moodle.org/browse/MDL-7354 as a subtask and perform the change to DB.
And that's all. It's important to note that, for now, we are adjusting EXCLUSIVELY TEXT fields, while CHAR fields continue using the old approach. We'll start changing them soon. But the final idea is to have all the fields that can store "undefined" values created as NULLs and if the field "ALWAYS" have a value, then define it as NOT NULL.
Hope this helps...ciao
current situation is that practically all char/text db fields are defined as NOT NULL DEFAULT '', independently if the field can be NULL (not defined) at insert or no.
The idea (Moodle 2.0) is to have all those fields properly defined, declaring them as NULL if they can be undefined and as NOT NULL if they must have ALWAYS a value.
So, starting with Moodle 1.7 and the new install.xml files, we have done this:
- For CHAR columns, in the install.xml files, all the DEFAULT ''" clauses are out. For compatibility, the XMLDB stuff will automatically re-add such default clauses to work with current code without changes. This allows to have the fields properly defined in our XML schema without breaking anything at code. Along the time, a lot of that fields will be converted to NULL (if they really allow NULL-undefined values).
- For TEXT columns we are a bit advanced and the transition has began, mainly because the number of fields affected was really lower and because a lot of DB don't allow to specify a default clause for TEXT fields. So, in NOT NULL fields using a DEFAULT '' clause you will find a list of TEXT fields that have been converted to NULL both for 1.7 and 1.8.
IMO, the basic question to answer for each field is: "At insert, can this field be undefined?" If so, the field must be "NULL". In the opposite, if the answer is no, the field must be declared as "NOT NULL". Sounds simple, eh?
So, going to your initial question, in the lesson_answer table, the question is: When a record is inserted in that table, is the response field defined with some meaningful value or no? As far as I know, following the logic of the module, it seems that the response field can be undefined at insert, so then It MUST be NULL. I would add it to http://tracker.moodle.org/browse/MDL-7354 as a subtask and perform the change to DB.
And that's all. It's important to note that, for now, we are adjusting EXCLUSIVELY TEXT fields, while CHAR fields continue using the old approach. We'll start changing them soon. But the final idea is to have all the fields that can store "undefined" values created as NULLs and if the field "ALWAYS" have a value, then define it as NOT NULL.
Hope this helps...ciao
Eloy - will the NOT NULL and blank default cause a problem if mysql has strict mode set?
Hi Anthony,
absolutely! TEXT/BLOB columns cannot have one default clause assigned (no problem with CHAR columns AFAIK) abd will fail under MySQL running in strict mode.
That's because, since Moodle 1.7 we are installing all the TEXT/BLOB columns without default clauses, converting them to NULL where it's demonstrable that they aren't mandatory for each record (like the lesson_answers->response example in this discussion).
Ciao
absolutely! TEXT/BLOB columns cannot have one default clause assigned (no problem with CHAR columns AFAIK) abd will fail under MySQL running in strict mode.
That's because, since Moodle 1.7 we are installing all the TEXT/BLOB columns without default clauses, converting them to NULL where it's demonstrable that they aren't mandatory for each record (like the lesson_answers->response example in this discussion).
Ciao
Thanks for the detailed response Eloy. Always appreciated!
I added a subtask to MDL-7354 for the lesson_answers response field. Let me know if you would like any help from me to close this issue.
Cheers,
Mark
I added a subtask to MDL-7354 for the lesson_answers response field. Let me know if you would like any help from me to close this issue.
Cheers,
Mark
Hi Mark,
as said in MDL-7354, as you want. Just be quick answering/implementing the change because branching for 1.8 is near and all the changes to DB must be committed before that.
Ciao
as said in MDL-7354, as you want. Just be quick answering/implementing the change because branching for 1.8 is near and all the changes to DB must be committed before that.
Ciao