database.lib.php 53 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * This is the main database library for Chamilo.
  5. * Include/require it in your code to use its functionality.
  6. * Because this library contains all the basic database calls, it could be
  7. * replaced by another library for say, PostgreSQL, to actually use Chamilo
  8. * with another database (this is not ready yet because a lot of code still
  9. * uses the MySQL database functions extensively).
  10. *
  11. * @package chamilo.library
  12. * @todo the table constants have all to start with TABLE_
  13. * This is because of the analogy with the tool constants TOOL_
  14. */
  15. /* CONSTANTS */
  16. // Main database tables
  17. define('TABLE_MAIN_COURSE', 'course');
  18. define('TABLE_MAIN_USER', 'user');
  19. define('TABLE_MAIN_CLASS', 'class');
  20. define('TABLE_MAIN_ADMIN', 'admin');
  21. define('TABLE_MAIN_COURSE_CLASS', 'course_rel_class');
  22. define('TABLE_MAIN_COURSE_USER', 'course_rel_user');
  23. define('TABLE_MAIN_CLASS_USER', 'class_user');
  24. define('TABLE_MAIN_CATEGORY', 'course_category');
  25. define('TABLE_MAIN_COURSE_MODULE', 'course_module');
  26. define('TABLE_MAIN_SYSTEM_ANNOUNCEMENTS', 'sys_announcement');
  27. define('TABLE_MAIN_LANGUAGE', 'language');
  28. define('TABLE_MAIN_SETTINGS_OPTIONS', 'settings_options');
  29. define('TABLE_MAIN_SETTINGS_CURRENT', 'settings_current');
  30. define('TABLE_MAIN_SESSION', 'session');
  31. define('TABLE_MAIN_SESSION_CATEGORY', 'session_category');
  32. define('TABLE_MAIN_SESSION_COURSE', 'session_rel_course');
  33. define('TABLE_MAIN_SESSION_USER', 'session_rel_user');
  34. define('TABLE_MAIN_SESSION_CLASS', 'session_rel_class');
  35. define('TABLE_MAIN_SESSION_COURSE_USER', 'session_rel_course_rel_user');
  36. define('TABLE_MAIN_SHARED_SURVEY', 'shared_survey');
  37. define('TABLE_MAIN_SHARED_SURVEY_QUESTION', 'shared_survey_question');
  38. define('TABLE_MAIN_SHARED_SURVEY_QUESTION_OPTION', 'shared_survey_question_option');
  39. define('TABLE_MAIN_TEMPLATES', 'templates');
  40. define('TABLE_MAIN_SYSTEM_TEMPLATE', 'system_template');
  41. define('TABLE_MAIN_OPENID_ASSOCIATION', 'openid_association');
  42. // Gradebook
  43. define('TABLE_MAIN_GRADEBOOK_CATEGORY', 'gradebook_category');
  44. define('TABLE_MAIN_GRADEBOOK_EVALUATION', 'gradebook_evaluation');
  45. define('TABLE_MAIN_GRADEBOOK_LINKEVAL_LOG', 'gradebook_linkeval_log');
  46. define('TABLE_MAIN_GRADEBOOK_RESULT', 'gradebook_result');
  47. define('TABLE_MAIN_GRADEBOOK_RESULT_LOG', 'gradebook_result_log');
  48. define('TABLE_MAIN_GRADEBOOK_LINK', 'gradebook_link');
  49. define('TABLE_MAIN_GRADEBOOK_SCORE_DISPLAY','gradebook_score_display');
  50. define('TABLE_MAIN_GRADEBOOK_CERTIFICATE', 'gradebook_certificate');
  51. //Profiling
  52. define('TABLE_MAIN_USER_FIELD', 'user_field');
  53. define('TABLE_MAIN_USER_FIELD_OPTIONS', 'user_field_options');
  54. define('TABLE_MAIN_USER_FIELD_VALUES', 'user_field_values');
  55. //User tags
  56. define('TABLE_MAIN_TAG', 'tag');
  57. define('TABLE_MAIN_USER_REL_TAG', 'user_rel_tag');
  58. //User groups
  59. define('TABLE_MAIN_GROUP', 'groups');
  60. define('TABLE_MAIN_USER_REL_GROUP', 'group_rel_user');
  61. define('TABLE_MAIN_GROUP_REL_TAG', 'group_rel_tag');
  62. // Search engine
  63. define('TABLE_MAIN_SPECIFIC_FIELD', 'specific_field');
  64. define('TABLE_MAIN_SPECIFIC_FIELD_VALUES', 'specific_field_values');
  65. define('TABLE_MAIN_SEARCH_ENGINE_REF', 'search_engine_ref');
  66. // Access URLs
  67. define('TABLE_MAIN_ACCESS_URL', 'access_url');
  68. define('TABLE_MAIN_ACCESS_URL_REL_USER', 'access_url_rel_user');
  69. define('TABLE_MAIN_ACCESS_URL_REL_COURSE', 'access_url_rel_course');
  70. define('TABLE_MAIN_ACCESS_URL_REL_SESSION', 'access_url_rel_session');
  71. // Global calendar
  72. define('TABLE_MAIN_SYSTEM_CALENDAR', 'sys_calendar');
  73. // Reservation System
  74. define('TABLE_MAIN_RESERVATION_ITEM', 'reservation_item');
  75. define('TABLE_MAIN_RESERVATION_RESERVATION', 'reservation_main');
  76. define('TABLE_MAIN_RESERVATION_SUBSCRIBTION', 'reservation_subscription');
  77. define('TABLE_MAIN_RESERVATION_CATEGORY', 'reservation_category');
  78. define('TABLE_MAIN_RESERVATION_ITEM_RIGHTS', 'reservation_item_rights');
  79. // Social networking
  80. define('TABLE_MAIN_USER_REL_USER', 'user_rel_user');
  81. define('TABLE_MAIN_USER_FRIEND_RELATION_TYPE', 'user_friend_relation_type');
  82. // Web services
  83. define('TABLE_MAIN_USER_API_KEY', 'user_api_key');
  84. define('TABLE_MAIN_COURSE_FIELD', 'course_field');
  85. define('TABLE_MAIN_COURSE_FIELD_VALUES', 'course_field_values');
  86. define('TABLE_MAIN_SESSION_FIELD', 'session_field');
  87. define('TABLE_MAIN_SESSION_FIELD_VALUES', 'session_field_values');
  88. // Message
  89. define('TABLE_MAIN_MESSAGE', 'message');
  90. // Term and conditions
  91. define('TABLE_MAIN_LEGAL', 'legal');
  92. // Dashboard blocks plugin
  93. define('TABLE_MAIN_BLOCK', 'block');
  94. // Statistic database tables
  95. define('TABLE_STATISTIC_TRACK_E_LASTACCESS', 'track_e_lastaccess');
  96. define('TABLE_STATISTIC_TRACK_E_ACCESS', 'track_e_access');
  97. define('TABLE_STATISTIC_TRACK_E_LOGIN', 'track_e_login');
  98. define('TABLE_STATISTIC_TRACK_E_DOWNLOADS', 'track_e_downloads');
  99. define('TABLE_STATISTIC_TRACK_E_LINKS', 'track_e_links');
  100. define('TABLE_STATISTIC_TRACK_E_ONLINE', 'track_e_online');
  101. define('TABLE_STATISTIC_TRACK_E_HOTPOTATOES', 'track_e_hotpotatoes');
  102. define('TABLE_STATISTIC_TRACK_E_COURSE_ACCESS', 'track_e_course_access');
  103. define('TABLE_STATISTIC_TRACK_E_EXERCICES', 'track_e_exercices');
  104. define('TABLE_STATISTIC_TRACK_E_ATTEMPT', 'track_e_attempt');
  105. define('TABLE_STATISTIC_TRACK_E_ATTEMPT_RECORDING', 'track_e_attempt_recording');
  106. define('TABLE_STATISTIC_TRACK_E_DEFAULT', 'track_e_default');
  107. define('TABLE_STATISTIC_TRACK_E_UPLOADS', 'track_e_uploads');
  108. define('TABLE_STATISTIC_TRACK_E_HOTSPOT', 'track_e_hotspot');
  109. define('TABLE_STATISTIC_TRACK_E_ITEM_PROPERTY', 'track_e_item_property');
  110. // SCORM database tables
  111. define('TABLE_SCORM_MAIN', 'scorm_main');
  112. define('TABLE_SCORM_SCO_DATA', 'scorm_sco_data');
  113. // Course tables
  114. define('TABLE_AGENDA', 'calendar_event');
  115. define('TABLE_AGENDA_REPEAT', 'calendar_event_repeat');
  116. define('TABLE_AGENDA_REPEAT_NOT', 'calendar_event_repeat_not');
  117. define('TABLE_AGENDA_ATTACHMENT', 'calendar_event_attachment');
  118. define('TABLE_ANNOUNCEMENT', 'announcement');
  119. define('TABLE_ANNOUNCEMENT_ATTACHMENT', 'announcement_attachment');
  120. define('TABLE_CHAT_CONNECTED', 'chat_connected'); // @todo: probably no longer in use !!!
  121. define('TABLE_COURSE_DESCRIPTION', 'course_description');
  122. define('TABLE_DOCUMENT', 'document');
  123. define('TABLE_ITEM_PROPERTY', 'item_property');
  124. define('TABLE_LINK', 'link');
  125. define('TABLE_LINK_CATEGORY', 'link_category');
  126. define('TABLE_TOOL_LIST', 'tool');
  127. define('TABLE_TOOL_INTRO', 'tool_intro');
  128. define('TABLE_SCORMDOC', 'scormdocument');
  129. define('TABLE_STUDENT_PUBLICATION', 'student_publication');
  130. define('TABLE_STUDENT_PUBLICATION_ASSIGNMENT', 'student_publication_assignment');
  131. define('CHAT_CONNECTED_TABLE', 'chat_connected');
  132. // Course forum tables
  133. define('TABLE_FORUM_CATEGORY', 'forum_category');
  134. define('TABLE_FORUM', 'forum_forum');
  135. define('TABLE_FORUM_THREAD', 'forum_thread');
  136. define('TABLE_FORUM_POST', 'forum_post');
  137. define('TABLE_FORUM_ATTACHMENT', 'forum_attachment');
  138. define('TABLE_FORUM_MAIL_QUEUE', 'forum_mailcue');
  139. define('TABLE_FORUM_THREAD_QUALIFY', 'forum_thread_qualify');
  140. define('TABLE_FORUM_THREAD_QUALIFY_LOG', 'forum_thread_qualify_log');
  141. define('TABLE_FORUM_NOTIFICATION', 'forum_notification');
  142. // Course group tables
  143. define('TABLE_GROUP', 'group_info');
  144. define('TABLE_GROUP_USER', 'group_rel_user');
  145. define('TABLE_GROUP_TUTOR', 'group_rel_tutor');
  146. define('TABLE_GROUP_CATEGORY', 'group_category');
  147. // Course dropbox tables
  148. define('TABLE_DROPBOX_CATEGORY','dropbox_category');
  149. define('TABLE_DROPBOX_FEEDBACK','dropbox_feedback');
  150. define('TABLE_DROPBOX_POST', 'dropbox_post');
  151. define('TABLE_DROPBOX_FILE', 'dropbox_file');
  152. define('TABLE_DROPBOX_PERSON', 'dropbox_person');
  153. // Course quiz (or test, or exercice) tables
  154. define('TABLE_QUIZ_QUESTION', 'quiz_question');
  155. define('TABLE_QUIZ_TEST', 'quiz');
  156. define('TABLE_QUIZ_ANSWER', 'quiz_answer');
  157. define('TABLE_QUIZ_TEST_QUESTION', 'quiz_rel_question');
  158. // Linked resource table
  159. define('TABLE_LINKED_RESOURCES', 'resource');
  160. // New SCORM tables
  161. define('TABLE_LP_MAIN', 'lp');
  162. define('TABLE_LP_ITEM', 'lp_item');
  163. define('TABLE_LP_VIEW', 'lp_view');
  164. define('TABLE_LP_ITEM_VIEW', 'lp_item_view');
  165. define('TABLE_LP_IV_INTERACTION', 'lp_iv_interaction'); // IV = Item View
  166. define('TABLE_LP_IV_OBJECTIVE', 'lp_iv_objective'); // IV = Item View
  167. // Smartblogs (Kevin Van Den Haute::kevin@develop-it.be)
  168. // Permission tables
  169. define('TABLE_PERMISSION_USER', 'permission_user');
  170. define('TABLE_PERMISSION_TASK', 'permission_task');
  171. define('TABLE_PERMISSION_GROUP', 'permission_group');
  172. // Role tables
  173. define('TABLE_ROLE', 'role');
  174. define('TABLE_ROLE_PERMISSION', 'role_permissions');
  175. define('TABLE_ROLE_USER', 'role_user');
  176. define('TABLE_ROLE_GROUP', 'role_group');
  177. // Blog tables
  178. define('TABLE_BLOGS', 'blog');
  179. define('TABLE_BLOGS_POSTS', 'blog_post');
  180. define('TABLE_BLOGS_COMMENTS', 'blog_comment');
  181. define('TABLE_BLOGS_REL_USER', 'blog_rel_user');
  182. define('TABLE_BLOGS_TASKS', 'blog_task');
  183. define('TABLE_BLOGS_TASKS_REL_USER', 'blog_task_rel_user');
  184. define('TABLE_BLOGS_RATING', 'blog_rating');
  185. define('TABLE_BLOGS_ATTACHMENT', 'blog_attachment');
  186. define('TABLE_BLOGS_TASKS_PERMISSIONS', 'permission_task');
  187. //end of Smartblogs
  188. // User information tables
  189. define('TABLE_USER_INFO', 'userinfo_def');
  190. define('TABLE_USER_INFO_CONTENT', 'userinfo_content');
  191. // Course settings table
  192. define('TABLE_COURSE_SETTING', 'course_setting');
  193. // Course online tables
  194. define('TABLE_ONLINE_LINK', 'online_link');
  195. define('TABLE_ONLINE_CONNECTED','online_connected');
  196. // User database
  197. define('TABLE_PERSONAL_AGENDA', 'personal_agenda');
  198. define('TABLE_PERSONAL_AGENDA_REPEAT', 'personal_agenda_repeat');
  199. define('TABLE_PERSONAL_AGENDA_REPEAT_NOT', 'personal_agenda_repeat_not');
  200. define('TABLE_USER_COURSE_CATEGORY', 'user_course_category');
  201. // Survey
  202. // @TODO: Are these MAIN tables or course tables?
  203. // @TODO: Probably these constants are obsolete.
  204. define('TABLE_MAIN_SURVEY', 'survey');
  205. define('TABLE_MAIN_SURVEYQUESTION', 'questions');
  206. // Survey
  207. define('TABLE_SURVEY', 'survey');
  208. define('TABLE_SURVEY_QUESTION', 'survey_question');
  209. define('TABLE_SURVEY_QUESTION_OPTION', 'survey_question_option');
  210. define('TABLE_SURVEY_INVITATION', 'survey_invitation');
  211. define('TABLE_SURVEY_ANSWER', 'survey_answer');
  212. define('TABLE_SURVEY_QUESTION_GROUP', 'survey_group');
  213. define('TABLE_SURVEY_REPORT', 'survey_report');
  214. // Wiki tables
  215. define('TABLE_WIKI', 'wiki');
  216. define('TABLE_WIKI_CONF', 'wiki_conf');
  217. define('TABLE_WIKI_DISCUSS', 'wiki_discuss');
  218. define('TABLE_WIKI_MAILCUE', 'wiki_mailcue');
  219. // Glossary
  220. define('TABLE_GLOSSARY', 'glossary');
  221. // Notebook
  222. define('TABLE_NOTEBOOK', 'notebook');
  223. // Message
  224. define('TABLE_MESSAGE', 'message');
  225. define('TABLE_MESSAGE_ATTACHMENT', 'message_attachment');
  226. // Metadata
  227. define('TABLE_METADATA', 'metadata');
  228. // Attendance Sheet
  229. define('TABLE_ATTENDANCE', 'attendance');
  230. define('TABLE_ATTENDANCE_CALENDAR', 'attendance_calendar');
  231. define('TABLE_ATTENDANCE_SHEET', 'attendance_sheet');
  232. define('TABLE_ATTENDANCE_RESULT', 'attendance_result');
  233. // Thematic
  234. define('TABLE_THEMATIC','thematic');
  235. define('TABLE_THEMATIC_PLAN', 'thematic_plan');
  236. define('TABLE_THEMATIC_ADVANCE','thematic_advance');
  237. /* DATABASE CLASS
  238. The class and its methods
  239. */
  240. class Database {
  241. /*
  242. Accessor methods
  243. Usually, you won't need these directly but instead
  244. rely on of the get_xxx_table methods.
  245. */
  246. /**
  247. * Returns the name of the main database.
  248. */
  249. public static function get_main_database() {
  250. global $_configuration;
  251. return $_configuration['main_database'];
  252. }
  253. /**
  254. * Returns the name of the statistics database.
  255. */
  256. public static function get_statistic_database() {
  257. global $_configuration;
  258. return $_configuration['statistics_database'];
  259. }
  260. /**
  261. * Returns the name of the SCORM database.
  262. * @deprecated
  263. */
  264. public static function get_scorm_database() {
  265. global $_configuration;
  266. return $_configuration['scorm_database'];
  267. }
  268. /**
  269. * Returns the name of the database where all the personal stuff of the user is stored
  270. */
  271. public static function get_user_personal_database() {
  272. global $_configuration;
  273. return $_configuration['user_personal_database'];
  274. }
  275. /**
  276. * Returns the name of the current course database.
  277. * @return mixed Glued database name of false if undefined
  278. */
  279. public static function get_current_course_database() {
  280. $course_info = api_get_course_info();
  281. if (empty($course_info['dbName'])) {
  282. return false;
  283. }
  284. return $course_info['dbName'];
  285. }
  286. /**
  287. * Returns the glued name of the current course database.
  288. * @return mixed Glued database name of false if undefined
  289. */
  290. public static function get_current_course_glued_database() {
  291. $course_info = api_get_course_info();
  292. if (empty($course_info['dbNameGlu'])) {
  293. return false;
  294. }
  295. return $course_info['dbNameGlu'];
  296. }
  297. /**
  298. * The glue is the string needed between database and table.
  299. * The trick is: in multiple databases, this is a period (with backticks).
  300. * In single database, this can be e.g. an underscore so we just fake
  301. * there are multiple databases and the code can be written independent
  302. * of the single / multiple database setting.
  303. */
  304. public static function get_database_glue() {
  305. global $_configuration;
  306. return $_configuration['db_glue'];
  307. }
  308. /**
  309. * Returns the database prefix.
  310. * All created COURSE databases are prefixed with this string.
  311. *
  312. * TIP: This can be convenient e.g. if you have multiple system installations
  313. * on the same physical server.
  314. */
  315. public static function get_database_name_prefix() {
  316. global $_configuration;
  317. return $_configuration['db_prefix'];
  318. }
  319. /**
  320. * Returns the course table prefix for single database.
  321. * Not certain exactly when this is used.
  322. * Do research.
  323. * It's used in local.inc.php.
  324. */
  325. public static function get_course_table_prefix() {
  326. global $_configuration;
  327. return $_configuration['table_prefix'];
  328. }
  329. /*
  330. Table name methods
  331. Use these methods to get table names for queries,
  332. instead of constructing them yourself.
  333. Backticks automatically surround the result,
  334. e.g. `COURSE_NAME`.`link`
  335. so the queries can look cleaner.
  336. Example:
  337. $table = Database::get_course_table(TABLE_DOCUMENT);
  338. $sql_query = "SELECT * FROM $table WHERE $condition";
  339. $sql_result = Database::query($sql_query);
  340. $result = Database::fetch_array($sql_result);
  341. */
  342. /**
  343. * A more generic method than the other get_main_xxx_table methods,
  344. * This one returns the correct complete name of any table of the main database of which you pass
  345. * the short name as a parameter.
  346. * Please, define table names as constants in this library and use them
  347. * instead of directly using magic words in your tool code.
  348. *
  349. * @param string $short_table_name, the name of the table
  350. */
  351. public static function get_main_table($short_table_name) {
  352. return self::format_table_name(self::get_main_database(), $short_table_name);
  353. }
  354. /**
  355. * A more generic method than the older get_course_xxx_table methods,
  356. * This one can return the correct complete name of any course table of which you pass
  357. * the short name as a parameter.
  358. * Please, define table names as constants in this library and use them
  359. * instead of directly using magic words in your tool code.
  360. *
  361. * @param string $short_table_name, the name of the table
  362. * @param string $database_name, optional, name of the course database
  363. * - if you don't specify this, you work on the current course.
  364. */
  365. public static function get_course_table($short_table_name, $database_name = '') {
  366. return self::format_glued_course_table_name(self::fix_database_parameter($database_name), $short_table_name);
  367. }
  368. /**
  369. * Gets a complete course table name from a course code
  370. *
  371. * @param string $course_code
  372. * @param string $table the name of the table
  373. */
  374. public static function get_course_table_from_code($course_code, $table) {
  375. $course_table = self::get_main_table(TABLE_MAIN_COURSE);
  376. $course_cat_table = self::get_main_table(TABLE_MAIN_CATEGORY);
  377. $result = self::fetch_array(self::query(
  378. "SELECT $course_table.db_name, $course_cat_table.code
  379. FROM $course_table
  380. LEFT JOIN $course_cat_table
  381. ON $course_table.category_code = $course_cat_table.code
  382. WHERE $course_table.code = '$course_code'
  383. LIMIT 1"));
  384. return sprintf("%s.%s", $result[0], $table);
  385. }
  386. /**
  387. * This generic method returns the correct and complete name of any statistic table
  388. * of which you pass the short name as a parameter.
  389. * Please, define table names as constants in this library and use them
  390. * instead of directly using magic words in your tool code.
  391. *
  392. * @param string $short_table_name, the name of the table
  393. */
  394. public static function get_statistic_table($short_table_name) {
  395. return self::format_table_name(self::get_statistic_database(), $short_table_name);
  396. }
  397. /**
  398. * This generic method returns the correct and complete name of any scorm
  399. * table of which you pass the short name as a parameter. Please, define
  400. * table names as constants in this library and use them instead of directly
  401. * using magic words in your tool code.
  402. *
  403. * @param string $short_table_name, the name of the table
  404. */
  405. public static function get_scorm_table($short_table_name) {
  406. return self::format_table_name(self::get_scorm_database(), $short_table_name);
  407. }
  408. /**
  409. * This generic method returns the correct and complete name of any scorm
  410. * table of which you pass the short name as a parameter. Please, define
  411. * table names as constants in this library and use them instead of directly
  412. * using magic words in your tool code.
  413. *
  414. * @param string $short_table_name, the name of the table
  415. */
  416. public static function get_user_personal_table($short_table_name) {
  417. return self::format_table_name(self::get_user_personal_database(), $short_table_name);
  418. }
  419. public static function get_course_chat_connected_table($database_name = '') {
  420. return self::format_glued_course_table_name(self::fix_database_parameter($database_name), CHAT_CONNECTED_TABLE);
  421. }
  422. /*
  423. Query methods
  424. These methods execute a query and return the result(s).
  425. */
  426. /**
  427. * @return a list (array) of all courses.
  428. * @todo shouldn't this be in the course.lib.php script?
  429. */
  430. public static function get_course_list() {
  431. $table = self::get_main_table(TABLE_MAIN_COURSE);
  432. return self::store_result(self::query("SELECT * FROM $table"));
  433. }
  434. /**
  435. * Returns an array with all database fields for the specified course.
  436. *
  437. * @param the real (system) code of the course (ID from inside the main course table)
  438. * @todo shouldn't this be in the course.lib.php script?
  439. */
  440. public static function get_course_info($course_code) {
  441. $course_code = self::escape_string($course_code);
  442. $table = self::get_main_table(TABLE_MAIN_COURSE);
  443. $result = self::generate_abstract_course_field_names(
  444. self::fetch_array(self::query("SELECT * FROM $table WHERE `code` = '$course_code'")));
  445. return $result === false ? array('db_name' => '') : $result;
  446. }
  447. /**
  448. * @param $user_id (integer): the id of the user
  449. * @return $user_info (array): user_id, lastname, firstname, username, email, ...
  450. * @author Patrick Cool <patrick.cool@UGent.be>, expanded to get info for any user
  451. * @author Roan Embrechts, first version + converted to Database API
  452. * @version 30 September 2004
  453. * @desc find all the information about a specified user. Without parameter this is the current user.
  454. * @todo shouldn't this be in the user.lib.php script?
  455. */
  456. public static function get_user_info_from_id($user_id = '') {
  457. if (empty($user_id)) {
  458. return $GLOBALS['_user'];
  459. }
  460. $table = self::get_main_table(TABLE_MAIN_USER);
  461. $user_id = self::escape_string($user_id);
  462. return self::generate_abstract_user_field_names(
  463. self::fetch_array(self::query("SELECT * FROM $table WHERE user_id = '$user_id'")));
  464. }
  465. /**
  466. * Returns course code from a given gradebook category's id
  467. * @param int Category ID
  468. * @return string Course code
  469. * @todo move this function in a gradebook-related library
  470. */
  471. public static function get_course_by_category($category_id) {
  472. $info = self::fetch_array(self::query('SELECT course_code FROM '.self::get_main_table(TABLE_MAIN_GRADEBOOK_CATEGORY).' WHERE id='.$category_id), 'ASSOC');
  473. return $info ? $info['course_code'] : false;
  474. }
  475. /**
  476. * This method creates an abstraction layer between database field names
  477. * and field names expected in code.
  478. *
  479. * This approach helps when changing database names.
  480. * It's also useful now to get rid of the 'franglais'.
  481. *
  482. * @todo add more array entries to abstract course info from field names
  483. * @author Roan Embrechts
  484. *
  485. * @todo What's the use of this method. I think this is better removed.
  486. * There should be consistency in the variable names and the use throughout the scripts
  487. * for the database name we should consistently use or db_name or database (db_name probably being the better one)
  488. */
  489. public static function generate_abstract_course_field_names($result_array) {
  490. $visual_code = isset($result_array['visual_code']) ? $result_array['visual_code'] : null;
  491. $code = isset($result_array['code']) ? $result_array['code'] : null;
  492. $title = isset($result_array['title']) ? $result_array['title'] : null;
  493. $db_name = isset($result_array['db_name']) ? $result_array['db_name'] : null;
  494. $category_code = isset($result_array['category_code']) ? $result_array['category_code'] : null;
  495. $result_array['official_code'] = $visual_code;
  496. $result_array['visual_code'] = $visual_code;
  497. $result_array['real_code'] = $code;
  498. $result_array['system_code'] = $code;
  499. $result_array['title'] = $title;
  500. $result_array['database'] = $db_name;
  501. $result_array['faculty'] = $category_code;
  502. //$result_array['directory'] = $result_array['directory'];
  503. /*
  504. still to do: (info taken from local.inc.php)
  505. $_course['id' ] = $cData['cours_id' ]; //auto-assigned integer
  506. $_course['name' ] = $cData['title' ];
  507. $_course['official_code'] = $cData['visual_code' ]; // use in echo
  508. $_course['sysCode' ] = $cData['code' ]; // use as key in db
  509. $_course['path' ] = $cData['directory' ]; // use as key in path
  510. $_course['dbName' ] = $cData['db_name' ]; // use as key in db list
  511. $_course['dbNameGlu' ] = $_configuration['table_prefix'] . $cData['dbName'] . $_configuration['db_glue']; // use in all queries
  512. $_course['titular' ] = $cData['tutor_name' ];
  513. $_course['language' ] = $cData['course_language' ];
  514. $_course['extLink' ]['url' ] = $cData['department_url' ];
  515. $_course['extLink' ]['name'] = $cData['department_name'];
  516. $_course['categoryCode'] = $cData['faCode' ];
  517. $_course['categoryName'] = $cData['faName' ];
  518. $_course['visibility' ] = (bool) ($cData['visibility'] == 2 || $cData['visibility'] == 3);
  519. $_course['registrationAllowed'] = (bool) ($cData['visibility'] == 1 || $cData['visibility'] == 2);
  520. */
  521. return $result_array;
  522. }
  523. /**
  524. * This method creates an abstraction layer between database field names
  525. * and field names expected in code.
  526. *
  527. * This helps when changing database names.
  528. * It's also useful now to get rid of the 'franglais'.
  529. *
  530. * @todo add more array entries to abstract user info from field names
  531. * @author Roan Embrechts
  532. * @author Patrick Cool
  533. *
  534. * @todo what's the use of this function. I think this is better removed.
  535. * There should be consistency in the variable names and the use throughout the scripts
  536. */
  537. public static function generate_abstract_user_field_names($result_array) {
  538. $result_array['firstName'] = $result_array['firstname'];
  539. $result_array['lastName'] = $result_array['lastname'];
  540. $result_array['mail'] = $result_array['email'];
  541. #$result_array['picture_uri'] = $result_array['picture_uri'];
  542. #$result_array ['user_id'] = $result_array['user_id'];
  543. return $result_array;
  544. }
  545. /**
  546. * Counts the number of rows in a table
  547. * @param string $table The table of which the rows should be counted
  548. * @return int The number of rows in the given table.
  549. */
  550. public static function count_rows($table) {
  551. $obj = self::fetch_object(self::query("SELECT COUNT(*) AS n FROM $table"));
  552. return $obj->n;
  553. }
  554. /*
  555. An intermediate API-layer between the system and the dabase server.
  556. */
  557. /**
  558. * Returns the number of affected rows in the last database operation.
  559. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  560. * @return int Returns the number of affected rows on success, and -1 if the last query failed.
  561. */
  562. public static function affected_rows($connection = null) {
  563. return self::use_default_connection($connection) ? mysql_affected_rows() : mysql_affected_rows($connection);
  564. }
  565. /**
  566. * Closes non-persistent database connection.
  567. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  568. * @return bool Returns TRUE on success or FALSE on failure.
  569. */
  570. public static function close($connection = null) {
  571. return self::use_default_connection($connection) ? mysql_close() : mysql_close($connection);
  572. }
  573. /**
  574. * Opens a connection to a database server.
  575. * @param array $parameters (optional) An array that contains the necessary parameters for accessing the server.
  576. * @return resource/boolean Returns a database connection on success or FALSE on failure.
  577. * Note: Currently the array could contain MySQL-specific parameters:
  578. * $parameters['server'], $parameters['username'], $parameters['password'],
  579. * $parameters['new_link'], $parameters['client_flags'], $parameters['persistent'].
  580. * For details see documentation about the functions mysql_connect() and mysql_pconnect().
  581. * @link http://php.net/manual/en/function.mysql-connect.php
  582. * @link http://php.net/manual/en/function.mysql-pconnect.php
  583. */
  584. public static function connect($parameters = array()) {
  585. // A MySQL-specific implementation.
  586. if (!isset($parameters['server'])) {
  587. $parameters['server'] = @ini_get('mysql.default_host');
  588. if (empty($parameters['server'])) {
  589. $parameters['server'] = 'localhost:3306';
  590. }
  591. }
  592. if (!isset($parameters['username'])) {
  593. $parameters['username'] = @ini_get('mysql.default_user');
  594. }
  595. if (!isset($parameters['password'])) {
  596. $parameters['password'] = @ini_get('mysql.default_password');
  597. }
  598. if (!isset($parameters['new_link'])) {
  599. $parameters['new_link'] = false;
  600. }
  601. if (!isset($parameters['client_flags'])) {
  602. $parameters['client_flags'] = 0;
  603. }
  604. return $parameters['persistent']
  605. ? mysql_pconnect($parameters['server'], $parameters['username'], $parameters['password'], $parameters['client_flags'])
  606. : mysql_connect($parameters['server'], $parameters['username'], $parameters['password'], $parameters['new_link'], $parameters['client_flags']);
  607. }
  608. /**
  609. * Returns the error number from the last operation done on the database server.
  610. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  611. * @return int Returns the error number from the last database (operation, or 0 (zero) if no error occurred.
  612. */
  613. public static function errno($connection = null) {
  614. return self::use_default_connection($connection) ? mysql_errno() : mysql_errno($connection);
  615. }
  616. /**
  617. * Returns the error text from the last operation done on the database server.
  618. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  619. * @return string Returns the error text from the last database operation, or '' (empty string) if no error occurred.
  620. */
  621. public static function error($connection = null) {
  622. return self::use_default_connection($connection) ? mysql_error() : mysql_error($connection);
  623. }
  624. /**
  625. * Escapes a string to insert into the database as text
  626. * @param string The string to escape
  627. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  628. * @return string The escaped string
  629. * @author Yannick Warnier <yannick.warnier@dokeos.com>
  630. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  631. */
  632. public static function escape_string($string, $connection = null) {
  633. return get_magic_quotes_gpc()
  634. ? (self::use_default_connection($connection)
  635. ? mysql_real_escape_string(stripslashes($string))
  636. : mysql_real_escape_string(stripslashes($string), $connection))
  637. : (self::use_default_connection($connection)
  638. ? mysql_real_escape_string($string)
  639. : mysql_real_escape_string($string, $connection));
  640. }
  641. /**
  642. * Gets the array from a SQL result (as returned by Database::query) - help achieving database independence
  643. * @param resource The result from a call to sql_query (e.g. Database::query)
  644. * @param string Optional: "ASSOC","NUM" or "BOTH", as the constant used in mysql_fetch_array.
  645. * @return array Array of results as returned by php
  646. * @author Yannick Warnier <yannick.warnier@dokeos.com>
  647. */
  648. public static function fetch_array($result, $option = 'BOTH') {
  649. return $option == 'ASSOC' ? mysql_fetch_array($result, MYSQL_ASSOC) : ($option == 'NUM' ? mysql_fetch_array($result, MYSQL_NUM) : mysql_fetch_array($result));
  650. }
  651. /**
  652. * Gets an associative array from a SQL result (as returned by Database::query).
  653. * This method is equivalent to calling Database::fetch_array() with 'ASSOC' value for the optional second parameter.
  654. * @param resource $result The result from a call to sql_query (e.g. Database::query).
  655. * @return array Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead.
  656. */
  657. public static function fetch_assoc($result) {
  658. return mysql_fetch_assoc($result);
  659. }
  660. /**
  661. * Gets the next row of the result of the SQL query (as returned by Database::query) in an object form
  662. * @param resource The result from a call to sql_query (e.g. Database::query)
  663. * @param string Optional class name to instanciate
  664. * @param array Optional array of parameters
  665. * @return object Object of class StdClass or the required class, containing the query result row
  666. * @author Yannick Warnier <yannick.warnier@dokeos.com>
  667. */
  668. public static function fetch_object($result, $class = null, $params = null) {
  669. return !empty($class) ? (is_array($params) ? mysql_fetch_object($result, $class, $params) : mysql_fetch_object($result, $class)) : mysql_fetch_object($result);
  670. }
  671. /**
  672. * Gets the array from a SQL result (as returned by Database::query) - help achieving database independence
  673. * @param resource The result from a call to sql_query (see Database::query()).
  674. * @return array Array of results as returned by php (mysql_fetch_row)
  675. */
  676. public static function fetch_row($result) {
  677. return mysql_fetch_row($result);
  678. }
  679. /**
  680. * Frees all the memory associated with the provided result identifier.
  681. * @return bool Returns TRUE on success or FALSE on failure.
  682. * Notes: Use this method if you are concerned about how much memory is being used for queries that return large result sets.
  683. * Anyway, all associated result memory is automatically freed at the end of the script's execution.
  684. */
  685. public static function free_result($result) {
  686. return mysql_free_result($result);
  687. }
  688. /**
  689. * Returns the database client library version.
  690. * @return strung Returns a string that represents the client library version.
  691. */
  692. public function get_client_info() {
  693. return mysql_get_client_info();
  694. }
  695. /**
  696. * Returns a list of databases created on the server. The list may contain all of the
  697. * available database names or filtered database names by using a pattern.
  698. * @param string $pattern (optional) A pattern for filtering database names as if it was needed for the SQL's LIKE clause, for example 'chamilo_%'.
  699. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  700. * @return array Returns in an array the retrieved list of database names.
  701. */
  702. public static function get_databases($pattern = '', $connection = null) {
  703. $result = array();
  704. $query_result = Database::query(!empty($pattern) ? "SHOW DATABASES LIKE '".self::escape_string($pattern, $connection)."'" : "SHOW DATABASES", $connection);
  705. while ($row = Database::fetch_row($query_result)) {
  706. $result[] = $row[0];
  707. }
  708. return $result;
  709. }
  710. /**
  711. * Returns a list of the fields that a given table contains. The list may contain all of the available field names or filtered field names by using a pattern.
  712. * By using a special option, this method is able to return an indexed list of fields' properties, where field names are keys.
  713. * @param string $table This is the examined table.
  714. * @param string $pattern (optional) A pattern for filtering field names as if it was needed for the SQL's LIKE clause, for example 'column_%'.
  715. * @param string $database (optional) The name of the targeted database. If it is omited, the current database is assumed, see Database::select_db().
  716. * @param bool $including_properties (optional) When this option is true, the returned result has the followong format:
  717. * array(field_name_1 => array(0 => property_1, 1 => property_2, ...), fieald_name_2 => array(0 => property_1, ...), ...)
  718. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  719. * @return array Returns in an array the retrieved list of field names.
  720. */
  721. public static function get_fields($table, $pattern = '', $database = '', $including_properties = false, $connection = null) {
  722. $result = array();
  723. $query = "SHOW COLUMNS FROM `".self::escape_string($table, $connection)."`";
  724. if (!empty($database)) {
  725. $query .= " FROM `".self::escape_string($database, $connection)."`";
  726. }
  727. if (!empty($pattern)) {
  728. $query .= " LIKE '".self::escape_string($pattern, $connection)."'";
  729. }
  730. $query_result = Database::query($query, $connection);
  731. if ($including_properties) {
  732. // Making an indexed list of the fields and their properties.
  733. while ($row = Database::fetch_row($query_result)) {
  734. $result[$row[0]] = $row;
  735. }
  736. } else {
  737. // Making a plain, flat list.
  738. while ($row = Database::fetch_row($query_result)) {
  739. $result[] = $row[0];
  740. }
  741. }
  742. return $result;
  743. }
  744. /**
  745. * Returns information about the type of the current connection and the server host name.
  746. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  747. * @return string/boolean Returns string data on success or FALSE on failure.
  748. */
  749. public function get_host_info($connection = null) {
  750. return self::use_default_connection($connection) ? mysql_get_host_info() : mysql_get_host_info($connection);
  751. }
  752. /**
  753. * Retrieves database client/server protocol version.
  754. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  755. * @return int/boolean Returns the protocol version on success or FALSE on failure.
  756. */
  757. public function get_proto_info($connection = null) {
  758. return self::use_default_connection($connection) ? mysql_get_proto_info() : mysql_get_proto_info($connection);
  759. }
  760. /**
  761. * Retrieves the database server version.
  762. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  763. * @return string/boolean Returns the MySQL server version on success or FALSE on failure.
  764. */
  765. public function get_server_info($connection = null) {
  766. return self::use_default_connection($connection) ? mysql_get_server_info() : mysql_get_server_info($connection);
  767. }
  768. /**
  769. * Returns a list of tables within a database. The list may contain all of the
  770. * available table names or filtered table names by using a pattern.
  771. * @param string $database (optional) The name of the examined database. If it is omited, the current database is assumed, see Database::select_db().
  772. * @param string $pattern (optional) A pattern for filtering table names as if it was needed for the SQL's LIKE clause, for example 'access_%'.
  773. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  774. * @return array Returns in an array the retrieved list of table names.
  775. */
  776. public static function get_tables($database = '', $pattern = '', $connection = null) {
  777. $result = array();
  778. $query = "SHOW TABLES";
  779. if (!empty($database)) {
  780. $query .= " FROM `".self::escape_string($database, $connection)."`";
  781. }
  782. if (!empty($pattern)) {
  783. $query .= " LIKE '".self::escape_string($pattern, $connection)."'";
  784. }
  785. $query_result = Database::query($query, $connection);
  786. while ($row = Database::fetch_row($query_result)) {
  787. $result[] = $row[0];
  788. }
  789. return $result;
  790. }
  791. /**
  792. * Gets the ID of the last item inserted into the database
  793. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  794. * @return int The last ID as returned by the DB function
  795. * @comment This should be updated to use ADODB at some point
  796. */
  797. public static function insert_id($connection = null) {
  798. return self::use_default_connection($connection) ? mysql_insert_id() : mysql_insert_id($connection);
  799. }
  800. /**
  801. * Gets the number of rows from the last query result - help achieving database independence
  802. * @param resource The result
  803. * @return integer The number of rows contained in this result
  804. * @author Yannick Warnier <yannick.warnier@dokeos.com>
  805. **/
  806. public static function num_rows($result) {
  807. return is_resource($result) ? mysql_num_rows($result) : false;
  808. }
  809. /**
  810. * Acts as the relative *_result() function of most DB drivers and fetches a
  811. * specific line and a field
  812. * @param resource The database resource to get data from
  813. * @param integer The row number
  814. * @param string Optional field name or number
  815. * @result mixed One cell of the result, or FALSE on error
  816. */
  817. public static function result($resource, $row, $field = '') {
  818. return self::num_rows($resource) > 0 ? (!empty($field) ? mysql_result($resource, $row, $field) : mysql_result($resource, $row)) : null;
  819. }
  820. /**
  821. * This method returns a resource
  822. * Documentation has been added by Arthur Portugal
  823. * Some adaptations have been implemented by Ivan Tcholakov, 2009, 2010
  824. * @author Olivier Brouckaert
  825. * @param string $query The SQL query
  826. * @param resource $connection (optional) The database server (MySQL) connection.
  827. * If it is not specified, the connection opened by mysql_connect() is assumed.
  828. * If no connection is found, the server will try to create one as if mysql_connect() was called with no arguments.
  829. * If no connection is found or established, an E_WARNING level error is generated.
  830. * @param string $file (optional) On error it shows the file in which the error has been trigerred (use the "magic" constant __FILE__ as input parameter)
  831. * @param string $line (optional) On error it shows the line in which the error has been trigerred (use the "magic" constant __LINE__ as input parameter)
  832. * @return resource The returned result from the query
  833. * Note: The parameter $connection could be skipped. Here are examples of this method usage:
  834. * Database::query($query);
  835. * $result = Database::query($query);
  836. * Database::query($query, $connection);
  837. * $result = Database::query($query, $connection);
  838. * The following ways for calling this method are obsolete:
  839. * Database::query($query, __FILE__, __LINE__);
  840. * $result = Database::query($query, __FILE__, __LINE__);
  841. * Database::query($query, $connection, __FILE__, __LINE__);
  842. * $result = Database::query($query, $connection, __FILE__, __LINE__);
  843. */
  844. public static function query($query, $connection = null, $file = null, $line = null) {
  845. $use_default_connection = self::use_default_connection($connection);
  846. if ($use_default_connection) {
  847. // Let us do parameter shifting, thus the method would be similar
  848. // (in regard to parameter order) to the original function mysql_query().
  849. $line = $file;
  850. $file = $connection;
  851. $connection = null;
  852. }
  853. if (!($result = $use_default_connection ? @mysql_query($query) : @mysql_query($query, $connection))) {
  854. $backtrace = debug_backtrace(); // Retrieving information about the caller statement.
  855. if (isset($backtrace[0])) {
  856. $caller = & $backtrace[0];
  857. } else {
  858. $caller = array();
  859. }
  860. if (isset($backtrace[1])) {
  861. $owner = & $backtrace[1];
  862. } else {
  863. $owner = array();
  864. }
  865. if (empty($file)) {
  866. $file = $caller['file'];
  867. }
  868. if (empty($line) && $line !== false) {
  869. $line = $caller['line'];
  870. }
  871. $type = $owner['type'];
  872. $function = $owner['function'];
  873. $class = $owner['class'];
  874. $server_type = api_get_setting('server_type');
  875. if (!empty($line) && !empty($server_type) && $server_type != 'production') {
  876. $info = '<pre>' .
  877. '<strong>DATABASE ERROR #'.self::errno($connection).':</strong><br /> ' .
  878. self::remove_XSS(self::error($connection)) . '<br />' .
  879. '<strong>QUERY :</strong><br /> ' .
  880. self::remove_XSS($query) . '<br />' .
  881. '<strong>FILE :</strong><br /> ' .
  882. (empty($file) ? ' unknown ' : $file) . '<br />' .
  883. '<strong>LINE :</strong><br /> ' .
  884. (empty($line) ? ' unknown ' : $line) . '<br />';
  885. if (empty($type)) {
  886. if (!empty($function)) {
  887. $info .= '<strong>FUNCTION :</strong><br /> ' . $function;
  888. }
  889. } else {
  890. if (!empty($class) && !empty($function)) {
  891. $info .= '<strong>CLASS :</strong><br /> ' . $class . '<br />';
  892. $info .= '<strong>METHOD :</strong><br /> ' . $function;
  893. }
  894. }
  895. $info .= '</pre>';
  896. echo $info;
  897. }
  898. }
  899. return $result;
  900. }
  901. /**
  902. * Selects a database.
  903. * @param string $database_name The name of the database that is to be selected.
  904. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  905. * @return bool Returns TRUE on success or FALSE on failure.
  906. */
  907. public static function select_db($database_name, $connection = null) {
  908. return self::use_default_connection($connection) ? mysql_select_db($database_name) : mysql_select_db($database_name, $connection);
  909. }
  910. /**
  911. * Stores a query result into an array.
  912. *
  913. * @author Olivier Brouckaert
  914. * @param resource $result - the return value of the query
  915. * @param option BOTH, ASSOC, or NUM
  916. * @return array - the value returned by the query
  917. */
  918. public static function store_result($result, $option = 'BOTH') {
  919. $array = array();
  920. if ($result !== false) { // For isolation from database engine's behaviour.
  921. while ($row = self::fetch_array($result, $option)) {
  922. $array[] = $row;
  923. }
  924. }
  925. return $array;
  926. }
  927. /*
  928. Encodings and collations supported by MySQL database server
  929. */
  930. /**
  931. * Checks whether a given encoding is supported by the database server.
  932. * @param string $encoding The encoding (a system conventional id, for example 'UTF-8') to be checked.
  933. * @return bool Returns a boolean value as a check-result.
  934. * @author Ivan Tcholakov
  935. */
  936. public static function is_encoding_supported($encoding) {
  937. static $supported = array();
  938. if (!isset($supported[$encoding])) {
  939. $supported[$encoding] = false;
  940. if (strlen($db_encoding = self::to_db_encoding($encoding)) > 0) {
  941. if (self::num_rows(self::query("SHOW CHARACTER SET WHERE Charset = '".self::escape_string($db_encoding)."';")) > 0) {
  942. $supported[$encoding] = true;
  943. }
  944. }
  945. }
  946. return $supported[$encoding];
  947. }
  948. /**
  949. * Constructs a SQL clause about default character set and default collation for newly created databases and tables.
  950. * Example: Database::make_charset_clause('UTF-8', 'bulgarian') returns
  951. * DEFAULT CHARACTER SET `utf8` DEFAULT COLLATE `utf8_general_ci`
  952. * @param string $encoding (optional) The default database/table encoding (a system conventional id) to be used.
  953. * @param string $language (optional) Language (a system conventional id) used for choosing language sensitive collation (if it is possible).
  954. * @return string Returns the constructed SQL clause or empty string if $encoding is not correct or is not supported.
  955. * @author Ivan Tcholakov
  956. */
  957. public static function make_charset_clause($encoding = null, $language = null) {
  958. if (empty($encoding)) {
  959. $encoding = api_get_system_encoding();
  960. }
  961. if (empty($language)) {
  962. $language = api_get_interface_language();
  963. }
  964. $charset_clause = '';
  965. if (self::is_encoding_supported($encoding)) {
  966. $db_encoding = Database::to_db_encoding($encoding);
  967. $charset_clause .= " DEFAULT CHARACTER SET `".$db_encoding."`";
  968. $db_collation = Database::to_db_collation($encoding, $language);
  969. if (!empty($db_collation)) {
  970. $charset_clause .= " DEFAULT COLLATE `".$db_collation."`";
  971. }
  972. }
  973. return $charset_clause;
  974. }
  975. /**
  976. * Converts an encoding identificator to MySQL-specific encoding identifictor,
  977. * i.e. 'UTF-8' --> 'utf8'.
  978. * @param string $encoding The conventional encoding identificator.
  979. * @return string Returns the corresponding MySQL-specific encoding identificator if any, otherwise returns NULL.
  980. * @author Ivan Tcholakov
  981. */
  982. public static function to_db_encoding($encoding) {
  983. static $result = array();
  984. if (!isset($result[$encoding])) {
  985. $result[$encoding] = null;
  986. $encoding_map = & self::get_db_encoding_map();
  987. foreach ($encoding_map as $key => $value) {
  988. if (api_equal_encodings($encoding, $key)) {
  989. $result[$encoding] = $value;
  990. break;
  991. }
  992. }
  993. }
  994. return $result[$encoding];
  995. }
  996. /**
  997. * Converts a MySQL-specific encoding identifictor to conventional encoding identificator,
  998. * i.e. 'utf8' --> 'UTF-8'.
  999. * @param string $encoding The MySQL-specific encoding identificator.
  1000. * @return string Returns the corresponding conventional encoding identificator if any, otherwise returns NULL.
  1001. * @author Ivan Tcholakov
  1002. */
  1003. public static function from_db_encoding($db_encoding) {
  1004. static $result = array();
  1005. if (!isset($result[$db_encoding])) {
  1006. $result[$db_encoding] = null;
  1007. $encoding_map = & self::get_db_encoding_map();
  1008. foreach ($encoding_map as $key => $value) {
  1009. if (strtolower($db_encoding) == $value) {
  1010. $result[$db_encoding] = $key;
  1011. break;
  1012. }
  1013. }
  1014. }
  1015. return $result[$db_encoding];
  1016. }
  1017. /**
  1018. * Chooses the default MySQL-specific collation from given encoding and language.
  1019. * @param string $encoding A conventional encoding id, i.e. 'UTF-8'
  1020. * @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.
  1021. * @return string Returns a suitable default collation, for example 'utf8_general_ci', or NULL if collation was not found.
  1022. * @author Ivan Tcholakov
  1023. */
  1024. public static function to_db_collation($encoding, $language = null) {
  1025. static $result = array();
  1026. if (!isset($result[$encoding][$language])) {
  1027. $result[$encoding][$language] = null;
  1028. if (self::is_encoding_supported($encoding)) {
  1029. $db_encoding = self::to_db_encoding($encoding);
  1030. if (!empty($language)) {
  1031. $lang = api_purify_language_id($language);
  1032. $res = self::check_db_collation($db_encoding, $lang);
  1033. if (empty($res)) {
  1034. $db_collation_map = & self::get_db_collation_map();
  1035. if (isset($db_collation_map[$lang])) {
  1036. $res = self::check_db_collation($db_encoding, $db_collation_map[$lang]);
  1037. }
  1038. }
  1039. if (empty($res)) {
  1040. $res = self::check_db_collation($db_encoding, null);
  1041. }
  1042. $result[$encoding][$language] = $res;
  1043. } else {
  1044. $result[$encoding][$language] = self::check_db_collation($db_encoding, null);
  1045. }
  1046. }
  1047. }
  1048. return $result[$encoding][$language];
  1049. }
  1050. /*
  1051. Private methods
  1052. You should not access these from outside the class
  1053. No effort is made to keep the names / results the same.
  1054. */
  1055. /**
  1056. * Glues a course database.
  1057. * glue format from local.inc.php.
  1058. */
  1059. private static function glue_course_database_name($database_name) {
  1060. return self::get_course_table_prefix().$database_name.self::get_database_glue();
  1061. }
  1062. /**
  1063. * @param string $database_name, can be empty to use current course db
  1064. *
  1065. * @return the glued parameter if it is not empty,
  1066. * or the current course database (glued) if the parameter is empty.
  1067. */
  1068. private static function fix_database_parameter($database_name) {
  1069. if (empty($database_name)) {
  1070. $course_info = api_get_course_info();
  1071. return $course_info['dbNameGlu'];
  1072. }
  1073. return self::glue_course_database_name($database_name);
  1074. }
  1075. /**
  1076. * Structures a course database and table name to ready them
  1077. * for querying. The course database parameter is considered glued:
  1078. * e.g. COURSE001`.`
  1079. */
  1080. private static function format_glued_course_table_name($database_name_with_glue, $table) {
  1081. return '`'.$database_name_with_glue.$table.'`';
  1082. }
  1083. /**
  1084. * Structures a database and table name to ready them
  1085. * for querying. The database parameter is considered not glued,
  1086. * just plain e.g. COURSE001
  1087. */
  1088. private static function format_table_name($database, $table) {
  1089. return '`'.$database.'`.`'.$table.'`';
  1090. }
  1091. /**
  1092. * This private method is to be used by the other methods in this class for
  1093. * checking whether the input parameter $connection actually has been provided.
  1094. * If the input parameter connection is not a resource or if it is not FALSE (in case of error)
  1095. * then the default opened connection should be used by the called method.
  1096. * @param resource/boolean $connection The checked parameter $connection.
  1097. * @return boolean TRUE means that calling method should use the default connection.
  1098. * FALSE means that (valid) parameter $connection has been provided and it should be used.
  1099. */
  1100. private static function use_default_connection($connection) {
  1101. return !is_resource($connection) && $connection !== false;
  1102. }
  1103. /**
  1104. * This private method tackles the XSS injections. It is similar to Security::remove_XSS() and works always,
  1105. * including the time of initialization when the class Security has not been loaded yet.
  1106. * @param string The input variable to be filtered from XSS, in this class it is expected to be a string.
  1107. * @return string Returns the filtered string as a result.
  1108. */
  1109. private static function remove_XSS(& $var) {
  1110. return class_exists('Security') && class_exists('HTMLPurifier') ? Security::remove_XSS($var) : api_htmlentities($var, ENT_QUOTES);
  1111. }
  1112. /**
  1113. * This private method encapsulates a table with relations between
  1114. * conventional and MuSQL-specific encoding identificators.
  1115. * @author Ivan Tcholakov
  1116. */
  1117. private static function & get_db_encoding_map() {
  1118. static $encoding_map = array(
  1119. 'ARMSCII-8' => 'armscii8',
  1120. 'BIG5' => 'big5',
  1121. 'BINARY' => 'binary',
  1122. 'CP866' => 'cp866',
  1123. 'EUC-JP' => 'ujis',
  1124. 'EUC-KR' => 'euckr',
  1125. 'GB2312' => 'gb2312',
  1126. 'GBK' => 'gbk',
  1127. 'ISO-8859-1' => 'latin1',
  1128. 'ISO-8859-2' => 'latin2',
  1129. 'ISO-8859-7' => 'greek',
  1130. 'ISO-8859-8' => 'hebrew',
  1131. 'ISO-8859-9' => 'latin5',
  1132. 'ISO-8859-13' => 'latin7',
  1133. 'ISO-8859-15' => 'latin1',
  1134. 'KOI8-R' => 'koi8r',
  1135. 'KOI8-U' => 'koi8u',
  1136. 'SHIFT-JIS' => 'sjis',
  1137. 'TIS-620' => 'tis620',
  1138. 'US-ASCII' => 'ascii',
  1139. 'UTF-8' => 'utf8',
  1140. 'WINDOWS-1250' => 'cp1250',
  1141. 'WINDOWS-1251' => 'cp1251',
  1142. 'WINDOWS-1252' => 'latin1',
  1143. 'WINDOWS-1256' => 'cp1256',
  1144. 'WINDOWS-1257' => 'cp1257'
  1145. );
  1146. return $encoding_map;
  1147. }
  1148. /**
  1149. * A helper language id translation table for choosing some collations.
  1150. * @author Ivan Tcholakov
  1151. */
  1152. private static function & get_db_collation_map() {
  1153. static $db_collation_map = array(
  1154. 'german' => 'german2',
  1155. 'simpl_chinese' => 'chinese',
  1156. 'trad_chinese' => 'chinese',
  1157. 'turkce' => 'turkish'
  1158. );
  1159. return $db_collation_map;
  1160. }
  1161. /**
  1162. * Constructs a MySQL-specific collation and checks whether it is supported by the database server.
  1163. * @param string $db_encoding A MySQL-specific encoding id, i.e. 'utf8'
  1164. * @param string $language A MySQL-compatible language id, i.e. 'bulgarian'
  1165. * @return string Returns a suitable default collation, for example 'utf8_general_ci', or NULL if collation was not found.
  1166. * @author Ivan Tcholakov
  1167. */
  1168. private static function check_db_collation($db_encoding, $language) {
  1169. if (empty($db_encoding)) {
  1170. return null;
  1171. }
  1172. if (empty($language)) {
  1173. $result = self::fetch_array(self::query("SHOW COLLATION WHERE Charset = '".self::escape_string($db_encoding)."' AND `Default` = 'Yes';"), 'NUM');
  1174. return $result ? $result[0] : null;
  1175. }
  1176. $collation = $db_encoding.'_'.$language.'_ci';
  1177. $query_result = self::query("SHOW COLLATION WHERE Charset = '".self::escape_string($db_encoding)."';");
  1178. while ($result = self::fetch_array($query_result, 'NUM')) {
  1179. if ($result[0] == $collation) {
  1180. return $collation;
  1181. }
  1182. }
  1183. return null;
  1184. }
  1185. /*
  1186. DEPRECATED METHODS
  1187. */
  1188. /**
  1189. * @deprecated Use api_get_language_isocode($language) instead.
  1190. */
  1191. public static function get_language_isocode($language) {
  1192. return api_get_language_isocode($language);
  1193. }
  1194. /**
  1195. * @deprecated Use Database::insert_id() instead.
  1196. */
  1197. public static function get_last_insert_id() {
  1198. return mysql_insert_id();
  1199. }
  1200. }
  1201. //end class Database