1 | <?php |
||
2 | |||
3 | /* For licensing terms, see /license.txt */ |
||
4 | |||
5 | use Doctrine\Common\Annotations\PsrCachedReader; |
||
6 | use Doctrine\Common\EventManager; |
||
7 | use Doctrine\DBAL\Connection; |
||
8 | use Doctrine\DBAL\Types\Type; |
||
9 | use Doctrine\ORM\Configuration; |
||
10 | use Doctrine\ORM\EntityManager; |
||
11 | use Symfony\Bridge\Doctrine\Types\UuidType; |
||
12 | use Symfony\Component\Cache\Adapter\ArrayAdapter; |
||
13 | |||
14 | class Database |
||
15 | { |
||
16 | private static EntityManager $em; |
||
17 | private static Connection $connection; |
||
18 | |||
19 | /** |
||
20 | * Setup doctrine only for the installation. |
||
21 | * |
||
22 | * @throws \Doctrine\ORM\ORMException |
||
23 | * @throws \Doctrine\DBAL\Exception |
||
24 | */ |
||
25 | public static function connect(array $params = [], string $entityRootPath = ''): void |
||
26 | { |
||
27 | $config = self::getDoctrineConfig($entityRootPath); |
||
28 | $config->setAutoGenerateProxyClasses(true); |
||
29 | $config->setEntityNamespaces( |
||
30 | [ |
||
31 | 'ChamiloCoreBundle' => 'Chamilo\CoreBundle\Entity', |
||
32 | 'ChamiloCourseBundle' => 'Chamilo\CourseBundle\Entity', |
||
33 | ] |
||
34 | ); |
||
35 | |||
36 | $params['charset'] = 'utf8'; |
||
37 | |||
38 | // standard annotation reader |
||
39 | $annotationReader = new Doctrine\Common\Annotations\AnnotationReader(); |
||
40 | $cachedAnnotationReader = new PsrCachedReader( |
||
41 | $annotationReader, |
||
42 | new ArrayAdapter() |
||
43 | ); |
||
44 | |||
45 | $evm = new EventManager(); |
||
46 | $timestampableListener = new Gedmo\Timestampable\TimestampableListener(); |
||
47 | $timestampableListener->setAnnotationReader($cachedAnnotationReader); |
||
48 | $evm->addEventSubscriber($timestampableListener); |
||
49 | |||
50 | $driverChain = new \Doctrine\Persistence\Mapping\Driver\MappingDriverChain(); |
||
51 | // load superclass metadata mapping only, into driver chain |
||
52 | // also registers Gedmo annotations.NOTE: you can personalize it |
||
53 | Gedmo\DoctrineExtensions::registerAbstractMappingIntoDriverChainORM( |
||
54 | $driverChain, // our metadata driver chain, to hook into |
||
55 | $cachedAnnotationReader // our cached annotation reader |
||
56 | ); |
||
57 | |||
58 | $entityManager = EntityManager::create($params, $config, $evm); |
||
59 | |||
60 | if (false === Type::hasType('uuid')) { |
||
61 | Type::addType('uuid', UuidType::class); |
||
62 | } |
||
63 | |||
64 | $connection = $entityManager->getConnection(); |
||
65 | |||
66 | self::setConnection($connection); |
||
67 | self::setManager($entityManager); |
||
68 | } |
||
69 | |||
70 | public static function setManager(EntityManager $em) |
||
71 | { |
||
72 | self::$em = $em; |
||
73 | } |
||
74 | |||
75 | public static function setConnection(Connection $connection) |
||
76 | { |
||
77 | self::$connection = $connection; |
||
78 | } |
||
79 | |||
80 | public static function getConnection(): Connection |
||
81 | { |
||
82 | return self::$connection; |
||
83 | } |
||
84 | |||
85 | public static function getManager(): EntityManager |
||
86 | { |
||
87 | return self::$em; |
||
88 | } |
||
89 | |||
90 | /** |
||
91 | * Returns the name of the main database. |
||
92 | * |
||
93 | * @throws \Doctrine\DBAL\Exception |
||
94 | */ |
||
95 | public static function get_main_database(): bool|string|null |
||
96 | { |
||
97 | return self::getManager()->getConnection()->getDatabase(); |
||
98 | } |
||
99 | |||
100 | /** |
||
101 | * Get main table. |
||
102 | */ |
||
103 | public static function get_main_table(string $table): string |
||
104 | { |
||
105 | return $table; |
||
106 | } |
||
107 | |||
108 | /** |
||
109 | * Get course table. |
||
110 | */ |
||
111 | public static function get_course_table(string $table): string |
||
112 | { |
||
113 | return DB_COURSE_PREFIX.$table; |
||
114 | } |
||
115 | |||
116 | /** |
||
117 | * Counts the number of rows in a table. |
||
118 | * |
||
119 | * @throws Exception |
||
120 | * @throws \Doctrine\DBAL\Exception |
||
121 | * |
||
122 | * @deprecated |
||
123 | */ |
||
124 | public static function count_rows(string $table): int |
||
125 | { |
||
126 | $result = self::query("SELECT COUNT(*) AS n FROM $table"); |
||
127 | |||
128 | return (int) $result->fetchOne(); |
||
129 | } |
||
130 | |||
131 | /** |
||
132 | * Returns the number of affected rows in the last database operation. |
||
133 | * |
||
134 | * @throws \Doctrine\DBAL\Exception |
||
135 | */ |
||
136 | public static function affected_rows(\Doctrine\DBAL\Result $result): int |
||
137 | { |
||
138 | return $result->rowCount(); |
||
139 | } |
||
140 | |||
141 | /** |
||
142 | * Escapes a string to insert into the database as text. |
||
143 | */ |
||
144 | public static function escape_string(mixed $string): string |
||
145 | { |
||
146 | $string = self::getManager()->getConnection()->quote($string); |
||
147 | // The quote method from PDO also adds quotes around the string, which |
||
148 | // is not how the legacy mysql_real_escape_string() was used in |
||
149 | // Chamilo, so we need to remove the quotes around. Using trim will |
||
150 | // remove more than one quote if they are sequenced, generating |
||
151 | // broken queries and SQL injection risks |
||
152 | return substr($string, 1, -1); |
||
153 | } |
||
154 | |||
155 | /** |
||
156 | * Gets the (associative) array from a SQL result (as returned by Database::query). |
||
157 | * |
||
158 | * @throws \Doctrine\DBAL\Exception |
||
159 | */ |
||
160 | public static function fetch_array(\Doctrine\DBAL\Result $result): mixed |
||
161 | { |
||
162 | $data = $result->fetchAssociative(); |
||
163 | |||
164 | if (empty($data)) { |
||
165 | return []; |
||
166 | } |
||
167 | |||
168 | $return = $data; |
||
169 | |||
170 | foreach ($data as $value) { |
||
171 | $return[] = $value; |
||
172 | } |
||
173 | |||
174 | return $return; |
||
175 | } |
||
176 | |||
177 | /** |
||
178 | * Gets an associative array from a SQL result (as returned by Database::query). |
||
179 | * |
||
180 | * @throws \Doctrine\DBAL\Exception |
||
181 | */ |
||
182 | public static function fetch_assoc(\Doctrine\DBAL\Result $result): array|bool |
||
183 | { |
||
184 | return $result->fetchAssociative(); |
||
185 | } |
||
186 | |||
187 | /** |
||
188 | * Gets the next row of the result of the SQL query |
||
189 | * (as returned by Database::query) in an object form. |
||
190 | * |
||
191 | * @throws \Doctrine\DBAL\Exception |
||
192 | */ |
||
193 | public static function fetch_object(\Doctrine\DBAL\Result $result): ?stdClass |
||
194 | { |
||
195 | $data = $result->fetchAssociative(); |
||
196 | |||
197 | if (empty($data)) { |
||
198 | return null; |
||
199 | } |
||
200 | |||
201 | $object = new stdClass(); |
||
202 | |||
203 | foreach ($data as $key => $value) { |
||
204 | $object->$key = $value; |
||
205 | } |
||
206 | |||
207 | return $object; |
||
208 | } |
||
209 | |||
210 | /** |
||
211 | * Gets the array from a SQL result (as returned by Database::query) |
||
212 | * help to achieve database independence. |
||
213 | * |
||
214 | * @throws \Doctrine\DBAL\Exception |
||
215 | */ |
||
216 | public static function fetch_row(\Doctrine\DBAL\Result $result): array |
||
217 | { |
||
218 | $row = $result->fetchNumeric(); |
||
219 | |||
220 | return empty($row) ? [] : $row; |
||
221 | } |
||
222 | |||
223 | /** |
||
224 | * Gets the ID of the last item inserted into the database. |
||
225 | * |
||
226 | * @throws \Doctrine\DBAL\Exception |
||
227 | */ |
||
228 | public static function insert_id(): int |
||
229 | { |
||
230 | return (int) self::getManager()->getConnection()->lastInsertId(); |
||
231 | } |
||
232 | |||
233 | /** |
||
234 | * @throws \Doctrine\DBAL\Exception |
||
235 | */ |
||
236 | public static function num_rows(\Doctrine\DBAL\Result $result): int |
||
237 | { |
||
238 | return $result->rowCount(); |
||
239 | } |
||
240 | |||
241 | /** |
||
242 | * Acts as the relative *_result() function of most DB drivers and fetches a |
||
243 | * specific line and a field. |
||
244 | * |
||
245 | * @throws \Doctrine\DBAL\Exception |
||
246 | */ |
||
247 | public static function result(\Doctrine\DBAL\Result $resource, int $row, string $field): mixed |
||
248 | { |
||
249 | if ($resource->rowCount() > 0) { |
||
250 | $result = $resource->fetchAllAssociative(); |
||
251 | |||
252 | $resultRow = $result[$row]; |
||
253 | |||
254 | foreach ($result[$row] as $value) { |
||
255 | $resultRow[] = $value; |
||
256 | } |
||
257 | |||
258 | return $resultRow[$field]; |
||
259 | } |
||
260 | |||
261 | return null; |
||
262 | } |
||
263 | |||
264 | /** |
||
265 | * Wrapper to executes a query on the defined database handler. |
||
266 | * @throws Exception |
||
267 | */ |
||
268 | public static function query(string $query): ?\Doctrine\DBAL\Result |
||
269 | { |
||
270 | $connection = self::getManager()->getConnection(); |
||
271 | $result = null; |
||
272 | try { |
||
273 | $result = $connection->executeQuery($query); |
||
274 | } catch (Exception $e) { |
||
275 | self::handleError($e); |
||
276 | } |
||
277 | |||
278 | return $result; |
||
279 | } |
||
280 | |||
281 | /** |
||
282 | * @throws Exception |
||
283 | */ |
||
284 | public static function handleError(Exception $e) |
||
285 | { |
||
286 | $debug = 'test' === api_get_setting('server_type'); |
||
287 | if ($debug) { |
||
288 | throw $e; |
||
289 | } else { |
||
290 | error_log($e->getMessage()); |
||
291 | api_not_allowed(false, get_lang('An error has occurred. Please contact your system administrator.')); |
||
292 | } |
||
293 | } |
||
294 | |||
295 | /** |
||
296 | * Stores a query result into an array. |
||
297 | * |
||
298 | * @throws \Doctrine\DBAL\Exception |
||
299 | */ |
||
300 | public static function store_result(\Doctrine\DBAL\Result $result, $option = 'BOTH'): array |
||
301 | { |
||
302 | if ('NUM' === $option) { |
||
303 | return $result->fetchAllNumeric(); |
||
304 | } |
||
305 | |||
306 | return $result->fetchAllAssociative(); |
||
307 | } |
||
308 | |||
309 | /** |
||
310 | * Database insert. |
||
311 | * |
||
312 | * @throws \Doctrine\DBAL\Exception |
||
313 | * @throws Exception |
||
314 | */ |
||
315 | public static function insert(string $table_name, array $attributes, bool $show_query = false): bool|int |
||
316 | { |
||
317 | if (empty($attributes) || empty($table_name)) { |
||
318 | return false; |
||
319 | } |
||
320 | |||
321 | $params = array_keys($attributes); |
||
322 | |||
323 | if (!empty($params)) { |
||
324 | $sql = 'INSERT INTO '.$table_name.' ('.implode(',', $params).') |
||
325 | VALUES (:'.implode(', :', $params).')'; |
||
326 | |||
327 | if ($show_query) { |
||
328 | var_dump($sql); |
||
0 ignored issues
–
show
Security
Debugging Code
introduced
by
Loading history...
|
|||
329 | error_log($sql); |
||
330 | } |
||
331 | |||
332 | try { |
||
333 | self::getConnection() |
||
334 | ->prepare($sql) |
||
335 | ->executeQuery($attributes) |
||
336 | ; |
||
337 | } catch (Exception $e) { |
||
338 | self::handleError($e); |
||
339 | |||
340 | return false; |
||
341 | } |
||
342 | |||
343 | return (int) self::getManager()->getConnection()->lastInsertId(); |
||
344 | } |
||
345 | |||
346 | return false; |
||
347 | } |
||
348 | |||
349 | /** |
||
350 | * Wrapper executing an SQL update query based on the given attributes array |
||
351 | * @param string $tableName use Database::get_main_table |
||
352 | * @param array $attributes Values to updates |
||
353 | * Example: $params['name'] = 'Julio'; $params['lastname'] = 'Montoya'; |
||
354 | * @param array $whereConditions where conditions i.e. array('id = ?' =>'4') |
||
355 | * @param bool $showQuery |
||
356 | * |
||
357 | * @throws Exception |
||
358 | * |
||
359 | * @return bool|int |
||
360 | */ |
||
361 | public static function update( |
||
362 | string $tableName, |
||
363 | array $attributes, |
||
364 | array $whereConditions = [], |
||
365 | bool $showQuery = false |
||
366 | ): bool|int { |
||
367 | if (!empty($tableName) && !empty($attributes)) { |
||
368 | $updateSql = ''; |
||
369 | $count = 1; |
||
370 | |||
371 | foreach ($attributes as $key => $value) { |
||
372 | if ($showQuery) { |
||
373 | echo $key.': '.$value.PHP_EOL; |
||
374 | } |
||
375 | $updateSql .= "$key = :$key "; |
||
376 | if ($count < count($attributes)) { |
||
377 | $updateSql .= ', '; |
||
378 | } |
||
379 | $count++; |
||
380 | } |
||
381 | |||
382 | if (!empty($updateSql)) { |
||
383 | // Parsing and cleaning the where conditions |
||
384 | $whereReturn = self::parse_where_conditions($whereConditions); |
||
385 | $sql = "UPDATE $tableName SET $updateSql $whereReturn "; |
||
386 | |||
387 | try { |
||
388 | $statement = self::getManager()->getConnection()->prepare($sql); |
||
389 | $result = $statement->executeQuery($attributes); |
||
390 | } catch (Exception $e) { |
||
391 | self::handleError($e); |
||
392 | |||
393 | return false; |
||
394 | } |
||
395 | |||
396 | if ($showQuery) { |
||
397 | var_dump($sql); |
||
0 ignored issues
–
show
|
|||
398 | var_dump($attributes); |
||
399 | var_dump($whereConditions); |
||
400 | } |
||
401 | |||
402 | return $result->rowCount(); |
||
403 | } |
||
404 | } |
||
405 | |||
406 | return false; |
||
407 | } |
||
408 | |||
409 | /** |
||
410 | * Experimental useful database finder. |
||
411 | * |
||
412 | * @throws Exception |
||
413 | * |
||
414 | * @todo lot of stuff to do here |
||
415 | * @todo known issues, it doesn't work when using LIKE conditions |
||
416 | * |
||
417 | * @example ['where'=> ['course_code LIKE "?%"']] |
||
418 | * @example ['where'=> ['type = ? AND category = ?' => ['setting', 'Plugins']]] |
||
419 | * @example ['where'=> ['name = "Julio" AND lastname = "montoya"']] |
||
420 | */ |
||
421 | public static function select( |
||
422 | string|array $columns, |
||
423 | string $table_name, |
||
424 | array $conditions = [], |
||
425 | string $type_result = 'all', |
||
426 | string $option = 'ASSOC', |
||
427 | bool $debug = false |
||
428 | ): int|array { |
||
429 | if ('count' === $type_result) { |
||
430 | $conditions['LIMIT'] = null; |
||
431 | $conditions['limit'] = null; |
||
432 | } |
||
433 | $conditions = self::parse_conditions($conditions); |
||
434 | |||
435 | //@todo we could do a describe here to check the columns ... |
||
436 | if (is_array($columns)) { |
||
437 | $clean_columns = implode(',', $columns); |
||
438 | } else { |
||
439 | if ('*' === $columns) { |
||
440 | $clean_columns = '*'; |
||
441 | } else { |
||
442 | $clean_columns = (string) $columns; |
||
443 | } |
||
444 | } |
||
445 | |||
446 | if ('count' === $type_result) { |
||
447 | $clean_columns = ' count(*) count '; |
||
448 | } |
||
449 | $sql = "SELECT $clean_columns FROM $table_name $conditions"; |
||
450 | if ($debug) { |
||
451 | var_dump($sql); |
||
0 ignored issues
–
show
|
|||
452 | } |
||
453 | $result = self::query($sql); |
||
454 | if ('count' === $type_result) { |
||
455 | $row = self::fetch_array($result); |
||
456 | if ($row) { |
||
457 | return (int) $row['count']; |
||
458 | } |
||
459 | |||
460 | return 0; |
||
461 | } |
||
462 | $array = []; |
||
463 | |||
464 | if ('all' === $type_result) { |
||
465 | while ($row = self::fetch_array($result)) { |
||
466 | if (isset($row['id'])) { |
||
467 | $array[$row['id']] = $row; |
||
468 | } else { |
||
469 | $array[] = $row; |
||
470 | } |
||
471 | } |
||
472 | } else { |
||
473 | $array = self::fetch_array($result); |
||
474 | } |
||
475 | |||
476 | return $array; |
||
477 | } |
||
478 | |||
479 | /** |
||
480 | * Parses WHERE/ORDER conditions i.e. array('where'=>array('id = ?' =>'4'), 'order'=>'id DESC'). |
||
481 | * |
||
482 | * @todo known issues, it doesn't work when using |
||
483 | * LIKE conditions example: array('where'=>array('course_code LIKE "?%"')) |
||
484 | */ |
||
485 | public static function parse_conditions(array $conditions): string |
||
486 | { |
||
487 | if (empty($conditions)) { |
||
488 | return ''; |
||
489 | } |
||
490 | $return_value = $where_return = ''; |
||
491 | foreach ($conditions as $type_condition => $condition_data) { |
||
492 | if (false == $condition_data) { |
||
493 | continue; |
||
494 | } |
||
495 | $type_condition = strtolower($type_condition); |
||
496 | switch ($type_condition) { |
||
497 | case 'where': |
||
498 | foreach ($condition_data as $condition => $value_array) { |
||
499 | if (is_array($value_array)) { |
||
500 | $clean_values = []; |
||
501 | foreach ($value_array as $item) { |
||
502 | $item = self::escape_string($item); |
||
503 | $clean_values[] = $item; |
||
504 | } |
||
505 | } else { |
||
506 | $value_array = self::escape_string($value_array); |
||
507 | $clean_values = [$value_array]; |
||
508 | } |
||
509 | |||
510 | if (!empty($condition) && '' != $clean_values) { |
||
511 | $condition = str_replace('%', "'@percentage@'", $condition); //replace "%" |
||
512 | $condition = str_replace("'?'", "%s", $condition); |
||
513 | $condition = str_replace("?", "%s", $condition); |
||
514 | |||
515 | $condition = str_replace("@%s@", "@-@", $condition); |
||
516 | $condition = str_replace("%s", "'%s'", $condition); |
||
517 | $condition = str_replace("@-@", "@%s@", $condition); |
||
518 | |||
519 | // Treat conditions as string |
||
520 | $condition = vsprintf($condition, $clean_values); |
||
521 | $condition = str_replace('@percentage@', '%', $condition); //replace "%" |
||
522 | $where_return .= $condition; |
||
523 | } |
||
524 | } |
||
525 | |||
526 | if (!empty($where_return)) { |
||
527 | $return_value = " WHERE $where_return"; |
||
528 | } |
||
529 | break; |
||
530 | case 'order': |
||
531 | $order_array = $condition_data; |
||
532 | |||
533 | if (!empty($order_array)) { |
||
534 | // 'order' => 'id desc, name desc' |
||
535 | $order_array = self::escape_string($order_array); |
||
536 | $new_order_array = explode(',', $order_array); |
||
537 | $temp_value = []; |
||
538 | |||
539 | foreach ($new_order_array as $element) { |
||
540 | $element = explode(' ', $element); |
||
541 | $element = array_filter($element); |
||
542 | $element = array_values($element); |
||
543 | |||
544 | if (!empty($element[1])) { |
||
545 | $element[1] = strtolower($element[1]); |
||
546 | $order = 'DESC'; |
||
547 | if (in_array($element[1], ['desc', 'asc'])) { |
||
548 | $order = $element[1]; |
||
549 | } |
||
550 | $temp_value[] = ' `'.$element[0].'` '.$order.' '; |
||
551 | } else { |
||
552 | //by default DESC |
||
553 | $temp_value[] = ' `'.$element[0].'` DESC '; |
||
554 | } |
||
555 | } |
||
556 | if (!empty($temp_value)) { |
||
557 | $return_value .= ' ORDER BY '.implode(', ', $temp_value); |
||
558 | } |
||
559 | } |
||
560 | break; |
||
561 | case 'limit': |
||
562 | $limit_array = explode(',', $condition_data); |
||
563 | if (!empty($limit_array)) { |
||
564 | if (count($limit_array) > 1) { |
||
565 | $return_value .= ' LIMIT '.intval($limit_array[0]).' , '.intval($limit_array[1]); |
||
566 | } else { |
||
567 | $return_value .= ' LIMIT '.intval($limit_array[0]); |
||
568 | } |
||
569 | } |
||
570 | break; |
||
571 | } |
||
572 | } |
||
573 | |||
574 | return $return_value; |
||
575 | } |
||
576 | |||
577 | public static function parse_where_conditions(array $conditions): string |
||
578 | { |
||
579 | return self::parse_conditions(['where' => $conditions]); |
||
580 | } |
||
581 | |||
582 | /** |
||
583 | * @throws Exception |
||
584 | */ |
||
585 | public static function delete(string $tableName, array $where_conditions, bool $show_query = false): int |
||
586 | { |
||
587 | $where_return = self::parse_where_conditions($where_conditions); |
||
588 | $sql = "DELETE FROM $tableName $where_return "; |
||
589 | if ($show_query) { |
||
590 | echo $sql; |
||
591 | echo '<br />'; |
||
592 | } |
||
593 | $result = self::query($sql); |
||
594 | |||
595 | return self::affected_rows($result); |
||
596 | } |
||
597 | |||
598 | /** |
||
599 | * Get Doctrine configuration. |
||
600 | */ |
||
601 | public static function getDoctrineConfig(string $path): Configuration |
||
602 | { |
||
603 | $cache = null; |
||
604 | $path = !empty($path) ? $path : api_get_path(SYMFONY_SYS_PATH); |
||
605 | |||
606 | $paths = [ |
||
607 | $path.'src/Chamilo/CoreBundle/Entity', |
||
608 | $path.'src/Chamilo/CourseBundle/Entity', |
||
609 | ]; |
||
610 | |||
611 | $proxyDir = $path.'var/cache/'; |
||
612 | |||
613 | return \Doctrine\ORM\Tools\Setup::createAnnotationMetadataConfiguration( |
||
614 | $paths, |
||
615 | true, // Forces doctrine to use ArrayCache instead of apc/xcache/memcache/redis |
||
616 | $proxyDir, |
||
617 | $cache, |
||
618 | false // related to annotations @Entity |
||
619 | ); |
||
620 | } |
||
621 | |||
622 | /** |
||
623 | * @throws \Doctrine\DBAL\Exception |
||
624 | */ |
||
625 | public static function tableExists(string $table): bool |
||
626 | { |
||
627 | return self::getManager()->getConnection()->createSchemaManager()->tablesExist($table); |
||
628 | } |
||
629 | |||
630 | /** |
||
631 | * @throws \Doctrine\DBAL\Exception |
||
632 | */ |
||
633 | public static function listTableColumns(string $table): array |
||
634 | { |
||
635 | return self::getManager()->getConnection()->createSchemaManager()->listTableColumns($table); |
||
636 | } |
||
637 | |||
638 | public static function escapeField($field): string |
||
639 | { |
||
640 | return self::escape_string(preg_replace("/[^a-zA-Z0-9_.]/", '', $field)); |
||
641 | } |
||
642 | } |
||
643 |