Form field maxlengths vs. database field sizes
This issue stems from Killes' comment [ http://drupal.org/node/46306#comment-71971 ]. Should form field maxlengths be consistent with corresponding database field sizes or should unicode encoding be accounted for? This is especially in light of the fact that 4.7 now converts all existing databases to UTF-8, but also retains support for (MySQL) 3.23.58. Or should we just not use maxlengths at all? Thanks -K P.S Please reply on the issue page: http://drupal.org/node/48510
On 08 Feb 2006, at 6:22 AM, Karthik wrote:
This issue stems from Killes' comment [ http://drupal.org/node/46306#comment-71971 ]. Should form field maxlengths be consistent with corresponding database field sizes or should unicode encoding be accounted for? Drupal has no way of knowing what the field size of the database is, and as such the forms api can't take that into account.
-- Adrian Rossouw Drupal developer and Bryght Guy http://drupal.org | http://bryght.com
Adrian Rossouw wrote:
On 08 Feb 2006, at 6:22 AM, Karthik wrote:
This issue stems from Killes' comment [ http://drupal.org/node/46306#comment-71971 ]. Should form field maxlengths be consistent with corresponding database field sizes or should unicode encoding be accounted for?
Drupal has no way of knowing what the field size of the database is, and as such the forms api can't take that into account.
It does not presently, but Drupal could know the field sizes in the database tables. However, is that worth doing? (I'm presently developing a database abstraction layer for my employer where the db layer figures out the metadata, like field sizes, on the fly so that the applications need not know anything at all about it.) ..chrisxj
Chris Johnson schreef:
Adrian Rossouw wrote:
On 08 Feb 2006, at 6:22 AM, Karthik wrote:
This issue stems from Killes' comment [ http://drupal.org/node/46306#comment-71971 ]. Should form field maxlengths be consistent with corresponding database field sizes or should unicode encoding be accounted for?
Drupal has no way of knowing what the field size of the database is, and as such the forms api can't take that into account.
It does not presently, but Drupal could know the field sizes in the database tables. However, is that worth doing? (I'm presently developing a database abstraction layer for my employer where the db layer figures out the metadata, like field sizes, on the fly so that the applications need not know anything at all about it.)
The problem is inconsistency across mysql versions. On MySQL 4.1+ (and w/ drupal 4.7), database field sizes are counted in characters. On 4.0 and below, they are in bytes. So for a varchar(256) we could have from 64 to 256 characters (4 to 1 bytes per character) with UTF-8. Should we set the maxlength to 64 then? We settled on the compromise of half (128) on the assumption that most people use 1-2 byte characters. Once we phase out MySQL 4.0 we can safely make them all match the database sizes. Steven
Op donderdag 09 februari 2006 10:01, schreef Steven Wittens:
Once we phase out MySQL 4.0 we can safely make them all match the database sizes.
Ruby on Rails's ActiveRecord does this database->form mapping amazingly well. We should take some of their concepts into Drupal, and I therefor encourage everyone to do a 20 minute quickstart in RoR development. Just to get the feeling of how Drupal /could be/ in this database mapping smartness! http://ar.rubyonrails.com/docs #tutorials Bèr
Ruby on Rails's ActiveRecord does this database->form mapping amazingly well. We should take some of their concepts into Drupal, and I therefor encourage everyone to do a 20 minute quickstart in RoR development. Just to get the feeling of how Drupal /could be/ in this database mapping smartness! http://ar.rubyonrails.com/docs #tutorials
schemeql (schematics.sf.net) goes one further - you forget the db (nearly) completely. You just use the language (php) to define your structures and it is automagially converted into an appropriate db call(s). Combined with the "other" half - a decent serialisation of the data into xhtml, via templates or whatever and you have everything you'll ever need. But is it really appropriate? Are we grown up enough?
On Thu, Feb 09, 2006 at 12:37:53PM +0000, vlado wrote:
Ruby on Rails's ActiveRecord does this database->form mapping amazingly well. We should take some of their concepts into Drupal, and I therefor encourage everyone to do a 20 minute quickstart in RoR development. Just to get the feeling of how Drupal /could be/ in this database mapping smartness! http://ar.rubyonrails.com/docs #tutorials
schemeql (schematics.sf.net) goes one further - you forget the db (nearly) completely. You just use the language (php) to define your structures and it is automagially converted into an appropriate db call(s).
That's the recommended way of doing things with Ruby as well. Of course, it's easier to simply disregard new technologies instead of taking the time to understand them. -- Keegan Quinn <keegan@thebasement.org> CEO, Producer the basement productions http://www.thebasement.org
Ruby on Rails's ActiveRecord does this database->form mapping amazingly well. We should take some of their concepts into Drupal, and I therefor encourage everyone to do a 20 minute quickstart in RoR development. Just to get the feeling of how Drupal /could be/ in this database mapping smartness!
Active Record is a standard OO pattern. Ruby on Rails did not invent this idea. It's been around for more than a decade.
Op maandag 13 februari 2006 16:45, schreef Chris Johnson:
Active Record is a standard OO pattern. Ruby on Rails did not invent this idea. It's been around for more than a decade.
The more reason for drupal to start using it :) Bèr
The problem is inconsistency across mysql versions. On MySQL 4.1+ (and w/ drupal 4.7), database field sizes are counted in characters. On 4.0 and below, they are in bytes. So for a varchar(256) we could have from 64 to 256 characters (4 to 1 bytes per character) with UTF-8. Should we set the maxlength to 64 then? We settled on the compromise of half (128) on the assumption that most people use 1-2 byte characters.
Once we phase out MySQL 4.0 we can safely make them all match the database sizes.
Steven
But where all should these guidelines be implemented? This affects any VARCHAR field from node titles (128) to usernames (60) and would effectively restricts (for e.g.) node title lengths to 64 characters? Won't it be better to just follow database field sizes and recommend users requiring multi-byte support to upgrade to 4.1+ for best results? This would allow for the greatest degree of consistency and the least amount of hassle. How about pgSQL? Thanks -K
On Sun, Feb 12, 2006 at 01:43:57PM +0530, Karthik wrote:
How about pgSQL?
Postgres counts in characters. [Sidenote] Furthermore, there's no difference in performance between char/varchar/text and I've changed several columns from varchar(n) to text in cases where data was of unknown lenght (e.g. referrer, url etc). I've left other columns unchanged because I didn't want to drift away from mysql version too much, but I'd prefer to change (almost) all columns for user-supplied data (e.g. node title comes to mind) to unlimited lenght... I don't see why we should limit user and say how long his title can be. -- Piotrek irc: #debian.pl Mors Drosophilis melanogastribus!
On Sun, 2006-02-12 at 09:40 +0100, Piotr Krukowiecki wrote:
On Sun, Feb 12, 2006 at 01:43:57PM +0530, Karthik wrote:
How about pgSQL?
Postgres counts in characters.
[Sidenote] Furthermore, there's no difference in performance between char/varchar/text and I've changed several columns from varchar(n) to text in cases where data was of unknown lenght (e.g. referrer, url etc).
I've left other columns unchanged because I didn't want to drift away from mysql version too much, but I'd prefer to change (almost) all columns for user-supplied data (e.g. node title comes to mind) to unlimited lenght... I don't see why we should limit user and say how long his title can be.
To add on... My understanding of Mysql(isam && innodb), correct me if I'm wrong, is that there is really no difference between the varchar(n) types and text. At least in terms of memory consumption/character. http://dev.mysql.com/doc/refman/4.1/en/storage-requirements.html I'd really prefer seeing greater text field lengths. It would close a few issues in the issue queue as well... Like aggregator clipping RSS guid's. .darrel.
On Mon, February 13, 2006 11:07 am, Darrel O'Pry said:
To add on...
My understanding of Mysql(isam && innodb), correct me if I'm wrong, is that there is really no difference between the varchar(n) types and text. At least in terms of memory consumption/character. http://dev.mysql.com/doc/refman/4.1/en/storage-requirements.html
I'd really prefer seeing greater text field lengths. It would close a few issues in the issue queue as well... Like aggregator clipping RSS guid's.
.darrel.
That may be true, but there's a lot less that you can do with a text field than a varchar field. A varchar can be a key, can be indexed, and can be used in a WHERE field='' clause. A text field has none of those, AFAIK. (You can use WHERE LIKE, but not =.) The real problem is the low character limit on varchar in MySQL, which I believe is 255. (Contrast to MS SQL which is some 8 KB for an nvarchar.) We may want to use text in more places than we do now, but it is less flexible than varchar programmatically. --Larry Garfield
2006/2/13, Larry Garfield <larry@garfieldtech.com>:
The real problem is the low character limit on varchar in MySQL, which I believe is 255. (Contrast to MS SQL which is some 8 KB for an nvarchar.)
True. But MySQL is SQL standart compliant where VARCHAR should hold 255 characters max.. For more, BLOB like (often called TEXT when they only have to deal with charactars) fields must be used..
On 08 Feb 2006, at 8:28 PM, Chris Johnson wrote:
It does not presently, but Drupal could know the field sizes in the database tables. However, is that worth doing? (I'm presently developing a database abstraction layer for my employer where the db layer figures out the metadata, like field sizes, on the fly so that the applications need not know anything at all about it.)
I want to do this for 4.8. it simplifies the install files (which are in core now), and allows us to possibly automate some of the _update_x function creation. essentially db_create_table('name', array('field' => 'type', 'field' => 'type'); etc. keeping track of all the columns in the database is important for stuff like views and relationships too. -- Adrian Rossouw Drupal developer and Bryght Guy http://drupal.org | http://bryght.com
http://drupal.org/node/46306#comment-71971 ]. Should form field maxlengths be consistent with corresponding database field sizes or should unicode encoding be accounted for?
Drupal has no way of knowing what the field size of the database is, and as such the forms api can't take that into account.
Huh? I know that a taxonomy term can be 255, because it is hardcoded in the database definition, so why is it hardcoded in FAPI as only 64? What's the use of the other 190 or so characters? -- Morbus Iff ( putting the sanity back in sanity ) Technical: http://www.oreillynet.com/pub/au/779 Culture: http://www.disobey.com/ and http://www.gamegrene.com/ icq: 2927491 / aim: akaMorbus / yahoo: morbus_iff / jabber.org: morbus
This issue stems from Killes' comment [ http://drupal.org/node/46306#comment-71971 ]. Should form field maxlengths be consistent with corresponding database field sizes or should unicode encoding be accounted for? Drupal has no way of knowing what the field size of the database is, and as such the forms api can't take that into account.
Achtually, php can get the complete properties of a mysql database/table/field ..including maximum field sizes ( mysql_field_len(), mysql_field_type() and mysql_field_flags() ), For postgres if's similar ( pg_field_size() ). So it must be possible for drupal to get to that information too. Regards, Lodewijk Evers http://www.ontwerpwerk.nl
On Thu, February 9, 2006 7:47 am, lodewijk evers said:
This issue stems from Killes' comment [ http://drupal.org/node/46306#comment-71971 ]. Should form field maxlengths be consistent with corresponding database field sizes or should unicode encoding be accounted for? Drupal has no way of knowing what the field size of the database is, and as such the forms api can't take that into account.
Achtually, php can get the complete properties of a mysql database/table/field ..including maximum field sizes ( mysql_field_len(), mysql_field_type() and mysql_field_flags() ), For postgres if's similar ( pg_field_size() ).
So it must be possible for drupal to get to that information too.
Regards,
Lodewijk Evers http://www.ontwerpwerk.nl
Allowing Drupal to auto-derive database information would be extremely useful. For instance, since many many nodes have a 1:1 mapping from their database table to their Drupal object, if the system "knows" what those fields are it can automate the basic hook_load(), hook_save(), and hook_insert() calls. That means less redundant code. (We could actually do that now without db introspection if a node could be asked for an array of its fields, but I've avoided trying to add that until after 4.7 ships. <g>) A step further, as I'm sure others are thinking, would be for Drupal to read the database table and pre-populate a $form object. It wouldn't be perfect, but it would again reduce the amount of code needed in each node module. Lots 'o fun. However, I would say up front do NOT derive that information on the fly! If every node_load() call has to rederive the structure of the node from the database, that's a big performance hit. That's the sort of information that can/should be build once when the module is enabled and then cached. PEAR and RoR do it through code generation, but to be honest I've never been a fan of that. If the information is standardized enough that it can be auto-generated, then it is standardized enough that it can be fed as input values into a common routine/function. --Larry Garfield
participants (13)
-
Adrian Rossouw -
Bèr Kessels -
Chris Johnson -
Darrel O'Pry -
Gildas Cotomale -
Karthik -
Keegan Quinn -
Larry Garfield -
lodewijk evers -
Morbus Iff -
piotr＠mallorn.ii.uj.edu.pl -
Steven Wittens -
vlado