database.lib.php 23 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. use Doctrine\Common\Annotations\AnnotationRegistry;
  4. use Doctrine\DBAL\Connection;
  5. use Doctrine\DBAL\Driver\Statement;
  6. use Doctrine\DBAL\Types\Type;
  7. use Doctrine\ORM\EntityManager;
  8. use Symfony\Component\Debug\ExceptionHandler;
  9. /**
  10. * Class Database.
  11. */
  12. class Database
  13. {
  14. public static $utcDateTimeClass;
  15. /**
  16. * @var EntityManager
  17. */
  18. private static $em;
  19. private static $connection;
  20. /**
  21. * @param EntityManager $em
  22. */
  23. public function setManager($em)
  24. {
  25. self::$em = $em;
  26. }
  27. /**
  28. * @param Connection $connection
  29. */
  30. public function setConnection(Connection $connection)
  31. {
  32. self::$connection = $connection;
  33. }
  34. /**
  35. * @return Connection
  36. */
  37. public function getConnection()
  38. {
  39. return self::$connection;
  40. }
  41. /**
  42. * @return EntityManager
  43. */
  44. public static function getManager()
  45. {
  46. return self::$em;
  47. }
  48. /**
  49. * Returns the name of the main database.
  50. *
  51. * @return string
  52. */
  53. public static function get_main_database()
  54. {
  55. return self::getManager()->getConnection()->getDatabase();
  56. }
  57. /**
  58. * Get main table.
  59. *
  60. * @param string $table
  61. *
  62. * @return string
  63. */
  64. public static function get_main_table($table)
  65. {
  66. return $table;
  67. }
  68. /**
  69. * Get course table.
  70. *
  71. * @param string $table
  72. *
  73. * @return string
  74. */
  75. public static function get_course_table($table)
  76. {
  77. return DB_COURSE_PREFIX.$table;
  78. }
  79. /**
  80. * Counts the number of rows in a table.
  81. *
  82. * @param string $table The table of which the rows should be counted
  83. *
  84. * @return int the number of rows in the given table
  85. *
  86. * @deprecated
  87. */
  88. public static function count_rows($table)
  89. {
  90. $obj = self::fetch_object(self::query("SELECT COUNT(*) AS n FROM $table"));
  91. return $obj->n;
  92. }
  93. /**
  94. * Returns the number of affected rows in the last database operation.
  95. *
  96. * @param Statement $result
  97. *
  98. * @return int
  99. */
  100. public static function affected_rows(Statement $result)
  101. {
  102. return $result->rowCount();
  103. }
  104. /**
  105. * @return string
  106. */
  107. public static function getUTCDateTimeTypeClass()
  108. {
  109. return isset(self::$utcDateTimeClass) ? self::$utcDateTimeClass : 'Application\DoctrineExtensions\DBAL\Types\UTCDateTimeType';
  110. }
  111. /**
  112. * Connect to the database sets the entity manager.
  113. *
  114. * @param array $params
  115. * @param string $sysPath
  116. * @param string $entityRootPath
  117. * @param bool $returnConnection
  118. * @param bool $returnManager
  119. *
  120. * @throws \Doctrine\ORM\ORMException
  121. *
  122. * @return
  123. */
  124. public function connect(
  125. $params = [],
  126. $sysPath = '',
  127. $entityRootPath = '',
  128. $returnConnection = false,
  129. $returnManager = false
  130. ) {
  131. $config = self::getDoctrineConfig($entityRootPath);
  132. $config->setAutoGenerateProxyClasses(true);
  133. $config->setEntityNamespaces(
  134. [
  135. 'ChamiloUserBundle' => 'Chamilo\UserBundle\Entity',
  136. 'ChamiloCoreBundle' => 'Chamilo\CoreBundle\Entity',
  137. 'ChamiloCourseBundle' => 'Chamilo\CourseBundle\Entity',
  138. 'ChamiloSkillBundle' => 'Chamilo\SkillBundle\Entity',
  139. 'ChamiloTicketBundle' => 'Chamilo\TicketBundle\Entity',
  140. 'ChamiloPluginBundle' => 'Chamilo\PluginBundle\Entity',
  141. 'ChamiloFaqBundle' => 'Chamilo\FaqBundle\Entity',
  142. ]
  143. );
  144. $params['charset'] = 'utf8';
  145. $entityManager = EntityManager::create($params, $config);
  146. $sysPath = !empty($sysPath) ? $sysPath : api_get_path(SYS_PATH);
  147. // Registering Constraints
  148. // Registering Constraints
  149. AnnotationRegistry::registerAutoloadNamespace(
  150. 'Symfony\Component\Validator\Constraint',
  151. $sysPath."vendor/symfony/symfony/src"
  152. );
  153. AnnotationRegistry::registerFile(
  154. $sysPath."vendor/symfony/symfony/src/Symfony/Bridge/Doctrine/Validator/Constraints/UniqueEntity.php"
  155. );
  156. // Registering gedmo extensions
  157. AnnotationRegistry::registerAutoloadNamespace(
  158. 'Gedmo\Mapping\Annotation',
  159. $sysPath."vendor/gedmo/doctrine-extensions/lib"
  160. );
  161. Type::overrideType(
  162. Type::DATETIME,
  163. self::getUTCDateTimeTypeClass()
  164. );
  165. $listener = new \Gedmo\Timestampable\TimestampableListener();
  166. $entityManager->getEventManager()->addEventSubscriber($listener);
  167. $listener = new \Gedmo\Tree\TreeListener();
  168. $entityManager->getEventManager()->addEventSubscriber($listener);
  169. $listener = new \Gedmo\Sortable\SortableListener();
  170. $entityManager->getEventManager()->addEventSubscriber($listener);
  171. $connection = $entityManager->getConnection();
  172. $connection->executeQuery('SET sql_mode = "";');
  173. $connection->executeQuery('SET SESSION sql_mode = ""');
  174. if ($returnConnection) {
  175. return $connection;
  176. }
  177. if ($returnManager) {
  178. return $entityManager;
  179. }
  180. $this->setConnection($connection);
  181. $this->setManager($entityManager);
  182. }
  183. /**
  184. * Escape MySQL wildchars _ and % in LIKE search.
  185. *
  186. * @param string $text The string to escape
  187. *
  188. * @return string The escaped string
  189. */
  190. public static function escape_sql_wildcards($text)
  191. {
  192. $text = api_preg_replace("/_/", "\_", $text);
  193. $text = api_preg_replace("/%/", "\%", $text);
  194. return $text;
  195. }
  196. /**
  197. * Escapes a string to insert into the database as text.
  198. *
  199. * @param string $string
  200. *
  201. * @return string
  202. */
  203. public static function escape_string($string)
  204. {
  205. $string = self::getManager()->getConnection()->quote($string);
  206. // The quote method from PDO also adds quotes around the string, which
  207. // is not how the legacy mysql_real_escape_string() was used in
  208. // Chamilo, so we need to remove the quotes around. Using trim will
  209. // remove more than one quote if they are sequenced, generating
  210. // broken queries and SQL injection risks
  211. return substr($string, 1, -1);
  212. }
  213. /**
  214. * Gets the array from a SQL result (as returned by Database::query).
  215. *
  216. * @param Statement $result
  217. * @param string $option Optional: "ASSOC","NUM" or "BOTH"
  218. *
  219. * @return array|mixed
  220. */
  221. public static function fetch_array(Statement $result, $option = 'BOTH')
  222. {
  223. if ($result === false) {
  224. return [];
  225. }
  226. return $result->fetch(self::customOptionToDoctrineOption($option));
  227. }
  228. /**
  229. * Gets an associative array from a SQL result (as returned by Database::query).
  230. *
  231. * @param Statement $result
  232. *
  233. * @return array
  234. */
  235. public static function fetch_assoc(Statement $result)
  236. {
  237. return $result->fetch(PDO::FETCH_ASSOC);
  238. }
  239. /**
  240. * Gets the next row of the result of the SQL query
  241. * (as returned by Database::query) in an object form.
  242. *
  243. * @param Statement $result
  244. *
  245. * @return mixed
  246. */
  247. public static function fetch_object(Statement $result)
  248. {
  249. return $result->fetch(PDO::FETCH_OBJ);
  250. }
  251. /**
  252. * Gets the array from a SQL result (as returned by Database::query)
  253. * help achieving database independence.
  254. *
  255. * @param Statement $result
  256. *
  257. * @return mixed
  258. */
  259. public static function fetch_row(Statement $result)
  260. {
  261. if ($result === false) {
  262. return [];
  263. }
  264. return $result->fetch(PDO::FETCH_NUM);
  265. }
  266. /**
  267. * Frees all the memory associated with the provided result identifier.
  268. *
  269. * @return bool|null Returns TRUE on success or FALSE on failure.
  270. * Notes: Use this method if you are concerned about how much memory is being used for queries that return large result sets.
  271. * Anyway, all associated result memory is automatically freed at the end of the script's execution.
  272. */
  273. public static function free_result(Statement $result)
  274. {
  275. $result->closeCursor();
  276. }
  277. /**
  278. * Gets the ID of the last item inserted into the database.
  279. *
  280. * @return string
  281. */
  282. public static function insert_id()
  283. {
  284. return self::getManager()->getConnection()->lastInsertId();
  285. }
  286. /**
  287. * @param Statement $result
  288. *
  289. * @return int
  290. */
  291. public static function num_rows(Statement $result)
  292. {
  293. if ($result === false) {
  294. return 0;
  295. }
  296. return $result->rowCount();
  297. }
  298. /**
  299. * Acts as the relative *_result() function of most DB drivers and fetches a
  300. * specific line and a field.
  301. *
  302. * @param Statement $resource
  303. * @param int $row
  304. * @param string $field
  305. *
  306. * @return mixed
  307. */
  308. public static function result(Statement $resource, $row, $field = '')
  309. {
  310. if ($resource->rowCount() > 0) {
  311. $result = $resource->fetchAll(PDO::FETCH_BOTH);
  312. return $result[$row][$field];
  313. }
  314. return false;
  315. }
  316. /**
  317. * @param string $query
  318. *
  319. * @return Statement
  320. */
  321. public static function query($query)
  322. {
  323. $connection = self::getManager()->getConnection();
  324. $result = null;
  325. try {
  326. $result = $connection->executeQuery($query);
  327. } catch (Exception $e) {
  328. self::handleError($e);
  329. }
  330. return $result;
  331. }
  332. /**
  333. * @param Exception $e
  334. */
  335. public static function handleError($e)
  336. {
  337. $debug = api_get_setting('server_type') == 'test';
  338. if ($debug) {
  339. // We use Symfony exception handler for better error information
  340. $handler = new ExceptionHandler();
  341. $handler->handle($e);
  342. exit;
  343. } else {
  344. error_log($e->getMessage());
  345. api_not_allowed(false, get_lang('GeneralError'));
  346. exit;
  347. }
  348. }
  349. /**
  350. * @param string $option
  351. *
  352. * @return int
  353. */
  354. public static function customOptionToDoctrineOption($option)
  355. {
  356. switch ($option) {
  357. case 'ASSOC':
  358. return PDO::FETCH_ASSOC;
  359. break;
  360. case 'NUM':
  361. return PDO::FETCH_NUM;
  362. break;
  363. case 'BOTH':
  364. default:
  365. return PDO::FETCH_BOTH;
  366. break;
  367. }
  368. }
  369. /**
  370. * Stores a query result into an array.
  371. *
  372. * @author Olivier Brouckaert
  373. *
  374. * @param Statement $result - the return value of the query
  375. * @param string $option BOTH, ASSOC, or NUM
  376. *
  377. * @return array - the value returned by the query
  378. */
  379. public static function store_result(Statement $result, $option = 'BOTH')
  380. {
  381. return $result->fetchAll(self::customOptionToDoctrineOption($option));
  382. }
  383. /**
  384. * Database insert.
  385. *
  386. * @param string $table_name
  387. * @param array $attributes
  388. * @param bool $show_query
  389. *
  390. * @return false|int
  391. */
  392. public static function insert($table_name, $attributes, $show_query = false)
  393. {
  394. if (empty($attributes) || empty($table_name)) {
  395. return false;
  396. }
  397. $params = array_keys($attributes);
  398. if (!empty($params)) {
  399. $sql = 'INSERT INTO '.$table_name.' ('.implode(',', $params).')
  400. VALUES (:'.implode(', :', $params).')';
  401. $statement = self::getManager()->getConnection()->prepare($sql);
  402. $result = $statement->execute($attributes);
  403. if ($show_query) {
  404. var_dump($sql);
  405. error_log($sql);
  406. }
  407. if ($result) {
  408. return (int) self::getManager()->getConnection()->lastInsertId();
  409. }
  410. }
  411. return false;
  412. }
  413. /**
  414. * @param string $tableName use Database::get_main_table
  415. * @param array $attributes Values to updates
  416. * Example: $params['name'] = 'Julio'; $params['lastname'] = 'Montoya';
  417. * @param array $whereConditions where conditions i.e array('id = ?' =>'4')
  418. * @param bool $showQuery
  419. *
  420. * @return bool|int
  421. */
  422. public static function update(
  423. $tableName,
  424. $attributes,
  425. $whereConditions = [],
  426. $showQuery = false
  427. ) {
  428. if (!empty($tableName) && !empty($attributes)) {
  429. $updateSql = '';
  430. $count = 1;
  431. foreach ($attributes as $key => $value) {
  432. if ($showQuery) {
  433. echo $key.': '.$value.PHP_EOL;
  434. }
  435. $updateSql .= "$key = :$key ";
  436. if ($count < count($attributes)) {
  437. $updateSql .= ', ';
  438. }
  439. $count++;
  440. }
  441. if (!empty($updateSql)) {
  442. //Parsing and cleaning the where conditions
  443. $whereReturn = self::parse_where_conditions($whereConditions);
  444. $sql = "UPDATE $tableName SET $updateSql $whereReturn ";
  445. $statement = self::getManager()->getConnection()->prepare($sql);
  446. $result = $statement->execute($attributes);
  447. if ($showQuery) {
  448. var_dump($sql);
  449. var_dump($attributes);
  450. var_dump($whereConditions);
  451. }
  452. if ($result) {
  453. return $statement->rowCount();
  454. }
  455. }
  456. }
  457. return false;
  458. }
  459. /**
  460. * Experimental useful database finder.
  461. *
  462. * @todo lot of stuff to do here
  463. * @todo known issues, it doesn't work when using LIKE conditions
  464. *
  465. * @example array('where'=> array('course_code LIKE "?%"'))
  466. * @example array('where'=> array('type = ? AND category = ?' => array('setting', 'Plugins'))
  467. * @example array('where'=> array('name = "Julio" AND lastname = "montoya"'))
  468. *
  469. * @param mixed $columns array (or string if only one column)
  470. * @param string $table_name
  471. * @param array $conditions
  472. * @param string $type_result
  473. * @param string $option
  474. * @param bool $debug
  475. *
  476. * @return array
  477. */
  478. public static function select(
  479. $columns,
  480. $table_name,
  481. $conditions = [],
  482. $type_result = 'all',
  483. $option = 'ASSOC',
  484. $debug = false
  485. ) {
  486. $conditions = self::parse_conditions($conditions);
  487. //@todo we could do a describe here to check the columns ...
  488. if (is_array($columns)) {
  489. $clean_columns = implode(',', $columns);
  490. } else {
  491. if ($columns == '*') {
  492. $clean_columns = '*';
  493. } else {
  494. $clean_columns = (string) $columns;
  495. }
  496. }
  497. $sql = "SELECT $clean_columns FROM $table_name $conditions";
  498. if ($debug) {
  499. var_dump($sql);
  500. }
  501. $result = self::query($sql);
  502. $array = [];
  503. if ($type_result === 'all') {
  504. while ($row = self::fetch_array($result, $option)) {
  505. if (isset($row['id'])) {
  506. $array[$row['id']] = $row;
  507. } else {
  508. $array[] = $row;
  509. }
  510. }
  511. } else {
  512. $array = self::fetch_array($result, $option);
  513. }
  514. return $array;
  515. }
  516. /**
  517. * Parses WHERE/ORDER conditions i.e array('where'=>array('id = ?' =>'4'), 'order'=>'id DESC').
  518. *
  519. * @todo known issues, it doesn't work when using
  520. * LIKE conditions example: array('where'=>array('course_code LIKE "?%"'))
  521. *
  522. * @param array $conditions
  523. *
  524. * @return string Partial SQL string to add to longer query
  525. */
  526. public static function parse_conditions($conditions)
  527. {
  528. if (empty($conditions)) {
  529. return '';
  530. }
  531. $return_value = $where_return = '';
  532. foreach ($conditions as $type_condition => $condition_data) {
  533. if ($condition_data == false) {
  534. continue;
  535. }
  536. $type_condition = strtolower($type_condition);
  537. switch ($type_condition) {
  538. case 'where':
  539. foreach ($condition_data as $condition => $value_array) {
  540. if (is_array($value_array)) {
  541. $clean_values = [];
  542. foreach ($value_array as $item) {
  543. $item = self::escape_string($item);
  544. $clean_values[] = $item;
  545. }
  546. } else {
  547. $value_array = self::escape_string($value_array);
  548. $clean_values = $value_array;
  549. }
  550. if (!empty($condition) && $clean_values != '') {
  551. $condition = str_replace('%', "'@percentage@'", $condition); //replace "%"
  552. $condition = str_replace("'?'", "%s", $condition);
  553. $condition = str_replace("?", "%s", $condition);
  554. $condition = str_replace("@%s@", "@-@", $condition);
  555. $condition = str_replace("%s", "'%s'", $condition);
  556. $condition = str_replace("@-@", "@%s@", $condition);
  557. // Treat conditions as string
  558. $condition = vsprintf($condition, $clean_values);
  559. $condition = str_replace('@percentage@', '%', $condition); //replace "%"
  560. $where_return .= $condition;
  561. }
  562. }
  563. if (!empty($where_return)) {
  564. $return_value = " WHERE $where_return";
  565. }
  566. break;
  567. case 'order':
  568. $order_array = $condition_data;
  569. if (!empty($order_array)) {
  570. // 'order' => 'id desc, name desc'
  571. $order_array = self::escape_string($order_array, null, false);
  572. $new_order_array = explode(',', $order_array);
  573. $temp_value = [];
  574. foreach ($new_order_array as $element) {
  575. $element = explode(' ', $element);
  576. $element = array_filter($element);
  577. $element = array_values($element);
  578. if (!empty($element[1])) {
  579. $element[1] = strtolower($element[1]);
  580. $order = 'DESC';
  581. if (in_array($element[1], ['desc', 'asc'])) {
  582. $order = $element[1];
  583. }
  584. $temp_value[] = $element[0].' '.$order.' ';
  585. } else {
  586. //by default DESC
  587. $temp_value[] = $element[0].' DESC ';
  588. }
  589. }
  590. if (!empty($temp_value)) {
  591. $return_value .= ' ORDER BY '.implode(', ', $temp_value);
  592. }
  593. }
  594. break;
  595. case 'limit':
  596. $limit_array = explode(',', $condition_data);
  597. if (!empty($limit_array)) {
  598. if (count($limit_array) > 1) {
  599. $return_value .= ' LIMIT '.intval($limit_array[0]).' , '.intval($limit_array[1]);
  600. } else {
  601. $return_value .= ' LIMIT '.intval($limit_array[0]);
  602. }
  603. }
  604. break;
  605. }
  606. }
  607. return $return_value;
  608. }
  609. /**
  610. * @param array $conditions
  611. *
  612. * @return string
  613. */
  614. public static function parse_where_conditions($conditions)
  615. {
  616. return self::parse_conditions(['where' => $conditions]);
  617. }
  618. /**
  619. * @param string $table_name
  620. * @param array $where_conditions
  621. * @param bool $show_query
  622. *
  623. * @return int
  624. */
  625. public static function delete($table_name, $where_conditions, $show_query = false)
  626. {
  627. $where_return = self::parse_where_conditions($where_conditions);
  628. $sql = "DELETE FROM $table_name $where_return ";
  629. if ($show_query) {
  630. echo $sql;
  631. echo '<br />';
  632. }
  633. $result = self::query($sql);
  634. $affected_rows = self::affected_rows($result);
  635. //@todo should return affected_rows for
  636. return $affected_rows;
  637. }
  638. /**
  639. * Get Doctrine configuration.
  640. *
  641. * @param string $path
  642. *
  643. * @return \Doctrine\ORM\Configuration
  644. */
  645. public static function getDoctrineConfig($path)
  646. {
  647. $isDevMode = true; // Forces doctrine to use ArrayCache instead of apc/xcache/memcache/redis
  648. $isSimpleMode = false; // related to annotations @Entity
  649. $cache = null;
  650. $path = !empty($path) ? $path : api_get_path(SYS_PATH);
  651. $paths = [
  652. //$path.'src/Chamilo/ClassificationBundle/Entity',
  653. //$path.'src/Chamilo/MediaBundle/Entity',
  654. //$path.'src/Chamilo/PageBundle/Entity',
  655. $path.'src/Chamilo/CoreBundle/Entity',
  656. $path.'src/Chamilo/UserBundle/Entity',
  657. $path.'src/Chamilo/CourseBundle/Entity',
  658. $path.'src/Chamilo/TicketBundle/Entity',
  659. $path.'src/Chamilo/SkillBundle/Entity',
  660. $path.'src/Chamilo/PluginBundle/Entity',
  661. //$path.'vendor/sonata-project/user-bundle/Entity',
  662. //$path.'vendor/sonata-project/user-bundle/Model',
  663. //$path.'vendor/friendsofsymfony/user-bundle/FOS/UserBundle/Entity',
  664. ];
  665. $proxyDir = $path.'app/cache/';
  666. $config = \Doctrine\ORM\Tools\Setup::createAnnotationMetadataConfiguration(
  667. $paths,
  668. $isDevMode,
  669. $proxyDir,
  670. $cache,
  671. $isSimpleMode
  672. );
  673. return $config;
  674. }
  675. /**
  676. * @param string $table
  677. *
  678. * @return bool
  679. */
  680. public static function tableExists($table)
  681. {
  682. return self::getManager()->getConnection()->getSchemaManager()->tablesExist($table);
  683. }
  684. /**
  685. * @param string $table
  686. *
  687. * @return \Doctrine\DBAL\Schema\Column[]
  688. */
  689. public static function listTableColumns($table)
  690. {
  691. return self::getManager()->getConnection()->getSchemaManager()->listTableColumns($table);
  692. }
  693. }