database.lib.php 21 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708
  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 :
  106. 'Application\DoctrineExtensions\DBAL\Types\UTCDateTimeType';
  107. }
  108. /**
  109. * Connect to the database sets the entity manager.
  110. *
  111. * @param array $params
  112. * @param string $sysPath
  113. * @param string $entityRootPath
  114. *
  115. * @throws \Doctrine\ORM\ORMException
  116. *
  117. * @return
  118. */
  119. public function connect($params = array(), $sysPath = '', $entityRootPath = '', $returnConnection = false)
  120. {
  121. $config = self::getDoctrineConfig($entityRootPath);
  122. $config->setAutoGenerateProxyClasses(true);
  123. $config->setEntityNamespaces(
  124. array(
  125. 'ChamiloUserBundle' => 'Chamilo\UserBundle\Entity',
  126. 'ChamiloCoreBundle' => 'Chamilo\CoreBundle\Entity',
  127. 'ChamiloCourseBundle' => 'Chamilo\CourseBundle\Entity',
  128. 'ChamiloSkillBundle' => 'Chamilo\SkillBundle\Entity',
  129. 'ChamiloTicketBundle' => 'Chamilo\TicketBundle\Entity'
  130. )
  131. );
  132. $params['charset'] = 'utf8';
  133. $entityManager = EntityManager::create($params, $config);
  134. $sysPath = !empty($sysPath) ? $sysPath : api_get_path(SYS_PATH);
  135. // Registering Constraints
  136. AnnotationRegistry::registerAutoloadNamespace(
  137. 'Symfony\Component\Validator\Constraint',
  138. $sysPath."vendor/symfony/validator"
  139. );
  140. AnnotationRegistry::registerFile(
  141. $sysPath."vendor/symfony/doctrine-bridge/Validator/Constraints/UniqueEntity.php"
  142. );
  143. // Registering gedmo extensions
  144. AnnotationRegistry::registerAutoloadNamespace(
  145. 'Gedmo\Mapping\Annotation',
  146. $sysPath."vendor/gedmo/doctrine-extensions/lib"
  147. );
  148. Type::overrideType(
  149. Type::DATETIME,
  150. self::getUTCDateTimeTypeClass()
  151. );
  152. $listener = new \Gedmo\Timestampable\TimestampableListener();
  153. $entityManager->getEventManager()->addEventSubscriber($listener);
  154. $listener = new \Gedmo\Tree\TreeListener();
  155. $entityManager->getEventManager()->addEventSubscriber($listener);
  156. $listener = new \Gedmo\Sortable\SortableListener();
  157. $entityManager->getEventManager()->addEventSubscriber($listener);
  158. $connection = $entityManager->getConnection();
  159. $connection->executeQuery('SET sql_mode = "";');
  160. if ($returnConnection) {
  161. return $connection;
  162. }
  163. $this->setConnection($connection);
  164. $this->setManager($entityManager);
  165. }
  166. /**
  167. * Escape MySQL wildchars _ and % in LIKE search
  168. * @param string $text The string to escape
  169. *
  170. * @return string The escaped string
  171. */
  172. public static function escape_sql_wildcards($text)
  173. {
  174. $text = api_preg_replace("/_/", "\_", $text);
  175. $text = api_preg_replace("/%/", "\%", $text);
  176. return $text;
  177. }
  178. /**
  179. * Escapes a string to insert into the database as text
  180. *
  181. * @param string $string
  182. *
  183. * @return string
  184. */
  185. public static function escape_string($string)
  186. {
  187. $string = self::getManager()->getConnection()->quote($string);
  188. return trim($string, "'");
  189. }
  190. /**
  191. * Gets the array from a SQL result (as returned by Database::query)
  192. *
  193. * @param Statement $result
  194. * @param string $option Optional: "ASSOC","NUM" or "BOTH"
  195. *
  196. * @return array|mixed
  197. */
  198. public static function fetch_array(Statement $result, $option = 'BOTH')
  199. {
  200. if ($result === false) {
  201. return array();
  202. }
  203. return $result->fetch(self::customOptionToDoctrineOption($option));
  204. }
  205. /**
  206. * Gets an associative array from a SQL result (as returned by Database::query).
  207. *
  208. * @param Statement $result
  209. *
  210. * @return array
  211. */
  212. public static function fetch_assoc(Statement $result)
  213. {
  214. return $result->fetch(PDO::FETCH_ASSOC);
  215. }
  216. /**
  217. * Gets the next row of the result of the SQL query
  218. * (as returned by Database::query) in an object form
  219. *
  220. * @param Statement $result
  221. *
  222. * @return mixed
  223. */
  224. public static function fetch_object(Statement $result)
  225. {
  226. return $result->fetch(PDO::FETCH_OBJ);
  227. }
  228. /**
  229. * Gets the array from a SQL result (as returned by Database::query)
  230. * help achieving database independence
  231. *
  232. * @param Statement $result
  233. *
  234. * @return mixed
  235. */
  236. public static function fetch_row(Statement $result)
  237. {
  238. return $result->fetch(PDO::FETCH_NUM);
  239. }
  240. /**
  241. * Frees all the memory associated with the provided result identifier.
  242. * @return boolean|null Returns TRUE on success or FALSE on failure.
  243. * Notes: Use this method if you are concerned about how much memory is being used for queries that return large result sets.
  244. * Anyway, all associated result memory is automatically freed at the end of the script's execution.
  245. */
  246. public static function free_result(Statement $result)
  247. {
  248. $result->closeCursor();
  249. }
  250. /**
  251. * Gets the ID of the last item inserted into the database
  252. *
  253. * @return string
  254. */
  255. public static function insert_id()
  256. {
  257. return self::getManager()->getConnection()->lastInsertId();
  258. }
  259. /**
  260. * @param Statement $result
  261. *
  262. * @return int
  263. */
  264. public static function num_rows(Statement $result)
  265. {
  266. return $result->rowCount();
  267. }
  268. /**
  269. * Acts as the relative *_result() function of most DB drivers and fetches a
  270. * specific line and a field
  271. *
  272. * @param Statement $resource
  273. * @param int $row
  274. * @param string $field
  275. *
  276. * @return mixed
  277. */
  278. public static function result(Statement $resource, $row, $field = '')
  279. {
  280. if ($resource->rowCount() > 0) {
  281. $result = $resource->fetchAll(PDO::FETCH_BOTH);
  282. return $result[$row][$field];
  283. }
  284. }
  285. /**
  286. * @param string $query
  287. *
  288. * @return Statement
  289. *
  290. * @throws \Doctrine\DBAL\DBALException
  291. */
  292. public static function query($query)
  293. {
  294. $connection = self::getManager()->getConnection();
  295. if (api_get_setting('server_type') == 'test') {
  296. $result = $connection->executeQuery($query);
  297. } else {
  298. try {
  299. $result = $connection->executeQuery($query);
  300. } catch (Exception $e) {
  301. error_log($e->getMessage());
  302. api_not_allowed(false, get_lang('GeneralError'));
  303. exit;
  304. }
  305. }
  306. return $result;
  307. }
  308. /**
  309. * @param string $option
  310. *
  311. * @return int
  312. */
  313. public static function customOptionToDoctrineOption($option)
  314. {
  315. switch ($option) {
  316. case 'ASSOC':
  317. return PDO::FETCH_ASSOC;
  318. break;
  319. case 'NUM':
  320. return PDO::FETCH_NUM;
  321. break;
  322. case 'BOTH':
  323. default:
  324. return PDO::FETCH_BOTH;
  325. break;
  326. }
  327. }
  328. /**
  329. * Stores a query result into an array.
  330. *
  331. * @author Olivier Brouckaert
  332. * @param Statement $result - the return value of the query
  333. * @param string $option BOTH, ASSOC, or NUM
  334. *
  335. * @return array - the value returned by the query
  336. */
  337. public static function store_result(Statement $result, $option = 'BOTH')
  338. {
  339. return $result->fetchAll(self::customOptionToDoctrineOption($option));
  340. }
  341. /**
  342. * Database insert
  343. * @param string $table_name
  344. * @param array $attributes
  345. * @param bool $show_query
  346. *
  347. * @return false|string
  348. */
  349. public static function insert($table_name, $attributes, $show_query = false)
  350. {
  351. if (empty($attributes) || empty($table_name)) {
  352. return false;
  353. }
  354. $params = array_keys($attributes);
  355. if (!empty($params)) {
  356. $sql = 'INSERT INTO '.$table_name.' ('.implode(',', $params).')
  357. VALUES (:'.implode(', :' ,$params).')';
  358. $statement = self::getManager()->getConnection()->prepare($sql);
  359. $result = $statement->execute($attributes);
  360. if ($show_query) {
  361. var_dump($sql);
  362. error_log($sql);
  363. }
  364. if ($result) {
  365. return self::getManager()->getConnection()->lastInsertId();
  366. }
  367. }
  368. return false;
  369. }
  370. /**
  371. * @param string $table_name use Database::get_main_table
  372. * @param array $attributes Values to updates
  373. * Example: $params['name'] = 'Julio'; $params['lastname'] = 'Montoya';
  374. * @param array $where_conditions where conditions i.e array('id = ?' =>'4')
  375. * @param bool $show_query
  376. *
  377. * @return bool|int
  378. */
  379. public static function update(
  380. $table_name,
  381. $attributes,
  382. $where_conditions = array(),
  383. $show_query = false
  384. ) {
  385. if (!empty($table_name) && !empty($attributes)) {
  386. $update_sql = '';
  387. //Cleaning attributes
  388. $count = 1;
  389. foreach ($attributes as $key => $value) {
  390. $update_sql .= "$key = :$key ";
  391. if ($count < count($attributes)) {
  392. $update_sql.=', ';
  393. }
  394. $count++;
  395. }
  396. if (!empty($update_sql)) {
  397. //Parsing and cleaning the where conditions
  398. $where_return = self::parse_where_conditions($where_conditions);
  399. $sql = "UPDATE $table_name SET $update_sql $where_return ";
  400. $statement = self::getManager()->getConnection()->prepare($sql);
  401. $result = $statement->execute($attributes);
  402. if ($show_query) {
  403. var_dump($sql);
  404. }
  405. if ($result) {
  406. return $statement->rowCount();
  407. }
  408. }
  409. }
  410. return false;
  411. }
  412. /**
  413. * Experimental useful database finder
  414. * @todo lot of stuff to do here
  415. * @todo known issues, it doesn't work when using LIKE conditions
  416. * @example array('where'=> array('course_code LIKE "?%"'))
  417. * @example array('where'=> array('type = ? AND category = ?' => array('setting', 'Plugins'))
  418. * @example array('where'=> array('name = "Julio" AND lastname = "montoya"'))
  419. * @param array $columns
  420. * @param string $table_name
  421. * @param array $conditions
  422. * @param string $type_result
  423. * @param string $option
  424. * @return array
  425. */
  426. public static function select($columns, $table_name, $conditions = array(), $type_result = 'all', $option = 'ASSOC')
  427. {
  428. $conditions = self::parse_conditions($conditions);
  429. //@todo we could do a describe here to check the columns ...
  430. if (is_array($columns)) {
  431. $clean_columns = implode(',', $columns);
  432. } else {
  433. if ($columns == '*') {
  434. $clean_columns = '*';
  435. } else {
  436. $clean_columns = (string)$columns;
  437. }
  438. }
  439. $sql = "SELECT $clean_columns FROM $table_name $conditions";
  440. $result = self::query($sql);
  441. $array = array();
  442. if ($type_result == 'all') {
  443. while ($row = self::fetch_array($result, $option)) {
  444. if (isset($row['id'])) {
  445. $array[$row['id']] = $row;
  446. } else {
  447. $array[] = $row;
  448. }
  449. }
  450. } else {
  451. $array = self::fetch_array($result, $option);
  452. }
  453. return $array;
  454. }
  455. /**
  456. * Parses WHERE/ORDER conditions i.e array('where'=>array('id = ?' =>'4'), 'order'=>'id DESC'))
  457. * @todo known issues, it doesn't work when using
  458. * LIKE conditions example: array('where'=>array('course_code LIKE "?%"'))
  459. * @param array $conditions
  460. */
  461. public static function parse_conditions($conditions)
  462. {
  463. if (empty($conditions)) {
  464. return '';
  465. }
  466. $return_value = $where_return = '';
  467. foreach ($conditions as $type_condition => $condition_data) {
  468. if ($condition_data == false) {
  469. continue;
  470. }
  471. $type_condition = strtolower($type_condition);
  472. switch ($type_condition) {
  473. case 'where':
  474. foreach ($condition_data as $condition => $value_array) {
  475. if (is_array($value_array)) {
  476. $clean_values = array();
  477. foreach($value_array as $item) {
  478. $item = Database::escape_string($item);
  479. $clean_values[]= $item;
  480. }
  481. } else {
  482. $value_array = Database::escape_string($value_array);
  483. $clean_values = $value_array;
  484. }
  485. if (!empty($condition) && $clean_values != '') {
  486. $condition = str_replace('%',"'@percentage@'", $condition); //replace "%"
  487. $condition = str_replace("'?'","%s", $condition);
  488. $condition = str_replace("?","%s", $condition);
  489. $condition = str_replace("@%s@","@-@", $condition);
  490. $condition = str_replace("%s","'%s'", $condition);
  491. $condition = str_replace("@-@","@%s@", $condition);
  492. // Treat conditions as string
  493. $condition = vsprintf($condition, $clean_values);
  494. $condition = str_replace('@percentage@','%', $condition); //replace "%"
  495. $where_return .= $condition;
  496. }
  497. }
  498. if (!empty($where_return)) {
  499. $return_value = " WHERE $where_return" ;
  500. }
  501. break;
  502. case 'order':
  503. $order_array = $condition_data;
  504. if (!empty($order_array)) {
  505. // 'order' => 'id desc, name desc'
  506. $order_array = self::escape_string($order_array, null, false);
  507. $new_order_array = explode(',', $order_array);
  508. $temp_value = array();
  509. foreach($new_order_array as $element) {
  510. $element = explode(' ', $element);
  511. $element = array_filter($element);
  512. $element = array_values($element);
  513. if (!empty($element[1])) {
  514. $element[1] = strtolower($element[1]);
  515. $order = 'DESC';
  516. if (in_array($element[1], array('desc', 'asc'))) {
  517. $order = $element[1];
  518. }
  519. $temp_value[]= $element[0].' '.$order.' ';
  520. } else {
  521. //by default DESC
  522. $temp_value[]= $element[0].' DESC ';
  523. }
  524. }
  525. if (!empty($temp_value)) {
  526. $return_value .= ' ORDER BY '.implode(', ', $temp_value);
  527. } else {
  528. //$return_value .= '';
  529. }
  530. }
  531. break;
  532. case 'limit':
  533. $limit_array = explode(',', $condition_data);
  534. if (!empty($limit_array)) {
  535. if (count($limit_array) > 1) {
  536. $return_value .= ' LIMIT '.intval($limit_array[0]).' , '.intval($limit_array[1]);
  537. } else {
  538. $return_value .= ' LIMIT '.intval($limit_array[0]);
  539. }
  540. }
  541. break;
  542. }
  543. }
  544. return $return_value;
  545. }
  546. /**
  547. * @param array $conditions
  548. *
  549. * @return string
  550. */
  551. public static function parse_where_conditions($conditions)
  552. {
  553. return self::parse_conditions(array('where' => $conditions));
  554. }
  555. /**
  556. * @param string $table_name
  557. * @param array $where_conditions
  558. * @param bool $show_query
  559. *
  560. * @return int
  561. */
  562. public static function delete($table_name, $where_conditions, $show_query = false)
  563. {
  564. $where_return = self::parse_where_conditions($where_conditions);
  565. $sql = "DELETE FROM $table_name $where_return ";
  566. if ($show_query) { echo $sql; echo '<br />'; }
  567. $result = self::query($sql);
  568. $affected_rows = self::affected_rows($result);
  569. //@todo should return affected_rows for
  570. return $affected_rows;
  571. }
  572. /**
  573. * Get Doctrine configuration
  574. * @param string $path
  575. *
  576. * @return \Doctrine\ORM\Configuration
  577. */
  578. public static function getDoctrineConfig($path)
  579. {
  580. $isDevMode = false;
  581. $isSimpleMode = false; // related to annotations @Entity
  582. $cache = null;
  583. $path = !empty($path) ? $path : api_get_path(SYS_PATH);
  584. $paths = array(
  585. //$path.'src/Chamilo/ClassificationBundle/Entity',
  586. //$path.'src/Chamilo/MediaBundle/Entity',
  587. //$path.'src/Chamilo/PageBundle/Entity',
  588. $path.'src/Chamilo/CoreBundle/Entity',
  589. $path.'src/Chamilo/UserBundle/Entity',
  590. $path.'src/Chamilo/CourseBundle/Entity',
  591. $path.'src/Chamilo/TicketBundle/Entity',
  592. $path.'src/Chamilo/SkillBundle/Entity',
  593. //$path.'vendor/sonata-project/user-bundle/Entity',
  594. //$path.'vendor/sonata-project/user-bundle/Model',
  595. //$path.'vendor/friendsofsymfony/user-bundle/FOS/UserBundle/Entity',
  596. );
  597. $proxyDir = $path.'app/cache/';
  598. $config = \Doctrine\ORM\Tools\Setup::createAnnotationMetadataConfiguration(
  599. $paths,
  600. $isDevMode,
  601. $proxyDir,
  602. $cache,
  603. $isSimpleMode
  604. );
  605. return $config;
  606. }
  607. /**
  608. * @param string $table
  609. *
  610. * @return bool
  611. */
  612. public static function tableExists($table)
  613. {
  614. return self::getManager()->getConnection()->getSchemaManager()->tablesExist($table);
  615. }
  616. /**
  617. * @param string $table
  618. * @return \Doctrine\DBAL\Schema\Column[]
  619. */
  620. public static function listTableColumns($table)
  621. {
  622. return self::getManager()->getConnection()->getSchemaManager()->listTableColumns($table);
  623. }
  624. }