database.lib.php 61 KB

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