database.lib.php 39 KB

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