database.lib.php

完美的在线教育系统

<?php // $Id: database.lib.php 15361 2008-05-22 18:25:31Z yannoo $
/* See license terms in /dokeos_license.txt */
/**
==============================================================================
*	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).
*
*	@package dokeos.library
* 	@todo the table constants have all to start with TABLE_
* 		  This is because of the analogy with the tool constants TOOL_
==============================================================================
*/
/*
==============================================================================
		CONSTANTS
==============================================================================
*/
//main database tables
define('TABLE_MAIN_COURSE', 'course');
define('TABLE_MAIN_USER', 'user');
define('TABLE_MAIN_CLASS', 'class');
define('TABLE_MAIN_ADMIN', 'admin');
define('TABLE_MAIN_COURSE_CLASS', 'course_rel_class');
define('TABLE_MAIN_COURSE_USER', 'course_rel_user');
define('TABLE_MAIN_CLASS_USER', 'class_user');
define('TABLE_MAIN_CATEGORY', 'course_category');
define('TABLE_MAIN_COURSE_MODULE', 'course_module');
define('TABLE_MAIN_SYSTEM_ANNOUNCEMENTS', 'sys_announcement');
define('TABLE_MAIN_LANGUAGE', 'language');
define('TABLE_MAIN_SETTINGS_OPTIONS', 'settings_options');
define('TABLE_MAIN_SETTINGS_CURRENT', 'settings_current');
define('TABLE_MAIN_SESSION', 'session');
define('TABLE_MAIN_SESSION_COURSE', 'session_rel_course');
define('TABLE_MAIN_SESSION_USER', 'session_rel_user');
define('TABLE_MAIN_SESSION_CLASS', 'session_rel_class');
define('TABLE_MAIN_SESSION_COURSE_USER', 'session_rel_course_rel_user');
define('TABLE_MAIN_SHARED_SURVEY', 'shared_survey');
define('TABLE_MAIN_SHARED_SURVEY_QUESTION', 'shared_survey_question');
define('TABLE_MAIN_SHARED_SURVEY_QUESTION_OPTION', 'shared_survey_question_option');
define('TABLE_MAIN_TEMPLATES', 'templates');
define('TABLE_MAIN_OPENID_ASSOCIATION','openid_association');
define('TABLE_MAIN_GRADEBOOK_CATEGORY', 	'gradebook_category');
define('TABLE_MAIN_GRADEBOOK_EVALUATION', 	'gradebook_evaluation');
define('TABLE_MAIN_GRADEBOOK_RESULT', 		'gradebook_result');
define('TABLE_MAIN_GRADEBOOK_LINK', 		'gradebook_link');
define('TABLE_MAIN_GRADEBOOK_SCORE_DISPLAY','gradebook_score_display');
define('TABLE_MAIN_USER_FIELD',			'user_field');
define('TABLE_MAIN_USER_FIELD_OPTIONS',	'user_field_options');
define('TABLE_MAIN_USER_FIELD_VALUES',	'user_field_values');
define('TABLE_MAIN_ACCESS_URL', 'access_url');

//statistic database tables
define('TABLE_STATISTIC_TRACK_E_LASTACCESS', 'track_e_lastaccess');
define('TABLE_STATISTIC_TRACK_E_ACCESS', 'track_e_access');
define('TABLE_STATISTIC_TRACK_E_LOGIN', 'track_e_login');
define('TABLE_STATISTIC_TRACK_E_DOWNLOADS', 'track_e_downloads');
define('TABLE_STATISTIC_TRACK_E_LINKS', 'track_e_links');
define('TABLE_STATISTIC_TRACK_E_ONLINE', 'track_e_online');
define('TABLE_STATISTIC_TRACK_E_HOTPOTATOES', 'track_e_hotpotatoes');
define('TABLE_STATISTIC_TRACK_E_COURSE_ACCESS', 'track_e_course_access');
define('TABLE_STATISTIC_TRACK_E_EXERCICES', 'track_e_exercices');
define('TABLE_STATISTIC_TRACK_E_ATTEMPT', 'track_e_attempt');
define('TABLE_STATISTIC_TRACK_E_DEFAULT', 'track_e_default');
define('TABLE_STATISTIC_TRACK_E_UPLOADS','track_e_uploads');
define('TABLE_STATISTIC_TRACK_E_HOTSPOT','track_e_hotspot');

