Monday, 25 March 2013


Last night as I was falling asleep I remembered a story from years ago about squirrel researchers.

The primary researcher and his/her assistant set up a series of challenges for squirrels. The initial estimate was that it would take a week for a group of squirrels to "learn" the challenges and complete them easily. Once they were set up, the primary went to get coffee on the assumption that they probably wouldn't see a squirrel for half an hour. S/He returned within twenty minutes to be greeted by the gobsmacked assistant who had not only seen the first squirrel attempt the challenges, but watched it complete the whole collection in the time it had taken the primary to collect coffee.

Anyone who doubts the brilliance of a motivated squirrel can check this video, on a similar theme.

Squirrels have very few motivations, really. They want to be physically comfortable, warm and well fed. They also want their offspring to survive to maturity. The challenges that we witness squirrels mastering are always to meet one of these motivations. What is astonishing is the problem solving ability squirrels have, when the motivation is strong enough.

Teenagers want to be warm, comfortable and well fed. As social creatures they also want the respect of their peers and the more ambitious of them have a goal for their future - career aspirations, or personal goals.

What we insist of students in the UK is that they stay in education until they are 18, that they study to the highest level they are capable of and that they leave the education system with basic qualifications.

Our demands, or expectations if you prefer, don't actually meet up with any of the primary motivators of the average teenager. Other than those with a specific ambition that requires an education, what incentives are there to the students to stay in education?

I was lucky: both my parents trained as teachers and knew the value of education. I didn't have to push myself, because they managed to convince my subconscious that education is something I should have and keep trying to get more of. I didn't have a goal, or driving force to perform well at school. I did it because my parents expected me to and I didn't have any sufficiently strong motivation to drive me in any other direction. It wasn't my goal, my dream or my motivation. I think it is telling that my only A* GCSE was the one that I particularly chose (Art) - the others were either mandatory, or the best of an uninteresting lot.

But what about everyone else? We tell them they must get good GCSEs to get good ALevels. They must get good ALevels to get a good job or go to university. They must get a good degree to get a good job. But, when you think about it, those are all very abstract concepts - especially to someone who has never had the responsibility of working so that they can feed themselves at the end of the day, or pay rent, or bills. The squirrels won't complete an obstacle course in order to qualify for a mortgage and likewise our teenagers aren't in a position to understand the value of what is on offer.

We either need to give them benefit of three years of life and work experience before offering them education, so they can see what it's all about, or offer them a more immediate motivator to enable them to really commit to their own improvement.

Friday, 15 March 2013


I am delighted to be looking at ePortfolios right now.

I'd like to finish my queries that I was having so much trouble with, but every time I look at a database I feel myself getting dizzy and confused.

So, I'm looking at the portfolio options enabled within Moodle.

I was slightly surprised to see the list of default options as it's rather more extensive than I was expecting:

Portfolio plugin

I've used some of these in various ways (although I'm intrigued by "File Download") and I know they all have their own benefits.

However, I think I'm going to succumb to hype - I really like the look of Mahara after a cursory examination, but the thing I genuinely like about it is the option for students to take ownership. The problem we're hearing is that students aren't particularly comfortable with Moodle and therefore don't take to it. 

It is possible that even if they aren't comfortable with Mahara to begin with, the freedom they have to customise it to suit themselves may give them the incentive to continue.

Thursday, 7 March 2013

Why handwritten exams?

ALevel students, as I'm sure many of you know, have exams at the end of the year. Our current education minister Michael Gove is actually pushing for the whole assessment of their education to be a single end-of-year paper. Because the papers are handwritten by the students, throughout the year they have to complete exercises that involve them writing large quantities to ensure they are prepared for an exam at the end of the year.

Because of this, we have to keep printing out papers for students to complete.

So is there an alternative? Well, we could certainly insist that every young person has a tablet device that is compatible with writing responses to an exam paper pdf, but given that more than a quarter of people live in "low-income households" it is difficult to justify pushing that additional cost onto them. Similarly, as the budget is continuously chipped away from education, it is more than slightly intimidating to consider the cost of issuing every student a tablet device for the forseeable future.

But that's only if the exam papers remain handwritten. What about the other possibility? Why do the exam papers have to be handwritten? Can that be changed?

Well, I have given this some thought, and I think there is at least one viable alternative.

Have you heard of the Raspberry Pi? It's a computer, about the size of a credit card, and costs £15. It's developed by a British charity and well worth looking into.

Now, imagine if, instead of printing and mailing papers, the exam boards put a copy of their paper onto the Pi, and mailed one per student out to each participating college? The college would plug the Pis in, attach a monitor, keyboard and mouse, and the student could type their responses as they would in every other aspect of life.

