Upgrading database schema using upgrade.php

If you need to upgrade your database schema for a custom plugin, e.g. adding a new table or changing the properties of an existing table, you should do it through db/upgrade.php per the suggestions in the MoodleDocs UpgradeAPI.

This isn’t a bad idea to do even when iterating through development as it isn’t a lot of work to keep things synchronised through the XMLDB editor and you won’t miss something later.

These are the basic steps involved:

  • Use the Moodle XMLDB editor to adjust your schema: Site adminstration > Development > XMLDB editor
  • Copy the relevant changes into your install.xml and upgrade.php straight from the editor
  • Update your plugin version and test.

The Moodle XMLDB editor actually makes this very easy.

  • Open the XMLDB editor
  • Find your plugin (assuming it is already installed etc). If you haven’t managed to get this far, at least install what you have so it will show up in XMLDB editor. Note you do not need to have anything DB related defined for this to work.
    Load the DB for you plugin or if nothing is defined using Create.
  • Follow the UI to create your table, fields, keys and indexes
  • When you are ready use View PHP code to get the code to go into db/upgrade.php
  • Go all the way back to the main XMLDB editor screen (where you can see all plugin databases) and use save to save the XML to db/install.xml as well.

Moodle and Timezones

Moodle timezone information in the first instance is loaded from:

http://download.moodle.org/timezone/

This returns a text file which is stored in the lib directory as timezone.txt

The data is stored in the table mdl_timezone (adjust to suit your db prefix).

Assign site admins through the database

The following steps can be used to add your user as a siteadmin in the database if required.

Step 1: Find your moodle user ID

select id from mdl_user where username = '{username}';

Step 2: Get the list of current siteadmin IDs

select value from mdl_config where name = 'siteadmins';

This gives you a list of Moodle user ids in a list that are siteadmins. What you want to do is append your Moodle user ID to the end of the list (assuming it isn’t there already!).

Step 3: Add your moodle user id to the list

You can do this manually, simple add ‘,{your_moodle_userid}’ to the text or you can use a query like this:

select concat(value, ',', (select id from mdl_user where username = '{username}'))
from mdl_config
where name = 'siteadmins';

Step 4: Update the siteadmin list

The following SQL does the deed of updating the list of siteadmins so be very careful. Ues the results from step 3 to perform this update. If anything goes wrong use the results from step 2 to revert the changes.

update mdl_config
set value = '{list_of_siteadmins_with_your_moodle_userid}'
where name = 'siteadmins';