database.lib.php 60 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374
  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. * If trying to replicate the database layer, don't forget to look for "sql"
  12. * named functions in main_api.lib.php
  13. *
  14. * @package chamilo.library
  15. */
  16. /**
  17. * Constants definition
  18. */
  19. require_once 'database.constants.inc.php';
  20. /**
  21. * Database class definition
  22. * @package chamilo.database
  23. */
  24. class Database {
  25. /* Variable use only in the installation process to log errors. See the Database::query function */
  26. static $log_queries = false;
  27. /*
  28. Accessor methods
  29. Usually, you won't need these directly but instead
  30. rely on of the get_xxx_table methods.
  31. */
  32. /**
  33. * Returns the name of the main database.
  34. */
  35. public static function get_main_database() {
  36. global $_configuration;
  37. return $_configuration['main_database'];
  38. }
  39. /**
  40. * Returns the name of the statistics database.
  41. * @todo use main_database
  42. */
  43. public static function get_statistic_database() {
  44. global $_configuration;
  45. return $_configuration['main_database'];
  46. }
  47. /**
  48. * Returns the name of the database where all the personal stuff of the user is stored
  49. * @todo use main_database
  50. */
  51. public static function get_user_personal_database() {
  52. global $_configuration;
  53. return $_configuration['main_database'];
  54. }
  55. /**
  56. * Returns the name of the current course database.
  57. * @return mixed Glued database name of false if undefined
  58. */
  59. public static function get_current_course_database() {
  60. $course_info = api_get_course_info();
  61. if (empty($course_info['dbName'])) {
  62. return false;
  63. }
  64. return $course_info['dbName'];
  65. }
  66. /**
  67. * Returns the glued name of the current course database.
  68. * @return mixed Glued database name of false if undefined
  69. */
  70. public static function get_current_course_glued_database() {
  71. $course_info = api_get_course_info();
  72. if (empty($course_info['dbNameGlu'])) {
  73. return false;
  74. }
  75. return $course_info['dbNameGlu'];
  76. }
  77. /**
  78. * The glue is the string needed between database and table.
  79. * The trick is: in multiple databases, this is a period (with backticks).
  80. * In single database, this can be e.g. an underscore so we just fake
  81. * there are multiple databases and the code can be written independent
  82. * of the single / multiple database setting.
  83. */
  84. public static function get_database_glue() {
  85. global $_configuration;
  86. return $_configuration['db_glue'];
  87. }
  88. /**
  89. * Returns the database prefix.
  90. * All created COURSE databases are prefixed with this string.
  91. *
  92. * TIP: This can be convenient if you have multiple system installations
  93. * on the same physical server.
  94. */
  95. public static function get_database_name_prefix() {
  96. global $_configuration;
  97. return $_configuration['db_prefix'];
  98. }
  99. /**
  100. * Returns the course table prefix for single database.
  101. * Not certain exactly when this is used.
  102. * Do research.
  103. * It's used in local.inc.php.
  104. */
  105. public static function get_course_table_prefix() {
  106. global $_configuration;
  107. return $_configuration['table_prefix'];
  108. }
  109. /*
  110. Table name methods
  111. Use these methods to get table names for queries,
  112. instead of constructing them yourself.
  113. Backticks automatically surround the result,
  114. e.g. COURSE_NAME.link
  115. so the queries can look cleaner.
  116. Example:
  117. $table = Database::get_course_table(TABLE_DOCUMENT);
  118. $sql_query = "SELECT * FROM $table WHERE $condition";
  119. $sql_result = Database::query($sql_query);
  120. $result = Database::fetch_array($sql_result);
  121. */
  122. /**
  123. * A more generic method than the other get_main_xxx_table methods,
  124. * This one returns the correct complete name of any table of the main
  125. * database of which you pass the short name as a parameter.
  126. * Please, define table names as constants in this library and use them
  127. * instead of directly using magic words in your tool code.
  128. *
  129. * @param string $short_table_name, the name of the table
  130. */
  131. public static function get_main_table($short_table_name) {
  132. return self::format_table_name(
  133. self::get_main_database(),
  134. $short_table_name);
  135. }
  136. /**
  137. * A more generic method than the older get_course_xxx_table methods,
  138. * This one can return the correct complete name of any course table of
  139. * which you pass the short name as a parameter.
  140. * Please, define table names as constants in this library and use them
  141. * instead of directly using magic words in your tool code.
  142. *
  143. * @param string $short_table_name, the name of the table
  144. * @param string $database_name, optional, name of the course database
  145. * - if you don't specify this, you work on the current course.
  146. */
  147. public static function get_course_table($short_table_name, $extra = null) {
  148. //forces fatal errors so we can debug more easily
  149. if (!empty($extra)) {
  150. var_dump($extra);
  151. //@todo remove this
  152. echo "<h3>Dev Message: get_course_table() doesn't have a 2nd parameter</h3>";
  153. //exit;
  154. }
  155. return self::format_table_name(self::get_main_database(), DB_COURSE_PREFIX.$short_table_name);
  156. //return self::format_glued_course_table_name(self::fix_database_parameter($database_name), $short_table_name);
  157. }
  158. /**
  159. * This generic method returns the correct and complete name of any
  160. * statistic table of which you pass the short name as a parameter.
  161. * Please, define table names as constants in this library and use them
  162. * instead of directly using magic words in your tool code.
  163. *
  164. * @param string $short_table_name, the name of the table
  165. */
  166. public static function get_statistic_table($short_table_name) {
  167. return self::format_table_name(self::get_statistic_database(), $short_table_name);
  168. }
  169. /**
  170. * This generic method returns the correct and complete name of any user
  171. * table of which you pass the short name as a parameter. Please, define
  172. * table names as constants in this library and use them instead of directly
  173. * using magic words in your tool code.
  174. *
  175. * @param string $short_table_name, the name of the table
  176. */
  177. public static function get_user_personal_table($short_table_name) {
  178. return self::format_table_name(self::get_user_personal_database(), $short_table_name);
  179. }
  180. public static function get_course_chat_connected_table($database_name = '') {
  181. return self::format_glued_course_table_name(self::fix_database_parameter($database_name), TABLE_CHAT_CONNECTED);
  182. }
  183. /*
  184. Query methods
  185. These methods execute a query and return the result(s).
  186. */
  187. /**
  188. * @return a list (array) of all courses.
  189. * @todo shouldn't this be in the course.lib.php script?
  190. */
  191. public static function get_course_list() {
  192. $table = self::get_main_table(TABLE_MAIN_COURSE);
  193. return self::store_result(self::query("SELECT *, id as real_id FROM $table"));
  194. }
  195. /**
  196. * Returns an array with all database fields for the specified course.
  197. *
  198. * @param string The real (system) course code (main course table ID)
  199. * @todo shouldn't this be in the course.lib.php script?
  200. */
  201. public static function get_course_info($course_code) {
  202. $course_code = self::escape_string($course_code);
  203. $table = self::get_main_table(TABLE_MAIN_COURSE);
  204. $result = self::generate_abstract_course_field_names(
  205. self::fetch_array(self::query("SELECT *, id as real_id FROM $table WHERE code = '$course_code'")));
  206. return $result === false ? array('db_name' => '') : $result;
  207. }
  208. /**
  209. * Gets user details from the "user" table
  210. * @param $user_id (integer): the id of the user
  211. * @return $user_info (array): user_id, lname, fname, username, email, ...
  212. * @author Patrick Cool <patrick.cool@UGent.be>, expanded to get info for any user
  213. * @author Roan Embrechts, first version + converted to Database API
  214. * @version 30 September 2004
  215. * @deprecated use api_get_user_info();
  216. * @desc find all the information about a specified user. Without parameter this is the current user.
  217. * @todo shouldn't this be in the user.lib.php script?
  218. */
  219. public static function get_user_info_from_id($user_id = '') {
  220. if (empty($user_id)) {
  221. return $GLOBALS['_user'];
  222. }
  223. $table = self::get_main_table(TABLE_MAIN_USER);
  224. $user_id = self::escape_string($user_id);
  225. return self::generate_abstract_user_field_names(
  226. self::fetch_array(self::query("SELECT * FROM $table WHERE user_id = '$user_id'")));
  227. }
  228. /**
  229. * Returns course code from a given gradebook category's id
  230. * @param int Category ID
  231. * @return string Course code
  232. * @todo move this function in a gradebook-related library
  233. */
  234. public static function get_course_by_category($category_id) {
  235. $category_id = intval($category_id);
  236. $info = self::fetch_array(self::query('SELECT course_code FROM '.self::get_main_table(TABLE_MAIN_GRADEBOOK_CATEGORY).' WHERE id='.$category_id), 'ASSOC');
  237. return $info ? $info['course_code'] : false;
  238. }
  239. /**
  240. * This method creates an abstraction layer between database field names
  241. * and field names expected in code.
  242. *
  243. * This approach helps when changing database names.
  244. * It's also useful now to get rid of the 'franglais'.
  245. *
  246. * @todo add more array entries to abstract course info from field names
  247. * @author Roan Embrechts
  248. *
  249. * @todo What's the use of this method. I think this is better removed.
  250. * There should be consistency in the variable names and the
  251. * use throughout the scripts
  252. * for the database name we should consistently use or db_name
  253. * or database (db_name probably being the better one)
  254. */
  255. public static function generate_abstract_course_field_names($result_array) {
  256. $visual_code = isset($result_array['visual_code']) ? $result_array['visual_code'] : null;
  257. $code = isset($result_array['code']) ? $result_array['code'] : null;
  258. $title = isset($result_array['title']) ? $result_array['title'] : null;
  259. $db_name = isset($result_array['db_name']) ? $result_array['db_name'] : null;
  260. $category_code = isset($result_array['category_code']) ? $result_array['category_code'] : null;
  261. $result_array['official_code'] = $visual_code;
  262. $result_array['visual_code'] = $visual_code;
  263. $result_array['real_code'] = $code;
  264. $result_array['system_code'] = $code;
  265. $result_array['title'] = $title;
  266. $result_array['database'] = $db_name;
  267. $result_array['faculty'] = $category_code;
  268. //$result_array['directory'] = $result_array['directory'];
  269. /*
  270. still to do: (info taken from local.inc.php)
  271. $_course['id' ] = $cData['cours_id' ]; //auto-assigned integer
  272. $_course['name' ] = $cData['title' ];
  273. $_course['official_code'] = $cData['visual_code' ]; // use in echo
  274. $_course['sysCode' ] = $cData['code' ]; // use as key in db
  275. $_course['path' ] = $cData['directory' ]; // use as key in path
  276. $_course['dbName' ] = $cData['db_name' ]; // use as key in db list
  277. $_course['dbNameGlu' ] = $_configuration['table_prefix'] . $cData['dbName'] . $_configuration['db_glue']; // use in all queries
  278. $_course['titular' ] = $cData['tutor_name' ];
  279. $_course['language' ] = $cData['course_language' ];
  280. $_course['extLink' ]['url' ] = $cData['department_url' ];
  281. $_course['extLink' ]['name'] = $cData['department_name'];
  282. $_course['categoryCode'] = $cData['faCode' ];
  283. $_course['categoryName'] = $cData['faName' ];
  284. $_course['visibility' ] = (bool) ($cData['visibility'] == 2 || $cData['visibility'] == 3);
  285. $_course['registrationAllowed'] = (bool) ($cData['visibility'] == 1 || $cData['visibility'] == 2);
  286. */
  287. return $result_array;
  288. }
  289. /**
  290. * This method creates an abstraction layer between database field names
  291. * and field names expected in code.
  292. *
  293. * This helps when changing database names.
  294. * It's also useful now to get rid of the 'franglais'.
  295. *
  296. * @todo add more array entries to abstract user info from field names
  297. * @author Roan Embrechts
  298. * @author Patrick Cool
  299. *
  300. * @todo what's the use of this function. I think this is better removed.
  301. * There should be consistency in the variable names and the use throughout the scripts
  302. */
  303. public static function generate_abstract_user_field_names($result_array) {
  304. $result_array['firstName'] = $result_array['firstname'];
  305. $result_array['lastName'] = $result_array['lastname'];
  306. $result_array['mail'] = $result_array['email'];
  307. #$result_array['picture_uri'] = $result_array['picture_uri'];
  308. #$result_array ['user_id'] = $result_array['user_id'];
  309. return $result_array;
  310. }
  311. /**
  312. * Counts the number of rows in a table
  313. * @param string $table The table of which the rows should be counted
  314. * @return int The number of rows in the given table.
  315. * @deprecated
  316. */
  317. public static function count_rows($table) {
  318. $obj = self::fetch_object(self::query("SELECT COUNT(*) AS n FROM $table")); //
  319. return $obj->n;
  320. }
  321. /*
  322. An intermediate API-layer between the system and the dabase server.
  323. */
  324. /**
  325. * Returns the number of affected rows in the last database operation.
  326. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  327. * @return int Returns the number of affected rows on success, and -1 if the last query failed.
  328. */
  329. public static function affected_rows($connection = null) {
  330. return self::use_default_connection($connection) ? mysql_affected_rows() : mysql_affected_rows($connection);
  331. }
  332. /**
  333. * Closes non-persistent database connection.
  334. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  335. * @return bool Returns TRUE on success or FALSE on failure.
  336. */
  337. public static function close($connection = null) {
  338. return self::use_default_connection($connection) ? mysql_close() : mysql_close($connection);
  339. }
  340. /**
  341. * Opens a connection to a database server.
  342. * @param array $parameters (optional) An array that contains the necessary parameters for accessing the server.
  343. * @return resource/boolean Returns a database connection on success or FALSE on failure.
  344. * Note: Currently the array could contain MySQL-specific parameters:
  345. * $parameters['server'], $parameters['username'], $parameters['password'],
  346. * $parameters['new_link'], $parameters['client_flags'], $parameters['persistent'].
  347. * For details see documentation about the functions mysql_connect() and mysql_pconnect().
  348. * @link http://php.net/manual/en/function.mysql-connect.php
  349. * @link http://php.net/manual/en/function.mysql-pconnect.php
  350. */
  351. public static function connect($parameters = array()) {
  352. // A MySQL-specific implementation.
  353. if (!isset($parameters['server'])) {
  354. $parameters['server'] = @ini_get('mysql.default_host');
  355. if (empty($parameters['server'])) {
  356. $parameters['server'] = 'localhost:3306';
  357. }
  358. }
  359. if (!isset($parameters['username'])) {
  360. $parameters['username'] = @ini_get('mysql.default_user');
  361. }
  362. if (!isset($parameters['password'])) {
  363. $parameters['password'] = @ini_get('mysql.default_password');
  364. }
  365. if (!isset($parameters['new_link'])) {
  366. $parameters['new_link'] = false;
  367. }
  368. if (!isset($parameters['client_flags'])) {
  369. $parameters['client_flags'] = 0;
  370. }
  371. $persistent = isset($parameters['persistent']) ? $parameters['persistent'] : null;
  372. $server = isset($parameters['server']) ? $parameters['server'] : null;
  373. $username = isset($parameters['username']) ? $parameters['username'] : null;
  374. $password = isset($parameters['password']) ? $parameters['password'] : null;
  375. $client_flag = isset($parameters['client_flags']) ? $parameters['client_flags'] : null;
  376. $new_link = isset($parameters['new_link']) ? $parameters['new_link'] : null;
  377. $client_flags = isset($parameters['client_flags']) ? $parameters['client_flags'] : null;
  378. return $persistent
  379. ? mysql_pconnect($server, $username, $password, $client_flags)
  380. : mysql_connect($server, $username, $password, $new_link, $client_flags);
  381. }
  382. /**
  383. * Returns error number from the last operation done on the database server.
  384. * @param resource $connection (optional) The database server connection,
  385. * for detailed description see the method query().
  386. * @return int Returns the error number from the last database (operation, or 0 (zero) if no error occurred.
  387. */
  388. public static function errno($connection = null) {
  389. return self::use_default_connection($connection) ? mysql_errno() : mysql_errno($connection);
  390. }
  391. /**
  392. * Returns error text from the last operation done on the database server.
  393. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  394. * @return string Returns the error text from the last database operation, or '' (empty string) if no error occurred.
  395. */
  396. public static function error($connection = null) {
  397. return self::use_default_connection($connection) ? mysql_error() : mysql_error($connection);
  398. }
  399. /**
  400. * Escape MySQL wildchars _ and % in LIKE search
  401. * @param string The string to escape
  402. * @return string The escaped string
  403. */
  404. public static function escape_sql_wildcards($in_txt) {
  405. $out_txt = api_preg_replace("/_/", "\_", $in_txt);
  406. $out_txt = api_preg_replace("/%/", "\%", $out_txt);
  407. return $out_txt;
  408. }
  409. /**
  410. * Escapes a string to insert into the database as text
  411. * @param string The string to escape
  412. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  413. * @return string The escaped string
  414. * @author Yannick Warnier <yannick.warnier@dokeos.com>
  415. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  416. */
  417. public static function escape_string($string, $connection = null) {
  418. return get_magic_quotes_gpc()
  419. ? (self::use_default_connection($connection)
  420. ? mysql_real_escape_string(stripslashes($string))
  421. : mysql_real_escape_string(stripslashes($string), $connection))
  422. : (self::use_default_connection($connection)
  423. ? mysql_real_escape_string($string)
  424. : mysql_real_escape_string($string, $connection));
  425. }
  426. /**
  427. * Gets the array from a SQL result (as returned by Database::query) - help achieving database independence
  428. * @param resource The result from a call to sql_query (e.g. Database::query)
  429. * @param string Optional: "ASSOC","NUM" or "BOTH", as the constant used in mysql_fetch_array.
  430. * @return array Array of results as returned by php
  431. * @author Yannick Warnier <yannick.warnier@beeznest.com>
  432. */
  433. public static function fetch_array($result, $option = 'BOTH') {
  434. if ($result === false) { return array(); }
  435. return $option == 'ASSOC' ? mysql_fetch_array($result, MYSQL_ASSOC) : ($option == 'NUM' ? mysql_fetch_array($result, MYSQL_NUM) : mysql_fetch_array($result));
  436. }
  437. /**
  438. * Gets an associative array from a SQL result (as returned by Database::query).
  439. * This method is equivalent to calling Database::fetch_array() with 'ASSOC' value for the optional second parameter.
  440. * @param resource $result The result from a call to sql_query (e.g. Database::query).
  441. * @return array Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead.
  442. */
  443. public static function fetch_assoc($result) {
  444. return mysql_fetch_assoc($result);
  445. }
  446. /**
  447. * Gets the next row of the result of the SQL query (as returned by Database::query) in an object form
  448. * @param resource The result from a call to sql_query (e.g. Database::query)
  449. * @param string Optional class name to instanciate
  450. * @param array Optional array of parameters
  451. * @return object Object of class StdClass or the required class, containing the query result row
  452. * @author Yannick Warnier <yannick.warnier@dokeos.com>
  453. */
  454. public static function fetch_object($result, $class = null, $params = null) {
  455. return !empty($class) ? (is_array($params) ? mysql_fetch_object($result, $class, $params) : mysql_fetch_object($result, $class)) : mysql_fetch_object($result);
  456. }
  457. /**
  458. * Gets the array from a SQL result (as returned by Database::query) - help achieving database independence
  459. * @param resource The result from a call to sql_query (see Database::query()).
  460. * @return array Array of results as returned by php (mysql_fetch_row)
  461. */
  462. public static function fetch_row($result) {
  463. return mysql_fetch_row($result);
  464. }
  465. /**
  466. * Frees all the memory associated with the provided result identifier.
  467. * @return bool Returns TRUE on success or FALSE on failure.
  468. * Notes: Use this method if you are concerned about how much memory is being used for queries that return large result sets.
  469. * Anyway, all associated result memory is automatically freed at the end of the script's execution.
  470. */
  471. public static function free_result($result) {
  472. return mysql_free_result($result);
  473. }
  474. /**
  475. * Returns the database client library version.
  476. * @return strung Returns a string that represents the client library version.
  477. */
  478. public static function get_client_info() {
  479. return mysql_get_client_info();
  480. }
  481. /**
  482. * Returns a list of databases created on the server. The list may contain all of the
  483. * available database names or filtered database names by using a pattern.
  484. * @param string $pattern (optional) A pattern for filtering database names as if it was needed for the SQL's LIKE clause, for example 'chamilo_%'.
  485. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  486. * @return array Returns in an array the retrieved list of database names.
  487. */
  488. public static function get_databases($pattern = '', $connection = null) {
  489. $result = array();
  490. $query_result = Database::query(!empty($pattern) ? "SHOW DATABASES LIKE '".self::escape_string($pattern, $connection)."'" : "SHOW DATABASES", $connection);
  491. while ($row = Database::fetch_row($query_result)) {
  492. $result[] = $row[0];
  493. }
  494. return $result;
  495. }
  496. /**
  497. * 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.
  498. * By using a special option, this method is able to return an indexed list of fields' properties, where field names are keys.
  499. * @param string $table This is the examined table.
  500. * @param string $pattern (optional) A pattern for filtering field names as if it was needed for the SQL's LIKE clause, for example 'column_%'.
  501. * @param string $database (optional) The name of the targeted database. If it is omited, the current database is assumed, see Database::select_db().
  502. * @param bool $including_properties (optional) When this option is true, the returned result has the followong format:
  503. * array(field_name_1 => array(0 => property_1, 1 => property_2, ...), fieald_name_2 => array(0 => property_1, ...), ...)
  504. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  505. * @return array Returns in an array the retrieved list of field names.
  506. */
  507. public static function get_fields($table, $pattern = '', $database = '', $including_properties = false, $connection = null) {
  508. $result = array();
  509. $query = "SHOW COLUMNS FROM `".self::escape_string($table, $connection)."`";
  510. if (!empty($database)) {
  511. $query .= " FROM `".self::escape_string($database, $connection)."`";
  512. }
  513. if (!empty($pattern)) {
  514. $query .= " LIKE '".self::escape_string($pattern, $connection)."'";
  515. }
  516. $query_result = Database::query($query, $connection);
  517. if ($including_properties) {
  518. // Making an indexed list of the fields and their properties.
  519. while ($row = Database::fetch_row($query_result)) {
  520. $result[$row[0]] = $row;
  521. }
  522. } else {
  523. // Making a plain, flat list.
  524. while ($row = Database::fetch_row($query_result)) {
  525. $result[] = $row[0];
  526. }
  527. }
  528. return $result;
  529. }
  530. /**
  531. * Returns information about the type of the current connection and the server host name.
  532. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  533. * @return string/boolean Returns string data on success or FALSE on failure.
  534. */
  535. public static function get_host_info($connection = null) {
  536. return self::use_default_connection($connection) ? mysql_get_host_info() : mysql_get_host_info($connection);
  537. }
  538. /**
  539. * Retrieves database client/server protocol version.
  540. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  541. * @return int/boolean Returns the protocol version on success or FALSE on failure.
  542. */
  543. public static function get_proto_info($connection = null) {
  544. return self::use_default_connection($connection) ? mysql_get_proto_info() : mysql_get_proto_info($connection);
  545. }
  546. /**
  547. * Retrieves the database server version.
  548. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  549. * @return string/boolean Returns the MySQL server version on success or FALSE on failure.
  550. */
  551. public static function get_server_info($connection = null) {
  552. return self::use_default_connection($connection) ? mysql_get_server_info() : mysql_get_server_info($connection);
  553. }
  554. /**
  555. * Returns a list of tables within a database. The list may contain all of the
  556. * available table names or filtered table names by using a pattern.
  557. * @param string $database (optional) The name of the examined database. If it is omited, the current database is assumed, see Database::select_db().
  558. * @param string $pattern (optional) A pattern for filtering table names as if it was needed for the SQL's LIKE clause, for example 'access_%'.
  559. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  560. * @return array Returns in an array the retrieved list of table names.
  561. */
  562. public static function get_tables($database = '', $pattern = '', $connection = null) {
  563. $result = array();
  564. $query = "SHOW TABLES";
  565. if (!empty($database)) {
  566. $query .= " FROM `".self::escape_string($database, $connection)."`";
  567. }
  568. if (!empty($pattern)) {
  569. $query .= " LIKE '".self::escape_string($pattern, $connection)."'";
  570. }
  571. $query_result = Database::query($query, $connection);
  572. while ($row = Database::fetch_row($query_result)) {
  573. $result[] = $row[0];
  574. }
  575. return $result;
  576. }
  577. /**
  578. * Gets the ID of the last item inserted into the database
  579. * This should be updated to use ADODB at some point
  580. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  581. * @return int The last ID as returned by the DB function
  582. */
  583. public static function insert_id($connection = null) {
  584. return self::use_default_connection($connection) ? mysql_insert_id() : mysql_insert_id($connection);
  585. }
  586. /**
  587. * Gets the number of rows from the last query result - help achieving database independence
  588. * @param resource The result
  589. * @return integer The number of rows contained in this result
  590. * @author Yannick Warnier <yannick.warnier@dokeos.com>
  591. **/
  592. public static function num_rows($result) {
  593. return is_resource($result) ? mysql_num_rows($result) : false;
  594. }
  595. /**
  596. * Acts as the relative *_result() function of most DB drivers and fetches a
  597. * specific line and a field
  598. * @param resource The database resource to get data from
  599. * @param integer The row number
  600. * @param string Optional field name or number
  601. * @return mixed One cell of the result, or FALSE on error
  602. */
  603. public static function result($resource, $row, $field = '') {
  604. return self::num_rows($resource) > 0 ? (!empty($field) ? mysql_result($resource, $row, $field) : mysql_result($resource, $row)) : null;
  605. }
  606. /**
  607. * This method returns a resource
  608. * Documentation has been added by Arthur Portugal
  609. * Some adaptations have been implemented by Ivan Tcholakov, 2009, 2010
  610. * @author Olivier Brouckaert
  611. * @param string $query The SQL query
  612. * @param resource $connection (optional) The database server (MySQL) connection.
  613. * If it is not specified, the connection opened by mysql_connect() is assumed.
  614. * If no connection is found, the server will try to create one as if mysql_connect() was called with no arguments.
  615. * If no connection is found or established, an E_WARNING level error is generated.
  616. * @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)
  617. * @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)
  618. * @return resource The returned result from the query
  619. * Note: The parameter $connection could be skipped. Here are examples of this method usage:
  620. * Database::query($query);
  621. * $result = Database::query($query);
  622. * Database::query($query, $connection);
  623. * $result = Database::query($query, $connection);
  624. * The following ways for calling this method are obsolete:
  625. * Database::query($query, __FILE__, __LINE__);
  626. * $result = Database::query($query, __FILE__, __LINE__);
  627. * Database::query($query, $connection, __FILE__, __LINE__);
  628. * $result = Database::query($query, $connection, __FILE__, __LINE__);
  629. */
  630. public static function query($query, $connection = null, $file = null, $line = null) {
  631. $use_default_connection = self::use_default_connection($connection);
  632. if ($use_default_connection) {
  633. // Let us do parameter shifting, thus the method would be similar
  634. // (in regard to parameter order) to the original function mysql_query().
  635. $line = $file;
  636. $file = $connection;
  637. $connection = null;
  638. }
  639. //@todo remove this before the stable release
  640. //Check if the table contains a c_ (means a course id)
  641. if (api_get_setting('server_type')==='test' && strpos($query, 'c_')) {
  642. //Check if the table contains inner joins
  643. if (
  644. strpos($query, 'assoc_handle') === false &&
  645. strpos($query, 'olpc_peru_filter') === false &&
  646. strpos($query, 'allow_public_certificates') === false &&
  647. strpos($query, 'DROP TABLE IF EXISTS') === false &&
  648. strpos($query, 'thematic_advance') === false &&
  649. strpos($query, 'thematic_plan') === false &&
  650. strpos($query, 'track_c_countries') === false &&
  651. strpos($query, 'track_c_os') === false &&
  652. strpos($query, 'track_c_providers') === false &&
  653. strpos($query, 'track_c_referers') === false &&
  654. strpos($query, 'track_c_browsers') === false &&
  655. strpos($query, 'settings_current') === false &&
  656. strpos($query, 'dokeos_classic_2D') === false &&
  657. strpos($query, 'cosmic_campus') === false &&
  658. strpos($query, 'static_') === false &&
  659. strpos($query, 'public_admin') === false &&
  660. strpos($query, 'chamilo_electric_blue') === false &&
  661. strpos($query, 'wcag_anysurfer_public_pages') === false &&
  662. strpos($query, 'specific_field') === false &&
  663. strpos($query, 'down_doc_path') === false &&
  664. strpos($query, 'INNER JOIN') === false &&
  665. strpos($query, 'inner join') === false &&
  666. strpos($query, 'left join') === false &&
  667. strpos($query, 'LEFT JOIN') === false &&
  668. strpos($query, 'insert') === false &&
  669. strpos($query, 'INSERT') === false &&
  670. strpos($query, 'ALTER') === false &&
  671. strpos($query, 'alter') === false &&
  672. strpos($query, 'c_id') === false &&
  673. strpos($query, 'create table') === false &&
  674. strpos($query, 'CREATE TABLE') === false &&
  675. strpos($query, 'AUTO_INCREMENT') === false
  676. ) {
  677. //@todo remove this
  678. echo '<pre>';
  679. $message = '<h4>Dev message: please add the c_id field in this query or report this error in support.chamilo.org </h4>';
  680. $message .= $query;
  681. echo $message;
  682. echo '</pre>';
  683. //error_log($message);
  684. }
  685. }
  686. if (!($result = $use_default_connection ? mysql_query($query) : mysql_query($query, $connection))) {
  687. $backtrace = debug_backtrace(); // Retrieving information about the caller statement.
  688. if (isset($backtrace[0])) {
  689. $caller = & $backtrace[0];
  690. } else {
  691. $caller = array();
  692. }
  693. if (isset($backtrace[1])) {
  694. $owner = & $backtrace[1];
  695. } else {
  696. $owner = array();
  697. }
  698. if (empty($file)) {
  699. $file = $caller['file'];
  700. }
  701. if (empty($line) && $line !== false) {
  702. $line = $caller['line'];
  703. }
  704. $type = isset($owner['type']) ? $owner['type'] : null;
  705. $function = $owner['function'];
  706. $class = isset($owner['class']) ? $owner['class'] : null;
  707. $server_type = api_get_setting('server_type');
  708. if (!empty($line) && !empty($server_type) && $server_type != 'production') {
  709. $info = '<pre>' .
  710. '<strong>DATABASE ERROR #'.self::errno($connection).':</strong><br /> ' .
  711. self::remove_XSS(self::error($connection)) . '<br />' .
  712. '<strong>QUERY :</strong><br /> ' .
  713. self::remove_XSS($query) . '<br />' .
  714. '<strong>FILE :</strong><br /> ' .
  715. (empty($file) ? ' unknown ' : $file) . '<br />' .
  716. '<strong>LINE :</strong><br /> ' .
  717. (empty($line) ? ' unknown ' : $line) . '<br />';
  718. if (empty($type)) {
  719. if (!empty($function)) {
  720. $info .= '<strong>FUNCTION :</strong><br /> ' . $function;
  721. }
  722. } else {
  723. if (!empty($class) && !empty($function)) {
  724. $info .= '<strong>CLASS :</strong><br /> ' . $class . '<br />';
  725. $info .= '<strong>METHOD :</strong><br /> ' . $function;
  726. }
  727. }
  728. $info .= '</pre>';
  729. echo $info;
  730. }
  731. if (isset(self::$log_queries) && self::$log_queries) {
  732. error_log("---------------- SQL error ---------------- ");
  733. error_log($query);
  734. error_log('error #'.self::errno($connection));
  735. error_log('error: '.self::error($connection));
  736. $info = 'FILE: ' .(empty($file) ? ' unknown ' : $file);
  737. $info .= ' +'.(empty($line) ? ' unknown ' : $line);
  738. error_log($info);
  739. if (empty($type)) {
  740. if (!empty($function)) {
  741. $info = 'FUNCTION: ' . $function;
  742. error_log($info);
  743. }
  744. } else {
  745. if (!empty($class) && !empty($function)) {
  746. $info = 'CLASS: ' . $class.' METHOD: '.$function;
  747. error_log($info);
  748. }
  749. }
  750. error_log("---------------- end ----------------");
  751. }
  752. }
  753. return $result;
  754. }
  755. /**
  756. * Selects a database.
  757. * @param string $database_name The name of the database that is to be selected.
  758. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  759. * @return bool Returns TRUE on success or FALSE on failure.
  760. */
  761. public static function select_db($database_name, $connection = null) {
  762. return self::use_default_connection($connection) ? mysql_select_db($database_name) : mysql_select_db($database_name, $connection);
  763. }
  764. /**
  765. * Stores a query result into an array.
  766. *
  767. * @author Olivier Brouckaert
  768. * @param resource $result - the return value of the query
  769. * @param option BOTH, ASSOC, or NUM
  770. * @return array - the value returned by the query
  771. */
  772. public static function store_result($result, $option = 'BOTH') {
  773. $array = array();
  774. if ($result !== false) { // For isolation from database engine's behaviour.
  775. while ($row = self::fetch_array($result, $option)) {
  776. $array[] = $row;
  777. }
  778. }
  779. return $array;
  780. }
  781. /*
  782. Encodings and collations supported by MySQL database server
  783. */
  784. /**
  785. * Checks whether a given encoding is supported by the database server.
  786. * @param string $encoding The encoding (a system conventional id, for example 'UTF-8') to be checked.
  787. * @return bool Returns a boolean value as a check-result.
  788. * @author Ivan Tcholakov
  789. */
  790. public static function is_encoding_supported($encoding) {
  791. static $supported = array();
  792. if (!isset($supported[$encoding])) {
  793. $supported[$encoding] = false;
  794. if (strlen($db_encoding = self::to_db_encoding($encoding)) > 0) {
  795. if (self::num_rows(self::query("SHOW CHARACTER SET WHERE Charset = '".self::escape_string($db_encoding)."';")) > 0) {
  796. $supported[$encoding] = true;
  797. }
  798. }
  799. }
  800. return $supported[$encoding];
  801. }
  802. /**
  803. * Constructs a SQL clause about default character set and default collation for newly created databases and tables.
  804. * Example: Database::make_charset_clause('UTF-8', 'bulgarian') returns
  805. * DEFAULT CHARACTER SET `utf8` DEFAULT COLLATE `utf8_general_ci`
  806. * @param string $encoding (optional) The default database/table encoding (a system conventional id) to be used.
  807. * @param string $language (optional) Language (a system conventional id) used for choosing language sensitive collation (if it is possible).
  808. * @return string Returns the constructed SQL clause or empty string if $encoding is not correct or is not supported.
  809. * @author Ivan Tcholakov
  810. */
  811. public static function make_charset_clause($encoding = null, $language = null) {
  812. if (empty($encoding)) {
  813. $encoding = api_get_system_encoding();
  814. }
  815. if (empty($language)) {
  816. $language = api_get_interface_language();
  817. }
  818. $charset_clause = '';
  819. if (self::is_encoding_supported($encoding)) {
  820. $db_encoding = Database::to_db_encoding($encoding);
  821. $charset_clause .= " DEFAULT CHARACTER SET `".$db_encoding."`";
  822. $db_collation = Database::to_db_collation($encoding, $language);
  823. if (!empty($db_collation)) {
  824. $charset_clause .= " DEFAULT COLLATE `".$db_collation."`";
  825. }
  826. }
  827. return $charset_clause;
  828. }
  829. /**
  830. * Converts an encoding identificator to MySQL-specific encoding identifictor,
  831. * i.e. 'UTF-8' --> 'utf8'.
  832. * @param string $encoding The conventional encoding identificator.
  833. * @return string Returns the corresponding MySQL-specific encoding identificator if any, otherwise returns NULL.
  834. * @author Ivan Tcholakov
  835. */
  836. public static function to_db_encoding($encoding) {
  837. static $result = array();
  838. if (!isset($result[$encoding])) {
  839. $result[$encoding] = null;
  840. $encoding_map = & self::get_db_encoding_map();
  841. foreach ($encoding_map as $key => $value) {
  842. if (api_equal_encodings($encoding, $key)) {
  843. $result[$encoding] = $value;
  844. break;
  845. }
  846. }
  847. }
  848. return $result[$encoding];
  849. }
  850. /**
  851. * Converts a MySQL-specific encoding identifictor to conventional encoding identificator,
  852. * i.e. 'utf8' --> 'UTF-8'.
  853. * @param string $encoding The MySQL-specific encoding identificator.
  854. * @return string Returns the corresponding conventional encoding identificator if any, otherwise returns NULL.
  855. * @author Ivan Tcholakov
  856. */
  857. public static function from_db_encoding($db_encoding) {
  858. static $result = array();
  859. if (!isset($result[$db_encoding])) {
  860. $result[$db_encoding] = null;
  861. $encoding_map = & self::get_db_encoding_map();
  862. foreach ($encoding_map as $key => $value) {
  863. if (strtolower($db_encoding) == $value) {
  864. $result[$db_encoding] = $key;
  865. break;
  866. }
  867. }
  868. }
  869. return $result[$db_encoding];
  870. }
  871. /**
  872. * Chooses the default MySQL-specific collation from given encoding and language.
  873. * @param string $encoding A conventional encoding id, i.e. 'UTF-8'
  874. * @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.
  875. * @return string Returns a suitable default collation, for example 'utf8_general_ci', or NULL if collation was not found.
  876. * @author Ivan Tcholakov
  877. */
  878. public static function to_db_collation($encoding, $language = null) {
  879. static $result = array();
  880. if (!isset($result[$encoding][$language])) {
  881. $result[$encoding][$language] = null;
  882. if (self::is_encoding_supported($encoding)) {
  883. $db_encoding = self::to_db_encoding($encoding);
  884. if (!empty($language)) {
  885. $lang = api_purify_language_id($language);
  886. $res = self::check_db_collation($db_encoding, $lang);
  887. if (empty($res)) {
  888. $db_collation_map = & self::get_db_collation_map();
  889. if (isset($db_collation_map[$lang])) {
  890. $res = self::check_db_collation($db_encoding, $db_collation_map[$lang]);
  891. }
  892. }
  893. if (empty($res)) {
  894. $res = self::check_db_collation($db_encoding, null);
  895. }
  896. $result[$encoding][$language] = $res;
  897. } else {
  898. $result[$encoding][$language] = self::check_db_collation($db_encoding, null);
  899. }
  900. }
  901. }
  902. return $result[$encoding][$language];
  903. }
  904. /*
  905. Private methods
  906. You should not access these from outside the class
  907. No effort is made to keep the names / results the same.
  908. */
  909. /**
  910. * Glues a course database.
  911. * glue format from local.inc.php.
  912. */
  913. private static function glue_course_database_name($database_name) {
  914. return self::get_course_table_prefix().$database_name.self::get_database_glue();
  915. }
  916. /**
  917. * @param string $database_name, can be empty to use current course db
  918. *
  919. * @return the glued parameter if it is not empty,
  920. * or the current course database (glued) if the parameter is empty.
  921. */
  922. private static function fix_database_parameter($database_name) {
  923. if (empty($database_name)) {
  924. $course_info = api_get_course_info();
  925. return $course_info['dbNameGlu'];
  926. }
  927. return self::glue_course_database_name($database_name);
  928. }
  929. /**
  930. * Structures a course database and table name to ready them
  931. * for querying. The course database parameter is considered glued:
  932. * e.g. COURSE001`.`
  933. */
  934. private static function format_glued_course_table_name($database_name_with_glue, $table) {
  935. return '`'.$database_name_with_glue.$table.'`';
  936. }
  937. /**
  938. * Structures a database and table name to ready them
  939. * for querying. The database parameter is considered not glued,
  940. * just plain e.g. COURSE001
  941. */
  942. private static function format_table_name($database, $table) {
  943. global $_configuration;
  944. if ($_configuration['single_database']) {
  945. $table_name = '`'.$database.'`.`'.$table.'`';
  946. } else {
  947. $table_name = '`'.$database.$_configuration['db_glue'].$table.'`';
  948. }
  949. return $table_name;
  950. }
  951. /**
  952. * This private method is to be used by the other methods in this class for
  953. * checking whether the input parameter $connection actually has been provided.
  954. * If the input parameter connection is not a resource or if it is not FALSE (in case of error)
  955. * then the default opened connection should be used by the called method.
  956. * @param resource/boolean $connection The checked parameter $connection.
  957. * @return boolean TRUE means that calling method should use the default connection.
  958. * FALSE means that (valid) parameter $connection has been provided and it should be used.
  959. */
  960. private static function use_default_connection($connection) {
  961. return !is_resource($connection) && $connection !== false;
  962. }
  963. /**
  964. * This private method tackles the XSS injections. It is similar to Security::remove_XSS() and works always,
  965. * including the time of initialization when the class Security has not been loaded yet.
  966. * @param string The input variable to be filtered from XSS, in this class it is expected to be a string.
  967. * @return string Returns the filtered string as a result.
  968. */
  969. private static function remove_XSS(& $var) {
  970. return class_exists('Security') ? Security::remove_XSS($var) : @htmlspecialchars($var, ENT_QUOTES, api_get_system_encoding());
  971. }
  972. /**
  973. * This private method encapsulates a table with relations between
  974. * conventional and MuSQL-specific encoding identificators.
  975. * @author Ivan Tcholakov
  976. */
  977. private static function & get_db_encoding_map() {
  978. static $encoding_map = array(
  979. 'ARMSCII-8' => 'armscii8',
  980. 'BIG5' => 'big5',
  981. 'BINARY' => 'binary',
  982. 'CP866' => 'cp866',
  983. 'EUC-JP' => 'ujis',
  984. 'EUC-KR' => 'euckr',
  985. 'GB2312' => 'gb2312',
  986. 'GBK' => 'gbk',
  987. 'ISO-8859-1' => 'latin1',
  988. 'ISO-8859-2' => 'latin2',
  989. 'ISO-8859-7' => 'greek',
  990. 'ISO-8859-8' => 'hebrew',
  991. 'ISO-8859-9' => 'latin5',
  992. 'ISO-8859-13' => 'latin7',
  993. 'ISO-8859-15' => 'latin1',
  994. 'KOI8-R' => 'koi8r',
  995. 'KOI8-U' => 'koi8u',
  996. 'SHIFT-JIS' => 'sjis',
  997. 'TIS-620' => 'tis620',
  998. 'US-ASCII' => 'ascii',
  999. 'UTF-8' => 'utf8',
  1000. 'WINDOWS-1250' => 'cp1250',
  1001. 'WINDOWS-1251' => 'cp1251',
  1002. 'WINDOWS-1252' => 'latin1',
  1003. 'WINDOWS-1256' => 'cp1256',
  1004. 'WINDOWS-1257' => 'cp1257'
  1005. );
  1006. return $encoding_map;
  1007. }
  1008. /**
  1009. * A helper language id translation table for choosing some collations.
  1010. * @author Ivan Tcholakov
  1011. */
  1012. private static function & get_db_collation_map() {
  1013. static $db_collation_map = array(
  1014. 'german' => 'german2',
  1015. 'simpl_chinese' => 'chinese',
  1016. 'trad_chinese' => 'chinese',
  1017. 'turkce' => 'turkish'
  1018. );
  1019. return $db_collation_map;
  1020. }
  1021. /**
  1022. * Constructs a MySQL-specific collation and checks whether it is supported by the database server.
  1023. * @param string $db_encoding A MySQL-specific encoding id, i.e. 'utf8'
  1024. * @param string $language A MySQL-compatible language id, i.e. 'bulgarian'
  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. private static function check_db_collation($db_encoding, $language) {
  1029. if (empty($db_encoding)) {
  1030. return null;
  1031. }
  1032. if (empty($language)) {
  1033. $result = self::fetch_array(self::query("SHOW COLLATION WHERE Charset = '".self::escape_string($db_encoding)."' AND `Default` = 'Yes';"), 'NUM');
  1034. return $result ? $result[0] : null;
  1035. }
  1036. $collation = $db_encoding.'_'.$language.'_ci';
  1037. $query_result = self::query("SHOW COLLATION WHERE Charset = '".self::escape_string($db_encoding)."';");
  1038. while ($result = self::fetch_array($query_result, 'NUM')) {
  1039. if ($result[0] == $collation) {
  1040. return $collation;
  1041. }
  1042. }
  1043. return null;
  1044. }
  1045. /*
  1046. New useful DB functions
  1047. */
  1048. /**
  1049. * Experimental useful database insert
  1050. * @todo lot of stuff to do here
  1051. */
  1052. public static function insert($table_name, $attributes, $show_query = false)
  1053. {
  1054. if (empty($attributes) || empty($table_name)) {
  1055. return false;
  1056. }
  1057. $filtred_attributes = array();
  1058. foreach($attributes as $key => $value) {
  1059. $filtred_attributes[$key] = "'".self::escape_string($value)."'";
  1060. }
  1061. //@todo check if the field exists in the table we should use a describe of that table
  1062. $params = array_keys($filtred_attributes);
  1063. $values = array_values($filtred_attributes);
  1064. if (!empty($params) && !empty($values)) {
  1065. $sql = 'INSERT INTO '.$table_name.' ('.implode(',',$params).') VALUES ('.implode(',',$values).')';
  1066. self::query($sql);
  1067. if ($show_query) {
  1068. var_dump($sql);
  1069. }
  1070. return self::insert_id();
  1071. }
  1072. return false;
  1073. }
  1074. /**
  1075. * Experimental useful database finder
  1076. * @todo lot of stuff to do here
  1077. * @todo known issues, it doesn't work when using LIKE conditions
  1078. * @example array('where'=> array('course_code LIKE "?%"'))
  1079. * @example array('where'=> array('type = ? AND category = ?' => array('setting', 'Plugins'))
  1080. * @example array('where'=> array('name = "Julio" AND lastname = "montoya"))
  1081. */
  1082. public static function select($columns, $table_name, $conditions = array(), $type_result = 'all', $option = 'ASSOC')
  1083. {
  1084. $conditions = self::parse_conditions($conditions);
  1085. //@todo we could do a describe here to check the columns ...
  1086. $clean_columns = '';
  1087. if (is_array($columns)) {
  1088. $clean_columns = implode(',', $columns);
  1089. } else {
  1090. if ($columns == '*') {
  1091. $clean_columns = '*';
  1092. } else {
  1093. $clean_columns = (string)$columns;
  1094. }
  1095. }
  1096. $sql = "SELECT $clean_columns FROM $table_name $conditions";
  1097. $result = self::query($sql);
  1098. $array = array();
  1099. //if (self::num_rows($result) > 0 ) {
  1100. if ($type_result == 'all') {
  1101. while ($row = self::fetch_array($result, $option)) {
  1102. if (isset($row['id'])) {
  1103. $array[$row['id']] = $row;
  1104. } else {
  1105. $array[] = $row;
  1106. }
  1107. }
  1108. } else {
  1109. $array = self::fetch_array($result, $option);
  1110. }
  1111. return $array;
  1112. }
  1113. /**
  1114. * Parses WHERE/ORDER conditions i.e array('where'=>array('id = ?' =>'4'), 'order'=>'id DESC'))
  1115. * @todo known issues, it doesn't work when using LIKE conditions example: array('where'=>array('course_code LIKE "?%"'))
  1116. * @param array
  1117. * @todo lot of stuff to do here
  1118. */
  1119. static function parse_conditions($conditions) {
  1120. if (empty($conditions)) {
  1121. return '';
  1122. }
  1123. $return_value = $where_return = '';
  1124. foreach ($conditions as $type_condition => $condition_data) {
  1125. if ($condition_data == false) {
  1126. continue;
  1127. }
  1128. $type_condition = strtolower($type_condition);
  1129. switch ($type_condition) {
  1130. case 'where':
  1131. foreach ($condition_data as $condition => $value_array) {
  1132. if (is_array($value_array)) {
  1133. $clean_values = array();
  1134. foreach($value_array as $item) {
  1135. $item = Database::escape_string($item);
  1136. $clean_values[]= $item;
  1137. }
  1138. } else {
  1139. $value_array = Database::escape_string($value_array);
  1140. $clean_values = $value_array;
  1141. }
  1142. if (!empty($condition) && $clean_values != '') {
  1143. $condition = str_replace('%',"'@percentage@'", $condition); //replace "%"
  1144. $condition = str_replace("'?'","%s", $condition);
  1145. $condition = str_replace("?","%s", $condition);
  1146. $condition = str_replace("@%s@","@-@", $condition);
  1147. $condition = str_replace("%s","'%s'", $condition);
  1148. $condition = str_replace("@-@","@%s@", $condition);
  1149. //Treat conditons as string
  1150. $condition = vsprintf($condition, $clean_values);
  1151. $condition = str_replace('@percentage@','%', $condition); //replace "%"
  1152. $where_return .= $condition;
  1153. }
  1154. }
  1155. if (!empty($where_return)) {
  1156. $return_value = " WHERE $where_return" ;
  1157. }
  1158. break;
  1159. case 'order':
  1160. $order_array = $condition_data;
  1161. if (!empty($order_array)) {
  1162. // 'order' => 'id desc, name desc'
  1163. $order_array = self::escape_string($order_array);
  1164. $new_order_array = explode(',', $order_array);
  1165. $temp_value = array();
  1166. foreach($new_order_array as $element) {
  1167. $element = explode(' ', $element);
  1168. $element = array_filter($element);
  1169. $element = array_values($element);
  1170. if (!empty($element[1])) {
  1171. $element[1] = strtolower($element[1]);
  1172. $order = 'DESC';
  1173. if (in_array($element[1], array('desc', 'asc'))) {
  1174. $order = $element[1];
  1175. }
  1176. $temp_value[]= $element[0].' '.$order.' ';
  1177. } else {
  1178. //by default DESC
  1179. $temp_value[]= $element[0].' DESC ';
  1180. }
  1181. }
  1182. if (!empty($temp_value)) {
  1183. $return_value .= ' ORDER BY '.implode(', ', $temp_value);
  1184. } else {
  1185. //$return_value .= '';
  1186. }
  1187. }
  1188. break;
  1189. case 'limit':
  1190. $limit_array = explode(',', $condition_data);
  1191. if (!empty($limit_array)) {
  1192. if (count($limit_array) > 1) {
  1193. $return_value .= ' LIMIT '.intval($limit_array[0]).' , '.intval($limit_array[1]);
  1194. } else {
  1195. $return_value .= ' LIMIT '.intval($limit_array[0]);
  1196. }
  1197. }
  1198. break;
  1199. }
  1200. }
  1201. return $return_value;
  1202. }
  1203. public static function parse_where_conditions($coditions) {
  1204. return self::parse_conditions(array('where'=>$coditions));
  1205. }
  1206. /**
  1207. * Experimental useful database update
  1208. * @todo lot of stuff to do here
  1209. */
  1210. public static function delete($table_name, $where_conditions, $show_query = false) {
  1211. $result = false;
  1212. $where_return = self::parse_where_conditions($where_conditions);
  1213. $sql = "DELETE FROM $table_name $where_return ";
  1214. if ($show_query) { echo $sql; echo '<br />'; }
  1215. $result = self::query($sql);
  1216. $affected_rows = self::affected_rows();
  1217. //@todo should return affected_rows for
  1218. return $affected_rows;
  1219. }
  1220. /**
  1221. * Experimental useful database update
  1222. * @param string table name use Database::get_main_table
  1223. * @param array array with values to updates, keys are the fields in the database: Example: $params['name'] = 'Julio'; $params['lastname'] = 'Montoya';
  1224. * @param array where conditions i.e array('id = ?' =>'4')
  1225. * @todo lot of stuff to do here
  1226. */
  1227. public static function update($table_name, $attributes, $where_conditions = array(), $show_query = false) {
  1228. if (!empty($table_name) && !empty($attributes)) {
  1229. $update_sql = '';
  1230. //Cleaning attributes
  1231. $count = 1;
  1232. foreach ($attributes as $key=>$value) {
  1233. if (!is_array($value))
  1234. $value = self::escape_string($value);
  1235. $update_sql .= "$key = '$value' ";
  1236. if ($count < count($attributes)) {
  1237. $update_sql.=', ';
  1238. }
  1239. $count++;
  1240. }
  1241. if (!empty($update_sql)) {
  1242. //Parsing and cleaning the where conditions
  1243. $where_return = self::parse_where_conditions($where_conditions);
  1244. $sql = "UPDATE $table_name SET $update_sql $where_return ";
  1245. if ($show_query) { var_dump($sql); }
  1246. $result = self::query($sql);
  1247. $affected_rows = self::affected_rows();
  1248. return $affected_rows;
  1249. }
  1250. }
  1251. return false;
  1252. }
  1253. /*
  1254. DEPRECATED METHODS
  1255. */
  1256. /**
  1257. * @deprecated Use api_get_language_isocode($language) instead.
  1258. */
  1259. public static function get_language_isocode($language) {
  1260. return api_get_language_isocode($language);
  1261. }
  1262. /**
  1263. * @deprecated Use Database::insert_id() instead.
  1264. */
  1265. public static function get_last_insert_id() {
  1266. return mysql_insert_id();
  1267. }
  1268. }