Dokeos PHP Cross Reference Learning Management Systems

Source: /main/inc/lib/database.lib.php - 863 lines - 33551 bytes - Summary - Text - Print

Description: This is the main database library for Dokeos. Include/require it in your code to use its functionality. Because this library contains all the basic database calls, it could be replaced by another library for say, PostgreSQL, to actually use Dokeos with another database (this is not ready yet because a lot of code still uses the MySQL database functions extensively).

   1  <?php
   2  /* For licensing terms, see /dokeos_license.txt */
   3  
   4  /**
   5  ==============================================================================
   6  *    This is the main database library for Dokeos.
   7  *    Include/require it in your code to use its functionality.
   8  *   Because this library contains all the basic database calls, it could be
   9  *   replaced by another library for say, PostgreSQL, to actually use Dokeos
  10  *   with another database (this is not ready yet because a lot of code still
  11  *   uses the MySQL database functions extensively).
  12  *
  13  *    @package dokeos.library
  14  *     @todo the table constants have all to start with TABLE_
  15  *           This is because of the analogy with the tool constants TOOL_
  16  ==============================================================================
  17  */
  18  
  19  /*
  20  ==============================================================================
  21          CONSTANTS
  22  ==============================================================================
  23  */
  24  
  25  // Main database tables
  26  define('TABLE_MAIN_COURSE', 'course');
  27  define('TABLE_MAIN_USER', 'user');
  28  define('TABLE_MAIN_CLASS', 'class');
  29  define('TABLE_MAIN_ADMIN', 'admin');
  30  define('TABLE_MAIN_COURSE_CLASS', 'course_rel_class');
  31  define('TABLE_MAIN_COURSE_USER', 'course_rel_user');
  32  define('TABLE_MAIN_CLASS_USER', 'class_user');
  33  define('TABLE_MAIN_CATEGORY', 'course_category');
  34  define('TABLE_MAIN_COURSE_MODULE', 'course_module');
  35  define('TABLE_MAIN_SYSTEM_ANNOUNCEMENTS', 'sys_announcement');
  36  define('TABLE_MAIN_LANGUAGE', 'language');
  37  define('TABLE_MAIN_SETTINGS_OPTIONS', 'settings_options');
  38  define('TABLE_MAIN_SETTINGS_CURRENT', 'settings_current');
  39  define('TABLE_MAIN_SESSION', 'session');
  40  define('TABLE_MAIN_SESSION_CATEGORY', 'session_category');
  41  define('TABLE_MAIN_SESSION_COURSE', 'session_rel_course');
  42  define('TABLE_MAIN_SESSION_USER', 'session_rel_user');
  43  define('TABLE_MAIN_SESSION_CLASS', 'session_rel_class');
  44  define('TABLE_MAIN_SESSION_COURSE_USER', 'session_rel_course_rel_user');
  45  define('TABLE_MAIN_SHARED_SURVEY', 'shared_survey');
  46  define('TABLE_MAIN_SHARED_SURVEY_QUESTION', 'shared_survey_question');
  47  define('TABLE_MAIN_SHARED_SURVEY_QUESTION_OPTION', 'shared_survey_question_option');
  48  define('TABLE_MAIN_TEMPLATES', 'templates');
  49  define('TABLE_MAIN_EMAILTEMPLATES', 'email_template');
  50  define('TABLE_MAIN_QUIZ_ANSWER_TEMPLATES', 'quiz_answer_templates');
  51  define('TABLE_MAIN_QUIZ_QUESTION_TEMPLATES', 'quiz_question_templates');
  52  define('TABLE_MAIN_SYSTEM_TEMPLATE', 'system_template');
  53  define('TABLE_MAIN_OPENID_ASSOCIATION', 'openid_association');
  54  define('TABLE_MAIN_SESSION_CURSUS', 'session_cursus');
  55  define('TABLE_MAIN_SINGLE_SIGN_ON_ASSOCIATION', 'single_sign_on_association');
  56  define('TABLE_MAIN_SEARCH_ENGINE_KEYWORDS', 'search_engine_keywords');
  57  define('TABLE_MAIN_CATALOGUE', 'catalogue');
  58  define('TABLE_MAIN_TOPIC', 'topic');
  59  define('TABLE_MAIN_SESSION_REL_CATEGORY', 'session_rel_category');
  60  define('TABLE_MAIN_SESSION_CATEGORY_REL_USER', 'session_category_rel_user');
  61  // Gradebook
  62  define('TABLE_MAIN_GRADEBOOK_CATEGORY',     'gradebook_category');
  63  define('TABLE_MAIN_GRADEBOOK_EVALUATION',     'gradebook_evaluation');
  64  define('TABLE_MAIN_GRADEBOOK_LINKEVAL_LOG', 'gradebook_linkeval_log');
  65  define('TABLE_MAIN_GRADEBOOK_RESULT',         'gradebook_result');
  66  define('TABLE_MAIN_GRADEBOOK_RESULT_LOG',     'gradebook_result_log');
  67  define('TABLE_MAIN_GRADEBOOK_LINK',         'gradebook_link');
  68  define('TABLE_MAIN_GRADEBOOK_SCORE_DISPLAY','gradebook_score_display');
  69  define('TABLE_MAIN_GRADEBOOK_CERTIFICATE',     'gradebook_certificate');
  70  //Profiling
  71  define('TABLE_MAIN_USER_FIELD',            'user_field');
  72  define('TABLE_MAIN_USER_FIELD_OPTIONS',    'user_field_options');
  73  define('TABLE_MAIN_USER_FIELD_VALUES',    'user_field_values');
  74  
  75  
  76  // Certificates
  77  define('TABLE_MAIN_CERTIFICATE',       'certificate');
  78  define('TABLE_MAIN_CERTIFICATE_TEMPLATE', 'certificate_template');
  79  
  80  //User tags
  81  define('TABLE_MAIN_TAG',                'tag');
  82  define('TABLE_MAIN_USER_REL_TAG',        'user_rel_tag');
  83  
  84  
  85  //User groups
  86  define('TABLE_MAIN_GROUP',                'group');
  87  define('TABLE_MAIN_USER_REL_GROUP',        'group_rel_user');
  88  define('TABLE_MAIN_GROUP_REL_TAG',        'group_rel_tag');
  89  
  90  
  91  // Search engine
  92  define('TABLE_MAIN_SPECIFIC_FIELD',            'specific_field');
  93  define('TABLE_MAIN_SPECIFIC_FIELD_VALUES',    'specific_field_values');
  94  define('TABLE_MAIN_SEARCH_ENGINE_REF',        'search_engine_ref');
  95  
  96  // Access URLs
  97  define('TABLE_MAIN_ACCESS_URL', 'access_url');
  98  define('TABLE_MAIN_ACCESS_URL_REL_USER',    'access_url_rel_user');
  99  define('TABLE_MAIN_ACCESS_URL_REL_COURSE',     'access_url_rel_course');
 100  define('TABLE_MAIN_ACCESS_URL_REL_SESSION', 'access_url_rel_session');
 101  define('TABLE_MAIN_ACCESS_URL_REL_ADMIN', 'access_url_rel_admin');
 102  
 103  // Global calendar
 104  define('TABLE_MAIN_SYSTEM_CALENDAR', 'sys_calendar');
 105  
 106  // Reservation System
 107  define('TABLE_MAIN_RESERVATION_ITEM', 'reservation_item');
 108  define('TABLE_MAIN_RESERVATION_RESERVATION', 'reservation_main');
 109  define('TABLE_MAIN_RESERVATION_SUBSCRIBTION', 'reservation_subscription');
 110  define('TABLE_MAIN_RESERVATION_CATEGORY', 'reservation_category');
 111  define('TABLE_MAIN_RESERVATION_ITEM_RIGHTS', 'reservation_item_rights');
 112  
 113  // Social networking
 114  define('TABLE_MAIN_USER_REL_USER', 'user_rel_user');
 115  define('TABLE_MAIN_USER_FRIEND_RELATION_TYPE', 'user_friend_relation_type');
 116  define('TABLE_MAIN_SOCIAL_CHAT', 'user_chat');
 117  
 118  // Web services
 119  define('TABLE_MAIN_USER_API_KEY', 'user_api_key');
 120  define('TABLE_MAIN_COURSE_FIELD', 'course_field');
 121  define('TABLE_MAIN_COURSE_FIELD_VALUES', 'course_field_values');
 122  define('TABLE_MAIN_SESSION_FIELD', 'session_field');
 123  define('TABLE_MAIN_SESSION_FIELD_VALUES', 'session_field_values');
 124  define('TABLE_MAIN_COUNTRY', 'country');
 125  define('TABLE_MAIN_PAYMENT_LOG', 'payment_log');
 126  define('TABLE_MAIN_PAYMENT_ATOS', 'payment_atos');
 127  
 128  //Slides
 129  define('TABLE_MAIN_SLIDES', 'slides');
 130  define('TABLE_MAIN_SLIDES_MANAGEMENT', 'slides_management');
 131  
 132  // Message
 133  define('TABLE_MAIN_MESSAGE', 'message');
 134  
 135  // Term and conditions
 136  define('TABLE_MAIN_LEGAL', 'legal');
 137  
 138  // Statistic database tables
 139  define('TABLE_STATISTIC_TRACK_E_LASTACCESS', 'track_e_lastaccess');
 140  define('TABLE_STATISTIC_TRACK_E_ACCESS', 'track_e_access');
 141  define('TABLE_STATISTIC_TRACK_E_LOGIN', 'track_e_login');
 142  define('TABLE_STATISTIC_TRACK_E_DOWNLOADS', 'track_e_downloads');
 143  define('TABLE_STATISTIC_TRACK_E_LINKS', 'track_e_links');
 144  define('TABLE_STATISTIC_TRACK_E_ONLINE', 'track_e_online');
 145  define('TABLE_STATISTIC_TRACK_E_HOTPOTATOES', 'track_e_hotpotatoes');
 146  define('TABLE_STATISTIC_TRACK_E_COURSE_ACCESS', 'track_e_course_access');
 147  define('TABLE_STATISTIC_TRACK_E_EXERCICES', 'track_e_exercices');
 148  define('TABLE_STATISTIC_TRACK_E_ATTEMPT', 'track_e_attempt');
 149  define('TABLE_STATISTIC_TRACK_E_ATTEMPT_RECORDING', 'track_e_attempt_recording');
 150  define('TABLE_STATISTIC_TRACK_E_DEFAULT', 'track_e_default');
 151  define('TABLE_STATISTIC_TRACK_E_UPLOADS', 'track_e_uploads');
 152  define('TABLE_STATISTIC_TRACK_E_HOTSPOT', 'track_e_hotspot');
 153  
 154  // SCORM database tables
 155  define('TABLE_SCORM_MAIN', 'scorm_main');
 156  define('TABLE_SCORM_SCO_DATA', 'scorm_sco_data');
 157  
 158  // Course tables
 159  define('TABLE_AGENDA', 'calendar_event');
 160  define('TABLE_AGENDA_REPEAT', 'calendar_event_repeat');
 161  define('TABLE_AGENDA_REPEAT_NOT', 'calendar_event_repeat_not');
 162  define('TABLE_AGENDA_ATTACHMENT', 'calendar_event_attachment');
 163  define('TABLE_ANNOUNCEMENT', 'announcement');
 164  define('TABLE_ANNOUNCEMENT_ATTACHMENT', 'announcement_attachment');
 165  define('TABLE_CHAT_CONNECTED', 'chat_connected'); // @todo: probably no longer in use !!!
 166  define('TABLE_COURSE_DESCRIPTION', 'course_description');
 167  define('TABLE_DOCUMENT', 'document');
 168  define('TABLE_ITEM_PROPERTY', 'item_property');
 169  define('TABLE_LINK', 'link');
 170  define('TABLE_LINK_CATEGORY', 'link_category');
 171  define('TABLE_TOOL_LIST', 'tool');
 172  define('TABLE_TOOL_INTRO', 'tool_intro');
 173  define('TABLE_SCORMDOC', 'scormdocument');
 174  define('TABLE_STUDENT_PUBLICATION', 'student_publication');
 175  define('TABLE_STUDENT_PUBLICATION_ASSIGNMENT', 'student_publication_assignment');
 176  define('CHAT_CONNECTED_TABLE', 'chat_connected');
 177  
 178  // Course forum tables
 179  define('TABLE_FORUM_CATEGORY', 'forum_category');
 180  define('TABLE_FORUM', 'forum_forum');
 181  define('TABLE_FORUM_THREAD', 'forum_thread');
 182  define('TABLE_FORUM_POST', 'forum_post');
 183  define('TABLE_FORUM_ATTACHMENT', 'forum_attachment');
 184  define('TABLE_FORUM_MAIL_QUEUE', 'forum_mailcue');
 185  define('TABLE_FORUM_THREAD_QUALIFY', 'forum_thread_qualify');
 186  define('TABLE_FORUM_THREAD_QUALIFY_LOG', 'forum_thread_qualify_log');
 187  define('TABLE_FORUM_NOTIFICATION', 'forum_notification');
 188  
 189  // Course group tables
 190  define('TABLE_GROUP', 'group_info');
 191  define('TABLE_GROUP_USER', 'group_rel_user');
 192  define('TABLE_GROUP_TUTOR', 'group_rel_tutor');
 193  define('TABLE_GROUP_CATEGORY', 'group_category');
 194  
 195  // Course dropbox tables
 196  define('TABLE_DROPBOX_CATEGORY', 'dropbox_category');
 197  define('TABLE_DROPBOX_FEEDBACK', 'dropbox_feedback');
 198  define('TABLE_DROPBOX_POST', 'dropbox_post');
 199  define('TABLE_DROPBOX_FILE', 'dropbox_file');
 200  define('TABLE_DROPBOX_PERSON', 'dropbox_person');
 201  
 202  // Course quiz (or test, or exercice) tables
 203  define('TABLE_QUIZ_QUESTION', 'quiz_question');
 204  define('TABLE_QUIZ_TEST', 'quiz');
 205  define('TABLE_QUIZ_ANSWER', 'quiz_answer');
 206  define('TABLE_QUIZ_TEST_QUESTION', 'quiz_rel_question');
 207  define('TABLE_QUIZ_SCENARIO', 'quiz_scenario');
 208  define('TABLE_QUIZ_CATEGORY', 'quiz_category');
 209  define('TABLE_QUIZ_TYPE', 'quiz_type');
 210  
 211  // Linked resource table
 212  define('TABLE_LINKED_RESOURCES', 'resource');
 213  
 214  // New SCORM tables
 215  define('TABLE_LP_MAIN', 'lp');
 216  define('TABLE_LP_ITEM', 'lp_item');
 217  define('TABLE_LP_VIEW', 'lp_view');
 218  define('TABLE_LP_ITEM_VIEW', 'lp_item_view');
 219  define('TABLE_LP_IV_INTERACTION', 'lp_iv_interaction'); // IV = Item View
 220  define('TABLE_LP_IV_OBJECTIVE', 'lp_iv_objective'); // IV = Item View
 221  
 222  // Smartblogs (Kevin Van Den Haute::kevin@develop-it.be)
 223  // Permission tables
 224  define('TABLE_PERMISSION_USER', 'permission_user');
 225  define('TABLE_PERMISSION_TASK', 'permission_task');
 226  define('TABLE_PERMISSION_GROUP', 'permission_group');
 227  // Role tables
 228  define('TABLE_ROLE', 'role');
 229  define('TABLE_ROLE_PERMISSION', 'role_permissions');
 230  define('TABLE_ROLE_USER', 'role_user');
 231  define('TABLE_ROLE_GROUP', 'role_group');
 232  // Blog tables
 233  define('TABLE_BLOGS', 'blog');
 234  define('TABLE_BLOGS_POSTS', 'blog_post');
 235  define('TABLE_BLOGS_COMMENTS', 'blog_comment');
 236  define('TABLE_BLOGS_REL_USER', 'blog_rel_user');
 237  define('TABLE_BLOGS_TASKS', 'blog_task');
 238  define('TABLE_BLOGS_TASKS_REL_USER', 'blog_task_rel_user');
 239  define('TABLE_BLOGS_RATING', 'blog_rating');
 240  define('TABLE_BLOGS_ATTACHMENT', 'blog_attachment');
 241  define('TABLE_BLOGS_TASKS_PERMISSIONS', 'permission_task');
 242  //end of Smartblogs
 243  
 244  // User information tables
 245  define('TABLE_USER_INFO', 'userinfo_def');
 246  define('TABLE_USER_INFO_CONTENT', 'userinfo_content');
 247  
 248  // Course settings table
 249  define('TABLE_COURSE_SETTING', 'course_setting');
 250  
 251  // Course online tables
 252  define('TABLE_ONLINE_LINK', 'online_link');
 253  define('TABLE_ONLINE_CONNECTED', 'online_connected');
 254  
 255  // Dokeos_user database
 256  define('TABLE_PERSONAL_AGENDA', 'personal_agenda');
 257  define('TABLE_PERSONAL_AGENDA_REPEAT', 'personal_agenda_repeat');
 258  define('TABLE_PERSONAL_AGENDA_REPEAT_NOT', 'personal_agenda_repeat_not');
 259  define('TABLE_USER_COURSE_CATEGORY', 'user_course_category');
 260  
 261  // Survey
 262  // @TODO: Are these MAIN tables or course tables?
 263  // @TODO: Probably these constants are obsolete.
 264  define('TABLE_MAIN_SURVEY', 'survey');
 265  // @TODO: already defined on line 72, if useful rename it (ie. "TABLE_MAIN_SURVEYGROUP")
 266  //define('TABLE_MAIN_GROUP', 'survey_group');
 267  define('TABLE_MAIN_SURVEYQUESTION', 'questions');
 268  
 269  // Survey
 270  define('TABLE_SURVEY', 'survey');
 271  define('TABLE_SURVEY_QUESTION', 'survey_question');
 272  define('TABLE_SURVEY_QUESTION_OPTION', 'survey_question_option');
 273  define('TABLE_SURVEY_INVITATION', 'survey_invitation');
 274  define('TABLE_SURVEY_ANSWER', 'survey_answer');
 275  define('TABLE_SURVEY_QUESTION_GROUP', 'survey_group');
 276  define('TABLE_SURVEY_REPORT', 'survey_report');
 277  
 278  // Wiki tables
 279  define('TABLE_WIKI', 'wiki');
 280  define('TABLE_WIKI_CONF', 'wiki_conf');
 281  define('TABLE_WIKI_DISCUSS', 'wiki_discuss');
 282  define('TABLE_WIKI_MAILCUE', 'wiki_mailcue');
 283  
 284  // Glossary
 285  define('TABLE_GLOSSARY', 'glossary');
 286  
 287  // Notebook
 288  define('TABLE_NOTEBOOK', 'notebook');
 289  
 290  // Message 
 291  define('TABLE_MESSAGE', 'message');
 292  define('TABLE_MESSAGE_ATTACHMENT', 'message_attachment');
 293  
 294  // Metadata
 295  define('TABLE_METADATA', 'metadata');
 296  
 297  
 298  /*
 299  ==============================================================================
 300          DATABASE CLASS
 301          the class and its functions
 302  ==============================================================================
 303  */
 304  
 305  /**
 306   *    @package dokeos.library
 307   */
 308  
 309  class Database {
 310  
 311      /*
 312      -----------------------------------------------------------------------------
 313          Accessor Functions
 314          Usually, you won't need these directly but instead
 315          rely on of the get_xxx_table functions.
 316      -----------------------------------------------------------------------------
 317      */
 318  
 319      /**
 320       *    Returns the name of the main Dokeos database.
 321       */
 322  	public static function get_main_database() {
 323          global $_configuration;
 324          return $_configuration['main_database'];
 325      }
 326  
 327      /**
 328       *    Returns the name of the Dokeos statistics database.
 329       */
 330  	public static function get_statistic_database() {
 331          global $_configuration;
 332          return $_configuration['statistics_database'];
 333      }
 334  
 335      /**
 336       *    Returns the name of the Dokeos SCORM database.
 337       *    @deprecated
 338       */
 339  	public static function get_scorm_database() {
 340          global $_configuration;
 341          return $_configuration['scorm_database'];
 342      }
 343  
 344      /**
 345       *    Returns the name of the database where all the personal stuff of the user is stored
 346       */
 347  	public static function get_user_personal_database() {
 348          global $_configuration;
 349          return $_configuration['user_personal_database'];
 350      }
 351  
 352      /**
 353       *    Returns the name of the main Dokeos database.
 354       */
 355  	public static function get_current_course_database() {
 356          $course_info = api_get_course_info();
 357          return $course_info['dbName'];
 358      }
 359  
 360      /**
 361       *    Returns the glued name of the current course database.
 362       */
 363  	public static function get_current_course_glued_database() {
 364          $course_info = api_get_course_info();
 365          return $course_info['dbNameGlu'];
 366      }
 367  
 368      /**
 369       *    The glue is the string needed between database and table.
 370       *    The trick is: in multiple databases, this is a period (with backticks)
 371       *    In single database, this can be e.g. an underscore so we just fake
 372       *    there are multiple databases and the code can be written independent
 373       *    of the single / multiple database setting.
 374       */
 375  	public static function get_database_glue() {
 376          global $_configuration;
 377          return $_configuration['db_glue'];
 378      }
 379  
 380      /**
 381       *    Returns the database prefix.
 382       *    All created COURSE databases are prefixed with this string.
 383       *
 384       *    TIP: this can be convenient e.g. if you have multiple Dokeos installations
 385       *    on the same physical server.
 386       */
 387  	public static function get_database_name_prefix() {
 388          global $_configuration;
 389          return $_configuration['db_prefix'];
 390      }
 391  
 392      /**
 393       *    Returns the course table prefix for single database.
 394       *    Not certain exactly when this is used.
 395       *    Do research.
 396       *    It's used in local.inc.php.
 397       */
 398  	public static function get_course_table_prefix() {
 399          global $_configuration;
 400          return $_configuration['table_prefix'];
 401      }
 402  
 403      /*
 404      -----------------------------------------------------------------------------
 405          Table Name functions
 406          use these functions to get a table name for queries,
 407          instead of constructing them yourself.
 408  
 409          Backticks automatically surround the result,
 410          e.g. `COURSE_NAME`.`link`
 411          so the queries can look cleaner.
 412  
 413          Example:
 414          $table = Database::get_course_table(TABLE_DOCUMENT);
 415          $sql_query = "SELECT * FROM $table WHERE $condition";
 416          $sql_result = Database::query($sql_query, __FILE__, __LINE__);
 417          $result = Database::fetch_array($sql_result);
 418      -----------------------------------------------------------------------------
 419      */
 420  
 421      /**
 422       * A more generic function than the other get_main_xxx_table functions,
 423       * this one can return the correct complete name of any table of the main database of which you pass
 424       * the short name as a parameter.
 425       * Please define table names as constants in this library and use them
 426       * instead of directly using magic words in your tool code.
 427       *
 428       * @param string $short_table_name, the name of the table
 429       */
 430  	public static function get_main_table($short_table_name) {
 431          return self::format_table_name(self::get_main_database(), $short_table_name);
 432      }
 433  
 434      /**
 435       * A more generic function than the older get_course_xxx_table functions,
 436       * this one can return the correct complete name of any course table of which you pass
 437       * the short name as a parameter.
 438       * Please define table names as constants in this library and use them
 439       * instead of directly using magic words in your tool code.
 440       *
 441       * @param string $short_table_name, the name of the table
 442       * @param string $database_name, optional, name of the course database
 443       * - if you don't specify this, you work on the current course.
 444       */
 445  	public static function get_course_table($short_table_name, $database_name = '') {
 446          return self::format_glued_course_table_name(self::fix_database_parameter($database_name), $short_table_name);
 447      }
 448  
 449      /**
 450       * Get a complete course table name from a course code
 451       *
 452       * @param string $course_code
 453       * @param string $table the name of the table
 454       */
 455  	public static function get_course_table_from_code($course_code, $table) {
 456          $course_table = self::get_main_table(TABLE_MAIN_COURSE);
 457          $course_cat_table = self::get_main_table(TABLE_MAIN_CATEGORY);
 458          $result = self::fetch_array(self::query(
 459              "SELECT $course_table.db_name, $course_cat_table.code
 460              FROM $course_table
 461                  LEFT JOIN $course_cat_table
 462                      ON $course_table.category_code =  $course_cat_table.code
 463              WHERE $course_table.code = '$course_code'
 464              LIMIT 1", __FILE__, __LINE__));
 465          return sprintf("%s.%s", $result[0], $table);
 466      }
 467  
 468      /**
 469       * This generic function returns the correct and complete name of any statistic table
 470       * of which you pass the short name as a parameter.
 471       * Please define table names as constants in this library and use them
 472       * instead of directly using magic words in your tool code.
 473       *
 474       * @param string $short_table_name, the name of the table
 475       */
 476  	public static function get_statistic_table($short_table_name) {
 477          return self::format_table_name(self::get_statistic_database(), $short_table_name);
 478      }
 479  
 480      /**
 481       * This generic function returns the correct and complete name of any scorm
 482       * table of which you pass the short name as a parameter. Please define
 483       * table names as constants in this library and use them instead of directly
 484       * using magic words in your tool code.
 485       *
 486       * @param string $short_table_name, the name of the table
 487       */
 488  	public static function get_scorm_table($short_table_name) {
 489          return self::format_table_name(self::get_scorm_database(), $short_table_name);
 490      }
 491  
 492      /**
 493       * This generic function returns the correct and complete name of any scorm
 494       * table of which you pass the short name as a parameter. Please define
 495       * table names as constants in this library and use them instead of directly
 496       * using magic words in your tool code.
 497       *
 498       * @param string $short_table_name, the name of the table
 499       */
 500  	public static function get_user_personal_table($short_table_name) {
 501          return self::format_table_name(self::get_user_personal_database(), $short_table_name);
 502      }
 503  
 504      /*
 505      -----------------------------------------------------------------------------
 506          Query Functions
 507          these execute a query and return the result(s).
 508      -----------------------------------------------------------------------------
 509      */
 510  
 511      /**
 512       *    @return a list (array) of all courses.
 513       *     @todo shouldn't this be in the course.lib.php script?
 514       */
 515  	public static function get_course_list() {
 516          $table = self::get_main_table(TABLE_MAIN_COURSE);
 517          return self::store_result(self::query("SELECT * FROM $table", __FILE__, __LINE__));
 518      }
 519  
 520      /**
 521       *    Returns an array with all database fields for the specified course.
 522       *
 523       *    @param the real (system) code of the course (ID from inside the main course table)
 524       *     @todo shouldn't this be in the course.lib.php script?
 525       */
 526  	public static function get_course_info($course_code) {
 527          $course_code = self::escape_string($course_code);
 528          $table = self::get_main_table(TABLE_MAIN_COURSE);
 529          $result = self::generate_abstract_course_field_names(
 530              self::fetch_array(self::query("SELECT * FROM $table WHERE `code` = '$course_code'", __FILE__, __LINE__)));
 531          return $result === false ? array('db_name' => '') : $result;
 532      }
 533  
 534      /**
 535       *    @param $user_id (integer): the id of the user
 536       *    @return $user_info (array): user_id, lastname, firstname, username, email, ...
 537       *    @author Patrick Cool <patrick.cool@UGent.be>, expanded to get info for any user
 538       *    @author Roan Embrechts, first version + converted to Database API
 539       *    @version 30 September 2004
 540       *    @desc find all the information about a specified user. Without parameter this is the current user.
 541       *     @todo shouldn't this be in the user.lib.php script?
 542       */
 543  	public static function get_user_info_from_id($user_id = '') {
 544          if (empty($user_id)) {
 545              return $GLOBALS['_user'];
 546          }
 547          $table = self::get_main_table(TABLE_MAIN_USER);
 548          $user_id = self::escape_string($user_id);
 549          return self::generate_abstract_user_field_names(
 550              self::fetch_array(self::query("SELECT * FROM $table WHERE user_id = '$user_id'", __FILE__, __LINE__)));
 551      }
 552  
 553      /**
 554       *    This creates an abstraction layer between database field names
 555       *    and field names expected in code.
 556       *
 557       *    This helps when changing database names.
 558       *    It's also useful now to get rid of the 'franglais'.
 559       *
 560       *    @todo    add more array entries to abstract course info from field names
 561       *    @author    Roan Embrechts
 562       *
 563       *     @todo what's the use of this function. I think this is better removed.
 564       *           There should be consistency in the variable names and the use throughout the scripts
 565       *           for the database name we should consistently use or db_name or database (db_name probably being the better one)
 566       */
 567  	public static function generate_abstract_course_field_names($result_array) {
 568          $visual_code = isset($result_array['visual_code']) ? $result_array['visual_code'] : null;
 569          $code        = isset($result_array['code']) ? $result_array['code'] : null;
 570          $title       = isset($result_array['title']) ? $result_array['title'] : null;
 571          $db_name     = isset($result_array['db_name']) ? $result_array['db_name'] : null;
 572          $category_code= isset($result_array['category_code']) ? $result_array['category_code'] : null;
 573          $result_array['official_code'] = $visual_code;
 574          $result_array['visual_code']   = $visual_code;
 575          $result_array['real_code']     = $code;
 576          $result_array['system_code']   = $code;
 577          $result_array['title']         = $title;
 578          $result_array['database']      = $db_name;
 579          $result_array['faculty']       = $category_code;
 580          //$result_array['directory'] = $result_array['directory'];
 581          /*
 582          still to do: (info taken from local.inc.php)
 583  
 584          $_course['id'          ]         = $cData['cours_id'         ]; //auto-assigned integer
 585          $_course['name'        ]         = $cData['title'            ];
 586          $_course['official_code']        = $cData['visual_code'        ]; // use in echo
 587          $_course['sysCode'     ]         = $cData['code'             ]; // use as key in db
 588          $_course['path'        ]         = $cData['directory'        ]; // use as key in path
 589          $_course['dbName'      ]         = $cData['db_name'           ]; // use as key in db list
 590          $_course['dbNameGlu'   ]         = $_configuration['table_prefix'] . $cData['dbName'] . $_configuration['db_glue']; // use in all queries
 591          $_course['titular'     ]         = $cData['tutor_name'       ];
 592          $_course['language'    ]         = $cData['course_language'   ];
 593          $_course['extLink'     ]['url' ] = $cData['department_url'    ];
 594          $_course['extLink'     ]['name'] = $cData['department_name'];
 595          $_course['categoryCode']         = $cData['faCode'           ];
 596          $_course['categoryName']         = $cData['faName'           ];
 597  
 598          $_course['visibility'  ]         = (bool) ($cData['visibility'] == 2 || $cData['visibility'] == 3);
 599          $_course['registrationAllowed']  = (bool) ($cData['visibility'] == 1 || $cData['visibility'] == 2);
 600          */
 601          return $result_array;
 602      }
 603  
 604      /**
 605       *    This creates an abstraction layer between database field names
 606       *    and field names expected in code.
 607       *
 608       *    This helps when changing database names.
 609       *    It's also useful now to get rid of the 'franglais'.
 610       *
 611       *    @todo add more array entries to abstract user info from field names
 612       *    @author Roan Embrechts
 613       *    @author Patrick Cool
 614       *
 615       *     @todo what's the use of this function. I think this is better removed.
 616       *         There should be consistency in the variable names and the use throughout the scripts
 617       */
 618  	public static function generate_abstract_user_field_names($result_array) {
 619          $result_array['firstName']         = $result_array['firstname'];
 620          $result_array['lastName']         = $result_array['lastname'];
 621          $result_array['mail']             = $result_array['email'];
 622          #$result_array['picture_uri']     = $result_array['picture_uri'];
 623          #$result_array ['user_id']        = $result_array['user_id'];
 624          return $result_array;
 625      }
 626  
 627      /*
 628      -----------------------------------------------------------------------------
 629          Private Functions
 630          You should not access these from outside the class
 631          No effort is made to keep the names / results the same.
 632      -----------------------------------------------------------------------------
 633      */
 634  
 635      /**
 636       *    Glues a course database.
 637       *    glue format from local.inc.php.
 638       */
 639  	public static function glue_course_database_name($database_name) {
 640          return self::get_course_table_prefix().$database_name.self::get_database_glue();
 641      }
 642  
 643      /**
 644       *    @param string $database_name, can be empty to use current course db
 645       *
 646       *    @return the glued parameter if it is not empty,
 647       *    or the current course database (glued) if the parameter is empty.
 648       */
 649  	public static function fix_database_parameter($database_name) {
 650          if (empty($database_name)) {
 651              $course_info = api_get_course_info();
 652              return $course_info['dbNameGlu'];
 653          }
 654          return self::glue_course_database_name($database_name);
 655      }
 656  
 657      /**
 658       *    Structures a course database and table name to ready them
 659       *    for querying. The course database parameter is considered glued:
 660       *    e.g. COURSE001`.`
 661       */
 662  	public static function format_glued_course_table_name($database_name_with_glue, $table) {
 663          //$course_info = api_get_course_info();
 664          return '`'.$database_name_with_glue.$table.'`';
 665      }
 666  
 667      /**
 668       *    Structures a database and table name to ready them
 669       *    for querying. The database parameter is considered not glued,
 670       *    just plain e.g. COURSE001
 671       */
 672  	public static function format_table_name($database, $table) {
 673          return '`'.$database.'`.`'.$table.'`';
 674      }
 675  
 676      /**
 677       * Count the number of rows in a table
 678       * @param string $table The table of which the rows should be counted
 679       * @return int The number of rows in the given table.
 680       */
 681  	public static function count_rows($table) {
 682          $obj = self::fetch_object(self::query("SELECT COUNT(*) AS n FROM $table", __FILE__, __LINE__));
 683          return $obj->n;
 684      }
 685  
 686      /**
 687       * Escapes a string to insert into the database as text
 688       * @param string        The string to escape
 689       * @return string        The escaped string
 690       * @author Yannick Warnier
 691       * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
 692       */
 693  	public static function escape_string($string) {
 694          return get_magic_quotes_gpc() ? mysql_real_escape_string(stripslashes($string)) : mysql_real_escape_string($string);
 695      }
 696  
 697      /**
 698       * Gets the array from a SQL result (as returned by Database::query) - help achieving database independence
 699       * @param resource        The result from a call to sql_query (e.g. Database::query)
 700       * @param string        Optional: "ASSOC","NUM" or "BOTH", as the constant used in mysql_fetch_array.
 701       * @return array        Array of results as returned by php
 702       * @author Yannick Warnier
 703       */
 704  	public static function fetch_array($res, $option = 'BOTH') {
 705          return $option == 'ASSOC' ? mysql_fetch_array($res, MYSQL_ASSOC) : ($option == 'NUM' ? mysql_fetch_array($res, MYSQL_NUM) : mysql_fetch_array($res));
 706      }
 707  
 708      /**
 709       * Gets the next row of the result of the SQL query (as returned by Database::query) in an object form
 710       * @param    resource    The result from a call to sql_query (e.g. Database::query)
 711       * @param    string        Optional class name to instanciate
 712       * @param    array        Optional array of parameters
 713       * @return    object        Object of class StdClass or the required class, containing the query result row
 714       * @author    Yannick Warnier
 715       */
 716  	public static function fetch_object($res, $class = null, $params = null) {
 717          return !empty($class) ? (is_array($params) ? mysql_fetch_object($res, $class, $params) : mysql_fetch_object($res,$class)) : mysql_fetch_object($res);
 718      }
 719  
 720      /**
 721       * Gets the array from a SQL result (as returned by Database::query) - help achieving database independence
 722       * @param resource        The result from a call to sql_query (e.g. Database::query)
 723       * @return array        Array of results as returned by php (mysql_fetch_row)
 724       * @author Yannick Warnier
 725       */
 726  	public static function fetch_row($res) {
 727          return mysql_fetch_row($res);
 728      }
 729  
 730      /**
 731       * Gets the number of rows from the last query result - help achieving database independence
 732       * @param resource        The result
 733       * @return integer        The number of rows contained in this result
 734       * @author Yannick Warnier
 735       **/
 736  	public static function num_rows($res) {
 737          return mysql_num_rows($res);
 738      }
 739  
 740  	public static function get_course_chat_connected_table($database_name = '') {
 741          return self::format_glued_course_table_name(self::fix_database_parameter($database_name), CHAT_CONNECTED_TABLE);
 742      }
 743  
 744      /**
 745       * Acts as the relative *_result() function of most DB drivers and fetches a
 746       * specific line and a field
 747       * @param    resource    The database resource to get data from
 748       * @param    integer        The row number
 749       * @param    string        Optional field name or number
 750       * @result    mixed        One cell of the result, or FALSE on error
 751       */
 752  	public static function result($resource, $row, $field = '') {
 753          return mysql_num_rows($resource) > 0 ? (!empty($field) ? mysql_result($resource, $row, $field) : mysql_result($resource, $row)) : null;
 754      }
 755  
 756      /**
 757       * Gets the ID of the last item inserted into the database
 758       *
 759       * @return integer The last ID as returned by the DB function
 760       * @comment This should be updated to use ADODB at some point
 761       */
 762  	public static function insert_id() {
 763          return mysql_insert_id();
 764      }
 765  
 766      /**
 767       * Returns the number of affected rows
 768       * @param    resource    Optional database resource
 769       */
 770  	public static function affected_rows($r = null) {
 771          return !is_null($r) ? mysql_affected_rows($r) : mysql_affected_rows();
 772      }
 773  
 774      /**
 775       * This function returns a resource
 776       * Documentation has been added by Arthur Portugal
 777       * An adaptation for reliable showing error messages has been done by Ivan Tcholakov, 2009
 778       * @author Olivier Brouckaert
 779       * @param string $query - SQL query
 780       * @param string $file - optional, the file path and name of the error (__FILE__)
 781       * @param string $line - optional, the line of the error (__LINE__)
 782       * @return resource - the return value of the query
 783       */
 784  
 785  	public static function query($query, $file = '', $line = 0) {
 786          if (!($result = @mysql_query($query)) && $line && api_get_setting('server_type') != 'production') {
 787              $security_ok = class_exists('Security') && class_exists('HTMLPurifier');
 788              $error = self::error();
 789              $info = '<pre>';
 790              $info .= '<strong>DATABASE ERROR :</strong><br /> ';
 791              $info .= $security_ok ? Security::remove_XSS($error) : api_htmlentities($error, ENT_QUOTES);
 792              $info .= '<br />';
 793              $info .= '<strong>QUERY       :</strong><br /> ';
 794              $info .= $security_ok ? Security::remove_XSS($query) : api_htmlentities($query, ENT_QUOTES);
 795              $info .= '<br />';
 796              $info .= '<strong>FILE        :</strong><br /> ';
 797              $info .= ($file == '' ? ' unknown ' : $file);
 798              $info .= '<br />';
 799              $info .= '<strong>LINE        :</strong><br /> ';
 800              $info .= ($line == 0 ? ' unknown ' : $line);
 801              $info .= '</pre>';
 802              echo $info;
 803          }
 804          return $result;
 805      }
 806  
 807  	public static function error() {
 808          return mysql_error();
 809      }
 810  
 811      /**
 812       * Return course code from one given gradebook category's id
 813       * @param int  Category ID
 814       * @return string  Course code
 815       * @todo move this function in a gradebook-related library
 816       */
 817  	public static function get_course_by_category($category_id) {
 818          $info = self::fetch_array(self::query('SELECT course_code FROM '.self::get_main_table(TABLE_MAIN_GRADEBOOK_CATEGORY).' WHERE id='.$category_id, __FILE__, __LINE__), 'ASSOC');
 819          return $info ? $info['course_code'] : false;
 820      }
 821  
 822      /**
 823       * Stores a query result into an array.
 824       *
 825       * @author Olivier Brouckaert
 826       * @param  resource $result - the return value of the query
 827       * @param  option BOTH, ASSOC, or NUM
 828       * @return array - the value returned by the query
 829       * 
 830       */
 831  	public static function store_result($result, $option = 'BOTH') {
 832          $array = array();
 833          if ($result !== false) { // For isolation from database engine's behaviour.
 834              while ($row = self::fetch_array($result, $option)) {
 835                  $array[] = $row;
 836              }
 837          }
 838          return $array;
 839      }
 840  
 841  
 842      /*
 843      ==============================================================================
 844          DEPRECATED METHODS
 845      ==============================================================================
 846      */
 847  
 848      /**
 849       * @deprecated Use api_get_language_isocode($language) instead.
 850       */
 851  	public static function get_language_isocode($language) {
 852          return api_get_language_isocode($language);
 853      }
 854  
 855      /**
 856       * @deprecated Use Database::insert_id() instead.
 857       */
 858  	public static function get_last_insert_id() {
 859          return mysql_insert_id();
 860      }
 861  
 862  }
 863  //end class Database

title

Description

title

Description

title

Description

title

title

Body