AdapterSqlite.php 3.8 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798
  1. <?php
  2. /**
  3. * @copyright Copyright (c) 2016, ownCloud, Inc.
  4. *
  5. * @author Bart Visscher <bartv@thisnet.nl>
  6. * @author Christoph Wurst <christoph@winzerhof-wurst.at>
  7. * @author Joas Schilling <coding@schilljs.com>
  8. * @author Morris Jobke <hey@morrisjobke.de>
  9. * @author Robin Appelman <robin@icewind.nl>
  10. * @author Thomas Müller <thomas.mueller@tmit.eu>
  11. *
  12. * @license AGPL-3.0
  13. *
  14. * This code is free software: you can redistribute it and/or modify
  15. * it under the terms of the GNU Affero General Public License, version 3,
  16. * as published by the Free Software Foundation.
  17. *
  18. * This program is distributed in the hope that it will be useful,
  19. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  20. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  21. * GNU Affero General Public License for more details.
  22. *
  23. * You should have received a copy of the GNU Affero General Public License, version 3,
  24. * along with this program. If not, see <http://www.gnu.org/licenses/>
  25. *
  26. */
  27. namespace OC\DB;
  28. use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
  29. class AdapterSqlite extends Adapter {
  30. /**
  31. * @param string $tableName
  32. */
  33. public function lockTable($tableName) {
  34. $this->conn->executeUpdate('BEGIN EXCLUSIVE TRANSACTION');
  35. }
  36. public function unlockTable() {
  37. $this->conn->executeUpdate('COMMIT TRANSACTION');
  38. }
  39. public function fixupStatement($statement) {
  40. $statement = preg_replace('/`(\w+)` ILIKE \?/', 'LOWER($1) LIKE LOWER(?)', $statement);
  41. $statement = str_replace('`', '"', $statement);
  42. $statement = str_ireplace('NOW()', 'datetime(\'now\')', $statement);
  43. $statement = str_ireplace('GREATEST(', 'MAX(', $statement);
  44. $statement = str_ireplace('UNIX_TIMESTAMP()', 'strftime(\'%s\',\'now\')', $statement);
  45. return $statement;
  46. }
  47. /**
  48. * Insert a row if the matching row does not exists. To accomplish proper race condition avoidance
  49. * it is needed that there is also a unique constraint on the values. Then this method will
  50. * catch the exception and return 0.
  51. *
  52. * @param string $table The table name (will replace *PREFIX* with the actual prefix)
  53. * @param array $input data that should be inserted into the table (column name => value)
  54. * @param array|null $compare List of values that should be checked for "if not exists"
  55. * If this is null or an empty array, all keys of $input will be compared
  56. * Please note: text fields (clob) must not be used in the compare array
  57. * @return int number of inserted rows
  58. * @throws \Doctrine\DBAL\Exception
  59. * @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
  60. */
  61. public function insertIfNotExist($table, $input, ?array $compare = null) {
  62. if (empty($compare)) {
  63. $compare = array_keys($input);
  64. }
  65. $fieldList = '`' . implode('`,`', array_keys($input)) . '`';
  66. $query = "INSERT INTO `$table` ($fieldList) SELECT "
  67. . str_repeat('?,', count($input) - 1).'? '
  68. . " WHERE NOT EXISTS (SELECT 1 FROM `$table` WHERE ";
  69. $inserts = array_values($input);
  70. foreach ($compare as $key) {
  71. $query .= '`' . $key . '`';
  72. if (is_null($input[$key])) {
  73. $query .= ' IS NULL AND ';
  74. } else {
  75. $inserts[] = $input[$key];
  76. $query .= ' = ? AND ';
  77. }
  78. }
  79. $query = substr($query, 0, -5);
  80. $query .= ')';
  81. try {
  82. return $this->conn->executeUpdate($query, $inserts);
  83. } catch (UniqueConstraintViolationException $e) {
  84. // if this is thrown then a concurrent insert happened between the insert and the sub-select in the insert, that should have avoided it
  85. // it's fine to ignore this then
  86. //
  87. // more discussions about this can be found at https://github.com/nextcloud/server/pull/12315
  88. return 0;
  89. }
  90. }
  91. }