In terms of security: Each student would register on the device with their student ID, in the same way they fill out the front of the exam paper. We could easily have the Pis locked until a specific time, so the exam only became accessible when it was supposed to be. And once the exam boards receive all the Pis they could upload all the papers to their own storage (as some already do) prior to marking, then wipe and reset the Pis for reuse the following year.

This way, we don't have to keep training students in handwriting, and exam boards save a fortune (and many forests) in paper costs. And, as the Pi is smaller and lighter than an exam paper, they would arguably save on postage too.

Wednesday, 6 March 2013

(Failing at) Understanding the Moodle database 4

This is deeply frustrating. I'm building up a sketch of how I think this is constructed and I can't find the missing piece. So:

On Moodle, we have courses. There is a table (mdl_course) which contains the basic construction details  for each course and assigns an id value.

On Moodle we have users. There is a table (mdl_users) which contains the basic details about each user and assigns an id value.

There are various contexts on Moodle in which people can be enrolled. These may be courses, categories, etc. Context Levels don't appear to be defined in the database but can be tracked down in the docs, which may or may not be obsolete (?)

However, for the purposes of this: Users are enrolled on Courses. The contextlevel for courses is 50.

Each context is described in a table (mdl_context) which gives each context an ID, describes the contextlevel, the depth it occurs at (system, category, sub-category^nth, course, resource) and the instanceid. In this table, instance id refers to the specific id of the item. ie, instanceid 474 occurs twice in my table - once for a user and once for a course.

There are various ways people can be enrolled on a course. The methods in which people are or may be enrolled are listed in a table (mdl_enrol) which describes an enrolment method, related to a courseid and states if it is currently allowed. It also describes a sort order for these methods and an id for each position in the table.

There are various roles users can have within courses and across the site. The different roles are described in  a table (mdl_role) and given an id.

There is a table (mdl_role_assignments) which assigns a roleid to each userid with a specific contextid.

So, in role_assignments, for a random userid there is an entry:

id   | roleid | contextid | userid | component
2169 | 5      | 3611      | 1034   |

Then, in context we can look for id 3611 and see

id   | contextlevel | instanceid | path | depth
3611 | 50           | 152        |      | 4

We know from the context level that this is a course, with instance id 152

So, lets look in mdl_course for id 152

id  | category | Course name and other details -->
152 | 49       | 

So we know the user, the course they are enrolled on and the role they have in that course. However, when I did my count, not all the students were listed. If we look in mdl_enrol, we can see this course only allows manual enrollments. 

So lets look at course 588. This allows manual, category and two meta enrollments. We'll try to track down the students in meta enrollment id 2926:

id   | enrol | status | courseid 
2926 | meta  | 0      | 588

so in mdl_context lets look for contextlevel = 50 and instanceid = 588

id    | contextlevel | instanceid | path | depth
10662 | 50           | 588        |      | 4

and in mdl_role_assignments lets check out contextid 10662

id   | roleid | contextid | userid | component
3835 | 3      | 10662     | 21     | enrol_meta
3836 | 4      | 10662     | 405    | enrol_meta
3837 | 5      | 10662     | 46     | enrol_meta 
3838 | 5      | 10662     | 48     | enrol_meta

and I get 14 entries. 1 teacher, 1 non-editing teacher and 12 students.

EDIT: see, that's not actually helpful. I just checked the course and of the two meta linked courses one has 44 and the other 77 students.

There must, somewhere in one of these tables be an enrolid column, surely? Where I can look up = 2926 (and 2925, which was the other option)

There is, in mdl_user_enrolments a column called enrolid, so I'll just look into that... There are 131 rows for 2926 and 133 for 2925. While that sounds more promising a little cross checking shows that there is too high a percentage of people who are not enrolled on this course listed here. Additionally, apart from the two additional userids in 2925, both lists are identical.

id    | status | enrolid | userid
46550 | 0      | 2926    | 17
46551 | 0      | 2926    | 18

Lunch break, I think.

EDIT (a couple of days later!): 

count (distinct mdl_user_enrolments.userid) as 'Students'
, mdl_context.instanceid as 'Course ID'
, mdl_course.fullname as 'Course Name'
from [moodle].[dbo].[mdl_role_assignments]
join [moodle].[dbo].mdl_context on mdl_role_assignments.contextid =
join [moodle].[dbo].mdl_course on mdl_context.instanceid =
join [moodle].[dbo].mdl_enrol on = mdl_enrol.courseid
join [moodle].[dbo].mdl_user on mdl_role_assignments.userid =
join [moodle].[dbo].mdl_user_enrolments on = mdl_user_enrolments.enrolid

where mdl_context.contextlevel = '50'
group by mdl_context.instanceid, mdl_course.fullname
order by [Course Name] asc

