Project

General

Profile

Actions

Single database to multiple database script [Warning: Very old article, 1.9 only supports single-database now]

This page explains how to migrate Chamilo from a single database to a multiple databases setup.

This version of a PHP migration script intends to do everything in one big step, all managed by the script.
The only thing you have to do is ensure file permissions allow you to overwrite the main/inc/conf/configuration.php script, to write anything in /tmp and to create databases starting with a given prefix.

A prefix is mandatory in this script and you should choose something that does not share the same root as databases you already have. For example, if you pick a prefix of "chamilo", make sure you don't have any database currently using the same prefix.

The prefix will automatically be added a trailing "_", so if you only want one underscore, don't mention it in the prefix.

Here is the script:

 <?php #!/usr/bin/php5
 /**
  * This script converts any single database, 1.8.* version of Chamilo to a multiple database system.
  * It needs complete access to: (1) the databases it is going to use and 
  * (2) the main/inc/conf/configuration.php file, to be able to change the settings in there.
  * This script should be launched from your Chamilo root.
  * It takes one mandatory parameter, whether on the command line or in web, which is the prefix
  * to be used for all the new databases.
  */
 /**
  * Generic functions
  */
 /**
  * This function gets the SQL backup for a table structure, given its database and table names
  * @param    string    Original database name
  * @param    string    Original table name
  * @param    string    Destination database name
  * @param    string    Destination table name
  */
 function copy_table($dbname,$tbname,$dbname2,$tbname2)
 {
     global $_configuration,$eol,$m;
     $str_exec = 'mysqldump -h '.$_configuration['db_host'].' -u '.$_configuration['db_user'].' --password='.$_configuration['db_password'].' '.$dbname.' '.$tbname.' --result-file=/tmp/res.sql';
     //echo $str_exec.$eol[$m];
     exec($str_exec);
     $fh = fopen('/tmp/res.sql','r');
     $fh2 = fopen('/tmp/res2.sql','w');
     while($line = fgets($fh))
     {
         $line = str_replace($tbname,$tbname2,$line);
         fputs($fh2,$line);
     }
     fclose($fh);
     $str_exec = 'mysql -h '.$_configuration['db_host'].' -u '.$_configuration['db_user'].' --password='.$_configuration['db_password'].' '.$dbname2.' < /tmp/res2.sql';
     //echo $str_exec.$eol[$m];
     exec($str_exec);
     exec('rm /tmp/res.sql');
     exec('rm /tmp/res2.sql');
     return true;
 } 
 /**
  * Identifies where a table comes from and puts it in the right array
  */
 function identify_table($rowt)
 {
        global $l,$crsprefix,$olddbprefix,$dbp,$ltfcourses,$ltmain,$ltfmain,$ltstats,$ltfstats,$ltusers,$ltfusers;
     //echo "Found table: ".$rowt[0].$eol[$m];
     if(substr($rowt[0],0,$l)==$crsprefix)
     {
         //echo "  This table has been recognized as a course table".$eol[$m];
         $first = substr($olddbprefix,0,1);
         if(!empty($dbp) && $first >= 0 && $first <=9)
         {
             $olddbprefix = 'z'.$olddbprefix;
         }
         $lp = strlen($olddbprefix);
          //take the last part of the string, removing the characters from the course table prefix
         // and the possible database prefix
         $table_name = substr($rowt[0],($l+$lp));
         //echo "The course table name, without prefixes, is: ".$table_name.$eol[$m];
         $ltfcourses[] = $rowt[0];
     }
     else
     {
         //echo "  This table is not a course table".$eol[$m];
         $table_name = $rowt[0];
         if(in_array($table_name,$ltmain))
         {
             //echo "This is a main table".$eol[$m];
             $ltfmain[] = $rowt[0];
         }
         if(in_array($table_name,$ltstats))
         {
             //echo "This is a stat table".$eol[$m];
             $ltfstats[] = $rowt[0];
         }
         if(in_array($table_name,$ltusers))
         {
             //echo "This is a user table".$eol[$m];
             $ltfusers[] = $rowt[0];
         }
     }
 }
 /**
  * Initialising the environment
  */
 require('main/inc/global.inc.php');
 $m = '';
 $dbprefix = '';
 $eol = array('web'=>"<br />\n",'cli'=>"\n");
 if(!empty($_GET['prefix']))
 {
     $dbprefix = mysql_real_escape_string($_GET['prefix']);
     $m = 'web';
     echo "<html><head><title>Chamilo singledb to multidb migration script</title></head><body>";
 }
 else
 {
     $dbprefix = $argv[1];
     $m = 'cli';
 }
 if(substr($_configuration['system_version'],2,1)<8 or substr($_configuration['system_version'],4,1)<7)
 {
     echo "This migration script has been written for Chamilo versions 1.8.7+. Your system could not be recognized as a 1.8.7+ version. The version detection system found the following version: '".$_configuration['system_version']."'. Please check that you are running this script from the root of your Chamilo installation and that your system is using Chamilo at a version of 1.8.7 or higher.".$eol[$m].$eol[$m];
     echo "</body></html>";
     die();
 }
 if(empty($dbprefix))
 {
     echo "This script needs one parameter: the new database prefix to be used for the new databases structure.".$eol[$m];
     if($m=='web')
     {
         echo "Please enter the database prefix in the URL, trailing it with '?prefix=[your prefix]'".$eol[$m];
     }
     else
     {
         echo "Please enter the database prefix on the command line, after the command".$eol[$m];
     }
     echo "</body></html>";
     die();
 }
 else
 {
     echo "You have selected a prefix of '$dbprefix'. This prefix will be used by this migration script.".$eol[$m].$eol[$m];
 }
 /**
  * Start the database and permissions checks
  */
 $confpath = api_get_path(INCLUDE_PATH).'conf/configuration.php';
 if(!is_writeable($confpath))
 {
     echo "It appears that your configuration file ($confpath) is not writeable by this script. Please ensure that the permissions on this file are set correctly to allow rewriting by this script.".$eol[$m];
 }
 else
 {
     echo "Configuration file is writeable. Good.".$eol[$m];
 }
 $sql = "CREATE DATABASE {$dbprefix}TEST";
 $res = mysql_query($sql);
 if($res === false)
 {
     echo "It appears that the database prefix you have given cannot be used safely by this script. The error returned was: ".mysql_error().$eol[$m]."Please check that you have the right to use any database prefixed by '$dbprefix'. This generally means you have permissions to create any database of the likes of '$dbprefix%'. This should have been defined somewhere in your MySQL configuration. Either by using phpMyAdmin and giving the permissions over all databases like '$dbprefix%' or by issuing a GRANT ALL query for the current user on the '$dbprefix%' databases. When done, please try to execute this script again.".$eol[$m];
     echo "</body></html>";
     die();
 }
 else
 {
     $sql = "DROP DATABASE {$dbprefix}TEST";
     $res = mysql_query($sql);
     if($res === false)
     {
         echo "The database {$dbprefix}TEST could be created but not removed. The error returned was: ".mysql_error().$eol[$m]."Please ensure that you have both the permission to create *and* delete databases using the prefix {$dbname}%".$eol[$m];
     echo "</body></html>";
         die();
     }
     else
     {
         echo "The database {$dbprefix}TEST could be created and removed, which we assume to be proof enough that you are authorized to create and delete all databases using the prefix {$dbprefix}. Good.".$eol[$m];
     }
 }
 $glue = $_configuration['db_glue'];
 $olddbprefix = $_configuration['db_prefix'];
 $crsprefix = $_configuration['table_prefix'];
 $track = $_configuration['tracking_enabled'];
 $singledb = $_configuration['single_database'];
 $olddbmain = Database::get_main_database();
 $olddbstat = Database::get_statistic_database();
 $olddbuser = Database::get_user_personal_database();
 if($singledb !== true)
 {
     echo "The settings of your configuration file say that you are not currently using the multiple database mode. Please make sure this setting reflects your current setup. If you are currently using the multiple database mode, you do not need this script.".$eol[$m];
     echo "</body></html>";
     die();
 }
 echo $eol[$m];
 /**
  * All checks seem to have succeeded. Now start with the real migration process.
  */
 // First, get the current database and make sure everything is in it
 $l = strlen($crsprefix);
 // TODO Recovering the table names by database type should be done by reading the scripts in /install if they are available
 $ltmain = array('admin','class','class_user','course','course_category','course_rel_class','course_rel_user','gradebook_category','gradebook_evaluation','gradebook_link','gradebook_result','gradebook_score_display','language','openid_association','php_session','session','session_rel_course','session_rel_course_rel_user','session_rel_user','settings_current','settings_options','shared_survey','shared_survey_question','shared_survey_question_option','sys_announcement','templates','user');
 $ltstats = array('track_c_browsers','track_c_countries','track_c_os','track_c_providers','track_c_referers','track_e_access','track_e_attempt','track_e_course_access','track_e_default','track_e_downloads','track_e_exercices','track_e_hotpotatoes','track_e_hotspot','track_e_lastaccess','track_e_links','track_e_login','track_e_online','track_e_open','track_e_uploads');
 $ltusers = array('personal_agenda','user_course_category');
 $ltfmain = array();
 $ltfstats = array();
 $ltfusers = array();
 $ltfcourses = array();
 mysql_select_db($olddbmain);
 $sqlt = "SHOW TABLES";
 $rest = api_sql_query($sqlt);
 while($rowt = Database::fetch_array($rest))
 {
   identify_table($rowt);
 } 
 mysql_select_db($olddbstat);
 $sqlt = "SHOW TABLES";
 $rest = api_sql_query($sqlt);
 while($rowt = Database::fetch_array($rest))
 {
   identify_table($rowt);
 } 
 mysql_select_db($olddbuser);
 $sqlt = "SHOW TABLES";
 $rest = api_sql_query($sqlt);
 while($rowt = Database::fetch_array($rest))
 {
   identify_table($rowt);
 }
 mysql_select_db($olddbmain);
 sort($ltfcourses);
 // Migrate the main, stats and user tables
 $dbmain = $dbprefix.'_chamilo_main';
 $dbstats= $dbprefix.'_chamilo_stats';
 $dbuser = $dbprefix.'_chamilo_user';
 $sqlic = "CREATE DATABASE ".$dbmain;
 echo "$sqlic".$eol[$m];
 $resic = api_sql_query($sqlic);
 foreach($ltfmain as $tname)
 {
     $tcreate = copy_table($olddbmain,$tname,$dbmain,$tname);
 }
 $sqlic = "CREATE DATABASE ".$dbstats;
 echo "$sqlic".$eol[$m];
 $resic = api_sql_query($sqlic);
 foreach($ltfstats as $tname)
 {
     $tcreate = copy_table($olddbstat,$tname,$dbstats,$tname);
 }
 $sqlic = "CREATE DATABASE ".$dbuser;
 echo "$sqlic".$eol[$m];
 $resic = api_sql_query($sqlic);
 foreach($ltfusers as $tname)
 {
     $tcreate = copy_table($olddbuser,$tname,$dbuser,$tname);
 }
 echo $eol[$m];
 // Now migrate each course table, one by one
 $tcourse = Database::get_main_table(TABLE_MAIN_COURSE);
 $sqlc = "SELECT code, db_name FROM $tcourse ORDER BY db_name";
 $resc = api_sql_query($sqlc);
 while($rowc = Database::fetch_array($resc))
 {
     echo "Found course: ".$rowc['db_name'].', so every table starting with '.$crsprefix.$rowc['db_name'].$glue.' should be a table of this course'.$eol[$m];
     $pref = $crsprefix.$rowc['db_name'].$glue;
     $l = strlen($pref);
     $created = 0;
     foreach($ltfcourses as $ctable)
     {
         if(substr($ctable,0,$l)==$pref)
         {
             $ltname = $dbprefix."_".str_replace($olddbprefix,'',$rowc['db_name']);
             if(!$created)
             {
                 $sqlic = "CREATE DATABASE ".$ltname;
                 echo $sqlic.$eol[$m];
                 $resic = api_sql_query($sqlic);
                 $created = 1;
             }
             //echo "Table $ctable is part of course ".$rowc['db_name'].$eol[$m];
             //echo "Moving table ".Database::get_main_database().".".$ctable." to ".$dbprefix.'_'.$rowc['db_name'].'.'.substr($ctable,$l).$eol[$m];
             $tcreate = copy_table($olddbmain,$ctable,$ltname,substr($ctable,$l));
             $sqluc = "UPDATE $dbmain.course SET db_name = '$ltname' WHERE code='{$rowc['code']}'";
             api_sql_query($sqluc,__FILE__,__LINE__);
         }
     }        
 }
 // Now update the configuration file to record the change
 $conffile = file_get_contents($confpath);
 file_put_contents('/tmp/configuration.bak.php',$conffile);
 $conffile = preg_replace('/(\$_configuration\[\'single_database\'\]\s*=\s*)(true)/',"$1false",$conffile);
 $conffile = preg_replace('/(\$_configuration\[\'table_prefix\'\]\s*=\s*)(\''.$crsprefix.'\')/',"$1''",$conffile);
 $conffile = preg_replace('/(\$_configuration\[\'db_prefix\'\]\s*=\s*)(\''.$olddbprefix.'\')/',"$1'{$dbprefix}_'",$conffile);
 $conffile = preg_replace('/(\$_configuration\[\'db_glue\'\]\s*=\s*)(\''.$glue.'\')/',"$1'`.`'",$conffile);
 $conffile = preg_replace('/(\$_configuration\[\'main_database\'\]\s*=\s*)(\''.$olddbmain.'\')/',"$1'$dbmain'",$conffile);
 $conffile = preg_replace('/(\$_configuration\[\'statistics_database\'\]\s*=\s*)(\''.$olddbstat.'\')/',"$1'$dbstats'",$conffile);
 $conffile = preg_replace('/(\$_configuration\[\'user_personal_database\'\]\s*=\s*)(\''.$olddbuser.'\')/',"$1'$dbuser'",$conffile);
 //echo $conffile;
 $resw = file_put_contents($confpath,$conffile);
 if($m == 'web'){echo '</body></html>';}else{echo "\n";}

Updated by Jérôme Warnier almost 6 years ago · 5 revisions