Unmasking the Mysteries of the Moodle User/Course Database: Part I

CAVEAT: From this blog post you'll [correctly] conclude I have way too much time on my /hands, but heh, when you work on your day off you can be as inefficient as you like!

(Oh yeah and all of this, running moodle, mysql, mysql GUI tools, gimp, etc. was all done on my Netbook. These are decent little machines. I'm glad I bought a hard drive instead of a flash because you can use them for real apps. All in a 2 pound package. I would recommend an external mouse/trackball if you want to save you thumbs). A bit hotter than

mfranz@mfranz-s10:~$ uptime
 09:23:44 up 15:39,  4 users,  load average: 0.53, 0.63, 0.59

mfranz@mfranz-s10:~$ free
             total       used       free     shared    buffers     cached
Mem:       1543920    1505324      38596          0     106532     571888
-/+ buffers/cache:     826904     717016
Swap:      1983988        668    1983320

A bit hotter (CPU-wise) than I'd like but Opera was the only thing that bogged down a bit.

The Problem: How do you programmatically find out which students are enrolled in a given moodle course? Since the new authorization/enrollment model implemented in Moodle 1.7 (IIRC) this becomes a little more difficult because the data is spread across a number of tables in the moodle database

Basically you want to find out something like this.



I'm the only student in CF102.

So we start with mdl_user (the table we retreived the metadata on in a previous blog)



Remember, my id is 3

Now to look at the courses (mdl_course)



Remember that CF102 has an id of 3 as well.

Here is where it starts to get interesting. The role_assignment table shows that my user has a roleid of 5 and a contextid of 11. Both of these are necessary to understand what a given use can or cannot do/view in terms of course content.



The role_capabilities table defines what roleid 5 is.



The roleid of 5 corresponds to a student and and the capability is self-explanatory.

Now back to the contextid (from the role_capabilities table), which is the indirect link to the course through the mdl_context table. For once I actually highlighted the correct row. In this case we are interested in a contextid of 11.



I cut the field names off, but the third field is instanceid (which is 3) and points us back to the courseid which corresponds to CF102.

Simple, eh?

In the next blog post on this topic I'll write some Python/SQLAlchemy code to retreive a list of users that are enrolled in a given course or which courses a student is enrolled in.