database.lib.php 65 KB

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