123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160 |
- <?php //$id: $
- /**
- * Script that updates the database by adding the four new learning path tables (that allow scorm data)
- * @package dokeos.learnpath
- * @author Yannick Warnier <ywarnier@beeznest.org>
- */
- /**
- * Script
- */
- require_once('back_compat.inc.php');
- require_once('learnpath.class.php');
- require_once('scorm.class.php');
- /**
- * New tables definition:
- */
- //table replacing learnpath_main
- $new_lp = 'lp';
- $create_lp = "CREATE TABLE IF NOT EXISTS XXX_$new_lp (" .
- "id int unsigned primary key auto_increment," . //unique ID, generated by MySQL
- "lp_type smallint unsigned not null," . //lp_types can be found in the main database's lp_type table
- "name tinytext not null," . //name is the text name of the learning path (e.g. Word 2000)
- "ref tinytext null," . //ref for SCORM elements is the SCORM ID in imsmanifest. For other learnpath types, just ignore
- "description text null,". //textual description
- "path text not null," . //path, starting at the platforms root (so all paths should start with 'courses/...' for now)
- "force_commit tinyint unsigned not null default 0, " . //stores the default behaviour regarding SCORM information
- "default_view_mod char(32) not null default 'embedded'," .//stores the default view mode (embedded or fullscreen)
- "default_encoding char(32) not null default 'ISO-8859-1', " . //stores the encoding detected at learning path reading
- "display_order int unsigned not null default 0," . //order of learnpaths display in the learnpaths list - not really important
- "content_maker tinytext not null default ''," . //content maker (ENI, Articulate, ...)
- "content_local varchar(32) not null default 'local'," . //content localisation ('local' or 'distant')
- "content_license text not null default ''," . //content license
- "prevent_reinit tinyint unsigned not null default 1," . //stores the default behaviour regarding items re-initialisation when viewed a second time after success
- "js_lib tinytext not null default ''," . //the JavaScript library to load for this lp
- "debug tinyint unsigned not null default 0" . //stores the default behaviour regarding items re-initialisation when viewed a second time after success
- ")";
- //new table, aimed at keeping track of attempts made to one learning path
- //no row exists if nobody has opened any learning path yet. A row is only written when someone opens a learnpath
- $new_lp_view = 'lp_view';
- $create_lp_view = "CREATE TABLE IF NOT EXISTS XXX_$new_lp_view (" .
- "id int unsigned primary key auto_increment," . //unique ID from MySQL
- "lp_id int unsigned not null," . //learnpath ID from 'lp'
- "user_id int unsigned not null," . //user ID from main.user
- "view_count smallint unsigned not null default 0," . //integer counting the amount of times this learning path has been attempted
- "last_item int unsigned not null default 0," . //last item seen in this view
- "progress int unsigned default 0" .
- ")";
- //table replacing learnpath_item AND learnpath_chapter
- $new_lp_item = 'lp_item';
- $create_lp_item = "CREATE TABLE IF NOT EXISTS XXX_$new_lp_item (" .
- "id int unsigned primary key auto_increment," . //unique ID from MySQL
- "lp_id int unsigned not null," . //lp_id from 'lp'
- "item_type char(32) not null default 'dokeos_document'," . //can be dokeos_document, dokeos_chapter or scorm_asset, scorm_sco, scorm_chapter
- "ref tinytext not null default ''," . //the ID given to this item in the imsmanifest file
- "title tinytext not null," . //the title/name of this item (to display in the T.O.C.)
- "description tinytext not null default ''," . //the description of this item (to display in the T.O.C.)
- "path text not null," . //the path to that item, starting at 'courses/...' level
- "min_score float unsigned not null default 0," . //min score allowed
- "max_score float unsigned not null default 100," . //max score allowed
- "mastery_score float unsigned null," . //minimum score to pass the test
- "parent_item_id int unsigned not null default 0," . //the item one level higher
- "previous_item_id int unsigned not null default 0," . //the item before this one in the sequential learning order (MySQL id)
- "next_item_id int unsigned not null default 0," . //the item after this one in the sequential learning order (MySQL id)
- "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)
- "prerequisite char(64) null," . //prerequisites in AICC scripting language as defined in the SCORM norm (allow logical operators)
- "launch_data text not null default ''" . //data from imsmanifest <item>
- ")";
- //table replacing the learnpath_user table
- $new_lp_item_view = 'lp_item_view';
- $create_lp_item_view = "CREATE TABLE IF NOT EXISTS XXX_$new_lp_item_view (" .
- "id bigint unsigned primary key auto_increment," . //unique ID
- "lp_item_id int unsigned not null," . //item ID (MySQL id)
- "lp_view_id int unsigned not null," . // learning path view id (attempt)
- "view_count int unsigned not null default 0," . //how many times this item has been viewed in the current attempt (generally 0 or 1)
- "start_time int unsigned not null," . //when did the user open it?
- "total_time int unsigned not null default 0," . //after how many seconds did he close it?
- "score float unsigned not null default 0," . //score returned by SCORM or other techs
- "status char(32) not null default 'not attempted'," . //status for this item (SCORM)
- "suspend_data text null default ''," .
- "lesson_location text null default ''" .
- ")";
- //table implementing item_view interactions
- $new_lp_iv_interaction = 'lp_iv_interaction';
- $alter_lp_iv_interaction = "CREATE TABLE XXX_$new_lp_iv_interaction(" .
- "id bigint unsigned AUTO_INCREMENT PRIMARY KEY," .
- "order_id smallint unsigned not null default 0,". //internal order (0->...) given by Dokeos but usable by sco
- "lp_iv_id bigint unsigned not null," . //identifier of the related sco_view
- "interaction_id varchar(255) not null default ''," . //sco-specific, given by the sco
- "interaction_type varchar(255) not null default ''," . //literal values, SCORM-specific (see p.63 of SCORM 1.2 RTE)
- "weighting double not null default 0," .
- "completion_time varchar(16) not null default ''," . //completion time for the interaction (timestamp in a day's time) - expected output format is scorm time
- "correct_responses text not null default ''," . //actually a serialised array. See p.65 os SCORM 1.2 RTE)
- "student_response text not null default ''," . //student response (format depends on type)
- "result varchar(255) not null default ''," . //textual result
- "latency varchar(16) not null default ''" . //time necessary for completion of the interaction
- ")";
- //table located in the main DB and holding a list of the possible learning paths types.
- //so far we only have 'dokeos' and 'scorm'
- $new_lp_type = 'lp_type';
- $create_lp_type = "CREATE TABLE IF NOT EXISTS YYY_$new_lp_type (" .
- "id smallint unsigned primary key auto_increment," .
- "name char(32) not null default 'dokeos'," .
- "description char(255) null" .
- ")";
- /**
- * First create the lp, lp_view, lp_item and lp_item_view tables in each course's DB
- */
- $main_db = Database::get_main_database();
- $sql = "SELECT * FROM $main_db.course";
- echo "$sql<br />\n";
- $res = api_sql_query($sql);
- $create_table = str_replace('YYY_',$main_db.'.',$create_lp_type);
- echo "$create_table<br />\n";
- api_sql_query($create_table);
- $ins_elems = "INSERT INTO $main_db.$new_lp_type (id,name,description) VALUES (1,'dokeos','The default format of a learning path in Dokeos')";
- api_sql_query($ins_elems);
- $ins_elems = "INSERT INTO $main_db.$new_lp_type (id,name,description) VALUES (2,'scorm','SCORM format')";
- api_sql_query($ins_elems);
- $courses_list = array();
- $courses_id_list = array();
- $courses_dir_list = array();
- while ($row = Database::fetch_array($res))
- {
- //TODO change this db name construction to use DB instead of claro_main.conf settings
- $course_pref = Database::get_course_table_prefix();
- $dbname = $row['db_name'].'.'.$course_pref;
- $courses_list[] = $row['db_name'];
- $courses_id_list[$row['code']] = $row['db_name'];
- $courses_dir_list[$row['code']] = $row['directory'];
- if(empty($_GET['delete'])){
- echo "Creating tables for ".$row['db_name']."<br />\n";
- $create_table = str_replace('XXX_',$dbname,$create_lp);
- echo "$create_table<br />\n";
- api_sql_query($create_table);
- $create_table = str_replace('XXX_',$dbname,$create_lp_view);
- echo "$create_table<br />\n";
- api_sql_query($create_table);
- $create_table = str_replace('XXX_',$dbname,$create_lp_item);
- echo "$create_table<br />\n";
- api_sql_query($create_table);
- $create_table = str_replace('XXX_',$dbname,$create_lp_item_view);
- echo "$create_table<br />\n";
- api_sql_query($create_table);
- echo "<br /><br />\n";
- }else{
- echo "Deleting tables for ".$row['db_name']."<br />\n";
- $del_sql = "DROP TABLE ".$dbname.$new_lp;
- echo "$del_sql<br />\n";
- $del_sql = "DROP TABLE ".$dbname.$new_lp_view;
- echo "$del_sql<br />\n";
- $del_sql = "DROP TABLE ".$dbname.$new_lp_item;
- echo "$del_sql<br />\n";
- $del_sql = "DROP TABLE ".$dbname.$new_lp_item_view;
- echo "$del_sql<br />\n";
- echo "<br /><br />\n";
- }
- }
- echo "Tables created/deleted for all courses<br />\n";
- ?>
|