AdapterSqlite.php 3.0 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879
  1. <?php
  2. /**
  3. * SPDX-FileCopyrightText: 2016-2024 Nextcloud GmbH and Nextcloud contributors
  4. * SPDX-FileCopyrightText: 2016 ownCloud, Inc.
  5. * SPDX-License-Identifier: AGPL-3.0-only
  6. */
  7. namespace OC\DB;
  8. use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
  9. class AdapterSqlite extends Adapter {
  10. /**
  11. * @param string $tableName
  12. */
  13. public function lockTable($tableName) {
  14. $this->conn->executeUpdate('BEGIN EXCLUSIVE TRANSACTION');
  15. }
  16. public function unlockTable() {
  17. $this->conn->executeUpdate('COMMIT TRANSACTION');
  18. }
  19. public function fixupStatement($statement) {
  20. $statement = preg_replace('/`(\w+)` ILIKE \?/', 'LOWER($1) LIKE LOWER(?)', $statement);
  21. $statement = str_replace('`', '"', $statement);
  22. $statement = str_ireplace('NOW()', 'datetime(\'now\')', $statement);
  23. $statement = str_ireplace('GREATEST(', 'MAX(', $statement);
  24. $statement = str_ireplace('UNIX_TIMESTAMP()', 'strftime(\'%s\',\'now\')', $statement);
  25. return $statement;
  26. }
  27. /**
  28. * Insert a row if the matching row does not exists. To accomplish proper race condition avoidance
  29. * it is needed that there is also a unique constraint on the values. Then this method will
  30. * catch the exception and return 0.
  31. *
  32. * @param string $table The table name (will replace *PREFIX* with the actual prefix)
  33. * @param array $input data that should be inserted into the table (column name => value)
  34. * @param array|null $compare List of values that should be checked for "if not exists"
  35. * If this is null or an empty array, all keys of $input will be compared
  36. * Please note: text fields (clob) must not be used in the compare array
  37. * @return int number of inserted rows
  38. * @throws \Doctrine\DBAL\Exception
  39. * @deprecated 15.0.0 - use unique index and "try { $db->insert() } catch (UniqueConstraintViolationException $e) {}" instead, because it is more reliable and does not have the risk for deadlocks - see https://github.com/nextcloud/server/pull/12371
  40. */
  41. public function insertIfNotExist($table, $input, ?array $compare = null) {
  42. if (empty($compare)) {
  43. $compare = array_keys($input);
  44. }
  45. $fieldList = '`' . implode('`,`', array_keys($input)) . '`';
  46. $query = "INSERT INTO `$table` ($fieldList) SELECT "
  47. . str_repeat('?,', count($input) - 1).'? '
  48. . " WHERE NOT EXISTS (SELECT 1 FROM `$table` WHERE ";
  49. $inserts = array_values($input);
  50. foreach ($compare as $key) {
  51. $query .= '`' . $key . '`';
  52. if (is_null($input[$key])) {
  53. $query .= ' IS NULL AND ';
  54. } else {
  55. $inserts[] = $input[$key];
  56. $query .= ' = ? AND ';
  57. }
  58. }
  59. $query = substr($query, 0, -5);
  60. $query .= ')';
  61. try {
  62. return $this->conn->executeUpdate($query, $inserts);
  63. } catch (UniqueConstraintViolationException $e) {
  64. // if this is thrown then a concurrent insert happened between the insert and the sub-select in the insert, that should have avoided it
  65. // it's fine to ignore this then
  66. //
  67. // more discussions about this can be found at https://github.com/nextcloud/server/pull/12315
  68. return 0;
  69. }
  70. }
  71. }