//scorm database tables
define('TABLE_SCORM_MAIN', 'scorm_main');
define('TABLE_SCORM_SCO_DATA', 'scorm_sco_data');

//course tables
define('TABLE_AGENDA', 'calendar_event');
define('TABLE_AGENDA_REPEAT', 'calendar_event_repeat');
define('TABLE_AGENDA_REPEAT_NOT', 'calendar_event_repeat_not');
define('TABLE_ANNOUNCEMENT', 'announcement');
define('TABLE_CHAT_CONNECTED', 'chat_connected'); // @todo: probably no longer in use !!!
define('TABLE_COURSE_DESCRIPTION', 'course_description');
define('TABLE_DOCUMENT', 'document');
define('TABLE_ITEM_PROPERTY', 'item_property');
define('TABLE_LINK', 'link');
define('TABLE_LINK_CATEGORY', 'link_category');
define('TABLE_TOOL_LIST', 'tool');
define('TABLE_TOOL_INTRO', 'tool_intro');
define('TABLE_SCORMDOC', 'scormdocument');
define('TABLE_STUDENT_PUBLICATION', 'student_publication');
define('CHAT_CONNECTED_TABLE', 'chat_connected');

//course forum tables
define('TABLE_FORUM_CATEGORY','forum_category');
define('TABLE_FORUM','forum_forum');
define('TABLE_FORUM_THREAD','forum_thread');
define('TABLE_FORUM_POST','forum_post');
define('TABLE_FORUM_ATTACHMENT','forum_attachment');
//course group tables
define('TABLE_GROUP', 'group_info');
define('TABLE_GROUP_USER', 'group_rel_user');
define('TABLE_GROUP_TUTOR', 'group_rel_tutor');
define('TABLE_GROUP_CATEGORY', 'group_category');
//course dropbox tables
define('TABLE_DROPBOX_CATEGORY','dropbox_category');
define('TABLE_DROPBOX_FEEDBACK','dropbox_feedback');
define('TABLE_DROPBOX_POST','dropbox_post');
define('TABLE_DROPBOX_FILE','dropbox_file');
define('TABLE_DROPBOX_PERSON','dropbox_person');
//course quiz tables
define('TABLE_QUIZ_QUESTION', 'quiz_question');
define('TABLE_QUIZ_TEST', 'quiz');
define('TABLE_QUIZ_ANSWER', 'quiz_answer');
define('TABLE_QUIZ_TEST_QUESTION', 'quiz_rel_question');
//linked resource table
define('TABLE_LINKED_RESOURCES', 'resource');
//learnpath tables
define('TABLE_LEARNPATH_MAIN', 'learnpath_main');
define('TABLE_LEARNPATH_CHAPTER', 'learnpath_chapter');
define('TABLE_LEARNPATH_ITEM', 'learnpath_item');
define('TABLE_LEARNPATH_USER', 'learnpath_user');
//new scorm tables
define('TABLE_LP_MAIN', 'lp');
define('TABLE_LP_ITEM', 'lp_item');
define('TABLE_LP_VIEW', 'lp_view');
define('TABLE_LP_ITEM_VIEW', 'lp_item_view');
define('TABLE_LP_IV_INTERACTION', 'lp_iv_interaction'); // IV = Item View
// Smartblogs (Kevin Van Den Haute::[e-mail])
// permission tables
define('TABLE_PERMISSION_USER', 'permission_user');
define('TABLE_PERMISSION_TASK', 'permission_task');
define('TABLE_PERMISSION_GROUP', 'permission_group');
// roles tables
define('TABLE_ROLE', 'role');
define('TABLE_ROLE_PERMISSION', 'role_permissions');
define('TABLE_ROLE_USER', 'role_user');
define('TABLE_ROLE_GROUP', 'role_group');
// blogs tables
define('TABLE_BLOGS', 'blog');
define('TABLE_BLOGS_POSTS', 'blog_post');
define('TABLE_BLOGS_COMMENTS', 'blog_comment');
define('TABLE_BLOGS_REL_USER', 'blog_rel_user');
define('TABLE_BLOGS_TASKS', 'blog_task');
define('TABLE_BLOGS_TASKS_REL_USER', 'blog_task_rel_user');
define('TABLE_BLOGS_RATING', 'blog_rating');
define('TABLE_BLOGS_ATTACHMENT', 'blog_attachment');
define('TABLE_BLOGS_TASKS_PERMISSIONS', 'permission_task');
//end of Smartblogs
// user information tables
define('TABLE_USER_INFO', 'userinfo_def');
define('TABLE_USER_INFO_CONTENT', 'userinfo_content');
// course settings table
define('TABLE_COURSE_SETTING', 'course_setting');
// course online tables
define('TABLE_ONLINE_LINK', 'online_link');
define('TABLE_ONLINE_CONNECTED', 'online_connected');
// dokeos_user database
define('TABLE_PERSONAL_AGENDA', 'personal_agenda');
define('TABLE_PERSONAL_AGENDA_REPEAT', 'personal_agenda_repeat');
define('TABLE_PERSONAL_AGENDA_REPEAT_NOT', 'personal_agenda_repeat_not');
define('TABLE_USER_COURSE_CATEGORY', 'user_course_category');
//Survey
// @todo: are these MAIN tables or course tables ?
define('TABLE_MAIN_SURVEY', 'survey');
define('TABLE_MAIN_GROUP', 'survey_group');
define('TABLE_MAIN_SURVEYQUESTION', 'questions');
// SURVEY
define('TABLE_SURVEY', 'survey');
define('TABLE_SURVEY_QUESTION', 'survey_question');
define('TABLE_SURVEY_QUESTION_OPTION', 'survey_question_option');
define('TABLE_SURVEY_INVITATION', 'survey_invitation');
define('TABLE_SURVEY_ANSWER', 'survey_answer');


