Database

My Moodle Page, Now With 99.6% Fewer Queries

 

My work recently upgraded from Moodle 1.9 to 2.3, and some users were experiencing slow page loads while the site was zippy for others. Today we discovered that, for some users, the My Moodle dashboard page was requiring several thousands of DB queries. For one user, enrolled in four courses, the page required over 14,000 queries. I guess it could be worse: one user reported over 95,000!

This was completely unacceptable; this page is a common navigation point for all users, and every user is forwarded there on login.

With xdebug and Autosalvage rocking, it only took me about three hours to rework the page so that only the course links are displayed by default, and a button is provided beside each to load that course’s activities into the page via Ajax. Since most users just use the page for navigation between courses, this tradeoff seems well worth the performance gain. Now this page–without displaying activities–is down to ~60 queries for every user (sadly average for a Moodle page).

I suspect that loading the activity list for a large course will still take a performance bite, but in my limited testing it seemed pretty instantaneous–yes, there’s a reason why modern apps are built on Ajax. Although good work has been done to cache front-end files, Moodle still seems to be in serious need of query reduction optimization when building HTML pages.

After getting some feedback over the weekend, I’ll release this patch for other Moodle providers. Our theme uses jQuery and the Javascript side of this was maybe 10 lines, but I imagine it would need to be ported to YUI to get into core.

Designing a Contextual Role-Based Capability Control System

 

Update May 2: ScopedRole is now a PHP5.3 library based on this design and is passing initial unit tests!

After surveying a few permissions models that one might want to choose for an LMS, I think Moodle really got right the notion that role-based permissions should be flexible depending on the domain(s) the user’s in. Unfortunately Moodle’s implementation is completely bolted in, so I started looking around for a standalone implementation (few dependencies, no globals/state) of something similar.

For PHP dating back to 2002, phpGACL is designed and documented really well. It’s truly a drop-in solution with some advanced functionality, but doesn’t quite cover the concept of contextual roles, so it doesn’t quite cut it. What I’m imagining is something very similar but ideally without forcing a particular DB abstraction library on you.

Using the awesome WWW SQL Designer, I designed a bare-bones schema for the data:

Schema diagram for Contextal Role-Based Capability ControlThis is loosely based on Moodle’s schema, but I removed quite a lot, not only in hopes of getting it working quickly, but also because most implementers will have varying needs. E.g., implementers may need to localize role names and descriptions by language, so simple keys are all this schema is responsible for storing. I also removed special feature-related columns on the entities and link tables: the implementer is free to add columns as needed or just reference the id on each table. I’d imagine most folks dropping this into, e.g., a Zend Framework app just won’t need most of the features that Moodle had in there.

Also note it would come without a users table. The implementer will provides the system with unique integers for each user and that’s all it should need to do its job. You’re free to join the tables to create whatever views you need.

Any ideas for a catchier name than “Contextual Role-Based Capability Control System”? ScopedRole?