database.lib.php 32 KB

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