Building a course programatically (2.3+); db structure questions

Building a course programatically (2.3+); db structure questions

by tim st.clair -
Number of replies: 7
Picture of Plugin developers

I'm trying to automate the process of building courses from an external system, by inserting records into the moodle database. My courses are fairly standard - they are topic based. My external system holds the names of the courses, the names of all the topics, and details about the activities for each topic. Each topic has a fairly standard block of html as its description, and has a set of activities that require each previous activity to be completed before the next one is made available. Building this through the interface tools is a tiresome process. The activities are like "Label, Page, Resource, Quiz" or "Label, Resource, Label, Resource, Quiz" or "Label, Resource, Assignment".

My current method is that I build a template course with more topics than I will need, manually set up all the activities for each topic, then manually adjust the conditions for each activity to require completion of its previous. When I need a new course, I clone (import from) the course from my template, then go through and delete the excess topics, which means deleting their activities too. Then going back through each page anyway and changing the text to match the topic text.

I'd much prefer to be able to either create the data directly in the database, or generate and run some SQL that builds my course (as there appears to be no import-from-csv or that type of plugin around).

I've studied a few ERD's for the database, and also fiddled around with blank databases watching what goes where when I add a course the way I want to structure it, and I'm finding some parts a little tricky to figure out. Finding and reading the PHP for a given tool doesn't always reveal how the data hangs together either. I've been finding /admin/tool/generator/locallib.php quite handy, but it's not the whole story for me.

here's what I know:

mdl_course_sections holds the topics. "section" indicates the order, and "sequence" indicates the id's of the mdl_course_modules records.

mdl_course_modules lists the activities in the course. "module" represents the mdl_module id. "instance" represents (I think) the row id for the particular type of table matching the "module" id. I'm not sure what "section" represents - it might be a backreference to the mdl_course_sections table's "id" column. Any one know?

mdl_module stores the type of activity. It seems that this matches loosely to a table named "mdl_" plus the value in the "name" column. So for type 13 (label) there would be a table named "mdl_label".

mdl_resources has a "displayoptions" column that stores some data that looks like this: a:2:{s:12:"printheading";i:0;s:10:"printintro";i:1;} - not really sure what any of it means but it's obviously structured.

I'd tried to put in some records in. For my blank course, I've put in some course_section records, matching them to course_module records and recording their "sequence" field. In the course_modules I've matched the "module" to the "modules" table for the type of activity I want, and added labels, resources and other rows for the matching tables (recording the id's in the course_modules.instance column. course_modules.section I'm still not sure about - I've put these in as the id of course_sections for now, since I can't see an obvious other table for them to match to. I've set course_modules_availability so that my activites all rely on the previous item to be completed (by setting coursemoduleid = current, sourcemid = previous item, requiredcompletion = 1). 

In my course, I get to see the topics with their correct headers and descriptions. I get to see the "resource" activity for each of the topics, but don't see any other type of activity - in this case I've tried to put a label first, then a page with some custom html on it, then a resource which is a file. The data seems to indicate everything is ok. If I edit the topic and save it again, nothing appears to change in the db and the activities still do not appear. Purging the cache doesn't seem to make them appear either. So there must be a process that has to be run or a database field somewhere else that I have missed that makes these things work. Anyone have a clue about this?

Has anybody come up with an external way to build courses? The developer docs are particularly shy about talking about the structure of courses and their activities, as are most of the ERD's I've read.

Average of ratings: Useful (2)
In reply to tim st.clair

Re: Building a course programatically (2.3+); db structure questions

by Dan Poltawski -

Apologies if i'm not answering your question properly, I'm afraid its a complex question and my brain isn't quite geared up to understand it at the moment. There is some data duplication in the tables, particularly the sequence field in course_sections needs to match with the course_modules records pointing at these sections (this is not ideal).

However, one thing you don't seem to mention is the modinfo/secinfo fields in the course table. These are two caches used by Moodle so it doesn't need to generate the course page structure on every request. You can set them to null and then on the next request Moodle will populate them. If might be that this is the solution to your problem.

[Note though that I don't recommend your approach of building this stuff manually. Hopefully we will have some web services to do this in the future, but at the moment i'd regard this approach as pretty risky]

cheers! dan

Average of ratings: -
In reply to Dan Poltawski

Re: Building a course programatically (2.3+); db structure questions

by tim st.clair -
Picture of Plugin developers