This query counts people enrolled on a course. It is not just students - but this is because a lot of my students do not have a specific role assigned to them - if you want to count students only, include "and mdl_role_assignments.roleid = '5'". The difficulty I was having was due to stupidity on my part (I was crosschecking on the wrong set of data :( )

Monday, 4 March 2013

Understanding Moodle database 3

Great news!

Getting the course name out (previously all reports were unit names, although in Moodle they are courses, so it gets a bit confusing) is actually very simple.

In mdl_course there is a category value and in mdl_course_categories we can identify the category name from the associated id.

So in my first query I added two new lines and changed the order:

, mdl_context.instanceid as 'CourseID'
, as 'Category'
, mdl_course.fullname as 'CourseName'
, as 'email'
from [moodle].[dbo].[mdl_role_assignments]
join [moodle].[dbo].[mdl_context] on mdl_role_assignments.contextid =
join [Moodle].[dbo].mdl_course on mdl_context.instanceid =
join [Moodle].[dbo].mdl_user on mdl_role_assignments.userid =
join [Moodle].[dbo].mdl_course_categories on mdl_course.category =
where mdl_context.contextlevel = '50' and roleid = '3' or roleid = '4' order by Category asc

C'est ca!

Understanding the Moodle database 2

Following on from my previous: here I am counting the number of students enrolled in each course.

Nice and simple this one :)

COUNT (userid) as 'Students'
, mdl_context.instanceid as 'CourseID'
, mdl_course.fullname as 'CourseName'
from [moodle].[dbo].[mdl_role_assignments]
join [moodle].[dbo].[mdl_context] on mdl_role_assignments.contextid =
join [Moodle].[dbo].mdl_course on mdl_context.instanceid =
join [Moodle].[dbo].mdl_user on mdl_role_assignments.userid =
where mdl_context.contextlevel = '50' and roleid = '5'
group by mdl_context.instanceid, mdl_course.fullname
order by CourseName asc

No new tables here, just a slightly different way of extracting the data.

We're counting the number of students in each course, which requires the other columns to be grouped (count is an aggregate function, you have to describe how you want the other columns to be arranged). Note - you have to use the actual column name in the Group by, you can't get away with the renamed value. I learned that the hard way :)

roleid for students is 5 - although forum recommendation is that you should use the short name rather than the id value as this is a customisable setting.

And now, I'm going to see if I can combine this query with the previous one and get one big table of results :)


So, a little further inspection of the returned results demonstrated that my query only returns those users enrolled directly onto the courses. The users who are enrolled via a meta link are not counted. I haven't tracked down the correct table for the meta linking yet and have actually opened a Moodle forum query about it because I am *stumped*.

I have a looming suspicion that cohort enrolment may also be wrongly counted, but I'm having trouble finding a Cohorts table with up to date information. There should be two cohorts for each ALevel we deliver and myriad others for the short courses/ complimentary courses available. Unfortunately, there are only 5 cohorts  in the helpfully named mdl_cohorts table, so I don't think that's correct.

*sigh* going crosseyed. Ideally I'd break at this point and think about something else for ten minutes, but all of my typical resources have a tenuous link to what I'm doing here and I can't stop myself consciously reflecting on it.

Understanding the Moodle database

I am trying to produce a report that tells us for every course on our Moodle site:

Name of course
Name of unit
Names of teachers on unit
Number of resources/ activities in each unit
Range of resources/ activities in each unit
Number of students enrolled on unit
Number of active students on unit
Number of hits on unit between September 3rd 2012 and June 22nd 2013
Number of hits on each resource/ activity between September 3rd 2012 and June 22nd 2013

Now, the Moodle database structure is not clear at a casual glance so writing up the queries is an interesting challenge and (when I come back to this next year) I will inevitable forget the reasons behind the structure of my queries so, query by query, here's a quick wherefore;

, mdl_context.instanceid as 'CourseID'
, mdl_course.fullname as 'CourseName'
, as 'email'
from [moodle].[dbo].[mdl_role_assignments]
join [moodle].[dbo].[mdl_context] on mdl_role_assignments.contextid =
join [Moodle].[dbo].mdl_course on mdl_context.instanceid =
join [Moodle].[dbo].mdl_user on mdl_role_assignments.userid =
where mdl_context.contextlevel = '50' and roleid = '3' or roleid = '4' order by CourseName asc

Teacher Names on Course.

mdl_role lists the different roles and assigns each an id. RoleId 3 & 4 are Teacher and non-editing teacher respectively.
mdl_role_assignments contains information about each role assigned to a specific user in each context (system, category, course, forum, etc). It contains the column roleid referred to in this query, context and userid.
mdl_contextlevel contains the different context levels. There is no description here of what the context levels are but it is explained on this page (not got time to figure out how to link to the exact response, but it's there!)
mdl_context gives the id of each item with enrolment (I'm pretty sure of this, but could be wrong), the contextlevel related to it, the instanceid (course ID) related to it and the path and depth (path = /system/category(^nth)/course)

Now for a lunch break and to see how much sense this makes on my return.