scorm_update_db_alter.php 5.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126
  1. <?php //$id: $
  2. /**
  3. * Script that updates the four new learning path tables (that allow scorm data) to add several fields
  4. * @package dokeos.learnpath
  5. * @author Yannick Warnier <ywarnier@beeznest.org>
  6. */
  7. /**
  8. * Script
  9. */
  10. require_once('back_compat.inc.php');
  11. require_once('learnpath.class.php');
  12. require_once('scorm.class.php');
  13. /**
  14. * New tables definition:
  15. */
  16. //table replacing learnpath_main
  17. $new_lp = 'lp';
  18. $alter_lp = "ALTER TABLE XXX_$new_lp ADD COLUMN (" .
  19. "content_license text not null default ''" . //content license
  20. ")";
  21. $alter_lp2 = "ALTER TABLE XXX_$new_lp ADD COLUMN (" .
  22. "prevent_reinit tinyint unsigned not null default 1" . //stores the default behaviour regarding items re-initialisation when viewed a second time after success
  23. ")";
  24. $alter_lp3 = "ALTER TABLE XXX_$new_lp ADD COLUMN (" .
  25. "debug tinyint unsigned not null default 0" . //stores the default behaviour regarding items re-initialisation when viewed a second time after success
  26. ")";
  27. //new table, aimed at keeping track of attempts made to one learning path
  28. //no row exists if nobody has opened any learning path yet. A row is only written when someone opens a learnpath
  29. $new_lp_view = 'lp_view';
  30. $alter_lp_view = "ALTER TABLE XXX_$new_lp_view ADD COLUMN " .
  31. "progress int unsigned default 0" .
  32. "";
  33. //table replacing the learnpath_user table
  34. $new_lp_item_view = 'lp_item_view';
  35. $alter_lp_item_view = "ALTER TABLE XXX_$new_lp_item_view ADD COLUMN " .
  36. "lesson_location text null default ''" .
  37. "";
  38. $new_lp_iv_interaction = 'lp_iv_interaction';
  39. $alter_lp_iv_interaction = "CREATE TABLE XXX_$new_lp_iv_interaction(" .
  40. "id bigint unsigned AUTO_INCREMENT PRIMARY KEY," .
  41. "order_id smallint unsigned not null default 0,". //internal order (0->...) given by Dokeos
  42. "lp_iv_id bigint unsigned not null," . //identifier of the related sco_view
  43. "interaction_id varchar(255) not null default ''," . //sco-specific, given by the sco
  44. "interaction_type varchar(255) not null default ''," . //literal values, SCORM-specific (see p.63 of SCORM 1.2 RTE)
  45. "weighting double not null default 0," .
  46. "completion_time varchar(16) not null default ''," . //completion time for the interaction (timestamp in a day's time) - expected output format is scorm time
  47. "correct_responses text not null default ''," . //actually a serialised array. See p.65 os SCORM 1.2 RTE)
  48. "student_response text not null default ''," . //student response (format depends on type)
  49. "result varchar(255) not null default ''," . //textual result
  50. "latency varchar(16) not null default ''" . //time necessary for completion of the interaction
  51. ")";
  52. $alter_lp_iv_interaction2 = "ALTER TABLE XXX_$new_lp_iv_interaction CHANGE `time` completion_time VARCHAR(16) NOT NULL DEFAULT '0'";
  53. $alter_lp_iv_interaction3 = "ALTER TABLE XXX_$new_lp_iv_interaction CHANGE `type` interaction_type VARCHAR(255) NOT NULL DEFAULT ''";
  54. /**
  55. * First create the lp, lp_view, lp_item and lp_item_view tables in each course's DB
  56. */
  57. $main_db = Database::get_main_database();
  58. $sql = "SELECT * FROM $main_db.course";
  59. echo "$sql<br />\n";
  60. $res = api_sql_query($sql);
  61. $courses_list = array();
  62. $courses_id_list = array();
  63. $courses_dir_list = array();
  64. while ($row = Database::fetch_array($res))
  65. {
  66. //TODO change this db name construction to use DB instead of claro_main.conf settings
  67. $course_pref = Database::get_course_table_prefix();
  68. $dbname = $row['db_name'].'.'.$course_pref;
  69. $courses_list[] = $row['db_name'];
  70. $courses_id_list[$row['code']] = $row['db_name'];
  71. $courses_dir_list[$row['code']] = $row['directory'];
  72. if(empty($_GET['delete'])){
  73. echo "Updating tables for ".$row['db_name']."<br />\n";
  74. if (mysql_query("SELECT content_license FROM $new_lp")==false)
  75. {
  76. $create_table = str_replace('XXX_',$dbname,$alter_lp);
  77. echo "$create_table<br />\n";
  78. api_sql_query($create_table);
  79. }
  80. if (mysql_query("SELECT prevent_reinit FROM $new_lp")==false)
  81. {
  82. $create_table = str_replace('XXX_',$dbname,$alter_lp2);
  83. echo "$create_table<br />\n";
  84. api_sql_query($create_table);
  85. }
  86. if (mysql_query("SELECT debug FROM $new_lp")==false)
  87. {
  88. $create_table = str_replace('XXX_',$dbname,$alter_lp3);
  89. echo "$create_table<br />\n";
  90. api_sql_query($create_table);
  91. }
  92. if (mysql_query("SELECT progress FROM $new_lp_view")==false)
  93. {
  94. $create_table = str_replace('XXX_',$dbname,$alter_lp_view);
  95. echo "$create_table<br />\n";
  96. api_sql_query($create_table);
  97. }
  98. if (mysql_query("SELECT lesson_location FROM $new_lp_item_view")==false)
  99. {
  100. $create_table = str_replace('XXX_',$dbname,$alter_lp_item_view);
  101. echo "$create_table<br />\n";
  102. api_sql_query($create_table);
  103. }
  104. if(mysql_query("SELECT id FROM $new_lp_iv_interaction")==false){
  105. $create_table = str_replace('XXX_',$dbname,$alter_lp_iv_interaction);
  106. echo "$create_table<br/>\n";
  107. api_sql_query($create_table);
  108. }
  109. if(mysql_query("SELECT `type` FROM $new_lp_iv_interaction")==false){
  110. $create_table = str_replace('XXX_',$dbname,$alter_lp_iv_interaction2);
  111. echo "$create_table<br/>\n";
  112. api_sql_query($create_table);
  113. }
  114. if(mysql_query("SELECT `time` FROM $new_lp_iv_interaction")==false){
  115. $create_table = str_replace('XXX_',$dbname,$alter_lp_iv_interaction3);
  116. echo "$create_table<br/>\n";
  117. api_sql_query($create_table);
  118. }
  119. echo "<br /><br />\n";
  120. }
  121. }
  122. echo "Tables updated for all courses<br />\n";
  123. ?>