update-db-1.8.6.1-1.8.6.2.inc.php 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444
  1. <?php // $Id: $
  2. /* See license terms in /dokeos_license.txt */
  3. /**
  4. ==============================================================================
  5. * Update the Dokeos database from an older version
  6. * Notice : This script has to be included by index.php or update_courses.php
  7. *
  8. * @package dokeos.install
  9. * @todo
  10. * - conditional changing of tables. Currently we execute for example
  11. * ALTER TABLE `$dbNameForm`.`cours` instructions without checking wether this is necessary.
  12. * - reorganise code into functions
  13. * @todo use database library
  14. ==============================================================================
  15. */
  16. //load helper functions
  17. require_once("install_upgrade.lib.php");
  18. require_once('../inc/lib/image.lib.php');
  19. $old_file_version = '1.8.6.1';
  20. $new_file_version = '1.8.6.2';
  21. //remove memory and time limits as much as possible as this might be a long process...
  22. if(function_exists('ini_set'))
  23. {
  24. ini_set('memory_limit',-1);
  25. ini_set('max_execution_time',0);
  26. }else{
  27. error_log('Update-db script: could not change memory and time limits',0);
  28. }
  29. /*
  30. ==============================================================================
  31. MAIN CODE
  32. ==============================================================================
  33. */
  34. //check if we come from index.php or update_courses.php - otherwise display error msg
  35. if (defined('DOKEOS_INSTALL') || defined('DOKEOS_COURSE_UPDATE'))
  36. {
  37. //check if the current Dokeos install is elligible for update
  38. if (!file_exists('../inc/conf/configuration.php'))
  39. {
  40. echo '<b>'.get_lang('Error').' !</b> Dokeos '.implode('|', $updateFromVersion).' '.get_lang('HasNotBeenFound').'.<br><br>
  41. '.get_lang('PleasGoBackToStep1').'.
  42. <p><button type="submit" class="back" name="step1" value="&lt; '.get_lang('Back').'">'.get_lang('Back').'</button></p>
  43. </td></tr></table></form></body></html>';
  44. exit ();
  45. }
  46. //get_config_param() comes from install_functions.inc.php and
  47. //actually gets the param from
  48. $_configuration['db_glue'] = get_config_param('dbGlu');
  49. if ($singleDbForm)
  50. {
  51. $_configuration['table_prefix'] = get_config_param('courseTablePrefix');
  52. $_configuration['main_database'] = get_config_param('mainDbName');
  53. $_configuration['db_prefix'] = get_config_param('dbNamePrefix');
  54. }
  55. $dbScormForm = eregi_replace('[^a-z0-9_-]', '', $dbScormForm);
  56. if (!empty ($dbPrefixForm) && !ereg('^'.$dbPrefixForm, $dbScormForm))
  57. {
  58. $dbScormForm = $dbPrefixForm.$dbScormForm;
  59. }
  60. if (empty ($dbScormForm) || $dbScormForm == 'mysql' || $dbScormForm == $dbPrefixForm)
  61. {
  62. $dbScormForm = $dbPrefixForm.'scorm';
  63. }
  64. $res = @mysql_connect($dbHostForm, $dbUsernameForm, $dbPassForm);
  65. //if error on connection to the database, show error and exit
  66. if ($res === false)
  67. {
  68. //$no = mysql_errno();
  69. //$msg = mysql_error();
  70. //echo '<hr>['.$no.'] - '.$msg.'<hr>';
  71. echo get_lang('DBServerDoesntWorkOrLoginPassIsWrong').'.<br /><br />' .
  72. ' '.get_lang('PleaseCheckTheseValues').' :<br /><br />
  73. <b>'.get_lang('DBHost').'</b> : '.$dbHostForm.'<br />
  74. <b>'.get_lang('DBLogin').'</b> : '.$dbUsernameForm.'<br />
  75. <b>'.get_lang('DBPassword').'</b> : '.$dbPassForm.'<br /><br />
  76. '.get_lang('PleaseGoBackToStep').' '. (defined('DOKEOS_INSTALL') ? '3' : '1').'.
  77. <p><button type="submit" class="back" name="step'. (defined('DOKEOS_INSTALL') ? '3' : '1').'" value="&lt; '.get_lang('Back').'">'.get_lang('Back').'</button></p>
  78. </td></tr></table></form></body></html>';
  79. exit ();
  80. }
  81. // The Dokeos system has not been designed to use special SQL modes that were introduced since MySQL 5
  82. @mysql_query("set session sql_mode='';");
  83. $dblistres = mysql_list_dbs();
  84. $dblist = array();
  85. while ($row = mysql_fetch_object($dblistres)) {
  86. $dblist[] = $row->Database;
  87. }
  88. /*
  89. -----------------------------------------------------------
  90. Normal upgrade procedure:
  91. start by updating main, statistic, user databases
  92. -----------------------------------------------------------
  93. */
  94. //if this script has been included by index.php, not update_courses.php, so
  95. // that we want to change the main databases as well...
  96. $only_test = false;
  97. $log = 0;
  98. if (defined('DOKEOS_INSTALL'))
  99. {
  100. if ($singleDbForm)
  101. {
  102. $dbStatsForm = $dbNameForm;
  103. $dbScormForm = $dbNameForm;
  104. $dbUserForm = $dbNameForm;
  105. }
  106. /**
  107. * Update the databases "pre" migration
  108. */
  109. include ("../lang/english/create_course.inc.php");
  110. if ($languageForm != 'english')
  111. {
  112. //languageForm has been escaped in index.php
  113. include ("../lang/$languageForm/create_course.inc.php");
  114. }
  115. //get the main queries list (m_q_list)
  116. $m_q_list = get_sql_file_contents('migrate-db-'.$old_file_version.'-'.$new_file_version.'-pre.sql','main');
  117. if(count($m_q_list)>0)
  118. {
  119. //now use the $m_q_list
  120. /**
  121. * We connect to the right DB first to make sure we can use the queries
  122. * without a database name
  123. */
  124. if(strlen($dbNameForm)>40){
  125. error_log('Database name '.$dbNameForm.' is too long, skipping',0);
  126. }elseif(!in_array($dbNameForm,$dblist)){
  127. error_log('Database '.$dbNameForm.' was not found, skipping',0);
  128. }else{
  129. mysql_select_db($dbNameForm);
  130. foreach($m_q_list as $query){
  131. if($only_test){
  132. error_log("mysql_query($dbNameForm,$query)",0);
  133. }else{
  134. $res = mysql_query($query);
  135. if($log)
  136. {
  137. error_log("In $dbNameForm, executed: $query",0);
  138. }
  139. }
  140. }
  141. // There might now be multiple course coaches. This implies
  142. // moving the previous course coach elements from the
  143. // session_rel_course table to the session_rel_course_rel_user
  144. // table with status 2
  145. // Select all the current course coaches in sessions
  146. $sql = "SELECT id_session, course_code, id_coach
  147. FROM session_rel_course
  148. ORDER BY id_session, course_code";
  149. $res = mysql_query($sql);
  150. if ($res === false) {
  151. error_log('Could not query session course coaches table: '.mysql_error());
  152. } else {
  153. // For each coach found, add him as a course coach in the
  154. // session_rel_course_rel_user table
  155. while ($row = mysql_fetch_array($res)) {
  156. // chech if coach is a student
  157. $sql = "SELECT 1 FROM session_rel_course_rel_user
  158. WHERE id_session='{$row[id_session]}' AND course_code='{$row[course_code]}' AND id_user='{$row[id_coach]}'";
  159. $rs = mysql_query($sql);
  160. if (mysql_num_rows($rs)>0) {
  161. $sql_upd = "UPDATE session_rel_course_rel_user SET status=2
  162. WHERE id_session='{$row[id_session]}' AND course_code='{$row[course_code]}' AND id_user='{$row[id_coach]}'";
  163. } else {
  164. $sql_ins = "INSERT INTO session_rel_course_rel_user(id_session, course_code, id_user, status)
  165. VALUES ('{$row[id_session]}','{$row[course_code]}','{$row[id_coach]}',2)";
  166. }
  167. $rs_coachs = mysql_query($sql_ins);
  168. if ($rs_coachs === false) {
  169. error_log('Could not move course coach to new table: '.mysql_error());
  170. }
  171. }
  172. }
  173. // Remove duplicated rows for 'show_tutor_data' AND 'show_teacher_data' into settings_current table
  174. $sql = "SELECT id FROM settings_current WHERE variable='show_tutor_data' ORDER BY id";
  175. $rs_chk_id1 = mysql_query($sql);
  176. if ($rs_chk_id1 === false) {
  177. error_log('Could not query settings_current ids table: '.mysql_error());
  178. } else {
  179. $i = 1;
  180. while ($row_id1 = mysql_fetch_array($rs_chk_id1)) {
  181. $id = $row_id1['id'];
  182. if ($i>1) {
  183. $sql_del = "DELETE FROM settings_current WHERE id = '$id'";
  184. mysql_query($sql_del);
  185. }
  186. $i++;
  187. }
  188. }
  189. $sql = "SELECT id FROM settings_current WHERE variable='show_teacher_data' ORDER BY id";
  190. $rs_chk_id2 = mysql_query($sql);
  191. if ($rs_chk_id2 === false) {
  192. error_log('Could not query settings_current ids table: '.mysql_error());
  193. } else {
  194. $i = 1;
  195. while ($row_id2 = mysql_fetch_array($rs_chk_id2)) {
  196. $id = $row_id2['id'];
  197. if ($i>1) {
  198. $sql_del = "DELETE FROM settings_current WHERE id = '$id'";
  199. mysql_query($sql_del);
  200. }
  201. $i++;
  202. }
  203. }
  204. }
  205. }
  206. // now clean the deprecated id_coach field from the session_rel_course
  207. // table
  208. $m_q_list = get_sql_file_contents('migrate-db-'.$old_file_version.'-'.$new_file_version.'-post.sql','main');
  209. if(count($m_q_list)>0)
  210. {
  211. //now use the $m_q_list
  212. /**
  213. * We connect to the right DB first to make sure we can use the queries
  214. * without a database name
  215. */
  216. if(strlen($dbNameForm)>40){
  217. error_log('Database name '.$dbNameForm.' is too long, skipping',0);
  218. }elseif(!in_array($dbNameForm,$dblist)){
  219. error_log('Database '.$dbNameForm.' was not found, skipping',0);
  220. }else{
  221. mysql_select_db($dbNameForm);
  222. foreach($m_q_list as $query){
  223. if($only_test){
  224. error_log("mysql_query($dbNameForm,$query)",0);
  225. }else{
  226. $res = mysql_query($query);
  227. if($log)
  228. {
  229. error_log("In $dbNameForm, executed: $query",0);
  230. }
  231. }
  232. }
  233. }
  234. }
  235. //get the stats queries list (s_q_list)
  236. $s_q_list = get_sql_file_contents('migrate-db-'.$old_file_version.'-'.$new_file_version.'-pre.sql','stats');
  237. if(count($s_q_list)>0)
  238. {
  239. //now use the $s_q_list
  240. /**
  241. * We connect to the right DB first to make sure we can use the queries
  242. * without a database name
  243. */
  244. if(strlen($dbStatsForm)>40){
  245. error_log('Database name '.$dbStatsForm.' is too long, skipping',0);
  246. }elseif(!in_array($dbStatsForm,$dblist)){
  247. error_log('Database '.$dbStatsForm.' was not found, skipping',0);
  248. }else{
  249. mysql_select_db($dbStatsForm);
  250. foreach($s_q_list as $query){
  251. if($only_test){
  252. error_log("mysql_query($dbStatsForm,$query)",0);
  253. }else{
  254. $res = mysql_query($query);
  255. if($log)
  256. {
  257. error_log("In $dbStatsForm, executed: $query",0);
  258. }
  259. }
  260. }
  261. }
  262. }
  263. //get the user queries list (u_q_list)
  264. $u_q_list = get_sql_file_contents('migrate-db-'.$old_file_version.'-'.$new_file_version.'-pre.sql','user');
  265. if(count($u_q_list)>0)
  266. {
  267. //now use the $u_q_list
  268. /**
  269. * We connect to the right DB first to make sure we can use the queries
  270. * without a database name
  271. */
  272. if(strlen($dbUserForm)>40){
  273. error_log('Database name '.$dbUserForm.' is too long, skipping',0);
  274. }elseif(!in_array($dbUserForm,$dblist)){
  275. error_log('Database '.$dbUserForm.' was not found, skipping',0);
  276. }else{
  277. mysql_select_db($dbUserForm);
  278. foreach($u_q_list as $query){
  279. if($only_test){
  280. error_log("mysql_query($dbUserForm,$query)",0);
  281. error_log("In $dbUserForm, executed: $query",0);
  282. }else{
  283. $res = mysql_query($query);
  284. }
  285. }
  286. }
  287. }
  288. //the SCORM database doesn't need a change in the pre-migrate part - ignore
  289. }
  290. /*
  291. -----------------------------------------------------------
  292. Update the Dokeos course databases
  293. this part can be accessed in two ways:
  294. - from the normal upgrade process
  295. - from the script update_courses.php,
  296. which is used to upgrade more than MAX_COURSE_TRANSFER courses
  297. Every time this script is accessed, only
  298. MAX_COURSE_TRANSFER courses are upgraded.
  299. -----------------------------------------------------------
  300. */
  301. $prefix = '';
  302. if ($singleDbForm)
  303. {
  304. $prefix = get_config_param ('table_prefix');
  305. }
  306. //get the courses databases queries list (c_q_list)
  307. $c_q_list = get_sql_file_contents('migrate-db-'.$old_file_version.'-'.$new_file_version.'-pre.sql','course');
  308. if(count($c_q_list)>0)
  309. {
  310. //get the courses list
  311. if(strlen($dbNameForm)>40)
  312. {
  313. error_log('Database name '.$dbNameForm.' is too long, skipping',0);
  314. }
  315. elseif(!in_array($dbNameForm,$dblist))
  316. {
  317. error_log('Database '.$dbNameForm.' was not found, skipping',0);
  318. }
  319. else
  320. {
  321. mysql_select_db($dbNameForm);
  322. $res = mysql_query("SELECT code,db_name,directory,course_language FROM course WHERE target_course_code IS NULL ORDER BY code");
  323. if($res===false){die('Error while querying the courses list in update_db.inc.php');}
  324. if(mysql_num_rows($res)>0)
  325. {
  326. $i=0;
  327. $list = array();
  328. //while( ($i < MAX_COURSE_TRANSFER) && ($row = mysql_fetch_array($res)))
  329. while($row = mysql_fetch_array($res))
  330. {
  331. $list[] = $row;
  332. $i++;
  333. }
  334. foreach($list as $row_course)
  335. {
  336. //now use the $c_q_list
  337. /**
  338. * We connect to the right DB first to make sure we can use the queries
  339. * without a database name
  340. */
  341. if (!$singleDbForm) //otherwise just use the main one
  342. {
  343. mysql_select_db($row_course['db_name']);
  344. }
  345. foreach($c_q_list as $query)
  346. {
  347. if ($singleDbForm) //otherwise just use the main one
  348. {
  349. $query = preg_replace('/^(UPDATE|ALTER TABLE|CREATE TABLE|DROP TABLE|INSERT INTO|DELETE FROM)\s+(\w*)(.*)$/',"$1 $prefix{$row_course['db_name']}_$2$3",$query);
  350. }
  351. if($only_test)
  352. {
  353. error_log("mysql_query(".$row_course['db_name'].",$query)",0);
  354. }
  355. else
  356. {
  357. $res = mysql_query($query);
  358. if($log)
  359. {
  360. error_log("In ".$row_course['db_name'].", executed: $query",0);
  361. }
  362. }
  363. }
  364. // fill description type into course_description table
  365. $t_course_description = $row_course['db_name'].".course_description";
  366. if($singleDbForm)
  367. {
  368. $t_course_description = "$prefix{$row_course['db_name']}_course_description";
  369. }
  370. // get all ids and update description_type field with them from course_description table
  371. $sql_sel = "SELECT id FROM $t_course_description";
  372. $rs_sel = mysql_query($sql_sel);
  373. if ($rs_sel === false) {
  374. error_log('Could not query course_description ids table: '.mysql_error());
  375. } else {
  376. if (mysql_num_rows($rs_sel) > 0) {
  377. while ($row_ids = mysql_fetch_array($rs_sel)) {
  378. $description_id = $row_ids['id'];
  379. $sql_upd = "UPDATE $t_course_description SET description_type='$description_id' WHERE id='$description_id'";
  380. mysql_query($sql_upd);
  381. }
  382. }
  383. }
  384. }
  385. }
  386. }
  387. }
  388. }
  389. else
  390. {
  391. echo 'You are not allowed here !';
  392. }