/*
==============================================================================
		DATABASE CLASS
		the class and its functions
==============================================================================
*/
/**
 *	@package dokeos.library
 */
class Database
{
	/*
	-----------------------------------------------------------------------------
		Accessor Functions
		Usually, you won't need these directly but instead
		rely on of the get_xxx_table functions.
	-----------------------------------------------------------------------------
	*/
	/**
	 *	Returns the name of the main Dokeos database.
	 */
	function get_main_database()
	{
		global $_configuration;
		return $_configuration['main_database'];
	}
	/**
	*	Returns the name of the Dokeos statistics database.
	*/
	function get_statistic_database()
	{
		global $_configuration;
		return $_configuration['statistics_database'];
	}
	/**
	*	Returns the name of the Dokeos SCORM database.
	*/
	function get_scorm_database()
	{
		global $_configuration;
		return $_configuration['scorm_database'];
	}
	/**
	*	Returns the name of the database where all the personal stuff of the user is stored
	*/
	function get_user_personal_database()
	{
		global $_configuration;
		return $_configuration['user_personal_database'];
	}
	/**
	*	Returns the name of the main Dokeos database.
	*/
	function get_current_course_database()
	{
		$course_info = api_get_course_info();
		return $course_info["dbName"];
	}
	/**
	*	Returns the glued name of the current course database.
	*/
	function get_current_course_glued_database()
	{
		$course_info = api_get_course_info();
		return $course_info["dbNameGlu"];
	}
	/**
	*	The glue is the string needed between database and table.
	*	The trick is: in multiple databases, this is a period (with backticks)
	*	In single database, this can be e.g. an underscore so we just fake
	*	there are multiple databases and the code can be written independent
	*	of the single / multiple database setting.
	*/
	function get_database_glue()
	{
		global $_configuration;
		return $_configuration['db_glue'];
	}
	/**
	*	Returns the database prefix.
	*	All created COURSE databases are prefixed with this string.
	*
	*	TIP: this can be convenient e.g. if you have multiple Dokeos installations
	*	on the same physical server.
	*/
	function get_database_name_prefix()
	{
		global $_configuration;
		return $_configuration['db_prefix'];
	}
	/**
	*	Returns the course table prefix for single database.
	*	Not certain exactly when this is used.
	*	Do research.
	*	It's used in local.inc.php.
	*/
	function get_course_table_prefix()
	{
		global $_configuration;
		return $_configuration['table_prefix'];
	}
	/*
	-----------------------------------------------------------------------------
		Table Name functions
		use these functions to get a table name for queries,
		instead of constructing them yourself.

		Backticks automatically surround the result,
		e.g. `COURSE_NAME`.`link`
		so the queries can look cleaner.

		Example:
		$table = Database::get_course_table(TABLE_DOCUMENT);
		$sql_query = "SELECT * FROM $table WHERE $condition";
		$sql_result = api_sql_query($sql_query,__FILE__,__LINE__);
		$result = mysql_fetch_array($sql_result);
	-----------------------------------------------------------------------------
	*/
	/**
	 * A more generic function than the other get_main_xxx_table functions,
	 * this one can return the correct complete name of any table of the main database of which you pass
	 * the short name as a parameter.
	 * Please define table names as constants in this library and use them
	 * instead of directly using magic words in your tool code.
	 *
	 * @param string $short_table_name, the name of the table
	 */
	function get_main_table($short_table_name)
	{
		$database = Database::get_main_database();
		return Database::format_table_name($database, $short_table_name);
	}
	/**
	 * A more generic function than the older get_course_xxx_table functions,
	 * this one can return the correct complete name of any course table of which you pass
	 * the short name as a parameter.
	 * Please define table names as constants in this library and use them
	 * instead of directly using magic words in your tool code.
	 *
	 * @param string $short_table_name, the name of the table
	 * @param string $database_name, optional, name of the course database
	 * - if you don't specify this, you work on the current course.
	 */
	function get_course_table($short_table_name, $database_name = '')
	{
		$database_name_with_glue = Database::fix_database_parameter($database_name);
		return Database::format_glued_course_table_name($database_name_with_glue, $short_table_name);
	}
	/**
	 * This generic function returns the correct and complete name of any statistic table
	 * of which you pass the short name as a parameter.
	 * Please define table names as constants in this library and use them
	 * instead of directly using magic words in your tool code.
	 *
	 * @param string $short_table_name, the name of the table
	 */
	function get_statistic_table($short_table_name)
	{
		$database = Database::get_statistic_database();
		return Database::format_table_name($database, $short_table_name);
	}
	/**
	 * This generic function returns the correct and complete name of any scorm
	 * table of which you pass the short name as a parameter. Please define
	 * table names as constants in this library and use them instead of directly
	 * using magic words in your tool code.
	 *
	 * @param string $short_table_name, the name of the table
	 */
	function get_scorm_table($short_table_name)
	{
		$database = Database::get_scorm_database();
		return Database::format_table_name($database, $short_table_name);
	}
	/**
	 * This generic function returns the correct and complete name of any scorm
	 * table of which you pass the short name as a parameter. Please define
	 * table names as constants in this library and use them instead of directly
	 * using magic words in your tool code.
	 *
	 * @param string $short_table_name, the name of the table
	 */
	function get_user_personal_table($short_table_name)
	{
		$database = Database::get_user_personal_database();
		return Database::format_table_name($database, $short_table_name);
	}
	/**