For developers, getting acquainted with the relational model that characterizes the information architecture of Moodle is a basic skill. But for the rest of us, getting to know how Moodle functions underneath can offer us some insight or welcome intellectual challenge.
Previously we have reviewed the work of Moodler Marcus Green, who showed us a bird’s-eye view of the tables and connections that make up the Moodle data model.
This time, it is the turn of James MacNee, developer at West College Scotland, and a pride SMUG. Member of the Scottish Moodle User Group, that is.
MacNee delivered his workshop, Running SQL Queries on the Moodle Database as requested by SMUG. It takes us further into the detail of columns rows, keys and selection of the data for Moodle. The possibilities of SQL queries are limitless: verifying editing permissions of administrative roles, track the course path of a cohort of students, analyze engagement and feedback by instructor controlling by gender or other characteristics; you name it.
To begin, MacNee presents us with the toolbox, which includes:
- The Moodle Adminer plugin, which combines the power of direct SQL query manipulation with the speed and practicality of automatic PHP scripting and running.
- The Moodle Ad-Hoc Database Queries plugin, which turns any query into a report that can be visualized or exported to a spreadsheet.
- The Moodle Configurable Reports plugin, that allows highly customizable report creation without any SQL knowledge required.
See the Moodle documentation on Configurable Reports.
- And phpMyAdmin, a PHP tool to manage MySQL databases.
Trying out different queries, to list users and permissions for example, could arouse one’s curiosity, but if the results are to cryptic or massive, the opposite can be the case. MacNee suggests applying limits to the number of results or use aggregators for numerical values, such as averages.
Using the tools to make changes on the database directly, MacNee warns, is “technically possible“. But this should be done with extreme caution and subject matter expertise.
The good things about the plugins is that upon installation, they become available right away for admins, on the “Site Administration” panel in Moodle. phpMyAdmin works as a different environment, and by definition it only works with MySQL databases, in any case the default. You’re better off verifying.
The results offered by the tools are, in the words of MacNee, “elegant“. The reports can be set up to share to other, perhaps less technically savvy, or they can be generated as “dumps” in the command line, for personal or more informal situations.
See MacNee’s slides below:
Suggest an interesting SQL query in the comments for your fellow MoodleNews readers!