How can you modify the existing tables in Moodle/Totara?

Author - Pawandeep Kaur

How can you modify the existing tables of the installed plugin in Moodle/Totara?

When you install any plugin, install.xml is used to create all the tables required for your plugin.
A most common practice that people use to do is install the plugin again with a new database but it creates a new problem. The present modules of the plugin get removed as we uninstall the plugin. Therefore, the more appropriate way to modify the database is to use plugin/db/upgrade.php in your plugin.

This file contains the code that people need to run to upgrade from version of your plugin. The upgrade.php file should contain a single function xmldb__upgrade that looks like:

function xmldb_<pluginname>_upgrade($oldversion) {
    global $DB;
    $dbman = $DB->get_manager();
    if ($oldversion < 2018022777) {
        // Code to make database changes
    return true;

Create a new table into your plugin:-

 if ($oldversion  < 2018022777) {
       	   $table = new xmldb_table('users');
 // Define field id to be added to ‘users’ table.
   	 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);   	
    	$table->add_field(‘first_name’, XMLDB_TYPE_TEXT, null, null, null, null, null);
$table->add_field(‘last_name’, XMLDB_TYPE_TEXT, null, null, null, null, null); 	
$table->add_field(‘username’, XMLDB_TYPE_TEXT, null, null, null, null, null);  
   	 // Adding keys to table users.
   	 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));   	   	
   	 if (!$dbman->table_exists($table)) { //check if table is already existed
      	 upgrade_mod_savepoint(true, 2018022777, ‘<pluginname>’);   	 

Add a new Column:-

if ($oldversion <2018022778) {
   	   	 $table = new xmldb_table('users');
   	             $field = new xmldb_field('email’, XMLDB_TYPE_TEXT, null, null, null, null, null);
   		 // Conditionally launch add field email.
   		 if (!$dbman->field_exists($table, $field)) {
   		 	$dbman->add_field($table, $field);
   	   	 upgrade_mod_savepoint(true, 2018022778, '<pluginname>’);

Drop Column:-

if ($oldversion < 2018022779) {    
   	   	 $table = new xmldb_table('users');
   		 $field = new xmldb_field(‘username’);    
   	   	iif (!$dbman-field_exists($table, $field)) {
   			 $dbman->drop_field($table, $field);
   	   	 upgrade_mod_savepoint(true, 2018022779, '<pluginname>’);
Webner Solutions is a Software Development company focused on developing Insurance Agency Management Systems, Learning Management Systems and Salesforce apps. Contact us at for your Insurance, eLearning and Salesforce applications.

Leave a Reply

Your email address will not be published. Required fields are marked *