Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
MySQL database fields definition.
Yesterday I was trying to optimize the database performance and I saw things that put me in doubt. See an example:
looking ‘textpattern’s table definition we see:
`Annotate` int(2) NOT NULL default ‘0’
About this sentence, two questions:
- Why default value of an integer field is a char?
- if a parentheses only specify the display with of integer data types (see [1]) why use `int(2)` when we store 0/1 values? Is not better using tynyint(1)?
Thanks for your time and sorry if it’s a silly question :-) ciao.
[1] http://dev.mysql.com/doc/refman/5.5/en/numeric-type-attributes.html
Offline
Offline
Re: MySQL database fields definition.
Hi Gocom,
where can I find 4.6-dev version’s code?
Offline
Offline
Re: MySQL database fields definition.
Sorry Gocom,
but I don’t see the changes. Neither [1], [2] or [3] had been modified to transfer int(2) to tinyint (or an other integer field adjustment). Sorry for my blindness :-)
[1] https://github.com/textpattern/textpattern/blob/1f284cf1d3b2a9b34443f156997d47161e4f139a/textpattern/update/_to_4.6.0.php
[2] http://textpattern.googlecode.com/svn/development/4.x/textpattern/update/_to_4.6.0.php
[3] http://textpattern.googlecode.com/svn/development/4.x/textpattern/setup/txpsql.php
Offline
Re: MySQL database fields definition.
Quoting myself:
Textpattern’s database field definitions are in places wrong; some of which we have resolved in 4.6-dev.
I general there has been improvements to field definitions. Where these integers come down to, is that we can’t simply downgrade field types to lower storage space, as that causes issues if the table contains a value greater than what the new type allows. Albeit changes are small, and no one should be doing it, some plugins do use fields to store values that aren’t used by core.
For instance, I myself use negative statuses to keep of track of purged articles; each purged article uses n*-1 of the original status. Now if we changed the status to unsigned, things would break. For boolean fields the change of course is smaller; its hard to find good reason to use the field to store something else.
Offline
Re: MySQL database fields definition.
Firts of all, thanks for your answers.
I’m agree with you that fixing data types on update.php could be dangerous. But, why not include it on TPX’s installation process?
This action could bring problems in the future with actual plugins that use actual database structure, I know it, but in some moment we should do things well :-)
Offline