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

|
| By Webner

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;
}

Examples:
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
   		 $dbman->create_table($table);
   	 }
      	 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>’);
}

One comment

Leave a Reply

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