Moodle External-Internal Authentication/Enrollment Synchronication

This is something I'd wish I'd blogged about because I wasted time remembering how to do this.

This assumes an Ubuntu 8.10 install

First you must sync up the users:

php -c /etc/php5/apache2/php.ini /var/www/moodle/auth/db/auth_db_sync_users.php

Then your must sync up the enrollments

php -c /etc/php5/apache2/php.ini /var/www/moodle/enrol/database/enrol_database_sync.php

Will flesh this out later...

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.

Why use Python to access your Moodle User Database?

Well, besides that PHP is an absolute shit for brains language and basic stuff like yaml, displaying syntax errors in imported modules and other sane things you would expect after using Python or Ruby just ain't there.

And oh yeah, and it is is butt ugly ($, ->, ::, ?> etc.)

Not only that because because I was able to whip this up cool script with SQLAlchemy (no I'm not using the ORM, just want to avoid MysqlDB)

mfranz@mfranz-s10:~/crap$ cat alctest.py 
#!/usr/bin/env python
from sqlalchemy import *
from pprint import pprint
e = create_engine("mysql://moodle:blackboard@127.0.0.1/moodle")
m = MetaData(e)
user_table = Table('mdl_user',m,autoload=True,autoload_with=e)
pprint(user_table.columns.keys())
mfranz@mfranz-s10:~/crap$ ./alctest.py 
[u'id',
 u'auth',
 u'confirmed',
 u'policyagreed',
 u'deleted',
 u'mnethostid',
 u'username',
 u'password',
 u'idnumber',
 u'firstname',
 u'lastname',
 u'email',
 u'emailstop',
 u'icq',
 u'skype',
 u'yahoo',
 u'aim',
 u'msn',
 u'phone1',
 u'phone2',
 u'institution',
 u'department',
 u'address',
 u'city',
 u'country',
 u'lang',
 u'theme',
 u'timezone',
 u'firstaccess',
 u'lastaccess',
 u'lastlogin',
 u'currentlogin',
 u'lastip',
 u'secret',
 u'picture',
 u'url',
 u'description',
 u'mailformat',
 u'maildigest',
 u'maildisplay',
 u'htmleditor',
 u'ajax',
 u'autosubscribe',
 u'trackforums',
 u'timemodified',
 u'trustbitmask',
 u'imagealt',
 u'screenreader']

Now that I've got that off my chest.

So what I was trying to do, since Moodle is PHP (and I'm stuck with Moodle) and we are a PHP shop and I thought I would do the right thing and try to use PHP even though I hate it, know it is evil, etc.

The app is in PHP and there are obviously some higher-level APIs/ for accessing Moodle tables, so it makes sense I should write my scripts in PHP?

And there were.So I started using DML (although I was using Pre-2.0 has awful documentation on the wiki, so I basically had to look at the source, which at least has decent internal documentation) to provide external (meaning not through the Moodle web UI) to the Moodle user database.

But that took way too long. Of course it has been years since I've touched any PHP, so I'll admit that was part of the problem. Mainly, forgetting semi-colons. What kind of insane language requires semi-colons as statement separators?

I was contemplating some a weird hack (which I know works just fine, because I've done it before) of sending YAML over SSH (in lieu of XMLRPC, which is a pain in the ass to secure) but php-syck is completely broken with CentOS and I wasn't able to build the PHP module manually, which I shouldn't have to, anyway.

So the long and short of it. I completed in Python (and my Python is rusty) in an hour what took me 3-4 in PHP so Python it is. Honestly, much of the time could have been saved If PHP had an interactive interpreter like Ruby or Python so could quickly test out the new APIs I was learning, inspect objects, etc.

MySQL Query Logging on CentOS5 and external Moodle Authentication DB's

When I'm not blogging about two-bit Alaska mayors/governors, most of the point is to jot down things that aren't necessarily profound but that are useful, and it did not up easily in one minute of googling.

I knew I'd done this before, but like so many things you don't use every day, it is easy to forget. Fortunately I discovered the nice Windows GUI admin tools for MySQL so I wouldn't have to write command-line PHP. Something else I do like every 3-4 years.

So my goal is to enable database queries so you can debug a web app. In my case I'm trying to enable Moodle to use and external authentication database and of course it fails the first time.

So the command line argument to enable query logging is "--log=/var/log/mysql.queries"

That is easy enough, but where to put it in /etc/init.d/mysqld?

Ideally I'd like to put in the global MySQL options file (/etc/my.cnf) which gets read in by get_mysql_option() but this doesn't work although I did get it to show up with my_print_defaults (a new one for me) so I'm not sure what is up. Tried both under [mysqld] and [mysqld_safe] so I did it the old fashioned way and added it to the line that starts up mysql_safe

/usr/bin/mysqld_safe --datadir="$datadir" --socket="$socketfile" \
--log=/var/log/mysql.queries \
--log-error="$errlogfile" \
--pid-file="$mypidfile" >/dev/null 2>&1 &

Not pretty but good enough and discovered that I had not granted by local user access to the database.