database.lib.php 23 KB

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