Cheers for the pointers on the cache fields, I hadn't actually spotted those (SequelPro wasn't showing a horizontal scrollbar for some reason; 'tis now; I just completely missed seeing that data.) - Nulled, and everything turns up like magic! Bing, new course built and set up in under 5 minutes. Sweet!

[I'm ok with risky - I generally build courses off-production and then have them reviewed anyway. But I do find that the method of putting together courses could do with a lot of streamlining, since the steps to go through to build a course where there is conditional release is far too fiddly to be cost effective for large course builds... In most cases I have all the structure of the course I am taking online in another format, be it Word or in a HR/CMS system, etc. So I like to automate what I can. I'm considering writing myself a coursebuilder plugin of some sort, maybe with a treeview and drag-n-drop sort of interface, now I am coming to grips with how these core structures work.]

An API or webservice for building the course activity structures and setting up their completion and conditional releases would be great - but I'm not going to hold my breath for it. Cleaning up some of these oddball data structures (again, like perhaps moving sequencing out to a linking table, even though what's there is reasonably efficient) would help too. I can live with what we have though, I'm adaptable.

 

Average of ratings: -
In reply to tim st.clair

Re: Building a course programatically (2.3+); db structure questions

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

You have successfully reverse-engineered a lot. To pick up on one other point:

The mdl_resources.displayoptions column is storing PHP serialised data. http://php.net/manual/en/function.serialize.php.

The other general approach you could take, rather than writing data directly to the database, would be to write a Moodle backup file. A Moodle backup is basically a .zip file (renamed to .mbz) containing a bunch of XML files. Once again, the format is not very well documented, but it very closely matches the database structure, and is easy to reverse engineer. There are ways to automate restoring a backup too. (http://docs.moodle.org/dev/Restore_2.0_for_developers#Automatically_triggering_restore_in_code).

Of course, since you have something that works already, I am not expecting you to re-write things now. I am just pointing it out as an alternative approach that might be worth considering in future.

Average of ratings: Useful (1)
In reply to Tim Hunt

Re: Building a course programatically (2.3+); db structure questions

by Rosario Carcò -

Wow... my approach, when I wrote my uploadusersandcourses.php and its silent version for batch/cron jobs, was to take what there existed, it was uploaduser.php and extend it with what I missed or wanted the code to do, again taking it from the built in GUI-functions. eg. I wanted to create a course using a csv-file, so I studied the built in Moodle code to create a new course and hence all its function/api calls. Then I wanted my code to be able to create a new course using an existing course as template course, so I looked up what RESTORE course in the GUI did and learned to use the restore-api-functions to simply restore an existing course in to a new one. Now it would really be fancy to extend my uploadusersandcourses-template-csv-file to contain also Sections, Activities, Resources, etc. as Tim says to be contained already in the mbz-files, and then look up the code/api used to create them.

I can not say if creating a mbz file would be faster than extending my code and which of those approaches would be smarter. But in my case you have to produce BOTH: the data in the csv-file AND the code, whilst using mbz files you would only have to bother about putting the data in a correct format and then upload/restore that course with already built in functionality.

Rosario

Average of ratings: -
In reply to Tim Hunt

Re: Building a course programatically (2.3+); db structure questions

by Rosario Carcò -

>>You have successfully reverse-engineered a lot

Should this not be copy/pasted to a Moodle-Doc? Or is it already there?

Average of ratings: -
In reply to Tim Hunt

Re: Building a course programatically (2.3+); db structure questions

by David Hempy -

Just to chime in...I am about to do exactly that.  We have a fairly small number of courses in a proprietary LMS, but the content in the courses is very large and rich. Manually copying the content would take perhaps a month per course.

Our strategy is to create .mbz files from our database, then import into Moodle. We're OK with some manual touch-up after the fact if needed, but I want to get 95% there through automation.

We've created a tiny model course in Moodle that has all the features we want to replicate (topics, quizzes, labels, pages and URL's, primarily) and backed that course up. I'm currently picking apart that .mbz file to distill a template and understand what I need to programatically inject into that template for each course. 

Our partner has suggested migrating all the questions first, then migrate the courses with quizzes, referencing the question bank categories.  I'm leaning toward putting everything into the .mbz and bring it over in one shot.  We'll see...

I'm currently reviewing documentation and other people's approaches.  So far, this thread is the only thing close to what I'm doing.  As I find more resources, I'll add links to this thread and update wiki docs as appropriate.  Any leads are very welcome!

-dave

 

 

 

Average of ratings: -