Posted by moshe weitzman on October 30, 2008 at 6:35pm
user.module has long supported storing custom fields in its table. values in those fields are automatically loaded into $user and saved during hook_user('insert'|'update').
has anyone used this feature in d6? i cam considering doing so for a high performance site where avoiding the join is helpful. with hook_scema_alter() we now have a clean wayto extend tables so i am feeling like this is a reasonable feature to use in custom code. i still would not recommend it for contrib code.
i know about the downsides of this approach with respect to upgrades. core is upgraded infrequently enough that we can deal with the repercussions.
Comments
Moshe, Are you talking about
Moshe,
Are you talking about the data column of the user table that gets a serialized array of all user array fields not stored elsewhere?
Dan DeGeest
Lead Software Developer
iMed Studios
http://www.imedstudios.com
Dan DeGeest
Software Developer
Somewhere or Another
Good question. No, thats a
Good question. No, thats a despicable feature that I hope folks are no longer using. I hope to get rid of it because all fields are serialized into one column.
The feature I refere to lets you add real custom DB columns, as many as you like, to the users table. Those columns are automatically used for loading and saving data. See user_fields() and the places where that is called in user.module
Awesome, I totally agree -
Awesome, I totally agree - the {user}.data thing has burned me with undesired behavior several times and should be phased out in light of all the new options for adding in additional data. I have not used user_fields() however so I fear I'm not much help for the original question. I don't know if JOINs are really a lot slower but there is definitely some nicety to having all your data in one table when it makes sense since the queries are a lot simpler.
Dan DeGeest
Lead Software Developer
iMed Studios
http://www.imedstudios.com
Dan DeGeest
Software Developer
Somewhere or Another
I didn't know it existed.
I didn't know it existed. It appears missing from the a.d.o D7 documentation. But based on the a.d.o documentation for D6 I don't see how to ``add'' a new field. Seems it would require an install step to add a new column; am I correct on that?
Eeew
I definitely agree that contrib modules should never do this, as it is bound to break. For instance, in D7 we're hoping ot have an SQLite driver and SQLite doesn't support DDL modifications. You have to create a new table and copy the data over. Depending on the implementation that could very easily break any custom fields you've added.
For site-specific custom code, maybe. I've not done so myself, preferring to just use hook_user(). The problem is that you can't (AFAIK) force another table to be joined to the user query. It has to be a second query. Just adding a join wouldn't be that expensive at all if it's properly indexed. It's the extra query that is a problem.
Hm... maybe in D7 we can make that a dynamic, alterable query... :-)
I agree, I was looking at
I agree, I was looking at user_fields() last night and that seems like a "hacky" shortcut used by core to get the column names and might be an upgrade issue if you add your own columns to that table. It doesn't look like user_fields() is called though on user_load() so I don't really see how this helps. Unless you call user_load(user_fields()) in your own code? The user object for the logged in user won't have your custom fields loaded by core AFAIK. It seems inconsistent and a little risky just for the end product of one query vs two.
It would be cool if in the future hook_user LOAD was changed or augmented to allow JOINs to be defined for the query used in user_load() rather then just being a hook called after to core load is complete where you can do secondary queries and add values.
And {user}.data should go a away, IMO
Dan DeGeest
Lead Software Developer
iMed Studios
http://www.imedstudios.com
Dan DeGeest
Software Developer
Somewhere or Another
user_load() does a SELECT *
user_load() does a SELECT * in D6 do thats how it loads custom columns as well.
In D7, we already ditched user_fields() in favor in favor of schema API. And the updating of those fields in cleaner too as we use drupal_write_record() instead of user_fields().
Yeah, I misread how
Yeah, I misread how user_load() uses the passed in array() parameter. Thanks for the clarification, this has been a good learning discussion.
Dan DeGeest
Lead Software Developer
iMed Studios
http://www.imedstudios.com
Dan DeGeest
Software Developer
Somewhere or Another
Speaking of this - why is
Speaking of this - why is user_fields doing a query instead of just looking at user_schema()??? If it did this we wouldn't even be having this discussion.
Dan DeGeest
Lead Software Developer
iMed Studios
http://www.imedstudios.com
Dan DeGeest
Software Developer
Somewhere or Another
data column might still be useful if...
...say for example that you're adding a field that allows users change a site wide setting. Maybe just a few number of users would change that value.
If you choose to alter the users table with a new column, then you might be wasting unnecessary space in the DB, or maybe creating more overhead. The alter table statement could also take a lot of time if there are a lot of records in the table (users), and that may cause module install/uninstall procedures to get a connection timeout.
If you choose the old method, then you have the option to only include the value in the generic data column if the value to store is really diferent from default.
So maybe it depends...
True, but this is a pretty
True, but this is a pretty obscure use case. I don't think we need to design for it ... Have you looked at the contents of this column on a live site. It ALWAYS has cruft it.
It's mostly lazyness...
I don't know how many contrib modules make use of this 'data' in users table. One example is TinyMCE/Wysiwyg module stores the user setting to enable rich-text by default. I've been using it to store a few custom user settings as well, so I could just alter the user object and go. No need to install profile module, no need to create a custom table, no need to extend users table with more fields... simple, but no really nice. For example, if a module adds a custom field that way, then it should be able to drop that field when uninstalled, but if it's implemented usind 'data', then it's almost 100% sure that it will remain as cruft.
The new database schema in D6 is a great thing. Though, maybe it needs a bit more documentation for contrib developers that need to add data that's user related. For example, how could you undo something that has been done using hook_schema_alter() ? Is that even possible?
we're doing this ...
We have a custom, in-house id for users. We added it to the users table and it has been extremely handy to have it automatically loaded into the user object w/out any extra work. We've been doing this for a while. So far, no downside from my perspective, but can see where this might be an issue for contrib ... for our purposes, where we just needed the one extra field, it has been really handy ... and it was easy to expose the extra field to views as well. Where did you wind up landing on this Moshe?
The Date API adds a user
The Date API adds a user timezone column using hook_schema_alter() and it works quite well. I only had to create the column in the install and set up hook_schema_alter() and my value gets automatically saved every time the user is updated and loaded every time the user is loaded. The Event module does the same thing for its user timezone field.
I'm concerned that there might be a future migration issue (Crell's comment at http://groups.drupal.org/node/16356#comment-55973), since this behavior is allowed and has never been discouraged, and it is often touted as a feature, I would hope we would provide an upgrade path for these things.