database.lib.php 39 KB

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