Hi,
the current 'lib/db/install.xml' of MOODLE_17_STABLE, version 1.30.2.1 defines
<FIELD NAME="mailformat" TYPE="int" LENGTH="1" NOTNULL="true" UNSIGNED="true" DEFAULT="1" SEQUENCE="false" ENUM="false" PREVIOUS="description" NEXT="maildigest"/>
for the "user" table. Shouldn't NOTNULL be "false"?
And, normally, its TYPE is set to "tinyint", however there is not one TYPE in that file set to "tinyint" - is this intended?
rob.
In reply to Robert Allerstorfer
Re: Databases: typo in lib/db/install.xml ?
Hi Robert,
in XMLDB slang, int is used to define any integer from "tiny-tiny" to "big-big". The key field in that definition is the "length" one that, ultimately, decides what type of integer implementation is going to be created really.
You can see the complete mapping of types and lengths and their correspondence for each DB implementation at:
http://docs.moodle.org/en/XMLDB_column_types
I think it explains why you won't see nothing but "simple" ints in the XMLDB files.
About the "NOT NULL" clause for that field, I really think its correct. The field is mandatory (i.e. NOT NULL) and it has a correct default value (1) in case somebody tries to insert one record without it.
Every user have one "mailformat". Yes (so NOT NULL). Why not?
Ciao
in XMLDB slang, int is used to define any integer from "tiny-tiny" to "big-big". The key field in that definition is the "length" one that, ultimately, decides what type of integer implementation is going to be created really.
You can see the complete mapping of types and lengths and their correspondence for each DB implementation at:
http://docs.moodle.org/en/XMLDB_column_types
I think it explains why you won't see nothing but "simple" ints in the XMLDB files.
About the "NOT NULL" clause for that field, I really think its correct. The field is mandatory (i.e. NOT NULL) and it has a correct default value (1) in case somebody tries to insert one record without it.
Every user have one "mailformat". Yes (so NOT NULL). Why not?
Ciao
In reply to Eloy Lafuente (stronk7)
Re: Databases: typo in lib/db/install.xml ?
by Robert Allerstorfer -
Hi Eloy,
thanks for making this clear to me - helped a lot. Thus, according to the matrix table at
Development:XMLDB column types
the only possible TYPE values of FIELD definitions within 'lib/db/install.xml' are
int, number, float, char, text, binary, datetime
(Note: I replaced "date with time" which has been listed there originally by "datetime" which seems to be the correct value).
Regarding the decision if columns are allowed to hold
mysql> describe mdl_user;
+---------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+----------------+
(...)
| description | text | YES | | NULL | |
| mailformat | tinyint(1) unsigned | YES | | 1 | |
(...)
| ajax | int(1) unsigned | YES | | 1 | |
(...)
+---------------+---------------------+------+-----+---------+----------------+
45 rows in set (0.02 sec)
while in the corrsponding 45 FIELD definitions in 'lib/db/install.xml' just have one with NOTNULL="false":
<FIELD NAME="description" TYPE="text" LENGTH="small" NOTNULL="false" SEQUENCE="false" ENUM="false" PREVIOUS="url" NEXT="mailformat"/>
Also wondering why the ajax column in my MySQL table is a int(1) type instead of tinyint(1). My understanding now was that the XMLDB type "int" of a length "1" (<=2) will automacigally be resolved into the type "tinyint" on MySQL.
thanks for making this clear to me - helped a lot. Thus, according to the matrix table at
Development:XMLDB column types
the only possible TYPE values of FIELD definitions within 'lib/db/install.xml' are
int, number, float, char, text, binary, datetime
(Note: I replaced "date with time" which has been listed there originally by "datetime" which seems to be the correct value).
Regarding the decision if columns are allowed to hold
NULL values or not, I just wondered that out of 45 columns of Moodle 1.7.1's "user" table I am having under MySQL 5.0.27 there are 3 allowing NULL values:mysql> describe mdl_user;
+---------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+----------------+
(...)
| description | text | YES | | NULL | |
| mailformat | tinyint(1) unsigned | YES | | 1 | |
(...)
| ajax | int(1) unsigned | YES | | 1 | |
(...)
+---------------+---------------------+------+-----+---------+----------------+
45 rows in set (0.02 sec)
while in the corrsponding 45 FIELD definitions in 'lib/db/install.xml' just have one with NOTNULL="false":
<FIELD NAME="description" TYPE="text" LENGTH="small" NOTNULL="false" SEQUENCE="false" ENUM="false" PREVIOUS="url" NEXT="mailformat"/>
Also wondering why the ajax column in my MySQL table is a int(1) type instead of tinyint(1). My understanding now was that the XMLDB type "int" of a length "1" (<=2) will automacigally be resolved into the type "tinyint" on MySQL.
In reply to Robert Allerstorfer
Re: Databases: typo in lib/db/install.xml ?
Hi Robert,
IMO the "correct" status for those columns (mailformat & ajax) is to be NOT NULL, i.e. foreach record, they must have one meaningful value. That is the approach followed both by the original lib/db/*.sql files and the new lib/db/install.xml schema.
Reviewing the upgrade path at lib/db/*.php files it seems that it contains a bug because the NOT NULL clause in missing there so, for upgraded installs such field will be defined as NULL, whereas for fresh installs such fields will be defined as NOT NULL.
Note that this little bug won't affect at all because such columns (both) have a meaningful default value that will guarantee contents for them.
About the TEXT description field it's NULL simply because it allows null (was changed in the 1.6 => 1.7 migration). No problem here.
Also, note that all the nullability of fields is going to be analysed soon (because right now we a have a BIG excess of NOT NULL fields due to some-years-ago limitations). In the future we'll end with more NULL fields (all those that aren't mandatory to have meaningful values), while NOT NULL fields will be applied to fields requiring values.
This is applicable both to CHAR and TEXT fields. With 1.7 we have started the migration of TEXT fields (they are less, we can use them to "experiment") and soon it will be the turn for CHAR fields (although it isn't the highest priority).
You can find some more information about all these ideas and progress of NOT NULL clauses in Moodle here:
http://docs.moodle.org/en/Development:XMLDB_problems#NOT_NULL_fields_using_a_DEFAULT_.27.27_clause
Ciao
Edited: I've created Bug MDL-8421 to track and fix the user->ajax bug.
IMO the "correct" status for those columns (mailformat & ajax) is to be NOT NULL, i.e. foreach record, they must have one meaningful value. That is the approach followed both by the original lib/db/*.sql files and the new lib/db/install.xml schema.
Reviewing the upgrade path at lib/db/*.php files it seems that it contains a bug because the NOT NULL clause in missing there so, for upgraded installs such field will be defined as NULL, whereas for fresh installs such fields will be defined as NOT NULL.
Note that this little bug won't affect at all because such columns (both) have a meaningful default value that will guarantee contents for them.
About the TEXT description field it's NULL simply because it allows null (was changed in the 1.6 => 1.7 migration). No problem here.
Also, note that all the nullability of fields is going to be analysed soon (because right now we a have a BIG excess of NOT NULL fields due to some-years-ago limitations). In the future we'll end with more NULL fields (all those that aren't mandatory to have meaningful values), while NOT NULL fields will be applied to fields requiring values.
This is applicable both to CHAR and TEXT fields. With 1.7 we have started the migration of TEXT fields (they are less, we can use them to "experiment") and soon it will be the turn for CHAR fields (although it isn't the highest priority).
You can find some more information about all these ideas and progress of NOT NULL clauses in Moodle here:
http://docs.moodle.org/en/Development:XMLDB_problems#NOT_NULL_fields_using_a_DEFAULT_.27.27_clause
Ciao
Edited: I've created Bug MDL-8421 to track and fix the user->ajax bug.