Should I hack the mdl_user table, or add my own?

Should I hack the mdl_user table, or add my own?

by Greg Lyon -
Number of replies: 17
Search though I might I can't seem to find what I'm looking for, which is advice.

I'm running Moodle 1.7 with daily cvs updates ().

I need to add some custom fields for user data.  I'm sure I've seen this topic discussed, but where?  The way I see it I have the following choices:

  1. reuse the 'extra' fields in mdl_user (yahoo, skype etc), and hack the language file. 
  2. create my own table which links to mdl_user, add as many fields as I want, customize any page that I need the data to show on (basically any view/edit page I guess).
  3. Add fields to mdl_user.  This seems less desirable than #2 to me.
  4. what about mdl_user_info_fields and mdl_user_info_data?  those tables sound like they were made exactly for what I want to do...but they seem to be orphans in moodle and there's not a bit of documentation here or in tracker, or on the web as far as I can tell.
  5. Other options I haven't considered?
I'd like to make my changes in such a way that it causes the least maintenance nightmare for me at upgrade time etc.  I'm mostly vacillating between option #1 and 2, with #1 in the lead right at this moment.  #3 seems to combine the downsides of #1 and 2, so unless there's a great argument I'm not in favor of it.  I could take a guess at how #4 works but there's not much advantage over #2 since it doesn't appear to be used in the UI anywhere, and if it's something about to be implemented, well...
that way there be monsters.

Thanks for any light you can shed!
Average of ratings: -
In reply to Greg Lyon

Re: Should I hack the mdl_user table, or add my own?

by sam marshall -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers
Quick answer: whatever you do that changes the database, do it in the local/db scripts.

I think we may have done both 2 and 3 here. smile 
Average of ratings: -
In reply to sam marshall

Re: Should I hack the mdl_user table, or add my own?

by Greg Lyon -
Thanks Sam.

By local/db scripts do you mean just script my edits so I can reapply them as needed, or do you mean that there is some Moodle built-in tool that I should use?  I'm assuming it's the former, but do tell if it's the latter!
Average of ratings: -
In reply to Greg Lyon

Re: Should I hack the mdl_user table, or add my own?

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
If you create a top-level folder called local, and a subfolder called db, with a version.php file in the local folder, and a postgres7.php/mysql.php file in the db folder, then those databse upgrades happen, like with a module. You will have to follow through exactly what the upgrade code in admin does to work out the details of what you need to do.
Average of ratings: -
In reply to Tim Hunt

Re: Should I hack the mdl_user table, or add my own?

by Greg Lyon -
Thanks, Tim,  Just what I needed.  I've scanned the admin code several times but never  picked up on the local/db bit...
Average of ratings: -
In reply to sam marshall

Re: Should I hack the mdl_user table, or add my own?

by Martín Langhoff -
If you are not going to be using those fields to retrieve records (in the WHERE part of your SELECT fields), a way to keep things simple is to stash that data in mdl_user_preferences. There are convenience functions -- set_user_preference() I think.

Keeping a modified mdl_user table over version transitions of moodle is tricky (local/db helped -- and you can blame me for it) and it's only justifiable performance-wise if you are using those fields in WHERE clauses.
Average of ratings: -
In reply to Martín Langhoff

Re: Should I hack the mdl_user table, or add my own?

by Greg Lyon -
Thanks for the tips, Martín.  I hadn't delved into user_preferences yet either.  As I do need to retrieve based on some of these fields sometimes I'll try out the new user_info* tables...
Average of ratings: -
In reply to Greg Lyon

Re: Should I hack the mdl_user table, or add my own?

by Martin Dougiamas -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
In Moodle 1.7 there are new tables exactly for this purpose:

user_info_field
user_info_data
user_info_category

The idea is to let the admin add new fields to the profile (and to eventually move a lot of the existing fields into this new structure too).

Shane Elliott's been working on the code, but it got a bit late and I didn't want to mess with the user profile page at this last minute before 1.7 so it didn't get included.

It'll go into 1.8 dev as soon as we branch. I'll ask him to post here with a little more details about how it works.
Average of ratings: -
In reply to Martin Dougiamas

Re: Should I hack the mdl_user table, or add my own?

by Shane Elliott -
Picture of Core developers Picture of Plugin developers
Actually Martin described it all pretty well.

user_info_field will hold the field name and some settings regarding locking, visibility, default data, etc
user_info_data holds the data for each field for each user
user_info_category will allow us to place the fields into categories - am thinking at this stage that there will be two legacy categories "moodle required" and "moodle optional" into which we'll move the current profile fields. These can be renamed through the admin interface

Categories have a sortorder as well as fields within each category to give us some flexibility in how they appear. We'll use the formlib library to build the user profile page.

The "datatype" in user_info_field will be the type of data to be stored in this field - similar concept to the database module. Types will be pluggable. Initially we'll have text, textarea, menu (taking names from database module). The plugin code will deal with the form field, validation, and output.

Things to do (not nec in order):
1. Database tables - already in 1.7, although there might be some tweaking
2. The admin interface to add/edit/delete profile fields
3. Migrating some of the fields from the user table to the new user_info tables
4. Change the various library functions that retrieve user data
5. Backup and restore (Eloy, Eloy wherefore art though?!!)
6. User profile pages

