database.lib.php 24 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713
  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. *
  7. * This library now uses a Doctrine DBAL Silex service provider
  8. *
  9. * @package chamilo.library
  10. */
  11. use Doctrine\DBAL\Connection;
  12. use Doctrine\ORM\EntityManager;
  13. use Doctrine\DBAL\Driver\Statement;
  14. /**
  15. * Database class definition
  16. * @package chamilo.database
  17. */
  18. class Database
  19. {
  20. /**
  21. * The main connection
  22. *
  23. * @var Connection
  24. */
  25. private static $db;
  26. /**
  27. * Read connection
  28. *
  29. * @var Connection
  30. */
  31. private static $connectionRead;
  32. /**
  33. * Write connection
  34. *
  35. * @var Connection
  36. */
  37. private static $connectionWrite;
  38. /**
  39. * @var EntityManager
  40. */
  41. private static $em;
  42. /**
  43. * Constructor
  44. */
  45. public function __construct()
  46. {
  47. }
  48. /**
  49. * Return current connection
  50. * @return Connection
  51. */
  52. public function getConnection()
  53. {
  54. return self::$db;
  55. }
  56. /**
  57. * @param $db
  58. * @param $dbs
  59. */
  60. public function setConnection($db, $dbs = array())
  61. {
  62. self::$db = $db;
  63. // Using read/write connections see the services.php file
  64. self::$connectionRead = isset($dbs['db_read']) ? $dbs['db_read'] : $db;
  65. self::$connectionWrite = isset($dbs['db_write']) ? $dbs['db_write'] : $db;
  66. }
  67. /**
  68. * @param EntityManager $em
  69. */
  70. public function setManager($em)
  71. {
  72. self::$em = $em;
  73. }
  74. /**
  75. * @return EntityManager
  76. */
  77. public static function getManager()
  78. {
  79. return self::$em;
  80. }
  81. /**
  82. * Returns the name of the main database.
  83. * @return string
  84. */
  85. public static function get_main_database()
  86. {
  87. return self::$db->getDatabase();
  88. }
  89. /**
  90. * The glue is the string needed between database and table.
  91. * The trick is: in multiple databases, this is a period (with backticks).
  92. * In single database, this can be e.g. an underscore so we just fake
  93. * there are multiple databases and the code can be written independent
  94. * of the single / multiple database setting.
  95. * @return string
  96. */
  97. public static function get_database_glue()
  98. {
  99. return `.`;
  100. }
  101. /*
  102. Table name methods
  103. Use these methods to get table names for queries,
  104. instead of constructing them yourself.
  105. Backticks automatically surround the result,
  106. e.g. COURSE_NAME.link
  107. so the queries can look cleaner.
  108. Example:
  109. $table = Database::get_course_table(TABLE_DOCUMENT);
  110. $sql_query = "SELECT * FROM $table WHERE $condition";
  111. $sql_result = Database::query($sql_query);
  112. $result = Database::fetch_array($sql_result);
  113. */
  114. /**
  115. * This function returns the correct complete name of any table of the main
  116. * database of which you pass the short name as a parameter.
  117. * Define table names as constants in this library and use them
  118. * instead of directly using magic words in your tool code.
  119. *
  120. * @param string $short_table_name, the name of the table
  121. * @return string
  122. */
  123. public static function get_main_table($short_table_name)
  124. {
  125. return self::format_table_name(self::get_main_database(), $short_table_name);
  126. }
  127. /**
  128. * This method returns the correct complete name of any course table of
  129. * which you pass the short name as a parameter.
  130. * Define table names as constants in this library and use them
  131. * instead of directly using magic words in your tool code.
  132. *
  133. * @param string $short_table_name, the name of the table
  134. * @return string
  135. *
  136. */
  137. public static function get_course_table($short_table_name)
  138. {
  139. return self::format_table_name(self::get_main_database(), DB_COURSE_PREFIX.$short_table_name);
  140. }
  141. /**
  142. * Returns the number of affected rows in the last database operation.
  143. * @param \Doctrine\DBAL\Driver\Statement $result
  144. * @return int
  145. */
  146. public static function affected_rows(Statement $result = null)
  147. {
  148. return $result->rowCount();
  149. //return self::use_default_connection($connection) ? mysql_affected_rows() : mysql_affected_rows($connection);
  150. }
  151. /**
  152. * Gets the array from a SQL result (as returned by Database::query) - help achieving database independence
  153. * @param resource The result from a call to sql_query (e.g. Database::query)
  154. * @param string Optional: "ASSOC","NUM" or "BOTH", as the constant used in mysql_fetch_array.
  155. * @return array Array of results as returned by php
  156. * @author Yannick Warnier <yannick.warnier@beeznest.com>
  157. */
  158. public static function fetch_array(Statement $result, $option = 'BOTH')
  159. {
  160. if ($result === false) {
  161. return array();
  162. }
  163. return $result->fetch(self::customOptionToDoctrineOption($option));
  164. /*return $option == 'ASSOC' ? mysql_fetch_array($result, MYSQL_ASSOC) : ($option == 'NUM' ? mysql_fetch_array(
  165. $result,
  166. MYSQL_NUM
  167. ) : mysql_fetch_array($result));*/
  168. }
  169. /**
  170. * Gets an associative array from a SQL result (as returned by Database::query).
  171. * This method is equivalent to calling Database::fetch_array() with 'ASSOC' value for the optional second parameter.
  172. * @param resource $result The result from a call to sql_query (e.g. Database::query).
  173. * @return array Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead.
  174. */
  175. public static function fetch_assoc(Statement $result)
  176. {
  177. return $result->fetch(PDO::FETCH_ASSOC);
  178. //return mysql_fetch_assoc($result);
  179. }
  180. /**
  181. * Gets the next row of the result of the SQL query (as returned by Database::query) in an object form
  182. * @param \Doctrine\DBAL\Driver\Statement The result from a call to Database::query())
  183. * @param string Optional class name to instanciate
  184. * @param array Optional array of parameters
  185. * @return object Object of class StdClass or the required class, containing the query result row
  186. * @author Yannick Warnier <yannick.warnier@dokeos.com>
  187. */
  188. public static function fetch_object(Statement $result)
  189. {
  190. // Waiting for http://www.doctrine-project.org/jira/browse/DBAL-544 in order to know which constant use.
  191. //return $result->fetch(\Doctrine\ORM\Query::HYDRATE_OBJECT);
  192. return $result->fetch(PDO::FETCH_OBJ);
  193. /*return !empty($class) ? (is_array($params) ? mysql_fetch_object($result, $class, $params) : mysql_fetch_object(
  194. $result,
  195. $class
  196. )) : mysql_fetch_object($result);*/
  197. }
  198. /**
  199. * Gets the array from a SQL result (as returned by Database::query) - help achieving database independence
  200. * @param \Doctrine\DBAL\Driver\Statement The result from a call to Database::query())
  201. * @return array Array of results as returned by php
  202. */
  203. public static function fetch_row(Statement $result)
  204. {
  205. return $result->fetch(PDO::FETCH_NUM);
  206. //return mysql_fetch_row($result);
  207. }
  208. /**
  209. * Gets the ID of the last item inserted into the database
  210. * @return int The last ID as returned by the DB function
  211. */
  212. public static function insert_id()
  213. {
  214. return self::$connectionWrite->lastInsertId();
  215. }
  216. /**
  217. * Gets the number of rows from the last query result - help achieving database independence
  218. * @param Statement $result
  219. * @return integer The number of rows contained in this result
  220. **/
  221. public static function num_rows(Statement $result)
  222. {
  223. return $result->rowCount();
  224. }
  225. /**
  226. * Acts as the relative *_result() function of most DB drivers and fetches a
  227. * specific line and a field
  228. * @param Statement The database resource to get data from
  229. * @param integer The row number
  230. * @param string Optional field name or number
  231. * @return mixed One cell of the result, or FALSE on error
  232. */
  233. public static function result(Statement $resource, $row, $field = 0)
  234. {
  235. if ($resource->rowCount() > 0) {
  236. $result = $resource->fetchAll(PDO::FETCH_BOTH);
  237. return $result[$row][$field];
  238. }
  239. return null;
  240. }
  241. /**
  242. * Frees all the memory associated with the provided result identifier.
  243. * @return bool Returns TRUE on success or FALSE on failure.
  244. * Notes: Use this method if you are concerned about how much memory is being used for queries that return large result sets.
  245. * Anyway, all associated result memory is automatically freed at the end of the script's execution.
  246. */
  247. public static function free_result(Statement $result)
  248. {
  249. $result->closeCursor();
  250. //return mysql_free_result($result);
  251. }
  252. /**
  253. * Detects if a query is going to modify something in the database in order to use the write connection
  254. * @param string $query
  255. * @return bool
  256. */
  257. public static function isWriteQuery($query)
  258. {
  259. $isWriteQuery = preg_match("/UPDATE(.*) FROM/i", $query) ||
  260. preg_match("/INSERT INTO/i", $query) ||
  261. preg_match("/REPLACE INTO/i", $query) ||
  262. preg_match("/DELETE FROM/i", $query);
  263. return $isWriteQuery;
  264. }
  265. /**
  266. * Escapes a string to insert into the database as text
  267. * @param string The string to escape
  268. * @return string The escaped string
  269. */
  270. public static function escape_string($string)
  271. {
  272. /* The pdo::quote function adds a "'" character we need to remove that '
  273. because in Chamilo, developers builds a query like this:
  274. $sql = "SELECT * FROM $table WHERE id = 'Database::escape_string($id)'";
  275. otherwise we will have an error because the query will be:
  276. SELECT * FROM user WHERE id = ''1'' instead of
  277. SELECT * FROM user WHERE id = '1'
  278. */
  279. // $string = '_@_'.self::$db->quote($string).'_@_';
  280. $string = self::$db->quote($string);
  281. return trim($string, "'");
  282. return $string;
  283. }
  284. /**
  285. * Executes a query in the database
  286. * @author Julio Montoya
  287. * @param string $query The SQL query
  288. * @return Statement
  289. */
  290. public static function query($query)
  291. {
  292. $isWriteQuery = self::isWriteQuery($query);
  293. if ($isWriteQuery) {
  294. $connection = self::$connectionWrite;
  295. } else {
  296. $connection = self::$connectionRead;
  297. }
  298. /* The solution below does not work because there are some case where we use the "LIKE" option like this:
  299. $sql = 'SELECT * FROM user WHERE id LIKE "%'.Database::escape_string($id).' %" ;
  300. Chamilo queries are formed in many ways:
  301. $sql = "SELECT * FROM user WHERE id = '".Database::escape_string($id)."'; or
  302. $sql = 'SELECT * FROM user WHERE id = '.Database::escape_string($id).';
  303. The problem here is that the function escape_string() calls the quote function that adds a "'" string.
  304. Instead of this we're adding a identifier __@__ so we can identify those cases and replace with a simple '
  305. */
  306. //var_dump($query);
  307. /*$query = str_replace(
  308. array(
  309. "\"_@_'",
  310. "'_@_\"",
  311. "'_@_'",
  312. "_@_'",
  313. "'_@_",
  314. ),
  315. "'",
  316. $query
  317. );*/
  318. //var_dump($query);
  319. return $connection->executeQuery($query);
  320. }
  321. public static function customOptionToDoctrineOption($option)
  322. {
  323. switch($option) {
  324. case 'ASSOC':
  325. return PDO::FETCH_ASSOC;
  326. break;
  327. case 'NUM':
  328. return PDO::FETCH_NUM;
  329. break;
  330. case 'BOTH':
  331. default:
  332. return PDO::FETCH_BOTH;
  333. break;
  334. }
  335. }
  336. /**
  337. * Stores a query result into an array.
  338. * @param Statement $result - the return value of the query
  339. * @param option BOTH, ASSOC, or NUM
  340. * @return array - the value returned by the query
  341. */
  342. public static function store_result(Statement $result, $option = 'BOTH')
  343. {
  344. return $result->fetchAll(self::customOptionToDoctrineOption($option));
  345. /*
  346. var_dump($a );
  347. $array = array();
  348. if ($result !== false) { // For isolation from database engine's behaviour.
  349. while ($row = self::fetch_array($result, $option)) {
  350. $array[] = $row;
  351. }
  352. }
  353. return $array;*/
  354. }
  355. /*
  356. Private methods
  357. You should not access these from outside the class
  358. No effort is made to keep the names / results the same.
  359. */
  360. /**
  361. * Structures a database and table name to ready them
  362. * for querying. The database parameter is considered not glued,
  363. * just plain e.g. COURSE001
  364. * @todo not sure if we need this now
  365. */
  366. private static function format_table_name($database, $table)
  367. {
  368. /*$glue = '`.`';
  369. $table_name = '`'.$database.$glue.$table.'`';
  370. */
  371. return $table;
  372. //return $table_name;
  373. }
  374. /*
  375. New useful DB functions
  376. */
  377. /**
  378. * Executes an insert to in the database (dbal already escape strings)
  379. * @param string table name
  380. * @param array An array of field and values
  381. * @param bool show query
  382. * @return int the id of the latest executed query
  383. */
  384. public static function insert($table_name, $attributes, $show_query = false)
  385. {
  386. $result = self::$connectionWrite->insert($table_name, $attributes);
  387. if ($result) {
  388. return self::insert_id();
  389. }
  390. return false;
  391. }
  392. /**
  393. * Experimental useful database finder
  394. * @todo lot of stuff to do here
  395. * @todo known issues, it doesn't work when using LIKE conditions
  396. * @example array('where'=> array('course_code LIKE "?%"'))
  397. * @example array('where'=> array('type = ? AND category = ?' => array('setting', 'Plugins'))
  398. * @example array('where'=> array('name = "Julio" AND lastname = "montoya"))
  399. */
  400. public static function select($columns, $table_name, $conditions = array(), $type_result = 'all', $option = 'ASSOC')
  401. {
  402. //$qb = self::$db->createQueryBuilder();
  403. $conditions = self::parse_conditions($conditions);
  404. //@todo we could do a describe here to check the columns ...
  405. $clean_columns = '';
  406. if (is_array($columns)) {
  407. $clean_columns = implode(',', $columns);
  408. } else {
  409. if ($columns == '*') {
  410. $clean_columns = '*';
  411. } else {
  412. $clean_columns = (string)$columns;
  413. }
  414. }
  415. /*$qb->select($clean_columns);
  416. $qb->from($table_name, 'table');
  417. $qb->orderBy('table.' . $sort_order, 'ASC');*/
  418. $sql = "SELECT $clean_columns FROM $table_name $conditions";
  419. //var_dump($sql);
  420. $result = self::query($sql);
  421. $array = array();
  422. if ($type_result == 'all') {
  423. while ($row = self::fetch_array($result, $option)) {
  424. if (isset($row['id'])) {
  425. $array[$row['id']] = $row;
  426. } else {
  427. $array[] = $row;
  428. }
  429. }
  430. } else {
  431. $array = self::fetch_array($result, $option);
  432. }
  433. return $array;
  434. }
  435. /**
  436. * Parses WHERE/ORDER conditions i.e array('where'=>array('id = ?' =>'4'), 'order'=>'id DESC'))
  437. * @todo known issues, it doesn't work when using LIKE conditions example: array('where'=>array('course_code LIKE "?%"'))
  438. * @param array
  439. * @return string
  440. * @todo lot of stuff to do here
  441. */
  442. static function parse_conditions($conditions)
  443. {
  444. if (empty($conditions)) {
  445. return '';
  446. }
  447. $return_value = $where_return = '';
  448. foreach ($conditions as $type_condition => $condition_data) {
  449. if ($condition_data == false) {
  450. continue;
  451. }
  452. $type_condition = strtolower($type_condition);
  453. switch ($type_condition) {
  454. case 'where':
  455. foreach ($condition_data as $condition => $value_array) {
  456. if (is_array($value_array)) {
  457. $clean_values = array();
  458. foreach ($value_array as $item) {
  459. $item = Database::escape_string($item);
  460. $clean_values[] = $item;
  461. }
  462. } else {
  463. $value_array = Database::escape_string($value_array);
  464. $clean_values = $value_array;
  465. }
  466. if (!empty($condition) && $clean_values != '') {
  467. $condition = str_replace('%', "'@percentage@'", $condition); //replace "%"
  468. $condition = str_replace("'?'", "%s", $condition);
  469. $condition = str_replace("?", "%s", $condition);
  470. $condition = str_replace("@%s@", "@-@", $condition);
  471. $condition = str_replace("%s", "'%s'", $condition);
  472. $condition = str_replace("@-@", "@%s@", $condition);
  473. //Treat conditons as string
  474. $condition = vsprintf($condition, $clean_values);
  475. $condition = str_replace('@percentage@', '%', $condition); //replace "%"
  476. $where_return .= $condition;
  477. }
  478. }
  479. if (!empty($where_return)) {
  480. $return_value = " WHERE $where_return";
  481. }
  482. break;
  483. case 'order':
  484. $order_array = $condition_data;
  485. if (!empty($order_array)) {
  486. // 'order' => 'id desc, name desc'
  487. $order_array = $order_array;
  488. $new_order_array = explode(',', $order_array);
  489. $temp_value = array();
  490. foreach ($new_order_array as $element) {
  491. $element = explode(' ', $element);
  492. $element = array_filter($element);
  493. $element = array_values($element);
  494. if (!empty($element[1])) {
  495. $element[1] = strtolower($element[1]);
  496. $order = 'DESC';
  497. if (in_array($element[1], array('desc', 'asc'))) {
  498. $order = $element[1];
  499. }
  500. $temp_value[] = $element[0].' '.$order.' ';
  501. } else {
  502. //by default DESC
  503. $temp_value[] = $element[0].' DESC ';
  504. }
  505. }
  506. if (!empty($temp_value)) {
  507. $return_value .= ' ORDER BY '.implode(', ', $temp_value);
  508. }
  509. }
  510. break;
  511. case 'limit':
  512. $limit_array = explode(',', $condition_data);
  513. if (!empty($limit_array)) {
  514. if (count($limit_array) > 1) {
  515. $return_value .= ' LIMIT '.intval($limit_array[0]).' , '.intval($limit_array[1]);
  516. } else {
  517. $return_value .= ' LIMIT '.intval($limit_array[0]);
  518. }
  519. }
  520. break;
  521. }
  522. }
  523. return $return_value;
  524. }
  525. /**
  526. * @param array $conditions
  527. * @return string
  528. */
  529. public static function parse_where_conditions($conditions)
  530. {
  531. return self::parse_conditions(array('where' => $conditions));
  532. }
  533. /**
  534. * Deletes an item depending of conditions
  535. * @param string $table_name
  536. * @param array $where_conditions
  537. * @param bool $show_query
  538. * @return int
  539. */
  540. public static function delete($table_name, $where_conditions, $show_query = false)
  541. {
  542. //return self::$connectionWrite->delete($table_name, $where_conditions);
  543. $where_return = self::parse_where_conditions($where_conditions);
  544. $sql = "DELETE FROM $table_name $where_return ";
  545. if ($show_query) {
  546. echo $sql;
  547. echo '<br />';
  548. }
  549. $result = self::query($sql);
  550. $affected_rows = self::affected_rows($result);
  551. //@todo should return affected_rows for
  552. return $affected_rows;
  553. }
  554. /**
  555. * Experimental useful database update
  556. * @param string table name use Database::get_main_table
  557. * @param array array with values to updates, keys are the fields in the database:
  558. * @example: $params['name'] = 'Julio'; $params['lastname'] = 'Montoya';
  559. * @param array where conditions i.e array('id = ?' =>'4')
  560. * @param bool show query
  561. * @todo lot of stuff to do here
  562. */
  563. public static function update($table_name, $attributes, $where_conditions = array(), $show_query = false)
  564. {
  565. if (!empty($table_name) && !empty($attributes)) {
  566. $update_sql = '';
  567. //Cleaning attributes
  568. $count = 1;
  569. foreach ($attributes as $key => $value) {
  570. if (!is_array($value)) {
  571. $value = self::escape_string($value);
  572. }
  573. $update_sql .= "$key = '$value' ";
  574. if ($count < count($attributes)) {
  575. $update_sql .= ', ';
  576. }
  577. $count++;
  578. }
  579. if (!empty($update_sql)) {
  580. //Parsing and cleaning the where conditions
  581. $where_return = self::parse_where_conditions($where_conditions);
  582. $sql = "UPDATE $table_name SET $update_sql $where_return ";
  583. if ($show_query) {
  584. var_dump($sql);
  585. }
  586. $result = self::query($sql);
  587. $affected_rows = self::affected_rows($result);
  588. return $affected_rows;
  589. }
  590. }
  591. return false;
  592. }
  593. /**
  594. * Counts the number of rows in a table
  595. * @param string $table The table of which the rows should be counted
  596. * @return int The number of rows in the given table.
  597. */
  598. public static function count_rows($table)
  599. {
  600. $obj = self::fetch_object(self::query("SELECT COUNT(*) AS n FROM $table"));
  601. return $obj->n;
  602. }
  603. /**
  604. * Returns a list of tables within a database. The list may contain all of the
  605. * available table names or filtered table names by using a pattern.
  606. * @param string $database (optional) The name of the examined database.
  607. * @param string $pattern (optional) A pattern for filtering table names as if it was needed for the SQL's LIKE clause, for example 'access_%'.
  608. * @deprecated
  609. * @return array Returns in an array the retrieved list of table names.
  610. */
  611. public static function get_tables($database = '', $pattern = '')
  612. {
  613. $result = array();
  614. $query = "SHOW TABLES";
  615. if (!empty($database)) {
  616. $query .= " FROM `".self::escape_string($database)."`";
  617. }
  618. if (!empty($pattern)) {
  619. $query .= " LIKE '".self::escape_string($pattern)."'";
  620. }
  621. $query_result = Database::query($query);
  622. while ($row = Database::fetch_row($query_result)) {
  623. $result[] = $row[0];
  624. }
  625. return $result;
  626. }
  627. /**
  628. * Returns a list of databases created on the server. The list may contain all of the
  629. * available database names or filtered database names by using a pattern.
  630. * @return array Returns in an array the retrieved list of database names.
  631. */
  632. public static function get_databases()
  633. {
  634. $sm = self::$db->getSchemaManager();
  635. return $sm->listDatabases();
  636. }
  637. }