compare_db.php 8.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265
  1. <?php // $Id: compare_db.php 22577 2009-08-03 04:31:24Z yannoo $
  2. /* For licensing terms, see /dokeos_license.txt */
  3. /**
  4. ==============================================================================
  5. * This script allows you to know which tables have been modified
  6. * between two versions of Dokeos databases.
  7. *
  8. * @package dokeos.install
  9. ==============================================================================
  10. */
  11. /**
  12. * Database configuration
  13. * INSTRUCTIONS
  14. * ============
  15. * Change these parameters to compare between an old and a new database install.
  16. * You will need to create a course called 'COURSE' on each side to be able to compare the
  17. * courses databases.
  18. * If you have given fancy names to your databases, you will need to modify these names
  19. * in the two $bases_* variables definitions below.
  20. * Also, make sure about the prefix possibly used in front of the normal prefix for courses
  21. * databases (i.e. 'zPrefix_course' contains 'z' as additional prefix).
  22. */
  23. $sql_server_new='localhost';
  24. $sql_user_new='root';
  25. $sql_pass_new='';
  26. $prefix_new = 'dokeos180_';
  27. $bases_new=array($prefix_new.'dokeos_main',$prefix_new.'dokeos_stats',$prefix_new.'dokeos_user','z'.$prefix_new.'COURSE',$prefix_new.'dokeos_scorm');
  28. $db_new = mysql_connect($sql_server_new,$sql_user_new,$sql_pass_new) or die(mysql_error());
  29. // The Dokeos system has not been designed to use special SQL modes that were introduced since MySQL 5
  30. @mysql_query("set session sql_mode='';", $db_new);
  31. $sql_server_old='localhost';
  32. $sql_user_old='root';
  33. $sql_pass_old='';
  34. $prefix_old = 'dokeos160_';
  35. $bases_old=array($prefix_old.'dokeos_main',$prefix_old.'dokeos_stats',$prefix_old.'dokeos_user',$prefix_old.'COURSE',$prefix_old.'dokeos_scorm');
  36. $db_old = mysql_connect($sql_server_old,$sql_user_old,$sql_pass_old) or die(mysql_error());
  37. // The Dokeos system has not been designed to use special SQL modes that were introduced since MySQL 5
  38. @mysql_query("set session sql_mode='';", $db_old);
  39. $field_details = array(0=>'Field',1=>'Type',2=>'Null',3=>'Key',4=>'Default',5=>'Extra');
  40. /********************************/
  41. $all_db_changes = array();
  42. echo "<h1>Databases structure comparison script</h1>";
  43. //iterate through databases given above (checking from the 'new' database side)
  44. foreach($bases_new as $num_base=>$base)
  45. {
  46. //init tables lists for this database
  47. $modif_tables=array();
  48. $tables_db_new=array();
  49. $tables_db_old=array();
  50. $dump=array();
  51. //display current processed database
  52. echo "<h2>Now analysing differences between databases <em>$base</em> and <em>".$bases_old[$num_base]."</em></h2>";
  53. //get a list of tables for this database
  54. $query_new="SHOW TABLES FROM ".$bases_new[$num_base];
  55. $result_new=mysql_query($query_new,$db_new);
  56. if($result_new) //if there are tables in this database
  57. {
  58. $i=0;
  59. //as there are tables, process them one by one
  60. while($row_new=mysql_fetch_row($result_new))
  61. {
  62. $dump[$i]['table_name']=$row_new[0];
  63. $dump[$i]['fields']=array();
  64. $query_old="SHOW FIELDS FROM ".$bases_new[$num_base].".".$row_new[0];
  65. $result_old=mysql_query($query_old,$db_old) or die(mysql_error());
  66. $j=0;
  67. //get the fields details (numbered fields)
  68. while($row_old=mysql_fetch_row($result_old))
  69. {
  70. $dump[$i]['fields'][$j][0]=$row_old[0];
  71. $dump[$i]['fields'][$j][1]=$row_old[1];
  72. $dump[$i]['fields'][$j][2]=$row_old[2];
  73. $dump[$i]['fields'][$j][3]=$row_old[3];
  74. $dump[$i]['fields'][$j][4]=$row_old[4];
  75. $dump[$i]['fields'][$j][5]=$row_old[5];
  76. //get the field name in one special element of this array
  77. $dump[$i]['field_names'][$row_old[0]]=$j;
  78. $j++;
  79. }
  80. $i++;
  81. }
  82. foreach($dump as $table)
  83. {
  84. $query="SHOW FIELDS FROM ".$bases_old[$num_base].".".$table['table_name'];
  85. $result=mysql_query($query,$db_old);
  86. if(!$result)
  87. {
  88. $modif_tables[]='**'.$table['table_name'].'**';
  89. }
  90. else
  91. {
  92. $i=0;
  93. //check for removed, new or modified fields
  94. $fields_old = array();
  95. $fields_new = array();
  96. //list the new fields in a enumeration array
  97. foreach($table['field_names'] as $dummy_key=>$dummy_field){
  98. $fields_new[] = $dummy_key;
  99. }
  100. //list the old fields in an enumeration array and check if their corresponding
  101. //field in the new table is different (if any)
  102. $modif_fields = array();
  103. while($row_old = mysql_fetch_row($result)){
  104. $fields_old[] = $row_old[0];
  105. $modif_field = '';
  106. if(isset($table['fields'][$table['field_names'][$row_old[0]]])){
  107. $field_infos=$table['fields'][$table['field_names'][$row_old[0]]];
  108. foreach($row_old as $key=>$enreg)
  109. {
  110. //if the old field information of this kind doesn't match the new, record it
  111. if($row_old[$key] != $field_infos[$key])
  112. {
  113. $modif_field .='~+~'.$field_details[$key].'~+~,';
  114. break;
  115. }
  116. }
  117. //only record the whole stuff if the string is not empty
  118. if(strlen($modif_field)>0){
  119. $modif_fields[$row_old[0]] .= substr($modif_field,0,-1);
  120. }
  121. }
  122. }
  123. $new_fields = array_diff($fields_new,$fields_old);
  124. foreach($new_fields as $dummy=>$val){
  125. $new_fields[$dummy] = '++'.$val.'++';
  126. }
  127. $old_fields = array_diff($fields_old,$fields_new);
  128. foreach($old_fields as $dummy=>$val){
  129. $old_fields[$dummy] = '--'.$val.'--';
  130. }
  131. if(count($old_fields)>0 or count($modif_fields)>0 or count($new_fields)>0 ){
  132. $modif_tables[]=array(
  133. 'table'=>$table['table_name'],
  134. 'old_fields'=>$old_fields,
  135. 'changed_fields'=>$modif_fields,
  136. 'new_fields'=>$new_fields,
  137. );
  138. }
  139. }
  140. $tables_db_new[]=$table['table_name'];
  141. }
  142. $query="SHOW TABLES FROM ".$bases_old[$num_base];
  143. $result=mysql_query($query,$db_old) or die(mysql_error());
  144. while($row=mysql_fetch_row($result))
  145. {
  146. $tables_db_old[]=$row[0];
  147. }
  148. $diff=array_diff($tables_db_old,$tables_db_new);
  149. foreach($diff as $enreg)
  150. {
  151. $modif_tables[]='---'.$enreg.'---';
  152. }
  153. //$modif_tables=array_unique($modif_tables); //deprecated with the structure complexification
  154. }else{ //this database was removed in the new version
  155. $query="SHOW TABLES FROM ".$bases_old[$num_base];
  156. $result=mysql_query($query,$db_old) or die(mysql_error());
  157. while($row=mysql_fetch_row($result))
  158. {
  159. $tables_db_old[]=$row[0];
  160. }
  161. $diff=array_diff($tables_db_old,$tables_db_new);
  162. foreach($diff as $enreg)
  163. {
  164. $modif_tables[]='---'.$enreg.'---';
  165. }
  166. $modif_tables=array_unique($modif_tables);
  167. echo "<h3>This database has been removed!</h3>";
  168. }
  169. echo "<h3>Differences between each table</h3>" .
  170. "- fields display under each table's name, <br>" .
  171. "- new tables are surrounded by '**', <br/>" .
  172. "- removed tables are surrounded by '---',<br/>" .
  173. "- new fields are surrounded by '++',<br/>" .
  174. "- removed fields are surrounded by '--',<br/>" .
  175. "- modified fields are surrounded by '~+~',<br/>";
  176. echo '<pre>'.print_r($modif_tables,true).'</pre>';
  177. $all_db_changes[$base] = $modif_tables;
  178. }
  179. mysql_close($db_new);
  180. mysql_close($db_old);
  181. echo "<h2>Generating SQL</h2>";
  182. //going through all databases changes
  183. foreach($all_db_changes as $base => $changes){
  184. echo "<h3>SQL for DB $base</h3>";
  185. foreach($changes as $table){
  186. if(is_array($table)){
  187. //we have a field-level difference
  188. $mytable = $table['table'];
  189. $myold = $table['old_fields'];
  190. $mychanged = $table['changed_fields'];
  191. $mynew = $table['new_fields'];
  192. foreach($myold as $myname){
  193. //column lost, display DROP command
  194. $myname = str_replace('--','',$myname);
  195. echo "ALTER TABLE ".$mytable." DROP ".$myname."<br/>";
  196. }
  197. foreach($mychanged as $myname=>$myprop){
  198. //field changed, display SET command
  199. $myprops = split(',',$myprop);
  200. $myprops_string = '';
  201. foreach($myprops as $myprop){
  202. $myprop = str_replace('~+~','',$myprop);
  203. $myprops_string .= $myprop." ";
  204. }
  205. echo "ALTER TABLE ".$mytable." CHANGE $myname $myname $myprops_string<br/>";
  206. }
  207. foreach($mynew as $myname){
  208. //column created, display ADD command
  209. $myname = str_replace('++','',$myname);
  210. echo "ALTER TABLE ".$mytable." ADD $myname...<br/>";
  211. }
  212. }else{
  213. //we have a table-level difference
  214. $open_tag = substr($table,0,2);
  215. switch($open_tag){
  216. case '**':
  217. //new table, display CREATE TABLE command
  218. $table = str_replace('**','',$table);
  219. echo "CREATE TABLE ".$table."();<br/>";
  220. break;
  221. case '--':
  222. //dropped table, display DROP TABLE command
  223. $table = str_replace('---','',$table);
  224. echo "DROP TABLE ".$table."();<br/>";
  225. break;
  226. default:
  227. echo "Unknown table problem: ".$table."<br/>";
  228. break;
  229. }
  230. }
  231. }
  232. }
  233. ?>