I've probably missed a heap of things in my haste but I hope that gives you some idea of what we're doing.

The upshot is that you can use these tables now if you want to although you will need to write your own code to retrieve/use/display the data.


Average of ratings: -
In reply to Shane Elliott

Re: Should I hack the mdl_user table, or add my own?

by Dan Poltawski -
Hmm - so is this like additional visible profile data rather than internal data?

I'm thinking specifically of wanting (at some stage) to perhpas link the moodle users to another external source and it'd be useful to have an id field which links a moodle user to the external id, if you understand what I mean. But this would need to be purely internal and not touched by users really.
Average of ratings: -
In reply to Dan Poltawski

Re: Should I hack the mdl_user table, or add my own?

by Martin Dougiamas -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
It can be both. The visibility/locking of the field determines who can see/edit the field.

About your need, there is already an "idnumber" field in the user table for that (most of the enrolment plugins use it already).
Average of ratings: -
In reply to Shane Elliott

Re: Should I hack the mdl_user table, or add my own?

by Greg Lyon -
Thanks for the explanations Martin, Shane.  I should have known they were New, what with the complete lack of info in forums/tracker/docs.  For some reason I thought they were old and abandoned...

This sounds GREAT by the way. approve   Too bad it couldn't make the 1.7 cut but obviously you can't do everything at once and 1.7 is already full of wonderful enhancements. 

I will proceed using the new tables and I may hit on some of your to-do list Shane.  I'm not sure if I'm up to production quality php yet but I'll post back my work in case it helps.  I'll be getting to work on it today.  My Moodle implementation requires several user fields that aren't in the default Moodle.

One of the things I'm going to try is to make the User entry form tabbed, such that mandatory info goes on one page and other info goes on other pages.  I don't usually mind long data entry forms but I hear complaints about them.  I'm just afraid that soon I'll be hearing complaints about tabbed dialogs ... wink
Average of ratings: -
In reply to Shane Elliott

Re: Should I hack the mdl_user table, or add my own?

by Greg Lyon -
As I read back over this thread I'm wondering, is the goal going to be to move ALL fields to the user_info* tables? Or will it be for optional info only, retaining a main mdl_user table for the key required fields?

The comment from Martin L. is making me wonder about performance of database searches if all of the data is moved to the mdl_user_info_data table. And since I need to be able to search on some of the fields I plan to add I wonder if I'm not going to be better off to fall back to my option 2 above and create a 2nd table that has a 1-1 relationship with mdl_user. This has to stand up to 30,000 users or more eventually.

I will be adding 3 fields that need to be indexed: State (U.S.), Zipcode, and 'supervisorid' or something of the sort (as I've mentioned in this thread). I'm also planning to add some less important fields. Maybe those would be appropriate for the user_info fields.

Also, would anybody care to share an example of their script from local/db ? I'm using MySql.

Thanks in advance for your thoughts.
Average of ratings: -
In reply to Greg Lyon

Re: Should I hack the mdl_user table, or add my own?

by Martin Dougiamas -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
The plan is to:

1) Set things up so these new tables are for new fields only.
2) Examine/decide which of the existing fields can be migrated to this (probably only a few)
Average of ratings: -
In reply to Martin Dougiamas

Re: Should I hack the mdl_user table, or add my own?

by Greg Lyon -
OK, that makes sense, Martin. I'll think of the mdl_user_info* tables as a repository for ancillary information. It'd be a great place to move the yahoo/skype/aim type info + any non-core customizations we (moodle developers) may want to add...

I suppose even the main mdl_user table could take advanatage of the mdl_user_info_category table for display purposes. It'd be nice to have a tabbed dialog for users to fill out instead of the long form currently in use. One tab per category would be pretty slick, and there could be a category called 'hidden' for any unneeded fields.

What I've stared (for my own needs) is to create a secondary user table which will have an (0/1)<=>(1) relationship with the mdl_user table. I still have to make my own custom form for data entry.
Average of ratings: -
In reply to Shane Elliott

Re: Should I hack the mdl_user table, or add my own?

by Richard Eagleton -
This sounds like a great approach to me and it is good to hear that you are well on the way to developing it. Do you need a hand with the coding in any area?
Average of ratings: -
In reply to Greg Lyon

I Hacked the moodle user table

by Greg Lyon -
For the sake of completeness, Here's what I decided to do:

Since some of the fields I'm adding will be fairly important indexed keys I've decided to hack the main user table. I decided that I didn't want to risk the possible performance (and other) issues of the other methods discussed here. There is also the nice benefit that there are only a couple SQL and form edits required to get the new fields to show up in the forms. Moodle is nice that way!

I've yet to implement the /local/db script, but my changes are all saved as SQL alter table queries so once I get some time I hope it's relatively easy to do.

Thanks for your ideas/suggestions and help.

Greg.
Average of ratings: -
In reply to Greg Lyon

autocomplete text box

by sharmistha banerjee -
i want to add a auto complete text box in moodle1.8 edit profile page.this code contains some java script functions also.how can i implement this.plz help
Average of ratings: -