scorm_update_db.php 9.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160
  1. <?php //$id: $
  2. /**
  3. * Script that updates the database by adding the four new learning path tables (that allow scorm data)
  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. $create_lp = "CREATE TABLE IF NOT EXISTS XXX_$new_lp (" .
  19. "id int unsigned primary key auto_increment," . //unique ID, generated by MySQL
  20. "lp_type smallint unsigned not null," . //lp_types can be found in the main database's lp_type table
  21. "name tinytext not null," . //name is the text name of the learning path (e.g. Word 2000)
  22. "ref tinytext null," . //ref for SCORM elements is the SCORM ID in imsmanifest. For other learnpath types, just ignore
  23. "description text null,". //textual description
  24. "path text not null," . //path, starting at the platforms root (so all paths should start with 'courses/...' for now)
  25. "force_commit tinyint unsigned not null default 0, " . //stores the default behaviour regarding SCORM information
  26. "default_view_mod char(32) not null default 'embedded'," .//stores the default view mode (embedded or fullscreen)
  27. "default_encoding char(32) not null default 'ISO-8859-1', " . //stores the encoding detected at learning path reading
  28. "display_order int unsigned not null default 0," . //order of learnpaths display in the learnpaths list - not really important
  29. "content_maker tinytext not null default ''," . //content maker (ENI, Articulate, ...)
  30. "content_local varchar(32) not null default 'local'," . //content localisation ('local' or 'distant')
  31. "content_license text not null default ''," . //content license
  32. "prevent_reinit tinyint unsigned not null default 1," . //stores the default behaviour regarding items re-initialisation when viewed a second time after success
  33. "js_lib tinytext not null default ''," . //the JavaScript library to load for this lp
  34. "debug tinyint unsigned not null default 0" . //stores the default behaviour regarding items re-initialisation when viewed a second time after success
  35. ")";
  36. //new table, aimed at keeping track of attempts made to one learning path
  37. //no row exists if nobody has opened any learning path yet. A row is only written when someone opens a learnpath
  38. $new_lp_view = 'lp_view';
  39. $create_lp_view = "CREATE TABLE IF NOT EXISTS XXX_$new_lp_view (" .
  40. "id int unsigned primary key auto_increment," . //unique ID from MySQL
  41. "lp_id int unsigned not null," . //learnpath ID from 'lp'
  42. "user_id int unsigned not null," . //user ID from main.user
  43. "view_count smallint unsigned not null default 0," . //integer counting the amount of times this learning path has been attempted
  44. "last_item int unsigned not null default 0," . //last item seen in this view
  45. "progress int unsigned default 0" .
  46. ")";
  47. //table replacing learnpath_item AND learnpath_chapter
  48. $new_lp_item = 'lp_item';
  49. $create_lp_item = "CREATE TABLE IF NOT EXISTS XXX_$new_lp_item (" .
  50. "id int unsigned primary key auto_increment," . //unique ID from MySQL
  51. "lp_id int unsigned not null," . //lp_id from 'lp'
  52. "item_type char(32) not null default 'dokeos_document'," . //can be dokeos_document, dokeos_chapter or scorm_asset, scorm_sco, scorm_chapter
  53. "ref tinytext not null default ''," . //the ID given to this item in the imsmanifest file
  54. "title tinytext not null," . //the title/name of this item (to display in the T.O.C.)
  55. "description tinytext not null default ''," . //the description of this item (to display in the T.O.C.)
  56. "path text not null," . //the path to that item, starting at 'courses/...' level
  57. "min_score float unsigned not null default 0," . //min score allowed
  58. "max_score float unsigned not null default 100," . //max score allowed
  59. "mastery_score float unsigned null," . //minimum score to pass the test
  60. "parent_item_id int unsigned not null default 0," . //the item one level higher
  61. "previous_item_id int unsigned not null default 0," . //the item before this one in the sequential learning order (MySQL id)
  62. "next_item_id int unsigned not null default 0," . //the item after this one in the sequential learning order (MySQL id)
  63. "display_order int unsigned not null default 0," . //this is needed for ordering items under the same parent (previous_item_id doesn't give correct order after reordering)
  64. "prerequisite char(64) null," . //prerequisites in AICC scripting language as defined in the SCORM norm (allow logical operators)
  65. "launch_data text not null default ''" . //data from imsmanifest <item>
  66. ")";
  67. //table replacing the learnpath_user table
  68. $new_lp_item_view = 'lp_item_view';
  69. $create_lp_item_view = "CREATE TABLE IF NOT EXISTS XXX_$new_lp_item_view (" .
  70. "id bigint unsigned primary key auto_increment," . //unique ID
  71. "lp_item_id int unsigned not null," . //item ID (MySQL id)
  72. "lp_view_id int unsigned not null," . // learning path view id (attempt)
  73. "view_count int unsigned not null default 0," . //how many times this item has been viewed in the current attempt (generally 0 or 1)
  74. "start_time int unsigned not null," . //when did the user open it?
  75. "total_time int unsigned not null default 0," . //after how many seconds did he close it?
  76. "score float unsigned not null default 0," . //score returned by SCORM or other techs
  77. "status char(32) not null default 'not attempted'," . //status for this item (SCORM)
  78. "suspend_data text null default ''," .
  79. "lesson_location text null default ''" .
  80. ")";
  81. //table implementing item_view interactions
  82. $new_lp_iv_interaction = 'lp_iv_interaction';
  83. $alter_lp_iv_interaction = "CREATE TABLE XXX_$new_lp_iv_interaction(" .
  84. "id bigint unsigned AUTO_INCREMENT PRIMARY KEY," .
  85. "order_id smallint unsigned not null default 0,". //internal order (0->...) given by Dokeos but usable by sco
  86. "lp_iv_id bigint unsigned not null," . //identifier of the related sco_view
  87. "interaction_id varchar(255) not null default ''," . //sco-specific, given by the sco
  88. "interaction_type varchar(255) not null default ''," . //literal values, SCORM-specific (see p.63 of SCORM 1.2 RTE)
  89. "weighting double not null default 0," .
  90. "completion_time varchar(16) not null default ''," . //completion time for the interaction (timestamp in a day's time) - expected output format is scorm time
  91. "correct_responses text not null default ''," . //actually a serialised array. See p.65 os SCORM 1.2 RTE)
  92. "student_response text not null default ''," . //student response (format depends on type)
  93. "result varchar(255) not null default ''," . //textual result
  94. "latency varchar(16) not null default ''" . //time necessary for completion of the interaction
  95. ")";
  96. //table located in the main DB and holding a list of the possible learning paths types.
  97. //so far we only have 'dokeos' and 'scorm'
  98. $new_lp_type = 'lp_type';
  99. $create_lp_type = "CREATE TABLE IF NOT EXISTS YYY_$new_lp_type (" .
  100. "id smallint unsigned primary key auto_increment," .
  101. "name char(32) not null default 'dokeos'," .
  102. "description char(255) null" .
  103. ")";
  104. /**
  105. * First create the lp, lp_view, lp_item and lp_item_view tables in each course's DB
  106. */
  107. $main_db = Database::get_main_database();
  108. $sql = "SELECT * FROM $main_db.course";
  109. echo "$sql<br />\n";
  110. $res = api_sql_query($sql);
  111. $create_table = str_replace('YYY_',$main_db.'.',$create_lp_type);
  112. echo "$create_table<br />\n";
  113. api_sql_query($create_table);
  114. $ins_elems = "INSERT INTO $main_db.$new_lp_type (id,name,description) VALUES (1,'dokeos','The default format of a learning path in Dokeos')";
  115. api_sql_query($ins_elems);
  116. $ins_elems = "INSERT INTO $main_db.$new_lp_type (id,name,description) VALUES (2,'scorm','SCORM format')";
  117. api_sql_query($ins_elems);
  118. $courses_list = array();
  119. $courses_id_list = array();
  120. $courses_dir_list = array();
  121. while ($row = Database::fetch_array($res))
  122. {
  123. //TODO change this db name construction to use DB instead of claro_main.conf settings
  124. $course_pref = Database::get_course_table_prefix();
  125. $dbname = $row['db_name'].'.'.$course_pref;
  126. $courses_list[] = $row['db_name'];
  127. $courses_id_list[$row['code']] = $row['db_name'];
  128. $courses_dir_list[$row['code']] = $row['directory'];
  129. if(empty($_GET['delete'])){
  130. echo "Creating tables for ".$row['db_name']."<br />\n";
  131. $create_table = str_replace('XXX_',$dbname,$create_lp);
  132. echo "$create_table<br />\n";
  133. api_sql_query($create_table);
  134. $create_table = str_replace('XXX_',$dbname,$create_lp_view);
  135. echo "$create_table<br />\n";
  136. api_sql_query($create_table);
  137. $create_table = str_replace('XXX_',$dbname,$create_lp_item);
  138. echo "$create_table<br />\n";
  139. api_sql_query($create_table);
  140. $create_table = str_replace('XXX_',$dbname,$create_lp_item_view);
  141. echo "$create_table<br />\n";
  142. api_sql_query($create_table);
  143. echo "<br /><br />\n";
  144. }else{
  145. echo "Deleting tables for ".$row['db_name']."<br />\n";
  146. $del_sql = "DROP TABLE ".$dbname.$new_lp;
  147. echo "$del_sql<br />\n";
  148. $del_sql = "DROP TABLE ".$dbname.$new_lp_view;
  149. echo "$del_sql<br />\n";
  150. $del_sql = "DROP TABLE ".$dbname.$new_lp_item;
  151. echo "$del_sql<br />\n";
  152. $del_sql = "DROP TABLE ".$dbname.$new_lp_item_view;
  153. echo "$del_sql<br />\n";
  154. echo "<br /><br />\n";
  155. }
  156. }
  157. echo "Tables created/deleted for all courses<br />\n";
  158. ?>