Looking for documentation on database schema

Looking for documentation on database schema

by Lewis Kapell -
Number of replies: 4

I have been hired to manage a Moodle 2.0 site and upgrade it to 3.0.  There is a customization which was created to allow courses/students to be managed from an external site, this was implemented by means of a database package in the underlying Oracle database.   This package has functions such as:

create_course - inserts records into M_COURSE, M_CONTEXT, M_BLOCK_INSTANCES, M_COURSE_SECTIONS and updates records in M_COURSE_CATEGORIES, M_CONTEXT

create_user - inserts records in M_USER

assign_role - inserts records into M_ENROL, M_ROLE_ASSIGNMENTS, M_COURSE_DISPLAY, M_USER_ENROLMENTS

This is just a sample, there about 15 functions dealing with various aspects of courses, course sections, users and roles.

I need to find out what changes will be necessary in this package to make it work with Moodle 3.0

Can anyone direct me to documentation on what changes have been made in the database schema since 2.0?  Thank you.

Average of ratings: -
In reply to Lewis Kapell

Re: Looking for documentation on database schema

by Mark Johnson -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I don't think there's a single piece of documentation that provides what you're looking for, but a good start would be to look at the release notes for each release that you'll be upgrading through.  These tables are fairly central to Moodle's core functionality so I'd be surprised if they've changed much.  You could also do a clean Moodle 3.0 install and compare the databases to see if anything's changed in those tables, or compare the install.xml files.

One thing that it could be useful to bear in mind is that Moodle has Web Service functions as a supported means for external systems to make changes like this, which should provide a stable API regardless of underlying database changes.

Average of ratings: -
In reply to Lewis Kapell

Re: Looking for documentation on database schema

by Luis de Vasconcelos -

I would try this:

Install a copy of Moodle 2.0 on my localhost computer using a MySQL database. Do a mysqldump of the 2.0 database structure.

Install a copy of Moodle 3.0 on my localhost computer again using a MySQL database and do a mysqldump of the 3.0 database structure.

Diff the two mysqldump dump files to get the database changes from 2.0 to 3.0. Upgrading from Moodle 2.0 to Moodle 3.0 is a big jump and there will be a lot of changes in the database structure.

Of course, if you know of a mysqldump equivalent function in Oracle then you could do it all in Oracle.

Lastly, can you explain what "create_user - inserts records in M_USER" does? Does it manually do INSERT statements in the Moodle USER table? You really shouldn't create users that way. Creating user profiles in Moodle involves a lot more than just inserting rows in the mdl_user table.

Average of ratings: -
In reply to Luis de Vasconcelos

Re: Looking for documentation on database schema

by Lewis Kapell -

Thanks for the suggestions.

Does it manually do INSERT statements in the Moodle USER table?  Yes it does.  It wasn't my idea, believe me.  I just got brought in to upgrade this system.

Average of ratings: -
In reply to Lewis Kapell

Re: Looking for documentation on database schema

by Luis de Vasconcelos -

In addition to mdl_user are you inserting the correct data into mdl_user_preferences, mdl_context and mdl_user_info_data (if you have any custom profile fields)? There are probably other tables that need to be updated too - Moodle profile data is stored in more than just the mdl_user table. And you have to set contexts and stuff like that, so you see why doing INSERTS is not a good idea.

Perhaps moving away from the code that does those INSERTS on the user table should be part of the upgrade. You could convert the code that does those INSERTS into code that uses the Moodle API or a web service.

See https://moodle.org/mod/forum/discuss.php?d=335205 for a similar discussion.

Average of ratings: -